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

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

export interface DealsOverviewStats {
  totalActive: number;
  expiringIn48h: number;
  lowStock: number;
  pendingApproval: number;
}

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

export async function getDealsOverviewStats(db: DrizzleClient): Promise<DealsOverviewStats> {
  const result = await db.execute(sql`
    SELECT
      COUNT(*) FILTER (WHERE deal_state = 'ACTIVE')::int AS "totalActive",
      COUNT(*) FILTER (
        WHERE deal_state = 'ACTIVE'
          AND window_end IS NOT NULL
          AND window_end > NOW()
          AND window_end <= NOW() + INTERVAL '48 hours'
      )::int AS "expiringIn48h",
      COUNT(*) FILTER (
        WHERE deal_state = 'ACTIVE'
          AND stock_remaining IS NOT NULL
          AND stock_remaining < 5
      )::int AS "lowStock",
      COUNT(*) FILTER (WHERE deal_state = 'PENDING_APPROVAL')::int AS "pendingApproval"
    FROM deals
  `);

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

  return {
    totalActive: row.totalActive,
    expiringIn48h: row.expiringIn48h,
    lowStock: row.lowStock,
    pendingApproval: row.pendingApproval,
  };
}