import {
	and,
	desc,
	eq,
	exists,
	isNotNull,
	ne,
	or,
	type SQL,
	sql,
} from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";
import { db } from "~/db";
import {
	entries,
	entryVideoJoin,
	profiles,
	roles,
	showStudioJoin,
	shows,
	showTranslations,
	staff,
	studios,
	studioTranslations,
	videos,
} from "~/db/schema";
import { watchlist } from "~/db/schema/watchlist";
import {
	coalesce,
	getColumns,
	jsonbAgg,
	jsonbBuildObject,
	jsonbObjectAgg,
	sqlarr,
} from "~/db/utils";
import type { Collection } from "~/models/collections";
import type { Entry } from "~/models/entry";
import type { MovieStatus } from "~/models/movie";
import { SerieStatus, type SerieTranslation } from "~/models/serie";
import type { Studio } from "~/models/studio";
import {
	buildRelations,
	type FilterDef,
	Genre,
	type Image,
	keysetPaginate,
	Sort,
	sortToSql,
} from "~/models/utils";
import type { EmbeddedVideo } from "~/models/video";
import { WatchlistStatus } from "~/models/watchlist";
import {
	entryProgressQ,
	entryVideosQ,
	getEntryTransQ,
	mapProgress,
} from "../entries";

export const watchStatusQ = db
	.select({
		...getColumns(watchlist),
		percent: sql<number>`${watchlist.seenCount}`.as("percent"),
	})
	.from(watchlist)
	.innerJoin(profiles, eq(watchlist.profilePk, profiles.pk))
	.where(eq(profiles.id, sql.placeholder("userId")))
	.as("watchstatus");

export const showFilters: FilterDef = {
	kind: {
		column: shows.kind,
		type: "enum",
		values: ["serie", "movie", "collection"],
	},
	genres: {
		column: shows.genres,
		type: "enum",
		values: Genre.enum,
		isArray: true,
	},
	status: { column: shows.status, type: "enum", values: SerieStatus.enum },
	runtime: { column: shows.runtime, type: "float" },
	airDate: { column: shows.startAir, type: "date" },
	startAir: { column: shows.startAir, type: "date" },
	endAir: { column: shows.endAir, type: "date" },
	originalLanguage: {
		column: sql`${shows.original}->'language'`,
		type: "string",
	},
	tags: {
		column: sql.raw(`t.${showTranslations.tags.name}`),
		type: "string",
		isArray: true,
	},
	watchStatus: {
		column: watchStatusQ.status,
		type: "enum",
		values: WatchlistStatus.enum,
	},
	score: { column: watchStatusQ.score, type: "int" },
	isAvailable: { column: sql`(${shows.availableCount} > 0)`, type: "bool" },
	rating: {
		column: (source: string) => sql`(${shows.rating}->>${source})::int`,
		type: "int",
	},
	nextRefresh: { column: shows.nextRefresh, type: "date" },
	studios: {
		column: db
			.select({ slug: studios.slug })
			.from(showStudioJoin)
			.innerJoin(studios, eq(studios.pk, showStudioJoin.studioPk))
			.where(eq(showStudioJoin.showPk, shows.pk)),
		type: "string",
		isArray: true,
	},
	staff: {
		column: db
			.select({ slug: staff.slug })
			.from(roles)
			.innerJoin(staff, eq(staff.pk, roles.staffPk))
			.where(eq(roles.showPk, shows.pk)),
		type: "string",
		isArray: true,
	},
};
export const showSort = Sort(
	{
		slug: shows.slug,
		name: {
			sql: sql.raw(`t.${showTranslations.name.name}`),
			isNullable: false,
			accessor: (x) => x.name,
		},
		airDate: shows.startAir,
		startAir: shows.startAir,
		endAir: shows.endAir,
		createdAt: shows.createdAt,
		nextRefresh: shows.nextRefresh,
		watchStatus: watchStatusQ.status,
		score: watchStatusQ.score,
		rating: (source: string) => ({
			sql: sql`(${shows.rating}->>${source})::int`,
			isNullable: true,
			accessor: (x: any) => x.rating?.[source] ?? null,
		}),
	},
	{
		default: ["slug"],
		tablePk: shows.pk,
	},
);

