-- ============================================
-- 云仓服务数据库初始化脚本
-- ============================================

-- 创建仓库表
CREATE TABLE IF NOT EXISTS `warehouse` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '仓库ID',
    `name` VARCHAR(255) NOT NULL COMMENT '仓库名称',
    `type` VARCHAR(20) NOT NULL COMMENT '仓库类型: erp=ERP仓库, cloud=云仓',
    `cloud_system` VARCHAR(50) DEFAULT NULL COMMENT '云仓系统: jst=聚水潭, wdt=旺店通, qimen=奇门',
    `owner_code` VARCHAR(100) DEFAULT NULL COMMENT '货主编码',
    `cloud_code` VARCHAR(100) DEFAULT NULL COMMENT '云仓编码',
    `app_key` VARCHAR(255) DEFAULT NULL COMMENT 'AppKey',
    `app_secret` VARCHAR(255) DEFAULT NULL COMMENT 'AppSecret',
    `api_url` VARCHAR(500) DEFAULT NULL COMMENT 'API地址',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `enabled` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_name` (`name`),
    KEY `idx_type` (`type`),
    KEY `idx_cloud_system` (`cloud_system`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='仓库表';

-- 创建仓库模板绑定表
CREATE TABLE IF NOT EXISTS `warehouse_template_binding` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '绑定ID',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `platform` VARCHAR(50) NOT NULL COMMENT '平台: taobao, jd, douyin, etc.',
    `template_id` BIGINT NOT NULL COMMENT '模板ID',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_warehouse_platform` (`warehouse_id`, `platform`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_platform` (`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='仓库模板绑定表';

-- 创建云仓订单同步日志表
CREATE TABLE IF NOT EXISTS `cloud_order_sync_log` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '日志ID',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `order_type` VARCHAR(20) NOT NULL COMMENT '订单类型: purchase=采购, sale=销售',
    `order_no` VARCHAR(100) NOT NULL COMMENT '订单号',
    `push_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '推送时间',
    `status` VARCHAR(20) NOT NULL COMMENT '状态: pending=待处理, success=成功, failed=失败',
    `request_data` TEXT DEFAULT NULL COMMENT '请求数据',
    `response_data` TEXT DEFAULT NULL COMMENT '响应数据',
    `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_order_no` (`order_no`),
    KEY `idx_status` (`status`),
    KEY `idx_push_time` (`push_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='云仓订单同步日志表';

-- 创建云仓库存同步日志表
CREATE TABLE IF NOT EXISTS `cloud_stock_sync_log` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '日志ID',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `sku_code` VARCHAR(100) NOT NULL COMMENT 'SKU编码',
    `sync_type` VARCHAR(20) NOT NULL COMMENT '同步类型: query=查询, adjust=调整',
    `quantity` INT DEFAULT NULL COMMENT '库存数量',
    `sync_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '同步时间',
    `status` VARCHAR(20) NOT NULL COMMENT '状态: pending=待处理, success=成功, failed=失败',
    `response_data` TEXT DEFAULT NULL COMMENT '响应数据',
    `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_sku_code` (`sku_code`),
    KEY `idx_sync_time` (`sync_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='云仓库存同步日志表';

-- ============================================
-- 初始化测试数据
-- ============================================

-- 插入测试仓库
INSERT INTO `warehouse` (`name`, `type`, `cloud_system`, `owner_code`, `cloud_code`, `app_key`, `app_secret`, `api_url`, `remark`, `enabled`)
VALUES 
    ('ERP主仓库', 'erp', NULL, NULL, NULL, NULL, NULL, NULL, 'ERP系统主仓库', 1),
    ('聚水潭云仓', 'cloud', 'jst', 'OWNER001', 'WH_JST_001', 'jst_app_key_xxx', 'jst_app_secret_xxx', 'https://open.jushuitan.com/api', '聚水潭测试云仓', 1),
    ('旺店通云仓', 'cloud', 'wdt', 'OWNER002', 'WH_WDT_001', 'wdt_app_key_xxx', 'wdt_app_secret_xxx', 'https://api.wdtms.com', '旺店通测试云仓', 1);

-- ============================================
-- ERP本地库存相关表
-- ============================================

-- 库存表
CREATE TABLE IF NOT EXISTS `stock` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '库存ID',
    `sku_code` VARCHAR(100) NOT NULL COMMENT 'SKU编码',
    `sku_name` VARCHAR(255) DEFAULT NULL COMMENT 'SKU名称',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `quantity` INT DEFAULT 0 COMMENT '总库存数量',
    `locked_quantity` INT DEFAULT 0 COMMENT '锁定数量',
    `reserved_quantity` INT DEFAULT 0 COMMENT '预扣数量（冻结）',
    `defective_quantity` INT DEFAULT 0 COMMENT '残次品数量',
    `warning_threshold` INT DEFAULT 10 COMMENT '预警阈值',
    `version` INT DEFAULT 0 COMMENT '乐观锁版本号',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` 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`),
    KEY `idx_warning_threshold` (`warning_threshold`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存表';

-- 库存流水日志表
CREATE TABLE IF NOT EXISTS `stock_log` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '日志ID',
    `sku_code` VARCHAR(100) NOT NULL COMMENT 'SKU编码',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `change_type` VARCHAR(50) NOT NULL COMMENT '变更类型: inbound/outbound/lock/unlock/reserve/confirm/cancel/ship/defective_inbound/adjust',
    `before_quantity` INT DEFAULT 0 COMMENT '变更前库存',
    `change_quantity` INT NOT NULL COMMENT '变更数量',
    `after_quantity` INT DEFAULT 0 COMMENT '变更后库存',
    `order_id` BIGINT DEFAULT NULL COMMENT '关联订单ID',
    `related_no` VARCHAR(100) DEFAULT NULL COMMENT '关联单号',
    `delivery_no` VARCHAR(100) DEFAULT NULL COMMENT '运单号',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `operator_id` BIGINT DEFAULT NULL COMMENT '操作人ID',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_sku_code` (`sku_code`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_change_type` (`change_type`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存流水日志表';

-- 入库单表
CREATE TABLE IF NOT EXISTS `inbound_order` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '入库单ID',
    `inbound_no` VARCHAR(50) NOT NULL COMMENT '入库单号',
    `inbound_type` VARCHAR(20) NOT NULL COMMENT '入库类型: purchase=采购入库, return=退货入库, transfer=调拨入库',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `related_no` VARCHAR(100) DEFAULT NULL COMMENT '关联单号',
    `supplier_id` BIGINT DEFAULT NULL COMMENT '供应商ID',
    `supplier_name` VARCHAR(255) DEFAULT NULL COMMENT '供应商名称',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending=待入库, partial=部分入库, completed=已完成, cancelled=已取消',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `operator_id` BIGINT DEFAULT NULL COMMENT '操作人ID',
    `operator_name` VARCHAR(100) DEFAULT NULL COMMENT '操作人名称',
    `expected_time` DATETIME DEFAULT NULL COMMENT '预计到货时间',
    `inbound_time` DATETIME DEFAULT NULL COMMENT '实际入库时间',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_inbound_no` (`inbound_no`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_inbound_type` (`inbound_type`),
    KEY `idx_status` (`status`),
    KEY `idx_related_no` (`related_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='入库单表';

-- 出库单表
CREATE TABLE IF NOT EXISTS `outbound_order` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '出库单ID',
    `outbound_no` VARCHAR(50) NOT NULL COMMENT '出库单号',
    `outbound_type` VARCHAR(20) NOT NULL COMMENT '出库类型: sale=销售出库, transfer=调拨出库',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `target_warehouse_id` BIGINT DEFAULT NULL COMMENT '目标仓库ID（调拨出库时）',
    `related_no` VARCHAR(100) DEFAULT NULL COMMENT '关联订单号',
    `customer_id` BIGINT DEFAULT NULL COMMENT '客户ID',
    `customer_name` VARCHAR(255) DEFAULT NULL COMMENT '客户名称',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending=待出库, partial=部分出库, completed=已完成, cancelled=已取消',
    `logistics_company` VARCHAR(100) DEFAULT NULL COMMENT '物流公司',
    `tracking_no` VARCHAR(100) DEFAULT NULL COMMENT '运单号',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `operator_id` BIGINT DEFAULT NULL COMMENT '操作人ID',
    `operator_name` VARCHAR(100) DEFAULT NULL COMMENT '操作人名称',
    `outbound_time` DATETIME DEFAULT NULL COMMENT '出库时间',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_outbound_no` (`outbound_no`),
    KEY `idx_warehouse_id` (`warehouse_id`),
    KEY `idx_outbound_type` (`outbound_type`),
    KEY `idx_status` (`status`),
    KEY `idx_related_no` (`related_no`),
    KEY `idx_tracking_no` (`tracking_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='出库单表';

-- 调拨单表
CREATE TABLE IF NOT EXISTS `transfer_order` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '调拨单ID',
    `transfer_no` VARCHAR(50) NOT NULL COMMENT '调拨单号',
    `source_warehouse_id` BIGINT NOT NULL COMMENT '源仓库ID',
    `target_warehouse_id` BIGINT NOT NULL COMMENT '目标仓库ID',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending=待调拨, in_transit=调拨中, received=已收货, cancelled=已取消',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `operator_id` BIGINT DEFAULT NULL COMMENT '操作人ID',
    `operator_name` VARCHAR(100) DEFAULT NULL COMMENT '操作人名称',
    `expected_ship_time` DATETIME DEFAULT NULL COMMENT '预计发货时间',
    `ship_time` DATETIME DEFAULT NULL COMMENT '实际发货时间',
    `receive_time` DATETIME DEFAULT NULL COMMENT '收货时间',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_transfer_no` (`transfer_no`),
    KEY `idx_source_warehouse_id` (`source_warehouse_id`),
    KEY `idx_target_warehouse_id` (`target_warehouse_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='调拨单表';

-- 库存预警配置表
CREATE TABLE IF NOT EXISTS `stock_alert_config` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '配置ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `global_threshold` INT DEFAULT 10 COMMENT '全局预警阈值',
    `enabled` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    `auto_check_enabled` TINYINT(1) DEFAULT 0 COMMENT '是否自动检查',
    `check_interval_minutes` INT DEFAULT 60 COMMENT '检查间隔（分钟）',
    `notify_only_when_critical` TINYINT(1) DEFAULT 0 COMMENT '仅严重时通知',
    `alert_channels` VARCHAR(500) DEFAULT '["system"]' COMMENT '预警渠道JSON',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存预警配置表';

-- 库存预警通知配置表
CREATE TABLE IF NOT EXISTS `stock_alert_notification` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '配置ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `email_enabled` TINYINT(1) DEFAULT 0 COMMENT '邮箱通知是否启用',
    `email_recipients` VARCHAR(500) DEFAULT NULL COMMENT '邮箱收件人（逗号分隔）',
    `dingtalk_enabled` TINYINT(1) DEFAULT 0 COMMENT '钉钉通知是否启用',
    `dingtalk_webhook_url` VARCHAR(500) DEFAULT NULL COMMENT '钉钉Webhook URL',
    `dingtalk_secret` VARCHAR(200) DEFAULT NULL COMMENT '钉钉签名密钥',
    `dingtalk_at_mobiles` VARCHAR(500) DEFAULT NULL COMMENT '钉钉@手机号（逗号分隔）',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存预警通知配置表';