-- ============================================================ -- 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', '
📺 Projukti TV — সেরা বাংলা লাইভ TV অ্যাপ ডাউনলোড করুন
', 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');