273 lines
7.8 KiB
TypeScript
273 lines
7.8 KiB
TypeScript
/**
|
|
* Queries para banco SQLite de itens usando Prisma
|
|
* Como o banco tem tabelas dinâmicas (uma por categoria),
|
|
* usamos $queryRaw para queries dinâmicas
|
|
*/
|
|
|
|
import { Prisma } from "@prisma/client";
|
|
// Nota: itemsPrisma não está disponível ainda - usando better-sqlite3 diretamente
|
|
// TODO: Implementar quando migrar SQLite para Prisma
|
|
// import { itemsPrisma } from "./items";
|
|
import { enToPtCategoryMap } from "../item-images";
|
|
|
|
// Stub temporário - não está sendo usado no momento
|
|
// eslint-disable-next-line @typescript-eslint/no-explicit-any
|
|
const itemsPrisma: any = null;
|
|
|
|
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;
|
|
|
|
// Tenta deduzir ordenação segura a partir do parâmetro de sort
|
|
function getOrderBy(sort: SearchParams["sort"]): string {
|
|
switch (sort) {
|
|
case "level-asc":
|
|
return "level ASC, name ASC";
|
|
case "level-desc":
|
|
return "level DESC, name ASC";
|
|
case "weight-asc":
|
|
return "weight ASC, name ASC";
|
|
case "weight-desc":
|
|
return "weight DESC, name ASC";
|
|
case "category":
|
|
return "category ASC, name ASC";
|
|
default:
|
|
return "name ASC";
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Busca itens no banco SQLite usando Prisma $queryRaw
|
|
*/
|
|
export async function searchItems<T = any>(
|
|
params: SearchParams,
|
|
): Promise<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;
|
|
|
|
try {
|
|
// Log básico do schema na primeira chamada
|
|
if (!schemaLogged) {
|
|
try {
|
|
const tables = (await (itemsPrisma.$queryRaw as any)(
|
|
Prisma.raw(
|
|
`SELECT name FROM sqlite_master WHERE type='table' ORDER BY name`,
|
|
),
|
|
)) as Array<{ name: string }>;
|
|
console.info(
|
|
"[items-db] Tabelas:",
|
|
tables.map((t: { name: string }) => t.name),
|
|
);
|
|
schemaLogged = true;
|
|
} catch (intErr) {
|
|
console.warn(
|
|
"[items-db] Falha ao ler schema:",
|
|
(intErr as any)?.message,
|
|
);
|
|
schemaLogged = true;
|
|
}
|
|
}
|
|
|
|
// Buscar todas as tabelas disponíveis
|
|
const allTables = (await (itemsPrisma.$queryRaw as any)(
|
|
Prisma.raw(
|
|
`SELECT name FROM sqlite_master WHERE type='table' AND name <> 'sqlite_sequence'`,
|
|
),
|
|
)) as Array<{ name: string }>;
|
|
|
|
// Filtra por categorias solicitadas (EN → PT)
|
|
const allowedPt = new Set(
|
|
categories.length > 0
|
|
? categories.map((en) => enToPtCategoryMap[en] || en)
|
|
: allTables.map((t: { name: string }) => t.name),
|
|
);
|
|
const candidateTables = allTables
|
|
.map((t: { name: string }) => t.name)
|
|
.filter((name: string) => allowedPt.has(name));
|
|
|
|
const selects: string[] = [];
|
|
const bindings: any[] = [];
|
|
|
|
for (const tableName of candidateTables) {
|
|
try {
|
|
// Verificar colunas da tabela
|
|
const cols = (await (itemsPrisma.$queryRaw as any)(
|
|
Prisma.raw(`PRAGMA table_info(${tableName})`),
|
|
)) as Array<{ name: string; type: string }>;
|
|
|
|
const colNames = new Set(
|
|
cols.map((c: { name?: string }) => (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
|
|
if (vocation && !colNames.has("voc")) {
|
|
continue;
|
|
}
|
|
|
|
const tableWhere: string[] = [];
|
|
const tableBindings: any[] = [];
|
|
|
|
if (q && colNames.has("nome")) {
|
|
tableWhere.push("LOWER(nome) LIKE ?");
|
|
tableBindings.push(`%${q.toLowerCase()}%`);
|
|
}
|
|
|
|
if (vocation && colNames.has("voc")) {
|
|
const sel = vocation.toLowerCase();
|
|
tableWhere.push("REPLACE(LOWER(voc), ' ', '') = REPLACE(?, ' ', '')");
|
|
tableBindings.push(sel);
|
|
}
|
|
|
|
const whereSqlTbl =
|
|
tableWhere.length > 0 ? `WHERE ${tableWhere.join(" AND ")}` : "";
|
|
|
|
// Mapear colunas dinâmicas
|
|
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
|
|
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(", ");
|
|
|
|
const selectSql = `SELECT
|
|
id,
|
|
nome AS name,
|
|
'${tableName}' AS category,
|
|
image_url AS image,
|
|
${levelCol} AS level,
|
|
${weightCol} AS weight,
|
|
${slotsCol} AS slots,
|
|
${skillCol} AS skill${extraSelect ? `, ${extraSelect}` : ""}
|
|
FROM ${tableName} ${whereSqlTbl}`;
|
|
|
|
selects.push(selectSql);
|
|
bindings.push(...tableBindings);
|
|
} catch (e) {
|
|
console.warn(
|
|
`[items-db] Ignorando tabela ${tableName}:`,
|
|
(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 ");
|
|
const orderBy = getOrderBy(sort);
|
|
|
|
// Contagem total
|
|
const countSql = `SELECT COUNT(1) as cnt FROM (${unionSql}) AS u`;
|
|
const countResult = (await (itemsPrisma.$queryRaw as any)(
|
|
Prisma.raw(countSql),
|
|
...bindings,
|
|
)) as Array<{ cnt: number }>;
|
|
const total = countResult[0]?.cnt ?? 0;
|
|
|
|
// Buscar itens com paginação
|
|
const selectSql = `SELECT * FROM (${unionSql}) AS u ORDER BY ${orderBy} LIMIT ? OFFSET ?`;
|
|
const items = (await (itemsPrisma.$queryRaw as any)(
|
|
Prisma.raw(selectSql),
|
|
...bindings,
|
|
safePageSize,
|
|
offset,
|
|
)) as T[];
|
|
|
|
return { items, total };
|
|
} catch (error) {
|
|
console.error("[items-db] Erro ao buscar itens:", error);
|
|
return { items: [], total: 0 } as SearchResult<T>;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Busca um item por ID no banco de dados
|
|
* Busca em todas as tabelas de categorias até encontrar o item
|
|
*/
|
|
export async function getItemById(itemId: number): Promise<any | null> {
|
|
try {
|
|
// Buscar em todas as tabelas de categorias
|
|
const tables = await itemsPrisma.$queryRaw<
|
|
Array<{ name: string }>
|
|
>`SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'`;
|
|
|
|
for (const table of tables) {
|
|
try {
|
|
const item = (await (itemsPrisma.$queryRaw as any)(
|
|
Prisma.raw(`SELECT * FROM ${table.name} WHERE id = ?`),
|
|
itemId,
|
|
)) as any[];
|
|
|
|
if (item && item.length > 0) {
|
|
return item[0];
|
|
}
|
|
} catch (_err) {}
|
|
}
|
|
|
|
return null;
|
|
} catch (error) {
|
|
console.error("[items-db] Erro ao buscar item por ID:", error);
|
|
return null;
|
|
}
|
|
}
|