START TRANSACTION;

/* =========================
   CATEGORIES
========================= */
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  slug VARCHAR(120) UNIQUE,
  status TINYINT DEFAULT 1
);

/* =========================
   VIDEOS
========================= */
CREATE TABLE videos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  description TEXT,
  category_id INT,
  source VARCHAR(255),
  thumbnail VARCHAR(255),
  is_premium TINYINT DEFAULT 0,
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

/* =========================
   SUBSCRIPTION PLANS
========================= */
CREATE TABLE subscription_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  duration_days INT,
  status ENUM('active','inactive') DEFAULT 'active'
);

/* =========================
   USER SUBSCRIPTIONS
========================= */
CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  plan_id INT,
  start_date DATETIME,
  end_date DATETIME,
  status ENUM('active','expired') DEFAULT 'active',
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

/* =========================
   PAYMENTS
========================= */
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  plan_id INT,
  amount DECIMAL(10,2),
  method VARCHAR(50),
  transaction_id VARCHAR(120) UNIQUE,
  status ENUM('pending','paid','failed') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

/* =========================
   WATCH HISTORY
========================= */
CREATE TABLE watch_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  video_id INT,
  watched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (video_id) REFERENCES videos(id)
);

COMMIT;
