import { randomUUID } from 'node:crypto';
import type pg from 'pg';
import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, readJsonBody, stringParam, tenantIdFrom, userIdFrom } from '../../core/request.js';
import { query, transaction } from '../../core/db.js';
import { autoGrantBadges } from './badges.js';
import { createUserNotification } from '../notifications/service.js';

type JsonMap = Record<string, unknown>;

const TASK_TYPES = [
  'daily_check_in',
  'feedback_submit',
  'feedback_resolved',
  'practice_complete',
  'vocabulary_review',
  'mock_exam_submit',
  'manual',
];
const PERIOD_TYPES = ['once', 'daily', 'weekly', 'monthly', 'unlimited'];
const STUDENT_CLAIMABLE_TASK_TYPES = new Set(['manual', 'practice_complete', 'vocabulary_review', 'mock_exam_submit']);

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

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

function optionalUuidString(value: unknown, key: string) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  if (!/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(candidate)) {
    throw new HttpError(400, `${key} must be a UUID`, 'INVALID_UUID');
  }
  return candidate;
}

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

function shanghaiDateKey(date = new Date()) {
  const parts = shanghaiDateParts(date);
  return `${parts.year}-${parts.month}-${parts.day}`;
}

function shanghaiWeekKey(date = new Date()) {
  const current = new Date(`${shanghaiDateKey(date)}T00:00:00.000Z`);
  const day = current.getUTCDay() || 7;
  current.setUTCDate(current.getUTCDate() + 4 - day);
  const yearStart = new Date(Date.UTC(current.getUTCFullYear(), 0, 1));
  const week = Math.ceil((((current.getTime() - yearStart.getTime()) / 86_400_000) + 1) / 7);
  return `${current.getUTCFullYear()}-W${String(week).padStart(2, '0')}`;
}

function safeIdempotencyText(value: unknown) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  if (!/^[a-zA-Z0-9:_-]{8,120}$/.test(candidate)) {
    throw new HttpError(400, 'idempotencyKey contains unsupported characters', 'INVALID_IDEMPOTENCY_KEY');
  }
  return candidate;
}

function periodKey(periodType: string, taskId: string, idempotencyKey: string | null = null) {
  if (periodType === 'daily') return shanghaiDateKey();
  if (periodType === 'weekly') return shanghaiWeekKey();
  if (periodType === 'monthly') {
    const parts = shanghaiDateParts();
    return `${parts.year}-${parts.month}`;
  }
  if (periodType === 'unlimited') return `claim:${idempotencyKey || randomUUID()}`;
  return `once:${taskId}`;
}

function normalizeTaskType(value: unknown) {
  const candidate = nullableString(value) || 'manual';
  if (!TASK_TYPES.includes(candidate)) throw new HttpError(400, `Invalid taskType: ${candidate}`, 'INVALID_TASK_TYPE');
  return candidate;
}

function normalizePeriodType(value: unknown) {
  const candidate = nullableString(value) || 'once';
  if (!PERIOD_TYPES.includes(candidate)) throw new HttpError(400, `Invalid periodType: ${candidate}`, 'INVALID_PERIOD_TYPE');
  return candidate;
}

function assertActiveWindow(row: { status: string; validFrom: string | null; validTo: string | null }, type: 'task' | 'item') {
  if (row.status !== 'active') {
    throw new HttpError(409, `${type} is not active`, type === 'task' ? 'POINT_TASK_INACTIVE' : 'POINT_EXCHANGE_ITEM_INACTIVE');
  }
  const now = Date.now();
  if (row.validFrom && now < new Date(row.validFrom).getTime()) {
    throw new HttpError(409, `${type} is not available yet`, type === 'task' ? 'POINT_TASK_NOT_STARTED' : 'POINT_EXCHANGE_ITEM_NOT_STARTED');
  }
  if (row.validTo && now > new Date(row.validTo).getTime()) {
    throw new HttpError(409, `${type} is expired`, type === 'task' ? 'POINT_TASK_EXPIRED' : 'POINT_EXCHANGE_ITEM_EXPIRED');
  }
}

