CREATE TABLE ohrm_requestdesk_request_type_template (id BIGINT AUTO_INCREMENT, name VARCHAR(150) NOT NULL,processor_class VARCHAR(150) NOT NULL,template text default NULL, approval_mandatory tinyint(1) DEFAULT 0 NOT NULL, status tinyint(1) DEFAULT 0 NOT NULL,PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;
CREATE TABLE ohrm_requestdesk_request_types (id BIGINT AUTO_INCREMENT, template_id BIGINT NOT NULL, name VARCHAR(150) NOT NULL, approver_required tinyint(1) DEFAULT 0 NOT NULL, approval_levels INT, primary_owner_id int(7), secondary_owner_id int(7),deleted tinyint(1) DEFAULT 0 NOT NULL, header LONGTEXT NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;

create table ohrm_requestdesk_custom_field_section (
    id int auto_increment,
    request_type_id BIGINT NOT NULL,
    display_name varchar(255),
    display_order smallint default 0,
    deleted tinyint(1) default 0,
    is_customizable tinyint(1) default 0,
    header LONGTEXT NOT NULL,
    primary key(id)
) engine = innodb default charset=utf8;

alter table `ohrm_requestdesk_custom_field_section` add constraint foreign key (`request_type_id`) references ohrm_requestdesk_request_types(`id`) on delete cascade;

INSERT INTO ohrm_requestdesk_request_type_template (`name`, `processor_class`, `template`, `approval_mandatory`, `status`) VALUES
 ('General Request', 'RequestDeskGeneralRequestProcessor','{
  "meta": {
    "terminalHandler": "",
    "maximumApprovalLevels": 5,
    "enableSubmitForOther": true
  },
  "formData": {
    "sections": [
      {
        "name": "Extra Custom Fields",
        "displayOrder": 1,
        "isCustomizable": true,
        "defaultFields": []
      }]

  }
}
', 0, 1);

create table `ohrm_request`(
    `id` int(11) AUTO_INCREMENT,
    `emp_number` int(7) NOT NULL,
    `request_type_id` BIGINT NOT NULL,
    `title` VARCHAR(500),
    `description` VARCHAR(5000),
    `priority` int(2),
    `resolution_date` date,
    `owner_id` int(7) default null,
    `pending_with_employee_id` int(7) default null,
    `workflow_request_id` int(11) default null,
    `resolution` VARCHAR(2000) default null,
    `requested_info` VARCHAR(4000) default null,
    `extra_details` text default NULL,
    `submitted_time_zone` varchar(255) default null,
    `submitted_date` timestamp not null default now(),
    `modified_date` datetime default null,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_request_submitted_timezone` FOREIGN KEY (`submitted_time_zone`) REFERENCES `ohrm_standard_time_zone` (`timezone_key`) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX `request_idx_submitted_date` (`submitted_date`)
)engine=innodb default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_request_attachment` (
    `request_id` INT,
    `attach_id` BIGINT,
    `attach_size` INT DEFAULT '0',
    `attach_desc` VARCHAR(200),
    `attach_filename` VARCHAR(255),
    `attach_attachment` LONGBLOB,
    `attach_type` VARCHAR(200),
    `screen` VARCHAR(100),
    `attached_by` INT,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(request_id,attach_id)
  ) ENGINE = INNODB DEFAULT CHARSET=utf8 ;

CREATE TABLE ohrm_request_comment (
    id INT(11) AUTO_INCREMENT,
    emp_number int(11),
    request_id int(11),
    comment varchar(2000),
    is_private boolean,
    created_date TIMESTAMP NOT NULL DEFAULT now(),
    modified_by int(11) NULL DEFAULT NULL,
    modified_date DATETIME default null,
    PRIMARY KEY(id)
) ENGINE = INNODB default charset=utf8;

alter table `ohrm_requestdesk_request_types` add constraint foreign key (`template_id`) references ohrm_requestdesk_request_type_template(`id`) on delete cascade;
alter table `ohrm_request` add constraint foreign key (`request_type_id`) references ohrm_requestdesk_request_types(`id`) on delete cascade;
alter table `ohrm_request` add constraint foreign key (`emp_number`) references `hs_hr_employee`(`emp_number`) on delete cascade;
alter table `ohrm_request` add constraint foreign key (`owner_id`) references `hs_hr_employee`(`emp_number`) on delete set null;
alter table `ohrm_request` add constraint foreign key (`workflow_request_id`) references `ohrm_advanced_workflow_request`(`id`) on delete set null;
alter table `ohrm_request` add constraint foreign key (`pending_with_employee_id`) references `hs_hr_employee`(`emp_number`) on delete set null;

alter table `ohrm_request_attachment`
    add constraint foreign key (`request_id`)
        references `ohrm_request`(`id`) on delete cascade;

alter table `ohrm_request_comment`
    add constraint foreign key (`request_id`)
        references `ohrm_request`(`id`) on delete cascade;
alter table `ohrm_request_comment`
    add constraint foreign key (`emp_number`)
        references `hs_hr_employee`(`emp_number`) on delete cascade;
alter table `ohrm_request_comment`
    add constraint foreign key (`modified_by`)
        references `hs_hr_employee`(`emp_number`) on delete set null;

ALTER TABLE `ohrm_request` CHANGE `description` `description` VARCHAR( 6000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE `ohrm_request` CHANGE `resolution` `resolution` VARCHAR( 6000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE `ohrm_request_comment` CHANGE `comment` `comment` VARCHAR( 6000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE `ohrm_request` ADD `archived` TINYINT( 1 ) NOT NULL DEFAULT '0';

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


INSERT INTO `ohrm_user_role` (`name` ,`display_name` ,`is_assignable` ,`is_predefined`) VALUES
('Requester', 'Requester', '0', '1'),
('RequestOwner', 'Request Owner', '0', '1'),
('RequestApprover', 'Request Approver', '0', '1'),
('AnyRole', 'Any Role', '0', '1'),
('ProxyRequester', 'Proxy Requester', '0', '1');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
( 'View Request Tracker Module', @module_id, 'viewRequestTrackerModule'),
( 'Add Request', @module_id, 'addRequest'),
( 'View Request', @module_id, 'viewRequest'),
( 'My General Request', @module_id, 'viewMyGeneralRequests'),
( 'Employee General Request', @module_id, 'viewEmployeeGeneralRequests'),
( 'View Request Information Report', @module_id, 'viewRequestInformationReport'),
( 'View Request Form Types', @module_id, 'viewHelpRequestFormTypes');

SET @main_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewRequestTrackerModule');
SET @view_request_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewRequest');
SET @my_general_requests_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewMyGeneralRequests');
SET @add_request_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'addRequest');
SET @employee_general_requests_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewEmployeeGeneralRequests');


SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');
SET @sup_id := (SELECT id FROM ohrm_user_role where name = 'Supervisor');
SET @ess_id := (SELECT id FROM ohrm_user_role where name = 'ESS');
SET @requester_id := (SELECT id FROM ohrm_user_role where name = 'Requester');
SET @request_owner_id := (SELECT id FROM ohrm_user_role where name = 'RequestOwner');
SET @request_approver_id := (SELECT id FROM ohrm_user_role where name = 'RequestApprover');
SET @proxy_requester_id := (SELECT id FROM ohrm_user_role where name = 'ProxyRequester');

INSERT INTO ohrm_module_default_page (module_id , user_role_id, action, priority) VALUES
(@module_id, @admin_id, 'requestTracker/viewEmployeeRequests', 10),
(@module_id, @requester_id, 'requestTracker/viewMyRequests', 20),
(@module_id, @request_owner_id, 'requestTracker/viewEmployeeRequests', 30),
(@module_id, @request_approver_id, 'requestTracker/viewEmployeeRequests', 40);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_id, @main_screen_id, 1, 0, 0, 0),
(@requester_id, @main_screen_id, 1, 0, 0, 0),
(@request_owner_id, @main_screen_id, 1, 0, 0, 0),
(@request_approver_id, @main_screen_id, 1, 0, 0, 0),
(@admin_id, @view_request_screen_id, 1, 1, 1, 1),
(@requester_id, @view_request_screen_id, 1, 1, 1, 1),
(@request_owner_id, @view_request_screen_id, 1, 1, 1, 1),
(@request_approver_id, @view_request_screen_id, 1, 1, 1, 1),
(@requester_id, @add_request_screen_id, 1, 1, 1, 1),
(@requester_id, @my_general_requests_screen_id, 1, 1, 1, 1),
(@admin_id, @employee_general_requests_screen_id, 1, 1, 1, 1),
(@request_owner_id, @employee_general_requests_screen_id, 1, 1, 1, 1),
(@request_approver_id, @employee_general_requests_screen_id, 1, 1, 1, 1),
(@proxy_requester_id, @employee_general_requests_screen_id, 1, 1, 1, 1);


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('add_request', 'Request Desk - Add Request', 1, 1, 1, 1),
('my_requests', 'Request Desk - My Requests', 1, 1, 1, 1),
('employee_requests', 'Request Desk - Employee Requests', 1, 1, 1, 1),
('request_attachment', 'Request Desk - Request Attachment', 1, 1, 1, 1),
('my_profile', 'Request Desk - Update My Profile', 1, 1, 1, 1);

SET @my_Profile_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'my_profile');
SET @add_request_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'add_request');
SET @my_requests_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'my_requests');
SET @employee_requests_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'employee_requests');
SET @request_attachement_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'request_attachment');


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, @my_Profile_data_group_id, 1, 1, 1, 0, 0),
(@ess_id, @my_Profile_data_group_id, 1, 1, 1, 0, 0),
(@requester_id, @my_Profile_data_group_id, 1, 1, 1, 0, 0),
(@request_owner_id, @my_Profile_data_group_id, 1, 1, 1, 0, 0),
(@request_approver_id, @my_Profile_data_group_id, 1, 1, 1, 0, 0),
(@admin_id, @add_request_data_group_id, 1, 1, 1, 1, 0),
(@requester_id, @add_request_data_group_id, 1, 1, 1, 1, 0),
(@admin_id, @my_requests_data_group_id, 1, 1, 1, 1, 0),
(@requester_id, @my_requests_data_group_id, 1, 1, 1, 1, 0);

