-- ========================================
-- DesignHub 数据库结构同步脚本
-- 用途: 自动检测并添加缺失的表和字段
-- 版本: v2.0
-- 生成时间: 2026-01-02
-- ========================================
-- 
-- 使用说明:
-- 1. 此脚本会自动检测并创建缺失的表
-- 2. 自动检测并添加缺失的字段
-- 3. 可以安全地重复执行，不会影响已有数据
--
-- 执行方式:
-- mysql -uroot -p designhub < sync_database_structure.sql
-- 或者在MySQL客户端中执行: source sync_database_structure.sql
--

USE `designhub`;

-- ========================================
-- 第一部分: 确保所有必需的表存在
-- ========================================

-- 如果表不存在，则创建。如果存在，则跳过。
-- 注意：MySQL不支持 CREATE TABLE IF NOT EXISTS 的完整字段检查，
-- 所以这里只创建表的基本结构，字段的添加在第二部分完成

-- ========================================
-- 第二部分: 为每个表添加缺失的字段
-- ========================================

-- 1. sys_user 表字段补全
ALTER TABLE `sys_user` 
    ADD COLUMN IF NOT EXISTS `dept_name` VARCHAR(50) NULL COMMENT '部门名称' AFTER `dept_id`,
    ADD COLUMN IF NOT EXISTS `score` INT DEFAULT 0 COMMENT '用户积分' AFTER `status`;

-- 2. task 表字段补全（所有缺失的字段）
ALTER TABLE `task`
    ADD COLUMN IF NOT EXISTS `third_party_task_no` VARCHAR(100) NULL COMMENT '第三方平台任务编号' AFTER `task_no`,
    ADD COLUMN IF NOT EXISTS `task_type_ids` VARCHAR(255) NULL COMMENT '任务类型ID列表（逗号分隔）' AFTER `task_type_id`,
    ADD COLUMN IF NOT EXISTS `score_reward` DECIMAL(10,2) NULL COMMENT '实际积分奖励' AFTER `score`,
    ADD COLUMN IF NOT EXISTS `estimated_duration` INT NULL COMMENT '预估时长（小时）' AFTER `estimated_hours`,
    ADD COLUMN IF NOT EXISTS `demand_type_id` BIGINT NULL COMMENT '需求类型ID' AFTER `estimated_duration`,
    ADD COLUMN IF NOT EXISTS `actual_start_time` DATETIME NULL COMMENT '实际开始时间' AFTER `start_time`,
    ADD COLUMN IF NOT EXISTS `submit_time` DATETIME NULL COMMENT '提交审核时间' AFTER `complete_time`,
    ADD COLUMN IF NOT EXISTS `actual_end_time` DATETIME NULL COMMENT '实际结束时间' AFTER `submit_time`,
    ADD COLUMN IF NOT EXISTS `priority` INT DEFAULT 1 COMMENT '优先级：0-低 1-普通 2-高 3-紧急' AFTER `actual_end_time`,
    ADD COLUMN IF NOT EXISTS `remark` VARCHAR(500) NULL COMMENT '备注信息' AFTER `priority`,
    ADD COLUMN IF NOT EXISTS `is_pipeline` TINYINT DEFAULT 0 COMMENT '是否流水线任务：0-否，1-是' AFTER `delay_days`,
    ADD COLUMN IF NOT EXISTS `pipeline_mode` VARCHAR(20) NULL COMMENT '流水线模式：sequential(顺序)/parallel(并行)' AFTER `is_pipeline`,
    ADD COLUMN IF NOT EXISTS `approval_status` VARCHAR(20) DEFAULT 'draft' COMMENT '审核状态：draft-草稿 pending_demand_approval-待需求审核 pending_receiving_approval-待接单审核 approved-已通过 rejected-已驳回' AFTER `audit_status`,
    ADD COLUMN IF NOT EXISTS `current_approver_id` BIGINT NULL COMMENT '当前审核人ID' AFTER `approval_status`,
    ADD COLUMN IF NOT EXISTS `current_approver_role` VARCHAR(50) NULL COMMENT '当前审核人角色' AFTER `current_approver_id`,
    ADD COLUMN IF NOT EXISTS `demand_approval_time` DATETIME NULL COMMENT '需求部门审核时间' AFTER `current_approver_role`,
    ADD COLUMN IF NOT EXISTS `receiving_approval_time` DATETIME NULL COMMENT '接单部门审核时间' AFTER `demand_approval_time`;

