-- =============================================
-- 售后工单明细增强 SQL 初始化脚本（可重复执行）
-- 需求：售后退货/工单每条明细独立售后单号，每条选部门、原因类型并填具体原因
-- 包含：after_sale_ticket_item 加字段 + aftersale_reason_type 字典
-- =============================================

-- 强制连接字符集，避免中文被双重编码（乱码）
SET NAMES utf8mb4;

-- =============================================
-- 1. 工单明细表新增字段（存在性判断，保证可重复执行）
-- =============================================
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket_item' AND COLUMN_NAME = 'item_no');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE `after_sale_ticket_item`
        ADD COLUMN `item_no` varchar(64) NULL COMMENT ''明细售后单号（工单号-序号）'' AFTER `ticket_id`,
        ADD COLUMN `dept_id` bigint NULL COMMENT ''部门ID，关联 system_dept.id'' AFTER `need_return`,
        ADD COLUMN `reason_type` tinyint NULL COMMENT ''原因类型，字典 aftersale_reason_type'' AFTER `dept_id`,
        ADD COLUMN `reason` varchar(500) NULL COMMENT ''具体原因'' AFTER `reason_type`',
    'SELECT 1');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =============================================
-- 2. 数据字典：售后原因类型 aftersale_reason_type
--    （取值可由业务在「系统管理 - 字典管理」中自行维护）
-- =============================================
INSERT INTO `system_dict_type` (`id`, `name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1065307, '售后原因类型', 'aftersale_reason_type', 0, '售后工单明细原因分类', '', NOW(), '', NOW(), b'0')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `type` = VALUES(`type`), `status` = VALUES(`status`), `deleted` = b'0';

INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1065352, 1, '外观缺陷', '1', 'aftersale_reason_type', 0, 'primary', NULL, '', NOW(), '', NOW(), b'0'),
(1065353, 2, '功能故障', '2', 'aftersale_reason_type', 0, 'warning', NULL, '', NOW(), '', NOW(), b'0'),
(1065354, 3, '尺寸偏差', '3', 'aftersale_reason_type', 0, 'info', NULL, '', NOW(), '', NOW(), b'0'),
(1065355, 4, '包装破损', '4', 'aftersale_reason_type', 0, 'warning', NULL, '', NOW(), '', NOW(), b'0'),
(1065356, 5, '物流损坏', '5', 'aftersale_reason_type', 0, 'danger', NULL, '', NOW(), '', NOW(), b'0'),
(1065357, 6, '缺件少发', '6', 'aftersale_reason_type', 0, 'danger', NULL, '', NOW(), '', NOW(), b'0'),
(1065358, 7, '规格型号不符', '7', 'aftersale_reason_type', 0, 'info', NULL, '', NOW(), '', NOW(), b'0'),
(1065359, 8, '使用不当', '8', 'aftersale_reason_type', 0, 'default', NULL, '', NOW(), '', NOW(), b'0'),
(1065360, 9, '其他', '9', 'aftersale_reason_type', 0, 'default', NULL, '', NOW(), '', NOW(), b'0')
ON DUPLICATE KEY UPDATE `sort` = VALUES(`sort`), `label` = VALUES(`label`), `value` = VALUES(`value`),
    `dict_type` = VALUES(`dict_type`), `status` = VALUES(`status`), `color_type` = VALUES(`color_type`), `deleted` = b'0';

SELECT '售后工单明细增强初始化完成' AS result;