-- 修复订单交付列表相关表的字符集校对问题
-- 将所有涉及 JOIN 的字符串字段统一为 utf8mb4_general_ci

USE aidopdev;

-- 1. crm_seorder 表
ALTER TABLE crm_seorder 
    MODIFY COLUMN bill_no VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    MODIFY COLUMN custom_no VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

-- 2. crm_seorderentry 表
ALTER TABLE crm_seorderentry 
    MODIFY COLUMN item_number VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

-- 3. mes_moentry 表
ALTER TABLE mes_moentry 
    MODIFY COLUMN moentry_mono VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

-- 4. mes_morder 表
ALTER TABLE mes_morder 
    MODIFY COLUMN morder_no VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    MODIFY COLUMN product_code VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

-- 5. ASNBOLShipperDetail 表
ALTER TABLE ASNBOLShipperDetail 
    MODIFY COLUMN OrdNbr VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    MODIFY COLUMN ContainerItem VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

-- 6. NbrMaster 表（如果存在）
SET @sql_nbrmaster = (SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE NbrMaster MODIFY COLUMN WorkOrd VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL',
    'SELECT "NbrMaster table or WorkOrd column not found" AS info'
) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'NbrMaster' 
  AND COLUMN_NAME = 'WorkOrd');

PREPARE stmt FROM @sql_nbrmaster;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. PeriodSequenceDet 表
ALTER TABLE PeriodSequenceDet 
    MODIFY COLUMN WorkOrds VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

-- 8. YearDemandManagement 表（如果存在）
SET @sql_yeardemand = (SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE YearDemandManagement MODIFY COLUMN SAPItemNumber VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL',
    'SELECT "YearDemandManagement table or SAPItemNumber column not found" AS info'
) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'YearDemandManagement' 
  AND COLUMN_NAME = 'SAPItemNumber');

PREPARE stmt FROM @sql_yeardemand;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 9. srm_pr_main 表（如果存在）
SET @sql_srmpr = (SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE srm_pr_main MODIFY COLUMN pr_mono VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL',
    'SELECT "srm_pr_main table or pr_mono column not found" AS info'
) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'srm_pr_main' 
  AND COLUMN_NAME = 'pr_mono');

PREPARE stmt FROM @sql_srmpr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 10. NbrDetail 表（如果存在）
SET @sql_nbrdetail = (SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE NbrDetail MODIFY COLUMN WorkOrd VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL',
    'SELECT "NbrDetail table or WorkOrd column not found" AS info'
) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'NbrDetail' 
  AND COLUMN_NAME = 'WorkOrd');

PREPARE stmt FROM @sql_nbrdetail;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'Order delivery collations have been fixed successfully' AS result;