create table `ohrm_timesheet_period`(
  `id` bigint(20) not null auto_increment,
  `start_date` date not null,
  `end_date` date not null,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  primary key  (`id`),
  UNIQUE `unique_start_end_dates`(`start_date`, `end_date`)
) engine=innodb default charset=utf8;

create table `ohrm_timesheet`(
  `timesheet_id` bigint(20) not null auto_increment,
  `timesheet_period_id` bigint(20) not null,
  `state` varchar(255) not null,
  `start_date` date not null,
  `end_date` date not null,
  `employee_id` int(7) not null,
  `type` varchar(20) not null default 'summary',
  primary key  (`timesheet_id`),
  INDEX ohrm_timesheet_emp_id_start_and_end_dates (employee_id, start_date, end_date),
  CONSTRAINT `fk_ohrm_timesheet_ohrm_timesheet_period` FOREIGN KEY (`timesheet_period_id`) REFERENCES `ohrm_timesheet_period` (`id`) ON DELETE CASCADE
) engine=innodb default charset=utf8;

create table `ohrm_timesheet_item`(
  `timesheet_item_id` bigint(20) not null auto_increment,
  `timesheet_id` bigint(20) not null,
  `date` date not null,
  `start_time` time null,
  `end_time` time null,
  `duration` bigint(20) default null,
  `comment` text default null,
  `project_id` bigint(20) null,
  `employee_id` bigint(20) not null,
  `activity_id` bigint(20) not null,
  primary key  (`timesheet_item_id`),
  INDEX ohrm_timesheet_item_project_id (project_id),
  key `timesheet_id` (`timesheet_id`),
  key `activity_id` (`activity_id`)
) engine=innodb default charset=utf8;

create table `ohrm_timesheet_action_log`(
  `timesheet_action_log_id` bigint(20) not null auto_increment,
  `comment` varchar(255) default null,
  `action` varchar(255),
  `date_time` date not null,
  `performed_by` int(20) not null,
  `timesheet_id` bigint(20) not null,
  primary key  (`timesheet_action_log_id`),
  key `timesheet_id` (`timesheet_id`),
  key `performed_by`(`performed_by`)
) engine=innodb default charset=utf8;

CREATE TABLE `ohrm_attendance_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `unique_key` VARCHAR(40),
  `employee_id` BIGINT NOT NULL,
  `punch_in_utc_time` DATETIME,
  `punch_in_note` VARCHAR(255),
  `punch_in_time_offset` VARCHAR(255),
  `punch_in_user_time` DATETIME,
  `punch_out_utc_time` DATETIME,
  `punch_out_note` VARCHAR(255),
  `punch_out_time_offset` VARCHAR(255),
  `punch_out_user_time` DATETIME,
  `state` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_attendance_record` (`unique_key`),
  KEY `emp_id_state` (`employee_id`, `state`),
  KEY `emp_id_time` (`employee_id`, `punch_in_utc_time`, `punch_out_utc_time`)
) ENGINE = InnoDB DEFAULT charset=utf8;

