-- =========================================================
--  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,
    category_source VARCHAR(32) NOT NULL DEFAULT 'legacy',
    division     VARCHAR(100) NULL,
    district_key VARCHAR(100) NULL,
    district     VARCHAR(100) NULL,
    upazila      VARCHAR(200) NULL,
    geo_lat      DECIMAL(10,6) NULL,
    geo_lon      DECIMAL(10,6) NULL,
    location_source VARCHAR(32) NOT NULL DEFAULT 'none',
    ai_confidence SMALLINT NULL,
    classified_at DATETIME 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_district_key_pubts (district_key, pub_ts),
    INDEX idx_division_pubts (division, 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,
    category_mode      VARCHAR(16) NOT NULL DEFAULT 'fixed',
    location_mode      VARCHAR(16) NOT NULL DEFAULT 'auto',
    ai_classification_enabled TINYINT(1) NOT NULL DEFAULT 1,
    division           VARCHAR(100) NULL,
    district_key       VARCHAR(100) NULL,
    district           VARCHAR(100) NULL,
    upazila            VARCHAR(200) NULL,
    geo_lat            DECIMAL(10,6) NULL,
    geo_lon            DECIMAL(10,6) NULL,
    coverage_km        SMALLINT NOT NULL DEFAULT 30,
    publisher_key      VARCHAR(80) NULL,
    source_scope       VARCHAR(30) NULL,
    health_status      VARCHAR(20) NOT NULL DEFAULT 'unchecked',
    http_status        SMALLINT NULL,
    last_health_at     DATETIME NULL,
    last_health_error  VARCHAR(1000) 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,
    category_mode      VARCHAR(16) NOT NULL DEFAULT 'fixed',
    location_mode      VARCHAR(16) NOT NULL DEFAULT 'auto',
    ai_classification_enabled TINYINT(1) NOT NULL DEFAULT 1,
    division           VARCHAR(100) NULL,
    district_key       VARCHAR(100) NULL,
    district           VARCHAR(100) NULL,
    upazila            VARCHAR(200) NULL,
    geo_lat            DECIMAL(10,6) NULL,
    geo_lon            DECIMAL(10,6) NULL,
    coverage_km        SMALLINT NOT NULL DEFAULT 30,
    publisher_key      VARCHAR(80) NULL,
    source_scope       VARCHAR(30) NULL,
    health_status      VARCHAR(20) NOT NULL DEFAULT 'unchecked',
    http_status        SMALLINT NULL,
    last_health_at     DATETIME NULL,
    last_health_error  VARCHAR(1000) 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,
    pagination_depth   TINYINT NOT NULL DEFAULT 1,
    discovery_limit    SMALLINT NOT NULL DEFAULT 100,
    include_regex      VARCHAR(500) NULL,
    exclude_regex      VARCHAR(500) NULL,
    min_title_length   SMALLINT NOT NULL DEFAULT 8,
    min_description_length SMALLINT NOT NULL DEFAULT 0,
    retry_limit        TINYINT NOT NULL DEFAULT 2,
    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,
    last_discovered    INT NOT NULL DEFAULT 0,
    last_attempted     INT NOT NULL DEFAULT 0,
    last_saved         INT NOT NULL DEFAULT 0,
    last_duplicates    INT NOT NULL DEFAULT 0,
    last_failed        INT NOT NULL DEFAULT 0,
    last_retry_pending INT NOT NULL DEFAULT 0,
    last_old_skipped   INT NOT NULL DEFAULT 0,
    last_extraction_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
    last_scrape_error  VARCHAR(1000) NULL,
    last_scrape_run_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;


CREATE TABLE IF NOT EXISTS scrape_retry_queue (
    url_hash CHAR(32) NOT NULL,
    source_id VARCHAR(100) NOT NULL,
    url VARCHAR(1500) NOT NULL,
    attempts TINYINT NOT NULL DEFAULT 0,
    last_http_status SMALLINT NULL,
    last_error VARCHAR(1000) NULL,
    next_retry_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (url_hash),
    KEY idx_scrape_retry_due (next_retry_at),
    KEY idx_scrape_retry_source (source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- কাস্টম ক্যাটাগরি লেবেল (আগে 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;

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;

-- ---------------------------------------------------------
-- এলার্ট / দুর্যোগ সতর্কতা (আগে 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;

-- ---------------------------------------------------------
-- Weather cities (used by admin/weather.php and weather API)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS weather_cities (
    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;

-- YouTube Live Monitor migration
CREATE TABLE IF NOT EXISTS youtube_live_events (
    video_id VARCHAR(50) NOT NULL,
    title VARCHAR(500) NOT NULL,
    thumbnail VARCHAR(700) NULL,
    watch_url VARCHAR(700) NOT NULL,
    channel_id VARCHAR(100) NOT NULL,
    channel_name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'general',
    status ENUM('live','upcoming','completed') NOT NULL DEFAULT 'upcoming',
    scheduled_start_at DATETIME NULL,
    actual_start_at DATETIME NULL,
    actual_end_at DATETIME NULL,
    concurrent_viewers BIGINT NOT NULL DEFAULT 0,
    view_count BIGINT NOT NULL DEFAULT 0,
    highlight TINYINT(1) NOT NULL DEFAULT 0,
    highlight_order INT NOT NULL DEFAULT 100,
    first_seen_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (video_id),
    INDEX idx_live_status (status, updated_at),
    INDEX idx_live_channel (channel_id, status),
    INDEX idx_live_highlight (highlight, highlight_order, updated_at)
) 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;

-- =========================================================
-- 📡 ADVANCED FEED HUB — YouTube Atom + RSS/Atom + Highlights
-- =========================================================
CREATE TABLE IF NOT EXISTS feed_sources (
    id VARCHAR(64) NOT NULL,
    name VARCHAR(255) NOT NULL,
    source_kind VARCHAR(30) NOT NULL DEFAULT 'rss',
    provider VARCHAR(30) NOT NULL DEFAULT 'generic',
    input_url VARCHAR(1000) NULL,
    feed_url VARCHAR(1000) NOT NULL,
    site_url VARCHAR(1000) NULL,
    channel_id VARCHAR(100) NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'general',
    tags VARCHAR(500) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    priority INT NOT NULL DEFAULT 100,
    include_keywords TEXT NULL,
    exclude_keywords TEXT NULL,
    last_fetch_at DATETIME NULL,
    last_success_at DATETIME NULL,
    last_error TEXT NULL,
    consecutive_fails INT NOT NULL DEFAULT 0,
    item_count INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_feed_url (feed_url(255)),
    INDEX idx_feed_active_priority (active, priority),
    INDEX idx_feed_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS feed_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_id VARCHAR(64) NOT NULL,
    external_id VARCHAR(500) NOT NULL,
    item_kind VARCHAR(30) NOT NULL DEFAULT 'feed_item',
    title VARCHAR(700) NOT NULL,
    description TEXT NULL,
    thumbnail_url VARCHAR(1200) NULL,
    watch_url VARCHAR(1200) NULL,
    author VARCHAR(255) NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'general',
    tags VARCHAR(500) NULL,
    published_at DATETIME NULL,
    published_ts BIGINT NOT NULL DEFAULT 0,
    highlight TINYINT(1) NOT NULL DEFAULT 0,
    featured TINYINT(1) NOT NULL DEFAULT 0,
    hidden TINYINT(1) NOT NULL DEFAULT 0,
    highlight_order INT NOT NULL DEFAULT 100,
    fetched_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_source_external (source_id, external_id(190)),
    INDEX idx_feed_items_pub (published_ts),
    INDEX idx_feed_items_category_pub (category, published_ts),
    INDEX idx_feed_items_highlight (highlight, highlight_order, published_ts),
    INDEX idx_feed_items_featured (featured, published_ts),
    CONSTRAINT fk_feed_items_source FOREIGN KEY (source_id) REFERENCES feed_sources(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS feed_refresh_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_id VARCHAR(64) NULL,
    status VARCHAR(20) NOT NULL,
    message VARCHAR(1000) NULL,
    items_seen INT NOT NULL DEFAULT 0,
    items_saved INT NOT NULL DEFAULT 0,
    duration_ms INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_feed_logs_created (created_at),
    INDEX idx_feed_logs_source (source_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 🏠 CONTROL CENTER V3 — Home Layout + Scheduled Content
-- =========================================================
CREATE TABLE IF NOT EXISTS home_layout_sections (
    section_key VARCHAR(80) NOT NULL,
    title VARCHAR(255) NOT NULL,
    section_type VARCHAR(40) NOT NULL,
    source_ref VARCHAR(120) NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 100,
    item_limit INT NOT NULL DEFAULT 6,
    style VARCHAR(40) NOT NULL DEFAULT 'list',
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (section_key),
    INDEX idx_home_layout_order (enabled, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_schedules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    content_type VARCHAR(30) NOT NULL,
    content_id VARCHAR(190) NOT NULL,
    mode VARCHAR(30) NOT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NULL,
    started_at DATETIME NULL,
    ended_at DATETIME NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_schedule_due (status, starts_at, ends_at),
    INDEX idx_schedule_content (content_type, content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- Automation Center / Story Intelligence
-- =========================================================

CREATE TABLE IF NOT EXISTS story_clusters (
  id CHAR(32) NOT NULL, canonical_news_id CHAR(32) NOT NULL, canonical_title VARCHAR(1000) NOT NULL,
  category VARCHAR(100) NOT NULL DEFAULT 'general', source_count INT NOT NULL DEFAULT 1,
  priority_score INT NOT NULL DEFAULT 0, is_breaking TINYINT(1) NOT NULL DEFAULT 0,
  first_pub_ts BIGINT NOT NULL DEFAULT 0, last_pub_ts BIGINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY(id), INDEX idx_story_score(priority_score,last_pub_ts), INDEX idx_story_category(category,last_pub_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS story_cluster_members (
  cluster_id CHAR(32) NOT NULL, news_id CHAR(32) NOT NULL, source_id VARCHAR(100) NOT NULL,
  similarity DECIMAL(5,4) NOT NULL DEFAULT 1, added_at DATETIME NOT NULL,
  PRIMARY KEY(cluster_id,news_id), UNIQUE KEY uniq_story_news(news_id), INDEX idx_story_member_cluster(cluster_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, action VARCHAR(80) NOT NULL, level VARCHAR(20) NOT NULL DEFAULT 'info',
  entity_type VARCHAR(40) NULL, entity_id VARCHAR(190) NULL, message VARCHAR(1000) NOT NULL, meta JSON NULL,
  created_at DATETIME NOT NULL, PRIMARY KEY(id), INDEX idx_auto_log_date(created_at), INDEX idx_auto_log_action(action,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_notifications (
  story_key VARCHAR(190) NOT NULL, sent_at DATETIME NOT NULL, title VARCHAR(500) NOT NULL,
  PRIMARY KEY(story_key), INDEX idx_auto_push_date(sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;