import crypto from 'node:crypto';
import type pg from 'pg';
import { config } from '../../core/config.js';
import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, optionalInteger, optionalString, readJsonBody, requiredString, stringParam } from '../../core/request.js';
import { query, transaction } from '../../core/db.js';
import { requireTenantAdmin, requireTenantPermission, type TenantAdminAuth } from '../tenant-admin/auth.js';

export type ReconciliationProvider = 'wechat_pay' | 'alipay' | 'manual';
export type BillType = 'payment' | 'refund' | 'combined';
export type ReconciliationSource = 'manual_upload' | 'provider_download' | 'api' | 'worker';
type TransactionType = 'payment' | 'refund';
type MatchStatus =
  | 'matched'
  | 'amount_mismatch'
  | 'status_mismatch'
  | 'missing_local'
  | 'missing_provider'
  | 'duplicate'
  | 'ignored';
type Severity = 'info' | 'warning' | 'error' | 'critical';
type ReconciliationIssueStatus = 'open' | 'investigating' | 'resolved' | 'ignored' | 'escalated';
type ReconciliationResolutionType =
  | 'none'
  | 'provider_confirmed'
  | 'local_corrected'
  | 'manual_adjustment'
  | 'false_positive'
  | 'duplicate'
  | 'write_off';
type ReconciliationIssueAction = 'start' | 'assign' | 'resolve' | 'ignore' | 'escalate' | 'reopen';

const BILL_TYPES = new Set<BillType>(['payment', 'refund', 'combined']);
const SOURCES = new Set<ReconciliationSource>(['manual_upload', 'provider_download', 'api', 'worker']);
const ACTIONABLE_MATCH_STATUSES = new Set<MatchStatus>([
  'amount_mismatch',
  'status_mismatch',
  'missing_local',
  'missing_provider',
  'duplicate',
]);
const ISSUE_STATUSES = new Set<ReconciliationIssueStatus>(['open', 'investigating', 'resolved', 'ignored', 'escalated']);
const RESOLUTION_TYPES = new Set<ReconciliationResolutionType>([
  'none',
  'provider_confirmed',
  'local_corrected',
  'manual_adjustment',
  'false_positive',
  'duplicate',
  'write_off',
]);
const ISSUE_ACTIONS = new Set<ReconciliationIssueAction>(['start', 'assign', 'resolve', 'ignore', 'escalate', 'reopen']);
const PROVIDER_SUCCESS_STATUSES = new Set([
  'success',
  'succeeded',
  'paid',
  'trade_success',
  'trade_finished',
  'refund_success',
  'finished',
  'completed',
]);
const LOCAL_PAID_PAYMENT_STATUSES = new Set(['paid', 'partially_refunded', 'refunded']);
const LOCAL_SUCCEEDED_REFUND_STATUSES = new Set(['succeeded']);
const MAX_PROVIDER_DOWNLOAD_ROWS = 50_000;

interface RawBillRow {
  [key: string]: unknown;
}

interface NormalizedBillRow {
  rowNo: number;
  transactionType: TransactionType;
  provider: ReconciliationProvider;
  providerTradeNo: string | null;
  providerRefundNo: string | null;
  orderNo: string | null;
  refundNo: string | null;
  amountCents: number;
  refundAmountCents: number;
  feeCents: number;
  paidAt: string | null;
  refundedAt: string | null;
  providerStatus: string | null;
  raw: RawBillRow;
  invalidIssue?: {
    code: string;
    message: string;
  };
}

interface LocalPaymentMatch {
  orderId: string;
  orderNo: string;
  orderStatus: string;
  orderAmountCents: number;
  orderRefundedAmountCents: number;
  paymentId: string;
  paymentStatus: string;
  paymentAmountCents: number;
  paymentRefundedAmountCents: number;
  paymentProvider: string;
  providerTradeNo: string | null;
  paidAt: string | null;
}

interface LocalRefundMatch {
  orderId: string;
  orderNo: string;
  orderStatus: string;
  refundRequestId: string;
  refundNo: string;
  refundStatus: string;
  amountCents: number;
  provider: string | null;
  providerRefundNo: string | null;
  paymentId: string | null;
  succeededAt: string | null;
}

interface ReconciliationItemDraft {
  rowNo: number;
  provider: ReconciliationProvider;
  transactionType: TransactionType;
  providerTradeNo: string | null;
  providerRefundNo: string | null;
  orderNo: string | null;
  refundNo: string | null;
  amountCents: number;
  refundAmountCents: number;
  feeCents: number;
  paidAt: string | null;
  refundedAt: string | null;
  providerStatus: string | null;
  localStatus: string | null;
  orderId: string | null;
  paymentId: string | null;
  refundRequestId: string | null;
  matchStatus: MatchStatus;
  severity: Severity;
  issueCode: string | null;
  details: Record<string, unknown>;
}

interface ReconciliationBuildResult {
  batch: {
    provider: ReconciliationProvider;
    billDate: string;
    billType: BillType;
    source: ReconciliationSource;
    sourceName: string | null;
    sourceHash: string;
    status: 'preview' | 'completed' | 'completed_with_issues';
    totalCount: number;
    matchedCount: number;
    mismatchCount: number;
    missingLocalCount: number;
    missingProviderCount: number;
    duplicateCount: number;
    ignoredCount: number;
    amountCents: number;
    refundAmountCents: number;
    feeCents: number;
  };
  items: ReconciliationItemDraft[];
}

export interface ReconciliationBatchImportInput {
  tenantId: string;
  actorUserId: string | null;
  provider: ReconciliationProvider;
  billDate: string;
  billType: BillType;
  source: ReconciliationSource;
  sourceName: string | null;
  rows: unknown[];
  metadata?: Record<string, unknown>;
}

export function normalizeReconciliationProvider(value: string): ReconciliationProvider {
  const normalized = value.trim().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';
  if (normalized === 'manual') return 'manual';
  throw new HttpError(400, 'Payment provider is not supported for reconciliation', 'RECONCILIATION_PROVIDER_UNSUPPORTED');
}

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

export function normalizeReconciliationBillType(value: unknown): BillType {
  if (typeof value !== 'string' || !value.trim()) return 'combined';
  const normalized = value.trim().toLowerCase();
  if (BILL_TYPES.has(normalized as BillType)) return normalized as BillType;
  throw new HttpError(400, 'billType is invalid', 'RECONCILIATION_BILL_TYPE_INVALID');
}

function normalizeSource(value: unknown): ReconciliationSource {
  if (typeof value !== 'string' || !value.trim()) return 'manual_upload';
  const normalized = value.trim().toLowerCase();
  if (SOURCES.has(normalized as ReconciliationSource)) return normalized as ReconciliationSource;
  throw new HttpError(400, 'source is invalid', 'RECONCILIATION_SOURCE_INVALID');
}

export function normalizeReconciliationBillDate(value: string) {
  const trimmed = value.trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(trimmed)) {
    throw new HttpError(400, 'billDate must be YYYY-MM-DD', 'RECONCILIATION_BILL_DATE_INVALID');
  }
  const date = new Date(`${trimmed}T00:00:00.000Z`);
  if (Number.isNaN(date.getTime()) || date.toISOString().slice(0, 10) !== trimmed) {
    throw new HttpError(400, 'billDate is invalid', 'RECONCILIATION_BILL_DATE_INVALID');
  }
  return trimmed;
}

function objectValue(value: unknown): RawBillRow {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as RawBillRow : {};
}

function nullableString(value: unknown) {
  return typeof value === 'string' && value.trim() ? value.trim() : null;
}

function jsonBodyValue(value: unknown) {
  return JSON.stringify(objectValue(value));
}

function optionalChoice<T extends string>(value: unknown, allowed: Set<T>, fallback: T, code = 'INVALID_FIELD_VALUE') {
  const candidate = (nullableString(value) || fallback) as T;
  if (!allowed.has(candidate)) {
    throw new HttpError(400, `Invalid value: ${candidate}`, code);
  }
  return candidate;
}

function timestampValue(value: unknown, fieldName: string) {
  const raw = nullableString(value);
  if (!raw) return null;
  const date = new Date(raw);
  if (Number.isNaN(date.getTime())) {
    throw new HttpError(400, `${fieldName} must be a valid timestamp`, 'INVALID_TIMESTAMP');
  }
  return date.toISOString();
}

function optionalRowString(row: RawBillRow, keys: string[]) {
  for (const key of keys) {
    const value = row[key];
    if (typeof value === 'string' && value.trim()) return value.trim();
    if (typeof value === 'number' && Number.isFinite(value)) return String(value);
  }
  return null;
}

function dateString(row: RawBillRow, keys: string[]) {
  const value = optionalRowString(row, keys);
  if (!value) return null;
  const date = new Date(value);
  if (Number.isNaN(date.getTime())) return null;
  return date.toISOString();
}

function centsValue(row: RawBillRow, centKeys: string[], yuanKeys: string[], fallback = 0) {
  for (const key of centKeys) {
    const value = row[key];
    const parsed = typeof value === 'number' ? value : typeof value === 'string' && value.trim() ? Number(value) : Number.NaN;
    if (Number.isFinite(parsed)) return Math.max(0, Math.round(parsed));
  }
  for (const key of yuanKeys) {
    const value = row[key];
    const parsed = typeof value === 'number' ? value : typeof value === 'string' && value.trim() ? Number(value) : Number.NaN;
    if (Number.isFinite(parsed)) return Math.max(0, Math.round(parsed * 100));
  }
  return fallback;
}

