import crypto from 'node:crypto';
import type pg from 'pg';
import { pool } from '../db.js';
import { config } from '../config.js';

type CommerceProvider = 'wechat_pay' | 'alipay';
type PaymentQueryStatus = 'paid' | 'pending' | 'closed' | 'failed';
type RefundQueryStatus = 'succeeded' | 'processing' | 'failed';

interface SecretRow {
  secretValue: string | null;
  secretJson: Record<string, unknown> | null;
}

interface ProviderConfig {
  tenantId: string;
  provider: CommerceProvider;
  configPublic: Record<string, unknown>;
  secret: SecretRow | null;
}

interface PaymentCandidate {
  id: string;
  tenantId: string;
  orderId: string;
  orderNo: string;
  provider: CommerceProvider;
  paymentStatus: string;
  orderStatus: string;
  amountCents: number;
  userId: string | null;
  days: number | null;
  regionId: string | null;
}

interface RefundCandidate {
  id: string;
  tenantId: string;
  refundNo: string;
  orderId: string;
  paymentId: string | null;
  orderNo: string;
  provider: CommerceProvider;
  providerRefundNo: string | null;
  status: string;
  amountCents: number;
  reason: string | null;
  entitlementAction: string;
  paymentAmountCents: number;
  providerTradeNo: string | null;
}

interface PaymentQueryResult {
  provider: CommerceProvider;
  status: PaymentQueryStatus;
  eventType: string;
  orderNo: string;
  providerTradeNo?: string | null;
  amountCents?: number | null;
  paidAt?: string | null;
  failureReason?: string | null;
  raw: Record<string, unknown>;
}

interface RefundQueryResult {
  provider: CommerceProvider;
  status: RefundQueryStatus;
  eventType: string;
  providerRefundNo?: string | null;
  failureReason?: string | null;
  raw: Record<string, unknown>;
}

interface ProcessResult {
  processed: number;
  payments: {
    processed: number;
    paid: number;
    pending: number;
    closed: number;
    failed: number;
    errors: number;
  };
  refunds: {
    processed: number;
    succeeded: number;
    processing: number;
    failed: number;
    errors: number;
  };
}

class CommerceWorkerError extends Error {
  readonly code: string;

  constructor(message: string, code = 'COMMERCE_WORKER_ERROR') {
    super(message);
    this.code = code;
  }
}

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

function optionalPublicString(providerConfig: ProviderConfig, keys: string[]) {
  for (const key of keys) {
    const value = providerConfig.configPublic[key];
    if (typeof value === 'string' && value.trim()) return value.trim();
  }
  return '';
}

function requirePublicString(providerConfig: ProviderConfig, keys: string[]) {
  const value = optionalPublicString(providerConfig, keys);
  if (value) return value;
  throw new CommerceWorkerError(`${providerConfig.provider} public config is missing ${keys[0]}`, 'PAYMENT_PUBLIC_CONFIG_REQUIRED');
}

function secretString(providerConfig: ProviderConfig, keys: string[]) {
  const secret = providerConfig.secret;
  if (!secret) return '';
  if (typeof secret.secretValue === 'string' && secret.secretValue.trim()) return secret.secretValue.trim();
  const json = objectValue(secret.secretJson);
  for (const key of keys) {
    const value = json[key];
    if (typeof value === 'string' && value.trim()) return value.trim();
  }
  return '';
}

function requireSecretString(providerConfig: ProviderConfig, keys: string[]) {
  const value = secretString(providerConfig, keys);
  if (value) return value;
  throw new CommerceWorkerError(`${providerConfig.provider} secret is missing ${keys[0]}`, 'PAYMENT_SECRET_REQUIRED');
}

function normalizeProvider(value: string | null | undefined): CommerceProvider | null {
  const normalized = String(value || '').toLowerCase().replace(/[-\s]/g, '_');
  if (['wechat', 'wechatpay', 'wxpay', 'wx_pay', 'wechat_pay'].includes(normalized)) return 'wechat_pay';
  if (['alipay', 'ali_pay'].includes(normalized)) return 'alipay';
  return null;
}

function providerAliases(provider: CommerceProvider) {
  return provider === 'wechat_pay'
    ? ['wechat_pay', 'wechat-pay', 'wechatpay', 'wxpay']
    : ['alipay', 'ali_pay'];
}

function parseSecretRef(value: unknown) {
  if (typeof value !== 'string' || !value.trim()) return null;
  const parts = value.trim().split(':');
  if (parts.length !== 3 || parts[0] !== 'app_private.tenant_secrets') return null;
  if (parts[1] !== 'payment' || !parts[2]) return null;
  return { scope: parts[1], key: parts[2] };
}

function truncate(value: unknown, max = 1900) {
  return String(value ?? '').slice(0, max);
}

function normalizePem(value: string, label: 'PRIVATE KEY' | 'PUBLIC KEY') {
  if (value.includes('-----BEGIN')) return value;
  const wrapped = value.match(/.{1,64}/g)?.join('\n') || value;
  return `-----BEGIN ${label}-----\n${wrapped}\n-----END ${label}-----`;
}

function rsaSignSha256(privateKey: string, message: string) {
  return crypto.createSign('RSA-SHA256').update(message, 'utf8').sign(privateKey, 'base64');
}

function randomNonce(size = 16) {
  return crypto.randomBytes(size).toString('base64url');
}

function canonicalForm(params: Record<string, string>) {
  return Object.keys(params)
    .filter(key => params[key] !== undefined && params[key] !== null && params[key] !== '')
    .sort()
    .map(key => `${key}=${params[key]}`)
    .join('&');
}

function encodedForm(params: Record<string, string>) {
  return Object.keys(params)
    .filter(key => params[key] !== undefined && params[key] !== null && params[key] !== '')
    .sort()
    .map(key => `${encodeURIComponent(key)}=${encodeURIComponent(params[key])}`)
    .join('&');
}

function safeJson(value: unknown) {
  return objectValue(value);
}

function optionalIntegerAmount(value: unknown) {
  const amount = typeof value === 'number' ? value : Number.NaN;
  return Number.isInteger(amount) && amount >= 0 ? amount : null;
}

