DELIMITER //

DROP TRIGGER IF EXISTS after_overtime_request_insert//
CREATE TRIGGER after_overtime_request_insert
AFTER INSERT ON employee_overtime_request
FOR EACH ROW
BEGIN
  IF NEW.status = 'Approved' THEN
    INSERT IGNORE INTO attendance (employee_id, employee_name, attendance_date)
    VALUES (NEW.employee_id, NEW.employee_name, NEW.date_requested);

    UPDATE attendance
    SET overtime_hours = NEW.hours_requested
    WHERE employee_id = NEW.employee_id
      AND attendance_date = NEW.date_requested;
  END IF;
END//

DROP TRIGGER IF EXISTS after_overtime_request_approved//
CREATE TRIGGER after_overtime_request_approved
AFTER UPDATE ON employee_overtime_request
FOR EACH ROW
BEGIN
  IF NEW.status = 'Approved' THEN
    INSERT IGNORE INTO attendance (employee_id, employee_name, attendance_date)
    VALUES (NEW.employee_id, NEW.employee_name, NEW.date_requested);

    UPDATE attendance
    SET overtime_hours = NEW.hours_requested
    WHERE employee_id = NEW.employee_id
      AND attendance_date = NEW.date_requested;
  END IF;
END//

DROP TRIGGER IF EXISTS after_overtime_request_rejected//
CREATE TRIGGER after_overtime_request_rejected
AFTER UPDATE ON employee_overtime_request
FOR EACH ROW
BEGIN
  IF NEW.status = 'Rejected' THEN
    UPDATE attendance
    SET overtime_hours = 0.00
    WHERE employee_id = NEW.employee_id
      AND attendance_date = NEW.date_requested;
  END IF;
END//

DELIMITER ;
