BEGIN;

DROP TABLE IF EXISTS medical_record_allergies CASCADE;
DROP TABLE IF EXISTS medical_record_symptoms CASCADE;
DROP TABLE IF EXISTS medical_record_procedures CASCADE;
DROP TABLE IF EXISTS medical_record_lab_results CASCADE;
DROP TABLE IF EXISTS performed_lab_tests CASCADE;
DROP TABLE IF EXISTS performed_procedures CASCADE;
DROP TABLE IF EXISTS medical_report_lab_results CASCADE;
DROP TABLE IF EXISTS prescription_medical_records CASCADE;
DROP TABLE IF EXISTS diagnosis_medical_records CASCADE;
DROP TABLE IF EXISTS billing_lab_tests CASCADE;
DROP TABLE IF EXISTS billing_procedures CASCADE;
DROP TABLE IF EXISTS department_procedures CASCADE;
DROP TABLE IF EXISTS specialization_procedures CASCADE;
DROP TABLE IF EXISTS diagnosis_symptoms CASCADE;
DROP TABLE IF EXISTS patient_symptoms CASCADE;
DROP TABLE IF EXISTS allergy_prescription_restrictions CASCADE;
DROP TABLE IF EXISTS patient_allergies CASCADE;
DROP TABLE IF EXISTS symptoms CASCADE;
DROP TABLE IF EXISTS allergies CASCADE;

DROP TABLE IF EXISTS billing CASCADE;
DROP TABLE IF EXISTS medical_report CASCADE;
DROP TABLE IF EXISTS referrals CASCADE;
DROP TABLE IF EXISTS medical_records CASCADE;
DROP TABLE IF EXISTS lab_results CASCADE;
DROP TABLE IF EXISTS lab_tests CASCADE;
DROP TABLE IF EXISTS prescription_restriction CASCADE;
DROP TABLE IF EXISTS prescriptions CASCADE;
DROP TABLE IF EXISTS procedure_results CASCADE;
DROP TABLE IF EXISTS procedures CASCADE;
DROP TABLE IF EXISTS diagnosis CASCADE;
DROP TABLE IF EXISTS appointments CASCADE;
DROP TABLE IF EXISTS lab_technician CASCADE;
DROP TABLE IF EXISTS admin CASCADE;
DROP TABLE IF EXISTS patients CASCADE;
DROP TABLE IF EXISTS doctors CASCADE;
DROP TABLE IF EXISTS departments CASCADE;
DROP TABLE IF EXISTS doctor_specialization CASCADE;
DROP TABLE IF EXISTS doctor_level CASCADE;
DROP TABLE IF EXISTS users CASCADE;


-- USERS & ROLES ---------------------------------------------------------

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true
);


-- STAFF -------------------------------------------------------------------

CREATE TABLE doctor_level (
    level_id BIGINT,
    level TEXT NOT NULL,

    CONSTRAINT doctor_level_PK
        PRIMARY KEY (level_id),

    CONSTRAINT doctor_level_level_UQ
        UNIQUE (level)
);

CREATE TABLE doctor_specialization (
    specialization_id BIGINT,
    specialization_name TEXT NOT NULL,

    CONSTRAINT doctor_specialization_PK
        PRIMARY KEY (specialization_id),

    CONSTRAINT doctor_specialization_name_UQ
        UNIQUE (specialization_name)
);


CREATE TABLE departments (
    department_id BIGINT,
    department_name TEXT NOT NULL,

    CONSTRAINT departments_PK
        PRIMARY KEY (department_id),

    CONSTRAINT departments_name_UQ
        UNIQUE (department_name)
);

CREATE TABLE doctors (
    doctor_id BIGINT,

    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,

    email_address TEXT NOT NULL,

    level_id BIGINT NOT NULL,
    specialization_id BIGINT NOT NULL,
    department_id BIGINT NOT NULL,

    user_id BIGINT NOT NULL,

    CONSTRAINT doctors_PK
        PRIMARY KEY (doctor_id),

    CONSTRAINT doctors_email_UQ
        UNIQUE (email_address),

    CONSTRAINT doctors_email_chk
        CHECK (email_address LIKE '%@%'),

    CONSTRAINT doctors_user_UQ
        UNIQUE (user_id),

    CONSTRAINT doctors_level_FK
        FOREIGN KEY (level_id)
            REFERENCES doctor_level(level_id)
            ON DELETE RESTRICT,

    CONSTRAINT doctors_specialization_FK
        FOREIGN KEY (specialization_id)
            REFERENCES doctor_specialization(specialization_id)
            ON DELETE RESTRICT,

    CONSTRAINT doctors_department_FK
        FOREIGN KEY (department_id)
            REFERENCES departments(department_id)
            ON DELETE RESTRICT,

    CONSTRAINT doctors_user_FK
        FOREIGN KEY (user_id)
            REFERENCES users(user_id)
            ON DELETE RESTRICT
);