function feeValue(row: RawBillRow) {
  const cents = centsValue(row, ['feeCents', 'fee_cents', 'serviceFeeCents', 'service_fee_cents'], [], Number.NaN);
  if (Number.isFinite(cents)) return cents;
  for (const key of ['feeYuan', 'fee_yuan', 'serviceFee', 'service_fee']) {
    const value = row[key];
    const parsed = typeof value === 'number' ? value : typeof value === 'string' && value.trim() ? Number(value) : Number.NaN;
    if (Number.isFinite(parsed)) return Math.round(parsed * 100);
  }
  return 0;
}

function normalizeTransactionType(value: unknown, billType: BillType): TransactionType | null {
  if (typeof value !== 'string' || !value.trim()) {
    return billType === 'refund' ? 'refund' : 'payment';
  }
  const normalized = value.trim().toLowerCase().replace(/[-\s]/g, '_');
  if (['payment', 'pay', 'trade', 'transaction', 'income'].includes(normalized)) return 'payment';
  if (['refund', 'refundment', 'return'].includes(normalized)) return 'refund';
  return null;
}

function normalizeProviderStatus(value: string | null) {
  return value ? value.trim().toLowerCase().replace(/[-\s]/g, '_') : null;
}

function providerStatusSucceeded(status: string | null) {
  if (!status) return true;
  return PROVIDER_SUCCESS_STATUSES.has(status);
}

function emptyItemFromInvalidRow(rowNo: number, provider: ReconciliationProvider, billType: BillType, raw: RawBillRow, code: string, message: string): NormalizedBillRow {
  return {
    rowNo,
    transactionType: billType === 'refund' ? 'refund' : 'payment',
    provider,
    providerTradeNo: null,
    providerRefundNo: null,
    orderNo: null,
    refundNo: null,
    amountCents: 0,
    refundAmountCents: 0,
    feeCents: 0,
    paidAt: null,
    refundedAt: null,
    providerStatus: null,
    raw,
    invalidIssue: { code, message },
  };
}

function normalizeRows(provider: ReconciliationProvider, billType: BillType, rows: unknown[]): NormalizedBillRow[] {
  return rows.map((value, index) => {
    const rowNo = index + 1;
    const row = objectValue(value);
    if (Object.keys(row).length === 0) {
      return emptyItemFromInvalidRow(rowNo, provider, billType, row, 'INVALID_ROW', 'row must be an object');
    }

    const transactionType = normalizeTransactionType(
      row.transactionType ?? row.transaction_type ?? row.type ?? row.billType ?? row.bill_type,
      billType,
    );
    if (!transactionType) {
      return emptyItemFromInvalidRow(rowNo, provider, billType, row, 'TRANSACTION_TYPE_INVALID', 'transaction type is invalid');
    }
    if (billType !== 'combined' && billType !== transactionType) {
      return emptyItemFromInvalidRow(rowNo, provider, billType, row, 'TRANSACTION_TYPE_MISMATCH', 'row type does not match billType');
    }

    const providerTradeNo = optionalRowString(row, ['providerTradeNo', 'provider_trade_no', 'transactionId', 'transaction_id', 'tradeNo', 'trade_no']);
    const providerRefundNo = optionalRowString(row, ['providerRefundNo', 'provider_refund_no', 'refundId', 'refund_id']);
    const orderNo = optionalRowString(row, ['orderNo', 'order_no', 'outTradeNo', 'out_trade_no', 'merchantOrderNo', 'merchant_order_no']);
    const refundNo = optionalRowString(row, ['refundNo', 'refund_no', 'outRefundNo', 'out_refund_no', 'outRequestNo', 'out_request_no']);
    const providerStatus = normalizeProviderStatus(optionalRowString(row, ['providerStatus', 'provider_status', 'status', 'tradeStatus', 'trade_status', 'refundStatus', 'refund_status']));

    if (transactionType === 'payment' && !providerTradeNo && !orderNo) {
      return emptyItemFromInvalidRow(rowNo, provider, billType, row, 'PAYMENT_IDENTITY_MISSING', 'payment row requires providerTradeNo or orderNo');
    }
    if (transactionType === 'refund' && !providerRefundNo && !refundNo && !orderNo) {
      return emptyItemFromInvalidRow(rowNo, provider, billType, row, 'REFUND_IDENTITY_MISSING', 'refund row requires providerRefundNo, refundNo, or orderNo');
    }

    const paymentAmountCents = centsValue(
      row,
      ['amountCents', 'amount_cents', 'totalAmountCents', 'total_amount_cents', 'paidAmountCents', 'paid_amount_cents'],
      ['amountYuan', 'amount_yuan', 'totalAmount', 'total_amount', 'paidAmount', 'paid_amount'],
    );
    const refundAmountCents = centsValue(
      row,
      ['refundAmountCents', 'refund_amount_cents'],
      ['refundAmount', 'refund_amount', 'refundFee', 'refund_fee'],
      transactionType === 'refund' ? paymentAmountCents : 0,
    );

    return {
      rowNo,
      transactionType,
      provider,
      providerTradeNo,
      providerRefundNo,
      orderNo,
      refundNo,
      amountCents: transactionType === 'payment' ? paymentAmountCents : 0,
      refundAmountCents: transactionType === 'refund' ? refundAmountCents : 0,
      feeCents: feeValue(row),
      paidAt: dateString(row, ['paidAt', 'paid_at', 'successTime', 'success_time', 'payTime', 'pay_time']),
      refundedAt: dateString(row, ['refundedAt', 'refunded_at', 'refundSuccessTime', 'refund_success_time', 'refundTime', 'refund_time']),
      providerStatus,
      raw: row,
    };
  });
}

function reconciliationHash(input: {
  provider: ReconciliationProvider;
  billDate: string;
  billType: BillType;
  sourceName: string | null;
  rows: NormalizedBillRow[];
}) {
  return crypto
    .createHash('sha256')
    .update(JSON.stringify({
      provider: input.provider,
      billDate: input.billDate,
      billType: input.billType,
      sourceName: input.sourceName,
      rows: input.rows.map(row => ({
        rowNo: row.rowNo,
        transactionType: row.transactionType,
        providerTradeNo: row.providerTradeNo,
        providerRefundNo: row.providerRefundNo,
        orderNo: row.orderNo,
        refundNo: row.refundNo,
        amountCents: row.amountCents,
        refundAmountCents: row.refundAmountCents,
        feeCents: row.feeCents,
        paidAt: row.paidAt,
        refundedAt: row.refundedAt,
        providerStatus: row.providerStatus,
        invalidIssue: row.invalidIssue || null,
      })),
    }))
    .digest('hex');
}

function rowIdentity(row: NormalizedBillRow) {
  if (row.invalidIssue) return `invalid:${row.rowNo}`;
  if (row.transactionType === 'payment') {
    return `payment:${row.providerTradeNo || ''}:${row.orderNo || ''}`;
  }
  return `refund:${row.providerRefundNo || ''}:${row.refundNo || ''}:${row.orderNo || ''}`;
}

async function findPaymentMatch(client: pg.PoolClient, tenantId: string, provider: ReconciliationProvider, row: NormalizedBillRow) {
  const aliases = providerAliases(provider);
  const result = await client.query<LocalPaymentMatch>(
    `
    select o.id as "orderId", o.order_no as "orderNo", o.status as "orderStatus",
           o.amount_cents as "orderAmountCents", o.refunded_amount_cents as "orderRefundedAmountCents",
           p.id as "paymentId", p.status as "paymentStatus", p.amount_cents as "paymentAmountCents",
           p.refunded_amount_cents as "paymentRefundedAmountCents", p.provider as "paymentProvider",
           p.provider_trade_no as "providerTradeNo", p.paid_at as "paidAt"
    from public.payments p
    join public.orders o on o.tenant_id = p.tenant_id and o.id = p.order_id
    where p.tenant_id = $1
      and p.provider = any($2::text[])
      and (
        ($3::text is not null and p.provider_trade_no = $3)
        or ($4::text is not null and o.order_no = $4)
      )
    order by
      case when $3::text is not null and p.provider_trade_no = $3 then 0 else 1 end,
      p.updated_at desc
    limit 1
    `,
    [tenantId, aliases, row.providerTradeNo, row.orderNo],
  );
  return result.rows[0] || null;
}

async function findRefundMatch(client: pg.PoolClient, tenantId: string, provider: ReconciliationProvider, row: NormalizedBillRow) {
  const aliases = providerAliases(provider);
  const result = await client.query<LocalRefundMatch>(
    `
    select o.id as "orderId", o.order_no as "orderNo", o.status as "orderStatus",
           rr.id as "refundRequestId", rr.refund_no as "refundNo", rr.status as "refundStatus",
           rr.amount_cents as "amountCents", rr.provider, rr.provider_refund_no as "providerRefundNo",
           rr.payment_id as "paymentId", rr.succeeded_at as "succeededAt"
    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.provider is null or rr.provider = any($2::text[]))
      and (
        ($3::text is not null and rr.provider_refund_no = $3)
        or ($4::text is not null and rr.refund_no = $4)
        or ($5::text is not null and o.order_no = $5)
      )
    order by
      case when $3::text is not null and rr.provider_refund_no = $3 then 0
           when $4::text is not null and rr.refund_no = $4 then 1
           else 2 end,
      rr.updated_at desc
    limit 1
    `,
    [tenantId, aliases, row.providerRefundNo, row.refundNo, row.orderNo],
  );
  return result.rows[0] || null;
}

