pagodera/lib/prisma/queries/items.ts

/**
 * Queries de itens usando Prisma
 * Busca itens da tabela unificada items (PostgreSQL)
 */

import { Prisma } from "@prisma/client";
import { ensureItemsSeeded } from "../../items/import-from-sqlite";
import { secureLogger } from "../../secure-logger";
import { prisma } from "../app";

export type ItemSort =
  | "name"
  | "level-asc"
  | "level-desc"
  | "weight-asc"
  | "weight-desc"
  | "category"
  | "updated-desc";

export interface Item {
  item_id: number;
  source_table_id: number;
  source_table_name: string;
  item_name: string;
  category: string;
  image_local_path: string | null;
  image_url: string | null;
  rarity: string | null;
  level: number | null;
  weight: number | null;
  slots: number | null;
  skill: string | null;
  metadata: Record<string, any>;
  created_at: Date | string;
  updated_at: Date | string;
}

export interface ItemWithMeta extends Item {
  servers: string[];
  tags: string[];
  description?: string | null;
}

export interface SearchItemsParams {
  q?: string;
  category?: string;
  rarity?: string;
  server?: string;
  sort?: ItemSort;
  page?: number;
  pageSize?: number;
}

export interface AdminSearchParams extends SearchItemsParams {
  tags?: string[];
}

export interface AdminItemsResult {
  items: ItemWithMeta[];
  total: number;
  page: number;
  pageSize: number;
  servers: string[];
  categories: string[];
}

export interface ItemUpsertInput {
  item_name: string;
  category: string;
  rarity?: string | null;
  image_url?: string | null;
  image_local_path?: string | null;
  level?: number | null;
  weight?: number | null;
  slots?: number | null;
  skill?: string | null;
  servers?: string[];
  tags?: string[];
  description?: string | null;
  source_table_name?: string;
  source_table_id?: number;
  metadata?: Record<string, any>;
}

function buildOrder(sort: ItemSort): Prisma.ItemOrderByWithRelationInput[] {
  switch (sort) {
    case "level-asc":
      return [{ level: "asc" }, { item_name: "asc" }];
    case "level-desc":
      return [{ level: "desc" }, { item_name: "asc" }];
    case "weight-asc":
      return [{ weight: "asc" }, { item_name: "asc" }];
    case "weight-desc":
      return [{ weight: "desc" }, { item_name: "asc" }];
    case "category":
      return [{ category: "asc" }, { item_name: "asc" }];
    case "name":
      return [{ item_name: "asc" }];
    default:
      return [{ updated_at: "desc" }];
  }
}

function parseMetadata(
  metadata: Prisma.JsonValue | null | undefined,
): Record<string, any> {
  if (!metadata || typeof metadata !== "object" || Array.isArray(metadata)) {
    return {};
  }
  return metadata as Record<string, any>;
}

function extractServers(meta: Record<string, any>): string[] {
  const servers = meta?.servers;
  if (Array.isArray(servers)) {
    return servers
      .map((server) => (typeof server === "string" ? server.trim() : ""))
      .filter(Boolean);
  }
  if (typeof servers === "string") {
    return servers
      .split(",")
      .map((server) => server.trim())
      .filter(Boolean);
  }
  return [];
}

function extractTags(meta: Record<string, any>): string[] {
  const tags = meta?.tags;
  if (Array.isArray(tags)) {
    return tags
      .map((tag) => (typeof tag === "string" ? tag.trim() : ""))
      .filter(Boolean);
  }
  if (typeof tags === "string") {
    return tags
      .split(",")
      .map((tag) => tag.trim())
      .filter(Boolean);
  }
  return [];
}

function buildItem(raw: any): Item {
  const createdAt =
    raw.created_at instanceof Date
      ? raw.created_at.toISOString()
      : raw.created_at;
  const updatedAt =
    raw.updated_at instanceof Date
      ? raw.updated_at.toISOString()
      : raw.updated_at;

  return {
    ...raw,
    weight: raw.weight ? Number(raw.weight) : null,
    metadata: parseMetadata(raw.metadata),
    created_at: createdAt,
    updated_at: updatedAt,
  } as Item;
}

