/**
 * Admin earnings overview — aggregate payout/settlement KPIs.
 */

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

export interface EarningsOverviewStats {
  pendingPayouts: number;
  pendingAmountAgorot: number;
  paidThisMonth: number;
  disputedPayouts: number;
}

const overviewRowSchema = z.object({
  pendingPayouts: z.coerce.number(),
  pendingAmountAgorot: z.coerce.number(),
  paidThisMonth: z.coerce.number(),
  disputedPayouts: z.coerce.number(),
});

export async function getEarningsOverviewStats(db: DrizzleClient): Promise<EarningsOverviewStats> {
  const result = await db.execute(sql`
    SELECT
      COUNT(*) FILTER (WHERE status = 'pending')::int AS "pendingPayouts",
      COALESCE(SUM(amount_agorot) FILTER (WHERE status = 'pending'), 0)::int AS "pendingAmountAgorot",
      COUNT(*) FILTER (
        WHERE status = 'paid'
          AND marked_paid_at >= date_trunc('month', NOW())
      )::int AS "paidThisMonth",
      COUNT(*) FILTER (WHERE status = 'disputed')::int AS "disputedPayouts"
    FROM payouts
  `);

  const row = overviewRowSchema.parse((result as { rows: unknown[] }).rows[0] ?? {});

  return {
    pendingPayouts: row.pendingPayouts,
    pendingAmountAgorot: row.pendingAmountAgorot,
    paidThisMonth: row.paidThisMonth,
    disputedPayouts: row.disputedPayouts,
  };
}