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 doctor_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 diagnosis_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;


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,

                         CONSTRAINT doctors_PK
                             PRIMARY KEY (doctor_id),

                         CONSTRAINT doctors_email_UQ
                             UNIQUE (email_address),

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

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

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,

                          CONSTRAINT patients_PK
                              PRIMARY KEY (patient_id),

                          CONSTRAINT patients_email_UQ
                              UNIQUE (email_address),

                          CONSTRAINT patients_embg_UQ
                              UNIQUE (embg),

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

CREATE TABLE admin (
                       admin_id BIGINT,

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

                       email TEXT 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%')
);

CREATE TABLE lab_technician (
                                technician_id BIGINT,

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

                                email TEXT 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%')
);

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),
                       patient_id BIGINT,
                       doctor_id BIGINT,
                       admin_id BIGINT,
                       technician_id BIGINT,
                       is_active BOOLEAN DEFAULT true,

                       CONSTRAINT users_patient_FK
                           FOREIGN KEY (patient_id)
                               REFERENCES patients(patient_id),

                       CONSTRAINT users_doctor_FK
                           FOREIGN KEY (doctor_id)
                               REFERENCES doctors(doctor_id),

                       CONSTRAINT users_admin_FK
                           FOREIGN KEY (admin_id)
                               REFERENCES admin(admin_id),

                       CONSTRAINT users_technician_FK
                           FOREIGN KEY (technician_id)
                               REFERENCES lab_technician(technician_id)
);



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

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 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,
                            diagnosis_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,

                            CONSTRAINT procedures_diagnosis_FK
                                FOREIGN KEY (diagnosis_id)
                                    REFERENCES diagnosis(diagnosis_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 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 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
);

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 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 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 billing (
                         bill_id BIGINT PRIMARY KEY,

                         total_cost DECIMAL NOT NULL,

                         payment_status TEXT NOT NULL,
                         payment_date DATE,

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

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

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

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 diagnosis_procedures (
                                      diagnosis_id BIGINT NOT NULL,
                                      procedure_id BIGINT NOT NULL,

                                      CONSTRAINT diagnosis_procedures_PK
                                          PRIMARY KEY (diagnosis_id, procedure_id),

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

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

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

CREATE TABLE doctor_medical_records (
                                        doctor_id BIGINT NOT NULL,
                                        record_id BIGINT NOT NULL,

                                        CONSTRAINT doctor_medical_records_PK
                                            PRIMARY KEY (doctor_id, record_id),

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

                                        CONSTRAINT doctor_medical_records_record_FK
                                            FOREIGN KEY (record_id)
                                                REFERENCES medical_records(record_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_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
);

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,

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

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

COMMIT;