CREATE TABLE resources
  (
     resource_id BIGINT IDENTITY,
     module_name NVARCHAR(255) NOT NULL,
     page_name   NVARCHAR(255) NOT NULL
  )

go

EXEC Sp_addextendedproperty
  'MS_Description',
  'Creation Of Resources Table',
  'SCHEMA',
  'dbo',
  'TABLE',
  'resources'

go

CREATE UNIQUE INDEX resources_resource_id_uindex
  ON resources (resource_id)

go

ALTER TABLE resources
  ADD CONSTRAINT resources_pk PRIMARY KEY NONCLUSTERED (resource_id)

go

CREATE TABLE role_group
  (
     role_group_id BIGINT IDENTITY,
     group_name    NVARCHAR(255) NOT NULL,
     user_id       BIGINT CONSTRAINT role_group_patients_patient_id_fk
     REFERENCES patients
  )

go

EXEC Sp_addextendedproperty
  'MS_Description',
  'Roles Group for Users',
  'SCHEMA',
  'dbo',
  'TABLE',
  'role_group'

go

CREATE UNIQUE INDEX role_group_role_group_id_uindex
  ON role_group (role_group_id)

go

CREATE UNIQUE INDEX role_group_group_name_uindex
  ON role_group (group_name)

go

ALTER TABLE role_group
  ADD CONSTRAINT role_group_pk PRIMARY KEY NONCLUSTERED (role_group_id)

go

EXEC Sp_rename
  'role_group',
  role_groups,
  'OBJECT'

go

ALTER TABLE patients
  ADD role_group_id BIGINT

go

ALTER TABLE patients
  ADD CONSTRAINT patients_role_groups_role_group_id_fk FOREIGN KEY (
  role_group_id) REFERENCES role_groups

go

create table roles
(
	role_id bigint identity
		constraint roles_pk
			primary key nonclustered,
	role_group_id bigint
		constraint roles_role_groups_role_group_id_fk
			references role_groups,
	[create] bit,
	[read] bit,
	[update] bit,
	[delete] bit
)
go

exec sp_addextendedproperty 'MS_Description', 'Creation of Roles Table', 'SCHEMA', 'dbo', 'TABLE', 'roles'
go

create unique index roles_role_id_uindex
	on roles (role_id)
go

alter table roles add default 0 for [create]
go

alter table roles add default 0 for [read]
go

alter table roles add default 0 for [update]
go

alter table roles add default 0 for [delete]
go

alter table resources
	add role_id BIGINT
go

alter table resources
	add constraint resources_roles_role_id_fk
		foreign key (role_id) references roles
go

alter table resources drop column role_id
go

alter table resources drop constraint resources_roles_role_id_fk
go

create table roles_and_resources_mappings
(
	roles_and_resources_mapping_id BIGINT identity,
	role_id BIGINT not null
		constraint roles_and_resources_mappings_roles_role_id_fk
			references roles,
	resource_id BIGINT not null
		constraint roles_and_resources_mappings_resources_resource_id_fk
			references resources
)
go

create unique index roles_and_resources_mappings_roles_and_resources_mapping_id_uindex
	on roles_and_resources_mappings (roles_and_resources_mapping_id)
go

alter table roles_and_resources_mappings
	add constraint roles_and_resources_mappings_pk
		primary key nonclustered (roles_and_resources_mapping_id)
go

alter table roles_and_resources_mappings drop constraint roles_and_resources_mappings_resources_resource_id_fk
go