-- Database: viral_video_platform
-- Character Set: utf8mb4_unicode_ci
-- Collation: utf8mb4_unicode_ci

-- ============================================
-- Table: users
-- ============================================
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
  username VARCHAR(255),
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  language_code VARCHAR(10) DEFAULT 'bn',
  
  -- VIP fields
  vip_status TINYINT(1) DEFAULT 0,
  vip_package_id INT UNSIGNED,
  vip_start_date DATETIME,
  vip_expire_date DATETIME,
  vip_channel_joined TINYINT(1) DEFAULT 0,
  vip_group_joined TINYINT(1) DEFAULT 0,
  
  -- Referral fields
  referrer_id INT UNSIGNED,                   -- Who referred this user
  referral_code VARCHAR(20) UNIQUE,           -- This user's referral code
  referral_count INT UNSIGNED DEFAULT 0,      -- How many users this user referred
  ad_discount_tokens INT UNSIGNED DEFAULT 0,  -- Available discount tokens
  
  -- Tracking
  ip_address VARCHAR(45),
  device_fingerprint VARCHAR(255),
  user_agent TEXT,
  
  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_active_at DATETIME,
  
  INDEX idx_telegram_id (telegram_id),
  INDEX idx_referrer_id (referrer_id),
  INDEX idx_vip_expire (vip_expire_date),
  INDEX idx_referral_code (referral_code),
  
  FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE SET NULL
  -- Will add FK constraint for vip_package_id after table creation
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================
-- Table: video_series
-- ============================================
CREATE TABLE video_series (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  thumbnail_url VARCHAR(500),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: videos
-- ============================================
CREATE TABLE videos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  video_id VARCHAR(50) NOT NULL UNIQUE,       -- abc123, xyz456 (for URLs)
  
  -- Telegram file storage
  file_id VARCHAR(255) NOT NULL,              -- Telegram file_id (BQACAgI...)
  
  -- Metadata
  title VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(50),                        -- TikToker, Actress, Model, Other
  series_id INT UNSIGNED,                      -- For grouping related videos
  duration INT UNSIGNED,                       -- In seconds
  
  -- Thumbnails
  thumbnail_url VARCHAR(500),                  -- Uploaded image path
  
  -- Ad configuration (stored as JSON for flexibility)
  monetag_zone VARCHAR(50) DEFAULT '10138126',
  monetag_count TINYINT UNSIGNED DEFAULT 3,
  gigapub_zone VARCHAR(50) DEFAULT '4307',
  gigapub_count TINYINT UNSIGNED DEFAULT 2,
  adsgram_block VARCHAR(50) DEFAULT 'int-23641',
  adsgram_count TINYINT UNSIGNED DEFAULT 2,
  total_ad_count TINYINT UNSIGNED GENERATED ALWAYS AS (monetag_count + gigapub_count + adsgram_count) STORED,
  
  -- SEO/OG tags
  og_title VARCHAR(255),
  og_description TEXT,
  og_image VARCHAR(500),
  
  -- Engagement metrics
  view_count INT UNSIGNED DEFAULT 0,
  like_count INT UNSIGNED DEFAULT 0,
  
  -- Visibility
  status ENUM('draft', 'published', 'scheduled', 'archived') DEFAULT 'published',
  scheduled_at DATETIME,
  is_featured TINYINT(1) DEFAULT 0,
  is_trending TINYINT(1) DEFAULT 0,
  
  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  published_at DATETIME,
  
  INDEX idx_video_id (video_id),
  INDEX idx_category (category),
  INDEX idx_status (status),
  INDEX idx_trending (is_trending),
  INDEX idx_scheduled (scheduled_at),
  FULLTEXT idx_search (title, description),
  
  FOREIGN KEY (series_id) REFERENCES video_series(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: tokens
-- ============================================
CREATE TABLE tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token VARCHAR(255) NOT NULL UNIQUE,         -- Encrypted token
  user_id INT UNSIGNED NOT NULL,
  video_id VARCHAR(50) NOT NULL,
  
  -- Validation flags
  is_used TINYINT(1) DEFAULT 0,
  is_discount_applied TINYINT(1) DEFAULT 0,   -- If referral discount used
  
  -- Expiry
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,               -- 5 minutes from creation
  used_at DATETIME,
  
  -- Tracking
  ip_address VARCHAR(45),
  user_agent TEXT,
  
  INDEX idx_token (token),
  INDEX idx_user_id (user_id),
  INDEX idx_expires (expires_at),
  INDEX idx_used (is_used),
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: watch_history
-- ============================================
CREATE TABLE watch_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  video_id VARCHAR(50) NOT NULL,
  
  -- Completion tracking
  ad_count_shown TINYINT UNSIGNED,            -- How many ads user watched
  completion_status ENUM('started', 'ads_completed', 'video_watched') DEFAULT 'started',
  
  -- Timestamps
  started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME,
  
  INDEX idx_user_id (user_id),
  INDEX idx_video_id (video_id),
  INDEX idx_completion (completion_status),
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: subscription_packages
-- ============================================
CREATE TABLE subscription_packages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,                 -- "Weekly VIP", "Monthly VIP"
  price DECIMAL(10,2) NOT NULL,               -- In BDT
  duration_days INT UNSIGNED NOT NULL,        -- 7, 30, 365
  description TEXT,
  status ENUM('active', 'disabled') DEFAULT 'active',
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add FK missing from users table
ALTER TABLE users ADD CONSTRAINT fk_vip_package FOREIGN KEY (vip_package_id) REFERENCES subscription_packages(id) ON DELETE SET NULL;

-- ============================================
-- Table: admin_users
-- ============================================
CREATE TABLE admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
  username VARCHAR(255),
  role ENUM('super_admin', 'admin', 'moderator') DEFAULT 'moderator',
  
  -- Permissions (JSON or separate table)
  permissions JSON,                           -- {"videos": "write", "users": "read"}
  
  -- Status
  status ENUM('active', 'suspended') DEFAULT 'active',
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_login_at DATETIME,
  
  INDEX idx_telegram_id (telegram_id),
  INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================
-- Table: subscription_transactions
-- ============================================
CREATE TABLE subscription_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  package_id INT UNSIGNED NOT NULL,
  
  -- Payment details (manual verification)
  payment_method ENUM('bkash', 'nagad', 'rocket', 'other'),
  payment_screenshot VARCHAR(500),            -- Path to uploaded screenshot
  payment_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
  
  -- Admin verification
  verified_by INT UNSIGNED,                   -- Admin user ID
  verified_at DATETIME,
  rejection_reason TEXT,
  
  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_user_id (user_id),
  INDEX idx_status (payment_status),
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES subscription_packages(id) ON DELETE CASCADE,
  FOREIGN KEY (verified_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: admin_sessions
-- ============================================
CREATE TABLE admin_sessions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NOT NULL,
  session_token VARCHAR(255) NOT NULL UNIQUE,
  ip_address VARCHAR(45),
  user_agent TEXT,
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  
  INDEX idx_token (session_token),
  INDEX idx_expires (expires_at),
  
  FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: analytics_events
-- ============================================
CREATE TABLE analytics_events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(50) NOT NULL,            -- 'video_view', 'ad_impression', 'referral_click'
  user_id INT UNSIGNED,
  video_id VARCHAR(50),
  
  -- Event metadata (JSON)
  metadata JSON,                              -- {"ad_network": "monetag", "duration": 15}
  
  -- Source tracking
  referrer_url VARCHAR(500),
  utm_source VARCHAR(100),
  utm_medium VARCHAR(100),
  utm_campaign VARCHAR(100),
  
  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_event_type (event_type),
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: bot_messages (for debugging)
-- ============================================
CREATE TABLE bot_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bot_name VARCHAR(50) NOT NULL,              -- 'bot1' or 'bot2'
  direction ENUM('incoming', 'outgoing') NOT NULL,
  
  -- Telegram data
  chat_id BIGINT,
  message_id BIGINT,
  update_type VARCHAR(50),                    -- 'message', 'callback_query', etc.
  
  -- Payload
  payload JSON,                               -- Full Telegram update object
  
  -- Response
  response JSON,                              -- Bot's response
  http_status INT,
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_bot (bot_name),
  INDEX idx_chat_id (chat_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Initial Data
-- ============================================

-- Insert default subscription packages
INSERT INTO subscription_packages (name, price, duration_days, description) VALUES
('Weekly VIP', 50.00, 7, 'এক সপ্তাহের জন্য Ad ছাড়া ভিডিও দেখুন'),
('Monthly VIP', 100.00, 30, 'এক মাসের জন্য Ad ছাড়া ভিডিও দেখুন'),
('Yearly VIP', 800.00, 365, 'এক বছরের জন্য Ad ছাড়া ভিডিও দেখুন');

-- Insert super admin (replace with actual Telegram ID)
INSERT INTO admin_users (telegram_id, username, role, status) VALUES
(123456789, 'SuperAdmin', 'super_admin', 'active');
