-- ================================================================
-- 订单服务数据库初始化脚本
-- ================================================================

-- 订单主表
CREATE TABLE IF NOT EXISTS `orders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `short_id` VARCHAR(32) NOT NULL UNIQUE COMMENT '订单短ID，如 O20240401ABC123',
    `platform_order_sn` VARCHAR(64) DEFAULT NULL COMMENT '平台订单号',
    `platform` VARCHAR(32) DEFAULT NULL COMMENT '平台：taobao/jd/pdd/douyin/weixin',
    `shop_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '店铺ID',
    `shop_name` VARCHAR(128) DEFAULT NULL COMMENT '店铺名称',
    `order_time` DATETIME DEFAULT NULL COMMENT '下单时间',
    `buyer_nick` VARCHAR(64) DEFAULT NULL COMMENT '买家昵称',
    `receiver_name` VARCHAR(64) DEFAULT NULL COMMENT '收货人姓名',
    `receiver_phone` VARCHAR(20) DEFAULT NULL COMMENT '收货人电话',
    `receiver_address` VARCHAR(512) DEFAULT NULL COMMENT '收货地址',
    `goods_amount` DECIMAL(12,2) DEFAULT '0.00' COMMENT '商品金额',
    `discount_amount` DECIMAL(12,2) DEFAULT '0.00' COMMENT '优惠金额',
    `freight` DECIMAL(12,2) DEFAULT '0.00' COMMENT '运费',
    `total_amount` DECIMAL(12,2) DEFAULT '0.00' COMMENT '订单总金额',
    `order_status` VARCHAR(20) DEFAULT 'pending' COMMENT '订单状态: pending/auditing/shipped/completed/cancelled',
    `platform_status` VARCHAR(64) DEFAULT NULL COMMENT '平台原始状态',
    `audit_status` VARCHAR(20) DEFAULT 'pending' COMMENT '审核状态: pending/approved/rejected',
    `delivery_status` VARCHAR(20) DEFAULT 'pending' COMMENT '发货状态: pending/delivered',
    `warehouse_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '仓库ID',
    `warehouse_name` VARCHAR(128) DEFAULT NULL COMMENT '仓库名称',
    `express_company` VARCHAR(64) DEFAULT NULL COMMENT '快递公司',
    `express_name` VARCHAR(64) DEFAULT NULL COMMENT '快递名称/简称',
    `express_no` VARCHAR(64) DEFAULT NULL COMMENT '快递单号',
    `delivery_time` DATETIME DEFAULT NULL COMMENT '发货时间',
    `end_time` DATETIME DEFAULT NULL COMMENT '订单完成时间',
    `audit_comment` VARCHAR(512) DEFAULT NULL COMMENT '审核备注/驳回原因',
    `remark` VARCHAR(1000) DEFAULT NULL COMMENT '订单备注',
    `version` INT UNSIGNED DEFAULT '0' COMMENT '乐观锁版本号',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT UNSIGNED DEFAULT '0' COMMENT '逻辑删除标记: 0未删除 1已删除',
    INDEX `idx_platform` (`platform`),
    INDEX `idx_shop_id` (`shop_id`),
    INDEX `idx_order_status` (`order_status`),
    INDEX `idx_audit_status` (`audit_status`),
    INDEX `idx_delivery_status` (`delivery_status`),
    INDEX `idx_order_time` (`order_time`),
    INDEX `idx_short_id` (`short_id`),
    INDEX `idx_platform_order_sn` (`platform_order_sn`),
    INDEX `idx_receiver_phone` (`receiver_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单主表';

-- 订单项表
CREATE TABLE IF NOT EXISTS `order_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `goods_name` VARCHAR(256) DEFAULT NULL COMMENT '商品名称',
    `platform_sku` VARCHAR(64) DEFAULT NULL COMMENT '平台SKU',
    `sku_code` VARCHAR(64) DEFAULT NULL COMMENT 'ERP SKU编码',
    `erp_sku_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'ERP SKU ID',
    `quantity` INT UNSIGNED DEFAULT '1' COMMENT '购买数量',
    `price` DECIMAL(12,2) DEFAULT '0.00' COMMENT '单价',
    `total_amount` DECIMAL(12,2) DEFAULT '0.00' COMMENT '小计金额',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_order_id` (`order_id`),
    INDEX `idx_sku_code` (`sku_code`),
    INDEX `idx_erp_sku_id` (`erp_sku_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单项表';

-- 订单操作日志表
CREATE TABLE IF NOT EXISTS `order_operation_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `operator_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '操作人ID',
    `operator_name` VARCHAR(64) DEFAULT NULL COMMENT '操作人名称',
    `action` VARCHAR(32) DEFAULT NULL COMMENT '操作类型',
    `action_text` VARCHAR(64) DEFAULT NULL COMMENT '操作类型文本',
    `content` VARCHAR(512) DEFAULT NULL COMMENT '操作内容',
    `before_status` VARCHAR(20) DEFAULT NULL COMMENT '操作前状态',
    `after_status` VARCHAR(20) DEFAULT NULL COMMENT '操作后状态',
    `ip` VARCHAR(45) DEFAULT NULL COMMENT 'IP地址',
    `user_agent` VARCHAR(512) DEFAULT NULL COMMENT 'User-Agent',
    `extra_data` TEXT DEFAULT NULL COMMENT '扩展数据JSON',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX `idx_order_id` (`order_id`),
    INDEX `idx_operator_id` (`operator_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单操作日志表';
