

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  description TEXT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(8) NOT NULL DEFAULT 'BRL',
  interval_unit ENUM('day','week','month','year') NOT NULL DEFAULT 'month',
  interval_count INT NOT NULL DEFAULT 1,
  max_storage_gb INT NOT NULL DEFAULT 10,
  max_bandwidth_gb INT NOT NULL DEFAULT 100,
  max_video_duration_min INT NOT NULL DEFAULT 120,
  trial_days INT NOT NULL DEFAULT 0,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  provider_plan_id_mp VARCHAR(120) NULL,
  provider_plan_id_pg VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  provider ENUM('mercadopago','pagbank','manual') NOT NULL,
  status ENUM('pending','authorized','active','paused','canceled','expired','payment_failed') NOT NULL DEFAULT 'pending',
  provider_plan_id VARCHAR(120) NULL,
  provider_subscription_id VARCHAR(120) NULL,
  external_reference VARCHAR(120) NULL,
  started_at DATETIME NULL,
  current_period_start DATETIME NULL,
  current_period_end DATETIME NULL,
  canceled_at DATETIME NULL,
  raw_payload JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_sub_user (user_id),
  KEY idx_sub_provider (provider, provider_subscription_id),
  KEY idx_sub_status (status),
  CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS videos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  storage_type ENUM('local','external') NOT NULL DEFAULT 'local',
  video_path VARCHAR(255) NULL,
  external_url VARCHAR(500) NULL,
  hls_master_path VARCHAR(255) NULL,
  visibility ENUM('public','private') NOT NULL DEFAULT 'private',
  status ENUM('processing','ready','failed') NOT NULL DEFAULT 'processing',
  original_filename VARCHAR(190) NULL,
  mime_type VARCHAR(120) NULL,
  file_size_bytes BIGINT NOT NULL DEFAULT 0,
  source_duration_seconds INT NULL,
  playback_starts INT NOT NULL DEFAULT 0,
  processing_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_video_user (user_id),
  KEY idx_video_status (status),
  CONSTRAINT fk_video_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS video_renditions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  video_id INT NOT NULL,
  label VARCHAR(30) NOT NULL, -- 360p / 720p / 1080p
  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;

CREATE TABLE IF NOT EXISTS webhook_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  provider ENUM('mercadopago','pagbank','manual') NOT NULL,
  event_type VARCHAR(120) NULL,
  event_id VARCHAR(120) NULL,
  payload JSON NULL,
  headers JSON NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_webhook_provider_event (provider, event_type, event_id),
  KEY idx_webhook_processed (processed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Plano inicial
INSERT INTO plans
(name,slug,description,price,currency,interval_unit,interval_count,max_storage_gb,max_bandwidth_gb,max_video_duration_min,trial_days,is_enabled)
VALUES
('Starter','starter','Plano inicial para criadores',29.90,'BRL','month',1,50,500,120,7,1),
('Pro','pro','Para operação profissional com mais tráfego',99.90,'BRL','month',1,250,3000,240,7,1)
ON DUPLICATE KEY UPDATE name=VALUES(name), description=VALUES(description);
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;
