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

-- =============================================================================
-- 1.0.256 — S5 IQC / S6 IPQC 同库分租户：贴源源表补 tenant_id 列 + 历史归属回填
--
-- 背景：qms_qcp_inspbill / qms_qcp_inspecapplyn / qms_qcp_insappnentry（IQC）与
--       qms_gcjyd / qms_gcjydzb（IPQC）均为外部贴源表，原无 tenant_id，全库共享 →
--       同库多租户下跨租户可见。本迁移补 tenant_id 列并回填历史归属，配合服务层查询/
--       详情/流程的强制租户过滤（本版本随附代码），实现"当前租户只见自己数据"。
--
-- 回填口径：无租户(NULL) 或落在框架默认租户 1300000000001 的历史行 → 参考租户
--           797403760988229；已归具体租户（如 AIDOPB 824585161322565）的预置行不动。
--           qms_qcp_* / qms_gcjyd* 为贴源表，新环境为空、1300000000001 为框架默认租户
--           （非真实客户数据），故此口径安全。
-- 幂等：ADD COLUMN 前用 information_schema 判存在；回填带条件；重复执行安全。
-- 兼容：MySQL 5.x / 8.x（PREPARE/EXECUTE 模式，无需 DELIMITER）。
-- =============================================================================

SET NAMES utf8mb4;
SET @db = DATABASE();
SET @col = 'tenant_id';

-- ── IQC 三表加列 ────────────────────────────────────────────────────────────
SET @tbl = 'qms_qcp_inspbill';
SET @exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col);
SET @sql = IF(@exists=0, CONCAT('ALTER TABLE ',@tbl,' ADD COLUMN ',@col,' BIGINT NULL COMMENT ''租户id（DOP 多租户隔离列）'''), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @tbl = 'qms_qcp_inspecapplyn';
SET @exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col);
SET @sql = IF(@exists=0, CONCAT('ALTER TABLE ',@tbl,' ADD COLUMN ',@col,' BIGINT NULL COMMENT ''租户id（DOP 多租户隔离列）'''), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @tbl = 'qms_qcp_insappnentry';
SET @exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col);
SET @sql = IF(@exists=0, CONCAT('ALTER TABLE ',@tbl,' ADD COLUMN ',@col,' BIGINT NULL COMMENT ''租户id（DOP 多租户隔离列）'''), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── IPQC 两表加列 ───────────────────────────────────────────────────────────
SET @tbl = 'qms_gcjyd';
SET @exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col);
SET @sql = IF(@exists=0, CONCAT('ALTER TABLE ',@tbl,' ADD COLUMN ',@col,' BIGINT NULL COMMENT ''租户id（DOP 多租户隔离列）'''), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @tbl = 'qms_gcjydzb';
SET @exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME=@col);
SET @sql = IF(@exists=0, CONCAT('ALTER TABLE ',@tbl,' ADD COLUMN ',@col,' BIGINT NULL COMMENT ''租户id（DOP 多租户隔离列）'''), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── IQC 历史归属回填（NULL 或 1300000000001 → 797；具体租户预置行不动）────────
UPDATE qms_qcp_inspbill      SET tenant_id=797403760988229 WHERE tenant_id IS NULL OR tenant_id=1300000000001;
UPDATE qms_qcp_inspecapplyn  SET tenant_id=797403760988229 WHERE tenant_id IS NULL OR tenant_id=1300000000001;
UPDATE qms_qcp_insappnentry  SET tenant_id=797403760988229 WHERE tenant_id IS NULL OR tenant_id=1300000000001;
UPDATE ado_s5_iqc_sqe_disposition SET tenant_id=797403760988229 WHERE tenant_id IS NULL OR tenant_id=1300000000001;
-- IPQC 两表历史为空，无需回填（新数据由抽数/预置携带 tenant_id）。