create table `ohrm_pay_code`(
  `id` INT(11) not null AUTO_INCREMENT,
  `code` VARCHAR(100) not null unique,
  `description` TEXT null,
  primary key (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_pay_code_assignment`(
  `id` BIGINT(20) not null AUTO_INCREMENT,
  `timesheet_period_id` BIGINT(20) not null,
  `timesheet_id` BIGINT(20) not null,
  `employee_id` INT(7) not null,
  `pay_code_id` INT(11) not null,
  `date` DATE not null,
  `duration` bigint(20) default null,
  primary key (`id`),
  CONSTRAINT `fk_ohrm_pay_code_assignment_timesheet_period_id` FOREIGN KEY (`timesheet_period_id`) REFERENCES `ohrm_timesheet_period` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ohrm_pay_code_assignment_timesheet_id` FOREIGN KEY (`timesheet_id`) REFERENCES `ohrm_timesheet` (`timesheet_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ohrm_pay_code_assignment_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
  CONSTRAINT `fk_ohrm_pay_code_assignment_pay_code_id` FOREIGN KEY (`pay_code_id`) REFERENCES `ohrm_pay_code` (`id`) ON DELETE RESTRICT,
  INDEX `date` (`date` ASC)
) engine=innodb default charset=utf8;

CREATE TABLE ohrm_attendance_allowed_ip_range (
  id BIGINT AUTO_INCREMENT,
   ip_from VARCHAR(255),
    ip_to VARCHAR(255),
     PRIMARY KEY(id)
) ENGINE = INNODB default charset=utf8;

alter table ohrm_timesheet_action_log
       add constraint foreign key (performed_by)
                             references ohrm_user(id) on delete cascade;


CREATE TABLE `ohrm_pay_policy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `rule` text NOT NULL,
  `processor_class` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `ohrm_pay_policy_location` (
  `location_id` int(11) NOT NULL,
  `pay_policy_id` int(11) NOT NULL,
  PRIMARY KEY (`location_id`, `pay_policy_id`),
  UNIQUE KEY `location_id` (`location_id`),
  CONSTRAINT `fk_ohrm_pay_policy_location_location_id` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ohrm_pay_policy_location_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_pay_policy` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ALTER TABLE ohrm_timesheet ADD FOREIGN KEY (employee_id) REFERENCES hs_hr_employee(emp_number) ON DELETE CASCADE;

INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('timesheet_type',  'summary');
INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('time.default_pay_policy_processor_class',  'DefaultPayPolicyProcessor');
INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('time.default_pay_policy',
              '<pay_policy>
                    <active>0</active>
                    <assigned_to></assigned_to>
                    <daily>
                        <until>
                            <hours_per_day>8</hours_per_day>
                            <pay_code>1</pay_code>
                            <multiplier></multiplier>
                        </until>
                        <after1>
                            <hours_per_day>8</hours_per_day>
                            <pay_code>2</pay_code>
                            <multiplier></multiplier>
                        </after1>
                        <after2>
                            <hours_per_day>12</hours_per_day>
                            <pay_code>3</pay_code>
                            <multiplier></multiplier>
                        </after2>
                    </daily>
                    <weekly>
                        <after>
                            <hours_per_week>40</hours_per_week>
                            <pay_code>4</pay_code>
                            <multiplier></multiplier>
                        </after>
                    </weekly>
                    <seventh_day>
                        <until>
                            <hours_per_day>8</hours_per_day>
                            <pay_code>2</pay_code>
                            <multiplier></multiplier>
                        </until>
                        <after>
                            <hours_per_day>8</hours_per_day>
                            <pay_code>3</pay_code>
                            <multiplier></multiplier>
                        </after>
                    </seventh_day>
                </pay_policy>');

INSERT INTO `ohrm_pay_code` (`id`,`code`, `description`) VALUES
(1, 'RT', 'Regular Time'),
(2, 'OT', 'Overtime'),
(3, 'DT', 'Double Time'),
(4, 'WOT', 'Weekly Overtime');

INSERT INTO `hs_hr_unique_id`(last_id, table_name, field_name) VALUES
(0, 'ohrm_timesheet', 'timesheet_id'),
(0, 'ohrm_timesheet_action_log', 'timesheet_action_log_id'),
(0, 'ohrm_timesheet_item', 'timesheet_item_id');

INSERT INTO `ohrm_module` (`name`, `status`, `data_group_prefix`) VALUES
('time', 1, 'Time');
SET @time_module_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_module` (`name`, `status`, `data_group_prefix`) VALUES
('attendance', 1, 'Attendance');
SET @attendance_module_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES
('ProjectAdmin', 'ProjectAdmin', 0, 1);

SET @project_admin_user_role_id := (SELECT LAST_INSERT_ID());
SET @admin_user_role_id := 1;
SET @ess_user_role_id := 2;
SET @supervisor_user_role_id := 3;
SET @hod_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');

-- admin/time dbscript start

SET @admin_module_id := (SELECT id FROM ohrm_module WHERE name = 'admin'); # expected values is 2

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Customer List', @time_module_id, 'viewCustomers', 'time_viewCustomers'),
('Project List', @time_module_id, 'viewProjects', 'time_viewProjects'),
('Add Customer', @admin_module_id, 'addCustomer', NULL),
('Delete Customer', @admin_module_id, 'deleteCustomer', NULL),
('Save Project', @admin_module_id, 'saveProject', NULL),
('Delete Project', @admin_module_id, 'deleteProject', NULL),
('Add Project Adtivity', @admin_module_id, 'addProjectActivity', NULL),
('Delete Project Adtivity', @admin_module_id, 'deleteProjectActivity', NULL);

SET @customer_list_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Customer List'); # Old Value was 36
SET @project_list_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Project List'); # Old Value was 37
SET @add_customer_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Add Customer'); # Old Value was 86
SET @delete_customer_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Delete Customer'); # Old Value was 87
SET @add_project_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Save Project'); # Old Value was 88
SET @delete_project_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Delete Project'); # Old Value was 89
SET @add_project_activity_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Add Project Adtivity'); # Old Value was 90
SET @delete_project_activity_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Delete Project Adtivity'); # Old Value was 91

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role_id, @customer_list_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @project_list_screen_id, 1, 1, 1, 1),
(@project_admin_user_role_id, @project_list_screen_id, 1, 0, 0, 0),
(@admin_user_role_id, @add_customer_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @delete_customer_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @add_project_screen_id, 1, 1, 1, 1),
(@project_admin_user_role_id, @add_project_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @delete_project_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @add_project_activity_screen_id, 1, 1, 1, 1),
(@project_admin_user_role_id, @add_project_activity_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @delete_project_activity_screen_id, 1, 1, 1, 1),
(@project_admin_user_role_id, @delete_project_activity_screen_id, 1, 1, 1, 1);

-- admin/time dbscript end


INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Define Timesheet Start Date', @time_module_id, 'defineTimesheetPeriod', NULL),
('View My Timesheet', @time_module_id, 'viewMyTimesheet', 'time_viewMyTimesheet'),
('View Employee Timesheet', @time_module_id, 'viewEmployeeTimesheet', 'time_viewEmployeeTimesheet'),
('View Project Report Criteria', @time_module_id, 'displayProjectReportCriteria', 'time_displayProjectReportCriteria'),
('View Employee Report Criteria', @time_module_id, 'displayEmployeeReportCriteria', 'time_displayEmployeeReportCriteria'),
('View Time Module', @time_module_id, 'viewTimeModule', 'time_viewTimeModule'),
('View Project Activity Details Report', @time_module_id, 'displayProjectActivityDetailsReport', NULL),
('Pay Code Report', @time_module_id, 'payCodeReport', NULL),
-- attendance
('View My Attendance', @attendance_module_id, 'viewMyAttendanceRecord', 'attendance_viewMyAttendanceRecord'),
('Punch In/Out', @attendance_module_id, 'punchIn', 'attendance_punchIn'),
('View Employee Attendance', @attendance_module_id, 'viewAttendanceRecord', 'attendance_viewAttendanceRecord'),
('Attendance Configuration', @attendance_module_id, 'configure', 'attendance_configure');

SET @define_time_start_date_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Define Timesheet Start Date');
SET @my_timesheet_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View My Timesheet');
SET @view_employee_timesheet_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View Employee Timesheet');
SET @view_project_report_criteria_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View Project Report Criteria');
SET @view_employee_report_criteria_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View Employee Report Criteria');
SET @view_time_module_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View Time Module');
SET @view_project_activity_detail_report_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View Project Activity Details Report');
SET @pay_code_report_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Pay Code Report');
SET @view_my_attendance_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View My Attendance');
SET @punch_in_out_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Punch In/Out');
SET @view_employee_attendance_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'View Employee Attendance');
SET @attendance_configuration_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Attendance Configuration');

-- INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
-- (2, @my_timesheet_screen_id),
-- (2, @punch_in_out_screen_id);

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`)
VALUES (2, @my_timesheet_screen_id);

SET @compensation_pillar_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Compensation' AND `level` = 1);
SET @time_tracking_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Time Tracking' AND parent_id = @compensation_pillar_menu_id AND `level` = 2);
SET @attendance_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Attendance' AND parent_id = @compensation_pillar_menu_id AND `level` = 2);

UPDATE ohrm_menu_item SET screen_id = @view_time_module_screen_id WHERE id = @time_tracking_menu_id;

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Activity Info', NULL, @time_tracking_menu_id, 3, 400, NULL, 1),
('Configuration', NULL, @time_tracking_menu_id, 3, 500, NULL, 1);

SET @project_info_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Activity Info' AND parent_id = @time_tracking_menu_id);
SET @configure_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Configuration' AND parent_id = @time_tracking_menu_id);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Customers', @customer_list_screen_id, @project_info_menu_id, 4, 100, NULL, 'vuetime.viewCustomers', 0),
('Projects', @project_list_screen_id, @project_info_menu_id, 4, 200, NULL, 'time.projects', 0),
('My Timesheets', @my_timesheet_screen_id, @time_tracking_menu_id, 3, 200, NULL, NULL, 0),
('Employee Timesheets', @view_employee_timesheet_screen_id, @time_tracking_menu_id, 3, 100, NULL, 'time.employee_timesheets', 0),
('My Records', @view_my_attendance_screen_id, @attendance_menu_id, 3, 600, NULL, 'attendance.my_attendance_sheet({"currentMonth": true})', 1),
('Punch In/Out', @punch_in_out_screen_id, @attendance_menu_id, 3, 400, NULL, 'attendance.my_punch_in_out', 1),
('Employee Records', @view_employee_attendance_screen_id, @attendance_menu_id, 3, 300, NULL, 'report_view_oxd_with_persistence({"id": "Attendance_Employee_Records_Report", "filters": null, "breadCrumbs": { "0" : {"name": "Attendance"}}, "reportNameAsMenuPill": false})', 1),
('Configuration', @attendance_configuration_screen_id, @attendance_menu_id, 3, 900, NULL, 'attendance.config', 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, @define_time_start_date_screen_id, 1, 1, 1, 1),
(@ess_user_role_id, @my_timesheet_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_employee_timesheet_screen_id, 1, 1, 1, 1),
(@supervisor_user_role_id, @view_employee_timesheet_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_project_report_criteria_screen_id, 1, 1, 1, 1),
(@project_admin_user_role_id, @view_project_report_criteria_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_employee_report_criteria_screen_id, 1, 1, 1, 1),
(@supervisor_user_role_id, @view_employee_report_criteria_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_time_module_screen_id, 1, 1, 1, 1),
(@ess_user_role_id, @view_time_module_screen_id, 1, 0, 1, 0),
(@supervisor_user_role_id, @view_time_module_screen_id, 1, 0, 1, 0),
(@admin_user_role_id, @view_project_activity_detail_report_screen_id, 1, 1, 1, 1),
(@project_admin_user_role_id, @view_project_activity_detail_report_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @pay_code_report_screen_id, 1, 1, 1, 1),
-- attendance
(@ess_user_role_id, @punch_in_out_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @view_employee_attendance_screen_id , 1, 1, 0, 1),
(@supervisor_user_role_id, @view_employee_attendance_screen_id , 1, 1, 0, 0),
(@admin_user_role_id, @attendance_configuration_screen_id, 1, 1, 1, 1);


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('time_customers', 'Time Tracking - Activity Info - Customers', 1, 1, 1, 1),
('time_projects', 'Time Tracking - Activity Info - Projects', 1, 1, 1, 1),
('attendance_configuration', 'Attendance - Configuration', 1, 0, 1, 0),
('attendance_records', 'Attendance - Attendance Records', 1, 0, 0, 0),
('time_project_reports', 'Time Tracking - Project Reports', 1, 0, 0, 0),
('time_employee_reports', 'Time Tracking - Employee Reports', 1, 0, 0, 0),
('time_pay_code_report', 'Time Tracking - Pay Hours Report', 1, 0, 0, 0),
('time_employee_timesheets', 'Time Tracking - Employee Timesheets', 1, 0, 0, 0);


SET @customer_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'time_customers');
SET @projects_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'time_projects');
SET @attendance_configuration_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'attendance_configuration');
SET @attendance_records_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'attendance_records');
SET @project_reports_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'time_project_reports');
SET @employee_reports_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'time_employee_reports');
SET @pay_code_reports_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'time_pay_code_report');
SET @employee_timesheets_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'time_employee_timesheets');


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, @customer_data_group_id, 1, 1, 1, 1, 0),
(@ess_user_role_id, @customer_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @customer_data_group_id, 0, 0, 0, 0, 0),
(@admin_user_role_id, @projects_data_group_id, 1, 1, 1, 1, 0),
(@ess_user_role_id, @projects_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @projects_data_group_id, 0, 0, 0, 0, 0),
(@project_admin_user_role_id, @projects_data_group_id, 1, 0, 1, 0, 0),
(@admin_user_role_id, @attendance_configuration_data_group_id, 1, NULL, 1, NULL, 0),
(@ess_user_role_id, @attendance_configuration_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @attendance_configuration_data_group_id, 0, 0, 0, 0, 0),
(@admin_user_role_id, @attendance_records_data_group_id, 1, 0, 0, 0, 0),
(@ess_user_role_id, @attendance_records_data_group_id, 0, 0, 0, 0, 0),
(@ess_user_role_id, @attendance_records_data_group_id, 1, 0, 0, 0, 1),
(@supervisor_user_role_id, @attendance_records_data_group_id, 1, 0, 0, 0, 0),
(@admin_user_role_id, @project_reports_data_group_id, 1, 0, 0, 0, 0),
(@ess_user_role_id, @project_reports_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @project_reports_data_group_id, 0, 0, 0, 0, 0),
(@project_admin_user_role_id, @project_reports_data_group_id, 1, 0, 0, 0, 0),
(@admin_user_role_id, @employee_reports_data_group_id, 1, 0, 0, 0, 0),
(@ess_user_role_id, @employee_reports_data_group_id, 0, 0, 0, 0, 0),
(@supervisor_user_role_id, @employee_reports_data_group_id, 1, 0, 0, 0, 0),
(@admin_user_role_id, @pay_code_reports_data_group_id, 1, 0, 0, 0, 0),
(@admin_user_role_id, @employee_timesheets_data_group_id, 1, 0, 0, 0, 0),
(@ess_user_role_id, @employee_timesheets_data_group_id, 0, 0, 0, 0, 0),
(@ess_user_role_id, @employee_timesheets_data_group_id, 1, 0, 0, 0, 1),
(@supervisor_user_role_id, @employee_timesheets_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@customer_data_group_id, @customer_list_screen_id, 1),
(@customer_data_group_id, @add_customer_screen_id, 2),
(@customer_data_group_id, @add_customer_screen_id, 3),
(@customer_data_group_id, @delete_customer_screen_id, 4),
(@customer_data_group_id, @view_time_module_screen_id, 1),
(@projects_data_group_id, @project_list_screen_id, 1),
(@projects_data_group_id, @add_project_screen_id, 1),
(@projects_data_group_id, @add_project_screen_id, 2),
(@projects_data_group_id, @add_project_screen_id, 3),
(@projects_data_group_id, @delete_project_screen_id, 4),
(@projects_data_group_id, @add_project_activity_screen_id, 2),
(@projects_data_group_id, @add_project_activity_screen_id, 3),
(@projects_data_group_id, @delete_project_activity_screen_id, 2),
(@projects_data_group_id, @delete_project_activity_screen_id, 3),
(@projects_data_group_id, @view_time_module_screen_id, 1),
(@attendance_configuration_data_group_id, @attendance_configuration_screen_id, 1),
(@attendance_configuration_data_group_id, @view_time_module_screen_id, 1),
(@attendance_records_data_group_id, @view_employee_attendance_screen_id, 1),
(@attendance_records_data_group_id, @view_time_module_screen_id, 1),
(@project_reports_data_group_id, @view_project_report_criteria_screen_id, 1),
(@project_reports_data_group_id, @view_project_activity_detail_report_screen_id, 1),
(@project_reports_data_group_id, @view_time_module_screen_id, 1),
(@employee_reports_data_group_id, @view_employee_report_criteria_screen_id, 1),
(@employee_reports_data_group_id, @view_time_module_screen_id, 1),
(@pay_code_reports_data_group_id, @pay_code_report_screen_id, 1),
(@pay_code_reports_data_group_id, @view_time_module_screen_id, 1),
(@employee_timesheets_data_group_id, @view_employee_timesheet_screen_id, 1),
(@employee_timesheets_data_group_id, @view_time_module_screen_id, 1);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@employee_timesheets_data_group_id, @view_time_module_screen_id, 1, 1);

INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
(@time_module_id, @ess_user_role_id, 'time/viewMyTimesheet', NULL, 0),
(@time_module_id, @admin_user_role_id, 'time/defineTimesheetPeriod', 'TimesheetPeriodDefinedHomePageEnabler', 100),
(@time_module_id, @ess_user_role_id, 'time/timesheetPeriodNotDefined', 'TimesheetPeriodDefinedHomePageEnabler', 90),
(@time_module_id, @admin_user_role_id, '/#/route/time.employee_timesheets', NULL, 50);


INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(1, @attendance_configuration_data_group_id),
(1, @attendance_records_data_group_id),
(3, @attendance_records_data_group_id),
(1, @employee_reports_data_group_id),
(3, @employee_reports_data_group_id),
(1, @pay_code_reports_data_group_id),
(1, @employee_timesheets_data_group_id),
(3, @employee_timesheets_data_group_id),
(1, @customer_data_group_id),
(1, @projects_data_group_id),
(1, @project_reports_data_group_id);

SET @time_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='time');
SET @attendance_module_id := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='attendance');
INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`) VALUES
('timesheet_summary', 'summaryTimesheet', 'Summary Timesheet', '1,3', '2', 'time', 'Time', @time_module_id, 2),
('timesheet_detailed', 'detailedTimesheet', 'Detailed Timesheet', '1,3', '2', 'time', 'Time', @time_module_id, 2),
('1', 'attendance', null, '1,2', NULL, 'attendance', 'Attendance', @attendance_module_id, 3);

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
('timesheet_summary','INITIAL','SYSTEM','7','NOT SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','ADMIN','2','APPROVED', '', 0),
('timesheet_summary','SUBMITTED','ADMIN','3','REJECTED', '', 0),
('timesheet_summary','SUBMITTED','ADMIN','0','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','ADMIN','5','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','SUPERVISOR','2','APPROVED', '', 0),
('timesheet_summary','SUBMITTED','SUPERVISOR','3','REJECTED', '', 0),
('timesheet_summary','SUBMITTED','SUPERVISOR','5','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','SUPERVISOR','0','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','HEADOFDEPARTMENT','2','APPROVED', '', 0),
('timesheet_summary','SUBMITTED','HEADOFDEPARTMENT','3','REJECTED', '', 0),
('timesheet_summary','SUBMITTED','HEADOFDEPARTMENT','5','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','HEADOFDEPARTMENT','0','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','ESS USER','0','SUBMITTED', '', 0),
('timesheet_summary','SUBMITTED','ESS USER','5','SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','ESS USER','1','SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','ESS USER','5','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','ESS USER','0','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','SUPERVISOR','0','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','SUPERVISOR','5','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','SUPERVISOR','1','SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','HEADOFDEPARTMENT','0','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','HEADOFDEPARTMENT','5','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','HEADOFDEPARTMENT','1','SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','ADMIN','0','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','ADMIN','5','NOT SUBMITTED', '', 0),
('timesheet_summary','NOT SUBMITTED','ADMIN','1','SUBMITTED', '', 0),
('timesheet_summary','REJECTED','ESS USER','1','SUBMITTED', '', 0),
('timesheet_summary','REJECTED','ESS USER','0','REJECTED', '', 0),
('timesheet_summary','REJECTED','ESS USER','5','REJECTED', '', 0),
('timesheet_summary','REJECTED','SUPERVISOR','1','SUBMITTED', '', 0),
('timesheet_summary','REJECTED','SUPERVISOR','0','REJECTED', '', 0),
('timesheet_summary','REJECTED','SUPERVISOR','5','REJECTED', '', 0),
('timesheet_summary','REJECTED','HEADOFDEPARTMENT','1','SUBMITTED', '', 0),
('timesheet_summary','REJECTED','HEADOFDEPARTMENT','0','REJECTED', '', 0),
('timesheet_summary','REJECTED','HEADOFDEPARTMENT','5','REJECTED', '', 0),
('timesheet_summary','REJECTED','ADMIN','0','REJECTED', '', 0),
('timesheet_summary','REJECTED','ADMIN','5','REJECTED', '', 0),
('timesheet_summary','REJECTED','ADMIN','1','SUBMITTED', '', 0),
('timesheet_summary','APPROVED','ESS USER','0','APPROVED', '', 0),
('timesheet_summary','APPROVED','SUPERVISOR','0','APPROVED', '', 0),
('timesheet_summary','APPROVED','HEADOFDEPARTMENT','0','APPROVED', '', 0),
('timesheet_summary','APPROVED','ADMIN','0','APPROVED', '', 0),
('timesheet_summary','APPROVED','ADMIN','4','SUBMITTED', '', 0),
('timesheet_summary','INITIAL','ADMIN','7','NOT SUBMITTED', '', 0),
('timesheet_summary','INITIAL','ESS USER','7','NOT SUBMITTED', '', 0),
('timesheet_summary','INITIAL','SUPERVISOR','7','NOT SUBMITTED', '', 0),
('timesheet_summary','INITIAL','HEADOFDEPARTMENT','7','NOT SUBMITTED', '', 0),
('timesheet_detailed','INITIAL','SYSTEM','7','NOT SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','ADMIN','2','APPROVED', '', 0),
('timesheet_detailed','SUBMITTED','ADMIN','3','REJECTED', '', 0),
('timesheet_detailed','SUBMITTED','ADMIN','0','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','ADMIN','5','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','SUPERVISOR','2','APPROVED', '', 0),
('timesheet_detailed','SUBMITTED','SUPERVISOR','3','REJECTED', '', 0),
('timesheet_detailed','SUBMITTED','SUPERVISOR','5','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','SUPERVISOR','0','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','HEADOFDEPARTMENT','2','APPROVED', '', 0),
('timesheet_detailed','SUBMITTED','HEADOFDEPARTMENT','3','REJECTED', '', 0),
('timesheet_detailed','SUBMITTED','HEADOFDEPARTMENT','5','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','HEADOFDEPARTMENT','0','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','ESS USER','0','SUBMITTED', '', 0),
('timesheet_detailed','SUBMITTED','ESS USER','5','SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','ESS USER','1','SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','ESS USER','5','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','ESS USER','0','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','SUPERVISOR','0','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','SUPERVISOR','5','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','SUPERVISOR','1','SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','HEADOFDEPARTMENT','0','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','HEADOFDEPARTMENT','5','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','HEADOFDEPARTMENT','1','SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','ADMIN','0','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','ADMIN','5','NOT SUBMITTED', '', 0),
('timesheet_detailed','NOT SUBMITTED','ADMIN','1','SUBMITTED', '', 0),
('timesheet_detailed','REJECTED','ESS USER','1','SUBMITTED', '', 0),
('timesheet_detailed','REJECTED','ESS USER','0','REJECTED', '', 0),
('timesheet_detailed','REJECTED','ESS USER','5','REJECTED', '', 0),
('timesheet_detailed','REJECTED','SUPERVISOR','1','SUBMITTED', '', 0),
('timesheet_detailed','REJECTED','SUPERVISOR','0','REJECTED', '', 0),
('timesheet_detailed','REJECTED','SUPERVISOR','5','REJECTED', '', 0),
('timesheet_detailed','REJECTED','HEADOFDEPARTMENT','1','SUBMITTED', '', 0),
('timesheet_detailed','REJECTED','HEADOFDEPARTMENT','0','REJECTED', '', 0),
('timesheet_detailed','REJECTED','HEADOFDEPARTMENT','5','REJECTED', '', 0),
('timesheet_detailed','REJECTED','ADMIN','0','REJECTED', '', 0),
('timesheet_detailed','REJECTED','ADMIN','5','REJECTED', '', 0),
('timesheet_detailed','REJECTED','ADMIN','1','SUBMITTED', '', 0),
('timesheet_detailed','APPROVED','ESS USER','0','APPROVED', '', 0),
('timesheet_detailed','APPROVED','SUPERVISOR','0','APPROVED', '', 0),
('timesheet_detailed','APPROVED','HEADOFDEPARTMENT','0','APPROVED', '', 0),
('timesheet_detailed','APPROVED','ADMIN','0','APPROVED', '', 0),
('timesheet_detailed','APPROVED','ADMIN','4','SUBMITTED', '', 0),
('timesheet_detailed','INITIAL','ADMIN','7','NOT SUBMITTED', '', 0),
('timesheet_detailed','INITIAL','ESS USER','7','NOT SUBMITTED', '', 0),
('timesheet_detailed','INITIAL','SUPERVISOR','7','NOT SUBMITTED', '', 0),
('timesheet_detailed','INITIAL','HEADOFDEPARTMENT','7','NOT SUBMITTED', '', 0),
-- ('1','PUNCHED IN','Default ESS','1','PUNCHED OUT', '', 0),
-- ('1','INITIAL','Default ESS','0','PUNCHED IN', '', 0),
('1','PUNCHED IN','ESS USER','1','PUNCHED OUT', '', 0),
('1','INITIAL','ESS USER','0','PUNCHED IN', '', 0),
('1','INITIAL','ADMIN','5','PUNCHED IN', '', 0),
('1','PUNCHED IN','ADMIN','6','PUNCHED OUT', '', 0),
('1','PUNCHED IN','ADMIN','2','PUNCHED IN', '', 0),
('1','PUNCHED IN','ADMIN','7','N/A', '', 0),
('1','PUNCHED IN','SYSTEM','1','PUNCHED OUT', '', 0),
('1','PUNCHED OUT','ADMIN','2','PUNCHED OUT', '', 0),
('1','PUNCHED OUT','ADMIN','3','PUNCHED OUT', '', 0),
('1','PUNCHED OUT','ADMIN','7','N/A', '', 0);

-- add attendance details report
SET @admin_user_role_id := (SELECT id FROM ohrm_user_role WHERE name = 'Admin');
SET @supervisor_user_role_id := (SELECT id FROM ohrm_user_role WHERE name = 'Supervisor');

SET @admin_user_role_type := (SELECT id FROM ohrm_user_role_type WHERE name = 'Admin');
SET @supervisor_user_role_type := (SELECT id FROM ohrm_user_role_type WHERE name = 'Supervisor');

SET @time_module_id := (SELECT id FROM ohrm_module WHERE name = 'time');
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Monthly Attendance Report', @time_module_id, 'displayAttendanceDetailsReport', 'time_displayAttendanceDetailsReport');

SET @details_report_screen := (SELECT id FROM ohrm_screen WHERE action_url = 'displayAttendanceDetailsReport');


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

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('attendance_details_report', 'Attendance - Monthly Attendance Report', 1, 0, 0, 0);

SET @attendance_details_report := (SELECT id FROM ohrm_data_group WHERE name = 'attendance_details_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_user_role_id, @attendance_details_report, 1, 0, 0, 0, 0),
(@supervisor_user_role_id, @attendance_details_report, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@attendance_details_report, @details_report_screen, 1);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(@admin_user_role_type, @attendance_details_report),
(@supervisor_user_role_type, @attendance_details_report);

-- HOD user role permission

insert into ohrm_user_role_screen(user_role_id,screen_id,can_read,can_create,can_update,can_delete)
select @hod_id,rs.screen_id,rs.can_read,rs.can_create,rs.can_update,rs.can_delete from ohrm_user_role_screen rs ,ohrm_screen s, ohrm_module m
where rs.screen_id=s.id and s.module_id=m.id and rs.user_role_id=@supervisor_user_role_id and m.name in ('time','attendance','attendanceBulk');

insert into ohrm_user_role_data_group(user_role_id,data_group_id,can_read,can_create,can_update,can_delete,self)
select @hod_id,data_group_id,can_read,can_create,can_update,can_delete,self from ohrm_user_role_data_group where user_role_id=@supervisor_user_role_id and data_group_id in (SELECT id FROM ohrm_data_group WHERE name in
('time_customers','time_projects','attendance_configuration','attendance_records','time_project_reports','time_employee_reports','time_employee_timesheets'));

SET @time_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'time');
SET @ess_user_role_id := (SELECT id FROM ohrm_user_role WHERE name = 'ESS');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, route_name) VALUES
('Display My Attendance Details', @time_module_id, 'displayMyAttendanceDetailsReport', 'time_displayMyAttendanceDetailsReport');

