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, 'DELETE DOCUMENT', 'Delete'),
  (@newsArticleId, 'DELETE NEWS', 'Delete');

UPDATE `ohrm_audittrail_section` SET `affected_entity`='News' WHERE `name`='NewsArticle';
UPDATE `ohrm_audittrail_section` SET `affected_entity`='Document' WHERE `name`='DocumentArticle';

UPDATE `ohrm_audittrail_section_actions` SET `action`='SAVE DOCUMENT' WHERE `action`='SAVE DOCUMENT ARTICLE';
UPDATE `ohrm_audittrail_section_actions` SET `action`='PUBLISH DOCUMENT' WHERE `action`='PUBLISH DOCUMENT ARTICLE';
UPDATE `ohrm_audittrail_section_actions` SET `action`='SAVE NEWS' WHERE `action`='SAVE NEWS ARTICLE';
UPDATE `ohrm_audittrail_section_actions` SET `action`='PUBLISH NEWS' WHERE `action`='PUBLISH NEWS ARTICLE';

UPDATE `ohrm_audittrail_admin_documents_article_trail` SET `action`='SAVE DOCUMENT' WHERE `action`='SAVE DOCUMENT ARTICLE';
UPDATE `ohrm_audittrail_admin_documents_article_trail` SET `action`='PUBLISH DOCUMENT' WHERE `action`='PUBLISH DOCUMENT ARTICLE';
UPDATE `ohrm_audittrail_admin_news_article_trail` SET `action`='SAVE NEWS' WHERE `action`='SAVE NEWS ARTICLE';
UPDATE `ohrm_audittrail_admin_news_article_trail` SET `action`='PUBLISH NEWS' WHERE `action`='PUBLISH NEWS ARTICLE';

CREATE TABLE `ohrm_document_article_published_user_roles` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `document_id` INT UNSIGNED NOT NULL,
 `user_role_id` INT NOT NULL ,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_document_article_published_user_roles_user_role_id` FOREIGN KEY (`user_role_id`) REFERENCES `ohrm_user_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_published_user_roles_document_article_id` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = InnoDB;

CREATE TABLE ohrm_document_article_published_employee (
  id INT NOT NULL AUTO_INCREMENT,
  document_id INT UNSIGNED NOT NULL,
  employee_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_document_article_published_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_published_employee_document_article_id` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE = INNODB;

CREATE TABLE ohrm_document_article_published_employee_status (
  id INT NOT NULL AUTO_INCREMENT,
  document_id INT UNSIGNED NOT NULL,
  employee_status_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_document_article_published_employee_status_id` FOREIGN KEY (`employee_status_id`) REFERENCES `ohrm_employment_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_published_emp_status_document_article_id` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB;

CREATE TABLE ohrm_document_article_published_job_titles (
  id INT NOT NULL AUTO_INCREMENT,
  document_id INT UNSIGNED NOT NULL,
  job_title_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_document_article_published_job_titles_id` FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_published_job_titles_document_article_id` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB;

CREATE TABLE ohrm_document_article_published_sub_units (
  id INT NOT NULL AUTO_INCREMENT,
  document_id INT UNSIGNED NOT NULL,
  sub_unit_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_document_article_published_sub_unit_id` FOREIGN KEY (`sub_unit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_published_sub_unit_document_article_id` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = INNODB;

CREATE TABLE `ohrm_news_article_published_user_roles` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `news_id` INT UNSIGNED NOT NULL,
 `user_role_id` INT NOT NULL ,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_news_article_published_user_roles_user_role_id` FOREIGN KEY (`user_role_id`) REFERENCES `ohrm_user_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_published_user_roles_news_article_id` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = InnoDB;

CREATE TABLE ohrm_news_article_published_employee (
  id INT NOT NULL AUTO_INCREMENT,
  news_id INT UNSIGNED NOT NULL,
  employee_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_news_article_published_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_published_employee_news_article_id` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE = INNODB;

CREATE TABLE ohrm_news_article_published_employee_status (
  id INT NOT NULL AUTO_INCREMENT,
  news_id INT UNSIGNED NOT NULL,
  employee_status_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_news_article_published_employee_status_id` FOREIGN KEY (`employee_status_id`) REFERENCES `ohrm_employment_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_published_emp_status_news_article_id` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB;

CREATE TABLE ohrm_news_article_published_job_titles (
  id INT NOT NULL AUTO_INCREMENT,
  news_id INT UNSIGNED NOT NULL,
  job_title_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_news_article_published_job_titles_id` FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_published_job_titles_news_article_id` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB;

CREATE TABLE ohrm_news_article_published_sub_units (
  id INT NOT NULL AUTO_INCREMENT,
  news_id INT UNSIGNED NOT NULL,
  sub_unit_id INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT `fk_news_article_published_sub_unit_id` FOREIGN KEY (`sub_unit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_published_sub_unit_news_article_id` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = INNODB;
