CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetUserProfileData`(
    IN p_emp_code VARCHAR(255),
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_req_date TIMESTAMP,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);

    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'work_logged_on';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'empcode'; -- Use alias name
    ELSEIF p_sort_column = 'TotalActive' THEN
        SET p_sort_column = 'totalactive';
    ELSEIF p_sort_column = 'TotalIdle' THEN
        SET p_sort_column = 'totalidle';
    ELSEIF p_sort_column = 'WorkLoggedOn' THEN
        SET p_sort_column = 'work_logged_on';
    ELSEIF p_sort_column = 'WorkEndedOn' THEN
        SET p_sort_column = 'work_ended_on';
    ELSEIF p_sort_column = 'TotalSleep' THEN
        SET p_sort_column = 'totalsleep';
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'createdon';
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SELECT 
        COUNT(DISTINCT emp_code) INTO v_total_records
    FROM 
        employee_working_time
    WHERE 
        p_emp_code IS NULL OR emp_code = p_emp_code;

    -- Calculate the total number of pages
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Create temporary table to store filtered results
    DROP TABLE IF EXISTS temp_filtered_results;
    
    CREATE TEMPORARY TABLE temp_filtered_results AS
    SELECT 
        ewt.emp_code as empcode,
        SUM(ewt.total_active) AS totalactive,
        SUM(ewt.total_idle) AS totalidle,
        MIN(ewt.work_logged_on) AS work_logged_on,
        MAX(ewt.work_logged_on) AS work_ended_on,
        SUM(ewt.total_sleep) AS totalsleep,
        MAX(ewt.work_logged_on) AS last_work_logged_on,
        MAX(ewt.created_on) AS createdon
    FROM 
        employee_working_time ewt
    WHERE
        (p_emp_code IS NULL OR lower(ewt.emp_code) LIKE CONCAT('%', p_emp_code, '%'))
        AND (p_req_date IS NULL OR ewt.work_logged_on >= p_req_date)
    GROUP BY ewt.emp_code;

    -- Calculate the total number of records after filtering
    SELECT COUNT(*) INTO v_total_records FROM temp_filtered_results;

    -- Calculate the total number of pages after filtering
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results
    IF NOT p_ignore_paging THEN
        SET @query = CONCAT('SELECT empcode, totalactive, totalidle, work_logged_on, work_ended_on, totalsleep, last_work_logged_on, createdon, ', 
                            v_total_records, ' AS total_records, ', 
                            v_total_pages, ' AS total_pages FROM temp_filtered_results', 
                            v_sort_query, v_limit_query);
    ELSE
        SET @query = CONCAT('SELECT empcode, totalactive, totalidle, work_logged_on, work_ended_on, totalsleep, last_work_logged_on, createdon, ', 
                            v_total_records, ' AS total_records, ', 
                            v_total_pages, ' AS total_pages FROM temp_filtered_results', 
                            v_sort_query);
    END IF;

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Drop the temporary table
    DROP TEMPORARY TABLE IF EXISTS temp_filtered_results;
END