/**
 * TMDB Enricher
 * Fetches metadata (synopsis, poster, backdrop, trailer, genres, runtime, popularity, imdb_id, original title,
 * languages, director, cast)
 * for titles and stores normalized cast/crew credits.
 *
 * AUTHORITY: TMDB is the canonical metadata source. Canonical TMDB fields are overwritten
 * on every successful sync so metadata stays fresh.
 */
import {
  genreOverlapCount,
  posterFingerprintSimilarity,
  sharedNameOverlapCount,
  titlePhoneticSimilarity,
  titleSimilarity,
} from '../utils/movie-matcher';
import {
  collectTitleVariants as collectTmdbTitleVariants,
  confirmTmdbMatch as confirmTmdbLink,
  pickBestSearchResult as pickTmdbSearchResult,
} from '../utils/tmdb-matcher.ts';
import { extractYear, isNonTheatricalTitle, isRecentTheatricalYear } from '../utils/title-classification';
import { mapWithConcurrency } from '../utils/concurrency';
import { fetchWithTimeout } from '../utils/http';
import { buildCatalogImageProxyPath } from '../../../src/sites/cultroll/lib/image-proxy';
import { syncGeneratedTitleSlugs } from '../../../src/sites/cultroll/lib/title-slugs';

const TMDB_BASE = 'https://api.themoviedb.org/3';
const TMDB_ENRICH_CONCURRENCY = 3;
const TMDB_FETCH_TIMEOUT_MS = 10_000;
const PRIMARY_CREW_JOBS = new Set([
  'Director',
  'Writer',
  'Screenplay',
  'Producer',
  'Executive Producer',
  'Director of Photography',
  'Editor',
  'Original Music Composer',
]);
const YOUTUBE_VIDEO_KEY_RE = /^[A-Za-z0-9_-]{11}$/;
const VIMEO_VIDEO_KEY_RE = /^\d+$/;
const SUPPORTED_VIDEO_TYPES = new Set(['Trailer', 'Teaser']);

const LANGUAGE_TOKEN_TO_CODE: Record<string, string> = {
  english: 'en',
  arabic: 'ar',
  kurdish: 'ku',
  sorani: 'ku',
  french: 'fr',
  spanish: 'es',
  turkish: 'tr',
  hindi: 'hi',
  italian: 'it',
  german: 'de',
  persian: 'fa',
  farsi: 'fa',
  russian: 'ru',
  chinese: 'zh',
  japanese: 'ja',
  korean: 'ko',
  urdu: 'ur',
  portuguese: 'pt',
  dutch: 'nl',
  greek: 'el',
  swedish: 'sv',
  danish: 'da',
  norwegian: 'no',
  finnish: 'fi',
  polish: 'pl',
  czech: 'cs',
  hungarian: 'hu',
  romanian: 'ro',
  thai: 'th',
  vietnamese: 'vi',
  indonesian: 'id',
  malay: 'ms',
  hebrew: 'he',
  amharic: 'am',
  'العربية': 'ar',
  'عربي': 'ar',
  'العربي': 'ar',
  'انگلیسی': 'en',
  'إنجليزي': 'en',
  'انجليزي': 'en',
  'كردي': 'ku',
  'کوردی': 'ku',
};

function isTmdbV3ApiKey(credential: string): boolean {
  return /^[0-9a-f]{32}$/i.test(credential.trim());
}

function buildTmdbRequestInit(
  params: URLSearchParams,
  credential: string,
): RequestInit | undefined {
  const trimmedCredential = credential.trim();
  if (isTmdbV3ApiKey(trimmedCredential)) {
    params.set('api_key', trimmedCredential);
    return undefined;
  }
  return {
    headers: {
      Authorization: `Bearer ${trimmedCredential}`,
    },
  };
}

export interface TmdbSearchResult {
  id: number;
  title: string;
  release_date?: string;
  poster_path?: string | null;
  backdrop_path?: string | null;
  overview?: string;
  popularity?: number;
  vote_average?: number;
  vote_count?: number;
}

export interface TmdbDetails {
  id: number;
  title?: string;
  original_title?: string;
  original_language?: string;
  imdb_id?: string | null;
  runtime?: number | null;
  genres?: Array<{ id: number; name: string }>;
  spoken_languages?: Array<{
    iso_639_1?: string;
    english_name?: string;
    name?: string;
  }>;
  overview?: string;
  release_date?: string;
  poster_path?: string | null;
  backdrop_path?: string | null;
  popularity?: number;
  vote_average?: number;
  vote_count?: number;
  videos?: {
    results?: TmdbVideo[];
  };
}

export interface TmdbCredits {
  crew: Array<{
    id?: number;
    job: string;
    name: string;
    department?: string;
    popularity?: number;
    profile_path?: string | null;
  }>;
  cast: Array<{
    id?: number;
    name: string;
    order: number;
    character?: string;
    known_for_department?: string;
    popularity?: number;
    profile_path?: string | null;
  }>;
}

