-- ============================================
-- 为所有工资表添加员工确认字段（安全版）
-- 功能：支持员工确认工资条，确认后工资数据不可修改
-- 创建时间：2025年
-- 说明：此版本会先检查字段是否存在，避免重复添加
-- ============================================

-- 说明：lq_employee_salary_statistics 表在创建时已包含这些字段，无需添加

-- 使用存储过程安全添加字段（如果字段不存在则添加）
DELIMITER $$

DROP PROCEDURE IF EXISTS AddEmployeeConfirmFields$$

CREATE PROCEDURE AddEmployeeConfirmFields()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(100);
    DECLARE tableCursor CURSOR FOR 
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME IN (
            'lq_salary_statistics',
            'lq_tech_teacher_salary_statistics',
            'lq_assistant_salary_statistics',
            'lq_store_manager_salary_statistics',
            'lq_director_salary_statistics',
            'lq_major_project_teacher_salary_statistics',
            'lq_major_project_director_salary_statistics',
            'lq_tech_general_manager_salary_statistics',
            'lq_business_unit_manager_salary_statistics'
        );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN tableCursor;
    read_loop: LOOP
        FETCH tableCursor INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 检查并添加 F_EmployeeConfirmStatus
        SET @sql = CONCAT('SELECT COUNT(*) INTO @colCount FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = ''', tableName, ''' 
            AND COLUMN_NAME = ''F_EmployeeConfirmStatus''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @colCount = 0 THEN
            SET @sql = CONCAT('ALTER TABLE ', tableName, ' 
                ADD COLUMN F_EmployeeConfirmStatus INT NOT NULL DEFAULT 0 COMMENT ''员工确认状态（0=未确认，1=已确认）''');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT CONCAT('已为表 ', tableName, ' 添加字段 F_EmployeeConfirmStatus') AS result;
        END IF;

        -- 检查并添加 F_EmployeeConfirmTime
        SET @sql = CONCAT('SELECT COUNT(*) INTO @colCount FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = ''', tableName, ''' 
            AND COLUMN_NAME = ''F_EmployeeConfirmTime''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @colCount = 0 THEN
            SET @sql = CONCAT('ALTER TABLE ', tableName, ' 
                ADD COLUMN F_EmployeeConfirmTime DATETIME NULL COMMENT ''员工确认时间''');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT CONCAT('已为表 ', tableName, ' 添加字段 F_EmployeeConfirmTime') AS result;
        END IF;

        -- 检查并添加 F_EmployeeConfirmRemark
        SET @sql = CONCAT('SELECT COUNT(*) INTO @colCount FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = ''', tableName, ''' 
            AND COLUMN_NAME = ''F_EmployeeConfirmRemark''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @colCount = 0 THEN
            SET @sql = CONCAT('ALTER TABLE ', tableName, ' 
                ADD COLUMN F_EmployeeConfirmRemark VARCHAR(500) NULL COMMENT ''员工确认备注''');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT CONCAT('已为表 ', tableName, ' 添加字段 F_EmployeeConfirmRemark') AS result;
        END IF;

    END LOOP;
    CLOSE tableCursor;
END$$

DELIMITER ;

-- 执行存储过程
CALL AddEmployeeConfirmFields();

-- 删除存储过程
DROP PROCEDURE IF EXISTS AddEmployeeConfirmFields;

-- ============================================
-- 验证字段是否添加成功
-- ============================================
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME IN (
    'lq_salary_statistics',
    'lq_tech_teacher_salary_statistics',
    'lq_assistant_salary_statistics',
    'lq_store_manager_salary_statistics',
    'lq_director_salary_statistics',
    'lq_major_project_teacher_salary_statistics',
    'lq_major_project_director_salary_statistics',
    'lq_tech_general_manager_salary_statistics',
    'lq_business_unit_manager_salary_statistics',
    'lq_employee_salary_statistics'
  )
  AND COLUMN_NAME LIKE '%Confirm%' 
ORDER BY TABLE_NAME, ORDINAL_POSITION;