-- =========================================================
-- Geo Location Migration — sources ও news টেবিলে location ফিল্ড যোগ
-- =========================================================

-- sources টেবিলে জেলা/উপজেলা ফিল্ড
ALTER TABLE sources
    ADD COLUMN IF NOT EXISTS division    VARCHAR(100) NULL AFTER category,
    ADD COLUMN IF NOT EXISTS district    VARCHAR(100) NULL AFTER division,
    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;

-- scrape_sources টেবিলেও একই ফিল্ড
ALTER TABLE scrape_sources
    ADD COLUMN IF NOT EXISTS division    VARCHAR(100) NULL AFTER category,
    ADD COLUMN IF NOT EXISTS district    VARCHAR(100) NULL AFTER division,
    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;

-- news টেবিলে জেলা ও লোকেশন ফিল্ড (নিউজ কন্টেন্ট থেকে অটো-ডিটেক্ট হবে)
ALTER TABLE news
    ADD COLUMN IF NOT EXISTS district    VARCHAR(100) NULL AFTER category,
    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;

-- লোকেশন দিয়ে কাছের নিউজ খুঁজতে index দরকার
CREATE INDEX IF NOT EXISTS idx_news_district ON news(district, pub_ts);
CREATE INDEX IF NOT EXISTS idx_news_geo ON news(geo_lat, geo_lon);