-- MySQL Database Schema for Anisub (Anime Scraper)

CREATE DATABASE IF NOT EXISTS `cloudnin_anime` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `cloudnin_anime`;

-- 1. Table: anime
-- Menyimpan informasi detail utama dari setiap judul anime.
CREATE TABLE IF NOT EXISTS `anime` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `title` VARCHAR(255) NOT NULL,
    `score` DECIMAL(3,2) DEFAULT NULL,
    `producer` VARCHAR(255) DEFAULT NULL,
    `type` VARCHAR(50) DEFAULT NULL,            -- TV, Movie, OVA, ONA, dll.
    `status` VARCHAR(50) DEFAULT NULL,          -- Completed, On-going
    `total_episodes` INT DEFAULT NULL,
    `duration` VARCHAR(100) DEFAULT NULL,       -- e.g. "23 Menit"
    `release_date` DATE DEFAULT NULL,           -- YYYY-MM-DD
    `studio` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `image_url` VARCHAR(500) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table: genres
-- Menyimpan daftar genre anime secara unik untuk optimasi relasi.
CREATE TABLE IF NOT EXISTS `genres` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table: anime_genres (Junction Table)
-- Relasi Many-to-Many antara anime dan genres.
CREATE TABLE IF NOT EXISTS `anime_genres` (
    `anime_id` INT NOT NULL,
    `genre_id` INT NOT NULL,
    PRIMARY KEY (`anime_id`, `genre_id`),
    FOREIGN KEY (`anime_id`) REFERENCES `anime` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table: episodes
-- Menyimpan daftar episode untuk setiap anime.
CREATE TABLE IF NOT EXISTS `episodes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `anime_id` INT NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `title` VARCHAR(255) NOT NULL,
    `released_on` DATETIME DEFAULT NULL,        -- YYYY-MM-DD HH:MM:SS (WIB)
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`anime_id`) REFERENCES `anime` (`id`) ON DELETE CASCADE,
    INDEX `idx_episode_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table: episode_streams
-- Menyimpan URL mirror streaming dan iframe player per resolusi/provider.
CREATE TABLE IF NOT EXISTS `episode_streams` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `episode_id` INT NOT NULL,
    `provider` VARCHAR(100) NOT NULL,           -- e.g. "Default", "otakuplay", "mp4load"
    `url` TEXT NOT NULL,                        -- URL embed iframe / mirror streaming
    `resolution` VARCHAR(20) DEFAULT NULL,      -- e.g. "360p", "480p", "720p"
    `data_content` TEXT DEFAULT NULL,           -- data base64 raw untuk request ajax mirror jika diperlukan
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Table: episode_downloads
-- Menyimpan link download per resolusi dan per file hosting provider.
CREATE TABLE IF NOT EXISTS `episode_downloads` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `episode_id` INT NOT NULL,
    `resolution` VARCHAR(20) NOT NULL,          -- e.g. "360p", "480p", "720p"
    `host` VARCHAR(100) NOT NULL,               -- e.g. "OtakuFiles", "Kraken", "PDrain"
    `url` TEXT NOT NULL,                        -- Link download tujuan
    `size` VARCHAR(50) DEFAULT NULL,            -- e.g. "35.8 MB" (opsional)
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;