function optionalYuanToCents(value: unknown) {
  if (typeof value !== 'string' && typeof value !== 'number') return null;
  const amount = Number(value);
  if (!Number.isFinite(amount) || amount < 0) return null;
  return Math.round(amount * 100);
}

function nowIso() {
  return new Date().toISOString();
}

function isLocalDevHost(hostname: string) {
  return ['127.0.0.1', 'localhost', '::1'].includes(hostname);
}

function isAllowedHost(hostname: string, allowedHosts: string[]) {
  return allowedHosts.some(host => hostname === host || hostname.endsWith(`.${host}`));
}

function providerEndpointForKeys(
  providerConfig: ProviderConfig,
  keys: string[],
  fallback: string,
  allowedHosts: string[],
) {
  const raw = optionalPublicString(providerConfig, keys) || fallback;
  let endpoint: URL;
  try {
    endpoint = new URL(raw);
  } catch {
    throw new CommerceWorkerError(`${providerConfig.provider} endpoint is invalid`, 'PAYMENT_ENDPOINT_INVALID');
  }

  const localDev = process.env.NODE_ENV !== 'production' && isLocalDevHost(endpoint.hostname);
  if (!localDev && endpoint.protocol !== 'https:') {
    throw new CommerceWorkerError(`${providerConfig.provider} endpoint must use HTTPS`, 'PAYMENT_ENDPOINT_NOT_ALLOWED');
  }
  if (!localDev && !isAllowedHost(endpoint.hostname, allowedHosts)) {
    throw new CommerceWorkerError(`${providerConfig.provider} endpoint host is not allowed`, 'PAYMENT_ENDPOINT_NOT_ALLOWED');
  }
  endpoint.username = '';
  endpoint.password = '';
  return endpoint.toString();
}

function wechatPrivateKey(providerConfig: ProviderConfig) {
  return normalizePem(requireSecretString(providerConfig, ['privateKey', 'merchantPrivateKey']), 'PRIVATE KEY');
}

function wechatTerminalStatus(value: unknown): PaymentQueryStatus {
  const status = typeof value === 'string' ? value.toUpperCase() : '';
  if (status === 'SUCCESS') return 'paid';
  if (['CLOSED', 'REVOKED'].includes(status)) return 'closed';
  if (status === 'PAYERROR') return 'failed';
  return 'pending';
}

function wechatRefundStatus(value: unknown): RefundQueryStatus {
  const status = typeof value === 'string' ? value.toUpperCase() : '';
  if (status === 'SUCCESS') return 'succeeded';
  if (['CLOSED', 'ABNORMAL'].includes(status)) return 'failed';
  return 'processing';
}

function alipayTradeStatus(value: unknown): PaymentQueryStatus {
  const status = typeof value === 'string' ? value.toUpperCase() : '';
  if (['TRADE_SUCCESS', 'TRADE_FINISHED'].includes(status)) return 'paid';
  if (status === 'TRADE_CLOSED') return 'closed';
  return 'pending';
}

function alipayRefundStatus(value: Record<string, unknown>): RefundQueryStatus {
  const refundStatus = typeof value.refund_status === 'string' ? value.refund_status.toUpperCase() : '';
  const fundChange = typeof value.fund_change === 'string' ? value.fund_change.toUpperCase() : '';
  if (refundStatus === 'REFUND_SUCCESS' || fundChange === 'Y') return 'succeeded';
  if (['REFUND_CLOSED', 'REFUND_FAIL'].includes(refundStatus)) return 'failed';
  return 'processing';
}

async function loadSecret(client: pg.PoolClient, tenantId: string, configPublic: Record<string, unknown>, provider: CommerceProvider) {
  const secretRef = parseSecretRef(configPublic.secretRef);
  const secretKey = secretRef?.key || provider;
  const result = await client.query<SecretRow>(
    `
    select secret_value as "secretValue", secret_json as "secretJson"
    from app_private.tenant_secrets
    where tenant_id = $1
      and secret_scope = 'payment'
      and secret_key = $2
    limit 1
    `,
    [tenantId, secretKey],
  );
  return result.rows[0] || null;
}

async function loadPaymentProviderConfig(client: pg.PoolClient, tenantId: string, provider: CommerceProvider): Promise<ProviderConfig> {
  const aliases = providerAliases(provider);
  const accountResult = await client.query<{
    provider: string;
    configPublic: Record<string, unknown> | null;
  }>(
    `
    select provider, config_public as "configPublic"
    from public.tenant_payment_accounts
    where tenant_id = $1
      and provider = any($2::text[])
      and status = 'active'
    order by array_position($2::text[], provider)
    limit 1
    `,
    [tenantId, aliases],
  );
  const account = accountResult.rows[0];
  if (!account) throw new CommerceWorkerError(`${provider} provider is not configured`, 'PAYMENT_PROVIDER_NOT_CONFIGURED');
  const normalized = normalizeProvider(account.provider);
  if (!normalized) throw new CommerceWorkerError(`${account.provider} provider is unsupported`, 'PAYMENT_PROVIDER_NOT_SUPPORTED');
  const configPublic = objectValue(account.configPublic);
  const secret = await loadSecret(client, tenantId, configPublic, normalized);
  return { tenantId, provider: normalized, configPublic, secret };
}

async function fetchJson(url: string, init: RequestInit, timeoutMs: number) {
  const controller = new AbortController();
  const timeout = setTimeout(() => controller.abort(), timeoutMs);
  try {
    const response = await fetch(url, { ...init, signal: controller.signal });
    const raw = safeJson(await response.json().catch(() => ({})));
    return { response, raw };
  } finally {
    clearTimeout(timeout);
  }
}

