DatabaseCreation: MedicalSystem Views.txt

MedicalSystem Views

CREATE OR REPLACE VIEW v_patient_full_profile AS
SELECT
    p.patient_id,
    p.first_name || ' ' || p.last_name                      AS full_name,
    p.date_of_birth,
    DATE_PART('year', AGE(p.date_of_birth))::INT            AS age,
    p.gender,
    p.phone,
    p.email,
    p.address,
    la.appointment_date                                      AS last_appointment_date,
    la.status                                                AS last_appointment_status,
    la.appointment_type,
    d.first_name || ' ' || d.last_name                      AS last_doctor,
    sp.spec_name                                             AS specialization,
    pd.diagnosis_name                                        AS active_primary_diagnosis,
    icd.code                                                 AS icd_code,
    pd.date_from                                             AS diagnosis_since,
    (SELECT COUNT(*) FROM Prescription pr
     WHERE pr.patient_id = p.patient_id)                    AS total_prescriptions,
    (SELECT COUNT(*) FROM Appointment a2
     WHERE a2.patient_id = p.patient_id)                    AS total_appointments
FROM Patient p
LEFT JOIN LATERAL (
    SELECT * FROM Appointment a
    WHERE a.patient_id = p.patient_id
    ORDER BY a.appointment_date DESC
    LIMIT 1
) la ON TRUE
LEFT JOIN Doctor d       ON d.doctor_id  = la.doctor_id
LEFT JOIN Specialization sp ON sp.spec_id = d.spec_id
LEFT JOIN LATERAL (
    SELECT pd2.*
    FROM Patient_diagnosis pd2
    WHERE pd2.patient_id = p.patient_id
      AND pd2.is_primary = TRUE
      AND (pd2.date_to IS NULL OR pd2.date_to >= CURRENT_DATE)
    ORDER BY pd2.date_from DESC
    LIMIT 1
) pd ON TRUE
LEFT JOIN ICD icd ON icd.icd_id = pd.icd_id;

CREATE OR REPLACE VIEW v_doctor_workload AS
SELECT
    d.doctor_id,
    d.first_name || ' ' || d.last_name                      AS full_name,
    d.email,
    sp.spec_name                                             AS specialization,
    dep.department_name,
    dd.employment_type,
    COUNT(a.appointment_id)                                  AS total_appointments,
    COUNT(CASE WHEN a.status = 'COMPLETED'  THEN 1 END)     AS completed,
    COUNT(CASE WHEN a.status = 'CANCELLED'  THEN 1 END)     AS cancelled,
    COUNT(CASE WHEN a.status = 'NO_SHOW'    THEN 1 END)     AS no_shows,
    COUNT(CASE WHEN a.status = 'SCHEDULED'
               AND a.appointment_date >= CURRENT_DATE
               THEN 1 END)                                   AS upcoming,
    ROUND(
        100.0 * COUNT(CASE WHEN a.status = 'COMPLETED' THEN 1 END)
        / NULLIF(COUNT(a.appointment_id), 0), 1
    )                                                        AS completion_rate_pct,
    COUNT(DISTINCT a.patient_id)                             AS unique_patients,
    COUNT(DISTINCT pr.presc_id)                              AS prescriptions_issued,
    COUNT(DISTINCT me.exam_id)                               AS exams_conducted
FROM Doctor d
LEFT JOIN Specialization sp ON sp.spec_id = d.spec_id
LEFT JOIN LATERAL (
    SELECT doc_dep.employment_type, doc_dep.department_id
    FROM Doctor_department doc_dep
    WHERE doc_dep.doctor_id = d.doctor_id
      AND doc_dep.date_to IS NULL
    ORDER BY doc_dep.date_from DESC
    LIMIT 1
) dd ON TRUE
LEFT JOIN Department dep     ON dep.department_id  = dd.department_id
LEFT JOIN Appointment a      ON a.doctor_id        = d.doctor_id
LEFT JOIN Prescription pr    ON pr.doctor_id       = d.doctor_id
LEFT JOIN Medical_examination me ON me.doctor_id   = d.doctor_id
GROUP BY d.doctor_id, d.first_name, d.last_name, d.email,
         sp.spec_name, dep.department_name, dd.employment_type;

CREATE OR REPLACE VIEW v_pharmacy_inventory_sales AS
SELECT
    ph.pharmacy_id,
    ph.name                                                  AS pharmacy_name,
    ph.address,
    COUNT(DISTINCT i.inventory_id)                           AS products_stocked,
    SUM(i.quantity)                                          AS total_units_in_stock,
    COUNT(CASE WHEN i.quantity < 20 THEN 1 END)             AS low_stock_alerts,
    COUNT(DISTINCT ps.sale_id)                               AS total_sales,
    COUNT(DISTINCT ps.patient_id)                            AS unique_customers,
    COALESCE(SUM(ps.total_amount), 0)                        AS total_revenue,
    ROUND(COALESCE(AVG(ps.total_amount), 0)::NUMERIC, 2)    AS avg_sale_value,
    COUNT(CASE WHEN ps.sale_date >= CURRENT_DATE - INTERVAL '30 days'
               THEN 1 END)                                   AS sales_last_30_days,
    COALESCE(SUM(CASE WHEN ps.sale_date >= CURRENT_DATE - INTERVAL '30 days'
                      THEN ps.total_amount END), 0)          AS revenue_last_30_days,
    MAX(ps.sale_date)                                        AS last_sale_date
