import {
	index,
	integer,
	sqliteTable,
	text,
	uniqueIndex,
} from "drizzle-orm/sqlite-core";
import type { PeopleRoleType } from "../enums";
import { movies } from "./movies";
import { people } from "./people";
import { episodes, series } from "./series";

// ----- CREDITS (junction table for movies/series/episodes and people with their roles) -----
export const credits = sqliteTable(
	"credits",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => {
				return Bun.randomUUIDv7();
			}),
		personId: text("person_id")
			.notNull()
			.references(() => people.id, { onDelete: "cascade" }),

		// Reference to content (movie, series, or episode)
		// Only one of these should be set per credit
		movieId: text("movie_id").references(() => movies.id, {
			onDelete: "cascade",
		}),
		seriesId: text("series_id").references(() => series.id, {
			onDelete: "cascade",
		}),
		episodeId: text("episode_id").references(() => episodes.id, {
			onDelete: "cascade",
		}),

		// Role type: CAST or CREW
		role: text("role").notNull().$type<PeopleRoleType>(), // "CAST" | "CREW"

		// CAST-specific fields
		character: text("character"), // Character name for cast members

		// CREW-specific fields
		job: text("job"), // Specific job title (e.g., "Director", "Producer", "Director of Photography")
		department: text("department"), // Department (e.g., "Directing", "Writing", "Production", "Camera")

		// Display order
		order: integer("order"), // Billing/credits display order (lower numbers appear first)

		// Timestamps
		createdAt: integer("created_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.notNull(),
		updatedAt: integer("updated_at", { mode: "timestamp" })
			.$defaultFn(() => new Date())
			.$onUpdate(() => new Date())
			.notNull(),
	},
	(t) => [
		// Indexes for efficient lookups
		index("idx_credits_person").on(t.personId),
		index("idx_credits_movie").on(t.movieId),
		index("idx_credits_series").on(t.seriesId),
		index("idx_credits_episode").on(t.episodeId),
		index("idx_credits_role").on(t.role),
		index("idx_credits_department").on(t.department),
		index("idx_credits_order").on(t.order),

		// Unique constraints to prevent duplicate credits
		uniqueIndex("ux_credits_movie_person_role").on(
			t.movieId,
			t.personId,
			t.role,
			t.character,
			t.job,
		),
		uniqueIndex("ux_credits_series_person_role").on(
			t.seriesId,
			t.personId,
			t.role,
			t.character,
			t.job,
		),
		uniqueIndex("ux_credits_episode_person_role").on(
			t.episodeId,
			t.personId,
			t.role,
			t.character,
			t.job,
		),
	],
);
