import { createServerFn } from "@tanstack/react-start";
import { z } from "zod";
import { getDb } from "@/lib/db";
import { importGamesFromFeeds } from "@/lib/feeds/import";
import { requireAuth } from "@/lib/auth/middleware";
import { getCurrentSite, resolveSiteSlug } from "@/lib/sites";
import { slugify } from "./slug";
import { sanitizeGameText, normalizeInstructions } from "@/lib/translate";

const gameSelect = `
  SELECT
    sg.id,
    sg.slug,
    sg.title,
    sg.description,
    sg.instructions,
    sg.is_featured,
    sg.meta_title,
    g.embed_url,
    COALESCE(g.thumb_local, g.thumb_url) AS thumb_url,
    g.thumb_local,
    g.width,
    g.height,
    g.category_id,
    g.source,
    g.source_id,
    g.plays,
    g.sort_order,
    g.created_at,
    g.updated_at,
    c.slug AS category_slug,
    c.name AS category_name
  FROM site_games sg
  JOIN games g ON g.id = sg.game_id
  LEFT JOIN categories c ON c.id = g.category_id
`;

function mapSiteGameRow(row: Record<string, unknown>) {
  return {
    id: row.id as string,
    slug: row.slug as string,
    title: sanitizeGameText(row.title as string) ?? (row.title as string),
    description: sanitizeGameText(row.description as string | null),
    instructions: normalizeInstructions(row.instructions as string | null),
    thumb_url: row.thumb_url as string,
    embed_url: row.embed_url as string,
    width: row.width as number,
    height: row.height as number,
    category_id: row.category_id as string | null,
    source: row.source as string | null,
    source_id: row.source_id as string | null,
    is_featured: Boolean(row.is_featured),
    plays: row.plays as number,
    sort_order: row.sort_order as number,
    created_at: row.created_at as string,
    updated_at: row.updated_at as string,
    meta_title: row.meta_title as string | null,
    categories:
      row.category_slug && row.category_name
        ? { slug: row.category_slug as string, name: row.category_name as string }
        : null,
  };
}

export const getSiteConfig = createServerFn({ method: "GET" }).handler(async () => {
  const slug = resolveSiteSlug();
  const site = getCurrentSite();
  const db = getDb();
  const gameCount = db
    .prepare("SELECT COUNT(*) AS c FROM site_games WHERE site_id = ?")
    .get(site.id) as { c: number };
  return { ...site, gameCount: gameCount.c, activeSlug: slug };
});

export const listCategories = createServerFn({ method: "GET" }).handler(async () => {
  const db = getDb();
  return db
    .prepare("SELECT id, slug, name, sort_order FROM categories ORDER BY sort_order ASC")
    .all() as { id: string; slug: string; name: string; sort_order: number }[];
});

export const listGames = createServerFn({ method: "GET" })
  .inputValidator(
    (
      d:
        | { limit?: number; featured?: boolean; offset?: number; category?: string; q?: string; sort?: string }
        | undefined,
    ) =>
      z
        .object({
          limit: z.number().int().min(1).max(120).optional(),
          featured: z.boolean().optional(),
          offset: z.number().int().min(0).optional(),
          category: z.string().max(40).optional(),
          q: z.string().max(80).optional(),
          sort: z.enum(["new", "popular"]).optional(),
        })
        .parse(d ?? {}),
  )
  .handler(async ({ data }) => {
    const db = getDb();
    const site = getCurrentSite();
    const limit = data.limit ?? 36;
    const offset = data.offset ?? 0;
    const params: (string | number)[] = [site.id];
    let where = "sg.site_id = ?";

    if (data.category) {
      where += " AND c.slug = ?";
      params.push(data.category);
    }
    if (data.q?.trim()) {
      where += " AND (sg.title LIKE ? OR sg.description LIKE ?)";
      const like = `%${data.q.trim()}%`;
      params.push(like, like);
    }

    if (data.featured) {
      where += " AND sg.is_featured = 1";
    }

    const order =
      data.sort === "popular"
        ? "g.plays DESC, g.created_at DESC"
        : "sg.is_featured DESC, g.created_at DESC";

    const totalRow = db
      .prepare(
        `SELECT COUNT(*) AS c FROM site_games sg JOIN games g ON g.id = sg.game_id LEFT JOIN categories c ON c.id = g.category_id WHERE ${where}`,
      )
      .get(...params) as { c: number };

    params.push(limit, offset);
    const rows = db
      .prepare(`${gameSelect} WHERE ${where} ORDER BY ${order} LIMIT ? OFFSET ?`)
      .all(...params) as Record<string, unknown>[];

    return { items: rows.map(mapSiteGameRow), total: totalRow.c, hasMore: offset + rows.length < totalRow.c };
  });