export const showRelations = {
	translations: () => {
		const { pk, language, ...trans } = getColumns(showTranslations);
		return db
			.select({
				json: jsonbObjectAgg(
					language,
					jsonbBuildObject<SerieTranslation>(trans),
				).as("json"),
			})
			.from(showTranslations)
			.where(eq(showTranslations.pk, shows.pk))
			.as("translations");
	},
	collection: ({
		languages,
		preferOriginal,
	}: {
		languages: string[];
		preferOriginal?: boolean;
	}) => {
		const collections = alias(shows, "collections");
		const colTrans = alias(showTranslations, "col_trans");
		const transQ = db
			.selectDistinctOn([colTrans.pk])
			.from(colTrans)
			.orderBy(
				colTrans.pk,
				sql`array_position(${sqlarr(languages)}, ${colTrans.language})`,
			)
			.as("t");

		return db
			.select({
				json: jsonbBuildObject<Collection>({
					...getColumns(collections),
					...getColumns(transQ),
					...(preferOriginal && {
						poster: sql<Image>`coalesce(nullif(${collections.original}->'poster', 'null'::jsonb), ${transQ.poster})`,
						thumbnail: sql<Image>`coalesce(nullif(${collections.original}->'thumbnail', 'null'::jsonb), ${transQ.thumbnail})`,
						banner: sql<Image>`coalesce(nullif(${collections.original}->'banner', 'null'::jsonb), ${transQ.banner})`,
						logo: sql<Image>`coalesce(nullif(${collections.original}->'logo', 'null'::jsonb), ${transQ.logo})`,
					}),
				}),
			})
			.from(collections)
			.innerJoin(transQ, eq(collections.pk, transQ.pk))
			.where(eq(collections.pk, shows.collectionPk))
			.as("collection");
	},
	studios: ({ languages }: { languages: string[] }) => {
		const studioTransQ = db
			.selectDistinctOn([studioTranslations.pk])
			.from(studioTranslations)
			.orderBy(
				studioTranslations.pk,
				sql`array_position(${sqlarr(languages)}, ${studioTranslations.language})`,
			)
			.as("t");
		const { pk, language, ...studioTrans } = getColumns(studioTransQ);

		return db
			.select({
				json: coalesce(
					jsonbAgg(
						jsonbBuildObject<Studio>({
							...getColumns(studios),
							...studioTrans,
						}),
					),
					sql`'[]'::jsonb`,
				).as("json"),
			})
			.from(studios)
			.innerJoin(studioTransQ, eq(studios.pk, studioTransQ.pk))
			.where(
				exists(
					db
						.select()
						.from(showStudioJoin)
						.where(
							and(
								eq(showStudioJoin.studioPk, studios.pk),
								eq(showStudioJoin.showPk, shows.pk),
							),
						),
				),
			)
			.as("studios");
	},
	// only available for movies
	videos: () => {
		const { guess, createdAt, updatedAt, ...videosCol } = getColumns(videos);
		return db
			.select({
				videos: coalesce(
					jsonbAgg(
						jsonbBuildObject<EmbeddedVideo>({
							slug: entryVideoJoin.slug,
							...videosCol,
						}),
					),
					sql`'[]'::jsonb`,
				).as("videos"),
			})
			.from(entryVideoJoin)
			.innerJoin(entries, eq(entries.showPk, shows.pk))
			.innerJoin(videos, eq(videos.pk, entryVideoJoin.videoPk))
			.where(eq(entryVideoJoin.entryPk, entries.pk))
			.as("videos");
	},
	// only available for series
	firstEntry: ({ languages }: { languages: string[] }) => {
		const transQ = getEntryTransQ(languages);

		return db
			.select({
				firstEntry: jsonbBuildObject<Entry>({
					...getColumns(entries),
					...getColumns(transQ),
					number: entries.episodeNumber,
					videos: entryVideosQ.videos,
					progress: mapProgress({ aliased: false }),
				}).as("firstEntry"),
			})
			.from(entries)
			.innerJoin(transQ, eq(entries.pk, transQ.pk))
			.leftJoin(entryProgressQ, eq(entries.pk, entryProgressQ.entryPk))
			.crossJoinLateral(entryVideosQ)
			.where(
				and(
					eq(entries.showPk, shows.pk),
					ne(entries.kind, "extra"),
					isNotNull(entries.availableSince),
				),
			)
			.orderBy(entries.order)
			.limit(1)
			.as("firstEntry");
	},
	nextEntry: ({ languages }: { languages: string[] }) => {
		const transQ = getEntryTransQ(languages);

		return db
			.select({
				nextEntry: jsonbBuildObject<Entry>({
					...getColumns(entries),
					...getColumns(transQ),
					number: entries.episodeNumber,
					videos: entryVideosQ.videos,
					progress: mapProgress({ aliased: false }),
				}).as("nextEntry"),
			})
			.from(entries)
			.innerJoin(transQ, eq(entries.pk, transQ.pk))
			.leftJoin(entryProgressQ, eq(entries.pk, entryProgressQ.entryPk))
			.crossJoinLateral(entryVideosQ)
			.where(eq(watchStatusQ.nextEntry, entries.pk))
			.as("nextEntry");
	},
};
export const serieRelations = {
	translations: showRelations.translations,
	studios: showRelations.studios,
	firstEntry: showRelations.firstEntry,
	nextEntry: showRelations.nextEntry,
};
export const movieRelations = {
	translations: showRelations.translations,
	studios: showRelations.studios,
	videos: showRelations.videos,
};
export const collectionRelations = {
	translations: showRelations.translations,
	studios: showRelations.studios,
};