SET @other_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Other' AND `level` = 1);

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`)
VALUES (NULL, 'Request Desk', NULL, NULL, @other_pillar_menu_id, '2', '10', NULL, 'request_desk.svg', NULL, '1', NULL, NULL);
SET @request_desk_parent_menu_id := LAST_INSERT_ID();

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`)
VALUES (NULL, 'Employee Requests', NULL, NULL, @request_desk_parent_menu_id, '3', '1000', NULL, NULL, NULL, '1', NULL, NULL);
SET @request_desk_employee_requests_menu_id := LAST_INSERT_ID();

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`)
VALUES (NULL, 'My Requests', NULL, NULL, @request_desk_parent_menu_id, '3', '1000', NULL, NULL, NULL, '1', NULL, NULL);
SET @request_desk_my_requests_menu_id := LAST_INSERT_ID();

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`,`parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Submit Request', @add_request_screen_id,  @request_desk_parent_menu_id, '3', '500', null, 'help_desk.submit_request', '1');

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`,`parent_id`, `level`, `order_hint`,  `url_extras`,`status`) VALUES
('My General Requests', @my_general_requests_screen_id,  @request_desk_my_requests_menu_id, '4', '800', null, '1');
INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`,`parent_id`, `level`, `order_hint`,  `url_extras`,`status`) VALUES
('Employee General Requests', @employee_general_requests_screen_id,  @request_desk_employee_requests_menu_id, '4', '600', null, '1');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@my_requests_data_group_id, @my_general_requests_screen_id, 1, 0),
(@my_requests_data_group_id, @main_screen_id, 1, 0),
(@my_requests_data_group_id, @view_request_screen_id, 1, 0),
(@employee_requests_data_group_id, @employee_general_requests_screen_id, 1, 0),
(@employee_requests_data_group_id, @main_screen_id, 1, 0),
(@employee_requests_data_group_id, @view_request_screen_id, 1, 0),
(@add_request_data_group_id, @add_request_screen_id, 1, 0),
(@add_request_data_group_id, @main_screen_id, 1, 0);

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, @employee_requests_data_group_id, 1, 0, 1, 1, 0),
(@request_owner_id, @employee_requests_data_group_id, 1, 0, 1, 0, 0),
(@request_approver_id, @employee_requests_data_group_id, 1, 0, 1, 0, 0),
(@proxy_requester_id, @employee_requests_data_group_id, 1, 0, 1, 1, 0),
(@admin_id, @request_attachement_data_group_id, 1, 1, 1, 1, 0),
(@requester_id, @request_attachement_data_group_id, 1, 1, 0, 0, 0),
(@request_owner_id, @request_attachement_data_group_id, 1, 1, 0, 0, 0),
(@request_approver_id, @request_attachement_data_group_id, 1, 1, 0, 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
(@admin_id, @employee_requests_data_group_id, 1, 0, 1, 1),
(@admin_id, @request_attachement_data_group_id, 1, 1, 1, 1);

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('requestTracker.change_request', 'RequestTrackerMailer', '1', '39'),
('requestTracker.request_update', 'RequestTrackerAuditListener', '1', '40'),
('requestTracker.add_request_type', 'RequestTypeAuditListener', '1', '44'),
('requestTracker.delete_request_type', 'RequestTypeAuditListener', '1', '45'),
('requestTracker.update_request_type', 'RequestTypeAuditListener', '1', '46'),
('requestTracker.add_custom_field', 'RequestTrackerCustomFieldAuditListener', '1', '50'),
('requestTracker.update_custom_field', 'RequestTrackerCustomFieldAuditListener', '1', '51'),
('requestTracker.delete_custom_field', 'RequestTrackerCustomFieldAuditListener', '1', '52'),
('requestTracker.custom_field_value_update', 'RequestTrackerCustomFieldValueAuditListener', '1', '55');

-- INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`,`parent_id`, `level`, `order_hint`,  `url_extras`,`status`) VALUES
-- ('Reports', null,  @request_desk_parent_menu_id, '2', '1000', null, '1');
-- SET @reports_menu_id := LAST_INSERT_ID();



INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('request_information_report', 'Request Desk - Dashboard Report', 1, 0, 0, 0);

SET @report_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewRequestInformationReport');
SET @report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'request_information_report');

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, @report_data_group_id, 1, 0, 0, 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
-- (@admin_id, @report_data_group_id, 1, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@report_data_group_id, @report_screen_id, 1, 0),
(@report_data_group_id, @main_screen_id, 1, 0);

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

SET @max_report_id := ( SELECT MAX(  `id` ) FROM ohrm_advanced_report ) ;

-- INSERT INTO ohrm_advanced_report (id, name, definition, unique_name) VALUES
-- (@max_report_id + 1, "Request Information Report", 'file://plugins/orangehrmRequestTrackerPlugin/install/reports/request_details_report.xml', 'Request_Inforamtion_Report');

-- INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`,`parent_id`, `level`, `order_hint`,  `url_extras`,`status`) VALUES
-- ('Request Information Report', @report_screen_id,  @reports_menu_id, '3', '200', concat('/reportId/', @max_report_id + 1), '1');



SET @data_group_id := (SELECT id from ohrm_data_group WHERE name = 'employee_requests');
SET @request_owner_id := (SELECT id FROM ohrm_user_role where name = 'RequestOwner');
SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');
SET @requester_user_role_id := (SELECT id FROM ohrm_user_role where name = 'Requester');

UPDATE ohrm_user_role_data_group SET can_delete = 1 WHERE data_group_id =  @data_group_id AND user_role_id IN (@admin_id);

SET @data_group_id := (SELECT id from ohrm_data_group WHERE name = 'my_requests');
UPDATE ohrm_user_role_data_group SET can_delete = 0 WHERE data_group_id =  @data_group_id AND user_role_id NOT IN (@requester_user_role_id,@admin_id);

UPDATE `ohrm_menu_item` SET `uisref` = 'report_view({"id": "Request_Inforamtion_Report", "filters": null})' WHERE `ohrm_menu_item`.`menu_title` = 'Request Information Report';
UPDATE `ohrm_menu_item` SET `status` = '0' WHERE `ohrm_menu_item`.`menu_title` = 'Requesters';
UPDATE `ohrm_menu_item` SET `status` = '0' WHERE `ohrm_menu_item`.`menu_title` = 'Request Owners';

ALTER TABLE ohrm_requestdesk_request_types ADD FOREIGN KEY (`primary_owner_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE CASCADE;
ALTER TABLE ohrm_requestdesk_request_types ADD FOREIGN KEY (`secondary_owner_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE CASCADE;


SET @help_form_type_screen := (SELECT id FROM `ohrm_screen` WHERE name = 'View Request Form Types');

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`,`parent_id`, `level`, `order_hint`, `uisref`, `url_extras`,`status`) VALUES
('Configure Request Types', @help_form_type_screen,  @request_desk_parent_menu_id, '3', '200', 'help_desk.form_types_configuration', null, '1');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('request_desk_form_types', 'Request Desk - Configure Request Types', 1, 1, 1, 1);

