CREATE TABLE lianer_activity (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32)  NOT NULL COMMENT '如 D11_2026',
  name          VARCHAR(64)  NOT NULL,
  start_time    DATETIME(3)  NOT NULL COMMENT '2026-11-01 10:00:00.000',
  end_time      DATETIME(3)  NOT NULL COMMENT '2026-11-11 23:59:59.999',
  hold_minutes  INT          NOT NULL DEFAULT 15 COMMENT '限量商品付款时效',
  score_per_amount INT       NOT NULL DEFAULT 100 COMMENT '每满多少元',
  score_per_points INT       NOT NULL DEFAULT 1   COMMENT '得多少二币',
  status        TINYINT      NOT NULL DEFAULT 1,
  created_time  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_time  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_code (activity_code)
) COMMENT '营销活动主表';

CREATE TABLE lianer_activity_item (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32) NOT NULL,
  item_key      VARCHAR(32) NOT NULL COMMENT 'pt_year/pt_60_half/card_month/... 与前端 key 一致',
  tier          CHAR(1)     NULL COMMENT 'A-E；NULL=不分档',
  store_id      INT         NULL COMMENT '按店覆盖；NULL=该档通用',
  product_type  TINYINT     NOT NULL COMMENT '1会员卡 2私教课程 3套餐',
  product_id    INT         NOT NULL COMMENT 'cardId/courseId/comboId',
  product_group VARCHAR(32) NULL COMMENT '商品组，如 PT60_HALF，用于券 scope 跨店同款',
  quota_pool_id INT         NULL COMMENT '限量池；NULL=不限量',
  per_user_limit INT        NOT NULL DEFAULT 0 COMMENT '0=不限',
  weekday_rule  VARCHAR(16) NULL COMMENT '如 TUE；NULL=不限',
  sort          INT         NOT NULL DEFAULT 0,
  status        TINYINT     NOT NULL DEFAULT 1,
  UNIQUE KEY uk_item (activity_code, item_key, tier, store_id),
  KEY idx_product (product_type, product_id)
) COMMENT '活动商品与真实商品映射；价格不存这里，取价以促销/套餐价为唯一来源';

CREATE TABLE lianer_activity_store_tier (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32) NOT NULL,
  store_id      INT         NOT NULL,
  tier          CHAR(1)     NOT NULL COMMENT 'A-E',
  venue_type    TINYINT     NOT NULL DEFAULT 0 COMMENT '0未区分 1综合馆 2私教馆（杭州年卡价不同）',
  tier_name     VARCHAR(64) NOT NULL COMMENT '展示用，如 杭州 / 郑州·新郑',
  UNIQUE KEY uk_store (activity_code, store_id)
) COMMENT '活动门店档位：区县档与馆型必须按店配置';

CREATE TABLE lianer_activity_quota_pool (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32) NOT NULL,
  pool_key      VARCHAR(32) NOT NULL COMMENT 'pt_year',
  total         INT NOT NULL COMMENT '100',
  held          INT NOT NULL DEFAULT 0 COMMENT '待支付占用',
  sold          INT NOT NULL DEFAULT 0 COMMENT '已售',
  updated_time  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_pool (activity_code, pool_key),
  CONSTRAINT chk_pool CHECK (held >= 0 AND sold >= 0 AND held + sold <= total)
) COMMENT '全国限量池（单行，条件更新扣减）';

