-- YT OTT Database Schema (MySQL)

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('Admin', 'User') DEFAULT 'User',
  `status` ENUM('Active', 'Blocked') DEFAULT 'Active',
  `device_usage` INT DEFAULT 0,
  `location` VARCHAR(100) DEFAULT 'Unknown',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `movies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `url` VARCHAR(255),
  `thumbnail` VARCHAR(255),
  `content_type` ENUM('MOVIE', 'WEB SERIES', 'KIDS MOVIE', 'KIDS SERIAL', 'DOCUMENT/AI') DEFAULT 'MOVIE',
  `genre` VARCHAR(255),
  `language` VARCHAR(255),
  `release_year` VARCHAR(10),
  `duration` VARCHAR(50),
  `cast` TEXT,
  `origin` ENUM('Direct', 'Dubbed') DEFAULT 'Direct',
  `status` ENUM('Active', 'Inactive') DEFAULT 'Inactive',
  `view_count` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `episodes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `movie_id` INT NOT NULL,
  `season_number` INT DEFAULT 1,
  `episode_number` INT NOT NULL,
  `title` VARCHAR(255),
  `url` VARCHAR(255) NOT NULL,
  `thumbnail` VARCHAR(255),
  `duration` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `live_channels` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `url` VARCHAR(255) NOT NULL,
  `logo` VARCHAR(255),
  `category` VARCHAR(100),
  `language` VARCHAR(100),
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `vip_plans` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `duration_days` INT NOT NULL,
  `device_limit` INT DEFAULT 1,
  `features` TEXT,
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `plan_id` INT NOT NULL,
  `amount` DECIMAL(10,2),
  `status` ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plan_id`) REFERENCES `vip_plans`(`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `youtube_channels` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `channel_id` VARCHAR(100) NOT NULL UNIQUE,
  `channel_url` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255),
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);