import crypto from 'node:crypto';
import type pg from 'pg';
import { currentSessionFromContext } from '../../core/auth-context.js';
import { getHeader, HttpError, type RequestContext } from '../../core/http.js';
import {
  intParam,
  optionalStringArray,
  optionalString,
  readJsonBody,
  requiredString,
  requirePlatformAdmin,
} from '../../core/request.js';
import { pool, query, queryOne, transaction } from '../../core/db.js';
import {
  centsFrom,
  createInvoiceNo,
  createInvoiceRecordWithClient,
  type CreateInvoiceInput,
  normalizeHost,
  normalizeInvoiceItems,
  normalizeSlug,
  processOverduePlatformInvoices,
  processUsageOverageInvoices,
  quantityFrom,
  recalculateInvoiceTotals,
  usageOverageCandidateQuery as loadUsageOverageInvoiceCandidates,
} from './service.js';

function jsonBodyValue(value: unknown) {
  return JSON.stringify(value && typeof value === 'object' && !Array.isArray(value) ? value : {});
}

function listQuery(ctx: RequestContext, name: string) {
  return ctx.url.searchParams.get(name)?.trim() || '';
}

function toDateText(value: unknown) {
  if (!value) return null;
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  return String(value).slice(0, 10);
}

function optionalUuidArray(body: Record<string, unknown>, key: string) {
  return optionalStringArray(body, key).filter(Boolean);
}

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

function booleanValue(value: unknown, fallback = false) {
  return typeof value === 'boolean' ? value : fallback;
}

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

const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
const TENANT_INVOICE_STATUSES = new Set(['draft', 'issued', 'paid', 'void', 'overdue']);
const PLATFORM_AUDIT_ALERT_STATUSES = new Set(['open', 'acknowledged', 'resolved', 'ignored']);
const PLATFORM_AUDIT_NOTIFICATION_EVENT_STATUSES = new Set(['pending', 'processing', 'sent', 'retrying', 'failed', 'discarded']);
const PLATFORM_AUDIT_NOTIFICATION_PROVIDERS = new Set(['generic', 'dingtalk', 'feishu', 'wecom']);
const PLATFORM_AUDIT_SEVERITIES = new Set(['low', 'medium', 'high', 'critical']);
const PLATFORM_DUNNING_REMINDER_TYPES = new Set(['due_soon', 'overdue', 'final_notice', 'manual']);
const PLATFORM_DUNNING_REMINDER_CHANNELS = new Set(['manual', 'internal', 'sms', 'email', 'wechat', 'crm']);
const PLATFORM_STAFF_STATUSES = new Set(['active', 'disabled']);

const PLATFORM_PERMISSION_CATALOG = [
  { key: 'platform:overview:read', group: 'overview', label: '平台概览' },
  { key: 'platform:staff:read', group: 'staff', label: '查看平台员工' },
  { key: 'platform:staff:write', group: 'staff', label: '创建/编辑平台员工' },
  { key: 'platform:staff:status', group: 'staff', label: '启停平台员工' },
  { key: 'platform:tenant:read', group: 'tenant', label: '查看租户' },
  { key: 'platform:tenant:write', group: 'tenant', label: '创建/编辑租户' },
  { key: 'platform:tenant:status', group: 'tenant', label: '变更租户状态' },
  { key: 'platform:tenant:billing_profile', group: 'tenant', label: '维护租户账务资料' },
  { key: 'platform:plan:read', group: 'billing', label: '查看 SaaS 套餐' },
  { key: 'platform:billing:read', group: 'billing', label: '查看平台账务' },
  { key: 'platform:billing:write', group: 'billing', label: '创建订阅/账单' },
  { key: 'platform:billing:payment', group: 'billing', label: '确认服务费收款' },
  { key: 'platform:billing:dunning', group: 'billing', label: '处理逾期催缴' },
  { key: 'platform:billing:notification', group: 'billing', label: '维护催缴通知' },
  { key: 'platform:usage:read', group: 'usage', label: '查看租户用量' },
  { key: 'platform:usage:write', group: 'usage', label: '记录租户用量' },
  { key: 'platform:audit:read', group: 'audit', label: '查看平台审计' },
  { key: 'platform:audit:export', group: 'audit', label: '导出平台审计' },
  { key: 'platform:audit:alert', group: 'audit', label: '处理平台审计告警' },
  { key: 'platform:audit:notification', group: 'audit', label: '维护审计告警通知' },
  { key: 'platform:question_bank:read', group: 'question_bank', label: '查看公共题库' },
  { key: 'platform:question_bank:grant', group: 'question_bank', label: '授权公共题库' },
  { key: 'platform:question_bank:ops', group: 'question_bank', label: '查看公共题库同步运营' },
] as const;

function csvEscape(value: unknown) {
  if (value === null || value === undefined) return '';
  const text = typeof value === 'object' ? JSON.stringify(value) : String(value);
  if (!/[",\r\n]/.test(text)) return text;
  return `"${text.replace(/"/g, '""')}"`;
}

function csvLine(values: unknown[]) {
  return values.map(csvEscape).join(',');
}

function redactAuditExportValue(value: unknown, parentKey = '', depth = 0): unknown {
  if (value === null || value === undefined) return value;
  if (depth > 8) return '[REDACTED_DEPTH_LIMIT]';
  if (
    /(?:password|passwd|secret|token|credential|private[_-]?key|api[_-]?key|app[_-]?secret|authorization|cookie|session|cert|signature|nonce)$/i
      .test(parentKey)
  ) {
    return '[REDACTED]';
  }
  if (Array.isArray(value)) return value.map(item => redactAuditExportValue(item, parentKey, depth + 1));
  if (typeof value === 'object') {
    const output: Record<string, unknown> = {};
    for (const [key, item] of Object.entries(value as Record<string, unknown>)) {
      output[key] = redactAuditExportValue(item, key, depth + 1);
    }
    return output;
  }
  return value;
}

const redactAuditAlertValue = redactAuditExportValue;

type PlatformStaffPublicRow = Record<string, unknown> & {
  rawProfile?: unknown;
};

function platformStaffPublicRow<T extends PlatformStaffPublicRow>(item: T): T {
  if (!Object.prototype.hasOwnProperty.call(item, 'rawProfile')) return item;
  return {
    ...item,
    rawProfile: redactAuditExportValue(item.rawProfile),
  };
}

function contentBase64AndHash(content: string) {
  const buffer = Buffer.from(content, 'utf8');
  return {
    contentBase64: buffer.toString('base64'),
    sha256: crypto.createHash('sha256').update(buffer).digest('hex'),
    sizeBytes: buffer.length,
  };
}

function platformExportTimestamp() {
  return new Date().toISOString().replace(/[-:]/g, '').replace(/\.\d{3}Z$/, 'Z');
}

function optionalDateFilter(ctx: RequestContext, name: string) {
  const value = listQuery(ctx, name);
  if (!value) return '';
  if (!/^\d{4}-\d{2}-\d{2}$/.test(value)) {
    throw new HttpError(400, `${name} must use YYYY-MM-DD format`, 'INVALID_DATE');
  }
  return value;
}

function auditExportFormat(value: string) {
  const format = value || 'csv';
  if (!['csv', 'json'].includes(format)) {
    throw new HttpError(400, 'format is invalid', 'INVALID_EXPORT_FORMAT');
  }
  return format;
}

function auditAlertStatusFrom(value: string) {
  const status = value || 'acknowledged';
  if (!PLATFORM_AUDIT_ALERT_STATUSES.has(status)) {
    throw new HttpError(400, 'alert status is invalid', 'INVALID_ALERT_STATUS');
  }
  if (status === 'open') {
    throw new HttpError(400, 'alert status cannot be reopened from this endpoint', 'INVALID_ALERT_STATUS');
  }
  return status;
}

function platformAuditNotificationProviderFrom(value: string) {
  const provider = value || 'generic';
  if (!PLATFORM_AUDIT_NOTIFICATION_PROVIDERS.has(provider)) {
    throw new HttpError(400, 'provider is invalid', 'INVALID_NOTIFICATION_PROVIDER');
  }
  return provider;
}

function platformAuditSeverityFrom(value: string, fallback = 'medium') {
  const severity = value || fallback;
  if (!PLATFORM_AUDIT_SEVERITIES.has(severity)) {
    throw new HttpError(400, 'severity is invalid', 'INVALID_ALERT_SEVERITY');
  }
  return severity;
}

function platformAuditNotificationStatusFilter(value: unknown) {
  const statuses = Array.isArray(value)
    ? value.map(item => String(item).trim()).filter(Boolean)
    : ['open'];
  if (statuses.length === 0 || statuses.length > 4) {
    throw new HttpError(400, 'statusFilter is invalid', 'INVALID_STATUS_FILTER');
  }
  for (const status of statuses) {
    if (!PLATFORM_AUDIT_ALERT_STATUSES.has(status)) {
      throw new HttpError(400, 'statusFilter contains invalid status', 'INVALID_STATUS_FILTER');
    }
  }
  return [...new Set(statuses)];
}

function platformAuditNotificationChannelCode(value: string) {
  const code = normalizeSlug(value).replace(/-/g, '_');
  if (!/^[a-z0-9_]{3,64}$/.test(code)) {
    throw new HttpError(400, 'channelCode is invalid', 'INVALID_CHANNEL_CODE');
  }
  return code;
}

function platformDunningReminderTypes(value: unknown) {
  const types = Array.isArray(value)
    ? value.map(item => String(item).trim()).filter(Boolean)
    : ['overdue', 'final_notice'];
  if (types.length === 0 || types.length > 4) {
    throw new HttpError(400, 'reminderTypes is invalid', 'INVALID_REMINDER_TYPES');
  }
  for (const type of types) {
    if (!PLATFORM_DUNNING_REMINDER_TYPES.has(type)) {
      throw new HttpError(400, 'reminderTypes contains invalid type', 'INVALID_REMINDER_TYPES');
    }
  }
  return [...new Set(types)];
}

function platformDunningReminderChannels(value: unknown) {
  const channels = Array.isArray(value)
    ? value.map(item => String(item).trim()).filter(Boolean)
    : ['internal'];
  if (channels.length === 0 || channels.length > 6) {
    throw new HttpError(400, 'reminderChannels is invalid', 'INVALID_REMINDER_CHANNELS');
  }
  for (const channel of channels) {
    if (!PLATFORM_DUNNING_REMINDER_CHANNELS.has(channel)) {
      throw new HttpError(400, 'reminderChannels contains invalid channel', 'INVALID_REMINDER_CHANNELS');
    }
  }
  return [...new Set(channels)];
}

function platformSecretRef(scope: string, key: string) {
  return `app_private.platform_secrets:${scope}:${key}`;
}

function parsePlatformSecretRef(ref: string | null) {
  if (!ref) return null;
  const parts = ref.split(':');
  if (parts.length !== 3 || parts[0] !== 'app_private.platform_secrets') return null;
  return { scope: parts[1], key: parts[2] };
}

function safeWebhookInfo(rawUrl: string) {
  try {
    const url = new URL(rawUrl);
    return {
      protocol: url.protocol.replace(':', ''),
      host: url.host,
      pathname: url.pathname,
    };
  } catch {
    return { protocol: '', host: '', pathname: '' };
  }
}

function validatePlatformWebhookUrl(rawUrl: string) {
  let url: URL;
  try {
    url = new URL(rawUrl);
  } catch {
    throw new HttpError(400, 'webhookUrl is invalid', 'INVALID_WEBHOOK_URL');
  }
  if (url.protocol !== 'https:' && !['localhost', '127.0.0.1', '::1'].includes(url.hostname)) {
    throw new HttpError(400, 'webhookUrl must use HTTPS outside local development', 'INVALID_WEBHOOK_URL');
  }
  url.username = '';
  url.password = '';
  return url.toString();
}

function numberBetween(value: unknown, fallback: number, min: number, max: number) {
  const parsed = Number(value ?? fallback);
  if (!Number.isFinite(parsed)) return fallback;
  return Math.min(Math.max(Math.trunc(parsed), min), max);
}

function channelResponse<T extends Record<string, unknown>>(item: T) {
  return {
    ...item,
    webhookUrl: undefined,
    webhook: safeWebhookInfo(String(item.webhookUrl || '')),
  };
}

interface PlatformAuditAlertRow {
  id: string;
  tenantId: string | null;
  details: unknown;
  [key: string]: unknown;
}

function optionalUuidList(value: unknown, key = 'ids', maxLength = 500) {
  const values = Array.isArray(value)
    ? value.map(item => String(item).trim()).filter(Boolean)
    : typeof value === 'string' && value.trim()
      ? value.split(',').map(item => item.trim()).filter(Boolean)
      : [];

  if (values.length > maxLength) throw new HttpError(400, `${key} contains too many values`, 'TOO_MANY_UUIDS');
  for (const item of values) {
    if (!UUID_RE.test(item)) throw new HttpError(400, `${key} contains an invalid UUID`, 'INVALID_UUID');
  }
  return values;
}

function daysAheadFrom(value: unknown, fallback = 45) {
  const parsed = Number(value ?? fallback);
  if (!Number.isFinite(parsed) || parsed < 0) return fallback;
  return Math.min(Math.trunc(parsed), 1095);
}

function booleanFrom(value: unknown, fallback = false) {
  if (typeof value === 'boolean') return value;
  if (typeof value === 'string') {
    if (value === 'true') return true;
    if (value === 'false') return false;
  }
  return fallback;
}

function billingInvoiceTypeFrom(value: string) {
  const invoiceType = value || 'none';
  if (!['none', 'normal_vat', 'special_vat'].includes(invoiceType)) {
    throw new HttpError(400, 'invoiceType is invalid', 'INVALID_INVOICE_TYPE');
  }
  return invoiceType;
}

function tenantInvoiceStatusFrom(value: string) {
  const status = value || 'issued';
  if (!TENANT_INVOICE_STATUSES.has(status)) {
    throw new HttpError(400, 'invoice status is invalid', 'INVALID_INVOICE_STATUS');
  }
  return status;
}

function usageOverageInvoiceStatusFrom(value: string): 'draft' | 'issued' {
  const status = tenantInvoiceStatusFrom(value);
  if (!['draft', 'issued'].includes(status)) {
    throw new HttpError(400, 'usage overage invoices can only be draft or issued', 'INVALID_INVOICE_STATUS');
  }
  return status as 'draft' | 'issued';
}

function dateTextFrom(value: unknown, key: string, required = true) {
  const text = typeof value === 'string' ? value.trim() : '';
  if (!text) {
    if (required) throw new HttpError(400, `${key} is required`, 'REQUIRED_FIELD');
    return '';
  }
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) {
    throw new HttpError(400, `${key} must use YYYY-MM-DD format`, 'INVALID_DATE');
  }
  const date = new Date(`${text}T00:00:00.000Z`);
  if (!Number.isFinite(date.getTime()) || date.toISOString().slice(0, 10) !== text) {
    throw new HttpError(400, `${key} must be a valid date`, 'INVALID_DATE');
  }
  return text;
}

function assertDateRange(periodStart: string, periodEnd: string) {
  if (periodStart > periodEnd) {
    throw new HttpError(400, 'periodStart must be before or equal to periodEnd', 'INVALID_DATE_RANGE');
  }
}

function platformAuditDetails(value: unknown) {
  return JSON.stringify(value && typeof value === 'object' && !Array.isArray(value) ? value : {});
}

function requestIp(ctx: RequestContext) {
  const forwarded = getHeader(ctx.req, 'x-forwarded-for').split(',')[0]?.trim();
  return forwarded || ctx.req.socket.remoteAddress || null;
}

function auditLogQueryParams(ctx: RequestContext, defaultLimit = 100, maxLimit = 500) {
  const tenantId = listQuery(ctx, 'tenantId');
  const action = listQuery(ctx, 'action');
  const targetType = listQuery(ctx, 'targetType');
  const actorUserId = listQuery(ctx, 'actorUserId');
  const q = listQuery(ctx, 'q');
  const startDate = optionalDateFilter(ctx, 'startDate');
  const endDate = optionalDateFilter(ctx, 'endDate');
  if (tenantId && !UUID_RE.test(tenantId)) throw new HttpError(400, 'tenantId is invalid', 'INVALID_UUID');
  if (actorUserId && !UUID_RE.test(actorUserId)) throw new HttpError(400, 'actorUserId is invalid', 'INVALID_UUID');
  if (startDate && endDate && startDate > endDate) throw new HttpError(400, 'startDate must be before endDate', 'INVALID_DATE_RANGE');

  const limit = intParam(ctx, 'limit', defaultLimit, maxLimit);
  const params: unknown[] = [];
  const filters: string[] = [];

  if (tenantId) {
    params.push(tenantId);
    filters.push(`al.tenant_id = $${params.length}::uuid`);
  }
  if (action) {
    params.push(`${action}%`);
    filters.push(`al.action ilike $${params.length}`);
  }
  if (targetType) {
    params.push(targetType);
    filters.push(`al.target_type = $${params.length}`);
  }
  if (actorUserId) {
    params.push(actorUserId);
    filters.push(`al.actor_user_id = $${params.length}::uuid`);
  }
  if (startDate) {
    params.push(startDate);
    filters.push(`al.created_at >= $${params.length}::date`);
  }
  if (endDate) {
    params.push(endDate);
    filters.push(`al.created_at < ($${params.length}::date + interval '1 day')`);
  }
  if (q) {
    params.push(`%${q}%`);
    filters.push(`(al.action ilike $${params.length} or al.target_type ilike $${params.length} or al.target_id ilike $${params.length})`);
  }

  return {
    filters,
    params,
    limit,
    summary: { tenantId, action, targetType, actorUserId, q, startDate, endDate },
  };
}

async function loadPlatformAuditLogs(input: {
  filters: string[];
  params: unknown[];
  limit: number;
}) {
  const params = [...input.params, input.limit];
  return query<{
    id: string;
    tenantId: string | null;
    tenantSlug: string | null;
    tenantName: string | null;
    actorUserId: string | null;
    actorUsername: string | null;
    actorName: string | null;
    actorPhone: string | null;
    action: string;
    targetType: string | null;
    targetId: string | null;
    details: Record<string, unknown> | null;
    ipAddress: string | null;
    userAgent: string | null;
    createdAt: string;
  }>(
    `
    select al.id, al.tenant_id as "tenantId", t.slug::text as "tenantSlug",
           t.name as "tenantName", al.actor_user_id as "actorUserId",
           u.username as "actorUsername", u.name as "actorName",
           u.phone as "actorPhone", al.action,
           al.target_type as "targetType", al.target_id as "targetId",
           al.details, al.ip_address as "ipAddress", al.user_agent as "userAgent",
           al.created_at as "createdAt"
    from public.audit_logs al
    left join public.tenants t on t.id = al.tenant_id
    left join public.platform_users u on u.id = al.actor_user_id
    ${input.filters.length ? `where ${input.filters.join(' and ')}` : ''}
    order by al.created_at desc
    limit $${params.length}
    `,
    params,
  );
}

function platformAuditExportItems(items: Awaited<ReturnType<typeof loadPlatformAuditLogs>>) {
  return items.map(item => ({
    id: item.id,
    tenantId: item.tenantId,
    tenantSlug: item.tenantSlug,
    tenantName: item.tenantName,
    actorUserId: item.actorUserId,
    actorUsername: item.actorUsername,
    actorName: item.actorName,
    action: item.action,
    targetType: item.targetType,
    targetId: item.targetId,
    details: redactAuditExportValue(item.details),
    ipAddress: item.ipAddress,
    userAgent: item.userAgent,
    createdAt: item.createdAt,
  }));
}

async function recordPlatformAudit(
  client: pg.PoolClient,
  ctx: RequestContext,
  action: string,
  targetType: string,
  targetId: string | null,
  details: Record<string, unknown> = {},
  tenantId: string | null = null,
) {
  const session = currentSessionFromContext(ctx);
  await client.query(
    `
    insert into public.audit_logs (
      tenant_id, actor_user_id, action, target_type, target_id,
      details, ip_address, user_agent
    )
    values ($1::uuid, $2::uuid, $3, $4, $5, $6::jsonb, $7, $8)
    `,
    [
      tenantId || null,
      session?.id || null,
      action,
      targetType,
      targetId,
      platformAuditDetails(details),
      requestIp(ctx),
      getHeader(ctx.req, 'user-agent') || null,
    ],
  );
}

function grantScopeFrom(value: string) {
  const scope = value || 'plans';
  if (!['all_active_tenants', 'plans', 'tenants', 'mixed'].includes(scope)) {
    throw new HttpError(400, 'grantScope is invalid', 'INVALID_GRANT_SCOPE');
  }
  return scope;
}

function grantStatusFrom(value: string) {
  const status = value || 'active';
  if (!['active', 'disabled', 'expired'].includes(status)) {
    throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
  }
  return status;
}

function platformStaffStatusFrom(value: string, fallback = 'active') {
  const status = value || fallback;
  if (!PLATFORM_STAFF_STATUSES.has(status)) {
    throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
  }
  return status;
}

function normalizeOptionalEmail(value: string) {
  if (!value) return null;
  const email = value.toLowerCase();
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email) || email.length > 254) {
    throw new HttpError(400, 'email is invalid', 'INVALID_EMAIL');
  }
  return email;
}

