/**
 * Purchase queries — T7 cutover.
 *
 * The purchases table has been removed. All reads and writes now target:
 *   order / orderLine / voucher (+ orderLineVoucherExt for QR)  (+ dealSkus, deals, vendors for joins)
 *
 * getPurchaseByProviderPaymentId now returns { id } | null instead of the full
 * purchases row — callers only read .id.
 *
 * redeem returns null.
 *
 * Refund claims are REAL: backed by the commerce-orders refund_intent table.
 *   claimRefundIntent / Tx  — atomic pending-intent insert (order FOR UPDATE,
 *                             over-refund + concurrent-claim guards)
 *   setPurchaseRefund       — settle: pending → executed + order status recompute
 *   releaseRefundClaim      — pending → failed
 */

import { eq, and, desc, sql, inArray } from 'drizzle-orm';
import { formatAgorotPlain } from '@/lib/money.js';
import type { DrizzleClient, TxDrizzleClient } from '../client.js';
import { moduleRefAsUuid } from '@/server/platform-seams/ids.js';
import {
  order,
  orderLine,
  orderLineVoucherExt,
  orderStep,
  refundIntent,
  refundIntentLineExt,
  deals,
  vendors,
  dealImages,
  outbox,
  users,
  dealSkus,
  shipmentPurchases,
  shipments,
} from '../schema.js';
import {
  lineRedemptionStatusSql,
  lineRedeemedAtSql,
  lineExpiresAtSql,
  lineAllVouchersTerminalSql,
  loadVouchersForLine,
  loadVouchersForLines,
  type LineVoucherSummary,
} from '@/server/fulfillment/voucher-line-state.js';
import { failOrder, recordStep, type OrdersSchema } from '@platform-modules/commerce-orders';
import type { Transaction } from '@platform-modules/db';
import { voucher } from '@platform-modules/commerce-fulfillment';
import { insertOutboxRow } from './outbox.js';

// ─── Error / input types (callers import from here) ──────────────────────────

export class AlreadyRefundedError extends Error {
  constructor(public readonly purchaseId: string) {
    super(`Purchase ${purchaseId} already has a claimed refund intent`);
    this.name = 'AlreadyRefundedError';
  }
}

export interface ClaimRefundIntentInput {
  purchaseId: string;
  refundAmountAgorot: number;
  refundType: string;
  initiatedBy: string;
  refundKey?: string;
  note?: string;
}

export interface RefundPurchaseInput {
  purchaseId: string;
  adminActionId?: string;
  amount?: number;
}

export interface ConfirmCheckoutInput {
  /** orderLine.id (was purchases.id) */
  purchaseId: string;
  userId?: string;
  dealId?: string;
  referralCode?: string;
}

// ─── Vendor-payment context ───────────────────────────────────────────────────

export interface PurchasePaymentContext {
  purchaseId: string;
  userId: string | null;
  quantity: number;
  totalAgorot: number;
  buyer: { name: string; email?: string };
  vendor: {
    id: string;
    stripeAccountId: string | null;
    chargesEnabled: boolean;
  };
  deal: {
    id: string;
    title: string;
    dealSkuId: string | null;
    dealType: string | null;
  };
}

// ─── Enriched purchase shapes ─────────────────────────────────────────────────

export type EnrichedPurchaseDetail = {
  id: string;
  userId: string | null;
  dealId: string;
  vendorId: string;
  dealTitle: string;
  vendorName: string;
  amountPaid: string;
  quantity: number;
  redemptionStatus: string;
  paymentStatus: string;
  expiresAt: Date;
  redeemedAt: Date | null;
  createdAt: Date;
  qrPngUrl: string | null;
  dealType: string | null;
  isPhysical: boolean;
  isReturnable: boolean;
  returnWindowDays: number;
  /** Always null — deliveredAt column removed in T7. */
  deliveredAt: Date | null;
  /** One entry per purchased unit (qty>1 lines have >1), ordered by unitIndex ASC. */
  vouchers: LineVoucherSummary[];
};

export type EnrichedPurchase = {
  id: string;
  userId: string | null;
  dealId: string;
  vendorId: string;
  dealTitle: string;
  vendorName: string;
  vendorLogo: string | null;
  dealImageUrl: string | null;
  amountPaid: string;
  quantity: number;
  redemptionStatus: string;
  paymentStatus: string;
  expiresAt: Date;
  redeemedAt: Date | null;
  createdAt: Date;
  qrPngUrl: string | null;
  dealType: string | null;
  /** One entry per purchased unit (qty>1 lines have >1), ordered by unitIndex ASC. */
  vouchers: LineVoucherSummary[];
};

export type ListByUserResult = {
  active: EnrichedPurchase[];
  history: EnrichedPurchase[];
  stats: {
    totalSpent: string;
    totalSavings: string;
    businessCount: number;
  };
};

