/**
 * Admin review-moderation resource — read-only queries.
 *
 * - listRemovals: queue entries (PENDING by default, filterable by status)
 *   with review, reviewer, deal, and vendor context.
 */

import { eq, sql, count } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  reviewRemovalQueue,
  reviews,
  vendors,
  users,
  deals,
  vendorTranslations,
} from '@/server/db/schema.js';
import { VENDOR_DEFAULT_LOCALE } from '@/server/db/queries/vendors.js';
import type { PendingRemovalEntry, RemovalStatus } from './types.js';

// ─── listRemovals ─────────────────────────────────────────────────────────────

export async function listRemovals(
  db: DrizzleClient,
  options: { limit: number; offset?: number; status?: RemovalStatus },
): Promise<{ data: PendingRemovalEntry[]; total: number }> {
  const statusFilter = options.status ?? 'PENDING';
  const offset = options.offset ?? 0;

  const whereClause = eq(reviewRemovalQueue.status, statusFilter);

  const [rows, [countRow]] = await Promise.all([
    db
      .select({
        id: reviewRemovalQueue.id,
        reviewId: reviewRemovalQueue.reviewId,
        reviewBody: reviews.body,
        reviewRating: reviews.rating,
        reviewCreatedAt: reviews.createdAt,
        reviewerName: sql<
          string | null
        >`(SELECT vt2.display_name FROM ${vendorTranslations} vt2 INNER JOIN vendors v2 ON v2.id = vt2.vendor_id AND v2.owner_user_id = ${sql.raw('"users"."id"')} WHERE vt2.locale = ${VENDOR_DEFAULT_LOCALE} LIMIT 1)`,
        vendorId: reviews.vendorId,
        vendorName: vendors.displayName,
        vendorRemovedCount: vendors.removedReviewsCount,
        dealTitle: deals.title,
        dealType: deals.dealType,
        removalReason: reviewRemovalQueue.reason,
        aiScore: reviewRemovalQueue.aiScore,
        status: reviewRemovalQueue.status,
        decidedAt: reviewRemovalQueue.decidedAt,
        createdAt: reviewRemovalQueue.createdAt,
      })
      .from(reviewRemovalQueue)
      .innerJoin(reviews, eq(reviewRemovalQueue.reviewId, reviews.id))
      .innerJoin(users, eq(reviews.userId, users.id))
      .innerJoin(vendors, eq(reviews.vendorId, vendors.id))
      .innerJoin(deals, eq(reviews.dealId, deals.id))
      .where(whereClause)
      .limit(options.limit)
      .offset(offset),
    db.select({ count: count() }).from(reviewRemovalQueue).where(whereClause),
  ]);

  return { data: rows as PendingRemovalEntry[], total: countRow?.count ?? 0 };
}