function paymentDraft(row: NormalizedBillRow, local: LocalPaymentMatch | null): ReconciliationItemDraft {
  if (!local) {
    return {
      ...baseDraft(row),
      localStatus: null,
      orderId: null,
      paymentId: null,
      refundRequestId: null,
      matchStatus: 'missing_local',
      severity: 'critical',
      issueCode: 'PAYMENT_MISSING_LOCAL',
      details: { rawRow: row.raw },
    };
  }

  const localStatus = local.paymentStatus;
  const expectedAmountCents = row.amountCents;
  const amountMatches = expectedAmountCents === local.paymentAmountCents;
  const statusMatches = providerStatusSucceeded(row.providerStatus)
    ? LOCAL_PAID_PAYMENT_STATUSES.has(local.paymentStatus)
    : !LOCAL_PAID_PAYMENT_STATUSES.has(local.paymentStatus);
  const matchStatus: MatchStatus = !amountMatches ? 'amount_mismatch' : !statusMatches ? 'status_mismatch' : 'matched';
  return {
    ...baseDraft(row),
    localStatus,
    orderId: local.orderId,
    paymentId: local.paymentId,
    refundRequestId: null,
    matchStatus,
    severity: matchStatus === 'matched' ? 'info' : 'error',
    issueCode: matchStatus === 'matched' ? null : matchStatus === 'amount_mismatch' ? 'PAYMENT_AMOUNT_MISMATCH' : 'PAYMENT_STATUS_MISMATCH',
    details: {
      rawRow: row.raw,
      local: {
        orderNo: local.orderNo,
        orderStatus: local.orderStatus,
        orderAmountCents: local.orderAmountCents,
        paymentStatus: local.paymentStatus,
        paymentAmountCents: local.paymentAmountCents,
        providerTradeNo: local.providerTradeNo,
        paidAt: local.paidAt,
      },
    },
  };
}

function refundDraft(row: NormalizedBillRow, local: LocalRefundMatch | null): ReconciliationItemDraft {
  if (!local) {
    return {
      ...baseDraft(row),
      localStatus: null,
      orderId: null,
      paymentId: null,
      refundRequestId: null,
      matchStatus: 'missing_local',
      severity: 'critical',
      issueCode: 'REFUND_MISSING_LOCAL',
      details: { rawRow: row.raw },
    };
  }

  const amountMatches = row.refundAmountCents === local.amountCents;
  const statusMatches = providerStatusSucceeded(row.providerStatus)
    ? LOCAL_SUCCEEDED_REFUND_STATUSES.has(local.refundStatus)
    : !LOCAL_SUCCEEDED_REFUND_STATUSES.has(local.refundStatus);
  const matchStatus: MatchStatus = !amountMatches ? 'amount_mismatch' : !statusMatches ? 'status_mismatch' : 'matched';
  return {
    ...baseDraft(row),
    localStatus: local.refundStatus,
    orderId: local.orderId,
    paymentId: local.paymentId,
    refundRequestId: local.refundRequestId,
    matchStatus,
    severity: matchStatus === 'matched' ? 'info' : 'error',
    issueCode: matchStatus === 'matched' ? null : matchStatus === 'amount_mismatch' ? 'REFUND_AMOUNT_MISMATCH' : 'REFUND_STATUS_MISMATCH',
    details: {
      rawRow: row.raw,
      local: {
        orderNo: local.orderNo,
        orderStatus: local.orderStatus,
        refundNo: local.refundNo,
        providerRefundNo: local.providerRefundNo,
        refundStatus: local.refundStatus,
        amountCents: local.amountCents,
        succeededAt: local.succeededAt,
      },
    },
  };
}

function baseDraft(row: NormalizedBillRow): Omit<ReconciliationItemDraft, 'localStatus' | 'orderId' | 'paymentId' | 'refundRequestId' | 'matchStatus' | 'severity' | 'issueCode' | 'details'> {
  return {
    rowNo: row.rowNo,
    provider: row.provider,
    transactionType: row.transactionType,
    providerTradeNo: row.providerTradeNo,
    providerRefundNo: row.providerRefundNo,
    orderNo: row.orderNo,
    refundNo: row.refundNo,
    amountCents: row.amountCents,
    refundAmountCents: row.refundAmountCents,
    feeCents: row.feeCents,
    paidAt: row.paidAt,
    refundedAt: row.refundedAt,
    providerStatus: row.providerStatus,
  };
}

function invalidDraft(row: NormalizedBillRow): ReconciliationItemDraft {
  return {
    ...baseDraft(row),
    localStatus: null,
    orderId: null,
    paymentId: null,
    refundRequestId: null,
    matchStatus: 'ignored',
    severity: 'error',
    issueCode: row.invalidIssue?.code || 'INVALID_ROW',
    details: { message: row.invalidIssue?.message || 'row is invalid', rawRow: row.raw },
  };
}

async function missingProviderPaymentDrafts(
  client: pg.PoolClient,
  tenantId: string,
  provider: ReconciliationProvider,
  billDate: string,
  seenPaymentIds: Set<string>,
  startRowNo: number,
) {
  const rows = await client.query<LocalPaymentMatch>(
    `
    select o.id as "orderId", o.order_no as "orderNo", o.status as "orderStatus",
           o.amount_cents as "orderAmountCents", o.refunded_amount_cents as "orderRefundedAmountCents",
           p.id as "paymentId", p.status as "paymentStatus", p.amount_cents as "paymentAmountCents",
           p.refunded_amount_cents as "paymentRefundedAmountCents", p.provider as "paymentProvider",
           p.provider_trade_no as "providerTradeNo", p.paid_at as "paidAt"
    from public.payments p
    join public.orders o on o.tenant_id = p.tenant_id and o.id = p.order_id
    where p.tenant_id = $1
      and p.provider = any($2::text[])
      and p.status in ('paid', 'partially_refunded', 'refunded')
      and (p.paid_at at time zone 'Asia/Shanghai')::date = $3::date
    order by p.paid_at asc nulls last, p.created_at asc
    limit 1000
    `,
    [tenantId, providerAliases(provider), billDate],
  );
  let rowNo = startRowNo;
  return rows.rows
    .filter(row => !seenPaymentIds.has(row.paymentId))
    .map(row => ({
      rowNo: rowNo++,
      provider,
      transactionType: 'payment' as const,
      providerTradeNo: row.providerTradeNo,
      providerRefundNo: null,
      orderNo: row.orderNo,
      refundNo: null,
      amountCents: row.paymentAmountCents,
      refundAmountCents: 0,
      feeCents: 0,
      paidAt: row.paidAt,
      refundedAt: null,
      providerStatus: null,
      localStatus: row.paymentStatus,
      orderId: row.orderId,
      paymentId: row.paymentId,
      refundRequestId: null,
      matchStatus: 'missing_provider' as const,
      severity: 'error' as const,
      issueCode: 'PAYMENT_MISSING_PROVIDER',
      details: {
        local: {
          orderNo: row.orderNo,
          orderStatus: row.orderStatus,
          paymentStatus: row.paymentStatus,
          paymentAmountCents: row.paymentAmountCents,
          providerTradeNo: row.providerTradeNo,
          paidAt: row.paidAt,
        },
      },
    }));
}

async function missingProviderRefundDrafts(
  client: pg.PoolClient,
  tenantId: string,
  provider: ReconciliationProvider,
  billDate: string,
  seenRefundRequestIds: Set<string>,
  startRowNo: number,
) {
  const rows = await client.query<LocalRefundMatch>(
    `
    select o.id as "orderId", o.order_no as "orderNo", o.status as "orderStatus",
           rr.id as "refundRequestId", rr.refund_no as "refundNo", rr.status as "refundStatus",
           rr.amount_cents as "amountCents", rr.provider, rr.provider_refund_no as "providerRefundNo",
           rr.payment_id as "paymentId", rr.succeeded_at as "succeededAt"
    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.provider is null or rr.provider = any($2::text[]))
      and rr.status = 'succeeded'
      and (rr.succeeded_at at time zone 'Asia/Shanghai')::date = $3::date
    order by rr.succeeded_at asc nulls last, rr.created_at asc
    limit 1000
    `,
    [tenantId, providerAliases(provider), billDate],
  );
  let rowNo = startRowNo;
  return rows.rows
    .filter(row => !seenRefundRequestIds.has(row.refundRequestId))
    .map(row => ({
      rowNo: rowNo++,
      provider,
      transactionType: 'refund' as const,
      providerTradeNo: null,
      providerRefundNo: row.providerRefundNo,
      orderNo: row.orderNo,
      refundNo: row.refundNo,
      amountCents: 0,
      refundAmountCents: row.amountCents,
      feeCents: 0,
      paidAt: null,
      refundedAt: row.succeededAt,
      providerStatus: null,
      localStatus: row.refundStatus,
      orderId: row.orderId,
      paymentId: row.paymentId,
      refundRequestId: row.refundRequestId,
      matchStatus: 'missing_provider' as const,
      severity: 'error' as const,
      issueCode: 'REFUND_MISSING_PROVIDER',
      details: {
        local: {
          orderNo: row.orderNo,
          orderStatus: row.orderStatus,
          refundNo: row.refundNo,
          providerRefundNo: row.providerRefundNo,
          refundStatus: row.refundStatus,
          amountCents: row.amountCents,
          succeededAt: row.succeededAt,
        },
      },
    }));
}