-- 3. task_type 表字段补全
ALTER TABLE `task_type`
    ADD COLUMN IF NOT EXISTS `allowed_dept_ids` VARCHAR(255) NULL COMMENT '可接单部门ID列表（逗号分隔）' AFTER `description`;

-- 4. category 表字段补全
ALTER TABLE `category`
    ADD COLUMN IF NOT EXISTS `description` VARCHAR(500) NULL COMMENT '描述' AFTER `is_leaf`;

-- 5. task_sub_type 表字段重命名和补全
-- 注意：如果字段名不一致，需要先检查并重命名
-- 这里使用存储过程来处理字段重命名（如果存在旧字段名）
ALTER TABLE `task_sub_type`
    ADD COLUMN IF NOT EXISTS `name` VARCHAR(100) NULL COMMENT '子类型名称' AFTER `id`,
    ADD COLUMN IF NOT EXISTS `code` VARCHAR(50) NULL COMMENT '子类型编码' AFTER `name`,
    ADD COLUMN IF NOT EXISTS `is_enabled` TINYINT DEFAULT 1 COMMENT '是否启用：0-禁用 1-启用' AFTER `code`;

-- 6. team_score_pool 表字段补全
ALTER TABLE `team_score_pool`
    ADD COLUMN IF NOT EXISTS `dept_id` BIGINT NULL COMMENT '部门ID（与team_id相同）' AFTER `id`,
    ADD COLUMN IF NOT EXISTS `total_recharge` INT DEFAULT 0 COMMENT '累计充值积分' AFTER `balance`,
    ADD COLUMN IF NOT EXISTS `total_consume` INT DEFAULT 0 COMMENT '累计消耗积分' AFTER `total_recharge`,
    ADD COLUMN IF NOT EXISTS `total_earn` INT DEFAULT 0 COMMENT '累计获得积分' AFTER `total_consume`,
    ADD COLUMN IF NOT EXISTS `period_limit` INT DEFAULT 0 COMMENT '周期限额' AFTER `monthly_used`,
    ADD COLUMN IF NOT EXISTS `period_used` INT DEFAULT 0 COMMENT '当前周期已消耗' AFTER `period_limit`,
    ADD COLUMN IF NOT EXISTS `period_start_time` DATETIME NULL COMMENT '当前周期开始时间' AFTER `period_used`,
    ADD COLUMN IF NOT EXISTS `alert_threshold` INT DEFAULT 80 COMMENT '预警阈值（百分比）' AFTER `monthly_used`,
    ADD COLUMN IF NOT EXISTS `limit_enabled` TINYINT DEFAULT 0 COMMENT '是否启用限制：0-否 1-是' AFTER `alert_threshold`,
    ADD COLUMN IF NOT EXISTS `limit_period` VARCHAR(20) DEFAULT 'month' COMMENT '限制周期：day/week/month' AFTER `limit_enabled`;

-- 7. achievement 表字段补全
ALTER TABLE `achievement`
    ADD COLUMN IF NOT EXISTS `achievement_code` VARCHAR(50) NULL COMMENT '成就代码' AFTER `id`,
    ADD COLUMN IF NOT EXISTS `achievement_name` VARCHAR(100) NULL COMMENT '成就名称' AFTER `achievement_code`;

-- 8. user_achievement 表字段补全
ALTER TABLE `user_achievement`
    ADD COLUMN IF NOT EXISTS `progress_value` INT NULL COMMENT '当前进度值' AFTER `achievement_id`,
    ADD COLUMN IF NOT EXISTS `reward_claimed` TINYINT DEFAULT 0 COMMENT '奖励是否已领取：0-否 1-是' AFTER `progress_value`,
    ADD COLUMN IF NOT EXISTS `claim_time` DATETIME NULL COMMENT '领取时间' AFTER `reward_claimed`;

