= Advanced Reports =


----

== Doctor performance analytics ==

This report identifies which doctors are performing best over a given date range (`report_start`–`report_end`), combining several independent activity streams into one weighted score:

* Appointment volume and completion rate.
* Procedure volume, revenue, and how often a performed procedure has a documented result on file.
* Lab test volume, revenue, and how often an ordered test type has a result available.
* Referral volume and whether referred patients actually followed up with the receiving doctor.
* Breadth of care, measured as the number of distinct patients touched across procedures, lab tests, and appointments combined.

Six factors feed into one weighted performance_score: appointment completion,
procedure documentation, lab result availability, and referral follow-up each
contribute a percentage-based rate, while procedure volume and patient breadth
contribute capped counts (so one very busy doctor can't dominate the score). Doctors
are then ranked with DENSE_RANK(), so tied scores share a rank instead of skipping
numbers.


=== SQL ===

{{{
WITH params AS (
    SELECT
        CAST('2026-05-16' AS DATE) AS report_start,
        CAST('2026-08-26' AS DATE) AS report_end
),


documented_procedure_ids AS (
    SELECT DISTINCT procedure_id FROM procedure_results
),
documented_test_ids AS (
    SELECT DISTINCT test_id FROM lab_results
),

doctor_appointments AS (
    SELECT
        d.doctor_id, d.first_name, d.last_name, d.email_address,
        ds.specialization_name, dept.department_name,
        COUNT(*) AS total_appointments,
        COUNT(*) FILTER (WHERE a.status = 'COMPLETED') AS completed_appointments,
        COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS cancelled_appointments,
        ROUND(100.0 * COUNT(*) FILTER (WHERE a.status = 'COMPLETED') / NULLIF(COUNT(*), 0), 2) AS appointment_completion_rate
    FROM doctors d
        JOIN appointments a ON d.doctor_id = a.doctor_id
        JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
        JOIN departments dept ON d.department_id = dept.department_id
        JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
    GROUP BY d.doctor_id, d.first_name, d.last_name, d.email_address, ds.specialization_name, dept.department_name
),

doctor_procedures AS (
    SELECT
        d.doctor_id,
        COUNT(*) AS procedures_performed,
        ROUND(AVG(proc.cost)::numeric, 2) AS avg_procedure_cost,
        SUM(proc.cost) AS total_procedure_revenue,
        ROUND(100.0 * COUNT(DISTINCT dpi.procedure_id) / NULLIF(COUNT(DISTINCT pp.procedure_id), 0), 2) AS procedure_documentation_rate
    FROM doctors d
        JOIN performed_procedures pp ON d.doctor_id = pp.doctor_id
        JOIN procedures proc ON pp.procedure_id = proc.procedure_id
        LEFT JOIN documented_procedure_ids dpi ON dpi.procedure_id = pp.procedure_id
        JOIN params p ON pp.procedure_date >= p.report_start AND pp.procedure_date <= p.report_end
    GROUP BY d.doctor_id
),

doctor_lab_tests AS (
    SELECT
        d.doctor_id,
        COUNT(*) AS lab_tests_ordered,
        ROUND(AVG(lt.cost)::numeric, 2) AS avg_test_cost,
        SUM(lt.cost) AS total_lab_revenue,
        ROUND(100.0 * COUNT(DISTINCT dti.test_id) / NULLIF(COUNT(DISTINCT plt.test_id), 0), 2) AS lab_result_availability_rate
    FROM doctors d
        JOIN performed_lab_tests plt ON d.doctor_id = plt.doctor_id
        JOIN lab_tests lt ON plt.test_id = lt.test_id
        LEFT JOIN documented_test_ids dti ON dti.test_id = plt.test_id
        JOIN params p ON plt.test_date >= p.report_start AND plt.test_date <= p.report_end
    GROUP BY d.doctor_id
),

doctor_referrals AS (
    SELECT
        d.doctor_id,
        COUNT(DISTINCT ref.referral_id) AS referrals_made,
        ROUND(100.0 * COUNT(DISTINCT CASE WHEN EXISTS (
            SELECT 1 FROM appointments fa
            WHERE fa.doctor_id = ref.to_doctor_id
              AND fa.patient_id = mr.patient_id
              AND fa.appointment_date > ref.referral_date
        ) THEN ref.referral_id END) / NULLIF(COUNT(DISTINCT ref.referral_id), 0), 2) AS referral_followup_rate
    FROM doctors d
        JOIN referrals ref ON d.doctor_id = ref.from_doctor_id
        JOIN medical_records mr ON ref.record_id = mr.record_id
        JOIN params p ON ref.referral_date >= p.report_start AND ref.referral_date <= p.report_end
    GROUP BY d.doctor_id
),

doctor_patient_touchpoints AS (
    SELECT pp.doctor_id, pp.patient_id FROM performed_procedures pp
        JOIN params p ON pp.procedure_date >= p.report_start AND pp.procedure_date <= p.report_end
    UNION
    SELECT plt.doctor_id, plt.patient_id FROM performed_lab_tests plt
        JOIN params p ON plt.test_date >= p.report_start AND plt.test_date <= p.report_end
    UNION
    SELECT a.doctor_id, a.patient_id FROM appointments a
        JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
),

doctor_unique_patients AS (
    SELECT doctor_id, COUNT(DISTINCT patient_id) AS total_unique_patients
    FROM doctor_patient_touchpoints
    GROUP BY doctor_id
),

doctor_scores AS (
    SELECT
        da.doctor_id, da.first_name, da.last_name, da.email_address,
        da.specialization_name, da.department_name,
        da.total_appointments, da.completed_appointments, da.cancelled_appointments, da.appointment_completion_rate,
        COALESCE(dp.procedures_performed, 0) AS procedures_performed,
        COALESCE(dp.avg_procedure_cost, 0) AS avg_procedure_cost,
        COALESCE(dp.total_procedure_revenue, 0) AS total_procedure_revenue,
        COALESCE(dp.procedure_documentation_rate, 0) AS procedure_documentation_rate,
        COALESCE(dl.lab_tests_ordered, 0) AS lab_tests_ordered,
        COALESCE(dl.avg_test_cost, 0) AS avg_test_cost,
        COALESCE(dl.total_lab_revenue, 0) AS total_lab_revenue,
        COALESCE(dl.lab_result_availability_rate, 0) AS lab_result_availability_rate,
        COALESCE(dr.referrals_made, 0) AS referrals_made,
        COALESCE(dr.referral_followup_rate, 0) AS referral_followup_rate,
        COALESCE(dup.total_unique_patients, 0) AS total_unique_patients,
        ROUND(
            COALESCE(da.appointment_completion_rate, 0) * 0.20
            + COALESCE(dp.procedure_documentation_rate, 0) * 0.20
            + COALESCE(dl.lab_result_availability_rate, 0) * 0.15
            + COALESCE(dr.referral_followup_rate, 0) * 0.15
            + LEAST(COALESCE(dp.procedures_performed, 0), 50) * 0.30
            + LEAST(COALESCE(dup.total_unique_patients, 0), 100) * 0.20
        , 2) AS performance_score
    FROM doctor_appointments da
        LEFT JOIN doctor_procedures dp ON da.doctor_id = dp.doctor_id
        LEFT JOIN doctor_lab_tests dl ON da.doctor_id = dl.doctor_id
        LEFT JOIN doctor_referrals dr ON da.doctor_id = dr.doctor_id
        LEFT JOIN doctor_unique_patients dup ON da.doctor_id = dup.doctor_id
)

SELECT *,
    DENSE_RANK() OVER (ORDER BY performance_score DESC) AS performance_rank
FROM doctor_scores
ORDER BY performance_rank, last_name, first_name;
}}}

=== Relational Algebra ===

{{{
Params ← {(report_start, report_end)}

DocumentedProcedureIds ← π_{procedure_id} (procedure_results)
DocumentedTestIds ← π_{test_id} (lab_results)

DoctorAppointments ←
γ
  doctor_id := d.doctor_id; first_name := d.first_name; last_name := d.last_name;
  email_address := d.email_address; specialization_name := ds.specialization_name;
  department_name := dept.department_name;
  total_appointments := COUNT(*);
  completed_appointments := COUNT(*) FILTER (a.status = 'COMPLETED');
  cancelled_appointments := COUNT(*) FILTER (a.status = 'CANCELLED');
  appointment_completion_rate := ROUND(100.0 * COUNT(*) FILTER (a.status = 'COMPLETED') / COUNT(*), 2)
(
  σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end)
  (
    (
      (doctors d ⨝ (d.doctor_id = a.doctor_id) appointments a)
      ⨝ (d.specialization_id = ds.specialization_id) doctor_specialization ds
    )
    ⨝ (d.department_id = dept.department_id) departments dept
    × Params p
  )
)

DoctorProcedures ←
γ
  doctor_id := d.doctor_id;
  procedures_performed := COUNT(*);
  avg_procedure_cost := ROUND(AVG(proc.cost), 2);
  total_procedure_revenue := SUM(proc.cost);
  procedure_documentation_rate := ROUND(100.0 * COUNT_DISTINCT(dpi.procedure_id) / COUNT_DISTINCT(pp.procedure_id), 2)
(
  σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end)
  (
    (
      (doctors d ⨝ (d.doctor_id = pp.doctor_id) performed_procedures pp)
      ⨝ (pp.procedure_id = proc.procedure_id) procedures proc
    )
    ⟕ (pp.procedure_id = dpi.procedure_id) DocumentedProcedureIds dpi
    × Params p
  )
)

DoctorLabTests ←
γ
  doctor_id := d.doctor_id;
  lab_tests_ordered := COUNT(*);
  avg_test_cost := ROUND(AVG(lt.cost), 2);
  total_lab_revenue := SUM(lt.cost);
  lab_result_availability_rate := ROUND(100.0 * COUNT_DISTINCT(dti.test_id) / COUNT_DISTINCT(plt.test_id), 2)
(
  σ (plt.test_date ≥ p.report_start ∧ plt.test_date ≤ p.report_end)
  (
    (
      (doctors d ⨝ (d.doctor_id = plt.doctor_id) performed_lab_tests plt)
      ⨝ (plt.test_id = lt.test_id) lab_tests lt
    )
    ⟕ (plt.test_id = dti.test_id) DocumentedTestIds dti
    × Params p
  )
)

FollowedUpReferrals ←
π_{referral_id}
(
  σ (fa.doctor_id = ref.to_doctor_id ∧ fa.patient_id = mr.patient_id ∧ fa.appointment_date > ref.referral_date)
  (
    (referrals ref ⨝ (ref.record_id = mr.record_id) medical_records mr)
    × appointments fa
  )
)

DoctorReferrals ←
γ
  doctor_id := d.doctor_id;
  referrals_made := COUNT_DISTINCT(ref.referral_id);
  referral_followup_rate := ROUND(100.0 * COUNT_DISTINCT(fur.referral_id) / COUNT_DISTINCT(ref.referral_id), 2)
(
  σ (ref.referral_date ≥ p.report_start ∧ ref.referral_date ≤ p.report_end)
  (
    (
      (doctors d ⨝ (d.doctor_id = ref.from_doctor_id) referrals ref)
      ⨝ (ref.record_id = mr.record_id) medical_records mr
    )
    ⟕ (ref.referral_id = fur.referral_id) FollowedUpReferrals fur
    × Params p
  )
)

DoctorPatientTouchpoints ←
π_{doctor_id, patient_id}
(
  σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end) (performed_procedures pp × Params p)
)
∪
π_{doctor_id, patient_id}
(
  σ (plt.test_date ≥ p.report_start ∧ plt.test_date ≤ p.report_end) (performed_lab_tests plt × Params p)
)
∪
π_{doctor_id, patient_id}
(
  σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end) (appointments a × Params p)
)

