AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.318.sql

-- =============================================================================
-- 1.0.318：已存在租户补种 S8 业务角色 + KPI calc config（从 1.0.317 拆出）
-- KPI 模板租户=797403760988229（AIDOP）；角色 Id 用临时表 seq，避免用户变量多行撞号
-- =============================================================================

SET @kpi_template_tid = 797403760988229;

-- ── 1) S8 七个业务角色（按 Code 幂等）──

DROP TEMPORARY TABLE IF EXISTS tmp_s8_role_seed;
CREATE TEMPORARY TABLE tmp_s8_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_s8_role_seed (tenant_id, code, name, order_no)
SELECT t.Id, v.Code, v.Name, v.OrderNo
FROM SysTenant t
CROSS JOIN (
  SELECT 'ROLE_ORDER_PLANNER' AS Code, '订单计划员' AS Name, 800 AS OrderNo
  UNION ALL SELECT 'ROLE_PRODUCTION_PLANNER', '生产计划员', 801
  UNION ALL SELECT 'ROLE_PURCHASER', '采购员', 802
  UNION ALL SELECT 'ROLE_WH_INBOUND', '仓库收货员', 803
  UNION ALL SELECT 'ROLE_WH_OUTBOUND', '仓库发货员', 804
  UNION ALL SELECT 'ROLE_QC', '质检员', 805
  UNION ALL SELECT 'ROLE_EQUIP_MAINT', '设备维修员', 806
) v
WHERE t.Status = 1
  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 @s8_role_base := (SELECT IFNULL(MAX(Id), 0) FROM SysRole);

INSERT INTO SysRole (Id, Name, Code, OrderNo, DataScope, Remark, Status, TenantId, CreateTime)
SELECT @s8_role_base + s.seq, s.name, s.code, s.order_no, 4, 'S8 业务角色（异常协同承接）', 1, s.tenant_id, NOW()
FROM tmp_s8_role_seed s;

DROP TEMPORARY TABLE IF EXISTS tmp_s8_role_seed;

-- ── 2) KPI calc config：从 AIDOP 模板复制到其它租户（auto_increment Id）──

INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`SqlParametersJson`,
   `TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT t.Id, src.`MetricCode`, src.`ModuleCode`, src.`VersionNo`, src.`CalcEngineType`, src.`DataSourceCode`,
       src.`SqlScript`, src.`SqlParametersJson`, src.`TimeoutSeconds`, src.`PublishStatus`, src.`IsCurrent`,
       src.`Remark`, 'UpdateScript-1.0.318', NOW()
FROM SysTenant t
CROSS JOIN ado_smart_ops_kpi_calc_config src
WHERE t.Status = 1
  AND src.`TenantId` = @kpi_template_tid
  AND t.Id <> @kpi_template_tid
  AND NOT EXISTS (
    SELECT 1 FROM ado_smart_ops_kpi_calc_config dst
    WHERE dst.`TenantId` = t.Id AND dst.`MetricCode` = src.`MetricCode` AND dst.`VersionNo` = src.`VersionNo`
  );