SET @form_type_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'request_desk_form_types');

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, @form_type_data_group_id, 1, 1, 1, 1, 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
 (@admin_id, @form_type_data_group_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@form_type_data_group_id, @help_form_type_screen, 1, 0);

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

-- INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
-- ('request_form_types', 'Request Desk - Request Form Types Config', 1, 1, 1, 1);

-- SET @request_form_types_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'request_form_types');
--
-- 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, @request_form_types_data_group_id, 1, 1, 1, 1, 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
--  (@admin_id, @request_form_types_data_group_id, 1, 1, 1, 1);

create table ohrm_requestdesk_custom_field (
  `id` int not null auto_increment,
  `name` varchar(250) not null,
  `unique_name` varchar(1275) null,
  `type` int(11) not null,
  `section_id` int not null,
  `extra_data` text default null,
  `display_order` smallint default 0 not null,
  `mandatory` tinyint(1) default 0 not null,
  `deletable` tinyint(1) default 1 not null,
  `deleted` tinyint(1) default 0 not null,
  `is_default_field` tinyint(1) default 0 not null,
  `other_data` text default NULL,
  primary key  (`id`)
) engine=innodb default charset=utf8;

alter table `ohrm_requestdesk_custom_field` add constraint foreign key (`section_id`) references ohrm_requestdesk_custom_field_section (`id`) on delete cascade;