DoctorUniquePatients ←
γ doctor_id; total_unique_patients := COUNT_DISTINCT(patient_id)
(DoctorPatientTouchpoints)

DoctorScores ←
π
  doctor_id, first_name, last_name, email_address, specialization_name, department_name,
  total_appointments, completed_appointments, cancelled_appointments, appointment_completion_rate,
  procedures_performed := COALESCE(dp.procedures_performed, 0),
  avg_procedure_cost := COALESCE(dp.avg_procedure_cost, 0),
  total_procedure_revenue := COALESCE(dp.total_procedure_revenue, 0),
  procedure_documentation_rate := COALESCE(dp.procedure_documentation_rate, 0),
  lab_tests_ordered := COALESCE(dl.lab_tests_ordered, 0),
  avg_test_cost := COALESCE(dl.avg_test_cost, 0),
  total_lab_revenue := COALESCE(dl.total_lab_revenue, 0),
  lab_result_availability_rate := COALESCE(dl.lab_result_availability_rate, 0),
  referrals_made := COALESCE(dr.referrals_made, 0),
  referral_followup_rate := COALESCE(dr.referral_followup_rate, 0),
  total_unique_patients := COALESCE(dup.total_unique_patients, 0),
  performance_score :=
    ROUND(
      COALESCE(appointment_completion_rate, 0) * 0.20
      + COALESCE(dp.procedure_documentation_rate, 0) * 0.20
      + COALESCE(dl.lab_result_availability_rate, 0) * 0.15
      + COALESCE(dr.referral_followup_rate, 0) * 0.15
      + LEAST(COALESCE(dp.procedures_performed, 0), 50) * 0.30
      + LEAST(COALESCE(dup.total_unique_patients, 0), 100) * 0.20
    , 2)