CREATE TABLE lianer_activity_order_hold (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32) NOT NULL,
  item_key      VARCHAR(32) NOT NULL,
  pool_id       INT         NULL COMMENT 'NULL=不占限量池（如星期二月卡只做每人一单）',
  user_id       INT         NOT NULL,
  store_id      INT         NOT NULL,
  order_type    VARCHAR(16) NOT NULL COMMENT 'private/member/combo',
  order_number  VARCHAR(64) NOT NULL,
  status        VARCHAR(16) NOT NULL COMMENT 'HELD/RELEASING/RELEASED/SOLD/COMPENSATED/REFUND_PENDING/REFUNDED',
  expires_at    DATETIME(3) NOT NULL COMMENT '与订单 expires_at 一致的快照',
  active_flag   TINYINT GENERATED ALWAYS AS (IF(status IN ('HELD','RELEASING'), 1, NULL)) STORED,
  limit_flag    TINYINT     NULL COMMENT '每人限购=1 的商品在非释放态置 1，释放/退款时应用置 NULL',
  release_attempts INT      NOT NULL DEFAULT 0,
  release_reason VARCHAR(64) NULL,
  released_time DATETIME(3) NULL,
  created_time  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_time  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_order (order_type, order_number),
  UNIQUE KEY uk_active_hold (activity_code, item_key, user_id, active_flag),
  UNIQUE KEY uk_user_limit (activity_code, item_key, user_id, limit_flag),
  KEY idx_release (status, expires_at)
) COMMENT '活动订单占位：一单一行，状态机保证名额只释放一次';

ALTER TABLE lianer_private_order
  ADD COLUMN activity_code VARCHAR(32) NULL COMMENT '活动订单标识',
  ADD COLUMN expires_at    BIGINT      NULL COMMENT '付款截止，epoch 毫秒，创建时快照',
  ADD COLUMN activity_snapshot JSON    NULL COMMENT '活动价/原价/档位/规则快照',
  ADD COLUMN score_rate    VARCHAR(16) NULL COMMENT '如 100:1，下单时快照',
  ADD COLUMN score_deduct_points INT   NOT NULL DEFAULT 0,
  ADD COLUMN score_deduct_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD KEY idx_activity_expire (activity_code, order_status, expires_at);

ALTER TABLE lianer_combo_order
  ADD COLUMN activity_code VARCHAR(32) NULL,
  ADD COLUMN expires_at    DATETIME(3) NULL,
  ADD COLUMN activity_snapshot JSON    NULL,
  ADD COLUMN score_rate    VARCHAR(16) NULL,
  ADD COLUMN score_deduct_points INT   NOT NULL DEFAULT 0,
  ADD COLUMN score_deduct_amount DECIMAL(10,2) NOT NULL DEFAULT 0;

ALTER TABLE lianer_member_order
  ADD COLUMN activity_code VARCHAR(32) NULL,
  ADD COLUMN score_rate    VARCHAR(16) NULL,
  ADD COLUMN score_deduct_points INT   NOT NULL DEFAULT 0,
  ADD COLUMN score_deduct_amount DECIMAL(10,2) NOT NULL DEFAULT 0;

ALTER TABLE lianer_counpon
  ADD COLUMN scope_type TINYINT NOT NULL DEFAULT 0 COMMENT '0不限商品(仍受apply约束) 1套餐 2课程 3会员卡 4商品组',
  ADD COLUMN scope_name VARCHAR(64) NULL COMMENT '展示用，如 私教60次半年卡';

CREATE TABLE lianer_counpon_scope_item (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  coupon_id  INT NOT NULL,
  target_type TINYINT NOT NULL COMMENT '1套餐 2课程 3会员卡 4商品组',
  target_ref VARCHAR(64) NOT NULL COMMENT 'comboId/courseId/cardId 或商品组编码 PT60_HALF',
  UNIQUE KEY uk_scope (coupon_id, target_type, target_ref)
) COMMENT '券模板适用商品';

ALTER TABLE lianer_user_coupon
  ADD COLUMN scope_type  TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN scope_refs  VARCHAR(512) NULL COMMENT '发券时快照，逗号分隔 target_type:target_ref',
  ADD COLUMN scope_name  VARCHAR(64) NULL,
  ADD COLUMN source_activity_code VARCHAR(32) NULL,
  ADD COLUMN source_order_number  VARCHAR(64) NULL;

ALTER TABLE lianer_combo_product
  ADD COLUMN product_group VARCHAR(32) NULL COMMENT '跨门店同款商品组，如 PT60_HALF';

