import { eq, and, count } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { reportTickets, deals, dealTranslations } from '@/server/db/schema.js';
import type { ListReportsFilter, ReportTicketRow } from './types.js';

export async function listReports(
  db: DrizzleClient,
  filter: ListReportsFilter,
): Promise<{ data: ReportTicketRow[]; total: number }> {
  const conditions = [];

  if (filter.status) {
    conditions.push(eq(reportTickets.status, filter.status));
  }
  if (filter.targetType) {
    conditions.push(eq(reportTickets.targetType, filter.targetType));
  }

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  const [rows, countResult] = await Promise.all([
    db
      .select({
        id: reportTickets.id,
        reporterUserId: reportTickets.reporterUserId,
        targetType: reportTickets.targetType,
        targetId: reportTickets.targetId,
        reason: reportTickets.reason,
        body: reportTickets.body,
        status: reportTickets.status,
        createdAt: reportTickets.createdAt,
        resolvedAt: reportTickets.resolvedAt,
        heSlug: dealTranslations.slug,
      })
      .from(reportTickets)
      .leftJoin(deals, eq(deals.id, reportTickets.targetId))
      .leftJoin(dealTranslations, and(
        eq(dealTranslations.dealId, deals.id),
        eq(dealTranslations.locale, 'he'),
      ))
      .where(whereClause)
      .limit(filter.limit)
      .offset(filter.offset),
    db.select({ count: count() }).from(reportTickets).where(whereClause),
  ]);

  const countRow = countResult[0];
  return { data: rows, total: countRow?.count ?? 0 };
}