import { randomBytes } from 'node:crypto';
import type pg from 'pg';
import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, optionalString, readJsonBody, requiredString, stringParam } from '../../core/request.js';
import { query, queryOne, transaction } from '../../core/db.js';
import {
  requireTenantAdmin,
  requireTenantPermission,
  tenantPermissionCatalog,
  type TenantAdminAuth,
} from './auth.js';
import { createUserNotification } from '../notifications/service.js';

type JsonBody = Record<string, unknown>;
type SecretScope = 'payment' | 'sms' | 'oauth' | 'storage' | 'crm' | 'ai' | 'system';

const SECRET_SCOPES = new Set<SecretScope>(['payment', 'sms', 'oauth', 'storage', 'crm', 'ai', 'system']);
const PAYMENT_MODES = ['platform_collect', 'tenant_collect', 'service_provider'];
const PAYMENT_STATUSES = ['active', 'disabled', 'pending'];
const AUTH_STATUSES = ['active', 'disabled', 'testing'];
const DISCOUNT_TYPES = ['percent', 'fixed'];
const COUPON_STATUSES = ['active', 'disabled', 'archived'];
const COUPON_REDEMPTION_STATUSES = ['claimed', 'pending', 'used', 'cancelled', 'expired'];
const TENANT_MEMBER_ROLES = ['tenant_owner', 'tenant_admin', 'tenant_operator', 'teacher', 'sales', 'agent', 'student'];
const TENANT_MEMBER_STATUSES = ['active', 'invited', 'disabled'];
const ROLE_TEMPLATE_STATUSES = ['active', 'disabled', 'archived'];
const BADGE_CATEGORIES = ['learning', 'practice', 'vocabulary', 'mock_exam', 'activity', 'feedback', 'sales', 'system', 'custom'];
const BADGE_UNLOCK_TYPES = ['manual', 'auto', 'score', 'check_in', 'practice_count', 'vocabulary_mastered', 'mock_exam_score', 'feedback_resolved', 'activity_reward', 'custom'];
const BADGE_OPERATORS = ['gte', 'lte', 'eq', 'gt', 'lt'];
const THEME_MODES = ['light', 'dark', 'auto'];
const THEME_DENSITIES = ['compact', 'comfortable', 'dense'];
const THEME_PUBLIC_ASSET_KEYS = new Set([
  'logoUrl',
  'faviconUrl',
  'appIconUrl',
  'shareImageUrl',
  'loginPosterUrl',
  'splashImageUrl',
  'iconSet',
  'shareCardStyle',
]);
const THEME_TOKEN_KEYS = new Set([
  'mode',
  'primaryColor',
  'accentColor',
  'backgroundColor',
  'surfaceColor',
  'textColor',
  'mutedColor',
  'borderColor',
  'successColor',
  'warningColor',
  'dangerColor',
  'borderRadius',
  'buttonRadius',
  'fontFamily',
  'layoutDensity',
  'customCssVars',
  'icons',
]);
const THEME_URL_ASSET_KEYS = new Set([
  'logoUrl',
  'faviconUrl',
  'appIconUrl',
  'shareImageUrl',
  'loginPosterUrl',
  'splashImageUrl',
]);
const SAFE_THEME_TOKEN_PATTERN = /^[a-z0-9][a-z0-9_-]{0,31}$/i;
const SAFE_THEME_CSS_VAR_KEY_PATTERN = /^--tiku-[a-z0-9-]{1,48}$/i;
const UNSAFE_THEME_STRING_PATTERN = /(app_private\.tenant_secrets|-----BEGIN|<script|javascript:|data:text\/html|expression\s*\(|@import|url\s*\()/i;

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

function jsonArrayValue(value: unknown) {
  return JSON.stringify(Array.isArray(value) ? value : []);
}

function uuidArrayValue(value: unknown, key: string) {
  if (!Array.isArray(value)) return [];
  return value.map((item, index) => {
    const candidate = optionalUuidString(item, `${key}[${index}]`);
    if (!candidate) {
      throw new HttpError(400, `${key}[${index}] is required`, 'INVALID_UUID');
    }
    return candidate;
  });
}

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

const UUID_PATTERN = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

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

function requiredUuidString(body: JsonBody, key: string) {
  const candidate = optionalUuidString(body[key], key);
  if (!candidate) {
    throw new HttpError(400, `${key} is required`, 'REQUIRED_FIELD');
  }
  return candidate;
}

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

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

function numberValue(value: unknown, fallback: number | null = null) {
  const parsed = Number(value ?? fallback);
  return Number.isFinite(parsed) ? parsed : fallback;
}

function optionalStatus(value: unknown, allowed: string[], fallback: string) {
  const candidate = nullableString(value) || fallback;
  if (!allowed.includes(candidate)) {
    throw new HttpError(400, `Invalid status: ${candidate}`, 'INVALID_STATUS');
  }
  return candidate;
}

function optionalChoice(value: unknown, allowed: string[], fallback: string) {
  const candidate = nullableString(value) || fallback;
  if (!allowed.includes(candidate)) {
    throw new HttpError(400, `Invalid value: ${candidate}`, 'INVALID_FIELD_VALUE');
  }
  return candidate;
}

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

function optionalNumberValue(value: unknown) {
  if (value === undefined || value === null || value === '') return null;
  const parsed = Number(value);
  if (!Number.isFinite(parsed)) {
    throw new HttpError(400, 'Numeric field is invalid', 'INVALID_NUMBER');
  }
  return parsed;
}

function assertPublicConfigHasNoSecrets(value: unknown, path = 'configPublic') {
  if (!value || typeof value !== 'object') return;

  if (Array.isArray(value)) {
    value.forEach((item, index) => assertPublicConfigHasNoSecrets(item, `${path}[${index}]`));
    return;
  }

  for (const [key, child] of Object.entries(value as Record<string, unknown>)) {
    const normalized = key.toLowerCase().replace(/[-_\s]/g, '');
    const allowedSecretRef = normalized === 'secretref' || normalized.endsWith('secretref');
    const sensitiveKey =
      normalized.includes('secret') ||
      normalized.includes('password') ||
      normalized.includes('token') ||
      normalized.includes('privatekey') ||
      normalized.includes('apikey') ||
      normalized.includes('apiv3key') ||
      normalized.includes('mchkey') ||
      normalized.includes('signkey') ||
      normalized.includes('aeskey') ||
      normalized.includes('partnerkey');

    if (sensitiveKey && !allowedSecretRef) {
      throw new HttpError(
        400,
        `${path}.${key} looks sensitive. Store secrets in app_private.tenant_secrets and expose only secretRef.`,
        'PUBLIC_CONFIG_SECRET_REJECTED',
      );
    }

    assertPublicConfigHasNoSecrets(child, `${path}.${key}`);
  }
}

function publicJsonValue(value: unknown) {
  const publicConfig = objectValue(value);
  assertPublicConfigHasNoSecrets(publicConfig);
  return JSON.stringify(publicConfig);
}

function assertThemePublicString(value: unknown, path: string) {
  if (value === undefined || value === null || value === '') return;
  if (typeof value !== 'string') {
    throw new HttpError(400, `${path} must be a string`, 'INVALID_THEME_STRING');
  }
  if (UNSAFE_THEME_STRING_PATTERN.test(value)) {
    throw new HttpError(400, `${path} contains unsafe public theme content`, 'PUBLIC_CONFIG_SECRET_REJECTED');
  }
}

function assertHexColor(value: unknown, key: string) {
  if (value === undefined || value === null || value === '') return;
  if (typeof value !== 'string' || !/^#[0-9a-f]{6}$/i.test(value.trim())) {
    throw new HttpError(400, `${key} must be a #RRGGBB color`, 'INVALID_THEME_COLOR');
  }
}

function themeNumber(value: unknown, key: string) {
  if (value === undefined || value === null || value === '') return undefined;
  const parsed = Number(value);
  if (!Number.isFinite(parsed) || parsed < 0 || parsed > 32) {
    throw new HttpError(400, `${key} must be a number between 0 and 32`, 'INVALID_THEME_NUMBER');
  }
  return Math.trunc(parsed);
}

function sanitizeThemeFontFamily(value: unknown) {
  const candidate = nullableString(value);
  if (!candidate) return 'system';
  assertThemePublicString(candidate, 'theme.fontFamily');
  if (!/^[\u4e00-\u9fa5a-zA-Z0-9\s,"'-]{1,80}$/.test(candidate)) {
    throw new HttpError(400, 'fontFamily contains unsupported characters', 'INVALID_THEME_FONT');
  }
  return candidate;
}

function sanitizeThemeCssVars(value: unknown) {
  const source = objectValue(value);
  const result: Record<string, string> = {};
  for (const [key, raw] of Object.entries(source)) {
    if (!SAFE_THEME_CSS_VAR_KEY_PATTERN.test(key)) {
      throw new HttpError(400, `Invalid CSS variable key: ${key}`, 'INVALID_THEME_CSS_VAR');
    }
    assertThemePublicString(raw, `theme.customCssVars.${key}`);
    const text = String(raw).trim();
    if (text.length > 96 || /[{};]/.test(text)) {
      throw new HttpError(400, `Invalid CSS variable value: ${key}`, 'INVALID_THEME_CSS_VAR');
    }
    result[key] = text;
  }
  return result;
}

function sanitizeThemeIcons(value: unknown) {
  const source = objectValue(value);
  const result: Record<string, string> = {};
  for (const [key, raw] of Object.entries(source)) {
    if (!SAFE_THEME_TOKEN_PATTERN.test(key)) {
      throw new HttpError(400, `Invalid icon key: ${key}`, 'INVALID_THEME_ICON');
    }
    assertThemePublicString(raw, `theme.icons.${key}`);
    const text = String(raw).trim();
    if (text.startsWith('/') || /^https?:\/\//i.test(text)) {
      result[key] = sanitizeThemePublicUrl(text, `theme.icons.${key}`);
      continue;
    }
    if (!SAFE_THEME_TOKEN_PATTERN.test(text)) {
      throw new HttpError(400, `Invalid icon value: ${key}`, 'INVALID_THEME_ICON');
    }
    result[key] = text;
  }
  return result;
}

function sanitizeThemePublicUrl(value: unknown, path: string) {
  if (value === undefined || value === null || value === '') return '';
  assertThemePublicString(value, path);
  const text = String(value).trim();
  if (text.startsWith('/')) {
    if (text.startsWith('//') || text.includes('\\')) {
      throw new HttpError(400, `${path} must be an HTTPS URL or absolute public path`, 'INVALID_THEME_ASSET_URL');
    }
    return text;
  }
  let parsed: URL;
  try {
    parsed = new URL(text);
  } catch {
    throw new HttpError(400, `${path} must be an HTTPS URL or absolute public path`, 'INVALID_THEME_ASSET_URL');
  }
  const isLocalDev = parsed.protocol === 'http:' && ['localhost', '127.0.0.1', '::1'].includes(parsed.hostname);
  if (parsed.protocol !== 'https:' && !isLocalDev) {
    throw new HttpError(400, `${path} must use HTTPS`, 'INVALID_THEME_ASSET_URL');
  }
  return parsed.toString();
}

function sanitizeThemePublicToken(value: unknown, path: string) {
  if (value === undefined || value === null || value === '') return '';
  assertThemePublicString(value, path);
  const text = String(value).trim();
  if (!SAFE_THEME_TOKEN_PATTERN.test(text)) {
    throw new HttpError(400, `${path} must be a safe token`, 'INVALID_THEME_ASSET_TOKEN');
  }
  return text;
}

function sanitizeThemeTokens(value: unknown) {
  const source = objectValue(value);
  assertPublicConfigHasNoSecrets(source, 'theme');
  const theme: Record<string, unknown> = {};
  for (const [key, raw] of Object.entries(source)) {
    if (!THEME_TOKEN_KEYS.has(key)) {
      throw new HttpError(400, `Invalid theme key: ${key}`, 'INVALID_THEME_KEY');
    }
    if (key.endsWith('Color')) {
      assertHexColor(raw, key);
      theme[key] = typeof raw === 'string' ? raw.trim().toLowerCase() : raw;
      continue;
    }
    if (key === 'mode') {
      theme.mode = optionalChoice(raw, THEME_MODES, 'light');
      continue;
    }
    if (key === 'layoutDensity') {
      theme.layoutDensity = optionalChoice(raw, THEME_DENSITIES, 'comfortable');
      continue;
    }
    if (key === 'borderRadius' || key === 'buttonRadius') {
      const parsed = themeNumber(raw, key);
      if (parsed !== undefined) theme[key] = parsed;
      continue;
    }
    if (key === 'fontFamily') {
      theme.fontFamily = sanitizeThemeFontFamily(raw);
      continue;
    }
    if (key === 'customCssVars') {
      theme.customCssVars = sanitizeThemeCssVars(raw);
      continue;
    }
    if (key === 'icons') {
      theme.icons = sanitizeThemeIcons(raw);
      continue;
    }
    theme[key] = raw;
  }
  return theme;
}

function sanitizeThemePublicAssets(value: unknown) {
  const source = objectValue(value);
  assertPublicConfigHasNoSecrets(source, 'publicAssets');
  const assets: Record<string, unknown> = {};
  for (const [key, raw] of Object.entries(source)) {
    if (!THEME_PUBLIC_ASSET_KEYS.has(key)) {
      throw new HttpError(400, `Invalid theme asset key: ${key}`, 'INVALID_THEME_ASSET_KEY');
    }
    assets[key] = THEME_URL_ASSET_KEYS.has(key)
      ? sanitizeThemePublicUrl(raw, `publicAssets.${key}`)
      : sanitizeThemePublicToken(raw, `publicAssets.${key}`);
  }
  return assets;
}

function mergeTheme(templateTheme: unknown, overrides: unknown) {
  return {
    ...objectValue(templateTheme),
    ...sanitizeThemeTokens(overrides),
  };
}

function mergeThemeAssets(templateAssets: unknown, overrides: unknown) {
  return {
    ...objectValue(templateAssets),
    ...sanitizeThemePublicAssets(overrides),
  };
}

function secretRef(scope: SecretScope, secretKey: string) {
  return `app_private.tenant_secrets:${scope}:${secretKey}`;
}

function parseSecretScope(value: unknown, fallback: SecretScope): SecretScope {
  const candidate = (nullableString(value) || fallback) as SecretScope;
  if (!SECRET_SCOPES.has(candidate)) {
    throw new HttpError(400, `Invalid secret scope: ${candidate}`, 'INVALID_SECRET_SCOPE');
  }
  return candidate;
}

function parseSecretPayload(
  body: JsonBody,
  fallbackScope: SecretScope,
  fallbackSecretKey: string,
  fallbackProvider: string,
) {
  const raw = body.secret;
  if (!raw) return null;
  if (!raw || typeof raw !== 'object' || Array.isArray(raw)) {
    throw new HttpError(400, 'secret must be an object', 'INVALID_SECRET_PAYLOAD');
  }

  const secret = raw as JsonBody;
  const scope = parseSecretScope(secret.secretScope || body.secretScope, fallbackScope);
  const secretKey = nullableString(secret.secretKey) || fallbackSecretKey;
  const secretValue = nullableString(secret.secretValue);
  const secretJson = objectValue(secret.secretJson);
  const provider = nullableString(secret.provider) || fallbackProvider;

  if (!secretValue && Object.keys(secretJson).length === 0) {
    throw new HttpError(400, 'secret.secretValue or secret.secretJson is required', 'SECRET_VALUE_REQUIRED');
  }

  return { scope, secretKey, secretValue, secretJson, provider };
}

async function upsertTenantSecret(
  client: pg.PoolClient,
  auth: TenantAdminAuth,
  payload: {
    scope: SecretScope;
    secretKey: string;
    secretValue: string | null;
    secretJson: Record<string, unknown>;
    provider: string | null;
  },
) {
  const result = await client.query(
    `
    insert into app_private.tenant_secrets (
      tenant_id, secret_scope, secret_key, secret_value, secret_json, provider, last_rotated_at
    )
    values ($1, $2, $3, $4, $5::jsonb, $6, now())
    on conflict (tenant_id, secret_scope, secret_key)
    do update set secret_value = excluded.secret_value,
                  secret_json = excluded.secret_json,
                  provider = excluded.provider,
                  last_rotated_at = now(),
                  updated_at = now()
    returning id, secret_scope as "secretScope", secret_key as "secretKey", provider,
              (secret_value is not null and secret_value <> '') as "hasSecretValue",
              (secret_json <> '{}'::jsonb) as "hasSecretJson",
              last_rotated_at as "lastRotatedAt", updated_at as "updatedAt"
    `,
    [
      auth.tenantId,
      payload.scope,
      payload.secretKey,
      payload.secretValue,
      JSON.stringify(payload.secretJson),
      payload.provider,
    ],
  );

  return {
    ...result.rows[0],
    secretRef: secretRef(payload.scope, payload.secretKey),
  };
}

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

function codeValue(body: JsonBody) {
  return requiredString(body, 'code').replace(/\s+/g, '').toUpperCase();
}

function randomCode(prefix = '') {
  return `${prefix}${randomBytes(5).toString('hex').toUpperCase()}`;
}

function permissionValue(value: unknown) {
  const source = objectValue(value);
  const permissions: Record<string, boolean> = {};
  for (const [key, raw] of Object.entries(source)) {
    if (typeof raw !== 'boolean') {
      throw new HttpError(400, `Permission ${key} must be boolean`, 'INVALID_PERMISSION_VALUE');
    }
    if (key !== '*' && !/^[a-z][a-z0-9]*(?::[a-z0-9*]+)+$/i.test(key)) {
      throw new HttpError(400, `Invalid permission key: ${key}`, 'INVALID_PERMISSION_KEY');
    }
    permissions[key] = raw;
  }
  return permissions;
}

function safeCodeValue(value: unknown, fallback = '') {
  const raw = nullableString(value) || fallback;
  const code = raw.trim().toLowerCase().replace(/[^a-z0-9_-]+/g, '-').replace(/^-+|-+$/g, '');
  if (!code || !/^[a-z][a-z0-9_-]{1,63}$/.test(code)) {
    throw new HttpError(400, 'Invalid role template code', 'INVALID_ROLE_TEMPLATE_CODE');
  }
  return code;
}

function accessControlMapValue(value: unknown, name: string) {
  const source = objectValue(value);
  const result: Record<string, boolean> = {};
  for (const [key, raw] of Object.entries(source)) {
    if (typeof raw !== 'boolean') {
      throw new HttpError(400, `${name}.${key} must be boolean`, 'INVALID_ACCESS_CONTROL_VALUE');
    }
    if (!/^[a-z][a-z0-9_.:-]*$/i.test(key)) {
      throw new HttpError(400, `Invalid ${name} key: ${key}`, 'INVALID_ACCESS_CONTROL_KEY');
    }
    result[key] = raw;
  }
  return result;
}

function dataScopeValue(value: unknown) {
  const scope = objectValue(value);
  const allowed = new Set(['mode', 'regionIds', 'contentNodeIds', 'classIds', 'ownLeadsOnly', 'teamScope', 'metadata']);
  for (const key of Object.keys(scope)) {
    if (!allowed.has(key)) {
      throw new HttpError(400, `Invalid dataScope key: ${key}`, 'INVALID_DATA_SCOPE_KEY');
    }
  }
  return scope;
}

function requiredMemberRole(value: unknown) {
  return optionalChoice(value, TENANT_MEMBER_ROLES, 'student');
}

function ensureCanGrantRole(auth: TenantAdminAuth, role: string, permissions: Record<string, boolean>) {
  if (auth.role === 'tenant_owner') return;
  if (role === 'tenant_owner' || role === 'tenant_admin' || permissions['*'] === true) {
    throw new HttpError(403, 'Only tenant owner can grant owner/admin level permissions', 'TENANT_OWNER_REQUIRED');
  }
}

async function resolveRoleTemplateForMember(client: pg.PoolClient, auth: TenantAdminAuth, roleTemplateId: string | null) {
  if (!roleTemplateId) return null;
  const template = await client.query<{
    id: string;
    baseRole: string;
    permissions: Record<string, boolean>;
    status: string;
  }>(
    `
    select id, base_role as "baseRole", permissions, status
    from public.tenant_role_templates
    where tenant_id = $1 and id = $2
    limit 1
    `,
    [auth.tenantId, roleTemplateId],
  );
  const item = template.rows[0];
  if (!item || item.status !== 'active') {
    throw new HttpError(404, 'Role template not found or inactive', 'ROLE_TEMPLATE_NOT_FOUND');
  }
  ensureCanGrantRole(auth, item.baseRole, item.permissions || {});
  return item;
}

async function ensureOwnerRemains(
  client: pg.PoolClient,
  tenantId: string,
  membershipId: string | null,
  nextRole: string,
  nextStatus: string,
) {
  if (!membershipId) return;

  const current = await client.query<{ role: string; status: string }>(
    'select role, status from public.tenant_memberships where tenant_id = $1 and id = $2 limit 1',
    [tenantId, membershipId],
  );
  if (current.rows[0]?.role !== 'tenant_owner' || current.rows[0]?.status !== 'active') return;
  if (nextRole === 'tenant_owner' && nextStatus === 'active') return;

  const owners = await client.query<{ count: string }>(
    `
    select count(*)::text as count
    from public.tenant_memberships
    where tenant_id = $1
      and role = 'tenant_owner'
      and status = 'active'
      and id <> $2
    `,
    [tenantId, membershipId],
  );
  if (Number(owners.rows[0]?.count || 0) <= 0) {
    throw new HttpError(400, 'At least one active tenant owner is required', 'LAST_TENANT_OWNER_REQUIRED');
  }
}

async function resolveOrCreateMemberUser(client: pg.PoolClient, body: JsonBody) {
  const userId = nullableString(body.userId);
  if (userId) {
    const existing = await client.query<{ id: string }>('select id from public.platform_users where id = $1 limit 1', [userId]);
    if (!existing.rows[0]) throw new HttpError(404, 'User not found', 'USER_NOT_FOUND');
    await client.query(
      `
      update public.platform_users
      set username = coalesce($2, username),
          email = coalesce($3::citext, email),
          phone = coalesce($4, phone),
          name = coalesce($5, name),
          primary_role = coalesce($6, primary_role),
          updated_at = now()
      where id = $1
      `,
      [
        userId,
        nullableString(body.username),
        nullableString(body.email),
        nullableString(body.phone),
        nullableString(body.name),
        nullableString(body.primaryRole),
      ],
    );
    return existing.rows[0].id;
  }

  const phone = nullableString(body.phone);
  const email = nullableString(body.email);
  const username = nullableString(body.username);
  const name = nullableString(body.name);
  if (!phone && !email && !username && !name) {
    throw new HttpError(400, 'userId, phone, email, username, or name is required', 'MEMBER_USER_REQUIRED');
  }

  const found = await client.query<{ id: string }>(
    `
    select id
    from public.platform_users
    where ($1::text is not null and phone = $1)
       or ($2::citext is not null and email = $2::citext)
       or ($3::text is not null and username = $3)
    order by created_at asc
    limit 1
    `,
    [phone, email, username],
  );
  if (found.rows[0]) return found.rows[0].id;

  const created = await client.query<{ id: string }>(
    `
    insert into public.platform_users (username, email, phone, name, primary_role, raw_profile)
    values ($1, $2::citext, $3, $4, $5, '{"source":"tenant-admin"}'::jsonb)
    returning id
    `,
    [
      username || phone || email,
      email,
      phone,
      name || username || phone || email,
      optionalChoice(body.primaryRole, ['student', 'teacher', 'sales', 'agent', 'tenant_operator'], 'student'),
    ],
  );
  return created.rows[0].id;
}

export async function tenantPermissionsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  return {
    current: {
      userId: auth.userId,
      role: auth.role,
      roleTemplateId: auth.roleTemplateId,
      roleTemplateCode: auth.roleTemplateCode,
      roleTemplateName: auth.roleTemplateName,
      permissions: auth.permissions,
      templatePermissions: auth.templatePermissions,
      effectivePermissions: {
        ...auth.templatePermissions,
        ...auth.permissions,
      },
      menuPermissions: auth.menuPermissions,
      modulePermissions: auth.modulePermissions,
      fieldPermissions: auth.fieldPermissions,
      dataScope: auth.dataScope,
    },
    ...tenantPermissionCatalog(),
  };
}

export async function tenantRoleTemplatesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'roles:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (status) {
    if (!ROLE_TEMPLATE_STATUSES.includes(status)) {
      throw new HttpError(400, `Invalid role template status: ${status}`, 'INVALID_ROLE_TEMPLATE_STATUS');
    }
    params.push(status);
    filters.push(`status = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select id, code, name, description, base_role as "baseRole", status, permissions,
           menu_permissions as "menuPermissions", module_permissions as "modulePermissions",
           field_permissions as "fieldPermissions", data_scope as "dataScope",
           is_system as "isSystem", sort_order as "sortOrder",
           created_by as "createdBy", updated_by as "updatedBy",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.tenant_role_templates
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function upsertTenantRoleTemplateRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'roles:write');
  const body = await readJsonBody(ctx);
  const templateId = nullableString(body.id);
  const baseRole = requiredMemberRole(body.baseRole || body.role);
  const permissions = permissionValue(body.permissions);
  ensureCanGrantRole(auth, baseRole, permissions);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.tenant_role_templates (
        id, tenant_id, code, name, description, base_role, status, permissions,
        menu_permissions, module_permissions, field_permissions, data_scope,
        sort_order, created_by, updated_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6, $7, $8::jsonb,
        $9::jsonb, $10::jsonb, $11::jsonb, $12::jsonb, $13, $14, $14
      )
      on conflict (tenant_id, code)
      do update set name = excluded.name,
                    description = excluded.description,
                    base_role = excluded.base_role,
                    status = excluded.status,
                    permissions = excluded.permissions,
                    menu_permissions = excluded.menu_permissions,
                    module_permissions = excluded.module_permissions,
                    field_permissions = excluded.field_permissions,
                    data_scope = excluded.data_scope,
                    sort_order = excluded.sort_order,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      returning id, code, name, description, base_role as "baseRole", status, permissions,
                menu_permissions as "menuPermissions", module_permissions as "modulePermissions",
                field_permissions as "fieldPermissions", data_scope as "dataScope",
                is_system as "isSystem", sort_order as "sortOrder",
                created_by as "createdBy", updated_by as "updatedBy",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        templateId,
        safeCodeValue(body.code, nullableString(body.name) || ''),
        requiredString(body, 'name'),
        nullableString(body.description),
        baseRole,
        optionalChoice(body.status, ROLE_TEMPLATE_STATUSES, 'active'),
        JSON.stringify(permissions),
        JSON.stringify(accessControlMapValue(body.menuPermissions, 'menuPermissions')),
        JSON.stringify(accessControlMapValue(body.modulePermissions, 'modulePermissions')),
        JSON.stringify(accessControlMapValue(body.fieldPermissions, 'fieldPermissions')),
        JSON.stringify(dataScopeValue(body.dataScope)),
        intValue(body.sortOrder, 100),
        auth.userId,
      ],
    );
    await recordAudit(client, auth, 'tenant.role_template.upserted', 'tenant_role_templates', result.rows[0].id, {
      code: result.rows[0].code,
      baseRole,
      permissionKeys: Object.keys(permissions),
    });
    return result.rows[0];
  });

  return { item };
}

export async function disableTenantRoleTemplateRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'roles:write');
  const body = await readJsonBody(ctx);
  const roleTemplateId = requiredString(body, 'roleTemplateId');

  const item = await transaction(async client => {
    const current = await client.query<{ id: string; permissions: Record<string, boolean>; baseRole: string; isSystem: boolean }>(
      `
      select id, permissions, base_role as "baseRole", is_system as "isSystem"
      from public.tenant_role_templates
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, roleTemplateId],
    );
    if (!current.rows[0]) throw new HttpError(404, 'Role template not found', 'ROLE_TEMPLATE_NOT_FOUND');
    if (current.rows[0].isSystem) throw new HttpError(400, 'System role template cannot be disabled', 'SYSTEM_ROLE_TEMPLATE_LOCKED');
    ensureCanGrantRole(auth, current.rows[0].baseRole, current.rows[0].permissions || {});

    const result = await client.query(
      `
      update public.tenant_role_templates
      set status = 'disabled',
          updated_by = $3,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, code, name, description, base_role as "baseRole", status, permissions,
                menu_permissions as "menuPermissions", module_permissions as "modulePermissions",
                field_permissions as "fieldPermissions", data_scope as "dataScope",
                is_system as "isSystem", sort_order as "sortOrder",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [auth.tenantId, roleTemplateId, auth.userId],
    );
    await recordAudit(client, auth, 'tenant.role_template.disabled', 'tenant_role_templates', roleTemplateId, {
      code: result.rows[0].code,
    });
    return result.rows[0];
  });

  return { item };
}

export async function tenantOverviewRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:overview:read');

  const item = await queryOne(
    `
    select t.id, t.slug, t.name, t.legal_name as "legalName", t.status, t.mode,
           t.billing_status as "billingStatus", t.metadata,
           b.brand_name as "brandName", b.short_name as "shortName", b.slogan,
           b.org_name as "orgName", b.logo_url as "logoUrl", b.favicon_url as "faviconUrl",
           b.service_wechat as "serviceWechat", b.service_account_name as "serviceAccountName",
           coalesce(b.theme, '{}'::jsonb) as theme,
           coalesce(b.public_assets, '{}'::jsonb) as "publicAssets",
           coalesce(s.feature_flags, '{}'::jsonb) as "featureFlags",
           coalesce(s.admin_feature_flags, '{}'::jsonb) as "adminFeatureFlags",
           coalesce(s.public_config, '{}'::jsonb) as "publicConfig",
           t.created_at as "createdAt", t.updated_at as "updatedAt"
    from public.tenants t
    left join public.tenant_branding b on b.tenant_id = t.id
    left join public.tenant_settings s on s.tenant_id = t.id
    where t.id = $1
    limit 1
    `,
    [auth.tenantId],
  );

  if (!item) throw new HttpError(404, 'Tenant not found', 'TENANT_NOT_FOUND');
  return { item };
}

export async function updateTenantBrandingRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:branding:write');
  const body = await readJsonBody(ctx);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.tenant_branding (
        tenant_id, brand_name, short_name, slogan, org_name, logo_url, favicon_url,
        service_wechat, service_account_name, theme, public_assets
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::jsonb, $11::jsonb)
      on conflict (tenant_id)
      do update set brand_name = excluded.brand_name,
                    short_name = excluded.short_name,
                    slogan = excluded.slogan,
                    org_name = excluded.org_name,
                    logo_url = excluded.logo_url,
                    favicon_url = excluded.favicon_url,
                    service_wechat = excluded.service_wechat,
                    service_account_name = excluded.service_account_name,
                    theme = excluded.theme,
                    public_assets = excluded.public_assets,
                    updated_at = now()
      returning tenant_id as "tenantId", brand_name as "brandName", short_name as "shortName",
                slogan, org_name as "orgName", logo_url as "logoUrl", favicon_url as "faviconUrl",
                service_wechat as "serviceWechat", service_account_name as "serviceAccountName",
                theme, public_assets as "publicAssets", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        requiredString(body, 'brandName'),
        optionalString(body, 'shortName') || null,
        optionalString(body, 'slogan') || null,
        optionalString(body, 'orgName') || null,
        optionalString(body, 'logoUrl') || null,
        optionalString(body, 'faviconUrl') || null,
        optionalString(body, 'serviceWechat') || null,
        optionalString(body, 'serviceAccountName') || null,
        jsonBodyValue(body.theme),
        jsonBodyValue(body.publicAssets),
      ],
    );
    await recordAudit(client, auth, 'tenant.branding.updated', 'tenant_branding', auth.tenantId);
    return result.rows[0];
  });

  return { item };
}

export async function updateTenantSettingsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:settings:write');
  const body = await readJsonBody(ctx);
  assertPublicConfigHasNoSecrets(body.publicConfig, 'publicConfig');

  const item = await transaction(async client => {
    const result = 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)
      on conflict (tenant_id)
      do update set feature_flags = excluded.feature_flags,
                    admin_feature_flags = excluded.admin_feature_flags,
                    public_config = excluded.public_config,
                    updated_at = now()
      returning tenant_id as "tenantId", feature_flags as "featureFlags",
                admin_feature_flags as "adminFeatureFlags",
                public_config as "publicConfig", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        jsonBodyValue(body.featureFlags),
        jsonBodyValue(body.adminFeatureFlags),
        jsonBodyValue(body.publicConfig),
      ],
    );
    await recordAudit(client, auth, 'tenant.settings.updated', 'tenant_settings', auth.tenantId);
    return result.rows[0];
  });

  return { item };
}

export async function tenantThemeTemplatesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:theme:read');

  const items = await query(
    `
    select code, name, description, preview_image_url as "previewImageUrl",
           theme, public_assets as "publicAssets", sort_order as "sortOrder",
           status, updated_at as "updatedAt"
    from public.tenant_theme_templates
    where status = 'active'
    order by sort_order asc, code asc
    `,
  );

  return { items };
}

export async function tenantThemeRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:theme:read');

  const item = await queryOne(
    `
    select c.tenant_id as "tenantId",
           c.active_template_code as "activeTemplateCode",
           c.active_theme as "activeTheme",
           c.active_public_assets as "activePublicAssets",
           c.draft_template_code as "draftTemplateCode",
           c.draft_theme as "draftTheme",
           c.draft_public_assets as "draftPublicAssets",
           c.status,
           c.published_at as "publishedAt",
           c.published_by as "publishedBy",
           c.draft_updated_by as "draftUpdatedBy",
           c.updated_at as "updatedAt",
           at.name as "activeTemplateName",
           dt.name as "draftTemplateName",
           coalesce(b.theme, '{}'::jsonb) as "brandingTheme",
           coalesce(b.public_assets, '{}'::jsonb) as "brandingPublicAssets"
    from public.tenant_theme_configs c
    left join public.tenant_theme_templates at on at.code = c.active_template_code
    left join public.tenant_theme_templates dt on dt.code = c.draft_template_code
    left join public.tenant_branding b on b.tenant_id = c.tenant_id
    where c.tenant_id = $1
    limit 1
    `,
    [auth.tenantId],
  );

  if (item) return { item };

  const fallback = await queryOne(
    `
    select b.tenant_id as "tenantId",
           null::text as "activeTemplateCode",
           coalesce(b.theme, '{}'::jsonb) as "activeTheme",
           coalesce(b.public_assets, '{}'::jsonb) as "activePublicAssets",
           null::text as "draftTemplateCode",
           '{}'::jsonb as "draftTheme",
           '{}'::jsonb as "draftPublicAssets",
           'published'::text as status,
           null::timestamptz as "publishedAt",
           null::uuid as "publishedBy",
           null::uuid as "draftUpdatedBy",
           b.updated_at as "updatedAt",
           null::text as "activeTemplateName",
           null::text as "draftTemplateName",
           coalesce(b.theme, '{}'::jsonb) as "brandingTheme",
           coalesce(b.public_assets, '{}'::jsonb) as "brandingPublicAssets"
    from public.tenant_branding b
    where b.tenant_id = $1
    limit 1
    `,
    [auth.tenantId],
  );

  return { item: fallback || {
    tenantId: auth.tenantId,
    activeTemplateCode: null,
    activeTheme: {},
    activePublicAssets: {},
    draftTemplateCode: null,
    draftTheme: {},
    draftPublicAssets: {},
    status: 'published',
    publishedAt: null,
    updatedAt: null,
  } };
}

export async function previewTenantThemeRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:theme:write');
  const body = await readJsonBody(ctx);
  const templateCode = requiredString(body, 'templateCode');
  const themeOverrides = body.theme || body.themeOverrides || {};
  const publicAssetOverrides = body.publicAssets || body.publicAssetOverrides || {};

  const item = await transaction(async client => {
    const template = await client.query<{
      code: string;
      name: string;
      theme: Record<string, unknown>;
      publicAssets: Record<string, unknown>;
    }>(
      `
      select code, name, theme, public_assets as "publicAssets"
      from public.tenant_theme_templates
      where code = $1 and status = 'active'
      limit 1
      `,
      [templateCode],
    );
    const selected = template.rows[0];
    if (!selected) throw new HttpError(404, 'Theme template not found', 'THEME_TEMPLATE_NOT_FOUND');

    const draftTheme = mergeTheme(selected.theme, themeOverrides);
    const draftPublicAssets = mergeThemeAssets(selected.publicAssets, publicAssetOverrides);

    const result = await client.query(
      `
      insert into public.tenant_theme_configs (
        tenant_id, draft_template_code, draft_theme, draft_public_assets,
        status, draft_updated_by
      )
      values ($1, $2, $3::jsonb, $4::jsonb, 'draft', $5)
      on conflict (tenant_id)
      do update set draft_template_code = excluded.draft_template_code,
                    draft_theme = excluded.draft_theme,
                    draft_public_assets = excluded.draft_public_assets,
                    status = 'draft',
                    draft_updated_by = excluded.draft_updated_by,
                    updated_at = now()
      returning tenant_id as "tenantId",
                draft_template_code as "draftTemplateCode",
                draft_theme as "draftTheme",
                draft_public_assets as "draftPublicAssets",
                status, updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        selected.code,
        JSON.stringify(draftTheme),
        JSON.stringify(draftPublicAssets),
        auth.userId,
      ],
    );

    await recordAudit(client, auth, 'tenant.theme.previewed', 'tenant_theme_configs', auth.tenantId, {
      templateCode: selected.code,
      themeKeys: Object.keys(draftTheme),
      publicAssetKeys: Object.keys(draftPublicAssets),
    });

    return {
      ...result.rows[0],
      templateName: selected.name,
    };
  });

  return { item };
}

