import type pg from 'pg';
import { getHeader, HttpError, type RequestContext } from '../../core/http.js';
import {
  intParam,
  optionalInteger,
  optionalString,
  readJsonBody,
  requiredString,
  stringParam,
  tenantIdFrom,
  userIdFrom,
} from '../../core/request.js';
import { query, queryOne, transaction } from '../../core/db.js';
import { createOrderNo, createRefundNo, grantSvipEntitlement } from './service.js';
import {
  createPaymentProvider,
  normalizePaymentProvider,
  paymentProviderAliases,
  type PaymentProviderName,
  type PaymentRefundNotificationResult,
} from './providers.js';
import { loadTenantPaymentProviderConfig } from '../../core/tenant-provider-config.js';
import { requireTenantAdmin, requireTenantPermission } from '../tenant-admin/auth.js';

interface PlanRow {
  id: string;
  name: string;
  price_cents: number;
  original_price_cents: number | null;
  days: number;
  region_id: string | null;
  coupon_only: boolean;
}

interface OrderRow {
  id: string;
  order_no: string;
  status: string;
  amount_cents: number;
  days: number | null;
  user_id: string | null;
  region_id: string | null;
}

interface PaymentRow {
  id: string;
  status: string;
  amount_cents: number;
  refunded_amount_cents?: number;
}

interface PaymentOrderRow {
  id: string;
  order_no: string;
  status: string;
  amount_cents: number;
  product_name: string | null;
  pay_provider: string | null;
  pay_method: string | null;
  days: number | null;
  user_id: string | null;
  region_id: string | null;
}

interface OrderDetailRow {
  id: string;
  orderNo: string;
  status: string;
  productType: string | null;
  productName: string | null;
  amountCents: number;
  payMethod: string | null;
  payProvider: string | null;
  tradeNo: string | null;
  planId: string | null;
  days: number | null;
  userId: string | null;
  regionId: string | null;
  paidAt: string | null;
  refundedAmountCents?: number;
  rawPayload: Record<string, unknown>;
  createdAt: string;
  updatedAt: string;
}

interface RefundRequestRow {
  id: string;
  refundNo: string;
  orderId: string;
  paymentId: string | null;
  orderNo: string;
  orderStatus: string;
  paymentProvider: string | null;
  provider: string | null;
  providerRefundNo: string | null;
  status: string;
  amountCents: number;
  reason: string | null;
  entitlementAction: string;
  requestedBy: string | null;
  reviewedBy: string | null;
  processedBy: string | null;
  requestedAt: string;
  reviewedAt: string | null;
  processedAt: string | null;
  succeededAt: string | null;
  failedAt: string | null;
  cancelledAt: string | null;
  failureReason: string | null;
  metadata: Record<string, unknown>;
  createdAt: string;
  updatedAt: string;
}

interface CouponRow {
  id: string;
  code: string;
  plan_id: string | null;
  discount_type: 'percent' | 'fixed' | null;
  discount_value: string | number | null;
  valid_from: string | null;
  valid_to: string | null;
  max_uses: number | null;
  used_count: number;
  status: string;
  campaign_name: string | null;
  min_order_amount_cents: number;
  max_discount_cents: number | null;
  per_user_limit: number;
  first_order_only: boolean;
  allowed_plan_ids: string[];
  allowed_region_ids: string[];
  metadata: Record<string, unknown>;
  source: string | null;
  remark: string | null;
}

interface CouponRedemptionRow {
  id: string;
  coupon_id: string | null;
  coupon_code: string | null;
  user_id: string | null;
  plan_id: string | null;
  order_id: string | null;
  status: string;
  discount_applied_cents: number | null;
  region_id: string | null;
  source: string | null;
  remark: string | null;
  claimed_at: string | null;
  used_at: string | null;
}

interface EntitlementRow {
  id: string;
  entitlementType: string;
  scopeType: string;
  scopeId: string | null;
  sourceType: string;
  sourceId: string | null;
  startsAt: string;
  expiresAt: string | null;
  status: string;
  metadata: Record<string, unknown>;
  createdAt: string;
}

function normalizeCode(value: string) {
  return value.trim().replace(/\s+/g, '').toUpperCase();
}

function formatPrice(amountCents: number) {
  return Number((Math.max(0, amountCents) / 100).toFixed(2));
}

function safeDiscountValue(value: string | number | null) {
  const parsed = Number(value ?? 0);
  return Number.isFinite(parsed) ? parsed : 0;
}

function calculateCouponDiscount(coupon: CouponRow, baseAmountCents: number) {
  const amount = Math.max(0, Math.trunc(baseAmountCents));
  if (!coupon.discount_type || coupon.discount_value === null || coupon.discount_value === undefined) return 0;
  const discount = coupon.discount_type === 'fixed'
    ? Math.max(0, Math.round(safeDiscountValue(coupon.discount_value)))
    : Math.floor((amount * Math.max(0, Math.min(100, safeDiscountValue(coupon.discount_value)))) / 100);
  const cappedByCoupon = coupon.max_discount_cents === null || coupon.max_discount_cents === undefined
    ? discount
    : Math.min(discount, Math.max(0, coupon.max_discount_cents));
  return Math.min(amount, cappedByCoupon);
}

function calculatePlanDays(planDays: number, quantity: number) {
  const days = Math.trunc(planDays || 0);
  if (days < 0) return -1;
  return Math.max(0, days * quantity);
}

function couponValidity(coupon: CouponRow, now = new Date()) {
  if (coupon.status !== 'active') {
    return { valid: false, code: 'COUPON_DISABLED', message: 'Coupon is not active' };
  }
  if (coupon.valid_from && now < new Date(coupon.valid_from)) {
    return { valid: false, code: 'COUPON_NOT_STARTED', message: 'Coupon is not active yet' };
  }
  if (coupon.valid_to && now > new Date(coupon.valid_to)) {
    return { valid: false, code: 'COUPON_EXPIRED', message: 'Coupon has expired' };
  }
  if (coupon.max_uses !== null && coupon.max_uses !== undefined && coupon.max_uses > 0 && coupon.used_count >= coupon.max_uses) {
    return { valid: false, code: 'COUPON_QUOTA_EXHAUSTED', message: 'Coupon quota has been exhausted' };
  }
  return { valid: true, code: '', message: '' };
}

function couponArray(value: unknown) {
  return Array.isArray(value) ? value.map(item => String(item)).filter(Boolean) : [];
}

function couponMetadata(coupon: CouponRow) {
  return {
    status: coupon.status,
    campaignName: coupon.campaign_name,
    minOrderAmountCents: coupon.min_order_amount_cents,
    maxDiscountCents: coupon.max_discount_cents,
    perUserLimit: coupon.per_user_limit,
    firstOrderOnly: coupon.first_order_only,
    allowedPlanIds: couponArray(coupon.allowed_plan_ids),
    allowedRegionIds: couponArray(coupon.allowed_region_ids),
    metadata: coupon.metadata || {},
  };
}

function couponRuleSnapshot(coupon: CouponRow) {
  return {
    campaignName: coupon.campaign_name,
    minOrderAmountCents: coupon.min_order_amount_cents,
    maxDiscountCents: coupon.max_discount_cents,
    perUserLimit: coupon.per_user_limit,
    firstOrderOnly: coupon.first_order_only,
    allowedPlanIds: couponArray(coupon.allowed_plan_ids),
    allowedRegionIds: couponArray(coupon.allowed_region_ids),
  };
}

function couponPayload(coupon: CouponRow, plan: PlanRow, redemption: CouponRedemptionRow | null, discountCents: number) {
  return {
    code: coupon.code,
    discountType: coupon.discount_type,
    discountValue: coupon.discount_value === null || coupon.discount_value === undefined ? null : Number(coupon.discount_value),
    discountCents,
    rules: couponMetadata(coupon),
    source: coupon.source,
    remark: coupon.remark,
    redemptionId: redemption?.id || null,
    redemptionStatus: redemption?.status || null,
    plan: {
      id: plan.id,
      name: plan.name,
      priceCents: plan.price_cents,
      price: formatPrice(plan.price_cents),
      originalPriceCents: plan.original_price_cents,
      originalPrice: plan.original_price_cents === null ? null : formatPrice(plan.original_price_cents),
      days: plan.days,
      regionId: plan.region_id,
      couponOnly: plan.coupon_only,
    },
  };
}

async function loadPlan(client: pg.PoolClient | null, tenantId: string, planId: string, lock = false) {
  const sql = `
    select id, name, price_cents, original_price_cents, days, region_id, coupon_only
    from public.svip_plans
    where tenant_id = $1 and id = $2 and is_active = true
    limit 1
    ${lock ? 'for update' : ''}
  `;
  if (client) {
    const result = await client.query<PlanRow>(sql, [tenantId, planId]);
    return result.rows[0] || null;
  }
  return queryOne<PlanRow>(sql, [tenantId, planId]);
}

async function findCouponByCode(client: pg.PoolClient, tenantId: string, code: string, lock = false) {
  const result = await client.query<CouponRow>(
    `
    select id, code::text as code, plan_id, discount_type, discount_value,
           valid_from, valid_to, max_uses, used_count, status,
           campaign_name, min_order_amount_cents, max_discount_cents,
           per_user_limit, first_order_only, allowed_plan_ids, allowed_region_ids,
           metadata, source, remark
    from public.coupons
    where tenant_id = $1 and lower(code::text) = lower($2)
    limit 1
    ${lock ? 'for update' : ''}
    `,
    [tenantId, normalizeCode(code)],
  );
  return result.rows[0] || null;
}

async function findCouponById(client: pg.PoolClient, tenantId: string, couponId: string, lock = false) {
  const result = await client.query<CouponRow>(
    `
    select id, code::text as code, plan_id, discount_type, discount_value,
           valid_from, valid_to, max_uses, used_count, status,
           campaign_name, min_order_amount_cents, max_discount_cents,
           per_user_limit, first_order_only, allowed_plan_ids, allowed_region_ids,
           metadata, source, remark
    from public.coupons
    where tenant_id = $1 and id = $2
    limit 1
    ${lock ? 'for update' : ''}
    `,
    [tenantId, couponId],
  );
  return result.rows[0] || null;
}

async function findCouponRedemption(client: pg.PoolClient, tenantId: string, userId: string, couponId: string) {
  const result = await client.query<CouponRedemptionRow>(
    `
    select id, coupon_id, coupon_code, user_id, plan_id, order_id, status,
           discount_applied_cents, region_id, source, remark, claimed_at, used_at
    from public.coupon_redemptions
    where tenant_id = $1 and user_id = $2 and coupon_id = $3
      and status in ('claimed', 'pending')
    order by created_at desc
    limit 1
    for update
    `,
    [tenantId, userId, couponId],
  );
  return result.rows[0] || null;
}