function normalizeOptionalPhone(value: string) {
  if (!value) return null;
  const phone = value.replace(/\s+/g, '');
  if (!/^\+?[0-9-]{6,32}$/.test(phone)) {
    throw new HttpError(400, 'phone is invalid', 'INVALID_PHONE');
  }
  return phone;
}

function normalizePlatformUsername(value: string, fallback: string) {
  const username = (value || fallback).trim();
  if (!/^[a-zA-Z0-9_.@-]{3,80}$/.test(username)) {
    throw new HttpError(400, 'username is invalid', 'INVALID_USERNAME');
  }
  return username;
}

function allowedPlatformPermissionKeys() {
  return new Set<string>(PLATFORM_PERMISSION_CATALOG.map(item => item.key));
}

function normalizePlatformPermissions(value: unknown) {
  const input = value && typeof value === 'object' && !Array.isArray(value)
    ? value as Record<string, unknown>
    : {};
  const allowed = allowedPlatformPermissionKeys();
  const output: Record<string, true> = {};
  for (const [key, enabled] of Object.entries(input)) {
    if (enabled !== true) continue;
    const permission = key.trim();
    const domainWildcard = /^platform:[a-z_]+:\*$/.test(permission);
    const validWildcard = domainWildcard && [...allowed].some(item => item.startsWith(permission.slice(0, -1)));
    if (permission !== '*' && !allowed.has(permission) && !validWildcard) {
      throw new HttpError(400, `Platform permission ${permission} is invalid`, 'INVALID_PLATFORM_PERMISSION');
    }
    output[permission] = true;
  }
  return output;
}

function platformPermissionKeys(permissions: Record<string, unknown>) {
  return Object.keys(permissions).filter(key => permissions[key] === true).sort();
}

function platformPermissionAllowed(permissions: Record<string, unknown>, permission: string) {
  if (permissions['*'] === true) return true;
  if (permissions[permission] === true) return true;
  const parts = permission.split(':');
  for (let i = parts.length - 1; i >= 1; i -= 1) {
    if (permissions[`${parts.slice(0, i).join(':')}:*`] === true) return true;
  }
  return false;
}

export async function platformPermissionsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx);
  const session = currentSessionFromContext(ctx);
  const permissions = session?.platformPermissions && typeof session.platformPermissions === 'object'
    ? session.platformPermissions
    : { '*': true };

  return {
    item: {
      userId: session?.id || null,
      primaryRole: session?.primaryRole || 'platform_admin_key',
      permissions,
      effective: Object.fromEntries(
        PLATFORM_PERMISSION_CATALOG.map(item => [item.key, platformPermissionAllowed(permissions, item.key)]),
      ),
      catalog: PLATFORM_PERMISSION_CATALOG,
    },
  };
}

export async function platformStaffRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:staff:read');

  const status = listQuery(ctx, 'status');
  if (status && !PLATFORM_STAFF_STATUSES.has(status)) throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
  const q = listQuery(ctx, 'q');
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query<PlatformStaffPublicRow>(
    `
    select id, auth_user_id as "authUserId", username, email::text, phone, name, avatar_url as "avatarUrl",
           primary_role as "primaryRole", status, platform_permissions as "platformPermissions",
           raw_profile as "rawProfile", last_seen_at as "lastSeenAt",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.platform_users
    where primary_role = 'platform_admin'
      and ($1::text = '' or status = $1)
      and (
        $2::text = ''
        or coalesce(username, '') ilike '%' || $2 || '%'
        or coalesce(name, '') ilike '%' || $2 || '%'
        or coalesce(phone, '') ilike '%' || $2 || '%'
        or coalesce(email::text, '') ilike '%' || $2 || '%'
      )
    order by status asc, created_at desc
    limit $3
    `,
    [status, q, limit],
  );

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

