START TRANSACTION;

/* =========================
   USERS
========================= */
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(120) UNIQUE NOT NULL,
  phone VARCHAR(20),
  password VARCHAR(255) NOT NULL,
  status ENUM('active','blocked') DEFAULT 'active',
  deleted_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

/* =========================
   ADMINS
========================= */
CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120),
  email VARCHAR(120) UNIQUE,
  password VARCHAR(255),
  role ENUM('super','editor') DEFAULT 'editor',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

/* =========================
   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)
);

/* =========================
   SETTINGS
========================= */
CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) UNIQUE,
  `value` TEXT
);

/* =========================
   DEFAULT DATA
========================= */
INSERT INTO settings (`key`,`value`) VALUES
('site_name','Premium Video'),
('payment_enabled','1'),
('sohojpay_store_id','CHANGE_ME'),
('sohojpay_secret','CHANGE_ME');

/* =========================
   DEFAULT ADMIN
========================= */
INSERT INTO admins (name,email,password,role) VALUES
('Super Admin','admin@site.com',
'$2y$10$8qvHj8eM8eJZ7xJjZgOe5uC7m7m7m7m7m7m7m7m7m7m7m7m7m7m','super');

COMMIT;
