import { and, eq, inArray } from "drizzle-orm";
import { database } from "@/db";
import {
	credits,
	episodes,
	mediaItems,
	people,
	seasons,
	series,
	titles,
} from "@/db/schema";
import type { LibraryType, SeriesStatus } from "@/db/schema/enums";
import type {
	MetadataLanguage,
	ResolvedSeriesMetadata,
	ResolvedTitleCredits,
	SeriesInfo,
} from "@/models";
import { SeriesNamings } from "@/namings/series";
import type { MetadataService } from "../../metadata";
import {
	BaseLibraryScanner,
	type MediaInspectionResult,
	type ScanFileOptions,
} from "./BaseLibraryScanner";

interface ParsedSeriesInfo {
	title: string;
	season: number;
	episode: number;
}

// Cache for series and season IDs to avoid repeated queries
interface SeriesCacheEntry {
	seriesId: string;
	titleId: string;
	seasons: Map<number, string>; // seasonNumber -> seasonId
}

type DbTransaction = Parameters<Parameters<typeof database.transaction>[0]>[0];

export class SeriesLibraryScanner extends BaseLibraryScanner<
	ParsedSeriesInfo,
	ResolvedSeriesMetadata
> {
	private readonly seriesNamings = new SeriesNamings();
	private readonly seriesCache = new Map<string, SeriesCacheEntry>();

	constructor(metadataService: MetadataService) {
		super("SeriesLibraryScanner", metadataService);
	}

	/**
	 * Clear cache - useful when starting a new scan
	 */
	public clearCache(): void {
		this.seriesCache.clear();
		this.logger.debug("Series cache cleared");
	}

	public getLibraryType(): LibraryType {
		return "SERIES";
	}

	protected async parseFilename(
		filename: string,
	): Promise<ParsedSeriesInfo | null> {
		return await this.seriesNamings.parseFilename(filename);
	}

	protected formatParsedInfo(parsedInfo: ParsedSeriesInfo): string {
		return `"${parsedInfo.title}" S${String(parsedInfo.season).padStart(2, "0")}E${String(parsedInfo.episode).padStart(2, "0")}`;
	}

	protected async resolveMetadata(
		parsedInfo: ParsedSeriesInfo,
		mediaInspection: MediaInspectionResult,
		metadataLanguage: MetadataLanguage,
		filename: string,
	): Promise<ResolvedSeriesMetadata | null> {
		const { durationSeconds, detectedLanguage } = mediaInspection;

		const info: SeriesInfo = {
			fileName: filename,
			runtimeSeconds: durationSeconds,
			title: parsedInfo.title,
			year: null, // Let metadata service provide the year
			language: detectedLanguage || "en",
			metadataLanguage,
			season: parsedInfo.season,
			episode: parsedInfo.episode,
		};

		try {
			// Use metadata queue for rate-limited API calls
			return await this.metadataQueue.add(async () => {
				return await this.metadataService.resolveSeries(info);
			});
		} catch (error) {
			this.logger.debug(`Metadata resolution failed: ${error}`);
			return null;
		}
	}

	protected async insertToDatabase(
		parsedInfo: ParsedSeriesInfo,
		metadata: ResolvedSeriesMetadata | null,
		mediaInspection: MediaInspectionResult,
		options: ScanFileOptions,
	): Promise<boolean> {
		const resolvedMetadata = metadata;
		const { durationSeconds, mediaInfo } = mediaInspection;
		const { absolutePath, libraryName, libraryFolderId } = options;

		try {
			// Use database queue to control concurrent database operations
			await this.databaseQueue.add(async () => {
				await database.transaction(async (tx) => {
					const seriesTitle = resolvedMetadata?.title || parsedInfo.title;
					const seasonNumber =
						resolvedMetadata?.season.number || parsedInfo.season;
					const seasonName = resolvedMetadata?.season.name;

					// Create cache key for this series
					const cacheKey = `${libraryFolderId}:${seriesTitle}`;

					// Try to get from cache first
					let cacheEntry = this.seriesCache.get(cacheKey);
					let seriesId: string;
					let titleId: string;
					let seasonId: string;
					let isNewlyCreatedSeries = false;

					if (cacheEntry) {
						// Use cached IDs
						seriesId = cacheEntry.seriesId;
						titleId = cacheEntry.titleId;

						// Check if season is in cache
						const cachedSeasonId = cacheEntry.seasons.get(seasonNumber);
						if (cachedSeasonId) {
							seasonId = cachedSeasonId;
							this.logger.debug(
								`Using cached series/season IDs for "${seriesTitle}" S${seasonNumber}`,
							);
						} else {
							// Season not in cache, check database
							const existingSeason = await tx
								.select({ id: seasons.id })
								.from(seasons)
								.where(
									and(
										eq(seasons.seriesId, seriesId),
										eq(seasons.seasonNumber, seasonNumber),
									),
								)
								.limit(1);

							if (existingSeason.length > 0) {
								seasonId = existingSeason[0].id;
							} else {
								const insertedSeason = await tx
									.insert(seasons)
									.values({
										seriesId,
										seasonNumber,
										title: seasonName,
									})
									.returning({ id: seasons.id });
								seasonId = insertedSeason[0].id;
							}

							// Add season to cache
							cacheEntry.seasons.set(seasonNumber, seasonId);
						}
					} else {
						// Not in cache, do optimized query with JOIN
						this.logger.debug(
							`Cache miss for "${seriesTitle}", querying database`,
						);

						// Query title and series in one go
						const existingData = await tx
							.select({
								titleId: titles.id,
								seriesId: series.id,
							})
							.from(titles)
							.leftJoin(series, eq(series.titleId, titles.id))
							.where(
								and(
									eq(titles.title, seriesTitle),
									eq(titles.libraryFolderId, libraryFolderId),
								),
							)
							.limit(1);

						if (existingData.length > 0 && existingData[0].titleId) {
							titleId = existingData[0].titleId;

							if (existingData[0].seriesId) {
								seriesId = existingData[0].seriesId;
							} else {
								// Title exists but series doesn't
								const insertedSeries = await tx
									.insert(series)
									.values({
										titleId,
										status: (resolvedMetadata?.status as SeriesStatus) || null,
									})
									.returning({ id: series.id });
								seriesId = insertedSeries[0].id;
								isNewlyCreatedSeries = true;
							}
						} else {
							// Neither title nor series exist, create both
							const insertedTitle = await tx
								.insert(titles)
								.values({
									libraryFolderId,
									type: "SERIES" as const,
									title: seriesTitle,
									originalTitle:
										resolvedMetadata?.originalTitle || parsedInfo.title,
									tagline: resolvedMetadata?.tagline || null,
									overview: resolvedMetadata?.description || null,
									poster: resolvedMetadata?.posterUrl || null,
									backdrop: resolvedMetadata?.backdropUrl || null,
									rating: resolvedMetadata?.rating || null,
									originalLanguage: resolvedMetadata?.originalLanguage || null,
									homepage: resolvedMetadata?.homepage || null,
									year: resolvedMetadata?.year || null,
									releaseDate: resolvedMetadata?.firstAirDate || null,
								})
								.returning({ id: titles.id });
							titleId = insertedTitle[0].id;

							const insertedSeries = await tx
								.insert(series)
								.values({
									titleId,
									status: (resolvedMetadata?.status as SeriesStatus) || null,
								})
								.returning({ id: series.id });
							seriesId = insertedSeries[0].id;
							isNewlyCreatedSeries = true;
						}

						// Now handle season
						const existingSeason = await tx
							.select({ id: seasons.id })
							.from(seasons)
							.where(
								and(
									eq(seasons.seriesId, seriesId),
									eq(seasons.seasonNumber, seasonNumber),
								),
							)
							.limit(1);

						if (existingSeason.length > 0) {
							seasonId = existingSeason[0].id;
						} else {
							const insertedSeason = await tx
								.insert(seasons)
								.values({
									seriesId,
									seasonNumber,
									title: seasonName,
								})
								.returning({ id: seasons.id });
							seasonId = insertedSeason[0].id;
						}

						// Add to cache
						cacheEntry = {
							seriesId,
							titleId,
							seasons: new Map([[seasonNumber, seasonId]]),
						};
						this.seriesCache.set(cacheKey, cacheEntry);
						this.logger.debug(`Cached series/season IDs for "${seriesTitle}"`);
					}

					const episodeNumber =
						resolvedMetadata?.episode.number || parsedInfo.episode;
					const episodeTitle =
						resolvedMetadata?.episode.title || `Episode ${episodeNumber}`;
					const episodeDescription =
						resolvedMetadata?.episode.description || null;
					const episodeRuntime =
						resolvedMetadata?.runtimeSeconds || durationSeconds;

					// Check if episode already exists to avoid duplicate inserts
					const existingEpisode = await tx
						.select({ id: episodes.id })
						.from(episodes)
						.where(
							and(
								eq(episodes.seriesId, seriesId),
								eq(episodes.seasonId, seasonId),
								eq(episodes.episodeNumber, episodeNumber),
							),
						)
						.limit(1);

					if (existingEpisode.length > 0) {
						this.logger.warn(
							`[${libraryName}] Episode already exists: "${parsedInfo.title}" S${String(parsedInfo.season).padStart(2, "0")}E${String(parsedInfo.episode).padStart(2, "0")}`,
						);
						return; // Skip this episode
					}

					const insertedEpisode = await tx
						.insert(episodes)
						.values({
							seriesId,
							seasonId,
							seasonNumber,
							episodeNumber,
							title: episodeTitle,
							overview: episodeDescription,
							airDate: resolvedMetadata?.airDate || null,
							runtimeSeconds: episodeRuntime,
							backgroundPath: resolvedMetadata?.episode.backgroundUrl || null,
						})
						.returning({ id: episodes.id });

					const episodeId = insertedEpisode[0].id;

					const insertedMediaItem = await tx
						.insert(mediaItems)
						.values({
							parentType: "EPISODE" as const,
							movieId: null,
							episodeId,
							path: absolutePath,
							etag: null,
							sizeBytes: mediaInfo.format.size,
							container: mediaInfo.format.format_name || null,
						})
						.returning({ id: mediaItems.id });

					const mediaItemId = insertedMediaItem[0].id;

					if (mediaInfo.streams && mediaInfo.streams.length > 0) {
						await this.insertMediaStreams(mediaItemId, mediaInfo.streams, tx);
					}

					const genresToInsert =
						resolvedMetadata?.genres && resolvedMetadata.genres.length > 0
							? resolvedMetadata.genres
							: ["TV Series"];

					await this.insertGenres(titleId, genresToInsert, tx);

					if (
						resolvedMetadata?.providerIds &&
						resolvedMetadata.providerIds.length > 0
					) {
						await this.insertProviderIds(
							titleId,
							resolvedMetadata.providerIds,
							tx,
						);
					}

					// Parallelize metadata fetching for series and episode credits
					if (resolvedMetadata) {
						try {
							this.logger.debug("Fetching credits from metadata provider");

							// Fetch series and episode credits in parallel
							const [seriesCreditsData, episodeCreditsData] = await Promise.all(
								[
									isNewlyCreatedSeries
										? this.metadataQueue.add(async () =>
												this.resolveSeriesCredits(seriesId, resolvedMetadata),
											)
										: Promise.resolve(null),
									this.metadataQueue.add(async () =>
										this.resolveEpisodeCredits(episodeId, resolvedMetadata),
									),
								],
							);

							// Insert credits in parallel
							await Promise.all([
								seriesCreditsData
									? this.insertSeriesCredits(seriesId, seriesCreditsData, tx)
									: Promise.resolve(),
								episodeCreditsData
									? this.insertEpisodeCredits(episodeId, episodeCreditsData, tx)
									: Promise.resolve(),
							]);
						} catch (error) {
							this.logger.warn(`Failed to resolve/insert credits: ${error}`);
						}
					}

					this.logger.debug(
						`Successfully inserted series "${parsedInfo.title}" S${String(parsedInfo.season).padStart(2, "0")}E${String(parsedInfo.episode).padStart(2, "0")}`,
					);
				});
			});

			this.logger.success(
				`[${libraryName}] Added "${parsedInfo.title}" S${String(parsedInfo.season).padStart(2, "0")}E${String(parsedInfo.episode).padStart(2, "0")}`,
			);
			return true;
		} catch (error) {
			this.logger.error(`[${libraryName}] Failed to insert series: ${error}`);
			return false;
		}
	}

	private async resolveSeriesCredits(
		_seriesId: string,
		metadata: ResolvedSeriesMetadata,
	): Promise<ResolvedTitleCredits | null> {
		try {
			return await this.metadataService.resolveTitleCredits({
				titleType: "SERIES",
				area: "SERIES",
				providerId: metadata.id,
			});
		} catch (error) {
			this.logger.warn(`Failed to resolve series credits: ${error}`);
			return null;
		}
	}

	private async resolveEpisodeCredits(
		_episodeId: string,
		metadata: ResolvedSeriesMetadata,
	): Promise<ResolvedTitleCredits | null> {
		try {
			return await this.metadataService.resolveTitleCredits({
				titleType: "SERIES",
				area: "EPISODE",
				providerId: metadata.id,
				seasonNumber: metadata.season.number,
				episodeNumber: metadata.episode.number,
			});
		} catch (error) {
			this.logger.warn(`Failed to resolve episode credits: ${error}`);
			return null;
		}
	}

	private async insertEpisodeCredits(
		episodeId: string,
		creditsData: ResolvedTitleCredits,
		tx: DbTransaction,
	): Promise<void> {
		// Collect all unique person names
		const allNames = [
			...creditsData.cast.map((c) => c.name),
			...creditsData.crew.map((c) => c.name),
		];

		if (allNames.length === 0) {
			return;
		}

		// Batch lookup existing people (1 query)
		const existingPeople = await tx
			.select({ id: people.id, name: people.name })
			.from(people)
			.where(inArray(people.name, allNames));

		const peopleMap = new Map(existingPeople.map((p) => [p.name, p.id]));

		// Determine which people need to be created
		const missingPeople = [
			...creditsData.cast
				.filter((c) => !peopleMap.has(c.name))
				.map((c) => ({ name: c.name, profilePicture: c.profilePath })),
			...creditsData.crew
				.filter((c) => !peopleMap.has(c.name))
				.map((c) => ({ name: c.name, profilePicture: c.profilePath })),
		];

		// Remove duplicates from missing people
		const uniqueMissingPeople = Array.from(
			new Map(missingPeople.map((p) => [p.name, p])).values(),
		);

		// Batch insert missing people (1 query)
		if (uniqueMissingPeople.length > 0) {
			const newPeople = await tx
				.insert(people)
				.values(uniqueMissingPeople)
				.returning({ id: people.id, name: people.name });

			for (const person of newPeople) {
				peopleMap.set(person.name, person.id);
			}
		}

		// Build all credits entries
		const allCredits = [
			...creditsData.cast
				.map((castMember) => {
					const personId = peopleMap.get(castMember.name);
					if (!personId) return null;
					return {
						personId,
						episodeId,
						movieId: null,
						seriesId: null,
						role: "CAST" as const,
						character: castMember.character,
						department: castMember.knownForDepartment,
						order: castMember.order,
						job: null,
					};
				})
				.filter(
					(credit): credit is NonNullable<typeof credit> => credit !== null,
				),
			...creditsData.crew
				.map((crewMember) => {
					const personId = peopleMap.get(crewMember.name);
					if (!personId) return null;
					return {
						personId,
						episodeId,
						movieId: null,
						seriesId: null,
						role: "CREW" as const,
						character: null,
						department: crewMember.department,
						order: null,
						job: crewMember.job,
					};
				})
				.filter(
					(credit): credit is NonNullable<typeof credit> => credit !== null,
				),
		];

		// Batch insert all credits (1 query)
		if (allCredits.length > 0) {
			await tx.insert(credits).values(allCredits).onConflictDoNothing();
		}

		this.logger.debug(
			`Inserted ${creditsData.cast.length} cast and ${creditsData.crew.length} crew for episode`,
		);
	}

	private async insertSeriesCredits(
		seriesId: string,
		creditsData: ResolvedTitleCredits,
		tx: DbTransaction,
	): Promise<void> {
		// Collect all unique person names
		const allNames = [
			...creditsData.cast.map((c) => c.name),
			...creditsData.crew.map((c) => c.name),
		];

		if (allNames.length === 0) {
			return;
		}

		// Batch lookup existing people (1 query)
		const existingPeople = await tx
			.select({ id: people.id, name: people.name })
			.from(people)
			.where(inArray(people.name, allNames));

		const peopleMap = new Map(existingPeople.map((p) => [p.name, p.id]));

		// Determine which people need to be created
		const missingPeople = [
			...creditsData.cast
				.filter((c) => !peopleMap.has(c.name))
				.map((c) => ({ name: c.name, profilePicture: c.profilePath })),
			...creditsData.crew
				.filter((c) => !peopleMap.has(c.name))
				.map((c) => ({ name: c.name, profilePicture: c.profilePath })),
		];

		// Remove duplicates from missing people
		const uniqueMissingPeople = Array.from(
			new Map(missingPeople.map((p) => [p.name, p])).values(),
		);

		// Batch insert missing people (1 query)
		if (uniqueMissingPeople.length > 0) {
			const newPeople = await tx
				.insert(people)
				.values(uniqueMissingPeople)
				.returning({ id: people.id, name: people.name });

			for (const person of newPeople) {
				peopleMap.set(person.name, person.id);
			}
		}

		// Build all credits entries
		const allCredits = [
			...creditsData.cast
				.map((castMember) => {
					const personId = peopleMap.get(castMember.name);
					if (!personId) return null;
					return {
						personId,
						seriesId,
						movieId: null,
						episodeId: null,
						role: "CAST" as const,
						character: castMember.character,
						department: castMember.knownForDepartment,
						order: castMember.order,
						job: null,
					};
				})
				.filter(
					(credit): credit is NonNullable<typeof credit> => credit !== null,
				),
			...creditsData.crew
				.map((crewMember) => {
					const personId = peopleMap.get(crewMember.name);
					if (!personId) return null;
					return {
						personId,
						seriesId,
						movieId: null,
						episodeId: null,
						role: "CREW" as const,
						character: null,
						department: crewMember.department,
						order: null,
						job: crewMember.job,
					};
				})
				.filter(
					(credit): credit is NonNullable<typeof credit> => credit !== null,
				),
		];

		// Batch insert all credits (1 query)
		if (allCredits.length > 0) {
			await tx.insert(credits).values(allCredits).onConflictDoNothing();
		}

		this.logger.debug(
			`Inserted ${creditsData.cast.length} cast and ${creditsData.crew.length} crew for series`,
		);
	}
}
