AiDOPWarehouse/tools/sql/add_all_tenant_id_fields.sql

-- ========================================================================
-- 完整的租户ID字段迁移脚本（包含所有业务表）
-- 生成时间：2026-04-22
-- 适用于：MySQL 5.7+ / 8.0+
-- 包含：Order、WorkOrder、Production 模块的所有业务表
-- ========================================================================

USE aidopdev;

-- ========================================================================
-- 第一部分：Order 模块业务表
-- ========================================================================

-- 1. 合同评审主表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ado_contract_review ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ado_contract_review.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ado_contract_review' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 合同评审流程节点表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ado_contract_review_flow ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ado_contract_review_flow.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ado_contract_review_flow' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 计划联动看板
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE LinkagePlan ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''LinkagePlan.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'LinkagePlan' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. 产品设计主表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ProductDesign ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ProductDesign.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ProductDesign' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. 产品设计BOM表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ProductDesignBom ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ProductDesignBom.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ProductDesignBom' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. 产品设计工艺表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ProductDesignRouting ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ProductDesignRouting.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ProductDesignRouting' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. 销售订单明细表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE crm_seorderentry ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''crm_seorderentry.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'crm_seorderentry' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 8. 发货计划主表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ShippingPlan ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ShippingPlan.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ShippingPlan' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 9. 发货计划明细表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ShippingPlanDetail ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ShippingPlanDetail.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ShippingPlanDetail' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 10. ASN发货单主表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE AsnShipperMaster ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''AsnShipperMaster.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'AsnShipperMaster' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 11. ASN发货单明细表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE AsnShipperDetail ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''AsnShipperDetail.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'AsnShipperDetail' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================================================
-- 第二部分：WorkOrder 模块业务表
-- ========================================================================

-- 12. 工单主数据
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE WorkOrdMaster ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''WorkOrdMaster.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'WorkOrdMaster' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 13. 工单物料明细
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE WorkOrdDetail ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''WorkOrdDetail.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'WorkOrdDetail' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 14. 工单工序表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE WorkOrdRouting ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''WorkOrdRouting.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'WorkOrdRouting' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 15. 工单附属表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE mes_moentry ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''mes_moentry.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'mes_moentry' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================================================
-- 第三部分：Production 模块业务表
-- ========================================================================

-- 16. 排产异常日志
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ScheduleExceptionMaster ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ScheduleExceptionMaster.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ScheduleExceptionMaster' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 17. 工序间衔接
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE PeriodSequenceDet ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''PeriodSequenceDet.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'PeriodSequenceDet' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 18. 排产结果
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ScheduleResultOpMaster ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ScheduleResultOpMaster.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ScheduleResultOpMaster' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 19. 产线工作日历表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ShopCalendarWorkCtr ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ShopCalendarWorkCtr.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ShopCalendarWorkCtr' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 20. 产线休息时间表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE QualityLineWorkDetail ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''QualityLineWorkDetail.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'QualityLineWorkDetail' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 21. 产线节假日表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE HolidayMaster ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''HolidayMaster.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'HolidayMaster' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 22. 产线加班表
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ResourceOccupancyTime ADD COLUMN tenant_id BIGINT NULL COMMENT ''租户ID（多租户隔离）''',
    'SELECT ''ResourceOccupancyTime.tenant_id already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' AND TABLE_NAME = 'ResourceOccupancyTime' AND COLUMN_NAME = 'tenant_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================================================
-- 第四部分：创建租户ID索引（提升查询性能）
-- ========================================================================

-- Order 模块索引
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ado_contract_review(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ado_contract_review_flow(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON LinkagePlan(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ProductDesign(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ProductDesignBom(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ProductDesignRouting(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON crm_seorder(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON crm_seorderentry(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ShippingPlan(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ShippingPlanDetail(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON AsnShipperMaster(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON AsnShipperDetail(tenant_id);

-- WorkOrder 模块索引
CREATE INDEX IF NOT EXISTS idx_tenant_id ON WorkOrdMaster(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON WorkOrdDetail(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON WorkOrdRouting(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON mes_morder(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON mes_moentry(tenant_id);

-- Production 模块索引
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ScheduleExceptionMaster(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON PeriodSequenceDet(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ScheduleResultOpMaster(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ShopCalendarWorkCtr(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON QualityLineWorkDetail(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON HolidayMaster(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_id ON ResourceOccupancyTime(tenant_id);

-- ========================================================================
-- 第五部分：验证结果
-- ========================================================================

SELECT '================ 租户ID字段检查 ================' AS 检查项;

SELECT 
    TABLE_NAME AS 表名,
    COLUMN_NAME AS 字段名,
    COLUMN_TYPE AS 字段类型,
    IS_NULLABLE AS 是否可空,
    COLUMN_COMMENT AS 注释
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND COLUMN_NAME = 'tenant_id'
ORDER BY TABLE_NAME;

SELECT '================ 租户ID索引检查 ================' AS 检查项;

SELECT 
    TABLE_NAME AS 表名,
    INDEX_NAME AS 索引名,
    COLUMN_NAME AS 字段名,
    NON_UNIQUE AS 非唯一
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND COLUMN_NAME = 'tenant_id'
  AND INDEX_NAME = 'idx_tenant_id'
ORDER BY TABLE_NAME;

-- ========================================================================
-- 完成！
-- ========================================================================
SELECT '✅ 所有业务表 tenant_id 字段添加完成！' AS 状态;
SELECT CONCAT('共添加 ', COUNT(DISTINCT TABLE_NAME), ' 张表的 tenant_id 字段') AS 统计
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND COLUMN_NAME = 'tenant_id';