sql

create table Administator
(
    id_admin int identity(1,1) primary key,
    login varchar(50) unique not null,
    password_hash binary(64) not null,
    surname nvarchar(50) not null,
    name nvarchar(50) not null,
    patronymic nvarchar(50)
)

create table Doctor
(
    id_doc int identity(1,1) primary key,
    login varchar(50) unique not null,
    password_hash binary(64) not null,
    surname nvarchar(50) not null,
    name nvarchar(50) unique not null,
    patronymic nvarchar(50),
    phone varchar(20) not null,
    id_spec tinyint not null foreign key references Specialty,
    sex nchar(1) not null,
    birthdate date not null,
    passport varchar(12) not null,
    room varchar(3) not null,
    is_active bit not null default 'true'
)

create table Patient
(
    id_pat int identity(1,1) primary key,
    login varchar(50) unique not null,
    password_hash binary(64) not null,
    surname nvarchar(50) not null,
    name nvarchar(50) not null,
    patronymic nvarchar(50),
    phone varchar(20) not null,
    sex nchar(1) not null,
    birthdate date not null,
    passport varchar(12) not null
)

create table Appointment
(
    id_app int identity(1,1) primary key,
    id_pat int foreign key references Patient not null,
    id_proc int foreign key references [Procedure] not null,
    date datetime2(7) not null,
    is_canceled bit not null default 'false',
    is_paid bit not null default 'false',
)

create table [Procedure]
(
    id_proc int identity(1,1) primary key,
    name nvarchar(100) not null,
    price money not null,
    id_doc int foreign key references Doctor not null
)

create table Report
(
    id_rep int identity(1,1) primary key,
    complaints nvarchar(MAX) not null,
    diagnosis nvarchar(MAX) not null,
    recommendations nvarchar(MAX) not null,
    id_app int foreign key references Appointment not null
)

create table Specialty
(
    id_spec tinyint identity(1,1) primary key,
    name nvarchar(50) not null,
)