async function buildReconciliation(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    provider: ReconciliationProvider;
    billDate: string;
    billType: BillType;
    source: ReconciliationSource;
    sourceName: string | null;
    rows: NormalizedBillRow[];
  },
): Promise<ReconciliationBuildResult> {
  const items: ReconciliationItemDraft[] = [];
  const seenKeys = new Set<string>();
  const seenPaymentIds = new Set<string>();
  const seenRefundRequestIds = new Set<string>();

  for (const row of input.rows) {
    if (row.invalidIssue) {
      items.push(invalidDraft(row));
      continue;
    }

    const identity = rowIdentity(row);
    if (seenKeys.has(identity)) {
      items.push({
        ...baseDraft(row),
        localStatus: null,
        orderId: null,
        paymentId: null,
        refundRequestId: null,
        matchStatus: 'duplicate',
        severity: 'warning',
        issueCode: 'DUPLICATE_PROVIDER_ROW',
        details: { rawRow: row.raw, identity },
      });
      continue;
    }
    seenKeys.add(identity);

    if (row.transactionType === 'payment') {
      const local = await findPaymentMatch(client, input.tenantId, input.provider, row);
      if (local) seenPaymentIds.add(local.paymentId);
      items.push(paymentDraft(row, local));
    } else {
      const local = await findRefundMatch(client, input.tenantId, input.provider, row);
      if (local) seenRefundRequestIds.add(local.refundRequestId);
      items.push(refundDraft(row, local));
    }
  }

  if (input.billType !== 'refund') {
    const missingPayments = await missingProviderPaymentDrafts(client, input.tenantId, input.provider, input.billDate, seenPaymentIds, items.length + 1);
    items.push(...missingPayments);
  }
  if (input.billType !== 'payment') {
    const missingRefunds = await missingProviderRefundDrafts(client, input.tenantId, input.provider, input.billDate, seenRefundRequestIds, items.length + 1);
    items.push(...missingRefunds);
  }

  const matchedCount = items.filter(item => item.matchStatus === 'matched').length;
  const mismatchCount = items.filter(item => item.matchStatus === 'amount_mismatch' || item.matchStatus === 'status_mismatch').length;
  const missingLocalCount = items.filter(item => item.matchStatus === 'missing_local').length;
  const missingProviderCount = items.filter(item => item.matchStatus === 'missing_provider').length;
  const duplicateCount = items.filter(item => item.matchStatus === 'duplicate').length;
  const ignoredCount = items.filter(item => item.matchStatus === 'ignored').length;
  const hasIssues = mismatchCount + missingLocalCount + missingProviderCount + duplicateCount + ignoredCount > 0;
  const sourceHash = reconciliationHash({
    provider: input.provider,
    billDate: input.billDate,
    billType: input.billType,
    sourceName: input.sourceName,
    rows: input.rows,
  });

  return {
    batch: {
      provider: input.provider,
      billDate: input.billDate,
      billType: input.billType,
      source: input.source,
      sourceName: input.sourceName,
      sourceHash,
      status: hasIssues ? 'completed_with_issues' : 'completed',
      totalCount: items.length,
      matchedCount,
      mismatchCount,
      missingLocalCount,
      missingProviderCount,
      duplicateCount,
      ignoredCount,
      amountCents: items.reduce((sum, item) => sum + item.amountCents, 0),
      refundAmountCents: items.reduce((sum, item) => sum + item.refundAmountCents, 0),
      feeCents: items.reduce((sum, item) => sum + item.feeCents, 0),
    },
    items,
  };
}

function readReconciliationInput(body: Record<string, unknown>) {
  const provider = normalizeReconciliationProvider(requiredString(body, 'provider'));
  const billDate = normalizeReconciliationBillDate(requiredString(body, 'billDate'));
  const billType = normalizeReconciliationBillType(body.billType ?? body.bill_type);
  const source = normalizeSource(body.source);
  const sourceName = optionalString(body, 'sourceName') || optionalString(body, 'source_name') || null;
  const rawRows = body.rows;
  if (!Array.isArray(rawRows)) {
    throw new HttpError(400, 'rows must be an array', 'RECONCILIATION_ROWS_REQUIRED');
  }
  if (rawRows.length > 5000) {
    throw new HttpError(413, 'Too many reconciliation rows. Max 5000.', 'RECONCILIATION_ROWS_TOO_MANY');
  }
  const rows = normalizeRows(provider, billType, rawRows);
  return { provider, billDate, billType, source, sourceName, rows };
}

function normalizeProviderDownloadedRows(provider: ReconciliationProvider, billType: BillType, rows: unknown[]) {
  if (rows.length > MAX_PROVIDER_DOWNLOAD_ROWS) {
    throw new HttpError(413, `Too many provider bill rows. Max ${MAX_PROVIDER_DOWNLOAD_ROWS}.`, 'RECONCILIATION_PROVIDER_ROWS_TOO_MANY');
  }
  return normalizeRows(provider, billType, rows);
}