export async function getShows({
	after,
	limit,
	query,
	sort,
	filter,
	languages,
	fallbackLanguage = true,
	preferOriginal = false,
	relations = [],
	userId,
}: {
	after?: string;
	limit: number;
	query?: string;
	sort?: Sort;
	filter?: SQL;
	languages: string[];
	fallbackLanguage?: boolean;
	preferOriginal?: boolean;
	relations?: (keyof typeof showRelations)[];
	userId: string;
}) {
	const searchQ = db.$with("show_search").as(
		db
			.select({
				pk: showTranslations.pk,
				similarity:
					sql<number>`max(word_similarity(${query ?? ""}::text, ${showTranslations.name}))`.as(
						"__similarity",
					),
			})
			.from(showTranslations)
			.where(
				query
					? or(
							sql`${showTranslations.name} %> ${query}::text`,
							exists(
								db
									.select()
									.from(sql`unnest(${showTranslations.tags}) as tag`)
									.where(sql`tag %> ${query}::text`),
							),
						)
					: sql`false`,
			)
			.groupBy(showTranslations.pk),
	);

	const transQ = db
		.selectDistinctOn([showTranslations.pk])
		.from(showTranslations)
		.where(
			!fallbackLanguage
				? eq(showTranslations.language, sql`any(${sqlarr(languages)})`)
				: undefined,
		)
		.orderBy(
			showTranslations.pk,
			sql`array_position(${sqlarr(languages)}, ${showTranslations.language})`,
			// ensure a stable sort to prevent future pages to contains the same element again
			showTranslations.language,
		)
		.as("t");

	return await db
		.with(searchQ)
		.select({
			...getColumns(shows),
			...getColumns(transQ),

			// movie columns (status is only a typescript hint)
			status: sql<MovieStatus>`${shows.status}`,
			airDate: shows.startAir,
			kind: sql<any>`${shows.kind}`,
			isAvailable: sql<boolean>`${shows.availableCount} != 0`,

			...(preferOriginal && {
				poster: sql<Image>`coalesce(nullif(${shows.original}->'poster', 'null'::jsonb), ${transQ.poster})`,
				thumbnail: sql<Image>`coalesce(nullif(${shows.original}->'thumbnail', 'null'::jsonb), ${transQ.thumbnail})`,
				banner: sql<Image>`coalesce(nullif(${shows.original}->'banner', 'null'::jsonb), ${transQ.banner})`,
				logo: sql<Image>`coalesce(nullif(${shows.original}->'logo', 'null'::jsonb), ${transQ.logo})`,
			}),

			watchStatus: getColumns(watchStatusQ),

			__similarity: searchQ.similarity,

			...buildRelations(relations, showRelations, {
				languages,
				preferOriginal,
			}),
		})
		.from(shows)
		.leftJoin(watchStatusQ, eq(shows.pk, watchStatusQ.showPk))
		.leftJoin(searchQ, eq(shows.pk, searchQ.pk))
		[fallbackLanguage ? "innerJoin" : ("leftJoin" as "innerJoin")](
			transQ,
			eq(shows.pk, transQ.pk),
		)
		.where(
			and(
				filter,
				query ? sql`${searchQ.pk} is not null` : undefined,
				keysetPaginate({ after, sort, query }),
			),
		)
		.orderBy(
			...(query ? [desc(searchQ.similarity)] : sortToSql(sort)),
			shows.pk,
		)
		.limit(limit)
		.execute({ userId });
}
