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

const SETTLEMENT_STATUSES = ['draft', 'pending_review', 'approved', 'paid', 'rejected', 'cancelled'];
const PROOF_TYPES = ['payment', 'invoice', 'receipt', 'adjustment', 'other'];
const PROOF_STATUSES = ['submitted', 'approved', 'rejected', 'voided'];
const RATE_SOURCE_ORDER: Record<string, number> = { batch: 1, member: 2, default: 3 };

type JsonBody = Record<string, unknown>;

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

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

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

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

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 rateValue(value: unknown, fallback = 0.2) {
  const rate = numberValue(value, fallback);
  if (rate < 0 || rate > 1) {
    throw new HttpError(400, 'Commission rate must be between 0 and 1', 'INVALID_COMMISSION_RATE');
  }
  return Number(rate.toFixed(4));
}

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

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

function periodFromParams(ctx: RequestContext) {
  const today = shanghaiDateKey(new Date());
  const startDate = dateValue(stringParam(ctx, 'startDate'), today.slice(0, 8) + '01');
  const endDate = dateValue(stringParam(ctx, 'endDate'), today);
  if (startDate > endDate) {
    throw new HttpError(400, 'startDate must be before or equal to endDate', 'INVALID_DATE_RANGE');
  }
  return { startDate, endDate };
}

function canViewAllReferral(auth: TenantAdminAuth) {
  return hasTenantPermission(auth, 'commission:read');
}

function canViewSelfReferral(auth: TenantAdminAuth) {
  return hasTenantPermission(auth, 'commission:self') || canViewAllReferral(auth);
}

function canWriteCommission(auth: TenantAdminAuth) {
  return hasTenantPermission(auth, 'commission:write');
}

function canReviewCommission(auth: TenantAdminAuth) {
  return hasTenantPermission(auth, 'commission:review');
}

function requireCommissionRead(auth: TenantAdminAuth) {
  if (!canViewSelfReferral(auth)) {
    throw new HttpError(403, 'Commission access is required', 'COMMISSION_ACCESS_REQUIRED');
  }
}

function requireCommissionWrite(auth: TenantAdminAuth) {
  if (!canWriteCommission(auth)) {
    throw new HttpError(403, 'Commission write permission is required', 'TENANT_PERMISSION_REQUIRED');
  }
}

function assertSettlementTransition(currentStatus: string, nextStatus: string) {
  if (currentStatus === 'paid') {
    throw new HttpError(409, 'Paid commission settlements are immutable', 'COMMISSION_SETTLEMENT_LOCKED');
  }
  if (nextStatus === 'paid' && currentStatus !== 'approved') {
    throw new HttpError(409, 'Settlement must be approved before marking paid', 'COMMISSION_SETTLEMENT_NOT_APPROVED');
  }
  if (currentStatus === 'approved' && !['paid', 'cancelled'].includes(nextStatus)) {
    throw new HttpError(409, 'Approved settlement can only be paid or cancelled', 'INVALID_SETTLEMENT_TRANSITION');
  }
  if (['rejected', 'cancelled'].includes(currentStatus) && currentStatus !== nextStatus) {
    throw new HttpError(409, 'Closed commission settlement cannot change status', 'COMMISSION_SETTLEMENT_CLOSED');
  }
}

function restrictReferrerScope(auth: TenantAdminAuth, requestedReferrerId: string | null) {
  if (canViewAllReferral(auth)) return requestedReferrerId;
  if (requestedReferrerId && requestedReferrerId !== auth.userId) {
    throw new HttpError(403, 'Only own commission data can be viewed', 'COMMISSION_SCOPE_REQUIRED');
  }
  return auth.userId;
}

function rowToCommissionItem(row: Record<string, unknown>) {
  const grossAmountCents = intValue(row.grossAmountCents);
  const commissionAmountCents = intValue(row.commissionAmountCents);
  const commissionRate = numberValue(row.commissionRate);
  return {
    sourceType: String(row.sourceType),
    sourceId: String(row.sourceId),
    sourceNo: row.sourceNo ? String(row.sourceNo) : null,
    sourcePaidAt: row.sourcePaidAt,
    referrerUserId: String(row.referrerUserId),
    referrerName: row.referrerName ? String(row.referrerName) : null,
    referrerPhone: row.referrerPhone ? String(row.referrerPhone) : null,
    studentUserId: row.studentUserId ? String(row.studentUserId) : null,
    studentName: row.studentName ? String(row.studentName) : null,
    studentPhone: row.studentPhone ? String(row.studentPhone) : null,
    grossAmountCents,
    commissionRate,
    commissionAmountCents,
    rateSource: String(row.rateSource || 'default'),
    attributionType: String(row.attributionType || 'protected_lead'),
    settlementId: row.settlementId ? String(row.settlementId) : null,
    settlementStatus: row.settlementStatus ? String(row.settlementStatus) : null,
  };
}

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

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

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

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

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

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

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

