INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('attendance.is_selfie_mandatory', 'No');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('attendance.is_geo_location_mandatory', 'No');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('attendance.max_selfie_size', '5');

INSERT INTO `ohrm_secure_configuration` (`configuration_name`, `configuration`) VALUES ('attendance.geo_location_api_url', 'CF97B77400B21606633D41DF836B2C8FC2AB2B6FCC9167090013D46AB76DC55F6463781C8875C2BF7E79B4769A404A8FA51CD2A89BF028B2810EE78686244907');

INSERT INTO `ohrm_secure_configuration` (`configuration_name`, `configuration`) VALUES ('attendance.geo_location_api_key', 'xyz');

CREATE TABLE ohrm_attendance_record_extra_detail
(id INT AUTO_INCREMENT,
    attendance_id BIGINT NOT NULL,
    punch_in_latitude VARCHAR(20),
    punch_in_longitude VARCHAR(20),
    punch_in_address VARCHAR(500),
    punch_in_picture_type VARCHAR(10),
    punch_in_picture_size FLOAT(18, 2),
    punch_in_picture LONGBLOB,
    punch_out_latitude VARCHAR(20),
    punch_out_longitude VARCHAR(20),
    punch_out_address VARCHAR(500),
    punch_out_picture_type VARCHAR(10),
    punch_out_picture_size FLOAT(18, 2),
    punch_out_picture LONGBLOB,
    additional_info text,
    created_date timestamp DEFAULT current_timestamp(),
    PRIMARY KEY(id),
foreign key (`attendance_id`) references `ohrm_attendance_record` (`id`) on delete cascade on update cascade) ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_cleanup_entities_config` (`id`, `entity_name`, `configuration`, `delete_days_after`, `date_configuration_getter`, `delete_strategy`)
VALUES (NULL, 'AttendanceRecordExtraDetail', '{"columnNames":[ "punchInPicture", "punchOutPicture"]}', '30', 'created_date', 'BaseDataDeletionStrategyDao');

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_period (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    period_generator_class VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_attendance_pay_period` (`id`, `name`, `period_generator_class`) VALUES
(1, 'Monthly', 'MonthlyAttendancePayPeriodGenerator'),
(2, 'Weekly', 'WeeklyAttendancePayPeriodGenerator'),
(3, 'Every Other Week', 'BiWeeklyAttendancePayPeriodGenerator'),
(4, 'Semi-Monthly', 'SemiMonthlyAttendancePayPeriodGenerator');

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_policy (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    pay_period_id INT NOT NULL,
    start_date DATE NOT NULL,
    first_period_end_date DATE NULL DEFAULT NULL,
    consecutive_days INT(4),
    rules LONGTEXT default null,
    deleted_at DATETIME DEFAULT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY `name` (`name`),
    CONSTRAINT `fk_ohrm_attendance_pay_policy_ohrm_attendance_pay_period` FOREIGN KEY (`pay_period_id`) REFERENCES `ohrm_attendance_pay_period` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

-- INSERT INTO `ohrm_attendance_pay_policy` (`name`, `pay_period_id`, `start_date`, `consecutive_days`, `rules`, `deleted_at`) VALUES
-- ('Default', '1', CURDATE(), NULL, '<?xml version="1.0"?><pay_policy></pay_policy>', NULL);

create table `ohrm_attendance_pay_code`(
  `id` INT(11) not null AUTO_INCREMENT,
  `pay_policy_id` INT(11) not null,
  `code` VARCHAR(10) not null,
  `description` VARCHAR(100) null,
  primary key (`id`),
  UNIQUE KEY `uk_pay_policy_id_code` (`pay_policy_id`, `code`),
  CONSTRAINT `fk_ohrm_attendance_pay_code_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE
) engine=innodb default charset=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_policy_emp_status (
    id INT AUTO_INCREMENT,
    pay_policy_id INT(11),
    emp_status_id INT(11) NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_pay_policy_emp_status_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_pay_policy_emp_status_emp_status_id` FOREIGN KEY (`emp_status_id`) REFERENCES `ohrm_employment_status` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_policy_job_title (
    id INT AUTO_INCREMENT,
    pay_policy_id INT(11),
    job_title_id INT(11) NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_pay_policy_job_title_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_pay_policy_job_title_job_title_id` FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_policy_location (
    id INT AUTO_INCREMENT,
    pay_policy_id INT(11),
    location_id INT(11) NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_pay_policy_location_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_pay_policy_location_location_id` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_policy_country (
    id INT AUTO_INCREMENT,
    pay_policy_id INT(11),
    cou_code char(2) NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_pay_policy_country_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_pay_policy_country_cou_code` FOREIGN KEY (`cou_code`) REFERENCES `hs_hr_country` (`cou_code`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_pay_policy_subunit (
    id INT AUTO_INCREMENT,
    pay_policy_id INT(11),
    sub_unit_id INT(11) NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_pay_policy_subunit_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_pay_policy_subunit_sub_unit_id` FOREIGN KEY (`sub_unit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_sheet_period (
    id INT AUTO_INCREMENT,
    pay_policy_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    active TINYINT(1) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_sheet_period_ohrm_attendance_pay_policy` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_sheet_status (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_attendance_sheet_status` (`id`, `name`) VALUES
(1, 'NOT SUBMITTED'),
(2, 'SUBMITTED'),
(3, 'APPROVED'),
(4, 'REJECTED');

CREATE TABLE IF NOT EXISTS ohrm_attendance_sheet (
    id INT AUTO_INCREMENT,
    period_id INT NOT NULL,
    emp_number INT(7) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    state_id INT NOT NULL DEFAULT 1,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_sheet_ohrm_attendance_sheet_period` FOREIGN KEY (`period_id`) REFERENCES `ohrm_attendance_sheet_period` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_sheet_hs_hr_employee` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_sheet_ohrm_attendance_sheet_status` FOREIGN KEY (`state_id`) REFERENCES `ohrm_attendance_sheet_status` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_sheet_record (
    id INT AUTO_INCREMENT,
    attendance_sheet_id INT NOT NULL,
    date DATE NOT NULL,
    pay_code_id INT(11) not null,
    duration BIGINT,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_sheet_record_ohrm_attendance_sheet` FOREIGN KEY (`attendance_sheet_id`) REFERENCES `ohrm_attendance_sheet` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_sheet_record_ohrm_attendance_pay_code` FOREIGN KEY (`pay_code_id`) REFERENCES `ohrm_attendance_pay_code` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_attendance_sheet_action_log (
    id INT AUTO_INCREMENT,
    attendance_sheet_id INT NOT NULL,
    performed_by_user_id int(10),
    performed_by_emp_number INT(7),
    action varchar(50),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comment varchar(255) default null,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_attendance_sheet_action_log_ohrm_attendance_sheet` FOREIGN KEY (`attendance_sheet_id`) REFERENCES `ohrm_attendance_sheet` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_sheet_action_log_ohrm_user` FOREIGN KEY (`performed_by_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_attendance_sheet_action_log_hs_hr_employee` FOREIGN KEY (`performed_by_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_attendance_pay_policy_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP 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,
    `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_attendance_pay_policy_trail_idx_app_id (app_id),
    INDEX audit_attendance_pay_policy_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE ohrm_job_record_history ADD CONSTRAINT `fk_job_record_history_pay_policy_id` FOREIGN KEY (`pay_policy_id`) REFERENCES `ohrm_attendance_pay_policy` (`id`) ON DELETE SET NULL;

INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES
('HourlyEmployee', 'Hourly Employee', 0, 1),
('SalariedEmployee', 'Salaried Employee', 0, 1);

SET @module_id := (SELECT id FROM ohrm_module WHERE name = 'attendance');
SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');
SET @sup_id := (SELECT id FROM ohrm_user_role where name = 'Supervisor');
SET @hod_id := (SELECT id FROM ohrm_user_role where name = 'HeadOfDepartment');
SET @ess_id := (SELECT id FROM ohrm_user_role where name = 'ESS');
SET @hourly_ess := (SELECT id FROM ohrm_user_role where name = 'HourlyEmployee');
SET @salaried_ess := (SELECT id FROM ohrm_user_role where name = 'SalariedEmployee');

INSERT INTO ohrm_screen (name, module_id, action_url) VALUES
( 'Pay Policies', @module_id, 'viewPayPolicies'),
( 'Attendance Pay Hours Report', @module_id, 'viewAttendancePayHoursReport'),
( 'Attendance Hours Report', @module_id, 'viewAttendanceHoursReport'),
( 'Approve Attendance Sheets', @module_id, 'approveAttendanceSheets'),
( 'My Attendance Sheet', @module_id, 'viewMyAttendanceSheet');

SET @attendance_pay_hours_report_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewAttendancePayHoursReport');
SET @attendance_hours_report_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewAttendanceHoursReport');
SET @my_attendance_sheet_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewMyAttendanceSheet');
SET @approve_attendance_sheets_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'approveAttendanceSheets');
SET @pay_policies_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewPayPolicies');
SET @view_my_attendance_screen_id := (SELECT id FROM ohrm_screen WHERE action_url = 'viewMyAttendanceRecord');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_id, @pay_policies_screen_id, 1, 0, 0, 0),
(@admin_id, @attendance_pay_hours_report_screen_id, 1, 0, 0, 0),
(@hod_id, @attendance_pay_hours_report_screen_id, 1, 0, 0, 0),
(@sup_id, @attendance_pay_hours_report_screen_id, 1, 0, 0, 0),
(@admin_id, @attendance_hours_report_screen_id, 1, 0, 0, 0),
(@hod_id, @attendance_hours_report_screen_id, 1, 0, 0, 0),
(@sup_id, @attendance_hours_report_screen_id, 1, 0, 0, 0),
(@admin_id, @approve_attendance_sheets_screen_id, 1, 0, 0, 0),
(@hod_id, @approve_attendance_sheets_screen_id, 1, 0, 0, 0),
(@sup_id, @approve_attendance_sheets_screen_id, 1, 0, 0, 0),
(@hourly_ess, @my_attendance_sheet_screen_id, 1, 0, 0, 0),
(@salaried_ess, @view_my_attendance_screen_id, 1, 0, 0, 0);

INSERT INTO ohrm_data_group (name, description, can_read, can_create, can_update, can_delete) VALUES
('pay_policies', 'Attendance - Pay Policy', 1, 1, 1, 1),
('attendance_sheets', 'Attendance - Attendance Sheets', 1, 1, 1, 0),
('attendance_hours_report', 'Attendance - Hours Report', 1, 0, 0, 0);

SET @attendance_hours_report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'attendance_hours_report');
SET @attendance_sheets_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'attendance_sheets');
SET @pay_policies_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'pay_policies');
SET @attendance_records_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'attendance_records');

INSERT INTO ohrm_user_role_data_group (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self) VALUES
(@admin_id, @pay_policies_data_group_id, 1, 1, 1, 1, 0),
(@admin_id, @attendance_hours_report_data_group_id, 1, 0, 0, 0, 0),
(@hod_id, @attendance_hours_report_data_group_id, 1, 0, 0, 0, 0),
(@sup_id, @attendance_hours_report_data_group_id, 1, 0, 0, 0, 0),
(@admin_id, @attendance_sheets_data_group_id, 1, 0, 0, 0, 0),
(@hod_id, @attendance_sheets_data_group_id, 1, 0, 0, 0, 0),
(@sup_id, @attendance_sheets_data_group_id, 1, 0, 0, 0, 0),
(@hourly_ess, @attendance_records_data_group_id, 1, 0, 0, 0, 1),
(@salaried_ess, @attendance_records_data_group_id, 1, 0, 0, 0, 1),
(@hourly_ess, @attendance_sheets_data_group_id, 1, 0, 0, 0, 1),
(@salaried_ess, @attendance_sheets_data_group_id, 1, 0, 0, 0, 1);

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_id, @attendance_hours_report_data_group_id, 1, 0, 0, 0),
(@sup_id, @attendance_hours_report_data_group_id, 1, 0, 0, 0);

INSERT INTO ohrm_data_group_screen (data_group_id, screen_id, permission, self) VALUES
(@pay_policies_data_group_id,@pay_policies_screen_id ,1 ,0),
(@attendance_sheets_data_group_id,@attendance_pay_hours_report_screen_id ,1 ,0),
(@attendance_hours_report_data_group_id,@attendance_hours_report_screen_id ,1 ,0),
(@attendance_sheets_data_group_id,@approve_attendance_sheets_screen_id ,1 ,0);

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

INSERT INTO ohrm_menu_item (menu_title, screen_id, parent_id, level, order_hint,  uisref, status) VALUES
('Pay Policies', @pay_policies_screen_id,  @attendance_menu, 3, 700, 'attendance.pay_policies', '0'),
('Approve Attendance Sheets', @approve_attendance_sheets_screen_id,  @attendance_menu, 3, 200, 'attendance.attendance_sheets', '0'),
('Attendance Sheets', @attendance_pay_hours_report_screen_id,  @attendance_menu, 3, 100, 'report_view_oxd_with_persistence({"id": "Attendance_Pay_Hours_Report", "filters": null, "breadCrumbs": { "0" : {"name": "Attendance"}}, "reportNameAsMenuPill": false})', '0'),
('My Attendance Sheet', @my_attendance_sheet_screen_id,  @attendance_menu, 3, 500, 'attendance.my_attendance_sheet', '1');

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
('ATTENDANCE SHEET', 'Attendance Sheet', null, '1,3', '2', 'attendance', 'Attendance', @attendance_module_id, 3);

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
('ATTENDANCE SHEET','NOT SUBMITTED','SYSTEM','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','NOT SUBMITTED','ESS USER','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','NOT SUBMITTED','ADMIN','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','NOT SUBMITTED','SUPERVISOR','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','NOT SUBMITTED','HEADOFDEPARTMENT','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','SUBMITTED','ADMIN','Approve','APPROVED', '', 0),
('ATTENDANCE SHEET','SUBMITTED','SUPERVISOR','Approve','APPROVED', '', 0),
('ATTENDANCE SHEET','SUBMITTED','HEADOFDEPARTMENT','Approve','APPROVED', '', 0),
('ATTENDANCE SHEET','SUBMITTED','ADMIN','Reject','REJECTED', '', 0),
('ATTENDANCE SHEET','SUBMITTED','SUPERVISOR','Reject','REJECTED', '', 0),
('ATTENDANCE SHEET','SUBMITTED','HEADOFDEPARTMENT','Reject','REJECTED', '', 0),
('ATTENDANCE SHEET','REJECTED','ESS USER','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','REJECTED','ADMIN','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','REJECTED','SUPERVISOR','Submit','SUBMITTED', '', 0),
('ATTENDANCE SHEET','REJECTED','HEADOFDEPARTMENT','Submit','SUBMITTED', '', 0);

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('attendance_sheet.auto_submit_enabled', '1'),
('attendance_sheet.frozen_statuses', 'APPROVED'),
('attendance.pay_policies_enabled', '0'),
('attendance.default_week_start_day', 'sunday'),
('attendance.auto_punch_out_missing_punch_out_records_at_day_end', 'No'),
('attendance.auto_punch_out_missing_punch_out_records_at_shift_end', 'No'),
('attendance.auto_punch_out_missing_punch_out_duration_to_run_hours', 24),
('attendance.export_attendance_to_csv.summary_strategy', 'DailyBreakdown');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('attendance.update_records', 'AttendanceAssignPayCodesListener', '1', @max_event_listener_position+1),
('attendance.delete_records', 'AttendanceAssignPayCodesListener', '1', @max_event_listener_position+2),
('admin.subunit_add', 'SubunitAddListenerForPayPolicyCriteriaUpdate', '1', @max_event_listener_position+3),
('admin.location_add', 'LocationAddListenerForPayPolicyCriteriaUpdate', '1', @max_event_listener_position+4),
('attendance.add_pay_policy', 'AddAttendancePayPolicyAuditListener', '1', @max_event_listener_position+5),
('attendance.update_pay_policy', 'UpdateAttendancePayPolicyAuditListener', '1', @max_event_listener_position+6),
('attendance.delete_pay_policy', 'DeleteAttendancePayPolicyAuditListener', '1', @max_event_listener_position+7),
('attendance.update_pay_policy_employee_selection_criteria', 'UpdateAttendancePayPolicyEmployeeSelectionCriteriaAuditListener', '1', @max_event_listener_position+8),
('admin.job_title_delete', 'JobTitleDeleteListenerForPayPolicyCriteriaUpdate', '1', @max_event_listener_position+9),
('admin.subunit_delete', 'SubunitDeleteListenerForPayPolicyCriteriaUpdate', '1', @max_event_listener_position+10),
('admin.location_delete', 'LocationDeleteListenerForPayPolicyCriteriaUpdate', '1', @max_event_listener_position+11),
('admin.employment_status_delete', 'EmploymentStatusDeleteListenerForPayPolicyCriteriaUpdate', '1', @max_event_listener_position+12),
('leave.holiday_add', 'RecalculatePayCodesOnHolidayListener', '1', @max_event_listener_position+13),
('leave.holiday_update', 'RecalculatePayCodesOnHolidayListener', '1', @max_event_listener_position+14),
('leave.holiday_delete', 'RecalculatePayCodesOnHolidayListener', '1', @max_event_listener_position+15),
('attendance.update_configurations', 'UpdateAttendanceConfigurationAuditListener', '1', @max_event_listener_position+16),
('attendance.delete_records', 'DeleteAttendanceRecordAuditListener', '1', @max_event_listener_position+17),
('attendance.update_records', 'UpdateAttendanceRecordAuditListener', '1', @max_event_listener_position+18),
('attendance.state_change', 'AttendanceRecordStateChangeAuditListener', '1', @max_event_listener_position+19),
('attendance.sheet_state_change', 'AttendanceSheetStateChangeAuditListener', '1', @max_event_listener_position+20),
('attendance.sheet_create', 'CreateAttendanceSheetAuditListener', '1', @max_event_listener_position+21),
('attendance.auto_punch_out_attendance_record', 'AutoPunchOutAttendanceRecordAuditListener', '1', @max_event_listener_position+22);

INSERT INTO `ohrm_audittrail_module` (`name`) VALUES ('Attendance');
SET @ohrm_audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Attendance');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('AttendancePayPolicy', 'Pay Policy', 'Pay Policy', @ohrm_audittrail_module_id);

SET @ohrm_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AttendancePayPolicy');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@ohrm_audittrail_section_id, 'ADD PAY POLICY', 'Add Pay Policy'),
(@ohrm_audittrail_section_id, 'UPDATE PAY POLICY', 'Update Pay Policy'),
(@ohrm_audittrail_section_id, 'DELETE PAY POLICY', 'Delete Pay Policy'),
(@ohrm_audittrail_section_id, 'UPDATE EMPLOYEE SELECTION CRITERIA', 'Update Employee Selection Criteria');

UPDATE ohrm_advanced_report SET icon = 'ohrm_clock_with_person' WHERE unique_name IN ('Attendance_Pay_Hours_Report', 'Attendance_Hours_Report');

SET @punch_in_out_action_url := (SELECT `action_url` FROM `ohrm_screen` WHERE `name`='Punch In/Out');
insert into ohrm_workflow_screen (workflow_name, user_role_type_ids, module, action)
values ('attendance', 2, 'attendance', @punch_in_out_action_url);


--  Attendance configuration audit
CREATE TABLE IF NOT EXISTS `ohrm_audittrail_attendance_configuration_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP 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_attendance_configuration_trail_idx_app_id (app_id),
    INDEX audit_attendance_configuration_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('AttendanceConfiguration', 'Attendance Configuration', '', @ohrm_audittrail_module_id);

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

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_section_id, 'UPDATE ATTENDANCE CONFIGURATION', 'Update Attendance Configuration');

--  Attendance record audit
CREATE TABLE IF NOT EXISTS `ohrm_audittrail_attendance_record_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP 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_attendance_record_trail_idx_app_id (app_id),
    INDEX audit_attendance_record_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('AttendanceRecord', 'Attendance Record', 'Employee', @ohrm_audittrail_module_id);

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

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_section_id, 'PUNCH IN', 'Punch In'),
       (@ohrm_audittrail_section_id, 'PUNCH OUT', 'Punch Out'),
       (@ohrm_audittrail_section_id, 'ADD ATTENDANCE RECORD', 'Add Attendance Record'),
       (@ohrm_audittrail_section_id, 'UPDATE ATTENDANCE RECORD', 'Update Attendance Record'),
       (@ohrm_audittrail_section_id, 'DELETE ATTENDANCE RECORD', 'Delete Attendance Record'),
       (@ohrm_audittrail_section_id, 'AUTO PUNCH OUT RECORD', 'Auto Punch Out Record');

--  Attendance sheet audit
CREATE TABLE IF NOT EXISTS `ohrm_audittrail_attendance_sheet_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP 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_attendance_sheet_trail_idx_app_id (app_id),
    INDEX audit_attendance_sheet_trail_idx_screen_name (screen_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('AttendanceSheet', 'Attendance Sheet', 'Employee', @ohrm_audittrail_module_id);

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

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_section_id, 'CREATE', 'Create'),
       (@ohrm_audittrail_section_id, 'SUBMIT', 'Submit'),
       (@ohrm_audittrail_section_id, 'APPROVE', 'Approve'),
       (@ohrm_audittrail_section_id, 'REJECT', 'Reject');