async function queryWechatPayment(providerConfig: ProviderConfig, payment: PaymentCandidate): Promise<PaymentQueryResult> {
  const mchId = requirePublicString(providerConfig, ['merchantId', 'mchId']);
  const merchantSerialNo = requirePublicString(providerConfig, ['merchantSerialNo']);
  const endpoint = providerEndpointForKeys(
    providerConfig,
    ['paymentQueryEndpoint', 'transactionQueryEndpoint'],
    'https://api.mch.weixin.qq.com/v3/pay/transactions/out-trade-no',
    ['api.mch.weixin.qq.com'],
  );
  const url = new URL(`${endpoint.replace(/\/$/, '')}/${encodeURIComponent(payment.orderNo)}`);
  url.searchParams.set('mchid', mchId);
  const timestamp = Math.floor(Date.now() / 1000).toString();
  const nonce = randomNonce();
  const message = ['GET', `${url.pathname}${url.search}`, timestamp, nonce, ''].join('\n') + '\n';
  const signature = rsaSignSha256(wechatPrivateKey(providerConfig), message);

  const { response, raw } = await fetchJson(url.toString(), {
    method: 'GET',
    headers: {
      accept: 'application/json',
      authorization: `WECHATPAY2-SHA256-RSA2048 mchid="${mchId}",nonce_str="${nonce}",signature="${signature}",timestamp="${timestamp}",serial_no="${merchantSerialNo}"`,
    },
  }, config.commerceRequestTimeoutMs);
  if (!response.ok) {
    if (response.status === 404) {
      return {
        provider: 'wechat_pay',
        status: 'pending',
        eventType: 'TRANSACTION.NOT_FOUND',
        orderNo: payment.orderNo,
        raw: { httpStatus: response.status, response: raw },
      };
    }
    throw new CommerceWorkerError('WeChat Pay transaction query failed', 'PAYMENT_PROVIDER_QUERY_FAILED');
  }

  const status = wechatTerminalStatus(raw.trade_state);
  const amount = objectValue(raw.amount);
  return {
    provider: 'wechat_pay',
    status,
    eventType: typeof raw.trade_state === 'string' ? `TRANSACTION.${raw.trade_state}` : 'TRANSACTION.QUERY',
    orderNo: payment.orderNo,
    providerTradeNo: typeof raw.transaction_id === 'string' ? raw.transaction_id : null,
    amountCents: optionalIntegerAmount(amount.total),
    paidAt: typeof raw.success_time === 'string' ? raw.success_time : null,
    failureReason: status === 'failed' || status === 'closed' ? String(raw.trade_state || status) : null,
    raw,
  };
}

async function queryWechatRefund(providerConfig: ProviderConfig, refund: RefundCandidate): Promise<RefundQueryResult> {
  const mchId = requirePublicString(providerConfig, ['merchantId', 'mchId']);
  const merchantSerialNo = requirePublicString(providerConfig, ['merchantSerialNo']);
  const endpoint = providerEndpointForKeys(
    providerConfig,
    ['refundQueryEndpoint', 'refundEndpoint'],
    'https://api.mch.weixin.qq.com/v3/refund/domestic/refunds',
    ['api.mch.weixin.qq.com'],
  );
  const url = new URL(`${endpoint.replace(/\/$/, '')}/${encodeURIComponent(refund.refundNo)}`);
  const timestamp = Math.floor(Date.now() / 1000).toString();
  const nonce = randomNonce();
  const message = ['GET', `${url.pathname}${url.search}`, timestamp, nonce, ''].join('\n') + '\n';
  const signature = rsaSignSha256(wechatPrivateKey(providerConfig), message);

  const { response, raw } = await fetchJson(url.toString(), {
    method: 'GET',
    headers: {
      accept: 'application/json',
      authorization: `WECHATPAY2-SHA256-RSA2048 mchid="${mchId}",nonce_str="${nonce}",signature="${signature}",timestamp="${timestamp}",serial_no="${merchantSerialNo}"`,
    },
  }, config.commerceRequestTimeoutMs);
  if (!response.ok) {
    throw new CommerceWorkerError('WeChat Pay refund query failed', 'PAYMENT_PROVIDER_REFUND_QUERY_FAILED');
  }

  const amount = objectValue(raw.amount);
  const amountCents = optionalIntegerAmount(amount.refund);
  if (amountCents !== null && amountCents !== refund.amountCents) {
    throw new CommerceWorkerError('WeChat Pay refund amount mismatch', 'PAYMENT_PROVIDER_REFUND_AMOUNT_MISMATCH');
  }
  const status = wechatRefundStatus(raw.status);
  return {
    provider: 'wechat_pay',
    status,
    eventType: typeof raw.status === 'string' ? `REFUND.${raw.status}` : 'REFUND.QUERY',
    providerRefundNo: typeof raw.refund_id === 'string' ? raw.refund_id : refund.providerRefundNo,
    failureReason: status === 'failed' ? String(raw.status || 'WeChat refund failed') : null,
    raw,
  };
}

async function queryAlipayPayment(providerConfig: ProviderConfig, payment: PaymentCandidate): Promise<PaymentQueryResult> {
  const appId = requirePublicString(providerConfig, ['appId']);
  const gateway = providerEndpointForKeys(
    providerConfig,
    ['paymentQueryEndpoint', 'endpoint'],
    'https://openapi.alipay.com/gateway.do',
    ['openapi.alipay.com'],
  );
  const privateKey = normalizePem(requireSecretString(providerConfig, ['privateKey', 'appPrivateKey']), 'PRIVATE KEY');
  const params: Record<string, string> = {
    app_id: appId,
    method: 'alipay.trade.query',
    charset: 'utf-8',
    sign_type: 'RSA2',
    timestamp: new Date().toISOString().replace('T', ' ').slice(0, 19),
    version: '1.0',
    biz_content: JSON.stringify({ out_trade_no: payment.orderNo }),
  };
  params.sign = rsaSignSha256(privateKey, canonicalForm(params));

  const { response, raw } = await fetchJson(gateway, {
    method: 'POST',
    headers: {
      accept: 'application/json',
      'content-type': 'application/x-www-form-urlencoded;charset=utf-8',
    },
    body: encodedForm(params),
  }, config.commerceRequestTimeoutMs);
  if (!response.ok) throw new CommerceWorkerError('Alipay trade query failed', 'PAYMENT_PROVIDER_QUERY_FAILED');

  const responseBody = objectValue(raw.alipay_trade_query_response);
  const code = typeof responseBody.code === 'string' ? responseBody.code : '';
  if (code !== '10000') {
    const subCode = typeof responseBody.sub_code === 'string' ? responseBody.sub_code : '';
    if (subCode === 'ACQ.TRADE_NOT_EXIST') {
      return {
        provider: 'alipay',
        status: 'pending',
        eventType: 'TRADE.NOT_EXIST',
        orderNo: payment.orderNo,
        raw,
      };
    }
    throw new CommerceWorkerError('Alipay trade query was rejected', 'PAYMENT_PROVIDER_QUERY_REJECTED');
  }

  const status = alipayTradeStatus(responseBody.trade_status);
  return {
    provider: 'alipay',
    status,
    eventType: typeof responseBody.trade_status === 'string' ? responseBody.trade_status : 'TRADE.QUERY',
    orderNo: payment.orderNo,
    providerTradeNo: typeof responseBody.trade_no === 'string' ? responseBody.trade_no : null,
    amountCents: optionalYuanToCents(responseBody.total_amount),
    paidAt: typeof responseBody.send_pay_date === 'string' ? responseBody.send_pay_date : null,
    failureReason: status === 'failed' || status === 'closed' ? String(responseBody.trade_status || status) : null,
    raw,
  };
}

