/**
 * Shared D1 database operations for all scraper adapters.
 * Extracted from index.ts so adapters stay thin and DRY.
 */

import type { ScraperEnv, ScraperResult, ScrapedTitle, AdapterRunResult } from '../types';
import { matchMovie } from '../utils/movie-matcher';
import { isNonTheatricalTitle } from '../utils/title-classification';
import { resolveGenreIds } from '../enricher/genres';
import { normalizeStoredImageProxyPath } from '../../../src/sites/cultroll/lib/image-proxy';
import { syncGeneratedTitleSlugs } from '../../../src/sites/cultroll/lib/title-slugs';

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 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 mergeLanguageCodes(...values: Array<string | null | undefined>): string | null {
  const merged: string[] = [];
  const seen = new Set<string>();
  for (const value of values) {
    for (const code of parseLanguageCodes(value)) {
      if (seen.has(code)) continue;
      seen.add(code);
      merged.push(code);
    }
  }
  return merged.length ? merged.join(',') : 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;
}

export async function logRunStart(db: D1Database, chainId: string, cityId?: string): Promise<number> {
  const result = await db.prepare(
    `INSERT INTO scraper_runs (chain_id, city_id, started_at, status)
     VALUES (?, ?, datetime('now'), 'running')`
  ).bind(chainId, cityId ?? null).run();
  return result.meta.last_row_id as number;
}

export async function logRunEnd(
  db: D1Database, runId: number, status: string,
  titles: number, showtimes: number, error?: string
) {
  await db.prepare(
    `UPDATE scraper_runs SET finished_at = datetime('now'), status = ?, titles_found = ?, showtimes_found = ?, error_message = ?
     WHERE id = ?`
  ).bind(status, titles, showtimes, error ?? null, runId).run();
}

/**
 * 3-step entity resolution:
 * 1. Check title_chain_refs for existing mapping
 * 2. Fuzzy-match against titles table
 * 3. Create new title if no match found
 * Returns the canonical titles.id (UUID).
 */
export async function upsertTitle(
  db: D1Database,
  chainId: string,
  chainMovieId: string,
  title: ScrapedTitle
): Promise<{ titleId: string; isNew: boolean }> {
  const titleLanguage = mergeLanguageCodes(title.language);
  const titleSubtitles = mergeLanguageCodes(title.subtitles);
  const titleImdbId = normalizeImdbId(title.imdb_id);
  const normalizedPosterUrl = normalizeStoredImageProxyPath(title.poster_url ?? null, 'poster');

  const existing = await db.prepare(
    `SELECT title_id FROM title_chain_refs WHERE chain_id = ? AND chain_movie_id = ?`
  ).bind(chainId, chainMovieId).first<{ title_id: string }>();

  if (existing) {
    await db.prepare(
      `UPDATE title_chain_refs SET exhibitor_title_raw = ?, exhibitor_rating = ?, is_coming_soon = ?
       WHERE chain_id = ? AND chain_movie_id = ?`
    ).bind(
      title.title_en, title.exhibitor_rating ?? null,
      title.is_coming_soon ? 1 : 0,
      chainId, chainMovieId
    ).run();
    await mergeTitleMetadata(db, existing.title_id, title);
    await clearExternalIdsForNonTheatrical(db, existing.title_id, title.title_en);
    return { titleId: existing.title_id, isNew: false };
  }

  const fuzzyMatch = await matchMovie(db, title);

  if (fuzzyMatch) {
    await db.prepare(
      `INSERT OR IGNORE INTO title_chain_refs
          (chain_id, chain_movie_id, title_id, exhibitor_title_raw, exhibitor_rating, is_coming_soon, confirmed)
        VALUES (?, ?, ?, ?, ?, ?, ?)`
    ).bind(
      chainId,
      chainMovieId,
      fuzzyMatch.id,
      title.title_en,
      title.exhibitor_rating ?? null,
      title.is_coming_soon ? 1 : 0,
      fuzzyMatch.confirmed ? 1 : 0,
    ).run();
    await mergeTitleMetadata(db, fuzzyMatch.id, title);
    await clearExternalIdsForNonTheatrical(db, fuzzyMatch.id, title.title_en);
    return { titleId: fuzzyMatch.id, isNew: false };
  }

  const canonicalId = crypto.randomUUID();
  const genreIds = await resolveGenreIds(title.genre ?? null, db);
  await db.batch([
    db.prepare(
      `INSERT INTO titles (id, title_en, title_ar, title_original, language, subtitles, genre_raw, genre_ids, duration_min,
         synopsis_en, poster_url, trailer_url, imdb_id, release_date, status, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`
    ).bind(
      canonicalId, title.title_en, title.title_ar ?? null, title.title_original ?? null, titleLanguage, titleSubtitles, title.genre ?? null,
      genreIds, title.duration_min ?? null, title.synopsis_en ?? null,
      normalizedPosterUrl, title.trailer_url ?? null, titleImdbId, title.release_date ?? null,
      title.is_coming_soon ? 'coming_soon' : 'now_playing'
    ),
    db.prepare(
      `INSERT INTO title_chain_refs
         (chain_id, chain_movie_id, title_id, exhibitor_title_raw, exhibitor_rating, is_coming_soon, confirmed)
        VALUES (?, ?, ?, ?, ?, ?, ?)`
    ).bind(chainId, chainMovieId, canonicalId, title.title_en, title.exhibitor_rating ?? null, title.is_coming_soon ? 1 : 0, 0),
  ]);
  await syncGeneratedTitleSlugs(db, canonicalId, title.title_en, canonicalId, title.release_date ?? null);
  await clearExternalIdsForNonTheatrical(db, canonicalId, title.title_en);
  return { titleId: canonicalId, isNew: true };
}