SET @employee_attendance_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Display My Attendance Details');

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

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
    ('My Monthly Attendance', @employee_attendance_screen_id, @attendance_menu_id, 3, 700, NULL, 'vueattendance.viewMyMonthlyAttendance',1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('my_attendance_report', 'Attendance - My Monthly Attendance Report', 1, 0, 0, 0);

SET @my_attendance_report_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'my_attendance_report');

SET @menu_item_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'My Monthly Attendance');
INSERT INTO ohrm_menu_item_data_group( menu_item_id, data_group_id ) VALUES ( @menu_item_id , @my_attendance_report_data_group_id);

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

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@my_attendance_report_data_group_id, @employee_attendance_screen_id, 1, 1);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(@ess_user_role_id, @my_attendance_report_data_group_id);

SET @time_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'time');
SET @time_activity_info_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Activity Info');
SET @admin_user_role_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET @admin_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Admin');


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('time_common_activities', 'Time Tracking - Activity Info - Common Activities', '1', '1', '1', '1');
SET @time_common_activities_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'time_common_activities');

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, @time_common_activities_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_user_role_type_id, @time_common_activities_data_group_id, '1', '1', '1', '1');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES('commonActivities', @time_module_id, 'commonActivities');
SET @common_activities_screen_id := (SELECT id FROM ohrm_screen WHERE `name` = 'commonActivities');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@admin_user_role_id, @common_activities_screen_id, 1, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES (@time_common_activities_data_group_id, @common_activities_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`) VALUES
(NULL, 'Common Activities', NULL, @common_activities_screen_id, @time_activity_info_menu_id, 4, 300, NULL, NULL, 'vuetime.commonActivities', 0, NULL);
SET @common_activities_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Common Activities');

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES (@common_activities_menu_id, @time_common_activities_data_group_id);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('time_configure_timesheet_periods', 'Time Tracking - Configuration - Timesheet Periods', 1, 0, 1, 0);
SET @configure_timesheet_periods_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
  (@admin_user_role_id, @configure_timesheet_periods_data_group_id, 1, 0, 1, 0, 0);

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES('timesheetPeriods', @time_module_id, 'timesheetPeriods');
SET @configure_timesheet_periods_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 (@admin_user_role_id, @configure_timesheet_periods_screen_id, 1, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES (@configure_timesheet_periods_data_group_id, @configure_timesheet_periods_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`) VALUES
(NULL, 'Timesheet Periods', NULL, @configure_timesheet_periods_screen_id, @configure_menu_id, 4, 300, NULL, NULL, 'time.configure_timesheet_period', 0, NULL);
SET @configure_timesheet_periods_menu_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES (@configure_timesheet_periods_menu_id, @configure_timesheet_periods_data_group_id);

