MedicalSystem DDL

CREATE TABLE Hospital (
    hospital_id INT PRIMARY KEY,
    hospital_address VARCHAR(255) NOT NULL
);

CREATE TABLE Specialization (
    spec_id INT PRIMARY KEY,
    spec_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE Department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL,
    spec_id INT,
    CONSTRAINT fk_department_specialization
        FOREIGN KEY (spec_id) REFERENCES Specialization(spec_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

CREATE TABLE Doctor (
    doctor_id INT PRIMARY KEY,
    spec_id INT,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    CONSTRAINT chk_doctor_phone
        CHECK (
            phone IS NULL OR
            (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
        ),
    CONSTRAINT chk_doctor_email
        CHECK (email IS NULL OR email LIKE '%_@_%._%'),
    CONSTRAINT fk_doctor_specialization
        FOREIGN KEY (spec_id) REFERENCES Specialization(spec_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

CREATE TABLE Doctor_department (
    doctor_dept_id INT PRIMARY KEY,
    doctor_id INT NOT NULL,
    department_id INT NOT NULL,
    employment_type VARCHAR(30),
    date_from DATE NOT NULL,
    date_to DATE,
    CONSTRAINT chk_doctor_department_employment_type
        CHECK (employment_type IS NULL OR employment_type IN ('FULL_TIME', 'PART_TIME', 'CONSULTANT', 'RESIDENT')),
    CONSTRAINT chk_doctor_department_dates
        CHECK (date_to IS NULL OR date_to >= date_from),
    CONSTRAINT fk_doctor_department_doctor
        FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_doctor_department_department
        FOREIGN KEY (department_id) REFERENCES Department(department_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Patient (
    patient_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender VARCHAR(20),
    phone VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    address VARCHAR(255),
    CONSTRAINT chk_patient_gender
        CHECK (gender IS NULL OR gender IN ('MALE', 'FEMALE', 'OTHER')),
    CONSTRAINT chk_patient_dob
        CHECK (date_of_birth <= CURRENT_DATE),
    CONSTRAINT chk_patient_phone
        CHECK (
            phone IS NULL OR
            (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
        ),
    CONSTRAINT chk_patient_email
        CHECK (email IS NULL OR email LIKE '%_@_%._%')
);

CREATE TABLE Referral (
    referral_id INT PRIMARY KEY,
    patient_id INT NOT NULL,
    department_id INT NOT NULL,
    referred_doctor_id INT,
    referring_doctor_id INT NOT NULL,
    CONSTRAINT fk_referral_patient
        FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_referral_department
        FOREIGN KEY (department_id) REFERENCES Department(department_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_referral_referred_doctor
        FOREIGN KEY (referred_doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_referral_referring_doctor
        FOREIGN KEY (referring_doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_referral_doctors_different
        CHECK (referred_doctor_id IS NULL OR referred_doctor_id <> referring_doctor_id)
);

CREATE TABLE Appointment (
    appointment_id INT PRIMARY KEY,
    referral_id INT,
    doctor_id INT,
    patient_id INT NOT NULL,
    parent_appointment_id INT,
    prescription_value BOOLEAN NOT NULL DEFAULT FALSE,
    appointment_date DATE NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'SCHEDULED',
    appointment_type VARCHAR(30),
    priority_level VARCHAR(30),
    CONSTRAINT chk_appointment_status
        CHECK (status IN ('SCHEDULED', 'COMPLETED', 'CANCELLED', 'NO_SHOW', 'IN_PROGRESS')),
    CONSTRAINT chk_appointment_type
        CHECK (appointment_type IS NULL OR appointment_type IN ('REGULAR', 'FOLLOW_UP', 'EMERGENCY', 'CONTROL')),
    CONSTRAINT chk_appointment_priority
        CHECK (priority_level IS NULL OR priority_level IN ('LOW', 'MEDIUM', 'HIGH', 'URGENT')),
    CONSTRAINT fk_appointment_referral
        FOREIGN KEY (referral_id) REFERENCES Referral(referral_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_appointment_doctor
        FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_appointment_patient
        FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_appointment_parent
        FOREIGN KEY (parent_appointment_id) REFERENCES Appointment(appointment_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

CREATE TABLE Medical_examination (
    exam_id INT PRIMARY KEY,
    exam_date DATE NOT NULL,
    notes TEXT,
    doctor_id INT NOT NULL,
    appointment_id INT NOT NULL UNIQUE,
    CONSTRAINT chk_medical_examination_date
        CHECK (exam_date <= CURRENT_DATE),
    CONSTRAINT fk_medical_examination_doctor
        FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_medical_examination_appointment
        FOREIGN KEY (appointment_id) REFERENCES Appointment(appointment_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Laboratory_test (
    lab_id INT PRIMARY KEY,
    exam_id INT NOT NULL,
    patient_id INT NOT NULL,
    doctor_id INT,
    result VARCHAR(255),
    test_name VARCHAR(100) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
    CONSTRAINT chk_laboratory_test_status
        CHECK (status IN ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED')),
    CONSTRAINT fk_laboratory_test_exam
        FOREIGN KEY (exam_id) REFERENCES Medical_examination(exam_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_laboratory_test_patient
        FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_laboratory_test_doctor
        FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

CREATE TABLE ICD (
    icd_id INT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    description VARCHAR(255) NOT NULL
);

CREATE TABLE Patient_diagnosis (
    patient_diagnosis_id INT PRIMARY KEY,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    exam_id INT NOT NULL,
    icd_id INT NOT NULL,
    diagnosis_name VARCHAR(255),
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,
    date_from DATE NOT NULL,
    date_to DATE,
    CONSTRAINT chk_patient_diagnosis_dates
        CHECK (date_to IS NULL OR date_to >= date_from),
    CONSTRAINT fk_patient_diagnosis_patient
        FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_patient_diagnosis_doctor
        FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_patient_diagnosis_exam
        FOREIGN KEY (exam_id) REFERENCES Medical_examination(exam_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_patient_diagnosis_icd
        FOREIGN KEY (icd_id) REFERENCES ICD(icd_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Pharmacy (
    pharmacy_id INT PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    license_num INT NOT NULL UNIQUE,
    CONSTRAINT chk_pharmacy_phone
        CHECK (
            phone IS NULL OR
            (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
        )
);

CREATE TABLE Drug_producers (
    drug_prod_id INT PRIMARY KEY,
    address VARCHAR(255),
    producer_name VARCHAR(255) NOT NULL,
    country_origin VARCHAR(100),
    website VARCHAR(100),
    phone VARCHAR(20),
    CONSTRAINT chk_drug_producers_phone
        CHECK (
            phone IS NULL OR
            (LENGTH(phone) BETWEEN 7 AND 20 AND phone NOT LIKE '%[A-Za-z]%')
        )
);

CREATE TABLE ATC_code (
    atc_id INT PRIMARY KEY,
    atc_code VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) NOT NULL
);

CREATE TABLE Drug (
    product_id INT PRIMARY KEY,
    drug_prod_id INT NOT NULL,
    atc_id INT NOT NULL,
    manufacturer VARCHAR(100),
    batch_number VARCHAR(50) NOT NULL,
    expiry_date DATE NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    storage_condition VARCHAR(100),
    CONSTRAINT chk_drug_expiry_date
        CHECK (expiry_date > CURRENT_DATE),
    CONSTRAINT chk_drug_unit_price
        CHECK (unit_price >= 0),
    CONSTRAINT fk_drug_producer
        FOREIGN KEY (drug_prod_id) REFERENCES Drug_producers(drug_prod_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_drug_atc
        FOREIGN KEY (atc_id) REFERENCES ATC_code(atc_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Inventory (
    inventory_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    pharmacy_id INT NOT NULL,
    location VARCHAR(100),
    quantity INT NOT NULL DEFAULT 0,
    CONSTRAINT chk_inventory_quantity
        CHECK (quantity >= 0),
    CONSTRAINT fk_inventory_product
        FOREIGN KEY (product_id) REFERENCES Drug(product_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_inventory_pharmacy
        FOREIGN KEY (pharmacy_id) REFERENCES Pharmacy(pharmacy_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Inventory_price (
    inventory_price_id INT PRIMARY KEY,
    purchasing_price DECIMAL(10,2) NOT NULL,
    referent_price DECIMAL(10,2),
    sale_price DECIMAL(10,2) NOT NULL,
    date DATE NOT NULL,
    inventory_id INT NOT NULL,
    CONSTRAINT chk_inventory_price_values
        CHECK (
            purchasing_price >= 0 AND
            (referent_price IS NULL OR referent_price >= 0) AND
            sale_price >= 0
        ),
    CONSTRAINT fk_inventory_price_inventory
        FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Prescription (
    presc_id INT PRIMARY KEY,
    atc_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    presc_date DATE NOT NULL,
    treatment_id INT,
    duration INT NOT NULL,
    dosage INT NOT NULL,
    inventory_id INT NOT NULL,
    patient_diagnosis_id INT NOT NULL,
    CONSTRAINT chk_prescription_date
        CHECK (presc_date <= CURRENT_DATE),
    CONSTRAINT chk_prescription_duration
        CHECK (duration > 0),
    CONSTRAINT chk_prescription_dosage
        CHECK (dosage > 0),
    CONSTRAINT fk_prescription_atc
        FOREIGN KEY (atc_id) REFERENCES ATC_code(atc_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_prescription_doctor
        FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_prescription_patient
        FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_prescription_inventory
        FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_prescription_patient_diagnosis
        FOREIGN KEY (patient_diagnosis_id) REFERENCES Patient_diagnosis(patient_diagnosis_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Pharmacy_sale (
    sale_id INT PRIMARY KEY,
    patient_id INT NOT NULL,
    pharmacy_id INT NOT NULL,
    presc_id INT NOT NULL,
    sale_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_pharmacy_sale_date
        CHECK (sale_date <= CURRENT_DATE),
    CONSTRAINT chk_pharmacy_sale_total
        CHECK (total_amount >= 0),
    CONSTRAINT fk_pharmacy_sale_patient
        FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_pharmacy_sale_pharmacy
        FOREIGN KEY (pharmacy_id) REFERENCES Pharmacy(pharmacy_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_pharmacy_sale_prescription
        FOREIGN KEY (presc_id) REFERENCES Prescription(presc_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE Sale_item (
    sale_item_id INT PRIMARY KEY,
    sale_id INT NOT NULL,
    presc_id INT NOT NULL,
    inventory_id INT NOT NULL,
    date DATE NOT NULL,
    CONSTRAINT chk_sale_item_date
        CHECK (date <= CURRENT_DATE),
    CONSTRAINT fk_sale_item_sale
        FOREIGN KEY (sale_id) REFERENCES Pharmacy_sale(sale_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_sale_item_prescription
        FOREIGN KEY (presc_id) REFERENCES Prescription(presc_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_sale_item_inventory
        FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);