# 数据库变更记录

## 2026-05-15 订单状态四字段分离（006-orderstate）

```sql
-- 新增 delivery_status 字段（配送状态，仅外送订单使用）
ALTER TABLE pos_order ADD COLUMN delivery_status BIGINT DEFAULT NULL COMMENT '配送状态：0待接单,1骑手已接单,2配送中,3已送达';

-- 新增 pay_status 字段（支付状态）
ALTER TABLE pos_order ADD COLUMN pay_status BIGINT DEFAULT 0 COMMENT '支付状态：0未支付,1已支付,2已退款';

-- 新增 after_sale_status 字段（售后状态）
ALTER TABLE pos_order ADD COLUMN after_sale_status BIGINT DEFAULT 0 COMMENT '售后状态：0无售后,1申请中,2退款中,3已退款,4退款拒绝,5客服介入,6售后完成';

-- 废弃 dining_status 字段（不删除，新逻辑不再使用）
-- ALTER TABLE pos_order DROP COLUMN dining_status;
```

## 2026-05-19 新增订单操作日志表（007-orderlog）

```sql
CREATE TABLE pos_order_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  dd_id VARCHAR(64) NOT NULL COMMENT '订单号',
  operator_type TINYINT NOT NULL COMMENT '操作人类型：0系统,1平台管理员,2商家,3骑手,4用户',
  operator_id BIGINT COMMENT '操作人ID',
  operator_name VARCHAR(128) COMMENT '操作人名称',
  content VARCHAR(512) NOT NULL COMMENT '操作内容',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  INDEX idx_dd_id (dd_id),
  INDEX idx_operator_id (operator_id),
  INDEX idx_create_time (create_time)
) COMMENT='订单操作日志';
```

## 2026-05-19 新增订单操作日志菜单

```sql
-- 新增订单操作日志菜单
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '订单日志', menu_id, 6, 'orderLog', 'system/order/log', 'C', '0', '0', 'system:orderLog:list', 'log', 'admin', NOW(), '订单操作日志菜单'
FROM sys_menu WHERE menu_name = '订单管理' AND parent_id = 0 LIMIT 1;

-- 日志查询按钮权限
SET @logMenuId = (SELECT menu_id FROM sys_menu WHERE perms = 'system:orderLog:list' LIMIT 1);
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('订单日志查询', @logMenuId, 1, '#', '', 'F', '0', '0', 'system:orderLog:query', '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('订单日志导出', @logMenuId, 2, '#', '', 'F', '0', '0', 'system:orderLog:export', '#', 'admin', NOW());
```

## 2026-05-29 促销+优惠券系统（008-promotion-coupon）

```sql
-- 促销活动表
CREATE TABLE promotion_activity (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id        BIGINT       NOT NULL COMMENT '门店ID',
  type            TINYINT      NOT NULL COMMENT '类型: 1=满减 2=折扣 3=第二份半价 4=新客立减',
  name            VARCHAR(100) NOT NULL COMMENT '活动名称',
  status          TINYINT      DEFAULT 0 COMMENT '0=未开始 1=进行中 2=已结束',
  start_time      DATETIME     NOT NULL COMMENT '开始时间',
  end_time        DATETIME     NOT NULL COMMENT '结束时间',
  create_time     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  update_time     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_store_type (store_id, type)
) COMMENT '促销活动表';

-- 促销规则明细表
CREATE TABLE promotion_activity_rule (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  activity_id     BIGINT   NOT NULL COMMENT '关联促销活动',
  product_id      BIGINT   DEFAULT NULL COMMENT '商品ID（折扣/第二份半价用）',
  threshold       DECIMAL(10,2) DEFAULT NULL COMMENT '满减门槛（满X元）',
  reduce_amount   DECIMAL(10,2) DEFAULT NULL COMMENT '减免金额（满减/新客立减）',
  discount_rate   DECIMAL(3,2)  DEFAULT NULL COMMENT '折扣率（0.70=7折 / 0.50=半价）',
  min_quantity    INT       DEFAULT NULL COMMENT '最低数量（第二份半价=2）',
  INDEX idx_activity (activity_id)
) COMMENT '促销规则明细';

-- 券批次表（模板）
CREATE TABLE promotion_coupon_batch (
  id                BIGINT AUTO_INCREMENT PRIMARY KEY,
  store_id          BIGINT       NOT NULL COMMENT '门店ID',
  name              VARCHAR(100) NOT NULL COMMENT '券名称',
  coupon_type       TINYINT      NOT NULL COMMENT '1=满减券 2=商品券',
  total_count       INT          NOT NULL COMMENT '发放总量',
  remain_count      INT          NOT NULL COMMENT '剩余数量',
  received_count    INT          DEFAULT 0 COMMENT '已领取数量',
  status            TINYINT      DEFAULT 0 COMMENT '0=未开始 1=进行中 2=已结束 3=已下架',
  start_time        DATETIME     NOT NULL COMMENT '领取开始时间',
  end_time          DATETIME     NOT NULL COMMENT '领取结束时间',
  valid_days        INT          NOT NULL COMMENT '领取后有效天数',
  create_time       DATETIME     DEFAULT CURRENT_TIMESTAMP,
  update_time       DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_store_status (store_id, status)
) COMMENT '券批次表(模板)';

-- 券规则表
CREATE TABLE promotion_coupon_rule (
  id                BIGINT AUTO_INCREMENT PRIMARY KEY,
  batch_id          BIGINT   NOT NULL COMMENT '关联券批次',
  product_id        BIGINT   DEFAULT NULL COMMENT '商品ID（商品券用，满减券=NULL）',
  is_mutex          TINYINT  DEFAULT 0 COMMENT '0=同享券 1=互斥券（满减券用）',
  threshold         DECIMAL(10,2) DEFAULT NULL COMMENT '使用门槛（满X元可用）',
  amount            DECIMAL(10,2) DEFAULT NULL COMMENT '减免金额（满减券/抵用券）',
  discount_rate     DECIMAL(3,2)  DEFAULT NULL COMMENT '折扣率（商品折扣券用）',
  INDEX idx_batch (batch_id)
) COMMENT '券规则';

-- 用户领券记录
CREATE TABLE promotion_user_coupon (
  id                BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id           BIGINT   NOT NULL COMMENT '用户ID',
  batch_id          BIGINT   NOT NULL COMMENT '券批次ID',
  store_id          BIGINT   NOT NULL COMMENT '门店ID',
  status            TINYINT  DEFAULT 0 COMMENT '0=未使用 1=已使用 2=已过期 3=冻结',
  order_id          BIGINT   DEFAULT NULL COMMENT '使用的订单ID',
  receive_time      DATETIME NOT NULL COMMENT '领取时间',
  use_time          DATETIME DEFAULT NULL COMMENT '使用时间',
  expire_time       DATETIME NOT NULL COMMENT '过期时间',
  INDEX idx_user_status (user_id, status),
  INDEX idx_store (store_id)
) COMMENT '用户领券记录';
```

## 2026-06-01 订单优惠明细表（008-promotion-coupon）

```sql
CREATE TABLE pos_order_promotion (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id        BIGINT       NOT NULL COMMENT '关联订单ID',
  promo_type      TINYINT      NOT NULL COMMENT '优惠类型: 1=促销活动 2=商家优惠券',
  promo_sub_type  TINYINT      DEFAULT NULL COMMENT '促销子类型: 1=满减 2=折扣 3=第二份半价 4=新客立减 (promo_type=1时有效)',
  promo_id        BIGINT       DEFAULT NULL COMMENT '促销活动ID(promo_type=1) 或 券批次ID(promo_type=2)',
  user_coupon_id  BIGINT       DEFAULT NULL COMMENT '用户券ID (仅优惠券 promo_type=2 时有值, 关联 promotion_user_coupon.id)',
  promo_name      VARCHAR(200) NOT NULL COMMENT '快照名称: 如"午市满减(满40减12)"',
  promo_detail    VARCHAR(500) DEFAULT NULL COMMENT '快照详情JSON: 如{"threshold":40,"reduce":12}',
  reduce_amount   DECIMAL(10,2) NOT NULL COMMENT '减免金额',
  path_summary    VARCHAR(500) DEFAULT NULL COMMENT '路径对比摘要, 仅第一条记录有值, 如"满减路径¥33 vs 折扣路径¥39, 选择满减"',
  create_time     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order (order_id)
) COMMENT '订单优惠明细';
```

## 2026-06-01 优惠券类型扩展

```
-- 2026-06-01 优惠券类型扩展：coupon_type COMMENT 更新为 '1=满减券 2=商品券 3=免配送费券'（无需实际 ALTER，仅记录）
```

## 2026-06-15 商家 ezPay 发票开通管理（009-ezpay-invoice-onboarding）

```sql
-- 1. pos_store 加列：是否免用发票
ALTER TABLE pos_store ADD COLUMN invoice_exempt TINYINT(1) DEFAULT 0 COMMENT '是否免用发票:0需开票/1免用发票';

-- 2. 新增门店 ezPay 开通配置表（与 pos_store 1:1）
CREATE TABLE pos_store_ezpay (
  id                 BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
  store_id           BIGINT       NOT NULL COMMENT '关联 pos_store.id（门店）',
  ezpay_status       TINYINT      NOT NULL DEFAULT 0 COMMENT '申请状态:0未申请/1申请中/2已开通',
  is_enabled         TINYINT      NOT NULL DEFAULT 1 COMMENT '启用开关:0停用/1启用(仅status=2有意义)',
  merchant_id        VARCHAR(32)  DEFAULT NULL COMMENT 'ezPay 商店代号 MerchantID_',
  hash_key           VARCHAR(64)  DEFAULT NULL COMMENT 'ezPay HashKey(32字节)',
  hash_iv            VARCHAR(64)  DEFAULT NULL COMMENT 'ezPay HashIV(16字节)',
  company_id         VARCHAR(32)  DEFAULT NULL COMMENT 'ezPay 会员编号 CompanyID_(字轨用,可空)',
  ubn                VARCHAR(16)  DEFAULT NULL COMMENT '统一编号(统编,商家上传)',
  apply_time         DATETIME     DEFAULT NULL COMMENT '提交申请时间(0->1)',
  approved_time      DATETIME     DEFAULT NULL COMMENT '开通时间(->2)',
  last_verify_result VARCHAR(255) DEFAULT NULL COMMENT '最近一次凭证验证结果',
  remark             VARCHAR(500) DEFAULT NULL COMMENT '备注',
  create_time        DATETIME     DEFAULT NULL COMMENT '创建时间',
  update_time        DATETIME     DEFAULT NULL COMMENT '更新时间',
  create_by          VARCHAR(64)  DEFAULT NULL COMMENT '创建人',
  update_by          VARCHAR(64)  DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (id),
  UNIQUE KEY uk_store_id (store_id),
  KEY idx_ezpay_status (ezpay_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店 ezPay 发票开通配置';

-- 3. 平台后台菜单：ezPay 发票开通管理（挂在门店菜单同级父节点下）
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 'ezPay发票管理', parent_id, 7, 'storeEzpay', 'mendian/storeEzpay/index', 'C', '0', '0', 'chanting:storeEzpay:list', 'money', 'admin', NOW(), '商家 ezPay 发票开通管理'
FROM sys_menu WHERE perms = 'chanting:store:list' LIMIT 1;

-- 按钮权限
SET @ezpayMenuId = (SELECT menu_id FROM sys_menu WHERE perms = 'chanting:storeEzpay:list' LIMIT 1);
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('ezPay详情', @ezpayMenuId, 1, '#', '', 'F', '0', '0', 'chanting:storeEzpay:query', '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('发起申请', @ezpayMenuId, 2, '#', '', 'F', '0', '0', 'chanting:storeEzpay:apply', '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('录入凭证', @ezpayMenuId, 3, '#', '', 'F', '0', '0', 'chanting:storeEzpay:saveCredentials', '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('停用/恢复', @ezpayMenuId, 4, '#', '', 'F', '0', '0', 'chanting:storeEzpay:toggleEnable', '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('免用发票标记', @ezpayMenuId, 5, '#', '', 'F', '0', '0', 'chanting:storeEzpay:markExempt', '#', 'admin', NOW());
```