export async function importReconciliationBatch(
  client: pg.PoolClient,
  input: ReconciliationBatchImportInput,
) {
  const provider = input.provider;
  const billDate = normalizeReconciliationBillDate(input.billDate);
  const billType = normalizeReconciliationBillType(input.billType);
  const source = normalizeSource(input.source);
  const sourceName = input.sourceName;
  const rows = normalizeProviderDownloadedRows(provider, billType, input.rows);
  const built = await buildReconciliation(client, {
    tenantId: input.tenantId,
    provider,
    billDate,
    billType,
    source,
    sourceName,
    rows,
  });
  const insertedBatch = await client.query<Record<string, unknown>>(
    `
    insert into public.commerce_reconciliation_batches (
      tenant_id, provider, bill_date, bill_type, source, source_name, source_hash,
      status, total_count, matched_count, mismatch_count, missing_local_count,
      missing_provider_count, duplicate_count, ignored_count, amount_cents,
      refund_amount_cents, fee_cents, created_by, completed_at, metadata
    )
    values (
      $1, $2, $3::date, $4, $5, $6, $7,
      $8, $9, $10, $11, $12,
      $13, $14, $15, $16,
      $17, $18, $19::uuid, now(), $20::jsonb
    )
    returning id, provider, bill_date as "billDate", bill_type as "billType",
              source, source_name as "sourceName", source_hash as "sourceHash",
              status, total_count as "totalCount", matched_count as "matchedCount",
              mismatch_count as "mismatchCount", missing_local_count as "missingLocalCount",
              missing_provider_count as "missingProviderCount", duplicate_count as "duplicateCount",
              ignored_count as "ignoredCount", amount_cents as "amountCents",
              refund_amount_cents as "refundAmountCents", fee_cents as "feeCents",
              created_by as "createdBy", completed_at as "completedAt", error,
              metadata, created_at as "createdAt", updated_at as "updatedAt"
    `,
    [
      input.tenantId,
      built.batch.provider,
      built.batch.billDate,
      built.batch.billType,
      built.batch.source,
      built.batch.sourceName,
      built.batch.sourceHash,
      built.batch.status,
      built.batch.totalCount,
      built.batch.matchedCount,
      built.batch.mismatchCount,
      built.batch.missingLocalCount,
      built.batch.missingProviderCount,
      built.batch.duplicateCount,
      built.batch.ignoredCount,
      built.batch.amountCents,
      built.batch.refundAmountCents,
      built.batch.feeCents,
      input.actorUserId,
      JSON.stringify(input.metadata || {}),
    ],
  );
  const batch = insertedBatch.rows[0];
  const batchId = String(batch.id);

  for (const item of built.items) {
    await client.query(
      `
      insert into public.commerce_reconciliation_items (
        tenant_id, batch_id, row_no, provider, transaction_type,
        provider_trade_no, provider_refund_no, order_no, refund_no,
        amount_cents, refund_amount_cents, fee_cents, paid_at, refunded_at,
        provider_status, local_status, order_id, payment_id, refund_request_id,
        match_status, severity, issue_code, details
      )
      values (
        $1, $2, $3, $4, $5,
        $6, $7, $8, $9,
        $10, $11, $12, $13::timestamptz, $14::timestamptz,
        $15, $16, $17::uuid, $18::uuid, $19::uuid,
        $20, $21, $22, $23::jsonb
      )
      `,
      [
        input.tenantId,
        batchId,
        item.rowNo,
        item.provider,
        item.transactionType,
        item.providerTradeNo,
        item.providerRefundNo,
        item.orderNo,
        item.refundNo,
        item.amountCents,
        item.refundAmountCents,
        item.feeCents,
        item.paidAt,
        item.refundedAt,
        item.providerStatus,
        item.localStatus,
        item.orderId,
        item.paymentId,
        item.refundRequestId,
        item.matchStatus,
        item.severity,
        item.issueCode,
        JSON.stringify(item.details),
      ],
    );
  }

  await client.query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2::uuid, 'commerce.reconciliation.imported', 'commerce_reconciliation_batch', $3, $4::jsonb)
    `,
    [
      input.tenantId,
      input.actorUserId,
      batchId,
      JSON.stringify({
        provider: built.batch.provider,
        billDate: built.batch.billDate,
        billType: built.batch.billType,
        source: built.batch.source,
        sourceName: built.batch.sourceName,
        sourceHash: built.batch.sourceHash,
        summary: {
          totalCount: built.batch.totalCount,
          matchedCount: built.batch.matchedCount,
          mismatchCount: built.batch.mismatchCount,
          missingLocalCount: built.batch.missingLocalCount,
          missingProviderCount: built.batch.missingProviderCount,
          duplicateCount: built.batch.duplicateCount,
          ignoredCount: built.batch.ignoredCount,
        },
      }),
    ],
  );

  return { batch, built };
}

function batchPayload(row: Record<string, unknown>) {
  return {
    id: row.id,
    provider: row.provider,
    billDate: row.billDate,
    billType: row.billType,
    source: row.source,
    sourceName: row.sourceName,
    sourceHash: row.sourceHash,
    status: row.status,
    totalCount: row.totalCount,
    matchedCount: row.matchedCount,
    mismatchCount: row.mismatchCount,
    missingLocalCount: row.missingLocalCount,
    missingProviderCount: row.missingProviderCount,
    duplicateCount: row.duplicateCount,
    ignoredCount: row.ignoredCount,
    amountCents: row.amountCents,
    refundAmountCents: row.refundAmountCents,
    feeCents: row.feeCents,
    createdBy: row.createdBy,
    completedAt: row.completedAt,
    error: row.error,
    metadata: row.metadata || {},
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  };
}

function itemPayload(row: Record<string, unknown>) {
  return {
    id: row.id,
    batchId: row.batchId,
    rowNo: row.rowNo,
    provider: row.provider,
    transactionType: row.transactionType,
    providerTradeNo: row.providerTradeNo,
    providerRefundNo: row.providerRefundNo,
    orderNo: row.orderNo,
    refundNo: row.refundNo,
    amountCents: row.amountCents,
    refundAmountCents: row.refundAmountCents,
    feeCents: row.feeCents,
    paidAt: row.paidAt,
    refundedAt: row.refundedAt,
    providerStatus: row.providerStatus,
    localStatus: row.localStatus,
    orderId: row.orderId,
    paymentId: row.paymentId,
    refundRequestId: row.refundRequestId,
    matchStatus: row.matchStatus,
    severity: row.severity,
    issueCode: row.issueCode,
    details: row.details || {},
    createdAt: row.createdAt,
  };
}

function issuePayload(row: Record<string, unknown>) {
  return {
    id: row.id,
    issueNo: row.issueNo,
    batchId: row.batchId,
    itemId: row.itemId,
    provider: row.provider,
    transactionType: row.transactionType,
    issueCode: row.issueCode,
    matchStatus: row.matchStatus,
    severity: row.severity,
    status: row.status,
    resolutionType: row.resolutionType,
    orderId: row.orderId,
    paymentId: row.paymentId,
    refundRequestId: row.refundRequestId,
    orderNo: row.orderNo,
    refundNo: row.refundNo,
    providerTradeNo: row.providerTradeNo,
    providerRefundNo: row.providerRefundNo,
    amountCents: row.amountCents,
    refundAmountCents: row.refundAmountCents,
    assignedTo: row.assignedTo,
    assignedToName: row.assignedToName,
    createdBy: row.createdBy,
    createdByName: row.createdByName,
    resolvedBy: row.resolvedBy,
    resolvedByName: row.resolvedByName,
    resolvedAt: row.resolvedAt,
    dueAt: row.dueAt,
    summary: row.summary,
    resolutionNote: row.resolutionNote,
    metadata: row.metadata || {},
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  };
}

function issueEventPayload(row: Record<string, unknown>) {
  return {
    id: row.id,
    issueId: row.issueId,
    fromStatus: row.fromStatus,
    toStatus: row.toStatus,
    eventType: row.eventType,
    actorUserId: row.actorUserId,
    actorName: row.actorName,
    note: row.note,
    details: row.details || {},
    createdAt: row.createdAt,
  };
}

async function recordReconciliationAudit(client: pg.PoolClient, auth: TenantAdminAuth, action: 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, 'commerce_reconciliation_batch', $4, $5::jsonb)
    `,
    [auth.tenantId, auth.userId, action, targetId, JSON.stringify(details)],
  );
}

async function recordReconciliationIssueEvent(
  client: pg.PoolClient,
  auth: TenantAdminAuth,
  input: {
    issueId: string;
    fromStatus?: string | null;
    toStatus?: string | null;
    eventType: string;
    note?: string | null;
    details?: Record<string, unknown>;
  },
) {
  await client.query(
    `
    insert into public.commerce_reconciliation_issue_events (
      tenant_id, issue_id, from_status, to_status, event_type, actor_user_id, note, details
    )
    values ($1, $2, $3, $4, $5, $6, $7, $8::jsonb)
    `,
    [
      auth.tenantId,
      input.issueId,
      input.fromStatus || null,
      input.toStatus || null,
      input.eventType,
      auth.userId,
      input.note || null,
      JSON.stringify(input.details || {}),
    ],
  );
}

async function ensureAssignableTenantMember(client: pg.PoolClient, tenantId: string, userId: string | null) {
  if (!userId) return null;
  const result = await client.query<{ id: string }>(
    `
    select tm.user_id as id
    from public.tenant_memberships tm
    where tm.tenant_id = $1
      and tm.user_id = $2
      and tm.status = 'active'
      and tm.role in ('tenant_owner', 'tenant_admin', 'tenant_operator', 'teacher', 'sales', 'agent')
    limit 1
    `,
    [tenantId, userId],
  );
  if (!result.rows[0]) {
    throw new HttpError(400, 'Assignee must be an active tenant staff member', 'RECONCILIATION_ASSIGNEE_INVALID');
  }
  return userId;
}

function nextIssueNo() {
  const dateKey = new Date().toISOString().slice(0, 10).replace(/-/g, '');
  return `RC-${dateKey}-${crypto.randomInt(100000, 999999)}`;
}

function issueSummaryForItem(item: Record<string, unknown>, summary: string | null) {
  if (summary) return summary;
  const identity = item.orderNo || item.refundNo || item.providerTradeNo || item.providerRefundNo || item.id;
  return `${item.issueCode || item.matchStatus || 'RECONCILIATION_ISSUE'} ${identity}`;
}

async function fetchIssueForTenant(client: pg.PoolClient, tenantId: string, issueId: string, lock = false) {
  const result = await client.query<Record<string, unknown>>(
    `
    select i.id, i.issue_no as "issueNo", i.batch_id as "batchId", i.item_id as "itemId",
           i.provider, i.transaction_type as "transactionType", i.issue_code as "issueCode",
           i.match_status as "matchStatus", i.severity, i.status,
           i.resolution_type as "resolutionType", i.order_id as "orderId",
           i.payment_id as "paymentId", i.refund_request_id as "refundRequestId",
           i.order_no as "orderNo", i.refund_no as "refundNo",
           i.provider_trade_no as "providerTradeNo", i.provider_refund_no as "providerRefundNo",
           i.amount_cents as "amountCents", i.refund_amount_cents as "refundAmountCents",
           i.assigned_to as "assignedTo", assignee.name as "assignedToName",
           i.created_by as "createdBy", creator.name as "createdByName",
           i.resolved_by as "resolvedBy", resolver.name as "resolvedByName",
           i.resolved_at as "resolvedAt", i.due_at as "dueAt", i.summary,
           i.resolution_note as "resolutionNote", i.metadata,
           i.created_at as "createdAt", i.updated_at as "updatedAt"
    from public.commerce_reconciliation_issues i
    left join public.platform_users assignee on assignee.id = i.assigned_to
    left join public.platform_users creator on creator.id = i.created_by
    left join public.platform_users resolver on resolver.id = i.resolved_by
    where i.tenant_id = $1 and i.id = $2
    limit 1
    ${lock ? 'for update of i' : ''}
    `,
    [tenantId, issueId],
  );
  return result.rows[0] || null;
}

async function authorizeRead(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:reconciliation:read');
  return auth;
}

async function authorizeWrite(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:reconciliation:write');
  return auth;
}

async function authorizeDownload(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:reconciliation:download');
  return auth;
}

function providerBillJobPayload(row: Record<string, unknown>) {
  return {
    id: row.id,
    provider: row.provider,
    billDate: row.billDate,
    billType: row.billType,
    status: row.status,
    sourceName: row.sourceName,
    sourceHash: row.sourceHash,
    rowCount: row.rowCount,
    downloadHashType: row.downloadHashType,
    downloadHashValue: row.downloadHashValue,
    downloadUrlHost: row.downloadUrlHost,
    reconciliationBatchId: row.reconciliationBatchId,
    requestedBy: row.requestedBy,
    claimedBy: row.claimedBy,
    claimedAt: row.claimedAt,
    completedAt: row.completedAt,
    failedAt: row.failedAt,
    errorCode: row.errorCode,
    errorMessage: row.errorMessage,
    metadata: row.metadata || {},
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  };
}

