幂等设计：从接口到消息消费的防重体系

// 客户端生成模式：入口中间件校验键格式与作用域
import { randomUUID } from 'node:crypto';

app.post('/api/orders', requireAuth, (req, res, next) => {
  const key = req.header('Idempotency-Key');
  if (!key || !/^[0-9a-f-]{36}$/.test(key)) {
    return res.status(400).json({ code: 'INVALID_IDEMPOTENCY_KEY' });
  }
  // 键的作用域绑定用户：不同用户撞同一个 uuid 时不会误判为重复
  res.locals.idempotencyKey = `order:${req.user.id}:${key}`;
  next();
});

// 前端：进入收银台时生成一次，成功/明确失败前不变
let checkoutKey: string | null = null;
async function submitOrder() {
  checkoutKey ??= randomUUID();
  return fetch('/api/orders', {
    method: 'POST',
    headers: { 'Idempotency-Key': checkoutKey, 'Content-Type': 'application/json' },
    body: JSON.stringify(payload),
  });
}

CREATE TABLE orders (
  id            BIGSERIAL PRIMARY KEY,
  order_no      TEXT NOT NULL,
  user_id       BIGINT NOT NULL,
  amount_cents INTEGER NOT NULL,
  status        TEXT NOT NULL DEFAULT 'PENDING',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 业务单号全局唯一：重复提交靠它硬挡
CREATE UNIQUE INDEX orders_order_no_uq ON orders (order_no);

async function insertOrder(orderNo: string, userId: bigint, amount: number) {
  try {
    return await db`INSERT INTO orders (order_no, user_id, amount_cents)
                    VALUES (${orderNo}, ${userId}, ${amount}) RETURNING *`;
  } catch (error: any) {
    if (error.code === '23505') {          // PostgreSQL unique_violation
      const [existing] = await db`SELECT * FROM orders WHERE order_no = ${orderNo}`;
      return existing;                     // 已存在：返回首次结果，而不是报错
    }
    throw error;
  }
}

PENDING ──支付成功──> PAID ──发货──> SHIPPED ──确认──> DONE
   │                    │
   └──取消──> CANCELLED ─┘（PAID 后取消走退款子流程）

async function applyPaymentCallback(orderNo: string, paidAmount: number) {
  // 只允许 PENDING -> PAID 一次；已 PAID 的重复回调会在这里 affected=0
  const result = await db`
    UPDATE orders SET status = 'PAID', paid_at = now()
    WHERE order_no = ${orderNo} AND status = 'PENDING' AND amount_cents = ${paidAmount}
    RETURNING *`;

  if (result.length === 0) {
    const [current] = await db`SELECT status, amount_cents FROM orders WHERE order_no = ${orderNo}`;
    if (!current) throw new Error('ORDER_NOT_FOUND');
    if (current.status === 'PAID') return { duplicated: true };   // 重复回调：幂等成功返回
    throw new Error(`ILLEGAL_TRANSITION:${current.status}`);       // PENDING 但金额不符等异常：必须告警
  }
  return { duplicated: false, order: result[0] };
}

UPDATE user_profile
SET nickname = $2, version = version + 1
WHERE user_id = $1 AND version = $3;
-- affected=0 说明读到的是旧版本：客户端用 GET 拿最新数据重试（带退避）

async function updateNickname(userId: bigint, nickname: string, baseVersion: number) {
  const rows = await db`
    UPDATE user_profile SET nickname = ${nickname}, version = version + 1
    WHERE user_id = ${userId} AND version = ${baseVersion} RETURNING version`;
  if (rows.length === 0) {
    const [latest] = await db`SELECT version, nickname FROM user_profile WHERE user_id = ${userId}`;
    if (latest.version === baseVersion) throw new Error('CONCURRENT_UPDATE_LOST');
    return { retryWithVersion: latest.version };   // 版本变了：让调用方基于最新版本重试
  }
  return { version: rows[0].version };
}

CREATE TABLE idempotency_records (
  key        TEXT PRIMARY KEY,          -- order:{userId}:{uuid}
  biz_type   TEXT NOT NULL,
  request_hash TEXT NOT NULL,           -- 同键不同请求体的检测
  status     TEXT NOT NULL,             -- PROCESSING | DONE
  result     JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

app.post('/api/orders', requireAuth, async (req, res) => {
  const key = res.locals.idempotencyKey;
  const requestHash = sha256(JSON.stringify(req.body));

  try {
    // 1. 抢占幂等记录（唯一约束，PROCESSING 占位）
    await db`INSERT INTO idempotency_records (key, biz_type, request_hash, status)
             VALUES (${key}, 'create_order', ${requestHash}, 'PROCESSING')`;
  } catch (error: any) {
    if (error.code !== '23505') throw error;
    const [record] = await db`SELECT * FROM idempotency_records WHERE key = ${key}`;
    if (record.request_hash !== requestHash) {
      return res.status(422).json({ code: 'KEY_REUSED_WITH_DIFFERENT_BODY' }); // 键复用滥用
    }
    if (record.status === 'PROCESSING') {
      return res.status(409).json({ code: 'REQUEST_IN_PROGRESS' });  // 上一次还没完，稍后再查
    }
    return res.status(200).json(record.result);   // 幂等命中：返回首次结果
  }

  try {
    const order = await createOrderInTx(req.user.id, req.body);      // 业务事务 + orders.order_no 唯一约束
    await db`UPDATE idempotency_records SET status='DONE', result=${JSON.stringify(order)} WHERE key=${key}`;
    res.status(201).json(order);
  } catch (error: any) {
    // 执行失败：删掉占位，允许真正的重试（成功结果才值得缓存）
    await db`DELETE FROM idempotency_records WHERE key = ${key} AND status='PROCESSING'`;
    throw error;
  }
});

KEY="0b2c1c6e-1f7b-4f0f-9d3a-8f2f6f0a1111"

# 第一次提交：期望 201，创建订单
curl -s -X POST http://127.0.0.1:3000/api/orders \
  -H "Idempotency-Key: $KEY" -H 'Content-Type: application/json' \
  -d '{"skuId": 9001, "qty": 2}'

# 第二次重放：期望 200 + 同一个订单（不是新建一单）
curl -s -X POST http://127.0.0.1:3000/api/orders \
  -H "Idempotency-Key: $KEY" -H 'Content-Type: application/json' \
  -d '{"skuId": 9001, "qty": 2}'

# 同键不同体：期望 422 KEY_REUSED_WITH_DIFFERENT_BODY
curl -s -X POST http://127.0.0.1:3000/api/orders \
  -H "Idempotency-Key: $KEY" -H 'Content-Type: application/json' \
  -d '{"skuId": 9002, "qty": 2}'

async function submitOrderWithRecovery() {
  try {
    return await submitOrder();
  } catch (error) {
    if (error.code !== 'ETIMEDOUT') throw error;
    // 超时 = 未知：先查，再决定重试还是收下结果
    const status = await fetch(`/api/orders/${checkoutKey}`, { method: 'GET' });
    if (status.ok) return status.json();       // 上次其实成功了
    if (status.status === 404) return submitOrder();   // 确认不存在：安全重试（同键）
    throw error;
  }
}