CREATE TABLE lianer_activity_grant (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32) NOT NULL,
  grant_type    VARCHAR(32) NOT NULL COMMENT 'PT60_REBUY_700',
  user_id       INT         NOT NULL,
  source_order_type   VARCHAR(16) NOT NULL,
  source_order_number VARCHAR(64) NOT NULL,
  user_coupon_id INT        NULL,
  status        VARCHAR(16) NOT NULL COMMENT 'PENDING/GRANTED/FAILED/REVOKED',
  attempts      INT         NOT NULL DEFAULT 0,
  last_error    VARCHAR(255) NULL,
  created_time  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_time  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_user_qual (activity_code, grant_type, user_id),
  UNIQUE KEY uk_source (source_order_type, source_order_number, grant_type)
) COMMENT '活动发券资格：活动+用户唯一';

CREATE TABLE lianer_activity_reserve (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  activity_code VARCHAR(32) NOT NULL,
  item_key      VARCHAR(32) NOT NULL,
  user_id       INT         NOT NULL,
  template_id   VARCHAR(64) NULL,
  subscribe_accepted TINYINT NOT NULL DEFAULT 0 COMMENT '用户是否在授权弹窗点了允许',
  notify_status VARCHAR(16) NOT NULL DEFAULT 'WAIT' COMMENT 'WAIT/SENT/FAILED/SKIPPED',
  notify_time   DATETIME(3) NULL,
  last_error    VARCHAR(255) NULL,
  created_time  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_reserve (activity_code, item_key, user_id),
  KEY idx_notify (activity_code, notify_status)
);

ALTER TABLE lianer_user_score
  ADD COLUMN order_type   VARCHAR(16) NULL,
  ADD COLUMN order_number VARCHAR(64) NULL,
  ADD COLUMN biz_type     VARCHAR(16) NULL COMMENT 'EARN/DEDUCT/RETURN/CLAWBACK',
  ADD UNIQUE KEY uk_order_biz (order_type, order_number, biz_type);

CREATE TABLE lianer_score_deduct (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id      INT NOT NULL,
  order_type   VARCHAR(16) NOT NULL,
  order_number VARCHAR(64) NOT NULL,
  points       INT NOT NULL,
  amount       DECIMAL(10,2) NOT NULL,
  status       VARCHAR(16) NOT NULL COMMENT 'LOCKED/USED/RETURNED',
  created_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uk_order (order_type, order_number)
) COMMENT '二币下单抵扣（M6，后续版本；本期不建表）';

{
  "nowTs": 1793500000000,
  "activityStartTs": 1793498400000,
  "activityEndTs": 1794412799999,
  "phase": "pre|on|end",
  "tier": "B", "tierName": "杭州", "tierMatched": true, "priceScope": "STORE|CITY_FROM",
  "hero": { "slogan": "练二一下，好状态", "headline": "健身年卡 999", "subline": "限量私教年卡最高立减 6,100" },
  "featured": {
    "key": "pt_year", "name": "私教年卡", "badge": "双11首发 · 全国限量 100 张",
    "desc": "360 天时长卡", "originPrice": 24000, "price": 18900, "save": 5100, "monthly": 1575,
    "lockMinutes": 15,
    "quota": { "...同 3.2" },
    "productRef": { "productType": 2, "productId": 0, "storeId": 0 }, "buyable": true, "unavailableReason": null
  },
  "memberCards": [ { "key": "card_year", "name": "健身年卡", "desc": "...", "originPrice": 1599, "price": 999,
                     "subPrice": { "label": "私教馆", "price": 899 }, "tag": null, "note": null, "limitText": null,
                     "productRef": { "productType": 1, "productId": 0, "storeId": 0 }, "buyable": true, "unavailableReason": null } ],
  "ptCards": [ { "key": "pt_60_half", "cat": "times", "months": 6, "tag": "首单送700券", "...": "...", "productRef": { "productType": 3, "productId": 0, "storeId": 0 }, "buyable": true, "unavailableReason": null } ],
  "singleClass": [ { "name": "常规单次课", "origin": "10 节起 · 单节 ¥240", "steps": [ { "label": "20 节", "price": 200 } ] } ],
  "points": { "name": "二币", "perAmount": 100, "perPoints": 1, "value": 1 },
  "rules": [ "..." ]
}

{ "key": "pt_year", "total": 100, "sold": 63, "locked": 5, "remain": 32, "soldOut": false, "availableNow": true,
  "reserved": true, "myLock": { "orderNo": "...", "expiresAt": 1793500900000, "status": "HELD" }, "nowTs": 1793500000000 }

