/**
 * Unified approval queue — aggregates moderation targets from 5 sources:
 *   1. deals      — dealState = PENDING_APPROVAL
 *   2. images     — approvalStatus = PENDING
 *   3. reviews    — reviewRemovalQueue status = PENDING
 *   4. reports    — reportTickets status IN (OPEN, INVESTIGATING)
 *   5. llm_jobs   — status = PENDING
 *
 * Returns a unified item shape sorted by createdAt desc.
 * No raw SQL. No PII returned.
 */

import { eq, and, inArray, desc } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  deals,
  vendors,
  imageUploads,
  reviewRemovalQueue,
  reportTickets,
  llmJobs,
  users,
} from '@/server/db/schema.js';
import {
  CASCADE_REGISTRY,
  type CascadePreview,
} from '@/server/admin/resources/image-moderation/cascade.js';

// ─── Unified item shape ────────────────────────────────────────────────────────

export type ApprovalItemType = 'deal' | 'image' | 'review' | 'report' | 'job';

export interface UnifiedApprovalItem {
  id: string;
  type: ApprovalItemType;
  title: string;
  subtitle: string;
  /** URL for drill-down into type-specific moderation route. */
  moderationHref: string;
  createdAt: Date;
  /** For deal/image items: vendor business name (if available). */
  vendorName?: string;
  /** For report items: reporter user ID (PII-safe truncated for display). */
  reporterRef?: string;
  /** Preview image URL (r2Key path for images, deal imageUrl otherwise). */
  previewKey?: string;
  /** For image items: MIME type. */
  mime?: string;
  /** For image items: file size in bytes. */
  sizeBytes?: number;
  /** For image items: display name of uploader user (when uploaded by a customer). */
  uploaderName?: string;
  /** For image items: scan status. */
  scanStatus?: string;
  // ── AI moderation fields (image items only) ──────────────────────────────
  /** Upload purpose (e.g. 'vendor_logo', 'vendor_hero', 'avatar'). */
  purpose?: string;
  /** Entity type the image belongs to (e.g. 'vendor', 'user'). */
  entityType?: string | null;
  /** Admin deep-link for the entity. */
  entityRefHref?: string | null;
  /** Human-readable entity label. */
  entityRefName?: string | null;
  /** AI moderation decision that caused inbox surfacing. */
  aiDecision?: 'FLAG' | 'ERROR' | null;
  /** Human-readable AI reason / explanation. */
  aiReason?: string | null;
  /** AI confidence score 0–1. */
  aiScore?: number | null;
  /** Model identifier used for this moderation pass. */
  aiModel?: string | null;
  /** When AI processed the image. */
  aiCheckedAt?: Date | null;
  /** Raw AI response payload (opaque). */
  aiRawPayload?: unknown;
  /** Pre-computed cascade side-effect preview (server-side only). */
  cascadePreview?: CascadePreview | null;
}

// ─── Helpers ──────────────────────────────────────────────────────────────────

/** Resolve admin deep-link href by entity type + id. */
function entityRefHref(entityType: string | null, entityId: string | null): string | null {
  if (!entityType || !entityId) return null;
  switch (entityType) {
    case 'vendor':
      return `/admin/vendors/${entityId}`;
    case 'user':
      return `/admin/users/${entityId}`;
    case 'deal':
      return `/admin/deals/${entityId}`;
    default:
      return null;
  }
}

export interface ListApprovalQueueInput {
  type?: ApprovalItemType | 'all';
  limit?: number;
  offset?: number;
}

export interface ListApprovalQueueResult {
  items: UnifiedApprovalItem[];
  total: number;
}

// ─── Query ────────────────────────────────────────────────────────────────────