CREATE TABLE admin (
    admin_id BIGINT,

    username TEXT NOT NULL,
    name TEXT NOT NULL,
    lastname TEXT NOT NULL,

    email TEXT NOT NULL,

    user_id BIGINT NOT NULL,

    CONSTRAINT admin_PK
        PRIMARY KEY (admin_id),

    CONSTRAINT admin_username_UQ
        UNIQUE (username),

    CONSTRAINT admin_email_UQ
        UNIQUE (email),

    CONSTRAINT admin_email_chk
        CHECK (email LIKE '%@adminmedora%'),

    CONSTRAINT admin_user_UQ
        UNIQUE (user_id),

    CONSTRAINT admin_user_FK
        FOREIGN KEY (user_id)
            REFERENCES users(user_id)
            ON DELETE RESTRICT
);

CREATE TABLE lab_technician (
    technician_id BIGINT,

    username TEXT NOT NULL,
    name TEXT NOT NULL,
    lastname TEXT NOT NULL,

    email TEXT NOT NULL,

    user_id BIGINT NOT NULL,

    CONSTRAINT lab_technician_PK
        PRIMARY KEY (technician_id),

    CONSTRAINT lab_technician_username_UQ
        UNIQUE (username),

    CONSTRAINT lab_technician_email_UQ
        UNIQUE (email),

    CONSTRAINT lab_technician_email_chk
        CHECK (email LIKE '%@labmedora%'),

    CONSTRAINT lab_technician_user_UQ
        UNIQUE (user_id),

    CONSTRAINT lab_technician_user_FK
        FOREIGN KEY (user_id)
            REFERENCES users(user_id)
            ON DELETE RESTRICT
);


-- PATIENTS ------------------------------------------------------------------

CREATE TABLE patients (
    patient_id BIGINT,

    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,

    email_address TEXT,
    date_of_birth DATE NOT NULL,

    blood_type TEXT,
    gender TEXT,

    phone_number TEXT,
    embg TEXT NOT NULL,

    user_id BIGINT NOT NULL,

    CONSTRAINT patients_PK
        PRIMARY KEY (patient_id),

    CONSTRAINT patients_email_UQ
        UNIQUE (email_address),

    CONSTRAINT patients_embg_UQ
        UNIQUE (embg),

    CONSTRAINT patients_embg_format_chk
        CHECK (embg ~ '^[0-9]{13}$'),

    CONSTRAINT patients_gender_chk
        CHECK (gender IN ('MALE', 'FEMALE')),

    CONSTRAINT patients_blood_type_chk
        CHECK (
            blood_type IN (
                           'A+', 'A-',
                           'B+', 'B-',
                           'AB+', 'AB-',
                           'O+', 'O-'
                )
            ),

    CONSTRAINT patients_user_UQ
        UNIQUE (user_id),

    CONSTRAINT patients_user_FK
        FOREIGN KEY (user_id)
            REFERENCES users(user_id)
            ON DELETE RESTRICT
);


-- APPOINTMENTS --------------------------------------------------------------

CREATE TABLE appointments (
    appointment_id BIGINT,

    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,

    status TEXT NOT NULL,

    patient_id BIGINT NOT NULL,
    doctor_id BIGINT NOT NULL,

    CONSTRAINT appointments_PK
        PRIMARY KEY (appointment_id),

    CONSTRAINT appointments_status_chk
        CHECK (
            status IN (
                       'SCHEDULED',
                       'COMPLETED',
                       'CANCELLED',
                       'IN_PROGRESS'
                )
            ),

    CONSTRAINT appointments_patient_FK
        FOREIGN KEY (patient_id)
            REFERENCES patients(patient_id)
            ON DELETE RESTRICT,

    CONSTRAINT appointments_doctor_FK
        FOREIGN KEY (doctor_id)
            REFERENCES doctors(doctor_id)
            ON DELETE RESTRICT
);


-- CLINICAL ENTITIES -----------------------------------------------------