export async function publishTenantThemeRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:theme:write');
  const body = await readJsonBody(ctx);
  const useDraft = boolValue(body.useDraft, true);
  const templateCode = nullableString(body.templateCode);

  const item = await transaction(async client => {
    let sourceTheme: Record<string, unknown>;
    let sourcePublicAssets: Record<string, unknown>;
    let sourceTemplateCode: string | null;
    let templateName: string | null = null;

    if (useDraft) {
      const draft = await client.query<{
        draftTemplateCode: string | null;
        draftTheme: Record<string, unknown>;
        draftPublicAssets: Record<string, unknown>;
        templateName: string | null;
      }>(
        `
        select c.draft_template_code as "draftTemplateCode",
               c.draft_theme as "draftTheme",
               c.draft_public_assets as "draftPublicAssets",
               t.name as "templateName"
        from public.tenant_theme_configs c
        left join public.tenant_theme_templates t on t.code = c.draft_template_code
        where c.tenant_id = $1
        limit 1
        `,
        [auth.tenantId],
      );
      const row = draft.rows[0];
      if (!row || !row.draftTemplateCode) {
        throw new HttpError(400, 'No draft theme to publish', 'THEME_DRAFT_NOT_FOUND');
      }
      sourceTheme = sanitizeThemeTokens(row.draftTheme);
      sourcePublicAssets = sanitizeThemePublicAssets(row.draftPublicAssets);
      sourceTemplateCode = row.draftTemplateCode;
      templateName = row.templateName;
    } else {
      const selectedCode = templateCode || requiredString(body, 'templateCode');
      const template = await client.query<{
        code: string;
        name: string;
        theme: Record<string, unknown>;
        publicAssets: Record<string, unknown>;
      }>(
        `
        select code, name, theme, public_assets as "publicAssets"
        from public.tenant_theme_templates
        where code = $1 and status = 'active'
        limit 1
        `,
        [selectedCode],
      );
      const selected = template.rows[0];
      if (!selected) throw new HttpError(404, 'Theme template not found', 'THEME_TEMPLATE_NOT_FOUND');
      sourceTheme = mergeTheme(selected.theme, body.theme || body.themeOverrides || {});
      sourcePublicAssets = mergeThemeAssets(selected.publicAssets, body.publicAssets || body.publicAssetOverrides || {});
      sourceTemplateCode = selected.code;
      templateName = selected.name;
    }

    await client.query(
      `
      insert into public.tenant_branding (tenant_id, brand_name, theme, public_assets)
      values (
        $1,
        coalesce((select brand_name from public.tenant_branding where tenant_id = $1), (select name from public.tenants where id = $1), '租户题库'),
        $2::jsonb,
        $3::jsonb
      )
      on conflict (tenant_id)
      do update set theme = excluded.theme,
                    public_assets = coalesce(public.tenant_branding.public_assets, '{}'::jsonb) || excluded.public_assets,
                    updated_at = now()
      `,
      [auth.tenantId, JSON.stringify(sourceTheme), JSON.stringify(sourcePublicAssets)],
    );

    const result = await client.query(
      `
      insert into public.tenant_theme_configs (
        tenant_id, active_template_code, active_theme, active_public_assets,
        draft_template_code, draft_theme, draft_public_assets,
        status, published_at, published_by, draft_updated_by
      )
      values ($1, $2, $3::jsonb, $4::jsonb, null, '{}'::jsonb, '{}'::jsonb, 'published', now(), $5, $5)
      on conflict (tenant_id)
      do update set active_template_code = excluded.active_template_code,
                    active_theme = excluded.active_theme,
                    active_public_assets = excluded.active_public_assets,
                    draft_template_code = null,
                    draft_theme = '{}'::jsonb,
                    draft_public_assets = '{}'::jsonb,
                    status = 'published',
                    published_at = now(),
                    published_by = excluded.published_by,
                    updated_at = now()
      returning tenant_id as "tenantId",
                active_template_code as "activeTemplateCode",
                active_theme as "activeTheme",
                active_public_assets as "activePublicAssets",
                status,
                published_at as "publishedAt",
                published_by as "publishedBy",
                updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        sourceTemplateCode,
        JSON.stringify(sourceTheme),
        JSON.stringify(sourcePublicAssets),
        auth.userId,
      ],
    );

    await recordAudit(client, auth, 'tenant.theme.published', 'tenant_theme_configs', auth.tenantId, {
      templateCode: sourceTemplateCode,
      themeKeys: Object.keys(sourceTheme),
      publicAssetKeys: Object.keys(sourcePublicAssets),
    });

    return {
      ...result.rows[0],
      templateName,
    };
  });

  return { item };
}

export async function tenantDomainsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:domains:read');
  const items = await query(
    `
    select id, host, domain_type as "domainType", status, is_primary as "isPrimary",
           verification_token as "verificationToken", verified_at as "verifiedAt",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.tenant_domains
    where tenant_id = $1
    order by is_primary desc, created_at asc
    `,
    [auth.tenantId],
  );

  return { items };
}

export async function createTenantDomainRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:domains:write');
  const body = await readJsonBody(ctx);
  const host = requiredString(body, 'host').toLowerCase().replace(/^https?:\/\//, '').split('/')[0];
  const domainType = optionalChoice(body.domainType, ['system', 'custom', 'miniapp'], 'custom');
  const isPrimary = body.isPrimary === true;
  const verificationToken = `tenant-${auth.tenantId.slice(0, 8)}-${Math.random().toString(36).slice(2, 10)}`;

  const item = await transaction(async client => {
    if (isPrimary) {
      await client.query('update public.tenant_domains set is_primary = false where tenant_id = $1', [auth.tenantId]);
    }

    const result = await client.query(
      `
      insert into public.tenant_domains (tenant_id, host, domain_type, status, is_primary, verification_token)
      values ($1, $2, $3, 'pending', $4, $5)
      returning id, host, domain_type as "domainType", status, is_primary as "isPrimary",
                verification_token as "verificationToken", created_at as "createdAt"
      `,
      [auth.tenantId, host, domainType, isPrimary, verificationToken],
    );

    await recordAudit(client, auth, 'tenant.domain.created', 'tenant_domains', result.rows[0].id, { host });
    return result.rows[0];
  });

  return { item };
}

export async function paymentAccountsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:payment:read');
  const items = await query(
    `
    select id, provider, mode, display_name as "displayName", status,
           config_public as "configPublic", created_at as "createdAt", updated_at as "updatedAt"
    from public.tenant_payment_accounts
    where tenant_id = $1
    order by created_at asc
    `,
    [auth.tenantId],
  );

  return { items };
}

export async function upsertPaymentAccountRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:payment:write');
  const body = await readJsonBody(ctx);
  const provider = requiredString(body, 'provider');
  const mode = optionalChoice(body.mode, PAYMENT_MODES, 'platform_collect');
  const secretPayload = parseSecretPayload(body, 'payment', provider, provider);
  const configPublic = objectValue(body.configPublic);

  const item = await transaction(async client => {
    const secret = secretPayload ? await upsertTenantSecret(client, auth, secretPayload) : null;
    if (secret) configPublic.secretRef = secret.secretRef;

    const result = await client.query(
      `
      insert into public.tenant_payment_accounts (tenant_id, provider, mode, display_name, status, config_public)
      values ($1, $2, $3, $4, $5, $6::jsonb)
      on conflict (tenant_id, provider)
      do update set mode = excluded.mode,
                    display_name = excluded.display_name,
                    status = excluded.status,
                    config_public = excluded.config_public,
                    updated_at = now()
      returning id, provider, mode, display_name as "displayName", status,
                config_public as "configPublic", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        provider,
        mode,
        optionalString(body, 'displayName') || null,
        optionalStatus(body.status, PAYMENT_STATUSES, 'disabled'),
        publicJsonValue(configPublic),
      ],
    );

    await recordAudit(client, auth, 'tenant.payment_account.upserted', 'tenant_payment_accounts', result.rows[0].id, {
      provider,
      mode,
      secretRotated: Boolean(secret),
    });

    return { ...result.rows[0], secret };
  });

  return { item };
}