FROM Pharmacy ph
LEFT JOIN Inventory i      ON i.pharmacy_id  = ph.pharmacy_id
LEFT JOIN Pharmacy_sale ps ON ps.pharmacy_id = ph.pharmacy_id
GROUP BY ph.pharmacy_id, ph.name, ph.address;

CREATE OR REPLACE VIEW v_appointment_daily_stats AS
SELECT
    a.appointment_date,
    COUNT(*)                                                 AS total_appointments,
    COUNT(CASE WHEN a.status = 'COMPLETED'   THEN 1 END)    AS completed,
    COUNT(CASE WHEN a.status = 'CANCELLED'   THEN 1 END)    AS cancelled,
    COUNT(CASE WHEN a.status = 'NO_SHOW'     THEN 1 END)    AS no_shows,
    COUNT(CASE WHEN a.status = 'SCHEDULED'   THEN 1 END)    AS scheduled,
    COUNT(CASE WHEN a.status = 'IN_PROGRESS' THEN 1 END)    AS in_progress,
    COUNT(CASE WHEN a.appointment_type = 'EMERGENCY' THEN 1 END) AS emergencies,
    COUNT(CASE WHEN a.priority_level = 'URGENT' THEN 1 END) AS urgent,
    COUNT(DISTINCT a.doctor_id)                              AS doctors_active,
    COUNT(DISTINCT a.patient_id)                             AS unique_patients,
    COUNT(CASE WHEN a.prescription_value = TRUE THEN 1 END) AS with_prescription,
    ROUND(
        100.0 * COUNT(CASE WHEN a.status = 'COMPLETED' THEN 1 END)
        / NULLIF(COUNT(*), 0), 1
    )                                                        AS completion_rate_pct
FROM Appointment a
GROUP BY a.appointment_date;

CREATE OR REPLACE VIEW v_patient_diagnosis_history AS
SELECT
    pd.patient_diagnosis_id,
    pd.patient_id,
    p.first_name || ' ' || p.last_name                      AS patient_name,
    d.first_name || ' ' || d.last_name                      AS diagnosing_doctor,
    sp.spec_name                                             AS doctor_specialization,
    icd.code                                                 AS icd_code,
    icd.description                                          AS icd_description,
    pd.diagnosis_name,
    pd.is_primary,
    pd.date_from,
    pd.date_to,
    CASE WHEN pd.date_to IS NULL OR pd.date_to >= CURRENT_DATE
         THEN 'ACTIVE' ELSE 'RESOLVED'
    END                                                      AS diagnosis_status,
    (COALESCE(pd.date_to, CURRENT_DATE) - pd.date_from)::INT AS days_active,
    me.exam_date,
    a.appointment_type
FROM Patient_diagnosis pd
JOIN Patient p              ON p.patient_id   = pd.patient_id
JOIN Doctor d               ON d.doctor_id    = pd.doctor_id
JOIN Specialization sp      ON sp.spec_id     = d.spec_id
JOIN ICD icd                ON icd.icd_id     = pd.icd_id
JOIN Medical_examination me ON me.exam_id     = pd.exam_id
JOIN Appointment a          ON a.appointment_id = me.appointment_id;

CREATE OR REPLACE VIEW v_lab_test_overview AS
SELECT
    lt.lab_id,
    lt.test_name,
    lt.status                                                AS test_status,
    lt.result,
    me.exam_date,
    p.patient_id,
    p.first_name || ' ' || p.last_name                      AS patient_name,
    p.date_of_birth,
    DATE_PART('year', AGE(p.date_of_birth))::INT            AS patient_age,
    d.first_name || ' ' || d.last_name                      AS ordering_doctor,
    sp.spec_name                                             AS doctor_specialization,
    a.appointment_date,
    a.appointment_type,
    a.priority_level
FROM Laboratory_test lt
JOIN Medical_examination me ON me.exam_id      = lt.exam_id
JOIN Appointment a          ON a.appointment_id = me.appointment_id
JOIN Patient p              ON p.patient_id    = lt.patient_id
JOIN Doctor d               ON d.doctor_id     = lt.doctor_id
JOIN Specialization sp      ON sp.spec_id      = d.spec_id;

