= Data Requirements  =

== ER Diagram
----

== Entities ==

=== Doctor_Level ===
* level_id (PK, bigint)
* level (text)

=== Doctor_Specialization ===
* specialization_id (PK, bigint)
* specialization_name (text)

=== Departments ===
* department_id (PK, bigint)
* department_name (text)

=== Doctors ===
* doctor_id (PK, bigint)
* first_name (text)
* last_name (text)
* email_address (text)
* level_id (FK, bigint)
* specialization_id (FK, bigint)
* department_id (FK, bigint)

=== Patients ===
* patient_id (PK, bigint)
* first_name (text)
* last_name (text)
* email_address (text)
* date_of_birth (date)
* blood_type (text)
* gender (text)
* phone_number (text)
* embg (text)

=== Admin ===
* admin_id (PK, bigint)
* username (text)
* name (text)
* lastname (text)
* email (text)

=== Lab_Technician ===
* technician_id (PK, bigint)
* username (text)
* name (text)
* lastname (text)
* email (text)

=== Users ===
* user_id (PK, serial)
* username (text)
* password (text)
* role (text)
* first_name (text)
* last_name (text)
* patient_id (FK, bigint, nullable)
* doctor_id (FK, bigint, nullable)
* admin_id (FK, bigint, nullable)
* technician_id (FK, bigint, nullable)
* is_active (boolean)

=== Appointments ===
* appointment_id (PK, bigint)
* appointment_date (date)
* appointment_time (time)
* status (text)
* patient_id (FK, bigint)
* doctor_id (FK, bigint)

=== Diagnosis ===
* diagnosis_id (PK, bigint)
* name (text)
* description (text)
* patient_id (FK, bigint)
* doctor_id (FK, bigint)

=== Procedures ===
* procedure_id (PK, bigint)
* procedure_type (text)
* procedure_date (date)
* description (text)
* cost (decimal)
* doctor_id (FK, bigint)
* diagnosis_id (FK, bigint)

=== Procedure_Results ===
* result_id (PK, bigint)
* result_description (text)
* result_date (date)
* procedure_id (FK, bigint)

=== Prescriptions ===
* prescription_id (PK, bigint)
* medication_name (text)

=== Prescription_Restriction ===
* restriction_id (PK, bigint)
* description (text)
* prescription_id (FK, bigint)

=== Lab_Tests ===
* test_id (PK, bigint)
* test_name (text)
* description (text)
* cost (decimal)

=== Lab_Results ===
* result_id (PK, bigint)
* results (text)
* result_date (date)
* test_id (FK, bigint)

=== Medical_Records ===
''(simplified)''
* record_id (PK, bigint)
* patient_id (FK, bigint)

=== Allergies ===
* allergy_id (PK, bigint)
* name (text)
* allergy_severity (text)

=== Symptoms ===
* symptom_id (PK, bigint)
* name (text)
* description (text)

=== Referrals ===
* referral_id (PK, bigint)
* reason (text)
* referral_date (date)
* record_id (FK, bigint)
* from_doctor_id (FK, bigint)
* to_doctor_id (FK, bigint)

=== Medical_Report ===
* report_id (PK, bigint)
* description (text)
* report_date (date)
* record_id (FK, bigint)
* doctor_id (FK, bigint)

=== Billing ===
* bill_id (PK, bigint)
* total_cost (decimal)
* payment_status (text)
* payment_date (date)
* record_id (FK, bigint)
* admin_id (FK, bigint)

=== Performed_Procedures ===
* performed_id (PK, bigint)
* procedure_id (FK, bigint)
* doctor_id (FK, bigint)
* patient_id (FK, bigint)
* diagnosis_id (FK, bigint, nullable)
* procedure_date (date)
* notes (text)

=== Performed_Lab_Tests ===

* performed_test_id (PK, bigint)
* test_id (FK, bigint)
* patient_id (FK, bigint)
* doctor_id (FK, bigint)
* technician_id (FK, bigint)
* test_date (date)
* notes (text)

=== Associative entities (M:N junctions that carry their own attributes) ===

==== Prescription_Medical_Records ====
* prescription_id (FK, bigint)
* record_id (FK, bigint)
* dosage (text)
* frequency (text)
* duration (text)
* notes (text)

==== Medical_Record_Symptoms ====
* record_id (FK, bigint)
* symptom_id (FK, bigint)
* severity (text)

==== Medical_Record_Allergies ====
* record_id (FK, bigint)
* allergy_id (FK, bigint)
* reaction (text)
* severity (text)

----

== Relations ==

=== is (Doctor_Level ↔ Doctors, 1:N) ===
each doctor has one level, a level applies to many doctors.

=== has_specialization (Doctor_Specialization ↔ Doctors, 1:N) ===
each doctor has one specialization, a specialization applies to many doctors.

=== works_in (Departments ↔ Doctors, 1:N) ===
each doctor belongs to one department, a department employs many doctors.

=== identifies (Users ↔ Patients / Doctors / Admin / Lab_Technician, 1:1) ===
a user account links to exactly one underlying person record, depending on role.

=== has_allergies (Patients ↔ Allergies, M:N via Patient_Allergies) ===
patients can have multiple allergies, an allergy can affect many patients.

=== affects_prescription (Allergies ↔ Prescription_Restriction, M:N via Allergy_Prescription_Restrictions) ===
an allergy can be tied to multiple restrictions; a restriction can apply to multiple allergies.

=== has_symptoms (Patients ↔ Symptoms, M:N via Patient_Symptoms) ===
patients can report multiple symptoms, a symptom can be reported by many patients.

