/**
 * Admin moderation overview — aggregate KPIs across deals, reviews, reports, and images.
 */

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

export interface ModerationOverviewStats {
  pendingDeals: number;
  pendingReviews: number;
  openReports: number;
  pendingImages: number;
}

const dealsRowSchema = z.object({ pendingDeals: z.coerce.number() });
const reviewsRowSchema = z.object({ pendingReviews: z.coerce.number() });
const reportsRowSchema = z.object({ openReports: z.coerce.number() });
const imagesRowSchema = z.object({ pendingImages: z.coerce.number() });

export async function getModerationOverviewStats(
  db: DrizzleClient,
): Promise<ModerationOverviewStats> {
  const [dealsResult, reviewsResult, reportsResult, imagesResult] = await Promise.all([
    db.execute(sql`
      SELECT COUNT(*)::int AS "pendingDeals"
      FROM deals
      WHERE deal_state = 'PENDING_APPROVAL'
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS "pendingReviews"
      FROM review_removal_queue
      WHERE status = 'PENDING'
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS "openReports"
      FROM report_tickets
      WHERE status IN ('OPEN', 'INVESTIGATING')
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS "pendingImages"
      FROM image_uploads
      WHERE approval_status = 'PENDING'
    `),
  ]);

  const dealsRow = dealsRowSchema.parse((dealsResult as { rows: unknown[] }).rows[0] ?? {});
  const reviewsRow = reviewsRowSchema.parse((reviewsResult as { rows: unknown[] }).rows[0] ?? {});
  const reportsRow = reportsRowSchema.parse((reportsResult as { rows: unknown[] }).rows[0] ?? {});
  const imagesRow = imagesRowSchema.parse((imagesResult as { rows: unknown[] }).rows[0] ?? {});

  return {
    pendingDeals: dealsRow.pendingDeals,
    pendingReviews: reviewsRow.pendingReviews,
    openReports: reportsRow.openReports,
    pendingImages: imagesRow.pendingImages,
  };
}