interface TmdbVideo {
  id?: string;
  iso_639_1?: string;
  iso_3166_1?: string;
  name?: string;
  key?: string;
  site?: string;
  size?: number;
  type?: string;
  official?: boolean;
  published_at?: string;
}

export interface TitleRow {
  id: string;
  title_en: string;
  title_ar?: string | null;
  title_original?: string | null;
  language?: string | null;
  imdb_id?: string | null;
  release_date?: string | null;
  duration_min?: number | null;
  genre_raw?: string | null;
  poster_url?: string | null;
  cast_list?: string | null;
  tmdb_id?: string | null;
}

export interface EnrichOptions {
  limit?: number;
  refreshHours?: number;
  castLimit?: number;
  includeExisting?: boolean;
  titleIds?: string[];
}

export interface EnrichResult {
  enriched: number;
  skipped: number;
  errors: number;
}

export async function enrichMovies(
  db: D1Database,
  apiKey: string,
  optionsOrLimit: EnrichOptions | number = 15,
): Promise<EnrichResult> {
  const credential = apiKey.trim();
  if (!credential) {
    throw new Error('TMDB_API_KEY is not configured');
  }

  const options = normalizeOptions(optionsOrLimit);
  const titles = await loadCandidateTitles(db, options);
  if (titles.length === 0) return { enriched: 0, skipped: 0, errors: 0 };

  let firstError: Error | null = null;
  const outcomes = await mapWithConcurrency(
    titles,
    TMDB_ENRICH_CONCURRENCY,
    async (title): Promise<'enriched' | 'skipped' | 'error'> => {
      try {
        return await enrichTitle(db, credential, options, title);
      } catch (err) {
        const error = err instanceof Error ? err : new Error(String(err));
        firstError ??= error;
        console.error(`TMDB enrichment failed for ${title.id}:`, error);
        return 'error';
      }
    },
  );

  const enriched = outcomes.filter((outcome) => outcome === 'enriched').length;
  const skipped = outcomes.filter((outcome) => outcome === 'skipped').length;
  const errors = outcomes.filter((outcome) => outcome === 'error').length;

  if (errors > 0 && enriched === 0 && skipped === 0 && firstError) {
    throw firstError;
  }

  return { enriched, skipped, errors };
}

