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

type VoucherSourceType = 'reconciliation_issue' | 'reconciliation_item' | 'order' | 'payment' | 'refund' | 'manual';
type AdjustmentType =
  | 'manual_payment_confirm'
  | 'refund_correction'
  | 'provider_confirmed'
  | 'local_corrected'
  | 'write_off'
  | 'duplicate'
  | 'other';
type AdjustmentDirection = 'increase' | 'decrease' | 'none';
type VoucherStatus = 'draft' | 'submitted' | 'approved' | 'rejected' | 'voided';

const SOURCE_TYPES = new Set<VoucherSourceType>([
  'reconciliation_issue',
  'reconciliation_item',
  'order',
  'payment',
  'refund',
  'manual',
]);
const ADJUSTMENT_TYPES = new Set<AdjustmentType>([
  'manual_payment_confirm',
  'refund_correction',
  'provider_confirmed',
  'local_corrected',
  'write_off',
  'duplicate',
  'other',
]);
const DIRECTIONS = new Set<AdjustmentDirection>(['increase', 'decrease', 'none']);
const STATUSES = new Set<VoucherStatus>(['draft', 'submitted', 'approved', 'rejected', 'voided']);
const CREATE_STATUSES = new Set<VoucherStatus>(['draft', 'submitted']);
const CLOSED_STATUSES = new Set<VoucherStatus>(['approved', 'rejected', 'voided']);
const UUID_PATTERN = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

interface VoucherSource {
  sourceType: VoucherSourceType;
  sourceId: string | null;
  reconciliationIssueId: string | null;
  reconciliationItemId: string | null;
  orderId: string | null;
  paymentId: string | null;
  refundRequestId: string | null;
  orderNo: string | null;
  refundNo: string | null;
  provider: string | null;
  providerTradeNo: string | null;
  providerRefundNo: string | null;
  titleHint: string;
  amountCents: number;
}

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

function uuidValue(value: unknown, fieldName: string) {
  const text = nullableString(value);
  if (!text) return null;
  if (!UUID_PATTERN.test(text)) {
    throw new HttpError(400, `${fieldName} must be a valid UUID`, 'INVALID_UUID');
  }
  return text;
}

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

function intValue(value: unknown, fallback = 0) {
  const parsed = Number(value ?? fallback);
  return Number.isFinite(parsed) ? Math.trunc(parsed) : fallback;
}

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 dateParam(ctx: RequestContext, key: string, fallback: string) {
  const candidate = stringParam(ctx, key) || fallback;
  if (!/^\d{4}-\d{2}-\d{2}$/.test(candidate)) {
    throw new HttpError(400, `${key} must use YYYY-MM-DD format`, 'INVALID_DATE');
  }
  return candidate;
}

function shanghaiDateKey(date = new Date()) {
  const formatter = new Intl.DateTimeFormat('en-US', {
    timeZone: 'Asia/Shanghai',
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
  });
  const parts = Object.fromEntries(formatter.formatToParts(date).map(part => [part.type, part.value]));
  return `${parts.year}-${parts.month}-${parts.day}`;
}

function normalizeVoucherNo(value: unknown) {
  const supplied = nullableString(value);
  if (supplied) {
    const normalized = supplied.replace(/\s+/g, '').toUpperCase();
    if (normalized.length > 80) throw new HttpError(400, 'voucherNo is too long', 'ADJUSTMENT_VOUCHER_NO_INVALID');
    return normalized;
  }
  return `ADJ-${shanghaiDateKey().replace(/-/g, '')}-${crypto.randomInt(100000, 999999)}`;
}

function titleValue(value: unknown, fallback: string) {
  const title = nullableString(value) || fallback;
  return title.length > 120 ? title.slice(0, 120) : title;
}

function externalUrlValue(value: unknown) {
  const url = nullableString(value);
  if (!url) return null;
  if (url.length > 1000) throw new HttpError(400, 'externalUrl is too long', 'ADJUSTMENT_EXTERNAL_URL_INVALID');
  try {
    const parsed = new URL(url);
    if (!['https:', 'http:'].includes(parsed.protocol)) throw new Error('unsupported protocol');
    return parsed.toString();
  } catch {
    throw new HttpError(400, 'externalUrl must be a valid http(s) URL', 'ADJUSTMENT_EXTERNAL_URL_INVALID');
  }
}

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 authorizeReview(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:reconciliation:review');
  return auth;
}

async function assertTenantAsset(client: pg.PoolClient, tenantId: string, assetId: string | null) {
  const safeAssetId = uuidValue(assetId, 'assetId');
  if (!safeAssetId) return null;
  const result = await client.query<{ id: string }>(
    `
    select id
    from public.content_assets
    where tenant_id = $1 and id = $2
    limit 1
    `,
    [tenantId, safeAssetId],
  );
  if (!result.rows[0]) {
    throw new HttpError(400, 'Adjustment proof asset does not belong to this tenant', 'ADJUSTMENT_ASSET_INVALID');
  }
  return safeAssetId;
}