INSERT INTO ohrm_data_list (`name`) VALUES ('employee_timesheets_list');

INSERT INTO `ohrm_audittrail_module` (`name`) VALUES ('Time Tracking');
SET @time_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Time Tracking');

CREATE TABLE `ohrm_audittrail_time_project_trail` (
                                                      `id` INT NOT NULL AUTO_INCREMENT,
                                                      `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                                      `action_owner_id` VARCHAR(6),
                                                      `action_owner_name` TEXT,
                                                      `version_id` int(11) NOT NULL DEFAULT '0',
                                                      `affected_entity_id` VARCHAR(20) NOT NULL DEFAULT '',
                                                      `affected_entity_name` TEXT NOT NULL DEFAULT '',
                                                      `action` VARCHAR(255) NOT NULL,
                                                      `action_description` TEXT,
                                                      `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
                                                      `app_id` INT DEFAULT NULL,
                                                      `app_name` VARCHAR(1000) DEFAULT NULL,
                                                      `screen_name` VARCHAR(1000) DEFAULT NULL,
                                                      PRIMARY KEY (`id`),
                                                      INDEX audit_time_project_trail_idx_app_id (app_id),
                                                      INDEX audit_time_project_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('TimeProject', 'Project', 'Project', @time_module_id);

SET @time_project_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='TimeProject' AND `module_id` = @time_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
                                                                                          (@time_project_section_id, 'ADD PROJECT', 'Add Project'),
                                                                                          (@time_project_section_id, 'UPDATE PROJECT', 'Update Project'),
                                                                                          (@time_project_section_id, 'DELETE PROJECT', 'Delete Project');

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'time.project_audit', 'TimeProjectAuditListener', '1', '72');



