
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_file_path', '');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_protocol', '');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_file_name_format', 'attendance_ymd');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_processing_frequency', 'D');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_processing_time', '09:00:00');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_file_format', 'csv');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_file_extractor', 'CSVFileExtractor');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_field_map', 'id:0,date:5,punchin:3,punchout:4');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_attendance_process_class', 'AttendanceProcessor');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_time_offset', '5.5');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_email_notification', 'No');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_email_notification_send_copy', '');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_process_log_notification_email', '');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_employee_code_mapper', '');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_attendance_process_service_class', 'AttendanceProcessService');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('devicedata_record_date_format', 'Y-m-d');

CREATE TABLE IF NOT EXISTS `ohrm_attendance_process_log` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `status` varchar(50) NOT NULL,
  `file_name` varchar(50) NOT NULL,
  `comment` varchar(250),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_automatic_punch_record` (
  `id` int(6) NOT NULL AUTO_INCREMENT ,
  `employee_id` int(7) NOT NULL,
  `attendance_id` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `notification_sent` varchar(50) DEFAULT 'No',
  PRIMARY KEY (`id`),
  KEY `employee_id`(`employee_id`),
  KEY `attendance_id`(`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE ohrm_automatic_punch_record
ADD CONSTRAINT
FOREIGN KEY (attendance_id)
REFERENCES  ohrm_attendance_record(id) ON DELETE CASCADE;

ALTER TABLE ohrm_automatic_punch_record
ADD CONSTRAINT
FOREIGN KEY (employee_id)
REFERENCES  hs_hr_employee(emp_number) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `ohrm_employee_biometric_info` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `emp_number` int(6) NOT NULL,
  `bio_id` varchar(20) NOT NULL unique,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE ohrm_employee_biometric_info
ADD CONSTRAINT
FOREIGN KEY (emp_number)
REFERENCES  hs_hr_employee(emp_number) ON DELETE CASCADE;

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');

SET @module_id := (SELECT id FROM `ohrm_module` WHERE `name` = 'attendance');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Attendance Data Upload', @module_id, 'AttendanceFileUpload', 'attendance_AttendanceFileUpload');

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

SET @compensation_pillar_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Compensation' AND `level` = 1);
SET @attendance_menu_id := (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`, `url_extras`, `uisref`, `status`) VALUES
    ('Data Upload', @attendance_file_upload_screen_id, @attendance_menu_id, 3, 800, '','vueattendance.AttendanceFileUpload', 1);

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

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_attendance_data_upload_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_attendance_data_upload_trail_idx_app_id (app_id),
    INDEX audit_attendance_data_upload_trail_idx_screen_name (screen_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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
    ('AttendanceDataUpload', 'Attendance Data Upload', '', @ohrm_audittrail_module_id);

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

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_section_id, 'UPLOAD ATTENDANCE CSV', 'Upload Attendance CSV'),
       (@ohrm_audittrail_section_id, 'PROCESS ATTENDANCE CSV', 'Process Attendance CSV');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
 ('attendance.csv_file_upload', 'AttendanceCsvFileUploadAuditListener', '1', @max_event_listener_position+1),
 ('attendance.csv_file_process', 'AttendanceCsvFileProcessAuditListener', '1', @max_event_listener_position+2);


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('attendance_data_upload', 'Attendance - Data Upload', 1, 1, 0, 0);

SET @attendance_data_upload_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'attendance_data_upload');

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

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
    (@attendance_data_upload_data_group_id, @attendance_file_upload_screen_id, 1);

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
    ( @ohrm_audittrail_section_id, @attendance_data_upload_data_group_id, 0);