function normalizeNullableDate(value: Date | string | number | null): Date | null {
  if (value == null) return null;
  const date = value instanceof Date ? value : new Date(value);
  if (Number.isNaN(date.getTime())) throw new Error('purchase query returned an invalid timestamp');
  return date;
}

// ─── findById ─────────────────────────────────────────────────────────────────

/**
 * Minimal purchase row — callers read .userId, .dealId, .vendorId,
 * .createdAt, .amountPaid, .quantity, .paymentStatus, .redemptionStatus.
 */
export async function findById(db: DrizzleClient, id: string) {
  const [row] = await db
    .select({
      id: orderLine.id,
      userId: order.buyerUserId,
      dealId: dealSkus.dealId,
      dealSkuId: orderLine.variantId,
      vendorId: orderLine.vendorId,
      quantity: orderLine.qty,
      lineTotal: orderLine.lineTotal,
      createdAt: orderLine.createdAt,
      orderStatus: order.status,
      qrTokenHash: sql<string | null>`(
        SELECT ${orderLineVoucherExt.qrTokenHash}
        FROM ${orderLineVoucherExt}
        WHERE ${orderLineVoucherExt.lineId} = ${orderLine.id}::text
        ORDER BY ${orderLineVoucherExt.voucherId}
        LIMIT 1
      )`,
      qrPngUrl: sql<string | null>`(
        SELECT ${orderLineVoucherExt.qrPngUrl}
        FROM ${orderLineVoucherExt}
        WHERE ${orderLineVoucherExt.lineId} = ${orderLine.id}::text
        ORDER BY ${orderLineVoucherExt.voucherId}
        LIMIT 1
      )`,
      redemptionStatus: lineRedemptionStatusSql(orderLine.id),
      redeemedAt: lineRedeemedAtSql(orderLine.id),
      expiresAt: lineExpiresAtSql(orderLine.id),
      reviewEligible: sql<boolean>`COALESCE((
        SELECT BOOL_OR(${orderLineVoucherExt.reviewEligible})
        FROM ${orderLineVoucherExt}
        WHERE ${orderLineVoucherExt.lineId} = ${orderLine.id}::text
      ), false)`,
    })
    .from(orderLine)
    .innerJoin(order, eq(order.id, orderLine.orderId))
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .where(eq(orderLine.id, id))
    .limit(1);

  if (!row) return null;

  const vouchers = await loadVouchersForLine(db, row.id);

  return {
    id: row.id,
    userId: row.userId ?? null,
    dealId: row.dealId,
    dealSkuId: row.dealSkuId,
    vendorId: row.vendorId ?? '',
    quantity: row.quantity,
    amountPaid: formatAgorotPlain(Number(row.lineTotal)),
    createdAt: row.createdAt,
    paymentStatus: row.orderStatus ?? 'pending',
    redemptionStatus: row.redemptionStatus ?? 'UNREDEEMED',
    qrTokenHash: row.qrTokenHash ?? null,
    qrPngUrl: row.qrPngUrl ?? null,
    redeemedAt: normalizeNullableDate(row.redeemedAt),
    expiresAt: normalizeNullableDate(row.expiresAt),
    reviewEligible: row.reviewEligible ?? false,
    vouchers,
  };
}

// ─── findEnrichedById ─────────────────────────────────────────────────────────

export async function findEnrichedById(
  db: DrizzleClient,
  id: string,
): Promise<EnrichedPurchaseDetail | null> {
  const [row] = await db
    .select({
      id: orderLine.id,
      lineTotal: orderLine.lineTotal,
      qty: orderLine.qty,
      createdAt: orderLine.createdAt,
      buyerUserId: order.buyerUserId,
      orderStatus: order.status,
      dealId: dealSkus.dealId,
      vendorId: orderLine.vendorId,
      dealTitle: deals.title,
      vendorName: vendors.displayName,
      dealType: deals.dealType,
      isPhysical: deals.isPhysical,
      isReturnable: deals.isReturnable,
      returnWindowDays: deals.returnWindowDays,
      redemptionStatus: lineRedemptionStatusSql(orderLine.id),
      expiresAt: lineExpiresAtSql(orderLine.id),
      redeemedAt: lineRedeemedAtSql(orderLine.id),
      qrPngUrl: sql<string | null>`(
        SELECT ${orderLineVoucherExt.qrPngUrl}
        FROM ${orderLineVoucherExt}
        WHERE ${orderLineVoucherExt.lineId} = ${orderLine.id}::text
        ORDER BY ${orderLineVoucherExt.voucherId}
        LIMIT 1
      )`,
      deliveredAt: sql<Date | null>`(
        SELECT ${shipments.deliveredAt}
        FROM ${shipmentPurchases}
        INNER JOIN ${shipments} ON ${shipments.id} = ${shipmentPurchases.shipmentId}
        WHERE ${shipmentPurchases.orderLineId} = ${orderLine.id}
        ORDER BY ${shipments.deliveredAt} DESC NULLS LAST
        LIMIT 1
      )`,
    })
    .from(orderLine)
    .innerJoin(order, eq(order.id, orderLine.orderId))
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .innerJoin(deals, eq(deals.id, dealSkus.dealId))
    .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${orderLine.vendorId}`)} = ${vendors.id}`)
    .where(eq(orderLine.id, id))
    .limit(1);

  if (!row) return null;

  const vouchers = await loadVouchersForLine(db, row.id);

  return {
    id: row.id,
    userId: row.buyerUserId ?? null,
    dealId: row.dealId,
    vendorId: row.vendorId ?? '',
    dealTitle: row.dealTitle,
    vendorName: row.vendorName,
    amountPaid: formatAgorotPlain(Number(row.lineTotal)),
    quantity: row.qty,
    redemptionStatus: row.redemptionStatus ?? 'UNREDEEMED',
    paymentStatus: row.orderStatus ?? 'pending',
    expiresAt: normalizeNullableDate(row.expiresAt) ?? new Date(Date.now() + 180 * 86400000),
    redeemedAt: normalizeNullableDate(row.redeemedAt),
    createdAt: row.createdAt,
    qrPngUrl: row.qrPngUrl ?? null,
    dealType: row.dealType ?? null,
    isPhysical: row.isPhysical,
    isReturnable: row.isReturnable,
    returnWindowDays: row.returnWindowDays,
    deliveredAt: normalizeNullableDate(row.deliveredAt),
    vouchers,
  };
}