export async function upsertPlatformStaffRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:staff:write');

  const body = await readJsonBody(ctx);
  const staffId = optionalString(body, 'id');
  if (staffId && !UUID_RE.test(staffId)) throw new HttpError(400, 'id is invalid', 'INVALID_UUID');
  const authUserId = requiredString(body, 'authUserId');
  if (!UUID_RE.test(authUserId)) throw new HttpError(400, 'authUserId is invalid', 'INVALID_UUID');

  const email = normalizeOptionalEmail(optionalString(body, 'email'));
  const phone = normalizeOptionalPhone(optionalString(body, 'phone'));
  const username = normalizePlatformUsername(optionalString(body, 'username'), email || phone || `platform_${Date.now().toString(36)}`);
  const name = requiredString(body, 'name');
  const avatarUrl = optionalString(body, 'avatarUrl') || null;
  const status = platformStaffStatusFrom(optionalString(body, 'status'), 'active');
  const permissions = normalizePlatformPermissions(body.platformPermissions);
  if (status === 'active' && platformPermissionKeys(permissions).length === 0) {
    throw new HttpError(400, 'Active platform staff must have at least one permission', 'PLATFORM_PERMISSION_EMPTY');
  }
  const metadata = objectValue(body.metadata);
  const session = currentSessionFromContext(ctx);

  const item = await transaction(async client => {
    let targetStaffId = staffId || null;

    const authUser = await client.query(
      `
      select id
      from auth.users
      where id = $1::uuid
      limit 1
      `,
      [authUserId],
    );
    if (authUser.rowCount === 0) {
      throw new HttpError(404, 'Supabase Auth user not found', 'AUTH_USER_NOT_FOUND');
    }

    const existingByAuth = await client.query(
      `
      select id, primary_role as "primaryRole"
      from public.platform_users
      where auth_user_id = $1::uuid
      for update
      `,
      [authUserId],
    );
    const existing = existingByAuth.rows[0];
    if (existing) {
      if (staffId && existing.id !== staffId) {
        throw new HttpError(409, 'authUserId is already bound to another platform user', 'AUTH_USER_ALREADY_BOUND');
      }
      if (existing.primaryRole !== 'platform_admin') {
        throw new HttpError(409, 'authUserId is already bound to a non-platform account', 'AUTH_USER_ALREADY_BOUND');
      }
      targetStaffId = existing.id;
    }

    if (targetStaffId) {
      const existingById = await client.query(
        `
        select id, auth_user_id as "authUserId", primary_role as "primaryRole"
        from public.platform_users
        where id = $1::uuid
        for update
        `,
        [targetStaffId],
      );
      const existing = existingById.rows[0];
      if (existing && existing.primaryRole !== 'platform_admin') {
        throw new HttpError(409, 'Platform staff id is already used by a non-platform account', 'PLATFORM_USER_ROLE_CONFLICT');
      }
      if (session?.id === targetStaffId && status !== 'active') {
        throw new HttpError(400, 'Current platform admin cannot disable itself', 'CANNOT_DISABLE_SELF');
      }
      if (session?.id === targetStaffId && existing.authUserId && existing.authUserId !== authUserId) {
        throw new HttpError(400, 'Current platform admin cannot change its own Auth binding', 'CANNOT_REBIND_SELF');
      }
      if (session?.id === targetStaffId && permissions['*'] !== true) {
        throw new HttpError(400, 'Current platform admin cannot remove its own super permission', 'CANNOT_DOWNGRADE_SELF');
      }
    }

    const result = await client.query(
      `
      insert into public.platform_users (
        id, auth_user_id, username, email, phone, name, avatar_url,
        primary_role, status, platform_permissions, raw_profile
      )
      values (
        coalesce($1::uuid, gen_random_uuid()), $2::uuid, $3, $4::citext, $5, $6, $7,
        'platform_admin', $8, $9::jsonb,
        jsonb_strip_nulls(coalesce($10::jsonb, '{}'::jsonb) || jsonb_build_object(
          'source', 'platform-admin:staff',
          'managedByPlatform', true
        ))
      )
      on conflict (id)
      do update set auth_user_id = coalesce(excluded.auth_user_id, public.platform_users.auth_user_id),
                    username = excluded.username,
                    email = excluded.email,
                    phone = excluded.phone,
                    name = excluded.name,
                    avatar_url = excluded.avatar_url,
                    primary_role = 'platform_admin',
                    status = excluded.status,
                    platform_permissions = excluded.platform_permissions,
                    raw_profile = jsonb_strip_nulls(public.platform_users.raw_profile || excluded.raw_profile),
                    updated_at = now()
      returning id, auth_user_id as "authUserId", username, email::text, phone, name,
                avatar_url as "avatarUrl", primary_role as "primaryRole", status,
                platform_permissions as "platformPermissions", raw_profile as "rawProfile",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        targetStaffId,
        authUserId || null,
        username,
        email,
        phone,
        name,
        avatarUrl,
        status,
        JSON.stringify(permissions),
        JSON.stringify(metadata),
      ],
    );
    const saved = result.rows[0];
    await recordPlatformAudit(client, ctx, 'platform.staff.upserted', 'platform_user', saved.id, {
      username,
      name,
      status,
      authUserBound: Boolean(authUserId),
      emailSet: Boolean(email),
      phoneSet: Boolean(phone),
      permissionKeys: platformPermissionKeys(permissions),
    });
    return platformStaffPublicRow(saved);
  });

  return { item };
}

export async function updatePlatformStaffStatusRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:staff:status');

  const body = await readJsonBody(ctx);
  const staffId = requiredString(body, 'staffId');
  if (!UUID_RE.test(staffId)) throw new HttpError(400, 'staffId is invalid', 'INVALID_UUID');
  const status = platformStaffStatusFrom(optionalString(body, 'status'));
  const reason = optionalString(body, 'reason') || null;
  const revokeSessions = booleanValue(body.revokeSessions, true);
  const session = currentSessionFromContext(ctx);
  if (session?.id === staffId && status !== 'active') {
    throw new HttpError(400, 'Current platform admin cannot disable itself', 'CANNOT_DISABLE_SELF');
  }

  const item = await transaction(async client => {
    const result = await client.query(
      `
      update public.platform_users
      set status = $2,
          raw_profile = jsonb_strip_nulls(raw_profile || jsonb_build_object(
            'platformStatusReason', $3::text,
            'platformStatusUpdatedAt', now()
          )),
          updated_at = now()
      where id = $1
        and primary_role = 'platform_admin'
      returning id, auth_user_id as "authUserId", username, email::text, phone, name,
                primary_role as "primaryRole", status, platform_permissions as "platformPermissions",
                updated_at as "updatedAt"
      `,
      [staffId, status, reason],
    );
    const saved = result.rows[0];
    if (!saved) throw new HttpError(404, 'Platform staff not found', 'PLATFORM_STAFF_NOT_FOUND');

    if (status === 'disabled' && revokeSessions) {
      await client.query(
        `
        update app_private.auth_sessions
        set revoked_at = now()
        where user_id = $1 and revoked_at is null
        `,
        [staffId],
      );
    }

    await recordPlatformAudit(client, ctx, 'platform.staff.status_updated', 'platform_user', staffId, {
      status,
      reasonSet: Boolean(reason),
      revokeSessions,
    });
    return saved;
  });

  return { item };
}

export async function platformOverviewRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:overview:read');

  const [tenantStats, invoiceStats, subscriptionStats, usageStats] = await Promise.all([
    queryOne<{
      totalTenants: string;
      activeTenants: string;
      suspendedTenants: string;
      trialTenants: string;
      pastDueTenants: string;
    }>(
      `
      select count(*)::text as "totalTenants",
             count(*) filter (where status = 'active')::text as "activeTenants",
             count(*) filter (where status = 'suspended')::text as "suspendedTenants",
             count(*) filter (where billing_status = 'trial')::text as "trialTenants",
             count(*) filter (where billing_status = 'past_due')::text as "pastDueTenants"
      from public.tenants
      `,
    ),
    queryOne<{
      unpaidAmountCents: string;
      paidAmountCents: string;
      overdueInvoices: string;
    }>(
      `
      select coalesce(sum(balance_cents) filter (where status in ('issued', 'overdue')), 0)::text as "unpaidAmountCents",
             coalesce(sum(paid_cents), 0)::text as "paidAmountCents",
             count(*) filter (where status = 'overdue' or (status = 'issued' and due_date < current_date))::text as "overdueInvoices"
      from public.tenant_invoices
      `,
    ),
    queryOne<{ activeSubscriptions: string; expiringSoon: string }>(
      `
      select count(*) filter (where status in ('trial', 'active'))::text as "activeSubscriptions",
             count(*) filter (where status in ('trial', 'active') and expires_at <= now() + interval '30 days')::text as "expiringSoon"
      from public.tenant_subscriptions
      `,
    ),
    queryOne<{ students: string; questions: string; storageGb: string }>(
      `
      with latest_usage as (
        select tenant_id, metric_key, metric_value,
               row_number() over (
                 partition by tenant_id, metric_key
                 order by case when metadata->>'source' = 'platform_usage_worker' then 0 else 1 end,
                          period_end desc,
                          created_at desc
               ) as rn
        from public.tenant_usage_records
        where period_end >= current_date - interval '31 days'
          and metric_key in ('students', 'questions', 'storage_gb')
      )
      select coalesce(sum(metric_value) filter (where metric_key = 'students'), 0)::text as students,
             coalesce(sum(metric_value) filter (where metric_key = 'questions'), 0)::text as questions,
             coalesce(sum(metric_value) filter (where metric_key = 'storage_gb'), 0)::text as "storageGb"
      from latest_usage
      where rn = 1
      `,
    ),
  ]);

  return {
    item: {
      tenants: {
        total: Number(tenantStats?.totalTenants || 0),
        active: Number(tenantStats?.activeTenants || 0),
        suspended: Number(tenantStats?.suspendedTenants || 0),
        trial: Number(tenantStats?.trialTenants || 0),
        pastDue: Number(tenantStats?.pastDueTenants || 0),
      },
      billing: {
        unpaidAmountCents: Number(invoiceStats?.unpaidAmountCents || 0),
        paidAmountCents: Number(invoiceStats?.paidAmountCents || 0),
        overdueInvoices: Number(invoiceStats?.overdueInvoices || 0),
      },
      subscriptions: {
        active: Number(subscriptionStats?.activeSubscriptions || 0),
        expiringSoon: Number(subscriptionStats?.expiringSoon || 0),
      },
      usage: {
        students: Number(usageStats?.students || 0),
        questions: Number(usageStats?.questions || 0),
        storageGb: Number(usageStats?.storageGb || 0),
      },
    },
  };
}

export async function platformPlansRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:plan:read');

  const includeArchived = listQuery(ctx, 'includeArchived') === 'true';
  const items = await query(
    `
    select id, code, name, description, billing_cycle as "billingCycle",
           base_amount_cents as "baseAmountCents", currency, included_quotas as "includedQuotas",
           overage_prices as "overagePrices", feature_flags as "featureFlags",
           status, sort_order as "sortOrder", created_at as "createdAt", updated_at as "updatedAt"
    from public.platform_saas_plans
    where ($1::boolean = true or status = 'active')
    order by sort_order asc, created_at asc
    `,
    [includeArchived],
  );

  return { items };
}

export async function platformQuestionBanksRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:question_bank:read');

  const q = listQuery(ctx, 'q');
  const regionId = listQuery(ctx, 'regionId');
  const status = listQuery(ctx, 'status') || 'active';
  const includeTenantBanks = listQuery(ctx, 'includeTenantBanks') === 'true';
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query(
    `
    select qb.id, qb.tenant_id as "tenantId", t.slug::text as "tenantSlug", t.name as "tenantName",
           qb.region_id as "regionId", r.name as "regionName", qb.name,
           qb.source_scope as "sourceScope", qb.status, qb.metadata,
           coalesce(qs.question_count, 0)::integer as "questionCount",
           qb.created_at as "createdAt", qb.updated_at as "updatedAt"
    from public.question_banks qb
    join public.tenants t on t.id = qb.tenant_id
    left join public.regions r on r.id = qb.region_id and r.tenant_id = qb.tenant_id
    left join lateral (
      select count(*)::integer as question_count
      from public.questions q
      where q.tenant_id = qb.tenant_id
        and q.question_bank_id = qb.id
        and q.status = 'published'
    ) qs on true
    where ($1::boolean = true or qb.source_scope = 'platform')
      and ($2::text = '' or qb.status = $2)
      and ($3::uuid is null or qb.region_id = $3::uuid)
      and (
        $4::text = ''
        or qb.name ilike '%' || $4 || '%'
        or coalesce(r.name, '') ilike '%' || $4 || '%'
      )
    order by qb.source_scope asc, r.sort_order asc nulls last, qb.created_at desc
    limit $5
    `,
    [includeTenantBanks, status, regionId || null, q, limit],
  );

  return { items };
}

export async function questionBankGrantsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:question_bank:read');

  const questionBankId = listQuery(ctx, 'questionBankId');
  const status = listQuery(ctx, 'status');
  const limit = intParam(ctx, 'limit', 100, 500);

  const items = await query(
    `
    select g.id, g.source_question_bank_id as "sourceQuestionBankId",
           qb.name as "sourceQuestionBankName", qb.region_id as "sourceRegionId",
           r.name as "sourceRegionName", g.grant_scope as "grantScope",
           g.allowed_plan_codes as "allowedPlanCodes",
           g.allowed_tenant_ids as "allowedTenantIds",
           g.allowed_region_ids as "allowedRegionIds",
           g.allowed_subject_ids as "allowedSubjectIds",
           g.status, g.starts_at as "startsAt", g.expires_at as "expiresAt",
           g.metadata, g.created_at as "createdAt", g.updated_at as "updatedAt"
    from public.question_bank_grants g
    join public.question_banks qb on qb.id = g.source_question_bank_id
    left join public.regions r on r.id = qb.region_id and r.tenant_id = qb.tenant_id
    where ($1::uuid is null or g.source_question_bank_id = $1::uuid)
      and ($2::text = '' or g.status = $2)
    order by g.updated_at desc, g.created_at desc
    limit $3
    `,
    [questionBankId || null, status, limit],
  );

  return { items };
}

export async function publicQuestionBankSyncStatusRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:question_bank:ops');

  const tenantId = listQuery(ctx, 'tenantId');
  const sourceQuestionBankId = listQuery(ctx, 'sourceQuestionBankId');
  const syncStatus = listQuery(ctx, 'syncStatus');
  const status = listQuery(ctx, 'status');
  const onlyOpenIssues = listQuery(ctx, 'onlyOpenIssues') === 'true';
  const limit = intParam(ctx, 'limit', 100, 500);
  if (tenantId && !UUID_RE.test(tenantId)) throw new HttpError(400, 'tenantId is invalid', 'INVALID_UUID');
  if (sourceQuestionBankId && !UUID_RE.test(sourceQuestionBankId)) throw new HttpError(400, 'sourceQuestionBankId is invalid', 'INVALID_UUID');
  if (syncStatus && !['pending', 'synced', 'failed'].includes(syncStatus)) {
    throw new HttpError(400, 'syncStatus is invalid', 'INVALID_SYNC_STATUS');
  }
  if (status && !['active', 'sync_pending', 'suspended', 'archived'].includes(status)) {
    throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
  }

  const items = await query(
    `
    select a.id,
           a.tenant_id as "tenantId",
           t.slug::text as "tenantSlug",
           t.name as "tenantName",
           a.source_question_bank_id as "sourceQuestionBankId",
           source_qb.name as "sourceQuestionBankName",
           a.grant_id as "grantId",
           a.target_question_bank_id as "targetQuestionBankId",
           target_qb.name as "targetQuestionBankName",
           a.target_entry_id as "targetEntryId",
           ce.name as "targetEntryName",
           a.target_collection_id as "targetCollectionId",
           qc.name as "targetCollectionName",
           a.status,
           a.sync_status as "syncStatus",
           a.copied_question_count as "copiedQuestionCount",
           a.last_synced_at as "lastSyncedAt",
           a.updated_at as "updatedAt",
           coalesce((a.metadata #>> '{lastSync,conflictCount}')::integer, 0) as "conflictCount",
           coalesce(a.metadata #> '{lastSync,counts}', '{}'::jsonb) as "lastSyncCounts",
           jsonb_build_object(
             'lastSync', coalesce(a.metadata->'lastSync', '{}'::jsonb),
             'publicBankSyncWorker', coalesce(a.metadata->'publicBankSyncWorker', '{}'::jsonb)
           ) as metadata,
           coalesce(open_notifications.open_count, 0)::integer as "openNotificationCount",
           coalesce(open_notifications.failure_count, 0)::integer as "openFailureNotificationCount",
           coalesce(open_notifications.conflict_count, 0)::integer as "openConflictNotificationCount"
    from public.tenant_question_bank_adoptions a
    join public.tenants t on t.id = a.tenant_id
    join public.question_banks source_qb on source_qb.id = a.source_question_bank_id
    left join public.question_banks target_qb on target_qb.id = a.target_question_bank_id
    left join public.content_entries ce on ce.id = a.target_entry_id and ce.tenant_id = a.tenant_id
    left join public.question_collections qc on qc.id = a.target_collection_id and qc.tenant_id = a.tenant_id
    left join lateral (
      select count(*) filter (where n.status in ('unread', 'read')) as open_count,
             count(*) filter (
               where n.status in ('unread', 'read')
                 and n.notification_type = 'public_question_bank_sync_failed'
             ) as failure_count,
             count(*) filter (
               where n.status in ('unread', 'read')
                 and n.notification_type = 'public_question_bank_conflict'
             ) as conflict_count
      from public.tenant_content_notifications n
      where n.tenant_id = a.tenant_id
        and n.adoption_id = a.id
        and n.notification_type in ('public_question_bank_sync_failed', 'public_question_bank_conflict')
    ) open_notifications on true
    where ($1::uuid is null or a.tenant_id = $1::uuid)
      and ($2::uuid is null or a.source_question_bank_id = $2::uuid)
      and ($3::text = '' or a.sync_status = $3)
      and ($4::text = '' or a.status = $4)
      and (
        $5::boolean = false
        or a.sync_status = 'failed'
        or coalesce(open_notifications.open_count, 0) > 0
      )
    order by
      case when a.sync_status = 'failed' then 0 when a.sync_status = 'pending' then 1 else 2 end,
      coalesce(a.last_synced_at, a.updated_at) desc
    limit $6
    `,
    [tenantId || null, sourceQuestionBankId || null, syncStatus, status, onlyOpenIssues, limit],
  );

  const summaryRows = await query<{ syncStatus: string; count: number }>(
    `
    select a.sync_status as "syncStatus", count(*)::integer as count
    from public.tenant_question_bank_adoptions a
    where ($1::uuid is null or a.tenant_id = $1::uuid)
      and ($2::uuid is null or a.source_question_bank_id = $2::uuid)
    group by a.sync_status
    `,
    [tenantId || null, sourceQuestionBankId || null],
  );

  return {
    items,
    summary: Object.fromEntries(summaryRows.map(row => [row.syncStatus, Number(row.count || 0)])),
  };
}

export async function upsertQuestionBankGrantRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:question_bank:grant');

  const body = await readJsonBody(ctx);
  const questionBankId = requiredString(body, 'sourceQuestionBankId');
  const grantScope = grantScopeFrom(optionalString(body, 'grantScope'));
  const allowedPlanCodes = optionalStringArray(body, 'allowedPlanCodes');
  const allowedTenantIds = optionalUuidArray(body, 'allowedTenantIds');
  const allowedRegionIds = optionalUuidArray(body, 'allowedRegionIds');
  const allowedSubjectIds = optionalUuidArray(body, 'allowedSubjectIds');
  const status = grantStatusFrom(optionalString(body, 'status'));
  const startsAt = optionalString(body, 'startsAt') || null;
  const expiresAt = optionalString(body, 'expiresAt') || null;

  if (grantScope === 'plans' && allowedPlanCodes.length === 0) {
    throw new HttpError(400, 'allowedPlanCodes is required for plan grants', 'ALLOWED_PLANS_REQUIRED');
  }
  if (grantScope === 'tenants' && allowedTenantIds.length === 0) {
    throw new HttpError(400, 'allowedTenantIds is required for tenant grants', 'ALLOWED_TENANTS_REQUIRED');
  }
  if (grantScope === 'mixed' && allowedPlanCodes.length === 0 && allowedTenantIds.length === 0) {
    throw new HttpError(400, 'mixed grants require plans or tenants', 'GRANT_TARGET_REQUIRED');
  }

  const item = await transaction(async client => {
    const bankResult = await client.query<{ id: string; tenantId: string; regionId: string | null }>(
      `
      select id, tenant_id as "tenantId", region_id as "regionId"
      from public.question_banks
      where id = $1
        and source_scope = 'platform'
      limit 1
      `,
      [questionBankId],
    );
    if (!bankResult.rows[0]) {
      throw new HttpError(404, 'Platform question bank not found', 'PLATFORM_QUESTION_BANK_NOT_FOUND');
    }
    const sourceBank = bankResult.rows[0];

    if (allowedRegionIds.length) {
      const regionCount = await client.query<{ count: string }>(
        'select count(*)::text as count from public.regions where tenant_id = $1 and id = any($2::uuid[])',
        [sourceBank.tenantId, allowedRegionIds],
      );
      if (Number(regionCount.rows[0]?.count || 0) !== allowedRegionIds.length) {
        throw new HttpError(400, 'One or more allowed regions do not belong to the source platform tenant', 'PUBLIC_BANK_REGION_NOT_FOUND');
      }
      if (sourceBank.regionId && !allowedRegionIds.includes(sourceBank.regionId)) {
        throw new HttpError(400, 'allowedRegionIds must include the source question bank region', 'PUBLIC_BANK_REGION_SCOPE_MISMATCH');
      }
    }

    if (allowedSubjectIds.length) {
      const subjectCount = await client.query<{ count: string }>(
        'select count(*)::text as count from public.subjects where tenant_id = $1 and id = any($2::uuid[])',
        [sourceBank.tenantId, allowedSubjectIds],
      );
      if (Number(subjectCount.rows[0]?.count || 0) !== allowedSubjectIds.length) {
        throw new HttpError(400, 'One or more allowed subjects do not belong to the source platform tenant', 'PUBLIC_BANK_SUBJECT_NOT_FOUND');
      }
      const outOfScopeSubjects = await client.query<{ count: string }>(
        `
        select count(distinct q.subject_id)::text as count
        from public.questions q
        where q.tenant_id = $1
          and q.question_bank_id = $2
          and q.status = 'published'
          and q.subject_id is not null
          and not q.subject_id = any($3::uuid[])
        `,
        [sourceBank.tenantId, questionBankId, allowedSubjectIds],
      );
      if (Number(outOfScopeSubjects.rows[0]?.count || 0) > 0) {
        throw new HttpError(400, 'allowedSubjectIds do not cover all published questions in this bank', 'PUBLIC_BANK_SUBJECT_SCOPE_MISMATCH');
      }
    }

    if (allowedPlanCodes.length) {
      const planCount = await client.query<{ count: string }>(
        'select count(*)::text as count from public.platform_saas_plans where code = any($1::text[])',
        [allowedPlanCodes],
      );
      if (Number(planCount.rows[0]?.count || 0) !== allowedPlanCodes.length) {
        throw new HttpError(400, 'One or more SaaS plans do not exist', 'SAAS_PLAN_NOT_FOUND');
      }
    }

    if (allowedTenantIds.length) {
      const tenantCount = await client.query<{ count: string }>(
        'select count(*)::text as count from public.tenants where id = any($1::uuid[])',
        [allowedTenantIds],
      );
      if (Number(tenantCount.rows[0]?.count || 0) !== allowedTenantIds.length) {
        throw new HttpError(400, 'One or more tenants do not exist', 'TENANT_NOT_FOUND');
      }
    }

    const result = await client.query(
      `
      insert into public.question_bank_grants (
        id, source_question_bank_id, grant_scope, allowed_plan_codes,
        allowed_tenant_ids, allowed_region_ids, allowed_subject_ids,
        status, starts_at, expires_at, metadata
      )
      values (
        coalesce($1::uuid, gen_random_uuid()), $2, $3, $4::text[],
        $5::uuid[], $6::uuid[], $7::uuid[],
        $8, $9::timestamptz, $10::timestamptz, $11::jsonb
      )
      on conflict (id)
      do update set source_question_bank_id = excluded.source_question_bank_id,
                    grant_scope = excluded.grant_scope,
                    allowed_plan_codes = excluded.allowed_plan_codes,
                    allowed_tenant_ids = excluded.allowed_tenant_ids,
                    allowed_region_ids = excluded.allowed_region_ids,
                    allowed_subject_ids = excluded.allowed_subject_ids,
                    status = excluded.status,
                    starts_at = excluded.starts_at,
                    expires_at = excluded.expires_at,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id, source_question_bank_id as "sourceQuestionBankId",
                grant_scope as "grantScope", allowed_plan_codes as "allowedPlanCodes",
                allowed_tenant_ids as "allowedTenantIds", allowed_region_ids as "allowedRegionIds",
                allowed_subject_ids as "allowedSubjectIds", status,
                starts_at as "startsAt", expires_at as "expiresAt",
                metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        optionalString(body, 'id') || null,
        questionBankId,
        grantScope,
        allowedPlanCodes,
        allowedTenantIds,
        allowedRegionIds,
        allowedSubjectIds,
        status,
        startsAt,
        expiresAt,
        jsonBodyValue(body.metadata),
      ],
    );

    await client.query(
      `
      insert into public.audit_logs (tenant_id, action, target_type, target_id, details)
      values (null, 'platform.question_bank.grant_upserted', 'question_bank_grant', $1, $2::jsonb)
      `,
      [
        result.rows[0].id,
        JSON.stringify({
          sourceQuestionBankId: questionBankId,
          grantScope,
          allowedPlanCodes,
          allowedTenantIds,
          allowedRegionIds,
          allowedSubjectIds,
          status,
        }),
      ],
    );

    return result.rows[0];
  });

  return { item };
}

export async function tenantsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:tenant:read');

  const status = listQuery(ctx, 'status');
  const billingStatus = listQuery(ctx, 'billingStatus');
  const q = listQuery(ctx, 'q');
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query(
    `
    select t.id, t.slug::text, t.name, t.legal_name as "legalName", t.status, t.mode,
           t.billing_status as "billingStatus", t.owner_user_id as "ownerUserId",
           t.metadata, t.created_at as "createdAt", t.updated_at as "updatedAt",
           b.brand_name as "brandName", b.logo_url as "logoUrl",
           s.plan_code as "planCode", s.status as "subscriptionStatus",
           s.expires_at as "subscriptionExpiresAt",
           coalesce(i.open_balance_cents, 0) as "openBalanceCents"
    from public.tenants t
    left join public.tenant_branding b on b.tenant_id = t.id
    left join lateral (
      select plan_code, status, expires_at
      from public.tenant_subscriptions
      where tenant_id = t.id
      order by created_at desc
      limit 1
    ) s on true
    left join lateral (
      select sum(balance_cents)::integer as open_balance_cents
      from public.tenant_invoices
      where tenant_id = t.id and status in ('issued', 'overdue')
    ) i on true
    where ($1::text = '' or t.status = $1)
      and ($2::text = '' or t.billing_status = $2)
      and (
        $3::text = ''
        or t.slug::text ilike '%' || $3 || '%'
        or t.name ilike '%' || $3 || '%'
        or coalesce(t.legal_name, '') ilike '%' || $3 || '%'
      )
    order by t.created_at desc
    limit $4
    `,
    [status, billingStatus, q, limit],
  );

  return { items };
}