export async function requestProviderBillDownloadRoute(ctx: RequestContext) {
  const auth = await authorizeDownload(ctx);
  const body = await readJsonBody(ctx);
  const provider = normalizeReconciliationProvider(requiredString(body, 'provider'));
  if (provider === 'manual') {
    throw new HttpError(400, 'Manual provider does not support official bill download', 'PROVIDER_BILL_DOWNLOAD_UNSUPPORTED');
  }
  const billDate = normalizeReconciliationBillDate(requiredString(body, 'billDate'));
  const billType = normalizeReconciliationBillType(body.billType ?? body.bill_type);
  const metadata = objectValue(body.metadata);
  const sourceName = `provider-bill:${provider}:${billDate}:${billType}`;

  const result = await transaction(async client => {
    const inserted = await client.query<Record<string, unknown>>(
      `
      insert into public.commerce_bill_download_jobs (
        tenant_id, provider, bill_date, bill_type, status,
        source_name, requested_by, metadata
      )
      values ($1, $2, $3::date, $4, 'queued', $5, $6::uuid, $7::jsonb)
      on conflict (tenant_id, provider, bill_date, bill_type)
      where status in ('queued', 'running', 'completed')
      do update set
        metadata = public.commerce_bill_download_jobs.metadata || excluded.metadata,
        updated_at = now()
      returning id, provider, bill_date as "billDate", bill_type as "billType",
                status, source_name as "sourceName", source_hash as "sourceHash",
                row_count as "rowCount", download_hash_type as "downloadHashType",
                download_hash_value as "downloadHashValue", download_url_host as "downloadUrlHost",
                reconciliation_batch_id as "reconciliationBatchId",
                requested_by as "requestedBy", claimed_by as "claimedBy",
                claimed_at as "claimedAt", completed_at as "completedAt",
                failed_at as "failedAt", error_code as "errorCode",
                error_message as "errorMessage", metadata,
                created_at as "createdAt", updated_at as "updatedAt",
                (xmax = 0) as "inserted"
      `,
      [
        auth.tenantId,
        provider,
        billDate,
        billType,
        sourceName,
        auth.userId,
        JSON.stringify({
          ...metadata,
          requestedFrom: 'api',
        }),
      ],
    );
    const job = inserted.rows[0];
    await recordReconciliationAudit(
      client,
      auth,
      job.inserted ? 'commerce.provider_bill_download.requested' : 'commerce.provider_bill_download.request_reused',
      String(job.id),
      {
        provider,
        billDate,
        billType,
        sourceName,
        status: job.status,
      },
    );
    return job;
  });

  return {
    item: providerBillJobPayload(result),
    idempotent: result.inserted === false,
  };
}

export async function providerBillDownloadJobsRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const provider = stringParam(ctx, 'provider');
  const status = stringParam(ctx, 'status');
  const billDate = stringParam(ctx, 'billDate');

  const params: unknown[] = [auth.tenantId, limit];
  const where = ['tenant_id = $1'];
  if (provider) {
    const normalized = normalizeReconciliationProvider(provider);
    if (normalized === 'manual') throw new HttpError(400, 'provider is invalid for bill download', 'PROVIDER_BILL_DOWNLOAD_UNSUPPORTED');
    params.push(normalized);
    where.push(`provider = $${params.length}`);
  }
  if (status) {
    params.push(status);
    where.push(`status = $${params.length}`);
  }
  if (billDate) {
    params.push(normalizeReconciliationBillDate(billDate));
    where.push(`bill_date = $${params.length}::date`);
  }

  const items = await query<Record<string, unknown>>(
    `
    select id, provider, bill_date as "billDate", bill_type as "billType",
           status, source_name as "sourceName", source_hash as "sourceHash",
           row_count as "rowCount", download_hash_type as "downloadHashType",
           download_hash_value as "downloadHashValue", download_url_host as "downloadUrlHost",
           reconciliation_batch_id as "reconciliationBatchId",
           requested_by as "requestedBy", claimed_by as "claimedBy",
           claimed_at as "claimedAt", completed_at as "completedAt",
           failed_at as "failedAt", error_code as "errorCode",
           error_message as "errorMessage", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.commerce_bill_download_jobs
    where ${where.join(' and ')}
    order by created_at desc
    limit $2
    `,
    params,
  );
  return { items: items.map(providerBillJobPayload) };
}

export async function previewReconciliationRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const body = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const input = readReconciliationInput(body);
  const result = await transaction(client => buildReconciliation(client, { tenantId: auth.tenantId, ...input }));
  return {
    item: {
      ...result.batch,
      status: 'preview',
    },
    items: result.items.slice(0, optionalInteger(body, 'previewLimit', 200)).map(item => ({
      ...item,
      id: null,
      batchId: null,
    })),
  };
}

export async function importReconciliationRoute(ctx: RequestContext) {
  const auth = await authorizeWrite(ctx);
  const body = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const input = readReconciliationInput(body);
  const metadata = objectValue(body.metadata);

  const result = await transaction(async client => {
    const imported = await importReconciliationBatch(client, {
      tenantId: auth.tenantId,
      actorUserId: auth.userId,
      provider: input.provider,
      billDate: input.billDate,
      billType: input.billType,
      source: input.source,
      sourceName: input.sourceName,
      rows: body.rows as unknown[],
      metadata,
    });
    return {
      batch: imported.batch,
      previewItems: imported.built.items.slice(0, optionalInteger(body, 'previewLimit', 50)),
    };
  });

  return {
    item: batchPayload(result.batch),
    previewItems: result.previewItems.map(item => ({ ...item, id: null, batchId: String(result.batch.id) })),
  };
}

export async function reconciliationBatchesRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const provider = stringParam(ctx, 'provider');
  const status = stringParam(ctx, 'status');
  const billDate = stringParam(ctx, 'billDate');

  const params: unknown[] = [auth.tenantId, limit];
  const where = ['tenant_id = $1'];
  if (provider) {
    params.push(normalizeReconciliationProvider(provider));
    where.push(`provider = $${params.length}`);
  }
  if (status) {
    params.push(status);
    where.push(`status = $${params.length}`);
  }
  if (billDate) {
    params.push(normalizeReconciliationBillDate(billDate));
    where.push(`bill_date = $${params.length}::date`);
  }

  const items = await query<Record<string, unknown>>(
    `
    select id, provider, bill_date as "billDate", bill_type as "billType",
           source, source_name as "sourceName", source_hash as "sourceHash",
           status, total_count as "totalCount", matched_count as "matchedCount",
           mismatch_count as "mismatchCount", missing_local_count as "missingLocalCount",
           missing_provider_count as "missingProviderCount", duplicate_count as "duplicateCount",
           ignored_count as "ignoredCount", amount_cents as "amountCents",
           refund_amount_cents as "refundAmountCents", fee_cents as "feeCents",
           created_by as "createdBy", completed_at as "completedAt", error,
           metadata, created_at as "createdAt", updated_at as "updatedAt"
    from public.commerce_reconciliation_batches
    where ${where.join(' and ')}
    order by created_at desc
    limit $2
    `,
    params,
  );
  return { items: items.map(batchPayload) };
}

export async function reconciliationItemsRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 100, 500);
  const batchId = stringParam(ctx, 'batchId');
  const matchStatus = stringParam(ctx, 'matchStatus');
  const severity = stringParam(ctx, 'severity');
  const orderNo = stringParam(ctx, 'orderNo');

  const params: unknown[] = [auth.tenantId, limit];
  const where = ['tenant_id = $1'];
  if (batchId) {
    params.push(batchId);
    where.push(`batch_id = $${params.length}::uuid`);
  }
  if (matchStatus) {
    params.push(matchStatus);
    where.push(`match_status = $${params.length}`);
  }
  if (severity) {
    params.push(severity);
    where.push(`severity = $${params.length}`);
  }
  if (orderNo) {
    params.push(orderNo);
    where.push(`order_no = $${params.length}`);
  }

  const items = await query<Record<string, unknown>>(
    `
    select id, batch_id as "batchId", row_no as "rowNo", provider,
           transaction_type as "transactionType", provider_trade_no as "providerTradeNo",
           provider_refund_no as "providerRefundNo", order_no as "orderNo", refund_no as "refundNo",
           amount_cents as "amountCents", refund_amount_cents as "refundAmountCents",
           fee_cents as "feeCents", paid_at as "paidAt", refunded_at as "refundedAt",
           provider_status as "providerStatus", local_status as "localStatus",
           order_id as "orderId", payment_id as "paymentId",
           refund_request_id as "refundRequestId", match_status as "matchStatus",
           severity, issue_code as "issueCode", details, created_at as "createdAt"
    from public.commerce_reconciliation_items
    where ${where.join(' and ')}
    order by created_at desc, row_no desc
    limit $2
    `,
    params,
  );
  return { items: items.map(itemPayload) };
}

