-- 用户部门关联表（支持多部门）
-- 创建时间: 2026-01-02
-- 说明: 支持单用户属于多个部门

CREATE TABLE IF NOT EXISTS `sys_user_department` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `dept_id` BIGINT NOT NULL COMMENT '部门ID',
    `is_primary` TINYINT DEFAULT 0 COMMENT '是否为主部门：0-否，1-是',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_dept` (`user_id`, `dept_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_dept_id` (`dept_id`),
    CONSTRAINT `fk_user_dept_user` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_user_dept_dept` FOREIGN KEY (`dept_id`) REFERENCES `sys_dept` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户部门关联表';

-- 数据迁移：将现有sys_user表中的dept_id迁移到sys_user_department表
INSERT INTO `sys_user_department` (`user_id`, `dept_id`, `is_primary`)
SELECT `id`, `dept_id`, 1
FROM `sys_user`
WHERE `dept_id` IS NOT NULL AND `dept_id` > 0
ON DUPLICATE KEY UPDATE `is_primary` = 1;