async function queryAlipayRefund(providerConfig: ProviderConfig, refund: RefundCandidate): Promise<RefundQueryResult> {
  const appId = requirePublicString(providerConfig, ['appId']);
  const gateway = providerEndpointForKeys(
    providerConfig,
    ['refundQueryEndpoint', 'refundEndpoint', 'endpoint'],
    'https://openapi.alipay.com/gateway.do',
    ['openapi.alipay.com'],
  );
  const privateKey = normalizePem(requireSecretString(providerConfig, ['privateKey', 'appPrivateKey']), 'PRIVATE KEY');
  const bizContent: Record<string, unknown> = {
    out_trade_no: refund.orderNo,
    out_request_no: refund.refundNo,
  };
  if (refund.providerTradeNo) {
    delete bizContent.out_trade_no;
    bizContent.trade_no = refund.providerTradeNo;
  }
  const params: Record<string, string> = {
    app_id: appId,
    method: 'alipay.trade.fastpay.refund.query',
    charset: 'utf-8',
    sign_type: 'RSA2',
    timestamp: new Date().toISOString().replace('T', ' ').slice(0, 19),
    version: '1.0',
    biz_content: JSON.stringify(bizContent),
  };
  params.sign = rsaSignSha256(privateKey, canonicalForm(params));

  const { response, raw } = await fetchJson(gateway, {
    method: 'POST',
    headers: {
      accept: 'application/json',
      'content-type': 'application/x-www-form-urlencoded;charset=utf-8',
    },
    body: encodedForm(params),
  }, config.commerceRequestTimeoutMs);
  if (!response.ok) throw new CommerceWorkerError('Alipay refund query failed', 'PAYMENT_PROVIDER_REFUND_QUERY_FAILED');

  const responseBody = objectValue(raw.alipay_trade_fastpay_refund_query_response);
  const code = typeof responseBody.code === 'string' ? responseBody.code : '';
  if (code !== '10000') throw new CommerceWorkerError('Alipay refund query was rejected', 'PAYMENT_PROVIDER_REFUND_QUERY_REJECTED');
  const amountCents = optionalYuanToCents(responseBody.refund_amount);
  if (amountCents !== null && amountCents !== refund.amountCents) {
    throw new CommerceWorkerError('Alipay refund amount mismatch', 'PAYMENT_PROVIDER_REFUND_AMOUNT_MISMATCH');
  }
  const status = alipayRefundStatus(responseBody);
  return {
    provider: 'alipay',
    status,
    eventType: 'REFUND.QUERY',
    providerRefundNo:
      (typeof responseBody.trade_no === 'string' && responseBody.trade_no)
      || (typeof responseBody.out_request_no === 'string' && responseBody.out_request_no)
      || refund.providerRefundNo,
    failureReason: status === 'failed'
      ? (typeof responseBody.sub_msg === 'string' && responseBody.sub_msg) || 'Alipay refund failed'
      : null,
    raw,
  };
}

async function queryPayment(providerConfig: ProviderConfig, payment: PaymentCandidate) {
  return providerConfig.provider === 'wechat_pay'
    ? queryWechatPayment(providerConfig, payment)
    : queryAlipayPayment(providerConfig, payment);
}

async function queryRefund(providerConfig: ProviderConfig, refund: RefundCandidate) {
  return providerConfig.provider === 'wechat_pay'
    ? queryWechatRefund(providerConfig, refund)
    : queryAlipayRefund(providerConfig, refund);
}

function eventError(error: unknown) {
  return error instanceof Error ? truncate(error.message) : truncate(error);
}

async function claimPaymentCandidates(client: pg.PoolClient, limit: number, claimId: string) {
  const result = await client.query<PaymentCandidate>(
    `
    with due as (
      select p.id
      from public.payments p
      join public.orders o on o.tenant_id = p.tenant_id and o.id = p.order_id
      where p.status = 'pending'
        and o.status = 'pending'
        and p.provider <> 'manual'
        and p.created_at <= now() - ($2::int * interval '1 second')
        and coalesce((p.raw_payload->'commerceWorker'->>'claimExpiresAt')::timestamptz, '-infinity'::timestamptz) <= now()
      order by p.created_at asc
      limit $1
      for update skip locked
    )
    update public.payments p
    set raw_payload = coalesce(p.raw_payload, '{}'::jsonb) || jsonb_build_object(
          'commerceWorker',
          jsonb_build_object(
            'claimId', $3::text,
            'claimedAt', now(),
            'claimExpiresAt', now() + interval '5 minutes'
          )
        ),
        updated_at = now()
    from due
    join public.orders o on o.id = (select order_id from public.payments where id = due.id)
    where p.id = due.id
    returning p.id,
              p.tenant_id as "tenantId",
              p.order_id as "orderId",
              o.order_no as "orderNo",
              p.provider as "provider",
              p.status as "paymentStatus",
              o.status as "orderStatus",
              p.amount_cents as "amountCents",
              o.user_id as "userId",
              o.days,
              o.region_id as "regionId"
    `,
    [limit, config.commerceMinAgeSeconds, claimId],
  );
  return result.rows
    .map(row => ({ ...row, provider: normalizeProvider(row.provider) }))
    .filter((row): row is PaymentCandidate => row.provider !== null);
}

