← back to Govarbitrage

src/lib/reports.ts

140 lines

import { prisma } from "@/lib/db";

export interface GroupPerf {
  key: string;
  count: number;
  avgRoi: number;
  totalNet: number;
}

export interface Reports {
  totalExpectedProfit: number;
  avgRoi: number;
  cashRequired: number;
  inventoryByStatus: { status: string; count: number }[];
  categoryPerformance: GroupPerf[];
  sourcePerformance: GroupPerf[];
  auctionSuccessRate: number; // won / (won + lost)
  estimatedVsActual: { title: string; estimated: number; actual: number }[];
}

const num = (d: unknown) => (d == null ? 0 : Number(d));

export async function getReports(): Promise<Reports> {
  // Run all independent DB queries in parallel.
  const [
    costAgg,
    inventoryGroups,
    outcomeWonLost,
    recentOutcomes,
  ] = await Promise.all([
    // Top-line aggregates over all listings that have a costBreakdown.
    // These are intentionally all-time (not ACTIVE-scoped) so the report
    // reflects the full pipeline value, not just currently live items.
    prisma.costBreakdown.aggregate({
      _sum: { expectedNetProfit: true, recommendedMaxBid: true, roi: true },
      _avg: { roi: true },
      _count: { id: true },
    }),

    // inventoryByStatus must cover ALL statuses (ACTIVE/ENDED/REMOVED) —
    // scoping to ACTIVE would collapse all non-ACTIVE rows and lose the
    // breakdown that the reports page displays.
    prisma.listing.groupBy({
      by: ["researchStatus"],
      _count: { id: true },
    }),

    // Outcome counts for success-rate denominator (WON + LOST only).
    prisma.auctionOutcome.groupBy({
      by: ["status"],
      _count: { id: true },
    }),

    // estimatedVsActual: only outcomes where actualSale is set. Bounded at
    // 500 rows — the chart is not intended to render thousands of data points.
    prisma.auctionOutcome.findMany({
      where: { actualSale: { not: null } },
      include: { listing: { select: { title: true } } },
      orderBy: { createdAt: "desc" },
      take: 500,
    }),
  ]);

  // ---- top-line numbers ------------------------------------------------
  const totalExpectedProfit = num(costAgg._sum.expectedNetProfit);
  const cashRequired = num(costAgg._sum.recommendedMaxBid);
  const avgRoi = num(costAgg._avg.roi);

  // ---- inventory by research status ------------------------------------
  const inventoryByStatus = inventoryGroups.map((g) => ({
    status: g.researchStatus,
    count: g._count.id,
  }));

  // ---- category / source performance -----------------------------------
  // Prisma groupBy cannot traverse relations, so we cannot group CostBreakdown
  // by Listing.category/source in a single DB aggregation without a schema
  // change. Instead, fetch CostBreakdown rows with the needed Listing fields
  // (capped at 5 000) and aggregate in JS — same semantics as the original
  // but with a hard safety cap instead of an unbounded load.
  const costRows = await prisma.costBreakdown.findMany({
    select: {
      roi: true,
      expectedNetProfit: true,
      listing: { select: { category: true, source: true } },
    },
    take: 5000,
  });
  if (costRows.length === 5000) {
    // Not silent: category/source performance undercounts past the cap. Durable
    // fix is a raw-SQL GROUP BY across the Listing↔CostBreakdown relation.
    console.warn("[getReports] hit 5000-row cost cap; category/source performance may undercount");
  }

  const buildGroupPerf = (keyFn: (r: (typeof costRows)[number]) => string): GroupPerf[] => {
    const m = new Map<string, { count: number; roiSum: number; net: number }>();
    for (const r of costRows) {
      const k = keyFn(r) || "Uncategorized";
      const g = m.get(k) ?? { count: 0, roiSum: 0, net: 0 };
      g.count++;
      g.roiSum += num(r.roi);
      g.net += num(r.expectedNetProfit);
      m.set(k, g);
    }
    return [...m]
      .map(([key, g]) => ({ key, count: g.count, avgRoi: g.roiSum / g.count, totalNet: g.net }))
      .sort((a, b) => b.totalNet - a.totalNet);
  };

  const categoryPerformance = buildGroupPerf((r) => r.listing.category ?? "Uncategorized");
  const sourcePerformance = buildGroupPerf((r) => r.listing.source);

  // ---- auction success rate --------------------------------------------
  let won = 0;
  let lost = 0;
  for (const g of outcomeWonLost) {
    if (g.status === "WON") won = g._count.id;
    if (g.status === "LOST") lost = g._count.id;
  }
  const auctionSuccessRate = won + lost > 0 ? won / (won + lost) : 0;

  // ---- estimated vs actual --------------------------------------------
  const estimatedVsActual = recentOutcomes.map((o) => ({
    title: o.listing.title,
    estimated: num(o.maxBidSet),
    actual: num(o.actualSale),
  }));

  return {
    totalExpectedProfit,
    avgRoi,
    cashRequired,
    inventoryByStatus,
    categoryPerformance,
    sourcePerformance,
    auctionSuccessRate,
    estimatedVsActual,
  };
}