-- Hybrid AI Local News Classification (V3.3) — additive migration
ALTER TABLE sources
    ADD COLUMN IF NOT EXISTS category_mode VARCHAR(16) NOT NULL DEFAULT 'fixed' AFTER category,
    ADD COLUMN IF NOT EXISTS location_mode VARCHAR(16) NOT NULL DEFAULT 'auto' AFTER category_mode,
    ADD COLUMN IF NOT EXISTS ai_classification_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER location_mode,
    ADD COLUMN IF NOT EXISTS division VARCHAR(100) NULL AFTER ai_classification_enabled,
    ADD COLUMN IF NOT EXISTS district_key VARCHAR(100) NULL AFTER division,
    ADD COLUMN IF NOT EXISTS district VARCHAR(100) NULL AFTER district_key,
    ADD COLUMN IF NOT EXISTS upazila VARCHAR(200) NULL AFTER district,
    ADD COLUMN IF NOT EXISTS geo_lat DECIMAL(10,6) NULL AFTER upazila,
    ADD COLUMN IF NOT EXISTS geo_lon DECIMAL(10,6) NULL AFTER geo_lat,
    ADD COLUMN IF NOT EXISTS coverage_km SMALLINT NOT NULL DEFAULT 30 AFTER geo_lon;

ALTER TABLE scrape_sources
    ADD COLUMN IF NOT EXISTS category_mode VARCHAR(16) NOT NULL DEFAULT 'fixed' AFTER category,
    ADD COLUMN IF NOT EXISTS location_mode VARCHAR(16) NOT NULL DEFAULT 'auto' AFTER category_mode,
    ADD COLUMN IF NOT EXISTS ai_classification_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER location_mode,
    ADD COLUMN IF NOT EXISTS division VARCHAR(100) NULL AFTER ai_classification_enabled,
    ADD COLUMN IF NOT EXISTS district_key VARCHAR(100) NULL AFTER division,
    ADD COLUMN IF NOT EXISTS district VARCHAR(100) NULL AFTER district_key,
    ADD COLUMN IF NOT EXISTS upazila VARCHAR(200) NULL AFTER district,
    ADD COLUMN IF NOT EXISTS geo_lat DECIMAL(10,6) NULL AFTER upazila,
    ADD COLUMN IF NOT EXISTS geo_lon DECIMAL(10,6) NULL AFTER geo_lat,
    ADD COLUMN IF NOT EXISTS coverage_km SMALLINT NOT NULL DEFAULT 30 AFTER geo_lon;

ALTER TABLE news
    ADD COLUMN IF NOT EXISTS category_source VARCHAR(32) NOT NULL DEFAULT 'legacy' AFTER category,
    ADD COLUMN IF NOT EXISTS division VARCHAR(100) NULL AFTER category_source,
    ADD COLUMN IF NOT EXISTS district_key VARCHAR(100) NULL AFTER division,
    ADD COLUMN IF NOT EXISTS district VARCHAR(100) NULL AFTER district_key,
    ADD COLUMN IF NOT EXISTS upazila VARCHAR(200) NULL AFTER district,
    ADD COLUMN IF NOT EXISTS geo_lat DECIMAL(10,6) NULL AFTER upazila,
    ADD COLUMN IF NOT EXISTS geo_lon DECIMAL(10,6) NULL AFTER geo_lat,
    ADD COLUMN IF NOT EXISTS location_source VARCHAR(32) NOT NULL DEFAULT 'none' AFTER geo_lon,
    ADD COLUMN IF NOT EXISTS ai_confidence SMALLINT NULL AFTER location_source,
    ADD COLUMN IF NOT EXISTS classified_at DATETIME NULL AFTER ai_confidence;

CREATE TABLE IF NOT EXISTS ai_classification_usage (
    usage_date DATE NOT NULL,
    calls INT NOT NULL DEFAULT 0,
    PRIMARY KEY (usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS idx_news_district_key_pubts ON news(district_key, pub_ts);
CREATE INDEX IF NOT EXISTS idx_news_division_pubts ON news(division, pub_ts);