-- core doc template plugin db

CREATE TABLE IF NOT EXISTS `ohrm_document_template_enabled_data_group_and_workflow`
(
    `id`
    INT
    AUTO_INCREMENT,
    `description`
    VARCHAR
(
    255
) NOT NULL,
    `data_group_id` INT UNIQUE,
    `module_id` INT,
    `workflow` VARCHAR
(
    100
),
    PRIMARY KEY
(
    `id`
),
    FOREIGN KEY
(
    `data_group_id`
) REFERENCES `ohrm_data_group`
(
    `id`
) ON UPDATE CASCADE
  ON DELETE CASCADE,
    FOREIGN KEY
(
    `workflow`
) REFERENCES `ohrm_workflow`
(
    `id`
)
  ON UPDATE CASCADE
  ON DELETE CASCADE,
    FOREIGN KEY
(
    `module_id`
) REFERENCES `ohrm_module`
(
    `id`
)
  ON UPDATE CASCADE
  ON DELETE CASCADE
    ) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_document_template_enabled_workflow_action`
(
    `id` int
(
    7
) AUTO_INCREMENT,
    `workflow_action_id` int
(
    7
),
    `workflow` VARCHAR
(
    100
),
    PRIMARY KEY
(
    `id`
),
    FOREIGN KEY
(
    `workflow`
) REFERENCES `ohrm_workflow`
(
    `id`
) ON UPDATE CASCADE
  ON DELETE CASCADE
    ) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_document_template`
(
    `id`
    INT
    AUTO_INCREMENT,
    `name`
    VARCHAR
(
    100
) NOT NULL,
    `description` VARCHAR
(
    255
),
    `template_string` MEDIUMTEXT,
    `page_size` VARCHAR
(
    100
),
    `module_id` INT,
    `document_template_enabled_data_group_id` INT,
    `document_template_enabled_workflow_action_id` INT,
    `margin_left` INT,
    `margin_right` INT,
    `margin_top` INT,
    `margin_bottom` INT,
    `is_template_string_editable` tinyint(1) default 0,
    `is_digital_sign_enabled` tinyint(1) default 0,
    `notification_subscriber_subscription_template_id` int(11) default null,
    PRIMARY KEY
(
    `id`
),
    FOREIGN KEY
(
    `module_id`
) REFERENCES `ohrm_module`
(
    `id`
) ON UPDATE CASCADE
  ON DELETE CASCADE,
    FOREIGN KEY
(
    `document_template_enabled_data_group_id`
) REFERENCES `ohrm_document_template_enabled_data_group_and_workflow`
(
    `id`
)
  ON UPDATE CASCADE
  ON DELETE CASCADE
    ) ENGINE = INNODB default charset=utf8;

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('personal_information_documents', 'Employee Management - Personal Details - Documents', 1, NULL, NULL, NULL),
       ('contact_details_documents', 'Employee Management - Contact Details - Documents', 1, NULL, NULL, NULL),
       ('employee_job_documents', 'Employee Management - Job - Documents', 1, NULL, NULL, NULL),
       ('employee_salary_documents', 'Employee Management - Salary - Documents', 1, NULL, NULL, NULL);

SET
@personal_information_documents_datagroup_id = (SELECT `id` FROM `ohrm_data_group` WHERE `name`='personal_information_documents');
SET
@contact_details_documents_datagroup_id = (SELECT `id` FROM `ohrm_data_group` WHERE `name`='contact_details_documents');
SET
@employee_job_documents_datagroup_id = (SELECT `id` FROM `ohrm_data_group` WHERE `name`='employee_job_documents');
SET
@employee_salary_documents_datagroup_id = (SELECT `id` FROM `ohrm_data_group` WHERE `name`='employee_salary_documents');
-- document template enabled data groups
INSERT INTO `ohrm_document_template_enabled_data_group_and_workflow`(`description`, `data_group_id`, `module_id`)
VALUES ('Employee Management - Personal Details', @personal_information_documents_datagroup_id, 3),
       ('Employee Management - Contact Details', @contact_details_documents_datagroup_id, 3),
       ('Employee Management - Job', @employee_job_documents_datagroup_id, 3),
       ('Employee Management - Salary', @employee_salary_documents_datagroup_id, 3);