async function usedCouponCount(client: pg.PoolClient, tenantId: string, userId: string, couponId: string) {
  const result = await client.query<{ count: string }>(
    `
    select count(*)::text as count
    from public.coupon_redemptions
    where tenant_id = $1
      and user_id = $2
      and coupon_id = $3
      and status = 'used'
    `,
    [tenantId, userId, couponId],
  );
  return Number(result.rows[0]?.count || 0);
}

async function paidOrderCount(client: pg.PoolClient, tenantId: string, userId: string) {
  const result = await client.query<{ count: string }>(
    `
    select count(*)::text as count
    from public.orders
    where tenant_id = $1
      and user_id = $2
      and status in ('paid', 'partially_refunded', 'refunded')
    `,
    [tenantId, userId],
  );
  return Number(result.rows[0]?.count || 0);
}

async function assertCouponBusinessRules(
  client: pg.PoolClient,
  coupon: CouponRow,
  input: {
    tenantId: string;
    userId: string;
    planId: string;
    regionId: string | null;
    baseAmountCents: number;
  },
) {
  const allowedPlans = couponArray(coupon.allowed_plan_ids);
  if (allowedPlans.length > 0 && !allowedPlans.includes(input.planId)) {
    throw new HttpError(409, 'Coupon does not apply to this plan', 'COUPON_PLAN_MISMATCH');
  }
  const allowedRegions = couponArray(coupon.allowed_region_ids);
  if (allowedRegions.length > 0 && (!input.regionId || !allowedRegions.includes(input.regionId))) {
    throw new HttpError(409, 'Coupon does not apply to this region', 'COUPON_REGION_MISMATCH');
  }
  if (input.baseAmountCents < Math.max(0, coupon.min_order_amount_cents || 0)) {
    throw new HttpError(409, 'Order amount does not meet coupon minimum', 'COUPON_MIN_ORDER_AMOUNT_NOT_MET');
  }
  if (coupon.first_order_only && (await paidOrderCount(client, input.tenantId, input.userId)) > 0) {
    throw new HttpError(409, 'Coupon is limited to first paid order', 'COUPON_FIRST_ORDER_ONLY');
  }
  const usedCount = await usedCouponCount(client, input.tenantId, input.userId, coupon.id);
  if (usedCount >= Math.max(1, coupon.per_user_limit || 1)) {
    const code = Math.max(1, coupon.per_user_limit || 1) === 1 ? 'COUPON_ALREADY_USED' : 'COUPON_USER_LIMIT_REACHED';
    throw new HttpError(409, 'Coupon user usage limit reached', code);
  }
}

async function claimCoupon(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    userId: string;
    code: string;
    planId?: string | null;
    regionId?: string | null;
    baseAmountCents?: number;
  },
) {
  const coupon = await findCouponByCode(client, input.tenantId, input.code, true);
  if (!coupon) throw new HttpError(404, 'Coupon not found', 'COUPON_NOT_FOUND');

  const validity = couponValidity(coupon);
  if (!validity.valid) throw new HttpError(409, validity.message, validity.code);

  const planId = input.planId || coupon.plan_id;
  if (!planId) throw new HttpError(409, 'Coupon is not bound to a plan', 'COUPON_PLAN_MISSING');
  if (coupon.plan_id && input.planId && coupon.plan_id !== input.planId) {
    throw new HttpError(409, 'Coupon does not apply to this plan', 'COUPON_PLAN_MISMATCH');
  }

  const plan = await loadPlan(client, input.tenantId, planId);
  if (!plan) throw new HttpError(404, 'Coupon plan not found', 'COUPON_PLAN_NOT_FOUND');
  const finalRegionId = input.regionId || plan.region_id;
  if (plan.region_id && finalRegionId && plan.region_id !== finalRegionId) {
    throw new HttpError(409, 'Coupon does not apply to this region', 'COUPON_REGION_MISMATCH');
  }
  await assertCouponBusinessRules(client, coupon, {
    tenantId: input.tenantId,
    userId: input.userId,
    planId: plan.id,
    regionId: finalRegionId,
    baseAmountCents: input.baseAmountCents ?? plan.price_cents,
  });

  const existing = await findCouponRedemption(client, input.tenantId, input.userId, coupon.id);
  if (existing && !['claimed', 'pending'].includes(existing.status)) {
    throw new HttpError(409, `Coupon redemption is ${existing.status}`, 'COUPON_REDEMPTION_UNAVAILABLE');
  }

  const discountCents = calculateCouponDiscount(coupon, input.baseAmountCents ?? plan.price_cents);
  if (existing) {
    return { coupon, plan, redemption: existing, discountCents, idempotent: true };
  }

  const inserted = await client.query<CouponRedemptionRow>(
    `
    insert into public.coupon_redemptions (
      tenant_id, coupon_id, coupon_code, user_id, plan_id, status,
      discount_applied_cents, region_id, source, remark, claimed_at
    )
    values ($1, $2, $3, $4, $5, 'claimed', $6, $7::uuid, $8, $9, now())
    returning id, coupon_id, coupon_code, user_id, plan_id, order_id, status,
              discount_applied_cents, region_id, source, remark, claimed_at, used_at
    `,
    [
      input.tenantId,
      coupon.id,
      coupon.code,
      input.userId,
      plan.id,
      discountCents,
      finalRegionId || null,
      coupon.source,
      coupon.remark,
    ],
  );

  return { coupon, plan, redemption: inserted.rows[0], discountCents, idempotent: false };
}

function orderDetailPayload(order: OrderDetailRow, payments: unknown[], items: unknown[], redemptions: unknown[]) {
  const snapshot =
    order.rawPayload && typeof order.rawPayload === 'object' && 'pricing' in order.rawPayload
      ? (order.rawPayload as Record<string, unknown>).pricing
      : null;
  return {
    id: order.id,
    orderNo: order.orderNo,
    status: order.status,
    productType: order.productType,
    productName: order.productName,
    amountCents: order.amountCents,
    amount: formatPrice(order.amountCents),
    refundedAmountCents: order.refundedAmountCents ?? 0,
    payMethod: order.payMethod,
    payProvider: order.payProvider,
    tradeNo: order.tradeNo,
    planId: order.planId,
    days: order.days,
    regionId: order.regionId,
    paidAt: order.paidAt,
    createdAt: order.createdAt,
    updatedAt: order.updatedAt,
    pricing: snapshot,
    payments,
    items,
    couponRedemptions: redemptions,
  };
}

function objectValue(value: unknown): Record<string, unknown> {
  return value && typeof value === 'object' && !Array.isArray(value) ? (value as Record<string, unknown>) : {};
}

function optionalChoice<T extends string>(value: unknown, allowed: readonly T[], fallback: T): T {
  if (typeof value === 'string' && allowed.includes(value as T)) return value as T;
  return fallback;
}

function normalizeRefundNo(value: string) {
  return value.trim().replace(/\s+/g, '').toUpperCase();
}

function refundPayload(row: RefundRequestRow) {
  return {
    id: row.id,
    refundNo: row.refundNo,
    orderId: row.orderId,
    paymentId: row.paymentId,
    orderNo: row.orderNo,
    orderStatus: row.orderStatus,
    paymentProvider: row.paymentProvider,
    provider: row.provider,
    providerRefundNo: row.providerRefundNo,
    status: row.status,
    amountCents: row.amountCents,
    amount: formatPrice(row.amountCents),
    reason: row.reason,
    entitlementAction: row.entitlementAction,
    requestedBy: row.requestedBy,
    reviewedBy: row.reviewedBy,
    processedBy: row.processedBy,
    requestedAt: row.requestedAt,
    reviewedAt: row.reviewedAt,
    processedAt: row.processedAt,
    succeededAt: row.succeededAt,
    failedAt: row.failedAt,
    cancelledAt: row.cancelledAt,
    failureReason: row.failureReason,
    metadata: row.metadata || {},
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  };
}

