-- WP-SD4: persist diagnosis severity / cross-dept for formal approval routing.
SET @sev_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND COLUMN_NAME='ProblemSeverity')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD COLUMN `ProblemSeverity` VARCHAR(16) NULL',
  'SELECT 1');
PREPARE sev_stmt FROM @sev_sql;
EXECUTE sev_stmt;
DEALLOCATE PREPARE sev_stmt;

SET @cross_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND COLUMN_NAME='IsCrossDept')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD COLUMN `IsCrossDept` TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT 1');
PREPARE cross_stmt FROM @cross_sql;
EXECUTE cross_stmt;
DEALLOCATE PREPARE cross_stmt;

-- Formal approval role baseline for all enabled business tenants.
DROP TEMPORARY TABLE IF EXISTS tmp_smart_ops_role_seed;
CREATE TEMPORARY TABLE tmp_smart_ops_role_seed (
  seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(64) NOT NULL,
  order_no INT NOT NULL
);

INSERT INTO tmp_smart_ops_role_seed (tenant_id, code, name, order_no)
SELECT t.Id, v.Code, v.Name, v.OrderNo
FROM SysTenant t
CROSS JOIN (
  SELECT 'SMART_OPS_DEPT_HEAD' AS Code, '责任部门负责人' AS Name, 910 AS OrderNo
  UNION ALL SELECT 'SMART_OPS_MODULE_OWNER', '模块负责人', 911
  UNION ALL SELECT 'SMART_OPS_OPS_OWNER', '运营负责人', 912
) v
WHERE t.Status = 1
  AND t.Id NOT IN (0, 1, 1300000000001)
  AND NOT EXISTS (SELECT 1 FROM SysRole x WHERE x.TenantId = t.Id AND x.Code = v.Code)
ORDER BY t.Id, v.OrderNo;

SET @smart_ops_role_base := (SELECT IFNULL(MAX(Id), 0) FROM SysRole);

INSERT INTO SysRole (Id, Name, Code, OrderNo, DataScope, Remark, Status, TenantId, CreateTime)
SELECT @smart_ops_role_base + s.seq, s.name, s.code, s.order_no, 4,
       '智慧诊断改善计划审批角色基线', 1, s.tenant_id, NOW()
FROM tmp_smart_ops_role_seed s;

DROP TEMPORARY TABLE IF EXISTS tmp_smart_ops_role_seed;

-- UAT tenants: bind enabled sys_admin members so yellow/red/global routes are resolvable.
DROP TEMPORARY TABLE IF EXISTS tmp_smart_ops_ur_seed;
CREATE TEMPORARY TABLE tmp_smart_ops_ur_seed (
  seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL
);

INSERT INTO tmp_smart_ops_ur_seed (user_id, role_id)
SELECT DISTINCT u.Id, target.Id
FROM SysUser u
JOIN SysRole admin_role
  ON admin_role.TenantId = u.TenantId AND admin_role.Code = 'sys_admin' AND admin_role.Status = 1
JOIN SysUserRole admin_ur
  ON admin_ur.UserId = u.Id AND admin_ur.RoleId = admin_role.Id
JOIN SysRole target
  ON target.TenantId = u.TenantId
 AND target.Code IN ('SMART_OPS_DEPT_HEAD', 'SMART_OPS_MODULE_OWNER', 'SMART_OPS_OPS_OWNER')
 AND target.Status = 1
WHERE u.Status = 1
  AND u.TenantId IN (838257186181189, 838257212780613, 838257237606469)
  AND NOT EXISTS (
    SELECT 1 FROM SysUserRole x WHERE x.UserId = u.Id AND x.RoleId = target.Id
  );

SET @smart_ops_ur_base := (SELECT IFNULL(MAX(Id), 0) FROM SysUserRole);

INSERT INTO SysUserRole (Id, UserId, RoleId)
SELECT @smart_ops_ur_base + s.seq, s.user_id, s.role_id
FROM tmp_smart_ops_ur_seed s;

DROP TEMPORARY TABLE IF EXISTS tmp_smart_ops_ur_seed;