import "server-only";
import { db, rawDb } from "./client";
import { images, collections } from "./schema";
import { eq, desc, sql, inArray, and, isNull } from "drizzle-orm";
import type { CardImage } from "@/components/grid/ImageCard";

export type SortKey = "newest" | "oldest" | "az" | "za" | "code-az" | "code-za";

const SORT_CLAUSE: Record<SortKey, string> = {
  newest: "i.created_at DESC, i.id DESC",
  oldest: "i.created_at ASC, i.id ASC",
  az: "COALESCE(i.title, i.filename) COLLATE NOCASE ASC",
  za: "COALESCE(i.title, i.filename) COLLATE NOCASE DESC",
  "code-az": "i.code COLLATE NOCASE ASC",
  "code-za": "i.code COLLATE NOCASE DESC",
};

const COVER_COLS = `
  i.id, i.thumb_path, i.width, i.height,
  i.code, i.title, i.rating, i.watched,
  i.is_vip, i.is_favorite, i.is_owned,
  i.has_video, i.has_subtitle,
  s.name AS studio_name
`;

const COVER_BASE_JOIN = `LEFT JOIN studios s ON s.id = i.studio_id`;

interface CoverRow {
  id: number; thumb_path: string; width: number; height: number;
  code: string | null; title: string | null;
  rating: number | null; watched: number;
  is_vip: number; is_favorite: number; is_owned: number;
  studio_name: string | null;
  has_video: number; has_subtitle: number;
}

function rowToCard(r: CoverRow): CardImage {
  return {
    id: r.id,
    thumbPath: r.thumb_path,
    width: r.width,
    height: r.height,
    code: r.code,
    title: r.title,
    rating: r.rating,
    watched: !!r.watched,
    isVip: !!r.is_vip,
    isFavorite: !!r.is_favorite,
    isOwned: !!r.is_owned,
    studioName: r.studio_name,
    actresses: [],
    hasVideo: !!r.has_video,
    hasSubtitle: !!r.has_subtitle,
  };
}

/**
 * Attach actress info (id/name/slug) to a list of CardImage. One batched
 * query for all ids; results grouped per image. Mutates the cards in
 * place and returns them.
 */
function attachActresses(cards: CardImage[]): CardImage[] {
  if (cards.length === 0) return cards;
  const ids = cards.map((c) => c.id);
  const placeholders = ids.map(() => "?").join(",");
  const rows = rawDb.prepare(`
    SELECT ia.image_id, a.id AS actress_id, a.name, a.slug
    FROM image_actresses ia
    JOIN actresses a ON a.id = ia.actress_id
    WHERE ia.image_id IN (${placeholders})
    ORDER BY a.name
  `).all(...ids) as Array<{ image_id: number; actress_id: number; name: string; slug: string }>;
  const byImage = new Map<number, CardImage["actresses"]>();
  for (const r of rows) {
    const arr = byImage.get(r.image_id) ?? [];
    arr.push({ id: r.actress_id, name: r.name, slug: r.slug });
    byImage.set(r.image_id, arr);
  }
  for (const c of cards) c.actresses = byImage.get(c.id) ?? [];
  return cards;
}

/**
 * Count covers grouped by code's first letter (A-Z, "#" for non-letter / no code).
 * Total returned as the empty key "" for the "All" bucket.
 */