export async function authProvidersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:auth:read');
  const items = await query(
    `
    select id, provider, status, display_name as "displayName",
           config_public as "configPublic", created_at as "createdAt", updated_at as "updatedAt"
    from public.tenant_auth_providers
    where tenant_id = $1
    order by created_at asc
    `,
    [auth.tenantId],
  );

  return { items };
}

export async function upsertAuthProviderRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:auth:write');
  const body = await readJsonBody(ctx);
  const provider = requiredString(body, 'provider');
  const fallbackScope = provider.toLowerCase().includes('sms') ? 'sms' : 'oauth';
  const secretPayload = parseSecretPayload(body, fallbackScope, provider, provider);
  const configPublic = objectValue(body.configPublic);

  const item = await transaction(async client => {
    const secret = secretPayload ? await upsertTenantSecret(client, auth, secretPayload) : null;
    if (secret) configPublic.secretRef = secret.secretRef;

    const result = await client.query(
      `
      insert into public.tenant_auth_providers (tenant_id, provider, status, display_name, config_public)
      values ($1, $2, $3, $4, $5::jsonb)
      on conflict (tenant_id, provider)
      do update set status = excluded.status,
                    display_name = excluded.display_name,
                    config_public = excluded.config_public,
                    updated_at = now()
      returning id, provider, status, display_name as "displayName",
                config_public as "configPublic", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        provider,
        optionalStatus(body.status, AUTH_STATUSES, 'disabled'),
        optionalString(body, 'displayName') || null,
        publicJsonValue(configPublic),
      ],
    );

    await recordAudit(client, auth, 'tenant.auth_provider.upserted', 'tenant_auth_providers', result.rows[0].id, {
      provider,
      secretRotated: Boolean(secret),
    });

    return { ...result.rows[0], secret };
  });

  return { item };
}

export async function tenantSecretsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:secrets:read');
  const scope = stringParam(ctx, 'scope');
  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (scope) {
    if (!SECRET_SCOPES.has(scope as SecretScope)) {
      throw new HttpError(400, `Invalid secret scope: ${scope}`, 'INVALID_SECRET_SCOPE');
    }
    params.push(scope);
    filters.push(`secret_scope = $${params.length}`);
  }

  const items = await query<{
    id: string;
    secretScope: SecretScope;
    secretKey: string;
    provider: string | null;
    hasSecretValue: boolean;
    hasSecretJson: boolean;
    lastRotatedAt: string | null;
    createdAt: string;
    updatedAt: string;
  }>(
    `
    select id, secret_scope as "secretScope", secret_key as "secretKey", provider,
           (secret_value is not null and secret_value <> '') as "hasSecretValue",
           (secret_json <> '{}'::jsonb) as "hasSecretJson",
           last_rotated_at as "lastRotatedAt", created_at as "createdAt", updated_at as "updatedAt"
    from app_private.tenant_secrets
    where ${filters.join(' and ')}
    order by secret_scope asc, secret_key asc
    `,
    params,
  );

  return {
    items: items.map(item => ({
      ...item,
      secretRef: secretRef(item.secretScope, item.secretKey),
    })),
  };
}

export async function upsertTenantSecretRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'tenant:secrets:write');
  const body = await readJsonBody(ctx);
  const scope = parseSecretScope(body.secretScope, 'system');
  const secretKey = requiredString(body, 'secretKey');
  const secretValue = nullableString(body.secretValue);
  const secretJson = objectValue(body.secretJson);
  if (!secretValue && Object.keys(secretJson).length === 0) {
    throw new HttpError(400, 'secretValue or secretJson is required', 'SECRET_VALUE_REQUIRED');
  }

  const item = await transaction(async client => {
    const secret = await upsertTenantSecret(client, auth, {
      scope,
      secretKey,
      secretValue,
      secretJson,
      provider: nullableString(body.provider),
    });
    await recordAudit(client, auth, 'tenant.secret.upserted', 'tenant_secrets', secret.id, {
      secretScope: scope,
      secretKey,
    });
    return secret;
  });

  return { item };
}

export async function bannersAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:read');
  const regionId = stringParam(ctx, 'regionId');
  const limit = intParam(ctx, 'limit', 100, 500);
  const includeInactive = ctx.url.searchParams.get('includeInactive') === 'true';
  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           title, subtitle, content, button_text as "buttonText",
           button_link as "buttonLink", bg_color as "bgColor",
           border_color as "borderColor", sort_order as "order",
           is_active as "isActive", created_at as "createdAt", updated_at as "updatedAt"
    from public.banners
    where tenant_id = $1
      and ($2::uuid is null or region_id = $2::uuid)
      and ($3::boolean or is_active = true)
    order by sort_order asc, created_at desc
    limit $4
    `,
    [auth.tenantId, regionId || null, includeInactive, limit],
  );
  return { items };
}

export async function upsertBannerRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:write');
  const body = await readJsonBody(ctx);
  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.banners (
        id, tenant_id, region_id, legacy_id, title, subtitle, content,
        button_text, button_link, bg_color, border_color, sort_order, is_active
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3::uuid, $4, $5, $6, $7,
        $8, $9, $10, $11, $12, $13
      )
      on conflict (id)
      do update set region_id = excluded.region_id,
                    legacy_id = coalesce(excluded.legacy_id, public.banners.legacy_id),
                    title = excluded.title,
                    subtitle = excluded.subtitle,
                    content = excluded.content,
                    button_text = excluded.button_text,
                    button_link = excluded.button_link,
                    bg_color = excluded.bg_color,
                    border_color = excluded.border_color,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    updated_at = now()
      where public.banners.tenant_id = excluded.tenant_id
      returning id, legacy_id as "legacyId", region_id as "regionId",
                title, subtitle, content, button_text as "buttonText",
                button_link as "buttonLink", bg_color as "bgColor",
                border_color as "borderColor", sort_order as "order",
                is_active as "isActive", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        nullableString(body.regionId),
        nullableString(body.legacyId),
        nullableString(body.title),
        nullableString(body.subtitle),
        nullableString(body.content),
        nullableString(body.buttonText),
        nullableString(body.buttonLink),
        nullableString(body.bgColor),
        nullableString(body.borderColor),
        intValue(body.order, 0),
        boolValue(body.isActive, true),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Banner not found for this tenant', 'BANNER_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.banner.upserted', 'banners', result.rows[0].id);
    return result.rows[0];
  });
  return { item };
}

