CREATE TABLE ohrm_leave_type (
  `id` int unsigned not null auto_increment,
  `name` varchar(50) not null,
  `deleted` tinyint(1) not null default 0,
  `deletable` tinyint(1) not null default 1,
  `leave_rule_modifiable` tinyint(1) not null default 1,
  `exclude_in_reports_if_no_entitlement` tinyint(1) not null default 0,
  `include_in_bradford_factor_report` tinyint(1) not null default 0,
  `operational_country_id` int unsigned default null,
  `hour_based` TINYINT( 1 ) NOT NULL DEFAULT '0',
  `is_short_term` TINYINT( 1 ) NOT NULL DEFAULT '0',
  `employee_nominee_enabled` tinyint not null default 0,
  primary key  (`id`)
) engine=innodb default charset=utf8;

CREATE TABLE ohrm_leave_entitlement_type(
  `id` int unsigned not null auto_increment,
  `name` varchar(50) not null,
  `is_editable`  tinyint(1) not null default 0,
  PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_leave_entitlement (
  `id` int unsigned not null auto_increment,
  emp_number int(7) not null,
  `length` decimal(19,9) NOT NULL,
  `used` decimal(19,9) not null default '0.000000000000000',
  leave_type_id int unsigned not null,
  from_date datetime not null,
  to_date datetime,
  credited_date datetime,
  note varchar(255) default null, 
  entitlement_type int unsigned not null,
  carry_forward tinyint(1) not null default 1,
  `deleted` tinyint(1) not null default 0,
  created_by_id int(10),
  created_by_name varchar(255),
  PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_leave_adjustment (
  `id` int unsigned not null auto_increment,
  emp_number int(7) not null,
  `length` decimal(19,9) not null,
  leave_type_id int unsigned not null,
  from_date datetime,
  to_date datetime,
  credited_date datetime,
  note varchar(255) default null,
  adjustment_type int unsigned not null default 0, 
  `deleted` tinyint(1) not null default 0,
  created_by_id int(10),
  created_by_name varchar(255),
  PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

-- Do we need the field duplication here (leave_request and leave)?
CREATE TABLE `ohrm_leave_request` (
  `id` int unsigned NOT NULL auto_increment,
  `leave_type_id` int unsigned NOT NULL,
  `date_applied` date NOT NULL,
  `emp_number` int(7) NOT NULL,
  `comments` varchar(256) default NULL,
  `attachment_config` smallint default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave` (
  `id` int(11) NOT NULL  auto_increment,
  `date` date default NULL,
  `length_minutes` decimal(8,4) unsigned default NULL,
  `length_days` decimal(6,4) unsigned default NULL,
  `status` smallint(6) default NULL,
  `comments` varchar(256) default NULL,
  `leave_request_id`int unsigned NOT NULL,
  `leave_type_id` int unsigned NOT NULL,
  `emp_number` int(7) NOT NULL,
  `start_time` time default NULL,
  `end_time` time default NULL,
  `start_utc_datetime` DATETIME,
  `end_utc_datetime` DATETIME,
  `duration_type` tinyint NOT NULL default 0,
  PRIMARY KEY  (`id`),
  KEY `leave_request_type_emp`(`leave_request_id`,`leave_type_id`,`emp_number`),
  KEY `request_status` (`leave_request_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave_comment` (
  `id` int(11) NOT NULL  auto_increment,
  `leave_id` int(11) NOT NULL,
  `created` datetime default NULL,
  `created_by_name` varchar(255) NOT NULL,
  `created_by_id` int(10),
  `created_by_emp_number` int(7) default NULL,
  `comments` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave_request_comment` (
  `id` int(11) NOT NULL  auto_increment,
  `leave_request_id` int unsigned NOT NULL,
  `created` datetime default NULL,
  `created_by_name` varchar(255) NOT NULL,
  `created_by_id` int(10),
  `created_by_emp_number` int(7) default NULL,
  `comments` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave_entitlement_comment` (
  `id` int(11) NOT NULL  auto_increment,
  `leave_entitlement_id` int unsigned NOT NULL,
  `created` datetime default NULL,
  `created_by_name` varchar(255) NOT NULL,
  `created_by_id` int(10),
  `created_by_emp_number` int(7) default NULL,
  `comment` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create TABLE `ohrm_leave_leave_entitlement` (
    `id` int(11) NOT NULL   auto_increment,
    `leave_id` int(11) NOT NULL,
    `entitlement_id` int unsigned NOT NULL,
    `length` decimal(19,9) unsigned default NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create TABLE `ohrm_leave_entitlement_adjustment` (
    `id` int(11) NOT NULL   auto_increment,
    `adjustment_id` int unsigned NOT NULL,
    `entitlement_id` int unsigned NOT NULL,
    `length` decimal(19,9) default NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave_period_history` (
  `id` int(11) NOT NULL auto_increment,
  `leave_period_start_month` int NOT NULL,
  `leave_period_start_day` int NOT NULL,
  `created_at` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave_status` (
  `id` int(11) NOT NULL auto_increment,
  `status` smallint(6) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_leave_request_attachment_configuration` (
  `id` SMALLINT NOT NULL,
  `status` VARCHAR (255) NOT NULL,
  `label` VARCHAR (255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `ohrm_leave_request_attachment`(
    `id` int(13) not null auto_increment,
    `leave_request_id` int(10) unsigned NOT NULL,
    `file_name` varchar(200) not null,
    `file_type` varchar(200) default null,
    `file_size` int(11) not null,
    `file_content` mediumblob,
    `description` varchar(255),
    primary key (`id`)
)engine=innodb default charset=utf8;

alter table ohrm_leave_type
    add foreign key (operational_country_id)
        references ohrm_operational_country(id) on delete set null;

alter table ohrm_leave_entitlement
    add foreign key (leave_type_id)
        references ohrm_leave_type(id) on delete cascade;

alter table ohrm_leave_entitlement
    add foreign key (emp_number)
        references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_leave_entitlement
    add foreign key (entitlement_type)
        references ohrm_leave_entitlement_type(id) on delete cascade;

alter table ohrm_leave_entitlement
    add foreign key (created_by_id)
        references ohrm_user(`id`) on delete set null;

alter table ohrm_leave_adjustment
    add foreign key (leave_type_id)
        references ohrm_leave_type(id) on delete cascade;

alter table ohrm_leave_adjustment
    add foreign key (emp_number)
        references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_leave_adjustment
    add foreign key (created_by_id)
        references ohrm_user(`id`) on delete set null;

alter table ohrm_leave_adjustment
    add foreign key (adjustment_type)
        references ohrm_leave_entitlement_type(id) on delete cascade;

alter table ohrm_leave_request
    add constraint foreign key (emp_number)
        references hs_hr_employee (emp_number) on delete cascade;

alter table ohrm_leave_request
    add constraint foreign key (leave_type_id)
        references ohrm_leave_type (id) on delete cascade;

alter table ohrm_leave_request
    add constraint `fk_ohrm_leave_request_attachment_config` foreign key (attachment_config)
        references ohrm_leave_request_attachment_configuration (id) on delete set NULL;

alter table ohrm_leave
    add foreign key (leave_request_id)
        references ohrm_leave_request(id) on delete cascade;

alter table ohrm_leave
    add constraint foreign key (leave_type_id)
        references ohrm_leave_type (id) on delete cascade;

alter table ohrm_leave
    add constraint `ohrm_leave_emp_number` foreign key (emp_number)
        references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_leave_leave_entitlement
    add constraint foreign key (entitlement_id)
        references ohrm_leave_entitlement (id) on delete cascade;

alter table ohrm_leave_leave_entitlement
    add constraint foreign key (leave_id)
        references ohrm_leave (id) on delete cascade;

alter table ohrm_leave_entitlement_adjustment
    add constraint foreign key (entitlement_id)
        references ohrm_leave_entitlement (id) on delete cascade;

alter table ohrm_leave_entitlement_adjustment
    add constraint foreign key (adjustment_id)
        references ohrm_leave_adjustment (id) on delete cascade;

alter table ohrm_leave_comment
    add constraint foreign key (leave_id)
        references ohrm_leave(id) on delete cascade;

alter table ohrm_leave_comment
    add constraint foreign key (created_by_id)
        references ohrm_user(`id`) on delete set NULL;

alter table ohrm_leave_comment
    add constraint foreign key (created_by_emp_number)
        references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_leave_request_comment
    add constraint foreign key (leave_request_id)
        references ohrm_leave_request(id) on delete cascade;

alter table ohrm_leave_request_comment
    add constraint foreign key (created_by_id)
        references ohrm_user(`id`) on delete set NULL;

alter table ohrm_leave_request_comment
    add constraint foreign key (created_by_emp_number)
        references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_leave_entitlement_comment
    add constraint foreign key (leave_entitlement_id)
        references ohrm_leave_entitlement(id) on delete cascade;

alter table ohrm_leave_entitlement_comment
    add constraint foreign key (created_by_id)
        references ohrm_user(`id`) on delete set NULL;

alter table ohrm_leave_entitlement_comment
    add constraint foreign key (created_by_emp_number)
        references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_leave_request_attachment
    add constraint foreign key (leave_request_id)
        references ohrm_leave_request (id) on delete cascade;

INSERT INTO `ohrm_module` (`name`, `status`) VALUES
('leave', 1);

SET @leave_module_id = (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_config`(`property`, `value`) VALUES
    ('leave.entitlement_consumption_algorithm', 'FIFOEntitlementConsumptionStrategy'),
    ('leave.work_schedule_implementation', 'WorkScheduleBasedWorkCalendar'),
    ('leave.leavePeriodStatus',1),
    ('leave.include_pending_leave_in_balance', 1),
    ('leave.include_self_in_add_entitlement', 'No'),
    ('leave.periods_admins_assign_outside_current', '1'),
    ('leave.monitor_accrual', 'No');

INSERT INTO `hs_hr_unique_id`(last_id, table_name, field_name) VALUES
    (0, 'hs_hr_leave', 'leave_id'),
    (0, 'hs_hr_leavetype', 'leave_type_id'),
    (0, 'hs_hr_leave_requests', 'leave_request_id');

SET @leave_workflow_id := 4;

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`) VALUES
('4', 'leave', 'Leave', '1,3', '2', NULL, NULL, @leave_module_id, 1);

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
    (@leave_workflow_id, 'INITIAL', 'ESS', 'APPLY', 'PENDING APPROVAL', 'Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'INITIAL', 'ADMIN', 'ASSIGN', 'SCHEDULED', 'ess,Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'INITIAL', 'SUPERVISOR', 'ASSIGN', 'SCHEDULED', 'ess,Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'ADMIN', 'APPROVE', 'SCHEDULED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'SUPERVISOR', 'APPROVE', 'SCHEDULED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'ESS', 'CANCEL', 'CANCELLED', 'Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'ADMIN', 'REJECT', 'REJECTED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'PENDING APPROVAL', 'SUPERVISOR', 'REJECT', 'REJECTED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'SCHEDULED', 'ESS', 'CANCEL', 'CANCELLED', 'Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'SCHEDULED', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'SCHEDULED', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'TAKEN', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED PENDING APPROVAL', 'ESS', 'CANCEL', 'CANCELLED', 'Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED PENDING APPROVAL', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED PENDING APPROVAL', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED SCHEDULED', 'ESS', 'CANCEL', 'CANCELLED', 'Supervisor,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED SCHEDULED', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED SCHEDULED', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0),
    (@leave_workflow_id, 'LEAVE TYPE DELETED TAKEN', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,Subscriber', 0);

INSERT INTO `ohrm_workflow_screen`(`workflow_name`, `module`, `action`) VALUES
('leave', 'leave', 'assignLeave'),
('leave', 'leave', 'assignLeaveAdvanced');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
    ('Leave Type List', @leave_module_id, 'leaveTypeList'),
    ('Define Leave Type', @leave_module_id, 'defineLeaveType'),
    ('Undelete Leave Type', @leave_module_id, 'undeleteLeaveType'),
    ('Delete Leave Type', @leave_module_id, 'deleteLeaveType'),
    ('View Holiday List', @leave_module_id, 'viewHolidayList'),
    ('Define Holiday', @leave_module_id, 'defineHoliday'),
    ('Delete Holiday', @leave_module_id, 'deleteHoliday'),
    ('Leave List', @leave_module_id, 'viewLeaveList'),
    ('Assign Leave', @leave_module_id, 'assignLeave'),
    ('Save Leave Entitlements', @leave_module_id, 'saveLeaveEntitlements'),
    ('Define Leave Period', @leave_module_id, 'defineLeavePeriod'),
    ('View My Leave List', @leave_module_id, 'viewMyLeaveList'),
    ('Apply Leave', @leave_module_id, 'applyLeave'),
    ('View Leave Module', @leave_module_id, 'viewLeaveModule'),
    ('Leave Entitlements', @leave_module_id, 'viewLeaveEntitlements'),
    ('My Leave Entitlements', @leave_module_id, 'viewMyLeaveEntitlements'),
    ('Delete Leave Entitlements', @leave_module_id, 'deleteLeaveEntitlements'),
    ('Add Leave Entitlement', @leave_module_id, 'addLeaveEntitlement'),
    ('Edit Leave Entitlement', @leave_module_id, 'editLeaveEntitlement'),
    ('Leave Balance Report', @leave_module_id, 'viewLeaveBalanceReport'),
    ('My Leave Balance Report', @leave_module_id, 'viewMyLeaveBalanceReport'),
    ('View Leave Request', @leave_module_id, 'viewLeaveRequest'),
    ('Change Leave Status', @leave_module_id, 'changeLeaveStatus');

SET @view_leave_module_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View Leave Module');

SET @compensation_pillar_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Compensation' AND `level` = 1);
SET @pto_leave_management_module_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Leave' AND `parent_id` = @compensation_pillar_menu_id);
UPDATE ohrm_menu_item SET screen_id = @view_leave_module_screen_id, uisref = 'leave.define_leave_period' WHERE id = @pto_leave_management_module_menu_id;


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

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
    ('Configure', NULL, @pto_leave_management_module_menu_id, 3, 800, NULL, 0);
SET @configure_menu_id := (SELECT LAST_INSERT_ID());

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 @reporting_and_analytics_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Reports and Analytics' 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
    ('Entitlements', NULL, @pto_leave_management_module_menu_id, 3, 700, NULL, 0);
SET @entitlement_menu_id := (SELECT LAST_INSERT_ID());

SET @define_leave_period_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Define Leave Period');
SET @leave_types_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Leave Type List');
SET @undelete_leave_type_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Undelete Leave Type');
SET @delete_leave_type_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Delete Leave Type');
SET @view_holiday_list_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View Holiday List');
SET @leave_list_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Leave List');
SET @assign_leave_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Assign Leave');
SET @view_leave_module_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View Leave Module');
SET @my_leave_list_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View My Leave List');
SET @apply_leave_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Apply Leave');
SET @add_entitlement_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Add Leave Entitlement');
SET @my_entitlements_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'My Leave Entitlements');
SET @leave_entitlements_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Leave Entitlements');
SET @leave_balance_report_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Leave Balance Report');
SET @my_leave_balance_report_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'My Leave Balance Report');
SET @define_holiday_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Define Holiday');
SET @delete_holiday_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Delete Holiday');
SET @save_leave_entitlements_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Save Leave Entitlements');
SET @delete_leave_entitlements_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Delete Leave Entitlements');
SET @edit_leave_entitlement_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Edit Leave Entitlement');
SET @view_leave_request_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View Leave Request');
SET @change_leave_status_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Change Leave Status');
SET @define_leave_type_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Define Leave Type');

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES 
(2, @my_leave_list_screen_id),
(2, @apply_leave_screen_id),
(2, @my_entitlements_screen_id),
(2, @my_leave_balance_report_screen_id);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
    ('Leave Period', @define_leave_period_screen_id, @configure_menu_id, 4, 100, NULL, 'leave.define_leave_period', 1),
    ('Leave Types', @leave_types_screen_id, @configure_menu_id, 4, 200, NULL, 'vueleave.leave_type_list', 0),
    ('Holidays', @view_holiday_list_screen_id, @configure_menu_id, 4, 400, NULL, NULL, 0),
    ('Leave List', @leave_list_screen_id, @pto_leave_management_module_menu_id, 3, 100, '/reset/1', NULL, 0),
    ('Assign Leave', @assign_leave_screen_id, @pto_leave_management_module_menu_id, 3, 200, NULL, 'vueleave.leave_assign', 0),
    ('My Leave', @my_leave_list_screen_id, @pto_leave_management_module_menu_id, 3, 600, '/reset/1', NULL, 0),
    ('Apply', @apply_leave_screen_id, @pto_leave_management_module_menu_id, 3, 400, NULL, 'vueleave.leave_apply', 0),
    ('Add Entitlements', @add_entitlement_screen_id, @entitlement_menu_id, 4, 100, NULL, NULL, 0),
    ('My Entitlements', @my_entitlements_screen_id, @entitlement_menu_id, 4, 300, '/reset/1', NULL, 0),
    ('Entitlement List', @leave_entitlements_screen_id, @entitlement_menu_id, 4, 200, '/reset/1', NULL, 0),
    ('My Leave Usage', @my_leave_balance_report_screen_id, @pto_leave_management_module_menu_id, 3, 500, NULL, 'leave.my_balance_report', 0);
    
SET @admin_user_role_id := 1;
SET @ess_user_role_id := 2;
SET @supervisor_user_role_id := 3;
SET @config_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `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, @leave_types_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @leave_types_screen_id, 1, 0, 0, 0),
(@config_admin_role_id, @undelete_leave_type_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @delete_leave_type_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_holiday_list_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @define_holiday_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @delete_holiday_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @leave_list_screen_id, 1, 1, 1, 0),
(@supervisor_user_role_id, @leave_list_screen_id, 1, 1, 1, 0),
(@admin_user_role_id, @assign_leave_screen_id, 1, 1, 1, 0),
(@supervisor_user_role_id, @assign_leave_screen_id, 1, 1, 1, 0),
(@admin_user_role_id, @save_leave_entitlements_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @define_leave_period_screen_id, 1, 0, 0, 0),
(@config_admin_role_id, @define_leave_period_screen_id, 1, 1, 1, 1),
(@ess_user_role_id, @my_leave_list_screen_id, 1, 1, 1, 0),
(@ess_user_role_id, @apply_leave_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @view_leave_module_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_leave_module_screen_id, 1, 1, 1, 1),
(@ess_user_role_id, @view_leave_module_screen_id, 1, 0, 1, 0),
(@supervisor_user_role_id, @view_leave_module_screen_id, 1, 0, 1, 0),
(@admin_user_role_id, @leave_entitlements_screen_id, 1, 1, 1, 1),
-- (2, 69, 0, 0, 0, 0),
(@supervisor_user_role_id, @leave_entitlements_screen_id, 1, 0, 0, 0),
-- (1, 70, 0, 0, 0, 0),
(@ess_user_role_id, @my_entitlements_screen_id, 1, 0, 0, 0),
-- (3, 70, 0, 0, 0, 0),
(@admin_user_role_id, @delete_leave_entitlements_screen_id, 1, 0, 0, 1),
(@admin_user_role_id, @add_entitlement_screen_id, 1, 1, 1, 0),
(@admin_user_role_id, @edit_leave_entitlement_screen_id, 1, 0, 1, 0),
(@admin_user_role_id, @leave_balance_report_screen_id, 1, 0, 0, 0),
(@supervisor_user_role_id, @leave_balance_report_screen_id, 1, 0, 0, 0),
(@ess_user_role_id, @my_leave_balance_report_screen_id, 1, 0, 0, 0),
(@admin_user_role_id, @view_leave_request_screen_id, 1, 1, 1, 1),
(@ess_user_role_id, @view_leave_request_screen_id, 1, 1, 1, 1),
(@supervisor_user_role_id, @view_leave_request_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @change_leave_status_screen_id, 1, 0, 1, 0),
(@ess_user_role_id, @change_leave_status_screen_id, 1, 0, 1, 0),
(@supervisor_user_role_id, @change_leave_status_screen_id, 1, 0, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('leave_entitlements', 'Leave - Leave Entitlements', 1, 1, 1, 1),
('leave_entitlements_usage_report', 'Leave - Leave Entitlements and Usage Report', 1, NULL, NULL, NULL),
('leave_period', 'Leave - Leave Period', 1, 0, 0, 0),
('leave_types', 'Leave - Leave Types', 1, 0, 0, 0),
('holidays', 'Leave - Holidays', 1, 1, 1, 1),
('leave_list', 'Leave - Leave List', 1, 0, 0, 0),
('leave_list_comments', 'Leave - Leave List - Comments', 0, 1, 0, 0);

SET @leave_entitlements_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_entitlements');
SET @leave_entitlements_usage_report_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_entitlements_usage_report');
SET @leave_period_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_period');
SET @leave_types_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_types');
SET @holidays_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'holidays');
SET @leave_list_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_list');
SET @leave_list_comments_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_list_comments');


INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_user_role_id, @leave_entitlements_data_group_id, 1, 1, 1, 1, 0),
(@admin_user_role_id, @leave_entitlements_data_group_id, 1, 1, 1, 1, 1),
(@ess_user_role_id, @leave_entitlements_data_group_id, 1, 0, 0, 0, 1),
(@supervisor_user_role_id, @leave_entitlements_data_group_id, 1, 0, 0, 0, 0),
(@admin_user_role_id, @leave_entitlements_usage_report_data_group_id, 1, NULL, NULL, NULL, 0),
(@ess_user_role_id, @leave_entitlements_usage_report_data_group_id, 1, NULL, NULL, NULL, 1),
(@supervisor_user_role_id, @leave_entitlements_usage_report_data_group_id, 1, NULL, NULL, NULL, 0),
(@config_admin_role_id, @leave_period_data_group_id, 1, 1, 1, NULL, 0),
(@admin_user_role_id, @leave_period_data_group_id, 1, 0, 0, NULL, 0),
(@ess_user_role_id, @leave_period_data_group_id, 0, 0, 0, NULL, 0),
(@supervisor_user_role_id, @leave_period_data_group_id, 0, 0, 0, NULL, 0),
(@config_admin_role_id, @leave_types_data_group_id, 1, 1, 1, 1, 0),
(@admin_user_role_id, @leave_types_data_group_id, 1, 0, 0, 0, 0),
(@ess_user_role_id, @leave_types_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @leave_types_data_group_id, 0, 0, 0, 0, 0),
(@admin_user_role_id, @holidays_data_group_id, 1, 1, 1, 1, 0),
(@ess_user_role_id, @holidays_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @holidays_data_group_id, 0, 0, 0, 0, 0),
(@admin_user_role_id, @leave_list_data_group_id, 1, 0, 0, 0, 0),
(@ess_user_role_id, @leave_list_data_group_id, 1, 0, 0, 0, 1),
(@supervisor_user_role_id, @leave_list_data_group_id, 1, 0, 0, 0, 0),
(@admin_user_role_id, @leave_list_comments_data_group_id, 0, 1, 0, 0, 0),
(@ess_user_role_id, @leave_list_comments_data_group_id, 0, 1, 0, 0, 1),
(@supervisor_user_role_id, @leave_list_comments_data_group_id, 0, 1, 0, 0, 0);


INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@leave_entitlements_data_group_id, @leave_entitlements_screen_id, 1),
(@leave_entitlements_data_group_id, @add_entitlement_screen_id, 2),
(@leave_entitlements_data_group_id, @edit_leave_entitlement_screen_id, 3),
(@leave_entitlements_data_group_id, @delete_leave_entitlements_screen_id, 4),
(@leave_entitlements_data_group_id, @view_leave_module_screen_id, 1),
(@leave_entitlements_usage_report_data_group_id, @leave_balance_report_screen_id, 1),
(@leave_entitlements_usage_report_data_group_id, @view_leave_module_screen_id, 1),
(@leave_period_data_group_id, @view_leave_module_screen_id, 1),
(@leave_period_data_group_id, @define_leave_period_screen_id, 1),
(@leave_types_data_group_id, @leave_types_screen_id, 1),
(@leave_types_data_group_id, @define_leave_type_screen_id, 1),
(@leave_types_data_group_id, @define_leave_type_screen_id, 2),
(@leave_types_data_group_id, @define_leave_type_screen_id, 3),
(@leave_types_data_group_id, @undelete_leave_type_screen_id, 2),
(@leave_types_data_group_id, @delete_leave_type_screen_id, 4),
(@leave_types_data_group_id, @view_leave_module_screen_id, 1),
(@holidays_data_group_id, @view_holiday_list_screen_id, 1),
(@holidays_data_group_id, @define_holiday_screen_id, 2),
(@holidays_data_group_id, @define_holiday_screen_id, 3),
(@holidays_data_group_id, @delete_holiday_screen_id, 4),
(@holidays_data_group_id, @view_leave_module_screen_id, 1),
(@leave_list_data_group_id, @leave_list_screen_id, 1),
(@leave_list_data_group_id, @view_leave_request_screen_id, 1),
(@leave_list_data_group_id, @change_leave_status_screen_id, 1),
(@leave_list_data_group_id, @view_leave_module_screen_id, 1);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@leave_list_data_group_id, @view_leave_request_screen_id, 1, 1),
(@leave_list_data_group_id, @change_leave_status_screen_id, 1, 1),
(@leave_list_data_group_id, @view_leave_module_screen_id, 1, 1);

INSERT INTO ohrm_leave_entitlement_type(`id`,`name`,`is_editable`) VALUES
    (1,'Added',1);

INSERT INTO `ohrm_leave_status` (`status`,`name`) VALUES
(-1, 'REJECTED'),
(0, 'CANCELLED'),
(1, 'PENDING APPROVAL'),
(2, 'SCHEDULED'),
(3, 'TAKEN'),
(4, 'WEEKEND'),
(5, 'HOLIDAY');

INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
(@leave_module_id, @admin_user_role_id, 'leave/viewLeaveList?reset=1', NULL, 20),
(@leave_module_id, @supervisor_user_role_id, 'leave/viewLeaveList?reset=1', NULL, 10),
(@leave_module_id, @ess_user_role_id, 'leave/viewMyLeaveList', NULL, 0),
(@leave_module_id, @admin_user_role_id, 'leave/showLeavePeriodNotDefinedWarning', 'LeavePeriodDefinedHomePageEnabler', 100),
(@leave_module_id, @ess_user_role_id, 'leave/showLeavePeriodNotDefinedWarning', 'LeavePeriodDefinedHomePageEnabler', 90),
(@leave_module_id, @config_admin_role_id, 'leave/leaveTypeList', NULL, 10),
(@leave_module_id, @config_admin_role_id, 'leave/defineLeavePeriod', 'LeavePeriodDefinedHomePageEnabler', 100);


INSERT INTO ohrm_email(`name`) VALUES 
    ('leave.apply'),
    ('leave.assign'),
    ('leave.approve'),
    ('leave.cancel'),
    ('leave.reject'),
    ('leave.change');

SET @leave_apply_email_id := (SELECT id FROM ohrm_email WHERE name = 'leave.apply');
SET @leave_assign_email_id := (SELECT id FROM ohrm_email WHERE name = 'leave.assign');
SET @leave_approve_email_id := (SELECT id FROM ohrm_email WHERE name = 'leave.approve');
SET @leave_cancel_email_id := (SELECT id FROM ohrm_email WHERE name = 'leave.cancel');
SET @leave_reject_email_id := (SELECT id FROM ohrm_email WHERE name = 'leave.reject');
SET @leave_change_email_id := (SELECT id FROM ohrm_email WHERE name = 'leave.change');

SET @action_email_notification_type := (SELECT id FROM ohrm_email_notification_type where `name`='Action Based');
INSERT INTO `ohrm_email_notification` (`name`, `email_id`, `is_enable`,`type_id`) VALUES
    ('Leave Applications', @leave_apply_email_id, 0,@action_email_notification_type),
    ('Leave Assignments', @leave_assign_email_id, 0,@action_email_notification_type),
    ('Leave Approvals', @leave_approve_email_id, 0,@action_email_notification_type),
    ('Leave Cancellations', @leave_cancel_email_id, 0,@action_email_notification_type),
    ('Leave Rejections', @leave_reject_email_id, 0,@action_email_notification_type);

INSERT INTO ohrm_email_template(`email_id`, `locale`, `performer_role`, `recipient_role`,`body`, `subject`) VALUES 
    (@leave_apply_email_id, 'en_US', NULL, 'supervisor', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationSubject.txt'),
    (@leave_apply_email_id, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationSubject.txt'),
    (@leave_approve_email_id, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalSubject.txt'),
    (@leave_approve_email_id, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalSubscriberSubject.txt'),
    (@leave_assign_email_id, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubject.txt'),
    (@leave_assign_email_id, 'en_US', NULL, 'supervisor', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentBodyForSupervisors.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubjectForSupervisors.txt'),
    (@leave_assign_email_id, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubscriberSubject.txt'),
    (@leave_cancel_email_id, 'en_US', 'ess', 'supervisor', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationSubject.txt'),
    (@leave_cancel_email_id, 'en_US', 'ess', 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationSubscriberSubject.txt'),
    (@leave_cancel_email_id, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationSubject.txt'),
    (@leave_cancel_email_id, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationSubscriberSubject.txt'),
    (@leave_reject_email_id, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionSubject.txt'),
    (@leave_reject_email_id, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionSubscriberSubject.txt'),
    (@leave_change_email_id, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeSubject.txt'),
    (@leave_change_email_id, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeSubscriberSubject.txt');

INSERT INTO  ohrm_email_processor(`email_id`, `class_name`) VALUES 
    (@leave_apply_email_id, 'LeaveEmailProcessor'),
    (@leave_assign_email_id, 'LeaveEmailProcessor'),
    (@leave_approve_email_id, 'LeaveEmailProcessor'),
    (@leave_cancel_email_id, 'LeaveEmailProcessor'),
    (@leave_reject_email_id, 'LeaveEmailProcessor'),
    (@leave_change_email_id, 'LeaveChangeMailProcessor');


INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES 
(1, @holidays_data_group_id), 
(1, @leave_entitlements_data_group_id),
(3, @leave_entitlements_data_group_id),
(1, @leave_entitlements_usage_report_data_group_id),
(3, @leave_entitlements_usage_report_data_group_id),
(1, @leave_list_data_group_id),
(3, @leave_list_data_group_id),
(1, @leave_list_comments_data_group_id),
(3, @leave_list_comments_data_group_id), 
(1, @leave_period_data_group_id),
(1, @leave_types_data_group_id);

INSERT INTO ohrm_config (`property`,`value`) values ('leave.allow_admins_past_period_changes_without_adjust', 'No');

ALTER TABLE `ohrm_leave_request`  CHANGE `date_applied` `leave_start_date` DATE NOT NULL ;
ALTER TABLE `ohrm_leave_request`  ADD `date_applied` DATE NOT NULL AFTER `leave_start_date`;

-- Bulk Assign

SET @leave_module_id = (SELECT `id` FROM `ohrm_module` WHERE `name` = 'leave' );
 
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
    ('Bulk Assign', @leave_module_id, 'bulkAssignLeave');

SET @bulk_assign_leave_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Bulk Assign');
 
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
    ('Bulk Assign',@bulk_assign_leave_screen_id, @pto_leave_management_module_menu_id, 3, 300, NULL, 0);

SET @admin_user_role_id := 1;
 
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role_id, @bulk_assign_leave_screen_id, 1, 1, 1, 1) ;

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('bulk_assign', 'Leave - Leave Bulk Assign', 1, 1, 1, 1);

SET @bulk_assign_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'bulk_assign'); 

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_user_role_id, @bulk_assign_data_group_id, 1, 1, 1, 1, 0);


INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@bulk_assign_data_group_id, @bulk_assign_leave_screen_id, 1) ;

-- HOD user role permission ----
SET @hod_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@hod_id, @leave_list_screen_id, 1, 1, 1, 0),
(@hod_id, @assign_leave_screen_id, 1, 1, 1, 0),
(@hod_id, @view_leave_module_screen_id, 1, 0, 1, 0),
(@hod_id, @leave_entitlements_screen_id, 1, 0, 0, 0),
(@hod_id, @leave_balance_report_screen_id, 1, 0, 0, 0),
(@hod_id, @view_leave_request_screen_id, 1, 1, 1, 1),
(@hod_id, @change_leave_status_screen_id, 1, 0, 1, 0);

SET @leave_entitlements_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_entitlements');
SET @leave_entitlements_usage_report_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_entitlements_usage_report');
SET @leave_period_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_period');
SET @leave_types_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_types');
SET @holidays_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'holidays');
SET @leave_list_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_list');
SET @leave_list_comments_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_list_comments');


INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@hod_id, @leave_entitlements_data_group_id, 1, 0, 0, 0, 0),
(@hod_id, @leave_entitlements_usage_report_data_group_id, 1, NULL, NULL, NULL, 0),
(@hod_id, @leave_period_data_group_id, 0, 0, 0, NULL, 0),
(@hod_id, @leave_types_data_group_id, 0, 0, 0, 0, 0),
(@hod_id, @holidays_data_group_id, 0, 0, 0, 0, 0),
(@hod_id, @leave_list_data_group_id, 1, 0, 0, 0, 0),
(@hod_id, @leave_list_comments_data_group_id, 0, 1, 0, 0, 0);

INSERT INTO ohrm_workflow_state_machine(workflow,state,ROLE,action,resulting_state,roles_to_notify,screens_to_show,priority)
SELECT workflow,state,'HEADOFDEPARTMENT',action,resulting_state,roles_to_notify,screens_to_show,priority FROM ohrm_workflow_state_machine WHERE ROLE='SUPERVISOR' AND workflow=@leave_workflow_id;

-- Redirect to eagle pages ----
UPDATE `ohrm_menu_item` SET `uisref` = 'leave.view_my_leave_entitlement' WHERE `menu_title` ='My Entitlements';
UPDATE `ohrm_menu_item` SET `uisref` = 'leave.view_leave_entitlement({"id":null,"employeeId":null,"leaveTypeId":null,"from":null,"to":null})' WHERE `menu_title` ='Entitlement List';
UPDATE `ohrm_menu_item` SET `uisref` = 'leave.add_leave_entitlement' WHERE `menu_title` ='Add Entitlements';
UPDATE `ohrm_menu_item` SET `uisref` = 'leave.view_my_leave_list' WHERE `menu_title` ='My Leave';
UPDATE `ohrm_menu_item` SET `uisref` = 'leave.view_leave_list({"filter":null})' WHERE `menu_title` ='Leave List';
UPDATE `ohrm_menu_item` SET `uisref` = 'vueleave.leave_apply' WHERE `menu_title` ='Apply';
UPDATE `ohrm_menu_item` SET `uisref` = 'leave.bulk_assign' WHERE `menu_title` ='Bulk Assign';

CREATE TABLE IF NOT EXISTS ohrm_leave_request_employee_nominee (
  id INT(10) NOT NULL AUTO_INCREMENT,
  emp_number INT(7) NOT NULL,
  state VARCHAR(255)NOT NULL,
  note TEXT,
  leave_request_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT fk_leave_request_employee_nominee_emp_number FOREIGN KEY (emp_number) REFERENCES hs_hr_employee(emp_number) ON DELETE CASCADE,
  CONSTRAINT fk_leave_request_employee_nominee_leave_request FOREIGN KEY (leave_request_id) REFERENCES ohrm_leave_request(id) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS ohrm_leave_request_employee_nominee_state (
  state VARCHAR(255)NOT NULL,
  leave_request_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY(leave_request_id),
  CONSTRAINT fk_ohrm_leave_request_employee_nominee_state_leave_request FOREIGN KEY (leave_request_id) REFERENCES ohrm_leave_request(id) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS ohrm_leave_action_log (
    id INT(10) NOT NULL AUTO_INCREMENT,
    leave_id int(11) NOT NULL,
    from_status smallint(6),
    to_status smallint(6),
    performed_user_id INT,
    performed_employee_number INT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_leave_action_log_leave_id` FOREIGN KEY (`leave_id`) REFERENCES `ohrm_leave` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_leave_action_log_performed_user_id` FOREIGN KEY (`performed_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_leave_action_log_performed_employee_number` FOREIGN KEY (`performed_employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO ohrm_data_list (`name`, `meta_data`) VALUES ('leaveList', '{"listFields":{"balance":"balance","requestedDuration":"requestedDuration","status":"status"},"availableFields":{"emp_id":"Employee Id","employee":"Employee","dateRange":"Date","appliedDate":"Applied On","leaveType":"Leave Type","balance":"Net Leave Balance","requestedDuration":"Requested Duration","status":"Status","costCentre":"Cost Center","listJobTitle":"Job Title","listEmploymentStatus":"Employment Status","subUnit":"Sub Unit","employeeLocation":"Location","listJobCategory":"Job Category","listWorkSchedule":"Work Schedule"},"defaultFields":["balance","requestedDuration","status"],"mandatoryFields":["employee","dateRange","leaveType"]}');
INSERT INTO ohrm_data_list (`name`, `meta_data`) VALUES
('leaveUsageReportByEmployeeList','{"listFields":{"leaveType":"leaveType","current":"current","leaveUnit":"leaveUnit","entitlement_total":"entitlement_total","pending":"pending","scheduled":"scheduled","taken":"taken","netBalance":"netBalance"},"availableFields":{"leaveType":"Leave Type","current":"Leave Period","leaveUnit":"Unit","entitlement_total":"Entitlements","pending":"Pending Approval","scheduled":"Scheduled","taken":"Taken","unused":"Available Balance","overdrawn":"Total Overdrawn","netBalance":"Net Balance"},"defaultFields":["netBalance"],"mandatoryFields":["leaveType","current","leaveUnit","entitlement_total","pending","scheduled","taken"]}'),
('leaveUsageReportByLeaveTypeList','{"listFields":{"employeeName":"employeeName","leaveTypeName":"leaveTypeName","current":"current","leaveUnit":"leaveUnit","entitlement_total":"entitlement_total","pending":"pending","scheduled":"scheduled","taken":"taken","netBalance":"netBalance"},"availableFields":{"employeeId":"Employee ID","employeeName":"Employee Name","leaveTypeName":"Leave Type","current":"Leave Period","leaveUnit":"Unit","entitlement_total":"Entitlements","pending":"Pending Approval","scheduled":"Scheduled","taken":"Taken","unused":"Available Balance","overdrawn":"Total Overdrawn","netBalance":"Net Balance"},"defaultFields":["netBalance"],"mandatoryFields":["employeeName","leaveTypeName","current","leaveUnit","entitlement_total","pending","scheduled","taken"]}');

INSERT INTO ohrm_config (`property`,`value`) values ('leave.attachment_submission_reminder.period_days', '0');

INSERT INTO `ohrm_leave_request_attachment_configuration` (`id`, `status`, `label`) VALUES
    (0, 'DISABLED', 'Attachment Disabled'),
    (1, 'ENABLED', 'Attachment Enabled'),
    (2, 'MANDATORY', 'Attachment Mandatory'),
    (3, 'OPTIONAL_WITH_REMINDER', 'Attachment Optional with Reminder');

INSERT INTO ohrm_config (`property`,`value`) values ('leave.pending_leave_request_approval_reminder.period_days', '30');
