CREATE TABLE `employee_penalties` (
 `employee_id` bigint(20) unsigned NOT NULL,
 `incident_id` bigint(20) DEFAULT NULL,
 `type` enum('leave','salary','suspension') NOT NULL,
 `amount` decimal(5,2) DEFAULT NULL,
 `date` date DEFAULT NULL,
 `effective` date NOT NULL,
 KEY `employee_id` (`employee_id`),
 KEY `type` (`type`),
 KEY `incident_id` (`incident_id`),
 KEY `date` (`date`),
 KEY `effective` (`effective`)
) ENGINE=InnoDB;
CREATE TABLE `letters_penalties` (
 `letter_id` bigint(20) unsigned NOT NULL,
 `type` enum('leave','salary') NOT NULL,
 `amount` decimal(5,2) NOT NULL,
 KEY `letter_id` (`letter_id`),
 KEY `type` (`type`)
) ENGINE=InnoDB;
CREATE PROCEDURE `get_day_suspension`(IN `e_id` BIGINT UNSIGNED, IN `day` DATE) SQL SECURITY INVOKER SELECT incident_id FROM employee_penalties WHERE employee_id = e_id AND date = day;
ALTER TABLE `employee_leaves` CHANGE `leave_qouta` `leave_qouta` DECIMAL(6,1) NOT NULL;
BEGIN;
INSERT INTO `language` (`phrase`, `english`) VALUES 
('has_penalty', 'Letter Has Reward / Penalty'),
('hr_manager_approval_required', 'HR Manager Approval Required'),
('leave_penalty', 'Leave  <small>(+Reward / -Penalty)</small>'),
('salary_penalty', 'Salary <small>(+Reward / -Penalty)</small>'),
('leave_empty_ignore', 'Positive value is addition and negative values is deduction. Leave it zero to disable');
COMMIT;