(
  (
    (
      (DoctorAppointments da
      ⟕ (da.doctor_id = dp.doctor_id) DoctorProcedures dp)
      ⟕ (da.doctor_id = dl.doctor_id) DoctorLabTests dl
    )
    ⟕ (da.doctor_id = dr.doctor_id) DoctorReferrals dr
  )
  ⟕ (da.doctor_id = dup.doctor_id) DoctorUniquePatients dup
)

RankedDoctors ←
rank_dense
  performance_rank := ORDER BY performance_score DESC
(DoctorScores)

Result ←
τ performance_rank ASC, last_name ASC, first_name ASC
(RankedDoctors)
}}}

----

== Patient health risk assessment ==


This report identifies which patients carry the highest clinical risk, for proactive outreach and care-coordination purposes, combining:

* Chronic condition burden is the number of distinct diagnoses on file, capped at 10 in scoring so one outlier patient can't dominate the whole composite score, plus the count of severe symptoms.
* Allergy severity reflects the worst allergy severity level on file for the patient.
* A direct medication safety flag counts how many of the patient's currently prescribed medications are linked, via `allergy_prescription_restrictions`, to one of their documented allergies. This is the strongest single signal in the score, worth 10 points per conflict.
* Polypharmacy tier reflects how many distinct medications the patient currently has on record, bucketed into `LOW_POLYPHARMACY`, `MODERATE_POLYPHARMACY`, or `HIGH_POLYPHARMACY`.
* Appointment adherence covers the completion rate and days since the last appointment, within the lookback window set by the `:lookback_months` parameter.
* Referral burden is the number of referrals in the last 6 months.
* Age acts as a standard clinical risk modifier, with patients over 65 receiving additional points.

