-- 文件名：triggers.sql
-- 这是为员工考勤管理系统编写的触发器创建脚本

DELIMITER //

-- 1. 触发器：trg_before_insert_attendance
-- 需求：在插入考勤记录前，自动计算工作时长和加班时长
CREATE TRIGGER trg_before_insert_attendance
BEFORE INSERT ON Attendance
FOR EACH ROW
BEGIN
    DECLARE work_duration INT;
    DECLARE overtime_duration INT;

    -- NULL 检查
    IF NEW.CheckInTime IS NULL OR NEW.CheckOutTime IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '上班时间或下班时间不能为空';
    END IF;

    -- 计算工作时长 (下班时间 - 上班时间)，单位为小时
    SET work_duration = TIMESTAMPDIFF(MINUTE, NEW.CheckInTime, NEW.CheckOutTime) / 60;
    
    -- 如果工作时长大于8小时，计算加班时长
    IF work_duration > 8 THEN
        SET overtime_duration = work_duration - 8;
        SET NEW.OvertimeHours = overtime_duration;
    ELSE
        SET NEW.OvertimeHours = 0;
    END IF;

    SET NEW.WorkingHours = work_duration;
END //

-- 2. 触发器：trg_after_update_leave_status
-- 需求：当假期申请的状态被更新为“已批准”时，自动更新员工的状态为“休假”
CREATE TRIGGER trg_after_update_leave_status
AFTER UPDATE ON LeaveRequest
FOR EACH ROW
BEGIN
    -- 如果请假申请已批准，且员工当前状态不是“休假”
    IF NEW.RequestStatus = '已批准' AND (SELECT EmploymentStatus FROM Employee WHERE EmployeeID = NEW.EmployeeID) != '休假' THEN
        UPDATE Employee
        SET EmploymentStatus = '休假'
        WHERE EmployeeID = NEW.EmployeeID;
    END IF;
END //

-- 3. 触发器：trg_before_insert_performance_appraisal
-- 需求：在插入绩效考核记录前，确保绩效评分为合法值（例如：0到100之间）
CREATE TRIGGER trg_before_insert_performance_appraisal
BEFORE INSERT ON PerformanceAppraisal
FOR EACH ROW
BEGIN
    -- 如果绩效评分不在0到100之间，抛出错误
    IF NEW.ReviewScore < 0 OR NEW.ReviewScore > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '绩效评分必须在0到100之间';
    END IF;
END //

-- 4. 触发器：trg_after_insert_employee
-- 需求：当插入新员工记录时，自动创建一个默认的用户登录记录
CREATE TRIGGER trg_after_insert_employee
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
    -- 插入新的用户登录记录
    INSERT INTO UserLogin (EmployeeID, Username, Password, AccountStatus)
    VALUES (NEW.EmployeeID, CONCAT('user_', NEW.EmployeeID), 'default_password', '有效');
END //

-- 5. 触发器：trg_before_delete_attendance
-- 需求：在删除考勤记录前，检查是否存在未处理的加班记录。如果有，则不允许删除该记录
CREATE TRIGGER trg_before_delete_attendance
BEFORE DELETE ON Attendance
FOR EACH ROW
BEGIN
    -- 检查该记录是否存在加班时长
    IF OLD.OvertimeHours > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '存在未处理的加班时长，不能删除考勤记录';
    END IF;
END //


DELIMITER ;