-- ============================================
-- SIMPLIFIED ANIME DATABASE SCHEMA
-- Main anime table + episodes tables
-- ============================================

-- Main anime table - stores ALL anime details
CREATE TABLE IF NOT EXISTS anime (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anime_id VARCHAR(300) UNIQUE NOT NULL,
  data_id VARCHAR(100),
  poster VARCHAR(1000),
  title VARCHAR(500),
  jname VARCHAR(500),
  description TEXT,
  
  -- TV Info fields (from all sources)
  tv_show_type VARCHAR(100),
  tv_duration VARCHAR(100),
  tv_release_date VARCHAR(100),
  tv_quality VARCHAR(50),
  tv_rating VARCHAR(50),
  
  -- Episode counts (both spotlight episodeInfo and extractPage use these)
  tv_sub VARCHAR(50),
  tv_dub VARCHAR(50),
  tv_eps VARCHAR(50),
  
  -- Anime Info fields (from animeInfo object)
  synonyms TEXT,
  japanese VARCHAR(500),
  studios VARCHAR(500),
  producers TEXT,
  genres TEXT,
  mal_score VARCHAR(50),
  status VARCHAR(100),
  aired VARCHAR(200),
  premiered VARCHAR(100),
  anilist_id INT,
  mal_id INT,
  
  -- Additional mapping IDs from ani.zip API
  animeplanet_id VARCHAR(200),
  kitsu_id INT,
  anisearch_id INT,
  anidb_id INT,
  notifymoe_id VARCHAR(100),
  livechart_id INT,
  thetvdb_id INT,
  imdb_id VARCHAR(50),
  themoviedb_id VARCHAR(50),
  type VARCHAR(50),
  season_number INT,
  pehe_id VARCHAR(1000),
  views INT DEFAULT 0,
  
  adult_content BOOLEAN DEFAULT FALSE,
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_anime_id (anime_id),
  INDEX idx_data_id (data_id),
  INDEX idx_title (title(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Episodes list for anime
CREATE TABLE IF NOT EXISTS anime_episodes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anime_id VARCHAR(300) NOT NULL,
  episode_id VARCHAR(300),
  episode_no INT NOT NULL,
  title VARCHAR(500),
  jname VARCHAR(500),
  filler BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_anime_episode (anime_id, episode_no),
  INDEX idx_anime_id (anime_id),
  INDEX idx_episode_id (episode_id),
  INDEX idx_episode_no (episode_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Episodes metadata (total count per anime)
CREATE TABLE IF NOT EXISTS anime_episodes_metadata (
  anime_id VARCHAR(300) PRIMARY KEY,
  total_episodes INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seasons list for anime
CREATE TABLE IF NOT EXISTS anime_seasons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anime_id VARCHAR(300) NOT NULL,
  season_id VARCHAR(300) NOT NULL,
  data_number INT NOT NULL,
  data_id INT NOT NULL,
  season VARCHAR(200),
  title VARCHAR(500),
  season_poster VARCHAR(1000),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_anime_season (anime_id, season_id),
  INDEX idx_anime_id (anime_id),
  INDEX idx_season_id (season_id),
  INDEX idx_data_id (data_id),
  INDEX idx_data_number (data_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Schedule table - stores daily anime schedule
CREATE TABLE IF NOT EXISTS anime_schedule (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anime_id VARCHAR(300) NOT NULL,
  data_id VARCHAR(100),
  title VARCHAR(500),
  jname VARCHAR(500),
  release_date VARCHAR(100),
  release_time VARCHAR(100),
  episode_number INT,
  poster VARCHAR(1000),
  adult_content BOOLEAN DEFAULT FALSE,
  schedule_date VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_anime_id (anime_id),
  INDEX idx_schedule_date (schedule_date),
  INDEX idx_release_time (release_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Video table - stores cached video streaming links
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,
  UNIQUE KEY unique_video_entry (episode_id, server_id, type),
  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;
