CREATE TABLE `account` (
	`id` text PRIMARY KEY NOT NULL,
	`account_id` text NOT NULL,
	`provider_id` text NOT NULL,
	`user_id` text NOT NULL,
	`access_token` text,
	`refresh_token` text,
	`id_token` text,
	`access_token_expires_at` integer,
	`refresh_token_expires_at` integer,
	`scope` text,
	`password` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `session` (
	`id` text PRIMARY KEY NOT NULL,
	`expires_at` integer NOT NULL,
	`token` text NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	`ip_address` text,
	`user_agent` text,
	`user_id` text NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `session_token_unique` ON `session` (`token`);--> statement-breakpoint
CREATE TABLE `user` (
	`id` text PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`email` text NOT NULL,
	`email_verified` integer DEFAULT false NOT NULL,
	`image` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	`username` text,
	`display_username` text
);
--> statement-breakpoint
CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);--> statement-breakpoint
CREATE UNIQUE INDEX `user_username_unique` ON `user` (`username`);--> statement-breakpoint
CREATE TABLE `verification` (
	`id` text PRIMARY KEY NOT NULL,
	`identifier` text NOT NULL,
	`value` text NOT NULL,
	`expires_at` integer NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `library_folders` (
	`id` integer PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`type` text NOT NULL,
	`path` text NOT NULL,
	`primary_language` text DEFAULT 'en' NOT NULL,
	`fallback_language` text DEFAULT 'en' NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `ux_library_folder_path` ON `library_folders` (`path`);--> statement-breakpoint
CREATE UNIQUE INDEX `ux_library_folder_name` ON `library_folders` (`name`);--> statement-breakpoint
CREATE TABLE `credits` (
	`id` text PRIMARY KEY NOT NULL,
	`person_id` text NOT NULL,
	`movie_id` text,
	`series_id` text,
	`episode_id` text,
	`role` text NOT NULL,
	`character` text,
	`job` text,
	`department` text,
	`order` integer,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`person_id`) REFERENCES `people`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`series_id`) REFERENCES `series`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`episode_id`) REFERENCES `episodes`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_credits_person` ON `credits` (`person_id`);--> statement-breakpoint
CREATE INDEX `idx_credits_movie` ON `credits` (`movie_id`);--> statement-breakpoint
CREATE INDEX `idx_credits_series` ON `credits` (`series_id`);--> statement-breakpoint
CREATE INDEX `idx_credits_episode` ON `credits` (`episode_id`);--> statement-breakpoint
CREATE INDEX `idx_credits_role` ON `credits` (`role`);--> statement-breakpoint
CREATE INDEX `idx_credits_department` ON `credits` (`department`);--> statement-breakpoint
CREATE INDEX `idx_credits_order` ON `credits` (`order`);--> statement-breakpoint
CREATE UNIQUE INDEX `ux_credits_movie_person_role` ON `credits` (`movie_id`,`person_id`,`role`,`character`,`job`);--> statement-breakpoint
CREATE UNIQUE INDEX `ux_credits_series_person_role` ON `credits` (`series_id`,`person_id`,`role`,`character`,`job`);--> statement-breakpoint
CREATE UNIQUE INDEX `ux_credits_episode_person_role` ON `credits` (`episode_id`,`person_id`,`role`,`character`,`job`);--> statement-breakpoint
CREATE TABLE `genres` (
	`id` text PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `genres_name_unique` ON `genres` (`name`);--> statement-breakpoint
CREATE INDEX `idx_genres_name` ON `genres` (`name`);--> statement-breakpoint
CREATE TABLE `movies` (
	`id` text PRIMARY KEY NOT NULL,
	`title_id` text NOT NULL,
	`runtime_seconds` integer,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`title_id`) REFERENCES `titles`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `movies_title_id_unique` ON `movies` (`title_id`);--> statement-breakpoint
CREATE INDEX `idx_movies_title` ON `movies` (`title_id`);--> statement-breakpoint
CREATE TABLE `people` (
	`id` text PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`biography` text,
	`birthday` text,
	`deathday` text,
	`place_of_birth` text,
	`profile_picture` text,
	`tmdb_id` integer,
	`imdb_id` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `people_tmdb_id_unique` ON `people` (`tmdb_id`);--> statement-breakpoint
CREATE UNIQUE INDEX `people_imdb_id_unique` ON `people` (`imdb_id`);--> statement-breakpoint
CREATE INDEX `idx_people_name` ON `people` (`name`);--> statement-breakpoint
CREATE INDEX `idx_people_tmdb` ON `people` (`tmdb_id`);--> statement-breakpoint
CREATE INDEX `idx_people_imdb` ON `people` (`imdb_id`);--> statement-breakpoint
CREATE TABLE `episodes` (
	`id` text PRIMARY KEY NOT NULL,
	`series_id` text NOT NULL,
	`season_id` text NOT NULL,
	`season_number` integer NOT NULL,
	`episode_number` integer NOT NULL,
	`title` text NOT NULL,
	`overview` text,
	`air_date` text,
	`runtime_seconds` integer,
	`created_at` integer NOT NULL,
	FOREIGN KEY (`series_id`) REFERENCES `series`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`season_id`) REFERENCES `seasons`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `ux_eps_series_s_e` ON `episodes` (`series_id`,`season_number`,`episode_number`);--> statement-breakpoint
CREATE INDEX `idx_eps_season` ON `episodes` (`season_id`);--> statement-breakpoint
CREATE INDEX `idx_eps_series` ON `episodes` (`series_id`);--> statement-breakpoint
CREATE TABLE `seasons` (
	`id` text PRIMARY KEY NOT NULL,
	`series_id` text NOT NULL,
	`season_number` integer NOT NULL,
	`title` text,
	`created_at` integer NOT NULL,
	FOREIGN KEY (`series_id`) REFERENCES `series`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `ux_seasons_series_number` ON `seasons` (`series_id`,`season_number`);--> statement-breakpoint
CREATE INDEX `idx_seasons_series` ON `seasons` (`series_id`);--> statement-breakpoint
CREATE TABLE `series` (
	`id` text PRIMARY KEY NOT NULL,
	`title_id` text NOT NULL,
	`status` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`title_id`) REFERENCES `titles`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `series_title_id_unique` ON `series` (`title_id`);--> statement-breakpoint
CREATE INDEX `idx_series_title` ON `series` (`title_id`);--> statement-breakpoint
CREATE TABLE `title_genres` (
	`title_id` text NOT NULL,
	`genre_id` text NOT NULL,
	`created_at` integer NOT NULL,
	PRIMARY KEY(`title_id`, `genre_id`),
	FOREIGN KEY (`title_id`) REFERENCES `titles`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`genre_id`) REFERENCES `genres`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_title_genres_title` ON `title_genres` (`title_id`);--> statement-breakpoint
CREATE INDEX `idx_title_genres_genre` ON `title_genres` (`genre_id`);--> statement-breakpoint
CREATE TABLE `title_providers` (
	`title_id` text NOT NULL,
	`provider` text NOT NULL,
	`provider_id` text NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	PRIMARY KEY(`title_id`, `provider`),
	FOREIGN KEY (`title_id`) REFERENCES `titles`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_title_providers_title` ON `title_providers` (`title_id`);--> statement-breakpoint
CREATE INDEX `idx_title_providers_provider` ON `title_providers` (`provider`);--> statement-breakpoint
CREATE INDEX `idx_title_providers_provider_id` ON `title_providers` (`provider`,`provider_id`);--> statement-breakpoint
CREATE TABLE `titles` (
	`id` text PRIMARY KEY NOT NULL,
	`library_folder_id` integer NOT NULL,
	`type` text NOT NULL,
	`title` text NOT NULL,
	`original_title` text NOT NULL,
	`tagline` text,
	`overview` text,
	`year` integer,
	`release_date` text,
	`poster` text,
	`backdrop` text,
	`rating` real,
	`popularity` real,
	`original_language` text,
	`homepage` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`library_folder_id`) REFERENCES `library_folders`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_titles_library` ON `titles` (`library_folder_id`);--> statement-breakpoint
CREATE INDEX `idx_titles_type` ON `titles` (`type`);--> statement-breakpoint
CREATE INDEX `idx_titles_title` ON `titles` (`title`);--> statement-breakpoint
CREATE INDEX `idx_titles_year` ON `titles` (`year`);--> statement-breakpoint
CREATE INDEX `idx_titles_rating` ON `titles` (`rating`);--> statement-breakpoint
CREATE INDEX `idx_titles_popularity` ON `titles` (`popularity`);--> statement-breakpoint
CREATE TABLE `media_items` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`parent_type` text NOT NULL,
	`movie_id` text,
	`episode_id` text,
	`path` text NOT NULL,
	`etag` text,
	`size_bytes` integer,
	`container` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`episode_id`) REFERENCES `episodes`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `ux_media_items_path` ON `media_items` (`path`);--> statement-breakpoint
CREATE INDEX `idx_media_items_parent_movie` ON `media_items` (`parent_type`,`movie_id`);--> statement-breakpoint
CREATE INDEX `idx_media_items_parent_episode` ON `media_items` (`parent_type`,`episode_id`);--> statement-breakpoint
CREATE TABLE `media_streams` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`media_item_id` integer NOT NULL,
	`stream_index` integer NOT NULL,
	`codec_type` text NOT NULL,
	`codec_name` text,
	`codec_long_name` text,
	`profile` text,
	`bit_rate` integer,
	`width` integer,
	`height` integer,
	`frame_rate` real,
	`channels` integer,
	`channel_layout` text,
	`sample_rate` integer,
	`language` text,
	`title` text,
	`is_default` integer DEFAULT false NOT NULL,
	`is_forced` integer DEFAULT false NOT NULL,
	`stream_json` text,
	FOREIGN KEY (`media_item_id`) REFERENCES `media_items`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `ux_stream_media_idx` ON `media_streams` (`media_item_id`,`stream_index`);--> statement-breakpoint
CREATE INDEX `idx_stream_media` ON `media_streams` (`media_item_id`);--> statement-breakpoint
CREATE INDEX `idx_stream_codec_type` ON `media_streams` (`codec_type`);