-- =============================================
-- 修复：部门/岗位的 tenant_id 错误（0 应为 1）
--
-- 现象：
--   1) 员工管理列表「部门」列为空（部分或全部）
--   2) 部门管理页面看不到大部分部门（只剩「总公司」）
--   3) 新增员工报「部门不存在 / 岗位不存在」，无法保存
--   4) 员工列表按部门筛选查不到数据
--
-- 根因：
--   导入部门/岗位时未写 tenant_id，落成 0；而系统按登录租户（通常为 1）查询，
--   导致这些部门/岗位在业务上"不存在"。
--   （hrm_employee 表无 tenant_id 列，所以员工本身能看到，只是部门名解析不到）
--
-- 执行前请确认目标租户 id（单租户系统一般为 1）：
--   SELECT id, name FROM system_tenant;
-- =============================================
SET NAMES utf8mb4;

-- 目标租户（按实际情况修改）
SET @T := 1;

-- ---------- 1. 执行前检查 ----------
SELECT '执行前：部门租户分布' AS step, tenant_id, COUNT(*) AS cnt
FROM system_dept WHERE deleted = b'0' GROUP BY tenant_id;

SELECT '执行前：岗位租户分布' AS step, tenant_id, COUNT(*) AS cnt
FROM system_post WHERE deleted = b'0' GROUP BY tenant_id;

-- 重名检查：必须都为 0，否则 UPDATE 会违反唯一索引
SELECT '部门重名数(须为0)' AS step, COUNT(*) AS cnt
FROM system_dept d0
JOIN system_dept d1 ON d1.tenant_id = @T AND d1.name = d0.name AND d1.deleted = b'0'
WHERE d0.tenant_id = 0 AND d0.deleted = b'0';

SELECT '岗位重名数(须为0)' AS step, COUNT(*) AS cnt
FROM system_post p0
JOIN system_post p1 ON p1.tenant_id = @T AND p1.name = p0.name AND p1.deleted = b'0'
WHERE p0.tenant_id = 0 AND p0.deleted = b'0';

-- ---------- 2. 备份 ----------
CREATE TABLE IF NOT EXISTS bak_system_dept_tenant AS SELECT * FROM system_dept;
CREATE TABLE IF NOT EXISTS bak_system_post_tenant AS SELECT * FROM system_post;

-- ---------- 3. 修复 ----------
UPDATE system_dept SET tenant_id = @T WHERE tenant_id = 0;
UPDATE system_post SET tenant_id = @T WHERE tenant_id = 0;

-- ---------- 4. 执行后核对 ----------
SELECT '执行后：部门租户分布' AS step, tenant_id, COUNT(*) AS cnt
FROM system_dept WHERE deleted = b'0' GROUP BY tenant_id;

SELECT '执行后：岗位租户分布' AS step, tenant_id, COUNT(*) AS cnt
FROM system_post WHERE deleted = b'0' GROUP BY tenant_id;

-- 员工部门悬空数（应为 0，表示所有员工的部门都能解析到）
SELECT '员工部门悬空数(应为0)' AS step, COUNT(*) AS cnt
FROM hrm_employee e
LEFT JOIN system_dept d ON d.id = e.dept_id AND d.tenant_id = @T AND d.deleted = b'0'
WHERE e.deleted = b'0' AND d.id IS NULL;

SELECT '修复完成，请让用户重新登录后刷新页面' AS result;