async function recordCommerceAudit(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    actorUserId?: string | null;
    action: string;
    targetType: string;
    targetId: string | null;
    details?: Record<string, unknown>;
  },
) {
  await client.query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2, $3, $4, $5, $6::jsonb)
    `,
    [
      input.tenantId,
      input.actorUserId || null,
      input.action,
      input.targetType,
      input.targetId,
      JSON.stringify(input.details || {}),
    ],
  );
}

async function recordRefundEvent(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    refundRequestId: string;
    fromStatus?: string | null;
    toStatus: string;
    eventType: string;
    actorUserId?: string | null;
    details?: Record<string, unknown>;
  },
) {
  await client.query(
    `
    insert into public.commerce_refund_events (
      tenant_id, refund_request_id, from_status, to_status, event_type, actor_user_id, details
    )
    values ($1, $2, $3, $4, $5, $6, $7::jsonb)
    `,
    [
      input.tenantId,
      input.refundRequestId,
      input.fromStatus || null,
      input.toStatus,
      input.eventType,
      input.actorUserId || null,
      JSON.stringify(input.details || {}),
    ],
  );
}

async function fetchRefundById(client: pg.PoolClient, tenantId: string, refundId: string, lock = false) {
  const result = await client.query<RefundRequestRow>(
    `
    select rr.id, rr.refund_no as "refundNo", rr.order_id as "orderId", rr.payment_id as "paymentId",
           o.order_no as "orderNo", o.status as "orderStatus", o.pay_provider as "paymentProvider",
           rr.provider, rr.provider_refund_no as "providerRefundNo", rr.status,
           rr.amount_cents as "amountCents", rr.reason, rr.entitlement_action as "entitlementAction",
           rr.requested_by as "requestedBy", rr.reviewed_by as "reviewedBy", rr.processed_by as "processedBy",
           rr.requested_at as "requestedAt", rr.reviewed_at as "reviewedAt", rr.processed_at as "processedAt",
           rr.succeeded_at as "succeededAt", rr.failed_at as "failedAt", rr.cancelled_at as "cancelledAt",
           rr.failure_reason as "failureReason", rr.metadata,
           rr.created_at as "createdAt", rr.updated_at as "updatedAt"
    from public.commerce_refund_requests rr
    join public.orders o on o.tenant_id = rr.tenant_id and o.id = rr.order_id
    where rr.tenant_id = $1 and rr.id = $2
    limit 1
    ${lock ? 'for update of rr' : ''}
    `,
    [tenantId, refundId],
  );
  return result.rows[0] || null;
}

async function fetchRefundByNo(client: pg.PoolClient, tenantId: string, refundNo: string, lock = false) {
  const result = await client.query<RefundRequestRow>(
    `
    select rr.id, rr.refund_no as "refundNo", rr.order_id as "orderId", rr.payment_id as "paymentId",
           o.order_no as "orderNo", o.status as "orderStatus", o.pay_provider as "paymentProvider",
           rr.provider, rr.provider_refund_no as "providerRefundNo", rr.status,
           rr.amount_cents as "amountCents", rr.reason, rr.entitlement_action as "entitlementAction",
           rr.requested_by as "requestedBy", rr.reviewed_by as "reviewedBy", rr.processed_by as "processedBy",
           rr.requested_at as "requestedAt", rr.reviewed_at as "reviewedAt", rr.processed_at as "processedAt",
           rr.succeeded_at as "succeededAt", rr.failed_at as "failedAt", rr.cancelled_at as "cancelledAt",
           rr.failure_reason as "failureReason", rr.metadata,
           rr.created_at as "createdAt", rr.updated_at as "updatedAt"
    from public.commerce_refund_requests rr
    join public.orders o on o.tenant_id = rr.tenant_id and o.id = rr.order_id
    where rr.tenant_id = $1 and rr.refund_no = $2
    limit 1
    ${lock ? 'for update of rr' : ''}
    `,
    [tenantId, normalizeRefundNo(refundNo)],
  );
  return result.rows[0] || null;
}

async function applySuccessfulRefund(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    refund: RefundRequestRow;
    actorUserId?: string | null;
    providerRefundNo?: string | null;
  },
) {
  const orderResult = await client.query<{
    id: string;
    orderNo: string;
    status: string;
    amountCents: number;
    refundedAmountCents: number;
    userId: string | null;
  }>(
    `
    select id, order_no as "orderNo", status, amount_cents as "amountCents",
           refunded_amount_cents as "refundedAmountCents", user_id as "userId"
    from public.orders
    where tenant_id = $1 and id = $2
    limit 1
    for update
    `,
    [input.tenantId, input.refund.orderId],
  );
  const order = orderResult.rows[0];
  if (!order) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');
  if (!['paid', 'partially_refunded'].includes(order.status)) {
    throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_REFUNDABLE');
  }

  const nextRefundedAmount = order.refundedAmountCents + input.refund.amountCents;
  if (nextRefundedAmount > order.amountCents) {
    throw new HttpError(409, 'Refund amount exceeds paid amount', 'REFUND_AMOUNT_EXCEEDS_PAID');
  }
  const finalStatus = nextRefundedAmount === order.amountCents ? 'refunded' : 'partially_refunded';

  await client.query(
    `
    update public.orders
    set status = $4, refunded_amount_cents = $3, updated_at = now(),
        raw_payload = coalesce(raw_payload, '{}'::jsonb) || $5::jsonb
    where tenant_id = $1 and id = $2
    `,
    [
      input.tenantId,
      order.id,
      nextRefundedAmount,
      finalStatus,
      JSON.stringify({
        lastRefund: {
          refundNo: input.refund.refundNo,
          amountCents: input.refund.amountCents,
          providerRefundNo: input.providerRefundNo || input.refund.providerRefundNo || null,
        },
      }),
    ],
  );

  if (input.refund.paymentId) {
    const paymentResult = await client.query<PaymentRow>(
      `
      select id, status, amount_cents, refunded_amount_cents
      from public.payments
      where tenant_id = $1 and id = $2
      limit 1
      for update
      `,
      [input.tenantId, input.refund.paymentId],
    );
    const payment = paymentResult.rows[0];
    if (!payment) throw new HttpError(404, 'Payment not found', 'PAYMENT_NOT_FOUND');
    const paymentRefundedAmount = (payment.refunded_amount_cents || 0) + input.refund.amountCents;
    if (paymentRefundedAmount > payment.amount_cents) {
      throw new HttpError(409, 'Refund amount exceeds payment amount', 'REFUND_AMOUNT_EXCEEDS_PAID');
    }
    await client.query(
      `
      update public.payments
      set status = $4, refunded_amount_cents = $3, updated_at = now(),
          raw_payload = coalesce(raw_payload, '{}'::jsonb) || $5::jsonb
      where tenant_id = $1 and id = $2
      `,
      [
        input.tenantId,
        payment.id,
        paymentRefundedAmount,
        paymentRefundedAmount === payment.amount_cents ? 'refunded' : 'partially_refunded',
        JSON.stringify({
          lastRefund: {
            refundNo: input.refund.refundNo,
            amountCents: input.refund.amountCents,
            providerRefundNo: input.providerRefundNo || input.refund.providerRefundNo || null,
          },
        }),
      ],
    );
  }

  let revokedEntitlements = 0;
  if (input.refund.entitlementAction === 'revoke_on_success' && finalStatus === 'refunded') {
    const revoked = await client.query(
      `
      update public.entitlements
      set status = 'revoked',
          revoked_at = now(),
          revoked_by = $3,
          revoked_reason = $4,
          metadata = coalesce(metadata, '{}'::jsonb) || $5::jsonb
      where tenant_id = $1
        and source_type = 'order'
        and source_id = $2
        and status = 'active'
      `,
      [
        input.tenantId,
        order.id,
        input.actorUserId,
        `refund:${input.refund.refundNo}`,
        JSON.stringify({
          revokedByRefundNo: input.refund.refundNo,
          refundAmountCents: input.refund.amountCents,
        }),
      ],
    );
    revokedEntitlements = revoked.rowCount || 0;
  }

  return {
    orderNo: order.orderNo,
    orderStatus: finalStatus,
    refundedAmountCents: nextRefundedAmount,
    revokedEntitlements,
  };
}

export async function createOrderRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const planId = requiredString(body, 'planId');
  const quantity = Math.max(1, optionalInteger(body, 'quantity', 1));
  const payMethod = optionalString(body, 'payMethod') || 'manual';
  const payProvider = optionalString(body, 'payProvider') || 'manual';
  const regionId = optionalString(body, 'regionId') || null;
  const couponCode = optionalString(body, 'couponCode');
  const couponRedemptionId = optionalString(body, 'couponRedemptionId');

  const plan = await loadPlan(null, tenantId, planId);

  if (!plan) {
    throw new HttpError(404, 'SVIP plan not found', 'PLAN_NOT_FOUND');
  }
  if (plan.coupon_only && !couponCode && !couponRedemptionId) {
    throw new HttpError(403, 'This plan requires a coupon', 'PLAN_REQUIRES_COUPON');
  }

  const finalRegionId = regionId || plan.region_id;
  const originalAmountCents = Math.max(0, plan.price_cents * quantity);
  const days = calculatePlanDays(plan.days, quantity);
  const orderNo = createOrderNo(payProvider === 'xpay' ? 'XP' : 'SVIP');

  const item = await transaction(async client => {
    let couponResult:
      | {
          coupon: CouponRow;
          plan: PlanRow;
          redemption: CouponRedemptionRow;
          discountCents: number;
          idempotent: boolean;
        }
      | null = null;

    if (couponRedemptionId) {
      const redemptionResult = await client.query<CouponRedemptionRow>(
        `
        select id, coupon_id, coupon_code, user_id, plan_id, order_id, status,
               discount_applied_cents, region_id, source, remark, claimed_at, used_at
        from public.coupon_redemptions
        where tenant_id = $1 and id = $2 and user_id = $3
        limit 1
        for update
        `,
        [tenantId, couponRedemptionId, userId],
      );
      const redemption = redemptionResult.rows[0];
      if (!redemption) throw new HttpError(404, 'Coupon redemption not found', 'COUPON_REDEMPTION_NOT_FOUND');
      if (redemption.status === 'used') throw new HttpError(409, 'Coupon already used', 'COUPON_ALREADY_USED');
      if (redemption.order_id) throw new HttpError(409, 'Coupon redemption is already bound to an order', 'COUPON_REDEMPTION_BOUND');
      if (redemption.plan_id && redemption.plan_id !== planId) {
        throw new HttpError(409, 'Coupon does not apply to this plan', 'COUPON_PLAN_MISMATCH');
      }
      const coupon = redemption.coupon_id
        ? await findCouponById(client, tenantId, redemption.coupon_id, true)
        : await findCouponByCode(client, tenantId, redemption.coupon_code || '', true);
      if (!coupon) throw new HttpError(404, 'Coupon not found', 'COUPON_NOT_FOUND');
      const validity = couponValidity(coupon);
      if (!validity.valid) throw new HttpError(409, validity.message, validity.code);
      await assertCouponBusinessRules(client, coupon, {
        tenantId,
        userId,
        planId,
        regionId: finalRegionId,
        baseAmountCents: originalAmountCents,
      });
      const discountCents = calculateCouponDiscount(coupon, originalAmountCents);
      couponResult = { coupon, plan, redemption, discountCents, idempotent: true };
    } else if (couponCode) {
      const claimed = await claimCoupon(client, {
        tenantId,
        userId,
        code: couponCode,
        planId,
        regionId: finalRegionId,
        baseAmountCents: originalAmountCents,
      });
      couponResult = {
        coupon: claimed.coupon,
        plan: claimed.plan,
        redemption: claimed.redemption,
        discountCents: claimed.discountCents,
        idempotent: claimed.idempotent,
      };
    }

    if (couponResult?.plan.id !== undefined && couponResult.plan.id !== plan.id) {
      throw new HttpError(409, 'Coupon plan does not match the order plan', 'COUPON_PLAN_MISMATCH');
    }
    if (couponResult?.redemption.order_id) {
      throw new HttpError(409, 'Coupon redemption is already bound to an order', 'COUPON_REDEMPTION_BOUND');
    }

    const discountCents = couponResult ? Math.min(originalAmountCents, Math.max(0, couponResult.discountCents)) : 0;
    const amountCents = Math.max(0, originalAmountCents - discountCents);
    const pricingSnapshot = {
      quantity,
      unitAmountCents: plan.price_cents,
      originalAmountCents,
      discountCents,
      amountCents,
      couponCode: couponResult?.coupon.code || null,
      couponRedemptionId: couponResult?.redemption.id || null,
    };

    const orderResult = await client.query(
      `
      insert into public.orders (
        tenant_id, user_id, order_no, status, product_type, product_name,
        amount_cents, pay_method, pay_provider, plan_id, days, region_id, raw_payload
      )
      values ($1, $2, $3, 'pending', 'svip', $4, $5, $6, $7, $8, $9, $10, $11::jsonb)
      returning id, order_no as "orderNo", status, product_type as "productType",
                product_name as "productName", amount_cents as "amountCents",
                pay_method as "payMethod", pay_provider as "payProvider",
                plan_id as "planId", days, region_id as "regionId",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        tenantId,
        userId,
        orderNo,
        `${plan.name}${quantity > 1 ? ` x${quantity}` : ''}`,
        amountCents,
        payMethod,
        payProvider,
        planId,
        days,
        finalRegionId,
        JSON.stringify({ quantity, pricing: pricingSnapshot, request: body }),
      ],
    );
    const order = orderResult.rows[0];

    await client.query(
      `
      insert into public.order_items (tenant_id, order_id, item_type, item_id, name, quantity, unit_amount_cents, total_amount_cents, metadata)
      values ($1, $2, 'svip_plan', $3, $4, $5, $6, $7, $8::jsonb)
      `,
      [
        tenantId,
        order.id,
        planId,
        plan.name,
        quantity,
        plan.price_cents,
        originalAmountCents,
        JSON.stringify({ days: plan.days, totalDays: days, discountCents, payableAmountCents: amountCents }),
      ],
    );

    if (couponResult) {
      if (discountCents > 0) {
        await client.query(
          `
          insert into public.order_items (
            tenant_id, order_id, item_type, item_id, name, quantity,
            unit_amount_cents, total_amount_cents, metadata
          )
          values ($1, $2, 'coupon_discount', $3, $4, 1, -($5::integer), -($5::integer), $6::jsonb)
          `,
          [
            tenantId,
            order.id,
            couponResult.coupon.id,
            `优惠券 ${couponResult.coupon.code}`,
            discountCents,
            JSON.stringify({
              couponCode: couponResult.coupon.code,
              redemptionId: couponResult.redemption.id,
              discountType: couponResult.coupon.discount_type,
              discountValue: couponResult.coupon.discount_value,
              couponRules: couponRuleSnapshot(couponResult.coupon),
            }),
          ],
        );
      }

      const redemptionUpdate = await client.query(
        `
        update public.coupon_redemptions
        set status = 'used',
            order_id = $3,
            plan_id = $4,
            discount_applied_cents = $5,
            region_id = coalesce($6::uuid, region_id),
            used_at = now(),
            updated_at = now()
        where tenant_id = $1 and id = $2 and status in ('claimed', 'pending')
        `,
        [tenantId, couponResult.redemption.id, order.id, plan.id, discountCents, finalRegionId],
      );
      if (redemptionUpdate.rowCount !== 1) {
        throw new HttpError(409, 'Coupon redemption is no longer available', 'COUPON_REDEMPTION_UNAVAILABLE');
      }

      await client.query(
        `
        update public.coupons
        set used_count = used_count + 1, updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [tenantId, couponResult.coupon.id],
      );
    }

    const zeroAmountTradeNo = amountCents === 0 ? `zero-${orderNo}` : null;
    if (zeroAmountTradeNo) {
      await client.query(
        `
        update public.orders
        set status = 'paid',
            trade_no = $3,
            paid_at = now(),
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [tenantId, order.id, zeroAmountTradeNo],
      );
      order.status = 'paid';
    }

    await client.query(
      `
      insert into public.payments (
        tenant_id, order_id, provider, method, status, amount_cents,
        provider_trade_no, paid_at, raw_payload
      )
      values (
        $1, $2, $3, $4, $5, $6,
        $7, case when $7::text is null then null else now() end, $8::jsonb
      )
      `,
      [
        tenantId,
        order.id,
        payProvider,
        payMethod,
        zeroAmountTradeNo ? 'paid' : 'pending',
        amountCents,
        zeroAmountTradeNo,
        JSON.stringify({ pricing: pricingSnapshot, request: body }),
      ],
    );

    const entitlement =
      amountCents === 0
        ? await grantSvipEntitlement(client, {
            tenantId,
            userId,
            days,
            regionId: finalRegionId,
            sourceType: 'order',
            sourceId: order.id,
            metadata: {
              orderNo,
              paymentProvider: payProvider,
              zeroAmountCheckout: true,
              couponCode: couponResult?.coupon.code || null,
            },
          })
        : null;

    return {
      ...order,
      status: amountCents === 0 ? 'paid' : order.status,
      tradeNo: zeroAmountTradeNo,
      originalAmountCents,
      discountCents,
      amount: formatPrice(amountCents),
      entitlement,
      coupon: couponResult
        ? couponPayload(couponResult.coupon, couponResult.plan, couponResult.redemption, discountCents)
        : null,
    };
  });

  return { item };
}

