/**
 * Admin purchases resource — read-only queries.
 *
 * - listPurchases: paginated, filterable order-line list enriched with deal
 *   title and vendor name. Returns { data, total } for pagination.
 * - getPurchaseDetail: fetches a single order-line's full record plus the
 *   purchasing user (nullable for guest), vendor, deal snapshot, and review.
 *
 * T7: purchases table removed. Queries now join orderLine → order.
 *   - paymentStatus filter maps to order.status
 *   - redemptionStatus filter maps to aggregated voucher.state
 *   - amountPaid = orderLine.lineTotal / 100 (agorot → shekels string)
 *   - commissionAmount / vendorAmount: not available in new model (return '0')
 *   - cancelledAt: not available in new model (return null)
 *   - providerPaymentId = order.chargeRef
 *   - qrPngUrl = orderLineVoucherExt.qrPngUrl (first unit)
 */

import { eq, and, desc, count, gte, lte, sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  users,
  vendors,
  deals,
  reviews,
  vendorTranslations,
  orderLine,
  orderLineVoucherExt,
  dealSkus,
} from '@/server/db/schema.js';
import { order, type OrderStatus } from '@platform-modules/commerce-orders';
import { VENDOR_DEFAULT_LOCALE } from '@/server/db/queries/vendors.js';
import {
  lineRedemptionStatusSql,
  lineRedeemedAtSql,
  lineExpiresAtSql,
} from '@/server/fulfillment/voucher-line-state.js';
import { moduleRefAsUuid } from '@/server/platform-seams/ids.js';
import type { ListPurchasesFilter, ListPurchasesResult, PurchaseDetail } from './types.js';
import { formatAgorotPlain } from '@/lib/money';

// ─── listPurchases ────────────────────────────────────────────────────────────

export async function listPurchases(
  db: DrizzleClient,
  filter: ListPurchasesFilter,
): Promise<ListPurchasesResult> {
  const conditions = [];

  if (filter.paymentStatus) {
    // Map old uppercase paymentStatus values to new lowercase order.status
    const statusMap: Record<string, string> = {
      PENDING: 'pending',
      COMPLETED: 'completed',
      REFUNDED: 'refunded',
      PARTIAL_REFUND: 'partially_refunded',
      REFUND_REQUESTED: 'refunded',
    };
    const mapped = (statusMap[filter.paymentStatus] ??
      filter.paymentStatus.toLowerCase()) as OrderStatus;
    conditions.push(eq(order.status, mapped));
  }
  if (filter.redemptionStatus) {
    conditions.push(sql`${lineRedemptionStatusSql(orderLine.id)} = ${filter.redemptionStatus}`);
  }
  if (filter.vendorId) {
    conditions.push(eq(orderLine.vendorId, filter.vendorId));
  }
  if (filter.from) {
    conditions.push(gte(orderLine.createdAt, new Date(filter.from)));
  }
  if (filter.to) {
    // Upper bound: include the entire "to" day
    const toDate = new Date(filter.to);
    toDate.setDate(toDate.getDate() + 1);
    conditions.push(lte(orderLine.createdAt, toDate));
  }

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

  const [rows, countRows] = await Promise.all([
    db
      .select({
        id: orderLine.id,
        dealTitle: deals.title,
        vendorName: vendors.displayName,
        customerDisplayName: sql<
          string | null
        >`CASE WHEN ${order.buyerUserId} IS NOT NULL THEN 'Customer #' || LEFT(${order.buyerUserId}::text, 8) ELSE NULL END`,
        lineTotalAgorot: orderLine.lineTotal,
        commissionAmount: sql<string>`'0'`,
        vendorAmount: sql<string>`'0'`,
        paymentStatus: order.status,
        redemptionStatus: lineRedemptionStatusSql(orderLine.id),
        dealId: dealSkus.dealId,
        vendorId: orderLine.vendorId,
        createdAt: orderLine.createdAt,
        redeemedAt: lineRedeemedAtSql(orderLine.id),
        cancelledAt: sql<null>`NULL`,
        expiresAt: lineExpiresAtSql(orderLine.id),
      })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .innerJoin(dealSkus, eq(orderLine.variantId, dealSkus.id))
      .innerJoin(deals, eq(dealSkus.dealId, deals.id))
      .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${orderLine.vendorId}`)} = ${vendors.id}`)
      .where(whereClause)
      .orderBy(desc(orderLine.createdAt))
      .limit(filter.limit)
      .offset(filter.offset),

    db
      .select({ total: count() })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .where(whereClause),
  ]);

  return {
    data: rows.map((row) => ({
      id: row.id,
      dealTitle: row.dealTitle,
      vendorName: row.vendorName,
      customerDisplayName: row.customerDisplayName,
      amountPaid: formatAgorotPlain(Number(row.lineTotalAgorot)),
      commissionAmount: row.commissionAmount,
      vendorAmount: row.vendorAmount,
      paymentStatus: row.paymentStatus,
      redemptionStatus: row.redemptionStatus,
      dealId: row.dealId,
      vendorId: row.vendorId,
      createdAt: row.createdAt,
      redeemedAt: row.redeemedAt,
      cancelledAt: row.cancelledAt,
      expiresAt: row.expiresAt,
    })) as ListPurchasesResult['data'],
    total: countRows[0]?.total ?? 0,
  };
}

