-- Districts
create table dbo.Districts
(
  Id int identity not null,
  [Name] nvarchar(100) not null,
    constraint Districts_PK primary key clustered(Id)
) -- Districts

-- Genders
create table dbo.Genders
(
  Id int identity not null,
  [Name] nvarchar(20) not null,
    constraint Genders_PK primary key clustered(Id)
) -- Genders

-- Levels
create table dbo.Levels
(
  Id int identity not null,
  [Name] nvarchar(20) not null,
    constraint Levels_PK primary key clustered(Id)
) -- Levels

-- Roles
create table dbo.Roles
(
  Id int identity not null,
  [Name] nvarchar(40) not null,
    constraint Roles_PK primary key clustered(Id)
) -- Roles

-- Subjects
create table dbo.Subjects
(
  Id int not null,
  [Name] nvarchar(100) null,
    constraint Subjects_PK primary key clustered(Id)
) -- Subjects

-- Users
create table dbo.Users
(
  Id int identity not null,
  Approved bit not null constraint Users_Approved_DF default(0),
  Comment nvarchar(2000) null,
  Created datetime not null,	
  Email nvarchar(200) not null,	
  LastLock datetime not null,
  LastLogin datetime not null,
  LastPasswordChange datetime not null,		
  LastReset datetime not null,
  Locked bit not null constraint Users_Locked_DF default(0),
  [Password] varbinary(200) not null,
  PasswordAttemptCount int not null,
  PasswordAttemptStart datetime not null,		
  Salt varbinary(800) not null,
  Updated datetime not null,
  Username nvarchar(40) not null constraint Users_Username_U unique,
    constraint Users_U primary key clustered(Id)
) -- Users

-- Profiles
create table dbo.Profiles
(
  Id int not null,
  Birthday datetime not null,
  City nvarchar(100) null,
  DistrictId int not null,
  GenderId int not null,  
  [Name] nvarchar(100) not null,
  Newsletter bit not null constraint Users_Newsletter_DF default(0)  
	constraint Profiles_PK primary key clustered(Id),
	constraint Profiles_Users_FK foreign key(Id) references dbo.Users(Id) on delete cascade on update cascade,
	constraint Profiles_Districts_FK foreign key(DistrictId) references dbo.Districts(Id) on delete cascade on update cascade,
	constraint Profiles_Genders_FK foreign key(GenderId) references dbo.Genders(Id) on delete cascade on update cascade	
)

-- Professors
create table dbo.Professors
(
  Id int not null,
  CurriculumVitae nvarchar(max) null,	
  Mobile nvarchar(20) null,
  Phone nvarchar(20) null
	constraint Professors_PK primary key clustered(Id),
	constraint Professors_Users_FK foreign key(Id) references dbo.Users(Id) on delete cascade on update cascade
)

-- ProfessorsLevels
create table dbo.ProfessorsLevels
(
  ProfessorId int not null,
  LevelId int not null,
    constraint ProfessorsLevels_PK primary key clustered(ProfessorId, LevelId),
    constraint ProfessorsLevels_Professors_FK foreign key(ProfessorId) references dbo.Professors(Id) on delete cascade on update cascade,
	constraint ProfessorsLevels_Levels_FK foreign key(LevelId) references dbo.Levels(Id) on delete cascade on update cascade
) -- ProfessorsLevels

-- ProfessorsSubjects
create table dbo.ProfessorsSubjects
(
  ProfessorId int not null,
  SubjectId int not null,
    constraint ProfessorsSubjects_PK primary key clustered(ProfessorId, SubjectId),
	constraint ProfessorsSubjects_Professors_FK foreign key(ProfessorId) references dbo.Professors(Id) on delete cascade on update cascade,
	constraint ProfessorsSubjects_Subjects_FK foreign key(SubjectId) references dbo.Subjects(Id) on delete cascade on update cascade 
) -- ProfessorsSubjects

-- UsersRoles
create table dbo.UsersRoles
(
  UserId int not null,
  RoleId int not null,
    constraint UsersRoles_PK primary key clustered(UserId, RoleId),
    constraint UsersRoles_Users_FK foreign key(UserId) references dbo.Users(Id) on delete cascade on update cascade,
	constraint UsersRoles_Roles_FK foreign key(RoleId) references dbo.Roles(Id) on delete cascade on update cascade
) -- UsersRoles