function buildItemWithMeta(raw: any): ItemWithMeta {
  const base = buildItem(raw);
  const servers = extractServers(base.metadata);
  const tags = extractTags(base.metadata);
  const description =
    typeof base.metadata.description === "string"
      ? base.metadata.description
      : null;

  return {
    ...base,
    servers,
    tags,
    description,
  };
}

function toDecimal(value: number | null | undefined): Prisma.Decimal | null {
  if (typeof value !== "number" || Number.isNaN(value)) {
    return null;
  }
  return new Prisma.Decimal(value);
}

function buildMetadata(
  existing: Record<string, any>,
  overrides: {
    servers?: string[];
    tags?: string[];
    description?: string | null;
    metadata?: Record<string, any>;
  },
): Record<string, any> {
  const merged = { ...existing, ...(overrides.metadata || {}) };

  if (overrides.servers) {
    merged.servers = overrides.servers;
  }
  if (overrides.tags) {
    merged.tags = overrides.tags;
  }
  if (overrides.description !== undefined) {
    merged.description = overrides.description;
  }

  return merged;
}

// Campos relacionados a valores/preços que NÃO pertencem ao modelo Item.
// Qualquer presença deles em metadata deve ser rejeitada.
const FORBIDDEN_VALUE_KEYS = new Set<string>([
  // Geral
  "price",
  "prices",
  "valor",
  "value",
  "sell_value",
  "sellPrice",
  "buy",
  "buy_price",
  // Chaves usadas em Listings
  "price_pix",
  "price_tibia_coins",
  "price_gold_coins",
  "initial_price_pix",
  "initial_price_tibia_coins",
  "initial_price_gold_coins",
  "current_bid_amount",
]);

/**
 * Verifica recursivamente se há campos proibidos relacionados a valores/preços.
 * Lança erro se encontrar.
 */
export function assertNoValueFieldsInMetadata(
  meta: Record<string, any> | undefined | null,
  context?: string,
): void {
  if (!meta || typeof meta !== "object") return;

  const stack: Array<{ obj: Record<string, any>; path: string }> = [
    { obj: meta as Record<string, any>, path: "metadata" },
  ];
  while (stack.length) {
    const { obj, path } = stack.pop()!;
    for (const key of Object.keys(obj)) {
      const nextPath = `${path}.${key}`;
      if (FORBIDDEN_VALUE_KEYS.has(key)) {
        const msg = `Itens não possuem campos de valor/preço no cadastro. Campo proibido: ${nextPath}. Use listings para preços.`;
        secureLogger.warn(msg, { context });
        throw new Error(msg);
      }
      const val = obj[key];
      if (val && typeof val === "object" && !Array.isArray(val)) {
        stack.push({ obj: val as Record<string, any>, path: nextPath });
      }
    }
  }
}

export async function getItemById(itemId: number): Promise<Item | null> {
  const item = await prisma.item.findUnique({ where: { item_id: itemId } });
  if (!item) {
    return null;
  }
  return buildItem(item);
}

export async function getItemByName(itemName: string): Promise<Item | null> {
  const item = await prisma.item.findFirst({
    where: {
      item_name: {
        equals: itemName,
        mode: "insensitive",
      },
    },
  });
  if (!item) {
    return null;
  }
  return buildItem(item);
}

export async function getItemBySource(
  sourceTableName: string,
  sourceTableId: number,
): Promise<Item | null> {
  const item = await prisma.item.findUnique({
    where: {
      source_table_name_source_table_id: {
        source_table_name: sourceTableName,
        source_table_id: sourceTableId,
      },
    },
  });
  if (!item) {
    return null;
  }
  return buildItem(item);
}

