Index: backend/src/main/resources/db.migration/R__allergy_prescription_safety_enforcement.sql =================================================================== --- backend/src/main/resources/db.migration/R__allergy_prescription_safety_enforcement.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/R__allergy_prescription_safety_enforcement.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,85 @@ +CREATE DOMAIN non_negative_currency AS DECIMAL(12,2) + CHECK (VALUE >= 0); + +CREATE OR REPLACE FUNCTION t1_prescription_allergy_check() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +DECLARE + v_patient_id BIGINT; + v_conflict_allergy_id BIGINT; + v_allergy_name TEXT; +BEGIN + SELECT patient_id INTO v_patient_id + FROM medical_records + WHERE record_id = NEW.record_id; + + IF v_patient_id IS NULL THEN + RAISE EXCEPTION 'Medical record % not found', NEW.record_id; + END IF; + + SELECT apr.allergy_id, a.name + INTO v_conflict_allergy_id, v_allergy_name + FROM prescription_restriction pr_rest + JOIN allergy_prescription_restrictions apr ON apr.restriction_id = pr_rest.restriction_id + JOIN allergies a ON apr.allergy_id = a.allergy_id + JOIN medical_record_allergies mra ON a.allergy_id = mra.allergy_id + WHERE pr_rest.prescription_id = NEW.prescription_id + AND mra.record_id = NEW.record_id + LIMIT 1; + + IF v_conflict_allergy_id IS NOT NULL THEN + RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) in patient''s record %', + NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, NEW.record_id; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_prescription_allergy_check ON prescription_medical_records; +CREATE TRIGGER trg_prescription_allergy_check + BEFORE INSERT + ON prescription_medical_records + FOR EACH ROW + EXECUTE FUNCTION t1_prescription_allergy_check(); + +CREATE TABLE IF NOT EXISTS prescription_allergy_conflicts_log ( + log_id BIGSERIAL PRIMARY KEY, + record_id BIGINT NOT NULL REFERENCES medical_records(record_id), + prescription_id BIGINT NOT NULL REFERENCES prescriptions(prescription_id), + allergy_id BIGINT NOT NULL REFERENCES allergies(allergy_id), + conflict_type TEXT CHECK (conflict_type IN ('ACTIVE', 'RESOLVED')), + detected_date TIMESTAMP DEFAULT NOW(), + resolution_notes TEXT, + resolved_date TIMESTAMP +); + +CREATE OR REPLACE VIEW v_prescription_allergy_conflicts AS +SELECT + mr.record_id, + p.patient_id, + p.first_name, + p.last_name, + pmr.prescription_id, + pr.medication_name, + a.allergy_id, + a.name AS allergy_name, + a.allergy_severity, + apr.restriction_id, + pr_rest.description AS restriction_description, + CASE + WHEN mra.record_id IS NOT NULL THEN 'ACTIVE_CONFLICT' + ELSE 'ARCHIVED' + END AS conflict_status +FROM prescription_medical_records pmr +JOIN medical_records mr ON pmr.record_id = mr.record_id +JOIN patients p ON mr.patient_id = p.patient_id +JOIN prescriptions pr ON pmr.prescription_id = pr.prescription_id +JOIN prescription_restriction pr_rest ON pr.prescription_id = pr_rest.prescription_id +JOIN allergy_prescription_restrictions apr ON pr_rest.restriction_id = apr.restriction_id +JOIN allergies a ON apr.allergy_id = a.allergy_id +LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id + AND a.allergy_id = mra.allergy_id +WHERE a.allergy_severity IN ('HIGH', 'CRITICAL') +ORDER BY p.patient_id, a.allergy_severity DESC; Index: backend/src/main/resources/db.migration/R__billing_integrity.sql =================================================================== --- backend/src/main/resources/db.migration/R__billing_integrity.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/R__billing_integrity.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,145 @@ +-- schema addition: billing had no "issued" date, only payment_date (populated only once +-- paid), so there was no way to measure how long a still-PENDING bill has been outstanding. +-- Existing rows will backfill to NOW() at ALTER time, which is not historically accurate — +-- acceptable for this project, but worth noting. +ALTER TABLE billing ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW(); + +CREATE TABLE IF NOT EXISTS billing_audit_log ( + audit_id BIGSERIAL PRIMARY KEY, + bill_id BIGINT NOT NULL REFERENCES billing(bill_id), + old_amount DECIMAL(12,2), + new_amount DECIMAL(12,2), + old_status TEXT, + new_status TEXT, + change_type TEXT CHECK (change_type IN ('INSERT', 'UPDATE', 'LINE_ITEM_ADD', 'LINE_ITEM_REMOVE')), + changed_at TIMESTAMP DEFAULT NOW() + ); + +-- Note: billing_procedures links to the catalog procedure_id, not to a specific +-- performed_procedures row, so when the same procedure type has been performed on more +-- than one patient there is no reliable way to verify a billed line item belongs to the +-- same patient as the bill. That check is intentionally left out rather than implemented +-- unreliably. + +CREATE OR REPLACE FUNCTION recalculate_billing_total(p_bill_id BIGINT, p_change_type TEXT) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE +v_procedure_total DECIMAL; + v_lab_total DECIMAL; + v_new_total DECIMAL; +BEGIN +SELECT COALESCE(SUM(p.cost), 0) INTO v_procedure_total +FROM billing_procedures bp + JOIN procedures p ON p.procedure_id = bp.procedure_id +WHERE bp.bill_id = p_bill_id; + +SELECT COALESCE(SUM(lt.cost), 0) INTO v_lab_total +FROM billing_lab_tests blt + JOIN lab_tests lt ON lt.test_id = blt.test_id +WHERE blt.bill_id = p_bill_id; + +v_new_total := v_procedure_total + v_lab_total; + +UPDATE billing SET total_cost = v_new_total WHERE bill_id = p_bill_id; + +INSERT INTO billing_audit_log (bill_id, new_amount, change_type) +VALUES (p_bill_id, v_new_total, p_change_type); +END; +$$; + +-- one shared trigger function, reused for both billing_procedures and billing_lab_tests +-- (both tables have a bill_id column, so NEW.bill_id / OLD.bill_id works either way) +CREATE OR REPLACE FUNCTION t1_billing_line_item_changed() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + IF TG_OP = 'DELETE' THEN + PERFORM recalculate_billing_total(OLD.bill_id, 'LINE_ITEM_REMOVE'); +RETURN OLD; +ELSE + PERFORM recalculate_billing_total(NEW.bill_id, 'LINE_ITEM_ADD'); +RETURN NEW; +END IF; +END; +$$; + +DROP TRIGGER IF EXISTS trg_billing_procedures_update_total ON billing_procedures; +CREATE TRIGGER trg_billing_procedures_update_total + AFTER INSERT OR DELETE ON billing_procedures +FOR EACH ROW +EXECUTE FUNCTION t1_billing_line_item_changed(); + +DROP TRIGGER IF EXISTS trg_billing_lab_tests_update_total ON billing_lab_tests; +CREATE TRIGGER trg_billing_lab_tests_update_total + AFTER INSERT OR DELETE ON billing_lab_tests +FOR EACH ROW +EXECUTE FUNCTION t1_billing_line_item_changed(); + +CREATE OR REPLACE FUNCTION is_valid_billing_transition(p_old TEXT, p_new TEXT) +RETURNS BOOLEAN +LANGUAGE sql +IMMUTABLE +AS $$ +SELECT CASE + WHEN p_old = p_new THEN TRUE + WHEN p_old = 'PENDING' AND p_new IN ('PAID', 'CANCELLED') THEN TRUE + ELSE FALSE + END; +$$; + +CREATE OR REPLACE FUNCTION t2_billing_status_transition() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + IF NOT is_valid_billing_transition(OLD.payment_status, NEW.payment_status) THEN + RAISE EXCEPTION 'Cannot transition billing status from % to %', + OLD.payment_status, NEW.payment_status; +END IF; + + IF OLD.payment_status <> NEW.payment_status THEN + INSERT INTO billing_audit_log (bill_id, old_status, new_status, change_type) + VALUES (NEW.bill_id, OLD.payment_status, NEW.payment_status, 'UPDATE'); +END IF; + +RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_billing_status_transition ON billing; +CREATE TRIGGER trg_billing_status_transition + BEFORE UPDATE ON billing + FOR EACH ROW + EXECUTE FUNCTION t2_billing_status_transition(); + +CREATE OR REPLACE VIEW v_overdue_billings AS +SELECT + b.bill_id, + p.patient_id, p.first_name, p.last_name, + b.total_cost, b.payment_status, b.created_at, + CURRENT_DATE - b.created_at::DATE AS days_outstanding, + CASE + WHEN CURRENT_DATE - b.created_at::DATE > 60 THEN 'CRITICAL' + WHEN CURRENT_DATE - b.created_at::DATE > 30 THEN 'OVERDUE' + ELSE 'PENDING' +END AS urgency +FROM billing b +JOIN medical_records mr ON b.record_id = mr.record_id +JOIN patients p ON mr.patient_id = p.patient_id +WHERE b.payment_status = 'PENDING' + AND CURRENT_DATE - b.created_at::DATE >= 30 +ORDER BY days_outstanding DESC; + +CREATE OR REPLACE PROCEDURE job_billing_alerts() +LANGUAGE plpgsql +AS $$ +DECLARE + v_overdue_count INT; +BEGIN + SELECT COUNT(*) INTO v_overdue_count FROM v_overdue_billings WHERE days_outstanding > 30; + RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count; +END; +$$; Index: backend/src/main/resources/db.migration/R__custom_domains.sql =================================================================== --- backend/src/main/resources/db.migration/R__custom_domains.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/R__custom_domains.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,14 @@ +CREATE DOMAIN embg_format AS TEXT + CHECK (VALUE ~ '^\d{13}$'); + +CREATE DOMAIN email_format AS TEXT + CHECK ( + VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' + AND LENGTH(VALUE) <= 254 + ); + +CREATE DOMAIN phone_number_format AS TEXT + CHECK (VALUE ~ '^\+?[\d\s\-().]{7,20}$'); + +CREATE DOMAIN non_negative_cost AS DECIMAL(12,2) + CHECK (VALUE >= 0); Index: backend/src/main/resources/db.migration/R__medical_record_integrity.sql =================================================================== --- backend/src/main/resources/db.migration/R__medical_record_integrity.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/R__medical_record_integrity.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,104 @@ +CREATE OR REPLACE FUNCTION t1_diagnosis_record_consistency() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +DECLARE + v_record_patient BIGINT; + v_diagnosis_patient BIGINT; +BEGIN + SELECT patient_id INTO v_record_patient FROM medical_records WHERE record_id = NEW.record_id; + SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id; + + IF v_record_patient IS NULL THEN + RAISE EXCEPTION 'Medical record % not found', NEW.record_id; + END IF; + + IF v_diagnosis_patient IS NULL THEN + RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id; + END IF; + + IF v_record_patient <> v_diagnosis_patient THEN + RAISE EXCEPTION 'Diagnosis % belongs to patient %, but medical record % belongs to patient %', + NEW.diagnosis_id, v_diagnosis_patient, NEW.record_id, v_record_patient; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_diagnosis_record_consistency ON diagnosis_medical_records; +CREATE TRIGGER trg_diagnosis_record_consistency + BEFORE INSERT ON diagnosis_medical_records + FOR EACH ROW EXECUTE FUNCTION t1_diagnosis_record_consistency(); + +CREATE OR REPLACE FUNCTION t2_procedure_diagnosis_consistency() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +DECLARE + v_diagnosis_patient BIGINT; +BEGIN + IF NEW.diagnosis_id IS NOT NULL THEN + SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id; + + IF v_diagnosis_patient IS NULL THEN + RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id; + END IF; + + IF NEW.patient_id <> v_diagnosis_patient THEN + RAISE EXCEPTION 'Procedure belongs to patient %, but diagnosis % belongs to patient %', + NEW.patient_id, NEW.diagnosis_id, v_diagnosis_patient; + END IF; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_procedure_diagnosis_consistency ON performed_procedures; +CREATE TRIGGER trg_procedure_diagnosis_consistency + BEFORE INSERT OR UPDATE ON performed_procedures + FOR EACH ROW EXECUTE FUNCTION t2_procedure_diagnosis_consistency(); + +CREATE OR REPLACE FUNCTION t3_referral_consistency() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + IF NOT EXISTS (SELECT 1 FROM medical_records WHERE record_id = NEW.record_id) THEN + RAISE EXCEPTION 'Medical record % not found', NEW.record_id; + END IF; + + IF NEW.from_doctor_id = NEW.to_doctor_id THEN + RAISE EXCEPTION 'Doctor % cannot refer to themselves', NEW.from_doctor_id; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_referral_consistency ON referrals; +CREATE TRIGGER trg_referral_consistency + BEFORE INSERT ON referrals + FOR EACH ROW EXECUTE FUNCTION t3_referral_consistency(); + +CREATE OR REPLACE VIEW v_medical_record_overview AS +SELECT + mr.record_id, + p.patient_id, p.first_name, p.last_name, p.embg, + COUNT(DISTINCT dmr.diagnosis_id) AS diagnosis_count, + COUNT(DISTINCT CASE WHEN d.patient_id IS NOT NULL AND d.patient_id <> p.patient_id THEN dmr.diagnosis_id END) AS diagnosis_mismatches, + COUNT(DISTINCT mrp.procedure_id) AS procedures_count, + COUNT(DISTINCT mrl.result_id) AS lab_results_count, + COUNT(DISTINCT ref.referral_id) AS referrals_count, + COUNT(DISTINCT mra.allergy_id) AS allergies_count, + COUNT(DISTINCT CASE WHEN ref.from_doctor_id = ref.to_doctor_id THEN ref.referral_id END) AS self_referrals_detected +FROM medical_records mr + JOIN patients p ON mr.patient_id = p.patient_id + LEFT JOIN diagnosis_medical_records dmr ON dmr.record_id = mr.record_id + LEFT JOIN diagnosis d ON d.diagnosis_id = dmr.diagnosis_id + LEFT JOIN medical_record_procedures mrp ON mrp.record_id = mr.record_id + LEFT JOIN medical_record_lab_results mrl ON mrl.record_id = mr.record_id + LEFT JOIN referrals ref ON mr.record_id = ref.record_id + LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id +GROUP BY mr.record_id, p.patient_id, p.first_name, p.last_name, p.embg; Index: backend/src/main/resources/db.migration/V6__Insert_Billing_Admin_Users.sql =================================================================== --- backend/src/main/resources/db.migration/V6__Insert_Billing_Admin_Users.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/V6__Insert_Billing_Admin_Users.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,28 @@ +SELECT + plt.performed_test_id, + lt.test_name, + u_p.first_name AS patient_first_name, + u_p.last_name AS patient_last_name, + u_d.first_name AS doctor_first_name, + u_d.last_name AS doctor_last_name, + plt.test_date, + plt.notes +FROM performed_lab_tests plt + JOIN lab_tests lt ON plt.test_id = lt.test_id + JOIN patients p ON plt.patient_id = p.patient_id + JOIN users u_p ON p.patient_id = u_p.patient_id + JOIN doctors d ON plt.doctor_id = d.doctor_id + JOIN users u_d ON d.doctor_id = u_d.doctor_id; + +SELECT + lt.test_id, + lt.test_name, + lt.description, + lt.cost, + plt.test_date, + plt.notes +FROM lab_tests lt + JOIN performed_lab_tests plt ON lt.test_id = plt.test_id +WHERE plt.patient_id = 4 AND plt.test_date = '2026-06-16'; + + Index: backend/src/main/resources/db.migration/V7__Update_Billing_Admin_Names.sql =================================================================== --- backend/src/main/resources/db.migration/V7__Update_Billing_Admin_Names.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/V7__Update_Billing_Admin_Names.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,1 @@ + Index: backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql =================================================================== --- backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,133 @@ +-- schema change: NO_SHOW must be added to the existing status constraint before +-- anything below can ever set it +ALTER TABLE appointments DROP CONSTRAINT appointments_status_chk; +ALTER TABLE appointments ADD CONSTRAINT appointments_status_chk + CHECK (status IN ('SCHEDULED','COMPLETED','CANCELLED','IN_PROGRESS','NO_SHOW')); + +-- No generated column needed - we'll compute the time ranges directly in the triggers +-- This approach is simpler and avoids immutability constraints + +CREATE OR REPLACE FUNCTION is_valid_appointment_transition(p_old TEXT, p_new TEXT) +RETURNS BOOLEAN +LANGUAGE sql +IMMUTABLE +AS $$ +SELECT CASE + WHEN p_old = p_new THEN TRUE + WHEN p_old = 'SCHEDULED' AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN TRUE + WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN TRUE + ELSE FALSE + END; +$$; + +CREATE OR REPLACE FUNCTION trigger_appointments_enforce() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +DECLARE + v_combined_datetime TIMESTAMP; +BEGIN + v_combined_datetime := NEW.appointment_date::TIMESTAMP + NEW.appointment_time; + + IF TG_OP = 'INSERT' AND v_combined_datetime < NOW() THEN + RAISE EXCEPTION 'Cannot schedule appointment in the past (appointment_date=%, appointment_time=%)', + NEW.appointment_date, NEW.appointment_time; + END IF; + + IF TG_OP = 'UPDATE' THEN + IF NOT is_valid_appointment_transition(OLD.status, NEW.status) THEN + RAISE EXCEPTION 'Appointment status cannot transition from % to %', + OLD.status, NEW.status; + END IF; + + IF NEW.status = 'COMPLETED' AND v_combined_datetime > NOW() THEN + RAISE EXCEPTION 'Cannot mark appointment COMPLETED before its scheduled time (scheduled for %)', + v_combined_datetime; + END IF; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trigger_appointments_enforce ON appointments; +CREATE TRIGGER trigger_appointments_enforce + BEFORE INSERT OR UPDATE + ON appointments + FOR EACH ROW + EXECUTE FUNCTION trigger_appointments_enforce(); + +CREATE OR REPLACE FUNCTION t1_appointments_no_overlap() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +DECLARE + v_new_start TIMESTAMP; + v_new_end TIMESTAMP; +BEGIN + IF NEW.status NOT IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED') THEN + RETURN NEW; + END IF; + + v_new_start := NEW.appointment_date::TIMESTAMP + NEW.appointment_time; + v_new_end := v_new_start + INTERVAL '30 minutes'; + + -- Check for doctor double-booking + -- Overlap condition: existing_start < new_end AND new_start < existing_end + IF EXISTS ( + SELECT 1 FROM appointments a + WHERE a.doctor_id = NEW.doctor_id + AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED') + AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end + AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes') + AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id) + ) THEN + RAISE EXCEPTION 'Doctor % has overlapping appointment at %', NEW.doctor_id, NEW.appointment_date; + END IF; + + -- Check for patient double-booking + IF EXISTS ( + SELECT 1 FROM appointments a + WHERE a.patient_id = NEW.patient_id + AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED') + AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end + AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes') + AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id) + ) THEN + RAISE EXCEPTION 'Patient % has overlapping appointment at %', NEW.patient_id, NEW.appointment_date; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trigger_appointments_no_overlap ON appointments; +CREATE TRIGGER trigger_appointments_no_overlap + BEFORE INSERT OR UPDATE + ON appointments + FOR EACH ROW + EXECUTE FUNCTION t1_appointments_no_overlap(); + +CREATE OR REPLACE PROCEDURE job_mark_no_show() +LANGUAGE plpgsql +AS $$ +BEGIN + UPDATE appointments + SET status = 'NO_SHOW' + WHERE status = 'SCHEDULED' + AND (appointment_date::TIMESTAMP + appointment_time) < (NOW() - INTERVAL '45 minutes'); +END; +$$; + +CREATE OR REPLACE VIEW v_overdue_appointments AS +SELECT + a.appointment_id, a.patient_id, p.first_name, p.last_name, + a.doctor_id, d.first_name AS doctor_first_name, d.last_name AS doctor_last_name, + a.appointment_date, a.appointment_time, a.status, + NOW() - (a.appointment_date::TIMESTAMP + a.appointment_time) AS time_overdue +FROM appointments a + JOIN patients p ON a.patient_id = p.patient_id + JOIN doctors d ON a.doctor_id = d.doctor_id +WHERE a.status = 'SCHEDULED' + AND (a.appointment_date::TIMESTAMP + a.appointment_time) < (NOW() - INTERVAL '45 minutes') +ORDER BY time_overdue DESC; Index: backend/src/main/resources/db.migration/V9__mv_revenue_reporting.sql =================================================================== --- backend/src/main/resources/db.migration/V9__mv_revenue_reporting.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) +++ backend/src/main/resources/db.migration/V9__mv_revenue_reporting.sql (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -0,0 +1,60 @@ +-- Revenue reporting: monthly revenue, split by procedure vs. lab test. +-- Procedure revenue is attributed to a department, since `procedures` carries a +-- doctor_id directly on the catalog row. Lab revenue cannot be attributed to a +-- department: lab_tests has no doctor/department reference at all, and +-- billing_lab_tests links only to the catalog test_id, not to a specific performed +-- instance, so there is no reliable way to know which doctor/department administered +-- a billed test. Lab rows are reported at clinic-wide monthly granularity only +-- (department_id/department_name are NULL for those rows). + +CREATE MATERIALIZED VIEW mv_revenue_monthly AS +SELECT + DATE_TRUNC('month', b.payment_date)::DATE AS month, + dept.department_id, + dept.department_name, + 'PROCEDURE' AS revenue_type, + SUM(p.cost) AS revenue, + COUNT(DISTINCT b.bill_id) AS transaction_count +FROM billing b + JOIN billing_procedures bp ON bp.bill_id = b.bill_id + JOIN procedures p ON p.procedure_id = bp.procedure_id + JOIN doctors doc ON doc.doctor_id = p.doctor_id + JOIN departments dept ON dept.department_id = doc.department_id +WHERE b.payment_status = 'PAID' +GROUP BY DATE_TRUNC('month', b.payment_date), dept.department_id, dept.department_name + +UNION ALL + +SELECT + DATE_TRUNC('month', b.payment_date)::DATE AS month, + NULL AS department_id, + NULL AS department_name, + 'LAB' AS revenue_type, + SUM(lt.cost) AS revenue, + COUNT(DISTINCT b.bill_id) AS transaction_count +FROM billing b + JOIN billing_lab_tests blt ON blt.bill_id = b.bill_id + JOIN lab_tests lt ON lt.test_id = blt.test_id +WHERE b.payment_status = 'PAID' +GROUP BY DATE_TRUNC('month', b.payment_date); + +CREATE INDEX idx_mv_revenue_monthly_month ON mv_revenue_monthly (month, revenue_type); + +-- Background job: recomputes the materialized view. Designed to be invoked +-- periodically (nightly) via pg_cron, OS-level cron, or an external scheduler. +-- pg_cron was not available in this hosting environment to test automatic +-- scheduling directly, so this procedure is verified by manual invocation: +-- CALL medora_job_refresh_revenue_view(); +CREATE OR REPLACE PROCEDURE medora_job_refresh_revenue_view() + LANGUAGE plpgsql +AS $$ +BEGIN + REFRESH MATERIALIZED VIEW mv_revenue_monthly; +END; +$$; + +CREATE OR REPLACE VIEW v_current_month_revenue AS +SELECT month, department_id, department_name, revenue_type, revenue, transaction_count +FROM mv_revenue_monthly +WHERE month = DATE_TRUNC('month', CURRENT_DATE)::DATE +ORDER BY revenue_type, revenue DESC; Index: frontend/src/App.js =================================================================== --- frontend/src/App.js (revision b63d7b5e4b1c98d1e978322be8e809ceee661337) +++ frontend/src/App.js (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -1,5 +1,7 @@ import React, { useEffect, useState } from 'react'; -import { BrowserRouter as Router, Routes, Route, Navigate } from 'react-router-dom'; +import { BrowserRouter as Router, Routes, Route } from 'react-router-dom'; import Navbar from './components/Navbar'; +import ProtectedRoute from './components/ProtectedRoute'; +import Login from './pages/Login'; import Dashboard from './pages/Dashboard'; import PatientList from './pages/patients/PatientList'; @@ -27,11 +29,38 @@ function App() { const [user, setUser] = useState(null); + const [refresh, setRefresh] = useState(0); useEffect(() => { - const userStr = localStorage.getItem('user'); - if (userStr) { - setUser(JSON.parse(userStr)); - } - }, []); + const loadUser = () => { + const userStr = localStorage.getItem('user'); + if (userStr) { + try { + setUser(JSON.parse(userStr)); + } catch (err) { + console.error('Failed to parse user:', err); + setUser(null); + } + } else { + setUser(null); + } + }; + + loadUser(); + + // Listen for storage changes (other tabs/windows) + window.addEventListener('storage', loadUser); + + // Create a custom event listener for same-tab changes + const handleStorageChange = (e) => { + console.log('Storage change detected:', e.detail); + loadUser(); + }; + window.addEventListener('userStorageChange', handleStorageChange); + + return () => { + window.removeEventListener('storage', loadUser); + window.removeEventListener('userStorageChange', handleStorageChange); + }; + }, [refresh]); return ( @@ -77,7 +106,4 @@ } /> - {/* Referral Routes */} - } /> - {/* Lab Test Routes */} } /> @@ -92,5 +118,6 @@ } /> - + {/* Referral Routes */} + } /> Index: frontend/src/components/Navbar.js =================================================================== --- frontend/src/components/Navbar.js (revision b63d7b5e4b1c98d1e978322be8e809ceee661337) +++ frontend/src/components/Navbar.js (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -9,8 +9,17 @@ useEffect(() => { - const userStr = localStorage.getItem('user'); - if (userStr) { - setUser(JSON.parse(userStr)); - } + const loadUser = () => { + const userStr = localStorage.getItem('user'); + if (userStr) { + setUser(JSON.parse(userStr)); + } else { + setUser(null); + } + }; + + loadUser(); + + window.addEventListener('storage', loadUser); + return () => window.removeEventListener('storage', loadUser); }, []); @@ -19,4 +28,10 @@ localStorage.removeItem('user'); setUser(null); + + + window.dispatchEvent(new CustomEvent('userStorageChange', { + detail: { userData: null } + })); + navigate('/login'); }; @@ -30,142 +45,142 @@ return ( - ); } Index: frontend/src/pages/Login.js =================================================================== --- frontend/src/pages/Login.js (revision b63d7b5e4b1c98d1e978322be8e809ceee661337) +++ frontend/src/pages/Login.js (revision 20d16ca871b551a66a35600154523092bd0e1f10) @@ -47,5 +47,5 @@ localStorage.setItem('token', data.token); - localStorage.setItem('user', JSON.stringify({ + const userData = { userId: data.userId, patientId: data.patientId, @@ -55,4 +55,10 @@ firstName: data.firstName, lastName: data.lastName, + }; + localStorage.setItem('user', JSON.stringify(userData)); + + // Dispatch custom event to notify components on the same tab + window.dispatchEvent(new CustomEvent('userStorageChange', { + detail: { userData } })); @@ -60,5 +66,5 @@ setTimeout(() => { navigate('/'); - }, 1000); + }, 500); } catch (err) { setError(err.message || 'Login failed'); @@ -69,66 +75,66 @@ return ( -
-
-
-

Medora

-

Hospital Management System

-
- - {error && setError(null)} />} - {success && setSuccess(null)} />} - -
-
- - setUsername(e.target.value)} - placeholder="Enter your username or EMBG" - className="w-full px-4 py-3 border-2 border-gray-300 rounded-lg focus:outline-none focus:border-purple-500 transition" - disabled={loading} - /> +
+
+
+