export async function tenantDetailRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:tenant:read');

  const tenantId = ctx.url.searchParams.get('tenantId') || '';
  if (!tenantId) throw new HttpError(400, 'tenantId is required', 'TENANT_ID_REQUIRED');

  const tenant = await queryOne(
    `
    select t.id, t.slug::text, t.name, t.legal_name as "legalName", t.status, t.mode,
           t.billing_status as "billingStatus", t.owner_user_id as "ownerUserId",
           t.metadata, t.created_at as "createdAt", t.updated_at as "updatedAt",
           b.brand_name as "brandName", b.short_name as "shortName", b.logo_url as "logoUrl",
           b.service_wechat as "serviceWechat",
           bp.billing_name as "billingName", bp.tax_id as "taxId",
           bp.contact_name as "contactName", bp.contact_phone as "contactPhone",
           bp.contact_email as "contactEmail", bp.invoice_title as "invoiceTitle",
           bp.invoice_type as "invoiceType", bp.billing_address as "billingAddress",
           bp.bank_name as "bankName", bp.bank_account_masked as "bankAccountMasked"
    from public.tenants t
    left join public.tenant_branding b on b.tenant_id = t.id
    left join public.tenant_billing_profiles bp on bp.tenant_id = t.id
    where t.id = $1
    limit 1
    `,
    [tenantId],
  );

  if (!tenant) throw new HttpError(404, 'Tenant not found', 'TENANT_NOT_FOUND');

  const [domains, subscriptions, invoices, usage] = await Promise.all([
    query(
      `
      select id, host::text, domain_type as "domainType", status, is_primary as "isPrimary",
             verified_at as "verifiedAt", created_at as "createdAt"
      from public.tenant_domains
      where tenant_id = $1
      order by is_primary desc, created_at asc
      `,
      [tenantId],
    ),
    query(
      `
      select id, plan_code as "planCode", status, starts_at as "startsAt",
             expires_at as "expiresAt", billing_cycle as "billingCycle",
             amount_cents as "amountCents", metadata, created_at as "createdAt"
      from public.tenant_subscriptions
      where tenant_id = $1
      order by created_at desc
      limit 10
      `,
      [tenantId],
    ),
    query(
      `
      select id, tenant_id as "tenantId", invoice_no as "invoiceNo", invoice_type as "invoiceType", status,
             total_cents as "totalCents", paid_cents as "paidCents",
             balance_cents as "balanceCents", due_date as "dueDate",
             issued_at as "issuedAt", paid_at as "paidAt", created_at as "createdAt"
      from public.tenant_invoices
      where tenant_id = $1
      order by created_at desc
      limit 20
      `,
      [tenantId],
    ),
    query(
      `
      select id, tenant_id as "tenantId", metric_key as "metricKey", metric_value as "metricValue",
             period_start as "periodStart", period_end as "periodEnd", metadata,
             created_at as "createdAt"
      from public.tenant_usage_records
      where tenant_id = $1
      order by period_end desc, metric_key asc
      limit 50
      `,
      [tenantId],
    ),
  ]);

  return { item: { tenant, domains, subscriptions, invoices, usage } };
}

export async function platformAuditLogsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:read');

  const auditQuery = auditLogQueryParams(ctx, 100, 500);
  const items = await loadPlatformAuditLogs(auditQuery);

  return { items };
}

export async function platformAuditLogsExportRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:export');

  const format = auditExportFormat(listQuery(ctx, 'format'));
  const auditQuery = auditLogQueryParams(ctx, 1000, 5000);
  const items = await loadPlatformAuditLogs(auditQuery);
  const exportItems = platformAuditExportItems(items);
  const exportedAt = new Date().toISOString();
  const filename = `platform-audit-${platformExportTimestamp()}.${format}`;
  const content = format === 'json'
    ? JSON.stringify({ exportedAt, filters: auditQuery.summary, items: exportItems }, null, 2)
    : [
      csvLine([
        'id',
        'tenantId',
        'tenantSlug',
        'tenantName',
        'actorUserId',
        'actorUsername',
        'actorName',
        'action',
        'targetType',
        'targetId',
        'details',
        'ipAddress',
        'userAgent',
        'createdAt',
      ]),
      ...exportItems.map(item => csvLine([
        item.id,
        item.tenantId,
        item.tenantSlug,
        item.tenantName,
        item.actorUserId,
        item.actorUsername,
        item.actorName,
        item.action,
        item.targetType,
        item.targetId,
        item.details,
        item.ipAddress,
        item.userAgent,
        item.createdAt,
      ])),
    ].join('\n');
  const encoded = contentBase64AndHash(content);

  await transaction(async client => {
    await recordPlatformAudit(client, ctx, 'platform.audit.exported', 'audit_logs', null, {
      format,
      filename,
      rowCount: items.length,
      sha256: encoded.sha256,
      filters: auditQuery.summary,
    });
  });

  return {
    item: {
      filename,
      format,
      mimeType: format === 'json' ? 'application/json' : 'text/csv',
      rowCount: items.length,
      exportedAt,
      filters: auditQuery.summary,
      ...encoded,
    },
  };
}

