/**
 * Admin deal detail query.
 *
 * getDealDetail: returns all data needed by the /admin/deals/[id] detail page,
 * using parallel Promise.all for performance.
 *
 * PII is never logged.
 */

import { eq, desc, and, sql } from 'drizzle-orm';
import { alias } from 'drizzle-orm/pg-core';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  deals,
  vendors,
  llmJobs,
  dealImages,
  reviews,
  dealCategories,
  categoryTranslations,
  orderLine,
  dealSkus,
} from '@/server/db/schema.js';
import { order } from '@platform-modules/commerce-orders';
import {
  lineRedemptionStatusSql,
  lineRedeemedAtSql,
} from '@/server/fulfillment/voucher-line-state.js';
import { getDealTagsWithNames } from '@/server/db/queries/tags.js';
import { formatAgorotPlain } from '@/lib/money';

// ─── Types ────────────────────────────────────────────────────────────────────

export interface DealDetailData {
  id: string;
  vendorId: string;
  vendorDisplayName: string;
  vendorBusinessName: string;
  dealType: string;
  title: string;
  description: string;
  categoryId: string | null;
  categoryNameHe: string | null;
  categoryNameEn: string | null;
  tags: { id: string; nameHe: string; nameEn: string }[];
  originalPrice: string;
  discountPercent: number;
  discountedPrice: string;
  quantityTotal: number;
  quantitySold: number;
  windowStart: Date | null;
  windowEnd: Date | null;
  commissionRate: string;
  isPersonalDeal: boolean;
  pickupAddress: string | null;
  specialInstructions: string | null;
  dealState: string;
  rejectionReason: string | null;
  rejectionDetail: string | null;
  appealStatus: string | null;
  appealReason: string | null;
  createdAt: Date;
}

export interface DealLlmJobRow {
  id: string;
  jobType: string;
  status: string;
  decision: string | null;
  flagReason: string | null;
  modelName: string | null;
  totalTokens: number | null;
  createdAt: Date;
}

export interface DealPurchaseRow {
  id: string;
  paymentStatus: string;
  redemptionStatus: string | null;
  amountPaid: string;
  createdAt: Date;
  redeemedAt: Date | null;
}

export interface DealImageRow {
  id: string;
  url: string;
  isPrimary: boolean;
  sortOrder: number;
  approvalStatus: string;
}

export interface DealReviewRow {
  id: string;
  rating: number | null;
  body: string;
  reviewType: string;
  isVisible: boolean;
  createdAt: Date;
}

export interface DealDetailResult {
  deal: DealDetailData;
  llmJobs: DealLlmJobRow[];
  purchases: DealPurchaseRow[];
  images: DealImageRow[];
  reviews: DealReviewRow[];
}

// ─── getDealDetail ────────────────────────────────────────────────────────────

