992 lines
33 KiB
TypeScript
992 lines
33 KiB
TypeScript
/**
|
|
* Queries de listings (anúncios)
|
|
* CRUD completo para a tabela listings
|
|
*/
|
|
|
|
import { query } from "../index";
|
|
import { itemExists } from "./items";
|
|
|
|
export interface Listing {
|
|
listing_id: number;
|
|
seller_id: number;
|
|
item_id: number; // ID legado (SQLite) - mantido para compatibilidade
|
|
item_name: string;
|
|
quantity: number;
|
|
server_name: string;
|
|
price_pix: number | null;
|
|
price_tibia_coins: number | null;
|
|
price_gold_coins: number | null;
|
|
is_auction: boolean;
|
|
current_bid_amount: number | null;
|
|
current_bidder_id: number | null;
|
|
auction_ends_at: Date | null;
|
|
description: string | null;
|
|
listing_status: "active" | "sold" | "cancelled" | "in_escrow" | "expired";
|
|
created_at: Date;
|
|
updated_at: Date;
|
|
sold_at: Date | null;
|
|
views_count: number;
|
|
// Campos adicionados na migração 012
|
|
is_featured?: boolean;
|
|
favorites_count?: number;
|
|
inquiries_count?: number;
|
|
price_history?: any; // JSONB
|
|
expires_at?: Date | null;
|
|
// Campos adicionados na migração 013 (troca por item)
|
|
trade_item_id?: number | null;
|
|
trade_item_name?: string | null;
|
|
trade_item_quantity?: number | null;
|
|
// Campos adicionados na migração 014 (leilões)
|
|
min_bid_increment?: number | null;
|
|
initial_price_pix?: number | null;
|
|
initial_price_tibia_coins?: number | null;
|
|
initial_price_gold_coins?: number | null;
|
|
// Campos do JOIN com items (unificado)
|
|
item_id_unified?: number | null; // ID unificado da tabela items
|
|
image_local_path?: string | null;
|
|
image_url?: string | null;
|
|
item_category?: string | null;
|
|
// Campo calculado (não vem do banco diretamente)
|
|
seller_character_name?: string | null;
|
|
}
|
|
|
|
export interface CreateListingData {
|
|
seller_id: number;
|
|
item_id?: number; // ID legado (SQLite) - opcional, mantido para compatibilidade
|
|
item_id_unified?: number; // ID unificado da tabela items - preferido
|
|
item_name: string;
|
|
quantity: number;
|
|
server_name: string;
|
|
price_pix?: number;
|
|
price_tibia_coins?: number;
|
|
price_gold_coins?: number;
|
|
is_auction?: boolean;
|
|
auction_ends_at?: Date;
|
|
description?: string;
|
|
trade_item_id?: number | null;
|
|
trade_item_name?: string | null;
|
|
trade_item_quantity?: number | null;
|
|
min_bid_increment?: number;
|
|
}
|
|
|
|
export interface UpdateListingData {
|
|
quantity?: number;
|
|
price_pix?: number;
|
|
price_tibia_coins?: number;
|
|
price_gold_coins?: number;
|
|
description?: string;
|
|
listing_status?: Listing["listing_status"];
|
|
current_bid_amount?: number;
|
|
current_bidder_id?: number;
|
|
}
|
|
|
|
export interface ListingFilters {
|
|
status?: Listing["listing_status"];
|
|
server_name?: string;
|
|
item_id?: number;
|
|
seller_id?: number;
|
|
is_auction?: boolean;
|
|
search?: string;
|
|
page?: number;
|
|
pageSize?: number;
|
|
sortBy?:
|
|
| "created_at"
|
|
| "price_pix"
|
|
| "price_tibia_coins"
|
|
| "price_gold_coins"
|
|
| "views_count";
|
|
sortOrder?: "ASC" | "DESC";
|
|
}
|
|
|
|
/**
|
|
* Cria um novo anúncio
|
|
*/
|
|
export async function createListing(data: CreateListingData): Promise<Listing> {
|
|
// Validar item_id_unified se fornecido
|
|
if (data.item_id_unified) {
|
|
const exists = await itemExists(data.item_id_unified);
|
|
if (!exists) {
|
|
throw new Error(
|
|
`Item com ID unificado ${data.item_id_unified} não encontrado na tabela items`,
|
|
);
|
|
}
|
|
}
|
|
|
|
// Calcular incremento mínimo se for leilão e não foi fornecido
|
|
let minBidIncrement = data.min_bid_increment;
|
|
if (data.is_auction && !minBidIncrement) {
|
|
// 5% do preço inicial ou mínimo de R$ 10,00
|
|
const initialPrice = data.price_pix || 0;
|
|
minBidIncrement = Math.max(initialPrice * 0.05, 10.0);
|
|
}
|
|
|
|
// Usar item_id_unified se fornecido, senão usar item_id legado
|
|
const itemIdUnified = data.item_id_unified || null;
|
|
const itemIdLegacy = data.item_id || null;
|
|
|
|
let result;
|
|
try {
|
|
// Tentar INSERT com todas as colunas (se migração 014 foi executada)
|
|
result = await query<Listing>(
|
|
`
|
|
INSERT INTO listings (
|
|
seller_id, item_id, item_id_unified, item_name, quantity, server_name,
|
|
price_pix, price_tibia_coins, price_gold_coins,
|
|
is_auction, auction_ends_at, description,
|
|
trade_item_id, trade_item_name, trade_item_quantity,
|
|
initial_price_pix, initial_price_tibia_coins, initial_price_gold_coins,
|
|
min_bid_increment
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
|
|
RETURNING
|
|
listing_id, seller_id, item_id, item_name, quantity, server_name,
|
|
price_pix, price_tibia_coins, price_gold_coins,
|
|
is_auction, current_bid_amount, current_bidder_id, auction_ends_at,
|
|
description, listing_status, created_at, updated_at, sold_at, views_count,
|
|
COALESCE(is_featured, false) as is_featured,
|
|
COALESCE(favorites_count, 0) as favorites_count,
|
|
COALESCE(inquiries_count, 0) as inquiries_count,
|
|
COALESCE(price_history, '[]'::jsonb) as price_history,
|
|
expires_at,
|
|
trade_item_id, trade_item_name, trade_item_quantity,
|
|
min_bid_increment, initial_price_pix, initial_price_tibia_coins, initial_price_gold_coins
|
|
`,
|
|
[
|
|
data.seller_id,
|
|
itemIdLegacy,
|
|
itemIdUnified,
|
|
data.item_name,
|
|
data.quantity,
|
|
data.server_name,
|
|
data.price_pix || null,
|
|
data.price_tibia_coins || null,
|
|
data.price_gold_coins || null,
|
|
data.is_auction || false,
|
|
data.auction_ends_at || null,
|
|
data.description || null,
|
|
data.trade_item_id || null,
|
|
data.trade_item_name || null,
|
|
data.trade_item_quantity || null,
|
|
data.is_auction ? data.price_pix || null : null, // initial_price_pix
|
|
data.is_auction ? data.price_tibia_coins || null : null, // initial_price_tibia_coins
|
|
data.is_auction ? data.price_gold_coins || null : null, // initial_price_gold_coins
|
|
minBidIncrement || null,
|
|
],
|
|
);
|
|
} catch (error) {
|
|
// Se as colunas da migração 014 ou item_id_unified não existirem, usar INSERT sem elas
|
|
if (error instanceof Error && error.message.includes("does not exist")) {
|
|
try {
|
|
// Tentar com item_id_unified mas sem colunas de migração 014
|
|
result = await query<Listing>(
|
|
`
|
|
INSERT INTO listings (
|
|
seller_id, item_id, item_id_unified, item_name, quantity, server_name,
|
|
price_pix, price_tibia_coins, price_gold_coins,
|
|
is_auction, auction_ends_at, description,
|
|
trade_item_id, trade_item_name, trade_item_quantity
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
|
|
RETURNING
|
|
listing_id, seller_id, item_id, item_name, quantity, server_name,
|
|
price_pix, price_tibia_coins, price_gold_coins,
|
|
is_auction, current_bid_amount, current_bidder_id, auction_ends_at,
|
|
description, listing_status, created_at, updated_at, sold_at, views_count,
|
|
COALESCE(is_featured, false) as is_featured,
|
|
COALESCE(favorites_count, 0) as favorites_count,
|
|
COALESCE(inquiries_count, 0) as inquiries_count,
|
|
COALESCE(price_history, '[]'::jsonb) as price_history,
|
|
expires_at,
|
|
trade_item_id, trade_item_name, trade_item_quantity
|
|
`,
|
|
[
|
|
data.seller_id,
|
|
itemIdLegacy,
|
|
itemIdUnified,
|
|
data.item_name,
|
|
data.quantity,
|
|
data.server_name,
|
|
data.price_pix || null,
|
|
data.price_tibia_coins || null,
|
|
data.price_gold_coins || null,
|
|
data.is_auction || false,
|
|
data.auction_ends_at || null,
|
|
data.description || null,
|
|
data.trade_item_id || null,
|
|
data.trade_item_name || null,
|
|
data.trade_item_quantity || null,
|
|
],
|
|
);
|
|
} catch (error2) {
|
|
// Se item_id_unified também não existir, usar apenas item_id legado
|
|
if (
|
|
error2 instanceof Error &&
|
|
error2.message.includes("does not exist")
|
|
) {
|
|
result = await query<Listing>(
|
|
`
|
|
INSERT INTO listings (
|
|
seller_id, item_id, item_name, quantity, server_name,
|
|
price_pix, price_tibia_coins, price_gold_coins,
|
|
is_auction, auction_ends_at, description,
|
|
trade_item_id, trade_item_name, trade_item_quantity
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
|
|
RETURNING
|
|
listing_id, seller_id, item_id, item_name, quantity, server_name,
|
|
price_pix, price_tibia_coins, price_gold_coins,
|
|
is_auction, current_bid_amount, current_bidder_id, auction_ends_at,
|
|
description, listing_status, created_at, updated_at, sold_at, views_count,
|
|
COALESCE(is_featured, false) as is_featured,
|
|
COALESCE(favorites_count, 0) as favorites_count,
|
|
COALESCE(inquiries_count, 0) as inquiries_count,
|
|
COALESCE(price_history, '[]'::jsonb) as price_history,
|
|
expires_at,
|
|
trade_item_id, trade_item_name, trade_item_quantity
|
|
`,
|
|
[
|
|
data.seller_id,
|
|
itemIdLegacy,
|
|
data.item_name,
|
|
data.quantity,
|
|
data.server_name,
|
|
data.price_pix || null,
|
|
data.price_tibia_coins || null,
|
|
data.price_gold_coins || null,
|
|
data.is_auction || false,
|
|
data.auction_ends_at || null,
|
|
data.description || null,
|
|
data.trade_item_id || null,
|
|
data.trade_item_name || null,
|
|
data.trade_item_quantity || null,
|
|
],
|
|
);
|
|
} else {
|
|
throw error2;
|
|
}
|
|
}
|
|
} else {
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
return result.rows[0];
|
|
}
|
|
|
|
/**
|
|
* Busca anúncio por ID
|
|
*/
|
|
export async function getListingById(
|
|
listingId: number,
|
|
): Promise<Listing | null> {
|
|
let result;
|
|
try {
|
|
// Tentar query com todas as colunas (se migração 014 foi executada)
|
|
result = await query<Listing & { seller_character_name: string }>(
|
|
`
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category,
|
|
uc.character_name as seller_character_name
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
LEFT JOIN user_characters uc ON uc.user_id = l.seller_id
|
|
AND uc.server_name = l.server_name
|
|
AND uc.is_main = TRUE
|
|
WHERE l.listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
} catch (error) {
|
|
// Se as colunas da migração 014 não existirem, usar query sem elas
|
|
if (error instanceof Error && error.message.includes("does not exist")) {
|
|
result = await query<Listing & { seller_character_name: string }>(
|
|
`
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
NULL::numeric as min_bid_increment,
|
|
NULL::numeric as initial_price_pix,
|
|
NULL::integer as initial_price_tibia_coins,
|
|
NULL::bigint as initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category,
|
|
uc.character_name as seller_character_name
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
LEFT JOIN user_characters uc ON uc.user_id = l.seller_id
|
|
AND uc.server_name = l.server_name
|
|
AND uc.is_main = TRUE
|
|
WHERE l.listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
} else {
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
if (result.rows.length === 0) {
|
|
return null;
|
|
}
|
|
|
|
const row = result.rows[0];
|
|
return {
|
|
...row,
|
|
seller_character_name: row.seller_character_name || null,
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Lista anúncios com filtros e paginação
|
|
*/
|
|
export async function getListings(
|
|
filters: ListingFilters = {},
|
|
): Promise<{ listings: Listing[]; total: number }> {
|
|
const {
|
|
status,
|
|
server_name,
|
|
item_id,
|
|
seller_id,
|
|
is_auction,
|
|
search,
|
|
page = 1,
|
|
pageSize = 20,
|
|
sortBy = "created_at",
|
|
sortOrder = "DESC",
|
|
} = filters;
|
|
|
|
const conditions: string[] = [];
|
|
const params: any[] = [];
|
|
let paramIndex = 1;
|
|
|
|
if (status) {
|
|
conditions.push(`l.listing_status = $${paramIndex}`);
|
|
params.push(status);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (server_name) {
|
|
conditions.push(`l.server_name = $${paramIndex}`);
|
|
params.push(server_name);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (item_id) {
|
|
conditions.push(`l.item_id = $${paramIndex}`);
|
|
params.push(item_id);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (seller_id) {
|
|
conditions.push(`l.seller_id = $${paramIndex}`);
|
|
params.push(seller_id);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (is_auction !== undefined) {
|
|
conditions.push(`l.is_auction = $${paramIndex}`);
|
|
params.push(is_auction);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (search) {
|
|
conditions.push(
|
|
`(l.item_name ILIKE $${paramIndex} OR l.description ILIKE $${paramIndex})`,
|
|
);
|
|
params.push(`%${search}%`);
|
|
paramIndex++;
|
|
}
|
|
|
|
const whereClause =
|
|
conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "WHERE 1=1";
|
|
|
|
// Contar total
|
|
const countResult = await query<{ count: string }>(
|
|
`SELECT COUNT(*) as count FROM listings l ${whereClause}`,
|
|
params,
|
|
);
|
|
const total = parseInt(countResult.rows[0].count, 10);
|
|
|
|
// Validar sortBy - garantir que seja uma coluna válida
|
|
const validSortColumns = [
|
|
"created_at",
|
|
"price_pix",
|
|
"price_tibia_coins",
|
|
"price_gold_coins",
|
|
"views_count",
|
|
];
|
|
const sortColumn = validSortColumns.includes(sortBy) ? sortBy : "created_at";
|
|
const sortDirection = sortOrder === "ASC" ? "ASC" : "DESC";
|
|
|
|
// Mapear colunas para usar alias 'l.' quando necessário
|
|
const sortColumnWithAlias = `l.${sortColumn}`;
|
|
|
|
// Buscar listings
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
// Incrementar paramIndex para os parâmetros de LIMIT e OFFSET
|
|
const limitParamIndex = paramIndex;
|
|
const offsetParamIndex = paramIndex + 1;
|
|
const finalParams = [...params, pageSize, offset];
|
|
|
|
// Tentar query com todas as colunas (se migração 014 foi executada)
|
|
// Se falhar, usar query sem as novas colunas
|
|
let result;
|
|
try {
|
|
const querySql = `
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category,
|
|
uc.character_name as seller_character_name
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
LEFT JOIN user_characters uc ON uc.user_id = l.seller_id
|
|
AND uc.server_name = l.server_name
|
|
AND uc.is_main = TRUE
|
|
${whereClause}
|
|
ORDER BY ${sortColumnWithAlias} ${sortDirection}
|
|
LIMIT $${limitParamIndex} OFFSET $${offsetParamIndex}
|
|
`;
|
|
|
|
result = await query<Listing & { seller_character_name: string }>(
|
|
querySql,
|
|
finalParams,
|
|
);
|
|
} catch (error) {
|
|
// Se as colunas da migração 014 não existirem, usar query sem elas
|
|
if (error instanceof Error && error.message.includes("does not exist")) {
|
|
const fallbackQuerySql = `
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
NULL::numeric as min_bid_increment,
|
|
NULL::numeric as initial_price_pix,
|
|
NULL::integer as initial_price_tibia_coins,
|
|
NULL::bigint as initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category,
|
|
uc.character_name as seller_character_name
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
LEFT JOIN user_characters uc ON uc.user_id = l.seller_id
|
|
AND uc.server_name = l.server_name
|
|
AND uc.is_main = TRUE
|
|
${whereClause}
|
|
ORDER BY ${sortColumnWithAlias} ${sortDirection}
|
|
LIMIT $${limitParamIndex} OFFSET $${offsetParamIndex}
|
|
`;
|
|
|
|
result = await query<Listing & { seller_character_name: string }>(
|
|
fallbackQuerySql,
|
|
finalParams,
|
|
);
|
|
} else {
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
// Mapear resultados para garantir que seller_character_name seja null se não existir
|
|
const listings = result.rows.map((row) => ({
|
|
...row,
|
|
seller_character_name: row.seller_character_name || null,
|
|
}));
|
|
|
|
return {
|
|
listings,
|
|
total,
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Atualiza um anúncio
|
|
*/
|
|
export async function updateListing(
|
|
listingId: number,
|
|
data: UpdateListingData,
|
|
): Promise<Listing | null> {
|
|
const updates: string[] = [];
|
|
const params: any[] = [];
|
|
let paramIndex = 1;
|
|
|
|
if (data.quantity !== undefined) {
|
|
updates.push(`quantity = $${paramIndex}`);
|
|
params.push(data.quantity);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.price_pix !== undefined) {
|
|
updates.push(`price_pix = $${paramIndex}`);
|
|
params.push(data.price_pix);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.price_tibia_coins !== undefined) {
|
|
updates.push(`price_tibia_coins = $${paramIndex}`);
|
|
params.push(data.price_tibia_coins);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.price_gold_coins !== undefined) {
|
|
updates.push(`price_gold_coins = $${paramIndex}`);
|
|
params.push(data.price_gold_coins);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.description !== undefined) {
|
|
updates.push(`description = $${paramIndex}`);
|
|
params.push(data.description);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.listing_status !== undefined) {
|
|
updates.push(`listing_status = $${paramIndex}`);
|
|
params.push(data.listing_status);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.current_bid_amount !== undefined) {
|
|
updates.push(`current_bid_amount = $${paramIndex}`);
|
|
params.push(data.current_bid_amount);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (data.current_bidder_id !== undefined) {
|
|
updates.push(`current_bidder_id = $${paramIndex}`);
|
|
params.push(data.current_bidder_id);
|
|
paramIndex++;
|
|
}
|
|
|
|
if (updates.length === 0) {
|
|
return getListingById(listingId);
|
|
}
|
|
|
|
params.push(listingId);
|
|
|
|
let result;
|
|
try {
|
|
// Tentar query com todas as colunas (se migração 014 foi executada)
|
|
result = await query<Listing & { seller_character_name: string }>(
|
|
`
|
|
UPDATE listings l
|
|
SET ${updates.join(", ")}
|
|
WHERE l.listing_id = $${paramIndex}
|
|
RETURNING
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar dados do JOIN após o UPDATE
|
|
if (result.rows.length > 0) {
|
|
const listingId = result.rows[0].listing_id;
|
|
const joinResult = await query<
|
|
Listing & { seller_character_name: string }
|
|
>(
|
|
`
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category,
|
|
uc.character_name as seller_character_name
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
LEFT JOIN user_characters uc ON uc.user_id = l.seller_id
|
|
AND uc.server_name = l.server_name
|
|
AND uc.is_main = TRUE
|
|
WHERE l.listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
|
|
if (joinResult.rows.length > 0) {
|
|
result.rows[0] = joinResult.rows[0];
|
|
}
|
|
}
|
|
} catch (error) {
|
|
// Se as colunas da migração 014 não existirem, usar query sem elas
|
|
if (error instanceof Error && error.message.includes("does not exist")) {
|
|
result = await query<Listing & { seller_character_name: string }>(
|
|
`
|
|
UPDATE listings l
|
|
SET ${updates.join(", ")}
|
|
WHERE l.listing_id = $${paramIndex}
|
|
RETURNING
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
NULL::numeric as min_bid_increment,
|
|
NULL::numeric as initial_price_pix,
|
|
NULL::integer as initial_price_tibia_coins,
|
|
NULL::bigint as initial_price_gold_coins
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar dados do JOIN após o UPDATE
|
|
if (result.rows.length > 0) {
|
|
const listingId = result.rows[0].listing_id;
|
|
const joinResult = await query<
|
|
Listing & { seller_character_name: string }
|
|
>(
|
|
`
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
NULL::numeric as min_bid_increment,
|
|
NULL::numeric as initial_price_pix,
|
|
NULL::integer as initial_price_tibia_coins,
|
|
NULL::bigint as initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category,
|
|
uc.character_name as seller_character_name
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
LEFT JOIN user_characters uc ON uc.user_id = l.seller_id
|
|
AND uc.server_name = l.server_name
|
|
AND uc.is_main = TRUE
|
|
WHERE l.listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
|
|
if (joinResult.rows.length > 0) {
|
|
result.rows[0] = joinResult.rows[0];
|
|
}
|
|
}
|
|
} else {
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
if (result.rows.length === 0) {
|
|
return null;
|
|
}
|
|
|
|
const row = result.rows[0];
|
|
return {
|
|
...row,
|
|
seller_character_name: row.seller_character_name || null,
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Cancela um anúncio (soft delete - muda status)
|
|
*/
|
|
export async function deleteListing(listingId: number): Promise<boolean> {
|
|
const result = await query(
|
|
`
|
|
UPDATE listings
|
|
SET listing_status = 'cancelled'
|
|
WHERE listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
|
|
return (result.rowCount ?? 0) > 0;
|
|
}
|
|
|
|
/**
|
|
* Incrementa contador de views
|
|
*/
|
|
export async function incrementViews(listingId: number): Promise<void> {
|
|
await query(
|
|
`
|
|
UPDATE listings
|
|
SET views_count = views_count + 1
|
|
WHERE listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Busca anúncios de um vendedor
|
|
*/
|
|
export async function getListingsBySeller(
|
|
sellerId: number,
|
|
filters: {
|
|
status?: Listing["listing_status"];
|
|
page?: number;
|
|
pageSize?: number;
|
|
} = {},
|
|
): Promise<{ listings: Listing[]; total: number }> {
|
|
return getListings({
|
|
seller_id: sellerId,
|
|
...filters,
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Busca apenas anúncios ativos
|
|
*/
|
|
export async function getActiveListings(
|
|
filters: Omit<ListingFilters, "status"> = {},
|
|
): Promise<{ listings: Listing[]; total: number }> {
|
|
return getListings({
|
|
...filters,
|
|
status: "active",
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Coloca um lance em um leilão
|
|
* Valida se o lance é maior que o lance atual + incremento mínimo
|
|
*/
|
|
export async function placeBid(
|
|
listingId: number,
|
|
bidderId: number,
|
|
bidAmount: number,
|
|
_paymentMethod: "pix" | "tc" | "gp",
|
|
): Promise<{
|
|
success: boolean;
|
|
listing: Listing | null;
|
|
previousBidderId: number | null;
|
|
error?: string;
|
|
}> {
|
|
const { getPool } = await import("../index");
|
|
const pool = getPool();
|
|
const client = await pool.connect();
|
|
|
|
try {
|
|
await client.query("BEGIN");
|
|
|
|
// Buscar listing atual com JOIN
|
|
const listingResult = await client.query<Listing>(
|
|
`
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
WHERE l.listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
|
|
if (listingResult.rows.length === 0) {
|
|
await client.query("ROLLBACK");
|
|
return {
|
|
success: false,
|
|
listing: null,
|
|
previousBidderId: null,
|
|
error: "Anúncio não encontrado",
|
|
};
|
|
}
|
|
|
|
const listing = listingResult.rows[0];
|
|
|
|
// Validações
|
|
if (listing.listing_status !== "active") {
|
|
await client.query("ROLLBACK");
|
|
return {
|
|
success: false,
|
|
listing: null,
|
|
previousBidderId: null,
|
|
error: "Anúncio não está ativo",
|
|
};
|
|
}
|
|
|
|
if (!listing.is_auction) {
|
|
await client.query("ROLLBACK");
|
|
return {
|
|
success: false,
|
|
listing: null,
|
|
previousBidderId: null,
|
|
error: "Este anúncio não é um leilão",
|
|
};
|
|
}
|
|
|
|
if (listing.seller_id === bidderId) {
|
|
await client.query("ROLLBACK");
|
|
return {
|
|
success: false,
|
|
listing: null,
|
|
previousBidderId: null,
|
|
error: "Você não pode dar lance no seu próprio anúncio",
|
|
};
|
|
}
|
|
|
|
if (
|
|
listing.auction_ends_at &&
|
|
new Date(listing.auction_ends_at) < new Date()
|
|
) {
|
|
await client.query("ROLLBACK");
|
|
return {
|
|
success: false,
|
|
listing: null,
|
|
previousBidderId: null,
|
|
error: "Leilão expirou",
|
|
};
|
|
}
|
|
|
|
// Calcular lance mínimo
|
|
const currentBid = listing.current_bid_amount || 0;
|
|
const minIncrement = listing.min_bid_increment || 10; // Padrão de R$ 10,00
|
|
const minBid = currentBid + minIncrement;
|
|
|
|
if (bidAmount < minBid) {
|
|
await client.query("ROLLBACK");
|
|
return {
|
|
success: false,
|
|
listing: null,
|
|
previousBidderId: listing.current_bidder_id,
|
|
error: `Lance mínimo é ${minBid.toFixed(2)} (lance atual: ${currentBid.toFixed(2)} + incremento: ${minIncrement.toFixed(2)})`,
|
|
};
|
|
}
|
|
|
|
// Guardar ID do lanceador anterior
|
|
const previousBidderId = listing.current_bidder_id;
|
|
|
|
// Atualizar listing com novo lance
|
|
await client.query(
|
|
`
|
|
UPDATE listings
|
|
SET
|
|
current_bid_amount = $1,
|
|
current_bidder_id = $2,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE listing_id = $3
|
|
`,
|
|
[bidAmount, bidderId, listingId],
|
|
);
|
|
|
|
// Buscar listing atualizado com JOIN
|
|
const updateResult = await client.query<Listing>(
|
|
`
|
|
SELECT
|
|
l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
|
|
l.price_pix, l.price_tibia_coins, l.price_gold_coins,
|
|
l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
|
|
l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
|
|
COALESCE(l.is_featured, false) as is_featured,
|
|
COALESCE(l.favorites_count, 0) as favorites_count,
|
|
COALESCE(l.inquiries_count, 0) as inquiries_count,
|
|
COALESCE(l.price_history, '[]'::jsonb) as price_history,
|
|
l.expires_at,
|
|
l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
|
|
l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
|
|
i.item_id as item_id_unified,
|
|
i.image_local_path,
|
|
i.image_url,
|
|
i.category as item_category
|
|
FROM listings l
|
|
LEFT JOIN items i ON i.item_id = l.item_id_unified
|
|
WHERE l.listing_id = $1
|
|
`,
|
|
[listingId],
|
|
);
|
|
|
|
await client.query("COMMIT");
|
|
|
|
return {
|
|
success: true,
|
|
listing: updateResult.rows[0],
|
|
previousBidderId,
|
|
};
|
|
} catch (error) {
|
|
await client.query("ROLLBACK");
|
|
throw error;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|