CREATE TABLE allergies (
    allergy_id BIGINT,

    name TEXT NOT NULL,
    allergy_severity TEXT NOT NULL,

    CONSTRAINT allergies_PK
        PRIMARY KEY (allergy_id),

    CONSTRAINT allergies_name_UQ
        UNIQUE (name),

    CONSTRAINT allergies_name_chk
        CHECK (length(trim(name)) > 0),

    CONSTRAINT allergies_severity_chk
        CHECK (allergy_severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL'))
);

CREATE TABLE symptoms (
    symptom_id BIGINT,

    name TEXT NOT NULL,
    description TEXT,

    CONSTRAINT symptoms_PK
        PRIMARY KEY (symptom_id),

    CONSTRAINT symptoms_name_UQ
        UNIQUE (name),

    CONSTRAINT symptoms_name_chk
        CHECK (length(trim(name)) > 0),

    CONSTRAINT symptoms_description_chk
        CHECK (
            description IS NULL
                OR length(trim(description)) > 0
            )
);

CREATE TABLE diagnosis (
    diagnosis_id BIGINT,

    name TEXT NOT NULL,
    description TEXT,

    patient_id BIGINT NOT NULL,
    doctor_id BIGINT NOT NULL,

    CONSTRAINT diagnosis_PK
        PRIMARY KEY (diagnosis_id),

    CONSTRAINT diagnosis_patient_FK
        FOREIGN KEY (patient_id)
            REFERENCES patients(patient_id)
            ON DELETE RESTRICT,

    CONSTRAINT diagnosis_doctor_FK
        FOREIGN KEY (doctor_id)
            REFERENCES doctors(doctor_id)
            ON DELETE RESTRICT
);

CREATE TABLE diagnosis_symptoms (
    diagnosis_id BIGINT NOT NULL,
    symptom_id BIGINT NOT NULL,

    CONSTRAINT diagnosis_symptoms_PK
        PRIMARY KEY (diagnosis_id, symptom_id),

    CONSTRAINT diagnosis_symptoms_diagnosis_FK
        FOREIGN KEY (diagnosis_id)
            REFERENCES diagnosis(diagnosis_id)
            ON DELETE RESTRICT,

    CONSTRAINT diagnosis_symptoms_symptom_FK
        FOREIGN KEY (symptom_id)
            REFERENCES symptoms(symptom_id)
            ON DELETE RESTRICT
);


-- PROCEDURES --------------------------------------------------------------

CREATE TABLE procedures (
    procedure_id BIGINT,

    procedure_type TEXT NOT NULL,
    procedure_date DATE NOT NULL,

    description TEXT,
    cost DECIMAL NOT NULL,

    doctor_id BIGINT NOT NULL,

    CONSTRAINT procedures_PK
        PRIMARY KEY (procedure_id),

    CONSTRAINT procedures_cost_chk
        CHECK (cost >= 0),

    CONSTRAINT procedures_type_chk
        CHECK (length(trim(procedure_type)) > 0),

    CONSTRAINT procedures_doctor_FK
        FOREIGN KEY (doctor_id)
            REFERENCES doctors(doctor_id)
            ON DELETE RESTRICT
);

CREATE TABLE procedure_results (
    result_id BIGINT,

    result_description TEXT,
    result_date DATE NOT NULL,

    procedure_id BIGINT NOT NULL,

    CONSTRAINT procedure_results_PK
        PRIMARY KEY (result_id),

    CONSTRAINT procedure_results_description_chk
        CHECK (
            result_description IS NULL
                OR length(trim(result_description)) > 0
            ),

    CONSTRAINT procedure_results_procedure_FK
        FOREIGN KEY (procedure_id)
            REFERENCES procedures(procedure_id)
            ON DELETE RESTRICT
);

CREATE TABLE specialization_procedures (
    specialization_id BIGINT NOT NULL,
    procedure_id BIGINT NOT NULL,

    CONSTRAINT specialization_procedures_PK
        PRIMARY KEY (specialization_id, procedure_id),

    CONSTRAINT specialization_procedures_specialization_FK
        FOREIGN KEY (specialization_id)
            REFERENCES doctor_specialization(specialization_id)
            ON DELETE RESTRICT,

    CONSTRAINT specialization_procedures_procedure_FK
        FOREIGN KEY (procedure_id)
            REFERENCES procedures(procedure_id)
            ON DELETE RESTRICT
);

CREATE TABLE department_procedures (
    department_id BIGINT NOT NULL,
    procedure_id BIGINT NOT NULL,

    CONSTRAINT department_procedures_PK
        PRIMARY KEY (department_id, procedure_id),

    CONSTRAINT department_procedures_department_FK
        FOREIGN KEY (department_id)
            REFERENCES departments(department_id)
            ON DELETE RESTRICT,

    CONSTRAINT department_procedures_procedure_FK
        FOREIGN KEY (procedure_id)
            REFERENCES procedures(procedure_id)
            ON DELETE RESTRICT
);


-- LAB TESTS -----------------------------------------------------------------

CREATE TABLE lab_tests (
    test_id BIGINT,

    test_name TEXT NOT NULL,
    description TEXT,
    cost DECIMAL NOT NULL,

    CONSTRAINT lab_tests_PK
        PRIMARY KEY (test_id),

    CONSTRAINT lab_tests_cost_chk
        CHECK (cost >= 0),

    CONSTRAINT lab_tests_name_chk
        CHECK (length(trim(test_name)) > 0)
);

CREATE TABLE lab_results (
    result_id BIGINT,

    results TEXT NOT NULL,
    result_date DATE NOT NULL,

    test_id BIGINT NOT NULL,

    CONSTRAINT lab_results_PK
        PRIMARY KEY (result_id),

    CONSTRAINT lab_results_results_chk
        CHECK (length(trim(results)) > 0),

    CONSTRAINT lab_results_date_chk
        CHECK (result_date <= CURRENT_DATE),

    CONSTRAINT lab_results_test_FK
        FOREIGN KEY (test_id)
            REFERENCES lab_tests(test_id)
            ON DELETE RESTRICT
);


-- PRESCRIPTIONS ---------------------------------------------------------

CREATE TABLE prescriptions (
    prescription_id BIGINT,

    medication_name TEXT NOT NULL,

    CONSTRAINT prescriptions_PK
        PRIMARY KEY (prescription_id)
);

CREATE TABLE prescription_restriction (
    restriction_id BIGINT,

    description TEXT NOT NULL,

    prescription_id BIGINT NOT NULL,

    CONSTRAINT prescription_restriction_PK
        PRIMARY KEY (restriction_id),

    CONSTRAINT prescription_restriction_desc_chk
        CHECK (length(trim(description)) > 0),

    CONSTRAINT prescription_restriction_prescription_FK
        FOREIGN KEY (prescription_id)
            REFERENCES prescriptions(prescription_id)
            ON DELETE RESTRICT
);

CREATE TABLE allergy_prescription_restrictions (
    allergy_id BIGINT NOT NULL,
    restriction_id BIGINT NOT NULL,

    CONSTRAINT allergy_prescription_restrictions_PK
        PRIMARY KEY (allergy_id, restriction_id),

    CONSTRAINT allergy_prescription_restrictions_allergy_FK
        FOREIGN KEY (allergy_id)
            REFERENCES allergies(allergy_id)
            ON DELETE RESTRICT,

    CONSTRAINT allergy_prescription_restrictions_restriction_FK
        FOREIGN KEY (restriction_id)
            REFERENCES prescription_restriction(restriction_id)
            ON DELETE RESTRICT
);


-- PATIENTS <-> ALLERGIES / SYMPTOMS -----------------------------------------

CREATE TABLE patient_allergies (
    patient_id BIGINT NOT NULL,
    allergy_id BIGINT NOT NULL,

    CONSTRAINT patient_allergies_PK
        PRIMARY KEY (patient_id, allergy_id),

    CONSTRAINT patient_allergies_patient_FK
        FOREIGN KEY (patient_id)
            REFERENCES patients(patient_id)
            ON DELETE RESTRICT,

    CONSTRAINT patient_allergies_allergy_FK
        FOREIGN KEY (allergy_id)
            REFERENCES allergies(allergy_id)
            ON DELETE RESTRICT
);

CREATE TABLE patient_symptoms (
    patient_id BIGINT NOT NULL,
    symptom_id BIGINT NOT NULL,

    CONSTRAINT patient_symptoms_PK
        PRIMARY KEY (patient_id, symptom_id),

    CONSTRAINT patient_symptoms_patient_FK
        FOREIGN KEY (patient_id)
            REFERENCES patients(patient_id)
            ON DELETE RESTRICT,

    CONSTRAINT patient_symptoms_symptom_FK
        FOREIGN KEY (symptom_id)
            REFERENCES symptoms(symptom_id)
            ON DELETE RESTRICT
);


-- MEDICAL RECORDS -------------------------------------------------------

CREATE TABLE medical_records (
    record_id BIGINT PRIMARY KEY,

    patient_id BIGINT NOT NULL,

    FOREIGN KEY (patient_id)
        REFERENCES patients(patient_id)
        ON DELETE RESTRICT
);

CREATE TABLE diagnosis_medical_records (
    diagnosis_id BIGINT NOT NULL,
    record_id BIGINT NOT NULL,

    CONSTRAINT diagnosis_medical_records_PK
        PRIMARY KEY (diagnosis_id, record_id),

    CONSTRAINT diagnosis_medical_records_diagnosis_FK
        FOREIGN KEY (diagnosis_id)
            REFERENCES diagnosis(diagnosis_id)
            ON DELETE RESTRICT,

    CONSTRAINT diagnosis_medical_records_record_FK
        FOREIGN KEY (record_id)
            REFERENCES medical_records(record_id)
            ON DELETE RESTRICT
);

CREATE TABLE prescription_medical_records (
    prescription_id BIGINT NOT NULL,
    record_id BIGINT NOT NULL,

    dosage TEXT NOT NULL,
    frequency TEXT NOT NULL,
    duration TEXT NOT NULL,

    notes TEXT,

    CONSTRAINT prescription_medical_records_PK
        PRIMARY KEY (prescription_id, record_id),

    CONSTRAINT prescription_medical_records_prescription_FK
        FOREIGN KEY (prescription_id)
            REFERENCES prescriptions(prescription_id)
            ON DELETE RESTRICT,

    CONSTRAINT prescription_medical_records_record_FK
        FOREIGN KEY (record_id)
            REFERENCES medical_records(record_id)
            ON DELETE RESTRICT
);

CREATE TABLE medical_record_lab_results (
    record_id BIGINT NOT NULL,
    result_id BIGINT NOT NULL,

    PRIMARY KEY (record_id, result_id),

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (result_id)
        REFERENCES lab_results(result_id)
        ON DELETE RESTRICT
);

CREATE TABLE medical_record_procedures (
    record_id BIGINT NOT NULL,
    procedure_id BIGINT NOT NULL,

    PRIMARY KEY (record_id, procedure_id),

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (procedure_id)
        REFERENCES procedures(procedure_id)
        ON DELETE RESTRICT
);

CREATE TABLE medical_record_symptoms (
    record_id BIGINT NOT NULL,
    symptom_id BIGINT NOT NULL,
    severity TEXT,

    PRIMARY KEY (record_id, symptom_id),

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (symptom_id)
        REFERENCES symptoms(symptom_id)
        ON DELETE RESTRICT
);

CREATE TABLE medical_record_allergies (
    record_id BIGINT NOT NULL,
    allergy_id BIGINT NOT NULL,
    reaction TEXT,
    severity TEXT,

    PRIMARY KEY (record_id, allergy_id),

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (allergy_id)
        REFERENCES allergies(allergy_id)
        ON DELETE RESTRICT
);


-- REPORTS & REFERRALS -----------------------------------------------------

CREATE TABLE referrals (
    referral_id BIGINT PRIMARY KEY,

    reason TEXT NOT NULL,
    referral_date DATE NOT NULL,

    record_id BIGINT NOT NULL,
    from_doctor_id BIGINT NOT NULL,
    to_doctor_id BIGINT NOT NULL,

    CONSTRAINT referrals_reason_chk
        CHECK (length(trim(reason)) > 0),

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (from_doctor_id)
        REFERENCES doctors(doctor_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (to_doctor_id)
        REFERENCES doctors(doctor_id)
        ON DELETE RESTRICT
);

CREATE TABLE medical_report (
    report_id BIGINT PRIMARY KEY,

    description TEXT NOT NULL,
    report_date DATE NOT NULL,

    record_id BIGINT NOT NULL,
    doctor_id BIGINT NOT NULL,

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (doctor_id)
        REFERENCES doctors(doctor_id)
        ON DELETE RESTRICT
);

CREATE TABLE medical_report_lab_results (
    report_id BIGINT NOT NULL,
    result_id BIGINT NOT NULL,

    CONSTRAINT medical_report_lab_results_PK
        PRIMARY KEY (report_id, result_id),

    CONSTRAINT medical_report_lab_results_report_FK
        FOREIGN KEY (report_id)
            REFERENCES medical_report(report_id)
            ON DELETE RESTRICT,

    CONSTRAINT medical_report_lab_results_result_FK
        FOREIGN KEY (result_id)
            REFERENCES lab_results(result_id)
            ON DELETE RESTRICT
);


-- PERFORMED PROCEDURES / LAB TESTS -----------------------------------------

CREATE TABLE performed_procedures (
    performed_id BIGINT,

    procedure_id BIGINT NOT NULL,
    doctor_id BIGINT NOT NULL,
    patient_id BIGINT NOT NULL,
    diagnosis_id BIGINT,

    procedure_date DATE NOT NULL,
    notes TEXT,

    CONSTRAINT performed_procedures_PK
        PRIMARY KEY (performed_id),

    CONSTRAINT performed_procedures_procedure_FK
        FOREIGN KEY (procedure_id)
            REFERENCES procedures(procedure_id)
            ON DELETE RESTRICT,

    CONSTRAINT performed_procedures_doctor_FK
        FOREIGN KEY (doctor_id)
            REFERENCES doctors(doctor_id)
            ON DELETE RESTRICT,

    CONSTRAINT performed_procedures_patient_FK
        FOREIGN KEY (patient_id)
            REFERENCES patients(patient_id)
            ON DELETE RESTRICT,

    CONSTRAINT performed_procedures_diagnosis_FK
        FOREIGN KEY (diagnosis_id)
            REFERENCES diagnosis(diagnosis_id)
            ON DELETE RESTRICT
);

CREATE TABLE performed_lab_tests (
    performed_test_id BIGINT,

    test_id BIGINT NOT NULL,
    patient_id BIGINT NOT NULL,
    doctor_id BIGINT NOT NULL,
    technician_id BIGINT NOT NULL,

    test_date DATE NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT now(),

    CONSTRAINT performed_lab_tests_PK
        PRIMARY KEY (performed_test_id),

    CONSTRAINT performed_lab_tests_test_FK
        FOREIGN KEY (test_id)
            REFERENCES lab_tests(test_id)
            ON DELETE RESTRICT,

    CONSTRAINT performed_lab_tests_patient_FK
        FOREIGN KEY (patient_id)
            REFERENCES patients(patient_id)
            ON DELETE RESTRICT,

    CONSTRAINT performed_lab_tests_doctor_FK
        FOREIGN KEY (doctor_id)
            REFERENCES doctors(doctor_id)
            ON DELETE RESTRICT,

    CONSTRAINT performed_lab_tests_technician_FK
        FOREIGN KEY (technician_id)
            REFERENCES lab_technician(technician_id)
            ON DELETE RESTRICT
);


-- BILLING -------------------------------------------------------------------

CREATE TABLE billing (
    bill_id BIGINT PRIMARY KEY,

    total_cost DECIMAL NOT NULL,

    payment_status TEXT NOT NULL,
    payment_date DATE,

    created_at TIMESTAMP DEFAULT now(),

    record_id BIGINT NOT NULL,
    admin_id BIGINT NOT NULL,

    CONSTRAINT billing_cost_chk CHECK (total_cost >= 0),
    CONSTRAINT billing_status_chk
        CHECK (payment_status IN ('PENDING', 'PAID', 'CANCELLED')),

    FOREIGN KEY (record_id)
        REFERENCES medical_records(record_id)
        ON DELETE RESTRICT,

    FOREIGN KEY (admin_id)
        REFERENCES admin(admin_id)
        ON DELETE RESTRICT
);

CREATE TABLE billing_procedures (
    bill_id BIGINT NOT NULL,
    procedure_id BIGINT NOT NULL,

    CONSTRAINT billing_procedures_PK
        PRIMARY KEY (bill_id, procedure_id),

    CONSTRAINT billing_procedures_bill_FK
        FOREIGN KEY (bill_id)
            REFERENCES billing(bill_id)
            ON DELETE RESTRICT,

    CONSTRAINT billing_procedures_procedure_FK
        FOREIGN KEY (procedure_id)
            REFERENCES procedures(procedure_id)
            ON DELETE RESTRICT
);

CREATE TABLE billing_lab_tests (
    bill_id BIGINT NOT NULL,
    test_id BIGINT NOT NULL,

    CONSTRAINT billing_lab_tests_PK
        PRIMARY KEY (bill_id, test_id),

    CONSTRAINT billing_lab_tests_bill_FK
        FOREIGN KEY (bill_id)
            REFERENCES billing(bill_id)
            ON DELETE RESTRICT,

    CONSTRAINT billing_lab_tests_test_FK
        FOREIGN KEY (test_id)
            REFERENCES lab_tests(test_id)
            ON DELETE RESTRICT
);

COMMIT;