export function libraryLetterCounts(opts?: {
  untagged?: boolean;
  uncollected?: boolean;
  unwatched?: boolean;
  watched?: boolean;
  rated?: boolean;
  unrated?: boolean;
  hasTags?: boolean;
  hasCollection?: boolean;
  hasVideo?: boolean;
  noVideo?: boolean;
  marks?: Array<"vip" | "favorite" | "owned" | "unmarked">;
  actressId?: number;
  // Multi-id filters — must match the same criteria the grid query uses,
  // otherwise the letter counts and the grid will disagree on every
  // filtered view.
  actressIds?: number[];
  actressMode?: FilterMode;
  studioIds?: number[];
  seriesIds?: number[];
  genreIds?: number[];
  genreMode?: FilterMode;
  collectionIds?: number[];
  collectionMode?: FilterMode;
  tagIds?: number[];
  tagMode?: FilterMode;
  /** Tag-category filter. OR mode: cover has any tag whose category_id
   *  is in the selected list. AND mode: cover has at least one tag from
   *  EACH selected category. */
  categoryIds?: number[];
  categoryMode?: FilterMode;
  search?: string;
}): Record<string, number> {
  const where: string[] = ["i.deleted_at IS NULL", "i.parent_image_id IS NULL"];
  const joins: string[] = [];
  const params: Array<string | number> = [];
  if (opts?.untagged) where.push("NOT EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
  if (opts?.uncollected) where.push("NOT EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
  if (opts?.unwatched) where.push("i.watched = 0");
  if (opts?.watched) where.push("i.watched = 1");
  if (opts?.rated) where.push("i.rating IS NOT NULL");
  if (opts?.unrated) where.push("i.rating IS NULL");
  if (opts?.hasTags) where.push("EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
  if (opts?.hasCollection) where.push("EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
  if (opts?.hasVideo) where.push("i.has_video = 1");
  if (opts?.noVideo) where.push("i.has_video = 0");
  if (opts?.marks && opts.marks.length > 0) {
    // Multi-select marks are OR'd: a cover that is VIP or Owned matches "vip+owned".
    const expr: Record<string, string> = {
      vip: "i.is_vip = 1",
      favorite: "i.is_favorite = 1",
      owned: "i.is_owned = 1",
      unmarked: "(i.is_vip = 0 AND i.is_favorite = 0)",
    };
    const parts = opts.marks.map((m) => expr[m]).filter(Boolean);
    if (parts.length > 0) where.push(`(${parts.join(" OR ")})`);
  }
  if (opts?.actressId != null) {
    joins.push("JOIN image_actresses ia ON ia.image_id = i.id");
    where.push("ia.actress_id = ?");
    params.push(opts.actressId);
  }
  const placeholders = (n: number) => Array(n).fill("?").join(",");
  function applyMulti(ids: number[] | undefined, mode: FilterMode | undefined, tableExists: string, fkExists: string) {
    if (!ids || ids.length === 0) return;
    if (mode === "and") {
      for (const id of ids) {
        where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} = ?)`);
        params.push(id);
      }
    } else {
      where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} IN (${placeholders(ids.length)}))`);
      params.push(...ids);
    }
  }
  applyMulti(opts?.actressIds, opts?.actressMode, "image_actresses", "actress_id");
  applyMulti(opts?.genreIds, opts?.genreMode, "image_genres", "genre_id");
  applyMulti(opts?.tagIds, opts?.tagMode, "image_tags", "tag_id");
  applyMulti(opts?.collectionIds, opts?.collectionMode, "collection_images", "collection_id");
  applyCategoryFilter(where, params, opts?.categoryIds, opts?.categoryMode, placeholders);
  if (opts?.studioIds && opts.studioIds.length > 0) {
    where.push(`i.studio_id IN (${placeholders(opts.studioIds.length)})`);
    params.push(...opts.studioIds);
  }
  if (opts?.seriesIds && opts.seriesIds.length > 0) {
    where.push(`i.series_id IN (${placeholders(opts.seriesIds.length)})`);
    params.push(...opts.seriesIds);
  }
  if (opts?.search) {
    const q = opts.search.trim();
    if (q) {
      const like = `%${q.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
      where.push("(COALESCE(i.code,'') LIKE ? ESCAPE '\\' OR COALESCE(i.title,'') LIKE ? ESCAPE '\\' OR COALESCE(i.notes,'') LIKE ? ESCAPE '\\')");
      params.push(like, like, like);
    }
  }
  const rows = rawDb.prepare(`
    SELECT
      CASE
        WHEN i.code IS NULL THEN '#'
        WHEN substr(upper(i.code), 1, 1) BETWEEN 'A' AND 'Z' THEN substr(upper(i.code), 1, 1)
        ELSE '#'
      END AS bucket,
      COUNT(*) AS n
    FROM images i
    ${joins.join("\n    ")}
    WHERE ${where.join(" AND ")}
    GROUP BY bucket
  `).all(...params) as Array<{ bucket: string; n: number }>;
  const out: Record<string, number> = { "": 0 };
  for (const r of rows) {
    out[r.bucket] = r.n;
    out[""] += r.n;
  }
  return out;
}

export type FilterMode = "or" | "and";

/** Translate a tag-category filter into SQL EXISTS clauses on the
 *  composed `where` and `params` arrays. OR mode: at least one tag of
 *  the cover lives in any selected category. AND mode: cover has at
 *  least one tag from EACH selected category. */
function applyCategoryFilter(
  where: string[],
  params: Array<string | number>,
  ids: number[] | undefined,
  mode: FilterMode | undefined,
  placeholders: (n: number) => string,
) {
  if (!ids || ids.length === 0) return;
  if (mode === "and") {
    for (const id of ids) {
      where.push(`EXISTS (
        SELECT 1 FROM image_tags it_c
        JOIN tags t_c ON t_c.id = it_c.tag_id
        WHERE it_c.image_id = i.id AND t_c.category_id = ?
      )`);
      params.push(id);
    }
  } else {
    where.push(`EXISTS (
      SELECT 1 FROM image_tags it_c
      JOIN tags t_c ON t_c.id = it_c.tag_id
      WHERE it_c.image_id = i.id AND t_c.category_id IN (${placeholders(ids.length)})
    )`);
    params.push(...ids);
  }
}

export function listImages(opts?: {
  limit?: number;
  /** Skip this many rows before applying limit. Used for paginated UIs. */
  offset?: number;
  tagId?: number;
  collectionId?: number;
  studioId?: number;
  labelId?: number;
  seriesId?: number;
  actressId?: number;
  genreId?: number;
  /** Multi-select equivalents. AND-mode requires the cover to match every id; OR-mode requires any. */
  actressIds?: number[];
  actressMode?: FilterMode;
  studioIds?: number[];
  seriesIds?: number[];
  genreIds?: number[];
  genreMode?: FilterMode;
  collectionIds?: number[];
  collectionMode?: FilterMode;
  tagIds?: number[];
  tagMode?: FilterMode;
  /** Tag-category filter; expands to "any tag in this category" via EXISTS. */
  categoryIds?: number[];
  categoryMode?: FilterMode;
  untagged?: boolean;
  uncollected?: boolean;
  unwatched?: boolean;
  watched?: boolean;
  rated?: boolean;
  unrated?: boolean;
  hasTags?: boolean;
  hasCollection?: boolean;
  hasVideo?: boolean;
  noVideo?: boolean;
  /** Mark filter: "vip" / "favorite" / "unmarked" — restrict by cover-level VIP/Favorite flag. */
  marks?: Array<"vip" | "favorite" | "owned" | "unmarked">;
  sort?: SortKey;
  /** Filter by code's first letter (uppercase A-Z) or "#" for non-letter / no code. */
  letter?: string;
  /** Free-text substring match against code, title, and notes. */
  search?: string;
}): CardImage[] {
  const limit = opts?.limit ?? 200;
  const joins: string[] = [COVER_BASE_JOIN];
  const where: string[] = ["i.deleted_at IS NULL", "i.parent_image_id IS NULL"];
  const params: Array<string | number> = [];
  const sortKey: SortKey = opts?.sort ?? "newest";
  let order = SORT_CLAUSE[sortKey];

  if (opts?.tagId != null) {
    joins.push("JOIN image_tags it ON it.image_id = i.id");
    where.push("it.tag_id = ?");
    params.push(opts.tagId);
  }
  if (opts?.actressId != null) {
    joins.push("JOIN image_actresses ia ON ia.image_id = i.id");
    where.push("ia.actress_id = ?");
    params.push(opts.actressId);
  }
  if (opts?.genreId != null) {
    joins.push("JOIN image_genres ig ON ig.image_id = i.id");
    where.push("ig.genre_id = ?");
    params.push(opts.genreId);
  }
  if (opts?.collectionId != null) {
    joins.push("JOIN collection_images ci ON ci.image_id = i.id");
    where.push("ci.collection_id = ?");
    params.push(opts.collectionId);
    if (!opts.sort) order = "ci.position ASC, i.created_at DESC";
  }
  if (opts?.studioId != null) {
    where.push("i.studio_id = ?");
    params.push(opts.studioId);
  }
  if (opts?.labelId != null) {
    where.push("i.label_id = ?");
    params.push(opts.labelId);
  }
  if (opts?.seriesId != null) {
    where.push("i.series_id = ?");
    params.push(opts.seriesId);
  }
  // Multi-id filters. Skip empties; in AND mode, emit one EXISTS per id; in OR mode, a single IN clause.
  const placeholders = (n: number) => Array(n).fill("?").join(",");
  function applyMulti(ids: number[] | undefined, mode: FilterMode | undefined, tableExists: string, fkExists: string) {
    if (!ids || ids.length === 0) return;
    if (mode === "and") {
      for (const id of ids) {
        where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} = ?)`);
        params.push(id);
      }
    } else {
      where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} IN (${placeholders(ids.length)}))`);
      params.push(...ids);
    }
  }
  applyMulti(opts?.actressIds, opts?.actressMode, "image_actresses", "actress_id");
  applyMulti(opts?.genreIds, opts?.genreMode, "image_genres", "genre_id");
  applyMulti(opts?.tagIds, opts?.tagMode, "image_tags", "tag_id");
  applyMulti(opts?.collectionIds, opts?.collectionMode, "collection_images", "collection_id");
  applyCategoryFilter(where, params, opts?.categoryIds, opts?.categoryMode, placeholders);
  // Studios and series live as scalar columns on images; AND across multiple is impossible (a cover has one studio).
  if (opts?.studioIds && opts.studioIds.length > 0) {
    where.push(`i.studio_id IN (${placeholders(opts.studioIds.length)})`);
    params.push(...opts.studioIds);
  }
  if (opts?.seriesIds && opts.seriesIds.length > 0) {
    where.push(`i.series_id IN (${placeholders(opts.seriesIds.length)})`);
    params.push(...opts.seriesIds);
  }
  if (opts?.untagged) {
    where.push("NOT EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
  }
  if (opts?.uncollected) {
    where.push("NOT EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
  }
  if (opts?.unwatched) {
    where.push("i.watched = 0");
  }
  if (opts?.watched) {
    where.push("i.watched = 1");
  }
  if (opts?.rated) {
    where.push("i.rating IS NOT NULL");
  }
  if (opts?.unrated) {
    where.push("i.rating IS NULL");
  }
  if (opts?.hasTags) {
    where.push("EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
  }
  if (opts?.hasCollection) {
    where.push("EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
  }
  if (opts?.hasVideo) {
    where.push("i.has_video = 1");
  }
  if (opts?.noVideo) {
    where.push("i.has_video = 0");
  }
  if (opts?.marks && opts.marks.length > 0) {
    // Multi-select marks are OR'd: a cover that is VIP or Owned matches "vip+owned".
    const expr: Record<string, string> = {
      vip: "i.is_vip = 1",
      favorite: "i.is_favorite = 1",
      owned: "i.is_owned = 1",
      unmarked: "(i.is_vip = 0 AND i.is_favorite = 0)",
    };
    const parts = opts.marks.map((m) => expr[m]).filter(Boolean);
    if (parts.length > 0) where.push(`(${parts.join(" OR ")})`);
  }
  if (opts?.letter) {
    if (opts.letter === "#") {
      where.push("(i.code IS NULL OR substr(upper(i.code), 1, 1) NOT BETWEEN 'A' AND 'Z')");
    } else if (/^[A-Z]$/.test(opts.letter)) {
      where.push("substr(upper(i.code), 1, 1) = ?");
      params.push(opts.letter);
    }
  }
  if (opts?.search) {
    const q = opts.search.trim();
    if (q) {
      const like = `%${q.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
      where.push("(COALESCE(i.code,'') LIKE ? ESCAPE '\\' OR COALESCE(i.title,'') LIKE ? ESCAPE '\\' OR COALESCE(i.notes,'') LIKE ? ESCAPE '\\')");
      params.push(like, like, like);
    }
  }

  const offset = Math.max(0, opts?.offset ?? 0);
  const sqlText = `
    SELECT ${COVER_COLS}
    FROM images i
    ${joins.join("\n    ")}
    WHERE ${where.join(" AND ")}
    ORDER BY ${order}
    LIMIT ? OFFSET ?
  `;
  params.push(limit, offset);
  const rows = rawDb.prepare(sqlText).all(...params) as CoverRow[];
  return attachActresses(rows.map(rowToCard));
}