async function claimRefundCandidates(client: pg.PoolClient, limit: number, claimId: string) {
  const result = await client.query<RefundCandidate>(
    `
    with due as (
      select rr.id
      from public.commerce_refund_requests rr
      join public.payments p on p.tenant_id = rr.tenant_id and p.id = rr.payment_id
      where rr.status = 'processing'
        and p.provider <> 'manual'
        and rr.processed_at <= now() - ($2::int * interval '1 second')
        and coalesce((rr.metadata->'commerceWorker'->>'claimExpiresAt')::timestamptz, '-infinity'::timestamptz) <= now()
      order by rr.processed_at asc nulls first, rr.created_at asc
      limit $1
      for update skip locked
    )
    update public.commerce_refund_requests rr
    set metadata = coalesce(rr.metadata, '{}'::jsonb) || jsonb_build_object(
          'commerceWorker',
          jsonb_build_object(
            'claimId', $3::text,
            'claimedAt', now(),
            'claimExpiresAt', now() + interval '5 minutes'
          )
        ),
        updated_at = now()
    from due
    join public.orders o on o.id = (select order_id from public.commerce_refund_requests where id = due.id)
    join public.payments p on p.id = (select payment_id from public.commerce_refund_requests where id = due.id)
    where rr.id = due.id
    returning rr.id,
              rr.tenant_id as "tenantId",
              rr.refund_no as "refundNo",
              rr.order_id as "orderId",
              rr.payment_id as "paymentId",
              o.order_no as "orderNo",
              coalesce(rr.provider, p.provider) as "provider",
              rr.provider_refund_no as "providerRefundNo",
              rr.status,
              rr.amount_cents as "amountCents",
              rr.reason,
              rr.entitlement_action as "entitlementAction",
              p.amount_cents as "paymentAmountCents",
              p.provider_trade_no as "providerTradeNo"
    `,
    [limit, config.commerceMinAgeSeconds, claimId],
  );
  return result.rows
    .map(row => ({ ...row, provider: normalizeProvider(row.provider) || normalizeProvider(String(row.provider || '')) }))
    .filter((row): row is RefundCandidate => row.provider !== null);
}

async function recordPaymentEvent(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    paymentId?: string | null;
    provider: CommerceProvider;
    eventType: string;
    eventId: string;
    payload: Record<string, unknown>;
    processed?: boolean;
    error?: string | null;
  },
) {
  await client.query(
    `
    insert into public.payment_events (
      tenant_id, payment_id, provider, event_type, event_id, signature_valid, payload, processed_at, error
    )
    values ($1, $2, $3, $4, $5, true, $6::jsonb, case when $7 then now() else null end, $8)
    on conflict (provider, event_id)
    do update set payload = excluded.payload,
                  payment_id = coalesce(public.payment_events.payment_id, excluded.payment_id),
                  processed_at = case
                    when excluded.processed_at is not null then excluded.processed_at
                    else public.payment_events.processed_at
                  end,
                  error = excluded.error
    `,
    [
      input.tenantId,
      input.paymentId || null,
      input.provider,
      input.eventType,
      input.eventId,
      JSON.stringify(input.payload),
      Boolean(input.processed),
      input.error || null,
    ],
  );
}