function summarizeCommissionRows(rows: Record<string, unknown>[]) {
  const seenUsers = new Set<string>();
  const byReferrer = new Map<string, {
    referrerUserId: string;
    referrerName: string | null;
    referrerPhone: string | null;
    sourceCount: number;
    paidUserCount: number;
    grossAmountCents: number;
    commissionAmountCents: number;
    effectiveRate: number;
    rateSource: string;
  }>();

  let grossAmountCents = 0;
  let commissionAmountCents = 0;
  for (const row of rows) {
    const referrerUserId = String(row.referrerUserId);
    const gross = intValue(row.grossAmountCents);
    const commission = intValue(row.commissionAmountCents);
    const studentUserId = row.studentUserId ? String(row.studentUserId) : '';
    if (studentUserId) seenUsers.add(studentUserId);
    grossAmountCents += gross;
    commissionAmountCents += commission;

    const current = byReferrer.get(referrerUserId) || {
      referrerUserId,
      referrerName: row.referrerName ? String(row.referrerName) : null,
      referrerPhone: row.referrerPhone ? String(row.referrerPhone) : null,
      sourceCount: 0,
      paidUserCount: 0,
      grossAmountCents: 0,
      commissionAmountCents: 0,
      effectiveRate: numberValue(row.commissionRate),
      rateSource: String(row.rateSource || 'default'),
    };
    current.sourceCount += 1;
    current.grossAmountCents += gross;
    current.commissionAmountCents += commission;
    if (RATE_SOURCE_ORDER[String(row.rateSource)] < RATE_SOURCE_ORDER[current.rateSource]) {
      current.rateSource = String(row.rateSource);
      current.effectiveRate = numberValue(row.commissionRate);
    }
    byReferrer.set(referrerUserId, current);
  }

  const paidUsersByReferrer = new Map<string, Set<string>>();
  for (const row of rows) {
    const referrerUserId = String(row.referrerUserId);
    const studentUserId = row.studentUserId ? String(row.studentUserId) : '';
    if (!studentUserId) continue;
    const set = paidUsersByReferrer.get(referrerUserId) || new Set<string>();
    set.add(studentUserId);
    paidUsersByReferrer.set(referrerUserId, set);
  }

  return {
    sourceCount: rows.length,
    paidUserCount: seenUsers.size,
    grossAmountCents,
    commissionAmountCents,
    byReferrer: Array.from(byReferrer.values()).map(item => ({
      ...item,
      paidUserCount: paidUsersByReferrer.get(item.referrerUserId)?.size || 0,
      effectiveRate: Number(item.effectiveRate.toFixed(4)),
    })),
  };
}

async function commissionSettings(tenantId: string) {
  const item = await queryOne<{
    defaultRate: string;
    minSettlementCents: number;
    settlementCycle: string;
    config: Record<string, unknown>;
  }>(
    `
    select default_rate as "defaultRate",
           min_settlement_cents as "minSettlementCents",
           settlement_cycle as "settlementCycle",
           config
    from public.tenant_commission_settings
    where tenant_id = $1
    limit 1
    `,
    [tenantId],
  );
  return {
    defaultRate: numberValue(item?.defaultRate, 0.2),
    minSettlementCents: intValue(item?.minSettlementCents),
    settlementCycle: item?.settlementCycle || 'monthly',
    config: item?.config || {},
  };
}