export async function ordersRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query<EntitlementRow>(
    `
    select id, order_no as "orderNo", status, product_type as "productType",
           product_name as "productName", amount_cents as "amountCents",
           pay_method as "payMethod", pay_provider as "payProvider",
           trade_no as "tradeNo", plan_id as "planId", days,
           region_id as "regionId", paid_at as "paidAt",
           refunded_amount_cents as "refundedAmountCents",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.orders
    where tenant_id = $1 and user_id = $2
    order by created_at desc
    limit $3
    `,
    [tenantId, userId, limit],
  );

  return { items };
}

async function loadOrderDetail(tenantId: string, userId: string, orderNo: string) {
  const order = await queryOne<OrderDetailRow>(
    `
    select id, order_no as "orderNo", status, product_type as "productType",
           product_name as "productName", amount_cents as "amountCents",
           pay_method as "payMethod", pay_provider as "payProvider",
           trade_no as "tradeNo", plan_id as "planId", days, user_id as "userId",
           region_id as "regionId", paid_at as "paidAt",
           refunded_amount_cents as "refundedAmountCents", raw_payload as "rawPayload",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.orders
    where tenant_id = $1 and user_id = $2 and order_no = $3
    limit 1
    `,
    [tenantId, userId, orderNo],
  );
  if (!order) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');

  const [payments, items, redemptions] = await Promise.all([
    query(
      `
      select id, provider, method, status, amount_cents as "amountCents",
             provider_trade_no as "providerTradeNo", paid_at as "paidAt",
             created_at as "createdAt", updated_at as "updatedAt"
      from public.payments
      where tenant_id = $1 and order_id = $2
      order by created_at desc
      `,
      [tenantId, order.id],
    ),
    query(
      `
      select id, item_type as "itemType", item_id as "itemId", name, quantity,
             unit_amount_cents as "unitAmountCents", total_amount_cents as "totalAmountCents",
             metadata
      from public.order_items
      where tenant_id = $1 and order_id = $2
      order by case item_type when 'svip_plan' then 0 when 'coupon_discount' then 1 else 2 end, id
      `,
      [tenantId, order.id],
    ),
    query(
      `
      select id, coupon_code as "couponCode", status, discount_applied_cents as "discountAppliedCents",
             region_id as "regionId", claimed_at as "claimedAt", used_at as "usedAt"
      from public.coupon_redemptions
      where tenant_id = $1 and order_id = $2 and user_id = $3
      order by created_at desc
      `,
      [tenantId, order.id, userId],
    ),
  ]);

  return orderDetailPayload(order, payments, items, redemptions);
}

export async function orderDetailRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const orderNo = stringParam(ctx, 'orderNo');
  if (!orderNo) throw new HttpError(400, 'orderNo is required', 'ORDER_NO_REQUIRED');
  return { item: await loadOrderDetail(tenantId, userId, orderNo) };
}

export async function orderStatusRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const orderNo = stringParam(ctx, 'orderNo');
  if (!orderNo) throw new HttpError(400, 'orderNo is required', 'ORDER_NO_REQUIRED');

  const order = await queryOne<OrderDetailRow>(
    `
    select id, order_no as "orderNo", status, product_type as "productType",
           product_name as "productName", amount_cents as "amountCents",
           pay_method as "payMethod", pay_provider as "payProvider",
           trade_no as "tradeNo", plan_id as "planId", days, user_id as "userId",
           region_id as "regionId", paid_at as "paidAt",
           refunded_amount_cents as "refundedAmountCents", raw_payload as "rawPayload",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.orders
    where tenant_id = $1 and user_id = $2 and order_no = $3
    limit 1
    `,
    [tenantId, userId, orderNo],
  );
  if (!order) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');

  const latestPayment = await queryOne<{
    provider: string;
    method: string | null;
    status: string;
    amountCents: number;
    providerTradeNo: string | null;
    paidAt: string | null;
    updatedAt: string;
  }>(
    `
    select provider, method, status, amount_cents as "amountCents",
           provider_trade_no as "providerTradeNo", paid_at as "paidAt",
           updated_at as "updatedAt"
    from public.payments
    where tenant_id = $1 and order_id = $2
    order by updated_at desc
    limit 1
    `,
    [tenantId, order.id],
  );

  return {
    item: {
      orderNo: order.orderNo,
      status: order.status,
      amountCents: order.amountCents,
      amount: formatPrice(order.amountCents),
      refundedAmountCents: order.refundedAmountCents ?? 0,
      payProvider: order.payProvider,
      payMethod: order.payMethod,
      tradeNo: order.tradeNo,
      paidAt: order.paidAt,
      updatedAt: order.updatedAt,
      payment: latestPayment,
    },
  };
}

export async function entitlementsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const now = new Date().toISOString();

  const items: EntitlementRow[] = await query<EntitlementRow>(
    `
    select id, entitlement_type as "entitlementType",
           scope_type as "scopeType", scope_id as "scopeId",
           source_type as "sourceType", source_id as "sourceId",
           starts_at as "startsAt", expires_at as "expiresAt",
           status, metadata, created_at as "createdAt"
    from public.entitlements
    where tenant_id = $1 and user_id = $2
    order by expires_at desc nulls first, created_at desc
    `,
    [tenantId, userId],
  );

  const activeSvip = await queryOne<{
    expiresAt: string | null;
    scopeType: string;
    scopeId: string | null;
  }>(
    `
    select expires_at as "expiresAt", scope_type as "scopeType", scope_id as "scopeId"
    from public.entitlements
    where tenant_id = $1
      and user_id = $2
      and entitlement_type = 'svip'
      and status = 'active'
      and starts_at <= $3::timestamptz
      and (expires_at is null or expires_at > $3::timestamptz)
    order by expires_at desc nulls first
    limit 1
    `,
    [tenantId, userId, now],
  );

  const svipExpiresAt =
    items.find(item => item.entitlementType === 'svip' && item.status === 'active' && !item.expiresAt)?.expiresAt ||
    items
      .filter(item => item.entitlementType === 'svip' && item.status === 'active' && item.expiresAt)
      .map(item => item.expiresAt)
      .sort()
      .at(-1) ||
    activeSvip?.expiresAt ||
    null;

  return {
    items,
    summary: {
      isSvip: !!activeSvip,
      svipExpiresAt,
      svipScopeType: activeSvip?.scopeType || null,
      svipScopeId: activeSvip?.scopeId || null,
    },
  };
}

export async function entitlementCheckRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const regionId = ctx.url.searchParams.get('regionId') || '';
  const now = new Date().toISOString();

  const row = await queryOne<{
    id: string;
    expiresAt: string | null;
    scopeType: string;
    scopeId: string | null;
  }>(
    `
    select id, expires_at as "expiresAt", scope_type as "scopeType", scope_id as "scopeId"
    from public.entitlements
    where tenant_id = $1
      and user_id = $2
      and entitlement_type = 'svip'
      and status = 'active'
      and starts_at <= $3::timestamptz
      and (expires_at is null or expires_at > $3::timestamptz)
      and (
        scope_type = 'tenant'
        or ($4::uuid is not null and scope_type = 'region' and scope_id = $4::uuid)
      )
    order by case when scope_type = 'region' then 0 else 1 end, expires_at desc nulls first
    limit 1
    `,
    [tenantId, userId, now, regionId || null],
  );

  return {
    allowed: !!row,
    entitlement: row || null,
  };
}

