升鲜宝生鲜配送供应链管理系统 · 仓储式收银系统（多公司多门店 · POS+会员+钱包+权益+门店WMS+库存成本+离线同步）

-- 升鲜宝 · 仓储式收银系统（多公司多门店 · POS+会员+钱包+权益+门店WMS+库存成本+离线同步）
-- MySQL 8.x / InnoDB / utf8mb4
-- 说明：本文件为“主干可上线版本”，覆盖组织、商品、会员、钱包、权益、POS、门店WMS、库存台账、成本台账、离线同步、配送、订货、对账报表等域。
-- 执行建议：
-- CREATE DATABASE IF NOT EXISTS sxb_pos_wholesale DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

 

--   CREATE DATABASE IF NOT EXISTS sxb_pos_wholesale DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
--   USE sxb_pos_wholesale;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- 0. 组织域 org_*
-- =========================================================
DROP TABLE IF EXISTS org_company;
CREATE TABLE org_company (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_code VARCHAR(32) NOT NULL COMMENT '公司编码',
  company_name VARCHAR(100) NOT NULL COMMENT '公司名称',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  remark VARCHAR(200) DEFAULT NULL COMMENT '备注',
  UNIQUE KEY uk_company_code (company_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='老板/公司（多租户）';

DROP TABLE IF EXISTS org_shop;
CREATE TABLE org_shop (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_code VARCHAR(32) NOT NULL COMMENT '门店编码',
  shop_name VARCHAR(100) NOT NULL COMMENT '门店名称',
  shop_type TINYINT NOT NULL DEFAULT 1 COMMENT '门店类型 1仓储式超市 2便利店…',
  address VARCHAR(200) DEFAULT NULL COMMENT '地址',
  contact_name VARCHAR(50) DEFAULT NULL COMMENT '联系人',
  contact_phone VARCHAR(30) DEFAULT NULL COMMENT '联系电话',
  longitude DECIMAL(18,10) DEFAULT NULL COMMENT '经度',
  latitude DECIMAL(18,10) DEFAULT NULL COMMENT '纬度',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  remark VARCHAR(200) DEFAULT NULL COMMENT '备注',
  UNIQUE KEY uk_company_shop_code (company_id, shop_code),
  KEY idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店';

DROP TABLE IF EXISTS org_shop_user;
CREATE TABLE org_shop_user (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  user_name VARCHAR(60) NOT NULL COMMENT '用户名',
  password_hash VARCHAR(120) NOT NULL COMMENT '密码hash',
  real_name VARCHAR(60) DEFAULT NULL COMMENT '真实姓名',
  phone VARCHAR(30) DEFAULT NULL COMMENT '手机号',
  user_type TINYINT NOT NULL DEFAULT 1 COMMENT '用户类型 1收银员 2店长 3仓管 9管理员',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_username (shop_id, user_name),
  KEY idx_company_shop (company_id, shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店用户/员工';

DROP TABLE IF EXISTS org_role;
CREATE TABLE org_role (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  role_code VARCHAR(60) NOT NULL COMMENT '角色编码',
  role_name VARCHAR(60) NOT NULL COMMENT '角色名称',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_role_code (company_id, role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色（公司维度）';

DROP TABLE IF EXISTS org_user_role;
CREATE TABLE org_user_role (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  role_id BIGINT NOT NULL COMMENT '角色ID',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_user_role (company_id, user_id, role_id),
  KEY idx_user (user_id),
  KEY idx_role (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关系';

DROP TABLE IF EXISTS org_shop_device;
CREATE TABLE org_shop_device (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  device_id VARCHAR(64) NOT NULL COMMENT '设备ID（POS-门店-序号）',
  device_type TINYINT NOT NULL DEFAULT 1 COMMENT '设备类型 1收银机 2PDA 3自助机',
  device_name VARCHAR(80) DEFAULT NULL COMMENT '设备名称',
  secret_key VARCHAR(120) DEFAULT NULL COMMENT '设备密钥（签名/鉴权）',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_device (shop_id, device_id),
  KEY idx_company_shop (company_id, shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店设备（收银机/离线同步主体）';

-- =========================================================
-- 1. 门店商品中心 sh_*（离线商品关键）
-- =========================================================
DROP TABLE IF EXISTS sh_good_category;
CREATE TABLE sh_good_category (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  parent_id BIGINT NOT NULL DEFAULT 0 COMMENT '父级ID',
  category_code VARCHAR(50) DEFAULT NULL COMMENT '分类编码',
  category_name VARCHAR(80) NOT NULL COMMENT '分类名称',
  sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_parent_name (shop_id, parent_id, category_name),
  KEY idx_shop_parent (shop_id, parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店商品分类（树）';

DROP TABLE IF EXISTS sh_good_unit;
CREATE TABLE sh_good_unit (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  unit_name VARCHAR(30) NOT NULL COMMENT '单位名称',
  unit_code VARCHAR(30) DEFAULT NULL COMMENT '单位编码',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_unit_name (company_id, unit_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='单位（公司维度）';

DROP TABLE IF EXISTS sh_good;
CREATE TABLE sh_good (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  goods_code VARCHAR(50) NOT NULL COMMENT '商品编码',
  goods_name VARCHAR(120) NOT NULL COMMENT '商品名称',
  category_id BIGINT DEFAULT NULL COMMENT '分类ID',
  brand_name VARCHAR(60) DEFAULT NULL COMMENT '品牌',
  origin_place VARCHAR(80) DEFAULT NULL COMMENT '产地',
  goods_attr VARCHAR(60) DEFAULT NULL COMMENT '属性（生鲜/标品等）',
  shelf_life_days INT DEFAULT NULL COMMENT '保质期天数（可选）',
  source_type TINYINT NOT NULL DEFAULT 1 COMMENT '来源 1门店自建 2平台继承',
  source_goods_id BIGINT DEFAULT NULL COMMENT '平台商品ID（继承时）',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  remark VARCHAR(200) DEFAULT NULL COMMENT '备注',
  UNIQUE KEY uk_company_goods_code (company_id, goods_code),
  KEY idx_company_category (company_id, category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店商品主档';

DROP TABLE IF EXISTS sh_good_sku;
CREATE TABLE sh_good_sku (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  goods_id BIGINT NOT NULL COMMENT '商品ID',
  sku_code VARCHAR(50) DEFAULT NULL COMMENT '规格编码',
  sku_name VARCHAR(80) NOT NULL COMMENT '规格名称',
  bar_code VARCHAR(64) DEFAULT NULL COMMENT '默认条码（可选）',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_goods_sku_name (goods_id, sku_name),
  KEY idx_goods (goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店商品规格（SKU）';

DROP TABLE IF EXISTS sh_good_sku_unit;
CREATE TABLE sh_good_sku_unit (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  goods_id BIGINT NOT NULL COMMENT '商品ID',
  sku_id BIGINT NOT NULL COMMENT '规格ID',
  unit_id BIGINT NOT NULL COMMENT '单位ID',
  sku_code VARCHAR(50) DEFAULT NULL COMMENT 'SKU编码',
  bar_code VARCHAR(64) DEFAULT NULL COMMENT '条码（离线收银查码关键）',
  unit_ratio DECIMAL(18,6) NOT NULL DEFAULT 1 COMMENT '换算比例',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_sku_unit (shop_id, sku_id, unit_id),
  KEY idx_shop_barcode (shop_id, bar_code),
  KEY idx_shop_goods (shop_id, goods_id),
  KEY idx_shop_sku (shop_id, sku_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店SKU+单位';

DROP TABLE IF EXISTS sh_good_price;
CREATE TABLE sh_good_price (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  sku_unit_id BIGINT NOT NULL COMMENT 'SKU单位ID',
  sale_price DECIMAL(18,6) NOT NULL COMMENT '销售价',
  price_version VARCHAR(32) NOT NULL COMMENT '价格版本',
  protect_start BIGINT DEFAULT NULL COMMENT '保护期开始(毫秒)',
  protect_end BIGINT DEFAULT NULL COMMENT '保护期结束(毫秒)',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_sku_unit_version (shop_id, sku_unit_id, price_version),
  KEY idx_shop_version (shop_id, price_version),
  KEY idx_shop_sku_unit (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店售价（含版本/价格保护期）';

DROP TABLE IF EXISTS sh_good_media;
CREATE TABLE sh_good_media (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  goods_id BIGINT NOT NULL COMMENT '商品ID',
  media_type TINYINT NOT NULL DEFAULT 1 COMMENT '媒体类型 1图片 2视频 3文件',
  media_url VARCHAR(300) NOT NULL COMMENT '媒体地址',
  sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  KEY idx_goods (goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品媒体';

-- =========================================================
-- 2. 会员域 mem_*
-- =========================================================
DROP TABLE IF EXISTS mem_member;
CREATE TABLE mem_member (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  member_no VARCHAR(32) NOT NULL COMMENT '会员号',
  phone VARCHAR(30) DEFAULT NULL COMMENT '手机号',
  nick_name VARCHAR(60) DEFAULT NULL COMMENT '昵称',
  avatar_url VARCHAR(200) DEFAULT NULL COMMENT '头像',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_member_no (member_no),
  KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台会员';

DROP TABLE IF EXISTS mem_company_member;
CREATE TABLE mem_company_member (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  register_shop_id BIGINT DEFAULT NULL COMMENT '首次归属门店ID',
  register_time BIGINT NOT NULL COMMENT '加入公司时间(毫秒)',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_member (company_id, member_id),
  KEY idx_company (company_id),
  KEY idx_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='公司维度会员关系';

DROP TABLE IF EXISTS mem_level;
CREATE TABLE mem_level (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  level_code VARCHAR(32) NOT NULL COMMENT '等级编码',
  level_name VARCHAR(50) NOT NULL COMMENT '等级名称',
  discount_rate DECIMAL(18,6) NOT NULL DEFAULT 1 COMMENT '折扣系数(0.95等)',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_level_code (company_id, level_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='公司会员等级';

DROP TABLE IF EXISTS mem_member_level;
CREATE TABLE mem_member_level (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  level_id BIGINT NOT NULL COMMENT '等级ID',
  effective_time BIGINT NOT NULL COMMENT '生效时间(毫秒)',
  expire_time BIGINT DEFAULT NULL COMMENT '失效时间(毫秒)',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_company_member_level (company_id, member_id),
  KEY idx_level (level_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员等级归属';

DROP TABLE IF EXISTS mem_member_address;
CREATE TABLE mem_member_address (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  receiver_name VARCHAR(60) NOT NULL COMMENT '收货人',
  receiver_phone VARCHAR(30) NOT NULL COMMENT '手机号',
  province VARCHAR(60) DEFAULT NULL COMMENT '省',
  city VARCHAR(60) DEFAULT NULL COMMENT '市',
  district VARCHAR(60) DEFAULT NULL COMMENT '区',
  address_detail VARCHAR(200) NOT NULL COMMENT '详细地址',
  is_default TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  KEY idx_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员地址';

-- =========================================================
-- 3. 钱包域 wal_*
-- =========================================================
DROP TABLE IF EXISTS wal_account;
CREATE TABLE wal_account (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  balance DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '余额',
  version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_member (company_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包账户（公司维度）';

DROP TABLE IF EXISTS wal_recharge_order;
CREATE TABLE wal_recharge_order (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  recharge_no VARCHAR(64) NOT NULL COMMENT '充值单号',
  recharge_amount DECIMAL(18,6) NOT NULL COMMENT '充值金额',
  pay_state TINYINT NOT NULL DEFAULT 0 COMMENT '支付状态 0待付1成功2失败',
  pay_time BIGINT DEFAULT NULL COMMENT '支付时间(毫秒)',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_company_recharge_no (company_id, recharge_no),
  KEY idx_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值单';

DROP TABLE IF EXISTS wal_trade_flow;
CREATE TABLE wal_trade_flow (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  trade_type TINYINT NOT NULL COMMENT '1充值 2消费 3退款 4冲正',
  amount DECIMAL(18,6) NOT NULL COMMENT '金额',
  source_type VARCHAR(40) NOT NULL COMMENT '来源类型 POS_ORDER/REFUND/RECHARGE',
  source_no VARCHAR(64) NOT NULL COMMENT '来源单号',
  idempotent_key VARCHAR(64) NOT NULL COMMENT '幂等键（建议用eventId）',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_idempotent (company_id, idempotent_key),
  KEY idx_source (company_id, source_type, source_no),
  KEY idx_member (company_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包流水（幂等）';

-- =========================================================
-- 4. 权益域 ben_*（券/卡/积分/抵现）
-- =========================================================
DROP TABLE IF EXISTS ben_rule;
CREATE TABLE ben_rule (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  rule_version VARCHAR(32) NOT NULL COMMENT '规则版本',
  rule_name VARCHAR(80) NOT NULL COMMENT '规则名称',
  rule_json MEDIUMTEXT NOT NULL COMMENT '规则配置JSON',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_company_rule_version (company_id, rule_version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权益规则（版本化）';

DROP TABLE IF EXISTS ben_coupon_template;
CREATE TABLE ben_coupon_template (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  template_code VARCHAR(32) NOT NULL COMMENT '模板编码',
  template_name VARCHAR(80) NOT NULL COMMENT '模板名称',
  coupon_type TINYINT NOT NULL COMMENT '券类型 1满减 2折扣 3立减',
  threshold_amount DECIMAL(18,6) DEFAULT NULL COMMENT '门槛金额',
  discount_amount DECIMAL(18,6) DEFAULT NULL COMMENT '优惠金额',
  discount_rate DECIMAL(18,6) DEFAULT NULL COMMENT '折扣率',
  valid_days INT DEFAULT NULL COMMENT '发放后有效天数',
  start_time BIGINT DEFAULT NULL COMMENT '固定开始时间',
  end_time BIGINT DEFAULT NULL COMMENT '固定结束时间',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_template_code (company_id, template_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券模板';

DROP TABLE IF EXISTS ben_coupon;
CREATE TABLE ben_coupon (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  template_id BIGINT NOT NULL COMMENT '模板ID',
  coupon_code VARCHAR(64) NOT NULL COMMENT '券码',
  state TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未用1已用2已过期3已作废',
  receive_time BIGINT NOT NULL COMMENT '领取时间(毫秒)',
  use_time BIGINT DEFAULT NULL COMMENT '使用时间(毫秒)',
  expire_time BIGINT NOT NULL COMMENT '过期时间(毫秒)',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_company_coupon_code (company_id, coupon_code),
  KEY idx_member_state (company_id, member_id, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券实例（发放到会员）';

DROP TABLE IF EXISTS ben_coupon_use;
CREATE TABLE ben_coupon_use (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  coupon_id BIGINT NOT NULL COMMENT '券ID',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  discount_amount DECIMAL(18,6) NOT NULL COMMENT '优惠金额',
  idempotent_key VARCHAR(64) NOT NULL COMMENT '幂等键（eventId/支付流水号）',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_coupon_order (company_id, coupon_id, order_no),
  UNIQUE KEY uk_coupon_idempotent (company_id, idempotent_key),
  KEY idx_order (company_id, order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券核销记录（幂等）';

DROP TABLE IF EXISTS ben_giftcard;
CREATE TABLE ben_giftcard (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  card_code VARCHAR(64) NOT NULL COMMENT '卡号',
  card_name VARCHAR(80) NOT NULL COMMENT '卡名称',
  card_type TINYINT NOT NULL COMMENT '1储值卡 2礼品卡(绑商品)',
  face_value DECIMAL(18,6) DEFAULT NULL COMMENT '面值金额',
  balance DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '当前余额（储值卡）',
  bind_goods_id BIGINT DEFAULT NULL COMMENT '绑定商品ID（礼品卡）',
  bind_sku_unit_id BIGINT DEFAULT NULL COMMENT '绑定SKU单位ID（礼品卡）',
  bind_quantity DECIMAL(18,6) DEFAULT NULL COMMENT '可兑换数量（礼品卡）',
  state TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未激活1正常2用尽3作废',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_company_card_code (company_id, card_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='储值卡/礼品卡';

DROP TABLE IF EXISTS ben_giftcard_tx;
CREATE TABLE ben_giftcard_tx (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  card_id BIGINT NOT NULL COMMENT '卡ID',
  member_id BIGINT DEFAULT NULL COMMENT '会员ID（可选）',
  tx_type TINYINT NOT NULL COMMENT '1激活 2消费 3退款 4冲正',
  amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '金额变动',
  quantity DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '数量变动（礼品卡）',
  source_type VARCHAR(40) NOT NULL COMMENT 'POS_ORDER/REFUND',
  source_no VARCHAR(64) NOT NULL COMMENT '来源单号',
  idempotent_key VARCHAR(64) NOT NULL COMMENT '幂等键',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_idempotent (company_id, idempotent_key),
  KEY idx_source (company_id, source_type, source_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='储值卡/礼品卡交易流水（幂等）';

DROP TABLE IF EXISTS ben_point_account;
CREATE TABLE ben_point_account (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  points BIGINT NOT NULL DEFAULT 0 COMMENT '积分余额',
  version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_company_member (company_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分账户（公司维度）';

DROP TABLE IF EXISTS ben_point_flow;
CREATE TABLE ben_point_flow (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  member_id BIGINT NOT NULL COMMENT '会员ID',
  flow_type TINYINT NOT NULL COMMENT '1获得 2消耗 3退回 4冲正',
  points BIGINT NOT NULL COMMENT '积分变动',
  source_type VARCHAR(40) NOT NULL COMMENT 'POS_ORDER/REFUND',
  source_no VARCHAR(64) NOT NULL COMMENT '来源单号',
  idempotent_key VARCHAR(64) NOT NULL COMMENT '幂等键',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_idempotent (company_id, idempotent_key),
  KEY idx_member (company_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分流水（幂等）';

DROP TABLE IF EXISTS ben_deduct_rule;
CREATE TABLE ben_deduct_rule (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  rule_version VARCHAR(32) NOT NULL COMMENT '规则版本',
  points_per_yuan INT NOT NULL DEFAULT 100 COMMENT '多少积分抵1元',
  max_deduct_rate DECIMAL(18,6) NOT NULL DEFAULT 1 COMMENT '最大抵扣比例(1=100%)',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_company_version (company_id, rule_version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分抵现规则';

-- =========================================================
-- 5. POS 域 pos_*
-- =========================================================
DROP TABLE IF EXISTS pos_order;
CREATE TABLE pos_order (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  order_source TINYINT NOT NULL COMMENT '订单来源 1POS 2会员商城',
  fulfill_type TINYINT NOT NULL COMMENT '履约方式 1自提 2配送',
  member_id BIGINT DEFAULT NULL COMMENT '会员ID',
  total_amount DECIMAL(18,6) NOT NULL COMMENT '原价合计',
  discount_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '优惠合计',
  payable_amount DECIMAL(18,6) NOT NULL COMMENT '应付金额',
  pay_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '实付金额',
  order_state TINYINT NOT NULL COMMENT '订单状态 0创建 1已支付 2已取消 3已完成 4退款中 5已退款',
  price_version VARCHAR(32) DEFAULT NULL COMMENT '价格版本',
  benefit_version VARCHAR(32) DEFAULT NULL COMMENT '权益版本',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  pay_time BIGINT DEFAULT NULL COMMENT '支付时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_order_no (shop_id, order_no),
  KEY idx_company_shop_time (company_id, shop_id, create_time),
  KEY idx_member (company_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='POS订单';

DROP TABLE IF EXISTS pos_order_item;
CREATE TABLE pos_order_item (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  line_no INT NOT NULL COMMENT '行号',
  sku_unit_id BIGINT NOT NULL COMMENT 'SKU单位ID',
  goods_name VARCHAR(120) NOT NULL COMMENT '商品名称快照',
  bar_code VARCHAR(64) DEFAULT NULL COMMENT '条码快照',
  quantity DECIMAL(18,6) NOT NULL COMMENT '数量',
  sale_price DECIMAL(18,6) NOT NULL COMMENT '成交单价',
  origin_price DECIMAL(18,6) DEFAULT NULL COMMENT '原价单价',
  amount DECIMAL(18,6) NOT NULL COMMENT '成交金额',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_order_line (shop_id, order_no, line_no),
  KEY idx_order (shop_id, order_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='POS订单明细';

DROP TABLE IF EXISTS pos_order_payment;
CREATE TABLE pos_order_payment (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  pay_type VARCHAR(20) NOT NULL COMMENT '支付方式 CASH/WECHAT/ALIPAY/BALANCE/CARD',
  pay_amount DECIMAL(18,6) NOT NULL COMMENT '支付金额',
  pay_ref_no VARCHAR(80) DEFAULT NULL COMMENT '支付参考号/流水号',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  KEY idx_order (shop_id, order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单支付明细';

DROP TABLE IF EXISTS pos_order_benefit;
CREATE TABLE pos_order_benefit (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  benefit_version VARCHAR(32) DEFAULT NULL COMMENT '权益版本',
  member_discount_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '会员折扣金额',
  coupon_discount_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '优惠券金额',
  points_deduct_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '积分抵扣金额',
  giftcard_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '储值卡/礼品卡抵扣金额',
  rounding_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '抹零金额（负数表示减少应付）',
  detail_json MEDIUMTEXT DEFAULT NULL COMMENT '权益明细JSON（券id/积分等）',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_order (shop_id, order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单权益快照（离线/重放锚点）';

DROP TABLE IF EXISTS pos_refund;
CREATE TABLE pos_refund (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  refund_no VARCHAR(64) NOT NULL COMMENT '退款单号',
  order_no VARCHAR(64) NOT NULL COMMENT '原订单号',
  refund_amount DECIMAL(18,6) NOT NULL COMMENT '退款金额',
  refund_state TINYINT NOT NULL COMMENT '状态 0申请 1完成 2失败',
  reason VARCHAR(200) DEFAULT NULL COMMENT '原因',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  confirm_time BIGINT DEFAULT NULL COMMENT '完成时间(毫秒)',
  UNIQUE KEY uk_shop_refund_no (shop_id, refund_no),
  KEY idx_order (shop_id, order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款单';

DROP TABLE IF EXISTS pos_pickup_code;
CREATE TABLE pos_pickup_code (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  pickup_code VARCHAR(6) NOT NULL COMMENT '自提码（6位）',
  expire_time BIGINT NOT NULL COMMENT '过期时间(毫秒)',
  used_flag TINYINT NOT NULL DEFAULT 0 COMMENT '是否已核销 0否1是',
  used_time BIGINT DEFAULT NULL COMMENT '核销时间(毫秒)',
  resend_count INT NOT NULL DEFAULT 0 COMMENT '重发次数',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_shop_order (shop_id, order_no),
  UNIQUE KEY uk_shop_code (shop_id, pickup_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自提码/核销';

DROP TABLE IF EXISTS pos_shift;
CREATE TABLE pos_shift (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  cashier_id BIGINT NOT NULL COMMENT '收银员ID',
  shift_no VARCHAR(64) NOT NULL COMMENT '班次号',
  start_time BIGINT NOT NULL COMMENT '开始时间(毫秒)',
  end_time BIGINT DEFAULT NULL COMMENT '结束时间(毫秒)',
  shift_state TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0进行中 1已交班',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_shop_shift_no (shop_id, shift_no),
  KEY idx_shop_cashier (shop_id, cashier_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收银班次';

DROP TABLE IF EXISTS pos_shift_settlement;
CREATE TABLE pos_shift_settlement (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  shift_no VARCHAR(64) NOT NULL COMMENT '班次号',
  total_order_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '订单总额',
  total_pay_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '实收总额',
  total_refund_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '退款总额',
  cash_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '现金',
  wechat_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '微信',
  alipay_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '支付宝',
  other_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '其他',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_shop_shift (shop_id, shift_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班次交班汇总';

DROP TABLE IF EXISTS pos_day_settlement;
CREATE TABLE pos_day_settlement (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  biz_date INT NOT NULL COMMENT '业务日期yyyyMMdd',
  total_order_count INT NOT NULL DEFAULT 0 COMMENT '订单数',
  total_pay_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '实收',
  total_refund_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '退款',
  net_amount DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '净收=实收-退款',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  UNIQUE KEY uk_shop_date (shop_id, biz_date),
  KEY idx_company_date (company_id, biz_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店日结汇总（POS侧）';

-- =========================================================
-- 6. 门店WMS域 hwms_*（仓库基础资料 + 单据）
-- =========================================================
DROP TABLE IF EXISTS hwms_workhouse;
CREATE TABLE hwms_workhouse (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  workhouse_code VARCHAR(32) NOT NULL COMMENT '仓库编码',
  workhouse_name VARCHAR(80) NOT NULL COMMENT '仓库名称',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_shop_workhouse_code (shop_id, workhouse_code),
  KEY idx_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店仓库';

DROP TABLE IF EXISTS hwms_workhouse_area;
CREATE TABLE hwms_workhouse_area (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  workhouse_id BIGINT NOT NULL COMMENT '仓库ID',
  area_code VARCHAR(32) NOT NULL COMMENT '库区编码',
  area_name VARCHAR(80) NOT NULL COMMENT '库区名称',
  sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_workhouse_area_code (workhouse_id, area_code),
  KEY idx_workhouse (workhouse_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库区';

DROP TABLE IF EXISTS hwms_shelves;
CREATE TABLE hwms_shelves (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  workhouse_id BIGINT NOT NULL COMMENT '仓库ID',
  area_id BIGINT NOT NULL COMMENT '库区ID',
  shelves_code VARCHAR(32) NOT NULL COMMENT '货架编码',
  shelves_name VARCHAR(80) NOT NULL COMMENT '货架名称',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_area_shelves_code (area_id, shelves_code),
  KEY idx_area (area_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='货架';

DROP TABLE IF EXISTS hwms_bin;
CREATE TABLE hwms_bin (
  id BIGINT PRIMARY KEY COMMENT '主键ID',
  company_id BIGINT NOT NULL COMMENT '公司ID',
  shop_id BIGINT NOT NULL COMMENT '门店ID',
  workhouse_id BIGINT NOT NULL COMMENT '仓库ID',
  area_id BIGINT NOT NULL COMMENT '库区ID',
  shelves_id BIGINT DEFAULT NULL COMMENT '货架ID',
  bin_code VARCHAR(32) NOT NULL COMMENT '库位编码',
  bin_name VARCHAR(80) NOT NULL COMMENT '库位名称',
  length_cm DECIMAL(18,6) DEFAULT NULL COMMENT '长(cm)',
  width_cm DECIMAL(18,6) DEFAULT NULL COMMENT '宽(cm)',
  height_cm DECIMAL(18,6) DEFAULT NULL COMMENT '高(cm)',
  max_weight DECIMAL(18,6) DEFAULT NULL COMMENT '最大承重(kg)',
  enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
  del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记 0否1是',
  create_time BIGINT NOT NULL COMMENT '创建时间(毫秒)',
  update_time BIGINT DEFAULT NULL COMMENT '更新时间(毫秒)',
  UNIQUE KEY uk_workhouse_bin_code (workhouse_id, bin_code),
  KEY idx_workhouse (workhouse_id),
  KEY idx_area (area_id),
  KEY idx_shelves (shelves_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库位';

DROP TABLE IF EXISTS hwms_stockin;
CREATE TABLE hwms_stockin (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  workhouse_id BIGINT NOT NULL,
  stockin_no VARCHAR(64) NOT NULL,
  source_type VARCHAR(40) DEFAULT NULL,
  source_no VARCHAR(64) DEFAULT NULL,
  bill_state TINYINT NOT NULL DEFAULT 0,
  audit_state TINYINT NOT NULL DEFAULT 0,
  audit_time BIGINT DEFAULT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  sync_state TINYINT NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_stockin_no (shop_id, stockin_no),
  KEY idx_shop_audit_time (shop_id, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='入库单';

DROP TABLE IF EXISTS hwms_stockin_item;
CREATE TABLE hwms_stockin_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  stockin_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  bin_id BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_stockin_line (shop_id, stockin_no, line_no),
  KEY idx_stockin (shop_id, stockin_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='入库单明细';

DROP TABLE IF EXISTS hwms_stockout;
CREATE TABLE hwms_stockout (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  workhouse_id BIGINT NOT NULL,
  stockout_no VARCHAR(64) NOT NULL,
  source_type VARCHAR(40) DEFAULT NULL,
  source_no VARCHAR(64) DEFAULT NULL,
  bill_state TINYINT NOT NULL DEFAULT 0,
  audit_state TINYINT NOT NULL DEFAULT 0,
  audit_time BIGINT DEFAULT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  sync_state TINYINT NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_stockout_no (shop_id, stockout_no),
  KEY idx_shop_audit_time (shop_id, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='出库单';

DROP TABLE IF EXISTS hwms_stockout_item;
CREATE TABLE hwms_stockout_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  stockout_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  bin_id BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_stockout_line (shop_id, stockout_no, line_no),
  KEY idx_stockout (shop_id, stockout_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='出库单明细';

DROP TABLE IF EXISTS hwms_stocktake;
CREATE TABLE hwms_stocktake (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  workhouse_id BIGINT NOT NULL,
  stocktake_no VARCHAR(64) NOT NULL,
  bill_state TINYINT NOT NULL DEFAULT 0,
  audit_state TINYINT NOT NULL DEFAULT 0,
  audit_time BIGINT DEFAULT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_stocktake_no (shop_id, stocktake_no),
  KEY idx_shop_audit_time (shop_id, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='盘点单';

DROP TABLE IF EXISTS hwms_stocktake_item;
CREATE TABLE hwms_stocktake_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  stocktake_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  book_qty DECIMAL(18,6) NOT NULL,
  actual_qty DECIMAL(18,6) NOT NULL,
  bin_id BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_stocktake_line (shop_id, stocktake_no, line_no),
  KEY idx_stocktake (shop_id, stocktake_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='盘点单明细';

DROP TABLE IF EXISTS hwms_loss;
CREATE TABLE hwms_loss (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  workhouse_id BIGINT NOT NULL,
  loss_no VARCHAR(64) NOT NULL,
  bill_state TINYINT NOT NULL DEFAULT 0,
  audit_state TINYINT NOT NULL DEFAULT 0,
  audit_time BIGINT DEFAULT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_loss_no (shop_id, loss_no),
  KEY idx_shop_audit_time (shop_id, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报损单';

DROP TABLE IF EXISTS hwms_loss_item;
CREATE TABLE hwms_loss_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  loss_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  bin_id BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_loss_line (shop_id, loss_no, line_no),
  KEY idx_loss (shop_id, loss_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报损单明细';

DROP TABLE IF EXISTS hwms_overflow;
CREATE TABLE hwms_overflow (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  workhouse_id BIGINT NOT NULL,
  overflow_no VARCHAR(64) NOT NULL,
  bill_state TINYINT NOT NULL DEFAULT 0,
  audit_state TINYINT NOT NULL DEFAULT 0,
  audit_time BIGINT DEFAULT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_overflow_no (shop_id, overflow_no),
  KEY idx_shop_audit_time (shop_id, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报溢单';

DROP TABLE IF EXISTS hwms_overflow_item;
CREATE TABLE hwms_overflow_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  overflow_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  bin_id BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_overflow_line (shop_id, overflow_no, line_no),
  KEY idx_overflow (shop_id, overflow_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报溢单明细';

DROP TABLE IF EXISTS hwms_move;
CREATE TABLE hwms_move (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  workhouse_id BIGINT NOT NULL,
  move_no VARCHAR(64) NOT NULL,
  bill_state TINYINT NOT NULL DEFAULT 0,
  audit_state TINYINT NOT NULL DEFAULT 0,
  audit_time BIGINT DEFAULT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_move_no (shop_id, move_no),
  KEY idx_shop_audit_time (shop_id, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='移库单';

DROP TABLE IF EXISTS hwms_move_item;
CREATE TABLE hwms_move_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  move_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  from_bin_id BIGINT NOT NULL,
  to_bin_id BIGINT NOT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_move_line (shop_id, move_no, line_no),
  KEY idx_move (shop_id, move_no),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='移库单明细';

DROP TABLE IF EXISTS hwms_bill_log;
CREATE TABLE hwms_bill_log (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  bill_type VARCHAR(40) NOT NULL,
  bill_no VARCHAR(64) NOT NULL,
  operate_type VARCHAR(40) NOT NULL,
  operate_user BIGINT DEFAULT NULL,
  operate_time BIGINT NOT NULL,
  remark VARCHAR(200) DEFAULT NULL,
  KEY idx_bill (shop_id, bill_type, bill_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='HWMS单据日志';

-- =========================================================
-- 7. 门店库存台账 hinv_*
-- =========================================================
DROP TABLE IF EXISTS hinv_inventory;
CREATE TABLE hinv_inventory (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
  update_time BIGINT NOT NULL,
  version INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_sku (company_id, shop_id, sku_unit_id),
  KEY idx_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存结存（SKU单位级）';

DROP TABLE IF EXISTS hinv_inventory_bin;
CREATE TABLE hinv_inventory_bin (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  bin_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
  update_time BIGINT NOT NULL,
  version INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_bin_sku (company_id, shop_id, bin_id, sku_unit_id),
  KEY idx_shop_bin (shop_id, bin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存结存（库位级）';

DROP TABLE IF EXISTS hinv_inventory_flow;
CREATE TABLE hinv_inventory_flow (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  change_qty DECIMAL(18,6) NOT NULL,
  after_qty DECIMAL(18,6) NOT NULL,
  source_type VARCHAR(40) NOT NULL,
  source_no VARCHAR(64) NOT NULL,
  source_line_no INT NOT NULL,
  posting_side TINYINT NOT NULL DEFAULT 0,
  biz_time BIGINT NOT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  reverse_event_id VARCHAR(64) DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_posting (company_id, shop_id, source_type, source_no, source_line_no, posting_side),
  KEY idx_shop_sku_time (shop_id, sku_unit_id, biz_time),
  KEY idx_source (source_type, source_no),
  KEY idx_reverse (reverse_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存流水（唯一键防重复过账）';

-- =========================================================
-- 8. 门店成本台账 hcost_*
-- =========================================================
DROP TABLE IF EXISTS hcost_stock;
CREATE TABLE hcost_stock (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
  amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  update_time BIGINT NOT NULL,
  version INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_sku (company_id, shop_id, sku_unit_id),
  KEY idx_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成本结存（数量+金额）';

DROP TABLE IF EXISTS hcost_flow;
CREATE TABLE hcost_flow (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  change_qty DECIMAL(18,6) NOT NULL,
  cost_amount DECIMAL(18,6) NOT NULL,
  cost_method VARCHAR(20) NOT NULL,
  source_type VARCHAR(40) NOT NULL,
  source_no VARCHAR(64) NOT NULL,
  source_line_no INT NOT NULL,
  posting_side TINYINT NOT NULL DEFAULT 0,
  biz_time BIGINT NOT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  reverse_event_id VARCHAR(64) DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_posting (company_id, shop_id, source_type, source_no, source_line_no, posting_side),
  KEY idx_shop_sku_time (shop_id, sku_unit_id, biz_time),
  KEY idx_source (source_type, source_no),
  KEY idx_reverse (reverse_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成本流水（唯一键防重复过账）';

DROP TABLE IF EXISTS hcost_fifo_layer;
CREATE TABLE hcost_fifo_layer (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  init_qty DECIMAL(18,6) NOT NULL DEFAULT 0,
  remain_qty DECIMAL(18,6) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
  source_type VARCHAR(40) NOT NULL,
  source_no VARCHAR(64) NOT NULL,
  source_line_no INT NOT NULL,
  posting_side TINYINT NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_layer (company_id, shop_id, sku_unit_id, source_type, source_no, source_line_no, posting_side),
  KEY idx_shop_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='FIFO分层（唯一键防重复建层）';

DROP TABLE IF EXISTS hcost_fifo_consume;
CREATE TABLE hcost_fifo_consume (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  source_type VARCHAR(40) NOT NULL,
  source_no VARCHAR(64) NOT NULL,
  source_line_no INT NOT NULL,
  posting_side TINYINT NOT NULL DEFAULT 0,
  layer_id BIGINT NOT NULL,
  consume_qty DECIMAL(18,6) NOT NULL,
  unit_cost DECIMAL(18,6) NOT NULL,
  consume_amount DECIMAL(18,6) NOT NULL,
  reverse_state TINYINT NOT NULL DEFAULT 0,
  reverse_time BIGINT DEFAULT NULL,
  reverse_event_id VARCHAR(64) DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_consume (company_id, shop_id, source_type, source_no, source_line_no, posting_side, layer_id),
  KEY idx_source (company_id, shop_id, source_type, source_no),
  KEY idx_layer (layer_id),
  KEY idx_reverse (reverse_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='FIFO扣层明细（出库回滚锚点）';

-- =========================================================
-- 9. 离线同步域 sync_*
-- =========================================================
DROP TABLE IF EXISTS sync_event_log;
CREATE TABLE sync_event_log (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  device_id VARCHAR(64) NOT NULL,
  event_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  biz_key VARCHAR(80) NOT NULL,
  event_time BIGINT NOT NULL,
  recv_time BIGINT NOT NULL,
  handle_state TINYINT NOT NULL,
  reject_code VARCHAR(64) DEFAULT NULL,
  reject_msg VARCHAR(200) DEFAULT NULL,
  payload_json MEDIUMTEXT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_event (company_id, shop_id, event_id),
  KEY idx_company_time (company_id, recv_time),
  KEY idx_event_type (event_type),
  KEY idx_biz_key (biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='同步事件日志（幂等/审计）';

DROP TABLE IF EXISTS sync_conflict;
CREATE TABLE sync_conflict (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  conflict_id VARCHAR(64) NOT NULL,
  event_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  biz_key VARCHAR(80) NOT NULL,
  reason_code VARCHAR(64) NOT NULL,
  reason_msg VARCHAR(200) NOT NULL,
  state TINYINT NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_conflict (conflict_id),
  KEY idx_event (company_id, shop_id, event_id),
  KEY idx_state (state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='同步冲突处理单';

DROP TABLE IF EXISTS sync_checkpoint;
CREATE TABLE sync_checkpoint (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  device_id VARCHAR(64) NOT NULL,
  last_event_time BIGINT NOT NULL DEFAULT 0,
  last_event_id VARCHAR(64) DEFAULT NULL,
  update_time BIGINT NOT NULL,
  UNIQUE KEY uk_shop_device (shop_id, device_id),
  KEY idx_company_shop (company_id, shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='同步水位（按门店设备）';

-- =========================================================
-- 10. 对账/重放任务 fin_reconcile_*
-- =========================================================
DROP TABLE IF EXISTS fin_reconcile_task;
CREATE TABLE fin_reconcile_task (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  biz_date INT NOT NULL,
  task_type TINYINT NOT NULL COMMENT '1对账 2重放 3对账+重放',
  task_state TINYINT NOT NULL COMMENT '0待执行 1执行中 2成功 3失败',
  fail_msg VARCHAR(200) DEFAULT NULL,
  start_time BIGINT DEFAULT NULL,
  end_time BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_shop_date_type (shop_id, biz_date, task_type),
  KEY idx_company_state (company_id, task_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='云端对账/重放任务表';

DROP TABLE IF EXISTS fin_reconcile_result;
CREATE TABLE fin_reconcile_result (
  id BIGINT PRIMARY KEY,
  task_id BIGINT NOT NULL,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  biz_date INT NOT NULL,
  result_state TINYINT NOT NULL COMMENT '0一致 1不一致 2已修复',
  pos_ok TINYINT NOT NULL DEFAULT 1,
  inv_ok TINYINT NOT NULL DEFAULT 1,
  cost_ok TINYINT NOT NULL DEFAULT 1,
  pos_diff_msg VARCHAR(200) DEFAULT NULL,
  inv_diff_msg VARCHAR(200) DEFAULT NULL,
  cost_diff_msg VARCHAR(200) DEFAULT NULL,
  create_time BIGINT NOT NULL,
  KEY idx_task (task_id),
  KEY idx_shop_date (shop_id, biz_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='云端对账结果主表';

DROP TABLE IF EXISTS fin_reconcile_diff;
CREATE TABLE fin_reconcile_diff (
  id BIGINT PRIMARY KEY,
  task_id BIGINT NOT NULL,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  biz_date INT NOT NULL,
  diff_type TINYINT NOT NULL COMMENT '1POS 2库存 3成本',
  sku_unit_id BIGINT DEFAULT NULL,
  cloud_value DECIMAL(18,6) DEFAULT NULL,
  local_value DECIMAL(18,6) DEFAULT NULL,
  diff_value DECIMAL(18,6) DEFAULT NULL,
  remark VARCHAR(200) DEFAULT NULL,
  create_time BIGINT NOT NULL,
  KEY idx_task_type (task_id, diff_type),
  KEY idx_sku (shop_id, sku_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账差异明细（到SKU）';

-- =========================================================
-- 11. 门店配送域 del_*
-- =========================================================
DROP TABLE IF EXISTS del_task;
CREATE TABLE del_task (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  task_no VARCHAR(64) NOT NULL,
  order_no VARCHAR(64) DEFAULT NULL,
  stockout_no VARCHAR(64) DEFAULT NULL,
  deliver_type TINYINT NOT NULL,
  task_state TINYINT NOT NULL,
  receiver_name VARCHAR(60) DEFAULT NULL,
  receiver_phone VARCHAR(30) DEFAULT NULL,
  address VARCHAR(200) DEFAULT NULL,
  plan_time BIGINT DEFAULT NULL,
  driver_user_id BIGINT DEFAULT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_task_no (shop_id, task_no),
  KEY idx_shop_state (shop_id, task_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店配送任务';

DROP TABLE IF EXISTS del_task_trace;
CREATE TABLE del_task_trace (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  task_no VARCHAR(64) NOT NULL,
  from_state TINYINT DEFAULT NULL,
  to_state TINYINT NOT NULL,
  operate_user BIGINT DEFAULT NULL,
  operate_time BIGINT NOT NULL,
  remark VARCHAR(200) DEFAULT NULL,
  KEY idx_task (shop_id, task_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配送任务状态轨迹';

DROP TABLE IF EXISTS del_sign;
CREATE TABLE del_sign (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  task_no VARCHAR(64) NOT NULL,
  sign_type TINYINT NOT NULL,
  sign_url VARCHAR(300) NOT NULL,
  sign_time BIGINT NOT NULL,
  create_time BIGINT NOT NULL,
  KEY idx_task (shop_id, task_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='签收凭证';

-- =========================================================
-- 12. 门店订货 sh_purchase_*
-- =========================================================
DROP TABLE IF EXISTS sh_purchase_order;
CREATE TABLE sh_purchase_order (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  purchase_no VARCHAR(64) NOT NULL,
  supplier_company_id BIGINT DEFAULT NULL,
  order_state TINYINT NOT NULL DEFAULT 0,
  total_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  update_time BIGINT DEFAULT NULL,
  UNIQUE KEY uk_shop_purchase_no (shop_id, purchase_no),
  KEY idx_company_shop (company_id, shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店订货主表';

DROP TABLE IF EXISTS sh_purchase_order_item;
CREATE TABLE sh_purchase_order_item (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  purchase_no VARCHAR(64) NOT NULL,
  line_no INT NOT NULL,
  sku_unit_id BIGINT NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  unit_price DECIMAL(18,6) DEFAULT NULL,
  amount DECIMAL(18,6) DEFAULT NULL,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_purchase_line (shop_id, purchase_no, line_no),
  KEY idx_purchase (shop_id, purchase_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店订货明细';

DROP TABLE IF EXISTS sh_purchase_order_log;
CREATE TABLE sh_purchase_order_log (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  purchase_no VARCHAR(64) NOT NULL,
  operate_type VARCHAR(40) NOT NULL,
  operate_user BIGINT DEFAULT NULL,
  operate_time BIGINT NOT NULL,
  remark VARCHAR(200) DEFAULT NULL,
  KEY idx_purchase (shop_id, purchase_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店订货日志';

-- =========================================================
-- 13. 报表 rpt_*
-- =========================================================
DROP TABLE IF EXISTS rpt_shop_day_settlement;
CREATE TABLE rpt_shop_day_settlement (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  shop_id BIGINT NOT NULL,
  biz_date INT NOT NULL,
  total_order_count INT NOT NULL DEFAULT 0,
  total_pay_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  total_refund_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  net_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_shop_date (shop_id, biz_date),
  KEY idx_company_date (company_id, biz_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店日汇总报表';

DROP TABLE IF EXISTS rpt_company_day_summary;
CREATE TABLE rpt_company_day_summary (
  id BIGINT PRIMARY KEY,
  company_id BIGINT NOT NULL,
  biz_date INT NOT NULL,
  shop_count INT NOT NULL DEFAULT 0,
  total_order_count INT NOT NULL DEFAULT 0,
  total_pay_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  total_refund_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  net_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
  create_time BIGINT NOT NULL,
  UNIQUE KEY uk_company_date (company_id, biz_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='公司汇总日报（老板视角）';

SET FOREIGN_KEY_CHECKS = 1;