async function loadTaskForClaim(client: pg.PoolClient, tenantId: string, body: JsonMap) {
  const taskId = optionalUuidString(body.taskId, 'taskId');
  const code = nullableString(body.code);
  if (!taskId && !code) throw new HttpError(400, 'taskId or code is required', 'POINT_TASK_REQUIRED');

  const result = await client.query<{
    id: string;
    code: string;
    title: string;
    description: string | null;
    taskType: string;
    rewardPoints: number;
    claimLimitPerUser: number;
    periodType: string;
    validFrom: string | null;
    validTo: string | null;
    status: string;
    metadata: JsonMap;
  }>(
    `
    select id, code::text, title, description, task_type as "taskType",
           reward_points as "rewardPoints", claim_limit_per_user as "claimLimitPerUser",
           period_type as "periodType", valid_from as "validFrom", valid_to as "validTo",
           status, metadata
    from public.point_activity_tasks
    where tenant_id = $1
      and (($2::uuid is not null and id = $2::uuid) or ($3::text is not null and code = $3))
    limit 1
    for update
    `,
    [tenantId, taskId, code],
  );
  const task = result.rows[0];
  if (!task) throw new HttpError(404, 'Point activity task not found', 'POINT_TASK_NOT_FOUND');
  assertActiveWindow(task, 'task');
  return task;
}

async function loadExchangeItemForRedeem(client: pg.PoolClient, tenantId: string, body: JsonMap) {
  const itemId = optionalUuidString(body.itemId, 'itemId');
  const code = nullableString(body.code);
  if (!itemId && !code) throw new HttpError(400, 'itemId or code is required', 'POINT_EXCHANGE_ITEM_REQUIRED');

  const result = await client.query<{
    id: string;
    code: string;
    title: string;
    description: string | null;
    costPoints: number;
    itemType: string;
    couponId: string | null;
    assetId: string | null;
    stockTotal: number | null;
    stockUsed: number;
    perUserLimit: number;
    validFrom: string | null;
    validTo: string | null;
    status: string;
    metadata: JsonMap;
  }>(
    `
    select id, code::text, title, description, cost_points as "costPoints",
           item_type as "itemType", coupon_id as "couponId", asset_id as "assetId",
           stock_total as "stockTotal", stock_used as "stockUsed",
           per_user_limit as "perUserLimit", valid_from as "validFrom", valid_to as "validTo",
           status, metadata
    from public.point_exchange_items
    where tenant_id = $1
      and (($2::uuid is not null and id = $2::uuid) or ($3::text is not null and code = $3))
    limit 1
    for update
    `,
    [tenantId, itemId, code],
  );
  const item = result.rows[0];
  if (!item) throw new HttpError(404, 'Point exchange item not found', 'POINT_EXCHANGE_ITEM_NOT_FOUND');
  assertActiveWindow(item, 'item');
  return item;
}

async function userScoreForUpdate(client: pg.PoolClient, userId: string) {
  const result = await client.query<{ score: number }>(
    `
    select score
    from public.platform_users
    where id = $1
    limit 1
    for update
    `,
    [userId],
  );
  const row = result.rows[0];
  if (!row) throw new HttpError(404, 'User not found', 'USER_NOT_FOUND');
  return Number(row.score || 0);
}