async function enrichTitle(
  db: D1Database,
  apiKey: string,
  options: Required<EnrichOptions>,
  title: TitleRow,
): Promise<'enriched' | 'skipped'> {
  const sourceReleaseYear = extractYear(title.release_date ?? null);
  let tmdbId = parseTmdbId(title.tmdb_id);
  let searchResult: TmdbSearchResult | null = null;

  if (isNonTheatricalTitle(title.title_en)) {
    await clearExternalIds(db, title.id);
    return 'skipped';
  }

  if (!tmdbId) {
    searchResult = await searchTmdb(title, apiKey);
    if (!searchResult) {
      await markEnriched(db, title.id);
      return 'skipped';
    }
    tmdbId = searchResult.id;
  }

  const [detailsEn, detailsArResult, credits] = await Promise.all([
    fetchDetails(tmdbId, 'en-US', apiKey, ['videos']),
    fetchDetails(tmdbId, 'ar', apiKey, ['videos']),
    fetchCredits(tmdbId, apiKey),
  ]);

  if (!detailsEn) {
    await clearExternalIds(db, title.id);
    return 'skipped';
  }

  const detailsAr = detailsArResult ?? { id: tmdbId };
  const confirmedMatch = confirmTmdbLink(title, searchResult, detailsEn, detailsAr, credits);
  if (!confirmedMatch.accepted) {
    console.warn(`TMDB confirmation rejected ${title.id}: ${confirmedMatch.reason}`);
    await clearExternalIds(db, title.id);
    return 'skipped';
  }

  const matchedReleaseYear = extractYear(
    detailsEn.release_date || searchResult?.release_date || title.release_date || null,
  );
  if (!isRecentTheatricalYear(matchedReleaseYear)) {
    await clearExternalIds(db, title.id);
    return 'skipped';
  }

  if (isLikelyNonTheatricalMatch({
    title: detailsEn.title || searchResult?.title || title.title_en,
    genres: detailsEn.genres ?? detailsAr.genres,
    popularity: detailsEn.popularity ?? searchResult?.popularity ?? null,
    voteCount: detailsEn.vote_count ?? searchResult?.vote_count ?? null,
  })) {
    await clearExternalIds(db, title.id);
    return 'skipped';
  }

  const directors = credits.crew.filter((c) => c.job === 'Director').map((c) => c.name);
  const director = directors[0] ?? null;

  const castNames = credits.cast
    .sort((a, b) => (a.order ?? 999) - (b.order ?? 999))
    .slice(0, options.castLimit)
    .map((c) => c.name.trim())
    .filter(Boolean);
  const castList = castNames.length ? castNames.join(',') : null;

  const posterPath = detailsEn.poster_path || searchResult?.poster_path || null;
  const backdropPath = detailsEn.backdrop_path || searchResult?.backdrop_path || null;
  const posterUrl = buildCatalogImageProxyPath('poster', posterPath);
  const backdropUrl = buildCatalogImageProxyPath('backdrop', backdropPath);
  const trailerUrl = selectTrailerUrl(title, detailsEn, detailsAr);

  const genreSource = detailsAr.genres?.length ? detailsAr.genres : detailsEn.genres;
  const genreRaw = genreSource?.map((g) => g.name).join(',') || null;
  const genreIds = genreSource?.map((g) => String(g.id)).join(',') || null;
  const mergedLanguage = mergeLanguageCodes(
    title.language,
    extractTmdbLanguageCodes(detailsEn, detailsAr),
  );
  const imdbId = normalizeImdbId(detailsEn.imdb_id) ?? normalizeImdbId(title.imdb_id) ?? null;
  const originalTitle = detailsEn.original_title?.trim() || null;

  await db
    .prepare(
      `UPDATE titles SET
        tmdb_id            = ?,
        imdb_id            = ?,
        title_en           = ?,
        title_ar           = ?,
        title_original     = ?,
        language           = ?,
        synopsis_en        = ?,
        synopsis_ar        = ?,
        poster_url         = ?,
        backdrop_url       = ?,
        trailer_url        = COALESCE(?, trailer_url),
        genre_raw          = ?,
        genre_ids          = ?,
        duration_min       = ?,
        release_date       = COALESCE(?, release_date),
        director           = ?,
        cast_list          = ?,
        tmdb_popularity    = ?,
        tmdb_vote_average  = ?,
        tmdb_vote_count    = ?,
        tmdb_enriched_at   = datetime('now'),
        updated_at         = datetime('now')
       WHERE id = ?`,
    )
    .bind(
      String(tmdbId),
      imdbId,
      detailsEn.title || searchResult?.title || title.title_en,
      detailsAr.title || null,
      originalTitle,
      mergedLanguage,
      detailsEn.overview || searchResult?.overview || null,
      detailsAr.overview || null,
      posterUrl,
      backdropUrl,
      trailerUrl,
      genreRaw,
      genreIds,
      detailsEn.runtime || detailsAr.runtime || null,
      detailsEn.release_date || searchResult?.release_date || null,
      director,
      castList,
      detailsEn.popularity ?? searchResult?.popularity ?? null,
      detailsEn.vote_average ?? searchResult?.vote_average ?? null,
      detailsEn.vote_count ?? searchResult?.vote_count ?? null,
      title.id,
    )
    .run();
  await syncGeneratedTitleSlugs(
    db,
    title.id,
    detailsEn.title || searchResult?.title || title.title_en,
    title.id,
    detailsEn.release_date || searchResult?.release_date || title.release_date || null,
  );
  await db.prepare(`UPDATE title_chain_refs SET confirmed = 1 WHERE title_id = ?`).bind(title.id).run();

  if (genreSource?.length) {
    for (const g of genreSource) {
      await db
        .prepare(`UPDATE genres SET name_ar = ? WHERE id = ? AND (name_ar IS NULL OR name_ar = '')`)
        .bind(g.name, String(g.id))
        .run();
    }
  }

  await upsertTitleCredits(db, title.id, credits, options.castLimit);
  return 'enriched';
}

function normalizeOptions(optionsOrLimit: EnrichOptions | number): Required<EnrichOptions> {
  if (typeof optionsOrLimit === 'number') {
    return {
      limit: Math.max(1, Math.min(200, Math.trunc(optionsOrLimit))),
      refreshHours: 36,
      castLimit: 10,
      includeExisting: false,
      titleIds: [],
    };
  }

  const limit = Number.isFinite(optionsOrLimit.limit)
    ? Math.max(1, Math.min(200, Math.trunc(optionsOrLimit.limit!)))
    : 15;
  const refreshHours = Number.isFinite(optionsOrLimit.refreshHours)
    ? Math.max(1, Math.min(24 * 30, Math.trunc(optionsOrLimit.refreshHours!)))
    : 72;
  const castLimit = Number.isFinite(optionsOrLimit.castLimit)
    ? Math.max(1, Math.min(30, Math.trunc(optionsOrLimit.castLimit!)))
    : 10;
  const includeExisting = optionsOrLimit.includeExisting ?? false;
  const titleIds = Array.isArray(optionsOrLimit.titleIds)
    ? optionsOrLimit.titleIds
      .map((value) => (typeof value === 'string' ? value.trim() : ''))
      .filter((value) => value.length > 0)
    : [];

  return { limit, refreshHours, castLimit, includeExisting, titleIds };
}

