SET @admin_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
SET @ess_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'ESS');

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

INSERT INTO `ohrm_module` (`name` ,`status`) VALUES ('news', '1');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'news');

-- Start of Adding News and Documents to More Menu

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`, `route_name`) VALUES
('News', @module_id, 'viewArticles', NULL),
('Documents', @module_id, 'viewArticles', NULL),
('Announcement', @module_id, 'viewAnnouncementModule', 'news_viewAnnouncementModule'),
('News Article', @module_id, 'viewNewsArticles', 'news_viewNewsArticles'),
('Document Article', @module_id, 'viewDocumentArticle', 'news_viewDocumentArticle');

SET @news_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name` = 'News' AND `module_id` = @module_id);
SET @document_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name` = 'Documents' AND `module_id` = @module_id);
SET @announcement_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name` = 'Announcement' AND `module_id` = @module_id);
SET @news_article_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name` = 'News Article' AND `module_id` = @module_id);
SET @document_article_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name` = 'Document Article' AND `module_id` = @module_id);

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(@ess_user_role_type_id, @news_screen_id),
(@ess_user_role_type_id, @document_screen_id),
(@ess_user_role_type_id, @announcement_screen_id),
(@ess_user_role_type_id, @news_article_screen_id),
(@ess_user_role_type_id, @document_article_screen_id);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE
    s.action_url IN ('viewArticles') AND r.name != 'ConfigAdmin';
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE
    s.action_url IN ('viewAnnouncementModule') AND r.name != 'ConfigAdmin';
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE
    s.action_url IN ('viewNewsArticles') AND r.name != 'ConfigAdmin';
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE
    s.action_url IN ('viewDocumentArticle') AND r.name != 'ConfigAdmin';

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@config_admin_role_id, @news_screen_id, 0, 0, 0, 0),
(@config_admin_role_id, @document_screen_id, 0, 0, 0, 0),
(@config_admin_role_id, @announcement_screen_id, 0, 0, 0, 0),
(@config_admin_role_id, @news_article_screen_id, 0, 0, 0, 0),
(@config_admin_role_id, @document_article_screen_id, 0, 0, 0, 0);

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND parent_id IS NULL AND level = 1);
SET @employee_management_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management' AND parent_id = @people_management_pillar_menu_id AND level = 2);

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Announcements', (SELECT `id` FROM  `ohrm_screen` WHERE `name`='Announcement'),@employee_management_module_menu_id, 3, 700, '/type/news/status/published', 1);

SELECT LAST_INSERT_ID() INTO @announcement_menu_id;

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Documents', (SELECT `id` FROM  `ohrm_screen` WHERE `name`='Document Article'), @announcement_menu_id, 4, 100, '/type/documents/status/published', 'vuepim.viewDocuments', 1),
('News', (SELECT `id` FROM  `ohrm_screen` WHERE `name`='News Article'), @announcement_menu_id, 4, 200, NULL, 'vuepim.viewNews', 1);

-- End of Adding News and Documents to More Menu


-- enable news
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('news_and_document_publisher_news', 'on');

-- enable documents
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('news_and_document_publisher_documents', 'on');



-- Start of Adding News and Documents to Admin Menu

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Manage News', @module_id, 'viewNewsList', 'news_viewNewsList'),
('Manage Documents', @module_id, 'viewDocumentList', 'news_viewDocumentList'),
('Manage Categories', @module_id, 'viewCategoryList', 'news_viewCategoryList');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE
    s.action_url in ('viewNewsList', 'viewDocumentList', 'viewCategoryList')
    AND r.`name` = 'Admin';