## 2026-06-16 订单 ezPay 电子发票开立（010-order-invoice）

```sql
-- 1. 新增订单电子发票表（与 pos_order 1:1，当前态：作废后可重开同行覆盖）
CREATE TABLE pos_order_invoice (
  id                 BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
  order_id           BIGINT       NOT NULL COMMENT '关联 pos_order.id',
  order_no           VARCHAR(64)  DEFAULT NULL COMMENT '冗余 pos_order.dd_id',
  store_id           BIGINT       NOT NULL COMMENT '冗余 pos_store.id（门店）',
  invoice_category   VARCHAR(8)   DEFAULT NULL COMMENT '发票类型 B2C/B2B',
  buyer_name         VARCHAR(100) DEFAULT NULL COMMENT '买方名称',
  buyer_ubn          VARCHAR(16)  DEFAULT NULL COMMENT '买方统一编号(统编8码)',
  buyer_email        VARCHAR(200) DEFAULT NULL COMMENT '接收邮箱',
  carrier_type       VARCHAR(8)   DEFAULT NULL COMMENT '载具类型 0手机条码/1自然人凭证/2ezPay会员',
  carrier_num        VARCHAR(64)  DEFAULT NULL COMMENT '载具号码',
  invoice_number     VARCHAR(16)  DEFAULT NULL COMMENT 'ezPay 发票号',
  random_num         VARCHAR(8)   DEFAULT NULL COMMENT '防伪随机码',
  invoice_status     TINYINT      NOT NULL DEFAULT 0 COMMENT '0未开/1已开/2失败/3作废',
  total_amt          INT          DEFAULT NULL COMMENT '含税总额(=amount-freight)',
  sales_amt          INT          DEFAULT NULL COMMENT '销售额未税',
  tax_amt            INT          DEFAULT NULL COMMENT '税额',
  fail_reason        VARCHAR(500) DEFAULT NULL COMMENT '失败原因',
  invoice_url        VARCHAR(500) DEFAULT NULL COMMENT '发票查看凭证/链接',
  apply_time         DATETIME     DEFAULT NULL COMMENT '申请开票时间',
  issue_time         DATETIME     DEFAULT NULL COMMENT '开立成功时间',
  invalid_time       DATETIME     DEFAULT NULL COMMENT '作废时间',
  create_time        DATETIME     DEFAULT NULL COMMENT '创建时间',
  update_time        DATETIME     DEFAULT NULL COMMENT '更新时间',
  create_by          VARCHAR(64)  DEFAULT NULL COMMENT '创建人',
  update_by          VARCHAR(64)  DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_id (order_id),
  KEY idx_status (invoice_status),
  KEY idx_store (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单电子发票(与pos_order 1:1)';

-- 2. 平台后台菜单：订单发票管理（挂在门店菜单同级父节点下，参考 009 写法）
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '订单发票管理', parent_id, 8, 'orderInvoice', 'mendian/orderInvoice/index', 'C', '0', '0', 'chanting:orderInvoice:list', 'documentation', 'admin', NOW(), '订单 ezPay 电子发票查看/重试/作废'
FROM sys_menu WHERE perms = 'chanting:store:list' LIMIT 1;

-- 按钮权限
SET @invMenuId = (SELECT menu_id FROM sys_menu WHERE perms = 'chanting:orderInvoice:list' LIMIT 1);
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('发票详情', @invMenuId, 1, '#', '', 'F', '0', '0', 'chanting:orderInvoice:query',   '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('重新开票', @invMenuId, 2, '#', '', 'F', '0', '0', 'chanting:orderInvoice:retry',   '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('作废发票', @invMenuId, 3, '#', '', 'F', '0', '0', 'chanting:orderInvoice:invalid', '#', 'admin', NOW());
```


```sql
-- ============================================================
-- 2026-06-22 蓝新金流(NewebPay)线上支付接入 (specs/011-newebpay-payment)
-- ============================================================

-- 1. 门店蓝新支付凭证表（与 pos_store 1:1，复用 009 pos_store_ezpay 状态机模式）
CREATE TABLE pos_store_newebpay (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  store_id BIGINT NOT NULL COMMENT '关联 pos_store.id(门店)',
  newebpay_status INT NOT NULL DEFAULT 0 COMMENT '申请状态:0未申请/1申请中/2已开通',
  is_enabled INT NOT NULL DEFAULT 1 COMMENT '启用开关:0停用/1启用',
  merchant_id VARCHAR(15) DEFAULT NULL COMMENT '蓝新商店代号 MerchantID',
  hash_key VARCHAR(64) DEFAULT NULL COMMENT '蓝新 HashKey(32字节)',
  hash_iv VARCHAR(32) DEFAULT NULL COMMENT '蓝新 HashIV(16字节)',
  enabled_payments VARCHAR(50) DEFAULT 'CREDIT' COMMENT '启用支付方式:CREDIT,LINEPAY,APPLEPAY',
  apply_time DATETIME DEFAULT NULL COMMENT '申请时间',
  approved_time DATETIME DEFAULT NULL COMMENT '开通时间',
  last_verify_result VARCHAR(240) DEFAULT NULL COMMENT '最近验证结果',
  remark VARCHAR(255) DEFAULT NULL COMMENT '备注',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  update_time DATETIME DEFAULT NULL COMMENT '更新时间',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (id),
  UNIQUE KEY uk_store_id (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店蓝新金流支付凭证';

-- 2. 支付交易流水表（每笔蓝新交易一条，按 trade_no 幂等）
CREATE TABLE pos_order_payment (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  dd_id VARCHAR(64) NOT NULL COMMENT '系统订单号(关联 pos_order.dd_id)',
  merchant_order_no VARCHAR(30) NOT NULL COMMENT '商店订单号 MerchantOrderNo',
  trade_no VARCHAR(20) DEFAULT NULL COMMENT '蓝新交易序号 TradeNo(幂等键)',
  store_id BIGINT DEFAULT NULL COMMENT '门店ID',
  merchant_id VARCHAR(15) DEFAULT NULL COMMENT '蓝新商店代号',
  pay_type VARCHAR(20) DEFAULT NULL COMMENT '支付方式:CREDIT/LINEPAY/APPLEPAY',
  amount INT DEFAULT NULL COMMENT '交易金额(整数元)',
  pay_status INT NOT NULL DEFAULT 0 COMMENT '支付状态:0未支付/1已支付/2失败',
  auth_code VARCHAR(20) DEFAULT NULL COMMENT '授权码',
  pay_time DATETIME DEFAULT NULL COMMENT '支付完成时间',
  callback_raw TEXT COMMENT '回调原始解密结果',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  update_time DATETIME DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_trade_no (trade_no),
  KEY idx_dd_id (dd_id),
  KEY idx_merchant_order_no (merchant_order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='蓝新支付交易流水';
```


```sql
-- 3. 平台后台菜单：门店蓝新支付开通管理（挂在门店菜单同级父节点下，参考 009/010 写法）
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '门店蓝新支付', parent_id, 9, 'storeNewebpay', 'mendian/storeNewebpay/index', 'C', '0', '0', 'chanting:storeNewebpay:list', 'money', 'admin', NOW(), '门店蓝新金流支付凭证开通管理'
FROM sys_menu WHERE perms = 'chanting:store:list' LIMIT 1;

-- 按钮权限
SET @nbMenuId = (SELECT menu_id FROM sys_menu WHERE perms = 'chanting:storeNewebpay:list' LIMIT 1);
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('门店详情',   @nbMenuId, 1, '#', '', 'F', '0', '0', 'chanting:storeNewebpay:query',           '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('发起申请',   @nbMenuId, 2, '#', '', 'F', '0', '0', 'chanting:storeNewebpay:apply',            '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('录入凭证',   @nbMenuId, 3, '#', '', 'F', '0', '0', 'chanting:storeNewebpay:saveCredentials', '#', 'admin', NOW());
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
VALUES ('停用恢复',   @nbMenuId, 4, '#', '', 'F', '0', '0', 'chanting:storeNewebpay:toggleEnable',    '#', 'admin', NOW());
```

```sql
-- 2026-06-22 发票凭证字段补全（invoice_url 改名 + 新增 3 个码值列，保留原数据）
ALTER TABLE pos_order_invoice CHANGE COLUMN invoice_url invoice_trans_no VARCHAR(64) DEFAULT NULL COMMENT 'ezPay 交易流水号 InvoiceTransNo';
ALTER TABLE pos_order_invoice ADD COLUMN invoice_bar_code VARCHAR(255) DEFAULT NULL COMMENT '发票条码码值(PrintFlag=Y 才有)';
ALTER TABLE pos_order_invoice ADD COLUMN invoice_qrcode_l VARCHAR(255) DEFAULT NULL COMMENT '发票左二维码码值(PrintFlag=Y 才有)';
ALTER TABLE pos_order_invoice ADD COLUMN invoice_qrcode_r VARCHAR(255) DEFAULT NULL COMMENT '发票右二维码码值(PrintFlag=Y 才有)';
```

```sql
-- 2026-06-23 IM 即时沟通账号接入（012-im-user-integration）：info_user 新增 IM 凭证两列
ALTER TABLE info_user ADD COLUMN im_api_key VARCHAR(64) DEFAULT NULL COMMENT 'IM平台API密钥(extCreate返回)';
ALTER TABLE info_user ADD COLUMN im_user_id BIGINT DEFAULT NULL COMMENT 'IM平台用户ID(extCreate返回)';
```

