-- =========================================================
--  BDNews — JSON ফাইল স্টোরেজ থেকে MySQL এ মাইগ্রেশনের জন্য স্কিমা
--  cPanel > phpMyAdmin এ গিয়ে এই পুরো ফাইলটা "Import" বা "SQL" ট্যাবে
--  পেস্ট করে একবার রান করুন। এটা idempotent (IF NOT EXISTS ব্যবহার
--  করা আছে), তাই ভুলে দুইবার রান হয়ে গেলেও সমস্যা নেই।
-- =========================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------
-- মূল নিউজ টেবিল (আগে data/news/news_YYYY-MM-DD.json ফাইলগুলোতে
-- ছিল)। id = news_hash(link,title) — এটাই PRIMARY KEY, তাই dedup
-- চেক এখন এক ইনডেক্সড লুকআপ, ফাইল স্ক্যান লাগে না।
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS news (
    id           CHAR(32) NOT NULL,
    source_id    VARCHAR(100) NOT NULL,
    source_name  VARCHAR(255) NOT NULL,
    category     VARCHAR(100) NOT NULL,
    title        VARCHAR(1000) NOT NULL,
    description  TEXT NULL,
    ai_summary   TEXT NULL,
    is_breaking  TINYINT(1) NOT NULL DEFAULT 0,
    image        VARCHAR(1000) NULL,
    link         VARCHAR(1000) NOT NULL,
    pub_date     DATETIME NOT NULL,
    pub_ts       BIGINT NOT NULL,
    fetched_at   DATETIME NOT NULL,
    added_via    VARCHAR(50) NOT NULL DEFAULT 'rss',
    PRIMARY KEY (id),
    INDEX idx_pub_ts (pub_ts),
    INDEX idx_category_pubts (category, pub_ts),
    INDEX idx_source_pubts (source_id, pub_ts),
    INDEX idx_breaking (is_breaking, pub_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- হ্যাশ-লেভেল dedup ইনডেক্স (আগে data/index.json)। যেসব আর্টিকেল
-- পুরনো বলে বাদ দেওয়া হয়েছে (news টেবিলে সেভ হয়নি) তাদের হ্যাশও
-- এখানে থাকে যাতে বারবার রিপ্রসেস না হয়।
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS news_index (
    hash    CHAR(32) NOT NULL,
    pub_ts  BIGINT NOT NULL,
    PRIMARY KEY (hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- RSS সোর্স লিস্ট (আগে data/sources.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS sources (
    id                 VARCHAR(100) NOT NULL,
    name               VARCHAR(255) NOT NULL,
    category           VARCHAR(100) NOT NULL,
    url                VARCHAR(1000) NOT NULL,
    active             TINYINT(1) NOT NULL DEFAULT 1,
    always_breaking    TINYINT(1) NOT NULL DEFAULT 0,
    consecutive_fails  INT NOT NULL DEFAULT 0,
    paused_by_health   TINYINT(1) NOT NULL DEFAULT 0,
    last_checked_at    DATETIME NULL,
    last_success_at    DATETIME NULL,
    sort_order         INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- RSS নেই এমন সাইটের স্ক্রেপ-সোর্স লিস্ট (আগে data/scrape_sources.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS scrape_sources (
    id                 VARCHAR(100) NOT NULL,
    name               VARCHAR(255) NOT NULL,
    category           VARCHAR(100) NOT NULL,
    url                VARCHAR(1000) NOT NULL,
    active             TINYINT(1) NOT NULL DEFAULT 1,
    always_breaking    TINYINT(1) NOT NULL DEFAULT 0,
    max_per_run        INT NOT NULL DEFAULT 8,
    consecutive_fails  INT NOT NULL DEFAULT 0,
    paused_by_health   TINYINT(1) NOT NULL DEFAULT 0,
    last_checked_at    DATETIME NULL,
    last_success_at    DATETIME NULL,
    sort_order         INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- আগে চেক করা লিংকের হ্যাশ (আগে data/scraped_links_index.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS scraped_link_index (
    link_hash   CHAR(32) NOT NULL,
    checked_at  BIGINT NOT NULL,
    PRIMARY KEY (link_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- কাস্টম ক্যাটাগরি লেবেল (আগে data/category_labels.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS category_labels (
    slug   VARCHAR(100) NOT NULL,
    label  VARCHAR(255) NOT NULL,
    PRIMARY KEY (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- এলার্ট / দুর্যোগ সতর্কতা (আগে data/alerts.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS alerts (
    id           VARCHAR(50) NOT NULL,
    title        VARCHAR(500) NOT NULL,
    message      TEXT NOT NULL,
    severity     ENUM('info','warning','danger') NOT NULL DEFAULT 'info',
    area         VARCHAR(255) NOT NULL DEFAULT 'সারাদেশ',
    valid_until  VARCHAR(50) NULL,
    created_at   DATETIME NOT NULL,
    active       TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- সরকারি সতর্কতা পেজ মনিটরিং লিস্ট (আগে data/alert_watch_sources.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS alert_watch_sources (
    id                 VARCHAR(100) NOT NULL,
    name               VARCHAR(255) NOT NULL,
    url                VARCHAR(1000) NOT NULL,
    default_severity   ENUM('info','warning','danger') NOT NULL DEFAULT 'warning',
    default_area       VARCHAR(255) NOT NULL DEFAULT 'সারাদেশ',
    active             TINYINT(1) NOT NULL DEFAULT 1,
    consecutive_fails  INT NOT NULL DEFAULT 0,
    last_checked_at    DATETIME NULL,
    last_content_hash  CHAR(32) NULL,
    last_updated_at    DATETIME NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 🎯 ব্রেকিং নিউজ ওয়াচ সোর্স — নির্দিষ্ট কোনো সাইট টার্গেট করে সেখানকার
-- সাম্প্রতিক/ব্রেকিং খবর অটো-মনিটর করে, পেলে সরাসরি সেই সাইটের লিংকসহ
-- breaking_news টেবিলে যোগ করে দেয়
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS breaking_watch_sources (
    id                  VARCHAR(100) NOT NULL,
    name                VARCHAR(255) NOT NULL,
    feed_url            VARCHAR(1000) NOT NULL,
    mode                ENUM('keyword','always_latest') NOT NULL DEFAULT 'keyword',
    keywords            VARCHAR(500) NULL,
    freshness_minutes   INT NOT NULL DEFAULT 20,
    category            VARCHAR(100) NOT NULL DEFAULT 'national',
    active              TINYINT(1) NOT NULL DEFAULT 1,
    consecutive_fails   INT NOT NULL DEFAULT 0,
    last_checked_at     DATETIME NULL,
    last_detected_link  VARCHAR(1000) NULL,
    last_detected_at    DATETIME NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    id   VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    lat  DOUBLE NOT NULL,
    lon  DOUBLE NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Editor's Pick / পিন করা নিউজ (আগে data/pinned.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS pinned_news (
    news_id    CHAR(32) NOT NULL,
    pinned_at  DATETIME NOT NULL,
    PRIMARY KEY (news_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- সেটিংস ও আবহাওয়া-ক্যাশ: এগুলো ছোট, একটামাত্র "ব্লব"-জাতীয়
-- অবজেক্ট (কখনো লাখো রো হবে না) — তাই পুরো JSON structure-টা একটা
-- একক রো তে JSON কলামে রাখা হয়েছে (settings.json / weather_cache.json
-- ফাইলের সরাসরি প্রতিস্থাপন, লজিক অপরিবর্তিত)।
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings_store (
    id         TINYINT NOT NULL,
    data       JSON NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS weather_cache_store (
    id         TINYINT NOT NULL,
    data       JSON NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 📺 YouTube ইন্টিগ্রেশন (আগে youtube_channels.json / youtube_videos.json / youtube_quota.json)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS youtube_channels (
    id                   VARCHAR(50) NOT NULL,
    channel_id           VARCHAR(100) NOT NULL,
    name                 VARCHAR(255) NOT NULL,
    category             VARCHAR(100) NOT NULL,
    active               TINYINT(1) NOT NULL DEFAULT 1,
    uploads_playlist_id  VARCHAR(100) NULL,
    last_fetch_at        DATETIME NULL,
    last_error           VARCHAR(500) NULL,
    created_at           DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_channel_id (channel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS youtube_videos (
    video_id         VARCHAR(50) NOT NULL,
    title            VARCHAR(500) NOT NULL,
    description      TEXT NULL,
    thumbnail        VARCHAR(500) NULL,
    channel_id       VARCHAR(100) NOT NULL,
    channel_name     VARCHAR(255) NOT NULL,
    category         VARCHAR(100) NOT NULL,
    published_at     VARCHAR(50) NULL,
    published_at_ts  BIGINT NOT NULL DEFAULT 0,
    is_live          TINYINT(1) NOT NULL DEFAULT 0,
    view_count       BIGINT NOT NULL DEFAULT 0,
    featured         TINYINT(1) NOT NULL DEFAULT 0,
    breaking         TINYINT(1) NOT NULL DEFAULT 0,
    hidden           TINYINT(1) NOT NULL DEFAULT 0,
    fetched_at       DATETIME NOT NULL,
    PRIMARY KEY (video_id),
    INDEX idx_published_ts (published_at_ts),
    INDEX idx_category_pubts (category, published_at_ts),
    INDEX idx_live (is_live),
    INDEX idx_featured (featured),
    INDEX idx_view_count (view_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 🔴 অ্যাডভান্স ব্রেকিং নিউজ (অটো-ডিটেক্ট + ম্যানুয়াল দুটোই এক জায়গায়)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS breaking_news (
    id           VARCHAR(50) NOT NULL,
    source       ENUM('auto','manual') NOT NULL DEFAULT 'manual',
    news_id      CHAR(32) NULL,
    title        VARCHAR(500) NOT NULL,
    description  VARCHAR(500) NULL,
    image        VARCHAR(1000) NULL,
    link         VARCHAR(1000) NULL,
    category     VARCHAR(100) NOT NULL DEFAULT 'national',
    priority     INT NOT NULL DEFAULT 100,
    active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at   DATETIME NOT NULL,
    expires_at   DATETIME NULL,
    PRIMARY KEY (id),
    INDEX idx_active_priority (active, priority, created_at),
    INDEX idx_news_id (news_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- YouTube quota ব্যবহারের হিসাব + থ্রটল-লাস্ট-রান টাইম — একটাই ছোট রো
CREATE TABLE IF NOT EXISTS youtube_meta (
    id            TINYINT NOT NULL,
    quota_date    DATE NULL,
    quota_units   INT NOT NULL DEFAULT 0,
    last_run_at   BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 🆕 always_breaking কলাম — টেবিল আগে থেকেই থাকলে (fresh install না
-- হয়ে upgrade হলে) CREATE TABLE IF NOT EXISTS নতুন কলাম যোগ করে না,
-- তাই নিচের ব্লক নিরাপদে (একাধিকবার রান করলেও সমস্যা নেই) কলাম যোগ করে।
-- ---------------------------------------------------------
SET @col_exists_sources := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='sources' AND COLUMN_NAME='always_breaking');
SET @sql_sources := IF(@col_exists_sources = 0, 'ALTER TABLE sources ADD COLUMN always_breaking TINYINT(1) NOT NULL DEFAULT 0 AFTER active', 'SELECT 1');
PREPARE stmt FROM @sql_sources;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists_scrape := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='scrape_sources' AND COLUMN_NAME='always_breaking');
SET @sql_scrape := IF(@col_exists_scrape = 0, 'ALTER TABLE scrape_sources ADD COLUMN always_breaking TINYINT(1) NOT NULL DEFAULT 0 AFTER active', 'SELECT 1');
PREPARE stmt FROM @sql_scrape;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------
-- 🎁 প্রোমোশন / অ্যানাউন্সমেন্ট পপ-আপ (in-app popup/banner সিস্টেম)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS promotions (
    id               VARCHAR(50) NOT NULL,
    title            VARCHAR(255) NOT NULL,
    message          TEXT NOT NULL,
    image_url        VARCHAR(1000) NULL,
    cta_text         VARCHAR(100) NULL,
    cta_link         VARCHAR(1000) NULL,
    bg_color         VARCHAR(20) NOT NULL DEFAULT '#DC2626',
    style            VARCHAR(30) NOT NULL DEFAULT 'popup_center',
    animation        VARCHAR(20) NOT NULL DEFAULT 'fade',
    frequency        VARCHAR(20) NOT NULL DEFAULT 'once',
    frequency_hours  INT NOT NULL DEFAULT 24,
    priority         INT NOT NULL DEFAULT 0,
    active           TINYINT(1) NOT NULL DEFAULT 1,
    start_at         DATETIME NULL,
    end_at           DATETIME NULL,
    created_at       DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_active_priority (active, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 📊 অ্যানালিটিক্স ইভেন্ট (Firebase-এর পাশাপাশি হালকা কপি, অ্যাডমিন প্যানেলে দেখানোর জন্য)
-- বড় আকারে বাড়তে পারে বলে array-bridge প্যাটার্ন না — সরাসরি INSERT/aggregate SELECT
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS analytics_events (
    id          BIGINT AUTO_INCREMENT,
    event_name  VARCHAR(60) NOT NULL,
    params      JSON NULL,
    device_id   VARCHAR(100) NULL,
    created_at  DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_event_date (event_name, created_at),
    INDEX idx_device_date (device_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 🐞 ক্র্যাশ রিপোর্ট (Firebase Crashlytics-এর পাশাপাশি, অ্যাডমিন প্যানেলে তাৎক্ষণিক দেখার জন্য)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS crash_reports (
    id              VARCHAR(50) NOT NULL,
    app_version     VARCHAR(50) NULL,
    device_model    VARCHAR(150) NULL,
    android_version VARCHAR(30) NULL,
    exception       VARCHAR(500) NULL,
    stack_trace     TEXT NULL,
    screen          VARCHAR(100) NULL,
    resolved        TINYINT(1) NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_resolved_date (resolved, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;