-- ============================================================
-- SIRA+ — Plateforme de streaming africaine
-- Schéma MySQL / MariaDB (utf8mb4)
-- Importer via phpMyAdmin sur cPanel
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Utilisateurs ----------
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(30) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Plans d'abonnement ----------
CREATE TABLE IF NOT EXISTS plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  price INT UNSIGNED NOT NULL COMMENT 'Prix en FCFA',
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  max_quality VARCHAR(10) NOT NULL DEFAULT '720p',
  description VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans (name, price, duration_days, max_quality, description) VALUES
('Découverte', 1000, 7,  '480p', 'Accès complet pendant 7 jours'),
('Classique',  2500, 30, '720p', 'Le catalogue complet en HD pendant 1 mois'),
('Premium',    4000, 30, '1080p', 'Full HD, le meilleur de Sira+ pendant 1 mois');

-- ---------- Abonnements ----------
CREATE TABLE IF NOT EXISTS subscriptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  INDEX idx_sub_user (user_id, status, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Paiements (Orange Money / Moov / Wave / LigdiCash...) ----------
CREATE TABLE IF NOT EXISTS payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  amount INT UNSIGNED NOT NULL,
  method VARCHAR(40) NOT NULL DEFAULT 'orange_money',
  reference VARCHAR(100) DEFAULT NULL,
  status ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Genres ----------
CREATE TABLE IF NOT EXISTS genres (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  slug VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO genres (name, slug) VALUES
('Drame','drame'),('Comédie','comedie'),('Action','action'),
('Romance','romance'),('Documentaire','documentaire'),('Histoire','histoire'),
('Thriller','thriller'),('Famille','famille'),('Musique','musique'),('Nollywood','nollywood');

-- ---------- Contenus (films & séries) ----------
CREATE TABLE IF NOT EXISTS contents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type ENUM('film','serie') NOT NULL DEFAULT 'film',
  title VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  synopsis TEXT,
  country VARCHAR(80) DEFAULT NULL COMMENT 'Pays de production',
  language VARCHAR(80) DEFAULT 'Français',
  release_year SMALLINT UNSIGNED DEFAULT NULL,
  duration_min SMALLINT UNSIGNED DEFAULT NULL COMMENT 'Durée (films)',
  poster VARCHAR(255) DEFAULT NULL COMMENT 'Affiche verticale',
  backdrop VARCHAR(255) DEFAULT NULL COMMENT 'Image horizontale',
  video_file VARCHAR(255) DEFAULT NULL COMMENT 'Fichier vidéo (films)',
  trailer_url VARCHAR(255) DEFAULT NULL,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_type (type, is_published),
  FULLTEXT KEY ft_search (title, synopsis)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_genre (
  content_id INT UNSIGNED NOT NULL,
  genre_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (content_id, genre_id),
  FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE CASCADE,
  FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Saisons & épisodes (séries) ----------
CREATE TABLE IF NOT EXISTS seasons (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_id INT UNSIGNED NOT NULL,
  number SMALLINT UNSIGNED NOT NULL,
  title VARCHAR(190) DEFAULT NULL,
  FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE CASCADE,
  UNIQUE KEY uq_season (content_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS episodes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  season_id INT UNSIGNED NOT NULL,
  number SMALLINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  synopsis TEXT,
  duration_min SMALLINT UNSIGNED DEFAULT NULL,
  video_file VARCHAR(255) DEFAULT NULL,
  thumbnail VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (season_id) REFERENCES seasons(id) ON DELETE CASCADE,
  UNIQUE KEY uq_episode (season_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Historique de lecture (reprise) ----------
CREATE TABLE IF NOT EXISTS watch_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  content_id INT UNSIGNED NOT NULL,
  episode_id INT UNSIGNED DEFAULT NULL,
  position_sec INT UNSIGNED NOT NULL DEFAULT 0,
  duration_sec INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE CASCADE,
  FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE,
  UNIQUE KEY uq_watch (user_id, content_id, episode_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Ma liste (favoris) ----------
CREATE TABLE IF NOT EXISTS favorites (
  user_id INT UNSIGNED NOT NULL,
  content_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, content_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;