-- ================================================================
-- 售后服务数据库初始化脚本
-- ================================================================

-- 售后主表
CREATE TABLE IF NOT EXISTS `after_sales` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `short_id` VARCHAR(32) NOT NULL UNIQUE COMMENT '售后单短ID，如 AS20240401ABC123',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '原订单ID',
    `order_short_id` VARCHAR(32) DEFAULT NULL COMMENT '原订单短ID',
    `platform_order_sn` VARCHAR(64) DEFAULT NULL COMMENT '平台订单号',
    `shop_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '店铺ID',
    `shop_name` VARCHAR(128) DEFAULT NULL COMMENT '店铺名称',
    `customer_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '客户ID',
    `customer_name` VARCHAR(64) DEFAULT NULL COMMENT '客户姓名',
    `customer_phone` VARCHAR(20) DEFAULT NULL COMMENT '客户电话',

    -- 售后类型与原因
    `type` VARCHAR(20) NOT NULL COMMENT '售后类型: refund(仅退款)/return(退货退款)/exchange(换货)/repair(维修)',
    `reason` VARCHAR(128) NOT NULL COMMENT '售后原因',
    `reason_detail` VARCHAR(512) DEFAULT NULL COMMENT '详细原因描述',
    `description` VARCHAR(1000) DEFAULT NULL COMMENT '客户描述',

    -- 金额信息
    `apply_amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00' COMMENT '申请退款金额',
    `approve_amount` DECIMAL(12,2) DEFAULT NULL COMMENT '审核通过退款金额',
    `freight_refund` DECIMAL(12,2) DEFAULT '0.00' COMMENT '运费退款金额',
    `actual_refund` DECIMAL(12,2) DEFAULT NULL COMMENT '实际退款金额',

    -- 状态流转
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '售后状态: pending/approved/rejected/processing/completed/closed',
    `audit_status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '审核状态: pending/approved/rejected',
    `previous_status` VARCHAR(20) DEFAULT NULL COMMENT '上一状态（用于撤销）',

    -- 审核信息
    `audit_comment` VARCHAR(512) DEFAULT NULL COMMENT '审核备注/驳回原因',
    `auditor_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '审核人ID',
    `auditor_name` VARCHAR(64) DEFAULT NULL COMMENT '审核人姓名',
    `audit_time` DATETIME DEFAULT NULL COMMENT '审核时间',

    -- 退款/收货信息
    `refund_time` DATETIME DEFAULT NULL COMMENT '退款时间',
    `return_tracking_no` VARCHAR(64) DEFAULT NULL COMMENT '退货快递单号',
    `return_express_company` VARCHAR(64) DEFAULT NULL COMMENT '退货快递公司',
    `receive_time` DATETIME DEFAULT NULL COMMENT '收到退货时间',
    `receive_comment` VARCHAR(512) DEFAULT NULL COMMENT '收货备注',

    -- 完成信息
    `complete_time` DATETIME DEFAULT NULL COMMENT '完成时间',
    `close_time` DATETIME DEFAULT NULL COMMENT '关闭时间',
    `close_reason` VARCHAR(512) DEFAULT NULL COMMENT '关闭原因',

    -- 图片凭证
    `images` TEXT DEFAULT NULL COMMENT '图片凭证JSON数组',

    -- 关联单据
    `exchange_order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '换货新订单ID',
    `repair_order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '维修工单ID',
    `finance_refund_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '财务退款记录ID',

    `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_short_id` (`short_id`),
    INDEX `idx_order_id` (`order_id`),
    INDEX `idx_platform_order_sn` (`platform_order_sn`),
    INDEX `idx_type` (`type`),
    INDEX `idx_status` (`status`),
    INDEX `idx_audit_status` (`audit_status`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_shop_id` (`shop_id`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_refund_time` (`refund_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后主表';

-- 售后明细表（售后关联的订单商品）
CREATE TABLE IF NOT EXISTS `after_sale_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `after_sale_id` BIGINT UNSIGNED NOT NULL COMMENT '售后单ID',
    `order_item_id` BIGINT UNSIGNED NOT NULL COMMENT '原订单明细ID',
    `product_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '商品ID',
    `product_name` VARCHAR(256) DEFAULT NULL COMMENT '商品名称',
    `sku_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'SKU ID',
    `sku_code` VARCHAR(64) DEFAULT NULL COMMENT 'SKU编码',
    `sku_specs` VARCHAR(512) DEFAULT NULL COMMENT 'SKU规格描述',
    `image_url` VARCHAR(512) DEFAULT NULL COMMENT '商品图片',
    `unit_price` DECIMAL(12,2) DEFAULT '0.00' COMMENT '单价',
    `quantity` INT UNSIGNED DEFAULT '1' COMMENT '数量',
    `apply_quantity` INT UNSIGNED DEFAULT '1' COMMENT '申请售后数量',
    `approve_quantity` INT UNSIGNED DEFAULT NULL COMMENT '审核通过数量',
    `return_quantity` INT UNSIGNED DEFAULT '0' COMMENT '实际退货数量',
    `item_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 '更新时间',
    `deleted` TINYINT UNSIGNED DEFAULT '0' COMMENT '逻辑删除标记',

    INDEX `idx_after_sale_id` (`after_sale_id`),
    INDEX `idx_order_item_id` (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后明细表';

-- 售后操作日志表
CREATE TABLE IF NOT EXISTS `after_sale_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `after_sale_id` BIGINT UNSIGNED NOT NULL COMMENT '售后单ID',
    `operator_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '操作人ID',
    `operator_name` VARCHAR(64) DEFAULT NULL COMMENT '操作人姓名',
    `operator_type` VARCHAR(20) DEFAULT 'user' COMMENT '操作人类型: user/system/admin',
    `action` VARCHAR(32) NOT NULL COMMENT '操作动作: create/audit/approve/reject/refund/complete/close/update',
    `from_status` VARCHAR(20) DEFAULT NULL COMMENT '操作前状态',
    `to_status` VARCHAR(20) DEFAULT NULL COMMENT '操作后状态',
    `content` VARCHAR(1000) DEFAULT NULL COMMENT '操作内容描述',
    `ip` VARCHAR(64) DEFAULT NULL COMMENT '操作IP',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

    INDEX `idx_after_sale_id` (`after_sale_id`),
    INDEX `idx_operator_id` (`operator_id`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后操作日志表';
