-- AWV + RPM Pilot — Annual Wellness Form
-- Run once to create the database and tables.

CREATE DATABASE IF NOT EXISTS awv_rpm_pilot
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE awv_rpm_pilot;

-- ─────────────────────────────────────────────────────────────────────────────
-- Main intake table  (one row per AWV intake session)
-- Array/multi-select values are stored as JSON strings.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS awv_intake (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- ── S1  Opening + Consent ─────────────────────────────────────────────
    s1_agrees               VARCHAR(20),
    s1_alert                VARCHAR(20),
    s1_interpreter          VARCHAR(20),
    s1_caregiver_present    VARCHAR(20),
    s1_continue_awv         VARCHAR(30),
    s1_opening_notes        TEXT,

    -- ── S2  Identity + Caregiver ──────────────────────────────────────────
    s2_address              VARCHAR(255),
    s2_language             VARCHAR(80),
    s2_living               VARCHAR(60),
    s2_caregiver            VARCHAR(180),
    s2_emergency            VARCHAR(180),
    s2_permission_caregiver VARCHAR(20),

    -- ── S3  Health + Main Concern ─────────────────────────────────────────
    s3_health_rating        VARCHAR(20),
    s3_health_compare       VARCHAR(20),
    s3_recent_er            VARCHAR(20),
    s3_pain                 TINYINT UNSIGNED,      -- 0-10
    s3_main_concern         TEXT,
    s3_patient_goal         TEXT,

    -- ── S4  History + Meds ────────────────────────────────────────────────
    s4_conditions           JSON,                  -- array of strings
    s4_allergies            TEXT,
    s4_med_verified         VARCHAR(20),
    s4_blood_thinner        VARCHAR(10),
    s4_opioid               VARCHAR(10),
    s4_benzo                VARCHAR(10),
    s4_med_cost_confusion   VARCHAR(10),
    s4_med_list             TEXT,
    s4_providers            TEXT,

    -- ── S5  Depression / Cognition / Falls ───────────────────────────────
    s5_phq2_q1              TINYINT UNSIGNED,      -- 0-3
    s5_phq2_q2              TINYINT UNSIGNED,      -- 0-3
    s5_phq9                 TINYINT UNSIGNED,      -- 0-27 (nullable)
    s5_self_harm            VARCHAR(10),
    s5_recall               TINYINT UNSIGNED,      -- 0-3
    s5_clock                VARCHAR(30),
    s5_memory_concern       VARCHAR(10),
    s5_med_money_lost       VARCHAR(10),
    s5_fall_12m             VARCHAR(10),
    s5_fall_injury          VARCHAR(10),
    s5_unsteady             VARCHAR(10),
    s5_assistive            VARCHAR(30),
    s5_adl                  TEXT,

    -- ── S6  Lifestyle + Counseling ────────────────────────────────────────
    s6_sex                  VARCHAR(20),
    s6_tobacco              VARCHAR(20),
    s6_quit_interest        VARCHAR(20),
    s6_audit_freq           TINYINT UNSIGNED,      -- 0-4
    s6_audit_qty            TINYINT UNSIGNED,      -- 0-4
    s6_audit_binge          TINYINT UNSIGNED,      -- 0-4
    s6_alcohol_red_flags    JSON,                  -- array
    s6_alcohol_open         VARCHAR(20),
    s6_drug_use             VARCHAR(10),
    s6_height_in            DECIMAL(5,1),
    s6_weight_lb            DECIMAL(6,1),
    s6_weight_permission    VARCHAR(10),
    s6_unintentional_loss   VARCHAR(10),
    s6_weight_goal          VARCHAR(60),
    s6_wants_acp            VARCHAR(20),
    s6_has_acp              VARCHAR(20),
    s6_surrogate            VARCHAR(180),
    s6_sdoh                 JSON,                  -- array

    -- ── S7  Preventive + Vaccines ─────────────────────────────────────────
    -- Stored as a single JSON object  { "Colorectal cancer": { status, note }, … }
    s7_preventive_services  JSON,

    -- ── S8  Provider Report ───────────────────────────────────────────────
    s8_report_text          LONGTEXT,
    s8_routed_to_provider   TINYINT(1) NOT NULL DEFAULT 0,
    s8_routed_at            DATETIME,

    -- ── Computed / flags ──────────────────────────────────────────────────
    flags_json              JSON,                  -- snapshot of flag array at submit time

    -- ── Link back to RPM pilot record (P0-P4 emr_id) ─────────────────────
    emr_id                  VARCHAR(80),

    INDEX idx_emr_id   (emr_id),
    INDEX idx_created  (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