export async function reconciliationAnomaliesRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const providerParam = stringParam(ctx, 'provider');
  const provider = providerParam ? normalizeReconciliationProvider(providerParam) : '';

  const issueParams: unknown[] = [auth.tenantId, limit];
  const issueWhere = [`i.tenant_id = $1`, `i.match_status not in ('matched', 'ignored')`];
  if (provider) {
    issueParams.push(provider);
    issueWhere.push(`i.provider = $${issueParams.length}`);
  }
  const reconciliationIssues = await query<Record<string, unknown>>(
    `
    select i.id, i.batch_id as "batchId", b.bill_date as "billDate", b.source_name as "sourceName",
           i.row_no as "rowNo", i.provider, i.transaction_type as "transactionType",
           i.provider_trade_no as "providerTradeNo", i.provider_refund_no as "providerRefundNo",
           i.order_no as "orderNo", i.refund_no as "refundNo",
           i.amount_cents as "amountCents", i.refund_amount_cents as "refundAmountCents",
           i.match_status as "matchStatus", i.severity, i.issue_code as "issueCode",
           i.details, i.created_at as "createdAt"
    from public.commerce_reconciliation_items i
    join public.commerce_reconciliation_batches b on b.tenant_id = i.tenant_id and b.id = i.batch_id
    where ${issueWhere.join(' and ')}
    order by i.created_at desc
    limit $2
    `,
    issueParams,
  );

  const eventParams: unknown[] = [auth.tenantId, limit];
  const eventWhere = [`tenant_id = $1`, `error is not null`];
  if (provider) {
    eventParams.push(provider);
    eventWhere.push(`provider = $${eventParams.length}`);
  }
  const paymentEventErrors = await query<Record<string, unknown>>(
    `
    select id, payment_id as "paymentId", provider, event_type as "eventType",
           event_id as "eventId", error, created_at as "createdAt", processed_at as "processedAt"
    from public.payment_events
    where ${eventWhere.join(' and ')}
    order by created_at desc
    limit $2
    `,
    eventParams,
  );

  return {
    items: reconciliationIssues.map(item => ({
      source: 'reconciliation',
      ...itemPayload(item),
      billDate: item.billDate,
      sourceName: item.sourceName,
    })),
    paymentEventErrors,
  };
}

export async function createReconciliationIssueRoute(ctx: RequestContext) {
  const auth = await authorizeWrite(ctx);
  const body = await readJsonBody(ctx);
  const itemId = requiredString(body, 'itemId');
  const assignedTo = nullableString(body.assignedTo) || nullableString(body.assignedToUserId);
  const dueAt = timestampValue(body.dueAt, 'dueAt');
  const summary = nullableString(body.summary);
  const note = nullableString(body.note);

  const result = await transaction(async client => {
    const itemResult = await client.query<Record<string, unknown>>(
      `
      select id, batch_id as "batchId", provider, transaction_type as "transactionType",
             provider_trade_no as "providerTradeNo", provider_refund_no as "providerRefundNo",
             order_no as "orderNo", refund_no as "refundNo",
             amount_cents as "amountCents", refund_amount_cents as "refundAmountCents",
             order_id as "orderId", payment_id as "paymentId", refund_request_id as "refundRequestId",
             match_status as "matchStatus", severity, issue_code as "issueCode", details
      from public.commerce_reconciliation_items
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, itemId],
    );
    const item = itemResult.rows[0];
    if (!item) throw new HttpError(404, 'Reconciliation item not found', 'RECONCILIATION_ITEM_NOT_FOUND');
    if (!ACTIONABLE_MATCH_STATUSES.has(item.matchStatus as MatchStatus)) {
      throw new HttpError(400, 'Reconciliation item is not actionable', 'RECONCILIATION_ITEM_NOT_ACTIONABLE');
    }

    const existing = await client.query<Record<string, unknown>>(
      `
      select i.id, i.issue_no as "issueNo", i.batch_id as "batchId", i.item_id as "itemId",
             i.provider, i.transaction_type as "transactionType", i.issue_code as "issueCode",
             i.match_status as "matchStatus", i.severity, i.status,
             i.resolution_type as "resolutionType", i.order_id as "orderId",
             i.payment_id as "paymentId", i.refund_request_id as "refundRequestId",
             i.order_no as "orderNo", i.refund_no as "refundNo",
             i.provider_trade_no as "providerTradeNo", i.provider_refund_no as "providerRefundNo",
             i.amount_cents as "amountCents", i.refund_amount_cents as "refundAmountCents",
             i.assigned_to as "assignedTo", assignee.name as "assignedToName",
             i.created_by as "createdBy", creator.name as "createdByName",
             i.resolved_by as "resolvedBy", resolver.name as "resolvedByName",
             i.resolved_at as "resolvedAt", i.due_at as "dueAt", i.summary,
             i.resolution_note as "resolutionNote", i.metadata,
             i.created_at as "createdAt", i.updated_at as "updatedAt"
      from public.commerce_reconciliation_issues i
      left join public.platform_users assignee on assignee.id = i.assigned_to
      left join public.platform_users creator on creator.id = i.created_by
      left join public.platform_users resolver on resolver.id = i.resolved_by
      where i.tenant_id = $1
        and i.item_id = $2
        and i.status in ('open', 'investigating', 'escalated')
      order by i.created_at desc
      limit 1
      `,
      [auth.tenantId, itemId],
    );
    if (existing.rows[0]) return { issue: existing.rows[0], idempotent: true };

    const assignee = await ensureAssignableTenantMember(client, auth.tenantId, assignedTo);
    const issue = await client.query<Record<string, unknown>>(
      `
      insert into public.commerce_reconciliation_issues (
        tenant_id, batch_id, item_id, issue_no, provider, transaction_type,
        issue_code, match_status, severity, status, resolution_type,
        order_id, payment_id, refund_request_id, order_no, refund_no,
        provider_trade_no, provider_refund_no, amount_cents, refund_amount_cents,
        assigned_to, created_by, due_at, summary, metadata
      )
      values (
        $1, $2::uuid, $3::uuid, $4, $5, $6,
        $7, $8, $9, 'open', 'none',
        $10::uuid, $11::uuid, $12::uuid, $13, $14,
        $15, $16, $17, $18,
        $19::uuid, $20::uuid, $21::timestamptz, $22, $23::jsonb
      )
      returning id, issue_no as "issueNo", batch_id as "batchId", item_id as "itemId",
                provider, transaction_type as "transactionType", issue_code as "issueCode",
                match_status as "matchStatus", severity, status,
                resolution_type as "resolutionType", order_id as "orderId",
                payment_id as "paymentId", refund_request_id as "refundRequestId",
                order_no as "orderNo", refund_no as "refundNo",
                provider_trade_no as "providerTradeNo", provider_refund_no as "providerRefundNo",
                amount_cents as "amountCents", refund_amount_cents as "refundAmountCents",
                assigned_to as "assignedTo", created_by as "createdBy", resolved_by as "resolvedBy",
                resolved_at as "resolvedAt", due_at as "dueAt", summary,
                resolution_note as "resolutionNote", metadata, created_at as "createdAt",
                updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        item.batchId,
        item.id,
        nextIssueNo(),
        item.provider,
        item.transactionType,
        item.issueCode,
        item.matchStatus,
        item.severity,
        item.orderId,
        item.paymentId,
        item.refundRequestId,
        item.orderNo,
        item.refundNo,
        item.providerTradeNo,
        item.providerRefundNo,
        item.amountCents,
        item.refundAmountCents,
        assignee,
        auth.userId,
        dueAt,
        issueSummaryForItem(item, summary),
        jsonBodyValue(body.metadata),
      ],
    );

    const created = await fetchIssueForTenant(client, auth.tenantId, String(issue.rows[0].id));
    await recordReconciliationIssueEvent(client, auth, {
      issueId: String(issue.rows[0].id),
      toStatus: 'open',
      eventType: 'created',
      note,
      details: {
        itemId,
        assignedTo: assignee,
        dueAt,
        matchStatus: item.matchStatus,
        issueCode: item.issueCode,
      },
    });
    await recordReconciliationAudit(client, auth, 'commerce.reconciliation_issue.created', String(issue.rows[0].id), {
      itemId,
      batchId: item.batchId,
      assignedTo: assignee,
      dueAt,
      matchStatus: item.matchStatus,
      issueCode: item.issueCode,
    });

    return { issue: created || issue.rows[0], idempotent: false };
  });

  return { item: issuePayload(result.issue), idempotent: result.idempotent };
}

