SET SESSION sql_mode = '';
CREATE TABLE `leave_applied_days` (
 `leave_appl_id` int(10) unsigned NOT NULL,
 `day` date NOT NULL,
 `type` enum('full_day','half_day') NOT NULL,
 `applied` bit(1) NOT NULL DEFAULT b'0',
 `active` bit(1) NOT NULL DEFAULT b'1',
 KEY `leave_appl_id` (`leave_appl_id`),
 KEY `day` (`day`),
 KEY `active` (`active`),
 KEY `applied` (`applied`)
) ENGINE=InnoDB;

ALTER TABLE `leave_apply`
    CHANGE `leave_appl_id` `leave_appl_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    CHANGE `apply_day` `apply_day` DECIMAL(6,1) NOT NULL,
    CHANGE `num_aprv_day` `num_aprv_day` DECIMAL(6,1) NULL DEFAULT NULL; 

CREATE PROCEDURE `get_day_leave`(IN `e_id` BIGINT UNSIGNED, IN `day` DATE) SQL SECURITY INVOKER SELECT leave_apply.leave_appl_id, IFNULL( leave_applied_days.type, 'full_day' ) as type FROM leave_apply LEFT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.day = day AND leave_applied_days.active = 1 WHERE leave_apply.employee_id = e_id AND leave_apply.leave_aprv_strt_date <= day AND leave_apply.leave_aprv_end_date >= day AND leave_apply.hr_status = 'approved';
CREATE PROCEDURE `get_used_leaves`(IN `leave_type` INT UNSIGNED, IN `e_id` BIGINT UNSIGNED, IN `year` YEAR) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER SELECT SUM(IF(LEAVES.type IS NULL, LEAVES.num_aprv_day, IF(LEAVES.type = 'full_day', 1, 0.5))) AS lv FROM (SELECT leave_apply.leave_appl_id, leave_apply.employee_id, leave_apply.num_aprv_day, leave_apply.leave_type_id, leave_apply.hr_status, leave_apply.leave_aprv_strt_date, leave_apply.leave_aprv_end_date, leave_applied_days.day, leave_applied_days.type, leave_applied_days.active FROM leave_apply LEFT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.active = 1 WHERE leave_apply.hr_status = 'approved' UNION SELECT leave_apply.leave_appl_id, leave_apply.employee_id, leave_apply.num_aprv_day, leave_apply.leave_type_id, leave_apply.hr_status, leave_apply.leave_aprv_strt_date, leave_apply.leave_aprv_end_date, leave_applied_days.day, leave_applied_days.type, leave_applied_days.active FROM leave_apply RIGHT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.active = 1 WHERE leave_apply.hr_status = 'approved') AS LEAVES WHERE `leave_type_id` = leave_type AND `employee_id` = e_id AND `leave_aprv_strt_date` >= CONCAT(year, '-01-01') AND `leave_aprv_end_date` <= CONCAT(year, '-12-31');

BEGIN;
INSERT INTO `language` (`phrase`, `english`) VALUES
('leave_successfully_created', 'Leave application successfully created'),
('attachments', 'Attachments'),
('success', 'Success');
COMMIT;