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

-- =============================================================================
-- 版本：1.0.208
-- 目的：为生产排程链路关键表添加 tenant_id 索引，提升多租户查询性能
-- 影响表：WorkOrdRouting / PeriodSequenceDet / ShopCalendarWorkCtr /
--         WorkOrdMaster / ScheduleExceptionMaster / ScheduleResultOpMaster
-- 幂等：通过 information_schema 检查索引是否已存在，重复执行安全
-- 兼容：MySQL 5.x / 8.x 通用（PREPARE/EXECUTE 模式，无需 DELIMITER）
-- =============================================================================

SET NAMES utf8mb4;
SET @db = DATABASE();

-- 1. WorkOrdRouting(tenant_id, WorkOrd) 复合索引
SET @idx = 'idx_workordrouting_tenant';
SET @tbl = 'WorkOrdRouting';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id, WorkOrd)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. PeriodSequenceDet(tenant_id) 单列索引
SET @idx = 'idx_periodseq_tenant';
SET @tbl = 'PeriodSequenceDet';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3. PeriodSequenceDet(tenant_id, WorkOrds) 复合索引 — 用于 DeactivateExistingScheduleAsync
SET @idx = 'idx_periodseq_deactivate';
SET @tbl = 'PeriodSequenceDet';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id, WorkOrds)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4. ShopCalendarWorkCtr(tenant_id)
SET @idx = 'idx_shopcalendar_tenant';
SET @tbl = 'ShopCalendarWorkCtr';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5. WorkOrdMaster(tenant_id)
SET @idx = 'idx_workordmaster_tenant';
SET @tbl = 'WorkOrdMaster';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6. ScheduleExceptionMaster(tenant_id)
SET @idx = 'idx_scheduleexception_tenant';
SET @tbl = 'ScheduleExceptionMaster';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7. ScheduleResultOpMaster(tenant_id)
SET @idx = 'idx_scheduleresultop_tenant';
SET @tbl = 'ScheduleResultOpMaster';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(tenant_id)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 8. ScheduleResultOpMaster(WorkOrd, Op) — 高频 JOIN 键
SET @idx = 'idx_scheduleresultop_workord';
SET @tbl = 'ScheduleResultOpMaster';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(WorkOrd, Op)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;