export async function refundRequestsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:refund:read');
  const limit = intParam(ctx, 'limit', 50, 200);
  const status = stringParam(ctx, 'status');
  const orderNo = stringParam(ctx, 'orderNo');

  const params: unknown[] = [auth.tenantId, limit];
  const where: string[] = ['rr.tenant_id = $1'];
  if (status) {
    params.push(status);
    where.push(`rr.status = $${params.length}`);
  }
  if (orderNo) {
    params.push(orderNo);
    where.push(`o.order_no = $${params.length}`);
  }

  const items = await query<RefundRequestRow>(
    `
    select rr.id, rr.refund_no as "refundNo", rr.order_id as "orderId", rr.payment_id as "paymentId",
           o.order_no as "orderNo", o.status as "orderStatus", o.pay_provider as "paymentProvider",
           rr.provider, rr.provider_refund_no as "providerRefundNo", rr.status,
           rr.amount_cents as "amountCents", rr.reason, rr.entitlement_action as "entitlementAction",
           rr.requested_by as "requestedBy", rr.reviewed_by as "reviewedBy", rr.processed_by as "processedBy",
           rr.requested_at as "requestedAt", rr.reviewed_at as "reviewedAt", rr.processed_at as "processedAt",
           rr.succeeded_at as "succeededAt", rr.failed_at as "failedAt", rr.cancelled_at as "cancelledAt",
           rr.failure_reason as "failureReason", rr.metadata,
           rr.created_at as "createdAt", rr.updated_at as "updatedAt"
    from public.commerce_refund_requests rr
    join public.orders o on o.tenant_id = rr.tenant_id and o.id = rr.order_id
    where ${where.join(' and ')}
    order by rr.created_at desc
    limit $2
    `,
    params,
  );

  return { items: items.map(refundPayload) };
}

export async function createRefundRequestRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:refund:write');
  const body = await readJsonBody(ctx);
  const orderNo = requiredString(body, 'orderNo');
  const requestedAmountCents = Math.max(0, optionalInteger(body, 'amountCents', 0));
  const reason = optionalString(body, 'reason') || null;
  const refundNo = normalizeRefundNo(optionalString(body, 'refundNo') || createRefundNo());
  const entitlementAction = optionalChoice(body.entitlementAction, ['none', 'revoke_on_success'] as const, 'revoke_on_success');
  const metadata = objectValue(body.metadata);

  const item = await transaction(async client => {
    const orderResult = await client.query<{
      id: string;
      orderNo: string;
      status: string;
      amountCents: number;
      refundedAmountCents: number;
      userId: string | null;
      payProvider: string | null;
      payMethod: string | null;
    }>(
      `
      select id, order_no as "orderNo", status, amount_cents as "amountCents",
             refunded_amount_cents as "refundedAmountCents", user_id as "userId",
             pay_provider as "payProvider", pay_method as "payMethod"
      from public.orders
      where tenant_id = $1 and order_no = $2
      limit 1
      for update
      `,
      [auth.tenantId, orderNo],
    );
    const order = orderResult.rows[0];
    if (!order) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');
    if (!order.userId) throw new HttpError(409, 'Order has no user', 'ORDER_USER_MISSING');
    if (!['paid', 'partially_refunded'].includes(order.status)) {
      throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_REFUNDABLE');
    }

    const existingByRefundNo = await client.query<{ id: string; order_id: string; amount_cents: number }>(
      `
      select id, order_id, amount_cents
      from public.commerce_refund_requests
      where tenant_id = $1 and refund_no = $2
      limit 1
      for update
      `,
      [auth.tenantId, refundNo],
    );
    const existingRefund = existingByRefundNo.rows[0];
    if (existingRefund) {
      if (existingRefund.order_id !== order.id || (requestedAmountCents > 0 && requestedAmountCents !== existingRefund.amount_cents)) {
        throw new HttpError(409, 'Refund number already belongs to another refund request', 'REFUND_NO_CONFLICT');
      }
      const refund = await fetchRefundById(client, auth.tenantId, existingRefund.id, true);
      if (!refund) throw new HttpError(500, 'Refund request lookup failed', 'REFUND_LOOKUP_FAILED');
      return { ...refundPayload(refund), idempotent: true };
    }

    const alreadySucceeded = await client.query<{ total: number }>(
      `
      select coalesce(sum(amount_cents), 0)::integer as total
      from public.commerce_refund_requests
      where tenant_id = $1
        and order_id = $2
        and status in ('requested', 'approved', 'processing', 'succeeded')
      `,
      [auth.tenantId, order.id],
    );
    const reservedAmount = Number(alreadySucceeded.rows[0]?.total || 0);
    const remainingAmount = Math.max(0, order.amountCents - reservedAmount);
    const amountCents = requestedAmountCents || remainingAmount;
    if (amountCents <= 0) throw new HttpError(409, 'Order has no refundable amount', 'ORDER_NOT_REFUNDABLE');
    if (amountCents > remainingAmount) {
      throw new HttpError(409, 'Refund amount exceeds paid amount', 'REFUND_AMOUNT_EXCEEDS_PAID');
    }

    const paymentResult = await client.query<PaymentRow & { provider: string | null }>(
      `
      select id, provider, status, amount_cents, refunded_amount_cents
      from public.payments
      where tenant_id = $1
        and order_id = $2
        and status in ('paid', 'partially_refunded', 'refunded')
      order by paid_at desc nulls last, updated_at desc
      limit 1
      for update
      `,
      [auth.tenantId, order.id],
    );
    const payment = paymentResult.rows[0];
    if (!payment && amountCents > 0) throw new HttpError(404, 'Paid payment not found', 'PAYMENT_NOT_FOUND');
    if (payment && amountCents > payment.amount_cents - (payment.refunded_amount_cents || 0)) {
      throw new HttpError(409, 'Refund amount exceeds payment amount', 'REFUND_AMOUNT_EXCEEDS_PAID');
    }

    const inserted = await client.query<RefundRequestRow>(
      `
      insert into public.commerce_refund_requests (
        tenant_id, order_id, payment_id, refund_no, provider, status, amount_cents,
        reason, entitlement_action, requested_by, metadata
      )
      values ($1, $2, $3, $4, $5, 'requested', $6, $7, $8, $9, $10::jsonb)
      returning id
      `,
      [
        auth.tenantId,
        order.id,
        payment?.id || null,
        refundNo,
        payment?.provider || order.payProvider || null,
        amountCents,
        reason,
        entitlementAction,
        auth.userId,
        JSON.stringify(metadata),
      ],
    );

    const refund = await fetchRefundById(client, auth.tenantId, inserted.rows[0].id, true);
    if (!refund) throw new HttpError(500, 'Refund request was not created', 'REFUND_CREATE_FAILED');
    await recordRefundEvent(client, {
      tenantId: auth.tenantId,
      refundRequestId: refund.id,
      toStatus: refund.status,
      eventType: 'created',
      actorUserId: auth.userId,
      details: { orderNo, amountCents, reason, entitlementAction },
    });
    await recordCommerceAudit(client, {
      tenantId: auth.tenantId,
      actorUserId: auth.userId,
      action: 'commerce.refund.requested',
      targetType: 'commerce_refund_request',
      targetId: refund.id,
      details: { refundNo: refund.refundNo, orderNo, amountCents, reason, entitlementAction },
    });
    return refundPayload(refund);
  });

  return { item };
}