export async function mergeTitleMetadata(db: D1Database, titleId: string, title: ScrapedTitle) {
  const genreIds = await resolveGenreIds(title.genre ?? null, db);
  const normalizedPosterUrl = normalizeStoredImageProxyPath(title.poster_url ?? null, 'poster');
  const existing = await db.prepare(
    `SELECT title_en, language, subtitles, imdb_id, title_original, release_date FROM titles WHERE id = ?`,
  ).bind(titleId).first<{
    title_en: string | null;
    language: string | null;
    subtitles: string | null;
    imdb_id: string | null;
    title_original: string | null;
    release_date: string | null;
  }>();
  const titleLanguage = mergeLanguageCodes(existing?.language, title.language);
  const titleSubtitles = mergeLanguageCodes(existing?.subtitles, title.subtitles);
  const titleImdbId = normalizeImdbId(title.imdb_id) ?? normalizeImdbId(existing?.imdb_id);
  await db.prepare(
    `UPDATE titles SET
       title_ar       = COALESCE(title_ar, ?),
       title_original = COALESCE(title_original, ?),
       language       = COALESCE(?, language),
       subtitles      = COALESCE(?, subtitles),
       genre_raw      = COALESCE(genre_raw, ?),
       genre_ids      = COALESCE(genre_ids, ?),
       duration_min   = COALESCE(duration_min, ?),
       synopsis_en    = COALESCE(synopsis_en, ?),
       poster_url     = COALESCE(poster_url, ?),
       trailer_url    = COALESCE(trailer_url, ?),
       imdb_id        = COALESCE(imdb_id, ?),
       release_date   = COALESCE(release_date, ?),
       updated_at     = datetime('now')
      WHERE id = ? AND tmdb_enriched_at IS NULL`
  ).bind(
    title.title_ar ?? null, title.title_original ?? null, titleLanguage, titleSubtitles, title.genre ?? null, genreIds,
    title.duration_min ?? null, title.synopsis_en ?? null, normalizedPosterUrl,
    title.trailer_url ?? null, titleImdbId, title.release_date ?? null,
    titleId
  ).run();
  await syncGeneratedTitleSlugs(
    db,
    titleId,
    existing?.title_en ?? title.title_en,
    titleId,
    existing?.release_date ?? title.release_date ?? null,
  );
}

async function clearExternalIdsForNonTheatrical(db: D1Database, titleId: string, titleEn: string): Promise<void> {
  if (!isNonTheatricalTitle(titleEn)) return;
  await 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).run();
}

