CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetSystemLoginDetails`(
    IN p_emp_code VARCHAR(255),
    IN p_login_time DATETIME,
    IN p_logout_time DATETIME,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    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 = 'login_time';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'LoginTime' THEN
        SET p_sort_column = 'login_time';  -- Map "LoginTime" to the correct column name "login_time"
    ELSEIF p_sort_column = 'LogoutTime' THEN
        SET p_sort_column = 'logout_time';  -- Map "LogoutTime" to the correct column name "logout_time"
    ELSEIF p_sort_column = 'SystemStatus' THEN
        SET p_sort_column = 'system_status';  -- Map "SystemStatus" to the correct column name "system_status"
    ELSEIF p_sort_column = 'IpAddress' THEN
        SET p_sort_column = 'ip_address';  -- Map "IpAddress" to the correct column name "ip_address"
    ELSEIF p_sort_column = 'ComputerName' THEN
        SET p_sort_column = 'computer_name';  -- Map "ComputerName" to the correct column name "computer_name"
    ELSEIF p_sort_column = 'AppVersion' THEN
        SET p_sort_column = 'app_version';  -- Map "AppVersion" to the correct column name "app_version"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    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
    SET @query = CONCAT(
        'SELECT COUNT(*) INTO @v_total_records FROM system_login_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_login_time IS NOT NULL, CONCAT('AND DATE(login_time) >= DATE("', p_login_time, '") '), ''),
        IF(p_logout_time IS NOT NULL, CONCAT('AND DATE(login_time) <= DATE("', p_logout_time, '") '), '')
    );
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Retrieve the count result
    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results along with total records and total pages
    SET @query = CONCAT(
        'SELECT id, emp_code, login_time, logout_time, system_status, ip_address, computer_name, app_version, created_on, updated_on, ', 
        v_total_records, ' AS total_records, ', 
        v_total_pages, ' AS total_pages ',
        'FROM system_login_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_login_time IS NOT NULL, CONCAT('AND DATE(login_time) >= DATE("', p_login_time, '") '), ''),
        IF(p_logout_time IS NOT NULL, CONCAT('AND DATE(login_time) <= DATE("', p_logout_time, '") '), ''),
        v_sort_query,
        v_limit_query
    );

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