async function assertTaskEvidence(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    userId: string;
    taskType: string;
    sourceId: string | null;
  },
) {
  if (input.taskType === 'manual') return;
  if (!input.sourceId) {
    throw new HttpError(400, 'sourceId is required for this activity task', 'POINT_TASK_SOURCE_REQUIRED');
  }

  if (input.taskType === 'practice_complete' || input.taskType === 'mock_exam_submit') {
    const result = await client.query(
      `
      select id
      from public.practice_session_reports
      where tenant_id = $1
        and user_id = $2
        and (id = $3::uuid or practice_session_id = $3::uuid)
        and ($4::text <> 'mock_exam_submit' or mode = 'mock_exam')
      limit 1
      `,
      [input.tenantId, input.userId, input.sourceId, input.taskType],
    );
    if (!result.rows[0]) {
      throw new HttpError(404, 'Practice evidence not found for this student', 'POINT_TASK_SOURCE_NOT_FOUND');
    }
    return;
  }

  if (input.taskType === 'vocabulary_review') {
    const result = await client.query(
      `
      select id
      from public.user_word_progress
      where tenant_id = $1
        and user_id = $2
        and id = $3::uuid
        and last_review_date is not null
      limit 1
      `,
      [input.tenantId, input.userId, input.sourceId],
    );
    if (!result.rows[0]) {
      throw new HttpError(404, 'Vocabulary review evidence not found for this student', 'POINT_TASK_SOURCE_NOT_FOUND');
    }
  }
}

async function createCouponRedemptionForExchange(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    userId: string;
    couponId: string;
    orderId: string;
    metadata: JsonMap;
  },
) {
  const couponResult = await client.query<{
    id: string;
    code: string;
    status: string;
    planId: string | null;
    validFrom: string | null;
    validTo: string | null;
    maxUses: number | null;
    usedCount: number;
    source: string | null;
    remark: string | null;
  }>(
    `
    select id, code::text, status, plan_id as "planId",
           valid_from as "validFrom", valid_to as "validTo",
           max_uses as "maxUses", used_count as "usedCount", source, remark
    from public.coupons
    where tenant_id = $1 and id = $2
    limit 1
    for update
    `,
    [input.tenantId, input.couponId],
  );
  const coupon = couponResult.rows[0];
  if (!coupon) throw new HttpError(404, 'Coupon not found for exchange item', 'POINT_EXCHANGE_COUPON_NOT_FOUND');
  assertActiveWindow({
    status: coupon.status,
    validFrom: coupon.validFrom,
    validTo: coupon.validTo,
  }, 'item');
  if (coupon.maxUses !== null && coupon.maxUses > 0 && coupon.usedCount >= coupon.maxUses) {
    throw new HttpError(409, 'Coupon stock is exhausted', 'POINT_EXCHANGE_COUPON_EXHAUSTED');
  }

  const redemption = await client.query<{
    id: string;
    couponId: string;
    couponCode: string;
    status: string;
    claimedAt: string | null;
  }>(
    `
    insert into public.coupon_redemptions (
      tenant_id, coupon_id, coupon_code, user_id, plan_id, order_id, status,
      discount_applied_cents, source, remark, claimed_at
    )
    values ($1, $2, $3, $4, $5::uuid, null, 'claimed', 0, 'points_exchange', $6, now())
    returning id, coupon_id as "couponId", coupon_code as "couponCode", status, claimed_at as "claimedAt"
    `,
    [
      input.tenantId,
      coupon.id,
      coupon.code,
      input.userId,
      coupon.planId,
      `积分兑换订单 ${input.orderId}`,
    ],
  );

  return redemption.rows[0];
}