-- ----------------------------------------------------------------------------- to move to PIM --------------------------------------------------------------------------------------------------------------------------

SET
@pim_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='pim');

SET
@max_screen_id := (SELECT MAX(`id`) FROM `ohrm_screen`);

SET
@pim_doc_template_list_screen_id := @max_screen_id + 1;
SET
@add_pim_doc_template_screen_id := @max_screen_id + 2;
SET
@delete_pim_doc_template_screen_id := @max_screen_id + 3;

INSERT INTO `ohrm_screen` (`id`, `name`, `module_id`, `action_url`)
VALUES (@pim_doc_template_list_screen_id, 'PIM Document Templates', @pim_module_id, 'viewPIMDocumentTemplates'),
       (@add_pim_doc_template_screen_id, 'PIM Add Document Templates', @pim_module_id, 'addPIMDocumentTemplates'),
       (@delete_pim_doc_template_screen_id, 'PIM Delete Document Templates', @pim_module_id,
        'deletePIMDocumentTemplates');

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);
SET
@employee_management_configurations_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Configurations' AND parent_id = @employee_management_module_menu_id AND level = 3);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`)
VALUES ('Document Templates', @pim_doc_template_list_screen_id, @employee_management_configurations_menu_id, 4, 900,
        'pim.document_templates', 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('pim_document_templates', 'Employee Management - Configure - Document Templates', 1, 1, 1, 1);
SET
@pim_doc_template_data_group_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, @pim_doc_template_list_screen_id, 1, 1, 1, 1),
       (1, @add_pim_doc_template_screen_id, 1, 1, 1, 1),
       (1, @delete_pim_doc_template_screen_id, 1, 1, 1, 1);

SET
@adminid := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET
@supervisorid := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Supervisor');
SET
@hodid := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`,
                                         `can_delete`, `self`)
VALUES (@adminid, @pim_doc_template_data_group_id, 1, 1, 1, 1, 0),
       (@adminid, @personal_information_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@adminid, @contact_details_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@adminid, @employee_job_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@adminid, @employee_salary_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@supervisorid, @personal_information_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@supervisorid, @contact_details_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@supervisorid, @employee_job_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@hodid, @personal_information_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@hodid, @contact_details_documents_datagroup_id, 1, 0, 0, 0, 0),
       (@hodid, @employee_job_documents_datagroup_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`)
VALUES (@pim_doc_template_data_group_id, @pim_doc_template_list_screen_id, 1),
       (@pim_doc_template_data_group_id, @add_pim_doc_template_screen_id, 1),
       (@pim_doc_template_data_group_id, @add_pim_doc_template_screen_id, 2),
       (@pim_doc_template_data_group_id, @add_pim_doc_template_screen_id, 3),
       (@pim_doc_template_data_group_id, @delete_pim_doc_template_screen_id, 4);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id)
VALUES (1, @pim_doc_template_data_group_id);

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, @personal_information_documents_datagroup_id, 1, 0, 0, 0),
       (1, @contact_details_documents_datagroup_id, 1, 0, 0, 0),
       (1, @employee_job_documents_datagroup_id, 1, 0, 0, 0),
       (1, @employee_salary_documents_datagroup_id, 1, 0, 0, 0),
       (2, @personal_information_documents_datagroup_id, 1, 0, 0, 0),
       (2, @contact_details_documents_datagroup_id, 1, 0, 0, 0),
       (2, @employee_job_documents_datagroup_id, 1, 0, 0, 0),
       (2, @employee_salary_documents_datagroup_id, 1, 0, 0, 0),
       (3, @personal_information_documents_datagroup_id, 1, 0, 0, 0),
       (3, @contact_details_documents_datagroup_id, 1, 0, 0, 0),
       (3, @employee_job_documents_datagroup_id, 1, 0, 0, 0),
       (3, @employee_salary_documents_datagroup_id, 1, 0, 0, 0);

SET
@max_event_listener_position = (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`)
VALUES ('admin.salary_component.add', 'PIMSalaryComponentsListener', 1, @max_event_listener_position + 1),
       ('admin.salary_component.update', 'PIMSalaryComponentsListener', 1, @max_event_listener_position + 2),
       ('admin.salary_component.delete', 'PIMSalaryComponentsListener', 1, @max_event_listener_position + 3);