/**
 * Count rows matching the same filter set as listImages. Used by the
 * paginated UI to know totalPages. Mirrors the WHERE-clause logic
 * verbatim — keep these in sync if listImages adds a new filter.
 */
export function countImages(opts?: Parameters<typeof listImages>[0]): number {
  const joins: string[] = [COVER_BASE_JOIN];
  const where: string[] = ["i.deleted_at IS NULL", "i.parent_image_id IS NULL"];
  const params: Array<string | number> = [];

  if (opts?.tagId != null) {
    joins.push("JOIN image_tags it ON it.image_id = i.id");
    where.push("it.tag_id = ?");
    params.push(opts.tagId);
  }
  if (opts?.actressId != null) {
    joins.push("JOIN image_actresses ia ON ia.image_id = i.id");
    where.push("ia.actress_id = ?");
    params.push(opts.actressId);
  }
  if (opts?.genreId != null) {
    joins.push("JOIN image_genres ig ON ig.image_id = i.id");
    where.push("ig.genre_id = ?");
    params.push(opts.genreId);
  }
  if (opts?.collectionId != null) {
    joins.push("JOIN collection_images ci ON ci.image_id = i.id");
    where.push("ci.collection_id = ?");
    params.push(opts.collectionId);
  }
  if (opts?.studioId != null) { where.push("i.studio_id = ?"); params.push(opts.studioId); }
  if (opts?.labelId != null) { where.push("i.label_id = ?"); params.push(opts.labelId); }
  if (opts?.seriesId != null) { where.push("i.series_id = ?"); params.push(opts.seriesId); }

  const placeholders = (n: number) => Array(n).fill("?").join(",");
  function applyMulti(ids: number[] | undefined, mode: FilterMode | undefined, tableExists: string, fkExists: string) {
    if (!ids || ids.length === 0) return;
    if (mode === "and") {
      for (const id of ids) {
        where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} = ?)`);
        params.push(id);
      }
    } else {
      where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} IN (${placeholders(ids.length)}))`);
      params.push(...ids);
    }
  }
  applyMulti(opts?.actressIds, opts?.actressMode, "image_actresses", "actress_id");
  applyMulti(opts?.genreIds, opts?.genreMode, "image_genres", "genre_id");
  applyMulti(opts?.tagIds, opts?.tagMode, "image_tags", "tag_id");
  applyMulti(opts?.collectionIds, opts?.collectionMode, "collection_images", "collection_id");
  applyCategoryFilter(where, params, opts?.categoryIds, opts?.categoryMode, placeholders);
  if (opts?.studioIds && opts.studioIds.length > 0) {
    where.push(`i.studio_id IN (${placeholders(opts.studioIds.length)})`);
    params.push(...opts.studioIds);
  }
  if (opts?.seriesIds && opts.seriesIds.length > 0) {
    where.push(`i.series_id IN (${placeholders(opts.seriesIds.length)})`);
    params.push(...opts.seriesIds);
  }
  if (opts?.untagged) where.push("NOT EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
  if (opts?.uncollected) where.push("NOT EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
  if (opts?.unwatched) where.push("i.watched = 0");
  if (opts?.watched) where.push("i.watched = 1");
  if (opts?.rated) where.push("i.rating IS NOT NULL");
  if (opts?.unrated) where.push("i.rating IS NULL");
  if (opts?.hasTags) where.push("EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
  if (opts?.hasCollection) where.push("EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
  if (opts?.hasVideo) where.push("i.has_video = 1");
  if (opts?.noVideo) where.push("i.has_video = 0");
  if (opts?.marks && opts.marks.length > 0) {
    const expr: Record<string, string> = {
      vip: "i.is_vip = 1",
      favorite: "i.is_favorite = 1",
      owned: "i.is_owned = 1",
      unmarked: "(i.is_vip = 0 AND i.is_favorite = 0)",
    };
    const parts = opts.marks.map((m) => expr[m]).filter(Boolean);
    if (parts.length > 0) where.push(`(${parts.join(" OR ")})`);
  }
  if (opts?.letter) {
    if (opts.letter === "#") {
      where.push("(i.code IS NULL OR substr(upper(i.code), 1, 1) NOT BETWEEN 'A' AND 'Z')");
    } else if (/^[A-Z]$/.test(opts.letter)) {
      where.push("substr(upper(i.code), 1, 1) = ?");
      params.push(opts.letter);
    }
  }
  if (opts?.search) {
    const q = opts.search.trim();
    if (q) {
      const like = `%${q.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
      where.push("(COALESCE(i.code,'') LIKE ? ESCAPE '\\' OR COALESCE(i.title,'') LIKE ? ESCAPE '\\' OR COALESCE(i.notes,'') LIKE ? ESCAPE '\\')");
      params.push(like, like, like);
    }
  }
  const sqlText = `
    SELECT COUNT(*) AS n
    FROM images i
    ${joins.join("\n    ")}
    WHERE ${where.join(" AND ")}
  `;
  const row = rawDb.prepare(sqlText).get(...params) as { n: number };
  return row.n;
}

export function getImageDetail(id: number) {
  const image = db
    .select()
    .from(images)
    .where(and(eq(images.id, id), isNull(images.deletedAt), isNull(images.parentImageId)))
    .get();
  if (!image) return null;
  const studio = image.studioId
    ? rawDb.prepare(`SELECT id, name, slug FROM studios WHERE id = ?`).get(image.studioId) as { id: number; name: string; slug: string } | undefined
    : null;
  const label = image.labelId
    ? rawDb.prepare(`SELECT id, name, slug FROM labels WHERE id = ?`).get(image.labelId) as { id: number; name: string; slug: string } | undefined
    : null;
  const seriesRow = image.seriesId
    ? rawDb.prepare(`SELECT id, name, slug FROM series WHERE id = ?`).get(image.seriesId) as { id: number; name: string; slug: string } | undefined
    : null;
  const actresses = rawDb.prepare(`
    SELECT a.id, a.name, a.slug FROM actresses a
    JOIN image_actresses ia ON ia.actress_id = a.id
    WHERE ia.image_id = ? ORDER BY a.name
  `).all(id) as Array<{ id: number; name: string; slug: string }>;
  const genres = rawDb.prepare(`
    SELECT g.id, g.name, g.slug FROM genres g
    JOIN image_genres ig ON ig.genre_id = g.id
    WHERE ig.image_id = ? ORDER BY g.name
  `).all(id) as Array<{ id: number; name: string; slug: string }>;
  const tagsList = rawDb.prepare(`
    SELECT t.id, t.name, t.color
    FROM tags t JOIN image_tags it ON it.tag_id = t.id
    WHERE it.image_id = ? ORDER BY t.name
  `).all(id) as Array<{ id: number; name: string; color: string | null }>;
  const collectionsList = rawDb.prepare(`
    SELECT c.id, c.name, c.slug FROM collections c
    JOIN collection_images ci ON ci.collection_id = c.id
    WHERE ci.image_id = ?
  `).all(id) as Array<{ id: number; name: string; slug: string }>;
  return { image, studio: studio ?? null, label: label ?? null, series: seriesRow ?? null, actresses, genres, tags: tagsList, collections: collectionsList };
}

export function searchCovers(query: string, limit = 200): CardImage[] {
  const trimmed = query.trim();
  if (!trimmed) return [];
  const tokens = trimmed.split(/\s+/).filter(Boolean);

  // 1. FTS over code/title/director/notes — preserves rank ordering.
  const ftsQuery = tokens.map((t) => `"${t.replace(/"/g, '""')}"*`).join(" ");
  const ftsRows = rawDb.prepare(`
    SELECT i.id
    FROM covers_fts f
    JOIN images i ON i.id = f.rowid
    WHERE covers_fts MATCH ? AND i.deleted_at IS NULL AND i.parent_image_id IS NULL
    ORDER BY rank
  `).all(ftsQuery) as Array<{ id: number }>;

  // 2. Actress name match — all tokens must appear in name OR alt_names, in any
  //    order. This makes "Ichika Matsumoto" == "Matsumoto Ichika".
  const conds = tokens
    .map(() => `(LOWER(a.name) LIKE ? ESCAPE '\\' OR LOWER(COALESCE(a.alt_names, '')) LIKE ? ESCAPE '\\')`)
    .join(" AND ");
  const params: string[] = [];
  for (const t of tokens) {
    const p = `%${escapeLike(t.toLowerCase())}%`;
    params.push(p, p);
  }
  const actressRows = rawDb.prepare(`
    SELECT DISTINCT i.id
    FROM images i
    JOIN image_actresses ia ON ia.image_id = i.id
    JOIN actresses a ON a.id = ia.actress_id
    WHERE ${conds} AND i.deleted_at IS NULL AND i.parent_image_id IS NULL
  `).all(...params) as Array<{ id: number }>;

  // Merge: FTS first (better ranked), then actress matches.
  const seen = new Set<number>();
  const orderedIds: number[] = [];
  for (const r of ftsRows) if (!seen.has(r.id)) { seen.add(r.id); orderedIds.push(r.id); }
  for (const r of actressRows) if (!seen.has(r.id)) { seen.add(r.id); orderedIds.push(r.id); }
  if (orderedIds.length === 0) return [];

  const limited = orderedIds.slice(0, limit);
  const placeholders = limited.map(() => "?").join(",");
  const rows = rawDb.prepare(`
    SELECT ${COVER_COLS}
    FROM images i
    ${COVER_BASE_JOIN}
    WHERE i.id IN (${placeholders})
  `).all(...limited) as CoverRow[];
  const byId = new Map(rows.map((r) => [r.id, r]));
  return attachActresses(limited.map((id) => byId.get(id)).filter((r): r is CoverRow => !!r).map(rowToCard));
}

export function listImagesByIds(ids: number[]): CardImage[] {
  if (!ids || ids.length === 0) return [];
  const placeholders = ids.map(() => "?").join(",");
  const rows = rawDb.prepare(`
    SELECT ${COVER_COLS}
    FROM images i
    ${COVER_BASE_JOIN}
    WHERE i.id IN (${placeholders}) AND i.deleted_at IS NULL
  `).all(...ids) as CoverRow[];
  const byId = new Map(rows.map((r) => [r.id, r]));
  // Preserve caller's order (queue order matters).
  return attachActresses(
    ids.map((id) => byId.get(id)).filter((r): r is CoverRow => !!r).map(rowToCard),
  );
}

export type TagSort = "az" | "count";

export interface TagRow {
  id: number;
  name: string;
  color: string | null;
  count: number;
  categoryId: number | null;
  categoryName: string | null;
  categoryColor: string | null;
}

export function listAllTags(sort: TagSort = "az"): TagRow[] {
  const order = sort === "count"
    ? "count DESC, t.name COLLATE NOCASE"
    : "t.name COLLATE NOCASE";
  return rawDb.prepare(`
    SELECT
      t.id, t.name, t.color,
      COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL THEN 1 END) AS count,
      t.category_id AS categoryId,
      tc.name AS categoryName,
      tc.color AS categoryColor
    FROM tags t
    LEFT JOIN image_tags it ON it.tag_id = t.id
    LEFT JOIN images i ON i.id = it.image_id
    LEFT JOIN tag_categories tc ON tc.id = t.category_id
    GROUP BY t.id ORDER BY ${order}
  `).all() as TagRow[];
}

export interface TagCategoryRow {
  id: number;
  name: string;
  slug: string;
  color: string | null;
  description: string | null;
  tagCount: number;
  imageCount: number;
  coverPortraitPath: string | null;
  coverPortraitZoom: number;
  coverPortraitOffsetX: number;
  coverPortraitOffsetY: number;
  coverLandscapePath: string | null;
  coverLandscapeZoom: number;
  coverLandscapeOffsetX: number;
  coverLandscapeOffsetY: number;
}

export type CategorySort = "az" | "count";

export function listAllTagCategories(sort: CategorySort = "az"): TagCategoryRow[] {
  const order = sort === "count"
    ? "imageCount DESC, c.name COLLATE NOCASE"
    : "c.name COLLATE NOCASE";
  return rawDb.prepare(`
    SELECT
      c.id, c.name, c.slug, c.color, c.description,
      c.cover_portrait_path     AS coverPortraitPath,
      c.cover_portrait_zoom     AS coverPortraitZoom,
      c.cover_portrait_offset_x AS coverPortraitOffsetX,
      c.cover_portrait_offset_y AS coverPortraitOffsetY,
      c.cover_landscape_path     AS coverLandscapePath,
      c.cover_landscape_zoom     AS coverLandscapeZoom,
      c.cover_landscape_offset_x AS coverLandscapeOffsetX,
      c.cover_landscape_offset_y AS coverLandscapeOffsetY,
      COUNT(DISTINCT t.id) AS tagCount,
      COUNT(DISTINCT CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL THEN i.id END) AS imageCount
    FROM tag_categories c
    LEFT JOIN tags t ON t.category_id = c.id
    LEFT JOIN image_tags it ON it.tag_id = t.id
    LEFT JOIN images i ON i.id = it.image_id
    GROUP BY c.id ORDER BY ${order}
  `).all() as TagCategoryRow[];
}

export interface TagCategoryDetail {
  id: number;
  name: string;
  slug: string;
  color: string | null;
  description: string | null;
  coverPortraitPath: string | null;
  coverPortraitZoom: number;
  coverPortraitOffsetX: number;
  coverPortraitOffsetY: number;
  coverLandscapePath: string | null;
  coverLandscapeZoom: number;
  coverLandscapeOffsetX: number;
  coverLandscapeOffsetY: number;
}

export function getTagCategoryBySlug(slug: string): TagCategoryDetail | undefined {
  return rawDb.prepare(`
    SELECT
      id, name, slug, color, description,
      cover_portrait_path     AS coverPortraitPath,
      cover_portrait_zoom     AS coverPortraitZoom,
      cover_portrait_offset_x AS coverPortraitOffsetX,
      cover_portrait_offset_y AS coverPortraitOffsetY,
      cover_landscape_path     AS coverLandscapePath,
      cover_landscape_zoom     AS coverLandscapeZoom,
      cover_landscape_offset_x AS coverLandscapeOffsetX,
      cover_landscape_offset_y AS coverLandscapeOffsetY
    FROM tag_categories WHERE slug = ?
  `).get(slug) as TagCategoryDetail | undefined;
}

export function listTagsInCategory(categoryId: number): Array<{ id: number; name: string; color: string | null; count: number }> {
  return rawDb.prepare(`
    SELECT t.id, t.name, t.color, COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL THEN 1 END) AS count
    FROM tags t
    LEFT JOIN image_tags it ON it.tag_id = t.id
    LEFT JOIN images i ON i.id = it.image_id
    WHERE t.category_id = ?
    GROUP BY t.id ORDER BY t.name COLLATE NOCASE
  `).all(categoryId) as Array<{ id: number; name: string; color: string | null; count: number }>;
}

export function listAllCollections() {
  return rawDb.prepare(`
    SELECT c.id, c.name, c.slug, c.description,
           c.cover_portrait_path  AS coverPortraitPath,
           c.cover_portrait_zoom  AS coverPortraitZoom,
           c.cover_portrait_offset_x AS coverPortraitOffsetX,
           c.cover_portrait_offset_y AS coverPortraitOffsetY,
           c.cover_landscape_path AS coverLandscapePath,
           c.cover_landscape_zoom AS coverLandscapeZoom,
           c.cover_landscape_offset_x AS coverLandscapeOffsetX,
           c.cover_landscape_offset_y AS coverLandscapeOffsetY,
           (SELECT thumb_path FROM images WHERE id = c.cover_image_id AND deleted_at IS NULL) AS cover_thumb,
           (SELECT thumb_path FROM images i JOIN collection_images ci ON ci.image_id = i.id WHERE ci.collection_id = c.id AND i.deleted_at IS NULL ORDER BY ci.position LIMIT 1) AS first_thumb,
           (SELECT COUNT(*) FROM collection_images ci JOIN images i ON i.id = ci.image_id WHERE ci.collection_id = c.id AND i.deleted_at IS NULL) AS count
    FROM collections c
    ORDER BY c.position ASC, c.id ASC
  `).all() as Array<{
    id: number; name: string; slug: string; description: string | null;
    coverPortraitPath: string | null; coverPortraitZoom: number; coverPortraitOffsetX: number; coverPortraitOffsetY: number;
    coverLandscapePath: string | null; coverLandscapeZoom: number; coverLandscapeOffsetX: number; coverLandscapeOffsetY: number;
    cover_thumb: string | null; first_thumb: string | null; count: number;
  }>;
}

export function getCollection(id: number) {
  const c = db.select().from(collections).where(eq(collections.id, id)).get();
  if (!c) return null;
  return c;
}

export function getCollectionBySlug(slug: string) {
  return rawDb.prepare(`SELECT * FROM collections WHERE slug = ?`).get(slug) as
    | { id: number; name: string; slug: string; description: string | null; cover_image_id: number | null; created_at: number }
    | undefined ?? null;
}

export interface ActressPortrait {
  portraitPath: string | null;
  portraitZoom: number;
  portraitOffsetX: number;
  portraitOffsetY: number;
}

export interface ActressPortraitSlot {
  path: string | null;
  zoom: number;
  offsetX: number;
  offsetY: number;
}

export type PortraitSlotKey = "1" | "2" | "3" | "4" | "h";

export interface ActressAllPortraits {
  p1: ActressPortraitSlot;
  p2: ActressPortraitSlot;
  p3: ActressPortraitSlot;
  p4: ActressPortraitSlot;
  ph: ActressPortraitSlot;
}

export interface ActressCategory {
  id: number;
  name: string;
  slug: string;
  color: string | null;
  icon: string | null;
  priority: number;
  builtin: number;
}

export type ActressListItem = {
  id: number;
  name: string;
  slug: string;
  count: number;
  altNames: string | null;
  categories: ActressCategory[];
  portraits: ActressAllPortraits;
} & ActressPortrait;

export function listActressCategories(): ActressCategory[] {
  return rawDb.prepare(`
    SELECT id, name, slug, color, icon, priority, builtin
    FROM actress_categories
    ORDER BY priority DESC, name
  `).all() as ActressCategory[];
}

export type CoStar = {
  id: number;
  name: string;
  slug: string;
  shared: number;
  portraitPath: string | null;
  portraitZoom: number;
  portraitOffsetX: number;
  portraitOffsetY: number;
};

export function listCoStars(actressId: number, limit = 24): CoStar[] {
  return rawDb.prepare(`
    SELECT a.id, a.name, a.slug,
           a.portrait_path AS portraitPath,
           a.portrait_zoom AS portraitZoom,
           a.portrait_offset_x AS portraitOffsetX,
           a.portrait_offset_y AS portraitOffsetY,
           COUNT(DISTINCT ia2.image_id) AS shared
    FROM image_actresses ia
    JOIN image_actresses ia2
      ON ia2.image_id = ia.image_id AND ia2.actress_id != ia.actress_id
    JOIN actresses a ON a.id = ia2.actress_id
    JOIN images i ON i.id = ia.image_id
    WHERE ia.actress_id = ?
      AND i.deleted_at IS NULL
      AND i.parent_image_id IS NULL
    GROUP BY a.id
    ORDER BY shared DESC, a.name ASC
    LIMIT ?
  `).all(actressId, limit) as CoStar[];
}

export function listAllActresses(): ActressListItem[] {
  const raw = rawDb.prepare(`
    SELECT a.id, a.name, a.slug,
           a.alt_names AS altNames,
           a.portrait_path AS portraitPath,
           a.portrait_zoom AS portraitZoom,
           a.portrait_offset_x AS portraitOffsetX,
           a.portrait_offset_y AS portraitOffsetY,
           a.portrait2_path AS p2Path, a.portrait2_zoom AS p2Zoom, a.portrait2_offset_x AS p2Ox, a.portrait2_offset_y AS p2Oy,
           a.portrait3_path AS p3Path, a.portrait3_zoom AS p3Zoom, a.portrait3_offset_x AS p3Ox, a.portrait3_offset_y AS p3Oy,
           a.portrait4_path AS p4Path, a.portrait4_zoom AS p4Zoom, a.portrait4_offset_x AS p4Ox, a.portrait4_offset_y AS p4Oy,
           a.portraith_path AS phPath, a.portraith_zoom AS phZoom, a.portraith_offset_x AS phOx, a.portraith_offset_y AS phOy,
           COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
    FROM actresses a
    LEFT JOIN image_actresses ia ON ia.actress_id = a.id
    LEFT JOIN images i ON i.id = ia.image_id
    GROUP BY a.id ORDER BY count DESC, a.name
  `).all() as Array<{
    id: number; name: string; slug: string; count: number; altNames: string | null;
    portraitPath: string | null; portraitZoom: number; portraitOffsetX: number; portraitOffsetY: number;
    p2Path: string | null; p2Zoom: number; p2Ox: number; p2Oy: number;
    p3Path: string | null; p3Zoom: number; p3Ox: number; p3Oy: number;
    p4Path: string | null; p4Zoom: number; p4Ox: number; p4Oy: number;
    phPath: string | null; phZoom: number; phOx: number; phOy: number;
  }>;
  const rows: ActressListItem[] = raw.map((r) => ({
    id: r.id, name: r.name, slug: r.slug, count: r.count, altNames: r.altNames,
    portraitPath: r.portraitPath, portraitZoom: r.portraitZoom, portraitOffsetX: r.portraitOffsetX, portraitOffsetY: r.portraitOffsetY,
    categories: [],
    portraits: {
      p1: { path: r.portraitPath, zoom: r.portraitZoom, offsetX: r.portraitOffsetX, offsetY: r.portraitOffsetY },
      p2: { path: r.p2Path, zoom: r.p2Zoom, offsetX: r.p2Ox, offsetY: r.p2Oy },
      p3: { path: r.p3Path, zoom: r.p3Zoom, offsetX: r.p3Ox, offsetY: r.p3Oy },
      p4: { path: r.p4Path, zoom: r.p4Zoom, offsetX: r.p4Ox, offsetY: r.p4Oy },
      ph: { path: r.phPath, zoom: r.phZoom, offsetX: r.phOx, offsetY: r.phOy },
    },
  }));

  if (rows.length === 0) return rows;

  const ids = rows.map((r) => r.id);
  const placeholders = ids.map(() => "?").join(",");
  const catRows = rawDb.prepare(`
    SELECT m.actress_id AS actressId,
           c.id, c.name, c.slug, c.color, c.icon, c.priority, c.builtin
    FROM actress_categories_map m
    JOIN actress_categories c ON c.id = m.category_id
    WHERE m.actress_id IN (${placeholders})
    ORDER BY c.priority DESC, c.name
  `).all(...ids) as Array<{ actressId: number } & ActressCategory>;

  const byActress = new Map<number, ActressCategory[]>();
  for (const row of catRows) {
    const { actressId, ...cat } = row;
    if (!byActress.has(actressId)) byActress.set(actressId, []);
    byActress.get(actressId)!.push(cat);
  }
  return rows.map((r) => ({ ...r, categories: byActress.get(r.id) ?? [] }));
}

export function getActressCategoryIds(actressId: number): number[] {
  const rows = rawDb.prepare(`SELECT category_id AS id FROM actress_categories_map WHERE actress_id = ?`).all(actressId) as Array<{ id: number }>;
  return rows.map((r) => r.id);
}

export function getActressBySlug(slug: string) {
  const row = rawDb.prepare(`
    SELECT id, name, slug, alt_names AS altNames, notes,
           portrait_path AS portraitPath,
           portrait_zoom AS portraitZoom,
           portrait_offset_x AS portraitOffsetX,
           portrait_offset_y AS portraitOffsetY,
           portrait2_path AS p2Path, portrait2_zoom AS p2Zoom, portrait2_offset_x AS p2Ox, portrait2_offset_y AS p2Oy,
           portrait3_path AS p3Path, portrait3_zoom AS p3Zoom, portrait3_offset_x AS p3Ox, portrait3_offset_y AS p3Oy,
           portrait4_path AS p4Path, portrait4_zoom AS p4Zoom, portrait4_offset_x AS p4Ox, portrait4_offset_y AS p4Oy,
           portraith_path AS phPath, portraith_zoom AS phZoom, portraith_offset_x AS phOx, portraith_offset_y AS phOy,
           born_on AS bornOn, height_cm AS heightCm, weight_kg AS weightKg, cup_size AS cupSize
    FROM actresses WHERE slug = ?
  `).get(slug) as
    | ({
        id: number; name: string; slug: string; altNames: string | null; notes: string | null;
        p2Path: string | null; p2Zoom: number; p2Ox: number; p2Oy: number;
        p3Path: string | null; p3Zoom: number; p3Ox: number; p3Oy: number;
        p4Path: string | null; p4Zoom: number; p4Ox: number; p4Oy: number;
        phPath: string | null; phZoom: number; phOx: number; phOy: number;
        bornOn: string | null; heightCm: number | null; weightKg: number | null; cupSize: string | null;
      } & ActressPortrait)
    | undefined;
  if (!row) return null;
  const portraits: ActressAllPortraits = {
    p1: { path: row.portraitPath, zoom: row.portraitZoom, offsetX: row.portraitOffsetX, offsetY: row.portraitOffsetY },
    p2: { path: row.p2Path, zoom: row.p2Zoom, offsetX: row.p2Ox, offsetY: row.p2Oy },
    p3: { path: row.p3Path, zoom: row.p3Zoom, offsetX: row.p3Ox, offsetY: row.p3Oy },
    p4: { path: row.p4Path, zoom: row.p4Zoom, offsetX: row.p4Ox, offsetY: row.p4Oy },
    ph: { path: row.phPath, zoom: row.phZoom, offsetX: row.phOx, offsetY: row.phOy },
  };
  const categories = rawDb.prepare(`
    SELECT c.id, c.name, c.slug, c.color, c.icon, c.priority, c.builtin
    FROM actress_categories_map m
    JOIN actress_categories c ON c.id = m.category_id
    WHERE m.actress_id = ?
    ORDER BY c.priority DESC, c.name
  `).all(row.id) as ActressCategory[];
  return { ...row, categories, portraits };
}

export function listAllStudios() {
  return rawDb.prepare(`
    SELECT s.id, s.name, s.slug,
           COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
    FROM studios s
    LEFT JOIN images i ON i.studio_id = s.id
    GROUP BY s.id ORDER BY count DESC, s.name
  `).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}

export function getStudioBySlug(slug: string) {
  return rawDb.prepare(`SELECT id, name, slug, notes FROM studios WHERE slug = ?`).get(slug) as
    | { id: number; name: string; slug: string; notes: string | null }
    | undefined ?? null;
}

export function listAllLabels() {
  return rawDb.prepare(`
    SELECT l.id, l.name, l.slug,
           COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
    FROM labels l
    LEFT JOIN images i ON i.label_id = l.id
    GROUP BY l.id ORDER BY count DESC, l.name
  `).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}

export function getLabelBySlug(slug: string) {
  return rawDb.prepare(`SELECT id, name, slug, notes FROM labels WHERE slug = ?`).get(slug) as
    | { id: number; name: string; slug: string; notes: string | null }
    | undefined ?? null;
}

export function listAllSeries() {
  return rawDb.prepare(`
    SELECT s.id, s.name, s.slug,
           COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
    FROM series s
    LEFT JOIN images i ON i.series_id = s.id
    GROUP BY s.id ORDER BY count DESC, s.name
  `).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}

export function getSeriesBySlug(slug: string) {
  return rawDb.prepare(`SELECT id, name, slug, notes FROM series WHERE slug = ?`).get(slug) as
    | { id: number; name: string; slug: string; notes: string | null }
    | undefined ?? null;
}

export function listAllGenres() {
  return rawDb.prepare(`
    SELECT g.id, g.name, g.slug,
           COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
    FROM genres g
    LEFT JOIN image_genres ig ON ig.genre_id = g.id
    LEFT JOIN images i ON i.id = ig.image_id
    GROUP BY g.id ORDER BY count DESC, g.name
  `).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}

export type NeighborImage = { id: number; code: string | null };

export function getNeighborImageIds(currentId: number): { prev: NeighborImage | null; next: NeighborImage | null } {
  const cur = rawDb.prepare(`SELECT created_at FROM images WHERE id = ?`).get(currentId) as { created_at: number } | undefined;
  if (!cur) return { prev: null, next: null };
  const next = rawDb.prepare(`
    SELECT id, code FROM images
    WHERE (created_at, id) > (?, ?) AND deleted_at IS NULL AND parent_image_id IS NULL
    ORDER BY created_at ASC, id ASC
    LIMIT 1
  `).get(cur.created_at, currentId) as NeighborImage | undefined;
  const prev = rawDb.prepare(`
    SELECT id, code FROM images
    WHERE (created_at, id) < (?, ?) AND deleted_at IS NULL AND parent_image_id IS NULL
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  `).get(cur.created_at, currentId) as NeighborImage | undefined;
  return { prev: prev ?? null, next: next ?? null };
}

export function getRandomImage(excludeId?: number): NeighborImage | null {
  const row = rawDb.prepare(`
    SELECT id, code FROM images
    WHERE id != COALESCE(?, -1) AND deleted_at IS NULL AND parent_image_id IS NULL
    ORDER BY RANDOM()
    LIMIT 1
  `).get(excludeId ?? null) as NeighborImage | undefined;
  return row ?? null;
}

export function getRandomImageId(excludeId?: number): number | null {
  return getRandomImage(excludeId)?.id ?? null;
}

export function listAttachedImages(parentId: number): Array<{ id: number; thumbPath: string; width: number; height: number; filename: string; sha256: string }> {
  return rawDb.prepare(`
    SELECT id, thumb_path AS thumbPath, width, height, filename, sha256
    FROM images
    WHERE parent_image_id = ? AND deleted_at IS NULL
    ORDER BY created_at ASC, id ASC
  `).all(parentId) as Array<{ id: number; thumbPath: string; width: number; height: number; filename: string; sha256: string }>;
}

export function getImageIdByCode(code: string): number | null {
  const row = rawDb.prepare(`
    SELECT id FROM images
    WHERE code = ? AND deleted_at IS NULL AND parent_image_id IS NULL
    ORDER BY id ASC
    LIMIT 1
  `).get(code) as { id: number } | undefined;
  return row?.id ?? null;
}

export interface ContextCoverInfo {
  id: number;
  code: string | null;
  title: string | null;
  isVip: boolean;
  isFavorite: boolean;
  isOwned: boolean;
  isWatched: boolean;
  rating: number | null;
  actresses: string[];
}

export interface ContextTagOption {
  id: number;
  name: string;
  color: string | null;
  /** How many of the supplied imageIds already have this tag. */
  count: number;
}

export interface ContextCollectionOption {
  id: number;
  name: string;
  count: number;
}

export interface ContextData {
  covers: ContextCoverInfo[];
  tags: ContextTagOption[];
  collections: ContextCollectionOption[];
  recentTags: Array<{ id: number; name: string; color: string | null }>;
  recentCollections: Array<{ id: number; name: string }>;
  selectedCount: number;
}

/**
 * Bundle every piece of context the right-click menu needs in a single
 * round-trip. Per-tag/per-collection `count` lets the UI render
 * tri-state (all / partial / none) for bulk operations.
 */
export function getImageContextData(imageIds: number[]): ContextData {
  const allTags = listAllTags();
  const allCollections = listAllCollections();
  const recentTags = rawDb.prepare(`
    SELECT id, name, color FROM tags WHERE last_used_at > 0 ORDER BY last_used_at DESC LIMIT 6
  `).all() as Array<{ id: number; name: string; color: string | null }>;
  const recentCollections = rawDb.prepare(`
    SELECT id, name FROM collections WHERE last_used_at > 0 ORDER BY last_used_at DESC LIMIT 6
  `).all() as Array<{ id: number; name: string }>;

  if (imageIds.length === 0) {
    return {
      covers: [],
      tags: allTags.map(({ id, name, color }) => ({ id, name, color, count: 0 })),
      collections: allCollections.map((c) => ({ id: c.id, name: c.name, count: 0 })),
      recentTags,
      recentCollections,
      selectedCount: 0,
    };
  }

  const placeholders = imageIds.map(() => "?").join(",");

  // Per-cover info for the header (single) or batch summary (bulk).
  const coverRows = rawDb.prepare(`
    SELECT id, code, title, is_vip AS isVip, is_favorite AS isFavorite,
           is_owned AS isOwned, watched AS isWatched, rating
    FROM images WHERE id IN (${placeholders})
  `).all(...imageIds) as Array<{
    id: number; code: string | null; title: string | null;
    isVip: number; isFavorite: number; isOwned: number; isWatched: number;
    rating: number | null;
  }>;
  // Actresses per cover, grouped.
  const actressRows = rawDb.prepare(`
    SELECT ia.image_id, a.name FROM image_actresses ia
    JOIN actresses a ON a.id = ia.actress_id
    WHERE ia.image_id IN (${placeholders})
    ORDER BY a.name
  `).all(...imageIds) as Array<{ image_id: number; name: string }>;
  const actressesByImage = new Map<number, string[]>();
  for (const r of actressRows) {
    const arr = actressesByImage.get(r.image_id) ?? [];
    arr.push(r.name);
    actressesByImage.set(r.image_id, arr);
  }
  const covers: ContextCoverInfo[] = coverRows.map((r) => ({
    id: r.id,
    code: r.code,
    title: r.title,
    isVip: !!r.isVip,
    isFavorite: !!r.isFavorite,
    isOwned: !!r.isOwned,
    isWatched: !!r.isWatched,
    rating: r.rating,
    actresses: actressesByImage.get(r.id) ?? [],
  }));

  // Per-tag and per-collection coverage counts.
  const tagCountRows = rawDb.prepare(`
    SELECT tag_id, COUNT(DISTINCT image_id) AS n FROM image_tags
    WHERE image_id IN (${placeholders})
    GROUP BY tag_id
  `).all(...imageIds) as Array<{ tag_id: number; n: number }>;
  const tagCountById = new Map(tagCountRows.map((r) => [r.tag_id, r.n] as const));
  const collCountRows = rawDb.prepare(`
    SELECT collection_id, COUNT(DISTINCT image_id) AS n FROM collection_images
    WHERE image_id IN (${placeholders})
    GROUP BY collection_id
  `).all(...imageIds) as Array<{ collection_id: number; n: number }>;
  const collCountById = new Map(collCountRows.map((r) => [r.collection_id, r.n] as const));

  return {
    covers,
    tags: allTags.map(({ id, name, color }) => ({ id, name, color, count: tagCountById.get(id) ?? 0 })),
    collections: allCollections.map((c) => ({ id: c.id, name: c.name, count: collCountById.get(c.id) ?? 0 })),
    recentTags,
    recentCollections,
    selectedCount: imageIds.length,
  };
}

export interface LibraryStats {
  // Cover counts
  images: number;
  attached: number;
  trashed: number;
  // Entity counts
  actresses: number;
  studios: number;
  series: number;
  labels: number;
  genres: number;
  // Tagging
  tags: number;
  tagCategories: number;
  collections: number;
  // State
  watched: number;
  vip: number;
  favorite: number;
  owned: number;
  rated: number;
  // Disk
  totalBytes: number;
  earliestImportedAt: number | null;
  latestImportedAt: number | null;
}

export function libraryStats(): LibraryStats {
  return rawDb.prepare(`
    SELECT
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL) AS images,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NOT NULL) AS attached,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NOT NULL) AS trashed,
      (SELECT COUNT(*) FROM actresses) AS actresses,
      (SELECT COUNT(*) FROM studios) AS studios,
      (SELECT COUNT(*) FROM series) AS series,
      (SELECT COUNT(*) FROM labels) AS labels,
      (SELECT COUNT(*) FROM genres) AS genres,
      (SELECT COUNT(*) FROM tags) AS tags,
      (SELECT COUNT(*) FROM tag_categories) AS tagCategories,
      (SELECT COUNT(*) FROM collections) AS collections,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND watched = 1) AS watched,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND is_vip = 1) AS vip,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND is_favorite = 1) AS favorite,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND is_owned = 1) AS owned,
      (SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND rating IS NOT NULL) AS rated,
      (SELECT COALESCE(SUM(bytes), 0) FROM images WHERE deleted_at IS NULL) AS totalBytes,
      (SELECT MIN(imported_at) FROM images WHERE deleted_at IS NULL) AS earliestImportedAt,
      (SELECT MAX(imported_at) FROM images WHERE deleted_at IS NULL) AS latestImportedAt
  `).get() as LibraryStats;
}

export function listTrashedImages(): Array<CardImage & { deletedAt: number }> {
  const rows = rawDb.prepare(`
    SELECT ${COVER_COLS}, i.deleted_at
    FROM images i
    ${COVER_BASE_JOIN}
    WHERE i.deleted_at IS NOT NULL AND i.parent_image_id IS NULL
    ORDER BY i.deleted_at DESC
  `).all() as Array<CoverRow & { deleted_at: number }>;
  const cards = attachActresses(rows.map(rowToCard));
  return cards.map((c, i) => ({ ...c, deletedAt: rows[i].deleted_at }));
}

export function getTrashedImagesByIds(ids: number[]) {
  if (ids.length === 0) return [];
  return rawDb.prepare(`
    SELECT id, rel_path, thumb_path, deleted_at
    FROM images
    WHERE deleted_at IS NOT NULL AND id IN (${ids.map(() => "?").join(",")})
  `).all(...ids) as Array<{ id: number; rel_path: string; thumb_path: string; deleted_at: number }>;
}

export { db, eq, inArray, and, sql, desc };

function escapeLike(s: string): string {
  return s.replace(/[\\%_]/g, (c) => `\\${c}`);
}