import {
	index,
	integer,
	primaryKey,
	real,
	sqliteTable,
	text,
} from "drizzle-orm/sqlite-core";
import type { LibraryType, MetadataProviderType } from "../enums";
import { libraryFolders } from "../libraries";
import { genres } from "./genres";

// ----- TITLES (common metadata for movies and series) -----
export const titles = sqliteTable(
	"titles",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => {
				return Bun.randomUUIDv7();
			}),
		libraryFolderId: integer("library_folder_id")
			.notNull()
			.references(() => libraryFolders.id, { onDelete: "cascade" }),
		type: text("type").notNull().$type<LibraryType>(), // "MOVIE" | "SERIES"

		// Basic information
		title: text("title").notNull(),
		originalTitle: text("original_title").notNull(),
		tagline: text("tagline"),
		overview: text("overview"),

		// Date information
		year: integer("year"),
		releaseDate: text("release_date"), // "YYYY-MM-DD"

		// Media assets
		poster: text("poster"),
		backdrop: text("backdrop"),

		// Ratings and popularity
		rating: real("rating"),
		popularity: real("popularity"),
		// Language
		originalLanguage: text("original_language"), // ISO 639-1 code (e.g., "en", "id")

		// Additional metadata
		homepage: text("homepage"),

		createdAt: integer("created_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.notNull(),
		updatedAt: integer("updated_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.$onUpdate(() => new Date())
			.notNull(),
	},
	(t) => [
		index("idx_titles_library").on(t.libraryFolderId),
		index("idx_titles_type").on(t.type),
		index("idx_titles_title").on(t.title),
		index("idx_titles_year").on(t.year),
		index("idx_titles_rating").on(t.rating),
		index("idx_titles_popularity").on(t.popularity),
	],
);

// ----- TITLE_GENRES (junction table) -----
export const titleGenres = sqliteTable(
	"title_genres",
	{
		titleId: text("title_id")
			.notNull()
			.references(() => titles.id, { onDelete: "cascade" }),
		genreId: text("genre_id")
			.notNull()
			.references(() => genres.id, { onDelete: "cascade" }),
		createdAt: integer("created_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.notNull(),
	},
	(t) => [
		primaryKey({ columns: [t.titleId, t.genreId] }),
		index("idx_title_genres_title").on(t.titleId),
		index("idx_title_genres_genre").on(t.genreId),
	],
);

// ----- TITLE_PROVIDERS (junction table for external metadata provider IDs) -----
export const titleProviders = sqliteTable(
	"title_providers",
	{
		titleId: text("title_id")
			.notNull()
			.references(() => titles.id, { onDelete: "cascade" }),
		provider: text("provider").notNull().$type<MetadataProviderType>(), // "TMDB" | "IMDB" | "OMDB" | etc.
		providerId: text("provider_id").notNull(), // The ID from the provider (e.g., "tt1234567" for IMDB, "550" for TMDB)
		createdAt: integer("created_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.notNull(),
		updatedAt: integer("updated_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.$onUpdate(() => new Date())
			.notNull(),
	},
	(t) => [
		primaryKey({ columns: [t.titleId, t.provider] }),
		index("idx_title_providers_title").on(t.titleId),
		index("idx_title_providers_provider").on(t.provider),
		index("idx_title_providers_provider_id").on(t.provider, t.providerId),
	],
);
