-- =============================================
-- 售后工单：客户/联系人/物料/批次 改为文本存储 + 兜底补齐工单按钮权限（可重复执行）
-- =============================================
SET @c1 := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket' AND COLUMN_NAME = 'customer_name');
SET @ddl1 := IF(@c1 = 0,
    'ALTER TABLE `after_sale_ticket` ADD COLUMN `customer_name` varchar(255) NULL COMMENT ''客户名称(文本)'' AFTER `customer_id`',
    'SELECT 1');
PREPARE s1 FROM @ddl1; EXECUTE s1; DEALLOCATE PREPARE s1;

SET @c2 := (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket' AND COLUMN_NAME = 'contact_name');
SET @ddl2 := IF(@c2 = 0,
    'ALTER TABLE `after_sale_ticket` ADD COLUMN `contact_name` varchar(255) NULL COMMENT ''联系人(文本)'' AFTER `contact_id`',
    'SELECT 1');
PREPARE s2 FROM @ddl2; EXECUTE s2; DEALLOCATE PREPARE s2;

-- 客户/联系人改为文本后，原外键列允许为空
ALTER TABLE `after_sale_ticket` MODIFY COLUMN `customer_id` bigint NULL COMMENT '客户编号(可选)';
ALTER TABLE `after_sale_ticket` MODIFY COLUMN `contact_id` bigint NULL COMMENT '联系人编号(可选)';

-- 物料/批次改为文本后，明细原外键列允许为空
ALTER TABLE `after_sale_ticket_item` MODIFY COLUMN `sale_order_item_id` bigint NULL COMMENT '销售订单行(可选)';
ALTER TABLE `after_sale_ticket_item` MODIFY COLUMN `item_id` bigint NULL COMMENT 'MDM物料编号(可选)';
ALTER TABLE `after_sale_ticket_item` MODIFY COLUMN `batch_id` bigint NULL COMMENT '批次编号(可选)';

-- 兜底：工单按钮权限（仅当该权限不存在时才插入，避免与已有菜单重复/撞 id）
INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
SELECT 602700,'售后工单新增','aftersales:ticket:create',3,1,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:create' AND `deleted`=b'0');
INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
SELECT 602701,'售后工单编辑','aftersales:ticket:update',3,2,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:update' AND `deleted`=b'0');
INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
SELECT 602702,'售后工单删除','aftersales:ticket:delete',3,3,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:delete' AND `deleted`=b'0');
INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
SELECT 602703,'售后工单查询','aftersales:ticket:query',3,4,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:query' AND `deleted`=b'0');

-- 兜底按钮 授权给超管
INSERT INTO `system_role_menu` (`role_id`,`menu_id`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
SELECT 1, m.menu_id, '1', NOW(), '1', NOW(), b'0'
FROM (SELECT 602700 AS menu_id UNION ALL SELECT 602701 UNION ALL SELECT 602702 UNION ALL SELECT 602703) m
WHERE EXISTS (SELECT 1 FROM `system_menu` sm WHERE sm.`id`=m.menu_id)
  AND NOT EXISTS (SELECT 1 FROM `system_role_menu` rm WHERE rm.`role_id`=1 AND rm.`menu_id`=m.menu_id AND rm.`deleted`=b'0');

SELECT '售后工单文本字段初始化完成' AS result;