-- MySQL dump 10.13  Distrib 8.0.36, for Win64 (x86_64)
--
-- Host: 64.31.43.162    Database: techwork_dump_simpleclock
-- ------------------------------------------------------
-- Server version	5.5.5-10.6.17-MariaDB-cll-lve

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping routines for database 'techwork_dump_simpleclock'
--
/*!50003 DROP PROCEDURE IF EXISTS `AddOrUpdateSettings` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `AddOrUpdateSettings`(
    IN p_company_id INT,
    IN p_region_id INT,
    IN p_location_id INT,
    IN p_device_id VARCHAR(255),
    IN p_ip VARCHAR(255),
    IN p_updated_by INT,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_data JSON
)
BEGIN
    DECLARE v_settings_id INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        GET DIAGNOSTICS CONDITION 1 
            @p_sqlstate = RETURNED_SQLSTATE,
            @p_errno = MYSQL_ERRNO,
            @p_text = MESSAGE_TEXT;
        SET p_success = FALSE;
        SET p_message = CONCAT('SQL Error: ', @p_sqlstate, ', ', @p_errno, ', ', @p_text);
        SET p_data = NULL;
    END;

    -- Check if settings already exist for the given company and region
    SELECT id INTO v_settings_id
    FROM settings
    WHERE company_id = p_company_id AND region_id = p_region_id
    LIMIT 1;

    IF v_settings_id IS NOT NULL THEN
        -- Update existing settings
        UPDATE settings
        SET location_id = p_location_id,
            device_id = p_device_id,
            ip = p_ip,
            updated_by = p_updated_by,
            updated_at = NOW()
        WHERE id = v_settings_id;

        SET p_success = TRUE;
        SET p_message = 'Settings updated successfully.';

        -- Fetch updated settings
        SELECT JSON_OBJECT(
            'id', id,
            'company_id', company_id,
            'region_id', region_id,
            'location_id', location_id,
            'device_id', device_id,
            'ip', ip,
            'created_at', created_at,
            'updated_at', updated_at,
            'updated_by', updated_by
        ) INTO p_data
        FROM settings
        WHERE id = v_settings_id;

    ELSE
        -- Insert new settings
        INSERT INTO settings (company_id, region_id, location_id, device_id, ip, created_at, updated_at, updated_by)
        VALUES (p_company_id, p_region_id, p_location_id, p_device_id, p_ip, NOW(), NOW(), p_updated_by);

        SET v_settings_id = LAST_INSERT_ID();
        SET p_success = TRUE;
        SET p_message = 'Settings added successfully.';

        -- Fetch newly added settings
        SELECT JSON_OBJECT(
            'id', id,
            'company_id', company_id,
            'region_id', region_id,
            'location_id', location_id,
            'device_id', device_id,
            'ip', ip,
            'created_at', created_at,
            'updated_at', updated_at,
            'updated_by', updated_by
        ) INTO p_data
        FROM settings
        WHERE id = v_settings_id;
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `AdminLogin` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `AdminLogin`(
    IN p_email VARCHAR(255),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_admin JSON,
    OUT p_regions JSON
)
BEGIN
    DECLARE v_admin_id INT;
    DECLARE v_admin JSON;
    DECLARE v_regions JSON;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
        SET p_admin = NULL;
        SET p_regions = NULL;
    END;

    SET v_admin_id = (SELECT id FROM admins WHERE email = p_email LIMIT 1);

    IF v_admin_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Admin not found.';
        SET p_admin = NULL;
        SET p_regions = NULL;
    ELSE
        SELECT JSON_OBJECT(
            'id', id,
            'company_id', company_id
        ) INTO v_admin
        FROM admins
        WHERE id = v_admin_id;

        SELECT JSON_ARRAYAGG(name) INTO v_regions
        FROM regions;

        SET p_success = TRUE;
        SET p_message = 'Admin logged in successfully.';
    END IF;

    SET p_admin = v_admin;
    SET p_regions = v_regions;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `ClockInAttendance` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `ClockInAttendance`(
    IN p_email VARCHAR(255),
    IN p_clock_in_time TIME,
    IN p_server_ip VARCHAR(255),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_data JSON
)
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_today DATE;
    DECLARE v_exists INT DEFAULT 0;
    DECLARE v_last_clock_in DATETIME;
	DECLARE v_working_time TIME DEFAULT '00:00:00';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
        ROLLBACK;
    END;

    SET v_today = CURDATE();
    START TRANSACTION;

    SELECT id INTO v_employee_id
    FROM employees
    WHERE email = p_email
    LIMIT 1;

    IF v_employee_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Employee not found.';
        ROLLBACK;
    ELSE
        SELECT COUNT(*) INTO v_exists
        FROM attendances
        WHERE employee_id = v_employee_id AND date = v_today;

        IF v_exists > 0 THEN
            SET p_success = FALSE;
            SET p_message = 'Employee has already checked in today.';
            ROLLBACK;
        ELSE
            UPDATE employees
            SET status_type_id = 2
            WHERE id = v_employee_id;

            SET v_last_clock_in = CONCAT(v_today, ' ', p_clock_in_time);

            INSERT INTO attendances (employee_id, date, clock_in_time, server_ip, working_time, last_clock_in)
            VALUES (v_employee_id, v_today, p_clock_in_time, p_server_ip, v_working_time, v_last_clock_in);

            COMMIT;
            SET p_success = TRUE;
            SET p_message = 'Employee logged in successfully.';

            SELECT JSON_OBJECT(
                'employee_id', v_employee_id,
                'date', v_today,
                'clock_in_time', p_clock_in_time,
                'server_ip', p_server_ip,
				'working_time', v_working_time,
                'last_clock_in', v_last_clock_in
            ) INTO p_data;
        END IF;
    END IF;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `ClockOutAttendance` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `ClockOutAttendance`(
    IN p_email VARCHAR(255),
    IN p_clock_out_time TIME,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_data JSON
)
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_today DATE;
    DECLARE v_attendance_id INT;
    DECLARE v_clock_in_time TIME;
    DECLARE v_total_break_time INT; 
    DECLARE v_working_time TIME;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
        ROLLBACK;
    END;

    SET v_today = CURDATE();
    START TRANSACTION;

    -- Find employee ID
    SELECT id INTO v_employee_id
    FROM employees
    WHERE email = p_email
    LIMIT 1;

    IF v_employee_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Employee not found.';
        ROLLBACK;
    ELSE
        SELECT id, clock_in_time INTO v_attendance_id, v_clock_in_time
        FROM attendances
        WHERE employee_id = v_employee_id AND date = v_today
        LIMIT 1;

        IF v_attendance_id IS NULL THEN
            SET p_success = FALSE;
            SET p_message = 'No check-in record found for today.';
            ROLLBACK;
        ELSE
            SELECT COALESCE(SUM(TIME_TO_SEC(time_spend)), 0) INTO v_total_break_time
            FROM breaks
            WHERE attendance_id = v_attendance_id;

            SET @work_duration_seconds = TIME_TO_SEC(TIMEDIFF(CONCAT(v_today, ' ', p_clock_out_time), CONCAT(v_today, ' ', v_clock_in_time))) - v_total_break_time;

            SET v_working_time = SEC_TO_TIME(@work_duration_seconds);

            -- Update employee status
            UPDATE employees
            SET status_type_id = 1
            WHERE id = v_employee_id;

            UPDATE attendances
            SET clock_out_time = p_clock_out_time, working_time = v_working_time
            WHERE id = v_attendance_id;

            COMMIT;
            SET p_success = TRUE;
            SET p_message = 'Check-out recorded successfully.';

            SELECT JSON_OBJECT(
                'employee_id', a.employee_id,
                'date', a.date,
                'clock_in_time', a.clock_in_time,
                'clock_out_time', a.clock_out_time,
                'server_ip', a.server_ip,
                'working_time', a.working_time,
                'last_clock_in', a.last_clock_in
            ) INTO p_data
            FROM attendances a
            WHERE a.id = v_attendance_id;
        END IF;
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `EndBreak` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `EndBreak`(
    IN p_email VARCHAR(255),
    IN p_break_end_time TIME,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_break_data JSON
)
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_today DATE;
    DECLARE v_attendance_id INT;
    DECLARE v_break_id INT;
    DECLARE v_break_start_time TIME;
    DECLARE v_time_spend TIME;
    DECLARE v_start_date_time DATETIME;
    DECLARE v_end_date_time DATETIME;
    DECLARE v_break_data_json TEXT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
    END;

    SET v_today = CURDATE();
    START TRANSACTION;

    -- Find employee ID
    SELECT id INTO v_employee_id
    FROM employees
    WHERE email = p_email
    LIMIT 1;

    IF v_employee_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Employee not found.';
    ELSE
        SELECT id INTO v_attendance_id
        FROM attendances
        WHERE employee_id = v_employee_id AND date = v_today
        LIMIT 1;

        IF v_attendance_id IS NULL THEN
            SET p_success = FALSE;
            SET p_message = 'Attendance record not found.';
        ELSE
            SELECT id, break_start_time INTO v_break_id, v_break_start_time
            FROM breaks
            WHERE attendance_id = v_attendance_id AND date = v_today AND break_end_time IS NULL
            LIMIT 1;

            IF v_break_id IS NULL THEN
                SET p_success = FALSE;
                SET p_message = 'Break record not found.';
            ELSE
                SET v_start_date_time = STR_TO_DATE(CONCAT(v_today, ' ', v_break_start_time), '%Y-%m-%d %H:%i:%s');
                SET v_end_date_time = STR_TO_DATE(CONCAT(v_today, ' ', p_break_end_time), '%Y-%m-%d %H:%i:%s');
                SET v_time_spend = TIMEDIFF(v_end_date_time, v_start_date_time);

                UPDATE employees
                SET status_type_id = 2
                WHERE id = v_employee_id;

                UPDATE breaks
                SET break_end_time = p_break_end_time, time_spend = v_time_spend
                WHERE id = v_break_id;

                SELECT JSON_OBJECT('id', id, 'attendance_id', attendance_id, 'break_type_id', break_type_id, 'date', date, 'break_start_time', break_start_time, 'break_end_time', break_end_time, 'time_spend', time_spend) INTO v_break_data_json
                FROM breaks
                WHERE id = v_break_id;

                COMMIT;
                SET p_success = TRUE;
                SET p_message = 'Break ended successfully.';
                SET p_break_data = v_break_data_json;
            END IF;
        END IF;
    END IF;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `GetLocationsByRegion` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `GetLocationsByRegion`(
    IN p_region_name VARCHAR(255),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_region_id INT,
    OUT p_data JSON
)
BEGIN
    DECLARE v_region_id INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
        SET p_region_id = NULL;
        SET p_data = NULL;
    END;

    SELECT id INTO v_region_id
    FROM regions
    WHERE name = p_region_name
    LIMIT 1;

    IF v_region_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Region not found.';
        SET p_region_id = NULL;
        SET p_data = NULL;
    ELSE
        SET p_region_id = v_region_id;
        SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'address', address)) INTO p_data
        FROM locations
        WHERE region_id = v_region_id;

        SET p_success = TRUE;
        SET p_message = 'Locations fetched successfully.';
    END IF;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `LoginEmployee` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `LoginEmployee`(
    IN p_email VARCHAR(255),
    IN p_current_time TIME,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_data JSON
)
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_today DATE;
    DECLARE v_clock_in_time TIME;
	DECLARE v_attendance_working_time TIME;
    DECLARE v_total_work_duration INT;
    DECLARE v_total_break_duration INT;
    DECLARE v_net_work_duration INT;

    DECLARE v_last_clock_in DATETIME;
    DECLARE v_clock_out_time TIME;
    DECLARE v_company_id INT;
    DECLARE v_department_id INT;
    DECLARE v_region_id INT;

    DECLARE v_break_id INT;
    DECLARE v_break_type_id INT;
    DECLARE v_break_start_time TIME;
    DECLARE v_break_end_time TIME;
	DECLARE v_break_time_spend TIME;
    DECLARE v_break_name VARCHAR(255);
    DECLARE v_break_duration TIME;
    DECLARE v_lunch_break BOOLEAN;
    DECLARE v_company_data JSON;
    DECLARE v_department_data JSON;
    DECLARE v_region_data JSON;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
        ROLLBACK;
    END;

    SET v_today = CURDATE();
    START TRANSACTION;

    SELECT id, company_id, department_id, region_id
    INTO v_employee_id, v_company_id, v_department_id, v_region_id
    FROM employees
    WHERE email = p_email
    LIMIT 1;

    IF v_employee_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Employee not found.';
        ROLLBACK;
    ELSE
        SELECT clock_in_time, working_time
        INTO v_clock_in_time, v_attendance_working_time
        FROM attendances
        WHERE employee_id = v_employee_id AND date = v_today
        LIMIT 1;

        IF v_clock_in_time IS NULL THEN
            SET v_total_work_duration = NULL;
            SET v_net_work_duration = NULL;
            SET v_total_break_duration = NULL;
        ELSE
            SELECT COALESCE(SUM(TIME_TO_SEC(time_spend)), 0)
            INTO v_total_break_duration
            FROM breaks
            WHERE attendance_id = (
                SELECT id 
                FROM attendances
                WHERE employee_id = v_employee_id AND date = v_today
                LIMIT 1
            );

            SET v_total_work_duration = TIME_TO_SEC(TIMEDIFF(p_current_time, v_clock_in_time));
            SET v_net_work_duration = v_total_work_duration - v_total_break_duration;
        END IF;

        SELECT COUNT(*)
        INTO v_lunch_break
        FROM breaks
        WHERE attendance_id = (
            SELECT id 
            FROM attendances
            WHERE employee_id = v_employee_id AND date = v_today
            LIMIT 1
        ) AND break_type_id = 1;

        SELECT id, break_type_id, break_start_time, break_end_time, time_spend
        INTO v_break_id, v_break_type_id, v_break_start_time, v_break_end_time, v_break_time_spend
        FROM breaks
        WHERE attendance_id = (
            SELECT id 
            FROM attendances
            WHERE employee_id = v_employee_id AND date = v_today
            LIMIT 1
        )
        ORDER BY break_start_time DESC
        LIMIT 1;

        IF v_break_id IS NOT NULL THEN
            SELECT name, duration_in_minutes
            INTO v_break_name, v_break_duration
            FROM break_types
            WHERE id = v_break_type_id
            LIMIT 1;
        ELSE
            SET v_break_name = NULL;
            SET v_break_duration = NULL;
        END IF;

        SELECT last_clock_in, clock_out_time, working_time
        INTO v_last_clock_in, v_clock_out_time, v_attendance_working_time
        FROM attendances
        WHERE employee_id = v_employee_id
        ORDER BY last_clock_in DESC
        LIMIT 1;

        SELECT JSON_OBJECT(
            'employee', JSON_OBJECT(
                'id', v_employee_id,
                'firstname', (SELECT firstname FROM employees WHERE id = v_employee_id LIMIT 1),
                'middlename', (SELECT middlename FROM employees WHERE id = v_employee_id LIMIT 1),
                'lastname', (SELECT lastname FROM employees WHERE id = v_employee_id LIMIT 1),
                'email', p_email,
                'status_type_id', (SELECT status_type_id FROM employees WHERE id = v_employee_id LIMIT 1),
				'photo', (SELECT photo FROM employees WHERE id = v_employee_id LIMIT 1),
				'designation', (SELECT designation FROM employees WHERE id = v_employee_id LIMIT 1)
            ),
            'last_attendance', JSON_OBJECT(
                'last_clock_in', v_last_clock_in,
                'working_time', v_attendance_working_time,
				'clock_out_time', v_clock_out_time
            ),
            'company', JSON_OBJECT(
                'id', v_company_id,
                'companyname', (SELECT companyname FROM company WHERE id = v_company_id LIMIT 1),
                'address', (SELECT address FROM company WHERE id = v_company_id LIMIT 1)
            ),
            'department', JSON_OBJECT(
                'name', (SELECT name FROM departments WHERE id = v_department_id LIMIT 1)
                ),
            'region',JSON_OBJECT(
                'name', (SELECT name FROM regions WHERE id = v_region_id LIMIT 1)
            ),
            'break', JSON_OBJECT(
                'break_type_id', v_break_type_id,
                'break_start_time', v_break_start_time,
                'break_end_time', v_break_end_time,
				'time_spend', v_break_time_spend
            ),
            'break_type', JSON_OBJECT(
                'name', v_break_name,
                'duration_in_minutes', v_break_duration
            ),
            'lunch_break', IF(v_lunch_break > 0, TRUE, FALSE),
            'total_duration', SEC_TO_TIME(v_total_work_duration),
            'break_duration', SEC_TO_TIME(v_total_break_duration),
            'work_duration', SEC_TO_TIME(v_net_work_duration)
        ) INTO p_data;

        COMMIT;
        SET p_success = TRUE;
        SET p_message = 'Login successful.';
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `StartBreak` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`techwork_dbadmin`@`%` PROCEDURE `StartBreak`(
    IN p_email VARCHAR(255),
    IN p_break_type_id INT,
    IN p_break_start_time TIME,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255),
    OUT p_data JSON
)
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_today DATE;
    DECLARE v_attendance_id INT;
    DECLARE v_lunch_break_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_success = FALSE;
        SET p_message = 'An unexpected error occurred.';
        ROLLBACK;
    END;

    SET v_today = CURDATE();
    START TRANSACTION;

    SELECT id INTO v_employee_id
    FROM employees
    WHERE email = p_email
    LIMIT 1;

    IF v_employee_id IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Employee not found.';
        ROLLBACK;
    ELSE
        SELECT id INTO v_attendance_id
        FROM attendances
        WHERE employee_id = v_employee_id AND date = v_today
        LIMIT 1;

        IF v_attendance_id IS NULL THEN
            SET p_success = FALSE;
            SET p_message = 'Attendance record not found.';
            ROLLBACK;
        ELSE
            IF p_break_type_id = 1 THEN
                SELECT COUNT(*) INTO v_lunch_break_exists
                FROM breaks
                WHERE attendance_id = v_attendance_id 
                AND break_type_id = p_break_type_id 
                AND date = v_today;

                IF v_lunch_break_exists > 0 THEN
                    SET p_success = FALSE;
                    SET p_message = 'Only one lunch break is allowed per day.';
                    ROLLBACK;
                END IF;
            END IF;

            IF v_lunch_break_exists = 0 THEN
                UPDATE employees
                SET status_type_id = 3
                WHERE id = v_employee_id;

                INSERT INTO breaks (attendance_id, break_type_id, date, break_start_time)
                VALUES (v_attendance_id, p_break_type_id, v_today, p_break_start_time);

                COMMIT;
                SET p_success = TRUE;
                SET p_message = 'Break started successfully.';

                SELECT JSON_OBJECT(
                    'attendance_id', v_attendance_id,
                    'break_type_id', p_break_type_id,
                    'date', v_today,
                    'break_start_time', p_break_start_time
                ) INTO p_data;
            END IF;
        END IF;
    END IF;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-06-10 19:39:15