async function loadCandidateTitles(db: D1Database, options: Required<EnrichOptions>): Promise<TitleRow[]> {
  if (options.titleIds.length > 0) {
    const ids = options.titleIds.slice(0, options.limit);
    const placeholders = ids.map(() => '?').join(',');
    const { results } = await db
      .prepare(
        `SELECT id, title_en, title_original, language, imdb_id, release_date, tmdb_id
                , title_ar, duration_min, genre_raw, poster_url, cast_list
           FROM titles
           WHERE id IN (${placeholders})
           LIMIT ?`,
      )
      .bind(...ids, options.limit)
      .all<TitleRow>();
    return results ?? [];
  }

  if (!options.includeExisting) {
    const { results } = await db
      .prepare(
        `SELECT id, title_en, title_original, language, imdb_id, release_date, tmdb_id,
                title_ar, duration_min, genre_raw, poster_url, cast_list
           FROM titles
             WHERE (tmdb_id IS NULL AND tmdb_enriched_at IS NULL)
               OR language IS NULL
               OR TRIM(language) = ''
               OR (
                 tmdb_id IS NOT NULL
                 AND (trailer_url IS NULL OR TRIM(trailer_url) = '')
                 AND (
                   tmdb_enriched_at IS NULL
                   OR tmdb_enriched_at <= datetime('now', ?)
                 )
               )
            ORDER BY
              CASE
                WHEN tmdb_id IS NULL AND tmdb_enriched_at IS NULL THEN 0
                WHEN language IS NULL OR TRIM(language) = '' THEN 1
                WHEN
                  tmdb_id IS NOT NULL
                  AND (trailer_url IS NULL OR TRIM(trailer_url) = '')
                  AND (
                    tmdb_enriched_at IS NULL
                    OR tmdb_enriched_at <= datetime('now', ?)
                  ) THEN 2
                ELSE 3
              END,
              created_at DESC
            LIMIT ?`,
      )
      .bind(`-${options.refreshHours} hours`, `-${options.refreshHours} hours`, options.limit)
      .all<TitleRow>();
    return results ?? [];
  }

  const { results } = await db
    .prepare(
       `SELECT id, title_en, title_original, language, imdb_id, release_date, tmdb_id,
               title_ar, duration_min, genre_raw, poster_url, cast_list
          FROM titles
          WHERE tmdb_id IS NULL
            OR tmdb_enriched_at IS NULL
            OR tmdb_enriched_at <= datetime('now', ?)
            OR language IS NULL
            OR TRIM(language) = ''
            OR (
              tmdb_id IS NOT NULL
              AND (trailer_url IS NULL OR TRIM(trailer_url) = '')
              AND (
                tmdb_enriched_at IS NULL
                OR tmdb_enriched_at <= datetime('now', ?)
              )
            )
          ORDER BY
            CASE
              WHEN language IS NULL OR TRIM(language) = '' THEN 0
              WHEN tmdb_id IS NULL THEN 1
              WHEN
                tmdb_id IS NOT NULL
                AND (trailer_url IS NULL OR TRIM(trailer_url) = '')
                AND (
                  tmdb_enriched_at IS NULL
                  OR tmdb_enriched_at <= datetime('now', ?)
                ) THEN 2
              ELSE 3
            END,
            COALESCE(tmdb_enriched_at, '1970-01-01') ASC
          LIMIT ?`,
    )
    .bind(
      `-${options.refreshHours} hours`,
      `-${options.refreshHours} hours`,
      `-${options.refreshHours} hours`,
      options.limit,
    )
    .all<TitleRow>();
  return results ?? [];
}

async function upsertTitleCredits(
  db: D1Database,
  titleId: string,
  credits: TmdbCredits,
  castLimit: number,
): Promise<void> {
  await db.prepare(`DELETE FROM title_credits WHERE title_id = ?`).bind(titleId).run();

  const castRows = credits.cast
    .slice()
    .sort((a, b) => (a.order ?? 999) - (b.order ?? 999))
    .slice(0, castLimit);

  const uniqueCrew = new Map<string, TmdbCredits['crew'][number]>();
  for (const crew of credits.crew) {
    if (!PRIMARY_CREW_JOBS.has(crew.job)) continue;
    const key = `${crew.id ?? crew.name}|${crew.job}`;
    if (!uniqueCrew.has(key)) uniqueCrew.set(key, crew);
  }

  const batch: Array<ReturnType<D1Database['prepare']>> = [];

  castRows.forEach((person, idx) => {
    batch.push(
      db
        .prepare(
          `INSERT INTO title_credits
            (title_id, person_tmdb_id, person_name, credit_type, department, job, character_name, credit_order, is_primary, popularity, profile_path, updated_at)
           VALUES (?, ?, ?, 'cast', ?, 'Cast', ?, ?, ?, ?, ?, datetime('now'))`,
        )
        .bind(
          titleId,
          person.id ? String(person.id) : null,
          person.name,
          person.known_for_department || 'Acting',
          person.character || '',
          person.order ?? idx,
          idx < 3 ? 1 : 0,
          person.popularity ?? null,
          person.profile_path || null,
        ),
    );
  });

  Array.from(uniqueCrew.values()).forEach((person, idx) => {
    const isPrimary = person.job === 'Director' || person.job === 'Writer' || person.job === 'Screenplay';
    batch.push(
      db
        .prepare(
          `INSERT INTO title_credits
            (title_id, person_tmdb_id, person_name, credit_type, department, job, character_name, credit_order, is_primary, popularity, profile_path, updated_at)
           VALUES (?, ?, ?, 'crew', ?, ?, '', ?, ?, ?, ?, datetime('now'))`,
        )
        .bind(
          titleId,
          person.id ? String(person.id) : null,
          person.name,
          person.department || null,
          person.job,
          idx,
          isPrimary ? 1 : 0,
          person.popularity ?? null,
          person.profile_path || null,
        ),
    );
  });

  for (let i = 0; i < batch.length; i += 100) {
    await db.batch(batch.slice(i, i + 100));
  }
}