// ─── listByUser ───────────────────────────────────────────────────────────────

const ACTIVE_ORDER_STATUSES = ['pending', 'charging', 'paid', 'fulfilled', 'completed'];

export async function listByUser(db: DrizzleClient, userId: string): Promise<ListByUserResult> {
  const rows = await db
    .select({
      id: orderLine.id,
      lineTotal: orderLine.lineTotal,
      qty: orderLine.qty,
      createdAt: orderLine.createdAt,
      buyerUserId: order.buyerUserId,
      orderStatus: order.status,
      dealId: dealSkus.dealId,
      vendorId: orderLine.vendorId,
      dealTitle: deals.title,
      vendorName: vendors.displayName,
      vendorLogo: vendors.logoUrl,
      dealType: deals.dealType,
      dealImageUrl: dealImages.url,
      redemptionStatus: lineRedemptionStatusSql(orderLine.id),
      expiresAt: lineExpiresAtSql(orderLine.id),
      redeemedAt: lineRedeemedAtSql(orderLine.id),
      qrPngUrl: sql<string | null>`(
        SELECT ${orderLineVoucherExt.qrPngUrl}
        FROM ${orderLineVoucherExt}
        WHERE ${orderLineVoucherExt.lineId} = ${orderLine.id}::text
        ORDER BY ${orderLineVoucherExt.voucherId}
        LIMIT 1
      )`,
      originalPrice: dealSkus.originalPrice,
    })
    .from(orderLine)
    .innerJoin(order, eq(order.id, orderLine.orderId))
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .innerJoin(deals, eq(deals.id, dealSkus.dealId))
    .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${orderLine.vendorId}`)} = ${vendors.id}`)
    .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
    .where(
      and(
        eq(order.buyerUserId, userId),
        inArray(order.status, [
          'pending',
          'charging',
          'paid',
          'fulfilled',
          'completed',
          'refunded',
          'partially_refunded',
        ]),
      ),
    )
    .orderBy(desc(orderLine.createdAt));

  let totalSpentAgorot = 0;
  let totalSavingsAgorot = 0;
  const vendorIds = new Set<string>();

  const active: EnrichedPurchase[] = [];
  const history: EnrichedPurchase[] = [];

  const vouchersByLine = await loadVouchersForLines(
    db,
    rows.map((r) => r.id),
  );

  for (const row of rows) {
    const lineTotalAgorot = Number(row.lineTotal);
    const originalAgorot = Math.round(parseFloat(String(row.originalPrice ?? '0')) * 100);
    const savingsAgorot = Math.max(0, originalAgorot * row.qty - lineTotalAgorot);

    totalSpentAgorot += lineTotalAgorot;
    totalSavingsAgorot += savingsAgorot;
    if (row.vendorId) vendorIds.add(row.vendorId);

    const enriched: EnrichedPurchase = {
      id: row.id,
      userId: row.buyerUserId ?? null,
      dealId: row.dealId,
      vendorId: row.vendorId ?? '',
      dealTitle: row.dealTitle,
      vendorName: row.vendorName,
      vendorLogo: row.vendorLogo ?? null,
      dealImageUrl: row.dealImageUrl ?? null,
      amountPaid: formatAgorotPlain(lineTotalAgorot),
      quantity: row.qty,
      redemptionStatus: row.redemptionStatus ?? 'UNREDEEMED',
      paymentStatus: row.orderStatus ?? 'pending',
      expiresAt: normalizeNullableDate(row.expiresAt) ?? new Date(Date.now() + 180 * 86400000),
      redeemedAt: normalizeNullableDate(row.redeemedAt),
      createdAt: row.createdAt,
      qrPngUrl: row.qrPngUrl ?? null,
      dealType: row.dealType ?? null,
      vouchers: vouchersByLine.get(row.id) ?? [],
    };

    const isActive =
      ACTIVE_ORDER_STATUSES.includes(row.orderStatus ?? '') &&
      (row.redemptionStatus ?? 'UNREDEEMED') === 'UNREDEEMED';

    if (isActive) {
      active.push(enriched);
    } else {
      history.push(enriched);
    }
  }

  return {
    active,
    history,
    stats: {
      totalSpent: formatAgorotPlain(totalSpentAgorot),
      totalSavings: formatAgorotPlain(totalSavingsAgorot),
      businessCount: vendorIds.size,
    },
  };
}

