Drop view if EXISTS  documents ;
Drop view if EXISTS  ohrm_documents_view;

CREATE VIEW documents AS
SELECT
    da.id,
    'documents' AS TYPE,
    IF(da.deleted_at IS NULL, topic ,concat(topic,' - [Deleted]') ) AS topic,
    da.`description`,
    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.`description`,
    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;

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 hs_hr_emp_locations el ON
    el.emp_number = e.emp_number
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 = el.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 hs_hr_emp_locations el ON
    el.emp_number = e.emp_number
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 = el.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`
);