```sql
-- 2026-06-30 推送 token 字段：info_user 新增设备推送 token（FCM/APNs 普通推送）与 iOS VoIP 来电 token
ALTER TABLE info_user ADD COLUMN device_token VARCHAR(255) DEFAULT NULL COMMENT '设备推送token(普通推送:Android FCM / iOS APNs device token)';
ALTER TABLE info_user ADD COLUMN voip_token VARCHAR(255) DEFAULT NULL COMMENT 'iOS VoIP推送token(PushKit,语音/视频来电推送)';
```

```sql
-- 2026-07-02 info_user 新增设备类型字段 cid_type（区分设备类型，如 ios/android，登录/注册时由客户端传入）
ALTER TABLE info_user ADD COLUMN cid_type VARCHAR(32) DEFAULT NULL COMMENT '设备类型(ios/android等,区分设备类型)';
```


## 2026-07-15 商品规格三表（013-food-spec）

```sql
-- 规格组（门店级可复用规格维度，如 甜度/加料）
CREATE TABLE food_specs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(64) NOT NULL COMMENT '规格名（如 甜度）',
  type CHAR(1) DEFAULT '1' COMMENT '选择类型：1=单选 2=多选',
  state CHAR(1) DEFAULT '1' COMMENT '是否必选：1=必选 2=可选',
  language VARCHAR(8) COMMENT '语言',
  remark VARCHAR(255) COMMENT '备注',
  md_id BIGINT COMMENT '所属门店id',
  sort INT DEFAULT 0 COMMENT '排序',
  is_open TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  is_delete TINYINT(1) DEFAULT 0 COMMENT '软删除标记',
  INDEX idx_md_lang (md_id, language, is_delete, is_open)
) COMMENT='商品规格组（门店级）';

-- 规格值（规格组下的可选项，带加价）
CREATE TABLE food_specs_value (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  parent_id BIGINT NOT NULL COMMENT '所属规格组id（food_specs.id）',
  name VARCHAR(64) NOT NULL COMMENT '规格值名（如 无糖）',
  price DECIMAL(10,2) DEFAULT 0 COMMENT '加价金额（与 pos_food.price 同口径）',
  note VARCHAR(255) COMMENT '备注',
  state CHAR(1) COMMENT '状态',
  is_open TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  INDEX idx_parent (parent_id, is_open)
) COMMENT='商品规格值';

-- 商品-规格关联（多对多）
CREATE TABLE food_spec_relation (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  food_id BIGINT NOT NULL COMMENT '商品id（pos_food.id）',
  specs_id BIGINT NOT NULL COMMENT '规格组id（food_specs.id）',
  INDEX idx_food (food_id),
  INDEX idx_specs (specs_id)
) COMMENT='商品-规格关联';

-- pos_food.food_sku 列已存在（text/json，存规格 JSON 冗余），无需新增；
-- 若历史库缺失该列则执行：ALTER TABLE pos_food ADD COLUMN food_sku TEXT COMMENT '商品属性/规格JSON' AFTER language;
```

## 2026-07-20 订单发票加捐赠码字段（010-order-invoice US6）

```sql
-- 订单发票表加捐赠码两列（捐赠发票时填，普通发票为空）
ALTER TABLE pos_order_invoice
  ADD COLUMN love_code VARCHAR(10) DEFAULT NULL COMMENT '捐赠码(爱心码,3-7位,含前导零)',
  ADD COLUMN love_org_name VARCHAR(255) DEFAULT NULL COMMENT '捐赠机构名(开票时从pos_love_org快照)';

-- 捐赠机构字典 pos_love_org 表 + 2016 行数据见 updatesql/pos_love_org.sql
-- （由 specs/010-order-invoice/gen_love_org_sql.py 从财政部 CSV 生成，需另执行）
```

## 2026-07-24 用户发票抬头管理（014-invoice-profile）

```sql
-- 用户发票抬头表（用户级，镜像 info_address；区别于订单级 pos_order_invoice）
CREATE TABLE info_invoice (
  id            BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
  user_id       BIGINT       NOT NULL                COMMENT '用户id（JWT隔离）',
  title_name    VARCHAR(50)  NOT NULL                COMMENT '抬头备注名(如 公司-美食達)',
  category      VARCHAR(8)   NOT NULL                COMMENT '类型 B2C=个人/B2B=公司',
  buyer_name    VARCHAR(100) NOT NULL                COMMENT '买方名称 B2C=个人姓名 B2B=公司名',
  buyer_ubn     VARCHAR(16)  DEFAULT NULL            COMMENT '统一编号(统编,8位数字,含前导零);仅B2B',
  buyer_email   VARCHAR(200) DEFAULT NULL            COMMENT '邮箱;B2B必填/B2C仅会员载具必填',
  carrier_type  VARCHAR(8)   DEFAULT NULL            COMMENT '载具类型 0手机条码/1自然人凭证/2ezPay会员;仅B2C',
  carrier_num   VARCHAR(64)  DEFAULT NULL            COMMENT '载具号码',
  create_time   DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户发票抬头(发票信息管理)';
```

## 2026-07-28 订单自动开票：发票意图落点调整 + 抬头默认标记（010-order-invoice Phase 11，方案B）

> 方案B：发票意图（下单捕获的 invoiceChoice/载具/统编/捐赠码）改落 `pos_order_invoice` 意图行（与开票结果同表，避免意图/结果双写），`pos_order` 不再加发票列。
> `pos_order_invoice` 加 `invoice_choice`（下单意图）+ `issue_triggered`（是否已触发开票 0/1，与 invoice_status 独立：下单捕获意图=0、出餐开票流程执行过=1；管理端「订单发票管理」按 `issue_triggered=1` 筛选，排除未触发的意图行）。

```sql
-- ① pos_order 发票意图列【已废弃·方案B】：意图改落 pos_order_invoice，pos_order 不再加列。
--    若 2026-07-27 初版 pos_order 加列已执行，用以下 DROP 回滚；未执行则跳过本段。
ALTER TABLE pos_order
  DROP COLUMN invoice_choice,
  DROP COLUMN carrier_type,
  DROP COLUMN carrier_num,
  DROP COLUMN buyer_ubn,
  DROP COLUMN invoice_buyer_name,
  DROP COLUMN love_code;

-- ② pos_order_invoice 加发票意图列（方案B：下单捕获意图 + 是否已触发开票标记）
ALTER TABLE pos_order_invoice
  ADD COLUMN invoice_choice   VARCHAR(16) DEFAULT NULL COMMENT '发票选择(下单意图) PAPER个人纸本/PHONE_BARCODE手机条码/CITIZEN自然人凭证/LOVE_CODE捐赠/COMPANY公司;NULL=该单不开票',
  ADD COLUMN issue_triggered  TINYINT     NOT NULL DEFAULT 0 COMMENT '是否已触发开票 0否1是;出餐开票流程执行过即为1,与 invoice_status 独立(下单捕获意图=0;管理端发票管理按=1筛选,排除未触发的意图行)';

-- ③ info_invoice 加"是否默认"标记（014 原"无默认"FR-006，现支持全联式"同意设置为默认载具/抬头"）
ALTER TABLE info_invoice
  ADD COLUMN is_default TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认载具/抬头 0否 1是(同一用户仅一条为1)';
```


## 2026-07-30 新增三方登录绑定表（017-oauth-login）

```sql
CREATE TABLE info_user_oauth (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id      BIGINT       NOT NULL COMMENT '关联 info_user.user_id',
  provider     VARCHAR(16)  NOT NULL COMMENT '三方渠道：apple/google/line',
  provider_uid VARCHAR(64)  NOT NULL COMMENT '三方稳定用户ID（Apple sub / Google sub / LINE userId）',
  create_time  DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
  UNIQUE KEY uk_provider_uid (provider, provider_uid),
  KEY idx_user_id (user_id)
) COMMENT='三方账号绑定（Apple/Google/LINE）';
```

## 2026-08-03 骑手 newTask 距离上限字典

```sql
-- 骑手 newTask 距离上限（PosOrderQsOprateController.orderList newTask 分支：距商家超过该值的订单不显示给骑手）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
VALUES ('骑手新任务距离上限', 'sys_qs_newtask_distance', '0', 'admin', NOW(), '骑手 newTask 列表只显示距商家该距离(公里)内的订单');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
VALUES (1, '30公里', '30', 'sys_qs_newtask_distance', '', 'default', 'Y', '0', 'admin', NOW(), '骑手新任务距商家距离上限(公里)，超过不显示');
```

## 2026-08-03 骑手车型字段

```sql
-- 骑手车型（InfoUser.vehicleType，仅骑手 userType=2 用）：1=机车 2=轿车
ALTER TABLE info_user ADD COLUMN vehicle_type VARCHAR(2) DEFAULT NULL COMMENT '骑手车型 1机车 2轿车';
```

## 2026-07-29 OMG(歐買尬/FunPoint)支付接入（016-omg-payment）

