ALTER TABLE `attendance_history` ADD `day` DATE NULL DEFAULT NULL AFTER `id`, ADD INDEX (`day`);

/*!50003 DROP PROCEDURE IF EXISTS  `add_attendance` */;

DELIMITER $$

/*!50003 CREATE PROCEDURE `add_attendance`(IN `employee_id` BIGINT, IN `punchin` DATETIME, IN `punchout` DATETIME, IN `flag_in` VARCHAR(24), IN `flag_out` VARCHAR(24), IN `author` BIGINT)
    SQL SECURITY INVOKER
BEGIN
	SET @group_no = (SELECT MAX(`group_no`) FROM `attendance_history`) + 1;
    INSERT INTO `attendance_history` (`id`, `group_no`, `uid`, `day`, `time`, `punch_author`, `active`, `punchin_time`, `punchout_time`, `status`, `duration`, `flag`) VALUES
    (0, @group_no, `employee_id`, DATE(`punchin`), `punchin`, `author`, 1, `punchin`, NULL, 'in', 0, `flag_in`),
    (0, @group_no, `employee_id`, DATE(`punchin`), DATE_ADD(`punchin`, INTERVAL 30 SECOND), `author`, 1, NULL, `punchout`, 'out', TIMESTAMPDIFF(SECOND, `punchin`, `punchout`), `flag_out`);
END */$$
DELIMITER ;

/*!50003 DROP PROCEDURE IF EXISTS  `get_attendance_in_range` */;

DELIMITER $$

/*!50003 CREATE PROCEDURE `get_attendance_in_range`(IN `e_id` bigint unsigned, IN `start` date, IN `end` date)
    READS SQL DATA
    SQL SECURITY INVOKER
SELECT
                    `day`,
                    MIN(punchin_time) AS punchin_time,
                    MAX(punchout_time) AS punchout_time,
                    SUM(duration) AS duration,
                    GROUP_CONCAT(flag) AS status
                FROM attendance_view
                WHERE `uid` = `e_id`
                AND `day` >= CONCAT(start, ' 00:00:00')
                AND `day` <= CONCAT(end, ' 23:59:59')
                GROUP BY `day`
                ORDER BY `day` ASC */$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `test_attendance`;

DELIMITER $$
CREATE PROCEDURE `test_attendance`(IN `in_uid` BIGINT, IN `in_group_no` BIGINT, IN `in_day` DATE)
BEGIN
UPDATE `attendance_history` SET `day` = `in_day` WHERE `uid` = `in_uid` AND `group_no` = `in_group_no`;
END$$
DELIMITER ;

CREATE 
     OR REPLACE ALGORITHM = UNDEFINED
    SQL SECURITY INVOKER
VIEW `attendance_view` AS
    SELECT 
        `attendance_history`.`group_no` AS `group_no`,
        `attendance_history`.`uid` AS `uid`,
        MIN(`day`) as `day`,
        MIN(`attendance_history`.`punchin_time`) AS `punchin_time`,
        MAX(`attendance_history`.`punchout_time`) AS `punchout_time`,
        MAX(`attendance_history`.`duration`) AS `duration`,
        GROUP_CONCAT(`attendance_history`.`flag`
            SEPARATOR ',') AS `flag`
    FROM
        `attendance_history`
    WHERE
        (`attendance_history`.`active` = 1)
    GROUP BY `attendance_history`.`group_no` , `attendance_history`.`uid`;


DROP procedure IF EXISTS `get_current_worked_time`;

DELIMITER $$
CREATE PROCEDURE `get_current_worked_time`(IN employee_id BIGINT, IN shift_start DATETIME)
BEGIN
(SELECT
    punchin_time,
    punchout_time,
    flag,
    IF(punchout_time IS NOT NULL,
        duration,
        TIMESTAMPDIFF(SECOND,
            punchin_time,
            CURRENT_TIMESTAMP())) AS duration
FROM
    attendance_view
WHERE
    `day` >= DATE(shift_start)
        AND uid = employee_id)
UNION
(SELECT
    punchin_time,
    punchout_time,
    flag,
    IF(punchout_time IS NOT NULL,
        duration,
        TIMESTAMPDIFF(SECOND,
            punchin_time,
            CURRENT_TIMESTAMP())) AS duration
FROM
    attendance_view
WHERE
    punchout_time IS NULL
        AND uid = employee_id);
END$$

DELIMITER ;