=== indicates (Symptoms ↔ Diagnosis, M:N via Diagnosis_Symptoms) ===
a diagnosis can involve multiple symptoms, a symptom can appear in multiple diagnoses.

=== books (Patients ↔ Appointments, 1:N) ===
a patient books many appointments, each appointment belongs to one patient.

=== attends (Doctors ↔ Appointments, 1:N) ===
a doctor attends many appointments, each appointment is handled by one doctor.

=== diagnoses (Doctors ↔ Diagnosis, 1:N) ===
a doctor makes many diagnoses, each diagnosis is made by one doctor.

=== diagnosed_with (Patients ↔ Diagnosis, 1:N) ===
a patient can have many diagnoses; each diagnosis belongs to one patient.

=== performs_allowed (Doctor_Specialization ↔ Procedures, M:N via Specialization_Procedures) ===
a specialization is linked to the procedures it's allowed to perform.

=== requires (Diagnosis ↔ Procedures, M:N via Diagnosis_Procedures) ===
a diagnosis can require multiple procedures, and the same procedure can be linked to multiple diagnoses ,in addition to the direct diagnosis_id FK each procedure row already carries.

=== produces (Procedures ↔ Procedure_Results, 1:N) ===
a procedure can produce multiple results, each result belongs to one procedure.

=== performs (Departments ↔ Procedures, M:N via Department_Procedures) ===
a department can perform multiple procedures, and a procedure can be performed in multiple departments.

=== billed_for (Procedures ↔ Billing, M:N via Billing_Procedures) ===
a bill can include multiple procedures, a procedure can appear on multiple bills.

=== restricts (Prescriptions ↔ Prescription_Restriction, 1:N) ===
a prescription can have multiple restrictions, each restriction belongs to one prescription.

=== tested_as (Lab_Tests ↔ Performed_Lab_Tests, 1:N) ===
a catalog test can be performed many times; each performed test refers to one catalog test.

=== takes_test (Patients ↔ Performed_Lab_Tests, 1:N) ===
a patient can undergo many performed tests, each performed test belongs to one patient.

=== orders_test (Doctors ↔ Performed_Lab_Tests, 1:N) ===
a doctor can order many tests, each performed test is ordered by one doctor.

=== conducts (Lab_Technician ↔ Performed_Lab_Tests, 1:N) ===
a technician can conduct many tests, each performed test is run by one technician.

=== produces_test (Lab_Tests ↔ Lab_Results, 1:N) ===
a catalog test can produce multiple results, each result belongs to one test.

=== billed_for_test (Lab_Tests ↔ Billing, M:N via Billing_Lab_Tests) ===
a bill can include multiple tests, a test can appear on multiple bills.

=== performed_as (Procedures ↔ Performed_Procedures, 1:N) ===
a catalog procedure can be performed many times.

=== performed_on (Patients ↔ Performed_Procedures, 1:N) ===
a patient can have many procedures performed, each performed procedure belongs to one patient.

=== performed_by (Doctors ↔ Performed_Procedures, 1:N) ===
a doctor can perform many procedures, each performed procedure has one doctor.

=== has (Patients ↔ Medical_Records, 1:N as constrained) ===
each medical record belongs to one patient.

=== updates (Doctors ↔ Medical_Records, M:N via Doctor_Medical_Records) ===
doctors can update multiple records; a record can be updated by multiple doctors.

=== relates_to (Diagnosis ↔ Medical_Records, M:N via Diagnosis_Medical_Records) ===
diagnoses can relate to multiple records, a record can include multiple diagnoses.

=== linked_to (Prescriptions ↔ Medical_Records, M:N via Prescription_Medical_Records) ===
prescriptions can be linked to multiple records, a record can include multiple prescriptions, each with its own dosage/frequency/duration/notes for that record.

=== contains_symptoms (Medical_Records ↔ Symptoms, M:N via Medical_Record_Symptoms) ===
a record can list multiple symptoms, each with its own severity.

=== contains_allergies (Medical_Records ↔ Allergies, M:N via Medical_Record_Allergies) ===
a record can list multiple allergies, each with its own reaction and severity.

=== includes_procedures (Medical_Records ↔ Procedures, M:N via Medical_Record_Procedures) ===
a record can reference multiple procedures.

=== includes_lab_results (Medical_Records ↔ Lab_Results, M:N via Medical_Record_Lab_Results) ===
a record can reference multiple lab results.

=== generates_referral (Medical_Records ↔ Referrals, 1:N) ===
a record can generate multiple referrals, each referral is based on one record.

=== issues (Doctors ↔ Referrals as from_doctor_id, 1:N) ===
a doctor can issue many referrals.

=== receives_referral (Doctors ↔ Referrals as to_doctor_id, 1:N) ===
a doctor can receive many referrals.

=== creates (Doctors ↔ Medical_Report, 1:N) ===
a doctor can create multiple reports, each report is created by one doctor.

=== generates_report (Medical_Records ↔ Medical_Report, 1:N) ===
a record can generate multiple reports, each report is based on one record.

=== includes_lab_results_report (Medical_Report ↔ Lab_Results, M:N via Medical_Report_Lab_Results) ===
a report can include multiple lab results, a result can appear in multiple reports.

=== processes (Admin ↔ Billing, 1:N) ===
an admin can process multiple bills, each bill is processed by one admin.

=== generates_bill (Medical_Records ↔ Billing, 1:N) ===
a record can generate multiple bills, each bill is tied to one record.