← back to Govarbitrage
src/lib/listings.ts
244 lines
import { prisma } from "@/lib/db";
import type { Prisma } from "@prisma/client";
// Flat row shape backing the dashboard table — every sortable column the spec
// lists, denormalized from Listing + Research + CostBreakdown + the chosen Score.
export interface ListingRow {
id: string;
source: string;
sourceAuctionId: string;
sourceUrl: string | null;
title: string;
category: string | null;
manufacturer: string | null;
model: string | null;
condition: string;
quantity: number;
currentBid: number;
currentCost: number; // bid + premium + tax (acquisition-in)
recommendedMaxBid: number;
retailLow: number | null;
retailAverage: number | null;
retailHigh: number | null;
usedLow: number | null;
usedAverage: number | null;
usedHigh: number | null;
wholesale: number | null;
liquidation: number | null;
sellNow: number | null;
value7Day: number | null;
value30Day: number | null;
value90Day: number | null;
expectedSale: number | null;
shipping: number;
freight: number;
repairs: number;
marketplaceFees: number;
netProfit: number;
roi: number;
risk: string;
confidence: number | null;
opportunityScore: number;
arbitrageScore: number;
demandScore: number;
velocityScore: number;
logisticsScore: number;
conditionScore: number;
competitionScore: number;
buyerScore: number;
dropShip: string;
closingAt: string | null;
researchStatus: string;
imageUrl: string | null;
}
type FullListing = Prisma.ListingGetPayload<{
include: { research: true; costBreakdown: true; scores: true };
}>;
const num = (d: Prisma.Decimal | number | null | undefined): number =>
d == null ? 0 : typeof d === "number" ? d : Number(d);
const numN = (d: Prisma.Decimal | number | null | undefined): number | null =>
d == null ? null : typeof d === "number" ? d : Number(d);
export function flattenListing(l: FullListing, profile = "OVERALL_OPPORTUNITY"): ListingRow {
const r = l.research;
const c = l.costBreakdown;
const s = l.scores.find((x) => x.profile === profile) ?? l.scores[0];
const currentCost = c ? num(c.winningBid) + num(c.buyerPremium) + num(c.salesTax) : num(l.currentBid);
return {
id: l.id,
source: l.source,
sourceAuctionId: l.sourceAuctionId,
sourceUrl: l.sourceUrl,
title: l.title,
category: l.category,
manufacturer: l.manufacturer,
model: l.model,
condition: l.condition,
quantity: l.quantity,
currentBid: num(l.currentBid),
currentCost,
recommendedMaxBid: num(c?.recommendedMaxBid),
retailLow: numN(r?.usedLow),
retailAverage: numN(r?.avgRetail),
retailHigh: numN(r?.newRetail),
usedLow: numN(r?.usedLow),
usedAverage: numN(r?.usedSoldPrice),
usedHigh: numN(r?.usedHigh),
wholesale: numN(r?.wholesaleValue),
liquidation: numN(r?.liquidationValue),
sellNow: numN(r?.sellTodayValue),
value7Day: numN(r?.value7Day),
value30Day: numN(r?.value30Day),
value90Day: numN(r?.value90Day),
expectedSale: numN(r?.expectedSalePrice),
shipping: num(c?.shipping),
freight: num(c?.freight),
repairs: num(c?.repairs),
marketplaceFees: num(c?.marketplaceFees),
netProfit: num(c?.expectedNetProfit),
roi: num(c?.roi),
risk: s?.risk ?? "MEDIUM",
confidence: numN(r?.confidenceScore),
opportunityScore: s?.value ?? 0,
arbitrageScore: s?.arbitrage ?? 0,
demandScore: s?.demand ?? 0,
velocityScore: s?.velocity ?? 0,
logisticsScore: s?.logistics ?? 0,
conditionScore: s?.condition ?? 0,
competitionScore: s?.competition ?? 0,
buyerScore: s?.buyer ?? 0,
dropShip: s?.dropShip ?? "MODERATE",
closingAt: l.closingAt ? l.closingAt.toISOString() : null,
researchStatus: l.researchStatus,
imageUrl: l.imageUrls[0] ?? null,
};
}
export interface QueryParams {
search?: string;
source?: string;
category?: string;
condition?: string;
risk?: string;
closingWithinHours?: number;
sort?: keyof ListingRow;
dir?: "asc" | "desc";
page?: number;
pageSize?: number;
profile?: string;
// Listing lifecycle filter. Defaults to ACTIVE so ended/removed (dead)
// listings never show on the live grid. Pass "ALL" to include everything.
status?: string;
}
// Listing scalar columns that map 1-to-1 to a DB field and can therefore be
// pushed into Prisma's orderBy. Everything else (scores, risk, computed cost
// fields) lives on joined relations and must be sorted in JS.
const NATIVE_SORT_COLUMNS = new Set<keyof ListingRow>([
"currentBid",
"closingAt",
"title",
"source",
"sourceAuctionId",
"category",
"manufacturer",
"model",
"condition",
"quantity",
"researchStatus",
]);
export async function queryListings(params: QueryParams) {
const {
search,
source,
category,
condition,
risk,
closingWithinHours,
sort = "opportunityScore",
dir = "desc",
page = 1,
pageSize = 50,
profile = "OVERALL_OPPORTUNITY",
status = "ACTIVE",
} = params;
const where: Prisma.ListingWhereInput = {};
if (status && status !== "ALL") {
where.listingStatus = status as Prisma.ListingWhereInput["listingStatus"];
}
if (source) where.source = source as Prisma.ListingWhereInput["source"];
if (condition) where.condition = condition as Prisma.ListingWhereInput["condition"];
if (category) where.category = { contains: category, mode: "insensitive" };
if (search) {
where.OR = [
{ title: { contains: search, mode: "insensitive" } },
{ manufacturer: { contains: search, mode: "insensitive" } },
{ model: { contains: search, mode: "insensitive" } },
{ sourceAuctionId: { contains: search, mode: "insensitive" } },
];
}
if (closingWithinHours) {
where.closingAt = { lte: new Date(Date.now() + closingWithinHours * 3_600_000), gte: new Date() };
}
// DB-side sort + pagination path: only when the sort column is a native
// Listing scalar AND no risk filter is active (risk is derived from the
// Score relation and cannot be pushed to the DB without a schema change).
const canPaginateAtDb = NATIVE_SORT_COLUMNS.has(sort) && !risk;
if (canPaginateAtDb) {
const [rawRows, total] = await Promise.all([
prisma.listing.findMany({
where,
include: { research: true, costBreakdown: true, scores: true },
// nulls first on asc / last on desc mirrors the JS fallback's
// String(v ?? "") ordering, so the DB path is order-equivalent.
orderBy: { [sort]: { sort: dir, nulls: dir === "asc" ? "first" : "last" } } as Prisma.ListingOrderByWithRelationInput,
skip: (page - 1) * pageSize,
take: pageSize,
}),
prisma.listing.count({ where }),
]);
const rows = rawRows.map((l) => flattenListing(l, profile));
return { rows, total, page, pageSize };
}
// JS-side sort + pagination fallback: used when sort is a relation/computed
// field (opportunityScore, arbitrageScore, netProfit, roi, risk, etc.) or
// when a risk filter is active. A hard cap prevents unbounded memory load.
// TODO(perf): denormalize score columns (value, arbitrage, risk, …) onto
// Listing to enable DB-side sort/pagination for score-based sorts — needs a
// gated migration adding columns + a backfill job.
const CAP = 5000;
const all = await prisma.listing.findMany({
where,
include: { research: true, costBreakdown: true, scores: true },
take: CAP,
});
if (all.length === CAP) {
// Not silent: past the cap, total (rows.length) undercounts for this
// filter+sort. The durable fix is the denormalization TODO above.
console.warn(`[queryListings] hit ${CAP}-row cap (sort=${sort}, risk=${risk ?? "none"}); total may undercount`);
}
let rows = all.map((l) => flattenListing(l, profile));
if (risk) rows = rows.filter((r) => r.risk === risk);
rows.sort((a, b) => {
const av = a[sort];
const bv = b[sort];
if (typeof av === "number" && typeof bv === "number") return dir === "asc" ? av - bv : bv - av;
const as = String(av ?? "");
const bs = String(bv ?? "");
return dir === "asc" ? as.localeCompare(bs) : bs.localeCompare(as);
});
const total = rows.length;
const start = (page - 1) * pageSize;
return { rows: rows.slice(start, start + pageSize), total, page, pageSize };
}