Medora

+

Hospital Management System

-
- - setPassword(e.target.value)} - placeholder="Enter your password" - className="w-full px-4 py-3 border-2 border-gray-300 rounded-lg focus:outline-none focus:border-purple-500 transition" - disabled={loading} - /> + {error && setError(null)} />} + {success && setSuccess(null)} />} + + +
+ + setUsername(e.target.value)} + placeholder="Enter your username or EMBG" + className="w-full px-4 py-3 border-2 border-gray-300 rounded-lg focus:outline-none focus:border-purple-500 transition" + disabled={loading} + /> +
+ +
+ + setPassword(e.target.value)} + placeholder="Enter your password" + className="w-full px-4 py-3 border-2 border-gray-300 rounded-lg focus:outline-none focus:border-purple-500 transition" + disabled={loading} + /> +
+ + + + +
+

Demo Credentials:

+

+ Admin: username: admin | password: admin123 +

+

+ Doctor: username: ivan.stojanov@medora.com | password: doctor123 +

+

+ Patient: username: [EMBG] | password: password123 +

- - - - -
-

Demo Credentials:

-

- Admin: username: admin | password: admin123 -

-

- Doctor: username: ivan.stojanov@medora.com | password: doctor123 -

-

- Patient: username: [EMBG] | password: password123 -

-
); }