export async function reconciliationIssuesRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const status = stringParam(ctx, 'status');
  const severity = stringParam(ctx, 'severity');
  const assignedTo = stringParam(ctx, 'assignedTo') || stringParam(ctx, 'assignedToUserId');
  const batchId = stringParam(ctx, 'batchId');
  const orderNo = stringParam(ctx, 'orderNo');

  const params: unknown[] = [auth.tenantId, limit];
  const where = ['i.tenant_id = $1'];
  if (status) {
    if (!ISSUE_STATUSES.has(status as ReconciliationIssueStatus)) {
      throw new HttpError(400, 'status is invalid', 'RECONCILIATION_ISSUE_STATUS_INVALID');
    }
    params.push(status);
    where.push(`i.status = $${params.length}`);
  }
  if (severity) {
    params.push(severity);
    where.push(`i.severity = $${params.length}`);
  }
  if (assignedTo) {
    params.push(assignedTo);
    where.push(`i.assigned_to = $${params.length}::uuid`);
  }
  if (batchId) {
    params.push(batchId);
    where.push(`i.batch_id = $${params.length}::uuid`);
  }
  if (orderNo) {
    params.push(orderNo);
    where.push(`i.order_no = $${params.length}`);
  }

  const items = await query<Record<string, unknown>>(
    `
    select i.id, i.issue_no as "issueNo", i.batch_id as "batchId", i.item_id as "itemId",
           i.provider, i.transaction_type as "transactionType", i.issue_code as "issueCode",
           i.match_status as "matchStatus", i.severity, i.status,
           i.resolution_type as "resolutionType", i.order_id as "orderId",
           i.payment_id as "paymentId", i.refund_request_id as "refundRequestId",
           i.order_no as "orderNo", i.refund_no as "refundNo",
           i.provider_trade_no as "providerTradeNo", i.provider_refund_no as "providerRefundNo",
           i.amount_cents as "amountCents", i.refund_amount_cents as "refundAmountCents",
           i.assigned_to as "assignedTo", assignee.name as "assignedToName",
           i.created_by as "createdBy", creator.name as "createdByName",
           i.resolved_by as "resolvedBy", resolver.name as "resolvedByName",
           i.resolved_at as "resolvedAt", i.due_at as "dueAt", i.summary,
           i.resolution_note as "resolutionNote", i.metadata,
           i.created_at as "createdAt", i.updated_at as "updatedAt"
    from public.commerce_reconciliation_issues i
    left join public.platform_users assignee on assignee.id = i.assigned_to
    left join public.platform_users creator on creator.id = i.created_by
    left join public.platform_users resolver on resolver.id = i.resolved_by
    where ${where.join(' and ')}
    order by
      case i.status
        when 'open' then 1
        when 'investigating' then 2
        when 'escalated' then 3
        when 'resolved' then 4
        else 5
      end,
      i.severity desc,
      i.created_at desc
    limit $2
    `,
    params,
  );
  return { items: items.map(issuePayload) };
}

export async function updateReconciliationIssueStatusRoute(ctx: RequestContext) {
  const auth = await authorizeWrite(ctx);
  const body = await readJsonBody(ctx);
  const issueId = requiredString(body, 'issueId');
  const action = optionalChoice(body.action, ISSUE_ACTIONS, 'start', 'RECONCILIATION_ISSUE_ACTION_INVALID');
  const note = nullableString(body.note);
  const dueAt = timestampValue(body.dueAt, 'dueAt');
  const assignedTo = nullableString(body.assignedTo) || nullableString(body.assignedToUserId);
  const resolutionTypeInput = nullableString(body.resolutionType);

  const result = await transaction(async client => {
    const issue = await fetchIssueForTenant(client, auth.tenantId, issueId, true);
    if (!issue) throw new HttpError(404, 'Reconciliation issue not found', 'RECONCILIATION_ISSUE_NOT_FOUND');

    const currentStatus = String(issue.status) as ReconciliationIssueStatus;
    let nextStatus = currentStatus;
    let nextResolutionType = String(issue.resolutionType || 'none') as ReconciliationResolutionType;
    let resolvedBy: string | null = String(issue.resolvedBy || '') || null;
    let resolvedAt: string | null = String(issue.resolvedAt || '') || null;
    let resolutionNote = nullableString(body.resolutionNote) || nullableString(body.note) || String(issue.resolutionNote || '') || null;
    const assignee = assignedTo ? await ensureAssignableTenantMember(client, auth.tenantId, assignedTo) : null;
    const details: Record<string, unknown> = {
      action,
      previousStatus: currentStatus,
    };

    if (action === 'start') {
      if (!['open', 'escalated'].includes(currentStatus)) {
        throw new HttpError(409, 'Only open or escalated issues can be started', 'RECONCILIATION_ISSUE_STATUS_CONFLICT');
      }
      nextStatus = 'investigating';
    } else if (action === 'assign') {
      if (!assignee) {
        throw new HttpError(400, 'assignedTo is required for assign action', 'RECONCILIATION_ASSIGNEE_REQUIRED');
      }
      details.assignedTo = assignee;
    } else if (action === 'resolve') {
      if (['resolved', 'ignored'].includes(currentStatus)) {
        throw new HttpError(409, 'Closed reconciliation issues must be reopened first', 'RECONCILIATION_ISSUE_STATUS_CONFLICT');
      }
      nextResolutionType = optionalChoice(
        resolutionTypeInput,
        RESOLUTION_TYPES,
        'manual_adjustment',
        'RECONCILIATION_RESOLUTION_TYPE_INVALID',
      );
      if (nextResolutionType === 'none') {
        throw new HttpError(400, 'resolutionType is required when resolving an issue', 'RECONCILIATION_RESOLUTION_REQUIRED');
      }
      nextStatus = 'resolved';
      resolvedBy = auth.userId;
      resolvedAt = new Date().toISOString();
      details.resolutionType = nextResolutionType;
    } else if (action === 'ignore') {
      if (['resolved', 'ignored'].includes(currentStatus)) {
        throw new HttpError(409, 'Closed reconciliation issues must be reopened first', 'RECONCILIATION_ISSUE_STATUS_CONFLICT');
      }
      nextResolutionType = optionalChoice(
        resolutionTypeInput,
        RESOLUTION_TYPES,
        'false_positive',
        'RECONCILIATION_RESOLUTION_TYPE_INVALID',
      );
      if (nextResolutionType === 'none') nextResolutionType = 'false_positive';
      nextStatus = 'ignored';
      resolvedBy = auth.userId;
      resolvedAt = new Date().toISOString();
      details.resolutionType = nextResolutionType;
    } else if (action === 'escalate') {
      if (['resolved', 'ignored'].includes(currentStatus)) {
        throw new HttpError(409, 'Closed reconciliation issues must be reopened first', 'RECONCILIATION_ISSUE_STATUS_CONFLICT');
      }
      nextStatus = 'escalated';
    } else if (action === 'reopen') {
      if (!['resolved', 'ignored'].includes(currentStatus)) {
        throw new HttpError(409, 'Only closed reconciliation issues can be reopened', 'RECONCILIATION_ISSUE_STATUS_CONFLICT');
      }
      nextStatus = 'open';
      nextResolutionType = 'none';
      resolvedBy = null;
      resolvedAt = null;
      resolutionNote = null;
    }

    const update = await client.query<Record<string, unknown>>(
      `
      update public.commerce_reconciliation_issues
      set status = $3,
          resolution_type = $4,
          resolution_note = $5,
          assigned_to = coalesce($6::uuid, assigned_to),
          due_at = coalesce($7::timestamptz, due_at),
          resolved_by = $8::uuid,
          resolved_at = $9::timestamptz,
          metadata = metadata || $10::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id
      `,
      [
        auth.tenantId,
        issueId,
        nextStatus,
        nextResolutionType,
        resolutionNote,
        assignee,
        dueAt,
        resolvedBy,
        resolvedAt,
        jsonBodyValue(body.metadata),
      ],
    );
    if (!update.rows[0]) throw new HttpError(404, 'Reconciliation issue not found', 'RECONCILIATION_ISSUE_NOT_FOUND');

    await recordReconciliationIssueEvent(client, auth, {
      issueId,
      fromStatus: currentStatus,
      toStatus: nextStatus,
      eventType: action,
      note,
      details: {
        ...details,
        assignedTo: assignee || issue.assignedTo || null,
        dueAt: dueAt || issue.dueAt || null,
      },
    });
    await recordReconciliationAudit(client, auth, 'commerce.reconciliation_issue.status_updated', issueId, {
      action,
      fromStatus: currentStatus,
      toStatus: nextStatus,
      assignedTo: assignee || issue.assignedTo || null,
      dueAt: dueAt || issue.dueAt || null,
      resolutionType: nextResolutionType,
      noteProvided: Boolean(note),
    });

    const nextIssue = await fetchIssueForTenant(client, auth.tenantId, issueId);
    return nextIssue || issue;
  });

  return { item: issuePayload(result) };
}

export async function reconciliationIssueEventsRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const issueId = stringParam(ctx, 'issueId');
  if (!issueId) throw new HttpError(400, 'issueId is required', 'RECONCILIATION_ISSUE_ID_REQUIRED');
  const limit = intParam(ctx, 'limit', 100, 300);

  const issue = await query<Record<string, unknown>>(
    'select id from public.commerce_reconciliation_issues where tenant_id = $1 and id = $2 limit 1',
    [auth.tenantId, issueId],
  );
  if (!issue[0]) throw new HttpError(404, 'Reconciliation issue not found', 'RECONCILIATION_ISSUE_NOT_FOUND');

  const events = await query<Record<string, unknown>>(
    `
    select e.id, e.issue_id as "issueId", e.from_status as "fromStatus",
           e.to_status as "toStatus", e.event_type as "eventType",
           e.actor_user_id as "actorUserId", actor.name as "actorName",
           e.note, e.details, e.created_at as "createdAt"
    from public.commerce_reconciliation_issue_events e
    left join public.platform_users actor on actor.id = e.actor_user_id
    where e.tenant_id = $1 and e.issue_id = $2
    order by e.created_at asc
    limit $3
    `,
    [auth.tenantId, issueId, limit],
  );
  return { items: events.map(issueEventPayload) };
}