{
  "orderNo": "私教订单号（orderNumberBusiness 生成）",
  "expiresAt": 1793500900000,
  "nowTs": 1793500000000,
  "orderPrice": 18900.00, "totalPrice": 18900.00,
  "courseName": "私教年卡（双11限定）", "storeName": "...",
  "lockStatus": "HELD",
  "quota": { "...同 3.2" },
  "pay": { "api": "/payment/front/trCreateOrder", "orderNumber": "同 orderNo" }
}

{ "status": "RELEASED|RELEASING|PAID", "quota": { "...": "..." }, "nowTs": 1793500000000 }

{ "storeId": 5, "cityId": 12597, "items": [ { "type": 3, "id": 0 }, { "type": 1, "id": 3 } ] }

{
  "phase": "pre|on|end",
  "nowTs": 1793500000000,
  "items": [
    { "type": 3, "id": 0, "inPromotion": true, "label": "双11价 · 首单送700券", "giftCoupon700Eligible": true },
    { "type": 1, "id": 3, "inPromotion": false, "label": null, "giftCoupon700Eligible": false }
  ]
}

@Transactional(rollbackFor = Exception.class)
public LockResult lock(...) {
    Activity act = config.getActivity(D11);
    long now = System.currentTimeMillis();
    if (now < act.startMs()) throw biz("D11_NOT_START");
    if (now > act.endMs())   throw biz("D11_END");
    Item item = config.requireItem(D11, "pt_year", storeId);          // 门店未配置 -> D11_STORE_NOT_ELIGIBLE
    Hold mine = holdMapper.selectActive(D11, "pt_year", userId);        // HELD/RELEASING
    if (mine != null) {
        if ("RELEASING".equals(mine.status)) throw biz("D11_RELEASING");
        return LockResult.of(mine);                                     // 幂等
    }
    PrivateOrderPojo po = orderCommon.buildPrivateOrder(req, ctx);      // 价格来自促销配置
    po.setActivityCode(D11);
    po.setExpiresAt(now + act.holdMinutes() * 60_000L);
    po.setActivitySnapshot(json(item, price, tier));
    paymentPrivateBusiness.insertPrivateOrder(po);
    try {
        holdMapper.insert(Hold.held(po, item, limitFlag(item)));        // uk_active_hold / uk_user_limit
    } catch (DuplicateKeyException e) {
        throw new RetryAsIdempotent();                                  // 外层捕获后查原单返回
    }
    int n = poolMapper.incHeld(item.poolId());                          // 放最后，缩短热点行锁持有时间
    if (n == 0) throw biz("SOLD_OUT");                                  // 抛出即整体回滚
    return LockResult.of(po);
}

-- incHeld
UPDATE lianer_activity_quota_pool SET held = held + 1
 WHERE id = #{id} AND held + sold < total;

if (po.getActivityCode() != null) {
    Hold h = holdMapper.selectByOrder("private", po.getOrderNumber());
    long left = po.getExpiresAt() - System.currentTimeMillis();
    if (h == null || !"HELD".equals(h.status) || left < SAFE_MARGIN_MS) // SAFE_MARGIN 建议 60s
        return fail("D11_LOCK_EXPIRED", quota());
    ctx.setPayExpireAt(po.getExpiresAt());                              // 传给渠道
}

// payPrivateSuccess 返回 >0 之后、insertPrivateRight 之前
if (order.getActivityCode() != null) {
    HoldOutcome o = d11Hold.onPaid("private", order.getOrderNumber());  // 独立事务 REQUIRES_NEW
    if (o == HoldOutcome.REFUND_REQUIRED) {
        markOrderRefundPending(order);                                  // 2 -> 8
        refundService.createAutoFullRefund(order, "D11_LATE_PAY_NO_QUOTA");
        return;                                                         // 不发权益、不记二币
    }
}

