-- Harden existing ClaimIt installations against AI text/array growth and
-- frontend values such as state="California".
--
-- Back up the database first, then run:
--   mysql -u USER -p DATABASE_NAME < database/migrations/20260618_harden_ai_storage.sql

ALTER TABLE eligibility_checks
    MODIFY state VARCHAR(100) NOT NULL
        COMMENT 'Canonical two-letter code; widened defensively',
    MODIFY household_size SMALLINT UNSIGNED NOT NULL,
    MODIFY total_programs_found SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    MODIFY total_eligible_programs SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    MODIFY ai_summary LONGTEXT NULL;

ALTER TABLE program_eligibility_rules
    MODIFY value_1 LONGTEXT NULL
        COMMENT 'Primary comparison value or JSON array for in_list',
    MODIFY value_2 LONGTEXT NULL
        COMMENT 'Upper bound for between operator',
    MODIFY description TEXT NULL;

ALTER TABLE eligibility_results
    MODIFY eligibility_reason LONGTEXT NULL,
    MODIFY disqualification_reason LONGTEXT NULL,
    MODIFY claude_explanation LONGTEXT NULL;

ALTER TABLE applications
    MODIFY notes LONGTEXT NULL;

ALTER TABLE user_document_checklist
    MODIFY notes TEXT NULL;

ALTER TABLE chat_messages
    MODIFY content LONGTEXT NOT NULL,
    MODIFY model_used VARCHAR(191) NULL COMMENT 'Provider model identifier',
    MODIFY prompt_tokens INT UNSIGNED NULL,
    MODIFY completion_tokens INT UNSIGNED NULL,
    MODIFY response_ms INT UNSIGNED NULL
        COMMENT 'API response time in milliseconds';

ALTER TABLE ai_api_logs
    MODIFY endpoint VARCHAR(191) NULL
        COMMENT 'e.g. eligibility, chat, ocr, translation',
    MODIFY model VARCHAR(191) NULL,
    MODIFY response_ms INT UNSIGNED NULL,
    MODIFY http_status SMALLINT UNSIGNED NULL,
    MODIFY error_message LONGTEXT NULL;

ALTER TABLE notifications
    MODIFY body LONGTEXT NOT NULL;

ALTER TABLE translations_cache
    MODIFY original_text LONGTEXT NOT NULL,
    MODIFY translated_text LONGTEXT NOT NULL,
    MODIFY model_used VARCHAR(191) NULL;

ALTER TABLE app_settings
    MODIFY setting_value LONGTEXT NULL;