function requestedSourceType(body: Record<string, unknown>) {
  if (nullableString(body.sourceType)) return optionalChoice(body.sourceType, SOURCE_TYPES, 'manual', 'ADJUSTMENT_SOURCE_TYPE_INVALID');
  if (nullableString(body.reconciliationIssueId)) return 'reconciliation_issue';
  if (nullableString(body.reconciliationItemId)) return 'reconciliation_item';
  if (nullableString(body.refundRequestId) || nullableString(body.refundNo)) return 'refund';
  if (nullableString(body.paymentId)) return 'payment';
  if (nullableString(body.orderId) || nullableString(body.orderNo)) return 'order';
  return 'manual';
}

async function resolveVoucherSource(client: pg.PoolClient, auth: TenantAdminAuth, body: Record<string, unknown>): Promise<VoucherSource> {
  const sourceType = requestedSourceType(body);
  const sourceId = uuidValue(body.sourceId, 'sourceId');

  if (sourceType === 'reconciliation_issue') {
    const id = uuidValue(body.reconciliationIssueId, 'reconciliationIssueId') || sourceId;
    if (!id) throw new HttpError(400, 'reconciliationIssueId is required', 'ADJUSTMENT_SOURCE_ID_REQUIRED');
    const result = await client.query<Record<string, unknown>>(
      `
      select id, item_id as "itemId", order_id as "orderId", payment_id as "paymentId",
             refund_request_id as "refundRequestId", order_no as "orderNo", refund_no as "refundNo",
             provider, provider_trade_no as "providerTradeNo", provider_refund_no as "providerRefundNo",
             amount_cents as "amountCents", refund_amount_cents as "refundAmountCents",
             issue_no as "issueNo", match_status as "matchStatus"
      from public.commerce_reconciliation_issues
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, id],
    );
    const row = result.rows[0];
    if (!row) throw new HttpError(404, 'Reconciliation issue not found', 'RECONCILIATION_ISSUE_NOT_FOUND');
    return {
      sourceType,
      sourceId: String(row.id),
      reconciliationIssueId: String(row.id),
      reconciliationItemId: nullableString(row.itemId),
      orderId: nullableString(row.orderId),
      paymentId: nullableString(row.paymentId),
      refundRequestId: nullableString(row.refundRequestId),
      orderNo: nullableString(row.orderNo),
      refundNo: nullableString(row.refundNo),
      provider: nullableString(row.provider),
      providerTradeNo: nullableString(row.providerTradeNo),
      providerRefundNo: nullableString(row.providerRefundNo),
      titleHint: `差错工单 ${row.issueNo || row.matchStatus || row.id}`,
      amountCents: intValue(row.refundAmountCents) || intValue(row.amountCents),
    };
  }

  if (sourceType === 'reconciliation_item') {
    const id = uuidValue(body.reconciliationItemId, 'reconciliationItemId') || sourceId;
    if (!id) throw new HttpError(400, 'reconciliationItemId is required', 'ADJUSTMENT_SOURCE_ID_REQUIRED');
    const result = await client.query<Record<string, unknown>>(
      `
      select id, order_id as "orderId", payment_id as "paymentId",
             refund_request_id as "refundRequestId", order_no as "orderNo", refund_no as "refundNo",
             provider, provider_trade_no as "providerTradeNo", provider_refund_no as "providerRefundNo",
             amount_cents as "amountCents", refund_amount_cents as "refundAmountCents",
             match_status as "matchStatus", issue_code as "issueCode"
      from public.commerce_reconciliation_items
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, id],
    );
    const row = result.rows[0];
    if (!row) throw new HttpError(404, 'Reconciliation item not found', 'RECONCILIATION_ITEM_NOT_FOUND');
    return {
      sourceType,
      sourceId: String(row.id),
      reconciliationIssueId: null,
      reconciliationItemId: String(row.id),
      orderId: nullableString(row.orderId),
      paymentId: nullableString(row.paymentId),
      refundRequestId: nullableString(row.refundRequestId),
      orderNo: nullableString(row.orderNo),
      refundNo: nullableString(row.refundNo),
      provider: nullableString(row.provider),
      providerTradeNo: nullableString(row.providerTradeNo),
      providerRefundNo: nullableString(row.providerRefundNo),
      titleHint: `对账明细 ${row.issueCode || row.matchStatus || row.id}`,
      amountCents: intValue(row.refundAmountCents) || intValue(row.amountCents),
    };
  }

  if (sourceType === 'order') {
    const orderNo = nullableString(body.orderNo);
    const id = uuidValue(body.orderId, 'orderId') || sourceId;
    if (!id && !orderNo) throw new HttpError(400, 'orderId or orderNo is required', 'ADJUSTMENT_SOURCE_ID_REQUIRED');
    const result = await client.query<Record<string, unknown>>(
      `
      select o.id, o.order_no as "orderNo", o.pay_provider as "provider",
             o.trade_no as "providerTradeNo", o.amount_cents as "amountCents",
             p.id as "paymentId", p.provider_trade_no as "paymentProviderTradeNo"
      from public.orders o
      left join public.payments p on p.tenant_id = o.tenant_id and p.order_id = o.id
      where o.tenant_id = $1 and (($2::uuid is not null and o.id = $2::uuid) or ($3::text is not null and o.order_no = $3))
      order by p.created_at desc nulls last
      limit 1
      `,
      [auth.tenantId, id, orderNo],
    );
    const row = result.rows[0];
    if (!row) throw new HttpError(404, 'Order not found', 'ORDER_NOT_FOUND');
    return {
      sourceType,
      sourceId: String(row.id),
      reconciliationIssueId: null,
      reconciliationItemId: null,
      orderId: String(row.id),
      paymentId: nullableString(row.paymentId),
      refundRequestId: null,
      orderNo: nullableString(row.orderNo),
      refundNo: null,
      provider: nullableString(row.provider),
      providerTradeNo: nullableString(row.paymentProviderTradeNo) || nullableString(row.providerTradeNo),
      providerRefundNo: null,
      titleHint: `订单 ${row.orderNo || row.id}`,
      amountCents: intValue(row.amountCents),
    };
  }

  if (sourceType === 'payment') {
    const id = uuidValue(body.paymentId, 'paymentId') || sourceId;
    if (!id) throw new HttpError(400, 'paymentId is required', 'ADJUSTMENT_SOURCE_ID_REQUIRED');
    const result = await client.query<Record<string, unknown>>(
      `
      select p.id, p.order_id as "orderId", o.order_no as "orderNo", p.provider,
             p.provider_trade_no as "providerTradeNo", p.amount_cents as "amountCents"
      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.id = $2
      limit 1
      `,
      [auth.tenantId, id],
    );
    const row = result.rows[0];
    if (!row) throw new HttpError(404, 'Payment not found', 'PAYMENT_NOT_FOUND');
    return {
      sourceType,
      sourceId: String(row.id),
      reconciliationIssueId: null,
      reconciliationItemId: null,
      orderId: nullableString(row.orderId),
      paymentId: String(row.id),
      refundRequestId: null,
      orderNo: nullableString(row.orderNo),
      refundNo: null,
      provider: nullableString(row.provider),
      providerTradeNo: nullableString(row.providerTradeNo),
      providerRefundNo: null,
      titleHint: `支付 ${row.orderNo || row.providerTradeNo || row.id}`,
      amountCents: intValue(row.amountCents),
    };
  }

  if (sourceType === 'refund') {
    const refundNo = nullableString(body.refundNo);
    const id = uuidValue(body.refundRequestId, 'refundRequestId') || sourceId;
    if (!id && !refundNo) throw new HttpError(400, 'refundRequestId or refundNo is required', 'ADJUSTMENT_SOURCE_ID_REQUIRED');
    const result = await client.query<Record<string, unknown>>(
      `
      select rr.id, rr.order_id as "orderId", rr.payment_id as "paymentId",
             o.order_no as "orderNo", rr.refund_no as "refundNo", rr.provider,
             rr.provider_refund_no as "providerRefundNo", p.provider_trade_no as "providerTradeNo",
             rr.amount_cents as "amountCents"
      from public.commerce_refund_requests rr
      join public.orders o on o.tenant_id = rr.tenant_id and o.id = rr.order_id
      left join public.payments p on p.tenant_id = rr.tenant_id and p.id = rr.payment_id
      where rr.tenant_id = $1 and (($2::uuid is not null and rr.id = $2::uuid) or ($3::text is not null and rr.refund_no = $3))
      limit 1
      `,
      [auth.tenantId, id, refundNo],
    );
    const row = result.rows[0];
    if (!row) throw new HttpError(404, 'Refund request not found', 'REFUND_NOT_FOUND');
    return {
      sourceType,
      sourceId: String(row.id),
      reconciliationIssueId: null,
      reconciliationItemId: null,
      orderId: nullableString(row.orderId),
      paymentId: nullableString(row.paymentId),
      refundRequestId: String(row.id),
      orderNo: nullableString(row.orderNo),
      refundNo: nullableString(row.refundNo),
      provider: nullableString(row.provider),
      providerTradeNo: nullableString(row.providerTradeNo),
      providerRefundNo: nullableString(row.providerRefundNo),
      titleHint: `退款 ${row.refundNo || row.orderNo || row.id}`,
      amountCents: intValue(row.amountCents),
    };
  }

  return {
    sourceType: 'manual',
    sourceId: null,
    reconciliationIssueId: null,
    reconciliationItemId: null,
    orderId: null,
    paymentId: null,
    refundRequestId: null,
    orderNo: nullableString(body.orderNo),
    refundNo: nullableString(body.refundNo),
    provider: nullableString(body.provider),
    providerTradeNo: nullableString(body.providerTradeNo),
    providerRefundNo: nullableString(body.providerRefundNo),
    titleHint: '人工调整凭证',
    amountCents: intValue(body.amountCents),
  };
}