```sql
-- 门店 OMG 支付凭证表（与 pos_store 1:1）
CREATE TABLE pos_store_omg (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  store_id BIGINT NOT NULL COMMENT '关联 pos_store.id(门店)',
  omg_status INT NOT NULL DEFAULT 0 COMMENT '申请状态:0未申请/1申请中/2已开通',
  is_enabled INT NOT NULL DEFAULT 1 COMMENT '启用开关:0停用/1启用',
  merchant_id VARCHAR(10) DEFAULT NULL COMMENT 'OMG商店代号 MerchantID',
  hash_key VARCHAR(64) DEFAULT NULL COMMENT 'OMG HashKey',
  hash_iv VARCHAR(64) DEFAULT NULL COMMENT 'OMG HashIV',
  enabled_payments VARCHAR(50) DEFAULT 'ALL' COMMENT '启用支付方式(本期ALL)',
  apply_time DATETIME DEFAULT NULL COMMENT '申请时间',
  approved_time DATETIME DEFAULT NULL COMMENT '开通时间',
  last_verify_result VARCHAR(240) DEFAULT NULL COMMENT '最近验证结果',
  remark VARCHAR(255) DEFAULT NULL COMMENT '备注',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  update_time DATETIME DEFAULT NULL COMMENT '更新时间',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (id),
  UNIQUE KEY uk_store_id (store_id),
  UNIQUE KEY uk_merchant_id (merchant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店OMG(歐買尬)支付凭证';

-- OMG 支付交易流水表（trade_no 幂等键，MySQL 唯一索引允许多个 NULL）
CREATE TABLE pos_order_omg_payment (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  dd_id VARCHAR(64) NOT NULL COMMENT '系统订单号(关联 pos_order.dd_id)',
  merchant_trade_no VARCHAR(20) NOT NULL COMMENT '商店交易编号 MerchantTradeNo',
  trade_no VARCHAR(20) DEFAULT NULL COMMENT 'OMG交易编号 TradeNo(幂等键)',
  store_id BIGINT NOT NULL COMMENT '门店ID',
  merchant_id VARCHAR(10) NOT NULL COMMENT 'OMG商店代号',
  choose_payment VARCHAR(20) DEFAULT 'ALL' COMMENT '发起方式',
  pay_type VARCHAR(32) DEFAULT NULL COMMENT '回覆PaymentType:Credit_CreditCard/ATM_*/CVS_*/AFTEE_AFTEE/BarcodeATM_CHINATRUST',
  amount INT NOT NULL COMMENT '交易金额(整数元)',
  rtn_code INT DEFAULT NULL COMMENT '回调RtnCode(1=成功)',
  rtn_msg VARCHAR(200) DEFAULT NULL COMMENT '回调RtnMsg',
  pay_status INT NOT NULL DEFAULT 0 COMMENT '0未支付/1已支付/2失败/3已退款/4退款中',
  auth_code VARCHAR(20) DEFAULT NULL COMMENT '授权码',
  pay_time DATETIME DEFAULT NULL COMMENT '支付完成时间',
  trade_date DATETIME DEFAULT NULL COMMENT '订单创建时间',
  callback_raw TEXT COMMENT '回调原始报文',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  update_time DATETIME DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_trade_no (trade_no),
  UNIQUE KEY uk_merchant_trade_no (merchant_trade_no),
  KEY idx_dd_id (dd_id),
  KEY idx_dd_id_pay_status_id (dd_id, pay_status, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OMG支付交易流水';

-- OMG 退款记录表
CREATE TABLE pos_order_omg_refund (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  payment_id BIGINT DEFAULT NULL COMMENT '关联 pos_order_omg_payment.id',
  dd_id VARCHAR(64) DEFAULT NULL COMMENT '系统订单号',
  trade_no VARCHAR(20) DEFAULT NULL COMMENT 'OMG交易编号 TradeNo',
  action CHAR(1) DEFAULT NULL COMMENT 'DoAction动作:C/R/E/N',
  amount INT DEFAULT NULL COMMENT '操作金额',
  rtn_code INT DEFAULT NULL COMMENT '响应RtnCode(1=成功)',
  rtn_msg VARCHAR(200) DEFAULT NULL COMMENT '响应RtnMsg',
  callback_raw TEXT COMMENT '响应原始报文',
  create_time DATETIME DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (id),
  KEY idx_payment_id (payment_id),
  KEY idx_dd_id (dd_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OMG退款记录';
```

## 2026-08-06 OMG支付流水约束与退款状态修复（已部署016表的环境执行）

```sql
-- 执行 ALTER 前必须先确认以下查询均返回 0 行；发现重复或空关联时先人工核对流水，禁止直接删除。
SELECT merchant_trade_no, COUNT(*) AS duplicate_count
FROM pos_order_omg_payment
GROUP BY merchant_trade_no
HAVING COUNT(*) > 1;

SELECT merchant_id, COUNT(*) AS duplicate_count
FROM pos_store_omg
WHERE merchant_id IS NOT NULL
GROUP BY merchant_id
HAVING COUNT(*) > 1;

SELECT id, dd_id, store_id, merchant_id, amount
FROM pos_order_omg_payment
WHERE store_id IS NULL OR merchant_id IS NULL OR amount IS NULL;

-- MerchantTradeNo 是回调关联键，必须唯一；扩大 PaymentType 以容纳 BarcodeATM_CHINATRUST。
ALTER TABLE pos_order_omg_payment
  DROP INDEX idx_merchant_trade_no,
  ADD UNIQUE KEY uk_merchant_trade_no (merchant_trade_no),
  ADD KEY idx_dd_id_pay_status_id (dd_id, pay_status, id),
  MODIFY COLUMN store_id BIGINT NOT NULL COMMENT '门店ID',
  MODIFY COLUMN merchant_id VARCHAR(10) NOT NULL COMMENT 'OMG商店代号',
  MODIFY COLUMN pay_type VARCHAR(32) DEFAULT NULL COMMENT '回覆PaymentType',
  MODIFY COLUMN amount INT NOT NULL COMMENT '交易金额(整数元)',
  MODIFY COLUMN pay_status INT NOT NULL DEFAULT 0 COMMENT '0未支付/1已支付/2失败/3已退款/4退款中';

-- MerchantID 由回调反查唯一门店凭证，禁止重复配置到多个门店。
ALTER TABLE pos_store_omg
  ADD UNIQUE KEY uk_merchant_id (merchant_id);
```

## 2026-07-29 OMG支付接入 - 门店支付配置菜单与权限（016-omg-payment US5，Tab页：OMG可用 / LINE Pay预留）

```sql
-- 注：parent_id 需匹配平台后台实际「门店/支付」目录的 menu_id；
--      部署前先 SELECT id, menu_name FROM sys_menu WHERE menu_name LIKE '%门店%'; 核对后替换占位值。

-- 主菜单（页面）
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark)
VALUES ('门店支付配置', 0, 50, 'storePayment', 'mendian/storePayment/index', 'C', '0', '0', 'chanting:storeOmg:list', '#', 'admin', NOW(), '门店支付配置(OMG；后续在同一页加Tab扩展LINE Pay等)');
SET @omgMenuId = LAST_INSERT_ID();

-- 按钮权限
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark) VALUES
('OMG凭证查询', @omgMenuId, 1, '#', '', 'F', '0', '0', 'chanting:storeOmg:query', '#', 'admin', NOW(), ''),
('OMG发起申请', @omgMenuId, 2, '#', '', 'F', '0', '0', 'chanting:storeOmg:apply', '#', 'admin', NOW(), ''),
('OMG录入凭证', @omgMenuId, 3, '#', '', 'F', '0', '0', 'chanting:storeOmg:saveCredentials', '#', 'admin', NOW(), ''),
('OMG停用恢复', @omgMenuId, 4, '#', '', 'F', '0', '0', 'chanting:storeOmg:toggleEnable', '#', 'admin', NOW(), '');
```


## 2026-08-05 修复：会员载具(MEMBER)误用 ezPay 电子载具 type2（016/010）

```sql
-- 背景：MEMBER 原映射 carrier_type='2'(ezPay会员载具+PrintFlag=N)，ezPay 强制要 BuyerEmail → INV10013 失败。
-- 决策(2026-08-05)：MEMBER 改走 PrintFlag=Y 索取纸本（不传 CarrierType），代码已改。
-- 本 SQL 清空历史 MEMBER 发票行里误存的 carrier_type/carrier_num，使重开(retry)与详情展示和代码一致
-- (会员手机号已在 buyer_name)。
UPDATE pos_order_invoice SET carrier_type = NULL, carrier_num = NULL
WHERE invoice_choice = 'MEMBER' AND carrier_type = '2';
```


## 2026-08-07 OMG支付漏单补单 - 流水扫描索引（016-omg-payment US6 方案B，定时补单扫描用）

```sql
-- 背景：OmgReconcileTask 定时扫描 pay_status=0 且 create_time 在补单窗口内的漏单。
--      现有 idx_dd_id_pay_status_id(dd_id,pay_status,id) 以 dd_id 打头，不适合按 pay_status+create_time 的全局窗口扫描。
--      加此复合索引提速扫描，不直接执行，由开发者统一手动执行。
ALTER TABLE pos_order_omg_payment ADD INDEX idx_pay_status_create (pay_status, create_time);
```

## 2026-08-12 OMG 3 表 collation 对齐 pos_order(修复定时补单 JOIN Illegal mix of collations)

```sql
-- 背景:定时补单 selectLeakOrderDdIds 报 "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
--      and (utf8mb4_general_ci,IMPLICIT) for operation '='"。根因:pos_order 是 utf8mb4_unicode_ci,
--      OMG 3 新表(pos_order_omg_payment/pos_order_omg_refund/pos_store_omg)建表时 CHARSET=utf8mb4
--      未指定 COLLATE,落成 utf8mb4_general_ci,JOIN pos_order.dd_id 时 collation 冲突。
--      把 3 表 CONVERT 到 utf8mb4_unicode_ci 对齐 pos_order(老表是核心,不动)。
--      安全:charset 不变(均 utf8mb4),仅 collation general_ci->unicode_ci;dd_id/merchant_trade_no 等
--      纯字母数字列不受排序语义影响。执行后 selectLeakOrderDdIds 等所有 JOIN pos_order 的 SQL 不再冲突。
ALTER TABLE pos_order_omg_payment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE pos_order_omg_refund CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE pos_store_omg CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

## 2026-08-12 OMG 账本重构(追加式 S):is_active 单活跃轴 + expire_date + 生成列唯一索引(016 Phase 10 T056)

```sql
-- 设计见 specs/016-omg-payment/payment-attempt-lifecycle.md §6.1。双轴:is_active ⊥ pay_status,
-- 轮换只翻 is_active 1→0 不动 pay_status → 迟到 notify 的 markSuccess CAS IN(0,2) 永命中。
-- 执行前先确认生产 MySQL 版本:SELECT VERSION();(生成列唯一索引需 5.7.6+)

ALTER TABLE pos_order_omg_payment ADD COLUMN expire_date DATETIME NULL
  COMMENT 'OMG 延期支付真实 ExpireDate,仅由 paymentInfo 回调解析写入;NULL=未知(信用卡/未取号),绝不由后台任务触发换号';
ALTER TABLE pos_order_omg_payment ADD COLUMN is_active TINYINT NOT NULL DEFAULT 1
  COMMENT '1=当前活跃尝试 0=已轮换历史(保留审计+迟到notify落地);与 pay_status 正交,轮换只翻 is_active 不动 pay_status';

-- 生成列唯一索引:每 ddId 同时至多一条活跃未付(is_active=1 AND pay_status=0)行(DB 级强制单活跃)
-- ⚠ 需 MySQL 5.7.6+;不够则注释掉下两行,退化为普通索引 idx_omg_dd_active(dd_id,is_active,pay_status)+ app 锁 + CAS
ALTER TABLE pos_order_omg_payment ADD COLUMN active_dd_id BIGINT
  GENERATED ALWAYS AS (IF(is_active=1 AND pay_status=0, dd_id, NULL)) VIRTUAL
  COMMENT '单活跃不变量载体';
ALTER TABLE pos_order_omg_payment ADD UNIQUE KEY uk_omg_active_dd (active_dd_id);

-- 历史回填(绝不回填 expire_date,NULL=未知):
-- ① 已终态(pay_status<>0)行 is_active=0
UPDATE pos_order_omg_payment SET is_active=0 WHERE pay_status<>0;
-- ② pay_status=0 的行:每 ddId 保留最新(id 最大)一条 is_active=1,其余置 0
UPDATE pos_order_omg_payment p
INNER JOIN (SELECT dd_id, MAX(id) AS max_id FROM pos_order_omg_payment WHERE pay_status=0 GROUP BY dd_id) m
  ON p.dd_id=m.dd_id