export async function getDealDetail(
  db: DrizzleClient,
  dealId: string,
): Promise<DealDetailResult | null> {
  // Fetch core deal + vendor in one join. Category names join from sidecar
  // (`category_translations` he/en, fallback to he row).
  const ctHe = alias(categoryTranslations, 'ct_he');
  const ctEn = alias(categoryTranslations, 'ct_en');
  const ctHeFb = alias(categoryTranslations, 'ct_he_fb');

  const [dealRow] = await db
    .select({
      id: deals.id,
      vendorId: deals.vendorId,
      vendorDisplayName: vendors.displayName,
      vendorBusinessName: vendors.businessName,
      dealType: deals.dealType,
      title: deals.title,
      description: deals.description,
      categoryId: deals.categoryId,
      categoryNameHe: sql<string | null>`COALESCE(${ctHe.name}, ${ctHeFb.name})`,
      categoryNameEn: sql<string | null>`COALESCE(${ctEn.name}, ${ctHeFb.name})`,
      originalPrice: sql<string>`(SELECT original_price FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
      discountPercent: sql<number>`(SELECT discount_percent FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
      discountedPrice: sql<string>`(SELECT discounted_price FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
      quantityTotal: sql<number>`(SELECT quantity_total FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
      quantitySold: sql<number>`(SELECT quantity_sold FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
      windowStart: deals.windowStart,
      windowEnd: deals.windowEnd,
      commissionRate: deals.commissionRate,
      isPersonalDeal: deals.isPersonalDeal,
      pickupAddress: deals.pickupAddress,
      specialInstructions: deals.specialInstructions,
      dealState: deals.dealState,
      rejectionReason: deals.rejectionReason,
      rejectionDetail: deals.rejectionDetail,
      appealStatus: deals.appealStatus,
      appealReason: deals.appealReason,
      createdAt: deals.createdAt,
    })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(dealCategories, eq(deals.categoryId, dealCategories.id))
    .leftJoin(ctHe, and(eq(ctHe.categoryId, dealCategories.id), eq(ctHe.locale, 'he')))
    .leftJoin(ctEn, and(eq(ctEn.categoryId, dealCategories.id), eq(ctEn.locale, 'en')))
    .leftJoin(ctHeFb, and(eq(ctHeFb.categoryId, dealCategories.id), eq(ctHeFb.locale, 'he')))
    .where(eq(deals.id, dealId))
    .limit(1);

  if (!dealRow) return null;

  // Fetch related data in parallel
  const [llmJobRows, purchaseRows, imageRows, reviewRows, tagRows] = await Promise.all([
    db
      .select({
        id: llmJobs.id,
        jobType: llmJobs.jobType,
        status: llmJobs.status,
        decision: llmJobs.decision,
        flagReason: llmJobs.flagReason,
        modelName: llmJobs.modelName,
        totalTokens: llmJobs.totalTokens,
        createdAt: llmJobs.createdAt,
      })
      .from(llmJobs)
      .where(eq(llmJobs.targetId, dealId))
      .orderBy(desc(llmJobs.createdAt))
      .limit(50),

    db
      .select({
        id: orderLine.id,
        paymentStatus: order.status,
        redemptionStatus: lineRedemptionStatusSql(orderLine.id),
        lineTotalAgorot: orderLine.lineTotal,
        createdAt: orderLine.createdAt,
        redeemedAt: lineRedeemedAtSql(orderLine.id),
      })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .innerJoin(dealSkus, eq(orderLine.variantId, dealSkus.id))
      .where(eq(dealSkus.dealId, dealId))
      .orderBy(desc(orderLine.createdAt))
      .limit(50),

    db
      .select({
        id: dealImages.id,
        url: dealImages.url,
        isPrimary: dealImages.isPrimary,
        sortOrder: dealImages.sortOrder,
        approvalStatus: dealImages.approvalStatus,
      })
      .from(dealImages)
      .where(eq(dealImages.dealId, dealId))
      .orderBy(dealImages.sortOrder),

    // reviews.dealId is now a direct NOT NULL column (T7 migration)
    db
      .select({
        id: reviews.id,
        rating: reviews.rating,
        body: reviews.body,
        reviewType: reviews.reviewType,
        isVisible: reviews.isVisible,
        createdAt: reviews.createdAt,
      })
      .from(reviews)
      .where(eq(reviews.dealId, dealId))
      .orderBy(desc(reviews.createdAt))
      .limit(50),

    getDealTagsWithNames(db, dealId),
  ]);

  return {
    deal: { ...dealRow, tags: tagRows },
    llmJobs: llmJobRows,
    purchases: purchaseRows.map((row) => ({
      id: row.id,
      paymentStatus: row.paymentStatus,
      redemptionStatus: row.redemptionStatus,
      amountPaid: formatAgorotPlain(Number(row.lineTotalAgorot)),
      createdAt: row.createdAt,
      redeemedAt: row.redeemedAt,
    })),
    images: imageRows,
    reviews: reviewRows,
  };
}