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

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

export interface VendorsOverviewStats {
  totalActive: number;
  pendingApproval: number;
  suspended: number;
  newThisWeek: number;
}

const overviewRowSchema = z.object({
  totalActive: z.coerce.number(),
  pendingApproval: z.coerce.number(),
  suspended: z.coerce.number(),
  newThisWeek: z.coerce.number(),
});

export async function getVendorsOverviewStats(db: DrizzleClient): Promise<VendorsOverviewStats> {
  const result = await db.execute(sql`
    SELECT
      COUNT(*) FILTER (WHERE account_state IN ('ACTIVE', 'VETERAN'))::int AS "totalActive",
      COUNT(*) FILTER (WHERE account_state IN ('PENDING_FIRST_APPROVAL', 'PENDING_PROCESSOR'))::int AS "pendingApproval",
      COUNT(*) FILTER (WHERE account_state IN ('FROZEN', 'BANNED'))::int AS "suspended",
      COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days')::int AS "newThisWeek"
    FROM vendors
  `);

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

  return {
    totalActive: row.totalActive,
    pendingApproval: row.pendingApproval,
    suspended: row.suspended,
    newThisWeek: row.newThisWeek,
  };
}