DELIMITER $$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_job_vacancy`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_job_vacancy`;$$

DROP TRIGGER IF EXISTS `watch_update_ohrm_job_vacancy`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_job_vacancy`;$$

DROP TRIGGER IF EXISTS `watch_delete_ohrm_job_vacancy`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_job_vacancy`;$$


CREATE TRIGGER watch_insert_ohrm_job_vacancy
AFTER INSERT ON ohrm_job_vacancy
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET 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 affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = NEW.id LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_ohrm_job_vacancy(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, NEW.id, affected_entity_name, NEW.job_title_code, NEW.name , NEW.no_of_positions , NEW.location_id , NEW.sub_unit_id , NEW.status ,  NEW.description, NEW.request_consent);
END;$$

CREATE PROCEDURE audit_insert_ohrm_job_vacancy
(
  IN action_owner_id VARCHAR(6),
  IN action_owner_name VARCHAR(300),
  IN action_name VARCHAR(40),
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300),
  IN new_job_title_code INT(4),
  IN new_name VARCHAR(100),
  IN new_no_of_positions INT(13),
  IN new_location_id INT(4),
  IN new_sub_unit_id INT(4),
  IN new_status INT(4),
  IN new_description TEXT CHARSET UTF8,
  IN new_request_consent TINYINT(1)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = 'New Job Vacancy was Added\n';
   IF(IFNULL(new_job_title_code,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '),'\n');
   END IF;
   IF(IFNULL(new_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(new_name,' '),'\n');END IF;
   IF(IFNULL(new_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(new_no_of_positions,' '),'\n');END IF;
   IF(IFNULL(new_location_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '),'\n');
   END IF;
   IF(IFNULL(new_sub_unit_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '),'\n');
   END IF;
   IF (IFNULL(new_status,'') != '') THEN
   SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);
   SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@new_status_text, ' '), '\n');
   END IF;
   SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Request Consent option: ', IFNULL(@new_request_consent_text, ' '), '\n');


   IF(IFNULL(new_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(new_description,' '),'\n');END IF;

   -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_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;$$


CREATE TRIGGER watch_update_ohrm_job_vacancy
AFTER UPDATE ON ohrm_job_vacancy
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_trail` WHERE `affected_entity_id` = OLD.id) + 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 affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = OLD.id LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_ohrm_job_vacancy(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.id, affected_entity_name, NEW.job_title_code, OLD.job_title_code, NEW.name, OLD.name, NEW.no_of_positions, OLD.no_of_positions, NEW.location_id, OLD.location_id, NEW.sub_unit_id, OLD.sub_unit_id, NEW.status, OLD.status,  NEW.description, OLD.description, NEW.request_consent, OLD.request_consent);
END;$$

CREATE PROCEDURE audit_update_ohrm_job_vacancy
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_job_title_code INT(4),
  IN old_job_title_code INT(4),
  IN new_name VARCHAR(100) CHARSET UTF8,
  IN old_name VARCHAR(100) CHARSET UTF8,
  IN new_no_of_positions INT(13),
  IN old_no_of_positions INT(13),
  IN new_location_id INT(4),
  IN old_location_id INT(4),
  IN new_sub_unit_id INT(4),
  IN old_sub_unit_id INT(4),
  IN new_status INT(4),
  IN old_status INT(4),
  IN new_description TEXT CHARSET UTF8,
  IN old_description TEXT CHARSET UTF8,
  IN new_request_consent TINYINT(1),
  IN old_request_consent TINYINT(1)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  IF (IFNULL(old_job_title_code,'') != IFNULL(new_job_title_code,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Job Title was changed from ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '), ' to ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '), '\n');
  END IF;
  IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\n');END IF;
  IF (IFNULL(old_no_of_positions,'') != IFNULL(new_no_of_positions,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions was changed from ', IFNULL(old_no_of_positions, ' '), ' to ', IFNULL(new_no_of_positions, ' '), '\n');END IF;
  IF (IFNULL(old_location_id,'') != IFNULL(new_location_id,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Location was changed from ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '), '\n');
  END IF;
  IF (IFNULL(old_sub_unit_id,'') != IFNULL(new_sub_unit_id,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Sub Unit was changed from ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '), '\n');
  END IF;
  IF (IFNULL(old_status,'') != IFNULL(new_status,'')) THEN
      SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);
      SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);
      SET action_description = CONCAT(action_description, ' ', 'Status was changed from ', IFNULL(@old_status_text, ' '), ' to ', IFNULL(@new_status_text, ' '), '\n');
  END IF;
  IF (old_request_consent != new_request_consent) THEN
      SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));
      SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));
      SET action_description = CONCAT(action_description, ' ', 'Request Consent option was changed from ', IFNULL(@old_request_consent_text, ' '), ' to ', IFNULL(@new_request_consent_text, ' '), '\n');
  END IF;

  IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\n');END IF;

-- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_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,
      'CHANGE JOB VACANCY',
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;

END;$$

CREATE PROCEDURE audit_delete_ohrm_job_vacancy
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_job_title_code INT(4),
  IN old_name VARCHAR(100),
  IN old_no_of_positions INT(13),
  IN old_location_id INT(4),
  IN old_sub_unit_id INT(4),
  IN old_status INT(4),
  IN old_description TEXT CHARSET UTF8,
  IN old_request_consent TINYINT(1)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Job Vacancy was Deleted');
   IF(IFNULL(old_job_title_code,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '),'\n');
   END IF;
   IF(IFNULL(old_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(old_name,' '),'\n');END IF;
   IF(IFNULL(old_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(old_no_of_positions,' '),'\n');END IF;
   IF(IFNULL(old_location_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '),'\n');
   END IF;
   IF(IFNULL(old_sub_unit_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '),'\n');
   END IF;
   IF (IFNULL(old_status,'') != '') THEN
   SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);
   SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@old_status_text, ' '), '\n');
   END IF;
   SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Request Consent was: ', IFNULL(@old_request_consent_text, ' '), '\n');

   IF(IFNULL(old_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(old_description,' '),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_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;$$

DELIMITER ;