export async function platformAuditAlertRulesRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:alert');

  const enabled = listQuery(ctx, 'enabled');
  if (enabled && !['true', 'false'].includes(enabled)) {
    throw new HttpError(400, 'enabled must be true or false', 'INVALID_BOOLEAN');
  }
  const items = await query(
    `
    select id, code, name, description, enabled, severity,
           action_patterns as "actionPatterns", target_types as "targetTypes",
           tenant_id as "tenantId", conditions, metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.platform_audit_alert_rules
    where ($1::text = '' or enabled = ($1 = 'true'))
    order by enabled desc,
             case severity when 'critical' then 1 when 'high' then 2 when 'medium' then 3 else 4 end,
             code asc
    `,
    [enabled],
  );
  return { items };
}

export async function platformAuditAlertsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:alert');

  const tenantId = listQuery(ctx, 'tenantId');
  const status = listQuery(ctx, 'status');
  const severity = listQuery(ctx, 'severity');
  const ruleCode = listQuery(ctx, 'ruleCode');
  const q = listQuery(ctx, 'q');
  const limit = intParam(ctx, 'limit', 100, 500);
  if (tenantId && !UUID_RE.test(tenantId)) throw new HttpError(400, 'tenantId is invalid', 'INVALID_UUID');
  if (status && !PLATFORM_AUDIT_ALERT_STATUSES.has(status)) throw new HttpError(400, 'status is invalid', 'INVALID_ALERT_STATUS');
  if (severity && !['low', 'medium', 'high', 'critical'].includes(severity)) throw new HttpError(400, 'severity is invalid', 'INVALID_ALERT_SEVERITY');

  const items = await query<PlatformAuditAlertRow>(
    `
    select a.id, a.rule_id as "ruleId", r.code as "ruleCode", r.name as "ruleName",
           a.audit_log_id as "auditLogId", a.tenant_id as "tenantId",
           t.slug::text as "tenantSlug", t.name as "tenantName",
           a.severity, a.status, a.action, a.target_type as "targetType",
           a.target_id as "targetId", a.title, a.summary, a.details,
           a.first_seen_at as "firstSeenAt", a.last_seen_at as "lastSeenAt",
           a.acknowledged_by as "acknowledgedBy", ack.username as "acknowledgedByUsername",
           ack.name as "acknowledgedByName", a.acknowledged_at as "acknowledgedAt",
           a.resolved_by as "resolvedBy", res.username as "resolvedByUsername",
           res.name as "resolvedByName", a.resolved_at as "resolvedAt",
           a.resolution_note as "resolutionNote",
           al.actor_user_id as "actorUserId", actor.username as "actorUsername",
           actor.name as "actorName", al.ip_address as "ipAddress",
           al.user_agent as "userAgent", al.created_at as "auditCreatedAt",
           a.created_at as "createdAt", a.updated_at as "updatedAt"
    from public.platform_audit_alerts a
    join public.platform_audit_alert_rules r on r.id = a.rule_id
    left join public.audit_logs al on al.id = a.audit_log_id
    left join public.tenants t on t.id = a.tenant_id
    left join public.platform_users actor on actor.id = al.actor_user_id
    left join public.platform_users ack on ack.id = a.acknowledged_by
    left join public.platform_users res on res.id = a.resolved_by
    where ($1::uuid is null or a.tenant_id = $1::uuid)
      and ($2::text = '' or a.status = $2)
      and ($3::text = '' or a.severity = $3)
      and ($4::text = '' or r.code = $4)
      and (
        $5::text = ''
        or a.action ilike '%' || $5 || '%'
        or a.title ilike '%' || $5 || '%'
        or coalesce(a.target_type, '') ilike '%' || $5 || '%'
        or coalesce(a.target_id, '') ilike '%' || $5 || '%'
        or coalesce(t.name, '') ilike '%' || $5 || '%'
        or coalesce(t.slug::text, '') ilike '%' || $5 || '%'
      )
    order by
      case a.status when 'open' then 1 when 'acknowledged' then 2 else 3 end,
      case a.severity when 'critical' then 1 when 'high' then 2 when 'medium' then 3 else 4 end,
      a.created_at desc
    limit $6
    `,
    [tenantId || null, status, severity, ruleCode, q, limit],
  );

  return {
    items: items.map(item => ({
      ...item,
      details: redactAuditAlertValue(item.details),
    })),
  };
}

export async function updatePlatformAuditAlertStatusRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:alert');

  const body = await readJsonBody(ctx);
  const alertId = requiredString(body, 'alertId');
  if (!UUID_RE.test(alertId)) throw new HttpError(400, 'alertId is invalid', 'INVALID_UUID');
  const status = auditAlertStatusFrom(optionalString(body, 'status'));
  const resolutionNote = optionalString(body, 'resolutionNote') || null;
  const session = currentSessionFromContext(ctx);

  const item = await transaction(async client => {
    const result = await client.query<PlatformAuditAlertRow>(
      `
      update public.platform_audit_alerts
      set status = $2,
          acknowledged_by = case when $2 in ('acknowledged', 'resolved', 'ignored') then $3::uuid else acknowledged_by end,
          acknowledged_at = case when $2 in ('acknowledged', 'resolved', 'ignored') then coalesce(acknowledged_at, now()) else acknowledged_at end,
          resolved_by = case when $2 in ('resolved', 'ignored') then $3::uuid else null end,
          resolved_at = case when $2 in ('resolved', 'ignored') then now() else null end,
          resolution_note = coalesce($4, resolution_note),
          updated_at = now()
      where id = $1
      returning id, rule_id as "ruleId", audit_log_id as "auditLogId",
                tenant_id as "tenantId", severity, status, action,
                target_type as "targetType", target_id as "targetId",
                title, summary, details, acknowledged_by as "acknowledgedBy",
                acknowledged_at as "acknowledgedAt", resolved_by as "resolvedBy",
                resolved_at as "resolvedAt", resolution_note as "resolutionNote",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [alertId, status, session?.id || null, resolutionNote],
    );
    const updated = result.rows[0];
    if (!updated) throw new HttpError(404, 'Audit alert not found', 'AUDIT_ALERT_NOT_FOUND');

    await recordPlatformAudit(client, ctx, 'platform.audit.alert_status_updated', 'platform_audit_alert', alertId, {
      status,
      resolutionNoteSet: Boolean(resolutionNote),
    }, updated.tenantId || null);
    return updated;
  });

  return {
    item: {
      ...item,
      details: redactAuditAlertValue(item.details),
    },
  };
}

export async function platformAuditNotificationChannelsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:notification');

  const enabled = listQuery(ctx, 'enabled');
  const provider = listQuery(ctx, 'provider');
  const limit = intParam(ctx, 'limit', 100, 500);
  if (enabled && !['true', 'false'].includes(enabled)) {
    throw new HttpError(400, 'enabled must be true or false', 'INVALID_BOOLEAN');
  }
  if (provider && !PLATFORM_AUDIT_NOTIFICATION_PROVIDERS.has(provider)) {
    throw new HttpError(400, 'provider is invalid', 'INVALID_NOTIFICATION_PROVIDER');
  }

  const items = await query<Record<string, unknown>>(
    `
    select id, channel_code as "channelCode", name, description, enabled,
           provider, webhook_url as "webhookUrl", secret_ref as "secretRef",
           min_severity as "minSeverity", status_filter as "statusFilter",
           action_patterns as "actionPatterns", tenant_ids as "tenantIds",
           timeout_sec as "timeoutSec", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.platform_audit_notification_channels
    where ($1::text = '' or enabled = ($1 = 'true'))
      and ($2::text = '' or provider = $2)
    order by enabled desc,
             case min_severity when 'critical' then 1 when 'high' then 2 when 'medium' then 3 else 4 end,
             channel_code asc
    limit $3
    `,
    [enabled, provider, limit],
  );

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

export async function upsertPlatformAuditNotificationChannelRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:notification');

  const body = await readJsonBody(ctx);
  const channelCode = platformAuditNotificationChannelCode(requiredString(body, 'channelCode'));
  const name = requiredString(body, 'name');
  const provider = platformAuditNotificationProviderFrom(optionalString(body, 'provider'));
  const webhookUrl = validatePlatformWebhookUrl(requiredString(body, 'webhookUrl'));
  const statusFilter = platformAuditNotificationStatusFilter(body.statusFilter);
  const actionPatterns = optionalStringArray(body, 'actionPatterns').slice(0, 50);
  const tenantIds = optionalUuidList(body.tenantIds, 'tenantIds', 200);
  const minSeverity = platformAuditSeverityFrom(optionalString(body, 'minSeverity'), 'medium');
  const timeoutSec = numberBetween(body.timeoutSec, 10, 1, 60);
  const description = optionalString(body, 'description') || null;
  const metadata = objectValue(body.metadata);
  let secretRef = optionalString(body, 'secretRef') || null;
  const secret = typeof body.secret === 'string' && body.secret.trim() ? body.secret.trim() : '';
  if (secret) secretRef = platformSecretRef('webhook', channelCode);
  if (secretRef && !parsePlatformSecretRef(secretRef)) {
    throw new HttpError(400, 'secretRef is invalid', 'INVALID_SECRET_REF');
  }

  const item = await transaction(async client => {
    if (secret) {
      await client.query(
        `
        insert into app_private.platform_secrets (
          secret_scope, secret_key, secret_value, provider, last_rotated_at
        )
        values ('webhook', $1, $2, $3, now())
        on conflict (secret_scope, secret_key)
        do update set secret_value = excluded.secret_value,
                      provider = excluded.provider,
                      last_rotated_at = now(),
                      updated_at = now()
        `,
        [channelCode, secret, provider],
      );
    }

    const result = await client.query<Record<string, unknown>>(
      `
      insert into public.platform_audit_notification_channels (
        channel_code, name, description, enabled, provider, webhook_url,
        secret_ref, min_severity, status_filter, action_patterns,
        tenant_ids, timeout_sec, metadata
      )
      values (
        $1, $2, $3, $4, $5, $6,
        $7, $8, $9::text[], $10::text[],
        $11::uuid[], $12, $13::jsonb
      )
      on conflict (channel_code)
      do update set name = excluded.name,
                    description = excluded.description,
                    enabled = excluded.enabled,
                    provider = excluded.provider,
                    webhook_url = excluded.webhook_url,
                    secret_ref = excluded.secret_ref,
                    min_severity = excluded.min_severity,
                    status_filter = excluded.status_filter,
                    action_patterns = excluded.action_patterns,
                    tenant_ids = excluded.tenant_ids,
                    timeout_sec = excluded.timeout_sec,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id, channel_code as "channelCode", name, description, enabled,
                provider, webhook_url as "webhookUrl", secret_ref as "secretRef",
                min_severity as "minSeverity", status_filter as "statusFilter",
                action_patterns as "actionPatterns", tenant_ids as "tenantIds",
                timeout_sec as "timeoutSec", metadata,
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        channelCode,
        name,
        description,
        body.enabled !== false,
        provider,
        webhookUrl,
        secretRef,
        minSeverity,
        statusFilter,
        actionPatterns,
        tenantIds,
        timeoutSec,
        JSON.stringify(metadata),
      ],
    );
    const saved = result.rows[0];
    await recordPlatformAudit(client, ctx, 'platform.audit.notification_channel_upserted', 'platform_audit_notification_channel', String(saved.id), {
      channelCode,
      provider,
      enabled: body.enabled !== false,
      minSeverity,
      statusFilter,
      actionPatternCount: actionPatterns.length,
      tenantIdCount: tenantIds.length,
      secretRefSet: Boolean(secretRef),
      secretRotated: Boolean(secret),
      webhook: safeWebhookInfo(webhookUrl),
    });
    return saved;
  });

  return { item: channelResponse(item) };
}

export async function platformAuditNotificationEventsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:audit:notification');

  const channelId = listQuery(ctx, 'channelId');
  const alertId = listQuery(ctx, 'alertId');
  const status = listQuery(ctx, 'status');
  const provider = listQuery(ctx, 'provider');
  const limit = intParam(ctx, 'limit', 100, 500);
  if (channelId && !UUID_RE.test(channelId)) throw new HttpError(400, 'channelId is invalid', 'INVALID_UUID');
  if (alertId && !UUID_RE.test(alertId)) throw new HttpError(400, 'alertId is invalid', 'INVALID_UUID');
  if (status && !PLATFORM_AUDIT_NOTIFICATION_EVENT_STATUSES.has(status)) {
    throw new HttpError(400, 'status is invalid', 'INVALID_NOTIFICATION_EVENT_STATUS');
  }
  if (provider && !PLATFORM_AUDIT_NOTIFICATION_PROVIDERS.has(provider)) {
    throw new HttpError(400, 'provider is invalid', 'INVALID_NOTIFICATION_PROVIDER');
  }

  const items = await query<Record<string, unknown>>(
    `
    select e.id, e.channel_id as "channelId", c.channel_code as "channelCode",
           c.name as "channelName", e.alert_id as "alertId",
           e.audit_log_id as "auditLogId", e.provider, e.status, e.attempts,
           e.scheduled_at as "scheduledAt", e.next_attempt_at as "nextAttemptAt",
           e.last_attempt_at as "lastAttemptAt", e.sent_at as "sentAt",
           e.last_error as "lastError", e.last_http_code as "lastHttpCode",
           e.last_response_summary as "lastResponseSummary",
           e.request_payload as "requestPayload", e.metadata,
           a.tenant_id as "tenantId", t.slug::text as "tenantSlug",
           t.name as "tenantName", a.severity as "alertSeverity",
           a.status as "alertStatus", a.title as "alertTitle",
           a.action as "alertAction", a.target_type as "targetType",
           a.target_id as "targetId",
           e.created_at as "createdAt", e.updated_at as "updatedAt"
    from public.platform_audit_notification_events e
    join public.platform_audit_notification_channels c on c.id = e.channel_id
    join public.platform_audit_alerts a on a.id = e.alert_id
    left join public.tenants t on t.id = a.tenant_id
    where ($1::uuid is null or e.channel_id = $1::uuid)
      and ($2::uuid is null or e.alert_id = $2::uuid)
      and ($3::text = '' or e.status = $3)
      and ($4::text = '' or e.provider = $4)
    order by
      case e.status when 'pending' then 1 when 'retrying' then 2 when 'processing' then 3 when 'failed' then 4 else 5 end,
      e.created_at desc
    limit $5
    `,
    [channelId || null, alertId || null, status, provider, limit],
  );

  return {
    items: items.map(item => ({
      ...item,
      requestPayload: redactAuditAlertValue(item.requestPayload),
      lastError: truncate(item.lastError),
      lastResponseSummary: truncate(item.lastResponseSummary),
    })),
  };
}

export async function platformDunningNotificationChannelsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:notification');

  const enabled = listQuery(ctx, 'enabled');
  const provider = listQuery(ctx, 'provider');
  const limit = intParam(ctx, 'limit', 100, 500);
  if (enabled && !['true', 'false'].includes(enabled)) {
    throw new HttpError(400, 'enabled must be true or false', 'INVALID_BOOLEAN');
  }
  if (provider && !PLATFORM_AUDIT_NOTIFICATION_PROVIDERS.has(provider)) {
    throw new HttpError(400, 'provider is invalid', 'INVALID_NOTIFICATION_PROVIDER');
  }

  const items = await query<Record<string, unknown>>(
    `
    select id, channel_code as "channelCode", name, description, enabled,
           provider, webhook_url as "webhookUrl", secret_ref as "secretRef",
           reminder_types as "reminderTypes", reminder_channels as "reminderChannels",
           min_reminder_level as "minReminderLevel", tenant_ids as "tenantIds",
           timeout_sec as "timeoutSec", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.platform_dunning_notification_channels
    where ($1::text = '' or enabled = ($1 = 'true'))
      and ($2::text = '' or provider = $2)
    order by enabled desc, min_reminder_level asc, channel_code asc
    limit $3
    `,
    [enabled, provider, limit],
  );

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

export async function upsertPlatformDunningNotificationChannelRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:notification');

  const body = await readJsonBody(ctx);
  const channelCode = platformAuditNotificationChannelCode(requiredString(body, 'channelCode'));
  const name = requiredString(body, 'name');
  const provider = platformAuditNotificationProviderFrom(optionalString(body, 'provider'));
  const webhookUrl = validatePlatformWebhookUrl(requiredString(body, 'webhookUrl'));
  const reminderTypes = platformDunningReminderTypes(body.reminderTypes);
  const reminderChannels = platformDunningReminderChannels(body.reminderChannels);
  const tenantIds = optionalUuidList(body.tenantIds, 'tenantIds', 200);
  const minReminderLevel = numberBetween(body.minReminderLevel, 1, 1, 20);
  const timeoutSec = numberBetween(body.timeoutSec, 10, 1, 60);
  const description = optionalString(body, 'description') || null;
  const metadata = objectValue(body.metadata);
  const secretKey = `platform_dunning_${channelCode}`;
  let secretRef = optionalString(body, 'secretRef') || null;
  const secret = typeof body.secret === 'string' && body.secret.trim() ? body.secret.trim() : '';
  if (secret) secretRef = platformSecretRef('webhook', secretKey);
  if (secretRef && !parsePlatformSecretRef(secretRef)) {
    throw new HttpError(400, 'secretRef is invalid', 'INVALID_SECRET_REF');
  }

  const item = await transaction(async client => {
    if (secret) {
      await client.query(
        `
        insert into app_private.platform_secrets (
          secret_scope, secret_key, secret_value, provider, last_rotated_at
        )
        values ('webhook', $1, $2, $3, now())
        on conflict (secret_scope, secret_key)
        do update set secret_value = excluded.secret_value,
                      provider = excluded.provider,
                      last_rotated_at = now(),
                      updated_at = now()
        `,
        [secretKey, secret, provider],
      );
    }

    const result = await client.query<Record<string, unknown>>(
      `
      insert into public.platform_dunning_notification_channels (
        channel_code, name, description, enabled, provider, webhook_url,
        secret_ref, reminder_types, reminder_channels, min_reminder_level,
        tenant_ids, timeout_sec, metadata
      )
      values (
        $1, $2, $3, $4, $5, $6,
        $7, $8::text[], $9::text[], $10,
        $11::uuid[], $12, $13::jsonb
      )
      on conflict (channel_code)
      do update set name = excluded.name,
                    description = excluded.description,
                    enabled = excluded.enabled,
                    provider = excluded.provider,
                    webhook_url = excluded.webhook_url,
                    secret_ref = excluded.secret_ref,
                    reminder_types = excluded.reminder_types,
                    reminder_channels = excluded.reminder_channels,
                    min_reminder_level = excluded.min_reminder_level,
                    tenant_ids = excluded.tenant_ids,
                    timeout_sec = excluded.timeout_sec,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id, channel_code as "channelCode", name, description, enabled,
                provider, webhook_url as "webhookUrl", secret_ref as "secretRef",
                reminder_types as "reminderTypes", reminder_channels as "reminderChannels",
                min_reminder_level as "minReminderLevel", tenant_ids as "tenantIds",
                timeout_sec as "timeoutSec", metadata,
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        channelCode,
        name,
        description,
        body.enabled !== false,
        provider,
        webhookUrl,
        secretRef,
        reminderTypes,
        reminderChannels,
        minReminderLevel,
        tenantIds,
        timeoutSec,
        JSON.stringify(metadata),
      ],
    );
    const saved = result.rows[0];
    await recordPlatformAudit(client, ctx, 'platform.invoice.dunning_notification_channel_upserted', 'platform_dunning_notification_channel', String(saved.id), {
      channelCode,
      provider,
      enabled: body.enabled !== false,
      reminderTypes,
      reminderChannels,
      minReminderLevel,
      tenantIdCount: tenantIds.length,
      secretRefSet: Boolean(secretRef),
      secretRotated: Boolean(secret),
      webhook: safeWebhookInfo(webhookUrl),
    });
    return saved;
  });

  return { item: channelResponse(item) };
}

export async function platformDunningNotificationEventsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:notification');

  const channelId = listQuery(ctx, 'channelId');
  const reminderId = listQuery(ctx, 'reminderId');
  const invoiceId = listQuery(ctx, 'invoiceId');
  const tenantId = listQuery(ctx, 'tenantId');
  const status = listQuery(ctx, 'status');
  const provider = listQuery(ctx, 'provider');
  const limit = intParam(ctx, 'limit', 100, 500);
  if (channelId && !UUID_RE.test(channelId)) throw new HttpError(400, 'channelId is invalid', 'INVALID_UUID');
  if (reminderId && !UUID_RE.test(reminderId)) throw new HttpError(400, 'reminderId is invalid', 'INVALID_UUID');
  if (invoiceId && !UUID_RE.test(invoiceId)) throw new HttpError(400, 'invoiceId is invalid', 'INVALID_UUID');
  if (tenantId && !UUID_RE.test(tenantId)) throw new HttpError(400, 'tenantId is invalid', 'INVALID_UUID');
  if (status && !PLATFORM_AUDIT_NOTIFICATION_EVENT_STATUSES.has(status)) {
    throw new HttpError(400, 'status is invalid', 'INVALID_NOTIFICATION_EVENT_STATUS');
  }
  if (provider && !PLATFORM_AUDIT_NOTIFICATION_PROVIDERS.has(provider)) {
    throw new HttpError(400, 'provider is invalid', 'INVALID_NOTIFICATION_PROVIDER');
  }

  const items = await query<Record<string, unknown>>(
    `
    select e.id, e.channel_id as "channelId", c.channel_code as "channelCode",
           c.name as "channelName", e.reminder_id as "reminderId",
           e.invoice_id as "invoiceId", i.invoice_no as "invoiceNo",
           e.tenant_id as "tenantId", t.slug::text as "tenantSlug",
           t.name as "tenantName", e.provider, e.status, e.attempts,
           e.scheduled_at as "scheduledAt", e.next_attempt_at as "nextAttemptAt",
           e.last_attempt_at as "lastAttemptAt", e.sent_at as "sentAt",
           e.last_error as "lastError", e.last_http_code as "lastHttpCode",
           e.last_response_summary as "lastResponseSummary",
           e.request_payload as "requestPayload", e.metadata,
           r.reminder_type as "reminderType", r.channel as "reminderChannel",
           r.reminder_level as "reminderLevel", r.reminder_date as "reminderDate",
           r.due_date as "dueDate", r.balance_cents_snapshot as "balanceCentsSnapshot",
           r.message as "reminderMessage", i.status as "invoiceStatus",
           i.balance_cents as "invoiceBalanceCents",
           i.total_cents as "invoiceTotalCents",
           e.created_at as "createdAt", e.updated_at as "updatedAt"
    from public.platform_dunning_notification_events e
    join public.platform_dunning_notification_channels c on c.id = e.channel_id
    join public.tenant_invoice_reminders r on r.id = e.reminder_id
    join public.tenant_invoices i on i.id = e.invoice_id
    join public.tenants t on t.id = e.tenant_id
    where ($1::uuid is null or e.channel_id = $1::uuid)
      and ($2::uuid is null or e.reminder_id = $2::uuid)
      and ($3::uuid is null or e.invoice_id = $3::uuid)
      and ($4::uuid is null or e.tenant_id = $4::uuid)
      and ($5::text = '' or e.status = $5)
      and ($6::text = '' or e.provider = $6)
    order by
      case e.status when 'pending' then 1 when 'retrying' then 2 when 'processing' then 3 when 'failed' then 4 else 5 end,
      e.created_at desc
    limit $7
    `,
    [channelId || null, reminderId || null, invoiceId || null, tenantId || null, status, provider, limit],
  );

  return {
    items: items.map(item => ({
      ...item,
      requestPayload: redactAuditAlertValue(item.requestPayload),
      lastError: truncate(item.lastError),
      lastResponseSummary: truncate(item.lastResponseSummary),
    })),
  };
}

export async function createTenantRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:tenant:write');

  const body = await readJsonBody(ctx);
  const slug = normalizeSlug(requiredString(body, 'slug'));
  const name = requiredString(body, 'name');
  if (!slug) throw new HttpError(400, 'slug is invalid', 'INVALID_SLUG');

  const planCode = optionalString(body, 'planCode') || 'starter_yearly';
  const mode = optionalString(body, 'mode') || 'saas';
  const billingStatus = optionalString(body, 'billingStatus') || 'trial';
  const status = optionalString(body, 'status') || 'active';
  const legalName = optionalString(body, 'legalName') || null;
  const primaryHost = optionalString(body, 'primaryHost');
  const billing = body.billing && typeof body.billing === 'object' ? (body.billing as Record<string, unknown>) : {};
  const billingInvoiceType = billingInvoiceTypeFrom(typeof billing.invoiceType === 'string' ? billing.invoiceType.trim() : '');
  const metadata = body.metadata && typeof body.metadata === 'object' && !Array.isArray(body.metadata) ? body.metadata : {};

  const item = await transaction(async client => {
    const planResult = await client.query<{ code: string; billing_cycle: string; base_amount_cents: number }>(
      `
      select code, billing_cycle, base_amount_cents
      from public.platform_saas_plans
      where code = $1 and status = 'active'
      limit 1
      `,
      [planCode],
    );
    const plan = planResult.rows[0];
    if (!plan) throw new HttpError(404, 'SaaS plan not found', 'SAAS_PLAN_NOT_FOUND');

    const tenantResult = await client.query(
      `
      insert into public.tenants (slug, name, legal_name, status, mode, billing_status, metadata)
      values ($1, $2, $3, $4, $5, $6, $7::jsonb)
      returning id, slug::text, name, legal_name as "legalName", status, mode,
                billing_status as "billingStatus", metadata, created_at as "createdAt"
      `,
      [slug, name, legalName, status, mode, billingStatus, JSON.stringify(metadata)],
    );
    const tenant = tenantResult.rows[0];

    await client.query(
      `
      insert into public.tenant_branding (tenant_id, brand_name, short_name, slogan)
      values ($1, $2, $3, $4)
      `,
      [tenant.id, optionalString(body, 'brandName') || name, optionalString(body, 'shortName') || name, optionalString(body, 'slogan') || null],
    );

    await client.query(
      `
      insert into public.tenant_settings (tenant_id, feature_flags, admin_feature_flags, public_config)
      values ($1, $2::jsonb, $3::jsonb, $4::jsonb)
      `,
      [
        tenant.id,
        jsonBodyValue(body.featureFlags || {}),
        jsonBodyValue(body.adminFeatureFlags || {}),
        jsonBodyValue(body.publicConfig || {}),
      ],
    );

    await client.query(
      `
      insert into public.tenant_billing_profiles (
        tenant_id, billing_name, tax_id, contact_name, contact_phone,
        contact_email, billing_address, invoice_title, invoice_type, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::jsonb)
      `,
      [
        tenant.id,
        typeof billing.billingName === 'string' ? billing.billingName : legalName || name,
        typeof billing.taxId === 'string' ? billing.taxId : null,
        typeof billing.contactName === 'string' ? billing.contactName : null,
        typeof billing.contactPhone === 'string' ? billing.contactPhone : null,
        typeof billing.contactEmail === 'string' ? billing.contactEmail : null,
        typeof billing.billingAddress === 'string' ? billing.billingAddress : null,
        typeof billing.invoiceTitle === 'string' ? billing.invoiceTitle : legalName || name,
        billingInvoiceType,
        jsonBodyValue(billing.metadata),
      ],
    );

    if (primaryHost) {
      const host = normalizeHost(primaryHost);
      if (host) {
        await client.query(
          `
          insert into public.tenant_domains (tenant_id, host, domain_type, status, is_primary, verification_token)
          values ($1, $2, 'custom', 'pending', true, $3)
          `,
          [tenant.id, host, `tenant-${tenant.id.slice(0, 8)}-${Math.random().toString(36).slice(2, 10)}`],
        );
      }
    }

    const startsAt = optionalString(body, 'startsAt') || new Date().toISOString();
    const expiresAt =
      optionalString(body, 'expiresAt') ||
      (plan.billing_cycle === 'yearly'
        ? new Date(new Date(startsAt).getTime() + 365 * 24 * 60 * 60 * 1000).toISOString()
        : null);

    await client.query(
      `
      insert into public.tenant_subscriptions (
        tenant_id, plan_code, status, starts_at, expires_at, billing_cycle, amount_cents, metadata
      )
      values ($1, $2, $3, $4::timestamptz, $5::timestamptz, $6, $7, $8::jsonb)
      `,
      [
        tenant.id,
        plan.code,
        billingStatus === 'trial' ? 'trial' : 'active',
        startsAt,
        expiresAt,
        plan.billing_cycle,
        centsFrom(body.amountCents, plan.base_amount_cents),
        JSON.stringify({ source: 'platform-admin:createTenant' }),
      ],
    );

    await recordPlatformAudit(client, ctx, 'platform.tenant.created', 'tenant', tenant.id, {
      slug,
      name,
      legalName,
      planCode: plan.code,
      billingStatus,
      primaryHost: primaryHost ? normalizeHost(primaryHost) : null,
    }, tenant.id);

    return tenant;
  });

  return { item };
}

export async function updateTenantStatusRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:tenant:status');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const status = optionalString(body, 'status');
  const billingStatus = optionalString(body, 'billingStatus');
  if (!status && !billingStatus) throw new HttpError(400, 'status or billingStatus is required', 'REQUIRED_FIELD');

  const item = await transaction(async client => {
    const result = await client.query(
      `
      update public.tenants
      set status = coalesce(nullif($2, ''), status),
          billing_status = coalesce(nullif($3, ''), billing_status),
          metadata = metadata || $4::jsonb,
          updated_at = now()
      where id = $1
      returning id, slug::text, name, status, billing_status as "billingStatus",
                metadata, updated_at as "updatedAt"
      `,
      [tenantId, status, billingStatus, jsonBodyValue({ statusReason: optionalString(body, 'reason') || null })],
    );

    if (!result.rows[0]) throw new HttpError(404, 'Tenant not found', 'TENANT_NOT_FOUND');
    await recordPlatformAudit(client, ctx, 'platform.tenant.status_updated', 'tenant', tenantId, {
      status: status || null,
      billingStatus: billingStatus || null,
      reason: optionalString(body, 'reason') || null,
    }, tenantId);
    return result.rows[0];
  });

  return { item };
}

export async function upsertBillingProfileRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:tenant:billing_profile');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const invoiceType = billingInvoiceTypeFrom(optionalString(body, 'invoiceType'));

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.tenant_billing_profiles (
        tenant_id, billing_name, tax_id, contact_name, contact_phone, contact_email,
        billing_address, invoice_title, invoice_type, bank_name, bank_account_masked, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12::jsonb)
      on conflict (tenant_id)
      do update set billing_name = excluded.billing_name,
                    tax_id = excluded.tax_id,
                    contact_name = excluded.contact_name,
                    contact_phone = excluded.contact_phone,
                    contact_email = excluded.contact_email,
                    billing_address = excluded.billing_address,
                    invoice_title = excluded.invoice_title,
                    invoice_type = excluded.invoice_type,
                    bank_name = excluded.bank_name,
                    bank_account_masked = excluded.bank_account_masked,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning tenant_id as "tenantId", billing_name as "billingName", tax_id as "taxId",
                contact_name as "contactName", contact_phone as "contactPhone",
                contact_email as "contactEmail", billing_address as "billingAddress",
                invoice_title as "invoiceTitle", invoice_type as "invoiceType",
                bank_name as "bankName", bank_account_masked as "bankAccountMasked",
                metadata, updated_at as "updatedAt"
      `,
      [
        tenantId,
        optionalString(body, 'billingName') || null,
        optionalString(body, 'taxId') || null,
        optionalString(body, 'contactName') || null,
        optionalString(body, 'contactPhone') || null,
        optionalString(body, 'contactEmail') || null,
        optionalString(body, 'billingAddress') || null,
        optionalString(body, 'invoiceTitle') || null,
        invoiceType,
        optionalString(body, 'bankName') || null,
        optionalString(body, 'bankAccountMasked') || null,
        jsonBodyValue(body.metadata),
      ],
    );

    await recordPlatformAudit(client, ctx, 'platform.tenant.billing_profile_upserted', 'tenant_billing_profile', tenantId, {
      billingName: optionalString(body, 'billingName') || null,
      contactName: optionalString(body, 'contactName') || null,
      contactPhoneSet: Boolean(optionalString(body, 'contactPhone')),
      contactEmailSet: Boolean(optionalString(body, 'contactEmail')),
      invoiceType,
      bankAccountMaskedSet: Boolean(optionalString(body, 'bankAccountMasked')),
    }, tenantId);

    return result.rows[0];
  });

  return { item };
}

