AiDOPWarehouse/tools/sql/add_tenant_id_to_new_entities.sql

-- ========================================================================
-- AiDOP插件新增实体表的 tenant_id 字段迁移
-- 生成时间：2026-04-22
-- 涉及表：WorkOrder 和 Production 模块的主数据表
-- ========================================================================

USE aidopdev;

-- ========================================================================
-- WorkOrder 模块业务表
-- ========================================================================

-- 1. 工单主数据
ALTER TABLE WorkOrdMaster
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 2. 工单物料明细
ALTER TABLE WorkOrdDetail
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 3. 工单工序表
ALTER TABLE WorkOrdRouting
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 4. 工单信息表（mes_morder 已有 tenant_id，跳过）
-- ALTER TABLE mes_morder ADD COLUMN IF NOT EXISTS tenant_id ...

-- 5. 工单附属表
ALTER TABLE mes_moentry
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- ========================================================================
-- Production 模块业务表
-- ========================================================================

-- 6. 工序间衔接
ALTER TABLE PeriodSequenceDet
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 7. 排产结果
ALTER TABLE ScheduleResultOpMaster
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 8. 产线工作日历表（ShopCalendarWorkCtr 已处理，检查是否需要添加）
ALTER TABLE ShopCalendarWorkCtr
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 9. 产线休息时间表
ALTER TABLE QualityLineWorkDetail
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 10. 产线节假日表（HolidayMaster 已处理）
-- ALTER TABLE HolidayMaster ADD COLUMN IF NOT EXISTS tenant_id ...

-- 11. 产线加班表
ALTER TABLE ResourceOccupancyTime
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- ========================================================================
-- 创建租户ID索引（提升查询性能）
-- ========================================================================

SET @db = 'aidopdev';

-- WorkOrder 模块索引
SET @table = 'WorkOrdMaster';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'WorkOrdDetail';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'WorkOrdRouting';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'mes_moentry';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Production 模块索引
SET @table = 'PeriodSequenceDet';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ScheduleResultOpMaster';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ShopCalendarWorkCtr';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'QualityLineWorkDetail';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ResourceOccupancyTime';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================================================
-- 验证：检查所有新增表的 tenant_id 字段
-- ========================================================================

SELECT '新增实体表 tenant_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'
  AND TABLE_NAME IN (
      'WorkOrdMaster',
      'WorkOrdDetail',
      'WorkOrdRouting',
      'mes_moentry',
      'PeriodSequenceDet',
      'ScheduleResultOpMaster',
      'ShopCalendarWorkCtr',
      'QualityLineWorkDetail',
      'ResourceOccupancyTime'
  )
ORDER BY TABLE_NAME;

-- ========================================================================
-- 验证：检查索引创建情况
-- ========================================================================

SELECT '新增实体表 tenant_id 索引检查' AS 检查项;

SELECT 
    TABLE_NAME AS 表名,
    INDEX_NAME AS 索引名,
    COLUMN_NAME AS 字段名,
    SEQ_IN_INDEX AS 序号
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND COLUMN_NAME = 'tenant_id'
  AND INDEX_NAME = 'idx_tenant_id'
  AND TABLE_NAME IN (
      'WorkOrdMaster',
      'WorkOrdDetail',
      'WorkOrdRouting',
      'mes_moentry',
      'PeriodSequenceDet',
      'ScheduleResultOpMaster',
      'ShopCalendarWorkCtr',
      'QualityLineWorkDetail',
      'ResourceOccupancyTime'
  )
ORDER BY TABLE_NAME;

-- ========================================================================
-- 完成！
-- ========================================================================
SELECT '✅ 新增实体表 tenant_id 字段添加完成！' AS 状态;