These combine into a `risk_score`, bucketed into `CRITICAL`/`HIGH`/`MODERATE`/`LOW`, and ranked with DENSE_RANK().

=== SQL ===

{{{
WITH params AS (
    SELECT
        CAST(:lookback_months AS INTEGER) AS lookback_months,
        CURRENT_DATE - (CAST(:lookback_months AS INTEGER) * INTERVAL '1 month') AS risk_assessment_start
),

patient_records AS (
    SELECT p.patient_id, mr.record_id
    FROM patients p
        LEFT JOIN medical_records mr ON mr.patient_id = p.patient_id
),


patient_chronic_conditions AS (
    SELECT
        p.patient_id, p.first_name, p.last_name, p.embg,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.date_of_birth))::int AS age,
        COUNT(DISTINCT d.diagnosis_id) AS chronic_diagnoses_count,
        COUNT(DISTINCT CASE WHEN UPPER(mrs.severity) = 'SEVERE' THEN mrs.symptom_id END) AS severe_symptoms_count
    FROM patients p
        LEFT JOIN diagnosis d ON d.patient_id = p.patient_id
        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
        LEFT JOIN medical_record_symptoms mrs ON mrs.record_id = pr.record_id
    GROUP BY p.patient_id, p.first_name, p.last_name, p.embg, p.date_of_birth
),


patient_medication_profile AS (
    SELECT
        p.patient_id,
        COUNT(DISTINCT pmr.prescription_id) AS current_medications,
        CASE
            WHEN COUNT(DISTINCT pmr.prescription_id) >= 5 THEN 'HIGH_POLYPHARMACY'
            WHEN COUNT(DISTINCT pmr.prescription_id) >= 3 THEN 'MODERATE_POLYPHARMACY'
            ELSE 'LOW_POLYPHARMACY'
        END AS polypharmacy_status,
        COUNT(DISTINCT pa.allergy_id) AS allergy_count,
        CASE
            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
            WHEN COUNT(DISTINCT pa.allergy_id) > 0 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS max_allergy_severity,
        COUNT(DISTINCT restr.restriction_id) FILTER (WHERE pmr2.prescription_id IS NOT NULL)
            AS conflicting_restrictions_on_current_meds
    FROM patients p
        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
        LEFT JOIN prescription_medical_records pmr ON pmr.record_id = pr.record_id
        LEFT JOIN patient_allergies pa ON pa.patient_id = p.patient_id
        LEFT JOIN allergies a ON a.allergy_id = pa.allergy_id
        LEFT JOIN allergy_prescription_restrictions apr ON apr.allergy_id = a.allergy_id
        LEFT JOIN prescription_restriction restr ON restr.restriction_id = apr.restriction_id
        LEFT JOIN prescription_medical_records pmr2
            ON pmr2.record_id = pr.record_id AND pmr2.prescription_id = restr.prescription_id
    GROUP BY p.patient_id
),

patient_activity AS (
    SELECT
        p.patient_id,
        ROUND(100.0 * COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED')
            / NULLIF(COUNT(a.appointment_id), 0), 2) AS appointment_completion_rate,
        CURRENT_DATE - MAX(a.appointment_date) AS days_since_last_appointment
    FROM patients p
        LEFT JOIN appointments a
            ON a.patient_id = p.patient_id
            AND a.appointment_date >= (SELECT risk_assessment_start FROM params)
    GROUP BY p.patient_id
),

patient_referrals AS (
    SELECT
        p.patient_id,
        COUNT(DISTINCT CASE WHEN r.referral_date > CURRENT_DATE - INTERVAL '6 months' THEN r.referral_id END)
            AS referrals_last_6_months
    FROM patients p
        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
        LEFT JOIN referrals r ON r.record_id = pr.record_id
    GROUP BY p.patient_id
),

patient_risk_scores AS (
    SELECT
        pcc.patient_id, pcc.first_name, pcc.last_name, pcc.embg, pcc.age,
        pcc.chronic_diagnoses_count, pcc.severe_symptoms_count,
        COALESCE(pmp.allergy_count, 0) AS allergy_count,
        COALESCE(pmp.max_allergy_severity, 'LOW') AS max_allergy_severity,
        COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) AS conflicting_restrictions_on_current_meds,
        COALESCE(pmp.current_medications, 0) AS current_medications,
        COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY') AS polypharmacy_status,
        COALESCE(pa.appointment_completion_rate, 100) AS appointment_completion_rate,
        COALESCE(pa.days_since_last_appointment, 9999) AS days_since_last_appointment,
        COALESCE(pr.referrals_last_6_months, 0) AS referrals_last_6_months,
        ROUND(
            LEAST(COALESCE(pcc.chronic_diagnoses_count, 0), 10) * 3
            + COALESCE(pcc.severe_symptoms_count, 0) * 4
            + CASE COALESCE(pmp.max_allergy_severity, 'LOW')
                  WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
            + CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
                  WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
            + COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) * 10
            + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
            + CASE WHEN COALESCE(pa.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
            + COALESCE(pr.referrals_last_6_months, 0) * 2
            + CASE WHEN pcc.age > 65 THEN 10 ELSE 0 END
        , 2) AS risk_score
    FROM patient_chronic_conditions pcc
        LEFT JOIN patient_medication_profile pmp ON pmp.patient_id = pcc.patient_id
        LEFT JOIN patient_activity pa ON pa.patient_id = pcc.patient_id
        LEFT JOIN patient_referrals pr ON pr.patient_id = pcc.patient_id
)