CREATE TABLE `ohrm_vas_custom_field_display_condition` (
`id` INT AUTO_INCREMENT,
`customfieldid` INT,
`parentcustomfieldId` INT,
`parentcustomfieldvalue` VARCHAR(255),
`required` TINYINT,
PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET=utf8;

ALTER TABLE ohrm_requestdesk_custom_field
ADD COLUMN `advance_type_map` INT NULL AFTER `mandatory`;

CREATE TABLE `ohrm_vas_custom_advance_type_map` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `entity` VARCHAR(255) NOT NULL,
  `display_name` VARCHAR(255) NOT NULL,
  `id_field` VARCHAR(255) NOT NULL,
  `name_field` VARCHAR(255) NOT NULL,
    `allow_multiple`  TINYINT(1) DEFAULT 0 NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_vas_custom_advance_type_map`(`entity`,`display_name`,`id_field`,`name_field`) VALUES ('JobTitle','Job Title','id','jobTitleName');
INSERT INTO `ohrm_vas_custom_advance_type_map`(`entity`,`display_name`,`id_field`,`name_field`) VALUES ('Subunit','Sub Unit','id','name');

create table ohrm_request_custom_field_value (
  id int not null auto_increment,
  request_id int(11) not null,
  custom_field_id int not null,
  value_type tinyint default 1 not null,
  string_value text null,
  text_value text null,
  number_value double null,
  date_value date null,
  boolean_value boolean null,
  version BIGINT(20) DEFAULT NULL,
  sequence_number INT NOT NULL,
  primary key(id),
  FOREIGN KEY (`request_id`) REFERENCES `ohrm_request`(`id`) ON DELETE CASCADE,
  foreign key (custom_field_id) references ohrm_requestdesk_custom_field(`id`) on delete cascade
) engine=innodb default charset=utf8;

create table ohrm_request_custom_field_value_history (
  id int not null,
  request_id int(11) not null,
  custom_field_id int not null,
  value_type tinyint default 1 not null,
  string_value text null,
  text_value text null,
  number_value double null,
  date_value date null,
  boolean_value boolean null,
  sequence_number INT NOT NULL,
  version BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`id`, `version`),
  CONSTRAINT `ohrm_request_custom_field_value_history_emp_number` FOREIGN KEY (`request_id`) REFERENCES `ohrm_request`(`id`) ON DELETE CASCADE,
  CONSTRAINT `ohrm_request_custom_field_value_history_custom_field` foreign key (custom_field_id) references ohrm_requestdesk_custom_field(`id`) on delete cascade
) engine=innodb default charset=utf8;

create table ohrm_request_custom_field_attachment (
  id int not null auto_increment,
  custom_field_value_id int not null,
  file_name varchar(255) not null,
  file_size int not null,
  file_type varchar(255) not null,
  content longblob not null,
  primary key(id),
  foreign key (custom_field_value_id) references ohrm_request_custom_field_value(`id`) on delete cascade
) engine=innodb default charset=utf8;

-- ALTER TABLE `ohrm_custom_field_value` ADD CONSTRAINT `ohrm_custom_field_value_request` FOREIGN KEY (`request_id`) REFERENCES `ohrm_request`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
--
-- ALTER TABLE `ohrm_custom_field_value_history` ADD CONSTRAINT `ohrm_custom_field_value_history_request` FOREIGN KEY (`request_id`) REFERENCES `ohrm_request`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;



ALTER TABLE `ohrm_request_comment`
ADD COLUMN `visible_to` varchar (150) NULL AFTER `is_private`;

UPDATE `ohrm_menu_item` SET `uisref` = 'help_desk.my_general_requests' WHERE `ohrm_menu_item`.`menu_title` = 'My General Requests';
UPDATE `ohrm_menu_item` SET `uisref` = 'help_desk.employee_general_requests' WHERE `ohrm_menu_item`.`menu_title` = 'Employee General Requests';


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('helpdesk_reports', 'Help Desk - Reports', 1, 0, 0, 0);

SET @request_information_report_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewRequestInformationReport');
SET @report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'helpdesk_reports');

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

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@report_data_group_id, @request_information_report_screen_id, 1, 0);

-- INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`) VALUES (NULL, 'Requests by Type', NULL, @report_screen_id, @reports_menu_id, '3', '300', '/reportId/3', NULL, 'report_view({\"id\": \"helpdesk_requests_by_type\", \"filters\": null})', '1', NULL, NULL);
-- INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`) VALUES (NULL, 'Requests by Status', NULL, @report_screen_id, @reports_menu_id, '3', '400', '/reportId/3', NULL, 'report_view({\"id\": \"helpdesk_requests_by_status\", \"filters\": null})', '1', NULL, NULL);
-- INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`) VALUES (NULL, 'Requests by Priority', NULL, @report_screen_id, @reports_menu_id, '3', '500', '/reportId/3', NULL, 'report_view({\"id\": \"helpdesk_requests_by_priority\", \"filters\": null})', '1', NULL, NULL);
-- INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`) VALUES (NULL, 'Request Closure Report', NULL, @report_screen_id, @reports_menu_id, '3', '700', '/reportId/3', NULL, 'report_view({\"id\": \"helpdesk_request_closure_by_owner\", \"filters\": null})', '1', NULL, NULL);
-- INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`) VALUES (NULL, 'Dashboard', NULL, @report_screen_id, @reports_menu_id, '3', '100', '/reportId/3', NULL, 'help_desk.dashboard', '1', NULL, NULL);

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
( 'maintenance.employee_purge_start', 'RequestDeskPurgeEmployeeListener', '1', '91');