async function commissionRows(
  tenantId: string,
  startDate: string,
  endDate: string,
  referrerUserId: string | null,
  limit: number | null,
) {
  const params: unknown[] = [tenantId, startDate, endDate, referrerUserId];
  const limitSql = limit ? `limit $5` : '';
  if (limit) params.push(limit);
  return query<Record<string, unknown>>(
    `
    with settings as (
      select coalesce(default_rate, 0.2000)::numeric(6,4) as default_rate
      from public.tenant_commission_settings
      where tenant_id = $1
      union all
      select 0.2000::numeric(6,4)
      limit 1
    ),
    order_sources as (
      select 'order'::text as source_type,
             o.id as source_id,
             o.order_no as source_no,
             coalesce(o.paid_at, o.updated_at, o.created_at) as source_paid_at,
             o.user_id as student_user_id,
             rl.referrer_user_id,
             o.amount_cents::integer as gross_amount_cents,
             null::numeric(6,4) as batch_rate,
             o.raw_payload as metadata
      from public.orders o
      join public.referral_leads rl
        on rl.tenant_id = o.tenant_id
       and rl.student_user_id = o.user_id
       and rl.status = 'protected'
      where o.tenant_id = $1
        and o.status = 'paid'
        and o.user_id is not null
        and coalesce(o.paid_at, o.updated_at, o.created_at) >= ($2::date::timestamp at time zone 'Asia/Shanghai')
        and coalesce(o.paid_at, o.updated_at, o.created_at) < (($3::date + 1)::timestamp at time zone 'Asia/Shanghai')
        and coalesce(o.paid_at, o.updated_at, o.created_at) >= rl.bound_at
    ),
    code_sources as (
      select 'activation_code'::text as source_type,
             ac.id as source_id,
             ac.code::text as source_no,
             ac.used_at as source_paid_at,
             ac.used_by as student_user_id,
             rl.referrer_user_id,
             greatest(coalesce(ac.unit_price_cents, cb.default_unit_price_cents, 0), 0)::integer as gross_amount_cents,
             cb.commission_rate as batch_rate,
             jsonb_build_object('saleType', ac.sale_type, 'batchId', ac.batch_id, 'days', ac.days) as metadata
      from public.activation_codes ac
      left join public.code_batches cb on cb.tenant_id = ac.tenant_id and cb.id = ac.batch_id
      join public.referral_leads rl
        on rl.tenant_id = ac.tenant_id
       and rl.student_user_id = ac.used_by
       and rl.status = 'protected'
      where ac.tenant_id = $1
        and ac.is_used is true
        and ac.used_by is not null
        and ac.used_at >= ($2::date::timestamp at time zone 'Asia/Shanghai')
        and ac.used_at < (($3::date + 1)::timestamp at time zone 'Asia/Shanghai')
        and ac.used_at >= rl.bound_at
    ),
    sources as (
      select * from order_sources
      union all
      select * from code_sources
    ),
    referrer_members as (
      select distinct on (tm.user_id)
             tm.user_id,
             tm.commission_rate,
             tm.role
      from public.tenant_memberships tm
      where tm.tenant_id = $1
        and tm.status = 'active'
        and tm.role in ('tenant_owner', 'tenant_admin', 'tenant_operator', 'teacher', 'sales', 'agent')
      order by tm.user_id,
               case tm.role
                 when 'sales' then 1
                 when 'agent' then 2
                 when 'tenant_operator' then 3
                 when 'teacher' then 4
                 else 9
               end
    )
    select s.source_type as "sourceType",
           s.source_id as "sourceId",
           s.source_no as "sourceNo",
           s.source_paid_at as "sourcePaidAt",
           s.student_user_id as "studentUserId",
           student.name as "studentName",
           student.phone as "studentPhone",
           s.referrer_user_id as "referrerUserId",
           referrer.name as "referrerName",
           referrer.phone as "referrerPhone",
           s.gross_amount_cents as "grossAmountCents",
           coalesce(s.batch_rate, rm.commission_rate, settings.default_rate)::numeric(6,4) as "commissionRate",
           round(s.gross_amount_cents * coalesce(s.batch_rate, rm.commission_rate, settings.default_rate))::integer as "commissionAmountCents",
           case
             when s.batch_rate is not null then 'batch'
             when rm.commission_rate is not null then 'member'
             else 'default'
           end as "rateSource",
           'protected_lead'::text as "attributionType",
           csi.settlement_id as "settlementId",
           cs.status as "settlementStatus",
           s.metadata
    from sources s
    cross join settings
    left join referrer_members rm on rm.user_id = s.referrer_user_id
    join public.platform_users referrer on referrer.id = s.referrer_user_id
    left join public.platform_users student on student.id = s.student_user_id
    left join public.commission_settlement_items csi
      on csi.tenant_id = $1
     and csi.source_type = s.source_type
     and csi.source_id = s.source_id
    left join public.commission_settlements cs
      on cs.tenant_id = csi.tenant_id
     and cs.id = csi.settlement_id
    where s.gross_amount_cents > 0
      and ($4::uuid is null or s.referrer_user_id = $4::uuid)
    order by s.source_paid_at desc, s.source_no desc
    ${limitSql}
    `,
    params,
  );
}

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)],
  );
}

export async function commissionSettingsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'commission:read');
  const item = await commissionSettings(auth.tenantId);
  return { item };
}

export async function updateCommissionSettingsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'commission:write');
  const body = await readJsonBody(ctx);
  const defaultRate = rateValue(body.defaultRate, 0.2);
  const minSettlementCents = Math.max(0, intValue(body.minSettlementCents, 0));
  const settlementCycle = optionalChoice(body.settlementCycle, ['manual', 'weekly', 'monthly'], 'monthly');

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.tenant_commission_settings (
        tenant_id, default_rate, min_settlement_cents, settlement_cycle, config, updated_by
      )
      values ($1, $2, $3, $4, $5::jsonb, $6)
      on conflict (tenant_id)
      do update set default_rate = excluded.default_rate,
                    min_settlement_cents = excluded.min_settlement_cents,
                    settlement_cycle = excluded.settlement_cycle,
                    config = excluded.config,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      returning default_rate as "defaultRate", min_settlement_cents as "minSettlementCents",
                settlement_cycle as "settlementCycle", config, updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        defaultRate,
        minSettlementCents,
        settlementCycle,
        JSON.stringify(objectValue(body.config)),
        auth.userId,
      ],
    );
    await recordAudit(client, auth, 'commission.settings.updated', 'tenant_commission_settings', auth.tenantId, {
      defaultRate,
      minSettlementCents,
      settlementCycle,
    });
    return result.rows[0];
  });

  return { item };
}

