-- Add doctor_id to clinical_alerts table
-- This allows alerts to be assigned to the treating doctor
-- Doctors will only see alerts for their own patients

ALTER TABLE clinical_alerts
ADD COLUMN doctor_id UUID REFERENCES doctors (id) ON DELETE SET NULL;

-- Create index for better query performance when filtering by doctor
CREATE INDEX idx_clinical_alerts_doctor_id ON clinical_alerts (doctor_id);

-- Update existing alerts to assign them to the patient's most recent treating doctor
-- Based on appointments or prescriptions
UPDATE clinical_alerts ca
SET doctor_id = (
    SELECT a.doctor_id
    FROM appointments a
    WHERE a.patient_id = ca.patient_id
      AND a.status IN ('SCHEDULED', 'CONFIRMED', 'COMPLETED')
      AND a.doctor_id IS NOT NULL
    ORDER BY a.created_at DESC
    LIMIT 1
)
WHERE ca.doctor_id IS NULL
  AND EXISTS (
      SELECT 1 FROM appointments a WHERE a.patient_id = ca.patient_id AND a.doctor_id IS NOT NULL
  );

-- If no appointment exists, try to assign from prescriptions
UPDATE clinical_alerts ca
SET doctor_id = (
    SELECT p.doctor_id
    FROM prescriptions p
    WHERE p.patient_id = ca.patient_id
      AND p.doctor_id IS NOT NULL
    ORDER BY p.created_at DESC
    LIMIT 1
)
WHERE ca.doctor_id IS NULL
  AND EXISTS (
      SELECT 1 FROM prescriptions p WHERE p.patient_id = ca.patient_id AND p.doctor_id IS NOT NULL
  );

