CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetCallsChart`(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP,
    IN p_show VARCHAR(100),
    IN p_device VARCHAR(100),
    IN p_chartType VARCHAR(100),
    IN p_type VARCHAR(100)
)
BEGIN
	
		-- Check if p_started_on and p_ended_on are the same
    IF (p_started_on IS NOT NULL AND p_ended_on IS NOT NULL AND p_started_on = p_ended_on) THEN
        -- Add one day to p_ended_on
        SET p_ended_on = DATE_ADD(p_ended_on, INTERVAL 1 DAY);
    END IF;
	
    IF p_type = 'Calls' THEN
        -- Calculate the total number of records grouped by date
        SELECT 
            COUNT(*) AS CallsCount,
            CAST(call_started_on AS DATE) AS StartedOn, 
            NULL AS callwith,
            rad.emp_code AS EmpCode
        FROM 
            call_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.call_started_on >= p_started_on)
            AND (p_ended_on IS NULL OR rad.call_started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(rad.emp_code) LIKE CONCAT('%', lower(p_emp_code), '%'))
        GROUP BY 
            CAST(call_started_on AS DATE), rad.emp_code;

    ELSEIF p_type = 'Calls(Top 10)' THEN
        -- Calculate the total number of records grouped by employee code
        SELECT 
            COUNT(rad.emp_code) AS CallsCount,
            rad.emp_code AS EmpCode,
            NULL AS StartedOn, NULL AS callwith
        FROM 
            call_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.call_started_on >= p_started_on)
            AND (p_ended_on IS NULL OR rad.call_started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(rad.emp_code) LIKE CONCAT('%', lower(p_emp_code), '%'))
        GROUP BY 
            rad.emp_code
        ORDER BY 
            CallsCount DESC 
        LIMIT 10;
    END IF;
END