export async function updateMemberCommissionRateRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'commission:write');
  const body = await readJsonBody(ctx);
  const userId = requiredString(body, 'userId');
  const rawRate = body.commissionRate;
  const commissionRate = rawRate === null || rawRate === undefined || rawRate === ''
    ? null
    : rateValue(rawRate, 0);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      update public.tenant_memberships
      set commission_rate = $3,
          commission_config = coalesce($4::jsonb, commission_config),
          updated_at = now()
      where tenant_id = $1
        and user_id = $2
        and role in ('sales', 'agent', 'tenant_operator', 'teacher', 'tenant_admin', 'tenant_owner')
        and status = 'active'
      returning id, user_id as "userId", role, commission_rate as "commissionRate",
                commission_config as "commissionConfig", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        userId,
        commissionRate,
        body.commissionConfig === undefined ? null : JSON.stringify(objectValue(body.commissionConfig)),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Commission member not found', 'COMMISSION_MEMBER_NOT_FOUND');
    await recordAudit(client, auth, 'commission.member_rate.updated', 'tenant_memberships', result.rows[0].id, {
      userId,
      commissionRate,
    });
    return result.rows[0];
  });

  return { item };
}

export async function commissionSummaryRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireCommissionRead(auth);
  const { startDate, endDate } = periodFromParams(ctx);
  const scopedReferrerId = restrictReferrerScope(auth, nullableString(stringParam(ctx, 'referrerUserId')));
  const rows = await commissionRows(auth.tenantId, startDate, endDate, scopedReferrerId, null);
  const settings = await commissionSettings(auth.tenantId);
  return {
    item: {
      tenantId: auth.tenantId,
      startDate,
      endDate,
      referrerUserId: scopedReferrerId,
      defaultRate: settings.defaultRate,
      minSettlementCents: settings.minSettlementCents,
      ...summarizeCommissionRows(rows),
    },
  };
}

export async function commissionOrdersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireCommissionRead(auth);
  const { startDate, endDate } = periodFromParams(ctx);
  const scopedReferrerId = restrictReferrerScope(auth, nullableString(stringParam(ctx, 'referrerUserId')));
  const limit = intParam(ctx, 'limit', 100, 500);
  const rows = await commissionRows(auth.tenantId, startDate, endDate, scopedReferrerId, limit);
  return {
    items: rows.map(rowToCommissionItem),
    scope: { tenantId: auth.tenantId, startDate, endDate, referrerUserId: scopedReferrerId },
  };
}