export async function updateTitleStatuses(db: D1Database) {
  await db.batch([
    db.prepare(
      `UPDATE titles SET status = 'now_playing'
       WHERE id IN (SELECT DISTINCT title_id FROM showtimes WHERE showtime >= date('now'))`
    ),
    db.prepare(
      `UPDATE titles SET status = 'coming_soon'
       WHERE id NOT IN (SELECT DISTINCT title_id FROM showtimes WHERE showtime >= date('now'))
       AND (
         id IN (SELECT title_id FROM title_chain_refs WHERE is_coming_soon = 1)
         OR (release_date IS NOT NULL AND release_date > date('now'))
       )`
    ),
    db.prepare(
      `UPDATE titles SET status = 'previously_playing'
       WHERE id NOT IN (SELECT DISTINCT title_id FROM showtimes WHERE showtime >= date('now'))
       AND id IN (SELECT DISTINCT title_id FROM title_chain_refs)
       AND id NOT IN (SELECT title_id FROM title_chain_refs WHERE is_coming_soon = 1)
       AND (release_date IS NULL OR release_date <= date('now'))`
    ),
  ]);
}

export async function replaceShowtimes(db: D1Database, chainId: string, showtimes: Array<{
  id: string; cinema_id: string; title_id: string; showtime: string;
  screen_type?: string; language?: string; subtitles?: string;
  price_display?: string; booking_url?: string;
}>) {
  await db.prepare(
    `DELETE FROM showtimes WHERE cinema_id IN (SELECT id FROM cinemas WHERE chain_id = ?)`
  ).bind(chainId).run();

  const batch = showtimes.map(st =>
    db.prepare(
      `INSERT INTO showtimes (id, cinema_id, title_id, showtime, screen_type, language, subtitles, price_display, booking_url, scraped_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`
    ).bind(
      st.id, st.cinema_id, st.title_id, st.showtime,
      st.screen_type ?? 'standard', st.language ?? null, st.subtitles ?? null,
      st.price_display ?? null, st.booking_url ?? null
    )
  );

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

/**
 * Full scrape pipeline: upsert titles, remap showtime IDs, replace showtimes,
 * update statuses, and log the run. Returns { titles, showtimes } counts.
 * Called by every chain adapter's run() method.
 */
export async function runChainScrape(
  env: ScraperEnv,
  chainId: string,
  cityId: string | undefined,
  result: ScraperResult
): Promise<AdapterRunResult> {
  const runId = await logRunStart(env.DB, chainId, cityId);
  try {
    const showtimeLanguageByTitle = new Map<string, string | null>();
    const showtimeSubtitlesByTitle = new Map<string, string | null>();
    for (const st of result.showtimes) {
      showtimeLanguageByTitle.set(
        st.title_id,
        mergeLanguageCodes(showtimeLanguageByTitle.get(st.title_id), st.language),
      );
      showtimeSubtitlesByTitle.set(
        st.title_id,
        mergeLanguageCodes(showtimeSubtitlesByTitle.get(st.title_id), st.subtitles),
      );
    }

    const titleIdMap = new Map<string, string>();
    let newTitles = 0;
    for (const title of result.titles) {
      const hintedTitle: ScrapedTitle = {
        ...title,
        language: mergeLanguageCodes(title.language, showtimeLanguageByTitle.get(title.chainMovieId)),
        subtitles: mergeLanguageCodes(title.subtitles, showtimeSubtitlesByTitle.get(title.chainMovieId)),
      };
      const upsert = await upsertTitle(env.DB, result.chainId, title.chainMovieId, hintedTitle);
      titleIdMap.set(title.chainMovieId, upsert.titleId);
      if (upsert.isNew) newTitles++;
    }
    const remapped = result.showtimes.map(st => ({
      ...st,
      title_id: titleIdMap.get(st.title_id) ?? st.title_id,
    }));
    await replaceShowtimes(env.DB, chainId, remapped);
    await updateTitleStatuses(env.DB);
    await logRunEnd(env.DB, runId, 'success', result.titles.length, remapped.length);
    console.log(`${chainId}: ${result.titles.length} titles, ${remapped.length} showtimes`);
    return { titles: result.titles.length, showtimes: remapped.length, new_titles: newTitles };
  } catch (err) {
    const msg = err instanceof Error ? err.message : String(err);
    console.error(`${chainId} failed: ${msg}`);
    await logRunEnd(env.DB, runId, 'failed', 0, 0, msg);
    throw err;
  }
}