export async function activityTasksRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 100, 300);
  const taskType = stringParam(ctx, 'taskType');
  const params: unknown[] = [tenantId, userId];
  const filters = [
    't.tenant_id = $1',
    "t.status = 'active'",
    '(t.valid_from is null or t.valid_from <= now())',
    '(t.valid_to is null or t.valid_to >= now())',
  ];
  if (taskType) {
    normalizeTaskType(taskType);
    params.push(taskType);
    filters.push(`t.task_type = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select t.id, t.code::text, t.title, t.description, t.task_type as "taskType",
           t.reward_points as "rewardPoints", t.claim_limit_per_user as "claimLimitPerUser",
           t.period_type as "periodType", t.valid_from as "validFrom", t.valid_to as "validTo",
           t.status, t.sort_order as "order", t.metadata,
           coalesce(c.claim_count, 0)::int as "claimCount",
           coalesce(c.current_period_claim_count, 0)::int as "currentPeriodClaimCount",
           c.last_claimed_at as "lastClaimedAt",
           c.current_period_claim_id as "currentPeriodClaimId",
           (c.current_period_claim_id is not null) as "claimedInCurrentPeriod",
           case
             when t.period_type in ('daily', 'weekly', 'monthly') then
               case when coalesce(c.current_period_claim_count, 0) > 0 then 0 else 1 end
             else greatest(t.claim_limit_per_user - coalesce(c.claim_count, 0), 0)
           end::int as "remainingClaims"
    from public.point_activity_tasks t
    left join lateral (
      select count(*)::int as claim_count,
             count(*) filter (
               where period_key = case
                 when t.period_type = 'daily' then to_char((now() at time zone 'Asia/Shanghai')::date, 'YYYY-MM-DD')
                 when t.period_type = 'weekly' then to_char((now() at time zone 'Asia/Shanghai')::date, 'IYYY-"W"IW')
                 when t.period_type = 'monthly' then to_char((now() at time zone 'Asia/Shanghai')::date, 'YYYY-MM')
                 when t.period_type = 'unlimited' then ''
                 else 'once:' || t.id::text
               end
             )::int as current_period_claim_count,
             max(claimed_at) as last_claimed_at,
             (array_agg(id order by claimed_at desc) filter (
               where period_key = case
                 when t.period_type = 'daily' then to_char((now() at time zone 'Asia/Shanghai')::date, 'YYYY-MM-DD')
                 when t.period_type = 'weekly' then to_char((now() at time zone 'Asia/Shanghai')::date, 'IYYY-"W"IW')
                 when t.period_type = 'monthly' then to_char((now() at time zone 'Asia/Shanghai')::date, 'YYYY-MM')
                 when t.period_type = 'unlimited' then ''
                 else 'once:' || t.id::text
               end
             ))[1] as current_period_claim_id
      from public.user_point_activity_claims c
      where c.tenant_id = t.tenant_id
        and c.task_id = t.id
        and c.user_id = $2
        and c.status = 'claimed'
    ) c on true
    where ${filters.join(' and ')}
    order by t.sort_order asc, t.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function claimActivityTaskRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const sourceType = nullableString(body.sourceType);
  const sourceId = optionalUuidString(body.sourceId, 'sourceId');

  const item = await transaction(async client => {
    const task = await loadTaskForClaim(client, tenantId, body);
    if (!STUDENT_CLAIMABLE_TASK_TYPES.has(task.taskType)) {
      throw new HttpError(403, 'This activity task is granted by system workflow only', 'POINT_TASK_SYSTEM_GRANTED_ONLY');
    }
    await assertTaskEvidence(client, {
      tenantId,
      userId,
      taskType: task.taskType,
      sourceId,
    });
    const requestIdempotencyKey = safeIdempotencyText(body.idempotencyKey);
    const currentPeriodKey = periodKey(task.periodType, task.id, requestIdempotencyKey);

    const periodClaims = await client.query<{ count: string }>(
      `
      select count(*)::text as count
      from public.user_point_activity_claims
      where tenant_id = $1 and user_id = $2 and task_id = $3 and status = 'claimed'
        and (
          $5::text <> 'period'
          or period_key = $4
        )
      `,
      [tenantId, userId, task.id, currentPeriodKey, ['daily', 'weekly', 'monthly'].includes(task.periodType) ? 'period' : 'total'],
    );
    const currentLimitCount = Number(periodClaims.rows[0]?.count || 0);
    const effectiveLimit = ['daily', 'weekly', 'monthly'].includes(task.periodType) ? 1 : task.claimLimitPerUser;
    if (currentLimitCount >= effectiveLimit) {
      throw new HttpError(409, 'Activity task claim limit reached', 'POINT_TASK_CLAIM_LIMIT_REACHED');
    }

    const totalClaims = await client.query<{ count: string }>(
      `
      select count(*)::text as count
      from public.user_point_activity_claims
      where tenant_id = $1 and user_id = $2 and task_id = $3 and status = 'claimed'
      `,
      [tenantId, userId, task.id],
    );
    const claimCount = Number(totalClaims.rows[0]?.count || 0);

    const scoreBefore = await userScoreForUpdate(client, userId);
    const scoreAfter = scoreBefore + task.rewardPoints;
    const idempotencyKey = `activity:${task.id}:user:${userId}:period:${currentPeriodKey}`;

    const ledger = await client.query<{
      id: string;
      eventType: string;
      points: number;
      balanceAfter: number;
      createdAt: string;
    }>(
      `
      insert into public.user_score_events (
        tenant_id, user_id, event_type, points, balance_after,
        source_type, source_id, idempotency_key, metadata
      )
      values ($1, $2, 'activity_reward', $3, $4, 'point_activity_tasks', $5, $6, $7::jsonb)
      on conflict (tenant_id, idempotency_key) do nothing
      returning id, event_type as "eventType", points, balance_after as "balanceAfter", created_at as "createdAt"
      `,
      [
        tenantId,
        userId,
        task.rewardPoints,
        scoreAfter,
        task.id,
        idempotencyKey,
        JSON.stringify({
          taskId: task.id,
          code: task.code,
          taskType: task.taskType,
          periodKey: currentPeriodKey,
          sourceType,
          sourceId,
        }),
      ],
    );

    if (!ledger.rows[0]) {
      throw new HttpError(409, 'Activity task already claimed in this period', 'POINT_TASK_ALREADY_CLAIMED');
    }

    const claim = await client.query(
      `
      insert into public.user_point_activity_claims (
        tenant_id, user_id, task_id, period_key, score_event_id,
        source_type, source_id, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7::uuid, $8::jsonb)
      returning id, task_id as "taskId", period_key as "periodKey",
                score_event_id as "scoreEventId", source_type as "sourceType",
                source_id as "sourceId", status, metadata, claimed_at as "claimedAt"
      `,
      [
        tenantId,
        userId,
        task.id,
        currentPeriodKey,
        ledger.rows[0].id,
        sourceType,
        sourceId,
        JSON.stringify(objectValue(body.metadata)),
      ],
    );

    const updatedUser = await client.query<{ score: number }>(
      `
      update public.platform_users
      set score = score + $2,
          updated_at = now()
      where id = $1
      returning score
      `,
      [userId, task.rewardPoints],
    );

    const nextScore = Number(updatedUser.rows[0]?.score || scoreAfter);
    const nextClaimCount = claimCount + 1;
    const autoBadges = [
      ...(await autoGrantBadges(client, {
        tenantId,
        userId,
        trigger: 'activity_reward',
        evidence: {
          activityRewardCount: nextClaimCount,
          rewardPoints: task.rewardPoints,
          score: nextScore,
          taskType: task.taskType,
          taskCode: task.code,
        },
      })),
      ...(await autoGrantBadges(client, {
        tenantId,
        userId,
        trigger: 'score',
        evidence: {
          source: 'activity_reward',
          activityRewardCount: nextClaimCount,
          rewardPoints: task.rewardPoints,
          score: nextScore,
        },
      })),
    ];

    return {
      task,
      claim: claim.rows[0],
      ledger: {
        ...ledger.rows[0],
        balanceAfter: nextScore,
      },
      pointsAdded: task.rewardPoints,
      score: nextScore,
      claimCount: nextClaimCount,
      autoBadges,
    };
  });

  return { item };
}

export async function exchangeItemsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 100, 300);
  const params: unknown[] = [tenantId, userId, limit];

  const items = await query(
    `
    select i.id, i.code::text, i.title, i.description, i.cost_points as "costPoints",
           i.item_type as "itemType", i.coupon_id as "couponId", i.asset_id as "assetId",
           i.stock_total as "stockTotal", i.stock_used as "stockUsed",
           case when i.stock_total is null then null else greatest(i.stock_total - i.stock_used, 0) end as "stockRemaining",
           i.per_user_limit as "perUserLimit", i.valid_from as "validFrom", i.valid_to as "validTo",
           i.status, i.sort_order as "order", i.metadata,
           coalesce(o.order_count, 0)::int as "redeemedCount",
           o.last_exchanged_at as "lastExchangedAt",
           greatest(i.per_user_limit - coalesce(o.order_count, 0), 0)::int as "remainingUserRedemptions"
    from public.point_exchange_items i
    left join lateral (
      select count(*)::int as order_count, max(exchanged_at) as last_exchanged_at
      from public.user_point_exchange_orders o
      where o.tenant_id = i.tenant_id
        and o.item_id = i.id
        and o.user_id = $2
        and o.status <> 'cancelled'
    ) o on true
    where i.tenant_id = $1
      and i.status = 'active'
      and (i.valid_from is null or i.valid_from <= now())
      and (i.valid_to is null or i.valid_to >= now())
    order by i.sort_order asc, i.created_at desc
    limit $3
    `,
    params,
  );

  return { items };
}

export async function redeemExchangeItemRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const idempotencyKey = safeIdempotencyText(body.idempotencyKey) || `exchange:${randomUUID()}`;

  const item = await transaction(async client => {
    const existing = await client.query(
      `
      select o.id, o.item_id as "itemId", o.status, o.cost_points as "costPoints",
             o.score_event_id as "scoreEventId", o.coupon_redemption_id as "couponRedemptionId",
             o.asset_id as "assetId", o.metadata, o.exchanged_at as "exchangedAt"
      from public.user_point_exchange_orders o
      where o.tenant_id = $1 and o.user_id = $2 and o.idempotency_key = $3
      limit 1
      `,
      [tenantId, userId, idempotencyKey],
    );
    if (existing.rows[0]) return { order: existing.rows[0], idempotent: true };

    const exchangeItem = await loadExchangeItemForRedeem(client, tenantId, body);
    const userOrders = await client.query<{ count: string }>(
      `
      select count(*)::text as count
      from public.user_point_exchange_orders
      where tenant_id = $1 and user_id = $2 and item_id = $3 and status <> 'cancelled'
      `,
      [tenantId, userId, exchangeItem.id],
    );
    const redeemedCount = Number(userOrders.rows[0]?.count || 0);
    if (redeemedCount >= exchangeItem.perUserLimit) {
      throw new HttpError(409, 'Point exchange user limit reached', 'POINT_EXCHANGE_USER_LIMIT_REACHED');
    }
    if (exchangeItem.stockTotal !== null && exchangeItem.stockUsed >= exchangeItem.stockTotal) {
      throw new HttpError(409, 'Point exchange item is out of stock', 'POINT_EXCHANGE_OUT_OF_STOCK');
    }

    const scoreBefore = await userScoreForUpdate(client, userId);
    if (scoreBefore < exchangeItem.costPoints) {
      throw new HttpError(409, 'Insufficient score balance', 'POINT_EXCHANGE_INSUFFICIENT_SCORE');
    }
    const scoreAfter = scoreBefore - exchangeItem.costPoints;

    const ledger = await client.query<{
      id: string;
      eventType: string;
      points: number;
      balanceAfter: number;
      createdAt: string;
    }>(
      `
      insert into public.user_score_events (
        tenant_id, user_id, event_type, points, balance_after,
        source_type, source_id, idempotency_key, metadata
      )
      values ($1, $2, 'redeem_cost', $3, $4, 'point_exchange_items', $5, $6, $7::jsonb)
      returning id, event_type as "eventType", points, balance_after as "balanceAfter", created_at as "createdAt"
      `,
      [
        tenantId,
        userId,
        -exchangeItem.costPoints,
        scoreAfter,
        exchangeItem.id,
        `redeem:${idempotencyKey}`,
        JSON.stringify({
          itemId: exchangeItem.id,
          code: exchangeItem.code,
          itemType: exchangeItem.itemType,
        }),
      ],
    );

    const order = await client.query<{
      id: string;
      itemId: string;
      status: string;
      costPoints: number;
      scoreEventId: string;
      couponRedemptionId: string | null;
      assetId: string | null;
      metadata: JsonMap;
      exchangedAt: string;
    }>(
      `
      insert into public.user_point_exchange_orders (
        tenant_id, user_id, item_id, status, cost_points, score_event_id,
        asset_id, idempotency_key, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7::uuid, $8, $9::jsonb)
      returning id, item_id as "itemId", status, cost_points as "costPoints",
                score_event_id as "scoreEventId", coupon_redemption_id as "couponRedemptionId",
                asset_id as "assetId", metadata, exchanged_at as "exchangedAt"
      `,
      [
        tenantId,
        userId,
        exchangeItem.id,
        exchangeItem.itemType === 'manual' || exchangeItem.itemType === 'custom' ? 'pending_fulfillment' : 'completed',
        exchangeItem.costPoints,
        ledger.rows[0].id,
        exchangeItem.assetId,
        idempotencyKey,
        JSON.stringify(objectValue(body.metadata)),
      ],
    );

    let couponRedemption = null;
    if (exchangeItem.itemType === 'coupon') {
      if (!exchangeItem.couponId) throw new HttpError(409, 'Coupon exchange item is missing couponId', 'POINT_EXCHANGE_COUPON_MISSING');
      couponRedemption = await createCouponRedemptionForExchange(client, {
        tenantId,
        userId,
        couponId: exchangeItem.couponId,
        orderId: order.rows[0].id,
        metadata: objectValue(body.metadata),
      });
      await client.query(
        `
        update public.user_point_exchange_orders
        set coupon_redemption_id = $3,
            status = 'completed',
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [tenantId, order.rows[0].id, couponRedemption.id],
      );
      order.rows[0].couponRedemptionId = couponRedemption.id;
      order.rows[0].status = 'completed';
    }

    if (exchangeItem.stockTotal !== null) {
      const stockUpdate = await client.query(
        `
        update public.point_exchange_items
        set stock_used = stock_used + 1,
            updated_at = now()
        where tenant_id = $1
          and id = $2
          and stock_total is not null
          and stock_used < stock_total
        `,
        [tenantId, exchangeItem.id],
      );
      if (stockUpdate.rowCount !== 1) {
        throw new HttpError(409, 'Point exchange item is out of stock', 'POINT_EXCHANGE_OUT_OF_STOCK');
      }
    }

    const updatedUser = await client.query<{ score: number }>(
      `
      update public.platform_users
      set score = score - $2,
          updated_at = now()
      where id = $1
      returning score
      `,
      [userId, exchangeItem.costPoints],
    );

    const finalStatus = order.rows[0].status;
    await createUserNotification(client, {
      tenantId,
      userId,
      notificationType: finalStatus === 'completed' ? 'point_exchange_completed' : 'point_exchange_pending_fulfillment',
      severity: finalStatus === 'completed' ? 'success' : 'info',
      title: finalStatus === 'completed' ? '积分兑换成功' : '积分兑换待发放',
      message: finalStatus === 'completed'
        ? `你已成功兑换「${exchangeItem.title}」。`
        : `你已提交「${exchangeItem.title}」兑换申请，运营人员会继续处理。`,
      actionLabel: '查看兑换',
      actionPath: '/student/profile?tab=points',
      sourceType: 'user_point_exchange_orders',
      sourceId: order.rows[0].id,
      dedupeKey: `point_exchange:${order.rows[0].id}`,
      metadata: {
        itemId: exchangeItem.id,
        itemCode: exchangeItem.code,
        itemTitle: exchangeItem.title,
        itemType: exchangeItem.itemType,
        costPoints: exchangeItem.costPoints,
        status: finalStatus,
        couponRedemptionId: order.rows[0].couponRedemptionId,
        assetId: order.rows[0].assetId,
      },
    });

    return {
      item: exchangeItem,
      order: order.rows[0],
      couponRedemption,
      ledger: {
        ...ledger.rows[0],
        balanceAfter: Number(updatedUser.rows[0]?.score || scoreAfter),
      },
      pointsSpent: exchangeItem.costPoints,
      score: Number(updatedUser.rows[0]?.score || scoreAfter),
      idempotent: false,
    };
  });

  return { item };
}
