-- ============================================================
-- SIRA+ — Migration v2 (idempotente — sûre à relancer)
-- Importer dans phpMyAdmin sur aesagro_siraplus
-- Compatible MySQL 5.7+ et MariaDB 10.3+
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- TABLES NOUVELLES (CREATE IF NOT EXISTS = toujours sûr)
-- ============================================================

-- Rate limiting (anti brute-force)
CREATE TABLE IF NOT EXISTS login_attempts (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip           VARCHAR(45)  NOT NULL,
  email        VARCHAR(190) NOT NULL DEFAULT '',
  attempted_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip_time    (ip, attempted_at),
  INDEX idx_email_time (email, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Réinitialisation de mot de passe
CREATE TABLE IF NOT EXISTS password_resets (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  token      VARCHAR(64)  NOT NULL UNIQUE,
  expires_at DATETIME     NOT NULL,
  used       TINYINT(1)   NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_token (token),
  INDEX idx_user  (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Vérification d'email
CREATE TABLE IF NOT EXISTS email_verifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  token      VARCHAR(64)  NOT NULL UNIQUE,
  expires_at DATETIME     NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notes / évaluations
CREATE TABLE IF NOT EXISTS ratings (
  user_id    INT UNSIGNED NOT NULL,
  content_id INT UNSIGNED NOT NULL,
  score      TINYINT 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;

-- Sous-titres
CREATE TABLE IF NOT EXISTS subtitles (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_id INT UNSIGNED NOT NULL,
  episode_id INT UNSIGNED DEFAULT NULL,
  language   VARCHAR(40)  NOT NULL DEFAULT 'Français',
  lang_code  VARCHAR(5)   NOT NULL DEFAULT 'fr',
  file_path  VARCHAR(255) NOT NULL,
  FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE CASCADE,
  FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sessions actives (limite multi-écrans)
CREATE TABLE IF NOT EXISTS active_sessions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  session_token VARCHAR(64)  NOT NULL UNIQUE,
  ip            VARCHAR(45)  NOT NULL,
  user_agent    VARCHAR(255) NOT NULL DEFAULT '',
  last_seen     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_seen (user_id, last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Logs d'accès vidéo
CREATE TABLE IF NOT EXISTS video_access_logs (
  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,
  ip          VARCHAR(45)  NOT NULL,
  accessed_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_date (user_id, accessed_at),
  INDEX idx_content   (content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notifications in-app
CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  type       ENUM('sub_expiring','sub_expired','new_content','payment_ok') NOT NULL,
  message    TEXT         NOT NULL,
  is_read    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_read (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Stats mensuelles
CREATE TABLE IF NOT EXISTS monthly_stats (
  ym        CHAR(7)       NOT NULL COMMENT 'YYYY-MM',
  revenue   INT UNSIGNED  NOT NULL DEFAULT 0,
  new_users INT UNSIGNED  NOT NULL DEFAULT 0,
  new_subs  INT UNSIGNED  NOT NULL DEFAULT 0,
  PRIMARY KEY (ym)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- COLONNES NOUVELLES — via procédure pour être idempotent
-- (ALTER TABLE ADD COLUMN IF NOT EXISTS n'existe pas sur MySQL 5.7)
-- ============================================================

DROP PROCEDURE IF EXISTS sira_add_column;

DELIMITER $$
CREATE PROCEDURE sira_add_column(
  IN tbl  VARCHAR(64),
  IN col  VARCHAR(64),
  IN def  TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = tbl
      AND COLUMN_NAME  = col
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', tbl, '` ADD COLUMN ', def);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

-- users.email_verified
CALL sira_add_column('users', 'email_verified',
  '`email_verified` TINYINT(1) NOT NULL DEFAULT 0 AFTER `phone`');

-- contents.rating_sum
CALL sira_add_column('contents', 'rating_sum',
  '`rating_sum` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `views`');

-- contents.rating_count
CALL sira_add_column('contents', 'rating_count',
  '`rating_count` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `rating_sum`');

-- episodes.thumbnail  (existait déjà en v1 — la procédure l'ignore silencieusement)
CALL sira_add_column('episodes', 'thumbnail',
  '`thumbnail` VARCHAR(255) DEFAULT NULL AFTER `video_file`');

-- Nettoyage
DROP PROCEDURE IF EXISTS sira_add_column;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- FIN — Migration v2 appliquée avec succès
-- ============================================================