-- Migration 002 - Pipeline de vídeos + limites de uso (MySQL 8+)
-- Rode após o schema antigo, para atualizar instalação existente.

ALTER TABLE plans ADD COLUMN IF NOT EXISTS slug VARCHAR(140) NOT NULL UNIQUE AFTER name;

ALTER TABLE videos
  ADD COLUMN IF NOT EXISTS hls_master_path VARCHAR(255) NULL AFTER external_url,
  ADD COLUMN IF NOT EXISTS original_filename VARCHAR(190) NULL AFTER status,
  ADD COLUMN IF NOT EXISTS mime_type VARCHAR(120) NULL AFTER original_filename,
  ADD COLUMN IF NOT EXISTS file_size_bytes BIGINT NOT NULL DEFAULT 0 AFTER mime_type,
  ADD COLUMN IF NOT EXISTS source_duration_seconds INT NULL AFTER file_size_bytes,
  ADD COLUMN IF NOT EXISTS playback_starts INT NOT NULL DEFAULT 0 AFTER source_duration_seconds,
  ADD COLUMN IF NOT EXISTS processing_error TEXT NULL AFTER playback_starts;

CREATE TABLE IF NOT EXISTS video_renditions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  video_id INT NOT NULL,
  label VARCHAR(30) NOT NULL,
  width INT NOT NULL,
  height INT NOT NULL,
  bitrate_kbps INT NOT NULL,
  playlist_file VARCHAR(160) NOT NULL,
  status ENUM('ready','failed') NOT NULL DEFAULT 'ready',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rend_video (video_id),
  CONSTRAINT fk_rend_video FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS video_jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  video_id INT NOT NULL,
  job_type ENUM('transcode_hls') NOT NULL DEFAULT 'transcode_hls',
  status ENUM('queued','processing','done','failed') NOT NULL DEFAULT 'queued',
  priority TINYINT NOT NULL DEFAULT 5,
  attempts INT NOT NULL DEFAULT 0,
  max_attempts INT NOT NULL DEFAULT 3,
  payload JSON NULL,
  run_after DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_jobs_status_run (status, run_after),
  KEY idx_jobs_video (video_id),
  CONSTRAINT fk_jobs_video FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bandwidth_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  video_id INT NOT NULL,
  bytes_sent BIGINT NOT NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'hls_segment',
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bw_user_date (user_id, created_at),
  KEY idx_bw_video (video_id, created_at),
  CONSTRAINT fk_bw_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_bw_video FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS current_plan_id INT NULL;

SET @fk_exists := (
  SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND CONSTRAINT_NAME = 'fk_user_plan'
);
SET @sql_fk := IF(@fk_exists = 0,
  'ALTER TABLE users ADD CONSTRAINT fk_user_plan FOREIGN KEY (current_plan_id) REFERENCES plans(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt FROM @sql_fk;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
