AiDOPWarehouse/tools/sql/fix_all_collations.sql

-- 统一修复所有 collation 冲突问题
-- 将 utf8mb4_0900_ai_ci 统一改为 utf8mb4_general_ci

USE aidopdev;

-- ═══════════════════════════════════════════════════════════
-- 1. 修复 ResourceOccupancyTime 表（产线加班）
-- ═══════════════════════════════════════════════════════════
SELECT '修复 ResourceOccupancyTime 表' AS 步骤;

ALTER TABLE ResourceOccupancyTime 
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Site VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Resource VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY ResourceType VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Descr VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Ufld1 VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Ufld2 VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY CreateUser VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY UpdateUser VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- ═══════════════════════════════════════════════════════════
-- 2. 验证修改结果
-- ═══════════════════════════════════════════════════════════
SELECT '验证修改结果' AS 步骤;

-- 检查 ResourceOccupancyTime
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'ResourceOccupancyTime'
  AND COLLATION_NAME IS NOT NULL
ORDER BY COLUMN_NAME;

-- ═══════════════════════════════════════════════════════════
-- 3. 测试产线加班列表的 JOIN（ResourceOccupancyTime + LineMaster）
-- ═══════════════════════════════════════════════════════════
SELECT '测试产线加班 JOIN' AS 步骤;

SELECT COUNT(*) AS test_overtime_join
FROM ResourceOccupancyTime r
LEFT JOIN LineMaster lm
  ON r.Domain = lm.Domain AND r.Resource = lm.Line
WHERE r.IsActive = 1
LIMIT 1;

SELECT '全部修复完成！' AS 状态;