SET @ohrm_audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Time Tracking');
CREATE TABLE `ohrm_audittrail_time_customer_trail` (
                                                       `id` INT NOT NULL AUTO_INCREMENT,
                                                       `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                                       `action_owner_id` varchar(6),
                                                       `action_owner_name` VARCHAR(300),
                                                       `version_id` INT NOT NULL DEFAULT 0,
                                                       `action` varchar(255) NOT NULL,
                                                       `affected_entity_id` VARCHAR(20),
                                                       `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
                                                       `action_description` MEDIUMTEXT,
                                                       `app_id` INT DEFAULT NULL,
                                                       `app_name` VARCHAR(1000) DEFAULT NULL,
                                                       `screen_name` VARCHAR(1000) DEFAULT NULL,
                                                       PRIMARY KEY (`id`),
                                                       INDEX audit_time_customer_trail_idx_app_id (app_id),
                                                       INDEX audit_time_customer_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('TimeCustomer', 'Customer', 'Customer',@ohrm_audittrail_module_id);

SET @ohrm_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='TimeCustomer');

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_section_id, 'ADD CUSTOMER', 'Add Customer'),
       (@ohrm_audittrail_section_id, 'UPDATE CUSTOMER', 'Update Customer'),
       (@ohrm_audittrail_section_id, 'DELETE CUSTOMER', 'Delete Customer');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
                                                                                         ('time.customer.create', 'CreateCustomerAuditListener', '1', @max_event_listener_position+1),
                                                                                         ('time.customer.update', 'UpdateCustomerAuditListener', '1', @max_event_listener_position+2),
                                                                                         ('time.customer.delete', 'DeleteCustomerAuditListener', '1', @max_event_listener_position+3);


SET @ohrm_data_group_time_projects_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'time_projects');
SET @ohrm_audittrail_section_time_project = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='TimeProject');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
  (@ohrm_audittrail_section_time_project, @ohrm_data_group_time_projects_id, 0),
  (@ohrm_audittrail_section_id, @customer_data_group_id, 0);