function parseTmdbId(value: string | null | undefined): number | null {
  if (!value) return null;
  const parsed = Number(value);
  return Number.isInteger(parsed) && parsed > 0 ? parsed : null;
}

function normalizeImdbId(value: string | null | undefined): string | null {
  if (!value) return null;
  const token = value.trim().toLowerCase();
  return /^tt\d{5,}$/.test(token) ? token : null;
}

function normalizeLanguageCode(value: string | null | undefined): string | null {
  if (!value) return null;
  const token = value.trim().toLowerCase();
  if (!token) return null;
  if (/^[a-z]{2,3}(?:-[a-z]{2})?$/.test(token)) return token;
  return LANGUAGE_TOKEN_TO_CODE[token] ?? null;
}

function parseLanguageCodes(value: string | null | undefined): string[] {
  if (!value) return [];
  const out: string[] = [];
  const seen = new Set<string>();
  for (const token of value.split(/[,/|;]+/g)) {
    const code = normalizeLanguageCode(token);
    if (!code || seen.has(code)) continue;
    seen.add(code);
    out.push(code);
  }
  return out;
}

function extractTmdbLanguageCodes(...detailsList: TmdbDetails[]): string[] {
  const codes: string[] = [];
  const seen = new Set<string>();

  for (const details of detailsList) {
    for (const language of details.spoken_languages ?? []) {
      const code = normalizeLanguageCode(language.iso_639_1);
      if (!code || seen.has(code)) continue;
      seen.add(code);
      codes.push(code);
    }
  }

  for (const details of detailsList) {
    const code = normalizeLanguageCode(details.original_language);
    if (!code || seen.has(code)) continue;
    seen.add(code);
    codes.push(code);
  }

  return codes;
}

function mergeLanguageCodes(
  existing: string | null | undefined,
  tmdbCodes: string[],
): string | null {
  const merged: string[] = [];
  const seen = new Set<string>();

  for (const code of parseLanguageCodes(existing)) {
    if (seen.has(code)) continue;
    seen.add(code);
    merged.push(code);
  }
  for (const code of tmdbCodes) {
    if (seen.has(code)) continue;
    seen.add(code);
    merged.push(code);
  }

  return merged.length ? merged.join(',') : null;
}

function selectTrailerUrl(title: TitleRow, ...detailsList: TmdbDetails[]): string | null {
  const preferredLanguages = buildPreferredTrailerLanguages(title, detailsList);
  const candidates = dedupeVideos(detailsList.flatMap((details) => details.videos?.results ?? []))
    .filter((video) => SUPPORTED_VIDEO_TYPES.has((video.type ?? '').trim()))
    .map((video) => {
      const url = buildTmdbVideoUrl(video);
      return url ? { video, url } : null;
    })
    .filter((entry): entry is { video: TmdbVideo; url: string } => Boolean(entry));

  if (candidates.length === 0) return null;

  candidates.sort((left, right) =>
    compareTrailerCandidates(left.video, right.video, preferredLanguages),
  );

  return candidates[0]?.url ?? null;
}

function buildPreferredTrailerLanguages(title: TitleRow, detailsList: TmdbDetails[]): string[] {
  const merged: string[] = [];
  const seen = new Set<string>();
  const sources = [
    ...detailsList.map((details) => details.original_language ?? null),
    title.language ?? null,
    'en',
    'ar',
    'ku',
  ];

  for (const source of sources) {
    for (const code of parseLanguageCodes(source)) {
      if (seen.has(code)) continue;
      seen.add(code);
      merged.push(code);
    }
  }

  return merged;
}

function dedupeVideos(videos: TmdbVideo[]): TmdbVideo[] {
  const merged: TmdbVideo[] = [];
  const seen = new Set<string>();

  for (const video of videos) {
    const key = `${(video.site ?? '').trim().toLowerCase()}::${(video.key ?? '').trim()}`;
    if (!video.key || seen.has(key)) continue;
    seen.add(key);
    merged.push(video);
  }

  return merged;
}

