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

-- =============================================================================
-- 1.0.317：租户隔离批次 4 — Demo 租户注册、S8 演示数据迁移、租户基线补种、qms DEFAULT 去除
-- DemoTenantId=797403760988230 / DemoFactoryId=797403760988231 / DemoRootOrgId=797403760988232
-- KPI 模板租户=797403760988229（AIDOP）
-- 幂等可重跑；执行前建议 §9.1 备份 ado_s8_order_flow_* 相关表
-- =============================================================================

-- ── 0) 迁移前检查：tenant_id=1 且 data_source<>SEED 的行（若有则不要盲目迁，先报负责人）──
-- SELECT 'ado_s8_order_flow_order' AS tbl, data_source, COUNT(*) AS cnt
-- FROM ado_s8_order_flow_order WHERE tenant_id = 1 AND IFNULL(data_source,'') <> 'SEED'
-- UNION ALL
-- SELECT 'ado_s8_order_flow_stage', data_source, COUNT(*) FROM ado_s8_order_flow_stage WHERE tenant_id = 1 AND IFNULL(data_source,'') <> 'SEED';

SET @demo_tenant_id  = 797403760988230;
SET @demo_factory_id = 797403760988231;
SET @demo_root_org   = 797403760988232;
SET @demo_user_id    = 797403760988233;
SET @demo_pos_id     = 797403760988234;
SET @kpi_template_tid = 797403760988229;

-- ── 1) 注册 AIDOP-Demo 租户（最小字段，对照 AIDOP 租户 797403760988229）──

INSERT INTO SysOrg (Id, Pid, Name, Code, Type, Level, OrderNo, Status, Remark, TenantId, CreateTime)
SELECT @demo_root_org, 0, 'AIDOP-Demo', 'AIDOP-Demo', '201', 1, 100, 1, 'S8/S0 演示租户根机构', @demo_tenant_id, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM SysOrg WHERE Id = @demo_root_org);

INSERT INTO SysOrg (Id, Pid, Name, Code, Type, Level, OrderNo, Status, Remark, TenantId, CreateTime)
SELECT @demo_factory_id, @demo_root_org, 'Demo工厂', 'AIDOP-Demo-F001', '501', 2, 101, 1, 'S8 订单流演示工厂', @demo_tenant_id, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM SysOrg WHERE Id = @demo_factory_id);

INSERT INTO SysPos (Id, Name, Code, OrderNo, Remark, Status, TenantId, CreateTime)
SELECT @demo_pos_id, '管理员-AIDOP-Demo', 'AIDOP-Demo', 100, 'AIDOP-Demo 演示租户', 1, @demo_tenant_id, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM SysPos WHERE Id = @demo_pos_id);

INSERT INTO SysUser
  (Id, Account, Password, RealName, NickName, Sex, Age, CardType, CultureLevel, OrderNo,
   Status, AccountType, PosId, OrgId, LangCode, TenantId, Remark, CreateTime, Birthday)
SELECT @demo_user_id, 'AIDOPDemoTenant', u.Password, 'Demo管理员', 'Demo管理员',
       0, 0, 0, 0, 100, 1, 777, @demo_pos_id, @demo_factory_id, 'zh-CN', @demo_tenant_id,
       'AIDOP-Demo 演示租户管理员（UpdateScript 1.0.317）', NOW(), '2000-01-01'
FROM SysUser u
WHERE u.Id = 797403762421829
  AND NOT EXISTS (SELECT 1 FROM SysUser WHERE Id = @demo_user_id);

INSERT INTO SysTenant
  (Id, UserId, OrgId, TenantType, DbType, OrderNo, Remark, Status, Title, CreateTime)
SELECT @demo_tenant_id, @demo_user_id, @demo_root_org, 0, 0, 100, 'AIDOP-Demo 演示租户', 1, 'AIDOP-Demo', NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM SysTenant WHERE Id = @demo_tenant_id);

-- ── 2) S8 演示数据：tenant_id=1 & data_source=SEED → Demo 租户/工厂 ──

UPDATE ado_s8_order_flow_order
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_stage
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_substep
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_substep_unit
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_snapshot
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_procurement_pivot
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

-- 该表无 data_source 列，仅按 tenant_id=1 迁移（演示期全为种子数据）
UPDATE ado_s8_order_flow_product_design_drawing
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1;

UPDATE ado_s8_order_flow_manufacturing_process
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_manufacturing_loss_factor
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_manufacturing_operator
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_final_assembly_gate
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

UPDATE ado_s8_order_flow_final_assembly_parallel
SET tenant_id = @demo_tenant_id, factory_id = @demo_factory_id
WHERE tenant_id = 1 AND data_source = 'SEED';

-- S0 示范组织改挂 Demo 租户（代码种子 S0OrgSeedData 同步）
UPDATE SysOrg
SET TenantId = @demo_tenant_id, Pid = @demo_root_org
WHERE Id = 1329900200001 AND TenantId <> @demo_tenant_id;

UPDATE SysOrg
SET TenantId = @demo_tenant_id
WHERE Id IN (1329900200002, 1329900200003) AND TenantId <> @demo_tenant_id;

-- ── 3/4) S8 角色补种 + KPI 模板复制：挪到 1.0.318（避免本脚本阻塞启动；见该文件）──

-- ── 5) qms_* 表 tenant_id 列去除 DEFAULT 1300000000001（避免新 INSERT silently 落默认租户）──

ALTER TABLE `qms_fqcbj` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_gcjyd` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_gcjydzb` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_qcp_insappnentry` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_qcp_inspbill` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_qcp_inspecapplyn` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_qcpp_inspbill` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';
ALTER TABLE `qms_qcpp_inspbillst` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT 'tenant_id WP0-D4';