export async function searchAdminItems(
  params: AdminSearchParams = {},
): Promise<AdminItemsResult> {
  const { q = "", category, rarity, server, page = 1, pageSize = 50 } = params;

  await ensureItemsSeeded();

  const safePage = Math.max(1, page);
  const safePageSize = Math.min(Math.max(1, pageSize), 200);

  const where: Prisma.ItemWhereInput = {};

  if (q) {
    where.item_name = {
      contains: q,
      mode: "insensitive",
    };
  }

  if (category && category !== "all") {
    where.category = category;
  }

  if (rarity && rarity !== "all") {
    where.rarity = rarity;
  }

  if (server && server !== "all") {
    const existingAnd = Array.isArray(where.AND)
      ? [...where.AND]
      : where.AND
        ? [where.AND]
        : [];

    existingAnd.push({
      metadata: {
        path: ["servers"],
        array_contains: [server],
      },
    });

    where.AND = existingAnd;
  }

  const [items, total, categoryRows] = await Promise.all([
    prisma.item.findMany({
      where,
      orderBy: buildOrder(params.sort || "updated-desc"),
      skip: (safePage - 1) * safePageSize,
      take: safePageSize,
    }),
    prisma.item.count({ where }),
    prisma.item.findMany({
      distinct: ["category"],
      select: { category: true },
      orderBy: { category: "asc" },
    }),
  ]);

  const mapped = items.map(buildItemWithMeta);

  const serversSet = new Set<string>();
  mapped.forEach((item) => {
    item.servers.forEach((srv) => serversSet.add(srv));
  });

  return {
    items: mapped,
    total,
    page: safePage,
    pageSize: safePageSize,
    servers: Array.from(serversSet).sort((a, b) =>
      a.localeCompare(b, "pt-BR", { sensitivity: "base" }),
    ),
    categories: categoryRows
      .map((row) => row.category)
      .filter((c): c is string => Boolean(c))
      .sort((a, b) => a.localeCompare(b, "pt-BR", { sensitivity: "base" })),
  };
}

export async function searchItems(
  params: SearchItemsParams = {},
): Promise<{ items: ItemWithMeta[]; total: number }> {
  const { items, total } = await searchAdminItems(params);
  return { items, total };
}

export async function createAdminItem(
  input: ItemUpsertInput,
): Promise<ItemWithMeta> {
  const now = Date.now();
  // Validação: impedir qualquer campo de valor em metadata (preço etc.)
  assertNoValueFieldsInMetadata(input.metadata, "createAdminItem");
  const data: Prisma.ItemCreateInput = {
    source_table_id: input.source_table_id ?? now,
    source_table_name: input.source_table_name ?? "custom",
    item_name: input.item_name,
    category: input.category,
    image_local_path: input.image_local_path ?? null,
    image_url: input.image_url ?? null,
    rarity: input.rarity ?? null,
    level: input.level ?? null,
    weight: toDecimal(input.weight),
    slots: input.slots ?? null,
    skill: input.skill ?? null,
    metadata: buildMetadata(
      {},
      {
        servers: input.servers ?? [],
        tags: input.tags ?? [],
        description: input.description ?? null,
        metadata: input.metadata ?? {},
      },
    ),
  };

  const created = await prisma.item.create({ data });
  return buildItemWithMeta(created);
}

export async function updateAdminItem(
  itemId: number,
  input: ItemUpsertInput,
): Promise<ItemWithMeta> {
  const existing = await prisma.item.findUnique({ where: { item_id: itemId } });
  if (!existing) {
    throw new Error("Item não encontrado");
  }

  const existingMeta = parseMetadata(existing.metadata);
  // Validação: impedir qualquer campo de valor em metadata (preço etc.)
  assertNoValueFieldsInMetadata(input.metadata, "updateAdminItem");
  const metadata = buildMetadata(existingMeta, {
    servers: input.servers ?? extractServers(existingMeta),
    tags: input.tags ?? extractTags(existingMeta),
    description:
      input.description !== undefined
        ? input.description
        : existingMeta.description,
    metadata: input.metadata,
  });

  const data: Prisma.ItemUpdateInput = {
    item_name: input.item_name ?? existing.item_name,
    category: input.category ?? existing.category,
    image_local_path:
      input.image_local_path !== undefined
        ? input.image_local_path
        : existing.image_local_path,
    image_url:
      input.image_url !== undefined ? input.image_url : existing.image_url,
    rarity: input.rarity !== undefined ? input.rarity : existing.rarity,
    level: input.level !== undefined ? input.level : existing.level,
    weight:
      input.weight !== undefined ? toDecimal(input.weight) : existing.weight,
    slots: input.slots !== undefined ? input.slots : existing.slots,
    skill: input.skill !== undefined ? input.skill : existing.skill,
    metadata,
  };

  const updated = await prisma.item.update({
    where: { item_id: itemId },
    data,
  });
  return buildItemWithMeta(updated);
}