-- 003_create_storyboards_videos_schedules.sql
CREATE TABLE IF NOT EXISTS storyboards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  json JSON NOT NULL,
  validated TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS videos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  provider VARCHAR(50) NOT NULL,
  external_id VARCHAR(255) NULL,
  file_url TEXT NULL,
  local_path VARCHAR(500) NULL,
  duration INT NULL,
  status ENUM('pending','rendering','ready','failed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- v1: TikTok + Instagram + Facebook only (per user ruling 2026-09-06)
CREATE TABLE IF NOT EXISTS schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  video_id INT NOT NULL,
  platform ENUM('tiktok','instagram','facebook') NOT NULL,
  publish_at DATETIME NOT NULL,
  status ENUM('scheduled','publishing','published','failed') NOT NULL DEFAULT 'scheduled',
  platform_video_id VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
  INDEX idx_sched (status, publish_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;