export async function createSubscriptionRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:write');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const planCode = requiredString(body, 'planCode');

  const plan = await queryOne<{ code: string; billingCycle: string; baseAmountCents: number }>(
    `
    select code, billing_cycle as "billingCycle", base_amount_cents as "baseAmountCents"
    from public.platform_saas_plans
    where code = $1 and status = 'active'
    limit 1
    `,
    [planCode],
  );
  if (!plan) throw new HttpError(404, 'SaaS plan not found', 'SAAS_PLAN_NOT_FOUND');

  const startsAt = optionalString(body, 'startsAt') || new Date().toISOString();
  const expiresAt =
    optionalString(body, 'expiresAt') ||
    (plan.billingCycle === 'yearly' ? new Date(new Date(startsAt).getTime() + 365 * 24 * 60 * 60 * 1000).toISOString() : null);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.tenant_subscriptions (
        tenant_id, plan_code, status, starts_at, expires_at, billing_cycle, amount_cents, metadata
      )
      values ($1, $2, $3, $4::timestamptz, $5::timestamptz, $6, $7, $8::jsonb)
      returning id, tenant_id as "tenantId", plan_code as "planCode", status,
                starts_at as "startsAt", expires_at as "expiresAt",
                billing_cycle as "billingCycle", amount_cents as "amountCents",
                metadata, created_at as "createdAt"
      `,
      [
        tenantId,
        plan.code,
        optionalString(body, 'status') || 'active',
        startsAt,
        expiresAt,
        optionalString(body, 'billingCycle') || plan.billingCycle,
        centsFrom(body.amountCents, plan.baseAmountCents),
        jsonBodyValue(body.metadata),
      ],
    );

    await client.query(
      `
      update public.tenants
      set billing_status = case when $2 = 'active' then 'active' else billing_status end,
          updated_at = now()
      where id = $1
      `,
      [tenantId, optionalString(body, 'status') || 'active'],
    );

    return result.rows[0];
  });

  return { item };
}

export async function tenantInvoicesRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:read');

  const tenantId = ctx.url.searchParams.get('tenantId') || '';
  const status = listQuery(ctx, 'status');
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query(
    `
    select i.id, i.tenant_id as "tenantId", t.slug::text as "tenantSlug", t.name as "tenantName",
           i.invoice_no as "invoiceNo", i.invoice_type as "invoiceType", i.status,
           i.currency, i.total_cents as "totalCents", i.paid_cents as "paidCents",
           i.balance_cents as "balanceCents", i.billing_period_start as "billingPeriodStart",
           i.billing_period_end as "billingPeriodEnd", i.due_date as "dueDate",
           i.issued_at as "issuedAt", i.paid_at as "paidAt", i.note,
           i.created_at as "createdAt", i.updated_at as "updatedAt"
    from public.tenant_invoices i
    join public.tenants t on t.id = i.tenant_id
    where ($1::uuid is null or i.tenant_id = $1::uuid)
      and ($2::text = '' or i.status = $2)
    order by i.created_at desc
    limit $3
    `,
    [tenantId || null, status, limit],
  );

  return { items };
}

async function createInvoiceRecord(input: CreateInvoiceInput) {
  return transaction(async client => createInvoiceRecordWithClient(client, input));
}

export async function createInvoiceRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:write');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const items = normalizeInvoiceItems(body.items);

  const item = await createInvoiceRecord({
    tenantId,
    items,
    invoiceNo: optionalString(body, 'invoiceNo') || undefined,
    invoiceType: optionalString(body, 'invoiceType') || 'subscription',
    status: optionalString(body, 'status') || 'issued',
    currency: optionalString(body, 'currency') || 'CNY',
    discountCents: centsFrom(body.discountCents, 0),
    taxCents: centsFrom(body.taxCents, 0),
    billingPeriodStart: optionalString(body, 'billingPeriodStart') || null,
    billingPeriodEnd: optionalString(body, 'billingPeriodEnd') || null,
    dueDate: optionalString(body, 'dueDate') || null,
    note: optionalString(body, 'note') || null,
    metadata: body.metadata && typeof body.metadata === 'object' && !Array.isArray(body.metadata) ? (body.metadata as Record<string, unknown>) : {},
  });

  return { item };
}

export async function confirmInvoicePaymentRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:payment');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const invoiceId = requiredString(body, 'invoiceId');
  const amountCents = centsFrom(body.amountCents, -1);
  if (amountCents <= 0) throw new HttpError(400, 'amountCents must be positive', 'INVALID_AMOUNT');

  const item = await transaction(async client => {
    const invoiceResult = await client.query<{ id: string; balance_cents: number }>(
      `
      select id, balance_cents
      from public.tenant_invoices
      where tenant_id = $1 and id = $2
      limit 1
      for update
      `,
      [tenantId, invoiceId],
    );
    const invoice = invoiceResult.rows[0];
    if (!invoice) throw new HttpError(404, 'Invoice not found', 'INVOICE_NOT_FOUND');

    if (amountCents > invoice.balance_cents && invoice.balance_cents > 0) {
      throw new HttpError(409, 'Payment amount exceeds invoice balance', 'PAYMENT_AMOUNT_EXCEEDS_BALANCE');
    }

    const paymentNo = optionalString(body, 'paymentNo') || createInvoiceNo('PAY');
    await client.query(
      `
      insert into public.tenant_invoice_payments (
        tenant_id, invoice_id, payment_no, provider, method, status,
        amount_cents, paid_at, provider_trade_no, raw_payload
      )
      values ($1, $2, $3, $4, $5, 'paid', $6, coalesce($7::timestamptz, now()), $8, $9::jsonb)
      `,
      [
        tenantId,
        invoiceId,
        paymentNo,
        optionalString(body, 'provider') || 'manual',
        optionalString(body, 'method') || 'manual',
        amountCents,
        optionalString(body, 'paidAt') || null,
        optionalString(body, 'providerTradeNo') || null,
        jsonBodyValue(body.rawPayload || body),
      ],
    );

    const updatedInvoice = await recalculateInvoiceTotals(client, invoiceId);

    if (updatedInvoice?.status === 'paid') {
      await client.query(
        `
        update public.tenants
        set billing_status = 'active', updated_at = now()
        where id = $1
        `,
        [tenantId],
      );

      await client.query(
        `
        update public.tenant_subscriptions
        set status = 'active', updated_at = now()
        where tenant_id = $1
          and status in ('trial', 'past_due')
          and id = (
            select id
            from public.tenant_subscriptions
            where tenant_id = $1
            order by created_at desc
            limit 1
          )
        `,
        [tenantId],
      );
    }

    await recordPlatformAudit(client, ctx, 'platform.invoice.payment_confirmed', 'tenant_invoice', invoiceId, {
      tenantId,
      invoiceId,
      paymentNo,
      provider: optionalString(body, 'provider') || 'manual',
      method: optionalString(body, 'method') || 'manual',
      amountCents,
      providerTradeNoSet: Boolean(optionalString(body, 'providerTradeNo')),
      invoiceStatus: updatedInvoice?.status || null,
    }, tenantId);

    return updatedInvoice;
  });

  return { item };
}

export async function processOverdueInvoicesRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:dunning');

  const body = await readJsonBody(ctx);
  const dryRun = booleanFrom(body.dryRun, false);
  const limit = Math.min(Math.max(Number(body.limit || 100), 1), 500);
  const channel = optionalString(body, 'channel') || 'internal';
  if (!['manual', 'internal', 'sms', 'email', 'wechat', 'crm'].includes(channel)) {
    throw new HttpError(400, 'channel is invalid', 'INVALID_REMINDER_CHANNEL');
  }

  const session = currentSessionFromContext(ctx);
  const item = await transaction(async client => {
    const result = await processOverduePlatformInvoices(client, {
      actorUserId: session?.id || null,
      channel,
      dryRun,
      limit,
    });

    if (!dryRun) {
      await recordPlatformAudit(client, ctx, 'platform.invoice.overdue_batch_processed', 'tenant_invoice_batch', null, {
        processed: result.processed,
        markedOverdue: result.markedOverdue,
        reminderCreated: result.reminderCreated,
        skippedReminder: result.skippedReminder,
        channel,
      });
    }

    return result;
  });

  return { item };
}

export async function invoiceRemindersRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:dunning');

  const tenantId = ctx.url.searchParams.get('tenantId') || '';
  const invoiceId = ctx.url.searchParams.get('invoiceId') || '';
  const status = listQuery(ctx, 'status');
  const reminderType = listQuery(ctx, 'reminderType');
  const limit = intParam(ctx, 'limit', 100, 500);
  if (tenantId && !UUID_RE.test(tenantId)) throw new HttpError(400, 'tenantId is invalid', 'INVALID_UUID');
  if (invoiceId && !UUID_RE.test(invoiceId)) throw new HttpError(400, 'invoiceId is invalid', 'INVALID_UUID');

  const items = await query(
    `
    select r.id, r.tenant_id as "tenantId", t.slug::text as "tenantSlug", t.name as "tenantName",
           r.invoice_id as "invoiceId", i.invoice_no as "invoiceNo",
           r.reminder_type as "reminderType", r.channel, r.status,
           r.reminder_date as "reminderDate", r.reminder_level as "reminderLevel",
           r.due_date as "dueDate", r.balance_cents_snapshot as "balanceCentsSnapshot",
           r.message, r.metadata, r.sent_at as "sentAt", r.acknowledged_at as "acknowledgedAt",
           r.created_at as "createdAt", r.updated_at as "updatedAt"
    from public.tenant_invoice_reminders r
    join public.tenants t on t.id = r.tenant_id
    join public.tenant_invoices i on i.id = r.invoice_id
    where ($1::uuid is null or r.tenant_id = $1::uuid)
      and ($2::uuid is null or r.invoice_id = $2::uuid)
      and ($3::text = '' or r.status = $3)
      and ($4::text = '' or r.reminder_type = $4)
    order by r.reminder_date desc, r.created_at desc
    limit $5
    `,
    [tenantId || null, invoiceId || null, status, reminderType, limit],
  );

  return { items };
}

export async function recordUsageRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:usage:write');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const metricKey = requiredString(body, 'metricKey');
  const metricValue = quantityFrom(body.metricValue, 0);
  const periodStart = dateTextFrom(body.periodStart, 'periodStart');
  const periodEnd = dateTextFrom(body.periodEnd, 'periodEnd');
  assertDateRange(periodStart, periodEnd);

  const item = await queryOne(
    `
    insert into public.tenant_usage_records (
      tenant_id, metric_key, metric_value, period_start, period_end, metadata
    )
    values ($1, $2, $3, $4::date, $5::date, $6::jsonb)
    returning id, tenant_id as "tenantId", metric_key as "metricKey",
              metric_value as "metricValue", period_start as "periodStart",
              period_end as "periodEnd", metadata, created_at as "createdAt"
    `,
    [tenantId, metricKey, metricValue, periodStart, periodEnd, jsonBodyValue(body.metadata)],
  );

  return { item };
}

export async function tenantUsageRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:usage:read');

  const tenantId = ctx.url.searchParams.get('tenantId') || '';
  const limit = intParam(ctx, 'limit', 100, 500);

  const items = await query(
    `
    select u.id, u.tenant_id as "tenantId", t.slug::text as "tenantSlug", t.name as "tenantName",
           u.metric_key as "metricKey", u.metric_value as "metricValue",
           u.period_start as "periodStart", u.period_end as "periodEnd",
           u.metadata, u.created_at as "createdAt"
    from public.tenant_usage_records u
    join public.tenants t on t.id = u.tenant_id
    where ($1::uuid is null or u.tenant_id = $1::uuid)
    order by u.period_end desc, u.created_at desc
    limit $2
    `,
    [tenantId || null, limit],
  );

  return { items };
}

export async function usageOverageInvoiceCandidatesRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:read');

  const tenantIds = optionalUuidList(ctx.url.searchParams.get('tenantIds'), 'tenantIds');
  const periodStart = dateTextFrom(ctx.url.searchParams.get('periodStart'), 'periodStart');
  const periodEnd = dateTextFrom(ctx.url.searchParams.get('periodEnd'), 'periodEnd');
  assertDateRange(periodStart, periodEnd);
  const includeExisting = listQuery(ctx, 'includeExisting') === 'true';
  const includeZero = listQuery(ctx, 'includeZero') === 'true';
  const limit = intParam(ctx, 'limit', 100, 500);
  const items = await loadUsageOverageInvoiceCandidates(pool, { tenantIds, periodStart, periodEnd, includeExisting, includeZero, limit });

  return { items };
}

async function subscriptionInvoiceCandidateQuery(params: {
  tenantIds: string[];
  subscriptionIds: string[];
  daysAhead: number;
  includeExisting: boolean;
  limit: number;
}) {
  return query<{
    tenantId: string;
    tenantSlug: string;
    tenantName: string;
    billingStatus: string;
    subscriptionId: string;
    planCode: string;
    planName: string | null;
    status: string;
    startsAt: string | null;
    expiresAt: string | null;
    billingCycle: string | null;
    amountCents: number;
    existingInvoiceId: string | null;
    existingInvoiceNo: string | null;
    existingInvoiceStatus: string | null;
  }>(
    `
    select t.id as "tenantId", t.slug::text as "tenantSlug", t.name as "tenantName",
           t.billing_status as "billingStatus",
           s.id as "subscriptionId", s.plan_code as "planCode",
           p.name as "planName", s.status, s.starts_at as "startsAt",
           s.expires_at as "expiresAt", s.billing_cycle as "billingCycle",
           s.amount_cents as "amountCents",
           existing.id as "existingInvoiceId",
           existing.invoice_no as "existingInvoiceNo",
           existing.status as "existingInvoiceStatus"
    from public.tenant_subscriptions s
    join public.tenants t on t.id = s.tenant_id
    left join public.platform_saas_plans p on p.code = s.plan_code
    left join lateral (
      select id, invoice_no, status
      from public.tenant_invoices i
      where i.tenant_id = s.tenant_id
        and i.invoice_type = 'subscription'
        and i.status <> 'void'
        and i.metadata->>'subscriptionId' = s.id::text
      order by i.created_at desc
      limit 1
    ) existing on true
    where t.status = 'active'
      and s.status in ('trial', 'active', 'past_due')
      and ($1::uuid[] = '{}'::uuid[] or s.tenant_id = any($1::uuid[]))
      and ($2::uuid[] = '{}'::uuid[] or s.id = any($2::uuid[]))
      and (
        s.expires_at is null
        or s.expires_at <= now() + ($3::integer * interval '1 day')
      )
      and ($4::boolean = true or existing.id is null)
    order by s.expires_at asc nulls last, t.created_at asc
    limit $5
    `,
    [params.tenantIds, params.subscriptionIds, params.daysAhead, params.includeExisting, params.limit],
  );
}

export async function subscriptionInvoiceCandidatesRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:read');

  const tenantIds = optionalUuidList(ctx.url.searchParams.get('tenantIds'), 'tenantIds');
  const subscriptionIds = optionalUuidList(ctx.url.searchParams.get('subscriptionIds'), 'subscriptionIds');
  const daysAhead = daysAheadFrom(ctx.url.searchParams.get('daysAhead'), 45);
  const includeExisting = listQuery(ctx, 'includeExisting') === 'true';
  const limit = intParam(ctx, 'limit', 100, 500);
  const items = await subscriptionInvoiceCandidateQuery({ tenantIds, subscriptionIds, daysAhead, includeExisting, limit });

  return {
    items: items.map(item => ({
      ...item,
      hasExistingInvoice: Boolean(item.existingInvoiceId),
    })),
  };
}

export async function createTenantInvoiceFromSubscriptionRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:write');

  const body = await readJsonBody(ctx);
  const tenantId = requiredString(body, 'tenantId');
  const subscriptionId = optionalString(body, 'subscriptionId');

  const item = await transaction(async client => {
    const subscriptionResult = await client.query<{
      id: string;
      planCode: string;
      amountCents: number;
      startsAt: string | null;
      expiresAt: string | null;
    }>(
      `
      select id, plan_code as "planCode", amount_cents as "amountCents",
             starts_at as "startsAt", expires_at as "expiresAt"
      from public.tenant_subscriptions
      where tenant_id = $1
        and ($2::uuid is null or id = $2::uuid)
      order by created_at desc
      limit 1
      for update
      `,
      [tenantId, subscriptionId || null],
    );
    const subscription = subscriptionResult.rows[0];
    if (!subscription) throw new HttpError(404, 'Subscription not found', 'SUBSCRIPTION_NOT_FOUND');

    const planResult = await client.query<{ name: string }>(
      `
      select name from public.platform_saas_plans
      where code = $1
      limit 1
      `,
      [subscription.planCode],
    );
    const plan = planResult.rows[0];

    const existing = await client.query<{ id: string; invoiceNo: string; status: string }>(
      `
      select id, invoice_no as "invoiceNo", status
      from public.tenant_invoices
      where tenant_id = $1
        and invoice_type = 'subscription'
        and status <> 'void'
        and metadata->>'subscriptionId' = $2
      order by created_at desc
      limit 1
      `,
      [tenantId, subscription.id],
    );
    if (existing.rows[0]) {
      throw new HttpError(409, 'Invoice already exists for this subscription', 'SUBSCRIPTION_INVOICE_EXISTS');
    }

    const invoice = await createInvoiceRecordWithClient(client, {
      tenantId,
      invoiceType: 'subscription',
      status: tenantInvoiceStatusFrom(optionalString(body, 'status')),
      dueDate: optionalString(body, 'dueDate') || null,
      billingPeriodStart: toDateText(subscription.startsAt),
      billingPeriodEnd: toDateText(subscription.expiresAt),
      note: optionalString(body, 'note') || null,
      metadata: { source: 'subscription', subscriptionId: subscription.id },
      items: [
        {
          itemType: 'subscription',
          description: `${plan?.name || subscription.planCode} ${subscription.planCode}`,
          quantity: 1,
          unitAmountCents: subscription.amountCents,
          metadata: { subscriptionId: subscription.id, planCode: subscription.planCode },
        },
      ],
    });

    await recordPlatformAudit(client, ctx, 'platform.invoice.subscription_created', 'tenant_invoice', invoice.id, {
      tenantId,
      subscriptionId: subscription.id,
      planCode: subscription.planCode,
      invoiceNo: invoice.invoiceNo,
      source: 'single',
    }, tenantId);
    return invoice;
  });

  return { item };
}

export async function createTenantInvoicesBatchFromSubscriptionsRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:write');

  const body = await readJsonBody(ctx);
  const tenantIds = optionalUuidList(body.tenantIds, 'tenantIds');
  const requestedSubscriptionIds = optionalUuidList(body.subscriptionIds, 'subscriptionIds');
  const daysAhead = daysAheadFrom(body.daysAhead, 45);
  const status = tenantInvoiceStatusFrom(optionalString(body, 'status'));
  const dueDate = optionalString(body, 'dueDate') || null;
  const note = optionalString(body, 'note') || null;
  const dryRun = booleanFrom(body.dryRun, false);
  const limit = Math.min(Math.max(requestedSubscriptionIds.length || 0, 100), 500);

  const candidates = await subscriptionInvoiceCandidateQuery({
    tenantIds,
    subscriptionIds: requestedSubscriptionIds,
    daysAhead,
    includeExisting: false,
    limit,
  });
  const scopedCandidates = candidates;

  if (!scopedCandidates.length) {
    return { item: { dryRun, createdCount: 0, skippedCount: 0, items: [] } };
  }

  if (dryRun) {
    return {
      item: {
        dryRun: true,
        createdCount: 0,
        skippedCount: 0,
        items: scopedCandidates.map(item => ({
          ...item,
          hasExistingInvoice: false,
          wouldCreate: true,
        })),
      },
    };
  }

  const result = await transaction(async client => {
    const created: unknown[] = [];
    const skipped: unknown[] = [];

    for (const candidate of scopedCandidates) {
      const lock = await client.query(
        `
        select id
        from public.tenant_subscriptions
        where id = $1
        for update
        `,
        [candidate.subscriptionId],
      );
      if (!lock.rows[0]) {
        skipped.push({ subscriptionId: candidate.subscriptionId, reason: 'SUBSCRIPTION_NOT_FOUND' });
        continue;
      }

      const existing = await client.query<{ id: string; invoiceNo: string; status: string }>(
        `
        select id, invoice_no as "invoiceNo", status
        from public.tenant_invoices
        where tenant_id = $1
          and invoice_type = 'subscription'
          and status <> 'void'
          and metadata->>'subscriptionId' = $2
        order by created_at desc
        limit 1
        `,
        [candidate.tenantId, candidate.subscriptionId],
      );
      if (existing.rows[0]) {
        skipped.push({
          tenantId: candidate.tenantId,
          subscriptionId: candidate.subscriptionId,
          reason: 'SUBSCRIPTION_INVOICE_EXISTS',
          invoiceId: existing.rows[0].id,
          invoiceNo: existing.rows[0].invoiceNo,
        });
        continue;
      }

      const invoice = await createInvoiceRecordWithClient(client, {
        tenantId: candidate.tenantId,
        invoiceType: 'subscription',
        status,
        dueDate,
        billingPeriodStart: toDateText(candidate.startsAt),
        billingPeriodEnd: toDateText(candidate.expiresAt),
        note,
        metadata: {
          source: 'subscription_batch',
          subscriptionId: candidate.subscriptionId,
          tenantSlug: candidate.tenantSlug,
        },
        items: [
          {
            itemType: 'subscription',
            description: `${candidate.planName || candidate.planCode} ${candidate.planCode}`,
            quantity: 1,
            unitAmountCents: Number(candidate.amountCents || 0),
            metadata: { subscriptionId: candidate.subscriptionId, planCode: candidate.planCode },
          },
        ],
      });
      created.push({ ...candidate, invoice });
    }

    await recordPlatformAudit(client, ctx, 'platform.invoice.subscription_batch_created', 'tenant_invoice_batch', null, {
      createdCount: created.length,
      skippedCount: skipped.length,
      tenantIds,
      subscriptionIds: requestedSubscriptionIds,
      daysAhead,
      dueDate,
      status,
    });

    return {
      dryRun: false,
      createdCount: created.length,
      skippedCount: skipped.length,
      items: created,
      skipped,
    };
  });

  return { item: result };
}

export async function createTenantInvoicesFromUsageOverageRoute(ctx: RequestContext) {
  await requirePlatformAdmin(ctx, 'platform:billing:write');

  const body = await readJsonBody(ctx);
  const tenantIds = optionalUuidList(body.tenantIds, 'tenantIds');
  const periodStart = dateTextFrom(body.periodStart, 'periodStart');
  const periodEnd = dateTextFrom(body.periodEnd, 'periodEnd');
  assertDateRange(periodStart, periodEnd);
  const dueDate = dateTextFrom(body.dueDate, 'dueDate', false) || null;
  const status = usageOverageInvoiceStatusFrom(optionalString(body, 'status'));
  const note = optionalString(body, 'note') || null;
  const dryRun = booleanFrom(body.dryRun, false);
  const limit = Math.min(Math.max(tenantIds.length || 0, 100), 500);

  const session = currentSessionFromContext(ctx);
  const result = await transaction(async client => processUsageOverageInvoices(client, {
    tenantIds,
    periodStart,
    periodEnd,
    dueDate,
    status,
    note,
    dryRun,
    limit,
    actorUserId: session?.id || null,
    ipAddress: requestIp(ctx),
    userAgent: getHeader(ctx.req, 'user-agent') || null,
  }));

  return { item: result };
}
