青海社区团购小程序：开团模型、自提点库存与团长体系的工程实现

CREATE TABLE leader (
 id           BIGINT PRIMARY KEY AUTO_INCREMENT,
 name         VARCHAR(30) NOT NULL,
 phone        CHAR(64) NOT NULL COMMENT '加密存储',
 station_id   BIGINT NOT NULL COMMENT '绑定自提点',
 commission_rate INT NOT NULL DEFAULT 800 COMMENT '佣金比例，万分比',
 status       TINYINT NOT NULL DEFAULT 1,
 UNIQUE KEY uk_station_phone (station_id, phone)
);

CREATE TABLE station (
 id          BIGINT PRIMARY KEY AUTO_INCREMENT,
 name        VARCHAR(60) NOT NULL,
 addr        VARCHAR(200) NOT NULL,
 lat DECIMAL(10,6), lng DECIMAL(10,6),
 capacity    INT NOT NULL DEFAULT 200 COMMENT '日均自提承载',
 status      TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE group_buy (
 id           BIGINT PRIMARY KEY AUTO_INCREMENT,
 leader_id    BIGINT NOT NULL,
 title        VARCHAR(80) NOT NULL,
 start_at     DATETIME NOT NULL,
 end_at       DATETIME NOT NULL,
 delivery_at  DATE NOT NULL COMMENT '预计到货日',
 status       TINYINT NOT NULL DEFAULT 1
 COMMENT '1开团中 2已截团 3已到货 4自提中 5已结束 6已取消',
 KEY idx_leader (leader_id, start_at)
);

CREATE TABLE gb_sku (
 id           BIGINT PRIMARY KEY AUTO_INCREMENT,
 group_id     BIGINT NOT NULL,
 sku_name     VARCHAR(80) NOT NULL,
 spec         VARCHAR(60) NULL,
 price        INT NOT NULL COMMENT '团购价，单位分',
 stock        INT NOT NULL DEFAULT 0 COMMENT '本次团限量',
 sold         INT NOT NULL DEFAULT 0,
 sort         INT NOT NULL DEFAULT 0,
 KEY idx_group (group_id, sort)
);

CREATE TABLE gb_order (
 id           BIGINT PRIMARY KEY AUTO_INCREMENT,
 order_no     VARCHAR(32) NOT NULL,
 group_id     BIGINT NOT NULL,
 leader_id    BIGINT NOT NULL,
 user_id      BIGINT NOT NULL,
 total        INT NOT NULL,
 commission   INT NOT NULL COMMENT '本单佣金，下单时锁定',
 status       TINYINT NOT NULL DEFAULT 1
 COMMENT '1已支付 2截团 3已到货 4已自提 5售后中 6已退款',
 picked_at    DATETIME NULL,
 UNIQUE KEY uk_no (order_no),
 KEY idx_group_status (group_id, status),
 KEY idx_leader (leader_id, status)
);

async function closeGroup(groupId) {
 const orders = await db.query(
 `SELECT * FROM gb_order WHERE group_id = ? AND status = 1`, [groupId]);

 // 1. 汇总为采购单：按 SKU 聚合
 const purchase = groupBy(orders.map(o => o.items))
 .map(([skuId, qty]) => ({ skuId, qty: sum(qty) }));

 // 2. 拆分为自提点分拣单：按 leader 归组
 const picking = groupBy(orders, o => o.leader_id)
 .map(([leaderId, os]) => ({
 leaderId,
 lines: groupBy(os.map(o => o.items))  // 每个自提点的拣货明细
 }));

 // 3. 生成售后责任快照：每个 SKU 在截团时的供应商
 // （供应商换批次后，售后归属要按截团时点判断）
 await savePurchaseSheet(groupId, purchase);
 await savePickingSheets(groupId, picking);
}

CREATE TABLE gb_refund_item (
 id          BIGINT PRIMARY KEY AUTO_INCREMENT,
 order_id    BIGINT NOT NULL,
 sku_id      BIGINT NOT NULL,
 qty         INT NOT NULL,
 amount      INT NOT NULL,
 reason      VARCHAR(100) NOT NULL COMMENT '缺货/质量/少称',
 status      TINYINT NOT NULL DEFAULT 1 COMMENT '1待退 2已退',
 created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_order (order_id)
);