-- MySQL Database Schema for SportzX IPTV Web Application
-- Database Name: likhons_1_tv

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS admins;
DROP TABLE IF EXISTS channels;
DROP TABLE IF EXISTS live_events;
DROP TABLE IF EXISTS highlights;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS playlists;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Admins Table
CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Categories Table
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  logo_url VARCHAR(500) DEFAULT NULL,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2b. Playlists Table
CREATE TABLE playlists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  url VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Channels Table
CREATE TABLE channels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  stream_url TEXT NOT NULL,
  logo_url VARCHAR(500) DEFAULT NULL,
  category_id INT DEFAULT NULL,
  playlist_id INT DEFAULT NULL,
  servers_json TEXT DEFAULT NULL, -- JSON array containing fallback stream URLs
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Live Events Table
CREATE TABLE live_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  league VARCHAR(255) DEFAULT NULL,
  team1_name VARCHAR(255) DEFAULT NULL,
  team1_logo VARCHAR(500) DEFAULT NULL,
  team2_name VARCHAR(255) DEFAULT NULL,
  team2_logo VARCHAR(500) DEFAULT NULL,
  start_time VARCHAR(100) DEFAULT NULL,
  status VARCHAR(50) DEFAULT 'Upcoming', -- 'Live', 'Upcoming', 'Ended'
  category VARCHAR(100) DEFAULT 'Sports', -- 'All', 'Football', 'Cricket', 'WWE', 'IPL'
  servers_json TEXT DEFAULT NULL, -- JSON array of server streams: [{"name":"Server 1", "url": "..."}]
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Highlights Table
CREATE TABLE highlights (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  league VARCHAR(255) DEFAULT NULL,
  team1_name VARCHAR(255) DEFAULT NULL,
  team1_logo VARCHAR(500) DEFAULT NULL,
  team2_name VARCHAR(255) DEFAULT NULL,
  team2_logo VARCHAR(500) DEFAULT NULL,
  score1 VARCHAR(50) DEFAULT '0',
  score2 VARCHAR(50) DEFAULT '0',
  video_url TEXT NOT NULL,
  category VARCHAR(100) DEFAULT 'All', -- 'All', 'Football', 'Cricket', 'WWE', 'IPL'
  date VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Settings Table
CREATE TABLE settings (
  `key` VARCHAR(150) PRIMARY KEY,
  `value` TEXT DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user: username=admin, password=adminpass
-- Hashed using bcrypt ($2a$10$T1KqL2j38C2d.2.p.D2wKei11E.yv4fO8d0sA5vHl7H4W52c0k1U6)
INSERT INTO admins (username, password) VALUES ('admin', '$2a$10$T1KqL2j38C2d.2.p.D2wKei11E.yv4fO8d0sA5vHl7H4W52c0k1U6');

-- Insert default announcement text
INSERT INTO settings (`key`, `value`) VALUES ('announcement', 'Welcome to SportzX! Paste your M3U playlist file inside the secure /admin panel to sync channels. Enjoy high-quality streaming!');