SET p.is_active = IF(p.id=m.max_id, 1, 0)
WHERE p.pay_status=0;

-- 验证:每 ddId 至多一条 is_active=1 AND pay_status=0(应返回 0 行;若 >0 说明回填或唯一索引有问题)
SELECT dd_id, COUNT(*) AS cnt FROM pos_order_omg_payment WHERE is_active=1 AND pay_status=0
GROUP BY dd_id HAVING COUNT(*)>1;
```

## 2026-08-13 OMG 支付重做 Task 1：支付尝试表

```sql
-- 仅记录脚本，不在 Codex 会话中执行。dd_id 保持 utf8mb4 与 pos_order 一致；网关标识使用 ascii_bin。
CREATE TABLE pos_order_omg_attempt (
  id BIGINT NOT NULL AUTO_INCREMENT,
  dd_id VARCHAR(64) NOT NULL,
  merchant_trade_no VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  store_id BIGINT NOT NULL,
  merchant_id VARCHAR(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  amount INT NOT NULL,
  hash_key_snapshot VARCHAR(64) NOT NULL COMMENT '创建时 HashKey 快照，仅用于该尝试回调验签',
  hash_iv_snapshot VARCHAR(64) NOT NULL COMMENT '创建时 HashIV 快照，仅用于该尝试回调验签',
  attempt_status TINYINT NOT NULL COMMENT '0=CREATED,1=PAID,2=FAILED,3=SUPERSEDED',
  active_dd_id VARCHAR(64)
    GENERATED ALWAYS AS (IF(attempt_status = 0, dd_id, NULL)) VIRTUAL,
  trade_no VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT 'OMG 金流交易编号',
  rtn_code INT DEFAULT NULL COMMENT 'OMG 原始交易状态码',
  rtn_msg VARCHAR(200) DEFAULT NULL COMMENT 'OMG 原始交易讯息',
  payment_type VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT 'OMG 回覆付款方式',
  payment_date DATETIME DEFAULT NULL COMMENT 'OMG 付款时间',
  trade_date DATETIME DEFAULT NULL COMMENT 'OMG 订单成立时间',
  payment_type_charge_fee INT DEFAULT NULL COMMENT 'OMG 回传手续费',
  simulate_paid TINYINT DEFAULT NULL COMMENT '0一般付款,1模拟付款',
  last_notify_time DATETIME DEFAULT NULL COMMENT '最近合法付款结果通知时间',
  next_query_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下一次允许自动查询的时间',
  query_count INT NOT NULL DEFAULT 0 COMMENT '已预留的自动查询次数',
  last_query_time DATETIME DEFAULT NULL COMMENT '最近一次自动查询预留时间',
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_omg_attempt_trade_no (merchant_trade_no),
  UNIQUE KEY uk_omg_attempt_gateway_trade_no (trade_no),
  UNIQUE KEY uk_omg_attempt_active_dd (active_dd_id),
  KEY idx_omg_attempt_dd_time (dd_id, create_time, id),
  KEY idx_omg_attempt_store_time (store_id, create_time, id),
  KEY idx_omg_attempt_query_due (attempt_status, next_query_time, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OMG 支付尝试追加式账本';
```

## 2026-08-12 LINE Pay 直连支付（019-line-pay）

```sql
-- 仅记录脚本，不在 Codex 会话中执行。MySQL 5.7+；凭证使用不可变版本，Secret 按产品决定明文保存。
CREATE TABLE pos_store_line_pay (
  id BIGINT NOT NULL AUTO_INCREMENT,
  store_id BIGINT NOT NULL,
  credential_version INT NOT NULL,
  channel_id VARCHAR(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  channel_secret VARCHAR(255) NOT NULL,
  environment VARCHAR(16) NOT NULL,
  credential_status VARCHAR(32) NOT NULL,
  is_enabled TINYINT NOT NULL DEFAULT 1,
  current_store_id BIGINT NULL,
  verify_return_code VARCHAR(8) NULL,
  verify_return_message VARCHAR(255) NULL,
  verified_time DATETIME NULL,
  create_by VARCHAR(64) NULL,
  update_by VARCHAR(64) NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_line_credential_version (store_id, credential_version),
  UNIQUE KEY uk_line_current_store (current_store_id),
  KEY idx_line_credential_store_time (store_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='LINE Pay门店不可变凭证版本';

CREATE TABLE pos_order_line_payment (
  id BIGINT NOT NULL AUTO_INCREMENT,
  dd_id VARCHAR(64) NOT NULL,
  line_order_id VARCHAR(100) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  transaction_id VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NULL,
  credential_id BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  amount INT NOT NULL,
  currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'TWD',
  payment_url_web VARCHAR(1000) NULL,
  payment_url_app VARCHAR(1000) NULL,
  payment_provider VARCHAR(16) NULL,
  status VARCHAR(40) NOT NULL,
  active_dd_id VARCHAR(64) NULL,
  version BIGINT NOT NULL DEFAULT 0,
  next_reconcile_at DATETIME NULL,
  reconcile_deadline DATETIME NULL,
  reconcile_count INT NOT NULL DEFAULT 0,
  lease_owner VARCHAR(64) NULL,
  lease_until DATETIME NULL,
  status_changed_at DATETIME NOT NULL,
  auth_completed_time DATETIME NULL,
  pay_time DATETIME NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_line_order_id (line_order_id),
  UNIQUE KEY uk_line_transaction_id (transaction_id),
  UNIQUE KEY uk_line_active_dd (active_dd_id),
  KEY idx_line_payment_dd_time (dd_id, create_time, id),
  KEY idx_line_payment_due (status, next_reconcile_at, id),
  KEY idx_line_payment_credential (credential_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='LINE Pay追加式支付尝试';

CREATE TABLE pos_order_line_refund (
  id BIGINT NOT NULL AUTO_INCREMENT,
  payment_id BIGINT NOT NULL,
  dd_id VARCHAR(64) NOT NULL,
  credential_id BIGINT NOT NULL,
  transaction_id VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  refund_transaction_id VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NULL,
  amount INT NOT NULL,
  status VARCHAR(32) NOT NULL,
  source VARCHAR(32) NOT NULL,
  version BIGINT NOT NULL DEFAULT 0,
  next_reconcile_at DATETIME NULL,
  reconcile_deadline DATETIME NULL,
  reconcile_count INT NOT NULL DEFAULT 0,
  lease_owner VARCHAR(64) NULL,
  lease_until DATETIME NULL,
  refund_time DATETIME NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_line_refund_payment (payment_id),
  UNIQUE KEY uk_line_refund_transaction (refund_transaction_id),
  KEY idx_line_refund_due (status, next_reconcile_at, id),
  KEY idx_line_refund_dd_time (dd_id, create_time, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='LINE Pay全额退款状态与事实';

CREATE TABLE payment_gateway_log (
  id BIGINT NOT NULL AUTO_INCREMENT,
  correlation_id VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  payment_id BIGINT NULL,
  refund_id BIGINT NULL,
  credential_id BIGINT NULL,
  store_id BIGINT NULL,
  dd_id VARCHAR(64) NULL,
  gateway_order_id VARCHAR(100) CHARACTER SET ascii COLLATE ascii_bin NULL,
  transaction_id VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NULL,
  action VARCHAR(32) NOT NULL,
  direction VARCHAR(8) NOT NULL,
  source VARCHAR(32) NOT NULL,
  http_status INT NULL,
  return_code VARCHAR(8) NULL,
  return_message VARCHAR(255) NULL,
  success TINYINT NOT NULL,
  duration_ms BIGINT NULL,
  payload LONGTEXT NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_gateway_log_payment (payment_id, create_time),
  KEY idx_gateway_log_refund (refund_id, create_time),
  KEY idx_gateway_log_order (gateway_order_id, create_time),
  KEY idx_gateway_log_transaction (transaction_id, create_time),
  KEY idx_gateway_log_correlation (correlation_id),
  KEY idx_gateway_log_store (store_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='LINE Pay网关追加审计日志';

-- 把门店支付页入口改为公共权限，避免只拥有 LINE 权限时无法进入页面。
UPDATE sys_menu SET perms='chanting:storePayment:list', remark='门店支付配置（OMG/LINE Pay）'
WHERE path='storePayment' AND component='mendian/storePayment/index';
SET @storePaymentMenuId = (SELECT menu_id FROM sys_menu WHERE path='storePayment' AND component='mendian/storePayment/index' LIMIT 1);
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time, remark) VALUES
('LINE Pay列表', @storePaymentMenuId, 11, '#', '', 'F', '0', '0', 'chanting:storeLinePay:list', '#', 'admin', NOW(), ''),
('LINE Pay详情', @storePaymentMenuId, 12, '#', '', 'F', '0', '0', 'chanting:storeLinePay:query', '#', 'admin', NOW(), ''),
('LINE Pay凭证保存', @storePaymentMenuId, 13, '#', '', 'F', '0', '0', 'chanting:storeLinePay:saveCredentials', '#', 'admin', NOW(), ''),
('LINE Pay启停', @storePaymentMenuId, 14, '#', '', 'F', '0', '0', 'chanting:storeLinePay:toggleEnable', '#', 'admin', NOW(), ''),
('LINE Pay主动查询', @storePaymentMenuId, 15, '#', '', 'F', '0', '0', 'system:order:linePaymentReconcile', '#', 'admin', NOW(), ''),
('LINE Pay全额退款', @storePaymentMenuId, 16, '#', '', 'F', '0', '0', 'system:order:lineRefund', '#', 'admin', NOW(), '');
```

## 2026-08-13 OMG rebuild 旧支付与退款表退役

```sql
-- 仅记录脚本，不在 Codex 会话中执行。先删引用支付流水的退款表，再删旧支付流水表。
-- 保留 pos_store_omg（门店凭证）、pos_order_omg_attempt（rebuild 支付尝试）和 ipn_log（回调原文）。
DROP TABLE IF EXISTS pos_order_omg_refund;
DROP TABLE IF EXISTS pos_order_omg_payment;
```

## 2026-08-18 LINE Pay 商家扫码 Offline 模式（019-line-pay）

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；Codex 不直接执行数据库变更。
ALTER TABLE pos_order
  ADD COLUMN order_source VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin
  NOT NULL DEFAULT 'USER' COMMENT '订单来源:USER/MERCHANT' AFTER pay_type;

ALTER TABLE pos_order_line_payment
  ADD COLUMN captured_amount INT NULL COMMENT 'LINE实际扣款金额' AFTER amount,
  ADD COLUMN payment_mode VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin
  NOT NULL DEFAULT 'ONLINE' COMMENT 'LINE支付模式:ONLINE/OFFLINE' AFTER currency,
  ADD KEY idx_line_payment_mode_due (payment_mode, status, next_reconcile_at, id);
```

## 2026-08-25 商家认证证件材料 + 门店菜单照片字段

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；Codex 不直接执行数据库变更。
-- 商家认证新增 4 个证件材料字段（图片URL，多张逗号分隔；營業登記證/食品業者/UBO 为"如有"可选）
-- 注：用 TEXT 类型；info_user 列多，VARCHAR(2000) 触发 MySQL 1118 行大小超限（TEXT 不计入行大小）
ALTER TABLE info_user
  ADD COLUMN business_license TEXT NULL COMMENT '營業登記證證明（图片URL，多张逗号分隔，如有）',
  ADD COLUMN directors_screenshot TEXT NULL COMMENT '董監事資料查詢截圖（图片URL，多张逗号分隔）',
  ADD COLUMN food_permit TEXT NULL COMMENT '食品業者登錄字號證明（图片URL，如有）',
  ADD COLUMN ubo_statement TEXT NULL COMMENT '實質受益人聲明書UBO（图片URL，如有）';

-- 门店新增菜单照片字段（图片URL，多张逗号分隔）
ALTER TABLE pos_store
  ADD COLUMN menu_photo TEXT NULL COMMENT '菜單照片（图片URL，多张逗号分隔）' AFTER logo;
```

## 2026-08-25 骑手申请资料新增字段（需求602：骑手端申请材料与后台审核字段对齐）

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；不直接执行数据库变更。
-- 骑手申请资料新增4字段：预上线城市/出生日期/强制险或电子保险卡/良民证（可后补）；大头照当时复用 avatar，后续已改为独立字段。
ALTER TABLE info_user
  ADD COLUMN pre_launch_city VARCHAR(100) DEFAULT NULL COMMENT '预上线城市（骑手申请时选择）' AFTER vehicle_type,
  ADD COLUMN birthday VARCHAR(20) DEFAULT NULL COMMENT '出生日期（yyyy-MM-dd）' AFTER pre_launch_city,
-- 注：两个照片字段用 TEXT，与上条同因——info_user 列多，VARCHAR(2000) 会触发 MySQL 1118 行大小超限（TEXT 不计入行大小）
  ADD COLUMN insurance_photo TEXT NULL COMMENT '强制险或电子保险卡（图片URL，多张逗号分隔）' AFTER birthday,
  ADD COLUMN police_certificate TEXT NULL COMMENT '良民证（图片URL，可后补）' AFTER insurance_photo;
```

## 2026-08-26 骑手审核大头照独立字段

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；不直接执行数据库变更。
-- 大头照用于骑手资料审核，原 avatar 字段继续作为用户头像使用。
ALTER TABLE info_user
  ADD COLUMN rider_head_photo TEXT NULL COMMENT '骑手审核大头照（图片URL）' AFTER vehicle_type;
```

## 2026-08-27 用户审核不通过原因

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；不直接执行数据库变更。
ALTER TABLE info_user
  ADD COLUMN audit_reject_reason TEXT NULL COMMENT '用户审核不通过原因' AFTER audit_status;

-- 用户审核状态新增 2=审核不通过；重复执行时不会重复插入同一字典值。
INSERT INTO sys_dict_data
  (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '审核不通过', '2', 'sys_user_audit', '', 'danger', 'N', '0', 'admin', NOW(), '用户审核不通过'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_dict_data WHERE dict_type = 'sys_user_audit' AND dict_value = '2'
);
```

## 2026-08-28 商家门店分管账号

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；Codex 不直接执行数据库变更。
ALTER TABLE info_user
  ADD COLUMN merchant_owner_id BIGINT NULL COMMENT '分管账号所属商家主账号ID' AFTER user_type,
  ADD COLUMN subaccount_status CHAR(1) NULL COMMENT '主账号控制状态：0启用 1停用' AFTER status,
  ADD COLUMN last_login_at DATETIME NULL COMMENT '最近一次商家端成功登录时间' AFTER subaccount_status,
  ADD KEY idx_info_user_merchant_owner (merchant_owner_id, user_type, del_flag);

CREATE TABLE merchant_subaccount_store (
  id BIGINT NOT NULL AUTO_INCREMENT,
  subaccount_user_id BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_subaccount_store (subaccount_user_id, store_id),
  KEY idx_subaccount_store_store (store_id, subaccount_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商家分管账号门店授权';
```

## 2026-08-31 闪送配送服务（024-flash-delivery）

```sql
-- 仅记录迁移脚本，由开发者确认环境后手动执行；Codex 不直接执行数据库变更。
-- 运维另行在字典 sys_google_routes_key 配置仅供后端使用的 Google Routes API 密钥；
-- 不得复用会被现有匿名接口返回的 sys_googlemap_key，且不得把真实密钥写入本迁移文件。
ALTER TABLE info_address
  ADD COLUMN is_top TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否置顶' AFTER annexes,
  ADD COLUMN top_time DATETIME NULL COMMENT '置顶时间' AFTER is_top,
  ADD KEY idx_info_address_user_top (user_id, is_top, top_time, id);

CREATE TABLE flash_delivery_pricing (
  id BIGINT NOT NULL AUTO_INCREMENT,
  service_type VARCHAR(20) NOT NULL,
  start_price DECIMAL(10,2) NOT NULL,
  start_distance_meters INT NOT NULL,
  per_km_price DECIMAL(10,2) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  config_version INT NOT NULL DEFAULT 1,
  updated_by BIGINT NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_flash_pricing_service_type (service_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送计价配置';

CREATE TABLE flash_delivery_order (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  client_request_id VARCHAR(64) NOT NULL,
  user_id BIGINT NOT NULL,
  rider_id BIGINT NULL,
  service_type VARCHAR(20) NOT NULL,
  status VARCHAR(24) NOT NULL,
  pickup_name VARCHAR(64) NOT NULL,
  pickup_phone VARCHAR(32) NOT NULL,
  pickup_address VARCHAR(255) NOT NULL,
  pickup_address_detail VARCHAR(255) NULL,
  pickup_longitude DECIMAL(10,7) NOT NULL,
  pickup_latitude DECIMAL(10,7) NOT NULL,
  delivery_name VARCHAR(64) NOT NULL,
  delivery_phone VARCHAR(32) NOT NULL,
  delivery_address VARCHAR(255) NOT NULL,
  delivery_address_detail VARCHAR(255) NULL,
  delivery_longitude DECIMAL(10,7) NOT NULL,
  delivery_latitude DECIMAL(10,7) NOT NULL,
  distance_meters INT NOT NULL,
  distance_source VARCHAR(20) NOT NULL,
  estimated_duration_seconds INT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'TWD',
  pricing_version INT NOT NULL,
  start_price DECIMAL(10,2) NOT NULL,
  start_distance_meters INT NOT NULL,
  per_km_price DECIMAL(10,2) NOT NULL,
  user_note VARCHAR(500) NULL,
  version INT NOT NULL DEFAULT 0,
  accepted_at DATETIME NULL,
  picked_up_at DATETIME NULL,
  delivered_at DATETIME NULL,
  completed_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  cancel_reason VARCHAR(500) NULL,
  cancel_operator_type VARCHAR(20) NULL,
  cancel_operator_id BIGINT NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_flash_order_no (order_no),
  UNIQUE KEY uk_flash_order_request (user_id, client_request_id),
  KEY idx_flash_available (status, service_type, create_time),
  KEY idx_flash_user (user_id, create_time),
  KEY idx_flash_rider (rider_id, status, create_time),
  KEY idx_flash_auto_complete (status, delivered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送配送订单';

CREATE TABLE flash_delivery_order_image (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  proof_type VARCHAR(20) NOT NULL,
  image_url VARCHAR(1000) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  operator_id BIGINT NOT NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_flash_image_order (order_id, proof_type, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送取送凭证';

CREATE TABLE flash_delivery_order_log (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  from_status VARCHAR(24) NULL,
  to_status VARCHAR(24) NOT NULL,
  operator_type VARCHAR(20) NOT NULL,
  operator_id BIGINT NULL,
  reason VARCHAR(500) NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_flash_log_order (order_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送状态审计日志';

-- 初始值仅用于系统启用前的后台调整：3 公里起步，普通 9.90、加急 12.50。
INSERT INTO flash_delivery_pricing
  (service_type, start_price, start_distance_meters, per_km_price, enabled, config_version, create_time, update_time)
VALUES
  ('HELP_SEND', 9.90, 3000, 3.00, 1, 1, NOW(), NOW()),
  ('HELP_PICKUP', 9.90, 3000, 3.00, 1, 1, NOW(), NOW()),
  ('URGENT', 12.50, 3000, 4.00, 1, 1, NOW(), NOW());

-- 平台功能权限；按需挂到实际闪送菜单并分配给管理员角色。
INSERT INTO sys_menu (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送计价查看',0,1,'#','','F','1','0','flash:pricing:list','#','admin',NOW(),'闪送后台权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:pricing:list');
INSERT INTO sys_menu (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送计价修改',0,2,'#','','F','1','0','flash:pricing:edit','#','admin',NOW(),'闪送后台权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:pricing:edit');
INSERT INTO sys_menu (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送订单查看',0,3,'#','','F','1','0','flash:order:list','#','admin',NOW(),'闪送后台权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:list');
INSERT INTO sys_menu (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送订单详情',0,4,'#','','F','1','0','flash:order:query','#','admin',NOW(),'闪送后台权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:query');
INSERT INTO sys_menu (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送订单取消',0,5,'#','','F','1','0','flash:order:cancel','#','admin',NOW(),'闪送后台权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:cancel');
INSERT INTO sys_menu (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送订单完成',0,6,'#','','F','1','0','flash:order:complete','#','admin',NOW(),'闪送后台权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:complete');
```

## 2026-08-31 闪送补充原型增量

> 用途：增加包裹重量档、预约配送、交付 PIN、寄件凭证和费用明细。以下 SQL 由开发者在已执行初版闪送建表脚本的环境手动执行。

```sql
ALTER TABLE flash_delivery_pricing
  ADD COLUMN minimum_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER start_price,
  ADD COLUMN per_minute_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER per_km_price,
  ADD COLUMN peak_multiplier DECIMAL(6,2) NOT NULL DEFAULT 1.00 AFTER per_minute_price;

UPDATE flash_delivery_pricing
SET minimum_price = start_price
WHERE minimum_price = 0.00;

ALTER TABLE flash_delivery_order
  ADD COLUMN package_type VARCHAR(32) NOT NULL DEFAULT 'OTHER' AFTER status,
  ADD COLUMN package_size VARCHAR(16) NOT NULL DEFAULT 'SMALL' AFTER package_type,
  ADD COLUMN delivery_mode VARCHAR(16) NOT NULL DEFAULT 'NOW' AFTER package_size,
  ADD COLUMN scheduled_pickup_start_at DATETIME NULL AFTER delivery_mode,
  ADD COLUMN scheduled_pickup_end_at DATETIME NULL AFTER scheduled_pickup_start_at,
  ADD COLUMN pin_required TINYINT(1) NOT NULL DEFAULT 0 AFTER scheduled_pickup_end_at,
  ADD COLUMN delivery_pin_code CHAR(4) NULL AFTER pin_required,
  ADD COLUMN pickup_city VARCHAR(64) NULL AFTER pickup_address_detail,
  ADD COLUMN pickup_area VARCHAR(64) NULL AFTER pickup_city,
  ADD COLUMN pickup_handoff_method VARCHAR(40) NULL AFTER pickup_area,
  ADD COLUMN delivery_city VARCHAR(64) NULL AFTER delivery_address_detail,
  ADD COLUMN delivery_area VARCHAR(64) NULL AFTER delivery_city,
  ADD COLUMN delivery_handoff_method VARCHAR(40) NULL AFTER delivery_area,
  ADD COLUMN minimum_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER start_price,
  ADD COLUMN per_minute_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER per_km_price,
  ADD COLUMN peak_multiplier DECIMAL(6,2) NOT NULL DEFAULT 1.00 AFTER per_minute_price,
  ADD COLUMN distance_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER peak_multiplier,
  ADD COLUMN duration_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER distance_fee,
  ADD COLUMN peak_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER duration_fee,
  DROP INDEX idx_flash_available,
  ADD KEY idx_flash_available
    (status, delivery_mode, scheduled_pickup_start_at, service_type, create_time);

UPDATE flash_delivery_order
SET minimum_price = start_price
WHERE minimum_price = 0.00;

ALTER TABLE flash_delivery_order_image
  ADD COLUMN operator_type VARCHAR(20) NOT NULL DEFAULT 'RIDER' AFTER sort_order;
```

## 2026-09-01 闪送平台管理前端菜单（024-flash-delivery US6）

> 用途：增加“闪送管理”父菜单、价格配置和订单管理页面，并把 2026-08-31 已记录的六项功能权限归入对应页面。只记录脚本，由开发者手动执行。

```sql
-- 父菜单。
INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送管理',0,55,'flashDelivery',NULL,'M','0','0','','shopping','admin',NOW(),'闪送平台管理入口'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE path='flashDelivery' AND menu_type='M'
);
SET @flashRootMenuId = (
  SELECT menu_id FROM sys_menu WHERE path='flashDelivery' AND menu_type='M' ORDER BY menu_id LIMIT 1
);

-- 价格配置页面；若旧权限行已经存在则保留 menu_id 并转换为页面菜单。
INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '价格配置',@flashRootMenuId,1,'pricing','flashDelivery/pricing/index','C','0','0','flash:pricing:list','money','admin',NOW(),'闪送价格配置页面'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:pricing:list');
UPDATE sys_menu
SET menu_name='价格配置',parent_id=@flashRootMenuId,order_num=1,path='pricing',
    component='flashDelivery/pricing/index',menu_type='C',visible='0',status='0',icon='money',
    remark='闪送价格配置页面'
WHERE perms='flash:pricing:list';
SET @flashPricingMenuId = (
  SELECT menu_id FROM sys_menu WHERE perms='flash:pricing:list' ORDER BY menu_id LIMIT 1
);

INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '修改闪送价格',@flashPricingMenuId,1,'#','', 'F','0','0','flash:pricing:edit','#','admin',NOW(),'闪送价格修改权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:pricing:edit');
UPDATE sys_menu
SET menu_name='修改闪送价格',parent_id=@flashPricingMenuId,order_num=1,path='#',component='',
    menu_type='F',visible='0',status='0',icon='#',remark='闪送价格修改权限'
WHERE perms='flash:pricing:edit';

-- 闪送订单页面及详情、取消、完成按钮权限。
INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '闪送订单',@flashRootMenuId,2,'orders','flashDelivery/orders/index','C','0','0','flash:order:list','list','admin',NOW(),'闪送订单管理页面'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:list');
UPDATE sys_menu
SET menu_name='闪送订单',parent_id=@flashRootMenuId,order_num=2,path='orders',
    component='flashDelivery/orders/index',menu_type='C',visible='0',status='0',icon='list',
    remark='闪送订单管理页面'
WHERE perms='flash:order:list';
SET @flashOrderMenuId = (
  SELECT menu_id FROM sys_menu WHERE perms='flash:order:list' ORDER BY menu_id LIMIT 1
);

INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '查看闪送订单',@flashOrderMenuId,1,'#','', 'F','0','0','flash:order:query','#','admin',NOW(),'闪送订单详情权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:query');
INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '取消闪送订单',@flashOrderMenuId,2,'#','', 'F','0','0','flash:order:cancel','#','admin',NOW(),'闪送订单取消权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:cancel');
INSERT INTO sys_menu
  (menu_name,parent_id,order_num,path,component,menu_type,visible,status,perms,icon,create_by,create_time,remark)
SELECT '完成闪送订单',@flashOrderMenuId,3,'#','', 'F','0','0','flash:order:complete','#','admin',NOW(),'闪送订单完成权限'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms='flash:order:complete');

UPDATE sys_menu
SET parent_id=@flashOrderMenuId,path='#',component='',menu_type='F',visible='0',status='0',icon='#'
WHERE perms IN ('flash:order:query','flash:order:cancel','flash:order:complete');
UPDATE sys_menu SET menu_name='查看闪送订单',order_num=1,remark='闪送订单详情权限' WHERE perms='flash:order:query';
UPDATE sys_menu SET menu_name='取消闪送订单',order_num=2,remark='闪送订单取消权限' WHERE perms='flash:order:cancel';
UPDATE sys_menu SET menu_name='完成闪送订单',order_num=3,remark='闪送订单完成权限' WHERE perms='flash:order:complete';

-- 已经拥有任一闪送页面权限的角色补挂父菜单；未授权角色仍由管理员在角色管理中分配。
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT DISTINCT assigned.role_id, @flashRootMenuId
FROM sys_role_menu assigned
JOIN sys_menu child ON child.menu_id=assigned.menu_id
WHERE child.perms IN ('flash:pricing:list','flash:order:list')
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu existing
    WHERE existing.role_id=assigned.role_id AND existing.menu_id=@flashRootMenuId
  );
```

## 2026-09-01 闪送时段运价与整数新台币计价

> 用途：移除时长费、尖峰倍率、最低价和启停状态，改为按服务类型配置多个不重叠时段，并按外卖订单的公里运费规则计算整数新台币。当前处于开发测试阶段，不保留闪送历史订单和旧运价数据。只记录脚本，由开发者手动执行。

```sql
-- 旧闪送表可能处于初版或补充原型版。开发测试数据无需保留，直接重建为最终结构，
-- 避免因 minimum_price 等旧增量尚未执行而再次出现 Unknown column。
DROP TABLE IF EXISTS flash_delivery_order_image;
DROP TABLE IF EXISTS flash_delivery_order_log;
DROP TABLE IF EXISTS flash_delivery_order;
DROP TABLE IF EXISTS flash_delivery_pricing;
DROP TABLE IF EXISTS flash_delivery_pricing_lock;

CREATE TABLE flash_delivery_pricing_lock (
  service_type VARCHAR(20) NOT NULL,
  PRIMARY KEY (service_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送运价服务类型并发锁';

INSERT INTO flash_delivery_pricing_lock (service_type)
VALUES ('HELP_SEND'), ('HELP_PICKUP'), ('URGENT');

CREATE TABLE flash_delivery_pricing (
  id BIGINT NOT NULL AUTO_INCREMENT,
  service_type VARCHAR(20) NOT NULL,
  start_time CHAR(5) NOT NULL,
  end_time CHAR(5) NOT NULL,
  starting_distance DECIMAL(8,2) NOT NULL,
  starting_fare BIGINT NOT NULL,
  distance DECIMAL(8,2) NOT NULL,
  freight BIGINT NOT NULL,
  config_version INT NOT NULL DEFAULT 1,
  updated_by BIGINT NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_flash_pricing_period (service_type, start_time, end_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送时段运价配置';

CREATE TABLE flash_delivery_order (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  client_request_id VARCHAR(64) NOT NULL,
  user_id BIGINT NOT NULL,
  rider_id BIGINT NULL,
  service_type VARCHAR(20) NOT NULL,
  status VARCHAR(24) NOT NULL,
  package_type VARCHAR(32) NOT NULL DEFAULT 'OTHER',
  package_size VARCHAR(16) NOT NULL DEFAULT 'SMALL',
  delivery_mode VARCHAR(16) NOT NULL DEFAULT 'NOW',
  scheduled_pickup_start_at DATETIME NULL,
  scheduled_pickup_end_at DATETIME NULL,
  pin_required TINYINT(1) NOT NULL DEFAULT 0,
  delivery_pin_code CHAR(4) NULL,
  pickup_name VARCHAR(64) NOT NULL,
  pickup_phone VARCHAR(32) NOT NULL,
  pickup_address VARCHAR(255) NOT NULL,
  pickup_address_detail VARCHAR(255) NULL,
  pickup_city VARCHAR(64) NULL,
  pickup_area VARCHAR(64) NULL,
  pickup_handoff_method VARCHAR(40) NULL,
  pickup_longitude DECIMAL(10,7) NOT NULL,
  pickup_latitude DECIMAL(10,7) NOT NULL,
  delivery_name VARCHAR(64) NOT NULL,
  delivery_phone VARCHAR(32) NOT NULL,
  delivery_address VARCHAR(255) NOT NULL,
  delivery_address_detail VARCHAR(255) NULL,
  delivery_city VARCHAR(64) NULL,
  delivery_area VARCHAR(64) NULL,
  delivery_handoff_method VARCHAR(40) NULL,
  delivery_longitude DECIMAL(10,7) NOT NULL,
  delivery_latitude DECIMAL(10,7) NOT NULL,
  distance_meters INT NOT NULL,
  distance_source VARCHAR(20) NOT NULL,
  estimated_duration_seconds INT NULL,
  amount BIGINT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'TWD',
  pricing_id BIGINT NOT NULL,
  pricing_version INT NOT NULL,
  pricing_start_time CHAR(5) NOT NULL,
  pricing_end_time CHAR(5) NOT NULL,
  starting_distance DECIMAL(8,2) NOT NULL,
  starting_fare BIGINT NOT NULL,
  distance DECIMAL(8,2) NOT NULL,
  freight BIGINT NOT NULL,
  distance_fee BIGINT NOT NULL,
  user_note VARCHAR(500) NULL,
  version INT NOT NULL DEFAULT 0,
  accepted_at DATETIME NULL,
  picked_up_at DATETIME NULL,
  delivered_at DATETIME NULL,
  completed_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  cancel_reason VARCHAR(500) NULL,
  cancel_operator_type VARCHAR(20) NULL,
  cancel_operator_id BIGINT NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_flash_order_no (order_no),
  UNIQUE KEY uk_flash_order_request (user_id, client_request_id),
  KEY idx_flash_available (status, delivery_mode, scheduled_pickup_start_at, service_type, create_time),
  KEY idx_flash_user (user_id, create_time),
  KEY idx_flash_rider (rider_id, status, create_time),
  KEY idx_flash_auto_complete (status, delivered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送配送订单';

CREATE TABLE flash_delivery_order_image (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  proof_type VARCHAR(20) NOT NULL,
  image_url VARCHAR(1000) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  operator_type VARCHAR(20) NOT NULL DEFAULT 'RIDER',
  operator_id BIGINT NOT NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_flash_image_order (order_id, proof_type, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送取送凭证';

CREATE TABLE flash_delivery_order_log (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  from_status VARCHAR(24) NULL,
  to_status VARCHAR(24) NOT NULL,
  operator_type VARCHAR(20) NOT NULL,
  operator_id BIGINT NULL,
  reason VARCHAR(500) NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_flash_log_order (order_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送状态审计日志';
```

## 2026-09-02 闪送自动完成定时任务调度（024-flash-delivery）

> 用途：注册“送达满 24 小时未签收自动完成”定时任务。此前只交付了 `flashDeliveryAutoCompleteTask` Bean，缺少 `sys_job` 配置，任务不会被执行。只记录脚本，由开发者手动执行；若依启动时会按 `status='0'` 自动装载调度。

```sql
INSERT INTO sys_job
  (job_name, job_group, invoke_target, cron_expression, misfire_policy, concurrent, status, create_by, create_time, remark)
SELECT '闪送订单自动完成', 'DEFAULT', 'flashDeliveryAutoCompleteTask.completeDeliveredOrders()',
       '0 0/30 * * * ?', '3', '1', '0', 'admin', NOW(), '送达满24小时未签收的闪送订单自动完成'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_job WHERE invoke_target = 'flashDeliveryAutoCompleteTask.completeDeliveredOrders()'
);
```

## 2026-09-02 骑手配送类型（025-rider-delivery-type）

> 用途：骑手审核资料增加配送类型多选（美食外送 FOOD、闪送 FLASH），派单按骑手选择的配送类型过滤。只记录脚本，由开发者手动执行。

```sql
ALTER TABLE info_user
  ADD COLUMN delivery_type VARCHAR(64) NULL COMMENT '骑手配送类型：FOOD美食外送、FLASH闪送，逗号分隔多选；空视为两者兼容' AFTER vehicle_type;

-- 存量骑手回填为同时兼容两类，保持现有派单行为不变；应用层对空值同样按兼容处理。
UPDATE info_user
SET delivery_type = 'FOOD,FLASH'
WHERE user_type = '2'
  AND del_flag = '0'
  AND delivery_type IS NULL;
```

```sql
-- 2026-09-02 优惠幅度限制平台开关（防止恶意竞争，specs/008-promotion-coupon Phase 5.10）
-- enabled=false 时所有新增逻辑不生效、行为与现状完全一致；如需上线后先关闭观察，把 'true' 改为 'false' 再执行（或在后台 参数管理 里改）
INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark)
VALUES ('优惠幅度限制开关', 'promotion.discount.limit.enabled', 'true', 'Y', 'admin', NOW(), '启用后：商家新增/修改促销和优惠券校验优惠上限；算价时订单总优惠不超过限定比例');

-- 优惠上限比例，单位百分比（20 = 20%），调整后立即生效，无需重启
INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark)
VALUES ('优惠幅度限制比例(%)', 'promotion.discount.limit.ratio', '20', 'Y', 'admin', NOW(), '满减/折扣/券单项优惠及订单总优惠的最大比例');
```

```sql
-- ============================================================
-- 2026-09-03 商家审核资料新增银行收款账户三字段（帳戶名稱/銀行名稱/銀行帳號）
-- 用途：商家App提交资料时填写，平台后台「商家审核」页展示与审核；
--      银行名称下拉数据源 = 字典 taiwan_bank_list，App 经 GET /infouser/user/bankList 读取
-- ============================================================
ALTER TABLE info_user
  ADD COLUMN bank_account_name VARCHAR(64) DEFAULT NULL COMMENT '银行账户户名：商家审核资料的收款账户户名（App提交，平台审核）',
  ADD COLUMN bank_name VARCHAR(64) DEFAULT NULL COMMENT '银行名称：商家审核资料「银行名称」下拉所选值，取字典taiwan_bank_list（台湾银行中文名），如國泰世華銀行',
  ADD COLUMN bank_account_no VARCHAR(32) DEFAULT NULL COMMENT '银行账号：商家审核资料的收款银行账号（存账号文本，不做格式强校验，人工审核兜底）';

-- 台湾银行列表字典（商家审核资料「银行名称」下拉数据源；后续增删银行在平台后台 系统管理-字典管理 操作，无需改代码）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
VALUES ('台湾银行列表', 'taiwan_bank_list', '0', 'admin', NOW(), '商家审核资料银行名称下拉；商家App经 /infouser/user/bankList 读取；label与value相同均为银行中文名');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
VALUES (1,'臺灣銀行','臺灣銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (2,'臺灣土地銀行','臺灣土地銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (3,'合作金庫銀行','合作金庫銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (4,'第一銀行','第一銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (5,'華南銀行','華南銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (6,'彰化銀行','彰化銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (7,'兆豐國際商業銀行','兆豐國際商業銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (8,'臺灣中小企業銀行','臺灣中小企業銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (9,'國泰世華銀行','國泰世華銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (10,'中國信託銀行','中國信託銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (11,'台北富邦銀行','台北富邦銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (12,'玉山銀行','玉山銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (13,'台新銀行','台新銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (14,'永豐銀行','永豐銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (15,'上海商業儲蓄銀行','上海商業儲蓄銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (16,'聯邦銀行','聯邦銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (17,'遠東國際商業銀行','遠東國際商業銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (18,'元大銀行','元大銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (19,'新光銀行','新光銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (20,'陽信銀行','陽信銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (21,'三信商業銀行','三信商業銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (22,'高雄銀行','高雄銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (23,'王道銀行','王道銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (24,'安泰銀行','安泰銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (25,'日盛銀行','日盛銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (26,'渣打國際商業銀行','渣打國際商業銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (27,'匯豐(台灣)商業銀行','匯豐(台灣)商業銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (28,'星展(台灣)銀行','星展(台灣)銀行','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL),
 (29,'中華郵政(郵局)','中華郵政(郵局)','taiwan_bank_list',NULL,'default','N','0','admin',NOW(),NULL);
```

## 2026-09-04 闪送收件账号绑定（024-flash-delivery）

> 用途：订单创建时按收件手机号唯一匹配已注册普通用户，支持用户端“我收的”列表。只记录脚本，由开发者手动执行；不回填历史订单。

```sql
ALTER TABLE flash_delivery_order
  ADD COLUMN receiver_user_id BIGINT NULL COMMENT '创建时匹配的收件用户ID' AFTER user_id,
  ADD KEY idx_flash_receiver_list (receiver_user_id, create_time);
```

## 2026-09-07 闪送统一运价、加急费用与完整物品信息（024-flash-delivery）

> 用途：帮送和帮取改为共享一套分时段运价；普通/加急改用独立 `delivery_type`，并补齐数量、总重量、规格、基础配送费、加急费和骑手小费快照。脚本只记录，由开发者备份并人工执行。迁移会保留优先级最高的一组旧服务运价（帮送优先），上线前必须在平台核对每个时段的加急比例和最低加急费。

```sql
CREATE TABLE flash_delivery_pricing_backup_20260907 LIKE flash_delivery_pricing;
INSERT INTO flash_delivery_pricing_backup_20260907 SELECT * FROM flash_delivery_pricing;

DELETE pricing
FROM flash_delivery_pricing pricing
JOIN (
  SELECT retained_type.service_type
  FROM (
    SELECT DISTINCT service_type
    FROM flash_delivery_pricing
  ) retained_type
  ORDER BY FIELD(retained_type.service_type, 'HELP_SEND', 'HELP_PICKUP', 'URGENT'), retained_type.service_type
  LIMIT 1
) retained ON retained.service_type <> pricing.service_type;

ALTER TABLE flash_delivery_pricing
  DROP INDEX idx_flash_pricing_period,
  DROP COLUMN service_type,
  ADD COLUMN urgent_rate DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '加急比例，百分比' AFTER freight,
  ADD COLUMN minimum_urgent_fee BIGINT NOT NULL DEFAULT 0 COMMENT '最低加急费，整数TWD' AFTER urgent_rate,
  ADD KEY idx_flash_pricing_period (start_time, end_time);

DROP TABLE IF EXISTS flash_delivery_pricing_lock;
CREATE TABLE flash_delivery_pricing_lock (
  lock_key VARCHAR(20) NOT NULL,
  PRIMARY KEY (lock_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='闪送统一运价并发锁';
INSERT INTO flash_delivery_pricing_lock (lock_key) VALUES ('GLOBAL');

ALTER TABLE flash_delivery_order
  ADD COLUMN delivery_type VARCHAR(16) NOT NULL DEFAULT 'NORMAL' COMMENT '配送等级：NORMAL普通、URGENT加急' AFTER service_type,
  ADD COLUMN quantity INT NOT NULL DEFAULT 1 COMMENT '物品数量' AFTER package_type,
  ADD COLUMN total_weight_kg DECIMAL(6,2) NOT NULL DEFAULT 1.00 COMMENT '物品总重量kg' AFTER quantity,
  ADD COLUMN specification VARCHAR(255) NULL COMMENT '体积或规格说明' AFTER total_weight_kg,
  ADD COLUMN base_delivery_fee BIGINT NOT NULL DEFAULT 0 COMMENT '基础配送费快照' AFTER distance_fee,
  ADD COLUMN urgent_rate DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '加急比例快照' AFTER base_delivery_fee,
  ADD COLUMN minimum_urgent_fee BIGINT NOT NULL DEFAULT 0 COMMENT '最低加急费快照' AFTER urgent_rate,
  ADD COLUMN urgent_fee BIGINT NOT NULL DEFAULT 0 COMMENT '加急费快照' AFTER minimum_urgent_fee,
  ADD COLUMN tip_amount BIGINT NOT NULL DEFAULT 0 COMMENT '骑手小费快照' AFTER urgent_fee;

UPDATE flash_delivery_order
SET delivery_type = 'URGENT', service_type = 'HELP_SEND'
WHERE service_type = 'URGENT';

UPDATE flash_delivery_order
SET base_delivery_fee = starting_fare + distance_fee,
    amount = starting_fare + distance_fee + urgent_fee + tip_amount;

ALTER TABLE flash_delivery_order
  DROP INDEX idx_flash_available,
  ADD KEY idx_flash_available (status, delivery_mode, scheduled_pickup_start_at, delivery_type, create_time);
```
