-- Migration: Create video table for storing streaming links
-- Date: 2025-01-XX
-- Description: Stores video streaming links fetched from /api/stream endpoint
--              Links are cached to avoid repeated API calls
--              Only stores episode number (from ep= parameter), not anime_id

-- Create video table
CREATE TABLE IF NOT EXISTS video (
  id INT AUTO_INCREMENT PRIMARY KEY,
  episode_id VARCHAR(300) NOT NULL COMMENT 'Episode number extracted from ep= parameter',
  server_id VARCHAR(50) NOT NULL,
  server_name VARCHAR(100) NOT NULL,
  type VARCHAR(10) NOT NULL COMMENT 'sub, dub, or raw',
  data_id VARCHAR(100),
  iframe_url TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- Composite unique key to prevent duplicate entries
  -- Same episode can have multiple servers/types, but same server+type should be unique
  UNIQUE KEY unique_video_entry (episode_id, server_id, type),
  
  -- Indexes for faster lookups
  INDEX idx_episode_id (episode_id),
  INDEX idx_server_type (server_id, type),
  INDEX idx_data_id (data_id),
  INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add comment to table
ALTER TABLE video COMMENT = 'Stores cached video streaming links from API stream endpoint';

