-- ═══════════════════════════════════════════════════════
-- Bouflix — MySQL Database Schema
-- Auto-generated from prisma/schema.prisma
-- ═══════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(191) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `password_hash` VARCHAR(255) NULL,
  `first_name` VARCHAR(100) NULL,
  `last_name` VARCHAR(100) NULL,
  `username` VARCHAR(50) NULL,
  `phone` VARCHAR(30) NULL,
  `photo_url` TEXT NULL,
  `role` VARCHAR(20) NOT NULL DEFAULT 'user',
  `level` INTEGER NOT NULL DEFAULT 1,
  `provider` VARCHAR(20) NOT NULL DEFAULT 'email',
  `email_verified` BOOLEAN NOT NULL DEFAULT false,
  `disabled` BOOLEAN NOT NULL DEFAULT false,
  `active_device_token` VARCHAR(255) NULL,
  `device_registered_at` BIGINT NULL,
  `refresh_token` VARCHAR(500) NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  UNIQUE INDEX `users_email_key`(`email`),
  UNIQUE INDEX `users_username_key`(`username`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `genres` (
  `tmdb_id` INTEGER NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`tmdb_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `movies` (
  `id` VARCHAR(191) NOT NULL,
  `tmdb_id` INTEGER NULL,
  `source` VARCHAR(20) NOT NULL DEFAULT 'tmdb',
  `title` VARCHAR(500) NULL,
  `original_title` VARCHAR(500) NULL,
  `original_language` VARCHAR(10) NULL,
  `origin_country` JSON NULL,
  `overview` TEXT NULL,
  `poster_path` TEXT NULL,
  `backdrop_path` TEXT NULL,
  `release_date` VARCHAR(20) NULL,
  `vote_average` DOUBLE NOT NULL DEFAULT 0,
  `popularity` DOUBLE NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NULL,
  `runtime` INTEGER NOT NULL DEFAULT 0,
  `active` BOOLEAN NOT NULL DEFAULT true,
  `trailer` TEXT NULL,
  `allow_embed_sources` BOOLEAN NOT NULL DEFAULT false,
  `field_overrides` JSON NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  UNIQUE INDEX `movies_tmdb_id_key`(`tmdb_id`),
  INDEX `movies_active_source_updated_at_idx`(`active`, `source`, `updated_at`),
  INDEX `movies_active_updated_at_idx`(`active`, `updated_at`),
  INDEX `movies_title_idx`(`title`(191)),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `movie_genres` (
  `movie_id` VARCHAR(191) NOT NULL,
  `genre_id` INTEGER NOT NULL,
  PRIMARY KEY (`movie_id`, `genre_id`),
  CONSTRAINT `movie_genres_movie_id_fkey` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `movie_genres_genre_id_fkey` FOREIGN KEY (`genre_id`) REFERENCES `genres`(`tmdb_id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tv_shows` (
  `id` VARCHAR(191) NOT NULL,
  `tmdb_id` INTEGER NULL,
  `source` VARCHAR(20) NOT NULL DEFAULT 'tmdb',
  `title` VARCHAR(500) NULL,
  `original_name` VARCHAR(500) NULL,
  `original_language` VARCHAR(10) NULL,
  `origin_country` JSON NULL,
  `languages` JSON NULL,
  `overview` TEXT NULL,
  `poster_path` TEXT NULL,
  `backdrop_path` TEXT NULL,
  `first_air_date` VARCHAR(20) NULL,
  `last_air_date` VARCHAR(20) NULL,
  `vote_average` DOUBLE NOT NULL DEFAULT 0,
  `popularity` DOUBLE NOT NULL DEFAULT 0,
  `number_of_seasons` INTEGER NOT NULL DEFAULT 0,
  `number_of_episodes` INTEGER NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NULL,
  `active` BOOLEAN NOT NULL DEFAULT true,
  `trailer` TEXT NULL,
  `allow_embed_sources` BOOLEAN NOT NULL DEFAULT false,
  `field_overrides` JSON NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  UNIQUE INDEX `tv_shows_tmdb_id_key`(`tmdb_id`),
  INDEX `tv_shows_active_source_updated_at_idx`(`active`, `source`, `updated_at`),
  INDEX `tv_shows_active_updated_at_idx`(`active`, `updated_at`),
  INDEX `tv_shows_title_idx`(`title`(191)),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tv_show_genres` (
  `tv_show_id` VARCHAR(191) NOT NULL,
  `genre_id` INTEGER NOT NULL,
  PRIMARY KEY (`tv_show_id`, `genre_id`),
  CONSTRAINT `tv_show_genres_tv_show_id_fkey` FOREIGN KEY (`tv_show_id`) REFERENCES `tv_shows`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tv_show_genres_genre_id_fkey` FOREIGN KEY (`genre_id`) REFERENCES `genres`(`tmdb_id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `seasons` (
  `id` VARCHAR(191) NOT NULL,
  `tv_show_id` VARCHAR(191) NOT NULL,
  `season_number` INTEGER NOT NULL,
  `season_name` VARCHAR(200) NOT NULL DEFAULT '',
  `overview` TEXT NOT NULL,
  `poster_path` TEXT NOT NULL,
  `episode_count` INTEGER NOT NULL DEFAULT 0,
  `air_date` VARCHAR(20) NOT NULL DEFAULT '',
  `override` BOOLEAN NOT NULL DEFAULT false,
  `field_overrides` JSON NULL,
  UNIQUE INDEX `seasons_tv_show_id_season_number_key`(`tv_show_id`, `season_number`),
  PRIMARY KEY (`id`),
  CONSTRAINT `seasons_tv_show_id_fkey` FOREIGN KEY (`tv_show_id`) REFERENCES `tv_shows`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `episodes` (
  `id` VARCHAR(191) NOT NULL,
  `season_id` VARCHAR(191) NOT NULL,
  `episode_number` INTEGER NOT NULL,
  `title` VARCHAR(500) NOT NULL DEFAULT '',
  `overview` TEXT NOT NULL,
  `air_date` VARCHAR(20) NOT NULL DEFAULT '',
  `still_path` TEXT NOT NULL,
  `runtime` INTEGER NOT NULL DEFAULT 0,
  `vote_average` DOUBLE NOT NULL DEFAULT 0,
  `override` BOOLEAN NOT NULL DEFAULT false,
  `field_overrides` JSON NULL,
  UNIQUE INDEX `episodes_season_id_episode_number_key`(`season_id`, `episode_number`),
  PRIMARY KEY (`id`),
  CONSTRAINT `episodes_season_id_fkey` FOREIGN KEY (`season_id`) REFERENCES `seasons`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `streams` (
  `id` VARCHAR(191) NOT NULL,
  `movie_id` VARCHAR(191) NULL,
  `episode_id` VARCHAR(191) NULL,
  `url` TEXT NOT NULL,
  `quality` VARCHAR(50) NOT NULL DEFAULT '1080p',
  `language` VARCHAR(50) NOT NULL DEFAULT 'en',
  `type` VARCHAR(50) NOT NULL DEFAULT 'direct',
  `name` VARCHAR(100) NULL,
  `format` VARCHAR(20) NULL,
  `enabled` BOOLEAN NOT NULL DEFAULT true,
  `order` INTEGER NOT NULL DEFAULT 0,
  `user_agent` TEXT NULL,
  `referer` TEXT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  INDEX `streams_movie_id_idx`(`movie_id`),
  INDEX `streams_episode_id_idx`(`episode_id`),
  INDEX `streams_order_idx`(`order`),
  PRIMARY KEY (`id`),
  CONSTRAINT `streams_movie_id_fkey` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `streams_episode_id_fkey` FOREIGN KEY (`episode_id`) REFERENCES `episodes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `embed_sources` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `movie_url` TEXT NOT NULL,
  `tv_url` TEXT NOT NULL,
  `enabled` BOOLEAN NOT NULL DEFAULT true,
  `order` INTEGER NOT NULL DEFAULT 0,
  `supported_languages` JSON NULL,
  `user_agent` TEXT NULL,
  `referer` TEXT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  INDEX `embed_sources_order_idx`(`order`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `id` INTEGER NOT NULL DEFAULT 1,
  `app` JSON NULL,
  `player` JSON NULL,
  `restrictions` JSON NULL,
  `contact` JSON NULL,
  `security` JSON NULL,
  `slideshow_mode` VARCHAR(20) NOT NULL DEFAULT 'horizontal',
  `slideshow_interval` INTEGER NOT NULL DEFAULT 5000,
  `slideshow_source` VARCHAR(20) NOT NULL DEFAULT 'manual',
  `slideshow_auto_strategy` VARCHAR(30) NOT NULL DEFAULT 'trending_day',
  `slideshow_auto_content_type` VARCHAR(10) NOT NULL DEFAULT 'both',
  `slideshow_auto_count` INTEGER NOT NULL DEFAULT 5,
  `slideshow_sticky` BOOLEAN NOT NULL DEFAULT false,
  `slideshow_full_poster` BOOLEAN NOT NULL DEFAULT false,
  `ads` JSON NULL,
  `subscription` JSON NULL,
  `embed_settings` JSON NULL,
  `license` JSON NULL,
  `privacy_policy` LONGTEXT NULL,
  `terms_of_service` LONGTEXT NULL,
  `updated_at` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `private_settings` (
  `id` INTEGER NOT NULL DEFAULT 1,
  `tmdb_api_key` TEXT NULL,
  `opensubtitles_api_key` TEXT NULL,
  `updated_at` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `slideshow_items` (
  `id` VARCHAR(191) NOT NULL,
  `tmdb_id` INTEGER NULL,
  `media_type` VARCHAR(10) NOT NULL,
  `title` VARCHAR(500) NOT NULL DEFAULT '',
  `overview` TEXT NOT NULL,
  `backdrop_path` TEXT NOT NULL,
  `poster_path` TEXT NOT NULL,
  `vote_average` DOUBLE NOT NULL DEFAULT 0,
  `enabled` BOOLEAN NOT NULL DEFAULT true,
  `order` INTEGER NOT NULL DEFAULT 0,
  INDEX `slideshow_items_enabled_order_idx`(`enabled`, `order`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` VARCHAR(191) NOT NULL,
  `user_id` VARCHAR(191) NOT NULL,
  `plan` VARCHAR(30) NOT NULL DEFAULT '',
  `provider` VARCHAR(30) NOT NULL DEFAULT '',
  `status` VARCHAR(30) NOT NULL DEFAULT 'none',
  `start_date` BIGINT NOT NULL DEFAULT 0,
  `expiry_date` BIGINT NOT NULL DEFAULT 0,
  `auto_renew` BOOLEAN NOT NULL DEFAULT false,
  `purchase_token` TEXT NOT NULL,
  `coupon_used` VARCHAR(50) NOT NULL DEFAULT '',
  `is_trial` BOOLEAN NOT NULL DEFAULT false,
  `original_price` DOUBLE NOT NULL DEFAULT 0,
  `amount_paid` DOUBLE NOT NULL DEFAULT 0,
  `discount_amount` DOUBLE NOT NULL DEFAULT 0,
  `discount_percent` INTEGER NOT NULL DEFAULT 0,
  `discount_type` VARCHAR(30) NOT NULL DEFAULT '',
  `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
  UNIQUE INDEX `subscriptions_user_id_key`(`user_id`),
  PRIMARY KEY (`id`),
  CONSTRAINT `subscriptions_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coupons` (
  `id` VARCHAR(191) NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `discount_percent` INTEGER NOT NULL DEFAULT 0,
  `max_uses` INTEGER NOT NULL DEFAULT 100,
  `current_uses` INTEGER NOT NULL DEFAULT 0,
  `expiry_date` BIGINT NOT NULL DEFAULT 0,
  `applicable_plans` JSON NULL,
  `active` BOOLEAN NOT NULL DEFAULT true,
  UNIQUE INDEX `coupons_code_key`(`code`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `embed_source_reports` (
  `id` VARCHAR(191) NOT NULL,
  `aggregate_id` VARCHAR(200) NOT NULL,
  `uid` VARCHAR(100) NULL,
  `source_name` VARCHAR(100) NOT NULL,
  `tmdb_id` INTEGER NULL,
  `media_type` VARCHAR(10) NOT NULL,
  `report_type` VARCHAR(30) NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `embed_source_reports_aggregate_id_created_at_idx`(`aggregate_id`, `created_at`),
  INDEX `embed_source_reports_aggregate_id_uid_idx`(`aggregate_id`, `uid`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Prisma migrations table (required by Prisma Client)
CREATE TABLE IF NOT EXISTS `_prisma_migrations` (
  `id` VARCHAR(36) NOT NULL,
  `checksum` VARCHAR(64) NOT NULL,
  `finished_at` DATETIME(3) NULL,
  `migration_name` VARCHAR(255) NOT NULL,
  `logs` TEXT NULL,
  `rolled_back_at` DATETIME(3) NULL,
  `started_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `applied_steps_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;