-- Incidents Schema - Per 9 CSR 10-5.200, Missouri Abuse/Neglect reporting

CREATE TABLE IF NOT EXISTS incidents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    consumer_id INT NOT NULL,
    location_id INT,
    incident_number VARCHAR(50),
    type ENUM('fall','medication_error','behavioral','injury','elopement','property_damage','abuse_neglect','rights_violation','choking','seizure','hospitalization','death','other') NOT NULL,
    severity ENUM('minor','moderate','serious','critical') NOT NULL,
    date_occurred DATETIME NOT NULL,
    date_reported DATETIME DEFAULT CURRENT_TIMESTAMP,
    location_detail VARCHAR(255),
    description TEXT NOT NULL,
    immediate_actions TEXT,
    injuries TEXT,
    witnesses TEXT,
    reported_by INT NOT NULL,
    status ENUM('open','under_investigation','pending_review','resolved','closed') DEFAULT 'open',
    resolution TEXT,
    resolved_by INT,
    resolved_at DATETIME,
    -- DMH reporting requirements
    dmh_reportable TINYINT(1) DEFAULT 0,
    dmh_reported_at DATETIME,
    dmh_report_number VARCHAR(50),
    guardian_notified TINYINT(1) DEFAULT 0,
    guardian_notified_at DATETIME,
    law_enforcement_notified TINYINT(1) DEFAULT 0,
    -- Follow-up
    follow_up_required TINYINT(1) DEFAULT 0,
    follow_up_date DATE,
    follow_up_notes TEXT,
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (organization_id, status, date_occurred),
    INDEX (consumer_id, date_occurred),
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (reported_by) REFERENCES users(id),
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS incident_follow_ups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    incident_id INT NOT NULL,
    organization_id INT NOT NULL,
    note TEXT NOT NULL,
    action_taken TEXT,
    created_by INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Compliance Schema - Per 9 CSR 45-5.010, 9 CSR 40-1, 42 CFR 483

CREATE TABLE IF NOT EXISTS compliance_standards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    regulation_source VARCHAR(100),
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_audits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    title VARCHAR(255),
    location_id INT,
    audit_type VARCHAR(50) DEFAULT 'internal',
    audit_date DATE,
    auditor_id INT,
    status ENUM('scheduled','in_progress','completed','cancelled') DEFAULT 'scheduled',
    overall_score DECIMAL(5,2),
    findings_count INT DEFAULT 0,
    critical_findings INT DEFAULT 0,
    notes TEXT,
    report_json JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (auditor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_findings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    audit_id INT NOT NULL,
    organization_id INT NOT NULL,
    standard_id INT,
    severity ENUM('observation','minor','major','critical') NOT NULL,
    description TEXT NOT NULL,
    corrective_action TEXT,
    due_date DATE,
    status ENUM('open','in_progress','resolved','verified','overdue') DEFAULT 'open',
    resolution_notes TEXT,
    resolved_date DATE,
    verified_by INT,
    verified_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (audit_id) REFERENCES compliance_audits(id) ON DELETE CASCADE,
    FOREIGN KEY (standard_id) REFERENCES compliance_standards(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    location_id INT,
    standard_id INT,
    score DECIMAL(5,2) NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    details JSON,
    calculated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX (organization_id, period_start),
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB;