Index: sql/ddl.sql =================================================================== --- sql/ddl.sql (revision 500b5beb173aa165bb5ff80d5a89107eddc43c03) +++ sql/ddl.sql (revision c613cbf6f9fd9bd8bfca2ae505ed47ced972227a) @@ -0,0 +1,927 @@ + +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; + + + + +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 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; + Index: sql/dml.sql =================================================================== --- sql/dml.sql (revision 500b5beb173aa165bb5ff80d5a89107eddc43c03) +++ sql/dml.sql (revision c613cbf6f9fd9bd8bfca2ae505ed47ced972227a) @@ -0,0 +1,1569 @@ +BEGIN; +INSERT INTO doctor_level (level_id, level) VALUES + (1, 'INTERN'), + (2, 'RESIDENT'), + (3, 'GENERAL_DOCTOR'), + (4, 'SPECIALIST'), + (5, 'CONSULTANT'); + + +INSERT INTO doctor_specialization (specialization_id, specialization_name) VALUES + (1, 'PULMONOLOGY'), + (2, 'CARDIOLOGY'), + (3, 'NEUROLOGY'), + (4, 'ORTHOPEDICS'), + (5, 'GYNECOLOGY'), + (6, 'ENT'), + (7, 'OPHTHALMOLOGY'), + (8, 'PATHOLOGY'), + (9, 'RADIOLOGY'), + (10, 'GASTROENTEROLOGY'), + (11, 'UROLOGY'), + (12, 'NEPHROLOGY'), + (13, 'ONCOLOGY'), + (14, 'DERMATOLOGY'), + (15, 'PEDIATRICS'), + (16, 'PSYCHIATRY'), + (17, 'REHABILITATION'), + (18, 'HEMATOLOGY'), + (19, 'INFECTIOUS_DISEASES'), + (20, 'NEUROSURGERY'), + (21, 'CARDIAC_SURGERY'), + (22, 'PLASTIC_SURGERY'), + (23, 'GENERAL_SURGERY'), + (24, 'DENTISTRY'), + (25, 'ENDOCRINOLOGY'); + +INSERT INTO departments (department_id, department_name) VALUES + (1, 'PULMONOLOGY_DEPT'), + (2, 'CARDIOLOGY_DEPT'), + (3, 'NEUROLOGY_DEPT'), + (4, 'ORTHOPEDICS_DEPT'), + (5, 'GYNECOLOGY_DEPT'), + (6, 'ENT_DEPT'), + (7, 'OPHTHALMOLOGY_DEPT'), + (8, 'PATHOLOGY_DEPT'), + (9, 'RADIOLOGY_DEPT'), + (10, 'GASTROENTEROLOGY_DEPT'), + (11, 'UROLOGY_DEPT'), + (12, 'NEPHROLOGY_DEPT'), + (13, 'ONCOLOGY_DEPT'), + (14, 'DERMATOLOGY_DEPT'), + (15, 'PEDIATRICS_DEPT'), + (16, 'PSYCHIATRY_DEPT'), + (17, 'REHABILITATION_DEPT'), + (18, 'HEMATOLOGY_DEPT'), + (19, 'INFECTIOUS_DISEASES_DEPT'), + (20, 'NEUROSURGERY_DEPT'), + (21, 'CARDIAC_SURGERY_DEPT'), + (22, 'PLASTIC_SURGERY_DEPT'), + (23, 'GENERAL_SURGERY_DEPT'), + (24, 'DENTISTRY_DEPT'); + + +INSERT INTO allergies (allergy_id, name, allergy_severity) VALUES + (1, 'PENICILLIN', 'HIGH'), + (2, 'AMOXICILLIN', 'HIGH'), + (3, 'ASPIRIN', 'MEDIUM'), + (4, 'IBUPROFEN', 'MEDIUM'), + (5, 'PARACETAMOL', 'LOW'), + (6, 'CODEINE', 'HIGH'), + (7, 'MORPHINE', 'CRITICAL'), + (8, 'SULFA_DRUGS', 'HIGH'), + (9, 'CEPHALOSPORINS', 'HIGH'), + (10, 'ANESTHESIA', 'CRITICAL'), + (11, 'LIDOCAINE', 'HIGH'), + (12, 'CONTRAST_DYE', 'HIGH'), + (13, 'PEANUTS', 'CRITICAL'), + (14, 'TREE_NUTS', 'CRITICAL'), + (15, 'ALMONDS', 'HIGH'), + (16, 'WALNUTS', 'HIGH'), + (17, 'HAZELNUTS', 'HIGH'), + (18, 'CASHEWS', 'HIGH'), + (19, 'PISTACHIOS', 'HIGH'), + (20, 'SESAME', 'HIGH'), + (21, 'MILK', 'MEDIUM'), + (22, 'EGGS', 'MEDIUM'), + (23, 'WHEAT', 'MEDIUM'), + (24, 'GLUTEN', 'MEDIUM'), + (25, 'SOY', 'MEDIUM'), + (26, 'FISH', 'HIGH'), + (27, 'SHELLFISH', 'HIGH'), + (28, 'SHRIMP', 'HIGH'), + (29, 'CRAB', 'HIGH'), + (30, 'LOBSTER', 'HIGH'), + (31, 'STRAWBERRIES', 'LOW'), + (32, 'KIWI', 'LOW'), + (33, 'BANANA', 'LOW'), + (34, 'APPLE', 'LOW'), + (35, 'CHOCOLATE', 'LOW'), + (36, 'CAFFEINE', 'LOW'), + (37, 'POLLEN', 'LOW'), + (38, 'GRASS_POLLEN', 'LOW'), + (39, 'TREE_POLLEN', 'LOW'), + (40, 'RAGWEED', 'LOW'), + (41, 'DUST_MITES', 'LOW'), + (42, 'MOLD', 'MEDIUM'), + (43, 'MILDEW', 'MEDIUM'), + (44, 'PET_DANDER_CAT', 'MEDIUM'), + (45, 'PET_DANDER_DOG', 'MEDIUM'), + (46, 'FEATHERS', 'LOW'), + (47, 'BEE_VENOM', 'HIGH'), + (48, 'WASP_VENOM', 'HIGH'), + (49, 'HORNET_VENOM', 'HIGH'), + (50, 'FIRE_ANT_STINGS', 'HIGH'), + (51, 'LATEX', 'HIGH'), + (52, 'NICKEL', 'MEDIUM'), + (53, 'COBALT', 'MEDIUM'), + (54, 'CHROMIUM', 'MEDIUM'), + (55, 'FORMALDEHYDE', 'HIGH'), + (56, 'FRAGRANCES', 'MEDIUM'), + (57, 'PERFUMES', 'MEDIUM'), + (58, 'DETERGENTS', 'MEDIUM'), + (59, 'CLEANING_CHEMICALS', 'HIGH'), + (60, 'HAIR_DYE', 'MEDIUM'), + (61, 'IODINE', 'MEDIUM'), + (62, 'MRI_CONTRAST', 'HIGH'), + (63, 'CT_CONTRAST', 'HIGH'), + (64, 'PLASTER_CAST_MATERIAL', 'LOW'), + (65, 'OXYCODONE', 'HIGH'), + (66, 'TRAMADOL', 'HIGH'), + (67, 'DICLOFENAC', 'MEDIUM'), + (68, 'NAPROXEN', 'MEDIUM'), + (69, 'AZITHROMYCIN', 'MEDIUM'), + (70, 'CLARITHROMYCIN', 'MEDIUM'), + (71, 'METRONIDAZOLE', 'MEDIUM'), + (72, 'INSULIN', 'HIGH'), + (73, 'TOMATOES', 'LOW'), + (74, 'POTATOES', 'LOW'), + (75, 'CORN', 'LOW'), + (76, 'CARROTS', 'LOW'), + (77, 'CELERY', 'MEDIUM'), + (78, 'ONION', 'LOW'), + (79, 'GARLIC', 'LOW'), + (80, 'MUSTARD', 'MEDIUM'), + (81, 'PEACHES', 'LOW'), + (82, 'PEARS', 'LOW'), + (83, 'PLUMS', 'LOW'), + (84, 'GRAPES', 'LOW'), + (85, 'MELON', 'LOW'), + (86, 'SMOKE', 'MEDIUM'), + (87, 'CIGARETTE_SMOKE', 'MEDIUM'), + (88, 'AIRBORNE_CHEMICALS', 'HIGH'), + (89, 'POLLUTED_AIR', 'MEDIUM'), + (90, 'CAT_DANDER', 'MEDIUM'), + (91, 'DOG_DANDER', 'MEDIUM'), + (92, 'HORSE_DANDER', 'LOW'), + (93, 'PREDNISONE', 'HIGH'), + (94, 'HYDROCORTISONE', 'MEDIUM'), + (95, 'VACCINE_COMPONENTS', 'HIGH'), + (96, 'GLYCERIN', 'LOW'), + (97, 'LANOLIN', 'MEDIUM'), + (98, 'ALCOHOL_BASED_PRODUCTS', 'MEDIUM'), + (99, 'SOAP', 'LOW'), + (100, 'SHAMPOO', 'LOW'), + (101, 'LATEX_GLOVES', 'HIGH'), + (102, 'SURGICAL_ADHESIVES', 'HIGH'), + (103, 'DENTAL_MATERIALS', 'MEDIUM'), + (104, 'METAL_IMPLANTS', 'HIGH'), + (105, 'TITANIUM', 'MEDIUM'), + (106, 'STAINLESS_STEEL', 'MEDIUM'), + (107, 'PENICILLIN_DERIVATIVES', 'HIGH'), + (108, 'VANCOMYCIN', 'HIGH'), + (109, 'HEPARIN', 'HIGH'), + (110, 'BLOOD_PRODUCTS', 'HIGH'), + (111, 'LATEX_BALLOONS', 'HIGH'), + (112, 'HOUSE_DUST', 'LOW'), + (113, 'WOOD_DUST', 'MEDIUM'), + (114, 'PAPER_DUST', 'LOW'), + (115, 'TEXTILE_DYES', 'MEDIUM'), + (116, 'INKS', 'LOW'), + (117, 'GLUE', 'MEDIUM'), + (118, 'PAINT_FUMES', 'HIGH'), + (119, 'SOLVENTS', 'HIGH'), + (120, 'RUBBER', 'HIGH'), + (121, 'AMPICILLIN', 'HIGH'), + (122, 'PIPERACILLIN', 'HIGH'), + (123, 'CEFTRIAXONE', 'HIGH'), + (124, 'CEFEPIME', 'HIGH'), + (125, 'MEROPENEM', 'CRITICAL'), + (126, 'IMIPENEM', 'CRITICAL'), + (127, 'ACETAMINOPHEN', 'LOW'), + (128, 'DIPHENHYDRAMINE', 'MEDIUM'), + (129, 'LORATADINE', 'LOW'), + (130, 'CETIRIZINE', 'LOW'), + (131, 'IBUPROFEN_HIGH_DOSE', 'MEDIUM'), + (132, 'KETOROLAC', 'HIGH'), + (133, 'MELOXICAM', 'MEDIUM'), + (134, 'CELECOXIB', 'MEDIUM'), + (135, 'FENTANYL', 'CRITICAL'), + (136, 'METHADONE', 'CRITICAL'), + (137, 'BUPRENORPHINE', 'HIGH'), + (138, 'PROPOFOL', 'CRITICAL'), + (139, 'SEVOFLURANE', 'HIGH'), + (140, 'ISOFLURANE', 'HIGH'), + (141, 'METFORMIN', 'MEDIUM'), + (142, 'INSULIN_ANALOGS', 'HIGH'), + (143, 'ATENOLOL', 'MEDIUM'), + (144, 'METOPROLOL', 'MEDIUM'), + (145, 'LISINOPRIL', 'HIGH'), + (146, 'ENALAPRIL', 'HIGH'), + (147, 'WARFARIN', 'HIGH'), + (148, 'HEPARIN_SODIUM', 'HIGH'), + (149, 'SERTRALINE', 'MEDIUM'), + (150, 'FLUOXETINE', 'MEDIUM'), + (151, 'CITALOPRAM', 'MEDIUM'), + (152, 'CISPLATIN', 'CRITICAL'), + (153, 'DOXORUBICIN', 'CRITICAL'), + (154, 'CYCLOPHOSPHAMIDE', 'CRITICAL'), + (155, 'PREDNISOLONE', 'HIGH'), + (156, 'DEXAMETHASONE', 'HIGH'), + (157, 'ALBUTEROL', 'MEDIUM'), + (158, 'SALBUTAMOL', 'MEDIUM'), + (159, 'LEVOFLOXACIN', 'HIGH'); + +INSERT INTO symptoms (symptom_id, name, description) VALUES + (1, 'ACID_REFLUX_IN_BABIES', 'Reflux in infants'), + (2, 'ACID_REFLUX', 'Stomach acid rising into esophagus'), + (3, 'AMNESIA', 'Memory loss'), + (4, 'ANAL_PAIN', 'Pain in anal region'), + (5, 'ANGER', 'Emotional irritation or rage'), + (6, 'ANKLE_PAIN', 'Pain in ankle'), + (7, 'SWOLLEN_ANKLES_FEET_LEGS', 'Swelling in lower limbs'), + (8, 'ANOSMIA', 'Loss of smell'), + (9, 'ITCHY_BOTTOM', 'Itching around anus'), + (10, 'ANXIETY', 'Fear and panic symptoms'), + (11, 'ARM_PAIN', 'Pain in arm'), + (12, 'BACK_PAIN', 'Pain in back'), + (13, 'BAD_BREATH', 'Unpleasant mouth odor'), + (14, 'BEDWETTING', 'Night-time urination'), + (15, 'VOMITING', 'Expulsion of stomach contents'), + (16, 'POSTMENOPAUSAL_BLEEDING', 'Bleeding after menopause'), + (17, 'BLEEDING_BETWEEN_PERIODS', 'Bleeding between periods'), + (18, 'RECTAL_BLEEDING', 'Bleeding from rectum'), + (19, 'BLEEDING_IN_PREGNANCY', 'Bleeding during pregnancy'), + (20, 'BLOATING', 'Abdominal swelling'), + (21, 'BLOOD_IN_PHLEGM', 'Blood in mucus'), + (22, 'BLOOD_IN_SEMEN', 'Blood in semen'), + (23, 'BLOOD_IN_URINE', 'Blood in urine'), + (24, 'CYANOSIS', 'Blue skin or lips'), + (25, 'BLUSHING', 'Facial redness'), + (26, 'BODY_ODOR', 'Strong smell'), + (27, 'BOWEL_INCONTINENCE', 'Loss of bowel control'), + (28, 'BREAST_LUMPS', 'Mass in breast'), + (29, 'BREAST_PAIN', 'Pain in breast'), + (30, 'EXOPHTHALMOS', 'Bulging eyes'), + (31, 'CATARRH', 'Excess mucus'), + (32, 'CHEST_PAIN', 'Chest pain'), + (33, 'COCCYX_PAIN', 'Tailbone pain'), + (34, 'CONFUSION', 'Sudden confusion'), + (35, 'CONSTIPATION', 'Hard stools'), + (36, 'COUGH', 'Cough reflex'), + (37, 'COUGHING_BLOOD', 'Blood in cough'), + (38, 'DEAFNESS', 'Hearing loss'), + (39, 'DEHYDRATION', 'Fluid loss'), + (40, 'DELIRIUM', 'Acute confusion'), + (41, 'DENTAL_PAIN', 'Tooth pain'), + (42, 'DIARRHEA_VOMITING', 'Digestive upset'), + (43, 'DIZZINESS', 'Balance disturbance'), + (44, 'DOUBLE_VISION', 'Seeing double'), + (45, 'DRY_EYES', 'Eye dryness'), + (46, 'DRY_LIPS', 'Dry lips'), + (47, 'DRY_MOUTH', 'Lack of saliva'), + (48, 'DYSPHAGIA', 'Swallowing difficulty'), + (49, 'EARACHE', 'Pain in ear'), + (50, 'EJACULATION_PROBLEMS', 'Problems with ejaculation'), + (51, 'ELBOW_AND_ARM_PAIN', 'Pain in elbow or arm'), + (52, 'EXCESSIVE_DAYTIME_SLEEPINESS', 'Abnormal daytime sleepiness'), + (53, 'EXCESSIVE_HAIR_GROWTH', 'Increased hair growth'), + (54, 'EXCESSIVE_SWEATING', 'High sweating levels'), + (55, 'EXCESSIVE_THIRST', 'Increased thirst'), + (56, 'FLOATERS_AND_FLASHES', 'Visual disturbances in vision'), + (57, 'EYE_PAIN', 'Pain in eye'), + (58, 'EYELID_PROBLEMS', 'Problems affecting eyelids'), + (59, 'FAINTING', 'Temporary loss of consciousness'), + (60, 'FLATULENCE', 'Gas in digestive system'), + (61, 'NAUSEA', 'Feeling of sickness'), + (62, 'FEVER', 'High body temperature'), + (63, 'FINGER_PAIN', 'Pain in fingers'), + (64, 'SEIZURES', 'Sudden uncontrolled brain activity'), + (65, 'FOOT_PAIN', 'Pain in foot'), + (66, 'HAIR_LOSS', 'Hair shedding'), + (67, 'HALLUCINATIONS_AND_HEARING_VOICES', 'Perception without stimulus'), + (68, 'HAND_PAIN', 'Pain in hand'), + (69, 'HEADACHE', 'Pain in head'), + (70, 'HEARING_LOSS', 'Reduced hearing ability'), + (71, 'HEART_PALPITATIONS', 'Irregular heartbeat sensation'), + (72, 'HEARTBURN_AND_ACID_REFLUX', 'Burning chest sensation'), + (73, 'HEAVY_PERIODS', 'Excess menstrual bleeding'), + (74, 'HEEL_PAIN', 'Pain in heel'), + (75, 'HICCUPS', 'Involuntary diaphragm spasms'), + (76, 'HIP_PAIN', 'Pain in hip'), + (77, 'HIVES', 'Skin rash with itching'), + (78, 'INDIGESTION', 'Difficulty digesting food'), + (79, 'INSOMNIA', 'Difficulty sleeping'), + (80, 'ITCHY_SKIN', 'Skin itching'), + (81, 'JOINT_PAIN', 'Pain in joints'), + (82, 'KNEE_PAIN', 'Pain in knee'), + (83, 'LEG_CRAMPS', 'Muscle cramps in legs'), + (84, 'LIMPING', 'Difficulty walking normally'), + (85, 'LOW_MOOD', 'Depressive mood state'), + (86, 'LUMPS', 'Abnormal tissue swelling'), + (87, 'MEMORY_LOSS', 'Difficulty remembering information'), + (88, 'METALLIC_TASTE', 'Unusual taste in mouth'), + (89, 'MISSED_PERIODS', 'Absent menstrual cycle'), + (90, 'NAIL_PROBLEMS', 'Abnormal nails'), + (91, 'NECK_PAIN', 'Pain in neck'), + (92, 'NIGHT_SWEATS', 'Sweating during sleep'), + (93, 'NIPPLE_DISCHARGE', 'Fluid from nipple'), + (94, 'OVULATION_PAIN', 'Pain during ovulation'), + (95, 'TESTICLE_PAIN', 'Pain in testicles'), + (96, 'PAINFUL_PERIODS', 'Pain during menstruation'), + (97, 'PARALYSIS', 'Loss of movement'), + (98, 'PELVIC_PAIN', 'Pain in pelvic area'), + (99, 'PINS_AND_NEEDLES', 'Tingling sensation'), + (100, 'PRIAPISM', 'Persistent painful erection'), + (101, 'RASH', 'Skin eruption or irritation'), + (102, 'RED_EYE', 'Redness in eye'), + (103, 'RUNNY_NOSE', 'Nasal discharge'), + (104, 'SHORTNESS_OF_BREATH', 'Difficulty breathing'), + (105, 'SORE_THROAT', 'Pain in throat'), + (106, 'SNEEZING', 'Reflex to clear nasal passages'), + (107, 'SNORING', 'Noisy breathing during sleep'), + (108, 'STOMACH_PAIN', 'Pain in abdomen'), + (109, 'STRESS', 'Mental strain condition'), + (110, 'STRETCH_MARKS', 'Skin streaking'), + (111, 'SWELLING', 'Abnormal enlargement of tissue'), + (112, 'SWOLLEN_GLANDS', 'Enlarged lymph nodes'), + (113, 'TASTE_CHANGE', 'Altered taste perception'), + (114, 'TREMOR', 'Uncontrolled shaking'), + (115, 'TIREDNESS', 'Fatigue'), + (116, 'TINNITUS', 'Ringing in ears'), + (117, 'TOOTHACHE', 'Pain in teeth'), + (118, 'TWITCHING', 'Muscle spasms'), + (119, 'UNINTENTIONAL_WEIGHT_LOSS', 'Weight loss without trying'), + (120, 'URINARY_INCONTINENCE', 'Loss of bladder control'), + (121, 'VAGINAL_BLEEDING', 'Bleeding from vagina'), + (122, 'VISION_LOSS', 'Reduced vision ability'), + (123, 'VOMITING_BLOOD', 'Blood in vomit'), + (124, 'WATERING_EYES', 'Excess tear production'), + (125, 'WHEEZING', 'Whistling breathing sound'), + (126, 'WRIST_PAIN', 'Pain in wrist'); + +INSERT INTO doctors (doctor_id, first_name, last_name, email_address, level_id, specialization_id, department_id) VALUES + (1, 'Ivan', 'Stojanov', 'ivan.stojanov@medora.com', 1, 1, 1), + (2, 'Elena', 'Kirova', 'elena.kirova@medora.com', 2, 1, 1), + (3, 'Nikola', 'Trajkov', 'nikola.trajkov@medora.com', 3, 1, 1), + (4, 'Sara', 'Dimova', 'sara.dimova@medora.com', 4, 1, 1), + (5, 'Marija', 'Bajramovska', 'marija.bajramovska@medora.com', 5, 1, 1), + (6, 'Ana', 'Petrova', 'ana.petrova@medora.com', 1, 2, 2), + (7, 'Petar', 'Iliev', 'petar.iliev@medora.com', 2, 2, 2), + (8, 'Mila', 'Stojanova', 'mila.stojanova@medora.com', 3, 2, 2), + (9, 'Dejan', 'Markovski', 'dejan.markovski@medora.com', 4, 2, 2), + (10, 'Kristina', 'Todorova', 'kristina.todorova@medora.com', 5, 2, 2), + (11, 'Goran', 'Spasov', 'goran.spasov@medora.com', 1, 3, 3), + (12, 'Ivana', 'Hristova', 'ivana.hristova@medora.com', 2, 3, 3), + (13, 'Bojan', 'Nikolovski', 'bojan.nikolovski@medora.com', 3, 3, 3), + (14, 'Tea', 'Angelova', 'tea.angelova@medora.com', 4, 3, 3), + (15, 'Filip', 'Jovanov', 'filip.jovanov@medora.com', 5, 3, 3), + (16, 'Natasha', 'Petkovska', 'natasha.petkovska@medora.com', 1, 4, 4), + (17, 'Viktor', 'Andreev', 'viktor.andreev@medora.com', 2, 4, 4), + (18, 'Sofija', 'Marinova', 'sofija.marinova@medora.com', 3, 4, 4), + (19, 'Luka', 'Nikolov', 'luka.nikolov@medora.com', 4, 4, 4), + (20, 'Marija', 'Damjanova', 'marija.damjanova@medora.com', 5, 4, 4), + (21, 'Katerina', 'Ilieva', 'katerina.ilieva@medora.com', 1, 5, 5), + (22, 'Dimitar', 'Petrov', 'dimitar.petrov@medora.com', 2, 5, 5), + (23, 'Jana', 'Kostova', 'jana.kostova@medora.com', 3, 5, 5), + (24, 'Stefan', 'Todorov', 'stefan.todorov@medora.com', 4, 5, 5), + (25, 'Elena', 'Markova', 'elena.markova@medora.com', 5, 5, 5), + (26, 'Aleksandar', 'Ristovski', 'aleksandar.ristovski@medora.com', 1, 6, 6), + (27, 'Ivana', 'Trajkovska', 'ivana.trajkovska@medora.com', 2, 6, 6), + (28, 'Goran', 'Nikolov', 'goran.nikolov@medora.com', 3, 6, 6), + (29, 'Mila', 'Stojkova', 'mila.stojkova@medora.com', 4, 6, 6), + (30, 'Bojan', 'Petrov', 'bojan.petrov@medora.com', 5, 6, 6), + (31, 'Petar', 'Angelov', 'petar.angelov@medora.com', 1, 7, 7), + (32, 'Ana', 'Dimovska', 'ana.dimovska@medora.com', 2, 7, 7), + (33, 'Nikola', 'Kostadinov', 'nikola.kostadinov@medora.com', 3, 7, 7), + (34, 'Sara', 'Petrova', 'sara.petrova@medora.com', 4, 7, 7), + (35, 'Marko', 'Stojanovski', 'marko.stojanovski@medora.com', 5, 7, 7), + (36, 'Elena', 'Markovic', 'elena.markovic@medora.com', 1, 8, 8), + (37, 'Ivan', 'Petrovic', 'ivan.petrovic@medora.com', 2, 8, 8), + (38, 'Dejan', 'Ilievski', 'dejan.ilievski@medora.com', 3, 8, 8), + (39, 'Kristina', 'Ristova', 'kristina.ristova@medora.com', 4, 8, 8), + (40, 'Filip', 'Sokolov', 'filip.sokolov@medora.com', 5, 8, 8), + (41, 'Goran', 'Spasovski', 'goran.spasovski@medora.com', 1, 9, 9), + (42, 'Ivana', 'Hristovska', 'ivana.hristovska@medora.com', 2, 9, 9), + (43, 'Bojan', 'Nikolovska', 'bojan.nikolovska@medora.com', 3, 9, 9), + (44, 'Tea', 'Angelkovska', 'tea.angelkovska@medora.com', 4, 9, 9), + (45, 'Luka', 'Petrovski', 'luka.petrovski@medora.com', 5, 9, 9), + (46, 'Marija', 'Ristova', 'marija.ristova@medora.com', 1, 10, 10), + (47, 'Viktor', 'Stojanovski', 'viktor.stojanovski@medora.com', 2, 10, 10), + (48, 'Sofija', 'Markovska', 'sofija.markovska@medora.com', 3, 10, 10), + (49, 'Dimitar', 'Ilievski', 'dimitar.ilievski@medora.com', 4, 10, 10), + (50, 'Jana', 'Petrovska', 'jana.petrovska2@medora.com', 5, 10, 10), + (51, 'Stefan', 'Nikolov', 'stefan.nikolov@medora.com', 1, 11, 11), + (52, 'Elena', 'Trajkovska', 'elena.trajkovska@medora.com', 2, 11, 11), + (53, 'Nikola', 'Angelov', 'nikola.angelov@medora.com', 3, 11, 11), + (54, 'Sara', 'Dimovska', 'sara.dimovska@medora.com', 4, 11, 11), + (55, 'Ivan', 'Kostov', 'ivan.kostov@medora.com', 5, 11, 11), + (56, 'Petar', 'Hristov', 'petar.hristov@medora.com', 1, 12, 12), + (57, 'Ana', 'Petrovska', 'ana.petrovska3@medora.com', 2, 12, 12), + (58, 'Dejan', 'Stojkov', 'dejan.stojkov@medora.com', 3, 12, 12), + (59, 'Kristina', 'Ristovska', 'kristina.ristovska@medora.com', 4, 12, 12), + (60, 'Goran', 'Nikolovski', 'goran.nikolovski@medora.com', 5, 12, 12), + (61, 'Ivana', 'Markov', 'ivana.markov@medora.com', 1, 13, 13), + (62, 'Bojan', 'Petrovski', 'bojan.petrovski1@medora.com', 2, 13, 13), + (63, 'Tea', 'Ilieva', 'tea.ilieva@medora.com', 3, 13, 13), + (64, 'Filip', 'Spasov', 'filip.spasov@medora.com', 4, 13, 13), + (65, 'Marija', 'Angelova', 'marija.angelova@medora.com', 5, 13, 13), + (66, 'Viktor', 'Kirov', 'viktor.kirov@medora.com', 1, 14, 14), + (67, 'Sofija', 'Petrovska', 'sofija.petrovska@medora.com', 2, 14, 14), + (68, 'Luka', 'Ristovski', 'luka.ristovski@medora.com', 3, 14, 14), + (69, 'Dimitar', 'Markov', 'dimitar.markov@medora.com', 4, 14, 14), + (70, 'Jana', 'Nikolova', 'jana.nikolova@medora.com', 5, 14, 14), + (71, 'Marko', 'Stojanov', 'marko.stojanov@medora.com', 1, 15, 15), + (72, 'Elena', 'Ilievska', 'elena.ilievska@medora.com', 2, 15, 15), + (73, 'Nikola', 'Petrov', 'nikola.petrov@medora.com', 3, 15, 15), + (74, 'Sara', 'Trajkovska', 'sara.trajkovska@medora.com', 4, 15, 15), + (75, 'Ivan', 'Dimov', 'ivan.dimov@medora.com', 5, 15, 15), + (76, 'Goran', 'Ristov', 'goran.ristov@medora.com', 1, 16, 16), + (77, 'Ivana', 'Kostadinova', 'ivana.kostadinova@medora.com', 2, 16, 16), + (78, 'Bojan', 'Petkov', 'bojan.petkov@medora.com', 3, 16, 16), + (79, 'Tea', 'Markovska', 'tea.markovska@medora.com', 4, 16, 16), + (80, 'Filip', 'Stojanov', 'filip.stojanov@medora.com', 5, 16, 16), + (81, 'Marija', 'Hristovska', 'marija.hristovska@medora.com', 1, 17, 17), + (82, 'Viktor', 'Iliev', 'viktor.iliev@medora.com', 2, 17, 17), + (83, 'Sofija', 'Petrovski', 'sofija.petrovski@medora.com', 3, 17, 17), + (84, 'Luka', 'Angelovska', 'luka.angelovska@medora.com', 4, 17, 17), + (85, 'Dimitar', 'Kirovski', 'dimitar.kirovski@medora.com', 5, 17, 17), + (86, 'Jana', 'Ristova', 'jana.ristova@medora.com', 1, 18, 18), + (87, 'Marko', 'Petrovic', 'marko.petrovic@medora.com', 2, 18, 18), + (88, 'Elena', 'Stojkovska', 'elena.stojkovska@medora.com', 3, 18, 18), + (89, 'Nikola', 'Trajkovic', 'nikola.trajkovic@medora.com', 4, 18, 18), + (90, 'Sara', 'Dimkovska', 'sara.dimkovska@medora.com', 5, 18, 18), + (91, 'Ivan', 'Spasov', 'ivan.spasov@medora.com', 1, 19, 19), + (92, 'Petar', 'Nikolov', 'petar.nikolov@medora.com', 2, 19, 19), + (93, 'Ana', 'Markovska', 'ana.markovska@medora.com', 3, 19, 19), + (94, 'Dejan', 'Iliev', 'dejan.iliev@medora.com', 4, 19, 19), + (95, 'Kristina', 'Petrova', 'kristina.petrova@medora.com', 5, 19, 19), + (96, 'Goran', 'Stojanovski', 'goran.stojanovski@medora.com', 1, 20, 20), + (97, 'Ivana', 'Hristova', 'ivana.hristova1@medora.com', 2, 20, 20), + (98, 'Bojan', 'Kostov', 'bojan.kostov@medora.com', 3, 20, 20), + (99, 'Tea', 'Petrovska', 'tea.petrovska@medora.com', 4, 20, 20), + (100, 'Filip', 'Ristovski', 'filip.ristovski@medora.com', 5, 20, 20), + (101, 'Marija', 'Angelovska', 'marija.angelovska@medora.com', 1, 21, 21), + (102, 'Viktor', 'Markovski', 'viktor.markovski@medora.com', 2, 21, 21), + (103, 'Sofija', 'Petrova', 'sofija.petrova@medora.com', 3, 21, 21), + (104, 'Luka', 'Ilievski', 'luka.ilievski@medora.com', 4, 21, 21), + (105, 'Dimitar', 'Stojanov', 'dimitar.stojanov@medora.com', 5, 21, 21), + (106, 'Jana', 'Kirovska', 'jana.kirovska@medora.com', 1, 22, 22), + (107, 'Marko', 'Petkov', 'marko.petkov@medora.com', 2, 22, 22), + (108, 'Elena', 'Ristova', 'elena.ristova@medora.com', 3, 22, 22), + (109, 'Nikola', 'Dimov', 'nikola.dimov@medora.com', 4, 22, 22), + (110, 'Sara', 'Nikolova', 'sara.nikolova@medora.com', 5, 22, 22), + (111, 'Ivan', 'Trajkov', 'ivan.trajkov@medora.com', 1, 23, 23), + (112, 'Petar', 'Stojkov', 'petar.stojkov@medora.com', 2, 23, 23), + (113, 'Ana', 'Petrovska', 'ana.petrovska1@medora.com', 3, 23, 23), + (114, 'Dejan', 'Markov', 'dejan.markov@medora.com', 4, 23, 23), + (115, 'Kristina', 'Ilieva', 'kristina.ilieva@medora.com', 5, 23, 23), + (116, 'Filip', 'Kostadinov', 'filip.kostadinov@medora.com', 1, 24, 24), + (117, 'Ivana', 'Spasova', 'ivana.spasova@medora.com', 2, 24, 24), + (118, 'Bojan', 'Petrovski', 'bojan.petrovski2@medora.com', 3, 24, 24), + (119, 'Teodora', 'Angelova', 'teodora.angelova@medora.com', 4, 24, 24), + (120, 'Mila', 'Lukanovska', 'mila.lukanovska@medora.com', 5, 24, 24); + + +INSERT INTO patients ( + patient_id, first_name, last_name, email_address, date_of_birth, + blood_type, gender, phone_number, embg +) VALUES + (1, 'Maja', 'Veljanova', 'maja.veljanova@gmail.com', '1994-02-14', 'A+', 'FEMALE', '070111222', '1402994123456'), + (2, 'Kristijan', 'Bojkov', 'kristijan.bojkov@gmail.com', '1991-06-21', 'O-', 'MALE', '071222333', '2106991123457'), + (3, 'Tamara', 'Gorgieva', 'tamara.gorgieva@gmail.com', '2000-11-03', 'B+', 'FEMALE', '072333444', '0311000123458'), + (4, 'Aleks', 'Nikolovski', 'aleks.nikolovski@gmail.com', '1988-09-10', 'AB+', 'MALE', '073444555', '1009988123459'), + (5, 'Sara', 'Milevska', 'sara.milevska@gmail.com', '1997-12-25', 'A-', 'FEMALE', '074555666', '2512997123460'), + (6, 'Stefan', 'Arsovski', 'stefan.arsovski@gmail.com', '1993-04-18', 'O+', 'MALE', '075666777', '1804993123461'), + (7, 'Elena', 'Petreska', 'elena.petreska@gmail.com', '1999-07-09', 'B-', 'FEMALE', '076777888', '0907999123462'), + (8, 'Viktor', 'Stankov', 'viktor.stankov@gmail.com', '1990-01-30', 'A+', 'MALE', '077888999', '3001990123463'), + (9, 'Ivana', 'Dimitrova', 'ivana.dimitrova@gmail.com', '1995-05-12', 'O-', 'FEMALE', '078999111', '1205995123464'), + (10, 'Marko', 'Lazarevski', 'marko.lazarevski@gmail.com', '1987-08-22', 'AB-', 'MALE', '070123456', '2208987123465'), + (11, 'Jana', 'Trajkovska', 'jana.trajkovska@gmail.com', '2001-03-17', 'A+', 'FEMALE', '071234567', '1703001123466'), + (12, 'Petar', 'Kostov', 'petar.kostov@gmail.com', '1992-10-05', 'B+', 'MALE', '072345678', '0510992123467'), + (13, 'Ana', 'Ristova', 'ana.ristova@gmail.com', '1996-06-28', 'O+', 'FEMALE', '073456789', '2806996123468'), + (14, 'Nikola', 'Sokolov', 'nikola.sokolov@gmail.com', '1989-02-11', 'A-', 'MALE', '074567890', '1102989123469'), + (15, 'Mila', 'Petrova', 'mila.petrova@gmail.com', '2002-09-19', 'AB+', 'FEMALE', '075678901', '1909022123470'), + (16, 'Dejan', 'Ilievski', 'dejan.ilievski@gmail.com', '1994-11-11', 'O+', 'MALE', '076789012', '1111994123471'), + (17, 'Kristina', 'Markovska', 'kristina.markovska@gmail.com', '1998-01-14', 'B+', 'FEMALE', '077890123', '1401988123472'), + (18, 'Goran', 'Petrov', 'goran.petrov@gmail.com', '1986-07-07', 'A+', 'MALE', '078901234', '0707986123473'), + (19, 'Sofija', 'Nikolova', 'sofija.nikolova@gmail.com', '2000-12-02', 'O-', 'FEMALE', '070112233', '0212000123474'), + (20, 'Dimitar', 'Angelov', 'dimitar.angelov@gmail.com', '1991-03-03', 'B-', 'MALE', '071223344', '0303991123475'), + (21, 'Teodora', 'Hristova', 'teodora.hristova@gmail.com', '1997-08-08', 'A+', 'FEMALE', '072334455', '0808997123476'), + (22, 'Filip', 'Stojanov', 'filip.stojanov@gmail.com', '1993-05-15', 'AB+', 'MALE', '073445566', '1505993123477'), + (23, 'Marija', 'Kirovska', 'marija.kirovska@gmail.com', '1999-10-10', 'O+', 'FEMALE', '074556677', '1010999123478'), + (24, 'Ivan', 'Bojkov', 'ivan.bojkov@gmail.com', '1988-12-12', 'A-', 'MALE', '075667788', '1212988123479'), + (25, 'Lina', 'Petrovska', 'lina.petrovska@gmail.com', '2001-04-04', 'B+', 'FEMALE', '076778899', '0404001123480'), + (26, 'Bojan', 'Spasov', 'bojan.spasov@gmail.com', '1992-02-20', 'O+', 'MALE', '077889900', '2002992123481'), + (27, 'Ana', 'Dimovska', 'ana.dimovska@gmail.com', '1995-09-09', 'A+', 'FEMALE', '078990011', '0909995123482'), + (28, 'Vladimir', 'Ristovski', 'vladimir.ristovski@gmail.com', '1987-06-16', 'AB-', 'MALE', '070998877', '1606987123483'), + (29, 'Katerina', 'Todorova', 'katerina.todorova@gmail.com', '2003-01-01', 'O-', 'FEMALE', '071887766', '0101033123484'), + (30, 'Martin', 'Iliev', 'martin.iliev@gmail.com', '1990-11-23', 'B+', 'MALE', '072776655', '2311990123485'), + (31, 'Elena', 'Trajkov', 'elena.trajkov@gmail.com', '1996-03-13', 'A-', 'FEMALE', '073665544', '1303996123486'), + (32, 'Aleksandar', 'Petrovski', 'aleksandar.petrovski@gmail.com', '1994-07-27', 'O+', 'MALE', '074554433', '2707994123487'), + (33, 'Maja', 'Stojanovska', 'maja.stojanovska@gmail.com', '1998-09-18', 'B-', 'FEMALE', '075443322', '1809998123488'), + (34, 'Nikola', 'Gligorov', 'nikola.gligorov@gmail.com', '1989-05-05', 'A+', 'MALE', '076332211', '0505989123489'), + (35, 'Sara', 'Ilieva', 'sara.ilieva@gmail.com', '2002-12-12', 'AB+', 'FEMALE', '077221100', '1212022123490'), + (36, 'Petar', 'Markovski', 'petar.markovski@gmail.com', '1993-03-03', 'O+', 'MALE', '078110099', '0303993123491'), + (37, 'Ivana', 'Petrovska', 'ivana.petrovska@gmail.com', '1997-07-07', 'A-', 'FEMALE', '070223344', '0707997123492'), + (38, 'Goran', 'Nikolovski', 'goran.nikolovski@gmail.com', '1986-10-10', 'B+', 'MALE', '071334455', '1010986123493'), + (39, 'Jana', 'Ristova', 'jana.ristova@gmail.com', '2000-01-20', 'O-', 'FEMALE', '072445566', '2001000123494'); + + + +INSERT INTO admin ( + admin_id, + username, + name, + lastname, + email +) VALUES + (1, 'admin_ilija', 'Ilija', 'Stojanov', 'ilija.stojanov@adminmedora.com'), + (2, 'admin_elena', 'Elena', 'Kirova', 'elena.kirova@adminmedora.com'), + (3, 'admin_marjan', 'Marjan', 'Petrov', 'marjan.petrov@adminmedora.com'), + (4, 'admin_vesna', 'Vesna', 'Dimova', 'vesna.dimova@adminmedora.com'), + (5, 'admin_dushanka', 'Dushanka', 'Trajkovska', 'drushanka.trajkovska@adminmedora.com'); + + +INSERT INTO lab_technician ( + technician_id, + username, + name, + lastname, + email +) VALUES + (1, 'lab_darko', 'Darko', 'Milosev', 'darko.milosev@labmedora.com'), + (2, 'lab_biljana', 'Biljana', 'Trajkovska', 'biljana.trajkovska@labmedora.com'), + (3, 'lab_stefan', 'Stefan', 'Nikolovski', 'stefan.nikolovski@labmedora.com'), + (4, 'lab_marina', 'Marina', 'Petreska', 'marina.petreska@labmedora.com'), + (5, 'lab_aleksandar', 'Aleksandar', 'Ristovski', 'aleksandar.ristovski@labmedora.com'); + + + +INSERT INTO appointments ( + appointment_id, + appointment_date, + appointment_time, + status, + patient_id, + doctor_id +) VALUES + (1, '2026-01-10', '09:00:00', 'SCHEDULED', 1, 6), + (2, '2026-01-10', '09:30:00', 'COMPLETED', 2, 12), + (3, '2026-01-11', '10:00:00', 'COMPLETED', 3, 18), + (4, '2026-01-11', '10:30:00', 'CANCELLED', 4, 24), + (5, '2026-01-12', '11:00:00', 'SCHEDULED', 5, 31), + (6, '2026-01-12', '11:30:00', 'COMPLETED', 6, 37), + (7, '2026-01-13', '12:00:00', 'IN_PROGRESS', 7, 43), + (8, '2026-01-13', '12:30:00', 'SCHEDULED', 8, 49), + (9, '2026-01-14', '13:00:00', 'COMPLETED', 9, 55), + (10, '2026-01-14', '13:30:00', 'SCHEDULED', 10, 61); + + +INSERT INTO diagnosis (diagnosis_id, name, description, patient_id, doctor_id) VALUES + (1, 'Essential hypertension', 'Persistent high blood pressure', 1, 1), + (2, 'Type 2 diabetes mellitus', 'Chronic glucose metabolism disorder', 1, 1), + (3, 'Acute bronchitis', 'Inflammation of bronchial tubes', 1, 1), + (4, 'Asthma', 'Chronic airway inflammation', 1, 1), + (5, 'Pneumonia', 'Lung infection', 1, 1), + (6, 'Chronic obstructive pulmonary disease', 'Progressive lung disease', 1, 1), + (7, 'Migraine', 'Recurrent severe headaches', 1, 1), + (8, 'Epilepsy', 'Neurological seizure disorder', 1, 1), + (9, 'Parkinson disease', 'Neurodegenerative movement disorder', 1, 1), + (10, 'Alzheimer disease', 'Progressive cognitive decline', 1, 1), + (11, 'Osteoarthritis', 'Degenerative joint disease', 1, 1), + (12, 'Rheumatoid arthritis', 'Autoimmune joint inflammation', 1, 1), + (13, 'Fracture of femur', 'Bone fracture of thigh', 1, 1), + (14, 'Lumbar disc herniation', 'Spinal disc displacement', 1, 1), + (15, 'Scoliosis', 'Abnormal spine curvature', 1, 1), + (16, 'Gastritis', 'Stomach lining inflammation', 1, 1), + (17, 'Peptic ulcer disease', 'Ulcer in stomach or duodenum', 1, 1), + (18, 'Gastroesophageal reflux disease', 'Acid reflux disorder', 1, 1), + (19, 'Appendicitis', 'Inflammation of appendix', 1, 1), + (20, 'Cholelithiasis', 'Gallstones', 1, 1), + (21, 'Acute kidney injury', 'Sudden kidney failure', 1, 1), + (22, 'Chronic kidney disease', 'Long-term kidney damage', 1, 1), + (23, 'Urinary tract infection', 'Bacterial urinary infection', 1, 1), + (24, 'Nephrotic syndrome', 'Kidney protein loss disorder', 1, 1), + (25, 'Kidney stone', 'Renal calculus', 1, 1), + (26, 'Breast cancer', 'Malignant tumor of breast', 1, 1), + (27, 'Lung cancer', 'Malignant lung tumor', 1, 1), + (28, 'Leukemia', 'Blood cancer', 1, 1), + (29, 'Lymphoma', 'Lymphatic system cancer', 1, 1), + (30, 'Skin melanoma', 'Malignant skin tumor', 1, 1), + (31, 'Atopic dermatitis', 'Chronic skin inflammation', 1, 1), + (32, 'Psoriasis', 'Autoimmune skin disorder', 1, 1), + (33, 'Acne vulgaris', 'Inflammatory skin condition', 1, 1), + (34, 'Urticaria', 'Allergic skin reaction', 1, 1), + (35, 'Cellulitis', 'Bacterial skin infection', 1, 1), + (36, 'Influenza', 'Viral respiratory infection', 1, 1), + (37, 'COVID-19', 'Coronavirus infection', 1, 1), + (38, 'Hepatitis B', 'Liver viral infection', 1, 1), + (39, 'Hepatitis C', 'Chronic liver infection', 1, 1), + (40, 'Tuberculosis', 'Mycobacterial infection', 1, 1), + (41, 'Anemia', 'Low red blood cell count', 1, 1), + (42, 'Iron deficiency anemia', 'Lack of iron in blood', 1, 1), + (43, 'Hemophilia', 'Blood clotting disorder', 1, 1), + (44, 'Thrombocytopenia', 'Low platelet count', 1, 1), + (45, 'Deep vein thrombosis', 'Blood clot in deep veins', 1, 1), + (46, 'Pulmonary embolism', 'Blocked lung artery', 1, 1), + (47, 'Hyperthyroidism', 'Overactive thyroid gland', 1, 1), + (48, 'Hypothyroidism', 'Underactive thyroid gland', 1, 1), + (49, 'Cushing syndrome', 'Excess cortisol production', 1, 1), + (50, 'Addison disease', 'Adrenal insufficiency', 1, 1), + (51, 'Depression', 'Mood disorder', 1, 1), + (52, 'Anxiety disorder', 'Chronic anxiety condition', 1, 1), + (53, 'Bipolar disorder', 'Mood instability disorder', 1, 1), + (54, 'Schizophrenia', 'Psychotic disorder', 1, 1), + (55, 'Insomnia', 'Sleep disorder', 1, 1), + (56, 'Sleep apnea', 'Breathing interruption during sleep', 1, 1), + (57, 'Otitis media', 'Middle ear infection', 1, 1), + (58, 'Tonsillitis', 'Tonsil inflammation', 1, 1), + (59, 'Sinusitis', 'Sinus infection', 1, 1), + (60, 'Hearing loss', 'Reduced hearing ability', 1, 1), + (61, 'Cataract', 'Eye lens clouding', 1, 1), + (62, 'Glaucoma', 'Optic nerve damage', 1, 1), + (63, 'Macular degeneration', 'Retinal deterioration', 1, 1), + (64, 'Conjunctivitis', 'Eye inflammation', 1, 1), + (65, 'Dry eye syndrome', 'Insufficient tear production', 1, 1), + (66, 'Obesity', 'Excess body fat', 1, 1), + (67, 'Metabolic syndrome', 'Cluster of metabolic disorders', 1, 1), + (68, 'Hyperlipidemia', 'High cholesterol levels', 1, 1), + (69, 'Vitamin D deficiency', 'Low vitamin D levels', 1, 1), + (70, 'Osteoporosis', 'Bone density loss', 1, 1), + (71, 'Gout', 'Uric acid crystal arthritis', 1, 1), + (72, 'Fibromyalgia', 'Chronic pain disorder', 1, 1), + (73, 'Multiple sclerosis', 'Autoimmune nerve disorder', 1, 1), + (74, 'Myocardial infarction', 'Heart attack', 1, 1), + (75, 'Heart failure', 'Reduced heart pumping function', 1, 1), + (76, 'Arrhythmia', 'Irregular heartbeat', 1, 1), + (77, 'Cardiomyopathy', 'Heart muscle disease', 1, 1), + (78, 'Endocarditis', 'Heart infection', 1, 1), + (79, 'Pericarditis', 'Heart lining inflammation', 1, 1), + (80, 'Atherosclerosis', 'Artery plaque buildup', 1, 1), + (81, 'Sepsis', 'Systemic infection response', 1, 1), + (82, 'Meningitis', 'Brain membrane infection', 1, 1), + (83, 'Encephalitis', 'Brain inflammation', 1, 1), + (84, 'Stroke', 'Brain blood flow interruption', 1, 1), + (85, 'Transient ischemic attack', 'Mini-stroke', 1, 1), + (86, 'Pancreatitis', 'Pancreas inflammation', 1, 1), + (87, 'Liver cirrhosis', 'Chronic liver damage', 1, 1), + (88, 'Fatty liver disease', 'Fat accumulation in liver', 1, 1), + (89, 'Cholecystitis', 'Gallbladder inflammation', 1, 1), + (90, 'Hernia', 'Organ protrusion through muscle', 1, 1), + (91, 'Eczema', 'Skin inflammation', 1, 1), + (92, 'Dermatitis', 'General skin irritation', 1, 1), + (93, 'Allergic rhinitis', 'Nasal allergy', 1, 1), + (94, 'Food allergy reaction', 'Immune response to food', 1, 1), + (95, 'Drug allergy reaction', 'Adverse medication reaction', 1, 1), + (96, 'Anaphylaxis', 'Severe allergic reaction', 1, 1), + (97, 'Heat stroke', 'Overheating condition', 1, 1), + (98, 'Hypothermia', 'Low body temperature', 1, 1), + (99, 'Dehydration', 'Fluid loss condition', 1, 1), + (100, 'Electrolyte imbalance', 'Disturbed mineral levels', 1, 1), + (101, 'Hypertensive crisis', 'Severely elevated blood pressure emergency', 1, 1), + (102, 'Stable angina', 'Chest pain due to reduced blood flow', 1, 1), + (103, 'Unstable angina', 'Acute coronary syndrome symptom', 1, 1), + (104, 'Atrial fibrillation', 'Irregular heart rhythm', 1, 1), + (105, 'Ventricular tachycardia', 'Fast abnormal heart rhythm', 1, 1), + (106, 'Bronchiectasis', 'Permanent airway dilation', 1, 1), + (107, 'Pleural effusion', 'Fluid in lung cavity', 1, 1), + (108, 'Pneumothorax', 'Collapsed lung', 1, 1), + (109, 'Pulmonary fibrosis', 'Lung tissue scarring', 1, 1), + (110, 'Respiratory failure', 'Inadequate oxygen exchange', 1, 1), + (111, 'Bell palsy', 'Facial nerve paralysis', 1, 1), + (112, 'Trigeminal neuralgia', 'Facial nerve pain disorder', 1, 1), + (113, 'Carpal tunnel syndrome', 'Median nerve compression', 1, 1), + (114, 'Peripheral neuropathy', 'Nerve damage disorder', 1, 1), + (115, 'Restless legs syndrome', 'Uncontrolled leg movement urge', 1, 1), + (116, 'Cervical spondylosis', 'Neck spine degeneration', 1, 1), + (117, 'Spinal stenosis', 'Narrowing of spinal canal', 1, 1), + (118, 'Sciatica', 'Sciatic nerve pain', 1, 1), + (119, 'Tension headache', 'Muscle contraction headache', 1, 1), + (120, 'Cluster headache', 'Severe recurring headaches', 1, 1), + (121, 'Crohn disease', 'Inflammatory bowel disease', 1, 1), + (122, 'Ulcerative colitis', 'Colon inflammation disease', 1, 1), + (123, 'Irritable bowel syndrome', 'Functional bowel disorder', 1, 1), + (124, 'Celiac disease', 'Gluten intolerance disorder', 1, 1), + (125, 'Lactose intolerance', 'Milk sugar digestion disorder', 1, 1), + (126, 'Hemorrhoids', 'Swollen rectal veins', 1, 1), + (127, 'Anal fissure', 'Small tear in anal lining', 1, 1), + (128, 'Diverticulitis', 'Colon pouch inflammation', 1, 1), + (129, 'Intestinal obstruction', 'Blocked bowel passage', 1, 1), + (130, 'Peritonitis', 'Abdominal cavity infection', 1, 1), + (131, 'Prostatitis', 'Prostate gland inflammation', 1, 1), + (132, 'Benign prostatic hyperplasia', 'Prostate enlargement', 1, 1), + (133, 'Erectile dysfunction', 'Male sexual dysfunction', 1, 1), + (134, 'End stage renal disease', 'Kidney failure stage 5', 1, 1), + (135, 'Glomerulonephritis', 'Kidney filter inflammation', 1, 1), + (136, 'Pyelonephritis', 'Kidney infection', 1, 1), + (137, 'Bladder cancer', 'Malignant bladder tumor', 1, 1), + (138, 'Testicular torsion', 'Twisted testicle condition', 1, 1), + (139, 'Ovarian cyst', 'Fluid-filled ovary sac', 1, 1), + (140, 'Endometriosis', 'Uterine tissue outside uterus', 1, 1), + (141, 'Thyroid nodule', 'Thyroid gland lump', 1, 1), + (142, 'Goiter', 'Thyroid enlargement', 1, 1), + (143, 'Hyperparathyroidism', 'Excess parathyroid hormone', 1, 1), + (144, 'Hypoparathyroidism', 'Low parathyroid function', 1, 1), + (145, 'Diabetic ketoacidosis', 'Diabetes metabolic emergency', 1, 1), + (146, 'Hypoglycemia', 'Low blood sugar', 1, 1), + (147, 'Hyperglycemia', 'High blood sugar', 1, 1), + (148, 'Metabolic acidosis', 'Excess acid in blood', 1, 1), + (149, 'Respiratory acidosis', 'CO2 buildup in blood', 1, 1), + (150, 'Respiratory alkalosis', 'Low CO2 in blood', 1, 1), + (151, 'Septic shock', 'Severe infection-induced shock', 1, 1), + (152, 'Systemic inflammatory response syndrome', 'Body-wide inflammation', 1, 1), + (153, 'Bacterial meningitis', 'Bacterial brain infection', 1, 1), + (154, 'Viral meningitis', 'Viral brain infection', 1, 1), + (155, 'Hepatic encephalopathy', 'Liver-related brain dysfunction', 1, 1), + (156, 'Alcoholic liver disease', 'Alcohol-related liver damage', 1, 1), + (157, 'Cholangitis', 'Bile duct infection', 1, 1), + (158, 'Pancreatic necrosis', 'Severe pancreatic tissue death', 1, 1), + (159, 'Gastroenteritis', 'Stomach and intestinal infection', 1, 1), + (160, 'Food poisoning', 'Toxin or bacteria ingestion illness', 1, 1), + (161, 'Psoriatic arthritis', 'Joint inflammation from psoriasis', 1, 1), + (162, 'Systemic lupus erythematosus', 'Autoimmune systemic disease', 1, 1), + (163, 'Scleroderma', 'Skin hardening autoimmune disease', 1, 1), + (164, 'Vasculitis', 'Blood vessel inflammation', 1, 1), + (165, 'Raynaud disease', 'Blood flow restriction in extremities', 1, 1), + (166, 'Cellulitis of leg', 'Skin bacterial infection in leg', 1, 1), + (167, 'Impetigo', 'Contagious skin infection', 1, 1), + (168, 'Herpes simplex infection', 'Viral skin infection', 1, 1), + (169, 'Shingles', 'Reactivation of chickenpox virus', 1, 1), + (170, 'Warts', 'HPV skin growths', 1, 1), + (171, 'Vitamin B12 deficiency', 'Low B12 levels', 1, 1), + (172, 'Folate deficiency', 'Low folic acid levels', 1, 1), + (173, 'Scurvy', 'Vitamin C deficiency disease', 1, 1), + (174, 'Rickets', 'Vitamin D deficiency in children', 1, 1), + (175, 'Osteomalacia', 'Softening of bones', 1, 1), + (176, 'Hypercalcemia', 'High calcium levels', 1, 1), + (177, 'Hypocalcemia', 'Low calcium levels', 1, 1), + (178, 'Hyponatremia', 'Low sodium levels', 1, 1), + (179, 'Hypernatremia', 'High sodium levels', 1, 1), + (180, 'Hypokalemia', 'Low potassium levels', 1, 1), + (181, 'Hyperkalemia', 'High potassium levels', 1, 1), + (182, 'Heat exhaustion', 'Heat-related illness', 1, 1), + (183, 'Frostbite', 'Cold-induced tissue damage', 1, 1), + (184, 'Carbon monoxide poisoning', 'Toxic gas exposure', 1, 1), + (185, 'Drug overdose', 'Excess medication intake', 1, 1), + (186, 'Alcohol poisoning', 'Toxic alcohol levels', 1, 1), + (187, 'Snake bite', 'Venomous bite injury', 1, 1), + (188, 'Animal bite infection', 'Infected bite wound', 1, 1), + (189, 'Tetanus', 'Bacterial muscle stiffness disease', 1, 1), + (190, 'Rabies exposure', 'Viral bite exposure', 1, 1), + (191, 'Acute stress disorder', 'Short-term trauma response', 1, 1), + (192, 'Post-traumatic stress disorder', 'Long-term trauma disorder', 1, 1), + (193, 'Panic disorder', 'Sudden anxiety attacks', 1, 1), + (194, 'Social anxiety disorder', 'Fear of social situations', 1, 1), + (195, 'Obsessive compulsive disorder', 'Repetitive thoughts/behavior', 1, 1), + (196, 'Attention deficit hyperactivity disorder', 'Attention regulation disorder', 1, 1), + (197, 'Autism spectrum disorder', 'Neurodevelopmental condition', 1, 1), + (198, 'Intellectual disability', 'Cognitive impairment condition', 1, 1), + (199, 'Sleepwalking', 'Parasomnia disorder', 1, 1), + (200, 'Night terrors', 'Severe sleep disturbance', 1, 1); + + + +INSERT INTO procedures (procedure_id, procedure_type, procedure_date, description, cost, doctor_id, diagnosis_id) VALUES + (1, 'Spirometry', '2026-01-10', 'Lung function test measuring airflow', 50, 1, 4), + (2, 'Bronchoscopy', '2026-01-10', 'Endoscopic examination of airways', 200, 1, 4), + (3, 'Body plethysmography', '2026-01-11', 'Measurement of lung volumes', 180, 1, 5), + (4, 'Diffusion capacity test (DLCO)', '2026-01-11', 'Gas exchange efficiency test', 120, 1, 106), + (5, 'Peak flow measurement', '2026-01-12', 'Maximum exhalation speed test', 30, 1, 4), + (6, 'Blood gas analysis', '2026-01-12', 'Oxygen and CO2 level test', 70, 1, 5), + (7, 'Allergy testing', '2026-01-13', 'Identification of respiratory allergens', 90, 2, 94), + (8, 'Bronchodilator test', '2026-01-13', 'Asthma response test after medication', 80, 1, 4), + (9, 'ECG', '2026-01-14', 'Electrical heart activity recording', 40, 2, 74), + (10, 'Echocardiography', '2026-01-14', 'Ultrasound of the heart', 120, 2, 75), + (11, 'Stress test', '2026-01-15', 'Heart performance under exercise', 100, 2, 74), + (12, 'Holter ECG', '2026-01-15', '24h heart rhythm monitoring', 150, 2, 76), + (13, 'Blood pressure Holter', '2026-01-16', '24h blood pressure monitoring', 140, 2, 1), + (14, 'Cardiac catheterization', '2026-01-16', 'Invasive heart vessel examination', 600, 2, 74), + (15, 'Coronary angiography', '2026-01-17', 'Imaging of coronary arteries', 700, 2, 74), + (16, 'Transesophageal echo', '2026-01-17', 'Internal heart ultrasound', 300, 2, 75), + (17, 'EEG', '2026-01-18', 'Brain electrical activity test', 100, 3, 7), + (18, 'EMG', '2026-01-18', 'Muscle electrical activity test', 120, 3, 113), + (19, 'Evoked potentials', '2026-01-19', 'Nerve response measurement', 130, 3, 113), + (20, 'Lumbar puncture', '2026-01-19', 'CSF fluid collection from spine', 250, 3, 82), + (21, 'Nerve conduction study', '2026-01-20', 'Nerve signal speed test', 110, 3, 113), + (22, 'Carotid Doppler', '2026-01-20', 'Blood flow in neck arteries', 140, 3, 84), + (23, 'Arthroscopy', '2026-01-21', 'Joint inspection using camera', 500, 4, 11), + (24, 'Hip replacement', '2026-01-21', 'Surgical hip joint replacement', 5000, 4, 11), + (25, 'Knee replacement', '2026-01-22', 'Surgical knee joint replacement', 4800, 4, 11), + (26, 'Fracture fixation', '2026-01-22', 'Bone stabilization surgery', 1200, 4, 13), + (27, 'Ligament reconstruction', '2026-01-23', 'Repair of torn ligaments', 2500, 4, 11), + (28, 'Spine surgery', '2026-01-23', 'Surgical spinal intervention', 6000, 4, 14), + (29, 'Gynecological ultrasound', '2026-01-24', 'Ultrasound of female reproductive system', 80, 5, 139), + (30, 'Pap smear', '2026-01-24', 'Cervical cancer screening test', 60, 5, 31), + (31, 'Colposcopy', '2026-01-25', 'Detailed cervix examination', 120, 5, 31), + (32, 'Hysteroscopy', '2026-01-25', 'Uterine cavity inspection', 300, 5, 140), + (33, 'Laparoscopy', '2026-01-26', 'Minimally invasive abdominal surgery', 700, 5, 139), + (34, 'Cesarean section', '2026-01-26', 'Surgical delivery of baby', 1500, 5, 34), + (35, 'Delivery', '2026-01-27', 'Natural childbirth', 800, 5, 35), + (36, 'Cervical biopsy', '2026-01-27', 'Tissue sampling from cervix', 200, 5, 30), + (37, 'Audiometry', '2026-01-28', 'Hearing test', 50, 6, 60), + (38, 'Tympanometry', '2026-01-28', 'Middle ear function test', 60, 6, 57), + (39, 'Nasal endoscopy', '2026-01-29', 'Nasal cavity examination', 120, 6, 59), + (40, 'Laryngoscopy', '2026-01-29', 'Throat and voice box examination', 130, 6, 58), + (41, 'Tonsillectomy', '2026-01-30', 'Removal of tonsils', 400, 6, 58), + (42, 'Sinus surgery', '2026-01-30', 'Surgical sinus treatment', 1200, 6, 59), + (43, 'Vision exam', '2026-02-01', 'Eye vision testing', 40, 7, 60), + (44, 'Optical coherence tomography', '2026-02-01', 'Retina imaging', 150, 7, 63), + (45, 'Perimetry', '2026-02-02', 'Visual field test', 70, 7, 62), + (46, 'Cataract surgery', '2026-02-02', 'Lens replacement surgery', 2000, 7, 61), + (47, 'Laser eye surgery', '2026-02-03', 'Vision correction procedure', 2500, 7, 61), + (48, 'Complete blood count', '2026-02-03', 'Basic blood analysis', 30, 8, 41), + (49, 'Biochemistry panel', '2026-02-04', 'Blood chemical analysis', 50, 8, 2), + (50, 'Coagulation tests', '2026-02-04', 'Blood clotting tests', 60, 8, 43), + (51, 'Hormone tests', '2026-02-05', 'Hormonal level analysis', 80, 8, 47), + (52, 'Microbiology tests', '2026-02-05', 'Infection detection tests', 90, 8, 40), + (53, 'PCR test', '2026-02-06', 'DNA/RNA detection test', 100, 8, 36), + (54, 'X-ray', '2026-02-06', 'Basic imaging scan', 50, 9, 13), + (55, 'CT scan', '2026-02-07', 'Cross-sectional imaging', 200, 9, 13), + (56, 'MRI', '2026-02-07', 'Magnetic resonance imaging', 350, 9, 7), + (57, 'Ultrasound', '2026-02-08', 'Sound wave imaging', 70, 9, 139), + (58, 'Mammography', '2026-02-08', 'Breast imaging', 120, 9, 26), + (59, 'Interventional radiology', '2026-02-09', 'Image-guided procedures', 800, 9, 74), + (60, 'Gastroscopy', '2026-02-09', 'Stomach camera examination', 200, 10, 16), + (61, 'Colonoscopy', '2026-02-10', 'Colon examination', 300, 10, 121), + (62, 'Biopsy', '2026-02-10', 'Tissue sampling', 250, 10, 26), + (63, 'ERCP', '2026-02-11', 'Bile duct procedure', 600, 10, 20), + (64, 'Abdominal ultrasound', '2026-02-11', 'Abdominal organ imaging', 90, 9, 19), + (65, 'Urinary tract ultrasound', '2026-02-12', 'Kidney and bladder imaging', 90, 9, 23), + (66, 'Cystoscopy', '2026-02-12', 'Bladder examination', 200, 11, 23), + (67, 'Urodynamic testing', '2026-02-13', 'Urine flow analysis', 150, 11, 134), + (68, 'Lithotripsy', '2026-02-13', 'Kidney stone breaking', 500, 11, 25), + (69, 'TURP', '2026-02-14', 'Prostate surgery', 1200, 11, 131), + (70, 'Prostate biopsy', '2026-02-14', 'Prostate tissue sampling', 250, 11, 131), + (71, 'Hemodialysis', '2026-02-15', 'Blood cleaning procedure', 200, 12, 22), + (72, 'Peritoneal dialysis', '2026-02-15', 'Abdominal dialysis', 180, 12, 22), + (73, 'Kidney biopsy', '2026-02-16', 'Kidney tissue sampling', 300, 12, 21), + (74, 'Renal clearance tests', '2026-02-16', 'Kidney function tests', 100, 12, 22), + (75, 'Electrolyte tests', '2026-02-17', 'Blood mineral analysis', 60, 12, 100), + (76, 'Chemotherapy', '2026-02-17', 'Cancer drug treatment', 2000, 13, 26), + (77, 'Radiotherapy', '2026-02-18', 'Radiation cancer treatment', 2500, 13, 27), + (78, 'Tumor biopsy', '2026-02-18', 'Cancer tissue sampling', 400, 13, 26), + (79, 'PET scan', '2026-02-19', 'Cancer imaging scan', 900, 9, 26), + (80, 'Immunotherapy', '2026-02-19', 'Immune cancer treatment', 3000, 13, 28), + (81, 'Dermatological exam', '2026-02-20', 'Skin examination', 50, 14, 31), + (82, 'Dermatoscopy', '2026-02-20', 'Skin lesion imaging', 80, 14, 30), + (83, 'Skin biopsy', '2026-02-21', 'Skin tissue sampling', 200, 14, 31), + (84, 'Cryotherapy', '2026-02-21', 'Freezing skin treatment', 150, 14, 34), + (85, 'Laser treatments', '2026-02-22', 'Skin laser therapy', 500, 14, 34), + (86, 'Pediatric exam', '2026-02-22', 'Child medical checkup', 60, 15, 1), + (87, 'Vaccination', '2026-02-23', 'Immunization procedure', 40, 15, 1), + (88, 'Neonatal screening', '2026-02-23', 'Newborn testing', 100, 15, 1), + (89, 'Growth assessment', '2026-02-24', 'Child development check', 50, 15, 1), + (90, 'Psychiatric evaluation', '2026-02-24', 'Mental health assessment', 120, 16, 51), + (91, 'Psychotherapy', '2026-02-25', 'Therapy sessions', 100, 16, 51), + (92, 'Cognitive testing', '2026-02-25', 'Brain function testing', 80, 16, 54), + (93, 'Pharmacotherapy', '2026-02-26', 'Psychiatric medication treatment', 150, 16, 51), + (94, 'Physiotherapy', '2026-02-26', 'Physical rehabilitation treatment', 80, 17, 11), + (95, 'Kinesiotherapy', '2026-02-27', 'Movement-based therapy', 90, 17, 11), + (96, 'Electrotherapy', '2026-02-27', 'Electrical stimulation therapy', 70, 17, 72), + (97, 'Hydrotherapy', '2026-02-28', 'Water-based rehabilitation therapy', 100, 17, 72), + (98, 'Blood count', '2026-03-01', 'Detailed blood cell analysis', 40, 8, 41), + (99, 'Bone marrow biopsy', '2026-03-01', 'Bone marrow sampling', 400, 8, 43), + (100, 'Coagulation studies', '2026-03-02', 'Blood clotting analysis', 80, 8, 43), + (101, 'Blood transfusion', '2026-03-02', 'Transfer of blood products', 300, 8, 45), + (102, 'Serology tests', '2026-03-03', 'Antibody detection tests', 90, 8, 36), + (103, 'PCR diagnostics', '2026-03-03', 'Molecular infection detection', 120, 8, 40), + (104, 'Microbial cultures', '2026-03-04', 'Bacteria growth testing', 100, 8, 40), + (105, 'Antibiotic therapy', '2026-03-04', 'Infection treatment with antibiotics', 200, 1, 40), + (106, 'Brain surgery', '2026-03-05', 'Surgical brain intervention', 8000, 3, 82), + (107, 'Spine surgery', '2026-03-05', 'Surgical spinal operation', 7000, 4, 117), + (108, 'Tumor removal', '2026-03-06', 'Removal of brain tumors', 9000, 3, 82), + (109, 'Shunt procedure', '2026-03-06', 'CSF drainage surgery', 6000, 3, 82), + (110, 'CABG', '2026-03-07', 'Heart bypass surgery', 10000, 2, 74), + (111, 'Valve replacement', '2026-03-07', 'Heart valve surgery', 9500, 2, 75), + (112, 'Pacemaker implantation', '2026-03-08', 'Heart rhythm device installation', 5000, 2, 76), + (113, 'Reconstructive surgery', '2026-03-08', 'Restoration of body structures', 4000, 4, 35), + (114, 'Cosmetic surgery', '2026-03-09', 'Aesthetic body improvement', 3500, 14, 33), + (115, 'Liposuction', '2026-03-09', 'Fat removal procedure', 3000, 14, 66), + (116, 'Rhinoplasty', '2026-03-10', 'Nose reshaping surgery', 3200, 6, 35), + (117, 'Appendectomy', '2026-03-10', 'Appendix removal surgery', 1500, 10, 19), + (118, 'Hernia repair', '2026-03-11', 'Hernia correction surgery', 1800, 10, 90), + (119, 'Gallbladder removal', '2026-03-11', 'Cholecystectomy procedure', 2000, 10, 20), + (120, 'Surgical biopsy', '2026-03-12', 'Tissue sampling surgery', 900, 13, 26), + (121, 'Tooth extraction', '2026-03-12', 'Removal of tooth', 100, 18, 35), + (122, 'Dental filling', '2026-03-13', 'Cavity repair', 120, 18, 35), + (123, 'Dental implants', '2026-03-13', 'Artificial tooth implantation', 1500, 18, 35), + (124, 'Teeth cleaning', '2026-03-14', 'Dental plaque removal', 80, 18, 35); + + +INSERT INTO procedure_results ( + result_id, + result_description, + result_date, + procedure_id +) VALUES + (1, 'Normal lung capacity detected', '2026-01-10', 1), + (2, 'Mild airway obstruction observed', '2026-01-11', 2), + (3, 'Reduced diffusion capacity detected', '2026-01-12', 4), + (4, 'Elevated blood pressure during stress test', '2026-01-13', 11), + (5, 'Normal cardiac rhythm recorded', '2026-01-14', 9), + (6, 'Abnormal EEG activity detected', '2026-01-15', 17), + (7, 'Reduced nerve conduction velocity', '2026-01-16', 21), + (8, 'Successful fracture stabilization', '2026-01-17', 26), + (9, 'Normal cervical screening result', '2026-01-18', 30), + (10, 'Moderate hearing loss detected', '2026-01-19', 37), + (11, 'Cataract successfully removed', '2026-01-20', 46), + (12, 'Elevated glucose levels detected', '2026-01-21', 48), + (13, 'No abnormalities on CT scan', '2026-01-22', 55), + (14, 'Gallstones successfully removed', '2026-01-23', 119), + (15, 'Kidney stones fragmented successfully', '2026-01-24', 68), + (16, 'Tumor biopsy confirmed malignant cells', '2026-01-25', 78), + (17, 'Skin lesion benign after biopsy', '2026-01-26', 83), + (18, 'Patient responded well to physiotherapy', '2026-01-27', 94), + (19, 'Successful pacemaker implantation', '2026-01-28', 112), + (20, 'Dental implant integrated successfully', '2026-01-29', 123); + +INSERT INTO prescriptions (prescription_id, medication_name) VALUES + (1, 'Paracetamol'), + (2, 'Ibuprofen'), + (3, 'Amoxicillin'), + (4, 'Azithromycin'), + (5, 'Ciprofloxacin'), + (6, 'Metformin'), + (7, 'Insulin Glargine'), + (8, 'Atorvastatin'), + (9, 'Simvastatin'), + (10, 'Omeprazole'), + (11, 'Pantoprazole'), + (12, 'Ranitidine'), + (13, 'Lisinopril'), + (14, 'Amlodipine'), + (15, 'Losartan'), + (16, 'Bisoprolol'), + (17, 'Salbutamol'), + (18, 'Fluticasone'), + (19, 'Montelukast'), + (20, 'Cetirizine'), + (21, 'Loratadine'), + (22, 'Dexamethasone'), + (23, 'Prednisone'), + (24, 'Hydrocortisone'), + (25, 'Diclofenac'), + (26, 'Naproxen'), + (27, 'Tramadol'), + (28, 'Morphine'), + (29, 'Codeine'), + (30, 'Diazepam'), + (31, 'Lorazepam'), + (32, 'Alprazolam'), + (33, 'Sertraline'), + (34, 'Fluoxetine'), + (35, 'Citalopram'), + (36, 'Escitalopram'), + (37, 'Quetiapine'), + (38, 'Risperidone'), + (39, 'Haloperidol'), + (40, 'Levodopa'), + (41, 'Carbidopa'), + (42, 'Donepezil'), + (43, 'Memantine'), + (44, 'Gabapentin'), + (45, 'Pregabalin'), + (46, 'Carbamazepine'), + (47, 'Valproic acid'), + (48, 'Lamotrigine'), + (49, 'Clopidogrel'), + (50, 'Aspirin'), + (51, 'Warfarin'), + (52, 'Heparin'), + (53, 'Enoxaparin'), + (54, 'Furosemide'), + (55, 'Spironolactone'), + (56, 'Hydrochlorothiazide'), + (57, 'Digoxin'), + (58, 'Nitroglycerin'), + (59, 'Isosorbide mononitrate'), + (60, 'Sildenafil'), + (61, 'Tamsulosin'), + (62, 'Finasteride'), + (63, 'Dutasteride'), + (64, 'Levothyroxine'), + (65, 'Methimazole'), + (66, 'Propylthiouracil'), + (67, 'Metoclopramide'), + (68, 'Ondansetron'), + (69, 'Loperamide'), + (70, 'Lactulose'), + (71, 'Rifaximin'), + (72, 'Metronidazole'), + (73, 'Vancomycin'), + (74, 'Linezolid'), + (75, 'Doxycycline'), + (76, 'Tetracycline'), + (77, 'Clarithromycin'), + (78, 'Erythromycin'), + (79, 'Nystatin'), + (80, 'Fluconazole'), + (81, 'Ketoconazole'), + (82, 'Aciclovir'), + (83, 'Valaciclovir'), + (84, 'Oseltamivir'), + (85, 'Zanamivir'), + (86, 'Ribavirin'), + (87, 'Interferon alfa'), + (88, 'Methotrexate'), + (89, 'Cyclophosphamide'), + (90, 'Cisplatin'), + (91, 'Doxorubicin'), + (92, 'Imatinib'), + (93, 'Trastuzumab'), + (94, 'Bevacizumab'), + (95, 'Epoetin alfa'), + (96, 'Filgrastim'), + (97, 'Calcium carbonate'), + (98, 'Vitamin D3'), + (99, 'Iron sulfate'), + (100, 'Magnesium sulfate'); + + +INSERT INTO prescription_restriction (restriction_id, description, prescription_id) VALUES + (1, 'Avoid use in patients with severe liver disease', 1), + (2, 'Do not exceed maximum daily dose', 1), + (3, 'Avoid in patients with active stomach ulcer', 2), + (4, 'Use with caution in elderly patients', 2), + (5, 'Contraindicated in penicillin allergy', 3), + (6, 'Avoid in patients with severe kidney impairment', 5), + (7, 'Monitor blood glucose regularly', 6), + (8, 'Risk of hypoglycemia if meals are skipped', 7), + (9, 'Not recommended during pregnancy', 8), + (10, 'Avoid grapefruit juice consumption', 8), + (11, 'May cause stomach irritation', 10), + (12, 'Take before meals for better absorption', 10), + (13, 'Do not stop abruptly without doctor supervision', 14), + (14, 'Monitor blood pressure regularly', 15), + (15, 'May cause dizziness or fatigue', 16), + (16, 'Use with caution in asthma patients', 17), + (17, 'Avoid sudden discontinuation', 18), + (18, 'May cause drowsiness', 19), + (19, 'Do not drive after administration', 30), + (20, 'Avoid alcohol consumption', 30), + (21, 'Risk of dependency with long-term use', 31), + (22, 'Use only for short-term treatment', 31), + (23, 'May increase suicidal thoughts in young patients', 33), + (24, 'Requires regular liver function monitoring', 34), + (25, 'Can cause weight gain', 37), + (26, 'Avoid in Parkinson disease patients', 39), + (27, 'May cause tremors or rigidity worsening', 40), + (28, 'Monitor heart rate regularly', 49), + (29, 'Risk of bleeding increases with NSAIDs', 50), + (30, 'Avoid in pregnancy unless necessary', 51); + + +INSERT INTO lab_tests (test_id, test_name, description, cost) VALUES + (1, 'Blood test', 'General blood analysis', 30), + (2, 'Urine test', 'Urinalysis examination', 25), + (3, 'Hormone test', 'Endocrine hormone evaluation', 80); + + +INSERT INTO lab_results (result_id, results, result_date, test_id) VALUES + (1, 'WBC 6.2 x10^9/L | RBC 4.7 x10^12/L | Hemoglobin 14.1 g/dL | Hematocrit 42% | Platelets 240 x10^9/L | MCV 88 fL | MCH 29 pg | MCHC 33 g/dL | Glucose 92 mg/dL | Sodium 140 mmol/L | Potassium 4.2 mmol/L | Chloride 103 mmol/L | Bicarbonate 24 mmol/L | BUN 14 mg/dL | Creatinine 0.9 mg/dL | Albumin 4.3 g/dL | Total protein 7.1 g/dL | ALT 22 U/L | AST 20 U/L | ALP 85 U/L | Bilirubin 0.8 mg/dL | Total cholesterol 180 mg/dL | LDL 110 mg/dL | HDL 55 mg/dL | Triglycerides 130 mg/dL', '2026-05-10', 1), + (2, 'WBC 7.5 x10^9/L | RBC 4.1 x10^12/L | Hemoglobin 12.3 g/dL | Hematocrit 38% | Platelets 220 x10^9/L | MCV 85 fL | MCH 27 pg | MCHC 32 g/dL | Glucose 105 mg/dL | Sodium 138 mmol/L | Potassium 4.5 mmol/L | Chloride 101 mmol/L | Bicarbonate 23 mmol/L | BUN 18 mg/dL | Creatinine 1.1 mg/dL | Albumin 4.0 g/dL | ALT 30 U/L | AST 28 U/L | Total cholesterol 210 mg/dL | LDL 140 mg/dL | HDL 42 mg/dL | Triglycerides 180 mg/dL', '2026-05-09', 1), + (3, 'WBC 5.8 x10^9/L | RBC 5.0 x10^12/L | Hemoglobin 15.0 g/dL | Hematocrit 45% | Platelets 260 x10^9/L | MCV 90 fL | MCH 30 pg | MCHC 34 g/dL | Glucose 88 mg/dL | Sodium 141 mmol/L | Potassium 4.0 mmol/L | Chloride 102 mmol/L | Bicarbonate 25 mmol/L | BUN 12 mg/dL | Creatinine 0.8 mg/dL | Albumin 4.5 g/dL | ALT 18 U/L | AST 19 U/L | Total cholesterol 170 mg/dL | LDL 100 mg/dL | HDL 60 mg/dL | Triglycerides 110 mg/dL', '2026-05-08', 1), + (11, 'TSH 2.1 mIU/L | Free T4 1.2 ng/dL | Free T3 3.2 pg/mL | Testosterone 520 ng/dL | Estradiol 35 pg/mL | Progesterone 1.2 ng/mL | FSH 6 IU/L | LH 5 IU/L | SHBG 40 nmol/L | Cortisol 14 µg/dL | DHEA-S 280 µg/dL | Insulin 8 µIU/mL | Prolactin 12 ng/mL | AMH 3.1 ng/mL', '2026-05-10', 3), + (12, 'TSH 6.5 mIU/L | Free T4 0.7 ng/dL | Free T3 2.0 pg/mL | Testosterone 380 ng/dL | Estradiol 20 pg/mL | Progesterone 0.8 ng/mL | FSH 9 IU/L | LH 7 IU/L | SHBG 55 nmol/L | Cortisol 10 µg/dL | DHEA-S 180 µg/dL | Insulin 14 µIU/mL | Prolactin 18 ng/mL | AMH 1.8 ng/mL', '2026-05-09', 3), + (13, 'TSH 1.8 mIU/L | Free T4 1.3 ng/dL | Free T3 3.5 pg/mL | Testosterone 610 ng/dL | Estradiol 40 pg/mL | Progesterone 1.5 ng/mL | FSH 5 IU/L | LH 4 IU/L | SHBG 38 nmol/L | Cortisol 22 µg/dL | DHEA-S 320 µg/dL | Insulin 6 µIU/mL | Prolactin 10 ng/mL | AMH 4.0 ng/mL', '2026-05-08', 3), + (6, 'pH 6.0 | Specific gravity 1.020 | Protein negative | Glucose negative | Ketones negative | RBC none | WBC 0-2/hpf | Bacteria none', '2026-05-10', 2), + (7, 'pH 5.5 | Specific gravity 1.030 | Trace protein | Glucose negative | Ketones negative | WBC 5-10/hpf | Mild bacteriuria', '2026-05-09', 2), + (8, 'pH 6.2 | Specific gravity 1.015 | Protein negative | Glucose negative | Ketones negative | Normal sediment', '2026-05-08', 2), + (9, 'pH 5.8 | Specific gravity 1.025 | Leukocytes ++ | Nitrites positive | Bacteria present | UTI suspected', '2026-05-07', 2), + (10, 'pH 6.0 | Specific gravity 1.018 | RBC trace | Protein negative | Glucose negative | Further evaluation required', '2026-05-06', 2); + + +INSERT INTO medical_records (record_id, patient_id) VALUES + (1, 1), + (2, 2), + (3, 3), + (4, 4), + (5, 5), + (6, 6), + (7, 7), + (8, 8), + (9, 9), + (10, 10), + (11, 11), + (12, 12), + (13, 13), + (14, 14), + (15, 15); + + +INSERT INTO referrals (referral_id, reason, referral_date, record_id, from_doctor_id, to_doctor_id) VALUES + (1, 'Suspected cardiac condition requiring specialist evaluation', '2026-05-10', 3, 1, 5), + (2, 'Neurological symptoms and recurrent headaches', '2026-05-09', 6, 2, 8), + (3, 'Persistent respiratory issues for pulmonology review', '2026-05-08', 1, 3, 6), + (4, 'Orthopedic follow-up for post-surgery assessment', '2026-05-07', 4, 4, 7), + (5, 'Suspected endocrine disorder requiring hormone testing', '2026-05-06', 10, 5, 9), + (6, 'Dermatological evaluation for chronic skin rash', '2026-05-05', 8, 6, 10), + (7, 'Pediatric developmental concern check', '2026-05-04', 7, 7, 11), + (8, 'Gastrointestinal pain requiring endoscopy', '2026-05-03', 9, 8, 12), + (9, 'Abnormal lab results requiring hematology review', '2026-05-02', 10, 9, 13), + (10, 'Possible kidney dysfunction requiring nephrology assessment', '2026-05-01', 10, 10, 14); + + +INSERT INTO medical_report (report_id, description, report_date, record_id, doctor_id) VALUES + (1, 'Pulmonary function assessment confirms mild asthma. Recommended inhaler therapy.', '2026-05-10', 1, 3), + (2, 'Cardiology report indicates elevated blood pressure and possible hypertension.', '2026-05-09', 2, 1), + (3, 'Neurological evaluation shows no acute abnormalities. Follow-up recommended.', '2026-05-08', 6, 2), + (4, 'Orthopedic post-surgery report shows stable recovery and good mobility.', '2026-05-07', 4, 4), + (5, 'Gynecological report: normal ultrasound findings, no pathological changes.', '2026-05-06', 5, 5), + (6, 'Dermatology report confirms allergic dermatitis. Topical treatment prescribed.', '2026-05-05', 8, 6), + (7, 'Pediatric assessment: normal growth and development indicators.', '2026-05-04', 7, 7), + (8, 'Gastroenterology report suggests possible gastritis. Further endoscopy advised.', '2026-05-03', 9, 8), + (9, 'Laboratory review indicates mild iron deficiency anemia.', '2026-05-02', 10, 9), + (10, 'Nephrology report suggests early signs of reduced kidney function.', '2026-05-01', 10, 10); + + +INSERT INTO billing (bill_id, total_cost, payment_status, payment_date, record_id, admin_id) VALUES + (1, 230, 'PAID', '2026-05-11', 1, 1), + (2, 340, 'PENDING', NULL, 2, 1), + (3, 900, 'PAID', '2026-05-10', 3, 2), + (4, 5200, 'PAID', '2026-05-09', 4, 1), + (5, 180, 'PAID', '2026-05-08', 5, 3), + (6, 420, 'PENDING', NULL, 6, 2), + (7, 160, 'PAID', '2026-05-07', 7, 3), + (8, 780, 'PENDING', NULL, 8, 1), + (9, 250, 'PAID', '2026-05-06', 9, 2), + (10, 610, 'PENDING', NULL, 10, 3); + + +INSERT INTO patient_allergies (patient_id, allergy_id) VALUES +-- Patient 1 allergies +(1, 1), -- Penicillin +(1, 3), -- Dust +-- Patient 2 allergies +(2, 2), -- Peanuts +(2, 5), -- Pollen +-- Patient 3 allergies +(3, 4), -- Lactose +-- Patient 4 allergies +(4, 1), -- Penicillin +(4, 6), -- Bee sting +-- Patient 5 allergies +(5, 3), -- Dust +(5, 2), -- Peanuts +-- Patient 6 allergies +(6, 7), -- Shellfish +-- Patient 7 allergies +(7, 5), -- Pollen +(7, 8), -- Latex +-- Patient 8 allergies +(8, 4), -- Lactose +(8, 1), -- Penicillin +-- Patient 9 allergies +(9, 6), -- Bee sting +-- Patient 10 allergies +(10, 2); -- Peanuts + + +INSERT INTO allergy_prescription_restrictions (allergy_id, restriction_id) VALUES +-- Penicillin allergy restrictions +(1, 1), -- avoid Amoxicillin-based drugs +(1, 2), -- avoid Ampicillin-based drugs +-- Peanuts allergy restrictions +(2, 3), -- avoid capsule-based allergens +(2, 4), -- avoid certain oral suspensions with traces +-- Dust allergy restrictions +(3, 5), -- avoid inhaled powder medications +-- Lactose allergy restrictions +(4, 6), -- avoid lactose-containing tablets +-- Pollen allergy restrictions +(5, 5), -- avoid inhalation-based medications +-- Bee sting allergy restrictions +(6, 7), -- avoid adrenaline-triggering compounds unless emergency +(6, 8), -- caution with immunotherapy drugs +-- Shellfish allergy restrictions +(7, 3), -- capsule-based restrictions (gelatin overlap) +(7, 9), -- contrast media precaution +-- Latex allergy restrictions +(8, 10), -- avoid latex-containing medical devices +-- Mixed severe allergies +(1, 6), +(2, 6), +(3, 10); + + +INSERT INTO patient_symptoms (patient_id, symptom_id) VALUES +-- Patient 1 +(1, 1), -- Fever +(1, 2), -- Cough +(1, 5), -- Fatigue +-- Patient 2 +(2, 3), -- Headache +(2, 4), -- Sore throat +-- Patient 3 +(3, 6), -- Shortness of breath +(3, 2), -- Cough +-- Patient 4 +(4, 7), -- Chest pain +(4, 5), -- Fatigue +-- Patient 5 +(5, 8), -- Nausea +(5, 9), -- Dizziness +-- Patient 6 +(6, 1), -- Fever +(6, 6), -- Shortness of breath +-- Patient 7 +(7, 10), -- Joint pain +(7, 5), -- Fatigue +-- Patient 8 +(8, 3), -- Headache +(8, 9), -- Dizziness +-- Patient 9 +(9, 4), -- Sore throat +(9, 2), -- Cough +-- Patient 10 +(10, 11), -- Skin rash +(10, 5); -- Fatigue + +INSERT INTO diagnosis_symptoms (diagnosis_id, symptom_id) VALUES + (1,1), + (1,2), + (1,5), + (2,3), + (2,4), + (3,6), + (3,2), + (4,7), + (4,5), + (5,8), + (5,9), + (6,1), + (6,6), + (7,10), + (7,5), + (8,3), + (8,9), + (9,4), + (9,2), + (10,11), + (10,5); + +INSERT INTO specialization_procedures (specialization_id, procedure_id) VALUES + (1,1), + (1,2), + (1,3), + (2,4), + (2,5), + (2,6), + (3,7), + (3,8), + (3,9), + (4,10), + (4,11), + (4,12), + (5,13), + (5,14), + (5,15), + (6,16), + (6,17), + (6,18), + (7,19), + (7,20); + +INSERT INTO diagnosis_procedures (diagnosis_id, procedure_id) VALUES + (1,1), + (1,2), + (2,3), + (2,4), + (3,5), + (3,6), + (4,7), + (4,8), + (5,9), + (5,10), + (6,11), + (6,12), + (7,13), + (7,14), + (8,15), + (8,16), + (9,17), + (9,18), + (10,19), + (10,20); + +INSERT INTO department_procedures (department_id, procedure_id) VALUES + (1,1), + (1,2), + (1,3), + (2,4), + (2,5), + (2,6), + (3,7), + (3,8), + (3,9), + (4,10), + (4,11), + (4,12), + (5,13), + (5,14), + (5,15), + (6,16), + (6,17), + (6,18), + (7,19), + (7,20); + +INSERT INTO billing_procedures (bill_id, procedure_id) VALUES + (1,1), + (1,2), + (1,3), + (2,4), + (2,5), + (2,6), + (3,7), + (3,8), + (3,9), + (4,10), + (4,11), + (4,12), + (5,13), + (5,14), + (5,15), + (6,16), + (6,17), + (6,18), + (7,19), + (7,20); + +INSERT INTO billing_lab_tests (bill_id, test_id) VALUES + (1,1), + (1,2), + (1,3), + (2,1), + (2,2), + (2,3), + (3,1), + (3,2), + (4,2), + (4,3), + (5,1), + (5,3), + (6,2), + (6,3), + (7,1), + (7,2), + (8,3), + (9,1), + (10,2); + +INSERT INTO doctor_medical_records (doctor_id, record_id) VALUES + (1,1), + (1,2), + (1,3), + (2,4), + (2,5), + (2,6), + (3,7), + (3,8), + (3,9), + (4,10), + (4,11), + (5,12), + (5,13), + (6,14), + (6,15); + +INSERT INTO diagnosis_medical_records (diagnosis_id, record_id) VALUES + (1,1), + (2,2), + (3,3), + (4,4), + (5,5), + (6,6), + (7,7), + (8,8), + (9,9), + (10,10), + (1,11), + (2,12), + (3,13), + (4,14), + (5,15); + + +INSERT INTO prescription_medical_records (prescription_id, record_id, dosage, frequency, duration, notes) VALUES + (1,1,'500mg','Twice daily','7 days','Take after meals'), + (2,2,'250mg','Once daily','5 days','Mild infection treatment'), + (3,3,'10mg','Once daily','10 days','Monitor blood pressure'), + (4,4,'20mg','Once daily','14 days','Use in morning'), + (5,5,'5mg','Twice daily','7 days','Short term therapy'), + (6,6,'1 tablet','Once daily','30 days','Chronic management'), + (7,7,'2 puffs','As needed','14 days','Respiratory relief'), + (8,8,'50mg','Once daily','5 days','Anti-inflammatory'), + (9,9,'100mg','Twice daily','10 days','Pain management'), + (10,10,'1 dose','Once daily','3 days','Acute condition'), + (1,11,'500mg','Twice daily','7 days','Repeat treatment'), + (2,12,'250mg','Once daily','5 days','Follow-up course'), + (3,13,'10mg','Once daily','10 days','Stable condition'), + (4,14,'20mg','Once daily','14 days','Adjusted dosage'), + (5,15,'5mg','Twice daily','7 days','Monitor response'); + + +INSERT INTO medical_report_lab_results (report_id, result_id) VALUES + (1,1), + (1,2), + (1,3), + (2,1), + (2,2), + (3,3), + (3,1), + (4,2), + (4,3), + (5,1), + (6,2), + (7,3), + (8,1), + (9,2), + (10,3); + +INSERT INTO performed_procedures (performed_id, procedure_id, doctor_id, patient_id, diagnosis_id, procedure_date, notes) VALUES + (1,1,1,1,1,'2026-05-01','Routine procedure completed successfully'), + (2,2,1,2,2,'2026-05-02','No complications'), + (3,3,2,3,3,'2026-05-03','Patient stable during procedure'), + (4,4,2,4,4,'2026-05-04','Follow-up recommended'), + (5,5,3,5,5,'2026-05-05','Procedure successful'), + (6,6,3,6,6,'2026-05-06','Mild post-op observation'), + (7,7,4,7,7,'2026-05-07','No issues detected'), + (8,8,4,8,8,'2026-05-08','Standard recovery'), + (9,9,5,9,9,'2026-05-09','Patient responded well'), + (10,10,5,10,10,'2026-05-10','Stable condition'), + (11,11,6,1,1,'2026-05-11','Additional procedure done'), + (12,12,6,2,2,'2026-05-12','Routine check'), + (13,13,7,3,3,'2026-05-13','No complications'), + (14,14,7,4,4,'2026-05-14','Observation required'), + (15,15,8,5,5,'2026-05-15','Procedure completed'), + (16,16,8,6,6,'2026-05-16','Patient recovered well'), + (17,17,9,7,7,'2026-05-17','Stable vitals'), + (18,18,9,8,8,'2026-05-18','Minor discomfort reported'), + (19,19,10,9,9,'2026-05-19','All normal'), + (20,20,10,10,10,'2026-05-20','Discharged after procedure'); + +INSERT INTO performed_lab_tests (performed_test_id, test_id, patient_id, doctor_id, technician_id, test_date, notes) VALUES + (1,1,1,1,1,'2026-05-01','Blood test routine check'), + (2,2,2,2,1,'2026-05-02','Urine infection screening'), + (3,3,3,3,2,'2026-05-03','Hormone imbalance check'), + (4,1,4,1,2,'2026-05-04','CBC follow-up'), + (5,2,5,2,3,'2026-05-05','Urine analysis repeat'), + (6,3,6,3,3,'2026-05-06','Hormone panel evaluation'), + (7,1,7,4,1,'2026-05-07','General blood screening'), + (8,2,8,5,2,'2026-05-08','UTI suspicion test'), + (9,3,9,6,3,'2026-05-09','Endocrine evaluation'), + (10,1,10,7,1,'2026-05-10','Blood count check'), + (11,2,1,8,2,'2026-05-11','Kidney/urine check'), + (12,3,2,9,3,'2026-05-12','Thyroid hormone test'), + (13,1,3,10,1,'2026-05-13','Routine blood panel'), + (14,2,4,1,2,'2026-05-14','Urine follow-up'), + (15,3,5,2,3,'2026-05-15','Hormone monitoring'), + (16,1,6,3,1,'2026-05-16','CBC repeat test'), + (17,2,7,4,2,'2026-05-17','Urine culture'), + (18,3,8,5,3,'2026-05-18','Hormone levels check'), + (19,1,9,6,1,'2026-05-19','Inflammation blood test'), + (20,2,10,7,2,'2026-05-20','Routine urinalysis'); + +INSERT INTO medical_record_lab_results (record_id, result_id) VALUES + (1,1), + (1,2), + (1,3), + (2,1), + (2,2), + (2,3), + (3,1), + (3,2), + (4,1), + (4,2), + (5,3), + (6,1), + (7,2), + (8,3), + (9,1), + (10,2); + +INSERT INTO medical_record_procedures (record_id, procedure_id) VALUES + (1,1), + (1,2), + (1,3), + (2,4), + (2,5), + (2,6), + (3,7), + (3,8), + (3,9), + (4,10), + (4,11), + (4,12), + (5,13), + (5,14), + (5,15), + (6,16), + (6,17), + (6,18), + (7,19), + (7,20), + (8,1), + (8,2), + (9,3), + (9,4), + (10,5), + (10,6); + +INSERT INTO medical_record_symptoms (record_id, symptom_id, severity) VALUES + (1,1,'HIGH'), + (1,2,'MEDIUM'), + (1,5,'LOW'), + (2,3,'MEDIUM'), + (2,4,'LOW'), + (3,6,'HIGH'), + (3,2,'MEDIUM'), + (4,7,'HIGH'), + (4,5,'MEDIUM'), + (5,8,'LOW'), + (5,9,'MEDIUM'), + (6,1,'HIGH'), + (6,6,'HIGH'), + (7,10,'MEDIUM'), + (7,5,'LOW'), + (8,3,'MEDIUM'), + (8,9,'LOW'), + (9,4,'MEDIUM'), + (9,2,'LOW'), + (10,11,'LOW'), + (10,5,'MEDIUM'); + +INSERT INTO medical_record_allergies (record_id, allergy_id, reaction, severity) VALUES + (1,1,'Rash','HIGH'), + (1,2,'Swelling','MEDIUM'), + (2,3,'Sneezing','LOW'), + (2,4,'Skin irritation','MEDIUM'), + (3,5,'Coughing','MEDIUM'), + (3,6,'Breathing difficulty','HIGH'), + (4,7,'Nausea','LOW'), + (4,8,'Itching','MEDIUM'), + (5,1,'Hives','HIGH'), + (5,3,'Mild reaction','LOW'), + (6,2,'Swelling','HIGH'), + (6,4,'Skin redness','MEDIUM'), + (7,5,'Respiratory reaction','HIGH'), + (7,6,'Fatigue','MEDIUM'), + (8,7,'Sneezing','LOW'), + (8,8,'Irritation','MEDIUM'), + (9,1,'Rash','MEDIUM'), + (9,2,'Swelling','HIGH'), + (10,3,'Mild itching','LOW'), + (10,4,'Skin reaction','MEDIUM'); + +COMMIT; +