-- ============================================================
-- Benefits Navigator — MySQL Database Schema
-- Engine: InnoDB · Charset: utf8mb4 · Collation: utf8mb4_unicode_ci
-- ============================================================

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

USE benefits_navigator;

-- ============================================================
-- TABLE: users
-- Anonymous sessions are allowed (user_id can be NULL elsewhere)
-- ============================================================
CREATE TABLE users (
    id                  INT UNSIGNED        AUTO_INCREMENT PRIMARY KEY,
    uuid                VARCHAR(36)         NOT NULL UNIQUE COMMENT 'Public-facing UUID',
    email               VARCHAR(255)        NULL UNIQUE,
    phone               VARCHAR(20)         NULL,
    first_name          VARCHAR(100)        NULL,
    last_name           VARCHAR(100)        NULL,
    preferred_language  VARCHAR(10)         NOT NULL DEFAULT 'en' COMMENT 'BCP47 code: en, es, zh, etc.',
    state               CHAR(2)             NULL COMMENT 'Two-letter state code',
    zip_code            VARCHAR(10)         NULL,
    password_hash       VARCHAR(255)        NULL,
    is_verified         TINYINT(1)          NOT NULL DEFAULT 0,
    last_login_at       TIMESTAMP           NULL,
    created_at          TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email     (email),
    INDEX idx_state     (state),
    INDEX idx_zip       (zip_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: federal_poverty_levels
-- Updated annually — seed data provided below
-- ============================================================
CREATE TABLE federal_poverty_levels (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    year            YEAR            NOT NULL,
    household_size  TINYINT         NOT NULL,
    annual_amount   DECIMAL(10,2)   NOT NULL,
    monthly_amount  DECIMAL(10,2)   GENERATED ALWAYS AS (annual_amount / 12) STORED,
    UNIQUE KEY uk_year_size (year, household_size)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed: 2025 FPL (48 contiguous states + DC)
INSERT INTO federal_poverty_levels (year, household_size, annual_amount) VALUES
(2025, 1,  15060.00),
(2025, 2,  20440.00),
(2025, 3,  25820.00),
(2025, 4,  31200.00),
(2025, 5,  36580.00),
(2025, 6,  41960.00),
(2025, 7,  47340.00),
(2025, 8,  52720.00);


-- ============================================================
-- TABLE: benefits_programs
-- Master list of all assistance programs
-- ============================================================
CREATE TABLE benefits_programs (
    id                      INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    program_code            VARCHAR(50)     NOT NULL UNIQUE COMMENT 'e.g. SNAP, MEDICAID, WIC',
    name                    VARCHAR(255)    NOT NULL,
    short_name              VARCHAR(100)    NULL,
    description             TEXT            NULL,
    category                ENUM(
                                'food','health','housing','childcare',
                                'utilities','education','employment','cash','other'
                            ) NOT NULL DEFAULT 'other',
    program_type            ENUM('federal','state','local','nonprofit') NOT NULL DEFAULT 'federal',
    state                   CHAR(2)         NULL COMMENT 'NULL = federal (available all states)',
    apply_url               VARCHAR(500)    NULL,
    official_site_url       VARCHAR(500)    NULL,
    phone_number            VARCHAR(30)     NULL,
    income_limit_pct_fpl    DECIMAL(6,2)    NULL COMMENT '130 means 130% of FPL',
    max_annual_income_2     DECIMAL(12,2)   NULL COMMENT 'Calculated max for 2-person HH (cache)',
    benefit_period          ENUM('monthly','annual','one_time','variable') NOT NULL DEFAULT 'monthly',
    min_benefit_amount      DECIMAL(10,2)   NULL,
    max_benefit_amount      DECIMAL(10,2)   NULL,
    priority_level          ENUM('apply_now','high','medium','low') NOT NULL DEFAULT 'medium',
    citizenship_required    TINYINT(1)      NOT NULL DEFAULT 1,
    min_household_size      TINYINT         NOT NULL DEFAULT 1,
    max_household_size      TINYINT         NULL,
    processing_days_min     TINYINT         NULL,
    processing_days_max     TINYINT         NULL,
    is_active               TINYINT(1)      NOT NULL DEFAULT 1,
    created_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category      (category),
    INDEX idx_state         (state),
    INDEX idx_active        (is_active),
    INDEX idx_type          (program_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed: Core Federal Programs
INSERT INTO benefits_programs
    (program_code, name, short_name, category, program_type, apply_url, income_limit_pct_fpl,
     max_annual_income_2, benefit_period, min_benefit_amount, max_benefit_amount,
     priority_level, processing_days_min, processing_days_max)
VALUES
    ('SNAP',        'Supplemental Nutrition Assistance Program',  'Basic Food',     'food',    'federal',
     'https://www.washingtonconnection.org/WAPPLE/', 130, 26973, 'monthly', 23, 1756, 'apply_now', 7,  30),
    ('MEDICAID',    'Medicaid / Apple Health',                    'Apple Health',   'health',  'federal',
     'https://www.washingtonconnection.org/WAPPLE/', 138, NULL,  'monthly', 0,  0,    'apply_now', 1,  45),
    ('WIC',         'Women, Infants, and Children',               'WIC',            'food',    'federal',
     'https://www.doh.wa.gov/YouandYourFamily/WIC',  185, NULL,  'monthly', 50, 500,  'apply_now', 3,  14),
    ('LIHEAP',      'Low Income Home Energy Assistance Program',  'LIHEAP',         'utilities','federal',
     'https://www.commerce.wa.gov/growing-the-economy/energy/low-income-home-energy-assistance/', 150, NULL, 'annual', 200, 1200, 'high', 30, 60),
    ('CCAP',        'Child Care Assistance Program',              'CCAP',           'childcare','state',
     'https://www.washingtonconnection.org/WAPPLE/', 200, NULL,  'monthly', 0,  2000, 'high', 14, 45);


-- ============================================================
-- TABLE: program_eligibility_rules
-- Structured rules for automated pre-screening
-- ============================================================
CREATE TABLE program_eligibility_rules (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    program_id      INT UNSIGNED    NOT NULL,
    rule_field      VARCHAR(100)    NOT NULL COMMENT 'e.g. annual_income, household_size, has_children',
    operator        ENUM('lt','lte','gt','gte','eq','between','in_list','not_null','is_true','is_false') NOT NULL,
    value_1         VARCHAR(255)    NULL COMMENT 'Primary comparison value or JSON array for in_list',
    value_2         VARCHAR(255)    NULL COMMENT 'Upper bound for between operator',
    is_disqualifier TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '1 = failing this rule disqualifies',
    description     VARCHAR(255)    NULL,
    FOREIGN KEY fk_rule_program (program_id) REFERENCES benefits_programs(id) ON DELETE CASCADE,
    INDEX idx_program (program_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SNAP eligibility rules example
INSERT INTO program_eligibility_rules (program_id, rule_field, operator, value_1, description)
VALUES
    (1, 'income_pct_fpl', 'lte',   '130',  'Gross income must be at or below 130% FPL'),
    (1, 'household_size', 'gte',   '1',    'Must have at least 1 household member'),
    (1, 'citizenship',    'eq',    '"citizen"', 'Must be US citizen or qualified immigrant');


-- ============================================================
-- TABLE: program_documents
-- Documents required per program
-- ============================================================
CREATE TABLE program_documents (
    id                  INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    program_id          INT UNSIGNED    NOT NULL,
    document_slug       VARCHAR(100)    NOT NULL COMMENT 'e.g. photo_id, pay_stub, proof_of_address',
    document_name       VARCHAR(255)    NOT NULL,
    document_description TEXT           NULL,
    accepted_types      JSON            NULL COMMENT 'Array of accepted document types',
    is_required         TINYINT(1)      NOT NULL DEFAULT 1,
    sample_pdf_url      VARCHAR(500)    NULL COMMENT 'URL to a sample filled form PDF',
    tips                TEXT            NULL,
    avoid_list          JSON            NULL COMMENT 'Array of things to avoid',
    sort_order          TINYINT         NOT NULL DEFAULT 0,
    FOREIGN KEY fk_doc_program (program_id) REFERENCES benefits_programs(id) ON DELETE CASCADE,
    INDEX idx_program (program_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO program_documents (program_id, document_slug, document_name, document_description, accepted_types, is_required, tips, sort_order)
VALUES
    (1, 'photo_id',       'Photo ID',                     'Government-issued photo ID',
     '["WA Driver License","State ID","Passport","Military ID"]', 1, 'Must not be expired', 1),
    (1, 'pay_stub',       'Recent pay stubs (last 30 days)', 'Proof of current income for all working household members',
     '["Pay stub","Bank statement","1099","Employer letter"]', 1, 'Must show gross income amount', 2),
    (1, 'proof_of_address','Proof of WA residency',        'Document showing your current Washington state address',
     '["Utility bill","Lease agreement","Bank statement","Official mail"]', 1, 'Must be within last 60 days', 3),
    (1, 'ssn',            'Social Security numbers',       'SSN for every person in your household',
     '["SSN card","W-2","Tax return","SSA letter","Medicare card"]', 1, 'The physical card is not required — just the number', 4);


-- ============================================================
-- TABLE: eligibility_checks
-- Each time a user runs an eligibility assessment
-- ============================================================
CREATE TABLE eligibility_checks (
    id                      INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    uuid                    VARCHAR(36)     NOT NULL UNIQUE,
    user_id                 INT UNSIGNED    NULL,
    session_token           VARCHAR(100)    NULL COMMENT 'For anonymous users',
    state                   CHAR(2)         NOT NULL,
    zip_code                VARCHAR(10)     NULL,
    household_size          TINYINT         NOT NULL,
    annual_income           DECIMAL(12,2)   NOT NULL,
    monthly_income          DECIMAL(10,2)   GENERATED ALWAYS AS (annual_income / 12) STORED,
    employment_status       ENUM('employed','unemployed','self_employed','retired','disabled','student') NULL,
    has_disability          TINYINT(1)      NOT NULL DEFAULT 0,
    has_children            TINYINT(1)      NOT NULL DEFAULT 0,
    youngest_child_age      TINYINT         NULL,
    pregnant                TINYINT(1)      NOT NULL DEFAULT 0,
    citizenship_status      ENUM('citizen','permanent_resident','visa','undocumented','unknown') DEFAULT 'citizen',
    rent_monthly            DECIMAL(10,2)   NULL,
    utilities_monthly       DECIMAL(10,2)   NULL,
    income_pct_fpl          DECIMAL(6,2)    NULL COMMENT 'Calculated: (annual_income/fpl) * 100',
    raw_inputs_json         JSON            NULL COMMENT 'Full original intake data',
    gpt_prescreen_json      JSON            NULL COMMENT 'GPT-4o-mini pre-screen result',
    claude_analysis_json    JSON            NULL COMMENT 'Full Claude eligibility analysis',
    total_programs_found    TINYINT         NOT NULL DEFAULT 0,
    total_eligible_programs TINYINT         NOT NULL DEFAULT 0,
    estimated_total_monthly DECIMAL(10,2)   NULL,
    ai_summary              TEXT            NULL,
    created_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_check_user (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user          (user_id),
    INDEX idx_state         (state),
    INDEX idx_session       (session_token),
    INDEX idx_created       (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: eligibility_results
-- Individual program result per eligibility check
-- ============================================================
CREATE TABLE eligibility_results (
    id                          INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    eligibility_check_id        INT UNSIGNED    NOT NULL,
    program_id                  INT UNSIGNED    NOT NULL,
    is_eligible                 TINYINT(1)      NOT NULL DEFAULT 0,
    confidence_score            TINYINT         NULL COMMENT '0-100 from AI',
    estimated_benefit_min       DECIMAL(10,2)   NULL,
    estimated_benefit_max       DECIMAL(10,2)   NULL,
    eligibility_reason          TEXT            NULL,
    disqualification_reason     TEXT            NULL,
    claude_explanation          TEXT            NULL,
    priority_rank               TINYINT         NULL,
    created_at                  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_result_check (eligibility_check_id) REFERENCES eligibility_checks(id) ON DELETE CASCADE,
    FOREIGN KEY fk_result_prog  (program_id) REFERENCES benefits_programs(id),
    UNIQUE KEY uk_check_program (eligibility_check_id, program_id),
    INDEX idx_check             (eligibility_check_id),
    INDEX idx_eligible          (is_eligible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: applications
-- Track user's actual applications to programs
-- ============================================================
CREATE TABLE applications (
    id                      INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    uuid                    VARCHAR(36)     NOT NULL UNIQUE,
    user_id                 INT UNSIGNED    NULL,
    program_id              INT UNSIGNED    NOT NULL,
    eligibility_check_id    INT UNSIGNED    NULL,
    status                  ENUM(
                                'started',
                                'docs_gathering',
                                'submitted',
                                'pending_review',
                                'approved',
                                'denied',
                                'appealing',
                                'withdrawn'
                            ) NOT NULL DEFAULT 'started',
    external_ref_number     VARCHAR(100)    NULL COMMENT 'Ref number from the official system',
    docs_ready_count        TINYINT         NOT NULL DEFAULT 0,
    docs_total_count        TINYINT         NOT NULL DEFAULT 0,
    submitted_at            TIMESTAMP       NULL,
    decision_at             TIMESTAMP       NULL,
    approved_amount         DECIMAL(10,2)   NULL,
    denial_reason           TEXT            NULL,
    appeal_filed_at         TIMESTAMP       NULL,
    benefit_start_date      DATE            NULL,
    notes                   TEXT            NULL,
    ai_assistance_used      TINYINT(1)      NOT NULL DEFAULT 0,
    created_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY fk_app_user     (user_id)              REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY fk_app_program  (program_id)           REFERENCES benefits_programs(id),
    FOREIGN KEY fk_app_check    (eligibility_check_id) REFERENCES eligibility_checks(id) ON DELETE SET NULL,
    INDEX idx_user              (user_id),
    INDEX idx_status            (status),
    INDEX idx_program           (program_id),
    INDEX idx_created           (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: user_document_checklist
-- Track which documents each user has gathered per application
-- ============================================================
CREATE TABLE user_document_checklist (
    id                      INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    application_id          INT UNSIGNED    NOT NULL,
    program_document_id     INT UNSIGNED    NOT NULL,
    is_checked              TINYINT(1)      NOT NULL DEFAULT 0,
    checked_at              TIMESTAMP       NULL,
    uploaded_file_path      VARCHAR(500)    NULL,
    upload_status           ENUM('none','pending','uploaded','verified','rejected') NOT NULL DEFAULT 'none',
    gpt_ocr_result          JSON            NULL COMMENT 'Extracted fields from document image via GPT-4o',
    notes                   VARCHAR(500)    NULL,
    FOREIGN KEY fk_checklist_app (application_id)      REFERENCES applications(id) ON DELETE CASCADE,
    FOREIGN KEY fk_checklist_doc (program_document_id) REFERENCES program_documents(id),
    UNIQUE KEY uk_app_doc   (application_id, program_document_id),
    INDEX idx_application   (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: chat_sessions
-- One session per user-program conversation
-- ============================================================
CREATE TABLE chat_sessions (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    uuid            VARCHAR(36)     NOT NULL UNIQUE,
    user_id         INT UNSIGNED    NULL,
    session_token   VARCHAR(100)    NULL COMMENT 'For anonymous users',
    program_id      INT UNSIGNED    NULL,
    application_id  INT UNSIGNED    NULL,
    ai_provider     ENUM('claude','openai','both') NOT NULL DEFAULT 'openai',
    session_type    ENUM('eligibility','guidance','form_help','document_help','general') NOT NULL DEFAULT 'general',
    context_json    JSON            NULL COMMENT 'User context snapshot (income, HH size, program, etc.)',
    message_count   SMALLINT        NOT NULL DEFAULT 0,
    started_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_active_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY fk_session_user    (user_id)       REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY fk_session_program (program_id)    REFERENCES benefits_programs(id) ON DELETE SET NULL,
    FOREIGN KEY fk_session_app     (application_id) REFERENCES applications(id) ON DELETE SET NULL,
    INDEX idx_user          (user_id),
    INDEX idx_session_token (session_token),
    INDEX idx_provider      (ai_provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: chat_messages
-- Individual messages in a chat session
-- ============================================================
CREATE TABLE chat_messages (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    session_id      INT UNSIGNED    NOT NULL,
    role            ENUM('user','assistant','system') NOT NULL,
    content         TEXT            NOT NULL,
    ai_provider     ENUM('claude','openai') NULL,
    model_used      VARCHAR(50)     NULL COMMENT 'e.g. claude-sonnet-4-6, gpt-4o-mini',
    prompt_tokens   SMALLINT        NULL,
    completion_tokens SMALLINT      NULL,
    response_ms     SMALLINT        NULL COMMENT 'API response time in milliseconds',
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_msg_session (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
    INDEX idx_session   (session_id),
    INDEX idx_created   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: ai_api_logs
-- Cost tracking and monitoring for both AIs
-- ============================================================
CREATE TABLE ai_api_logs (
    id                  INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    provider            ENUM('claude','openai') NOT NULL,
    endpoint            VARCHAR(100)    NULL COMMENT 'e.g. eligibility, chat, ocr, translation',
    model               VARCHAR(50)     NULL,
    user_id             INT UNSIGNED    NULL,
    session_id          INT UNSIGNED    NULL,
    prompt_tokens       INT             NULL,
    completion_tokens   INT             NULL,
    total_tokens        INT             GENERATED ALWAYS AS (IFNULL(prompt_tokens,0) + IFNULL(completion_tokens,0)) STORED,
    cost_usd            DECIMAL(10,6)   NULL,
    response_ms         SMALLINT        NULL,
    http_status         SMALLINT        NULL,
    error_message       TEXT            NULL,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_provider  (provider),
    INDEX idx_user      (user_id),
    INDEX idx_created   (created_at),
    INDEX idx_endpoint  (endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: notifications
-- In-app + email + SMS notification queue
-- ============================================================
CREATE TABLE notifications (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED    NOT NULL,
    application_id  INT UNSIGNED    NULL,
    type            ENUM(
                        'status_update','document_required','deadline',
                        'approved','denied','renewal_due','welcome'
                    ) NOT NULL,
    channel         ENUM('in_app','email','sms') NOT NULL DEFAULT 'in_app',
    title           VARCHAR(255)    NOT NULL,
    body            TEXT            NOT NULL,
    action_url      VARCHAR(500)    NULL,
    is_read         TINYINT(1)      NOT NULL DEFAULT 0,
    sent_at         TIMESTAMP       NULL,
    read_at         TIMESTAMP       NULL,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_notif_user (user_id)         REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY fk_notif_app  (application_id)  REFERENCES applications(id) ON DELETE SET NULL,
    INDEX idx_user      (user_id),
    INDEX idx_unread    (user_id, is_read),
    INDEX idx_type      (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: translations_cache
-- Cache translated content to avoid re-billing translation API
-- ============================================================
CREATE TABLE translations_cache (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    source_hash     CHAR(64)        NOT NULL COMMENT 'SHA-256 of original text',
    language_code   VARCHAR(10)     NOT NULL,
    original_text   TEXT            NOT NULL,
    translated_text TEXT            NOT NULL,
    model_used      VARCHAR(50)     NULL,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_hash_lang (source_hash, language_code),
    INDEX idx_lang      (language_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- TABLE: app_settings
-- Runtime configuration without code deploys
-- ============================================================
CREATE TABLE app_settings (
    id              INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    setting_key     VARCHAR(100)    NOT NULL UNIQUE,
    setting_value   TEXT            NULL,
    setting_type    ENUM('string','integer','boolean','json') NOT NULL DEFAULT 'string',
    description     TEXT            NULL,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO app_settings (setting_key, setting_value, setting_type, description) VALUES
    ('claude_enabled',           '1',                  'boolean', 'Enable Claude API calls'),
    ('openai_enabled',           '1',                  'boolean', 'Enable OpenAI API calls'),
    ('max_chat_messages',        '50',                 'integer', 'Max messages per chat session'),
    ('ocr_enabled',              '1',                  'boolean', 'Allow document image scanning'),
    ('translation_enabled',      '1',                  'boolean', 'Show language switcher'),
    ('supported_languages',      '["en","es","zh","ar","tl","vi"]', 'json', 'Available UI languages'),
    ('fpl_year',                 '2025',               'integer', 'Which FPL year to use for calculations'),
    ('session_expiry_days',      '30',                 'integer', 'Days before anonymous session expires');


-- ============================================================
-- VIEWS
-- ============================================================

-- Active programs sorted by category
CREATE VIEW v_active_programs AS
SELECT
    bp.id,
    bp.program_code,
    bp.name,
    bp.category,
    bp.program_type,
    bp.income_limit_pct_fpl,
    bp.max_benefit_amount,
    bp.benefit_period,
    bp.priority_level,
    bp.apply_url,
    bp.processing_days_min,
    bp.processing_days_max,
    fpl.annual_amount AS fpl_annual_2024,
    ROUND(fpl.annual_amount * bp.income_limit_pct_fpl / 100, 2) AS income_limit_2person
FROM benefits_programs bp
LEFT JOIN federal_poverty_levels fpl
    ON fpl.year = 2025 AND fpl.household_size = 2
WHERE bp.is_active = 1
ORDER BY FIELD(bp.priority_level,'apply_now','high','medium','low'), bp.category;


-- Application status summary per user
CREATE VIEW v_user_application_summary AS
SELECT
    u.id AS user_id,
    u.uuid,
    u.email,
    COUNT(a.id)                                             AS total_applications,
    SUM(a.status = 'approved')                              AS approved_count,
    SUM(a.status = 'pending_review')                        AS pending_count,
    SUM(a.status = 'denied')                                AS denied_count,
    COALESCE(SUM(CASE WHEN a.status = 'approved' THEN a.approved_amount END), 0) AS total_monthly_approved
FROM users u
LEFT JOIN applications a ON a.user_id = u.id
GROUP BY u.id;


-- AI cost summary by day
CREATE VIEW v_ai_cost_daily AS
SELECT
    DATE(created_at)    AS log_date,
    provider,
    endpoint,
    COUNT(*)            AS call_count,
    SUM(total_tokens)   AS total_tokens,
    SUM(cost_usd)       AS total_cost_usd
FROM ai_api_logs
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at), provider, endpoint
ORDER BY log_date DESC, total_cost_usd DESC;


-- ============================================================
-- STORED PROCEDURES
-- ============================================================

DELIMITER $$

-- Get eligible programs for a household (automated pre-filter)
CREATE PROCEDURE sp_get_eligible_programs(
    IN p_household_size    TINYINT,
    IN p_annual_income     DECIMAL(12,2),
    IN p_state             CHAR(2),
    IN p_has_children      TINYINT(1),
    IN p_has_disability    TINYINT(1)
)
BEGIN
    DECLARE v_fpl_annual DECIMAL(10,2);
    DECLARE v_income_pct DECIMAL(6,2);

    -- Get this year's FPL for this household size
    SELECT annual_amount INTO v_fpl_annual
    FROM federal_poverty_levels
    WHERE year = 2025 AND household_size = LEAST(p_household_size, 8)
    LIMIT 1;

    SET v_income_pct = IF(v_fpl_annual > 0, (p_annual_income / v_fpl_annual) * 100, 999);

    SELECT
        bp.program_code,
        bp.name,
        bp.category,
        bp.max_benefit_amount,
        bp.apply_url,
        bp.priority_level,
        v_income_pct AS calculated_income_pct_fpl,
        CASE
            WHEN v_income_pct <= bp.income_limit_pct_fpl THEN 'likely_eligible'
            WHEN v_income_pct <= bp.income_limit_pct_fpl * 1.1 THEN 'borderline'
            ELSE 'likely_ineligible'
        END AS pre_screen_status
    FROM benefits_programs bp
    WHERE bp.is_active = 1
      AND (bp.state IS NULL OR bp.state = p_state)
      AND (bp.income_limit_pct_fpl IS NULL OR v_income_pct <= bp.income_limit_pct_fpl * 1.15)
    ORDER BY FIELD(bp.priority_level,'apply_now','high','medium','low');
END$$

DELIMITER ;
