/**
 * Admin affiliates overview — enrollment, referral, and fraud queue KPIs.
 */

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

export interface AffiliatesOverviewStats {
  activeEnrollments: number;
  pendingReferrals: number;
  quarantinedReferrals: number;
  unresolvedFraudFlags: number;
}

const enrollmentsRowSchema = z.object({ activeEnrollments: z.coerce.number() });
const referralsRowSchema = z.object({
  pendingReferrals: z.coerce.number(),
  quarantinedReferrals: z.coerce.number(),
});
const fraudRowSchema = z.object({ unresolvedFraudFlags: z.coerce.number() });

export async function getAffiliatesOverviewStats(
  db: DrizzleClient,
): Promise<AffiliatesOverviewStats> {
  const [enrollmentsResult, referralsResult, fraudResult] = await Promise.all([
    db.execute(sql`
      SELECT COUNT(*)::int AS "activeEnrollments"
      FROM affiliate_enrollments
      WHERE status = 'active'
    `),
    db.execute(sql`
      SELECT
        COUNT(*) FILTER (WHERE status = 'pending')::int AS "pendingReferrals",
        COUNT(*) FILTER (WHERE status = 'quarantined')::int AS "quarantinedReferrals"
      FROM referrals
      WHERE kind = 'affiliate'
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS "unresolvedFraudFlags"
      FROM fraud_events
      WHERE resolved_at IS NULL
        AND action = 'flag'
    `),
  ]);

  const enrollmentsRow = enrollmentsRowSchema.parse(
    (enrollmentsResult as { rows: unknown[] }).rows[0] ?? {},
  );
  const referralsRow = referralsRowSchema.parse(
    (referralsResult as { rows: unknown[] }).rows[0] ?? {},
  );
  const fraudRow = fraudRowSchema.parse((fraudResult as { rows: unknown[] }).rows[0] ?? {});

  return {
    activeEnrollments: enrollmentsRow.activeEnrollments,
    pendingReferrals: referralsRow.pendingReferrals,
    quarantinedReferrals: referralsRow.quarantinedReferrals,
    unresolvedFraudFlags: fraudRow.unresolvedFraudFlags,
  };
}