DELIMITER $$

DROP TRIGGER IF EXISTS `ohrm_vas_custom_field_display_order`;$$

CREATE TRIGGER ohrm_vas_custom_field_display_order
BEFORE INSERT
ON ohrm_requestdesk_custom_field FOR EACH ROW
BEGIN
        SET @ROW_COUNT:=(SELECT COUNT(id) FROM ohrm_requestdesk_custom_field);
        SET NEW.display_order=@ROW_COUNT+1;
END; $$

DELIMITER ;
-- adding location and employee advanced type
INSERT INTO `ohrm_vas_custom_advance_type_map` (`entity`, `display_name`, `id_field`, `name_field`) VALUES ('Location', 'Location', 'id', 'name');
INSERT INTO `ohrm_vas_custom_advance_type_map` (`entity`, `display_name`, `id_field`, `name_field`, `allow_multiple`) VALUES ('Employee', 'Employee Multiple Select', 'empNumber', 'firstName', 1);

SET @next_wf_order = (SELECT max(order_no)
                      FROM ohrm_workflow);

INSERT INTO `ohrm_workflow`(`id`,`name`, `label`, `available_to`, `default_available_to`, `module_id`,`order_no`) VALUES
('REQUEST','requestDesk','Request Desk - Requests With Approval',1,2,@module_id,@next_wf_order+1),
('REQUEST_WITHOUT_APPROVE','requestDeskWithOutApprove','Request Desk - Requests Without Approval',1,2,@module_id,@next_wf_order+2);

