-- ==========================================
-- 第一部分：DDL - 表结构修改
-- ==========================================

-- 1. 工序路线主表 (pro_route) 扩展
-- 新增字段：制造类型（制造类型直接决定时间计算模式）
ALTER TABLE `pro_route` 
ADD COLUMN `manufacture_type` VARCHAR(20) DEFAULT 'DISCRETE' 
    COMMENT '制造类型: DISCRETE=离散制造(顺序推进), CONTINUOUS=连续制造(同步开工)' 
    AFTER `status`;

-- 2. 工序路线明细表 (pro_route_process) 扩展
-- 新增字段：转运时间（离散制造）和等待开始时间（连续制造）
ALTER TABLE `pro_route_process` 
ADD COLUMN `transfer_time` BIGINT DEFAULT 0 
    COMMENT '转运时间（秒），离散制造时从上一工序到本工序的转运耗时' 
    AFTER `duration`,
ADD COLUMN `wait_start_time` BIGINT DEFAULT 0 
    COMMENT '等待开始时间（秒），连续制造时从生产起始时间的延迟' 
    AFTER `transfer_time`;

-- ==========================================
-- 第二部分：DML - 数据迁移
-- ==========================================

-- 3. 为现有工序路线设置默认值（离散制造）
UPDATE `pro_route` 
SET `manufacture_type` = 'DISCRETE' 
WHERE `manufacture_type` IS NULL;

-- 4. 为现有工序明细设置默认转运时间和等待开始时间为0
UPDATE `pro_route_process` 
SET `transfer_time` = 0,
    `wait_start_time` = 0
WHERE `transfer_time` IS NULL 
   OR `wait_start_time` IS NULL;

-- ==========================================
-- 第三部分：数据验证查询
-- ==========================================

-- 5. 验证工序路线表新增字段
SELECT 
    COUNT(*) AS total_routes,
    SUM(CASE WHEN manufacture_type = 'DISCRETE' THEN 1 ELSE 0 END) AS discrete_count,
    SUM(CASE WHEN manufacture_type = 'CONTINUOUS' THEN 1 ELSE 0 END) AS continuous_count
FROM `pro_route`;

-- 6. 验证工序明细表新增字段
SELECT 
    COUNT(*) AS total_processes,
    SUM(CASE WHEN transfer_time = 0 THEN 1 ELSE 0 END) AS zero_transfer_time,
    SUM(CASE WHEN transfer_time > 0 THEN 1 ELSE 0 END) AS has_transfer_time,
    SUM(CASE WHEN wait_start_time = 0 THEN 1 ELSE 0 END) AS zero_wait_time,
    SUM(CASE WHEN wait_start_time > 0 THEN 1 ELSE 0 END) AS has_wait_time,
    AVG(transfer_time) AS avg_transfer_time,
    MAX(transfer_time) AS max_transfer_time,
    AVG(wait_start_time) AS avg_wait_time,
    MAX(wait_start_time) AS max_wait_time
FROM `pro_route_process`;

-- 7. 查看表结构变化（可选）
-- SHOW FULL COLUMNS FROM `pro_route`;
-- SHOW FULL COLUMNS FROM `pro_route_process`;

-- ==========================================
-- 第四部分：回滚脚本（如需要）
-- ==========================================

/*
-- 警告：此部分仅用于回滚，会删除新增的字段和数据
-- 请谨慎执行！

-- 回滚工序路线主表
ALTER TABLE `pro_route` 
DROP COLUMN `manufacture_type`;

-- 回滚工序明细表
ALTER TABLE `pro_route_process` 
DROP COLUMN `transfer_time`,
DROP COLUMN `wait_start_time`;
*/

-- ==========================================
-- 执行说明
-- ==========================================
-- 1. 建议在测试环境先执行验证
-- 2. 生产环境执行前务必备份数据库
-- 3. 分步执行，每步验证无误后再继续
-- 4. 执行第三部分（验证查询）确认数据正确性
-- 5. 如需回滚，使用第五部分的回滚脚本
-- ==========================================