-- ========================================
-- 第三部分: 创建缺失的表（如果不存在）
-- ========================================

-- 这里使用存储过程来检查表是否存在，如果不存在则创建
-- 由于MySQL的限制，我们需要使用不同的方法

-- 注意：由于MySQL不支持 ALTER TABLE ADD COLUMN IF NOT EXISTS 和 CREATE TABLE IF NOT EXISTS 的完整功能，
-- 上面的脚本在实际执行时可能会报错。我们需要使用存储过程或应用程序来处理这个问题。

-- ========================================
-- 第四部分: 创建存储过程来安全地添加字段
-- ========================================

DELIMITER $$

-- 存储过程：安全地添加列（如果不存在）
DROP PROCEDURE IF EXISTS AddColumnIfNotExists $$
CREATE PROCEDURE AddColumnIfNotExists(
    IN tableName VARCHAR(64),
    IN columnName VARCHAR(64),
    IN columnDefinition TEXT
)
BEGIN
    DECLARE columnExists INT DEFAULT 0;
    
    SELECT COUNT(*) INTO columnExists
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = tableName
      AND COLUMN_NAME = columnName;
    
    IF columnExists = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', tableName, '` ADD COLUMN `', columnName, '` ', columnDefinition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

-- ========================================
-- 第五部分: 使用存储过程添加所有缺失的字段
-- ========================================

-- sys_user 表
CALL AddColumnIfNotExists('sys_user', 'dept_name', 'VARCHAR(50) NULL COMMENT ''部门名称'' AFTER `dept_id`');
CALL AddColumnIfNotExists('sys_user', 'score', 'INT DEFAULT 0 COMMENT ''用户积分'' AFTER `status`');

-- task 表
CALL AddColumnIfNotExists('task', 'third_party_task_no', 'VARCHAR(100) NULL COMMENT ''第三方平台任务编号'' AFTER `task_no`');
CALL AddColumnIfNotExists('task', 'task_type_ids', 'VARCHAR(255) NULL COMMENT ''任务类型ID列表（逗号分隔）'' AFTER `task_type_id`');
CALL AddColumnIfNotExists('task', 'score_reward', 'DECIMAL(10,2) NULL COMMENT ''实际积分奖励'' AFTER `score`');
CALL AddColumnIfNotExists('task', 'estimated_duration', 'INT NULL COMMENT ''预估时长（小时）'' AFTER `estimated_hours`');
CALL AddColumnIfNotExists('task', 'demand_type_id', 'BIGINT NULL COMMENT ''需求类型ID'' AFTER `estimated_duration`');
CALL AddColumnIfNotExists('task', 'actual_start_time', 'DATETIME NULL COMMENT ''实际开始时间'' AFTER `start_time`');
CALL AddColumnIfNotExists('task', 'submit_time', 'DATETIME NULL COMMENT ''提交审核时间'' AFTER `complete_time`');
CALL AddColumnIfNotExists('task', 'actual_end_time', 'DATETIME NULL COMMENT ''实际结束时间'' AFTER `submit_time`');
CALL AddColumnIfNotExists('task', 'priority', 'INT DEFAULT 1 COMMENT ''优先级：0-低 1-普通 2-高 3-紧急'' AFTER `actual_end_time`');
CALL AddColumnIfNotExists('task', 'remark', 'VARCHAR(500) NULL COMMENT ''备注信息'' AFTER `priority`');
CALL AddColumnIfNotExists('task', 'is_pipeline', 'TINYINT DEFAULT 0 COMMENT ''是否流水线任务：0-否，1-是'' AFTER `delay_days`');
CALL AddColumnIfNotExists('task', 'pipeline_mode', 'VARCHAR(20) NULL COMMENT ''流水线模式：sequential(顺序)/parallel(并行)'' AFTER `is_pipeline`');
CALL AddColumnIfNotExists('task', 'approval_status', 'VARCHAR(20) DEFAULT ''draft'' COMMENT ''审核状态：draft-草稿 pending_demand_approval-待需求审核 pending_receiving_approval-待接单审核 approved-已通过 rejected-已驳回'' AFTER `audit_status`');
CALL AddColumnIfNotExists('task', 'current_approver_id', 'BIGINT NULL COMMENT ''当前审核人ID'' AFTER `approval_status`');
CALL AddColumnIfNotExists('task', 'current_approver_role', 'VARCHAR(50) NULL COMMENT ''当前审核人角色'' AFTER `current_approver_id`');
CALL AddColumnIfNotExists('task', 'demand_approval_time', 'DATETIME NULL COMMENT ''需求部门审核时间'' AFTER `current_approver_role`');
CALL AddColumnIfNotExists('task', 'receiving_approval_time', 'DATETIME NULL COMMENT ''接单部门审核时间'' AFTER `demand_approval_time`');

-- task_type 表
CALL AddColumnIfNotExists('task_type', 'allowed_dept_ids', 'VARCHAR(255) NULL COMMENT ''可接单部门ID列表（逗号分隔）'' AFTER `description`');

-- category 表
CALL AddColumnIfNotExists('category', 'description', 'VARCHAR(500) NULL COMMENT ''描述'' AFTER `is_leaf`');

-- team_score_pool 表
CALL AddColumnIfNotExists('team_score_pool', 'dept_id', 'BIGINT NULL COMMENT ''部门ID（与team_id相同）'' AFTER `id`');
CALL AddColumnIfNotExists('team_score_pool', 'total_recharge', 'INT DEFAULT 0 COMMENT ''累计充值积分'' AFTER `balance`');
CALL AddColumnIfNotExists('team_score_pool', 'total_consume', 'INT DEFAULT 0 COMMENT ''累计消耗积分'' AFTER `total_recharge`');
CALL AddColumnIfNotExists('team_score_pool', 'total_earn', 'INT DEFAULT 0 COMMENT ''累计获得积分'' AFTER `total_consume`');
CALL AddColumnIfNotExists('team_score_pool', 'period_limit', 'INT DEFAULT 0 COMMENT ''周期限额'' AFTER `monthly_used`');
CALL AddColumnIfNotExists('team_score_pool', 'period_used', 'INT DEFAULT 0 COMMENT ''当前周期已消耗'' AFTER `period_limit`');
CALL AddColumnIfNotExists('team_score_pool', 'period_start_time', 'DATETIME NULL COMMENT ''当前周期开始时间'' AFTER `period_used`');
CALL AddColumnIfNotExists('team_score_pool', 'alert_threshold', 'INT DEFAULT 80 COMMENT ''预警阈值（百分比）'' AFTER `monthly_used`');
CALL AddColumnIfNotExists('team_score_pool', 'limit_enabled', 'TINYINT DEFAULT 0 COMMENT ''是否启用限制：0-否 1-是'' AFTER `alert_threshold`');
CALL AddColumnIfNotExists('team_score_pool', 'limit_period', 'VARCHAR(20) DEFAULT ''month'' COMMENT ''限制周期：day/week/month'' AFTER `limit_enabled`');

-- achievement 表
CALL AddColumnIfNotExists('achievement', 'achievement_code', 'VARCHAR(50) NULL COMMENT ''成就代码'' AFTER `id`');
CALL AddColumnIfNotExists('achievement', 'achievement_name', 'VARCHAR(100) NULL COMMENT ''成就名称'' AFTER `achievement_code`');

-- user_achievement 表
CALL AddColumnIfNotExists('user_achievement', 'progress_value', 'INT NULL COMMENT ''当前进度值'' AFTER `achievement_id`');
CALL AddColumnIfNotExists('user_achievement', 'reward_claimed', 'TINYINT DEFAULT 0 COMMENT ''奖励是否已领取：0-否 1-是'' AFTER `progress_value`');
CALL AddColumnIfNotExists('user_achievement', 'claim_time', 'DATETIME NULL COMMENT ''领取时间'' AFTER `reward_claimed`');

-- ========================================
-- 第六部分: 清理存储过程
-- ========================================

DROP PROCEDURE IF EXISTS AddColumnIfNotExists;

-- ========================================
-- 完成提示
-- ========================================

SELECT '数据库结构同步完成！' AS message;