export async function faqsAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:read');
  const regionId = stringParam(ctx, 'regionId');
  const limit = intParam(ctx, 'limit', 100, 500);
  const includeInactive = ctx.url.searchParams.get('includeInactive') === 'true';
  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId",
           question, answer, sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.faqs
    where tenant_id = $1
      and ($2::uuid is null or region_id = $2::uuid)
      and ($3::boolean or is_active = true)
    order by sort_order asc, created_at desc
    limit $4
    `,
    [auth.tenantId, regionId || null, includeInactive, limit],
  );
  return { items };
}

export async function upsertFaqRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:write');
  const body = await readJsonBody(ctx);
  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.faqs (
        id, tenant_id, region_id, legacy_id, question, answer, sort_order, is_active
      )
      values (coalesce($2::uuid, gen_random_uuid()), $1, $3::uuid, $4, $5, $6, $7, $8)
      on conflict (id)
      do update set region_id = excluded.region_id,
                    legacy_id = coalesce(excluded.legacy_id, public.faqs.legacy_id),
                    question = excluded.question,
                    answer = excluded.answer,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    updated_at = now()
      where public.faqs.tenant_id = excluded.tenant_id
      returning id, legacy_id as "legacyId", region_id as "regionId",
                question, answer, sort_order as "order", is_active as "isActive",
                updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        nullableString(body.regionId),
        nullableString(body.legacyId),
        requiredString(body, 'question'),
        nullableString(body.answer),
        intValue(body.order, 0),
        boolValue(body.isActive, true),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'FAQ not found for this tenant', 'FAQ_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.faq.upserted', 'faqs', result.rows[0].id);
    return result.rows[0];
  });
  return { item };
}

export async function announcementsAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const includeInactive = ctx.url.searchParams.get('includeInactive') === 'true';
  const items = await query(
    `
    select id, legacy_id as "legacyId", content, link,
           bg_color as "bgColor", sort_order as "order",
           is_active as "isActive", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.announcements
    where tenant_id = $1 and ($2::boolean or is_active = true)
    order by sort_order asc, created_at desc
    limit $3
    `,
    [auth.tenantId, includeInactive, limit],
  );
  return { items };
}

export async function upsertAnnouncementRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:write');
  const body = await readJsonBody(ctx);
  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.announcements (
        id, tenant_id, legacy_id, content, link, bg_color, sort_order, is_active
      )
      values (coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6, $7, $8)
      on conflict (id)
      do update set legacy_id = coalesce(excluded.legacy_id, public.announcements.legacy_id),
                    content = excluded.content,
                    link = excluded.link,
                    bg_color = excluded.bg_color,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    updated_at = now()
      where public.announcements.tenant_id = excluded.tenant_id
      returning id, legacy_id as "legacyId", content, link, bg_color as "bgColor",
                sort_order as "order", is_active as "isActive", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        nullableString(body.legacyId),
        requiredString(body, 'content'),
        nullableString(body.link),
        nullableString(body.bgColor),
        intValue(body.order, 0),
        boolValue(body.isActive, true),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Announcement not found for this tenant', 'ANNOUNCEMENT_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.announcement.upserted', 'announcements', result.rows[0].id);
    return result.rows[0];
  });
  return { item };
}

export async function badgesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'badges:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const category = stringParam(ctx, 'category');
  const includeInactive = ctx.url.searchParams.get('includeInactive') === 'true';
  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (category) {
    if (!BADGE_CATEGORIES.includes(category)) {
      throw new HttpError(400, `Invalid badge category: ${category}`, 'INVALID_BADGE_CATEGORY');
    }
    params.push(category);
    filters.push(`category = $${params.length}`);
  }
  if (!includeInactive) {
    filters.push('is_active = true');
  }
  params.push(limit);

  const items = await query(
    `
    select id, legacy_id as "legacyId", name, description, category,
           icon_url as "iconUrl", level, unlock_type as "unlockType",
           condition_field as "conditionField", condition_operator as "conditionOperator",
           condition_value as "conditionValue", condition_extra as "conditionExtra",
           metadata, sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.badges
    where ${filters.join(' and ')}
    order by sort_order asc, level asc nulls last, created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function upsertBadgeRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'badges:write');
  const body = await readJsonBody(ctx);
  const category = optionalChoice(body.category, BADGE_CATEGORIES, 'custom');
  const unlockType = optionalChoice(body.unlockType, BADGE_UNLOCK_TYPES, 'manual');
  const conditionOperator = body.conditionOperator === undefined || body.conditionOperator === null || body.conditionOperator === ''
    ? null
    : optionalChoice(body.conditionOperator, BADGE_OPERATORS, 'gte');
  const item = await transaction(async client => {
    const requestedId = optionalUuidString(body.id, 'id');
    const legacyId = nullableString(body.legacyId);
    const existing = await client.query<{ id: string }>(
      `
      select id
      from public.badges
      where tenant_id = $1
        and (
          ($2::uuid is not null and id = $2::uuid)
          or ($3::text is not null and legacy_id = $3)
        )
      order by case when $2::uuid is not null and id = $2::uuid then 0 else 1 end
      limit 2
      `,
      [auth.tenantId, requestedId, legacyId],
    );
    if ((existing.rowCount || 0) > 1) {
      throw new HttpError(409, 'Badge id and legacyId point to different records', 'BADGE_ID_CONFLICT');
    }
    if (requestedId && existing.rows[0]?.id && existing.rows[0].id !== requestedId) {
      throw new HttpError(409, 'Badge legacyId already belongs to another record', 'BADGE_ID_CONFLICT');
    }

    const result = await client.query(
      `
      insert into public.badges (
        id, tenant_id, legacy_id, name, description, category, icon_url,
        level, unlock_type, condition_field, condition_operator,
        condition_value, condition_extra, metadata, sort_order, is_active
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6, $7,
        $8, $9, $10, $11, $12, $13::jsonb, $14::jsonb, $15, $16
      )
      on conflict (id)
      do update set legacy_id = coalesce(excluded.legacy_id, public.badges.legacy_id),
                    name = excluded.name,
                    description = excluded.description,
                    category = excluded.category,
                    icon_url = excluded.icon_url,
                    level = excluded.level,
                    unlock_type = excluded.unlock_type,
                    condition_field = excluded.condition_field,
                    condition_operator = excluded.condition_operator,
                    condition_value = excluded.condition_value,
                    condition_extra = excluded.condition_extra,
                    metadata = excluded.metadata,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    updated_at = now()
      where public.badges.tenant_id = excluded.tenant_id
      returning id, legacy_id as "legacyId", name, description, category,
                icon_url as "iconUrl", level, unlock_type as "unlockType",
                condition_field as "conditionField", condition_operator as "conditionOperator",
                condition_value as "conditionValue", condition_extra as "conditionExtra",
                metadata, sort_order as "order", is_active as "isActive",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        existing.rows[0]?.id || requestedId,
        legacyId,
        requiredString(body, 'name'),
        nullableString(body.description),
        category,
        nullableString(body.iconUrl),
        body.level === undefined ? null : intValue(body.level, 0),
        unlockType,
        nullableString(body.conditionField),
        conditionOperator,
        optionalNumberValue(body.conditionValue),
        jsonBodyValue(body.conditionExtra),
        jsonBodyValue(body.metadata),
        intValue(body.order, 0),
        boolValue(body.isActive, true),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Badge not found for this tenant', 'BADGE_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.badge.upserted', 'badges', result.rows[0].id, {
      name: result.rows[0].name,
      category,
      unlockType,
    });
    return result.rows[0];
  });

  return { item };
}

export async function badgeGrantsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'badges:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const userId = optionalUuidString(stringParam(ctx, 'userId'), 'userId');
  const badgeId = optionalUuidString(stringParam(ctx, 'badgeId'), 'badgeId');
  const params: unknown[] = [auth.tenantId];
  const filters = ['ub.tenant_id = $1'];
  if (userId) {
    params.push(userId);
    filters.push(`ub.user_id = $${params.length}::uuid`);
  }
  if (badgeId) {
    params.push(badgeId);
    filters.push(`ub.badge_id = $${params.length}::uuid`);
  }
  params.push(limit);

  const items = await query(
    `
    select ub.id, ub.legacy_id as "legacyId", ub.user_id as "userId",
           u.name as "userName", u.phone as "userPhone", null::text as "userAvatarUrl",
           ub.badge_id as "badgeId", b.name as "badgeName", b.category,
           b.icon_url as "iconUrl", b.level, ub.granted_by as "grantedBy",
           gu.name as "grantedByName", ub.note, ub.metadata,
           ub.granted_at as "grantedAt", ub.created_at as "createdAt",
           ub.updated_at as "updatedAt"
    from public.user_badges ub
    join public.badges b on b.tenant_id = ub.tenant_id and b.id = ub.badge_id
    join public.platform_users u on u.id = ub.user_id
    left join public.platform_users gu on gu.id = ub.granted_by
    where ${filters.join(' and ')}
    order by ub.granted_at desc nulls last, ub.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function grantBadgeRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'badges:grant');
  const body = await readJsonBody(ctx);
  const badgeId = requiredUuidString(body, 'badgeId');
  const userId = requiredUuidString(body, 'userId');

  const item = await transaction(async client => {
    const badge = await client.query<{ id: string; is_active: boolean; name: string }>(
      'select id, is_active, name from public.badges where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, badgeId],
    );
    if (!badge.rows[0]) throw new HttpError(404, 'Badge not found for this tenant', 'BADGE_NOT_FOUND');
    if (!badge.rows[0].is_active) throw new HttpError(409, 'Cannot grant inactive badge', 'BADGE_INACTIVE');

    const member = await client.query<{ id: string }>(
      `
      select tm.id
      from public.tenant_memberships tm
      where tm.tenant_id = $1
        and tm.user_id = $2
        and tm.status = 'active'
      limit 1
      `,
      [auth.tenantId, userId],
    );
    if (!member.rows[0]) throw new HttpError(400, 'Badge target user is not an active member of this tenant', 'BADGE_TARGET_NOT_IN_TENANT');

    const legacyId = nullableString(body.legacyId) || `badge:${badgeId}:user:${userId}`;
    const result = await client.query(
      `
      insert into public.user_badges (
        tenant_id, user_id, badge_id, granted_by, legacy_id,
        note, metadata, granted_at
      )
      values ($1, $2, $3, $4, $5, $6, $7::jsonb, coalesce($8::timestamptz, now()))
      on conflict (tenant_id, user_id, badge_id)
      do update set note = coalesce(excluded.note, public.user_badges.note),
                    metadata = public.user_badges.metadata || excluded.metadata,
                    granted_by = coalesce(public.user_badges.granted_by, excluded.granted_by),
                    granted_at = coalesce(public.user_badges.granted_at, excluded.granted_at),
                    updated_at = now()
      returning id, legacy_id as "legacyId", user_id as "userId",
                badge_id as "badgeId", granted_by as "grantedBy",
                note, metadata, granted_at as "grantedAt",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        userId,
        badgeId,
        auth.userId,
        legacyId,
        nullableString(body.note),
        JSON.stringify({ source: 'tenant_admin', ...objectValue(body.metadata) }),
        nullableString(body.grantedAt),
      ],
    );

    await recordAudit(client, auth, 'tenant.badge.granted', 'user_badges', result.rows[0].id, {
      userId,
      badgeId,
      badgeName: badge.rows[0].name,
    });

    await createUserNotification(client, {
      tenantId: auth.tenantId,
      userId,
      notificationType: 'badge_granted',
      severity: 'success',
      title: `获得勋章：${badge.rows[0].name}`,
      message: nullableString(body.note) || '管理员为你发放了一枚新的学习勋章。',
      actionLabel: '查看勋章',
      actionPath: '/student/profile?tab=badges',
      sourceType: 'user_badges',
      sourceId: result.rows[0].id,
      dedupeKey: `badge:${badgeId}:user:${userId}`,
      metadata: {
        source: 'tenant_admin_badge_grant',
        badgeId,
        badgeName: badge.rows[0].name,
        grantId: result.rows[0].id,
      },
      createdBy: auth.userId,
    });

    return result.rows[0];
  });

  return { item };
}

export async function codeBatchesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'codes:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const items = await query(
    `
    select id, legacy_id as "legacyId", name, sale_type as "saleType", channel,
           campaign_name as "campaignName", default_unit_price_cents as "defaultUnitPriceCents",
           cost_price_cents as "costPriceCents", total_count as "totalCount", days,
           region_id as "regionId", issued_at as "issuedAt", created_by as "createdBy",
           remark, commission_rate as "commissionRate", created_at as "createdAt", updated_at as "updatedAt"
    from public.code_batches
    where tenant_id = $1
    order by created_at desc
    limit $2
    `,
    [auth.tenantId, limit],
  );
  return { items };
}

export async function upsertCodeBatchRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'codes:write');
  const body = await readJsonBody(ctx);
  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.code_batches (
        id, tenant_id, legacy_id, name, sale_type, channel, campaign_name,
        default_unit_price_cents, cost_price_cents, total_count, days,
        region_id, issued_at, created_by, remark, commission_rate
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6, $7,
        $8, $9, $10, $11, $12::uuid, $13::timestamptz, $14, $15, $16
      )
      on conflict (id)
      do update set legacy_id = coalesce(excluded.legacy_id, public.code_batches.legacy_id),
                    name = excluded.name,
                    sale_type = excluded.sale_type,
                    channel = excluded.channel,
                    campaign_name = excluded.campaign_name,
                    default_unit_price_cents = excluded.default_unit_price_cents,
                    cost_price_cents = excluded.cost_price_cents,
                    total_count = excluded.total_count,
                    days = excluded.days,
                    region_id = excluded.region_id,
                    issued_at = excluded.issued_at,
                    remark = excluded.remark,
                    commission_rate = excluded.commission_rate,
                    updated_at = now()
      where public.code_batches.tenant_id = excluded.tenant_id
      returning id, legacy_id as "legacyId", name, sale_type as "saleType", channel,
                campaign_name as "campaignName", default_unit_price_cents as "defaultUnitPriceCents",
                cost_price_cents as "costPriceCents", total_count as "totalCount", days,
                region_id as "regionId", issued_at as "issuedAt", created_by as "createdBy",
                remark, commission_rate as "commissionRate", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        nullableString(body.legacyId),
        requiredString(body, 'name'),
        nullableString(body.saleType),
        nullableString(body.channel),
        nullableString(body.campaignName),
        intValue(body.defaultUnitPriceCents, 0),
        intValue(body.costPriceCents, 0),
        intValue(body.totalCount, 0),
        body.days === undefined ? null : intValue(body.days, 0),
        nullableString(body.regionId),
        nullableString(body.issuedAt),
        auth.userId,
        nullableString(body.remark),
        numberValue(body.commissionRate, null),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Code batch not found for this tenant', 'CODE_BATCH_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.code_batch.upserted', 'code_batches', result.rows[0].id);
    return result.rows[0];
  });
  return { item };
}

export async function activationCodesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'codes:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const batchId = stringParam(ctx, 'batchId');
  const code = stringParam(ctx, 'code');
  const isUsed = ctx.url.searchParams.get('isUsed');
  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (batchId) {
    params.push(batchId);
    filters.push(`batch_id = $${params.length}::uuid`);
  }
  if (code) {
    params.push(`%${code}%`);
    filters.push(`code::text ilike $${params.length}`);
  }
  if (isUsed === 'true' || isUsed === 'false') {
    params.push(isUsed === 'true');
    filters.push(`is_used = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select id, legacy_id as "legacyId", code, days, is_used as "isUsed",
           used_by as "usedBy", used_at as "usedAt", agent_user_id as "agentUserId",
           batch_id as "batchId", sale_type as "saleType", unit_price_cents as "unitPriceCents",
           sold_to as "soldTo", used_region_id as "usedRegionId", coupon_code as "couponCode",
           coupon_redemption_id as "couponRedemptionId", remark, created_at as "createdAt",
           updated_at as "updatedAt"
    from public.activation_codes
    where ${filters.join(' and ')}
    order by created_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items };
}

export async function upsertActivationCodeRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'codes:write');
  const body = await readJsonBody(ctx);
  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.activation_codes (
        id, tenant_id, legacy_id, code, days, batch_id, agent_user_id,
        sale_type, unit_price_cents, sold_to, used_region_id, coupon_code, remark
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6::uuid, $7::uuid,
        $8, $9, $10, $11::uuid, $12, $13
      )
      on conflict (tenant_id, code)
      do update set days = excluded.days,
                    batch_id = excluded.batch_id,
                    agent_user_id = excluded.agent_user_id,
                    sale_type = excluded.sale_type,
                    unit_price_cents = excluded.unit_price_cents,
                    sold_to = excluded.sold_to,
                    used_region_id = excluded.used_region_id,
                    coupon_code = excluded.coupon_code,
                    remark = excluded.remark,
                    updated_at = now()
      returning id, legacy_id as "legacyId", code, days, is_used as "isUsed",
                used_by as "usedBy", used_at as "usedAt", agent_user_id as "agentUserId",
                batch_id as "batchId", sale_type as "saleType", unit_price_cents as "unitPriceCents",
                sold_to as "soldTo", used_region_id as "usedRegionId", coupon_code as "couponCode",
                remark, updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        nullableString(body.legacyId),
        codeValue(body),
        intValue(body.days, 0),
        nullableString(body.batchId),
        nullableString(body.agentUserId),
        nullableString(body.saleType),
        body.unitPriceCents === undefined ? null : intValue(body.unitPriceCents, 0),
        nullableString(body.soldTo),
        nullableString(body.usedRegionId),
        nullableString(body.couponCode),
        nullableString(body.remark),
      ],
    );
    await recordAudit(client, auth, 'tenant.activation_code.upserted', 'activation_codes', result.rows[0].id, {
      code: result.rows[0].code,
    });
    return result.rows[0];
  });
  return { item };
}

