

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` = 'pim');

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

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

SET @attendance_file_upload_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Attendance' AND `level` = 2); 

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Attendance Data Upload', @attendance_file_upload_screen_id, @attendance_file_upload_menu_id, 3, 1000, '', 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);

