-- CreateTable
CREATE TABLE `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;

-- CreateTable
CREATE TABLE `genres` (
    `tmdb_id` INTEGER NOT NULL,
    `name` VARCHAR(100) NOT NULL,

    PRIMARY KEY (`tmdb_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `movie_genres` (
    `movie_id` VARCHAR(191) NOT NULL,
    `genre_id` INTEGER NOT NULL,

    PRIMARY KEY (`movie_id`, `genre_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `tv_show_genres` (
    `tv_show_id` VARCHAR(191) NOT NULL,
    `genre_id` INTEGER NOT NULL,

    PRIMARY KEY (`tv_show_id`, `genre_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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 DEFAULT '',
    `poster_path` TEXT NOT NULL DEFAULT '',
    `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`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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 DEFAULT '',
    `air_date` VARCHAR(20) NOT NULL DEFAULT '',
    `still_path` TEXT NOT NULL DEFAULT '',
    `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`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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;

-- CreateTable
CREATE TABLE `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,
    `ads` JSON NULL,
    `subscription` JSON NULL,
    `embed_settings` JSON NULL,
    `license` JSON NULL,
    `updated_at` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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;

-- CreateTable
CREATE TABLE `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 DEFAULT '',
    `backdrop_path` TEXT NOT NULL DEFAULT '',
    `poster_path` TEXT NOT NULL DEFAULT '',
    `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;

-- CreateTable
CREATE TABLE `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 DEFAULT '',
    `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`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `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;

-- CreateTable
CREATE TABLE `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;

-- AddForeignKey
ALTER TABLE `movie_genres` ADD CONSTRAINT `movie_genres_movie_id_fkey` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `movie_genres` ADD CONSTRAINT `movie_genres_genre_id_fkey` FOREIGN KEY (`genre_id`) REFERENCES `genres`(`tmdb_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `tv_show_genres` ADD CONSTRAINT `tv_show_genres_tv_show_id_fkey` FOREIGN KEY (`tv_show_id`) REFERENCES `tv_shows`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `tv_show_genres` ADD CONSTRAINT `tv_show_genres_genre_id_fkey` FOREIGN KEY (`genre_id`) REFERENCES `genres`(`tmdb_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `seasons` ADD CONSTRAINT `seasons_tv_show_id_fkey` FOREIGN KEY (`tv_show_id`) REFERENCES `tv_shows`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `episodes` ADD CONSTRAINT `episodes_season_id_fkey` FOREIGN KEY (`season_id`) REFERENCES `seasons`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `streams` ADD CONSTRAINT `streams_movie_id_fkey` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `streams` ADD CONSTRAINT `streams_episode_id_fkey` FOREIGN KEY (`episode_id`) REFERENCES `episodes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `subscriptions` ADD CONSTRAINT `subscriptions_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;