import path from "node:path";
import { enToPtCategoryMap } from "./item-images";

export interface SearchParams {
  q?: string;
  categories?: string[];
  rarities?: string[];
  sort?:
    | "name"
    | "level-asc"
    | "level-desc"
    | "weight-asc"
    | "weight-desc"
    | "category";
  vocation?: string;
  page?: number;
  pageSize?: number;
}

export interface SearchResult<T> {
  items: T[];
  total: number;
}

let schemaLogged = false;

function getDatabase() {
  // Carrega better-sqlite3 apenas em runtime para não quebrar o build se não instalado
  let Database: any;
  try {
    // eslint-disable-next-line @typescript-eslint/no-var-requires
    Database = require("better-sqlite3");
  } catch (err) {
    // Retorna null para permitir fallback silencioso (API responde vazio ao invés de 500)
    return null as any;
  }

  const dbPath = path.join(process.cwd(), "data", "tibiawiki.db");
  const db = new Database(dbPath, { readonly: true, fileMustExist: true });
  return db;
}

// Tenta deduzir ordenação segura a partir do parâmetro de sort
function getOrderBy(sort: SearchParams["sort"], vocation?: string): { sql: string; bindings: any[] } {
  // Ordenação sem prioridade de vocação (filtro de voc estrito cuida apenas do WHERE)
  switch (sort) {
    case "level-asc":
      return { sql: "level ASC, name ASC", bindings: [] };
    case "level-desc":
      return { sql: "level DESC, name ASC", bindings: [] };
    case "weight-asc":
      return { sql: "weight ASC, name ASC", bindings: [] };
    case "weight-desc":
      return { sql: "weight DESC, name ASC", bindings: [] };
    case "category":
      return { sql: "category ASC, name ASC", bindings: [] };
    case "name":
    default:
      return { sql: "name ASC", bindings: [] };
  }
}

