-- 异常工单模块完整初始化脚本
-- 包含表结构创建、权限配置、测试数据插入

-- 1. 创建异常工单表
CREATE TABLE IF NOT EXISTS t_exception_workorder (
    workorder_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '工单ID',
    workorder_no VARCHAR(30) NOT NULL COMMENT '工单编号',
    order_no VARCHAR(30) COMMENT '关联订单编号',
    dealer_code VARCHAR(20) COMMENT '经销商编码',
    dealer_name VARCHAR(100) COMMENT '经销商名称',
    exception_type TINYINT NOT NULL COMMENT '异常类型（1-逻辑验证异常/2-源头验证异常/3-交叉验证异常）',
    severity_level TINYINT NOT NULL COMMENT '严重程度（1-高/2-中/3-低）',
    workorder_status TINYINT DEFAULT 1 COMMENT '工单状态（1-待处理/2-处理中/3-已解决/4-已关闭）',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '工单创建时间',
    expect_complete_time DATETIME COMMENT '预计处理完成时间',
    handler_user VARCHAR(20) COMMENT '处理人',
    exception_desc VARCHAR(500) COMMENT '异常描述',
    handle_suggest VARCHAR(500) COMMENT '处理建议',
    data_source VARCHAR(20) COMMENT '数据来源',
    create_by VARCHAR(64) DEFAULT '' COMMENT '创建者',
    update_by VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    del_flag CHAR(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='异常工单表';

-- 2. 创建异常工单处理日志表
CREATE TABLE IF NOT EXISTS t_exception_workorder_log (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    workorder_id BIGINT NOT NULL COMMENT '关联工单ID',
    workorder_no VARCHAR(30) NOT NULL COMMENT '关联工单编号',
    handle_user VARCHAR(20) NOT NULL COMMENT '处理人',
    handle_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '处理时间',
    before_status TINYINT NOT NULL COMMENT '处理前状态',
    after_status TINYINT NOT NULL COMMENT '处理后状态',
    handle_opinion VARCHAR(500) COMMENT '处理意见',
    attach_url VARCHAR(200) COMMENT '附件URL',
    create_by VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_by VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    del_flag CHAR(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='异常工单处理日志表';

-- 3. 创建索引
-- 异常工单表索引
CREATE UNIQUE INDEX IF NOT EXISTS uk_workorder_no ON t_exception_workorder(workorder_no);
CREATE INDEX IF NOT EXISTS idx_workorder_dealer_code ON t_exception_workorder(dealer_code);
CREATE INDEX IF NOT EXISTS idx_workorder_order_no ON t_exception_workorder(order_no);
CREATE INDEX IF NOT EXISTS idx_workorder_status ON t_exception_workorder(workorder_status);
CREATE INDEX IF NOT EXISTS idx_workorder_exception_type ON t_exception_workorder(exception_type);
CREATE INDEX IF NOT EXISTS idx_workorder_severity_level ON t_exception_workorder(severity_level);
CREATE INDEX IF NOT EXISTS idx_workorder_create_time ON t_exception_workorder(create_time);
CREATE INDEX IF NOT EXISTS idx_workorder_handler_user ON t_exception_workorder(handler_user);

-- 异常工单处理日志表索引
CREATE INDEX IF NOT EXISTS idx_log_workorder_id ON t_exception_workorder_log(workorder_id);
CREATE INDEX IF NOT EXISTS idx_log_workorder_no ON t_exception_workorder_log(workorder_no);
CREATE INDEX IF NOT EXISTS idx_log_handle_user ON t_exception_workorder_log(handle_user);
CREATE INDEX IF NOT EXISTS idx_log_handle_time ON t_exception_workorder_log(handle_time);

-- 4. 创建外键约束
ALTER TABLE t_exception_workorder_log 
ADD CONSTRAINT IF NOT EXISTS fk_workorder_log_workorder_id 
FOREIGN KEY (workorder_id) REFERENCES t_exception_workorder(workorder_id) 
ON DELETE CASCADE ON UPDATE CASCADE;

-- 5. 插入异常工单菜单
INSERT IGNORE INTO t_sys_menu (parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time) VALUES 
(0, '异常工单', 1, '/main/exception-workorder', '⚠️', 7, 1, 'exception:workorder:view', 'admin', NOW());

-- 获取异常工单菜单ID
SET @exception_menu_id = LAST_INSERT_ID();

-- 6. 插入异常工单子菜单
INSERT IGNORE INTO t_sys_menu (parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time) VALUES 
(@exception_menu_id, '工单查询', 2, '', '', 1, 1, 'exception:workorder:list', 'admin', NOW()),
(@exception_menu_id, '工单详情', 2, '', '', 2, 1, 'exception:workorder:detail', 'admin', NOW()),
(@exception_menu_id, '工单新增', 2, '', '', 3, 1, 'exception:workorder:add', 'admin', NOW()),
(@exception_menu_id, '工单编辑', 2, '', '', 4, 1, 'exception:workorder:edit', 'admin', NOW()),
(@exception_menu_id, '工单删除', 2, '', '', 5, 1, 'exception:workorder:delete', 'admin', NOW()),
(@exception_menu_id, '状态更新', 2, '', '', 6, 1, 'exception:workorder:status', 'admin', NOW()),
(@exception_menu_id, '批量处理', 2, '', '', 7, 1, 'exception:workorder:batch', 'admin', NOW()),
(@exception_menu_id, '统计查看', 2, '', '', 8, 1, 'exception:workorder:stats', 'admin', NOW());


-- 12. 为管理员角色分配异常工单权限
INSERT IGNORE INTO t_sys_role_menu (role_id, menu_id, create_by, create_time) 
SELECT r.role_id, @exception_menu_id, 'admin', NOW()
FROM t_sys_role r 
WHERE r.role_code = 'ADMIN';

-- 13. 为管理员角色分配异常工单子菜单权限
INSERT IGNORE INTO t_sys_role_menu (role_id, menu_id, create_by, create_time) 
SELECT r.role_id, m.menu_id, 'admin', NOW()
FROM t_sys_role r, t_sys_menu m 
WHERE r.role_code = 'ADMIN' 
AND m.parent_id = @exception_menu_id;


-- 完成初始化
SELECT '异常工单模块初始化完成' AS message;