CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetRunningApplications`(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Sort column
    IN p_sort_type VARCHAR(4)      -- 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 = 'started_on';
    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 = 'ApplicationName' THEN
        SET p_sort_column = 'application_name';  -- Map "ApplicationName" to the correct column name "application_name"
    ELSEIF p_sort_column = 'StartedOn' THEN
        SET p_sort_column = 'started_on';  -- Map "StartedOn" to the correct column name "started_on"
    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"
    ELSEIF p_sort_column = 'Description' THEN
        SET p_sort_column = 'description';  -- Map "Description" to the correct column name "description"
    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 running_application_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_started_on IS NOT NULL, CONCAT('AND DATE(started_on) >= DATE("', p_started_on, '") '), ''),
                        IF(p_ended_on IS NOT NULL, CONCAT('AND DATE(started_on) <= DATE("', p_ended_on, '") '), ''));
    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 records with pagination if paging is not ignored
    SET @query = CONCAT('SELECT id, emp_code, application_name, started_on, created_on, updated_on, description, ',
                        IF(p_ended_on IS NOT NULL, CONCAT('"', p_ended_on, '" AS EndedOn, '), 'NULL AS EndedOn, '),
                        v_total_records, ' AS total_records, ', 
                        v_total_pages, ' AS total_pages FROM running_application_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_started_on IS NOT NULL, CONCAT('AND DATE(started_on) >= DATE("', p_started_on, '") '), ''),
                        IF(p_ended_on IS NOT NULL, CONCAT('AND DATE(started_on) <= DATE("', p_ended_on, '") '), ''),
                        v_sort_query, 
                        v_limit_query);

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