SET @news_screen_id := (SELECT id FROM ohrm_screen WHERE action_url = 'viewNewsList');
SET @document_screen_id := (SELECT id FROM ohrm_screen WHERE action_url = 'viewDocumentList');
SET @category_screen_id := (SELECT id FROM ohrm_screen WHERE action_url = 'viewCategoryList');
SET @admin_screen_id := (SELECT id FROM ohrm_screen WHERE action_url = 'viewAdminModule');

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND `level` = 1);
SET @hr_administration_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'HR Administration'
                                                                            AND `level` = 2 AND parent_id = @people_management_pillar_menu_id);

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Announcements', NULL, @hr_administration_module_menu_id, 3, 600, NULL, 1);

SET @announcement_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Announcements' AND `level` = 3 AND parent_id = @hr_administration_module_menu_id);

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`, `uisref`) VALUES
('News', @news_screen_id, @announcement_menu_id, 4, 100, NULL, 1, 'vueadmin.announcements.viewNewsList'),
('Document Categories', @category_screen_id, @announcement_menu_id, 4, 300, NULL, 1,'vueadmin.announcements.viewDocumentCategories'),
('Documents', @document_screen_id, @announcement_menu_id, 4, 200, NULL, 1,'vueadmin.announcements.viewDocumentList');


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('announcement_news', 'HR Administration - Announcements 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_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
(@announcement_news_data_group_id, @news_screen_id, 1),
(@announcement_news_data_group_id, @news_screen_id, 2),
(@announcement_news_data_group_id, @news_screen_id, 3),
(@announcement_news_data_group_id, @news_screen_id, 4),
(@announcement_news_data_group_id, @admin_screen_id, 1);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  (@admin_user_role_type_id, @announcement_news_data_group_id, 1, 1, 1, 1);





INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('announcement_document', 'HR Administration - Announcements Documents', 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');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
(@announcement_document_data_group_id, @document_screen_id, 1),
(@announcement_document_data_group_id, @document_screen_id, 2),
(@announcement_document_data_group_id, @document_screen_id, 3),
(@announcement_document_data_group_id, @document_screen_id, 4),
(@announcement_document_data_group_id, @admin_screen_id, 1);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  (@admin_user_role_type_id, @announcement_document_data_group_id, 1, 1, 1, 1);



INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('admin_document_categories', 'HR Administration - Announcements Document Categories', 1, 1, 1, 1);
SET @admin_document_categoried_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, @admin_document_categoried_group_id, '1', '1', '1', '1', '0');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
(@admin_document_categoried_group_id, @category_screen_id, 1),
(@admin_document_categoried_group_id, @category_screen_id, 2),
(@admin_document_categoried_group_id, @category_screen_id, 3),
(@admin_document_categoried_group_id, @category_screen_id, 4),
(@admin_document_categoried_group_id, @admin_screen_id, 1);

-- End of Adding News and Documents to Admin Menu


-- Start of Employee_Acknowledgment_Report

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Announcement Acknowledgement Report', @module_id , 'announcementAcknowledgementReport');

SET @view_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(1, @view_report_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('announcement_acknowledgement_report', 'HR Administration - Employee Acknowledgements of News and Documents Report', 1, 0, 0, 0);
SET @report_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, @report_data_group_id, '1', '0', '0', '0', '0');


INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@report_data_group_id, @view_report_screen_id, 1);

SET @report_id := (SELECT id FROM `ohrm_advanced_report` WHERE `unique_name` = 'Employee_Acknowledgment_Report');

INSERT INTO `ohrm_reports_catalog` (  `report_id`, `data_group_id`, `active`) VALUES (  @report_id, @report_data_group_id, '1');


INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('1',@report_data_group_id , '1', NULL, NULL, NULL);

-- End of Employee_Acknowledgment_Report


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'news');

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`)
VALUES ('DOCUMENT', 'Document', NULL, NULL, '1', NULL, NULL, @module_id, '0');