async function recordAudit(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    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, null, $2, $3, $4, $5::jsonb)
    `,
    [
      input.tenantId,
      input.action,
      input.targetType,
      input.targetId,
      JSON.stringify(input.details || {}),
    ],
  );
}

async function grantOrderEntitlementIfMissing(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    orderId: string;
    orderNo: string;
    userId: string;
    days: number;
    regionId: string | null;
    provider: CommerceProvider;
    providerTradeNo?: string | null;
  },
) {
  const existing = await client.query<{ id: string }>(
    `
    select id
    from public.entitlements
    where tenant_id = $1
      and source_type = 'order'
      and source_id = $2
    limit 1
    `,
    [input.tenantId, input.orderId],
  );
  if (existing.rows[0]) return existing.rows[0];

  const days = Math.trunc(input.days || 0);
  const expiresAtSql =
    days < 0 ? `'2099-12-31T23:59:59Z'::timestamptz` : `$6::timestamptz + ($7::text || ' days')::interval`;
  const scopeType = input.regionId ? 'region' : 'tenant';
  const startsAtResult = await client.query<{ startsAt: string }>(
    `
    select greatest(now(), coalesce(max(expires_at), now())) as "startsAt"
    from public.entitlements
    where tenant_id = $1
      and user_id = $2
      and entitlement_type = 'svip'
      and scope_type = $3
      and (($4::uuid is null and scope_id is null) or scope_id = $4::uuid)
      and status = 'active'
    `,
    [input.tenantId, input.userId, scopeType, input.regionId],
  );
  const inserted = await client.query<{ id: string }>(
    `
    insert into public.entitlements (
      tenant_id, user_id, entitlement_type, scope_type, scope_id,
      source_type, source_id, starts_at, expires_at, status, metadata
    )
    values (
      $1, $2, 'svip', $3, $4,
      'order', $5, $6::timestamptz,
      ${expiresAtSql},
      'active', $8::jsonb
    )
    returning id
    `,
    [
      input.tenantId,
      input.userId,
      scopeType,
      input.regionId,
      input.orderId,
      startsAtResult.rows[0]?.startsAt || nowIso(),
      days,
      JSON.stringify({
        orderNo: input.orderNo,
        paymentProvider: input.provider,
        providerTradeNo: input.providerTradeNo || null,
        source: 'commerce_worker_reconciliation',
      }),
    ],
  );
  return inserted.rows[0];
}

async function settlePaidPayment(client: pg.PoolClient, payment: PaymentCandidate, providerResult: PaymentQueryResult) {
  const orderResult = await client.query<{
    id: string;
    orderNo: string;
    status: string;
    amountCents: number;
    userId: string | null;
    days: number | null;
    regionId: string | null;
  }>(
    `
    select id, order_no as "orderNo", status, amount_cents as "amountCents",
           user_id as "userId", days, region_id as "regionId"
    from public.orders
    where tenant_id = $1 and id = $2
    limit 1
    for update
    `,
    [payment.tenantId, payment.orderId],
  );
  const order = orderResult.rows[0];
  if (!order) throw new CommerceWorkerError('Order not found', 'ORDER_NOT_FOUND');
  if (!order.userId) throw new CommerceWorkerError('Order has no user', 'ORDER_USER_MISSING');
  if (providerResult.amountCents !== null && providerResult.amountCents !== undefined && providerResult.amountCents !== order.amountCents) {
    throw new CommerceWorkerError('Payment amount mismatch', 'PAYMENT_AMOUNT_MISMATCH');
  }

  const paymentResult = await client.query<{
    id: string;
    status: string;
    amountCents: number;
  }>(
    `
    select id, status, amount_cents as "amountCents"
    from public.payments
    where tenant_id = $1 and id = $2
    limit 1
    for update
    `,
    [payment.tenantId, payment.id],
  );
  const currentPayment = paymentResult.rows[0];
  if (!currentPayment) throw new CommerceWorkerError('Payment not found', 'PAYMENT_NOT_FOUND');
  if (providerResult.amountCents !== null && providerResult.amountCents !== undefined && providerResult.amountCents !== currentPayment.amountCents) {
    throw new CommerceWorkerError('Payment amount mismatch', 'PAYMENT_AMOUNT_MISMATCH');
  }

  if (order.status === 'paid') {
    await client.query(
      `
      update public.payments
      set status = case when status = 'pending' then 'paid' else status end,
          provider_trade_no = coalesce($3, provider_trade_no),
          paid_at = coalesce(paid_at, $4::timestamptz),
          raw_payload = coalesce(raw_payload, '{}'::jsonb) || $5::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        payment.tenantId,
        payment.id,
        providerResult.providerTradeNo || null,
        providerResult.paidAt || nowIso(),
        JSON.stringify({ lastProviderQuery: providerResult.raw, commerceWorker: { lastSettledAt: nowIso() } }),
      ],
    );
    return { idempotent: true, entitlementId: null };
  }

  if (order.status !== 'pending') {
    throw new CommerceWorkerError(`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(),
        raw_payload = coalesce(raw_payload, '{}'::jsonb) || $6::jsonb
    where tenant_id = $1 and id = $2
    `,
    [
      payment.tenantId,
      order.id,
      providerResult.providerTradeNo || null,
      providerResult.paidAt || nowIso(),
      providerResult.provider,
      JSON.stringify({ lastProviderQuery: providerResult.raw, commerceWorker: { reconciledAt: nowIso() } }),
    ],
  );

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

  const entitlement = await grantOrderEntitlementIfMissing(client, {
    tenantId: payment.tenantId,
    orderId: order.id,
    orderNo: order.orderNo,
    userId: order.userId,
    days: order.days || 0,
    regionId: order.regionId,
    provider: providerResult.provider,
    providerTradeNo: providerResult.providerTradeNo || null,
  });

  await recordAudit(client, {
    tenantId: payment.tenantId,
    action: 'commerce.payment.reconciled_paid',
    targetType: 'order',
    targetId: order.id,
    details: {
      paymentId: payment.id,
      orderNo: order.orderNo,
      provider: providerResult.provider,
      providerTradeNo: providerResult.providerTradeNo || null,
      entitlementId: entitlement.id,
    },
  });

  return { idempotent: false, entitlementId: entitlement.id };
}

async function settleTerminalPayment(client: pg.PoolClient, payment: PaymentCandidate, providerResult: PaymentQueryResult) {
  const nextOrderStatus = providerResult.status === 'closed' ? 'closed' : 'failed';
  const nextPaymentStatus = providerResult.status === 'closed' ? 'cancelled' : 'failed';
  await client.query(
    `
    update public.orders
    set status = $3,
        updated_at = now(),
        raw_payload = coalesce(raw_payload, '{}'::jsonb) || $4::jsonb
    where tenant_id = $1
      and id = $2
      and status = 'pending'
    `,
    [
      payment.tenantId,
      payment.orderId,
      nextOrderStatus,
      JSON.stringify({
        lastProviderQuery: providerResult.raw,
        commerceWorker: {
          reconciledAt: nowIso(),
          terminalStatus: providerResult.status,
          failureReason: providerResult.failureReason || null,
        },
      }),
    ],
  );
  await client.query(
    `
    update public.payments
    set status = $3,
        raw_payload = coalesce(raw_payload, '{}'::jsonb) || $4::jsonb,
        updated_at = now()
    where tenant_id = $1
      and id = $2
      and status = 'pending'
    `,
    [
      payment.tenantId,
      payment.id,
      nextPaymentStatus,
      JSON.stringify({
        lastProviderQuery: providerResult.raw,
        commerceWorker: {
          reconciledAt: nowIso(),
          terminalStatus: providerResult.status,
          failureReason: providerResult.failureReason || null,
        },
      }),
    ],
  );
  await recordAudit(client, {
    tenantId: payment.tenantId,
    action: `commerce.payment.reconciled_${nextOrderStatus}`,
    targetType: 'order',
    targetId: payment.orderId,
    details: {
      paymentId: payment.id,
      orderNo: payment.orderNo,
      provider: providerResult.provider,
      reason: providerResult.failureReason || null,
    },
  });
}

