-- ============================================================
-- 酒水仓库管理系统 数据库 Schema
-- MySQL 8.0+
-- 说明：所有业务表含 shop_id 实现多租户隔离
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 门店（租户）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `shops` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`             VARCHAR(100)    NOT NULL COMMENT '门店名称',
  `code`             VARCHAR(50)     NOT NULL COMMENT '门店编号（唯一）',
  `address`          VARCHAR(255)    DEFAULT NULL,
  `phone`            VARCHAR(30)     DEFAULT NULL,
  `manager_name`     VARCHAR(50)     DEFAULT NULL COMMENT '负责人',
  `description`      TEXT            DEFAULT NULL COMMENT '店铺简介',
  `logo_url`         VARCHAR(500)    DEFAULT ''   COMMENT '门店 logo URL',
  `business_license` VARCHAR(500)    DEFAULT NULL COMMENT '营业执照照片URL',
  `shop_photos`      JSON            DEFAULT NULL COMMENT '门店照片URL数组',
  `custom_fields`    JSON            DEFAULT NULL COMMENT '扩展字段',
  `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`       DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店（租户）';

-- ------------------------------------------------------------
-- 用户（操作人员）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`       BIGINT UNSIGNED NOT NULL,
  `username`      VARCHAR(50)     NOT NULL,
  `password_hash` VARCHAR(255)    NOT NULL COMMENT 'bcrypt',
  `real_name`     VARCHAR(50)     DEFAULT NULL,
  `phone`         VARCHAR(30)     DEFAULT NULL,
  `role`          ENUM('admin','operator','readonly','superadmin') NOT NULL DEFAULT 'operator',
  `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
  `last_login_at` DATETIME        DEFAULT NULL COMMENT '最近登录时间',
  `custom_fields` JSON            DEFAULT NULL,
  `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`    DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_shop_username` (`shop_id`, `username`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户';

-- ------------------------------------------------------------
-- 登录会话（支持登出/踢人/在线状态监控；JWT 的 sid claim 指向此表）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_sessions` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`        BIGINT UNSIGNED NOT NULL,
  `user_id`        BIGINT UNSIGNED NOT NULL,
  `sid`            VARCHAR(64)     NOT NULL COMMENT '嵌入 JWT 的会话标识',
  `device_id`      VARCHAR(255)    DEFAULT NULL,
  `device_name`    VARCHAR(255)    DEFAULT NULL,
  `platform`       VARCHAR(50)     DEFAULT NULL COMMENT 'windows|macos|linux|android|ios|web',
  `platform_class` VARCHAR(20)     DEFAULT NULL COMMENT 'desktop|mobile|web',
  `ip`             VARCHAR(64)     DEFAULT NULL,
  `user_agent`     VARCHAR(512)    DEFAULT NULL,
  `refresh_jti`    VARCHAR(64)     DEFAULT NULL COMMENT '当前有效 refresh token 的 jti，用于轮换+重用检测',
  `created_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `revoked_at`     DATETIME        DEFAULT NULL,
  `revoked_reason` VARCHAR(30)     DEFAULT NULL COMMENT 'kicked|logout|admin|disabled|reuse|pwd_reset|relogin',
  `revoked_by`     BIGINT UNSIGNED DEFAULT NULL COMMENT '吊销操作人 user_id；系统/自助吊销为 NULL',
  `refresh_exp_at` DATETIME        DEFAULT NULL,
  `kind`           VARCHAR(10)     DEFAULT 'login' COMMENT 'login=正常登录；sso=App换票的网页短时会话（不计设备配额）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_session_sid` (`sid`),
  KEY `idx_session_shop_user` (`shop_id`, `user_id`),
  KEY `idx_session_class` (`platform_class`),
  KEY `idx_session_revoked` (`revoked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录会话';

-- ------------------------------------------------------------
-- 登录尝试审计（当前只记失败尝试，用于风控/排查；由保留期清理任务定期删旧）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_code`  VARCHAR(64)     DEFAULT NULL,
  `username`   VARCHAR(50)     DEFAULT NULL,
  `ip`         VARCHAR(64)     DEFAULT NULL,
  `user_agent` VARCHAR(512)    DEFAULT NULL,
  `success`    TINYINT(1)      NOT NULL DEFAULT 0,
  `reason`     VARCHAR(40)     DEFAULT NULL COMMENT 'invalid_shop|invalid_user|bad_password|locked|inactive|platform_not_allowed',
  `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_attempt_user` (`username`, `created_at`),
  KEY `idx_attempt_ip` (`ip`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录尝试审计';

-- ------------------------------------------------------------
-- 许可证
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `licenses` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `license_key`  VARCHAR(768)    NOT NULL COMMENT 'Ed25519 signed token',
  `device_id`    VARCHAR(255)    DEFAULT NULL COMMENT 'deprecated: use license_devices',
  `type`         ENUM('trial','monthly','annual','lifetime') NOT NULL DEFAULT 'trial',
  `tier`         VARCHAR(32)     NOT NULL DEFAULT 'standard' COMMENT '权益档位钩子(pro/max-like)，当前默认 standard',
  `expires_at`   DATETIME        DEFAULT NULL COMMENT 'NULL=永久',
  `is_active`    TINYINT(1)      NOT NULL DEFAULT 1,
  `max_devices`  INT             NOT NULL DEFAULT 3 COMMENT '最大绑定设备数',
  `features`     JSON            DEFAULT NULL COMMENT '功能开关 {"finance":true}',
  `activated_at` DATETIME        DEFAULT NULL COMMENT 'deprecated',
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_license_key` (`license_key`),
  KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='许可证';

CREATE TABLE IF NOT EXISTS `license_devices` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `license_id`   BIGINT UNSIGNED NOT NULL,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `device_id`    VARCHAR(255)    NOT NULL,
  `device_name`  VARCHAR(255)    DEFAULT NULL,
  `platform`     VARCHAR(50)     DEFAULT NULL COMMENT 'windows|macos|android|ios|web',
  `activated_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_license_device` (`license_id`, `device_id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_license_id` (`license_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='许可证设备绑定';

-- 兑换券（激活码）码池：平台方批量生成，用户兑换后叠加时长到门店授权
CREATE TABLE IF NOT EXISTS `license_codes` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`               VARCHAR(32)     NOT NULL COMMENT '归一化大写无连字符',
  `type`               ENUM('trial','monthly','annual','lifetime') NOT NULL DEFAULT 'annual' COMMENT '计费/时长标签',
  `tier`               VARCHAR(32)     NOT NULL DEFAULT 'standard' COMMENT '档位钩子(pro/max-like)，当前默认 standard',
  `duration_days`      INT             NOT NULL DEFAULT 0 COMMENT '授予时长；0=永久(lifetime)',
  `max_devices`        INT             NOT NULL DEFAULT 0 COMMENT '授予设备上限；0=不改变现值',
  `status`             ENUM('unused','redeemed','void') NOT NULL DEFAULT 'unused',
  `redeemed_shop_id`   BIGINT UNSIGNED DEFAULT NULL COMMENT '被哪个门店兑换',
  `redeemed_at`        DATETIME        DEFAULT NULL,
  `redeemed_device_id` VARCHAR(255)    DEFAULT NULL COMMENT '兑换设备(审计)',
  `batch`              VARCHAR(64)     DEFAULT NULL COMMENT '发放批次/活动名',
  `note`               VARCHAR(255)    DEFAULT NULL,
  `created_at`         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_status` (`status`),
  KEY `idx_redeemed_shop` (`redeemed_shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='兑换券码池';

-- ------------------------------------------------------------
-- 在线购买/续费记录（pay 收款中枢，契约 pay-contract v1.0.0）
-- out_trade_no = pay 订单号，对账键 + 幂等键；amount 为 pay 回传权威金额
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `license_purchases` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`          BIGINT UNSIGNED NOT NULL,
  `user_id`          BIGINT UNSIGNED NOT NULL COMMENT '下单管理员',
  `product_biz_code` VARCHAR(64)     NOT NULL COMMENT '套餐稳定码(monthly_standard/annual_standard/monthly_pro/annual_pro)',
  `amount_minor`     BIGINT          NOT NULL DEFAULT 0 COMMENT 'v2 口径：最小单位金额(CNY=分)',
  `currency`         VARCHAR(8)      NOT NULL DEFAULT '' COMMENT 'v2 口径：币种，如 CNY',
  `pay_url`          TEXT            DEFAULT NULL COMMENT 'v2 下单 session payload.url（支付宝网关 URL 带签名参数可超 2KB），订单管理「继续支付」复用',
  `renewed_to`       DATETIME        DEFAULT NULL COMMENT 'settle 续期后的授权到期日（订单流水「授权续期至」展示）',
  `amount`           VARCHAR(16)     DEFAULT NULL COMMENT 'Deprecated: v1 元字符串，如 2999.00，观察一版后 DROP',
  `out_trade_no`     VARCHAR(64)     DEFAULT NULL COMMENT 'pay 订单号',
  `status`           ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  `trade_no`         VARCHAR(64)     DEFAULT NULL COMMENT '渠道交易号(支付宝/微信)',
  `channel`          VARCHAR(16)     DEFAULT NULL,
  `paid_at`          DATETIME        DEFAULT NULL,
  `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`       DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_out_trade_no` (`out_trade_no`),
  KEY `idx_shop` (`shop_id`),
  KEY `idx_status` (`status`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='在线购买/续费记录';

-- ------------------------------------------------------------
-- 商品分类
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_categories` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `name`         VARCHAR(100)    NOT NULL,
  `parent_id`    BIGINT UNSIGNED DEFAULT NULL COMMENT '父分类，支持二级',
  `sort_order`   INT             NOT NULL DEFAULT 0,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`   DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类';

-- ------------------------------------------------------------
-- 商品
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`       BIGINT UNSIGNED NOT NULL,
  `code`          VARCHAR(50)     DEFAULT NULL COMMENT '商品编号',
  `barcode`       VARCHAR(100)    DEFAULT NULL COMMENT '条码',
  `name`          VARCHAR(200)    NOT NULL COMMENT '商品名称',
  `series`        VARCHAR(100)    DEFAULT NULL COMMENT '系列',
  `spec`          VARCHAR(100)    DEFAULT NULL COMMENT '规格',
  `unit`          VARCHAR(20)     DEFAULT NULL COMMENT '单位',
  `category_id`   BIGINT UNSIGNED DEFAULT NULL,
  `brand`         VARCHAR(100)    DEFAULT NULL COMMENT '品牌',
  `purchase_price` DECIMAL(16,2)  DEFAULT NULL COMMENT '参考进价',
  `sale_price`    DECIMAL(16,2)   DEFAULT NULL COMMENT '参考售价',
  `public_id`     CHAR(36)        NOT NULL DEFAULT '' COMMENT 'UUID，公开URL使用',
  `min_stock`     INT             DEFAULT 0 COMMENT '库存预警值',
  `description`   TEXT            DEFAULT NULL COMMENT '商品描述',
  `custom_fields` JSON            DEFAULT NULL COMMENT '动态扩展字段',
  `remark`        VARCHAR(500)    DEFAULT NULL,
  `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`    DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_deleted_at` (`deleted_at`),
  UNIQUE KEY `uk_product_code` (`shop_id`, `code`),
  UNIQUE KEY `uk_public_id` (`public_id`),
  FULLTEXT KEY `ft_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品';

-- ------------------------------------------------------------
-- 商品图片
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_images` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `shop_id`    BIGINT UNSIGNED NOT NULL,
  `url`        VARCHAR(500)    NOT NULL COMMENT '相对路径，如 /images/products/1/xxx.jpg',
  `sort_order` INT             NOT NULL DEFAULT 0,
  `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品图片';

-- ------------------------------------------------------------
-- 仓库
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouses` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `name`         VARCHAR(100)    NOT NULL,
  `location`     VARCHAR(200)    DEFAULT NULL,
  `is_default`   TINYINT(1)      NOT NULL DEFAULT 0,
  `custom_fields` JSON           DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`   DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓库';

-- ------------------------------------------------------------
-- 往来单位（供应商/客户）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `partners` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `code`         VARCHAR(50)     DEFAULT NULL,
  `name`         VARCHAR(200)    NOT NULL,
  `name_pinyin`   VARCHAR(400)   DEFAULT NULL COMMENT '名称全拼（搜索用，写入时自动生成）',
  `name_initials` VARCHAR(100)   DEFAULT NULL COMMENT '名称拼音首字母（搜索用）',
  `type`         SET('supplier','customer') NOT NULL DEFAULT 'supplier' COMMENT '可同时是供应商和客户',
  `contact`      VARCHAR(50)     DEFAULT NULL COMMENT '联系人',
  `phone`        VARCHAR(30)     DEFAULT NULL,
  `address`      VARCHAR(255)    DEFAULT NULL,
  `bank_account` VARCHAR(100)    DEFAULT NULL COMMENT '银行账号',
  `status`       ENUM('enabled','disabled') NOT NULL DEFAULT 'enabled' COMMENT '正常/禁用',
  `custom_fields` JSON           DEFAULT NULL,
  `remark`       VARCHAR(500)    DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`   DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='往来单位';

-- ------------------------------------------------------------
-- 入库单
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_in_orders` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `order_no`     VARCHAR(50)     NOT NULL COMMENT '入库单号',
  `type`         VARCHAR(30)     NOT NULL DEFAULT 'purchase' COMMENT '入库类型: purchase采购/return退货/other其他',
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `partner_id`   BIGINT UNSIGNED DEFAULT NULL COMMENT '供应商',
  `operator_id`  BIGINT UNSIGNED NOT NULL COMMENT '经办人',
  `creator_id`   BIGINT UNSIGNED DEFAULT NULL COMMENT '制单人',
  `reviewer_id`  BIGINT UNSIGNED DEFAULT NULL COMMENT '审核人',
  `status`       ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
  `order_date`   DATE            NOT NULL,
  `cost_total`   DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '应付合计=Σ总进价(2026-07 消歧，旧列 total_amount 弃用)',
  `reviewed_at`  DATETIME        DEFAULT NULL,
  `custom_fields` JSON           DEFAULT NULL,
  `remark`       VARCHAR(500)    DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`   DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`shop_id`, `order_no`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='入库单';

-- ------------------------------------------------------------
-- 入库单明细
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_in_items` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id`     BIGINT UNSIGNED NOT NULL,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `product_id`   BIGINT UNSIGNED NOT NULL,
  `product_code` VARCHAR(50)     DEFAULT NULL COMMENT '商品编号(源流水号，历史导入)',
  `product_name` VARCHAR(255)    DEFAULT NULL COMMENT '商品名称(历史导入快照)',
  `series`       VARCHAR(100)    DEFAULT NULL COMMENT '系列(历史导入快照)',
  `spec`         VARCHAR(100)    DEFAULT NULL COMMENT '规格(历史导入快照)',
  `quantity`     DECIMAL(12,3)   NOT NULL COMMENT '数量',
  `cost_price`   DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '进价·单瓶(2026-07 消歧，旧列 unit_price 弃用)',
  `cost_amount`  DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '总进价=quantity×cost_price(旧列 total_price 弃用)',
  `batch_no`        VARCHAR(50)     DEFAULT NULL COMMENT '批次号',
  `production_date` DATE            DEFAULT NULL COMMENT '生产日期',
  `expire_date`     DATE            DEFAULT NULL COMMENT '有效期',
  `custom_fields` JSON           DEFAULT NULL,
  `remark`       VARCHAR(255)    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_shop_product` (`shop_id`, `product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='入库单明细';

-- ------------------------------------------------------------
-- 出库单
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_out_orders` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `order_no`     VARCHAR(50)     NOT NULL,
  `type`         VARCHAR(30)     NOT NULL DEFAULT 'sale' COMMENT 'sale销售/return退货/other其他',
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `partner_id`   BIGINT UNSIGNED DEFAULT NULL COMMENT '客户',
  `operator_id`  BIGINT UNSIGNED NOT NULL,
  `creator_id`   BIGINT UNSIGNED DEFAULT NULL COMMENT '制单人',
  `reviewer_id`  BIGINT UNSIGNED DEFAULT NULL,
  `status`       ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
  `order_date`   DATE            NOT NULL,
  `sale_total`   DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '应收合计=Σ售价小计(2026-07 消歧，旧列 total_amount 弃用)',
  `profit_total` DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '总利润=Σ(sale_price>0?(sale_price-cost_price)×qty:0)，建单落库、确认售价/进价联动重算',
  `reviewed_at`  DATETIME        DEFAULT NULL,
  `custom_fields` JSON           DEFAULT NULL,
  `remark`       VARCHAR(500)    DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`   DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`shop_id`, `order_no`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='出库单';

-- ------------------------------------------------------------
-- 出库单明细
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_out_items` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id`     BIGINT UNSIGNED NOT NULL,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `product_id`   BIGINT UNSIGNED NOT NULL,
  `product_code` VARCHAR(50)     DEFAULT NULL COMMENT '商品编号(源流水号，历史导入)',
  `product_name` VARCHAR(255)    DEFAULT NULL COMMENT '商品名称(历史导入快照)',
  `series`       VARCHAR(100)    DEFAULT NULL COMMENT '系列(历史导入快照)',
  `spec`         VARCHAR(100)    DEFAULT NULL COMMENT '规格(历史导入快照)',
  `quantity`     DECIMAL(12,3)   NOT NULL,
  `cost_price`   DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '成本单价·入库成本快照(旧列 unit_price 弃用)',
  `sale_price`   DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '售价(出库实际销售单价，应收按售价×数量)',
  `cost_amount`  DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '成本小计=quantity×cost_price(旧列 total_price 弃用)',
  `sale_amount`  DECIMAL(16,2)   NOT NULL DEFAULT 0 COMMENT '售价小计=quantity×sale_price(待定价=0)',
  `batch_no`        VARCHAR(50)  DEFAULT NULL COMMENT '批次号',
  `production_date` DATE         DEFAULT NULL COMMENT '生产日期',
  `custom_fields` JSON           DEFAULT NULL,
  `remark`       VARCHAR(255)    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_shop_product` (`shop_id`, `product_id`),
  KEY `idx_so_items_shop_code` (`shop_id`, `product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='出库单明细';

-- ------------------------------------------------------------
-- 库存（批次模式：每条记录代表一个批次/入库批）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventories` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`             BIGINT UNSIGNED NOT NULL,
  `warehouse_id`        BIGINT UNSIGNED DEFAULT NULL,
  `product_id`          BIGINT UNSIGNED DEFAULT NULL,
  `stock_in_item_id`    BIGINT UNSIGNED DEFAULT NULL,
  `inventory_check_id`  BIGINT UNSIGNED DEFAULT NULL,
  `quantity`            DECIMAL(12,3)   NOT NULL DEFAULT 0,
  `status`              ENUM('stock','on_sale','sold') NOT NULL DEFAULT 'stock' COMMENT '库存/在售(公开)/已售(卖光留痕)',
  `product_code`        VARCHAR(50)     DEFAULT NULL,
  `product_name`        VARCHAR(200)    DEFAULT NULL,
  `series`              VARCHAR(100)    DEFAULT NULL,
  `spec`                VARCHAR(100)    DEFAULT NULL,
  `unit`                VARCHAR(20)     DEFAULT NULL,
  `warehouse_name`      VARCHAR(100)    DEFAULT NULL,
  `unit_price`          DECIMAL(16,2)   DEFAULT NULL,
  `production_date`     DATE            DEFAULT NULL,
  `batch_no`            VARCHAR(50)     DEFAULT NULL,
  `supplier_name`       VARCHAR(200)    DEFAULT NULL,
  `remark`              VARCHAR(500)    DEFAULT NULL,
  `created_at`          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`          DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_fifo` (`shop_id`, `warehouse_id`, `product_id`, `created_at`),
  KEY `idx_shop_wh_product` (`shop_id`, `warehouse_id`, `product_id`),
  KEY `idx_stock_in_item` (`stock_in_item_id`),
  KEY `idx_inventory_check` (`inventory_check_id`),
  KEY `idx_deleted_at` (`deleted_at`),
  KEY `idx_inv_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存批次记录';

-- ------------------------------------------------------------
-- 库存流水（出入库记录明细）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_logs` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `product_id`   BIGINT UNSIGNED NOT NULL,
  `direction`    ENUM('in','out') NOT NULL,
  `quantity`     DECIMAL(12,3)   NOT NULL,
  `qty_before`   DECIMAL(12,3)   NOT NULL COMMENT '变动前数量',
  `qty_after`    DECIMAL(12,3)   NOT NULL COMMENT '变动后数量',
  `ref_type`     VARCHAR(30)     NOT NULL COMMENT 'stock_in/stock_out/check',
  `ref_id`       BIGINT UNSIGNED NOT NULL COMMENT '关联单据ID',
  `operator_id`  BIGINT UNSIGNED DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_shop_product` (`shop_id`, `product_id`),
  KEY `idx_ref` (`ref_type`, `ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存流水';

-- ------------------------------------------------------------
-- 库存盘点
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_checks` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `check_no`     VARCHAR(50)     NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `operator_id`  BIGINT UNSIGNED NOT NULL,
  `status`       ENUM('draft','completed') NOT NULL DEFAULT 'draft',
  `check_date`   DATE            NOT NULL,
  `remark`       VARCHAR(500)    DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_check_no` (`shop_id`, `check_no`),
  KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存盘点单';

CREATE TABLE IF NOT EXISTS `inventory_check_items` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `check_id`       BIGINT UNSIGNED NOT NULL,
  `shop_id`        BIGINT UNSIGNED NOT NULL,
  `product_id`     BIGINT UNSIGNED NOT NULL,
  `system_qty`     DECIMAL(12,3)  NOT NULL COMMENT '系统数量',
  `actual_qty`     DECIMAL(12,3)  NOT NULL COMMENT '实际盘点数量',
  `diff_qty`       DECIMAL(12,3)  GENERATED ALWAYS AS (`actual_qty` - `system_qty`) STORED COMMENT '差异',
  `remark`         VARCHAR(255)   DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_check_id` (`check_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='盘点明细';

-- ------------------------------------------------------------
-- 财务流水
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `finance_records` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`      BIGINT UNSIGNED NOT NULL,
  `partner_id`   BIGINT UNSIGNED DEFAULT NULL,
  `type`         ENUM('receivable','payable','receipt','payment') NOT NULL COMMENT '应收/应付/收款/付款',
  `amount`       DECIMAL(16,2)   NOT NULL,
  `balance`      DECIMAL(16,2)   NOT NULL COMMENT '操作后余额',
  `status`       ENUM('open','closed') NOT NULL DEFAULT 'open' COMMENT '结清状态（payable/receivable 有效）',
  `ref_type`     VARCHAR(30)     DEFAULT NULL COMMENT '关联单据类型',
  `ref_id`       BIGINT UNSIGNED DEFAULT NULL,
  `operator_id`  BIGINT UNSIGNED NOT NULL,
  `record_date`  DATE            NOT NULL,
  `custom_fields` JSON           DEFAULT NULL,
  `remark`       VARCHAR(500)    DEFAULT NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_partner` (`partner_id`),
  KEY `idx_record_date` (`record_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务流水';

-- ------------------------------------------------------------
-- 编号规则配置
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `number_rules` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`    BIGINT UNSIGNED NOT NULL,
  `type`       VARCHAR(30)     NOT NULL COMMENT 'stock_in/stock_out/check',
  `prefix`     VARCHAR(20)     DEFAULT '' COMMENT '前缀',
  `current_no` INT             NOT NULL DEFAULT 0 COMMENT '当前序号',
  `date_format` VARCHAR(20)    DEFAULT 'YYYYMMDD' COMMENT '日期格式',
  `updated_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_shop_type` (`shop_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='单号规则';

-- ------------------------------------------------------------
-- 基础数据选项
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_name_options` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`    BIGINT UNSIGNED NOT NULL,
  `code`       VARCHAR(50)  DEFAULT NULL COMMENT '选项编号',
  `name`       VARCHAR(200) NOT NULL    COMMENT '名称',
  `remark`     VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品名称选项';

CREATE TABLE IF NOT EXISTS `product_series_options` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`    BIGINT UNSIGNED NOT NULL,
  `code`       VARCHAR(50)  DEFAULT NULL COMMENT '选项编号',
  `name`       VARCHAR(200) NOT NULL    COMMENT '系列名称',
  `remark`     VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品系列选项';

CREATE TABLE IF NOT EXISTS `product_spec_options` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id`    BIGINT UNSIGNED NOT NULL,
  `code`       VARCHAR(50)  DEFAULT NULL COMMENT '选项编号',
  `name`       VARCHAR(200) NOT NULL    COMMENT '规格名称',
  `quantity`   INT          DEFAULT 0   COMMENT '单品数量',
  `remark`     VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格选项';

CREATE TABLE IF NOT EXISTS `feedbacks` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `shop_id`     BIGINT UNSIGNED NOT NULL,
  `shop_code`   VARCHAR(50)  DEFAULT NULL,
  `user_id`     BIGINT UNSIGNED DEFAULT NULL,
  `username`    VARCHAR(50)  DEFAULT NULL,
  `role`        VARCHAR(20)  DEFAULT NULL,
  `type`        VARCHAR(20)  NOT NULL COMMENT 'bug / suggestion',
  `content`     TEXT,
  `images`      JSON,
  `app_version` VARCHAR(30)  DEFAULT NULL,
  `platform`    VARCHAR(20)  DEFAULT NULL,
  `client_ip`   VARCHAR(60)  DEFAULT NULL,
  `status`      VARCHAR(20)  NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`),
  KEY `idx_shop_id` (`shop_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户意见反馈';

-- ------------------------------------------------------------
-- App→网页免登录一次性换票（magic-link；TTL 60s 单次可用，used_at 原子置位防重放）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `web_tickets` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket`     VARCHAR(64)     NOT NULL,
  `shop_id`    BIGINT UNSIGNED NOT NULL,
  `user_id`    BIGINT UNSIGNED NOT NULL,
  `expires_at` DATETIME        NOT NULL,
  `used_at`    DATETIME        DEFAULT NULL,
  `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_web_ticket` (`ticket`),
  KEY `idx_web_ticket_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='App→网页免登录一次性票据';

SET FOREIGN_KEY_CHECKS = 1;