DROP TRIGGER IF EXISTS `watch_update_performance_appraisal_evaluator`;$$
CREATE TRIGGER watch_update_performance_appraisal_evaluator AFTER  UPDATE
ON  ohrm_performance_appraisal_evaluator
FOR EACH ROW BEGIN
    DECLARE orangehrm_user_name VARCHAR(300) CHARSET UTF8;
    DECLARE statusName VARCHAR(100);
    DECLARE comment MEDIUMTEXT;
    IF (SELECT @no_review_history IS NULL) THEN 
        SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
        SET orangehrm_user_name = IFNULL(orangehrm_user_name, 'admin');
        SET statusName='invalid';
        SET comment='';
        IF(NEW.comment_added_time != Old.comment_added_time or (Old.comment_added_time is null and NEW.comment_added_time is not null)) THEN
            SET comment = NEW.admin_comment;
        END IF;
        SET statusName = @workflow_action;
        IF(statusName != 'invalid') THEN
            INSERT INTO `ohrm_audittrail_performance_appraisal_trail`
            SET  
                 action_time=CURRENT_TIMESTAMP,
                 action_owner_id = @orangehrm_user,
                 action_owner_name= orangehrm_user_name,
                 version_id=1,
                 action=statusName,
                 affected_entity_id=NEW.id,
                 action_description=comment; 
        END IF;
    END IF;    
END;$$

DROP TRIGGER IF EXISTS `watch_delete_performance_appraisal`;$$
CREATE TRIGGER watch_delete_performance_appraisal AFTER  DELETE
ON  ohrm_performance_appraisal
FOR EACH ROW BEGIN
    DECLARE formId INT(6) ;
    DECLARE new_version INT;
          DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
          DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;

    SET formId = OLD.question_form_id;
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_performance_appraisal_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
          SET new_version = IFNULL(new_version, 1);
          SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
          SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
          SET affected_entity_name = (SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1 );
          DELETE FROM ohrm_question_form WHERE ohrm_question_form.id = formId;
          CALL audit_DELETE_ohrm_performance_appraisal(@orangehrm_user, orangehrm_user_name, 'DELETE APPRAISAL', new_version, OLD.emp_number, affected_entity_name, OLD.description, OLD.from_date, OLD.to_date);
END;$$

DROP PROCEDURE IF EXISTS `audit_DELETE_ohrm_performance_appraisal`;$$
CREATE PROCEDURE audit_DELETE_ohrm_performance_appraisal
(
  IN action_owner_id VARCHAR(6) CHARSET utf8,
  IN action_owner_name VARCHAR(300) CHARSET utf8,
  IN action_name VARCHAR(255) CHARSET utf8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET utf8,
  IN old_description VARCHAR(300) CHARSET utf8,
  IN old_from_date DATE,
  IN old_to_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;

  SET action_description = '';

  -- Start generating the action description

  SET action_description = CONCAT(action_description, 'Appraisal was deleted ( Description: ', old_description, ' ','Appraisal Period: ', old_from_date, '-', old_to_date);

  -- End generating the action description


  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_performance_appraisal_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
END;$$