async function markPaymentPending(client: pg.PoolClient, payment: PaymentCandidate, providerResult: PaymentQueryResult) {
  await client.query(
    `
    update public.payments
    set raw_payload = coalesce(raw_payload, '{}'::jsonb) || $3::jsonb,
        updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [
      payment.tenantId,
      payment.id,
      JSON.stringify({
        lastProviderQuery: providerResult.raw,
        commerceWorker: { lastCheckedAt: nowIso(), lastStatus: providerResult.status },
      }),
    ],
  );
}

async function processPayment(payment: PaymentCandidate) {
  const client = await pool.connect();
  const eventId = `${payment.tenantId}:payment-query:${payment.orderNo}`;
  try {
    const providerConfig = await loadPaymentProviderConfig(client, payment.tenantId, payment.provider);
    const providerResult = await queryPayment(providerConfig, payment);
    await client.query('begin');
    if (providerResult.status === 'paid') {
      const settlement = await settlePaidPayment(client, payment, providerResult);
      await recordPaymentEvent(client, {
        tenantId: payment.tenantId,
        paymentId: payment.id,
        provider: payment.provider,
        eventType: providerResult.eventType,
        eventId,
        payload: {
          source: 'commerce_worker',
          providerResult: providerResult.raw,
          idempotent: settlement.idempotent,
          entitlementId: settlement.entitlementId,
        },
        processed: true,
      });
      await client.query('commit');
      return 'paid';
    }
    if (providerResult.status === 'closed' || providerResult.status === 'failed') {
      await settleTerminalPayment(client, payment, providerResult);
      await recordPaymentEvent(client, {
        tenantId: payment.tenantId,
        paymentId: payment.id,
        provider: payment.provider,
        eventType: providerResult.eventType,
        eventId,
        payload: { source: 'commerce_worker', providerResult: providerResult.raw },
        processed: true,
      });
      await client.query('commit');
      return providerResult.status;
    }
    await markPaymentPending(client, payment, providerResult);
    await recordPaymentEvent(client, {
      tenantId: payment.tenantId,
      paymentId: payment.id,
      provider: payment.provider,
      eventType: providerResult.eventType,
      eventId,
      payload: { source: 'commerce_worker', providerResult: providerResult.raw },
      processed: false,
    });
    await client.query('commit');
    return 'pending';
  } catch (error) {
    await client.query('rollback').catch(() => {});
    await client.query(
      `
      update public.payments
      set raw_payload = coalesce(raw_payload, '{}'::jsonb) || $3::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        payment.tenantId,
        payment.id,
        JSON.stringify({ commerceWorker: { lastCheckedAt: nowIso(), lastError: eventError(error) } }),
      ],
    ).catch(() => {});
    await recordPaymentEvent(client, {
      tenantId: payment.tenantId,
      paymentId: payment.id,
      provider: payment.provider,
      eventType: 'PAYMENT.QUERY.ERROR',
      eventId,
      payload: { source: 'commerce_worker' },
      processed: false,
      error: eventError(error),
    }).catch(() => {});
    return 'error';
  } finally {
    client.release();
  }
}

async function applySuccessfulRefund(
  client: pg.PoolClient,
  refund: RefundCandidate,
  providerResult: RefundQueryResult,
) {
  const orderResult = await client.query<{
    id: string;
    status: string;
    amountCents: number;
    refundedAmountCents: number;
  }>(
    `
    select id, status, amount_cents as "amountCents", refunded_amount_cents as "refundedAmountCents"
    from public.orders
    where tenant_id = $1 and id = $2
    limit 1
    for update
    `,
    [refund.tenantId, refund.orderId],
  );
  const order = orderResult.rows[0];
  if (!order) throw new CommerceWorkerError('Order not found', 'ORDER_NOT_FOUND');
  if (!['paid', 'partially_refunded'].includes(order.status)) {
    throw new CommerceWorkerError(`Order status is ${order.status}`, 'ORDER_NOT_REFUNDABLE');
  }

  const nextRefundedAmount = order.refundedAmountCents + refund.amountCents;
  if (nextRefundedAmount > order.amountCents) {
    throw new CommerceWorkerError('Refund amount exceeds paid amount', 'REFUND_AMOUNT_EXCEEDS_PAID');
  }
  const finalOrderStatus = 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
    `,
    [
      refund.tenantId,
      order.id,
      nextRefundedAmount,
      finalOrderStatus,
      JSON.stringify({
        lastRefund: {
          refundNo: refund.refundNo,
          amountCents: refund.amountCents,
          providerRefundNo: providerResult.providerRefundNo || refund.providerRefundNo || null,
          source: 'commerce_worker_reconciliation',
        },
      }),
    ],
  );

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

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

  return { orderStatus: finalOrderStatus, refundedAmountCents: nextRefundedAmount, revokedEntitlements };
}

async function recordRefundEvent(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    refundRequestId: string;
    fromStatus: string | null;
    toStatus: string;
    eventType: string;
    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, null, $6::jsonb)
    `,
    [
      input.tenantId,
      input.refundRequestId,
      input.fromStatus,
      input.toStatus,
      input.eventType,
      JSON.stringify(input.details || {}),
    ],
  );
}

