-- Patient Safety Features Migration
-- Includes Clinical Alerts, Duplicate Patient Detection, Critical Results, and Sentinel Events

-- Clinical Alerts table for drug interactions and other clinical warnings
CREATE TABLE clinical_alerts
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    patient_id          UUID        NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    alert_type          VARCHAR(50) NOT NULL, -- DRUG_INTERACTION, ALLERGY, CONTRAINDICATION, etc.
    severity            VARCHAR(20) NOT NULL CHECK (severity IN ('INFO', 'WARNING', 'CRITICAL')),
    title               VARCHAR(255) NOT NULL,
    description         TEXT        NOT NULL,
    medication_name     VARCHAR(255),
    related_prescription_id UUID     REFERENCES prescriptions (id) ON DELETE SET NULL,
    acknowledged        BOOLEAN     DEFAULT FALSE,
    acknowledged_at     TIMESTAMP,
    acknowledged_by     UUID                REFERENCES users (id),
    created_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at         TIMESTAMP,
    resolved_by         UUID                REFERENCES users (id)
);

-- Duplicate Patient Detection records
CREATE TABLE duplicate_patient_records
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    primary_patient_id  UUID        NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    duplicate_patient_id UUID       NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    match_score         DECIMAL(5,2) NOT NULL, -- 0-100 match confidence
    status              VARCHAR(20) DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'CONFIRMED', 'RESOLVED', 'REJECTED')),
    reviewed_by         UUID                REFERENCES users (id),
    reviewed_at         TIMESTAMP,
    review_notes        TEXT,
    created_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(primary_patient_id, duplicate_patient_id)
);

-- Match reasons for duplicate records
CREATE TABLE duplicate_match_reasons
(
    id                   UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    duplicate_record_id  UUID        NOT NULL REFERENCES duplicate_patient_records (id) ON DELETE CASCADE,
    reason               TEXT        NOT NULL
);

-- Critical Results Management
CREATE TABLE critical_results
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    lab_result_id       UUID        NOT NULL REFERENCES lab_results (id) ON DELETE CASCADE,
    patient_id          UUID        NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    doctor_id           UUID        NOT NULL REFERENCES doctors (id),
    criticality_level   VARCHAR(20) NOT NULL CHECK (criticality_level IN ('URGENT', 'CRITICAL', 'CRITICAL_PANIC')),
    test_name           VARCHAR(255) NOT NULL,
    result_value        VARCHAR(255),
    reference_range     VARCHAR(100),
    clinical_significance TEXT,
    acknowledgment_required BOOLEAN DEFAULT TRUE,
    acknowledged        BOOLEAN     DEFAULT FALSE,
    acknowledged_at     TIMESTAMP,
    acknowledged_by     UUID                REFERENCES users (id),
    acknowledgment_method VARCHAR(50), -- CALL, EMAIL, SMS, IN_PERSON
    follow_up_required  BOOLEAN     DEFAULT TRUE,
    follow_up_status    VARCHAR(50),
    notified_at         TIMESTAMP,
    created_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Sentinel Event Reporting
CREATE TABLE sentinel_events
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    event_type          VARCHAR(100) NOT NULL, -- DEATH, SURGICAL_ERROR, MEDICATION_ERROR, etc.
    severity            VARCHAR(20) NOT NULL CHECK (severity IN ('SEVERE', 'MODERATE', 'MILD')),
    patient_id          UUID                REFERENCES patients (id) ON DELETE SET NULL,
    doctor_id           UUID                REFERENCES doctors (id),
    appointment_id      UUID                REFERENCES appointments (id),
    description         TEXT        NOT NULL,
    location            VARCHAR(255), -- Where event occurred
    occurred_at         TIMESTAMP   NOT NULL,
    reported_at         TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reported_by         UUID        NOT NULL REFERENCES users (id),
    status              VARCHAR(20) DEFAULT 'REPORTED' CHECK (status IN ('REPORTED', 'UNDER_INVESTIGATION', 'RESOLVED', 'CLOSED')),
    investigation_notes TEXT,
    root_cause_analysis TEXT,
    corrective_action   TEXT,
    resolved_at         TIMESTAMP,
    resolved_by         UUID                REFERENCES users (id),
    created_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_clinical_alerts_patient_id ON clinical_alerts (patient_id);
CREATE INDEX idx_clinical_alerts_acknowledged ON clinical_alerts (acknowledged) WHERE acknowledged = FALSE;
CREATE INDEX idx_clinical_alerts_severity ON clinical_alerts (severity);
CREATE INDEX idx_clinical_alerts_created_at ON clinical_alerts (created_at DESC);

CREATE INDEX idx_duplicate_patient_records_primary ON duplicate_patient_records (primary_patient_id);
CREATE INDEX idx_duplicate_patient_records_duplicate ON duplicate_patient_records (duplicate_patient_id);
CREATE INDEX idx_duplicate_patient_records_status ON duplicate_patient_records (status);
CREATE INDEX idx_duplicate_patient_records_created_at ON duplicate_patient_records (created_at DESC);

CREATE INDEX idx_critical_results_patient_id ON critical_results (patient_id);
CREATE INDEX idx_critical_results_doctor_id ON critical_results (doctor_id);
CREATE INDEX idx_critical_results_acknowledged ON critical_results (acknowledged) WHERE acknowledged = FALSE;
CREATE INDEX idx_critical_results_criticality ON critical_results (criticality_level);
CREATE INDEX idx_critical_results_created_at ON critical_results (created_at DESC);

CREATE INDEX idx_sentinel_events_patient_id ON sentinel_events (patient_id);
CREATE INDEX idx_sentinel_events_doctor_id ON sentinel_events (doctor_id);
CREATE INDEX idx_sentinel_events_status ON sentinel_events (status);
CREATE INDEX idx_sentinel_events_event_type ON sentinel_events (event_type);
CREATE INDEX idx_sentinel_events_occurred_at ON sentinel_events (occurred_at DESC);
CREATE INDEX idx_sentinel_events_reported_at ON sentinel_events (reported_at DESC);

