foodie-server/updatesql/sql.md

数据库变更记录

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

-- 新增 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）

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 新增订单操作日志菜单

-- 新增订单操作日志菜单
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）

-- 促销活动表
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）

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）

-- 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）

-- 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());