-- ============================================================
--  Projukti TV — MySQL Database Schema v3.0
--  Developer: Ziaul Islam
--  Features: Multi-source, EPG, analytics, custom embed
-- ============================================================

CREATE DATABASE IF NOT EXISTS projukti_tv
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE projukti_tv;

-- ─── ADMIN USERS ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admin_users (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(50)  NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(150) DEFAULT NULL,
    role       ENUM('superadmin','admin') NOT NULL DEFAULT 'admin',
    last_login DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default: zihad / zihad  (run generate_hash.php to regenerate)
INSERT INTO admin_users (username, password, full_name, role) VALUES
('zihad','$2y$12$RISCm.6fRAH.vvD89w.h0.WgWRcm.iC8tQ.KRlOIYBf6yJ.3TZPCS','Zihad Admin','superadmin');

-- ─── CATEGORIES ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS categories (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_bn    VARCHAR(100) NOT NULL,
    name_en    VARCHAR(100) NOT NULL,
    icon       VARCHAR(80)  DEFAULT 'fa-tv',
    color      VARCHAR(20)  DEFAULT '#00c6ff',
    sort_order TINYINT UNSIGNED DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO categories (name_bn, name_en, icon, color, sort_order) VALUES
('সব',          'All',           'fa-th-large',    '#00c6ff', 0),
('সংবাদ',       'News',          'fa-newspaper',   '#ff4444', 1),
('বিনোদন',     'Entertainment', 'fa-film',         '#9b59b6', 2),
('খেলাধুলা',   'Sports',        'fa-futbol',       '#00d084', 3),
('ধর্মীয়',     'Religious',     'fa-mosque',       '#f39c12', 4),
('শিশু',        'Kids',          'fa-child',        '#e91e63', 5),
('সরকারি',     'Government',    'fa-landmark',     '#3498db', 6),
('আন্তর্জাতিক','International', 'fa-globe',        '#1abc9c', 7),
('সঙ্গীত',     'Music',         'fa-music',        '#e74c3c', 8);

-- ─── CHANNELS ────────────────────────────────────────────
-- source_type:
--   'hls'      → .m3u8 stream via HLS.js
--   'youtube'  → YouTube original URL (auto extract video ID)
--   'iframe'   → any embed URL loaded in iframe
--   'custom'   → raw HTML/iframe code pasted by admin
CREATE TABLE IF NOT EXISTS channels (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(150)  NOT NULL,
    name_en       VARCHAR(150)  DEFAULT NULL,
    logo_url      VARCHAR(500)  NOT NULL,
    source_type   ENUM('hls','youtube','iframe','custom') NOT NULL DEFAULT 'iframe',
    stream_url    VARCHAR(2000) DEFAULT NULL,  -- for hls/youtube/iframe
    custom_code   TEXT          DEFAULT NULL,  -- for custom HTML/iframe code
    -- Quality variants (JSON array for HLS multi-quality)
    quality_json  TEXT          DEFAULT NULL,  -- [{"label":"1080p","url":"..."},...]
    -- Metadata
    description   TEXT          DEFAULT NULL,
    tags          VARCHAR(500)  DEFAULT NULL,  -- comma-separated
    category_id   INT UNSIGNED  DEFAULT NULL,
    sort_order    SMALLINT UNSIGNED DEFAULT 0,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    is_featured   TINYINT(1)    NOT NULL DEFAULT 0,
    is_18plus     TINYINT(1)    NOT NULL DEFAULT 0,
    view_count    INT UNSIGNED  NOT NULL DEFAULT 0,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Sample channels
INSERT INTO channels (name, logo_url, source_type, stream_url, category_id, sort_order, is_featured) VALUES
('T Sports',
 'https://upload.wikimedia.org/wikipedia/en/a/a8/T_Sports_logo.png',
 'iframe',
 'https://tv.roarzone.net/player.php?stream=edge2/tsports',
 4, 1, 1),

('Desh TV',
 'https://upload.wikimedia.org/wikipedia/en/0/07/Desh_TV_Bangladesh_logo.png',
 'hls',
 'https://bozztv.com/rongo/rongo-DeshTV/tracks-v2a1/mono.m3u8',
 2, 2, 1),

('BTV National',
 'https://upload.wikimedia.org/wikipedia/commons/thumb/7/7c/Bangladesh_Television.svg/200px-Bangladesh_Television.svg.png',
 'youtube',
 'https://www.youtube.com/watch?v=live',
 7, 3, 0),

('NTV BD',
 'https://upload.wikimedia.org/wikipedia/en/e/e8/NTV_Bangladesh_logo.png',
 'iframe',
 'https://www.youtube.com/embed/live_stream?channel=UCqrGFG9NU_vI8kVQkJf8tCQ',
 2, 4, 0);

-- ─── VIEW LOG (analytics) ────────────────────────────────
CREATE TABLE IF NOT EXISTS view_log (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_id INT UNSIGNED NOT NULL,
    ip_hash    VARCHAR(64)  DEFAULT NULL,  -- hashed for privacy
    user_agent VARCHAR(300) DEFAULT NULL,
    device     ENUM('desktop','mobile','tablet','unknown') DEFAULT 'unknown',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_channel (channel_id),
    INDEX idx_date    (created_at)
) ENGINE=InnoDB;

-- ─── ACTIVITY LOG ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS activity_log (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id   INT UNSIGNED DEFAULT NULL,
    action     VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ─── SITE SETTINGS ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS settings (
    setting_key   VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('site_name',        'Projukti TV'),
('site_tagline',     'আপনার পছন্দের চ্যানেল, যেকোনো সময়'),
('developer_name',   'Ziaul Islam'),
('developer_url',    'https://www.facebook.com/'),
('facebook_url',     'https://www.facebook.com/'),
('ticker_text',      'স্বাগতম Projukti TV তে — সেরা বাংলা চ্যানেল সমূহ উপভোগ করুন'),
('logo_url',         ''),
('favicon_url',      ''),
('primary_color',    '#00c6ff'),
('show_view_count',  '1'),
('show_analytics',   '1'),
('hls_buffer_sec',   '30'),
('yt_autoplay',      '1');

-- ─── UPGRADE from v2.x (run if upgrading) ────────────────
-- ALTER TABLE channels
--   ADD COLUMN source_type ENUM('hls','youtube','iframe','custom') NOT NULL DEFAULT 'iframe' AFTER logo_url,
--   ADD COLUMN custom_code TEXT DEFAULT NULL AFTER stream_url,
--   ADD COLUMN quality_json TEXT DEFAULT NULL AFTER custom_code,
--   ADD COLUMN description TEXT DEFAULT NULL AFTER quality_json,
--   ADD COLUMN tags VARCHAR(500) DEFAULT NULL AFTER description,
--   ADD COLUMN is_18plus TINYINT(1) NOT NULL DEFAULT 0 AFTER is_featured,
--   ADD COLUMN view_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER is_18plus,
--   MODIFY COLUMN stream_url VARCHAR(2000) DEFAULT NULL;
-- ALTER TABLE categories
--   ADD COLUMN icon VARCHAR(80) DEFAULT 'fa-tv' AFTER name_en,
--   ADD COLUMN color VARCHAR(20) DEFAULT '#00c6ff' AFTER icon;

-- ═══════════════════════════════════════════════════════
--  v3.1 ADDITIONS — Notices, Ads, User Preferences
-- ═══════════════════════════════════════════════════════

-- ─── NOTICES ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notices (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    message    TEXT NOT NULL,
    type       ENUM('info','success','warning','danger') DEFAULT 'info',
    icon       VARCHAR(50)  DEFAULT 'fa-bell',
    link_text  VARCHAR(100) DEFAULT NULL,
    link_url   VARCHAR(500) DEFAULT NULL,
    target     ENUM('all','mobile','desktop') DEFAULT 'all',
    is_active  TINYINT(1)   NOT NULL DEFAULT 1,
    starts_at  DATETIME     DEFAULT NULL,
    ends_at    DATETIME     DEFAULT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO notices (title, message, type, icon, is_active) VALUES
('স্বাগতম!', 'Projukti TV তে আপনাকে স্বাগতম। সেরা বাংলা চ্যানেল উপভোগ করুন।', 'info', 'fa-satellite-dish', 1);

-- ─── ADS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ads (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(150) NOT NULL,
    ad_type      ENUM('banner_top','banner_bottom','sidebar','video_pre','video_mid','popup') NOT NULL,
    media_type   ENUM('image','video','html','adsense') NOT NULL DEFAULT 'image',
    content      TEXT         NOT NULL,   -- image URL / video URL / HTML code / adsense code
    link_url     VARCHAR(500) DEFAULT NULL,
    link_target  ENUM('_blank','_self') DEFAULT '_blank',
    device       ENUM('all','mobile','desktop') DEFAULT 'all',
    width        VARCHAR(20)  DEFAULT NULL,
    height       VARCHAR(20)  DEFAULT NULL,
    -- Video ad settings
    skip_after   INT UNSIGNED DEFAULT 5,   -- seconds before skip button (0 = not skippable)
    duration     INT UNSIGNED DEFAULT 30,  -- video ad duration (seconds)
    -- Scheduling
    is_active    TINYINT(1)   NOT NULL DEFAULT 1,
    starts_at    DATETIME     DEFAULT NULL,
    ends_at      DATETIME     DEFAULT NULL,
    -- Stats
    impressions  INT UNSIGNED NOT NULL DEFAULT 0,
    clicks       INT UNSIGNED NOT NULL DEFAULT 0,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO ads (name, ad_type, media_type, content, link_url, device, width, height) VALUES
('Sample Banner Top', 'banner_top', 'html',
 '<div style="text-align:center;padding:8px;background:linear-gradient(90deg,#1a1a2e,#16213e);color:#fff;font-size:13px">📺 Projukti TV — সেরা বাংলা লাইভ TV অ্যাপ ডাউনলোড করুন</div>',
 NULL, 'all', '100%', '40px');

-- ─── USER PREFERENCES ────────────────────────────────
CREATE TABLE IF NOT EXISTS user_preferences (
    session_id    VARCHAR(128) PRIMARY KEY,
    layout        ENUM('sidebar','grid','theatre') DEFAULT 'sidebar',
    quality       ENUM('auto','low','medium','high') DEFAULT 'auto',
    volume        TINYINT UNSIGNED DEFAULT 100,
    notifications TINYINT(1) DEFAULT 1,
    theme         VARCHAR(20) DEFAULT 'dark',
    notices_seen  TEXT DEFAULT NULL,  -- JSON array of seen notice IDs
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Add new setting keys
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('notice_bar_enabled', '1'),
('video_ads_enabled',  '1'),
('banner_ads_enabled', '1'),
('popup_ads_enabled',  '0'),
('ad_refresh_seconds', '300');
