-- 创建赛事级别表
CREATE TABLE `competition_levels` (
  `level_id` int(11) NOT NULL AUTO_INCREMENT,
  `level_name` varchar(50) NOT NULL COMMENT '赛事级别(校级/院级/国家级等)',
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`level_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='赛事级别表';

-- 创建项目生命周期状态表
CREATE TABLE `project_lifecycle_status` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT,
  `status_name` varchar(50) NOT NULL COMMENT '状态名称(预立项/立项/中期/结项)',
  `description` varchar(255) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0 COMMENT '状态顺序',
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目生命周期状态表';

-- 创建统一项目推荐表
CREATE TABLE `project_recommendations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL COMMENT '项目ID',
  `source_event_id` int(11) NOT NULL COMMENT '来源赛事ID',
  `target_event_id` int(11) NOT NULL COMMENT '目标赛事ID',
  `recommender_id` int(11) NOT NULL COMMENT '推荐人ID',
  `recommendation_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '推荐日期',
  `status` enum('待审核','已通过','已拒绝') NOT NULL DEFAULT '待审核' COMMENT '推荐状态',
  `reviewer_id` int(11) DEFAULT NULL COMMENT '审核人ID',
  `review_date` datetime DEFAULT NULL COMMENT '审核日期',
  `review_comments` text DEFAULT NULL COMMENT '审核意见',
  PRIMARY KEY (`id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_source_event` (`source_event_id`),
  KEY `idx_target_event` (`target_event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目推荐表';

-- auto-generated definition  
create table competition_recommendation  
(  
    id                    int auto_increment  
        primary key,  
    pro_id                int                                 not null comment '项目id',  
    pro_event_id          int                                 not null comment '项目赛事id',  
    recommended_event_id  int                                 not null comment '推荐到赛事id',  
    recommendation_status enum ('成功推荐', '未成功推荐')     not null comment '推荐的状态',  
    created_at            timestamp default CURRENT_TIMESTAMP not null comment '创建时间',  
    updated_at            timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'  
)  
    comment '赛事推荐表' charset = utf8mb4;


-- 插入赛事级别数据
INSERT INTO `competition_levels` (`level_id`, `level_name`, `description`) VALUES
(1, '院级', '院系内部举办的赛事'),
(2, '校级', '全校范围内举办的赛事'),
(3, '市级', '市级举办的赛事'),
(4, '省级', '省级举办的赛事'),
(5, '国家级', '国家级赛事');

-- 插入项目生命周期状态数据
INSERT INTO `project_lifecycle_status` (`status_id`, `status_name`, `description`, `order_num`) VALUES
(1, '预立项', '项目初始状态，尚未正式立项', 1),
(2, '立项', '项目已通过初步审核，正式立项', 2),
(3, '中期', '项目处于中期检查阶段', 3),
(4, '结项', '项目已完成全部内容，处于结项状态', 4);


-- 修改赛事表，添加级别和上级赛事字段
ALTER TABLE `pro_events` 
  ADD COLUMN `level_id` int(11) DEFAULT 1 COMMENT '赛事级别ID',
  ADD COLUMN `parent_event_id` int(11) DEFAULT NULL COMMENT '上级赛事ID';

-- 修改项目表，添加生命周期状态
ALTER TABLE `pro_details3`
  ADD COLUMN `lifecycle_status_id` int(11) DEFAULT 1 COMMENT '生命周期状态ID';

-- 优化中期检查表
ALTER TABLE `midterm_check`
  ADD COLUMN `lifecycle_status_id` int(11) DEFAULT 3 COMMENT '对应的生命周期状态ID';
  
  -- 根据部门ID更新赛事级别（添加条件确保只更新有效记录）
UPDATE `pro_events` 
SET `level_id` = CASE 
                  WHEN `dept_id` < 100 THEN 2 -- 校级
                  ELSE 1 -- 院级
                END
WHERE `event_id` > 0;  -- 确保只更新有效记录

-- 推断项目生命周期状态（添加条件确保只更新有效记录）
UPDATE `pro_details3` 
SET `lifecycle_status_id` = CASE 
                            WHEN `isMidTermCheck` = 1 THEN 3 -- 中期
                            WHEN `pro_status` = 2 THEN 2 -- 已推荐，视为已立项
                            ELSE 1 -- 默认预立项
                          END
WHERE `pro_id` > 0;  -- 确保只更新有效记录

-- 从旧推荐表迁移数据到新推荐表
INSERT INTO `project_recommendations` 
  (`project_id`, `source_event_id`, `target_event_id`, `recommender_id`, 
   `recommendation_date`, `status`)
SELECT 
  `pro_id`, 
  `event_id`, 
  COALESCE(`rm_event_id`, 0), 
  `rm_user_id`, 
  `rm_time`, 
  CASE 
    WHEN `rm_status` = 1 THEN '待审核'
    WHEN `rm_status` = 2 THEN '已通过'
    ELSE '待审核'
  END
FROM `pro_rm_event`
WHERE `pro_id` > 0;

-- 从竞赛推荐表迁移数据
INSERT INTO `project_recommendations` 
  (`project_id`, `source_event_id`, `target_event_id`, `recommender_id`, 
   `recommendation_date`, `status`)
SELECT 
  `pro_id`, 
  `pro_event_id`, 
  `recommended_event_id`, 
  0, -- 无法确定推荐人，置为0
  `created_at`, 
  CASE 
    WHEN `recommendation_status` = '成功推荐' THEN '已通过'
    ELSE '待审核'
  END
FROM `competition_recommendation`
WHERE `pro_id` > 0
-- 避免重复记录
AND NOT EXISTS (
  SELECT 1 FROM `project_recommendations` 
  WHERE `project_id` = `competition_recommendation`.`pro_id`
  AND `source_event_id` = `competition_recommendation`.`pro_event_id`
  AND `target_event_id` = `competition_recommendation`.`recommended_event_id`
);




-- 移除现有触发器（如果存在）

DROP TRIGGER IF EXISTS `update_pro_allocation_status`;

DROP TRIGGER IF EXISTS `update_group_id`;

DROP TRIGGER IF EXISTS `delete_pro_pro_details3`;

DROP TRIGGER IF EXISTS `project_status_update`;