SET @requestStatusReportID := (SELECT id from ohrm_advanced_report WHERE unique_name = 'helpdesk_requests_by_status');
SET @requestTypesReportID := (SELECT id from ohrm_advanced_report WHERE unique_name = 'helpdesk_requests_by_type');
SET @requestPrioritiesReportID := (SELECT id from ohrm_advanced_report WHERE unique_name = 'helpdesk_requests_by_priority');
SET @requestDashboardReportID := (SELECT id from ohrm_advanced_report WHERE unique_name = 'helpdesk_request_dashboard_summary_1');

SET @system_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'SYSTEM');
SET @system_user_id := (SELECT `id` FROM `ohrm_user` WHERE `user_role_id` = @system_user_role_id);

SET @menu_item_id = (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Request Desk' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Request Desk', @system_user_id, 12);
SET @request_desk_folder_id = (SELECT id FROM ohrm_report_folder WHERE name = 'Request Desk');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`) VALUES
    (@request_desk_folder_id, @menu_item_id );

UPDATE `ohrm_advanced_report` SET `folder_id` = @request_desk_folder_id, `custom_url`= 'report_view({"id": "helpdesk_requests_by_status", "filters": null})', `created_by`= @system_user_id WHERE `id` = @requestStatusReportID;
UPDATE `ohrm_advanced_report` SET `folder_id` = @request_desk_folder_id, `custom_url`= 'report_view({"id": "helpdesk_requests_by_type", "filters": null})', `created_by`= @system_user_id WHERE `id` = @requestTypesReportID;
UPDATE `ohrm_advanced_report` SET `folder_id` = @request_desk_folder_id, `custom_url`= 'report_view({"id": "helpdesk_requests_by_priority", "filters": null})', `created_by`= @system_user_id WHERE `id` = @requestPrioritiesReportID;
UPDATE `ohrm_advanced_report` SET `folder_id` = @request_desk_folder_id, `custom_url`= '/help_desk/dashboard', `created_by`= @system_user_id WHERE `id` = @requestDashboardReportID;

-- Approver level User Role Changes --
INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`, `is_editable`, `deletable`) VALUES
    ('LevelOneRequestApprover', 'Level 1 Request Approver', 0, 1, 0, 0),
    ('LevelTwoRequestApprover', 'Level 2 Request Approver', 0, 1, 0, 0),
    ('LevelThreeRequestApprover', 'Level 3 Request Approver', 0, 1, 0, 0),
    ('LevelFourRequestApprover', 'Level 4 Request Approver', 0, 1, 0, 0),
    ('LevelFiveRequestApprover', 'Level 5 Request Approver', 0, 1, 0, 0);

create table `ohrm_request_notification_history`
(
    `id` int(13) not null auto_increment,
    `notification_history_id` int(13) not null,
    `request_id` int NOT NULL,
    `tokens` text DEFAULT NULL,
    primary key (`id`),
    CONSTRAINT `fk_request_notification_notification_history` FOREIGN KEY (`notification_history_id`) REFERENCES `ohrm_notification_history` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_request_notification_history_request` FOREIGN KEY (`request_id`) REFERENCES `ohrm_request` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `ohrm_request_generated_document`
(
    `id` int(13) not null auto_increment,
    `generated_document_id` int(13) not null,
    `request_id` INT(11) NOT NULL,
    `tokens` text DEFAULT NULL,
    primary key (`id`),
    CONSTRAINT `fk_request_generated_document_generated_document` FOREIGN KEY (`generated_document_id`) REFERENCES `ohrm_generated_document` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_request_generated_document_request` FOREIGN KEY (`request_id`) REFERENCES `ohrm_request` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `ohrm_vas_notification_history_attachment`
(
    `id` int(13) not null auto_increment,
    `notification_history_id` int(13) not null,
    `attachment_id` INT(13) NOT NULL,
    primary key (`id`),
    CONSTRAINT `fk_notification_history_attachment_notification_history` FOREIGN KEY (`notification_history_id`) REFERENCES `ohrm_notification_history` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_notification_history_attachment_generated_document` FOREIGN KEY (`attachment_id`) REFERENCES `ohrm_generated_document` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