function voucherPayload(row: Record<string, unknown>) {
  return {
    id: row.id,
    voucherNo: row.voucherNo,
    sourceType: row.sourceType,
    sourceId: row.sourceId,
    reconciliationIssueId: row.reconciliationIssueId,
    reconciliationItemId: row.reconciliationItemId,
    orderId: row.orderId,
    paymentId: row.paymentId,
    refundRequestId: row.refundRequestId,
    orderNo: row.orderNo,
    refundNo: row.refundNo,
    provider: row.provider,
    providerTradeNo: row.providerTradeNo,
    providerRefundNo: row.providerRefundNo,
    adjustmentType: row.adjustmentType,
    direction: row.direction,
    amountCents: row.amountCents,
    status: row.status,
    title: row.title,
    description: row.description,
    assetId: row.assetId,
    externalUrl: row.externalUrl,
    submittedBy: row.submittedBy,
    submittedByName: row.submittedByName,
    submittedAt: row.submittedAt,
    reviewedBy: row.reviewedBy,
    reviewedByName: row.reviewedByName,
    reviewedAt: row.reviewedAt,
    reviewNote: row.reviewNote,
    voidedBy: row.voidedBy,
    voidedAt: row.voidedAt,
    metadata: row.metadata || {},
    createdBy: row.createdBy,
    createdByName: row.createdByName,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  };
}

