USE [DB_PEMI]
GO

/****** Object:  Table [dbo].[x_auth]    Script Date: 8/28/2022 00:24:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[x_auth](
	[xauth_id] [int] IDENTITY(1,1) NOT NULL,
	[xauth_name] [varchar](50) NOT NULL,
	[xauth_descriptions] [varchar](255) NULL,
	[xauth_lists] [text] NOT NULL,
	[xauth_is_actiive] [bit] NOT NULL,
	[created_datetime] [datetime] NOT NULL,
	[created_by] [int] NOT NULL,
	[lupd_datetime] [datetime] NOT NULL,
	[lupd_by] [int] NOT NULL,
 CONSTRAINT [PK_x_auth] PRIMARY KEY CLUSTERED 
(
	[xauth_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[x_auth] ADD  CONSTRAINT [DF_x_auth_xauth_is_actiive]  DEFAULT ((1)) FOR [xauth_is_actiive]
GO

ALTER TABLE [dbo].[x_auth] ADD  CONSTRAINT [DF_x_auth_created_by]  DEFAULT ((99)) FOR [created_by]
GO

ALTER TABLE [dbo].[x_auth] ADD  CONSTRAINT [DF_x_auth_lupd_by]  DEFAULT ((99)) FOR [lupd_by]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This column shall contain JSON formated data of all x_menus and it''s permittions' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_auth', @level2type=N'COLUMN',@level2name=N'xauth_lists'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'authentication list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_auth'
GO


USE [DB_PEMI]
GO

/****** Object:  Table [dbo].[x_menus]    Script Date: 8/28/2022 00:24:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[x_menus](
	[xmenus_id] [int] IDENTITY(1,1) NOT NULL,
	[xmenus_name] [varchar](200) NOT NULL,
	[xmenus_display_name] [varchar](255) NOT NULL,
	[xmenus_routes] [varchar](255) NOT NULL,
	[xmenus_parent_id] [int] NOT NULL,
	[xmenus_is_active] [bit] NOT NULL,
	[created_datetime] [datetime] NOT NULL,
	[created_by] [int] NOT NULL,
	[lupd_datetime] [datetime] NOT NULL,
	[lupd_by] [int] NOT NULL,
 CONSTRAINT [PK_x_menus_1] PRIMARY KEY CLUSTERED 
(
	[xmenus_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[x_menus] ADD  CONSTRAINT [DF_x_menus_xmenu_parent_id]  DEFAULT ((0)) FOR [xmenus_parent_id]
GO

ALTER TABLE [dbo].[x_menus] ADD  CONSTRAINT [DF_x_menus_xmenu_is_active]  DEFAULT ((1)) FOR [xmenus_is_active]
GO

ALTER TABLE [dbo].[x_menus] ADD  CONSTRAINT [DF_x_menus_created_by]  DEFAULT ((99)) FOR [created_by]
GO

ALTER TABLE [dbo].[x_menus] ADD  CONSTRAINT [DF_x_menus_lupd_by]  DEFAULT ((99)) FOR [lupd_by]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'menu name shall be unique' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_menus', @level2type=N'COLUMN',@level2name=N'xmenus_name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'UI display name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_menus', @level2type=N'COLUMN',@level2name=N'xmenus_display_name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'fill with specific ci routes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_menus', @level2type=N'COLUMN',@level2name=N'xmenus_routes'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'menu list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_menus'
GO

USE [DB_PEMI]
GO

/****** Object:  Table [dbo].[x_roles]    Script Date: 8/28/2022 00:25:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[x_roles](
	[xroles_id] [int] IDENTITY(1,1) NOT NULL,
	[xroles_name] [varchar](50) NOT NULL,
	[xroles_description] [varchar](255) NULL,
	[created_datetime] [datetime] NOT NULL,
	[created_by] [int] NOT NULL,
	[lupd_datetime] [datetime] NOT NULL,
	[lupd_by] [int] NOT NULL,
 CONSTRAINT [PK_x_roles_1] PRIMARY KEY CLUSTERED 
(
	[xroles_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[x_roles] ADD  CONSTRAINT [DF_x_roles_created_by]  DEFAULT ((99)) FOR [created_by]
GO

ALTER TABLE [dbo].[x_roles] ADD  CONSTRAINT [DF_x_roles_lupd_by]  DEFAULT ((99)) FOR [lupd_by]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'role name shall be unique' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_roles', @level2type=N'COLUMN',@level2name=N'xroles_name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'roles list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_roles'
GO


USE [DB_PEMI]
GO

/****** Object:  Table [dbo].[x_users]    Script Date: 8/28/2022 00:25:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[x_users](
	[xusers_id] [int] IDENTITY(1,1) NOT NULL,
	[xusers_username] [varchar](50) NOT NULL,
	[xusers_password] [varchar](50) NOT NULL,
	[xusers_name] [varchar](50) NOT NULL,
	[xusers_is_blocked] [bit] NOT NULL,
	[xusers_is_root] [bit] NOT NULL,
	[created_date_time] [datetime] NOT NULL,
	[created_by] [int] NOT NULL,
	[lupd_date_time] [datetime] NOT NULL,
	[lupd_by] [int] NOT NULL,
 CONSTRAINT [PK_x_users] PRIMARY KEY CLUSTERED 
(
	[xusers_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[x_users] ADD  CONSTRAINT [DF_x_users_is_blocked]  DEFAULT ((0)) FOR [xusers_is_blocked]
GO

ALTER TABLE [dbo].[x_users] ADD  CONSTRAINT [DF_x_users_is_root]  DEFAULT ((0)) FOR [xusers_is_root]
GO

ALTER TABLE [dbo].[x_users] ADD  CONSTRAINT [DF_x_users_created_by]  DEFAULT ((99)) FOR [created_by]
GO

ALTER TABLE [dbo].[x_users] ADD  CONSTRAINT [DF_x_users_lupd_by]  DEFAULT ((99)) FOR [lupd_by]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'username shall be unique' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_users', @level2type=N'COLUMN',@level2name=N'xusers_username'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_users'
GO

USE [DB_PEMI]
GO

/****** Object:  Table [dbo].[x_user_roles]    Script Date: 8/28/2022 00:25:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[x_user_roles](
	[xuser_roles_id] [int] IDENTITY(1,1) NOT NULL,
	[xuser_roles_user_id] [int] NOT NULL,
	[xuser_roles_role_id] [int] NOT NULL,
	[created_date_time] [datetime] NOT NULL,
	[created_by] [int] NOT NULL,
	[lupd_date_time] [datetime] NOT NULL,
	[lupd_by] [int] NOT NULL,
 CONSTRAINT [PK_x_user_roles] PRIMARY KEY CLUSTERED 
(
	[xuser_roles_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[x_user_roles] ADD  CONSTRAINT [DF_x_user_roles_created_by]  DEFAULT ((99)) FOR [created_by]
GO

ALTER TABLE [dbo].[x_user_roles] ADD  CONSTRAINT [DF_x_user_roles_lupd_by]  DEFAULT ((99)) FOR [lupd_by]
GO

ALTER TABLE [dbo].[x_user_roles]  WITH CHECK ADD  CONSTRAINT [FK_x_user_roles_x_roles] FOREIGN KEY([xuser_roles_role_id])
REFERENCES [dbo].[x_roles] ([xroles_id])
GO

ALTER TABLE [dbo].[x_user_roles] CHECK CONSTRAINT [FK_x_user_roles_x_roles]
GO

ALTER TABLE [dbo].[x_user_roles]  WITH CHECK ADD  CONSTRAINT [FK_x_user_roles_x_users] FOREIGN KEY([xuser_roles_user_id])
REFERENCES [dbo].[x_users] ([xusers_id])
GO

ALTER TABLE [dbo].[x_user_roles] CHECK CONSTRAINT [FK_x_user_roles_x_users]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK from x_users' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_user_roles', @level2type=N'COLUMN',@level2name=N'xuser_roles_user_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK from x_roles' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_user_roles', @level2type=N'COLUMN',@level2name=N'xuser_roles_role_id'
GO

USE [DB_PEMI]
GO

/****** Object:  Table [dbo].[x_roles_auth]    Script Date: 8/28/2022 00:25:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[x_roles_auth](
	[xrole_auth_id] [int] IDENTITY(1,1) NOT NULL,
	[xrole_auth_role_id] [int] NOT NULL,
	[xrole_auth_auth_id] [int] NOT NULL,
	[created_date_time] [datetime] NOT NULL,
	[created_by] [int] NOT NULL,
	[lupd_date_time] [datetime] NOT NULL,
	[lupd_by] [int] NOT NULL,
 CONSTRAINT [PK_x_roles_auth] PRIMARY KEY CLUSTERED 
(
	[xrole_auth_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[x_roles_auth] ADD  CONSTRAINT [DF_x_roles_auth_created_by]  DEFAULT ((99)) FOR [created_by]
GO

ALTER TABLE [dbo].[x_roles_auth] ADD  CONSTRAINT [DF_x_roles_auth_lupd_by]  DEFAULT ((99)) FOR [lupd_by]
GO

ALTER TABLE [dbo].[x_roles_auth]  WITH CHECK ADD  CONSTRAINT [FK_x_roles_auth_x_auth] FOREIGN KEY([xrole_auth_auth_id])
REFERENCES [dbo].[x_auth] ([xauth_id])
GO

ALTER TABLE [dbo].[x_roles_auth] CHECK CONSTRAINT [FK_x_roles_auth_x_auth]
GO

ALTER TABLE [dbo].[x_roles_auth]  WITH CHECK ADD  CONSTRAINT [FK_x_roles_auth_x_roles] FOREIGN KEY([xrole_auth_role_id])
REFERENCES [dbo].[x_roles] ([xroles_id])
GO

ALTER TABLE [dbo].[x_roles_auth] CHECK CONSTRAINT [FK_x_roles_auth_x_roles]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK from x_roles' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_roles_auth', @level2type=N'COLUMN',@level2name=N'xrole_auth_role_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK from x_auth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'x_roles_auth', @level2type=N'COLUMN',@level2name=N'xrole_auth_auth_id'
GO