// Nota: O schema exato do tibiawiki.db pode variar. Partimos do pressuposto mínimo:
// Tabela 'items' com colunas: id, name, category, image, rarity, npc_price
// Ajustes poderão ser feitos após validação do schema real.
export function searchItems<T = any>(params: SearchParams): SearchResult<T> {
  const {
    q = "",
    categories = [],
    rarities = [],
    sort = "name",
    vocation,
    page = 1,
    pageSize = 24,
  } = params;

  const safePage = Math.max(1, page);
  const safePageSize = Math.min(Math.max(1, pageSize), 50);
  const offset = (safePage - 1) * safePageSize;

  const db = getDatabase();
  if (!db) {
    // Fallback quando better-sqlite3 não está disponível no ambiente
    return { items: [], total: 0 } as SearchResult<T>;
  }
  try {
    // Log básico do schema na primeira chamada
    if (!schemaLogged) {
      try {
        const tables = db
          .prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
          .all() as { name: string }[];
        console.info("[items-db] Tabelas:", tables.map((t) => t.name));
        for (const t of tables.slice(0, 5)) {
          try {
            const cols = db.prepare(`PRAGMA table_info(${t.name})`).all() as any[];
            console.info(`[items-db] Colunas ${t.name}:`, cols.map((c) => `${c.name}:${c.type}`));
            const sample = db.prepare(`SELECT * FROM ${t.name} LIMIT 3`).all();
            console.info(`[items-db] Amostra ${t.name}:`, sample);
          } catch (inner) {
            console.warn(`[items-db] Falha ao inspecionar ${t.name}:`, (inner as any)?.message);
          }
        }
      } catch (intErr) {
        console.warn("[items-db] Falha ao ler schema:", (intErr as any)?.message);
      } finally {
        schemaLogged = true;
      }
    }

    const where: string[] = [];
    const bindings: any[] = [];

    if (q) {
      where.push("LOWER(name) LIKE ?");
      bindings.push(`%${q.toLowerCase()}%`);
    }
    if (categories.length > 0) {
      const placeholders = categories.map(() => "?").join(",");
      where.push(`LOWER(category) IN (${placeholders})`);
      bindings.push(...categories.map((c) => c.toLowerCase()));
    }
    if (rarities.length > 0) {
      const placeholders = rarities.map(() => "?").join(",");
      where.push(`LOWER(rarity) IN (${placeholders})`);
      bindings.push(...rarities.map((r) => r.toLowerCase()));
    }

    const { sql: orderBy, bindings: orderBindings } = getOrderBy(sort, vocation);

    // Seleciona de múltiplas tabelas (PT) unificando shape padronizado
    const allTables = db
      .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name <> 'sqlite_sequence'")
      .all() as { name: string }[];
    // Filtra por categorias solicitadas (EN → PT); caso vazio, usa todas
    const allowedPt = new Set(
      (categories.length > 0
        ? categories.map((en) => enToPtCategoryMap[en] || en)
        : allTables.map((t) => t.name))
    );
    const candidateTables = allTables
      .map((t) => t.name)
      .filter((name) => allowedPt.has(name));

    const selects: string[] = [];
    const unionBindings: any[] = [];
    for (const t of candidateTables) {
      try {
        const cols = db.prepare(`PRAGMA table_info(${t})`).all() as any[];
        const colNames = new Set(cols.map((c) => (c?.name || "").toLowerCase()));
        // Requer colunas mínimas
        if (!colNames.has("id") || !colNames.has("nome") || !colNames.has("image_url")) continue;
        // Se filtro de vocação foi solicitado e a tabela não possui coluna 'voc', ignora esta tabela
        if (vocation && !colNames.has("voc")) {
          continue;
        }

        const tableWhere: string[] = [];
        if (q && colNames.has("nome")) {
          tableWhere.push("LOWER(nome) LIKE ?");
          unionBindings.push(`%${q.toLowerCase()}%`);
        }
        if (vocation && colNames.has("voc")) {
          const sel = vocation.toLowerCase();
          // Filtro estrito: comparar normalizando espaços
          tableWhere.push("REPLACE(LOWER(voc), ' ', '') = REPLACE(?, ' ', '')");
          unionBindings.push(sel);
        }
        const whereSqlTbl = tableWhere.length ? `WHERE ${tableWhere.join(" AND ")}` : "";
        // category: derivamos do nome da tabela (pt)
        const levelCol = colNames.has("nivel") || colNames.has("lvl")
          ? `COALESCE(${colNames.has("nivel") ? "nivel" : "NULL"}, ${colNames.has("lvl") ? "lvl" : "NULL"})`
          : "NULL";
        const weightCol = colNames.has("peso") || colNames.has("peso_em_oz")
          ? `COALESCE(${colNames.has("peso") ? "peso" : "NULL"}, ${colNames.has("peso_em_oz") ? "peso_em_oz" : "NULL"})`
          : "NULL";
        const slotsCol = colNames.has("slots") ? "slots" : "NULL";
        const skillCol = colNames.has("skill_para_usar") ? "skill_para_usar" : "NULL";
        // Campos extras opcionais por tabela (ex.: aljavas)
        const extraCols = [
          "lvl",
          "voc",
          "volume",
          "bonus_de_skill",
          "protecao",
          "dropa_de",
        ] as const;
        const extraSelect = extraCols
          .map((c) => (colNames.has(c) ? `${c}` : `NULL AS ${c}`))
          .join(",\n            ");
        selects.push(
          `SELECT 
            id, 
            nome AS name, 
            '${t}' AS category, 
            image_url AS image,
            ${levelCol} AS level,
            ${weightCol} AS weight,
            ${slotsCol} AS slots,
            ${skillCol} AS skill${extraSelect ? ",\n            " + extraSelect : ""}
          FROM ${t} ${whereSqlTbl}`
        );
      } catch (e) {
        console.warn(`[items-db] Ignorando tabela ${t}:`, (e as any)?.message);
      }
    }

    if (selects.length === 0) {
      console.warn("[items-db] Nenhuma tabela compatível encontrada");
      return { items: [], total: 0 } as SearchResult<T>;
    }

    const unionSql = selects.join(" UNION ALL ");
    // Contagem total
    const totalStmt = db.prepare(`SELECT COUNT(1) as cnt FROM (${unionSql}) AS u`);
    const totalRow = totalStmt.get(...unionBindings) as { cnt: number };
    const total = totalRow?.cnt ?? 0;

    const selectStmt = db.prepare(
      `SELECT * FROM (${unionSql}) AS u ORDER BY ${orderBy} LIMIT ? OFFSET ?`
    );
    const items = selectStmt.all(...unionBindings, safePageSize, offset) as T[];

    return { items, total };
  } finally {
    try {
      db.close();
    } catch {
      // no-op
    }
  }
}