function voucherSelectSql() {
  return `
    select v.id, v.voucher_no as "voucherNo", v.source_type as "sourceType", v.source_id as "sourceId",
           v.reconciliation_issue_id as "reconciliationIssueId",
           v.reconciliation_item_id as "reconciliationItemId",
           v.order_id as "orderId", v.payment_id as "paymentId",
           v.refund_request_id as "refundRequestId", v.order_no as "orderNo",
           v.refund_no as "refundNo", v.provider, v.provider_trade_no as "providerTradeNo",
           v.provider_refund_no as "providerRefundNo", v.adjustment_type as "adjustmentType",
           v.direction, v.amount_cents as "amountCents", v.status,
           v.title, v.description, v.asset_id as "assetId", v.external_url as "externalUrl",
           v.submitted_by as "submittedBy", submitter.name as "submittedByName",
           v.submitted_at as "submittedAt", v.reviewed_by as "reviewedBy",
           reviewer.name as "reviewedByName", v.reviewed_at as "reviewedAt",
           v.review_note as "reviewNote", v.voided_by as "voidedBy",
           v.voided_at as "voidedAt", v.metadata, v.created_by as "createdBy",
           creator.name as "createdByName", v.created_at as "createdAt",
           v.updated_at as "updatedAt"
    from public.commerce_adjustment_vouchers v
    left join public.platform_users submitter on submitter.id = v.submitted_by
    left join public.platform_users reviewer on reviewer.id = v.reviewed_by
    left join public.platform_users creator on creator.id = v.created_by
  `;
}

async function fetchVoucherForTenant(client: pg.PoolClient, tenantId: string, voucherId: string, lock = false) {
  const safeVoucherId = uuidValue(voucherId, 'voucherId');
  if (!safeVoucherId) return null;
  const result = await client.query<Record<string, unknown>>(
    `
    ${voucherSelectSql()}
    where v.tenant_id = $1 and v.id = $2
    limit 1
    ${lock ? 'for update of v' : ''}
    `,
    [tenantId, safeVoucherId],
  );
  return result.rows[0] || null;
}