// ─── listByVendor ─────────────────────────────────────────────────────────────

export async function listByVendor(
  db: DrizzleClient,
  vendorId: string,
  opts?: { limit?: number; offset?: number },
) {
  const lim = opts?.limit ?? 50;
  const off = opts?.offset ?? 0;

  const rows = await db
    .select({
      id: orderLine.id,
      lineTotal: orderLine.lineTotal,
      qty: orderLine.qty,
      createdAt: orderLine.createdAt,
      buyerUserId: order.buyerUserId,
      orderStatus: order.status,
      dealId: dealSkus.dealId,
      dealTitle: deals.title,
      redemptionStatus: lineRedemptionStatusSql(orderLine.id),
      expiresAt: lineExpiresAtSql(orderLine.id),
      redeemedAt: lineRedeemedAtSql(orderLine.id),
    })
    .from(orderLine)
    .innerJoin(order, eq(order.id, orderLine.orderId))
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .innerJoin(deals, eq(deals.id, dealSkus.dealId))
    .where(eq(orderLine.vendorId, vendorId))
    .orderBy(desc(orderLine.createdAt))
    .limit(lim)
    .offset(off);

  return rows.map((row) => ({
    id: row.id,
    userId: row.buyerUserId ?? null,
    dealId: row.dealId,
    vendorId,
    dealTitle: row.dealTitle,
    amountPaid: formatAgorotPlain(Number(row.lineTotal)),
    quantity: row.qty,
    redemptionStatus: row.redemptionStatus ?? 'UNREDEEMED',
    paymentStatus: row.orderStatus ?? 'pending',
    expiresAt: row.expiresAt ?? null,
    redeemedAt: row.redeemedAt ?? null,
    createdAt: row.createdAt,
  }));
}

// ─── loadPurchasePaymentContext ───────────────────────────────────────────────
//
// Loads payment context keyed to an orderLine ID.  Used by webhook for
// post-payment actions when only the orderLine ID is known.

export async function loadPurchasePaymentContext(
  db: DrizzleClient,
  orderLineId: string,
  _piiKey?: string,
): Promise<PurchasePaymentContext> {
  const [row] = await db
    .select({
      lineTotal: orderLine.lineTotal,
      qty: orderLine.qty,
      variantId: orderLine.variantId,
      vendorId: orderLine.vendorId,
      buyerUserId: order.buyerUserId,
      dealId: dealSkus.dealId,
      dealTitle: deals.title,
      dealType: deals.dealType,
      vendorUuid: vendors.id,
      stripeAccountId: vendors.stripeAccountId,
      chargesEnabled: vendors.stripeChargesEnabled,
      buyerName: users.displayName,
      buyerEmail: users.email,
    })
    .from(orderLine)
    .innerJoin(order, eq(order.id, orderLine.orderId))
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .innerJoin(deals, eq(deals.id, dealSkus.dealId))
    .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${orderLine.vendorId}`)} = ${vendors.id}`)
    .leftJoin(users, eq(users.id, order.buyerUserId))
    .where(eq(orderLine.id, orderLineId))
    .limit(1);

  if (!row) throw new Error(`orderLine ${orderLineId} not found`);

  return {
    purchaseId: orderLineId,
    userId: row.buyerUserId ?? null,
    quantity: row.qty,
    totalAgorot: Number(row.lineTotal),
    buyer: {
      name: row.buyerName ?? 'Customer',
      email: row.buyerEmail ?? undefined,
    },
    vendor: {
      id: row.vendorUuid,
      stripeAccountId: row.stripeAccountId ?? null,
      chargesEnabled: row.chargesEnabled ?? false,
    },
    deal: {
      id: row.dealId,
      title: row.dealTitle,
      dealSkuId: row.variantId ?? null,
      dealType: row.dealType ?? null,
    },
  };
}

// ─── loadVendorPaymentContext ─────────────────────────────────────────────────
//
// Loads payment context from vendorId + dealId when the orderLine ID is not
// yet available (e.g. placeHold in cart-checkout before QRs are generated).

