-- =============================================================================
-- Behavioral Health Intelligence (BHI) Layer - Postgres schema extension
-- =============================================================================
-- This file adds BHI tables to the existing `brain` database that the base
-- Economic Brain agent is creating. DO NOT run until the base Brain schema
-- is finalized. Then run: psql -d brain -f schemas/bhi_tables.sql
--
-- All tables are prefixed `bhi_` to avoid any collision with the base Brain.
-- Foreign keys are intentionally soft (no REFERENCES) where the target table
-- belongs to the base Brain, so this file can be applied independently.
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 1. Facilities master table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_facilities (
    facility_id              SERIAL PRIMARY KEY,
    ccn                      VARCHAR(20),           -- CMS Certification Number
    npi                      VARCHAR(20),           -- National Provider Identifier
    name                     TEXT NOT NULL,
    address                  TEXT,
    city                     TEXT,
    state                    TEXT,
    zip                      TEXT,
    county_fips              TEXT,
    lat                      DOUBLE PRECISION,
    lon                      DOUBLE PRECISION,
    facility_type            TEXT,                  -- IPF, PRTF, CMHC, SUD, acute, nursing_home, etc.
    ownership                TEXT,                  -- for-profit, non-profit, gov
    bed_count                INT,
    psych_bed_count          INT,
    pediatric_psych_bed_count INT,
    adolescent_unit          BOOLEAN,
    young_adult_unit         BOOLEAN,
    services_offered         TEXT[],
    populations_served       TEXT[],                -- ['adolescent','young_adult','adult','geriatric']
    payment_accepted         TEXT[],
    medicaid_accepted        BOOLEAN,
    accreditation            TEXT,
    opened_date              DATE,
    closed_date              DATE,
    last_verified            DATE,
    source                   TEXT,                  -- 'cms_ipfqr','samhsa_locator','nppes', etc.
    source_raw_id            INT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_state    ON bhi_facilities (state);
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_county   ON bhi_facilities (county_fips);
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_ccn      ON bhi_facilities (ccn);
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_npi      ON bhi_facilities (npi);
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_type     ON bhi_facilities (facility_type);
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_pops     ON bhi_facilities USING GIN (populations_served);

-- -----------------------------------------------------------------------------
-- 2. Facility quality measures (IPFQR, Care Compare)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_facility_quality (
    id                       SERIAL PRIMARY KEY,
    facility_id              INT REFERENCES bhi_facilities(facility_id) ON DELETE CASCADE,
    measure_id               TEXT,                  -- e.g. HBIPS-2, SUB-3, SMD, TOB-3
    measure_name             TEXT,
    value                    NUMERIC,
    benchmark                NUMERIC,
    period                   TEXT,                  -- '2024Q1', 'FY2024'
    reported_at              DATE,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_quality_facility ON bhi_facility_quality (facility_id);
CREATE INDEX IF NOT EXISTS idx_bhi_quality_measure  ON bhi_facility_quality (measure_id);

-- -----------------------------------------------------------------------------
-- 3. Facility financials from Medicare Cost Reports
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_facility_financials (
    id                       SERIAL PRIMARY KEY,
    facility_id              INT REFERENCES bhi_facilities(facility_id) ON DELETE CASCADE,
    year                     INT,
    medicare_discharges      INT,
    medicaid_discharges      INT,
    psych_discharges         INT,
    psych_los_avg            NUMERIC,
    psych_revenue            BIGINT,
    psych_costs              BIGINT,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_financials_facility ON bhi_facility_financials (facility_id);
CREATE INDEX IF NOT EXISTS idx_bhi_financials_year     ON bhi_facility_financials (year);

-- -----------------------------------------------------------------------------
-- 4. Demand indicators (CDC WONDER, BRFSS, YRBSS, IDEA, NSCH)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_demand_indicators (
    id                       SERIAL PRIMARY KEY,
    geo_type                 TEXT,                  -- 'state','county','msa','district'
    geo_code                 TEXT,                  -- FIPS or code
    measure                  TEXT,                  -- 'suicide_rate','overdose_rate','depression_pct', etc.
    age_bracket              TEXT,                  -- '13-17','18-25','all'
    period                   TEXT,
    value                    NUMERIC,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_demand_geo      ON bhi_demand_indicators (geo_type, geo_code);
CREATE INDEX IF NOT EXISTS idx_bhi_demand_measure  ON bhi_demand_indicators (measure);
CREATE INDEX IF NOT EXISTS idx_bhi_demand_age      ON bhi_demand_indicators (age_bracket);

-- -----------------------------------------------------------------------------
-- 5. Workforce (BLS OES)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_workforce (
    id                       SERIAL PRIMARY KEY,
    msa_code                 TEXT,
    msa_name                 TEXT,
    occupation_code          TEXT,                  -- SOC code, e.g. 29-1223 (psychiatrists)
    occupation_title         TEXT,
    employment               INT,
    annual_wage_median       NUMERIC,
    annual_wage_mean         NUMERIC,
    period                   TEXT,                  -- 'May2024'
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_workforce_msa  ON bhi_workforce (msa_code);
CREATE INDEX IF NOT EXISTS idx_bhi_workforce_occ  ON bhi_workforce (occupation_code);

-- -----------------------------------------------------------------------------
-- 6. HRSA HPSA mental health shortage areas
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_shortages (
    id                       SERIAL PRIMARY KEY,
    hpsa_id                  TEXT,
    state                    TEXT,
    county_fips              TEXT,
    score                    INT,                   -- HPSA score 0-25 (higher = worse shortage)
    population_served        INT,
    designated_date          DATE,
    withdrawn_date           DATE,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_shortages_state  ON bhi_shortages (state);
CREATE INDEX IF NOT EXISTS idx_bhi_shortages_county ON bhi_shortages (county_fips);
CREATE INDEX IF NOT EXISTS idx_bhi_shortages_score  ON bhi_shortages (score);

-- -----------------------------------------------------------------------------
-- 7. State RTF (Residential Treatment Facility) licensing data
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_rtf_licensing (
    id                       SERIAL PRIMARY KEY,
    state                    TEXT,
    license_number           TEXT,
    facility_name            TEXT,
    facility_type            TEXT,
    capacity                 INT,
    populations              TEXT[],
    services                 TEXT[],
    inspection_date          DATE,
    violations               JSONB,
    status                   TEXT,
    opened_date              DATE,
    closed_date              DATE,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_rtf_state ON bhi_rtf_licensing (state);
CREATE INDEX IF NOT EXISTS idx_bhi_rtf_name  ON bhi_rtf_licensing (facility_name);

-- -----------------------------------------------------------------------------
-- 8. Policy events (Medicaid rules, state legislation)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_policy_events (
    id                       SERIAL PRIMARY KEY,
    event_type               TEXT,                  -- 'medicaid_rule','state_law','federal_rule'
    state                    TEXT,
    title                    TEXT,
    summary                  TEXT,
    effective_date           DATE,
    url                      TEXT,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_policy_state    ON bhi_policy_events (state);
CREATE INDEX IF NOT EXISTS idx_bhi_policy_eff_date ON bhi_policy_events (effective_date);

-- -----------------------------------------------------------------------------
-- 9. Crisis calls / EMS transports (NEMSIS aggregates)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bhi_crisis_calls (
    id                       SERIAL PRIMARY KEY,
    state                    TEXT,
    county_fips              TEXT,
    period                   TEXT,
    call_count               INT,
    mental_health_calls      INT,
    transport_outcomes       JSONB,
    source                   TEXT,
    created_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bhi_crisis_state  ON bhi_crisis_calls (state);
CREATE INDEX IF NOT EXISTS idx_bhi_crisis_county ON bhi_crisis_calls (county_fips);

COMMIT;

-- =============================================================================
-- Verify
-- =============================================================================
-- \dt bhi_*
-- SELECT tablename FROM pg_tables WHERE tablename LIKE 'bhi_%' ORDER BY tablename;