export const getGamesBySlugs = createServerFn({ method: "GET" })
  .inputValidator((d: { slugs: string[] }) => z.object({ slugs: z.array(z.string().max(120)).max(100) }).parse(d))
  .handler(async ({ data }) => {
    if (!data.slugs.length) return [];
    const db = getDb();
    const site = getCurrentSite();
    const ph = data.slugs.map(() => "?").join(",");
    const rows = db
      .prepare(`${gameSelect} WHERE sg.site_id = ? AND sg.slug IN (${ph})`)
      .all(site.id, ...data.slugs) as Record<string, unknown>[];
    const map = new Map(rows.map((r) => [(r.slug as string), mapSiteGameRow(r)]));
    return data.slugs.map((s) => map.get(s)).filter(Boolean);
  });

const homeGameSelect = `
  SELECT
    sg.id,
    sg.slug,
    sg.title,
    COALESCE(g.thumb_local, g.thumb_url) AS thumb_url,
    c.slug AS category_slug,
    c.name AS category_name
  FROM site_games sg
  JOIN games g ON g.id = sg.game_id
  LEFT JOIN categories c ON c.id = g.category_id
`;

function mapHomeGameRow(row: Record<string, unknown>) {
  return {
    id: row.id as string,
    slug: row.slug as string,
    title: sanitizeGameText(row.title as string) ?? (row.title as string),
    thumb_url: row.thumb_url as string,
    categories:
      row.category_slug && row.category_name
        ? { slug: row.category_slug as string, name: row.category_name as string }
        : null,
  };
}

function fetchGamesForSite(siteId: string, opts: { category?: string; categories?: string[]; limit: number }) {
  const db = getDb();
  const params: (string | number)[] = [siteId];
  let where = "sg.site_id = ?";
  if (opts.category) {
    where += " AND c.slug = ?";
    params.push(opts.category);
  } else if (opts.categories?.length) {
    where += ` AND c.slug IN (${opts.categories.map(() => "?").join(",")})`;
    params.push(...opts.categories);
  }
  params.push(opts.limit);
  return (
    db
      .prepare(`${homeGameSelect} WHERE ${where} ORDER BY sg.is_featured DESC, g.created_at DESC LIMIT ?`)
      .all(...params) as Record<string, unknown>[]
  ).map(mapHomeGameRow);
}

export const getHomePageData = createServerFn({ method: "GET" }).handler(async () => {
  const site = getCurrentSite();
  const db = getDb();
  const total = (db.prepare("SELECT COUNT(*) AS c FROM site_games WHERE site_id = ?").get(site.id) as { c: number }).c;
  const sideSlugs = ["arcade", "puzzle", "strategy", "adventure", "io", "sports"];
  const catNames = db.prepare("SELECT slug, name FROM categories").all() as { slug: string; name: string }[];
  const nameMap = new Map(catNames.map((c) => [c.slug, c.name]));

  return {
    total,
    girls: fetchGamesForSite(site.id, { category: "girls", limit: 32 }),
    boys: fetchGamesForSite(site.id, { categories: ["action", "shooter", "sports", "racing"], limit: 28 }),
    sideCategories: sideSlugs.map((slug) => ({
      slug,
      name: nameMap.get(slug) ?? slug,
      games: fetchGamesForSite(site.id, { category: slug, limit: 48 }),
    })),
    catalog: fetchGamesForSite(site.id, { limit: 48 }),
    best: fetchGamesForSite(site.id, { limit: 50 }),
  };
});