export async function loadVendorPaymentContext(
  db: DrizzleClient,
  vendorId: string,
  dealId: string,
  totalAgorot: number,
  userId: string,
): Promise<PurchasePaymentContext> {
  const [row] = await db
    .select({
      dealTitle: deals.title,
      dealType: deals.dealType,
      vendorUuid: vendors.id,
      stripeAccountId: vendors.stripeAccountId,
      chargesEnabled: vendors.stripeChargesEnabled,
      buyerName: users.displayName,
      buyerEmail: users.email,
    })
    .from(deals)
    .innerJoin(vendors, eq(vendors.id, deals.vendorId))
    .leftJoin(users, eq(users.id, userId))
    .where(and(eq(deals.id, dealId), eq(vendors.id, vendorId)))
    .limit(1);

  if (!row) throw new Error(`Deal ${dealId} not found`);

  return {
    purchaseId: '',
    userId,
    quantity: 1,
    totalAgorot,
    buyer: {
      name: row.buyerName ?? 'Customer',
      email: row.buyerEmail ?? undefined,
    },
    vendor: {
      id: row.vendorUuid,
      stripeAccountId: row.stripeAccountId ?? null,
      chargesEnabled: row.chargesEnabled ?? false,
    },
    deal: {
      id: dealId,
      title: row.dealTitle,
      dealSkuId: null,
      dealType: row.dealType ?? null,
    },
  };
}

// ─── getPurchaseByProviderPaymentId ──────────────────────────────────────────

/**
 * Find the first orderLine for a given Stripe PaymentIntent ID.
 * Returns { id } | null — callers only read .id.
 */
export async function getPurchaseByProviderPaymentId(
  db: DrizzleClient,
  providerPaymentId: string,
): Promise<{ id: string } | null> {
  const [ord] = await db
    .select({ id: order.id })
    .from(order)
    .where(eq(order.chargeRef, providerPaymentId))
    .limit(1);

  if (!ord) return null;

  const [ol] = await db
    .select({ id: orderLine.id })
    .from(orderLine)
    .where(eq(orderLine.orderId, ord.id))
    .orderBy(orderLine.createdAt)
    .limit(1);

  return ol ?? null;
}

// ─── markPurchaseFailed ───────────────────────────────────────────────────────

export async function markPurchaseFailed(db: DrizzleClient, orderLineId: string): Promise<void> {
  const [ol] = await db
    .select({ orderId: orderLine.orderId })
    .from(orderLine)
    .where(eq(orderLine.id, orderLineId))
    .limit(1);

  if (!ol) return;

  const orderTx = db as unknown as Transaction<OrdersSchema>;
  await failOrder(orderTx, ol.orderId, 'payment_failed');
}

// ─── confirmCheckout ──────────────────────────────────────────────────────────
//
// Called from the Stripe webhook after payment succeeds.
// The order state is managed by commerce-orders (claimForCharge → captureHold).
// This function only inserts the outbox rows for the confirmation email and
// optional referral credit.

export async function confirmCheckout(
  db: TxDrizzleClient,
  input: ConfirmCheckoutInput,
): Promise<{ outboxId: string } | { alreadyCompleted: true }> {
  const orderLineId = input.purchaseId;

  // Idempotency: if we already enqueued an email receipt for this line, skip.
  const [existing] = await db
    .select({ id: outbox.id })
    .from(outbox)
    .where(
      and(
        eq(outbox.aggregateType, 'purchase'),
        eq(outbox.aggregateId, orderLineId),
        eq(outbox.eventType, 'purchase.email_receipt'),
      ),
    )
    .limit(1);

  if (existing) return { alreadyCompleted: true };

  const { id: outboxId } = await insertOutboxRow(db, {
    aggregateType: 'purchase',
    aggregateId: orderLineId,
    eventType: 'purchase.email_receipt',
    payload: {
      purchaseId: orderLineId,
      userId: input.userId ?? null,
    },
  });

  if (input.referralCode) {
    await insertOutboxRow(db, {
      aggregateType: 'purchase',
      aggregateId: orderLineId,
      eventType: 'referral.earn',
      payload: {
        purchaseId: orderLineId,
        userId: input.userId ?? null,
        referralCode: input.referralCode,
        dealId: input.dealId ?? null,
      },
    });
  }

  return { outboxId };
}

// ─── Refund intents (commerce-orders refund_intent table) ────────────────────

export interface ClaimedRefundIntent {
  intentId: string;
  refundKey: string;
  orderId: string;
  seq: number;
  /** Kept for legacy destructuring — the claim no longer writes an outbox row. */
  outboxId: null;
}

async function resolveOrderIdForLine(
  db: DrizzleClient,
  orderLineId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ orderId: orderLine.orderId })
    .from(orderLine)
    .where(eq(orderLine.id, orderLineId))
    .limit(1);
  return row?.orderId ?? null;
}