SELECT
    patient_id, first_name, last_name, embg, age,
    chronic_diagnoses_count, severe_symptoms_count,
    allergy_count, max_allergy_severity, conflicting_restrictions_on_current_meds,
    current_medications, polypharmacy_status,
    appointment_completion_rate, days_since_last_appointment,
    referrals_last_6_months,
    risk_score,
    CASE
        WHEN risk_score > 75 THEN 'CRITICAL'
        WHEN risk_score > 50 THEN 'HIGH'
        WHEN risk_score > 25 THEN 'MODERATE'
        ELSE 'LOW'
    END AS risk_category,
    DENSE_RANK() OVER (ORDER BY risk_score DESC) AS risk_rank
FROM patient_risk_scores
WHERE chronic_diagnoses_count > 0 OR allergy_count > 0 OR current_medications > 0
ORDER BY risk_rank, last_name, first_name;
}}}

=== Relational Algebra ===

{{{
Params ← {(lookback_months, risk_assessment_start)}

PatientRecords ←
π_{patient_id, record_id}
(patients p ⟕ (p.patient_id = mr.patient_id) medical_records mr)

PatientChronicConditions ←
γ
  patient_id := p.patient_id; first_name := p.first_name; last_name := p.last_name; embg := p.embg;
  age := YEAR(AGE(CURRENT_DATE, p.date_of_birth));
  chronic_diagnoses_count := COUNT_DISTINCT(d.diagnosis_id);
  severe_symptoms_count := COUNT_DISTINCT(CASE WHEN UPPER(mrs.severity) = 'SEVERE' THEN mrs.symptom_id END)
(
  (
    (patients p ⟕ (p.patient_id = d.patient_id) diagnosis d)
    ⟕ (p.patient_id = pr.patient_id) PatientRecords pr
  )
  ⟕ (pr.record_id = mrs.record_id) medical_record_symptoms mrs
)

PatientMedicationProfile ←
γ
  patient_id := p.patient_id;
  current_medications := COUNT_DISTINCT(pmr.prescription_id);
  polypharmacy_status :=
    CASE WHEN COUNT_DISTINCT(pmr.prescription_id) ≥ 5 THEN 'HIGH_POLYPHARMACY'
         WHEN COUNT_DISTINCT(pmr.prescription_id) ≥ 3 THEN 'MODERATE_POLYPHARMACY'
         ELSE 'LOW_POLYPHARMACY' END;
  allergy_count := COUNT_DISTINCT(pa.allergy_id);
  max_allergy_severity :=
    CASE WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
         WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
         WHEN COUNT_DISTINCT(pa.allergy_id) > 0 THEN 'MEDIUM' ELSE 'LOW' END;
  conflicting_restrictions_on_current_meds :=
    COUNT_DISTINCT(restr.restriction_id) FILTER (pmr2.prescription_id IS NOT NULL)
(
  (
    (
      (
        (
          (patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
          ⟕ (pr.record_id = pmr.record_id) prescription_medical_records pmr
        )
        ⟕ (p.patient_id = pa.patient_id) patient_allergies pa
      )
      ⟕ (pa.allergy_id = a.allergy_id) allergies a
    )
    ⟕ (a.allergy_id = apr.allergy_id) allergy_prescription_restrictions apr
  )
  ⟕ (apr.restriction_id = restr.restriction_id) prescription_restriction restr
  ⟕ (pr.record_id = pmr2.record_id ∧ pmr2.prescription_id = restr.prescription_id) prescription_medical_records pmr2
)

FilteredAppointments ← σ (a.appointment_date ≥ p.risk_assessment_start) (appointments a × Params p)

PatientActivity ←
γ
  patient_id := p.patient_id;
  appointment_completion_rate := ROUND(100.0 * COUNT(fa.appointment_id) FILTER (fa.status = 'COMPLETED') / COUNT(fa.appointment_id), 2);
  days_since_last_appointment := CURRENT_DATE - MAX(fa.appointment_date)
(
  patients p ⟕ (p.patient_id = fa.patient_id) FilteredAppointments fa
)

PatientReferrals ←
γ
  patient_id := p.patient_id;
  referrals_last_6_months := COUNT_DISTINCT(CASE WHEN r.referral_date > CURRENT_DATE - 6 MONTHS THEN r.referral_id END)
(
  (patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
  ⟕ (pr.record_id = r.record_id) referrals r
)

PatientRiskScores ←
π
  patient_id, first_name, last_name, embg, age, chronic_diagnoses_count, severe_symptoms_count,
  allergy_count := COALESCE(pmp.allergy_count, 0),
  max_allergy_severity := COALESCE(pmp.max_allergy_severity, 'LOW'),
  conflicting_restrictions_on_current_meds := COALESCE(pmp.conflicting_restrictions_on_current_meds, 0),
  current_medications := COALESCE(pmp.current_medications, 0),
  polypharmacy_status := COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY'),
  appointment_completion_rate := COALESCE(pa.appointment_completion_rate, 100),
  days_since_last_appointment := COALESCE(pa.days_since_last_appointment, 9999),
  referrals_last_6_months := COALESCE(pr.referrals_last_6_months, 0),
  risk_score :=
    ROUND(
      LEAST(COALESCE(chronic_diagnoses_count, 0), 10) * 3
      + COALESCE(severe_symptoms_count, 0) * 4
      + CASE COALESCE(pmp.max_allergy_severity, 'LOW')
             WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
      + CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
             WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
      + COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) * 10
      + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
      + CASE WHEN COALESCE(pa.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
      + COALESCE(pr.referrals_last_6_months, 0) * 2
      + CASE WHEN age > 65 THEN 10 ELSE 0 END
    , 2)
(
  (
    (PatientChronicConditions pcc
    ⟕ (pcc.patient_id = pmp.patient_id) PatientMedicationProfile pmp)
    ⟕ (pcc.patient_id = pa.patient_id) PatientActivity pa
  )
  ⟕ (pcc.patient_id = pr.patient_id) PatientReferrals pr
)

RiskCategorized ←
π
  *,
  risk_category :=
    CASE WHEN risk_score > 75 THEN 'CRITICAL'
         WHEN risk_score > 50 THEN 'HIGH'
         WHEN risk_score > 25 THEN 'MODERATE'
         ELSE 'LOW' END
(PatientRiskScores)

FilteredPatients ←
σ (chronic_diagnoses_count > 0 ∨ allergy_count > 0 ∨ current_medications > 0)
(RiskCategorized)

RankedPatients ←
rank_dense
  risk_rank := ORDER BY risk_score DESC
(FilteredPatients)

Result ←
τ risk_rank ASC, last_name ASC, first_name ASC
(RankedPatients)
}}}