export async function generateActivationCodesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'codes:write');
  const body = await readJsonBody(ctx);
  const batchId = requiredString(body, 'batchId');
  const count = Math.min(Math.max(intValue(body.count, 1), 1), 1000);
  const prefix = (nullableString(body.prefix) || '').replace(/\s+/g, '').toUpperCase();

  const result = await transaction(async client => {
    const batch = await client.query<{ id: string; days: number | null; sale_type: string | null; default_unit_price_cents: number }>(
      `
      select id, days, sale_type, default_unit_price_cents
      from public.code_batches
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, batchId],
    );
    if (!batch.rows[0]) throw new HttpError(404, 'Code batch not found', 'CODE_BATCH_NOT_FOUND');

    const items: unknown[] = [];
    let attempts = 0;
    while (items.length < count && attempts < count * 5) {
      attempts += 1;
      const code = randomCode(prefix);
      const insert = await client.query(
        `
        insert into public.activation_codes (
          tenant_id, code, days, batch_id, sale_type, unit_price_cents, sold_to, remark
        )
        values ($1, $2, $3, $4, $5, $6, $7, $8)
        on conflict (tenant_id, code) do nothing
        returning id, code, days, batch_id as "batchId", sale_type as "saleType",
                  unit_price_cents as "unitPriceCents", sold_to as "soldTo",
                  remark, created_at as "createdAt"
        `,
        [
          auth.tenantId,
          code,
          body.days === undefined ? batch.rows[0].days || 0 : intValue(body.days, 0),
          batchId,
          nullableString(body.saleType) || batch.rows[0].sale_type,
          body.unitPriceCents === undefined ? batch.rows[0].default_unit_price_cents : intValue(body.unitPriceCents, 0),
          nullableString(body.soldTo),
          nullableString(body.remark),
        ],
      );
      if (insert.rows[0]) items.push(insert.rows[0]);
    }

    await client.query(
      'update public.code_batches set total_count = total_count + $3, updated_at = now() where tenant_id = $1 and id = $2',
      [auth.tenantId, batchId, items.length],
    );
    await recordAudit(client, auth, 'tenant.activation_codes.generated', 'activation_codes', batchId, {
      count: items.length,
      prefix,
    });

    return { count: items.length, items };
  });

  return result;
}

export async function couponsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'coupons:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const campaignName = stringParam(ctx, 'campaignName');
  const params: unknown[] = [auth.tenantId, limit];
  const filters = ['tenant_id = $1'];
  if (status) {
    if (!COUPON_STATUSES.includes(status)) throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
    params.push(status);
    filters.push(`status = $${params.length}`);
  }
  if (campaignName) {
    params.push(campaignName);
    filters.push(`campaign_name = $${params.length}`);
  }
  const items = await query(
    `
    select id, legacy_id as "legacyId", code, plan_id as "planId",
           discount_type as "discountType", discount_value as "discountValue",
           valid_from as "validFrom", valid_to as "validTo", max_uses as "maxUses",
           used_count as "usedCount", status, campaign_name as "campaignName",
           min_order_amount_cents as "minOrderAmountCents",
           max_discount_cents as "maxDiscountCents", per_user_limit as "perUserLimit",
           first_order_only as "firstOrderOnly", allowed_plan_ids as "allowedPlanIds",
           allowed_region_ids as "allowedRegionIds", metadata, source, remark,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.coupons
    where ${filters.join(' and ')}
    order by created_at desc
    limit $2
    `,
    params,
  );
  return { items };
}

export async function upsertCouponRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'coupons:write');
  const body = await readJsonBody(ctx);
  const discountType = body.discountType ? optionalChoice(body.discountType, DISCOUNT_TYPES, 'fixed') : null;
  const status = optionalStatus(body.status, COUPON_STATUSES, 'active');
  const minOrderAmountCents = Math.max(0, intValue(body.minOrderAmountCents, 0));
  const maxDiscountCents = body.maxDiscountCents === undefined || body.maxDiscountCents === null || body.maxDiscountCents === ''
    ? null
    : Math.max(0, intValue(body.maxDiscountCents, 0));
  const perUserLimit = Math.max(1, Math.min(100, intValue(body.perUserLimit, 1)));
  const allowedPlanIds = uuidArrayValue(body.allowedPlanIds, 'allowedPlanIds');
  const allowedRegionIds = uuidArrayValue(body.allowedRegionIds, 'allowedRegionIds');
  const planId = optionalUuidString(body.planId, 'planId');
  if (planId && allowedPlanIds.length > 0 && !allowedPlanIds.includes(planId)) {
    allowedPlanIds.unshift(planId);
  }

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.coupons (
        id, tenant_id, legacy_id, code, plan_id, discount_type, discount_value,
        valid_from, valid_to, max_uses, status, campaign_name,
        min_order_amount_cents, max_discount_cents, per_user_limit, first_order_only,
        allowed_plan_ids, allowed_region_ids, metadata, source, remark
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5::uuid, $6, $7,
        $8::timestamptz, $9::timestamptz, $10, $11, $12,
        $13, $14, $15, $16, $17::uuid[], $18::uuid[], $19::jsonb, $20, $21
      )
      on conflict (tenant_id, code)
      do update set plan_id = excluded.plan_id,
                    discount_type = excluded.discount_type,
                    discount_value = excluded.discount_value,
                    valid_from = excluded.valid_from,
                    valid_to = excluded.valid_to,
                    max_uses = excluded.max_uses,
                    status = excluded.status,
                    campaign_name = excluded.campaign_name,
                    min_order_amount_cents = excluded.min_order_amount_cents,
                    max_discount_cents = excluded.max_discount_cents,
                    per_user_limit = excluded.per_user_limit,
                    first_order_only = excluded.first_order_only,
                    allowed_plan_ids = excluded.allowed_plan_ids,
                    allowed_region_ids = excluded.allowed_region_ids,
                    metadata = excluded.metadata,
                    source = excluded.source,
                    remark = excluded.remark,
                    updated_at = now()
      returning id, legacy_id as "legacyId", code, plan_id as "planId",
                discount_type as "discountType", discount_value as "discountValue",
                valid_from as "validFrom", valid_to as "validTo", max_uses as "maxUses",
                used_count as "usedCount", status, campaign_name as "campaignName",
                min_order_amount_cents as "minOrderAmountCents",
                max_discount_cents as "maxDiscountCents", per_user_limit as "perUserLimit",
                first_order_only as "firstOrderOnly", allowed_plan_ids as "allowedPlanIds",
                allowed_region_ids as "allowedRegionIds", metadata, source, remark,
                updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        nullableString(body.legacyId),
        codeValue(body),
        planId,
        discountType,
        numberValue(body.discountValue, null),
        nullableString(body.validFrom),
        nullableString(body.validTo),
        body.maxUses === undefined ? null : intValue(body.maxUses, 0),
        status,
        nullableString(body.campaignName),
        minOrderAmountCents,
        maxDiscountCents,
        perUserLimit,
        boolValue(body.firstOrderOnly, false),
        allowedPlanIds,
        allowedRegionIds,
        JSON.stringify(objectValue(body.metadata)),
        nullableString(body.source),
        nullableString(body.remark),
      ],
    );
    await recordAudit(client, auth, 'tenant.coupon.upserted', 'coupons', result.rows[0].id, {
      code: result.rows[0].code,
      status: result.rows[0].status,
      campaignName: result.rows[0].campaignName,
    });
    return result.rows[0];
  });

  return { item };
}

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

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

export async function couponRedemptionsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'coupons:redemptions:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const couponId = stringParam(ctx, 'couponId');
  const status = stringParam(ctx, 'status');
  const params: unknown[] = [auth.tenantId, limit];
  const filters = ['cr.tenant_id = $1'];
  if (couponId) {
    params.push(optionalUuidString(couponId, 'couponId'));
    filters.push(`cr.coupon_id = $${params.length}::uuid`);
  }
  if (status) {
    if (!COUPON_REDEMPTION_STATUSES.includes(status)) throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
    params.push(status);
    filters.push(`cr.status = $${params.length}`);
  }

  const items = await query(
    `
    select cr.id, cr.coupon_id as "couponId", cr.coupon_code as "couponCode",
           c.campaign_name as "campaignName", cr.user_id as "userId",
           u.name as "userName", u.phone as "userPhone", cr.plan_id as "planId",
           p.name as "planName", cr.order_id as "orderId", o.order_no as "orderNo",
           cr.status, cr.discount_applied_cents as "discountAppliedCents",
           cr.region_id as "regionId", r.name as "regionName", cr.source, cr.remark,
           cr.claimed_at as "claimedAt", cr.used_at as "usedAt",
           cr.created_at as "createdAt", cr.updated_at as "updatedAt"
    from public.coupon_redemptions cr
    left join public.coupons c on c.tenant_id = cr.tenant_id and c.id = cr.coupon_id
    left join public.platform_users u on u.id = cr.user_id
    left join public.svip_plans p on p.tenant_id = cr.tenant_id and p.id = cr.plan_id
    left join public.orders o on o.tenant_id = cr.tenant_id and o.id = cr.order_id
    left join public.regions r on r.tenant_id = cr.tenant_id and r.id = cr.region_id
    where ${filters.join(' and ')}
    order by cr.created_at desc
    limit $2
    `,
    params,
  );
  return { items };
}

export async function couponReportRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'coupons:redemptions:read');
  const today = shanghaiDateKey();
  const startDate = dateParam(ctx, 'startDate', today.slice(0, 8) + '01');
  const endDate = dateParam(ctx, 'endDate', today);
  if (startDate > endDate) throw new HttpError(400, 'startDate must be before or equal to endDate', 'INVALID_DATE_RANGE');
  const couponId = stringParam(ctx, 'couponId');
  const campaignName = stringParam(ctx, 'campaignName');
  const params: unknown[] = [auth.tenantId, startDate, endDate];
  const couponFilters = ['c.tenant_id = $1'];
  const redemptionFilters = ['cr.tenant_id = $1', `cr.created_at >= $2::date`, `cr.created_at < ($3::date + interval '1 day')`];
  if (couponId) {
    params.push(optionalUuidString(couponId, 'couponId'));
    couponFilters.push(`c.id = $${params.length}::uuid`);
    redemptionFilters.push(`cr.coupon_id = $${params.length}::uuid`);
  }
  if (campaignName) {
    params.push(campaignName);
    couponFilters.push(`c.campaign_name = $${params.length}`);
    redemptionFilters.push(`c.campaign_name = $${params.length}`);
  }

  const [summaryRows, couponRows, dailyRows, campaignRows] = await Promise.all([
    query<Record<string, unknown>>(
      `
      select
        count(*)::int as "claimCount",
        count(*) filter (where cr.status = 'used')::int as "usedCount",
        coalesce(sum(cr.discount_applied_cents) filter (where cr.status = 'used'), 0)::int as "discountCents",
        coalesce(sum(o.amount_cents) filter (where cr.status = 'used'), 0)::int as "paidAmountCents"
      from public.coupon_redemptions cr
      left join public.coupons c on c.tenant_id = cr.tenant_id and c.id = cr.coupon_id
      left join public.orders o on o.tenant_id = cr.tenant_id and o.id = cr.order_id
      where ${redemptionFilters.join(' and ')}
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select c.id, c.code::text as code, c.status, c.campaign_name as "campaignName",
             c.used_count as "usedCount", c.max_uses as "maxUses",
             count(cr.id)::int as "claimCount",
             count(cr.id) filter (where cr.status = 'used')::int as "redeemedCount",
             coalesce(sum(cr.discount_applied_cents) filter (where cr.status = 'used'), 0)::int as "discountCents",
             coalesce(sum(o.amount_cents) filter (where cr.status = 'used'), 0)::int as "paidAmountCents"
      from public.coupons c
      left join public.coupon_redemptions cr on cr.tenant_id = c.tenant_id
        and cr.coupon_id = c.id
        and cr.created_at >= $2::date
        and cr.created_at < ($3::date + interval '1 day')
      left join public.orders o on o.tenant_id = cr.tenant_id and o.id = cr.order_id
      where ${couponFilters.join(' and ')}
      group by c.id, c.code, c.status, c.campaign_name, c.used_count, c.max_uses
      order by "claimCount" desc, c.created_at desc
      limit 200
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select cr.created_at::date::text as date, cr.status,
             count(*)::int as count,
             coalesce(sum(cr.discount_applied_cents) filter (where cr.status = 'used'), 0)::int as "discountCents"
      from public.coupon_redemptions cr
      left join public.coupons c on c.tenant_id = cr.tenant_id and c.id = cr.coupon_id
      where ${redemptionFilters.join(' and ')}
      group by cr.created_at::date, cr.status
      order by date asc, cr.status
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select coalesce(c.campaign_name, '未分组') as "campaignName",
             count(cr.id)::int as "claimCount",
             count(cr.id) filter (where cr.status = 'used')::int as "usedCount",
             coalesce(sum(cr.discount_applied_cents) filter (where cr.status = 'used'), 0)::int as "discountCents"
      from public.coupon_redemptions cr
      left join public.coupons c on c.tenant_id = cr.tenant_id and c.id = cr.coupon_id
      where ${redemptionFilters.join(' and ')}
      group by coalesce(c.campaign_name, '未分组')
      order by "claimCount" desc
      `,
      params,
    ),
  ]);

  const summary = summaryRows[0] || {};
  return {
    item: {
      startDate,
      endDate,
      claimCount: intValue(summary.claimCount, 0),
      usedCount: intValue(summary.usedCount, 0),
      discountCents: intValue(summary.discountCents, 0),
      paidAmountCents: intValue(summary.paidAmountCents, 0),
      conversionRate: intValue(summary.claimCount, 0) > 0
        ? Number((intValue(summary.usedCount, 0) / intValue(summary.claimCount, 0)).toFixed(4))
        : 0,
      byCoupon: couponRows,
      byCampaign: campaignRows,
      daily: dailyRows,
    },
  };
}

export async function tenantMembersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'members:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const role = stringParam(ctx, 'role');
  const status = stringParam(ctx, 'status');
  const keyword = stringParam(ctx, 'keyword');
  const params: unknown[] = [auth.tenantId];
  const filters = ['tm.tenant_id = $1'];

  if (role) {
    if (!TENANT_MEMBER_ROLES.includes(role)) {
      throw new HttpError(400, `Invalid member role: ${role}`, 'INVALID_MEMBER_ROLE');
    }
    params.push(role);
    filters.push(`tm.role = $${params.length}`);
  }
  if (status) {
    if (!TENANT_MEMBER_STATUSES.includes(status)) {
      throw new HttpError(400, `Invalid member status: ${status}`, 'INVALID_MEMBER_STATUS');
    }
    params.push(status);
    filters.push(`tm.status = $${params.length}`);
  }
  if (keyword) {
    params.push(`%${keyword}%`);
    filters.push(`(
      u.username ilike $${params.length}
      or u.name ilike $${params.length}
      or u.phone ilike $${params.length}
      or u.email::text ilike $${params.length}
    )`);
  }
  params.push(limit);

  const items = await query(
    `
    select tm.id, tm.user_id as "userId", tm.role, tm.status, tm.permissions,
           tm.role_template_id as "roleTemplateId",
           rt.code as "roleTemplateCode",
           rt.name as "roleTemplateName",
           rt.menu_permissions as "menuPermissions",
           rt.module_permissions as "modulePermissions",
           rt.field_permissions as "fieldPermissions",
           rt.data_scope as "dataScope",
           tm.legacy_role as "legacyRole", tm.created_at as "createdAt", tm.updated_at as "updatedAt",
           u.username, u.email::text as email, u.phone, u.name, u.avatar_url as "avatarUrl",
           u.primary_role as "primaryRole", u.last_seen_at as "lastSeenAt"
    from public.tenant_memberships tm
    join public.platform_users u on u.id = tm.user_id
    left join public.tenant_role_templates rt on rt.id = tm.role_template_id and rt.tenant_id = tm.tenant_id
    where ${filters.join(' and ')}
    order by case tm.role
      when 'tenant_owner' then 1
      when 'tenant_admin' then 2
      when 'tenant_operator' then 3
      when 'teacher' then 4
      when 'sales' then 5
      when 'agent' then 6
      else 9
    end, tm.created_at asc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function upsertTenantMemberRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'members:write');
  const body = await readJsonBody(ctx);
  const membershipId = nullableString(body.membershipId) || nullableString(body.id);
  const roleTemplateId = nullableString(body.roleTemplateId);
  let role = requiredMemberRole(body.role);
  const status = optionalChoice(body.status, TENANT_MEMBER_STATUSES, 'active');
  const permissions = permissionValue(body.permissions);

  const item = await transaction(async client => {
    const template = await resolveRoleTemplateForMember(client, auth, roleTemplateId);
    if (template) role = template.baseRole;
    ensureCanGrantRole(auth, role, permissions);

    const userId = await resolveOrCreateMemberUser(client, body);
    if (userId === auth.userId && status === 'disabled') {
      throw new HttpError(400, 'Cannot disable your own tenant membership', 'CANNOT_DISABLE_SELF');
    }
    await ensureOwnerRemains(client, auth.tenantId, membershipId, role, status);

    let result;
    if (membershipId) {
      result = await client.query(
        `
        update public.tenant_memberships
        set user_id = $3,
            role = $4,
            status = $5,
            permissions = $6::jsonb,
            role_template_id = $7::uuid,
            updated_at = now()
        where tenant_id = $1 and id = $2
        returning id, user_id as "userId", role, status, permissions,
                  role_template_id as "roleTemplateId",
                  legacy_role as "legacyRole", created_at as "createdAt", updated_at as "updatedAt"
        `,
        [auth.tenantId, membershipId, userId, role, status, JSON.stringify(permissions), roleTemplateId],
      );
    } else {
      result = await client.query(
        `
        insert into public.tenant_memberships (tenant_id, user_id, role, status, permissions, role_template_id)
        values ($1, $2, $3, $4, $5::jsonb, $6::uuid)
        on conflict (tenant_id, user_id, role)
        do update set status = excluded.status,
                      permissions = excluded.permissions,
                      role_template_id = excluded.role_template_id,
                      updated_at = now()
        returning id, user_id as "userId", role, status, permissions,
                  role_template_id as "roleTemplateId",
                  legacy_role as "legacyRole", created_at as "createdAt", updated_at as "updatedAt"
        `,
        [auth.tenantId, userId, role, status, JSON.stringify(permissions), roleTemplateId],
      );
    }

    if (!result.rows[0]) throw new HttpError(404, 'Tenant member not found', 'TENANT_MEMBER_NOT_FOUND');

    await recordAudit(client, auth, 'tenant.member.upserted', 'tenant_memberships', result.rows[0].id, {
      userId,
      role,
      status,
      roleTemplateId,
      permissionKeys: Object.keys(permissions),
    });

    return result.rows[0];
  });

  return { item };
}

export async function disableTenantMemberRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'members:write');
  const body = await readJsonBody(ctx);
  const membershipId = requiredString(body, 'membershipId');

  const item = await transaction(async client => {
    const existing = await client.query<{ userId: string; role: string; status: string }>(
      `
      select user_id as "userId", role, status
      from public.tenant_memberships
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, membershipId],
    );
    if (!existing.rows[0]) throw new HttpError(404, 'Tenant member not found', 'TENANT_MEMBER_NOT_FOUND');
    if (existing.rows[0].userId === auth.userId) {
      throw new HttpError(400, 'Cannot disable your own tenant membership', 'CANNOT_DISABLE_SELF');
    }
    ensureCanGrantRole(auth, existing.rows[0].role, {});
    await ensureOwnerRemains(client, auth.tenantId, membershipId, existing.rows[0].role, 'disabled');

    const result = await client.query(
      `
      update public.tenant_memberships
      set status = 'disabled',
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, user_id as "userId", role, status, permissions,
                legacy_role as "legacyRole", created_at as "createdAt", updated_at as "updatedAt"
      `,
      [auth.tenantId, membershipId],
    );

    await recordAudit(client, auth, 'tenant.member.disabled', 'tenant_memberships', membershipId, {
      userId: result.rows[0].userId,
      role: result.rows[0].role,
    });

    return result.rows[0];
  });

  return { item };
}

export async function auditLogsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'audit:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const action = stringParam(ctx, 'action');
  const targetType = stringParam(ctx, 'targetType');
  const actorUserId = stringParam(ctx, 'actorUserId');
  const params: unknown[] = [auth.tenantId];
  const filters = ['al.tenant_id = $1'];

  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`);
  }
  params.push(limit);

  const items = await query(
    `
    select al.id, al.actor_user_id as "actorUserId", 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",
           u.username as "actorUsername", u.name as "actorName", u.phone as "actorPhone"
    from public.audit_logs al
    left join public.platform_users u on u.id = al.actor_user_id
    where ${filters.join(' and ')}
    order by al.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}