async function resolveOrderLineTotals(
  db: DrizzleClient,
  orderLineId: string,
): Promise<{ orderId: string; lineTotal: bigint } | null> {
  const [row] = await db
    .select({ orderId: orderLine.orderId, lineTotal: orderLine.lineTotal })
    .from(orderLine)
    .where(eq(orderLine.id, orderLineId))
    .limit(1);
  return row ? { orderId: row.orderId, lineTotal: BigInt(row.lineTotal) } : null;
}

/**
 * Atomically claim a refund intent for the order that owns `purchaseId`
 * (= orderLine.id). Mirrors commerce-orders claimRefundIntentInTx semantics:
 *   - order row locked FOR UPDATE
 *   - order must carry a chargeRef (money actually captured)
 *   - one in-flight (pending) intent per order at a time
 *   - SUM(pending + executed) + this amount must not exceed order.total
 * Throws AlreadyRefundedError on any guard failure a caller can act on.
 */
export async function claimRefundIntentInTx(
  tx: TxDrizzleClient,
  input: ClaimRefundIntentInput,
): Promise<ClaimedRefundIntent> {
  const line = await resolveOrderLineTotals(tx, input.purchaseId);
  if (!line) {
    throw new AlreadyRefundedError(input.purchaseId);
  }
  const { orderId, lineTotal } = line;

  const [ord] = await tx
    .select({ id: order.id, total: order.total, chargeRef: order.chargeRef })
    .from(order)
    .where(eq(order.id, orderId))
    .for('update');
  if (!ord) throw new AlreadyRefundedError(input.purchaseId);
  if (!ord.chargeRef) {
    throw new Error(`Order ${orderId} has no chargeRef — nothing captured to refund`);
  }

  if (input.refundKey) {
    const [existing] = await tx
      .select({
        intentId: refundIntent.id,
        amount: refundIntent.amount,
        seq: refundIntent.seq,
      })
      .from(refundIntent)
      .where(and(eq(refundIntent.orderId, orderId), eq(refundIntent.refundKey, input.refundKey)))
      .limit(1);
    if (existing) {
      if (BigInt(existing.amount) !== BigInt(input.refundAmountAgorot)) {
        throw new AlreadyRefundedError(input.purchaseId);
      }
      return {
        intentId: existing.intentId,
        refundKey: input.refundKey,
        orderId,
        seq: existing.seq,
        outboxId: null,
      };
    }
  }

  const [redeemedVoucher] = await tx
    .select({ id: voucher.id })
    .from(voucher)
    .where(and(eq(voucher.lineId, input.purchaseId), eq(voucher.state, 'REDEEMED')))
    .limit(1);
  if (redeemedVoucher) throw new AlreadyRefundedError(input.purchaseId);

  const [sums] = await tx
    .select({
      pendingCount: sql<number>`COUNT(*) FILTER (WHERE ${refundIntent.status} = 'pending')`,
      prior: sql<string>`COALESCE(SUM(${refundIntent.amount}) FILTER (WHERE ${refundIntent.status} IN ('pending','executed')), 0)`,
      maxSeq: sql<number>`COALESCE(MAX(${refundIntent.seq}), 0)`,
    })
    .from(refundIntent)
    .where(eq(refundIntent.orderId, orderId));

  if (Number(sums?.pendingCount ?? 0) > 0) {
    throw new AlreadyRefundedError(input.purchaseId);
  }
  const prior = BigInt(sums?.prior ?? '0');
  const requested = BigInt(input.refundAmountAgorot);
  if (requested <= 0n || prior + requested > BigInt(ord.total)) {
    throw new AlreadyRefundedError(input.purchaseId);
  }

  const [lineSums] = await tx
    .select({
      prior: sql<string>`COALESCE(SUM(${refundIntent.amount}), 0)`,
    })
    .from(refundIntent)
    .innerJoin(refundIntentLineExt, eq(refundIntentLineExt.refundIntentId, refundIntent.id))
    .where(
      and(
        eq(refundIntentLineExt.orderLineId, input.purchaseId),
        inArray(refundIntent.status, ['pending', 'executed']),
      ),
    );
  const priorForLine = BigInt(lineSums?.prior ?? '0');
  if (priorForLine + requested > lineTotal) {
    throw new AlreadyRefundedError(input.purchaseId);
  }

  const intentId = crypto.randomUUID();
  const refundKey = input.refundKey ?? `refund:${intentId}`;
  const seq = Number(sums?.maxSeq ?? 0) + 1;
  await tx.insert(refundIntent).values({
    id: intentId,
    orderId,
    seq,
    amount: requested,
    status: 'pending',
    refundKey,
  });
  await tx.insert(refundIntentLineExt).values({
    refundIntentId: intentId,
    orderLineId: input.purchaseId,
  });

  return { intentId, refundKey, orderId, seq, outboxId: null };
}

