-- ISP (Individual Support Plan) Schema - Per 42 CFR 483.440

CREATE TABLE IF NOT EXISTS isps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    consumer_id INT NOT NULL,
    title VARCHAR(255) DEFAULT 'Individual Support Plan',
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status ENUM('draft','active','under_review','expired','archived') DEFAULT 'draft',
    case_manager_id INT,
    qidp_id INT,
    review_date DATE,
    next_review_date DATE,
    notes TEXT,
    metadata JSON,
    created_by INT,
    approved_by INT,
    approved_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (organization_id, consumer_id, status),
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE,
    FOREIGN KEY (case_manager_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (qidp_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS isp_goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    isp_id INT NOT NULL,
    organization_id INT NOT NULL,
    area ENUM('daily_living','community','health_safety','social','employment','self_advocacy','other') NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    measurable_objective TEXT,
    baseline TEXT,
    target_criteria TEXT,
    target_date DATE,
    review_frequency ENUM('weekly','biweekly','monthly','quarterly') DEFAULT 'monthly',
    status ENUM('active','achieved','modified','discontinued') DEFAULT 'active',
    progress_percent INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (isp_id) REFERENCES isps(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS isp_goal_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    goal_id INT NOT NULL,
    organization_id INT NOT NULL,
    date DATE NOT NULL,
    value DECIMAL(10,2),
    notes TEXT,
    recorded_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (goal_id) REFERENCES isp_goals(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- MAR (Medication Administration Record) Schema - Per 9 CSR 45-5.010(4)

CREATE TABLE IF NOT EXISTS medications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    consumer_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    generic_name VARCHAR(255),
    dosage VARCHAR(100) NOT NULL,
    route ENUM('oral','topical','injection','inhaled','sublingual','rectal','ophthalmic','otic','nasal','transdermal') DEFAULT 'oral',
    frequency VARCHAR(100) NOT NULL,
    times JSON,
    prescribing_physician VARCHAR(200),
    pharmacy VARCHAR(200),
    pharmacy_phone VARCHAR(20),
    rx_number VARCHAR(50),
    purpose TEXT,
    special_instructions TEXT,
    start_date DATE,
    end_date DATE,
    is_prn TINYINT(1) DEFAULT 0,
    prn_reason TEXT,
    status ENUM('active','discontinued','hold','pending') DEFAULT 'active',
    discontinued_date DATE,
    discontinued_reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (organization_id, consumer_id, status),
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS mar_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    consumer_id INT NOT NULL,
    medication_id INT NOT NULL,
    scheduled_date DATE NOT NULL,
    scheduled_time TIME NOT NULL,
    status ENUM('administered','missed','refused','held','not_available','late') DEFAULT 'administered',
    administered_at DATETIME,
    administered_by INT,
    witness_by INT,
    notes TEXT,
    refusal_reason TEXT,
    hold_reason TEXT,
    vital_signs JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX (organization_id, consumer_id, scheduled_date),
    INDEX (medication_id, scheduled_date),
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE,
    FOREIGN KEY (medication_id) REFERENCES medications(id) ON DELETE CASCADE,
    FOREIGN KEY (administered_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (witness_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS mar_count_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    medication_id INT NOT NULL,
    count_date DATE NOT NULL,
    shift ENUM('am','pm','night') NOT NULL,
    count_amount INT NOT NULL,
    counted_by INT NOT NULL,
    verified_by INT,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (medication_id) REFERENCES medications(id) ON DELETE CASCADE,
    FOREIGN KEY (counted_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;