@Transactional(propagation = REQUIRES_NEW)
public HoldOutcome onPaid(String type, String no) {
    Hold h = holdMapper.lockByOrder(type, no);                          // SELECT ... FOR UPDATE
    switch (h.status) {
      case "HELD": case "RELEASING":
        holdMapper.cas(h.id, h.status, "SOLD");
        poolMapper.heldToSold(h.poolId);       // held=held-1, sold=sold+1 where held>0
        return OK;
      case "SOLD": case "COMPENSATED":
        return OK;                                                      // 重放
      case "RELEASED":
        if (poolMapper.incSold(h.poolId) == 1) {                        // where held+sold<total
            holdMapper.cas(h.id, "RELEASED", "COMPENSATED");
            return OK;
        }
        holdMapper.cas(h.id, "RELEASED", "REFUND_PENDING");
        return REFUND_REQUIRED;
      default: return OK;
    }
}

public ReleaseResult release(Hold h, String reason) {
    if (holdMapper.cas(h.id, "HELD", "RELEASING") == 0 && !"RELEASING".equals(h.status)) return SKIP;
    List<LianerPayBill> bills = payBillMapper.selectByRefNums(singletonList(h.orderNumber)); // 全部流水
    boolean unknown = false;
    for (LianerPayBill b : bills) {
        TrTransactionStatusPojo r = tairong.queryTransactionStatus(b.getOrderNum(), cfg(h.storeId));
        if (r == null || !r.isResult()) { unknown = true; continue; }
        if ("S".equals(r.getTranStatus())) {
            bankCallBack.bankPayCallBack(TairongPayType.Private, b.getOrderNum(), r.getOriTranSeqNo(), r.getClrgAmt(), now(), new JSONObject());
            return PAID;                                                // onPaid 会把 RELEASING -> SOLD
        }
        if ("P".equals(r.getTranStatus())) unknown = true;
    }
    if (unknown) { holdMapper.incAttempts(h.id); alertIfOver(h, 20); return PENDING; }
    closeChannelIfSupported(bills);                                     // 待确认
    return txTemplate.execute(s -> {
        if (privateOrderMapper.closeOrder(h.orderNumber, now()) == 0) return CHANGED; // 0/1 -> -1
        if (holdMapper.casRelease(h.id, "RELEASING", "RELEASED", reason) == 0) return CHANGED; // 同时 limit_flag=NULL
        poolMapper.decHeld(h.poolId);                                   // where held>0
        if (order.discountId != null) couponBusiness.unlockUserCoupon(order.userId, order.discountId);
        scoreDeduct.returnIfLocked("private", h.orderNumber);           // M6 后续版本，本期不加
        return RELEASED;
    });
}

if (activityConfig.isGrantTarget(D11, combo.getProductGroup(), order.getPayTime())
        && !isPaidWithGrantCoupon(order)) {
    activityGrantBusiness.grantOnce(D11, "PT60_REBUY_700", order);
}

public void grantOnce(...) {
    int n = grantMapper.insertIgnore(code, type, userId, "combo", orderNo, "PENDING"); // 两个唯一键
    if (n == 0) return;                                                 // 非首单或重放
    try {
        Integer ucId = couponBusiness.issueScoped(templateId, userId, scopeSnapshot, code, orderNo); // 单张、显式 new 实体
        grantMapper.granted(code, type, userId, ucId);
    } catch (Exception e) {
        grantMapper.failed(code, type, userId, e.getMessage());        // 补偿任务 activityGrantRetryHandle 重试
    }
}

@Transactional(propagation = REQUIRES_NEW)
public void accrue(String orderType, String orderNo, Integer userId, BigDecimal payAmount, String rateSnapshot) {
    Rate r = Rate.parse(rateSnapshot != null ? rateSnapshot : globalRate());   // 默认取 lianer_score source=2
    int points = payAmount.divide(BigDecimal.valueOf(r.perAmount), 0, RoundingMode.DOWN).intValue() * r.perPoints;
    if (points <= 0) return;
    try { userScoreMapper.insertEarn(userId, points, orderType, orderNo, "购买获得二币"); }
    catch (DuplicateKeyException e) { return; }                       // 幂等
    scoreBusiness.incScore(userId, points);
    userScoreLevelApi.addUserScoreAndLevel(userId, points);
}