DELIMITER $$
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 prev_evaluator_name VARCHAR(300) CHARSET UTF8;
    DECLARE new_evaluator_name VARCHAR(300) CHARSET UTF8;
    DECLARE orangehrm_user_name_in_sentence_case 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 orangehrm_user_name_in_sentence_case = IF(orangehrm_user_name='admin', 'Admin',orangehrm_user_name);
        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;
        IF (comment = '') THEN
            IF @workflow_action = 'Replaced' THEN
                SET prev_evaluator_name = (SELECT CONCAT(emp_firstname, IF(emp_middle_name IS NULL OR emp_middle_name = '', '', CONCAT(' ', emp_middle_name)),' ', emp_lastname, IF(termination_id IS NULL, '', ' (Past Employee)')) FROM  hs_hr_employee WHERE emp_number = OLD.employee_id);
                SET new_evaluator_name = (SELECT CONCAT(emp_firstname, IF(emp_middle_name IS NULL OR emp_middle_name = '', '', CONCAT(' ', emp_middle_name)),' ', emp_lastname) FROM  hs_hr_employee WHERE emp_number = NEW.employee_id);
                SET comment = CONCAT(orangehrm_user_name_in_sentence_case,' has ',@workflow_action, ' ',prev_evaluator_name,' by ',new_evaluator_name, '\n', 'Existing ratings and comments from the previous evaluator have been allocated to the new evaluator');
            ELSE
                SET comment = (SELECT CONCAT_WS(' ',orangehrm_user_name_in_sentence_case,'has',@workflow_action,pa.description) FROM  ohrm_performance_appraisal_evaluator pae LEFT JOIN ohrm_performance_appraisal pa ON pa.id = pae.employee_appraisal_id WHERE pae.id = NEW.id);
            END IF;
        END IF;
        SET statusName = @workflow_action;
        IF(statusName != 'invalid') THEN
            INSERT INTO `ohrm_performance_appraisal_history`
            SET
                 action_time=CURRENT_TIMESTAMP,
                 action_owner_id = @orangehrm_user,
                 action_owner_name= orangehrm_user_name,
                 action=statusName,
                 evaluator_id = NEW.id,
                 action_description=comment;
        END IF;
    END IF;    
END;$$

DROP TRIGGER IF EXISTS `watch_delete_performance_competency`;$$
CREATE TRIGGER watch_delete_performance_competency BEFORE  DELETE
ON  ohrm_performance_competency
FOR EACH ROW BEGIN
    DECLARE questionId INT(11) ;  
    SET questionId = (SELECT question_id FROM `ohrm_performance_competency_question` WHERE ohrm_performance_competency_question.employee_competency_id=OLD.id);   
    DELETE FROM ohrm_question WHERE ohrm_question.id = questionId;
END;$$



DROP TRIGGER IF EXISTS `watch_delete_performance_goal`;$$
DELIMITER $$
CREATE TRIGGER watch_delete_performance_goal BEFORE  DELETE
ON  ohrm_performance_goal
FOR EACH ROW BEGIN
   
    DECLARE questionId INT(11) ;   
    SET questionId = (SELECT question_id FROM `ohrm_performance_goal_question` WHERE ohrm_performance_goal_question.employee_goal_id=OLD.id);  
    DELETE FROM ohrm_question WHERE ohrm_question.id = questionId;
END;$$

DROP TRIGGER IF EXISTS `watch_delete_performance_appraisal`;$$


DROP TRIGGER IF EXISTS `watch_delete_evaluator`;$$
CREATE TRIGGER watch_delete_evaluator BEFORE  DELETE
ON ohrm_performance_appraisal_evaluator
FOR EACH ROW BEGIN
    DELETE FROM ohrm_answer WHERE ohrm_answer.id IN ( SELECT  `answer_id` FROM `ohrm_performance_evaluator_answer` WHERE `employee_appraisal_evaluator_id`=OLD.id);
END;$$

DROP TRIGGER IF EXISTS `watch_ohrm_bulk_appraisal_archives_insert`;$$
CREATE TRIGGER watch_ohrm_bulk_appraisal_archives_insert BEFORE INSERT
ON ohrm_bulk_appraisal_archives
FOR EACH ROW BEGIN
    SET NEW.file_identity = UUID();
END;$$
DELIMITER ;