function buildTmdbVideoUrl(video: TmdbVideo): string | null {
  const key = video.key?.trim();
  const site = video.site?.trim().toLowerCase();
  if (!key || !site) return null;

  if (site === 'youtube' && YOUTUBE_VIDEO_KEY_RE.test(key)) {
    return `https://www.youtube.com/watch?v=${key}`;
  }

  if (site === 'vimeo' && VIMEO_VIDEO_KEY_RE.test(key)) {
    return `https://vimeo.com/${key}`;
  }

  return null;
}

function compareTrailerCandidates(
  left: TmdbVideo,
  right: TmdbVideo,
  preferredLanguages: string[],
): number {
  return (
    compareNumbers(videoTypeRank(left), videoTypeRank(right)) ||
    compareNumbers(videoOfficialRank(left), videoOfficialRank(right)) ||
    compareNumbers(videoLanguageRank(left, preferredLanguages), videoLanguageRank(right, preferredLanguages)) ||
    compareNumbers(videoSiteRank(left), videoSiteRank(right)) ||
    compareNumbers(right.size ?? 0, left.size ?? 0) ||
    compareNumbers(parsePublishedAt(right.published_at), parsePublishedAt(left.published_at))
  );
}

function videoTypeRank(video: TmdbVideo): number {
  const type = (video.type ?? '').trim();
  if (type === 'Trailer') return 0;
  if (type === 'Teaser') return 1;
  return 2;
}

function videoOfficialRank(video: TmdbVideo): number {
  return video.official ? 0 : 1;
}

function videoLanguageRank(video: TmdbVideo, preferredLanguages: string[]): number {
  const language = normalizeLanguageCode(video.iso_639_1);
  if (!language) return preferredLanguages.length + 1;
  const idx = preferredLanguages.indexOf(language);
  return idx >= 0 ? idx : preferredLanguages.length + 1;
}

function videoSiteRank(video: TmdbVideo): number {
  const site = (video.site ?? '').trim().toLowerCase();
  if (site === 'youtube') return 0;
  if (site === 'vimeo') return 1;
  return 2;
}

function parsePublishedAt(value: string | null | undefined): number {
  if (!value) return 0;
  const parsed = Date.parse(value);
  return Number.isFinite(parsed) ? parsed : 0;
}

function compareNumbers(left: number, right: number): number {
  if (left < right) return -1;
  if (left > right) return 1;
  return 0;
}

async function markEnriched(db: D1Database, titleId: string): Promise<void> {
  await db
    .prepare("UPDATE titles SET tmdb_enriched_at = datetime('now') WHERE id = ?")
    .bind(titleId)
    .run();
}

async function clearExternalIds(db: D1Database, titleId: string): Promise<void> {
  await db.batch([
    db
      .prepare(
        `UPDATE titles
            SET tmdb_id = NULL,
                imdb_id = NULL,
                tmdb_popularity = NULL,
                tmdb_vote_average = NULL,
                tmdb_vote_count = NULL,
                tmdb_enriched_at = datetime('now'),
                updated_at = datetime('now')
          WHERE id = ?`,
      )
      .bind(titleId),
    db.prepare(`UPDATE title_chain_refs SET confirmed = 0 WHERE title_id = ?`).bind(titleId),
  ]);
}

async function searchTmdb(
  title: TitleRow,
  apiKey: string,
): Promise<TmdbSearchResult | null> {
  const year = extractYear(title.release_date);
  const queries = collectTmdbTitleVariants(title.title_en, title.title_original, title.title_ar);
  let best: { result: TmdbSearchResult; score: number } | null = null;

  for (const query of queries) {
    const params = new URLSearchParams({
      query,
      language: 'en-US',
      page: '1',
      include_adult: 'false',
    });
    if (year) params.set('year', String(year));

    const data = await requestTmdbJson<{ results: TmdbSearchResult[] }>(
      '/search/movie',
      params,
      apiKey,
      `search:${query}`,
    );
    if (!data) continue;

    const candidate = pickTmdbSearchResult(title, data.results ?? []);
    if (!candidate) continue;

    if (!best || candidate.score > best.score) {
      best = candidate;
    }
  }

  return best?.result ?? null;
}

