CREATE TABLE `ohrm_news_article` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `topic` VARCHAR(100) DEFAULT NULL,
  `description` LONGTEXT DEFAULT NULL,
  `published_date` DATE DEFAULT NULL,
  `status` TINYINT UNSIGNED DEFAULT NULL,
  `sort_order` INT UNSIGNED DEFAULT NULL,
  `deleted_at` DATETIME DEFAULT NULL,
  `is_acknowledgable` TINYINT(2) DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_document_category` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `deleted_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_document_article` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `topic` VARCHAR(100) DEFAULT NULL,
  `description` LONGTEXT DEFAULT NULL,
  `published_date` DATE DEFAULT NULL,
  `status` TINYINT UNSIGNED DEFAULT NULL,
  `sort_order` INT UNSIGNED DEFAULT NULL,
  `deleted_at` DATETIME DEFAULT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `is_acknowledgable` TINYINT(2) DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_news_article_attachment`(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `news_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(400) DEFAULT NULL,
  `size` INT NOT NULL,
  `type` VARCHAR(200) DEFAULT NULL,
  `date_added` DATETIME DEFAULT NULL,
  `added_by_user_id` INT(10) DEFAULT NULL,
  `content` MEDIUMBLOB,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_news_article_attachment_1` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_attachment_2` FOREIGN KEY (`added_by_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_document_article_attachment`(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(400) DEFAULT NULL,
  `size` INT NOT NULL,
  `type` VARCHAR(200) DEFAULT NULL,
  `date_added` DATETIME DEFAULT NULL,
  `added_by_user_id` INT(10) DEFAULT NULL,
  `content` MEDIUMBLOB,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_document_article_attachment_1` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_attachment_2` FOREIGN KEY (`added_by_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

ALTER TABLE `ohrm_document_article`
  ADD CONSTRAINT `fk_ohrm_document_article_1` FOREIGN KEY (`category_id`) REFERENCES `ohrm_document_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE `ohrm_document_article_published_location`(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` INT UNSIGNED NOT NULL,
  `location_id` INT (11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_document_article_published_location_1` FOREIGN KEY (`document_id`) REFERENCES `ohrm_document_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_document_article_published_location_2` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_news_article_published_location`(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `news_id` INT UNSIGNED NOT NULL,
  `location_id` INT (11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_news_article_published_location_1` FOREIGN KEY (`news_id`) REFERENCES `ohrm_news_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_news_article_published_location_2` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;


Drop view if EXISTS  documents ;

CREATE VIEW documents AS
SELECT
    da.id,
    'documents' AS TYPE,
    IF(da.deleted_at IS NULL, topic ,concat(topic,' - [Deleted]') ) AS topic,
    da.`published_date`,
    da.`status`,
    da.`is_acknowledgable`,
    dapl.location_id,
    daps.sub_unit_id,
    dapur.user_role_id,
    dapjt.job_title_id,
    dapes.employee_status_id,
    dapee.employee_id
FROM
    ohrm_document_article da
LEFT JOIN ohrm_document_article_published_location dapl ON
    dapl.document_id = da.id
LEFT JOIN ohrm_document_article_published_sub_units daps ON
    daps.document_id = da.id
LEFT JOIN ohrm_document_article_published_user_roles dapur ON
    dapur.document_id = da.id
LEFT JOIN ohrm_document_article_published_job_titles dapjt ON
    dapjt.document_id = da.id
LEFT JOIN ohrm_document_article_published_employee_status dapes ON
    dapes.document_id = da.id
LEFT JOIN ohrm_document_article_published_employee dapee ON
    dapee.document_id = da.id
UNION
SELECT
    da.id,
    'news' AS TYPE,
    IF(da.deleted_at IS NULL, topic ,concat(topic,' - [Deleted]') ) AS topic,
    da.`published_date`,
    da.`status`,
    da.`is_acknowledgable`,
    dapl.location_id,
    daps.sub_unit_id,
    dapur.user_role_id,
    dapjt.job_title_id,
    dapes.employee_status_id,
    dapee.employee_id
FROM
    ohrm_news_article da
LEFT JOIN ohrm_news_article_published_location dapl ON
    dapl.news_id = da.id
LEFT JOIN ohrm_news_article_published_sub_units daps ON
    daps.news_id = da.id
LEFT JOIN ohrm_news_article_published_user_roles dapur ON
    dapur.news_id = da.id
LEFT JOIN ohrm_news_article_published_job_titles dapjt ON
    dapjt.news_id = da.id
LEFT JOIN ohrm_news_article_published_employee_status dapes ON
    dapes.news_id = da.id
LEFT JOIN ohrm_news_article_published_employee dapee ON
    dapee.news_id= da.id;

Drop view if EXISTS  ohrm_documents_view;

CREATE VIEW ohrm_documents_view AS SELECT
    CONCAT(d.type, '-', d.id) AS entity_id,
    d.*,
    ack.is_acknowledge,
    ack.date,
    IF(ack.is_acknowledge = 1, "1", "2") AS acknowledged,
    e.emp_number AS emp_number,
    e.emp_firstname,
    e.emp_middle_name,
    e.emp_lastname,
    e.employee_id AS emp_id,
    uur.user_role_id emp_user_role
FROM
    `hs_hr_employee` e
LEFT JOIN ohrm_user ur ON
    ur.emp_number = e.emp_number
LEFT JOIN ohrm_user_user_role uur ON
    ur.id = uur.user_id
CROSS JOIN documents d LEFT JOIN ohrm_acknowledgement ack ON
    d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
WHERE
    uur.user_role_type_id NOT IN(3) AND(
        d.location_id = e.location_id OR d.location_id IS NULL
    ) AND(
        d.sub_unit_id = e.work_station OR d.sub_unit_id IS NULL
    ) AND(
        d.user_role_id = uur.user_role_id OR d.user_role_id IS NULL
    ) AND(
        d.job_title_id = e.job_title_code OR d.job_title_id IS NULL
    ) AND(
        d.employee_status_id = e.emp_status OR d.employee_status_id IS NULL
    ) AND(
        d.employee_id = e.`emp_number` OR d.employee_id IS NULL
    ) OR(
        d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
    )
UNION
SELECT
    CONCAT(d.type, '-', d.id) AS entity_id,
    d.*,
    ack.is_acknowledge,
    ack.date,
    IF(ack.is_acknowledge = 1, "1", "2") AS acknowledged,
    e.emp_number AS emp_number,
    e.emp_firstname,
    e.emp_middle_name,
    e.emp_lastname,
    e.employee_id AS emp_id,
    uur.user_role_id emp_user_role
FROM
    `hs_hr_employee` e
LEFT JOIN ohrm_user ur ON
    ur.emp_number = e.emp_number
LEFT JOIN ohrm_user_user_role uur ON
    ur.id = uur.user_id
CROSS JOIN documents d LEFT JOIN ohrm_acknowledgement ack ON
    d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
WHERE
    uur.user_role_type_id IN(3) AND e.emp_number IN(
    SELECT
        `erep_sup_emp_number`
    FROM
        `hs_hr_emp_reportto`
) AND(
    d.location_id = e.location_id OR d.location_id IS NULL
) AND(
    d.sub_unit_id = e.work_station OR d.sub_unit_id IS NULL
) AND(
    d.user_role_id = uur.user_role_id OR d.user_role_id IS NULL
) AND(
    d.job_title_id = e.job_title_code OR d.job_title_id IS NULL
) AND(
    d.employee_status_id = e.emp_status OR d.employee_status_id IS NULL
) AND(
    d.employee_id = e.`emp_number` OR d.employee_id IS NULL
) OR(
    d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
);
