create database hospital_db;
use hospital_db;
create table prescriptions (
prescription_id int auto_increment primary key, --
hospitalization_id int not null,
doctor_id int not null,
patient_id int not null,
date_issued date not null,
foreign key (hospitalization_id) references hospitalizations(hospitalization_id)
on delete cascade on update cascade,
foreign key (doctor_id) references doctors(doctor_id)
on delete set null on update cascade,
foreign key (patient_id) references patients(patient_id)
on delete cascade on update cascade
);
create table departments (
department_id int auto_increment primary key,
name varchar(100) not null unique, -- може само до 100 символа, не може да беъде празна и стойноста е уникална за всяка редица
location varchar(100) not null
);
create table medications (
medication_id int auto_increment primary key,
prescription_id int not null, -- само цели числа и не може да няма стойност
name varchar(100) not null,
dosage varchar(50) not null,
frequency varchar(50) not null,

foreign key (prescription_id) references prescriptions(prescription_id)
on delete cascade on update cascade
);
create table doctors (
doctor_id int auto_increment primary key,
first_name varchar(50) not null,
last_name varchar(50) not null,
department_id int null,
specialization varchar(100),
phone varchar(20) unique,

foreign key (department_id) references departments(department_id)
on delete set null on update cascade
);
create table patients (
patient_id int auto_increment primary key,
first_name varchar(50) not null,
last_name varchar(50) not null,
birth_date date not null,
gender enum('male', 'female', 'other') not null,
phone varchar(20) unique,
address varchar(255)
);

create table rooms (
room_id int auto_increment primary key,
department_id int not null,
room_number varchar(10) not null,
capacity int not null check (capacity > 0),

unique(department_id, room_number),

foreign key (department_id) references departments(department_id)
on delete cascade on update cascade
);
create table hospitalizations (
hospitalization_id int auto_increment primary key,
patient_id int not null,
room_id int null,
doctor_id int null,
admission_date date not null,
discharge_date date,
check (discharge_date is null or discharge_date >= admission_date),
foreign key (patient_id) references patients(patient_id)
on delete cascade on update cascade, -- ако нещо от patient_id се промени или изтрие тук става същото
foreign key (room_id) references rooms(room_id)
on delete set null on update cascade,
foreign key (doctor_id) references doctors(doctor_id)
on delete set null on update cascade
);