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

-- 1.0.312.sql  S0/S6 租户隔离收口：BarCodeNbr / wms_rwzp 租户化 DDL（幂等 + information_schema 守卫 + 冲突前置）。
--   本批同时收口 S6 IPQC Flow/Disposition 服务的严格租户解析（纯代码，无 DDL）。
--   两表阶段 2 运行时双租户验收已 PASS：无 Token 拒绝 / 超管未选拒绝 / 跨租户 CRUD/状态阻断 / 租户内唯一。
--   两表均 0 行 → ADD tenant_id BIGINT NOT NULL（无 DEFAULT）+ 租户内业务唯一索引；不插/改/删任何业务数据、不触碰其它表。
--   幂等：可重复执行；ADD COLUMN / ADD INDEX 均以 information_schema 守卫；冲突则跳过、绝不自动清理数据。
-- ============================================================================

-- ############################################################################
-- # SECTION 1 — BarCodeNbr 条码规则主数据 租户化
-- #   租户内唯一：uk_BarCodeNbr_tenant_domain_customer_type (tenant_id, Domain, Customer, Type)
-- ############################################################################

-- 一、补 tenant_id 列（不存在才加；空表故 NOT NULL 无默认值安全，最终态无 DEFAULT）
SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'BarCodeNbr' AND COLUMN_NAME = 'tenant_id');
SET @sql := IF(@has_col = 0,
    'ALTER TABLE BarCodeNbr ADD COLUMN tenant_id BIGINT NOT NULL COMMENT ''租户 ID''',
    'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
-- 兜底：若某些服务器在 ADD NOT NULL 时隐式落了 DEFAULT，立即剥离，保证最终态无默认值。
SET @has_default := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'BarCodeNbr' AND COLUMN_NAME = 'tenant_id'
      AND COLUMN_DEFAULT IS NOT NULL);
SET @sql := IF(@has_default > 0, 'ALTER TABLE BarCodeNbr ALTER COLUMN tenant_id DROP DEFAULT', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 二、冲突前置检测（同租户内 (Domain,Customer,Type) 重复则跳过建索引，绝不自动清理数据）
SET @dup := (SELECT COUNT(*) FROM (
    SELECT tenant_id, `Domain`, `Customer`, `Type`
    FROM BarCodeNbr
    GROUP BY tenant_id, `Domain`, `Customer`, `Type`
    HAVING COUNT(*) > 1) t);
SELECT @dup AS barcodenbr_intratenant_dup;   -- 期望 0（空表）；若非 0，下方 ADD 被守卫跳过

-- 三、创建租户内唯一索引（不存在且无同租户冲突才建）
SET @has_uk := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'BarCodeNbr'
      AND INDEX_NAME = 'uk_BarCodeNbr_tenant_domain_customer_type');
SET @sql := IF(@has_uk = 0 AND @dup = 0,
    'ALTER TABLE BarCodeNbr ADD UNIQUE INDEX uk_BarCodeNbr_tenant_domain_customer_type (tenant_id, `Domain`, `Customer`, `Type`)',
    'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 四、执行后验证
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'BarCodeNbr' AND COLUMN_NAME = 'tenant_id';
-- 期望：tenant_id bigint / IS_NULLABLE=NO / COLUMN_DEFAULT=NULL（无默认值）。
SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'BarCodeNbr' AND INDEX_NAME <> 'PRIMARY'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
-- 期望：含 uk_BarCodeNbr_tenant_domain_customer_type (tenant_id, Domain, Customer, Type)（唯一）。

-- ############################################################################
-- # SECTION 2 — wms_rwzp 物料状态任务指派 租户化
-- #   业务唯一口径（wms_rwzp 无单一任务号列，取"同租户+同工厂域下同物料任务的移动指派"复合键）：
-- #     uk_wms_rwzp_tenant_org_task (tenant_id, company_ref_id, factory_ref_id, domain_code, rwlx, wlbm, rqpc, yskw, mdkw)
-- ############################################################################

-- 一、补 tenant_id 列（不存在才加；0 行表 → NOT NULL 无 DEFAULT 安全）
SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wms_rwzp' AND COLUMN_NAME = 'tenant_id');
SET @sql := IF(@has_col = 0,
    'ALTER TABLE wms_rwzp ADD COLUMN tenant_id BIGINT NOT NULL AFTER rec_id',
    'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
-- 若历史执行遗留了 DEFAULT（本脚本本身不加 DEFAULT，此步为防御性收口，确保最终态无默认值）：
SET @has_default := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wms_rwzp' AND COLUMN_NAME = 'tenant_id'
      AND COLUMN_DEFAULT IS NOT NULL);
SET @sql := IF(@has_default > 0, 'ALTER TABLE wms_rwzp ALTER COLUMN tenant_id DROP DEFAULT', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 二、冲突前置检测（同租户+业务键重复则跳过创建，绝不自动清理历史数据）
SET @dup := (SELECT COUNT(*) FROM (
    SELECT tenant_id, company_ref_id, factory_ref_id, domain_code, rwlx, wlbm, rqpc, yskw, mdkw
    FROM wms_rwzp
    GROUP BY tenant_id, company_ref_id, factory_ref_id, domain_code, rwlx, wlbm, rqpc, yskw, mdkw
    HAVING COUNT(*) > 1) t);
SELECT @dup AS wms_rwzp_intratenant_dup;   -- 期望 0（0 行表必为 0）；若非 0，下方 ADD 被守卫跳过

-- 三、创建租户内业务唯一索引（不存在且无同键冲突才建）
SET @has_uk := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wms_rwzp'
      AND INDEX_NAME = 'uk_wms_rwzp_tenant_org_task');
SET @sql := IF(@has_uk = 0 AND @dup = 0,
    'ALTER TABLE wms_rwzp ADD UNIQUE INDEX uk_wms_rwzp_tenant_org_task (tenant_id, company_ref_id, factory_ref_id, domain_code, rwlx, wlbm, rqpc, yskw, mdkw)',
    'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 四、执行后验证
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wms_rwzp' AND COLUMN_NAME = 'tenant_id';
-- 期望：tenant_id / IS_NULLABLE=NO / COLUMN_DEFAULT=NULL / DATA_TYPE=bigint。
SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wms_rwzp' AND INDEX_NAME <> 'PRIMARY'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
-- 期望：uk_wms_rwzp_tenant_org_task (tenant_id, company_ref_id, factory_ref_id, domain_code, rwlx, wlbm, rqpc, yskw, mdkw)（NON_UNIQUE=0）。

-- ============================================================================
-- 回滚 SQL（空表期可安全执行）：
--   ALTER TABLE BarCodeNbr DROP INDEX uk_BarCodeNbr_tenant_domain_customer_type;
--   ALTER TABLE BarCodeNbr DROP COLUMN tenant_id;
--   ALTER TABLE wms_rwzp   DROP INDEX uk_wms_rwzp_tenant_org_task;
--   ALTER TABLE wms_rwzp   DROP COLUMN tenant_id;
-- ============================================================================