/**
 * Admin purchases overview — aggregate KPIs for the /admin/purchases overview tab.
 */

import { sql } from 'drizzle-orm';
import { z } from 'zod';
import type { DrizzleClient } from '@/server/db/client.js';

export interface PurchasesOverviewStats {
  /** Sum of amount_paid for COMPLETED purchases today (UTC). */
  revenueToday: string;
  /** Sum of amount_paid for COMPLETED purchases in the last 7 days. */
  revenueThisWeek: string;
  /** Open return requests (non-terminal statuses). */
  pendingReturns: number;
  /** Failed payment attempts created today (UTC). */
  failedPaymentsToday: number;
}

const purchasesRowSchema = z.object({
  revenueToday: z.coerce.string(),
  revenueThisWeek: z.coerce.string(),
  failedPaymentsToday: z.coerce.number(),
});
const returnsRowSchema = z.object({ pendingReturns: z.coerce.number() });

export async function getPurchasesOverviewStats(
  db: DrizzleClient,
): Promise<PurchasesOverviewStats> {
  const [purchasesResult, returnsResult] = await Promise.all([
    db.execute(sql`
      SELECT
        COALESCE(
          SUM(total) FILTER (
            WHERE status = 'paid'
              AND created_at >= date_trunc('day', NOW())
          ),
          0
        )::text AS "revenueToday",
        COALESCE(
          SUM(total) FILTER (
            WHERE status = 'paid'
              AND created_at >= NOW() - INTERVAL '7 days'
          ),
          0
        )::text AS "revenueThisWeek",
        COUNT(*) FILTER (
          WHERE status = 'failed'
            AND created_at >= date_trunc('day', NOW())
        )::int AS "failedPaymentsToday"
      FROM "order"
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS "pendingReturns"
      FROM returns
      WHERE status NOT IN ('refunded', 'denied', 'cancelled', 'expired')
    `),
  ]);

  const purchasesRow = purchasesRowSchema.parse(
    (purchasesResult as { rows: unknown[] }).rows[0] ?? {},
  );
  const returnsRow = returnsRowSchema.parse((returnsResult as { rows: unknown[] }).rows[0] ?? {});

  return {
    revenueToday: purchasesRow.revenueToday,
    revenueThisWeek: purchasesRow.revenueThisWeek,
    pendingReturns: returnsRow.pendingReturns,
    failedPaymentsToday: purchasesRow.failedPaymentsToday,
  };
}