DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_dependents`;
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_dependents`;
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_dependents`;

CREATE PROCEDURE audit_INSERT_hs_hr_emp_dependents
(
  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 VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_ed_name VARCHAR(100) CHARSET UTF8,  
  IN new_ed_relationship_type ENUM('CHILD','OTHER'),  
  IN new_ed_relationship VARCHAR(100) CHARSET UTF8,  
  IN new_ed_date_of_birth DATE,
  IN new_ed_nationality INT
  
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


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

   SET action_description = IFNULL( CONCAT(action_description, ' ', 'New Dependent was added (Name: ',new_ed_name,')\n'),'');
   SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship: ',new_ed_relationship_type,'\n'),'');
   IF (new_ed_relationship != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship (Other): ',new_ed_relationship,'\n'),''); END IF;
   IF (new_ed_date_of_birth != '') THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth: ',new_ed_date_of_birth,'\n');END IF;
   IF (IFNULL(new_ed_nationality,'') != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Nationality: ',(SELECT name from `ohrm_nationality` WHERE id=new_ed_nationality),'\n'),''); END IF;


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_dependents_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 PROCEDURE audit_UPDATE_hs_hr_emp_dependents
(
  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 VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_ed_name VARCHAR(100) CHARSET UTF8,  
  IN old_ed_name VARCHAR(100) CHARSET UTF8,  
  IN new_ed_relationship_type ENUM('CHILD','OTHER'), 
  IN old_ed_relationship_type ENUM('CHILD','OTHER'),   
  IN new_ed_relationship VARCHAR(100) CHARSET UTF8,  
  IN old_ed_relationship VARCHAR(100) CHARSET UTF8,
  IN new_ed_date_of_birth DATE,
  IN old_ed_date_of_birth DATE,
  IN new_ed_nationality INT,
  IN old_ed_nationality INT

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


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

  SET action_description = IFNULL( CONCAT(action_description, ' ', '(Name: ',old_ed_name,')\n'),'');
  IF (old_ed_name != new_ed_name) THEN SET action_description = CONCAT(action_description, ' ', 'Name has changed from ', IFNULL(old_ed_name, ' '), ' to ', IFNULL(new_ed_name, ' '), '\n');END IF;
  IF (old_ed_relationship_type != new_ed_relationship_type) THEN SET action_description = CONCAT(action_description, ' ', 'Relationship was changed from ', IFNULL(old_ed_relationship_type,' ') , ' to ', IFNULL(new_ed_relationship_type,' ') , '\n');END IF;
  IF (old_ed_relationship != new_ed_relationship) THEN SET action_description = CONCAT(action_description, ' ', 'Other Relationship was changed from ', IFNULL(old_ed_relationship, ' '), ' to ', IFNULL(new_ed_relationship, ' '), '\n');END IF;
  IF (IFNULL(old_ed_date_of_birth,'') != IFNULL(new_ed_date_of_birth,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth was changed from ', IFNULL(old_ed_date_of_birth, ' '), ' to ', IFNULL(new_ed_date_of_birth, ' '), '\n');END IF;
  IF (IFNULL(old_ed_nationality,'') != IFNULL(new_ed_nationality,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Nationality was changed from ', IFNULL((SELECT name from `ohrm_nationality` WHERE id=old_ed_nationality), '-'), ' to ', IFNULL((SELECT name from `ohrm_nationality` WHERE id=new_ed_nationality), '-'), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_dependents_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 PROCEDURE audit_DELETE_hs_hr_emp_dependents
(
  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 VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_ed_name VARCHAR(100) CHARSET UTF8,   
  IN old_ed_relationship_type ENUM('CHILD','OTHER'),  
  IN old_ed_relationship VARCHAR(100) CHARSET UTF8,  
  IN old_ed_date_of_birth DATE,
  IN old_ed_nationality INT
)
BEGIN
  DECLARE action_description VARCHAR(600);


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

   SET action_description = CONCAT(action_description, ' ', 'Dependent was deleted (Name: ',old_ed_name,')\n');
   SET action_description = CONCAT(action_description, ' ', 'Relationship: ',old_ed_relationship_type,'\n');
   IF (old_ed_relationship != '') THEN SET action_description = CONCAT(action_description, ' ', 'Relationship (Other): ',old_ed_relationship,'\n');END IF;
   IF (old_ed_date_of_birth != '') THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth: ',old_ed_date_of_birth,'\n');END IF;
   IF (IFNULL(old_ed_nationality,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Nationality: ',(SELECT name from `ohrm_nationality` WHERE id=old_ed_nationality),'\n'); END IF;


  -- End generating the action description

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