export async function updateRefundStatusRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  const body = await readJsonBody(ctx);
  const refundId = requiredString(body, 'refundId');
  const action = requiredString(body, 'action');
  const note = optionalString(body, 'note') || null;
  const providerRefundNo = optionalString(body, 'providerRefundNo') || null;
  const providerNotifyUrl = optionalString(body, 'providerNotifyUrl') || null;
  const failureReason = optionalString(body, 'failureReason') || note;
  const metadataPatch = objectValue(body.metadata);

  const reviewActions = new Set(['approve', 'reject', 'cancel']);
  requireTenantPermission(auth, reviewActions.has(action) ? 'tenant:refund:review' : 'tenant:refund:write');

  const item = await transaction(async client => {
    const refund = await fetchRefundById(client, auth.tenantId, refundId, true);
    if (!refund) throw new HttpError(404, 'Refund request not found', 'REFUND_NOT_FOUND');

    const fromStatus = refund.status;
    let toStatus = fromStatus;
    let eventType = action;
    let processResult: Record<string, unknown> = {};
    let providerResult: Record<string, unknown> = {};
    let statusFailureReason = failureReason;
    const details: Record<string, unknown> = { note, providerRefundNo, providerNotifyUrl, failureReason, metadata: metadataPatch };

    if (action === 'approve') {
      if (fromStatus !== 'requested') throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      toStatus = 'approved';
    } else if (action === 'reject') {
      if (!['requested', 'approved'].includes(fromStatus)) {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      toStatus = 'rejected';
    } else if (action === 'mark_processing') {
      if (!['requested', 'approved'].includes(fromStatus)) {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      toStatus = 'processing';
      eventType = 'processing';
    } else if (action === 'submit_provider_refund') {
      if (fromStatus !== 'approved') {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      const submitted = await submitRefundToProvider(client, {
        tenantId: auth.tenantId,
        refund,
        actorUserId: auth.userId,
        notifyUrl: providerNotifyUrl,
      });
      toStatus = submitted.nextStatus;
      eventType = submitted.nextStatus === 'succeeded'
        ? 'provider_succeeded'
        : submitted.nextStatus === 'failed'
          ? 'provider_failed'
          : 'provider_submitted';
      providerResult = submitted.refundResult.raw;
      processResult = submitted.processResult;
      details.provider = submitted.refundResult.provider;
      details.providerStatus = submitted.refundResult.status;
      details.providerRefundNo = submitted.refundResult.providerRefundNo || providerRefundNo || null;
      details.processResult = processResult;
      if (submitted.refundResult.failureReason) {
        statusFailureReason = submitted.refundResult.failureReason;
        details.failureReason = submitted.refundResult.failureReason;
      }
    } else if (action === 'query_provider_refund') {
      if (fromStatus !== 'processing') {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      const queried = await queryRefundFromProvider(client, {
        tenantId: auth.tenantId,
        refund,
        actorUserId: auth.userId,
      });
      toStatus = queried.nextStatus;
      eventType = queried.nextStatus === 'succeeded'
        ? 'provider_query_succeeded'
        : queried.nextStatus === 'failed'
          ? 'provider_query_failed'
          : 'provider_query_processing';
      providerResult = queried.refundResult.raw;
      processResult = queried.processResult;
      details.provider = queried.refundResult.provider;
      details.providerStatus = queried.refundResult.status;
      details.providerRefundNo = queried.refundResult.providerRefundNo || providerRefundNo || null;
      statusFailureReason = queried.refundResult.failureReason || failureReason;
      details.failureReason = statusFailureReason;
      details.processResult = processResult;
    } else if (action === 'mark_succeeded') {
      if (!['requested', 'approved', 'processing'].includes(fromStatus)) {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      processResult = await applySuccessfulRefund(client, {
        tenantId: auth.tenantId,
        refund,
        actorUserId: auth.userId,
        providerRefundNo,
      });
      toStatus = 'succeeded';
      eventType = 'succeeded';
      details.processResult = processResult;
    } else if (action === 'mark_failed') {
      if (!['approved', 'processing'].includes(fromStatus)) {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      toStatus = 'failed';
      eventType = 'failed';
    } else if (action === 'cancel') {
      if (!['requested', 'approved'].includes(fromStatus)) {
        throw new HttpError(409, `Refund status is ${fromStatus}`, 'REFUND_STATUS_INVALID');
      }
      toStatus = 'cancelled';
    } else {
      throw new HttpError(400, 'Unsupported refund action', 'REFUND_ACTION_INVALID');
    }

    const updated = await client.query<RefundRequestRow>(
      `
      update public.commerce_refund_requests
      set status = $3,
          provider_refund_no = coalesce($4, $8, provider_refund_no),
          reviewed_by = case when $3 in ('approved', 'rejected') then $5 else reviewed_by end,
          processed_by = case when $3 in ('processing', 'succeeded', 'failed') then $5 else processed_by end,
          reviewed_at = case when $3 in ('approved', 'rejected') then now() else reviewed_at end,
          processed_at = case when $3 in ('processing', 'succeeded', 'failed') then now() else processed_at end,
          succeeded_at = case when $3 = 'succeeded' then now() else succeeded_at end,
          failed_at = case when $3 = 'failed' then now() else failed_at end,
          cancelled_at = case when $3 = 'cancelled' then now() else cancelled_at end,
          failure_reason = case when $3 = 'failed' then $6 else failure_reason end,
          metadata = coalesce(metadata, '{}'::jsonb) || $7::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id
      `,
      [
        auth.tenantId,
        refund.id,
        toStatus,
        providerRefundNo,
        auth.userId,
        statusFailureReason,
        JSON.stringify({ lastAction: action, note, processResult, providerResult, ...metadataPatch }),
        typeof details.providerRefundNo === 'string' ? details.providerRefundNo : null,
      ],
    );
    const updatedRefund = await fetchRefundById(client, auth.tenantId, updated.rows[0].id, true);
    if (!updatedRefund) throw new HttpError(500, 'Refund request update failed', 'REFUND_UPDATE_FAILED');

    await recordRefundEvent(client, {
      tenantId: auth.tenantId,
      refundRequestId: refund.id,
      fromStatus,
      toStatus,
      eventType,
      actorUserId: auth.userId,
      details,
    });
    await recordCommerceAudit(client, {
      tenantId: auth.tenantId,
      actorUserId: auth.userId,
      action: `commerce.refund.${eventType}`,
      targetType: 'commerce_refund_request',
      targetId: refund.id,
      details: {
        refundNo: refund.refundNo,
        orderNo: refund.orderNo,
        fromStatus,
        toStatus,
        amountCents: refund.amountCents,
        providerRefundNo: details.providerRefundNo || providerRefundNo,
        processResult,
      },
    });

    return refundPayload(updatedRefund);
  });

  return { item };
}

export async function confirmManualPaymentRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:payment:write');
  const tenantId = auth.tenantId;
  const orderNo = requiredString(body, 'orderNo');
  const providerTradeNo = optionalString(body, 'providerTradeNo') || `manual-${orderNo}`;
  const amountCents = optionalInteger(body, 'amountCents', -1);

  const item = await transaction(async client => {
    const orderResult = await client.query<OrderRow>(
      `
      select id, order_no, status, amount_cents, days, user_id, region_id
      from public.orders
      where tenant_id = $1 and order_no = $2
      limit 1
      for update
      `,
      [tenantId, orderNo],
    );

    const order = orderResult.rows[0];
    if (!order) {
      throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');
    }
    if (!order.user_id) {
      throw new HttpError(409, 'Order has no user', 'ORDER_USER_MISSING');
    }
    if (amountCents >= 0 && amountCents !== order.amount_cents) {
      throw new HttpError(409, 'Payment amount mismatch', 'PAYMENT_AMOUNT_MISMATCH');
    }

    if (order.status === 'paid') {
      return { orderNo, status: 'paid', idempotent: true };
    }
    if (['partially_refunded', 'refunded'].includes(order.status)) {
      throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_PAYABLE');
    }
    if (order.status !== 'pending') {
      throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_PAYABLE');
    }

    await client.query(
      `
      update public.orders
      set status = 'paid', trade_no = $3, paid_at = now(), updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [tenantId, order.id, providerTradeNo],
    );

    await client.query(
      `
      update public.payments
      set status = 'paid', provider_trade_no = $3, paid_at = now(), updated_at = now(),
          raw_payload = coalesce(raw_payload, '{}'::jsonb) || $4::jsonb
      where tenant_id = $1 and order_id = $2
      `,
      [tenantId, order.id, providerTradeNo, JSON.stringify({ manualConfirm: body })],
    );

    const entitlement = await grantSvipEntitlement(client, {
      tenantId,
      userId: order.user_id,
      days: order.days || 0,
      regionId: order.region_id,
      sourceType: 'order',
      sourceId: order.id,
      metadata: { orderNo },
    });

    return { orderNo, status: 'paid', confirmedBy: auth.userId, entitlement };
  });

  return { item };
}

function requestHeaders(ctx: RequestContext) {
  const headers: Record<string, string> = {};
  for (const [key, value] of Object.entries(ctx.req.headers)) {
    headers[key.toLowerCase()] = Array.isArray(value) ? value[0] || '' : value || '';
  }
  return headers;
}

async function readWebhookBody(ctx: RequestContext) {
  const contentLength = Number(getHeader(ctx.req, 'content-length') || 0);
  const maxBytes = 1024 * 1024;
  if (Number.isFinite(contentLength) && contentLength > maxBytes) {
    throw new HttpError(413, 'Webhook body is too large', 'WEBHOOK_BODY_TOO_LARGE');
  }

  const chunks: Buffer[] = [];
  let totalBytes = 0;
  for await (const chunk of ctx.req) {
    const buffer = Buffer.isBuffer(chunk) ? chunk : Buffer.from(chunk);
    totalBytes += buffer.length;
    if (totalBytes > maxBytes) {
      ctx.req.destroy();
      throw new HttpError(413, 'Webhook body is too large', 'WEBHOOK_BODY_TOO_LARGE');
    }
    chunks.push(buffer);
  }

  const rawBody = Buffer.concat(chunks).toString('utf8');
  const contentType = getHeader(ctx.req, 'content-type').toLowerCase();
  if (contentType.includes('application/x-www-form-urlencoded')) {
    return {
      rawBody,
      body: Object.fromEntries(new URLSearchParams(rawBody).entries()),
    };
  }
  try {
    const parsed = rawBody.trim() ? JSON.parse(rawBody) : {};
    if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) {
      throw new HttpError(400, 'Webhook JSON body must be an object', 'INVALID_WEBHOOK_BODY');
    }
    return { rawBody, body: parsed as Record<string, unknown> };
  } catch (error) {
    if (error instanceof HttpError) throw error;
    throw new HttpError(400, 'Invalid webhook body', 'INVALID_WEBHOOK_BODY');
  }
}

function paidAtValue(value?: string) {
  return value || new Date().toISOString();
}

async function loadPaymentProvider(tenantId: string, providerName: string) {
  const provider = normalizePaymentProvider(providerName);
  const providerConfig =
    provider === 'manual' ? null : await loadTenantPaymentProviderConfig(tenantId, paymentProviderAliases(provider));
  return createPaymentProvider(provider, providerConfig);
}

async function submitRefundToProvider(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    refund: RefundRequestRow;
    actorUserId: string;
    notifyUrl?: string | null;
  },
) {
  const paymentResult = await client.query<{
    id: string;
    provider: string;
    providerTradeNo: string | null;
    amountCents: number;
  }>(
    `
    select id, provider, provider_trade_no as "providerTradeNo", amount_cents as "amountCents"
    from public.payments
    where tenant_id = $1 and id = $2
    limit 1
    `,
    [input.tenantId, input.refund.paymentId],
  );
  const payment = paymentResult.rows[0];
  if (!payment) throw new HttpError(404, 'Payment not found', 'PAYMENT_NOT_FOUND');
  if (normalizePaymentProvider(payment.provider) === 'manual') {
    throw new HttpError(409, 'Manual payment refunds must be recorded manually', 'PAYMENT_PROVIDER_REFUND_NOT_SUPPORTED');
  }

  const provider = await loadPaymentProvider(input.tenantId, payment.provider);
  const refundResult = await provider.createRefund({
    tenantId: input.tenantId,
    orderId: input.refund.orderId,
    orderNo: input.refund.orderNo,
    refundNo: input.refund.refundNo,
    amountCents: input.refund.amountCents,
    totalAmountCents: payment.amountCents,
    providerTradeNo: payment.providerTradeNo,
    reason: input.refund.reason,
    notifyUrl: input.notifyUrl || null,
  });

  if (refundResult.status === 'succeeded') {
    const processResult = await applySuccessfulRefund(client, {
      tenantId: input.tenantId,
      refund: input.refund,
      actorUserId: input.actorUserId,
      providerRefundNo: refundResult.providerRefundNo || null,
    });
    return { refundResult, nextStatus: 'succeeded', processResult };
  }
  if (refundResult.status === 'failed') {
    return { refundResult, nextStatus: 'failed', processResult: {} };
  }

  return { refundResult, nextStatus: 'processing', processResult: {} };
}

async function queryRefundFromProvider(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    refund: RefundRequestRow;
    actorUserId: string;
  },
) {
  const paymentResult = await client.query<{
    id: string;
    provider: string;
    providerTradeNo: string | null;
    amountCents: number;
  }>(
    `
    select id, provider, provider_trade_no as "providerTradeNo", amount_cents as "amountCents"
    from public.payments
    where tenant_id = $1 and id = $2
    limit 1
    `,
    [input.tenantId, input.refund.paymentId],
  );
  const payment = paymentResult.rows[0];
  if (!payment) throw new HttpError(404, 'Payment not found', 'PAYMENT_NOT_FOUND');
  if (normalizePaymentProvider(payment.provider) === 'manual') {
    throw new HttpError(409, 'Manual payment refunds must be recorded manually', 'PAYMENT_PROVIDER_REFUND_NOT_SUPPORTED');
  }

  const provider = await loadPaymentProvider(input.tenantId, payment.provider);
  const refundResult = await provider.queryRefund({
    tenantId: input.tenantId,
    orderId: input.refund.orderId,
    orderNo: input.refund.orderNo,
    refundNo: input.refund.refundNo,
    amountCents: input.refund.amountCents,
    totalAmountCents: payment.amountCents,
    providerTradeNo: payment.providerTradeNo,
    providerRefundNo: input.refund.providerRefundNo,
  });

  if (refundResult.status === 'succeeded') {
    const processResult = await applySuccessfulRefund(client, {
      tenantId: input.tenantId,
      refund: input.refund,
      actorUserId: input.actorUserId,
      providerRefundNo: refundResult.providerRefundNo || null,
    });
    return { refundResult, nextStatus: 'succeeded', processResult };
  }

  if (refundResult.status === 'failed') {
    return { refundResult, nextStatus: 'failed', processResult: {} };
  }

  return { refundResult, nextStatus: 'processing', processResult: {} };
}

async function settleRefundNotification(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    refund: RefundRequestRow;
    notification: PaymentRefundNotificationResult;
  },
) {
  const { refund, notification } = input;
  if (refund.orderNo !== notification.orderNo) {
    throw new HttpError(409, 'Refund notification order number mismatch', 'REFUND_NOTIFICATION_ORDER_MISMATCH');
  }
  if (refund.amountCents !== notification.amountCents) {
    throw new HttpError(409, 'Refund notification amount mismatch', 'REFUND_NOTIFICATION_AMOUNT_MISMATCH');
  }
  if (refund.provider && normalizePaymentProvider(refund.provider) !== notification.provider) {
    throw new HttpError(409, 'Refund notification provider mismatch', 'REFUND_NOTIFICATION_PROVIDER_MISMATCH');
  }

  if (refund.status === 'succeeded') {
    return { nextStatus: 'succeeded', processResult: {}, idempotent: true, eventType: 'provider_notify_idempotent' };
  }
  if (['failed', 'rejected', 'cancelled'].includes(refund.status)) {
    throw new HttpError(409, `Refund status is ${refund.status}`, 'REFUND_STATUS_INVALID');
  }
  if (!['approved', 'processing'].includes(refund.status)) {
    throw new HttpError(409, `Refund status is ${refund.status}`, 'REFUND_STATUS_INVALID');
  }

  if (notification.status === 'succeeded') {
    const processResult = await applySuccessfulRefund(client, {
      tenantId: input.tenantId,
      refund,
      actorUserId: null,
      providerRefundNo: notification.providerRefundNo || null,
    });
    return { nextStatus: 'succeeded', processResult, idempotent: false, eventType: 'provider_notify_succeeded' };
  }
  if (notification.status === 'failed') {
    return { nextStatus: 'failed', processResult: {}, idempotent: false, eventType: 'provider_notify_failed' };
  }
  return { nextStatus: 'processing', processResult: {}, idempotent: false, eventType: 'provider_notify_processing' };
}

export async function createPaymentRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const body = await readJsonBody(ctx);
  const userId = await userIdFrom(ctx, body);
  const orderNo = requiredString(body, 'orderNo');

  const order = await queryOne<PaymentOrderRow>(
    `
    select id, order_no, status, amount_cents, product_name, pay_provider, pay_method,
           days, user_id, region_id
    from public.orders
    where tenant_id = $1 and order_no = $2 and user_id = $3
    limit 1
    `,
    [tenantId, orderNo, userId],
  );
  if (!order) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');
  if (order.status === 'paid') throw new HttpError(409, 'Order is already paid', 'ORDER_ALREADY_PAID');
  if (order.status !== 'pending') throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_PAYABLE');

  const providerName = optionalString(body, 'provider') || order.pay_provider || 'manual';
  const provider = await loadPaymentProvider(tenantId, providerName);
  const payment = await provider.createPayment({
    tenantId,
    orderId: order.id,
    orderNo: order.order_no,
    description: order.product_name || `题库会员 ${order.order_no}`,
    amountCents: order.amount_cents,
    userId,
    openId: optionalString(body, 'openId') || undefined,
    clientIp: getHeader(ctx.req, 'x-forwarded-for').split(',')[0] || ctx.req.socket.remoteAddress || undefined,
    returnUrl: optionalString(body, 'returnUrl') || undefined,
    quitUrl: optionalString(body, 'quitUrl') || undefined,
  });

  const item = await transaction(async client => {
    const updatedPayment = await client.query(
      `
      update public.payments
      set provider_trade_no = coalesce($4, provider_trade_no),
          method = $5,
          raw_payload = coalesce(raw_payload, '{}'::jsonb) || $6::jsonb,
          updated_at = now()
      where tenant_id = $1
        and order_id = $2
        and provider = $3
        and status = 'pending'
      returning id
      `,
      [
        tenantId,
        order.id,
        payment.provider,
        payment.providerTradeNo || null,
        payment.method,
        JSON.stringify({ createPayment: payment.raw }),
      ],
    );

    if (!updatedPayment.rows[0]) {
      await client.query(
      `
      insert into public.payments (tenant_id, order_id, provider, method, status, amount_cents, provider_trade_no, raw_payload)
      values ($1, $2, $3, $4, 'pending', $5, $6, $7::jsonb)
      `,
        [
          tenantId,
          order.id,
          payment.provider,
          payment.method,
          order.amount_cents,
          payment.providerTradeNo || null,
          JSON.stringify({ createPayment: payment.raw }),
        ],
      );
    }

    await client.query(
      `
      update public.orders
      set pay_provider = $3, pay_method = $4, updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [tenantId, order.id, payment.provider, payment.method],
    );

    return {
      orderNo: order.order_no,
      provider: payment.provider,
      method: payment.method,
      paymentParams: payment.paymentParams,
    };
  });

  return { item };
}

export async function paymentNotifyRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const providerName = ctx.url.pathname.split('/').at(-1) || optionalString(Object.fromEntries(ctx.url.searchParams), 'provider');
  const provider = await loadPaymentProvider(tenantId, providerName);
  const { rawBody, body } = await readWebhookBody(ctx);
  const notification = await provider.parseNotification({
    headers: requestHeaders(ctx),
    body,
    rawBody,
  });
  const eventKey = `${tenantId}:${notification.eventId}`;

  const item = await transaction(async client => {
    const eventResult = await client.query<{
      id: string;
      processedAt: string | null;
      error: string | null;
    }>(
      `
      insert into public.payment_events (
        tenant_id, provider, event_type, event_id, signature_valid, payload
      )
      values ($1, $2, $3, $4, true, $5::jsonb)
      on conflict (provider, event_id)
      do update set payload = case
                        when public.payment_events.processed_at is null or public.payment_events.error is not null
                          then excluded.payload
                        else public.payment_events.payload
                      end,
                    signature_valid = true
      returning id, processed_at as "processedAt", error
      `,
      [
        tenantId,
        notification.provider,
        notification.eventType,
        eventKey,
        JSON.stringify(notification.raw),
      ],
    );

    const event = eventResult.rows[0];
    if (event.processedAt && !event.error) {
      return {
        status: 'processed',
        idempotent: true,
        eventId: notification.eventId,
        orderNo: notification.orderNo,
      };
    }

    try {
      const orderResult = await client.query<OrderRow>(
        `
        select id, order_no, status, amount_cents, days, user_id, region_id
        from public.orders
        where tenant_id = $1 and order_no = $2
        limit 1
        for update
        `,
        [tenantId, notification.orderNo],
      );
      const order = orderResult.rows[0];
      if (!order) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');
      if (!order.user_id) throw new HttpError(409, 'Order has no user', 'ORDER_USER_MISSING');
      if (order.amount_cents !== notification.amountCents) {
        throw new HttpError(409, 'Payment amount mismatch', 'PAYMENT_AMOUNT_MISMATCH');
      }

      let payment = (
        await client.query<PaymentRow>(
          `
          select id, status, amount_cents
          from public.payments
          where tenant_id = $1 and order_id = $2 and provider = $3
          order by created_at desc
          limit 1
          for update
          `,
          [tenantId, order.id, notification.provider],
        )
      ).rows[0];

      if (!payment) {
        const inserted = await client.query<PaymentRow>(
          `
          insert into public.payments (
            tenant_id, order_id, provider, method, status, amount_cents, provider_trade_no, paid_at, raw_payload
          )
          values ($1, $2, $3, 'webhook', 'pending', $4, $5, $6::timestamptz, $7::jsonb)
          returning id, status, amount_cents
          `,
          [
            tenantId,
            order.id,
            notification.provider,
            notification.amountCents,
            notification.providerTradeNo,
            paidAtValue(notification.paidAt),
            JSON.stringify({ notification: notification.raw }),
          ],
        );
        payment = inserted.rows[0];
      }

      if (payment.amount_cents !== notification.amountCents) {
        throw new HttpError(409, 'Payment amount mismatch', 'PAYMENT_AMOUNT_MISMATCH');
      }

      if (order.status === 'paid') {
        await client.query(
          `
          update public.payment_events
          set payment_id = $2, processed_at = coalesce(processed_at, now()), error = null
          where id = $1
          `,
          [event.id, payment.id],
        );
        return {
          status: 'paid',
          idempotent: true,
          eventId: notification.eventId,
          orderNo: order.order_no,
        };
      }
      if (['partially_refunded', 'refunded'].includes(order.status)) {
        throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_PAYABLE');
      }
      if (order.status !== 'pending') {
        throw new HttpError(409, `Order status is ${order.status}`, 'ORDER_NOT_PAYABLE');
      }

      await client.query(
        `
        update public.orders
        set status = 'paid', trade_no = $3, paid_at = $4::timestamptz,
            pay_provider = $5, updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [
          tenantId,
          order.id,
          notification.providerTradeNo,
          paidAtValue(notification.paidAt),
          notification.provider,
        ],
      );

      await client.query(
        `
        update public.payments
        set status = 'paid', provider_trade_no = $3, paid_at = $4::timestamptz,
            raw_payload = coalesce(raw_payload, '{}'::jsonb) || $5::jsonb,
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [
          tenantId,
          payment.id,
          notification.providerTradeNo,
          paidAtValue(notification.paidAt),
          JSON.stringify({ notification: notification.raw }),
        ],
      );

      const entitlement = await grantSvipEntitlement(client, {
        tenantId,
        userId: order.user_id,
        days: order.days || 0,
        regionId: order.region_id,
        sourceType: 'order',
        sourceId: order.id,
        metadata: {
          orderNo: order.order_no,
          paymentProvider: notification.provider,
          providerTradeNo: notification.providerTradeNo,
        },
      });

      await client.query(
        `
        update public.payment_events
        set payment_id = $2, processed_at = now(), error = null
        where id = $1
        `,
        [event.id, payment.id],
      );

      return {
        status: 'paid',
        idempotent: false,
        eventId: notification.eventId,
        orderNo: order.order_no,
        entitlement,
      };
    } catch (error) {
      await client.query(
        `
        update public.payment_events
        set error = $2
        where id = $1
        `,
        [event.id, error instanceof Error ? error.message : 'Unknown payment notification error'],
      );
      throw error;
    }
  });

  return { item };
}

export async function refundNotifyRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const providerName = ctx.url.pathname.split('/').at(-1) || optionalString(Object.fromEntries(ctx.url.searchParams), 'provider');
  const provider = await loadPaymentProvider(tenantId, providerName);
  const { rawBody, body } = await readWebhookBody(ctx);
  const notification = await provider.parseRefundNotification({
    headers: requestHeaders(ctx),
    body,
    rawBody,
  });
  const eventKey = `${tenantId}:refund:${notification.eventId}`;

  const item = await transaction(async client => {
    const eventResult = await client.query<{
      id: string;
      processedAt: string | null;
      error: string | null;
    }>(
      `
      insert into public.payment_events (
        tenant_id, provider, event_type, event_id, signature_valid, payload
      )
      values ($1, $2, $3, $4, true, $5::jsonb)
      on conflict (provider, event_id)
      do update set payload = case
                        when public.payment_events.processed_at is null or public.payment_events.error is not null
                          then excluded.payload
                        else public.payment_events.payload
                      end,
                    signature_valid = true
      returning id, processed_at as "processedAt", error
      `,
      [
        tenantId,
        notification.provider,
        notification.eventType,
        eventKey,
        JSON.stringify(notification.raw),
      ],
    );

    const event = eventResult.rows[0];
    if (event.processedAt && !event.error) {
      return {
        status: 'processed',
        idempotent: true,
        eventId: notification.eventId,
        refundNo: notification.refundNo,
      };
    }

    try {
      const refund = await fetchRefundByNo(client, tenantId, notification.refundNo, true);
      if (!refund) throw new HttpError(404, 'Refund request not found', 'REFUND_NOT_FOUND');
      const settled = await settleRefundNotification(client, {
        tenantId,
        refund,
        notification,
      });

      if (settled.idempotent) {
        await client.query(
          `
          update public.payment_events
          set payment_id = $2, processed_at = coalesce(processed_at, now()), error = null
          where id = $1
          `,
          [event.id, refund.paymentId],
        );
        return {
          status: settled.nextStatus,
          idempotent: true,
          eventId: notification.eventId,
          refundNo: notification.refundNo,
          item: refundPayload(refund),
        };
      }

      const updated = await client.query<RefundRequestRow>(
        `
        update public.commerce_refund_requests
        set status = $3,
            provider_refund_no = coalesce($4, provider_refund_no),
            processed_by = case when $3 in ('processing', 'succeeded', 'failed') then null else processed_by end,
            processed_at = case when $3 in ('processing', 'succeeded', 'failed') then now() else processed_at end,
            succeeded_at = case when $3 = 'succeeded' then coalesce(succeeded_at, now()) else succeeded_at end,
            failed_at = case when $3 = 'failed' then coalesce(failed_at, now()) else failed_at end,
            failure_reason = case when $3 = 'failed' then $5 else failure_reason end,
            metadata = coalesce(metadata, '{}'::jsonb) || $6::jsonb,
            updated_at = now()
        where tenant_id = $1 and id = $2
        returning id
        `,
        [
          tenantId,
          refund.id,
          settled.nextStatus,
          notification.providerRefundNo || null,
          notification.failureReason || null,
          JSON.stringify({
            lastAction: 'refund_notify',
            providerResult: notification.raw,
            processResult: settled.processResult,
            notifiedAt: notification.notifiedAt || null,
          }),
        ],
      );
      const updatedRefund = await fetchRefundById(client, tenantId, updated.rows[0].id, true);
      if (!updatedRefund) throw new HttpError(500, 'Refund request update failed', 'REFUND_UPDATE_FAILED');

      await recordRefundEvent(client, {
        tenantId,
        refundRequestId: refund.id,
        fromStatus: refund.status,
        toStatus: settled.nextStatus,
        eventType: settled.eventType,
        actorUserId: null,
        details: {
          source: 'payment_provider_notify',
          provider: notification.provider,
          providerStatus: notification.status,
          providerRefundNo: notification.providerRefundNo || null,
          eventId: notification.eventId,
          notifiedAt: notification.notifiedAt || null,
          failureReason: notification.failureReason || null,
          processResult: settled.processResult,
          idempotent: settled.idempotent,
        },
      });

      await client.query(
        `
        update public.payment_events
        set payment_id = $2, processed_at = now(), error = null
        where id = $1
        `,
        [event.id, refund.paymentId],
      );

      return {
        status: settled.nextStatus,
        idempotent: settled.idempotent,
        eventId: notification.eventId,
        refundNo: notification.refundNo,
        item: refundPayload(updatedRefund),
      };
    } catch (error) {
      await client.query(
        `
        update public.payment_events
        set error = $2
        where id = $1
        `,
        [event.id, error instanceof Error ? error.message : 'Unknown refund notification error'],
      );
      throw error;
    }
  });

  return { item };
}

export async function checkActivationCodeRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const code = normalizeCode(requiredString(body, 'code'));
  const regionId = optionalString(body, 'regionId') || null;

  const activationCode = await queryOne<{
    id: string;
    code: string;
    days: number;
    isUsed: boolean;
    usedRegionId: string | null;
    agentUserId: string | null;
    saleType: string | null;
    unitPriceCents: number | null;
    soldTo: string | null;
    remark: string | null;
    regionName: string | null;
  }>(
    `
    select ac.id, ac.code::text as code, ac.days, ac.is_used as "isUsed",
           ac.used_region_id as "usedRegionId", ac.agent_user_id as "agentUserId",
           ac.sale_type as "saleType", ac.unit_price_cents as "unitPriceCents",
           ac.sold_to as "soldTo", ac.remark, r.name as "regionName"
    from public.activation_codes ac
    left join public.regions r on r.tenant_id = ac.tenant_id and r.id = ac.used_region_id
    where ac.tenant_id = $1 and lower(ac.code::text) = lower($2)
    limit 1
    `,
    [tenantId, code],
  );

  if (!activationCode) {
    return {
      valid: false,
      code,
      reasonCode: 'ACTIVATION_CODE_NOT_FOUND',
      message: 'Activation code not found',
    };
  }
  if (activationCode.isUsed) {
    return {
      valid: false,
      code: activationCode.code,
      reasonCode: 'ACTIVATION_CODE_USED',
      message: 'Activation code already used',
      days: activationCode.days,
    };
  }
  if (activationCode.agentUserId && activationCode.agentUserId === userId) {
    return {
      valid: false,
      code: activationCode.code,
      reasonCode: 'ACTIVATION_CODE_SELF_REDEEM_FORBIDDEN',
      message: 'Cannot redeem your own activation code',
      days: activationCode.days,
    };
  }
  if (regionId && activationCode.usedRegionId && regionId !== activationCode.usedRegionId) {
    return {
      valid: false,
      code: activationCode.code,
      reasonCode: 'ACTIVATION_CODE_REGION_MISMATCH',
      message: 'Activation code does not apply to this region',
      days: activationCode.days,
      regionId: activationCode.usedRegionId,
      regionName: activationCode.regionName,
    };
  }

  return {
    valid: true,
    code: activationCode.code,
    days: activationCode.days,
    regionId: activationCode.usedRegionId,
    regionName: activationCode.regionName,
    saleType: activationCode.saleType,
    unitPriceCents: activationCode.unitPriceCents,
    soldTo: activationCode.soldTo,
    remark: activationCode.remark,
  };
}

export async function redeemActivationCodeRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const code = normalizeCode(requiredString(body, 'code'));
  const regionId = optionalString(body, 'regionId') || null;

  const item = await transaction(async client => {
    const codeResult = await client.query<{
      id: string;
      code: string;
      days: number;
      is_used: boolean;
      used_region_id: string | null;
      agent_user_id: string | null;
    }>(
      `
      select id, code::text as code, days, is_used, used_region_id, agent_user_id
      from public.activation_codes
      where tenant_id = $1 and lower(code::text) = lower($2)
      limit 1
      for update
      `,
      [tenantId, code],
    );

    const activationCode = codeResult.rows[0];
    if (!activationCode) {
      throw new HttpError(404, 'Activation code not found', 'ACTIVATION_CODE_NOT_FOUND');
    }
    if (activationCode.is_used) {
      throw new HttpError(409, 'Activation code already used', 'ACTIVATION_CODE_USED');
    }
    if (activationCode.agent_user_id && activationCode.agent_user_id === userId) {
      throw new HttpError(409, 'Cannot redeem your own activation code', 'ACTIVATION_CODE_SELF_REDEEM_FORBIDDEN');
    }
    if (regionId && activationCode.used_region_id && regionId !== activationCode.used_region_id) {
      throw new HttpError(409, 'Activation code does not apply to this region', 'ACTIVATION_CODE_REGION_MISMATCH');
    }

    const finalRegionId = regionId || activationCode.used_region_id;
    await client.query(
      `
      update public.activation_codes
      set is_used = true, used_by = $3, used_at = now(),
          used_region_id = coalesce($4::uuid, used_region_id),
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [tenantId, activationCode.id, userId, finalRegionId],
    );

    const entitlement = await grantSvipEntitlement(client, {
      tenantId,
      userId,
      days: activationCode.days,
      regionId: finalRegionId,
      sourceType: 'activation_code',
      sourceId: activationCode.id,
      legacySourceId: activationCode.code,
      metadata: { code: activationCode.code },
    });

    return { code: activationCode.code, entitlement };
  });

  return { item };
}

export async function claimCouponRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const code = normalizeCode(requiredString(body, 'code'));
  const planId = optionalString(body, 'planId') || null;
  const regionId = optionalString(body, 'regionId') || null;

  const result = await transaction(async client => {
    const claimed = await claimCoupon(client, {
      tenantId,
      userId,
      code,
      planId,
      regionId,
    });
    return {
      valid: true,
      idempotent: claimed.idempotent,
      coupon: couponPayload(claimed.coupon, claimed.plan, claimed.redemption, claimed.discountCents),
      redemption: {
        id: claimed.redemption.id,
        status: claimed.redemption.status,
        claimedAt: claimed.redemption.claimed_at,
        usedAt: claimed.redemption.used_at,
      },
    };
  });

  return result;
}
