-- ============================================================
-- ANIMEZ - DATABASE SCHEMA & INITIAL DATA
-- Compatible with MySQL 5.7+ / MariaDB 10.2+ / cPanel phpMyAdmin
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `comments`;
DROP TABLE IF EXISTS `watch_history`;
DROP TABLE IF EXISTS `saved`;
DROP TABLE IF EXISTS `episodes`;
DROP TABLE IF EXISTS `anime_categories`;
DROP TABLE IF EXISTS `anime`;
DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `users`;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- USERS
-- ------------------------------------------------------------
CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(500) DEFAULT NULL,
  `api_token` VARCHAR(64) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- CATEGORIES (genres / chips)
-- ------------------------------------------------------------
CREATE TABLE `categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(60) NOT NULL UNIQUE,
  `slug` VARCHAR(60) NOT NULL UNIQUE,
  `jikan_genre_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- ANIME
-- ------------------------------------------------------------
CREATE TABLE `anime` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `mal_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `title_japanese` VARCHAR(255) DEFAULT NULL,
  `image_url` VARCHAR(500) DEFAULT NULL,
  `large_image_url` VARCHAR(500) DEFAULT NULL,
  `trailer_youtube_id` VARCHAR(64) DEFAULT NULL,
  `synopsis` TEXT,
  `score` DECIMAL(4,2) NOT NULL DEFAULT 0,
  `episodes` INT UNSIGNED NOT NULL DEFAULT 12,
  `status` VARCHAR(40) DEFAULT 'FINISHED',
  `type` VARCHAR(20) DEFAULT 'TV',
  `year` INT DEFAULT NULL,
  `members` INT UNSIGNED NOT NULL DEFAULT 0,
  `rank` INT DEFAULT NULL,
  `featured` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_anime_mal` (`mal_id`),
  INDEX `idx_anime_score` (`score`),
  INDEX `idx_anime_members` (`members`),
  INDEX `idx_anime_featured` (`featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- ANIME <-> CATEGORIES (many to many)
-- ------------------------------------------------------------
CREATE TABLE `anime_categories` (
  `anime_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`anime_id`, `category_id`),
  CONSTRAINT `fk_ac_anime` FOREIGN KEY (`anime_id`) REFERENCES `anime`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ac_cat` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- EPISODES
-- ------------------------------------------------------------
CREATE TABLE `episodes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `anime_id` INT UNSIGNED NOT NULL,
  `episode_number` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `stream_url` VARCHAR(1000) NOT NULL,
  `duration_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY `uq_ep_anime` (`anime_id`, `episode_number`),
  CONSTRAINT `fk_ep_anime` FOREIGN KEY (`anime_id`) REFERENCES `anime`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- SAVED
-- ------------------------------------------------------------
CREATE TABLE `saved` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `anime_id` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_saved_user_anime` (`user_id`, `anime_id`),
  CONSTRAINT `fk_sav_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sav_anime` FOREIGN KEY (`anime_id`) REFERENCES `anime`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- WATCH HISTORY
-- ------------------------------------------------------------
CREATE TABLE `watch_history` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `anime_id` INT UNSIGNED NOT NULL,
  `episode` INT UNSIGNED NOT NULL DEFAULT 1,
  `position_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
  `watched_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_hist_user_anime` (`user_id`, `anime_id`),
  CONSTRAINT `fk_hist_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_hist_anime` FOREIGN KEY (`anime_id`) REFERENCES `anime`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- COMMENTS
-- ------------------------------------------------------------
CREATE TABLE `comments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `anime_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `text` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_cmt_anime` FOREIGN KEY (`anime_id`) REFERENCES `anime`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cmt_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- DEFAULT CATEGORIES
-- ------------------------------------------------------------
INSERT INTO `categories` (`name`, `slug`, `jikan_genre_id`) VALUES
('Action', 'action', 1),
('Adventure', 'adventure', 2),
('Comedy', 'comedy', 4),
('Drama', 'drama', 8),
('Fantasy', 'fantasy', 10),
('Horror', 'horror', 14),
('Romance', 'romance', 22),
('Sci-Fi', 'sci-fi', 24),
('Slice of Life', 'slice-of-life', 36),
('Sports', 'sports', 30),
('Supernatural', 'supernatural', 37),
('Thriller', 'thriller', 41);