-- =============================================
-- 生产订单、排产、报工优化 - 数据库变更脚本
-- 执行时间：2024-01-15
-- 说明：新增任务状态扩展字段、工单工艺路线关联字段
-- =============================================

-- =============================================
-- 1. 生产任务表新增字段
-- =============================================
ALTER TABLE mes_pro_task
    ADD COLUMN IF NOT EXISTS first_feedback_time datetime COMMENT '首次报工时间' AFTER cancel_date,
    ADD COLUMN IF NOT EXISTS last_feedback_time datetime COMMENT '最近报工时间' AFTER first_feedback_time;

-- =============================================
-- 2. 生产工单表新增字段
-- =============================================
ALTER TABLE mes_pro_work_order
    ADD COLUMN IF NOT EXISTS route_id bigint COMMENT '工艺路线编号' AFTER batch_code,
    ADD COLUMN IF NOT EXISTS auto_finish_flag tinyint(1) DEFAULT 1 COMMENT '是否自动完成（报工达标后自动完成工单）' AFTER route_id;

-- =============================================
-- 3. 补充索引
-- =============================================
ALTER TABLE mes_pro_task ADD INDEX IF NOT EXISTS idx_work_order_process (work_order_id, process_id);

-- =============================================
-- 4. 字典数据变更 - 任务状态枚举扩展
-- =============================================
-- 新增「生产中」状态
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted)
SELECT 1, '生产中', '1', 'mes_pro_task_status', 0, 'processing', '', '任务正在生产中', 'admin', NOW(), 'admin', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_pro_task_status' AND value = '1');

-- 新增「部分完成」状态
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted)
SELECT 2, '部分完成', '2', 'mes_pro_task_status', 0, 'warning', '', '任务部分完成', 'admin', NOW(), 'admin', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_pro_task_status' AND value = '2');

-- 更新原有枚举的 sort
UPDATE system_dict_data SET sort = 0 WHERE dict_type = 'mes_pro_task_status' AND value = '0' AND deleted = 0;
UPDATE system_dict_data SET sort = 3 WHERE dict_type = 'mes_pro_task_status' AND value = '3' AND deleted = 0;
UPDATE system_dict_data SET sort = 4 WHERE dict_type = 'mes_pro_task_status' AND value = '4' AND deleted = 0;