export function pickBestSearchResult(
  title: TitleRow,
  results: TmdbSearchResult[],
): { result: TmdbSearchResult; score: number } | null {
  const sourceYear = extractYear(title.release_date);
  const scored = results
    .map((result) => {
      const titleEvidence = scoreTmdbTitleEvidence(title, [result.title]);
      const releaseYear = extractYear(result.release_date);
      let score = titleEvidence.score;

      if (sourceYear) {
        if (!releaseYear) return null;
        const diff = Math.abs(releaseYear - sourceYear);
        if (diff > 1) return null;
        if (diff === 0) {
          score += 0.05;
        } else if (titleEvidence.score >= 0.93) {
          score += 0.02;
        } else {
          return null;
        }
      } else if (!isRecentTheatricalYear(releaseYear)) {
        return null;
      }

      if (title.poster_url && !result.poster_path && titleEvidence.score < 0.92) {
        return null;
      }

      const posterSimilarity = posterFingerprintSimilarity(title.poster_url ?? null, result.poster_path ?? null);
      if (posterSimilarity !== null && posterSimilarity >= 0.9) {
        score += 0.02;
      }

      return {
        result,
        score,
        similarity: titleEvidence.similarity,
        phonetic: titleEvidence.phonetic,
        releaseYear,
      };
    })
    .filter((entry): entry is { result: TmdbSearchResult; score: number; similarity: number; phonetic: number; releaseYear: number | null } => {
      if (!entry) return false;
      const { result, similarity, phonetic, score, releaseYear } = entry;
      if (!result.title || isLikelyNonTheatricalSearchResult(result)) return false;
      if (similarity < 0.82 && phonetic < 0.88) return false;
      if (sourceYear && !releaseYear) return false;
      return score >= 0.86;
    })
    .sort((a, b) =>
      b.score - a.score ||
      b.similarity - a.similarity ||
      (b.result.popularity ?? 0) - (a.result.popularity ?? 0) ||
      (b.result.vote_count ?? 0) - (a.result.vote_count ?? 0),
    );

  const best = scored[0];
  const runnerUp = scored[1];
  if (
    best &&
    runnerUp &&
    best.score - runnerUp.score < 0.02 &&
    best.releaseYear === runnerUp.releaseYear
  ) {
    return null;
  }

  return best ? { result: best.result, score: best.score } : null;
}

function scoreTmdbTitleEvidence(
  title: TitleRow,
  candidateTitles: Array<string | null | undefined>,
): { similarity: number; phonetic: number; score: number } {
  const sourceVariants = collectTitleVariants(title.title_en, title.title_original, title.title_ar);
  const targetVariants = collectTitleVariants(...candidateTitles);
  let similarity = 0;
  let phonetic = 0;

  for (const sourceVariant of sourceVariants) {
    for (const targetVariant of targetVariants) {
      similarity = Math.max(similarity, titleSimilarity(sourceVariant, targetVariant));
      phonetic = Math.max(phonetic, titlePhoneticSimilarity(sourceVariant, targetVariant));
    }
  }

  return {
    similarity,
    phonetic,
    score: similarity * 0.84 + phonetic * 0.16,
  };
}

function collectTitleVariants(...values: Array<string | null | undefined>): string[] {
  const out: string[] = [];
  const seen = new Set<string>();
  for (const value of values) {
    const trimmed = value?.trim();
    if (!trimmed) continue;
    const key = trimmed.toLowerCase();
    if (seen.has(key)) continue;
    seen.add(key);
    out.push(trimmed);
  }
  return out;
}

export function confirmTmdbMatch(
  title: TitleRow,
  searchResult: TmdbSearchResult | null,
  detailsEn: TmdbDetails,
  detailsAr: TmdbDetails,
  credits: TmdbCredits,
): { accepted: boolean; reason: string } {
  const sourceImdbId = normalizeImdbId(title.imdb_id);
  const tmdbImdbId = normalizeImdbId(detailsEn.imdb_id);
  if (sourceImdbId && tmdbImdbId) {
    return sourceImdbId === tmdbImdbId
      ? { accepted: true, reason: 'matched by imdb id' }
      : { accepted: false, reason: 'imdb mismatch' };
  }

  const titleEvidence = scoreTmdbTitleEvidence(title, [
    detailsEn.title,
    detailsEn.original_title,
    detailsAr.title,
    searchResult?.title,
  ]);
  if (titleEvidence.similarity < 0.82 && titleEvidence.phonetic < 0.88) {
    return { accepted: false, reason: 'weak title similarity' };
  }

  let score = titleEvidence.score;
  let corroboratingSignals = 0;

  const sourceYear = extractYear(title.release_date);
  const matchedYear = extractYear(detailsEn.release_date || searchResult?.release_date || null);
  if (sourceYear && matchedYear) {
    const diff = Math.abs(sourceYear - matchedYear);
    if (diff > 1) return { accepted: false, reason: 'release year mismatch' };
    if (diff === 0) {
      score += 0.06;
      corroboratingSignals += 1;
    } else if (titleEvidence.score >= 0.93) {
      score += 0.02;
      corroboratingSignals += 1;
    } else {
      return { accepted: false, reason: 'release year too far for weak title match' };
    }
  }

  const matchedRuntime = detailsEn.runtime ?? detailsAr.runtime ?? null;
  if (Number.isFinite(title.duration_min) && Number.isFinite(matchedRuntime) && title.duration_min && matchedRuntime) {
    const runtimeDiff = Math.abs(title.duration_min - matchedRuntime);
    if (runtimeDiff > 35) return { accepted: false, reason: 'runtime mismatch' };
    if (runtimeDiff <= 5) {
      score += 0.08;
      corroboratingSignals += 1;
    } else if (runtimeDiff <= 12) {
      score += 0.04;
      corroboratingSignals += 1;
    } else if (runtimeDiff > 20) {
      score -= 0.06;
    }
  }

  const matchedGenres = (detailsEn.genres ?? detailsAr.genres ?? []).map((genre) => genre.name);
  if ((title.genre_raw ?? '').trim() && matchedGenres.length > 0) {
    if (genreOverlapCount(title.genre_raw ?? null, matchedGenres) > 0) {
      score += 0.04;
      corroboratingSignals += 1;
    } else {
      score -= 0.06;
    }
  }

  const posterSimilarity = posterFingerprintSimilarity(
    title.poster_url ?? null,
    detailsEn.poster_path || searchResult?.poster_path || null,
  );
  if (posterSimilarity !== null && posterSimilarity >= 0.9) {
    score += 0.02;
    corroboratingSignals += 1;
  }

  const castOverlap = sharedNameOverlapCount(
    title.cast_list ?? null,
    credits.cast.slice(0, 8).map((person) => person.name),
  );
  if (castOverlap > 0) {
    score += Math.min(0.06, castOverlap * 0.03);
    corroboratingSignals += 1;
  }

  score = Math.max(0, Math.min(1, score));
  const sourceSignalCount = [
    title.release_date,
    title.duration_min,
    title.genre_raw,
    title.poster_url,
    title.cast_list,
    title.imdb_id,
  ].filter(Boolean).length;
  const minimumScore = sourceSignalCount >= 2 ? 0.86 : 0.9;

  if (score < minimumScore) {
    return { accepted: false, reason: `composite score too low (${score.toFixed(2)})` };
  }
  if (sourceSignalCount >= 2 && corroboratingSignals === 0 && titleEvidence.score < 0.93) {
    return { accepted: false, reason: 'missing corroborating metadata signals' };
  }

  return { accepted: true, reason: `confirmed (${score.toFixed(2)})` };
}

