/**
 * Admin support overview — aggregate KPIs for support ticket and case queues.
 */

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

export interface SupportOverviewStats {
  openTickets: number;
  openCases: number;
  escalatedTickets: number;
  awaitingAgent: number;
}

const ticketsRowSchema = z.object({
  openTickets: z.coerce.number(),
  escalatedTickets: z.coerce.number(),
  awaitingAgent: z.coerce.number(),
});
const casesRowSchema = z.object({
  openCases: z.coerce.number(),
});

export async function getSupportOverviewStats(db: DrizzleClient): Promise<SupportOverviewStats> {
  const [ticketsResult, casesResult] = await Promise.all([
    db.execute(sql`
      SELECT
        COUNT(*) FILTER (WHERE status NOT IN ('resolved', 'closed'))::int AS "openTickets",
        COUNT(*) FILTER (WHERE status = 'escalated')::int AS "escalatedTickets",
        COUNT(*) FILTER (WHERE status IN ('awaiting_agent', 'human_handling'))::int AS "awaitingAgent"
      FROM support_tickets
    `),
    db.execute(sql`
      SELECT COUNT(*) FILTER (WHERE status NOT IN ('resolved', 'closed'))::int AS "openCases"
      FROM transaction_cases
    `),
  ]);

  const ticketsRow = ticketsRowSchema.parse((ticketsResult as { rows: unknown[] }).rows[0] ?? {});
  const casesRow = casesRowSchema.parse((casesResult as { rows: unknown[] }).rows[0] ?? {});

  return {
    openTickets: ticketsRow.openTickets,
    openCases: casesRow.openCases,
    escalatedTickets: ticketsRow.escalatedTickets,
    awaitingAgent: ticketsRow.awaitingAgent,
  };
}