CREATE OR REPLACE VIEW v_prescription_dispensing AS
SELECT
    pr.presc_id,
    pr.presc_date,
    p.patient_id,
    p.first_name || ' ' || p.last_name                      AS patient_name,
    p.date_of_birth,
    d.first_name || ' ' || d.last_name                      AS prescribing_doctor,
    sp.spec_name                                             AS doctor_specialization,
    atc.atc_code,
    atc.description                                          AS drug_class,
    dp.producer_name                                         AS manufacturer,
    dr.unit_price,
    pr.dosage,
    pr.duration,
    round((dr.unit_price * pr.dosage * pr.duration)::NUMERIC, 2) AS estimated_cost,
    pd.diagnosis_name                                        AS prescribed_for,
    icd.code                                                 AS icd_code
FROM Prescription pr
JOIN Patient p              ON p.patient_id    = pr.patient_id
JOIN Doctor d               ON d.doctor_id     = pr.doctor_id
JOIN Specialization sp      ON sp.spec_id      = d.spec_id
JOIN ATC_code atc           ON atc.atc_id      = pr.atc_id
JOIN Inventory i            ON i.inventory_id  = pr.inventory_id
JOIN Drug dr                ON dr.product_id   = i.product_id
JOIN Drug_producers dp      ON dp.drug_prod_id = dr.drug_prod_id
JOIN Patient_diagnosis pd   ON pd.patient_diagnosis_id = pr.patient_diagnosis_id
JOIN ICD icd                ON icd.icd_id      = pd.icd_id;

CREATE OR REPLACE VIEW v_referral_tracking AS
SELECT
    r.referral_id,
    p.patient_id,
    p.first_name || ' ' || p.last_name                      AS patient_name,
    ref_dr.first_name || ' ' || ref_dr.last_name            AS referring_doctor,
    ref_sp.spec_name                                         AS referring_specialization,
    rfd.first_name || ' ' || rfd.last_name                  AS referred_to_doctor,
    rfd_sp.spec_name                                         AS referred_specialization,
    dep.department_name                                      AS referred_department,
    CASE WHEN EXISTS (
        SELECT 1 FROM Appointment a
        WHERE a.referral_id = r.referral_id
    ) THEN 'YES' ELSE 'NO' END                               AS appointment_booked,
    -- Count of appointments from this referral
    (SELECT COUNT(*) FROM Appointment a
     WHERE a.referral_id = r.referral_id)                    AS appointments_count
FROM Referral r
JOIN Patient p              ON p.patient_id    = r.patient_id
JOIN Doctor ref_dr          ON ref_dr.doctor_id = r.referring_doctor_id
JOIN Specialization ref_sp  ON ref_sp.spec_id  = ref_dr.spec_id
LEFT JOIN Doctor rfd        ON rfd.doctor_id   = r.referred_doctor_id
LEFT JOIN Specialization rfd_sp ON rfd_sp.spec_id = rfd.spec_id
JOIN Department dep         ON dep.department_id = r.department_id;

CREATE OR REPLACE VIEW v_department_performance AS
SELECT
    dep.department_id,
    dep.department_name,
    sp.spec_name                                             AS specialization,
    COUNT(DISTINCT dd.doctor_id)                             AS total_doctors,
    COUNT(DISTINCT CASE WHEN dd.employment_type = 'FULL_TIME'
                        THEN dd.doctor_id END)               AS full_time_doctors,
    COUNT(DISTINCT CASE WHEN dd.employment_type = 'RESIDENT'
                        THEN dd.doctor_id END)               AS residents,
    COUNT(DISTINCT a.appointment_id)                         AS total_appointments,
    COUNT(DISTINCT CASE WHEN a.status = 'COMPLETED'
                        THEN a.appointment_id END)           AS completed_appointments,
    COUNT(DISTINCT CASE WHEN a.appointment_type = 'EMERGENCY'
                        THEN a.appointment_id END)           AS emergency_appointments,
    COUNT(DISTINCT a.patient_id)                             AS unique_patients_seen,
    COUNT(DISTINCT me.exam_id)                               AS exams_performed,
    COUNT(DISTINCT r.referral_id)                            AS referrals_received,
    ROUND(
        100.0 * COUNT(DISTINCT CASE WHEN a.status = 'COMPLETED'
                                    THEN a.appointment_id END)
        / NULLIF(COUNT(DISTINCT a.appointment_id), 0), 1
    )                                                        AS completion_rate_pct
FROM Department dep
JOIN Specialization sp          ON sp.spec_id      = dep.spec_id
LEFT JOIN Doctor_department dd  ON dd.department_id = dep.department_id
                               AND dd.date_to IS NULL
LEFT JOIN Appointment a         ON a.doctor_id IN (
    SELECT doctor_id FROM Doctor_department
    WHERE department_id = dep.department_id
)
LEFT JOIN Medical_examination me ON me.exam_id IN (
    SELECT me2.exam_id FROM Medical_examination me2
    JOIN Appointment a2 ON a2.appointment_id = me2.appointment_id
    WHERE a2.doctor_id IN (
        SELECT doctor_id FROM Doctor_department
        WHERE department_id = dep.department_id
    )
)
LEFT JOIN Referral r ON r.department_id = dep.department_id
GROUP BY dep.department_id, dep.department_name, sp.spec_name;