function isLikelyNonTheatricalSearchResult(result: TmdbSearchResult): boolean {
  const titleLooksNonTheatrical = isNonTheatricalTitle(result.title);
  if (!titleLooksNonTheatrical) return false;
  return (result.vote_count ?? 0) < 150 && (result.popularity ?? 0) < 30;
}

function isLikelyNonTheatricalMatch(input: {
  title: string;
  genres?: Array<{ id: number; name: string }>;
  popularity: number | null;
  voteCount: number | null;
}): boolean {
  if (isNonTheatricalTitle(input.title)) return true;
  const genres = new Set((input.genres ?? []).map((genre) => genre.name.toLowerCase()));
  const musicHeavy = genres.has('music');
  if (!musicHeavy) return false;
  return (input.voteCount ?? 0) < 200 && (input.popularity ?? 0) < 35;
}

async function fetchDetails(
  tmdbId: number,
  language: string,
  apiKey: string,
  appendToResponse: string[] = [],
): Promise<TmdbDetails | null> {
  const params = new URLSearchParams({ language });
  if (appendToResponse.length > 0) {
    params.set('append_to_response', appendToResponse.join(','));
  }
  return requestTmdbJson<TmdbDetails>(
    `/movie/${tmdbId}`,
    params,
    apiKey,
    `movie:${tmdbId}:${language}`,
    { allowNotFound: true },
  );
}

async function fetchCredits(tmdbId: number, apiKey: string): Promise<TmdbCredits> {
  const params = new URLSearchParams();
  const credits = await requestTmdbJson<TmdbCredits>(
    `/movie/${tmdbId}/credits`,
    params,
    apiKey,
    `credits:${tmdbId}`,
    { allowNotFound: true },
  );
  return credits ?? { crew: [], cast: [] };
}

async function requestTmdbJson<T>(
  path: string,
  params: URLSearchParams,
  credential: string,
  resource: string,
  options: { allowNotFound?: boolean } = {},
): Promise<T | null> {
  const init = buildTmdbRequestInit(params, credential);
  const query = params.toString();
  const url = query ? `${TMDB_BASE}${path}?${query}` : `${TMDB_BASE}${path}`;
  const resp = await fetchWithTimeout(url, init, {
    timeoutMs: TMDB_FETCH_TIMEOUT_MS,
    resource: `TMDB ${resource}`,
  });

  if (options.allowNotFound && resp.status === 404) {
    return null;
  }
  if (!resp.ok) {
    throw await buildTmdbHttpError(resp, resource);
  }
  return (await resp.json()) as T;
}

async function buildTmdbHttpError(resp: Response, resource: string): Promise<Error> {
  let detail = '';
  try {
    const raw = await resp.text();
    if (raw) {
      try {
        const parsed = JSON.parse(raw) as { status_message?: string; message?: string };
        detail = parsed.status_message ?? parsed.message ?? raw;
      } catch {
        detail = raw;
      }
    }
  } catch {
    detail = '';
  }

  const normalizedDetail = detail.trim();
  const suffix = normalizedDetail ? `: ${normalizedDetail}` : '';
  return new Error(`TMDB request failed (${resp.status}) for ${resource}${suffix}`);
}