async function settleRefund(client: pg.PoolClient, refund: RefundCandidate, providerResult: RefundQueryResult) {
  const currentResult = await client.query<{
    id: string;
    status: string;
  }>(
    `
    select id, status
    from public.commerce_refund_requests
    where tenant_id = $1 and id = $2
    limit 1
    for update
    `,
    [refund.tenantId, refund.id],
  );
  const current = currentResult.rows[0];
  if (!current) throw new CommerceWorkerError('Refund request not found', 'REFUND_NOT_FOUND');
  if (current.status === 'succeeded') return { idempotent: true, nextStatus: 'succeeded', processResult: {} };
  if (current.status !== 'processing') {
    throw new CommerceWorkerError(`Refund status is ${current.status}`, 'REFUND_STATUS_INVALID');
  }

  if (providerResult.status === 'succeeded') {
    const processResult = await applySuccessfulRefund(client, refund, providerResult);
    await client.query(
      `
      update public.commerce_refund_requests
      set status = 'succeeded',
          provider_refund_no = coalesce($3, provider_refund_no),
          succeeded_at = coalesce(succeeded_at, now()),
          processed_at = coalesce(processed_at, now()),
          metadata = coalesce(metadata, '{}'::jsonb) || $4::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        refund.tenantId,
        refund.id,
        providerResult.providerRefundNo || null,
        JSON.stringify({
          lastAction: 'commerce_worker_refund_query',
          providerResult: providerResult.raw,
          processResult,
        }),
      ],
    );
    await recordRefundEvent(client, {
      tenantId: refund.tenantId,
      refundRequestId: refund.id,
      fromStatus: current.status,
      toStatus: 'succeeded',
      eventType: 'provider_query_succeeded',
      details: {
        source: 'commerce_worker',
        provider: providerResult.provider,
        providerRefundNo: providerResult.providerRefundNo || null,
        processResult,
      },
    });
    return { idempotent: false, nextStatus: 'succeeded', processResult };
  }

  if (providerResult.status === 'failed') {
    await client.query(
      `
      update public.commerce_refund_requests
      set status = 'failed',
          provider_refund_no = coalesce($3, provider_refund_no),
          failed_at = coalesce(failed_at, now()),
          failure_reason = $4,
          metadata = coalesce(metadata, '{}'::jsonb) || $5::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        refund.tenantId,
        refund.id,
        providerResult.providerRefundNo || null,
        providerResult.failureReason || 'Provider refund failed',
        JSON.stringify({
          lastAction: 'commerce_worker_refund_query',
          providerResult: providerResult.raw,
        }),
      ],
    );
    await recordRefundEvent(client, {
      tenantId: refund.tenantId,
      refundRequestId: refund.id,
      fromStatus: current.status,
      toStatus: 'failed',
      eventType: 'provider_query_failed',
      details: {
        source: 'commerce_worker',
        provider: providerResult.provider,
        providerRefundNo: providerResult.providerRefundNo || null,
        failureReason: providerResult.failureReason || null,
      },
    });
    return { idempotent: false, nextStatus: 'failed', processResult: {} };
  }

  await client.query(
    `
    update public.commerce_refund_requests
    set metadata = coalesce(metadata, '{}'::jsonb) || $3::jsonb,
        updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [
      refund.tenantId,
      refund.id,
      JSON.stringify({
        lastAction: 'commerce_worker_refund_query',
        providerResult: providerResult.raw,
        commerceWorker: { lastCheckedAt: nowIso(), lastStatus: providerResult.status },
      }),
    ],
  );
  await recordRefundEvent(client, {
    tenantId: refund.tenantId,
    refundRequestId: refund.id,
    fromStatus: current.status,
    toStatus: 'processing',
    eventType: 'provider_query_processing',
    details: {
      source: 'commerce_worker',
      provider: providerResult.provider,
      providerRefundNo: providerResult.providerRefundNo || null,
    },
  });
  return { idempotent: false, nextStatus: 'processing', processResult: {} };
}

async function processRefund(refund: RefundCandidate) {
  const client = await pool.connect();
  const eventId = `${refund.tenantId}:refund-query:${refund.refundNo}`;
  try {
    const providerConfig = await loadPaymentProviderConfig(client, refund.tenantId, refund.provider);
    const providerResult = await queryRefund(providerConfig, refund);
    await client.query('begin');
    const settlement = await settleRefund(client, refund, providerResult);
    await recordPaymentEvent(client, {
      tenantId: refund.tenantId,
      paymentId: refund.paymentId,
      provider: refund.provider,
      eventType: providerResult.eventType,
      eventId,
      payload: {
        source: 'commerce_worker',
        refundNo: refund.refundNo,
        providerResult: providerResult.raw,
        settlement,
      },
      processed: providerResult.status !== 'processing',
    });
    await client.query('commit');
    return settlement.nextStatus;
  } catch (error) {
    await client.query('rollback').catch(() => {});
    await client.query(
      `
      update public.commerce_refund_requests
      set metadata = coalesce(metadata, '{}'::jsonb) || $3::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        refund.tenantId,
        refund.id,
        JSON.stringify({ commerceWorker: { lastCheckedAt: nowIso(), lastError: eventError(error) } }),
      ],
    ).catch(() => {});
    await recordPaymentEvent(client, {
      tenantId: refund.tenantId,
      paymentId: refund.paymentId,
      provider: refund.provider,
      eventType: 'REFUND.QUERY.ERROR',
      eventId,
      payload: { source: 'commerce_worker', refundNo: refund.refundNo },
      processed: false,
      error: eventError(error),
    }).catch(() => {});
    return 'error';
  } finally {
    client.release();
  }
}

export async function processCommerceBatch(limit = config.commerceBatchSize): Promise<ProcessResult> {
  const claimId = crypto.randomUUID();
  const client = await pool.connect();
  let payments: PaymentCandidate[] = [];
  let refunds: RefundCandidate[] = [];
  try {
    await client.query('begin');
    payments = await claimPaymentCandidates(client, limit, claimId);
    refunds = await claimRefundCandidates(client, limit, claimId);
    await client.query('commit');
  } catch (error) {
    await client.query('rollback');
    throw error;
  } finally {
    client.release();
  }

  const result: ProcessResult = {
    processed: payments.length + refunds.length,
    payments: { processed: payments.length, paid: 0, pending: 0, closed: 0, failed: 0, errors: 0 },
    refunds: { processed: refunds.length, succeeded: 0, processing: 0, failed: 0, errors: 0 },
  };

  for (const payment of payments) {
    const status = await processPayment(payment);
    if (status === 'paid') result.payments.paid += 1;
    else if (status === 'pending') result.payments.pending += 1;
    else if (status === 'closed') result.payments.closed += 1;
    else if (status === 'failed') result.payments.failed += 1;
    else result.payments.errors += 1;
  }

  for (const refund of refunds) {
    const status = await processRefund(refund);
    if (status === 'succeeded') result.refunds.succeeded += 1;
    else if (status === 'processing') result.refunds.processing += 1;
    else if (status === 'failed') result.refunds.failed += 1;
    else result.refunds.errors += 1;
  }

  return result;
}