async function recordVoucherEvent(
  client: pg.PoolClient,
  auth: TenantAdminAuth,
  input: {
    voucherId: string;
    fromStatus?: string | null;
    toStatus?: string | null;
    eventType: string;
    note?: string | null;
    details?: Record<string, unknown>;
  },
) {
  await client.query(
    `
    insert into public.commerce_adjustment_voucher_events (
      tenant_id, voucher_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.voucherId,
      input.fromStatus || null,
      input.toStatus || null,
      input.eventType,
      auth.userId,
      input.note || null,
      JSON.stringify(input.details || {}),
    ],
  );
}

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

function assertVoucherTransition(currentStatus: VoucherStatus, nextStatus: VoucherStatus) {
  if (nextStatus === 'draft') {
    throw new HttpError(400, 'Cannot move voucher back to draft through status API', 'ADJUSTMENT_STATUS_INVALID');
  }
  if (currentStatus === nextStatus) return;
  if (CLOSED_STATUSES.has(currentStatus)) {
    throw new HttpError(409, 'Closed adjustment voucher cannot change status', 'ADJUSTMENT_VOUCHER_CLOSED');
  }
  if (currentStatus === 'draft' && !['submitted', 'voided'].includes(nextStatus)) {
    throw new HttpError(409, 'Draft voucher must be submitted before review', 'ADJUSTMENT_STATUS_CONFLICT');
  }
  if (currentStatus === 'submitted' && !['approved', 'rejected', 'voided'].includes(nextStatus)) {
    throw new HttpError(409, 'Submitted voucher can only be approved, rejected, or voided', 'ADJUSTMENT_STATUS_CONFLICT');
  }
}

export async function adjustmentVouchersRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const status = stringParam(ctx, 'status');
  const sourceType = stringParam(ctx, 'sourceType');
  const orderNo = stringParam(ctx, 'orderNo');
  const voucherNo = stringParam(ctx, 'voucherNo');
  const issueId = uuidValue(stringParam(ctx, 'reconciliationIssueId') || stringParam(ctx, 'issueId'), 'reconciliationIssueId');

  const params: unknown[] = [auth.tenantId, limit];
  const where = ['v.tenant_id = $1'];
  if (status) {
    if (!STATUSES.has(status as VoucherStatus)) throw new HttpError(400, 'status is invalid', 'ADJUSTMENT_STATUS_INVALID');
    params.push(status);
    where.push(`v.status = $${params.length}`);
  }
  if (sourceType) {
    if (!SOURCE_TYPES.has(sourceType as VoucherSourceType)) throw new HttpError(400, 'sourceType is invalid', 'ADJUSTMENT_SOURCE_TYPE_INVALID');
    params.push(sourceType);
    where.push(`v.source_type = $${params.length}`);
  }
  if (orderNo) {
    params.push(orderNo);
    where.push(`v.order_no = $${params.length}`);
  }
  if (voucherNo) {
    params.push(voucherNo);
    where.push(`v.voucher_no = $${params.length}`);
  }
  if (issueId) {
    params.push(issueId);
    where.push(`v.reconciliation_issue_id = $${params.length}::uuid`);
  }

  const items = await query<Record<string, unknown>>(
    `
    ${voucherSelectSql()}
    where ${where.join(' and ')}
    order by v.created_at desc
    limit $2
    `,
    params,
  );
  return { items: items.map(voucherPayload) };
}

export async function createAdjustmentVoucherRoute(ctx: RequestContext) {
  const auth = await authorizeWrite(ctx);
  const body = await readJsonBody(ctx);
  const status = optionalChoice(body.status, CREATE_STATUSES, 'submitted', 'ADJUSTMENT_STATUS_INVALID');
  const adjustmentType = optionalChoice(body.adjustmentType, ADJUSTMENT_TYPES, 'other', 'ADJUSTMENT_TYPE_INVALID');
  const direction = optionalChoice(body.direction, DIRECTIONS, 'none', 'ADJUSTMENT_DIRECTION_INVALID');
  const amountCents = Math.max(0, intValue(body.amountCents));
  const voucherNo = normalizeVoucherNo(body.voucherNo);
  const externalUrl = externalUrlValue(body.externalUrl);
  const note = nullableString(body.note);

  const item = await transaction(async client => {
    const source = await resolveVoucherSource(client, auth, body);
    const assetId = await assertTenantAsset(client, auth.tenantId, nullableString(body.assetId));
    const effectiveAmountCents = amountCents || source.amountCents;
    const result = await client.query<Record<string, unknown>>(
      `
      insert into public.commerce_adjustment_vouchers (
        tenant_id, voucher_no, source_type, source_id,
        reconciliation_issue_id, reconciliation_item_id, order_id, payment_id, refund_request_id,
        order_no, refund_no, provider, provider_trade_no, provider_refund_no,
        adjustment_type, direction, amount_cents, status, title, description,
        asset_id, external_url, submitted_by, submitted_at, metadata, created_by
      )
      values (
        $1, $2, $3, $4::uuid,
        $5::uuid, $6::uuid, $7::uuid, $8::uuid, $9::uuid,
        $10, $11, $12, $13, $14,
        $15, $16, $17, $18, $19, $20,
        $21::uuid, $22, case when $18 = 'submitted' then $23::uuid else null end,
        case when $18 = 'submitted' then now() else null end,
        $24::jsonb, $23::uuid
      )
      on conflict (tenant_id, voucher_no) do nothing
      returning id
      `,
      [
        auth.tenantId,
        voucherNo,
        source.sourceType,
        source.sourceId,
        source.reconciliationIssueId,
        source.reconciliationItemId,
        source.orderId,
        source.paymentId,
        source.refundRequestId,
        source.orderNo,
        source.refundNo,
        source.provider,
        source.providerTradeNo,
        source.providerRefundNo,
        adjustmentType,
        direction,
        effectiveAmountCents,
        status,
        titleValue(body.title, source.titleHint),
        nullableString(body.description),
        assetId,
        externalUrl,
        auth.userId,
        JSON.stringify(objectValue(body.metadata)),
      ],
    );
    if (!result.rows[0]) {
      throw new HttpError(409, 'Adjustment voucher number already exists', 'ADJUSTMENT_VOUCHER_DUPLICATE');
    }
    const voucherId = String(result.rows[0].id);
    await recordVoucherEvent(client, auth, {
      voucherId,
      toStatus: status,
      eventType: status === 'submitted' ? 'submitted' : 'created',
      note,
      details: {
        sourceType: source.sourceType,
        sourceId: source.sourceId,
        adjustmentType,
        direction,
        amountCents: effectiveAmountCents,
        hasAsset: Boolean(assetId),
        hasExternalUrl: Boolean(externalUrl),
      },
    });
    await recordAudit(client, auth, 'commerce.adjustment_voucher.created', voucherId, {
      voucherNo,
      sourceType: source.sourceType,
      sourceId: source.sourceId,
      adjustmentType,
      direction,
      amountCents: effectiveAmountCents,
      status,
    });
    const row = await fetchVoucherForTenant(client, auth.tenantId, voucherId);
    return row || result.rows[0];
  });

  return { item: voucherPayload(item) };
}

export async function updateAdjustmentVoucherStatusRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const nextStatus = optionalChoice(body.status, STATUSES, 'approved', 'ADJUSTMENT_STATUS_INVALID');
  const auth = nextStatus === 'submitted' ? await authorizeWrite(ctx) : await authorizeReview(ctx);
  const voucherId = uuidValue(body.voucherId, 'voucherId');
  if (!voucherId) throw new HttpError(400, 'voucherId is required', 'ADJUSTMENT_VOUCHER_ID_REQUIRED');
  const reviewNote = nullableString(body.reviewNote) || nullableString(body.note);

  const result = await transaction(async client => {
    const current = await fetchVoucherForTenant(client, auth.tenantId, voucherId, true);
    if (!current) throw new HttpError(404, 'Adjustment voucher not found', 'ADJUSTMENT_VOUCHER_NOT_FOUND');
    const currentStatus = String(current.status) as VoucherStatus;
    assertVoucherTransition(currentStatus, nextStatus);

    if (currentStatus === nextStatus) {
      return { item: current, idempotent: true };
    }

    const update = await client.query<Record<string, unknown>>(
      `
      update public.commerce_adjustment_vouchers
      set status = $3,
          submitted_by = case when $3 = 'submitted' and submitted_by is null then $4::uuid else submitted_by end,
          submitted_at = case when $3 = 'submitted' and submitted_at is null then now() else submitted_at end,
          reviewed_by = case when $3 in ('approved', 'rejected') then $4::uuid else reviewed_by end,
          reviewed_at = case when $3 in ('approved', 'rejected') then now() else reviewed_at end,
          review_note = case when $3 in ('approved', 'rejected') then coalesce($5, review_note) else review_note end,
          voided_by = case when $3 = 'voided' then $4::uuid else voided_by end,
          voided_at = case when $3 = 'voided' then now() else voided_at end,
          metadata = metadata || $6::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id
      `,
      [
        auth.tenantId,
        voucherId,
        nextStatus,
        auth.userId,
        reviewNote,
        JSON.stringify(objectValue(body.metadata)),
      ],
    );
    if (!update.rows[0]) throw new HttpError(404, 'Adjustment voucher not found', 'ADJUSTMENT_VOUCHER_NOT_FOUND');

    await recordVoucherEvent(client, auth, {
      voucherId,
      fromStatus: currentStatus,
      toStatus: nextStatus,
      eventType: nextStatus,
      note: reviewNote,
      details: {
        previousStatus: currentStatus,
        nextStatus,
        businessStateMutated: false,
      },
    });
    await recordAudit(client, auth, 'commerce.adjustment_voucher.status_updated', voucherId, {
      fromStatus: currentStatus,
      toStatus: nextStatus,
      businessStateMutated: false,
    });

    const row = await fetchVoucherForTenant(client, auth.tenantId, voucherId);
    return { item: row || current, idempotent: false };
  });

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

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

  const voucher = await query<Record<string, unknown>>(
    'select id from public.commerce_adjustment_vouchers where tenant_id = $1 and id = $2 limit 1',
    [auth.tenantId, voucherId],
  );
  if (!voucher[0]) throw new HttpError(404, 'Adjustment voucher not found', 'ADJUSTMENT_VOUCHER_NOT_FOUND');

  const items = await query<Record<string, unknown>>(
    `
    select e.id, e.voucher_id as "voucherId", 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_adjustment_voucher_events e
    left join public.platform_users actor on actor.id = e.actor_user_id
    where e.tenant_id = $1 and e.voucher_id = $2
    order by e.created_at asc
    limit $3
    `,
    [auth.tenantId, voucherId, limit],
  );
  return { items };
}

export async function adjustmentVoucherReportRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const today = shanghaiDateKey();
  const startDate = dateParam(ctx, 'startDate', today.slice(0, 8) + '01');
  const endDate = dateParam(ctx, 'endDate', today);
  if (startDate > endDate) throw new HttpError(400, 'startDate must be before or equal to endDate', 'INVALID_DATE_RANGE');

  const [statusRows, typeRows, sourceRows, dailyRows] = await Promise.all([
    query<Record<string, unknown>>(
      `
      select status, count(*)::int as count, coalesce(sum(amount_cents), 0)::int as "amountCents"
      from public.commerce_adjustment_vouchers
      where tenant_id = $1
        and created_at >= ($2::date::timestamp at time zone 'Asia/Shanghai')
        and created_at < (($3::date + interval '1 day')::timestamp at time zone 'Asia/Shanghai')
      group by status
      order by status
      `,
      [auth.tenantId, startDate, endDate],
    ),
    query<Record<string, unknown>>(
      `
      select adjustment_type as "adjustmentType", direction,
             count(*)::int as count, coalesce(sum(amount_cents), 0)::int as "amountCents"
      from public.commerce_adjustment_vouchers
      where tenant_id = $1
        and created_at >= ($2::date::timestamp at time zone 'Asia/Shanghai')
        and created_at < (($3::date + interval '1 day')::timestamp at time zone 'Asia/Shanghai')
      group by adjustment_type, direction
      order by adjustment_type, direction
      `,
      [auth.tenantId, startDate, endDate],
    ),
    query<Record<string, unknown>>(
      `
      select source_type as "sourceType", count(*)::int as count,
             coalesce(sum(amount_cents), 0)::int as "amountCents"
      from public.commerce_adjustment_vouchers
      where tenant_id = $1
        and created_at >= ($2::date::timestamp at time zone 'Asia/Shanghai')
        and created_at < (($3::date + interval '1 day')::timestamp at time zone 'Asia/Shanghai')
      group by source_type
      order by source_type
      `,
      [auth.tenantId, startDate, endDate],
    ),
    query<Record<string, unknown>>(
      `
      select (created_at at time zone 'Asia/Shanghai')::date::text as date, status, count(*)::int as count,
             coalesce(sum(amount_cents), 0)::int as "amountCents"
      from public.commerce_adjustment_vouchers
      where tenant_id = $1
        and created_at >= ($2::date::timestamp at time zone 'Asia/Shanghai')
        and created_at < (($3::date + interval '1 day')::timestamp at time zone 'Asia/Shanghai')
      group by (created_at at time zone 'Asia/Shanghai')::date, status
      order by date asc, status
      `,
      [auth.tenantId, startDate, endDate],
    ),
  ]);

  const totalCount = statusRows.reduce((sum, row) => sum + intValue(row.count), 0);
  const totalAmountCents = statusRows.reduce((sum, row) => sum + intValue(row.amountCents), 0);
  return {
    item: {
      startDate,
      endDate,
      totalCount,
      totalAmountCents,
      pendingReviewCount: statusRows
        .filter(row => ['draft', 'submitted'].includes(String(row.status)))
        .reduce((sum, row) => sum + intValue(row.count), 0),
      byStatus: statusRows,
      byType: typeRows,
      bySource: sourceRows,
      daily: dailyRows,
    },
  };
}

function operationSeverity(severity: unknown, fallback: 'info' | 'warning' | 'error' | 'critical' = 'warning') {
  const text = String(severity || fallback);
  return ['info', 'warning', 'error', 'critical'].includes(text) ? text : fallback;
}

export async function commerceOperationsAnomaliesRoute(ctx: RequestContext) {
  const auth = await authorizeRead(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const provider = stringParam(ctx, 'provider');
  const params: unknown[] = [auth.tenantId, Math.max(limit, 20)];
  const providerWhere = provider ? 'and provider = $3' : '';
  if (provider) params.push(provider);

  const [issues, billJobs, paymentEvents, pendingPayments, pendingRefunds] = await Promise.all([
    query<Record<string, unknown>>(
      `
      select id, issue_no as "issueNo", status, severity, provider,
             order_no as "orderNo", refund_no as "refundNo", amount_cents as "amountCents",
             refund_amount_cents as "refundAmountCents", summary, created_at as "createdAt",
             updated_at as "updatedAt"
      from public.commerce_reconciliation_issues
      where tenant_id = $1
        and status in ('open', 'investigating', 'escalated')
        ${providerWhere}
      order by case severity when 'critical' then 1 when 'error' then 2 when 'warning' then 3 else 4 end,
               created_at desc
      limit $2
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select id, provider, bill_date as "billDate", bill_type as "billType", status,
             error_code as "errorCode", error_message as "errorMessage",
             created_at as "createdAt", updated_at as "updatedAt"
      from public.commerce_bill_download_jobs
      where tenant_id = $1
        and (status = 'failed' or (status = 'running' and claimed_at < now() - interval '30 minutes'))
        ${providerWhere}
      order by updated_at desc
      limit $2
      `,
      params,
    ),
    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 tenant_id = $1 and error is not null ${providerWhere}
      order by created_at desc
      limit $2
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select p.id, p.order_id as "orderId", o.order_no as "orderNo",
             p.provider, p.status, p.amount_cents as "amountCents",
             p.provider_trade_no as "providerTradeNo",
             p.created_at as "createdAt", p.updated_at as "updatedAt"
      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.status = 'pending'
        and p.created_at < now() - interval '30 minutes'
        ${provider ? 'and p.provider = $3' : ''}
      order by p.created_at asc
      limit $2
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select rr.id, rr.refund_no as "refundNo", o.order_no as "orderNo",
             coalesce(rr.provider, o.pay_provider) as provider, rr.status,
             rr.amount_cents as "amountCents", rr.failure_reason as "failureReason",
             rr.created_at as "createdAt", rr.updated_at as "updatedAt"
      from public.commerce_refund_requests rr
      join public.orders o on o.tenant_id = rr.tenant_id and o.id = rr.order_id
      where rr.tenant_id = $1
        and rr.status in ('requested', 'approved', 'processing')
        and rr.created_at < now() - interval '1 day'
        ${provider ? 'and coalesce(rr.provider, o.pay_provider) = $3' : ''}
      order by rr.created_at asc
      limit $2
      `,
      params,
    ),
  ]);

  const items = [
    ...issues.map(item => ({
      type: 'reconciliation_issue',
      id: item.id,
      severity: operationSeverity(item.severity),
      status: item.status,
      title: item.summary || item.issueNo || '待处理对账差错',
      provider: item.provider,
      orderNo: item.orderNo,
      refundNo: item.refundNo,
      amountCents: intValue(item.refundAmountCents) || intValue(item.amountCents),
      createdAt: item.createdAt,
      updatedAt: item.updatedAt,
      source: item,
    })),
    ...billJobs.map(item => ({
      type: 'provider_bill_job',
      id: item.id,
      severity: item.status === 'failed' ? 'error' : 'warning',
      status: item.status,
      title: `${item.provider} ${item.billDate} 官方账单任务异常`,
      provider: item.provider,
      orderNo: null,
      refundNo: null,
      amountCents: 0,
      createdAt: item.createdAt,
      updatedAt: item.updatedAt,
      source: item,
    })),
    ...paymentEvents.map(item => ({
      type: 'payment_event_error',
      id: item.id,
      severity: 'error',
      status: 'error',
      title: `支付事件处理失败 ${item.eventType || item.eventId || item.id}`,
      provider: item.provider,
      orderNo: null,
      refundNo: null,
      amountCents: 0,
      createdAt: item.createdAt,
      updatedAt: item.processedAt || item.createdAt,
      source: item,
    })),
    ...pendingPayments.map(item => ({
      type: 'stuck_pending_payment',
      id: item.id,
      severity: 'warning',
      status: item.status,
      title: `支付长时间待确认 ${item.orderNo || item.id}`,
      provider: item.provider,
      orderNo: item.orderNo,
      refundNo: null,
      amountCents: intValue(item.amountCents),
      createdAt: item.createdAt,
      updatedAt: item.updatedAt,
      source: item,
    })),
    ...pendingRefunds.map(item => ({
      type: 'stuck_refund',
      id: item.id,
      severity: item.status === 'processing' ? 'warning' : 'info',
      status: item.status,
      title: `退款长时间待处理 ${item.refundNo || item.orderNo || item.id}`,
      provider: item.provider,
      orderNo: item.orderNo,
      refundNo: item.refundNo,
      amountCents: intValue(item.amountCents),
      createdAt: item.createdAt,
      updatedAt: item.updatedAt,
      source: item,
    })),
  ]
    .sort((a, b) => String(b.updatedAt || b.createdAt).localeCompare(String(a.updatedAt || a.createdAt)))
    .slice(0, limit);

  const byType = items.reduce<Record<string, number>>((acc, item) => {
    acc[item.type] = (acc[item.type] || 0) + 1;
    return acc;
  }, {});
  const bySeverity = items.reduce<Record<string, number>>((acc, item) => {
    acc[item.severity] = (acc[item.severity] || 0) + 1;
    return acc;
  }, {});

  return {
    summary: {
      total: items.length,
      byType,
      bySeverity,
    },
    items,
  };
}