// ─── getPurchaseDetail ────────────────────────────────────────────────────────

export async function getPurchaseDetail(
  db: DrizzleClient,
  purchaseId: string,
): Promise<PurchaseDetail | null> {
  // Main order-line row (purchaseId = orderLine.id)
  const [purchase] = await db
    .select({
      id: orderLine.id,
      userId: order.buyerUserId,
      dealId: dealSkus.dealId,
      vendorId: orderLine.vendorId,
      quantity: orderLine.qty,
      lineTotalAgorot: orderLine.lineTotal,
      commissionAmount: sql<string>`'0'`,
      vendorAmount: sql<string>`'0'`,
      paymentStatus: order.status,
      redemptionStatus: lineRedemptionStatusSql(orderLine.id),
      providerPaymentId: order.chargeRef,
      qrPngUrl: sql<string | null>`(
        SELECT ${orderLineVoucherExt.qrPngUrl}
        FROM ${orderLineVoucherExt}
        WHERE ${orderLineVoucherExt.lineId} = ${orderLine.id}::text
        ORDER BY ${orderLineVoucherExt.voucherId}
        LIMIT 1
      )`,
      createdAt: orderLine.createdAt,
      redeemedAt: lineRedeemedAtSql(orderLine.id),
      cancelledAt: sql<null>`NULL`,
      expiresAt: lineExpiresAtSql(orderLine.id),
    })
    .from(orderLine)
    .innerJoin(order, eq(orderLine.orderId, order.id))
    .innerJoin(dealSkus, eq(orderLine.variantId, dealSkus.id))
    .where(eq(orderLine.id, purchaseId))
    .limit(1);

  if (!purchase) return null;

  // Parallel supporting queries
  const [vendorRows, dealRows, userRows, reviewRows] = await Promise.all([
    db
      .select({
        id: vendors.id,
        displayName: vendors.displayName,
        businessName: vendors.businessName,
        accountState: vendors.accountState,
      })
      .from(vendors)
      .where(sql`${vendors.id}::text = ${purchase.vendorId}`)
      .limit(1),

    db
      .select({
        id: deals.id,
        title: deals.title,
        dealType: deals.dealType,
        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)`,
        windowStart: deals.windowStart,
        windowEnd: deals.windowEnd,
        pickupAddress: deals.pickupAddress,
      })
      .from(deals)
      .where(eq(deals.id, purchase.dealId as string))
      .limit(1),

    purchase.userId
      ? db
          .select({
            id: users.id,
            displayName: 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)`,
            accountState: users.accountState,
            isAdmin: users.isAdmin,
            createdAt: users.createdAt,
          })
          .from(users)
          .where(eq(users.id, purchase.userId))
          .limit(1)
      : Promise.resolve([]),

    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.orderLineId, purchaseId))
      .limit(1),
  ]);

  const vendor = vendorRows[0];
  const deal = dealRows[0];

  if (!vendor || !deal) return null;

  return {
    purchase: {
      ...(purchase as Omit<typeof purchase, 'cancelledAt' | 'expiresAt' | 'lineTotalAgorot'> & {
        cancelledAt: null;
        expiresAt: Date;
      }),
      amountPaid: formatAgorotPlain(Number(purchase.lineTotalAgorot)),
      isGuest: purchase.userId === null,
    },
    customer: userRows[0] ?? null,
    vendor,
    deal: deal as PurchaseDetail['deal'],
    review: reviewRows[0] ?? null,
  };
}