-- 库存盘点模块数据库初始化脚本
-- 对应PHP Stock和StockLog模型
-- Migration from Laravel to Java MyBatis-Plus

-- 创建 stocks 表
CREATE TABLE IF NOT EXISTS `stocks` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `sku_code` VARCHAR(64) NOT NULL COMMENT 'SKU编码',
    `sku_name` VARCHAR(255) DEFAULT NULL COMMENT 'SKU名称（冗余）',
    `warehouse_id` BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
    `quantity` INT NOT NULL DEFAULT 0 COMMENT '实际库存',
    `locked_quantity` INT NOT NULL DEFAULT 0 COMMENT '锁定库存',
    `reserved_quantity` INT NOT NULL DEFAULT 0 COMMENT '预扣库存（两阶段模式）',
    `defective_quantity` INT NOT NULL DEFAULT 0 COMMENT '残次品数量',
    `warning_threshold` INT NOT NULL DEFAULT 0 COMMENT '预警阈值',
    `version` INT NOT NULL DEFAULT 0 COMMENT '版本号（乐观锁）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sku_warehouse` (`sku_code`, `warehouse_id`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_sku_code` (`sku_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存表';

-- 创建 stock_logs 表
CREATE TABLE IF NOT EXISTS `stock_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `sku_code` VARCHAR(64) NOT NULL COMMENT 'SKU编码',
    `warehouse_id` BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
    `change_quantity` INT NOT NULL COMMENT '变化数量（正为入库，负为出库）',
    `type` VARCHAR(20) NOT NULL COMMENT '类型（inbound/outbound/lock/unlock/ship/defective_inbound/adjust/reserve/confirm/cancel）',
    `related_no` VARCHAR(64) DEFAULT NULL COMMENT '关联单号',
    `order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '订单ID',
    `delivery_no` VARCHAR(64) DEFAULT NULL COMMENT '运单号',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_sku_code` (`sku_code`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_type` (`type`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存流水表';

-- 创建 stock_initial 表（初始库存，用于一致性检查）
CREATE TABLE IF NOT EXISTS `stock_initial` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `sku_code` VARCHAR(64) NOT NULL COMMENT 'SKU编码',
    `warehouse_id` BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
    `quantity` INT NOT NULL DEFAULT 0 COMMENT '初始库存数量',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sku_warehouse` (`sku_code`, `warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存初始表';

-- 创建 stock_freezes 表（库存冻结记录，用于预扣两阶段模式）
CREATE TABLE IF NOT EXISTS `stock_freezes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `sku_code` VARCHAR(64) NOT NULL COMMENT 'SKU编码',
    `warehouse_id` BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `quantity` INT NOT NULL COMMENT '冻结数量',
    `status` VARCHAR(20) NOT NULL DEFAULT 'frozen' COMMENT '状态（frozen=冻结, confirmed=已确认, cancelled=已取消）',
    `freeze_token` VARCHAR(128) DEFAULT NULL COMMENT '冻结凭证',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_freeze_token` (`freeze_token`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存冻结表';