export async function claimRefundIntent(
  db: DrizzleClient,
  input: ClaimRefundIntentInput,
): Promise<ClaimedRefundIntent> {
  return (db as TxDrizzleClient).transaction((tx) => claimRefundIntentInTx(tx, input));
}

export async function advanceOrderFulfillment(
  db: DrizzleClient,
  orderId: string,
): Promise<boolean> {
  const [current] = await db
    .select({ status: order.status })
    .from(order)
    .where(eq(order.id, orderId))
    .limit(1);
  if (!current || current.status !== 'paid') return false;

  const lines = await db
    .select({
      lineId: orderLine.id,
      isFulfilled: sql<number>`
        CASE
          WHEN ${deals.dealType} = 'ITEM' THEN
            CASE
              WHEN EXISTS (
                SELECT 1
                FROM ${shipmentPurchases} sp
                INNER JOIN ${shipments} s ON s.id = sp.shipment_id
                WHERE sp.order_line_id = ${orderLine.id}
                  AND s.status = 'delivered'
              ) THEN 1 ELSE 0
            END
          ELSE
            CASE
              WHEN ${lineAllVouchersTerminalSql(orderLine.id)} = 1 THEN 1 ELSE 0
            END
        END
      `,
    })
    .from(orderLine)
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .innerJoin(deals, eq(deals.id, dealSkus.dealId))
    .where(eq(orderLine.orderId, orderId));

  if (lines.length === 0 || lines.some((line) => Number(line.isFulfilled) !== 1)) {
    return false;
  }

  const advanced = await db
    .update(order)
    .set({ status: 'fulfilled', updatedAt: new Date() })
    .where(and(eq(order.id, orderId), eq(order.status, 'paid')))
    .returning({ id: order.id });
  if (advanced.length === 0) return false;

  const [existingStep] = await db
    .select({ orderId: orderStep.orderId })
    .from(orderStep)
    .where(and(eq(orderStep.orderId, orderId), eq(orderStep.stepId, 'fulfilled')))
    .limit(1);
  if (!existingStep) {
    await recordStep(db as unknown as Transaction<OrdersSchema>, orderId, 'fulfilled', {
      at: new Date().toISOString(),
    });
  }

  return true;
}

/** Statuses whose refund-sum recompute may overwrite them. */
const RECOMPUTABLE_ORDER_STATUSES = [
  'paid',
  'fulfilled',
  'completed',
  'unfulfillable',
  'partially_refunded',
  'refunded',
] as const;

/**
 * Recompute order.status from executed refund intents:
 * SUM(executed) == total → 'refunded'; 0 < SUM < total → 'partially_refunded'.
 */
export async function recomputeOrderRefundStatus(
  db: DrizzleClient,
  orderId: string,
): Promise<void> {
  const [row] = await db
    .select({
      total: order.total,
      refunded: sql<string>`COALESCE(SUM(${refundIntent.amount}) FILTER (WHERE ${refundIntent.status} = 'executed'), 0)`,
    })
    .from(order)
    .leftJoin(refundIntent, eq(refundIntent.orderId, order.id))
    .where(eq(order.id, orderId))
    .groupBy(order.id, order.total)
    .limit(1);
  if (!row) return;
  const refunded = BigInt(row.refunded ?? '0');
  if (refunded <= 0n) return;
  const newStatus = refunded >= BigInt(row.total) ? 'refunded' : 'partially_refunded';
  await db
    .update(order)
    .set({ status: newStatus, updatedAt: new Date() })
    .where(and(eq(order.id, orderId), inArray(order.status, [...RECOMPUTABLE_ORDER_STATUSES])));
}

/**
 * Settle a refund on the order that owns `orderLineId`: mark the in-flight
 * pending intent executed (stamping the provider refund id) and recompute the
 * order's refunded/partially_refunded status. Called by payment providers
 * after the provider refund succeeded.
 *
 * Self-healing: when no pending intent exists (externally-initiated refund),
 * inserts an executed intent directly — deduped on providerRef.
 */
