SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');

SET @onlinetraining_module_id := (SELECT `id` FROM ohrm_module WHERE `name` = 'onlinetraining');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('configure_otm_certificate_template', 'Online Training - Configure Certificate Template', 1, 1, 1, 1);

SET @configure_otm_certificate_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'configure_otm_certificate_template');

INSERT INTO ohrm_user_role_data_group (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self) VALUES
    (@admin_id, @configure_otm_certificate_data_group_id, 1, 1, 1, 0, 0);

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, @configure_otm_certificate_data_group_id, 1, 0, 0, 0);

INSERT INTO ohrm_screen (name, module_id, action_url) VALUES
    ( 'Configure Certificate Template', @onlinetraining_module_id, 'configureCertificateTemplate');

SET @certificate_configure_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'configureCertificateTemplate');
SET @online_assessment_courses_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Online Assessment Courses');

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`,
                              `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`,
                              `include_attachment_section`, `has_multiple_records`)
VALUES ('Configure Certificate Template', NULL, @certificate_configure_screen_id, @online_assessment_courses_menu_id, 4, 220, NULL,
        NULL, 'vuetraining.configure_certificate_template', '1', NULL, NULL);

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

INSERT INTO ohrm_data_group_screen (data_group_id, screen_id, permission, self) VALUES
    (@configure_otm_certificate_data_group_id,@certificate_configure_screen_id ,1 ,0);

-- Course certificate configure

UPDATE `ohrm_online_course_certificate_template`
SET body = REPLACE(body, '%imgUrl%', 'https://st.depositphotos.com/1575949/1859/v/950/depositphotos_18591401-stock-illustration-red-wax-seal-ribbon.jpg');

UPDATE `ohrm_online_course_certificate_template` SET body = REPLACE(body, 'width="200"', 'width="100"');

ALTER TABLE `ohrm_online_employee_course_certificate` ADD `template_id` INT(11) DEFAULT NULL;

ALTER TABLE `ohrm_online_employee_course_certificate` ADD CONSTRAINT FOREIGN KEY (`template_id`) REFERENCES `ohrm_online_course_certificate_template`(`id`) ON DELETE SET NULL;

UPDATE `ohrm_online_employee_course_certificate` SET `template_id`=1;