export async function commissionSettlementsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireCommissionRead(auth);
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const requestedReferrerId = nullableString(stringParam(ctx, 'referrerUserId'));
  const scopedReferrerId = restrictReferrerScope(auth, requestedReferrerId);
  const params: unknown[] = [auth.tenantId];
  const filters = ['cs.tenant_id = $1'];
  if (status) {
    if (!SETTLEMENT_STATUSES.includes(status)) {
      throw new HttpError(400, 'Invalid settlement status', 'INVALID_SETTLEMENT_STATUS');
    }
    params.push(status);
    filters.push(`cs.status = $${params.length}`);
  }
  if (scopedReferrerId) {
    params.push(scopedReferrerId);
    filters.push(`cs.referrer_user_id = $${params.length}::uuid`);
  }
  params.push(limit);

  const items = await query(
    `
    select cs.id, cs.settlement_no as "settlementNo", cs.referrer_user_id as "referrerUserId",
           u.name as "referrerName", u.phone as "referrerPhone", cs.status,
           cs.period_start as "periodStart", cs.period_end as "periodEnd",
           cs.source_count as "sourceCount", cs.paid_user_count as "paidUserCount",
           cs.gross_amount_cents as "grossAmountCents",
           cs.commission_amount_cents as "commissionAmountCents",
           cs.default_rate as "defaultRate", cs.effective_rate as "effectiveRate",
           cs.reviewed_by as "reviewedBy", cs.reviewed_at as "reviewedAt",
           cs.paid_by as "paidBy", cs.paid_at as "paidAt",
           cs.payment_method as "paymentMethod", cs.payment_account as "paymentAccount",
           cs.remark, cs.metadata, cs.created_at as "createdAt", cs.updated_at as "updatedAt"
    from public.commission_settlements cs
    join public.platform_users u on u.id = cs.referrer_user_id
    where ${filters.join(' and ')}
    order by cs.period_start desc, cs.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

async function settlementForScope(auth: TenantAdminAuth, settlementId: string) {
  const item = await queryOne<Record<string, unknown>>(
    `
    select cs.id, cs.settlement_no as "settlementNo", cs.referrer_user_id as "referrerUserId",
           u.name as "referrerName", u.phone as "referrerPhone", cs.status,
           cs.period_start as "periodStart", cs.period_end as "periodEnd",
           cs.source_count as "sourceCount", cs.paid_user_count as "paidUserCount",
           cs.gross_amount_cents as "grossAmountCents",
           cs.commission_amount_cents as "commissionAmountCents",
           cs.default_rate as "defaultRate", cs.effective_rate as "effectiveRate",
           cs.reviewed_by as "reviewedBy", cs.reviewed_at as "reviewedAt",
           cs.paid_by as "paidBy", cs.paid_at as "paidAt",
           cs.payment_method as "paymentMethod", cs.payment_account as "paymentAccount",
           cs.remark, cs.metadata, cs.created_at as "createdAt", cs.updated_at as "updatedAt"
    from public.commission_settlements cs
    join public.platform_users u on u.id = cs.referrer_user_id
    where cs.tenant_id = $1 and cs.id = $2
    limit 1
    `,
    [auth.tenantId, settlementId],
  );
  if (!item) throw new HttpError(404, 'Commission settlement not found', 'COMMISSION_SETTLEMENT_NOT_FOUND');
  restrictReferrerScope(auth, String(item.referrerUserId));
  return item;
}

async function settlementItems(tenantId: string, settlementId: string) {
  return query<Record<string, unknown>>(
    `
    select csi.id, csi.source_type as "sourceType", csi.source_id as "sourceId",
           csi.source_no as "sourceNo", csi.source_paid_at as "sourcePaidAt",
           csi.student_user_id as "studentUserId", student.name as "studentName",
           student.phone as "studentPhone", csi.referrer_user_id as "referrerUserId",
           referrer.name as "referrerName", referrer.phone as "referrerPhone",
           csi.gross_amount_cents as "grossAmountCents",
           csi.commission_rate as "commissionRate",
           csi.commission_amount_cents as "commissionAmountCents",
           csi.rate_source as "rateSource", csi.attribution_type as "attributionType",
           csi.metadata, csi.created_at as "createdAt"
    from public.commission_settlement_items csi
    join public.platform_users referrer on referrer.id = csi.referrer_user_id
    left join public.platform_users student on student.id = csi.student_user_id
    where csi.tenant_id = $1 and csi.settlement_id = $2
    order by csi.source_paid_at asc nulls last, csi.source_no asc nulls last
    `,
    [tenantId, settlementId],
  );
}

export async function commissionSettlementExportRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireCommissionRead(auth);
  const settlementId = stringParam(ctx, 'settlementId');
  if (!settlementId) throw new HttpError(400, 'settlementId is required', 'REQUIRED_FIELD');
  const format = optionalChoice(ctx.url.searchParams.get('format'), ['csv', 'json'], 'csv');
  const settlement = await settlementForScope(auth, settlementId);
  const rows = await settlementItems(auth.tenantId, settlementId);
  const filename = `${settlement.settlementNo || settlementId}.${format}`;

  const payload = {
    settlement,
    items: rows.map(rowToCommissionItem),
    exportedAt: new Date().toISOString(),
  };
  const content = format === 'json'
    ? JSON.stringify(payload, null, 2)
    : [
      csvLine([
        'settlementNo',
        'referrerName',
        'referrerPhone',
        'periodStart',
        'periodEnd',
        'sourceType',
        'sourceNo',
        'sourcePaidAt',
        'studentName',
        'studentPhone',
        'grossAmountCents',
        'commissionRate',
        'commissionAmountCents',
        'rateSource',
      ]),
      ...rows.map(row => csvLine([
        settlement.settlementNo,
        settlement.referrerName,
        settlement.referrerPhone,
        settlement.periodStart,
        settlement.periodEnd,
        row.sourceType,
        row.sourceNo,
        row.sourcePaidAt,
        row.studentName,
        row.studentPhone,
        row.grossAmountCents,
        row.commissionRate,
        row.commissionAmountCents,
        row.rateSource,
      ])),
    ].join('\n');
  const encoded = contentBase64AndHash(content);

  await transaction(async client => {
    await client.query(
      `
      insert into public.commission_settlement_export_events (
        tenant_id, settlement_id, export_format, filename, row_count, content_sha256, exported_by, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8::jsonb)
      `,
      [
        auth.tenantId,
        settlementId,
        format,
        filename,
        rows.length,
        encoded.sha256,
        auth.userId,
        JSON.stringify({ sizeBytes: encoded.sizeBytes }),
      ],
    );
    await recordAudit(client, auth, 'commission.settlement.exported', 'commission_settlements', settlementId, {
      format,
      filename,
      rowCount: rows.length,
      sha256: encoded.sha256,
    });
  });

  return {
    item: {
      settlementId,
      filename,
      format,
      mimeType: format === 'json' ? 'application/json' : 'text/csv',
      rowCount: rows.length,
      ...encoded,
    },
  };
}

export async function commissionSettlementProofsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireCommissionRead(auth);
  const settlementId = stringParam(ctx, 'settlementId');
  if (!settlementId) throw new HttpError(400, 'settlementId is required', 'REQUIRED_FIELD');
  await settlementForScope(auth, settlementId);
  const items = await query(
    `
    select p.id, p.settlement_id as "settlementId", p.proof_type as "proofType", p.status,
           p.title, p.description, p.asset_id as "assetId", p.external_url as "externalUrl",
           p.amount_cents as "amountCents", p.payment_method as "paymentMethod",
           p.payment_account as "paymentAccount", p.paid_at as "paidAt",
           p.submitted_by as "submittedBy", submitter.name as "submittedByName",
           p.reviewed_by as "reviewedBy", reviewer.name as "reviewedByName",
           p.reviewed_at as "reviewedAt", p.review_note as "reviewNote",
           p.metadata, p.created_at as "createdAt", p.updated_at as "updatedAt"
    from public.commission_settlement_proofs p
    left join public.platform_users submitter on submitter.id = p.submitted_by
    left join public.platform_users reviewer on reviewer.id = p.reviewed_by
    where p.tenant_id = $1 and p.settlement_id = $2
    order by p.created_at desc
    `,
    [auth.tenantId, settlementId],
  );
  return { items };
}

export async function createCommissionSettlementProofRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  if (!canReviewCommission(auth)) {
    throw new HttpError(403, 'Commission review permission is required', 'TENANT_PERMISSION_REQUIRED');
  }
  const body = await readJsonBody(ctx);
  const settlementId = requiredString(body, 'settlementId');
  const settlement = await settlementForScope(auth, settlementId);
  const proofType = optionalChoice(body.proofType, PROOF_TYPES, 'payment');
  const amountCents = body.amountCents === undefined || body.amountCents === null
    ? intValue(settlement.commissionAmountCents)
    : Math.max(0, intValue(body.amountCents));
  const externalUrl = externalProofUrl(body.externalUrl);
  const paidAt = optionalTimestamp(body.paidAt, 'paidAt');

  const item = await transaction(async client => {
    const assetId = await assertTenantAsset(client, auth.tenantId, nullableString(body.assetId));
    const result = await client.query(
      `
      insert into public.commission_settlement_proofs (
        tenant_id, settlement_id, proof_type, status, title, description,
        asset_id, external_url, amount_cents, payment_method, payment_account,
        paid_at, submitted_by, metadata
      )
      values ($1, $2, $3, 'submitted', $4, $5, $6, $7, $8, $9, $10, $11, $12, $13::jsonb)
      returning id, settlement_id as "settlementId", proof_type as "proofType", status,
                title, description, asset_id as "assetId", external_url as "externalUrl",
                amount_cents as "amountCents", payment_method as "paymentMethod",
                payment_account as "paymentAccount", paid_at as "paidAt",
                submitted_by as "submittedBy", metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        settlementId,
        proofType,
        proofTitle(body.title, `${settlement.settlementNo || settlementId} 打款凭证`),
        nullableString(body.description),
        assetId,
        externalUrl,
        amountCents,
        nullableString(body.paymentMethod),
        nullableString(body.paymentAccount),
        paidAt,
        auth.userId,
        JSON.stringify(objectValue(body.metadata)),
      ],
    );
    await recordAudit(client, auth, 'commission.settlement.proof_submitted', 'commission_settlement_proofs', result.rows[0].id, {
      settlementId,
      proofType,
      amountCents,
      hasAsset: Boolean(assetId),
      hasExternalUrl: Boolean(externalUrl),
    });
    return result.rows[0];
  });

  return { item };
}

export async function updateCommissionSettlementProofStatusRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  if (!canReviewCommission(auth)) {
    throw new HttpError(403, 'Commission review permission is required', 'TENANT_PERMISSION_REQUIRED');
  }
  const body = await readJsonBody(ctx);
  const proofId = requiredString(body, 'proofId');
  const status = optionalChoice(body.status, PROOF_STATUSES, 'approved');
  if (status === 'submitted') {
    throw new HttpError(400, 'Cannot move proof back to submitted through status API', 'INVALID_PROOF_STATUS');
  }

  const item = await transaction(async client => {
    const current = await client.query<{ id: string; settlementId: string; status: string }>(
      `
      select id, settlement_id as "settlementId", status
      from public.commission_settlement_proofs
      where tenant_id = $1 and id = $2
      limit 1
      for update
      `,
      [auth.tenantId, proofId],
    );
    const proof = current.rows[0];
    if (!proof) throw new HttpError(404, 'Commission settlement proof not found', 'COMMISSION_PROOF_NOT_FOUND');
    await settlementForScope(auth, proof.settlementId);
    if (['approved', 'rejected', 'voided'].includes(proof.status) && proof.status !== status) {
      throw new HttpError(409, 'Closed proof cannot change status', 'COMMISSION_PROOF_CLOSED');
    }

    const result = await client.query(
      `
      update public.commission_settlement_proofs
      set status = $3,
          reviewed_by = $4,
          reviewed_at = now(),
          review_note = coalesce($5, review_note),
          metadata = metadata || $6::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, settlement_id as "settlementId", proof_type as "proofType", status,
                title, description, asset_id as "assetId", external_url as "externalUrl",
                amount_cents as "amountCents", payment_method as "paymentMethod",
                payment_account as "paymentAccount", paid_at as "paidAt",
                submitted_by as "submittedBy", reviewed_by as "reviewedBy",
                reviewed_at as "reviewedAt", review_note as "reviewNote",
                metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [auth.tenantId, proofId, status, auth.userId, nullableString(body.reviewNote), JSON.stringify(objectValue(body.metadata))],
    );
    await recordAudit(client, auth, 'commission.settlement.proof_status_updated', 'commission_settlement_proofs', proofId, {
      settlementId: proof.settlementId,
      status,
    });
    return result.rows[0];
  });

  return { item };
}

export async function generateCommissionSettlementRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireCommissionWrite(auth);
  const body = await readJsonBody(ctx);
  const startDate = dateValue(nullableString(body.startDate) || '', '');
  const endDate = dateValue(nullableString(body.endDate) || '', '');
  const referrerUserId = requiredString(body, 'referrerUserId');
  if (startDate > endDate) throw new HttpError(400, 'startDate must be before or equal to endDate', 'INVALID_DATE_RANGE');

  const item = await transaction(async client => {
    const member = await client.query<{ userId: string }>(
      `
      select user_id as "userId"
      from public.tenant_memberships
      where tenant_id = $1
        and user_id = $2
        and status = 'active'
        and role in ('sales', 'agent', 'tenant_operator', 'teacher', 'tenant_admin', 'tenant_owner')
      limit 1
      `,
      [auth.tenantId, referrerUserId],
    );
    if (!member.rows[0]) throw new HttpError(404, 'Referrer member not found', 'COMMISSION_MEMBER_NOT_FOUND');

    const rows = await commissionRows(auth.tenantId, startDate, endDate, referrerUserId, null);
    const unsettledRows = rows.filter(row => !row.settlementId);
    const summary = summarizeCommissionRows(unsettledRows);
    const settings = await commissionSettings(auth.tenantId);
    if (summary.sourceCount <= 0) {
      throw new HttpError(409, 'No unsettled commission sources found in this period', 'COMMISSION_NO_UNSETTLED_SOURCES');
    }
    if (summary.commissionAmountCents < settings.minSettlementCents) {
      throw new HttpError(409, 'Commission amount is below tenant settlement minimum', 'COMMISSION_BELOW_MINIMUM');
    }

    const settlementNo = `COMM-${startDate.replace(/-/g, '')}-${endDate.replace(/-/g, '')}-${referrerUserId.slice(0, 8)}`;
    const referrerSummary = summary.byReferrer[0] || {
      effectiveRate: settings.defaultRate,
      rateSource: 'default',
    };

    const settlement = await client.query(
      `
      insert into public.commission_settlements (
        tenant_id, settlement_no, referrer_user_id, status, period_start, period_end,
        source_count, paid_user_count, gross_amount_cents, commission_amount_cents,
        default_rate, effective_rate, generated_by, remark, metadata
      )
      values (
        $1, $2, $3, $4, $5::date, $6::date,
        $7, $8, $9, $10, $11, $12, $13, $14, $15::jsonb
      )
      on conflict (tenant_id, referrer_user_id, period_start, period_end)
      do update set status = case
                        when public.commission_settlements.status in ('paid', 'approved')
                          then public.commission_settlements.status
                        else excluded.status
                      end,
                    source_count = excluded.source_count,
                    paid_user_count = excluded.paid_user_count,
                    gross_amount_cents = excluded.gross_amount_cents,
                    commission_amount_cents = excluded.commission_amount_cents,
                    default_rate = excluded.default_rate,
                    effective_rate = excluded.effective_rate,
                    generated_by = excluded.generated_by,
                    remark = excluded.remark,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id, settlement_no as "settlementNo", referrer_user_id as "referrerUserId",
                status, period_start as "periodStart", period_end as "periodEnd",
                source_count as "sourceCount", paid_user_count as "paidUserCount",
                gross_amount_cents as "grossAmountCents",
                commission_amount_cents as "commissionAmountCents",
                default_rate as "defaultRate", effective_rate as "effectiveRate",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        settlementNo,
        referrerUserId,
        optionalChoice(body.status, ['draft', 'pending_review'], 'pending_review'),
        startDate,
        endDate,
        summary.sourceCount,
        summary.paidUserCount,
        summary.grossAmountCents,
        summary.commissionAmountCents,
        settings.defaultRate,
        referrerSummary.effectiveRate,
        auth.userId,
        nullableString(body.remark),
        JSON.stringify({ rateSource: referrerSummary.rateSource, generatedFrom: 'api' }),
      ],
    );

    for (const row of unsettledRows) {
      await client.query(
        `
        insert into public.commission_settlement_items (
          tenant_id, settlement_id, referrer_user_id, student_user_id,
          source_type, source_id, source_no, source_paid_at,
          gross_amount_cents, commission_rate, commission_amount_cents,
          rate_source, attribution_type, metadata
        )
        values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14::jsonb)
        on conflict (tenant_id, source_type, source_id)
        do update set settlement_id = excluded.settlement_id,
                      commission_rate = excluded.commission_rate,
                      commission_amount_cents = excluded.commission_amount_cents,
                      rate_source = excluded.rate_source,
                      metadata = excluded.metadata,
                      updated_at = now()
        `,
        [
          auth.tenantId,
          settlement.rows[0].id,
          row.referrerUserId,
          row.studentUserId,
          row.sourceType,
          row.sourceId,
          row.sourceNo,
          row.sourcePaidAt,
          intValue(row.grossAmountCents),
          numberValue(row.commissionRate),
          intValue(row.commissionAmountCents),
          row.rateSource,
          row.attributionType,
          JSON.stringify(row.metadata || {}),
        ],
      );
    }

    await recordAudit(client, auth, 'commission.settlement.generated', 'commission_settlements', settlement.rows[0].id, {
      referrerUserId,
      startDate,
      endDate,
      sourceCount: summary.sourceCount,
      commissionAmountCents: summary.commissionAmountCents,
    });
    return { ...settlement.rows[0], items: unsettledRows.map(rowToCommissionItem) };
  });

  return { item };
}

export async function updateCommissionSettlementStatusRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  if (!canReviewCommission(auth)) {
    throw new HttpError(403, 'Commission review permission is required', 'TENANT_PERMISSION_REQUIRED');
  }
  const body = await readJsonBody(ctx);
  const settlementId = requiredString(body, 'settlementId');
  const status = optionalChoice(body.status, SETTLEMENT_STATUSES, 'approved');
  if (status === 'draft') {
    throw new HttpError(400, 'Cannot move settlement back to draft through status API', 'INVALID_SETTLEMENT_STATUS');
  }

  const item = await transaction(async client => {
    const current = await client.query<{ status: string }>(
      `
      select status
      from public.commission_settlements
      where tenant_id = $1 and id = $2
      limit 1
      for update
      `,
      [auth.tenantId, settlementId],
    );
    if (!current.rows[0]) throw new HttpError(404, 'Commission settlement not found', 'COMMISSION_SETTLEMENT_NOT_FOUND');
    assertSettlementTransition(current.rows[0].status, status);

    const result = await client.query(
      `
      update public.commission_settlements
      set status = $3,
          reviewed_by = case when $3 in ('approved', 'rejected', 'cancelled') then $4 else reviewed_by end,
          reviewed_at = case when $3 in ('approved', 'rejected', 'cancelled') then now() else reviewed_at end,
          paid_by = case when $3 = 'paid' then $4 else paid_by end,
          paid_at = case when $3 = 'paid' then now() else paid_at end,
          payment_method = coalesce($5, payment_method),
          payment_account = coalesce($6, payment_account),
          remark = coalesce($7, remark),
          metadata = metadata || $8::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, settlement_no as "settlementNo", referrer_user_id as "referrerUserId",
                status, period_start as "periodStart", period_end as "periodEnd",
                source_count as "sourceCount", paid_user_count as "paidUserCount",
                gross_amount_cents as "grossAmountCents",
                commission_amount_cents as "commissionAmountCents",
                reviewed_by as "reviewedBy", reviewed_at as "reviewedAt",
                paid_by as "paidBy", paid_at as "paidAt",
                payment_method as "paymentMethod", payment_account as "paymentAccount",
                remark, metadata, updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        settlementId,
        status,
        auth.userId,
        nullableString(body.paymentMethod),
        nullableString(body.paymentAccount),
        nullableString(body.remark),
        JSON.stringify(objectValue(body.metadata)),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Commission settlement not found', 'COMMISSION_SETTLEMENT_NOT_FOUND');
    await recordAudit(client, auth, 'commission.settlement.status_updated', 'commission_settlements', settlementId, {
      status,
    });
    return result.rows[0];
  });

  return { item };
}