export async function setPurchaseRefund(
  db: DrizzleClient,
  orderLineId: string,
  opts: { refundId: string; amount: number },
): Promise<void> {
  const orderId = await resolveOrderIdForLine(db, orderLineId);
  if (!orderId) return;

  const [target] = await db
    .select({
      id: refundIntent.id,
    })
    .from(refundIntent)
    .leftJoin(refundIntentLineExt, eq(refundIntentLineExt.refundIntentId, refundIntent.id))
    .where(
      and(
        eq(refundIntent.orderId, orderId),
        eq(refundIntent.status, 'pending'),
        sql<boolean>`(${refundIntentLineExt.orderLineId} = ${orderLineId} OR ${refundIntentLineExt.orderLineId} IS NULL)`,
      ),
    )
    .orderBy(sql`CASE WHEN ${refundIntentLineExt.orderLineId} = ${orderLineId} THEN 0 ELSE 1 END`)
    .limit(1);

  const settled =
    target == null
      ? []
      : await db
          .update(refundIntent)
          .set({
            status: 'executed',
            providerRef: opts.refundId,
            updatedAt: new Date(),
          })
          .where(and(eq(refundIntent.id, target.id), eq(refundIntent.status, 'pending')))
          .returning({ id: refundIntent.id });

  if (settled.length === 0 && opts.amount > 0) {
    const [dup] = await db
      .select({ id: refundIntent.id })
      .from(refundIntent)
      .where(and(eq(refundIntent.orderId, orderId), eq(refundIntent.providerRef, opts.refundId)))
      .limit(1);
    if (!dup) {
      const [sums] = await db
        .select({ maxSeq: sql<number>`COALESCE(MAX(${refundIntent.seq}), 0)` })
        .from(refundIntent)
        .where(eq(refundIntent.orderId, orderId));
      const intentId = crypto.randomUUID();
      await db.insert(refundIntent).values({
        id: intentId,
        orderId,
        seq: Number(sums?.maxSeq ?? 0) + 1,
        amount: BigInt(opts.amount),
        status: 'executed',
        refundKey: `refund:${intentId}`,
        providerRef: opts.refundId,
      });
      await db.insert(refundIntentLineExt).values({
        refundIntentId: intentId,
        orderLineId,
      });
    }
  }

  await recomputeOrderRefundStatus(db, orderId);
}

/** Release an in-flight refund claim: pending intent → failed (excluded from over-refund sums). */
export async function releaseRefundClaim(db: DrizzleClient, orderLineId: string): Promise<void> {
  const orderId = await resolveOrderIdForLine(db, orderLineId);
  if (!orderId) return;
  const [target] = await db
    .select({ id: refundIntent.id })
    .from(refundIntent)
    .leftJoin(refundIntentLineExt, eq(refundIntentLineExt.refundIntentId, refundIntent.id))
    .where(
      and(
        eq(refundIntent.orderId, orderId),
        eq(refundIntent.status, 'pending'),
        sql<boolean>`(${refundIntentLineExt.orderLineId} = ${orderLineId} OR ${refundIntentLineExt.orderLineId} IS NULL)`,
      ),
    )
    .orderBy(sql`CASE WHEN ${refundIntentLineExt.orderLineId} = ${orderLineId} THEN 0 ELSE 1 END`)
    .limit(1);
  if (!target) return;
  await db
    .update(refundIntent)
    .set({ status: 'failed', updatedAt: new Date() })
    .where(and(eq(refundIntent.id, target.id), eq(refundIntent.status, 'pending')));
}

/** Agorot already refunded (executed intents) on the order that owns this line. */
export async function refundedAgorotForLineOrder(
  db: DrizzleClient,
  orderLineId: string,
): Promise<number> {
  const orderId = await resolveOrderIdForLine(db, orderLineId);
  if (!orderId) return 0;
  const [row] = await db
    .select({
      refunded: sql<string>`COALESCE(SUM(${refundIntent.amount}), 0)`,
    })
    .from(refundIntent)
    .where(
      and(eq(refundIntent.orderId, orderId), inArray(refundIntent.status, ['pending', 'executed'])),
    );
  return Number(row?.refunded ?? 0);
}

export async function refundedAgorotForLine(
  db: DrizzleClient,
  orderLineId: string,
): Promise<number> {
  const [row] = await db
    .select({
      refunded: sql<string>`COALESCE(SUM(${refundIntent.amount}), 0)`,
    })
    .from(refundIntent)
    .innerJoin(refundIntentLineExt, eq(refundIntentLineExt.refundIntentId, refundIntent.id))
    .where(
      and(
        eq(refundIntentLineExt.orderLineId, orderLineId),
        inArray(refundIntent.status, ['pending', 'executed']),
      ),
    );
  return Number(row?.refunded ?? 0);
}

/** Next refund sequence number for the order that owns this line. */
export async function getRefundSequence(db: DrizzleClient, orderLineId: string): Promise<number> {
  const orderId = await resolveOrderIdForLine(db, orderLineId);
  if (!orderId) return 0;
  const [row] = await db
    .select({ maxSeq: sql<number>`COALESCE(MAX(${refundIntent.seq}), 0)` })
    .from(refundIntent)
    .where(eq(refundIntent.orderId, orderId));
  return Number(row?.maxSeq ?? 0);
}

/** @deprecated QR redemption is handled via platform voucher + orderLineVoucherExt. */
export async function redeem(
  _db: DrizzleClient,
  _orderLineId: string,
  _tokenHash: string,
): Promise<null> {
  return null;
}

/** @deprecated Refund marking handled by setPurchaseRefund. */
export async function markRefunded(
  db: DrizzleClient,
  orderLineId: string,
  opts?: { amount?: number },
): Promise<{ claimed: true }> {
  await setPurchaseRefund(db, orderLineId, {
    refundId: `manual:${orderLineId}`,
    amount: opts?.amount ?? 0,
  });
  return { claimed: true };
}