Evandro Fonseca Junior
2b790d8eb4
Added vocation state and included it in query string builder, updated onFilter to accept a vocation parameter, improved grid layout with grid flow classes, normalized category names for consistent item icons, and refined stats mapping to handle type safety.
235 lines
8.1 KiB
TypeScript
235 lines
8.1 KiB
TypeScript
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
|
|
}
|
|
}
|
|
}
|
|
|
|
|