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
);