ALTER TABLE `ohrm_audittrail_section_actions` DROP INDEX action;

set @adminAuditModuleId := (SELECT id FROM ohrm_audittrail_module WHERE name = 'Admin');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
  ('NewsArticle', 'News', 'News Article', @adminAuditModuleId),
  ('DocumentArticle', 'Document', 'Document Article', @adminAuditModuleId);
set @newsArticleId := (SELECT id FROM ohrm_audittrail_section WHERE name = 'NewsArticle');
set @documentArticleId := (SELECT id FROM ohrm_audittrail_section WHERE name = 'DocumentArticle');

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@documentArticleId, 'SAVE DOCUMENT ARTICLE', 'Save'),
  (@documentArticleId, 'PUBLISH DOCUMENT ARTICLE', 'Publish'),
  (@documentArticleId, 'ACKNOWLEDGE DOCUMENT', 'Acknowledge'),
  (@newsArticleId, 'SAVE NEWS ARTICLE', 'Save'),
  (@newsArticleId, 'PUBLISH NEWS ARTICLE', 'Publish'),
  (@newsArticleId, 'ACKNOWLEDGE NEWS', 'Acknowledge');


DROP TABLE IF EXISTS `ohrm_audittrail_admin_news_article_trail`;

CREATE TABLE `ohrm_audittrail_admin_news_article_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL,
  `affected_entity_id` varchar(20) NOT NULL DEFAULT '',
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP TABLE IF EXISTS `ohrm_audittrail_admin_documents_article_trail`;

CREATE TABLE `ohrm_audittrail_admin_documents_article_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL,
  `affected_entity_id` varchar(20) NOT NULL DEFAULT '',
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE `ohrm_acknowledgement` (
  id INT NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(255) NOT NULL,
  entity_id INT(11) NOT NULL,
  employee_id INT(11) NOT NULL,
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_acknowledge TINYINT(2) NULL,
  CONSTRAINT unique_acknowledgement_record UNIQUE (entity_type,entity_id,employee_id),
  PRIMARY KEY(id)
) ENGINE = InnoDB default charset=utf8;

ALTER TABLE `ohrm_news_article` ADD `is_acknowledgable` TINYINT(2) NULL DEFAULT '0';
ALTER TABLE `ohrm_news_article` ADD `deleted_at` DATETIME DEFAULT NULL;
ALTER TABLE `ohrm_document_article` ADD `is_acknowledgable` TINYINT(2)  NULL DEFAULT '0';
ALTER TABLE `ohrm_document_article` ADD `deleted_at` DATETIME DEFAULT NULL;


alter table `ohrm_acknowledgement` ADD CONSTRAINT `ohrm_acknowledgement_employee_id_fk_hs_hr_employee_emp_number`
foreign key (`employee_id`) references `hs_hr_employee`(`emp_number`) on delete cascade;

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'admin.acknowledgement', 'AcknowledgmentListener', '1', '7');
INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'admin.news_audit', 'NewsAuditListener', '1', '17');
INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'admin.documents_audit', 'DocumentAuditListener', '1', '7');

SET @global_admin_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('announcement_news', 'Announcement - News', 1, 1, 1, 1);
SET @announcement_news_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
 VALUES (@global_admin_id, @announcement_news_data_group_id, '1', '1', '1', '1', '0');


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('announcement_document', 'Announcement - Document', 1, 1, 1, 1);
SET @announcement_document_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
 VALUES (@global_admin_id, @announcement_document_data_group_id, '1', '1', '1', '1', '0');