INSERT INTO `ohrm_workflow_state_machine` (`id`, `workflow`, `state`, `role`, `action`, `resulting_state`, `roles_to_notify`, `screens_to_show`, `priority`)
VALUES (NULL, 'DOCUMENT', 'NEW', 'Admin', 'Publish', 'PUBLISHED', NULL, NULL, '0');

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`)
VALUES ('NEWS', 'News', NULL, NULL, '1', NULL, NULL, @module_id, '0');

INSERT INTO `ohrm_workflow_state_machine` (`id`, `workflow`, `state`, `role`, `action`, `resulting_state`, `roles_to_notify`, `screens_to_show`, `priority`)
VALUES (NULL, 'NEWS', 'NEW', 'Admin', 'Publish', 'PUBLISHED', NULL, NULL, '0');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Publish Document Notification', 1, @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PublishDocumentEmailProcessor"}', 'DOCUMENT', 'Publish', @module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Publish Document Notification Template', @notification_id, @notification_media_id, 'en_US', "Document Notification: %topic% has been published", 'Hi,<br /><br />Document "%topic%" has been published.<br /><br />You can click %url% to view the document.<br /><br />Thank you.<br /><br />This is an automated notification.');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'DOCUMENT' AND `action` = 'Publish' AND `role` = 'Admin');

INSERT INTO `ohrm_notification_subscription` (`id`, `notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `workflow_item_id`, `hidden`)
VALUES (NULL, @notification_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, @workflow_state_machine_id, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, address_part, parameters, type, name) VALUES
    (@notification_media_id, 'bcc','{"prevent_delete":true,"restrict_employees":true,"include_bcc":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id, @notification_template_id);

SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'news');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Publish News Notification', 1, @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PublishNewsEmailProcessor"}', 'NEWS', 'Publish', @module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Publish News Notification Template', @notification_id, @notification_media_id, 'en_US', "News Notification: %topic% has been published", 'Hi,<br /><br />News "%topic%" has been published.<br /><br />You can click %url% to view the news.<br /><br />Thank you.<br /><br />This is an automated notification.');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'NEWS' AND `action` = 'Publish' AND `role` = 'Admin');

INSERT INTO `ohrm_notification_subscription` (`id`, `notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `workflow_item_id`, `hidden`)
VALUES (NULL, @notification_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, @workflow_state_machine_id, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, address_part, parameters, type, name) VALUES
    (@notification_media_id, 'bcc','{"prevent_delete":true,"restrict_employees":true,"include_bcc":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id, @notification_template_id);

INSERT INTO ohrm_data_list (`name`, `meta_data`) VALUES
('News', '{"mandatoryFields":["topic"],"availableFields":{"topic":"Topic","publishedDate":"Published Date","status":"Status","publishedLocations":"Published Locations","publishedUserRoles":"Published User Roles","publishedJobTitles":"Published Job Titles","publishedEmpStatus":"Published Employment Status","publishedSubunits":"Published Subunits","publishedEmployee":"Published Employees","attachment":"Attachments","ackState":"Acknowledgment Status"},"defaultFields":["topic","publishedDate","publishedLocations","publishedUserRoles","status","attachment","ackState"],"listFields":{"topic":"topic","publishedDate":"publishedDate","publishedLocations":"publishedLocations","publishedUserRoles":"publishedUserRoles","status":"status","attachment":"attachment","ackState":"ackState"}}'),
('Document', '{"mandatoryFields":["topic"],"availableFields":{"category":"Category","topic":"Topic","publishedDate":"Published Date","status":"Status","publishedLocations":"Published Locations","publishedUserRoles":"Published User Roles","publishedJobTitles":"Published Job Titles","publishedEmpStatus":"Published Employment Status","publishedSubunits":"Published Subunits","publishedEmployee":"Published Employees","attachment":"Attachments","ackState":"Acknowledgment Status"},"defaultFields":["category","topic","publishedDate","publishedLocations","publishedUserRoles","status","attachment","ackState"],"listFields":{"category":"category","topic":"topic","publishedDate":"publishedDate","publishedLocations":"publishedLocations","publishedUserRoles":"publishedUserRoles","status":"status","attachment":"attachment","ackState":"ackState"}}');
