-- 1. 增加周/月计划相关字段
ALTER TABLE pro_plan
    ADD COLUMN schedule_cycle VARCHAR(10) DEFAULT NULL COMMENT '计划周期: week=周计划, month=月计划' AFTER plan_type,
    ADD COLUMN plan_year INT DEFAULT NULL COMMENT '计划年份' AFTER schedule_cycle,
    ADD COLUMN week_number INT DEFAULT NULL COMMENT '周号(1-53)' AFTER plan_year,
    ADD COLUMN month_number INT DEFAULT NULL COMMENT '月份(1-12)' AFTER week_number,
    ADD COLUMN total_quantity DECIMAL(18,2) DEFAULT 0 COMMENT '计划总产量' AFTER month_number,
    ADD COLUMN completed_quantity DECIMAL(18,2) DEFAULT 0 COMMENT '已完成产量' AFTER total_quantity;

-- 2. 增加索引
ALTER TABLE pro_plan
    ADD INDEX idx_schedule_cycle (schedule_cycle),
    ADD INDEX idx_year_week (plan_year, week_number),
    ADD INDEX idx_year_month (plan_year, month_number);

-- 3. 增加计划周期字典数据
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
VALUES ('计划周期', 'pro_schedule_cycle', '0', 'admin', NOW(), '生产计划周期类型');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
VALUES 
(1, '周计划', 'week', 'pro_schedule_cycle', '', 'primary', 'N', '0', 'admin', NOW(), '按周制定的生产计划'),
(2, '月计划', 'month', 'pro_schedule_cycle', '', 'success', 'N', '0', 'admin', NOW(), '按月制定的生产计划');

-- 4. 更新计划类型字典，增加"生产计划"类型
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
VALUES (8, '生产计划', 'production', 'pro_plan_type', '', 'warning', 'N', '0', 'admin', NOW(), '周/月生产计划');

-- 5. 新增"周/月计划"菜单 (parent_id=2049 为"计划表"菜单)
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
VALUES ('周/月产量计划', 2049, 2, 'schedule', 'mes/production/plan/schedule', NULL, 1, 0, 'C', '0', '0', 'production:plan:list', '#', 'admin', NOW(), '生产计划周/月视图');

-- 6. 增加产品关联字段（用于动态计算进度）
ALTER TABLE pro_plan
    ADD COLUMN material_id BIGINT DEFAULT NULL COMMENT '产品ID' AFTER completed_quantity,
    ADD COLUMN material_name VARCHAR(200) DEFAULT NULL COMMENT '产品名称' AFTER material_id;

-- 7. 在 pro_plan 表中添加负责人员字段
ALTER TABLE pro_plan ADD COLUMN assigned_user_ids VARCHAR(500) COMMENT '负责人员（多个用逗号分隔，存储用户ID）';

-- 8. 增加计划时间范围字段
ALTER TABLE pro_plan
    ADD COLUMN start_time DATETIME DEFAULT NULL COMMENT '计划开始时间' AFTER assigned_user_ids,
    ADD COLUMN end_time DATETIME DEFAULT NULL COMMENT '计划结束时间' AFTER start_time;

-- 9. 添加时间范围索引以优化查询性能
ALTER TABLE pro_plan
    ADD INDEX idx_time_range (start_time, end_time);