export async function listApprovalQueue(
  db: DrizzleClient,
  { type = 'all', limit = 50, offset = 0 }: ListApprovalQueueInput = {},
): Promise<ListApprovalQueueResult> {
  const buckets: UnifiedApprovalItem[][] = await Promise.all([
    // ── 1. Deals awaiting moderation (UNDER_REVIEW = LLM in-flight, PENDING_APPROVAL = human review) ──
    type === 'all' || type === 'deal'
      ? db
          .select({
            id: deals.id,
            title: deals.title,
            vendorId: deals.vendorId,
            createdAt: deals.createdAt,
          })
          .from(deals)
          .where(inArray(deals.dealState, ['UNDER_REVIEW', 'PENDING_APPROVAL']))
          .orderBy(desc(deals.createdAt))
          .then(async (rows) => {
            // Fetch vendor names in one batch
            const vendorIds = [...new Set(rows.map((r) => r.vendorId))];
            const vendorMap: Record<string, string> = {};
            if (vendorIds.length > 0) {
              const vs = await db
                .select({ id: vendors.id, businessName: vendors.businessName })
                .from(vendors)
                .where(inArray(vendors.id, vendorIds));
              for (const v of vs) vendorMap[v.id] = v.businessName;
            }
            return rows.map(
              (r): UnifiedApprovalItem => ({
                id: r.id,
                type: 'deal',
                title: r.title,
                subtitle: vendorMap[r.vendorId] ?? '',
                moderationHref: `/admin/deals/${r.id}`,
                createdAt: r.createdAt,
                vendorName: vendorMap[r.vendorId],
              }),
            );
          })
      : Promise.resolve([]),

    // ── 2. Images PENDING approval with AI FLAG or ERROR decision ─────────────
    type === 'all' || type === 'image'
      ? db
          .select({
            id: imageUploads.id,
            r2Key: imageUploads.r2Key,
            mime: imageUploads.mime,
            sizeBytes: imageUploads.sizeBytes,
            scanStatus: imageUploads.scanStatus,
            uploaderVendorId: imageUploads.uploaderVendorId,
            uploaderUserId: imageUploads.uploaderUserId,
            createdAt: imageUploads.createdAt,
            purpose: imageUploads.purpose,
            entityType: imageUploads.entityType,
            entityId: imageUploads.entityId,
            aiDecision: imageUploads.aiDecision,
            aiReason: imageUploads.aiReason,
            aiScore: imageUploads.aiScore,
            aiModel: imageUploads.aiModel,
            aiCheckedAt: imageUploads.aiCheckedAt,
            aiRawPayload: imageUploads.aiRawPayload,
          })
          .from(imageUploads)
          .where(
            // Surface only images that AI flagged or errored — pending human review
            and(
              eq(imageUploads.approvalStatus, 'PENDING'),
              inArray(imageUploads.aiDecision, ['FLAG', 'ERROR']),
            ),
          )
          .orderBy(desc(imageUploads.createdAt))
          .then(async (rows) => {
            const vendorIds = [
              ...new Set(rows.map((r) => r.uploaderVendorId).filter(Boolean) as string[]),
            ];
            const userIds = [
              ...new Set(rows.map((r) => r.uploaderUserId).filter(Boolean) as string[]),
            ];
            const vendorMap: Record<string, string> = {};
            const userMap: Record<string, string> = {};
            if (vendorIds.length > 0) {
              const vs = await db
                .select({ id: vendors.id, businessName: vendors.businessName })
                .from(vendors)
                .where(inArray(vendors.id, vendorIds));
              for (const v of vs) vendorMap[v.id] = v.businessName;
            }
            if (userIds.length > 0) {
              const us = await db
                .select({ id: users.id, displayName: users.displayName })
                .from(users)
                .where(inArray(users.id, userIds));
              for (const u of us) {
                if (u.displayName) userMap[u.id] = u.displayName;
              }
            }
            return rows.map((r, idx): UnifiedApprovalItem => {
              // Compute cascade preview server-side — never import registry client-side
              const purpose = r.purpose ?? '';
              const cascadeEntry = CASCADE_REGISTRY[purpose];
              const cascadePreview: CascadePreview | null = cascadeEntry
                ? cascadeEntry.preview({
                    imageId: r.id,
                    purpose,
                    entityType: r.entityType,
                    entityId: r.entityId,
                    reason: r.aiReason ?? 'unspecified',
                    uploaderUserId: r.uploaderUserId,
                    uploaderVendorId: r.uploaderVendorId,
                  })
                : null;

              const entityName =
                (r.entityType === 'vendor' && r.entityId && vendorMap[r.entityId]) ||
                (r.entityType === 'user' && r.entityId && userMap[r.entityId]) ||
                null;

              return {
                id: r.id,
                type: 'image',
                title: `תמונה ${idx + 1}`,
                subtitle: r.r2Key,
                moderationHref: `/admin/moderation/images?imageId=${r.id}`,
                createdAt: r.createdAt,
                vendorName: r.uploaderVendorId ? vendorMap[r.uploaderVendorId] : undefined,
                uploaderName: r.uploaderUserId ? userMap[r.uploaderUserId] : undefined,
                previewKey: r.r2Key,
                mime: r.mime ?? undefined,
                sizeBytes: r.sizeBytes ?? undefined,
                scanStatus: r.scanStatus ?? undefined,
                purpose,
                entityType: r.entityType,
                entityRefHref: entityRefHref(r.entityType, r.entityId),
                entityRefName: entityName || undefined,
                aiDecision: (r.aiDecision as 'FLAG' | 'ERROR' | null) ?? null,
                aiReason: r.aiReason,
                aiScore: r.aiScore ? Number(r.aiScore) : null,
                aiModel: r.aiModel,
                aiCheckedAt: r.aiCheckedAt,
                aiRawPayload: r.aiRawPayload,
                cascadePreview,
              };
            });
          })
      : Promise.resolve([]),

    // ── 3. Review removals PENDING ─────────────────────────────────────────
    type === 'all' || type === 'review'
      ? db
          .select({
            id: reviewRemovalQueue.id,
            reviewId: reviewRemovalQueue.reviewId,
            reason: reviewRemovalQueue.reason,
            submittedBy: reviewRemovalQueue.submittedBy,
            createdAt: reviewRemovalQueue.createdAt,
          })
          .from(reviewRemovalQueue)
          .where(eq(reviewRemovalQueue.status, 'PENDING'))
          .orderBy(desc(reviewRemovalQueue.createdAt))
          .then((rows) =>
            rows.map(
              (r): UnifiedApprovalItem => ({
                id: r.id,
                type: 'review',
                title: `הסרת ביקורת`,
                subtitle: r.reason,
                moderationHref: `/admin/moderation/reviews/${r.id}`,
                createdAt: r.createdAt,
              }),
            ),
          )
      : Promise.resolve([]),

    // ── 4. Report tickets OPEN or INVESTIGATING ────────────────────────────
    type === 'all' || type === 'report'
      ? db
          .select({
            id: reportTickets.id,
            targetType: reportTickets.targetType,
            targetId: reportTickets.targetId,
            reason: reportTickets.reason,
            reporterUserId: reportTickets.reporterUserId,
            createdAt: reportTickets.createdAt,
          })
          .from(reportTickets)
          .where(inArray(reportTickets.status, ['OPEN', 'INVESTIGATING']))
          .orderBy(desc(reportTickets.createdAt))
          .then((rows) =>
            rows.map(
              (r): UnifiedApprovalItem => ({
                id: r.id,
                type: 'report',
                title: `target:${r.targetType}`,
                subtitle: `reason:${r.reason}`,
                moderationHref: `/admin/moderation/reports/${r.id}`,
                createdAt: r.createdAt,
                // Truncate reporter ID — no PII
                reporterRef: r.reporterUserId ? r.reporterUserId.slice(0, 8) : undefined,
              }),
            ),
          )
      : Promise.resolve([]),

    // ── 5. LLM jobs PENDING ────────────────────────────────────────────────
    type === 'all' || type === 'job'
      ? db
          .select({
            id: llmJobs.id,
            jobType: llmJobs.jobType,
            targetId: llmJobs.targetId,
            targetType: llmJobs.targetType,
            createdAt: llmJobs.createdAt,
          })
          .from(llmJobs)
          .where(eq(llmJobs.status, 'PENDING'))
          .orderBy(desc(llmJobs.createdAt))
          .then((rows) =>
            rows.map(
              (r): UnifiedApprovalItem => ({
                id: r.id,
                type: 'job',
                title: `משימת AI: ${r.jobType}`,
                subtitle: `${r.targetType} — ${r.targetId.slice(0, 8)}`,
                moderationHref: `/admin/moderation/jobs/${r.id}`,
                createdAt: r.createdAt,
              }),
            ),
          )
      : Promise.resolve([]),
  ]);

  // Merge + sort by createdAt desc
  const all = buckets.flat().sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime());
  const total = all.length;
  const items = all.slice(offset, offset + limit);

  return { items, total };
}