export const mirrorAllThumbs = createServerFn({ method: "POST" })
  .middleware([requireAuth])
  .inputValidator((d: { limit?: number; offset?: number } | undefined) =>
    z.object({ limit: z.number().int().min(1).max(50_000).optional(), offset: z.number().int().min(0).optional() }).parse(d ?? {}))
  .handler(async ({ data, context }) => {
    await assertAdmin(context.userId);
    const { mirrorThumbsBatch } = await import("@/lib/media/thumbs");
    return mirrorThumbsBatch(data.limit ?? 50_000, data.offset ?? 0);
  });

export const getGameBySlug = createServerFn({ method: "GET" })
  .inputValidator((d: { slug: string }) => z.object({ slug: z.string().min(1).max(120) }).parse(d))
  .handler(async ({ data }) => {
    const db = getDb();
    const site = getCurrentSite();
    const gameRow = db
      .prepare(`${gameSelect} WHERE sg.site_id = ? AND sg.slug = ?`)
      .get(site.id, data.slug) as Record<string, unknown> | undefined;
    if (!gameRow) return null;

    const game = mapSiteGameRow(gameRow);
    const relatedRows = game.category_id
      ? (db
          .prepare(
            `${gameSelect} WHERE sg.site_id = ? AND sg.slug != ? AND g.category_id = ? ORDER BY sg.is_featured DESC, g.created_at DESC LIMIT 8`,
          )
          .all(site.id, data.slug, game.category_id) as Record<string, unknown>[])
      : (db
          .prepare(
            `${gameSelect} WHERE sg.site_id = ? AND sg.slug != ? ORDER BY g.created_at DESC LIMIT 8`,
          )
          .all(site.id, data.slug) as Record<string, unknown>[]);

    return { game, related: relatedRows.map(mapSiteGameRow) };
  });

export const getCategoryBySlug = createServerFn({ method: "GET" })
  .inputValidator((d: { slug: string; limit?: number; offset?: number }) =>
    z
      .object({
        slug: z.string().min(1).max(120),
        limit: z.number().int().min(1).max(200).optional(),
        offset: z.number().int().min(0).optional(),
      })
      .parse(d),
  )
  .handler(async ({ data }) => {
    const db = getDb();
    const site = getCurrentSite();
    const limit = data.limit ?? 80;
    const offset = data.offset ?? 0;
    const category = db
      .prepare("SELECT * FROM categories WHERE slug = ?")
      .get(data.slug) as Record<string, unknown> | undefined;
    if (!category) return null;

    const totalRow = db
      .prepare(
        `SELECT COUNT(*) AS c FROM site_games sg JOIN games g ON g.id = sg.game_id WHERE sg.site_id = ? AND g.category_id = ?`,
      )
      .get(site.id, category.id) as { c: number };

    const games = (
      db
        .prepare(
          `${gameSelect} WHERE sg.site_id = ? AND g.category_id = ? ORDER BY sg.is_featured DESC, g.created_at DESC LIMIT ? OFFSET ?`,
        )
        .all(site.id, category.id, limit, offset) as Record<string, unknown>[]
    ).map(mapSiteGameRow);

    return {
      category: { id: category.id, slug: category.slug, name: category.name },
      games,
      total: totalRow.c,
      hasMore: offset + games.length < totalRow.c,
    };
  });

export const syncGamesFromFeed = createServerFn({ method: "POST" })
  .middleware([requireAuth])
  .inputValidator((d: { sources?: string[] } | undefined) =>
    z.object({ sources: z.array(z.string()).optional() }).parse(d ?? {}))
  .handler(async ({ data, context }) => {
    await assertAdmin(context.userId);
    const result = await importGamesFromFeeds({
      sources: data.sources,
      translateDuringImport: true,
      mirrorThumbs: true,
    });
    return {
      ok: true,
      inserted: result.inserted,
      updated: result.updated,
      skipped: result.skipped,
      total: result.total,
      totalInDb: result.totalInDb,
      targetTotal: result.targetTotal,
      bySource: result.bySource,
    };
  });

async function assertAdmin(userId: string) {
  const db = getDb();
  const role = db
    .prepare("SELECT id FROM user_roles WHERE user_id = ? AND role = 'admin'")
    .get(userId);
  if (!role) throw new Error("Доступ только для администраторов");
}

export { slugify };