CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetGraphDetail`(
    IN Req_time DATETIME,
    IN Live_data_flag BOOL
)
BEGIN
    DECLARE ApplicationCount INT;
    DECLARE CallCount INT;
    DECLARE LoginCount INT;
    DECLARE StartTime DATETIME;
    DECLARE EndTime DATETIME;
    DECLARE HourCounter INT DEFAULT 0;

    IF Live_data_flag THEN
        -- 15-second interval queries
        SET StartTime = Req_time;
        SET EndTime = DATE_ADD(Req_time, INTERVAL -60 SECOND);

        SELECT count(*) INTO ApplicationCount
        FROM running_application_details 
        WHERE started_on >= StartTime
        AND started_on < EndTime;

        SELECT count(*) INTO CallCount
        FROM call_details 
        WHERE call_started_on >= StartTime
        AND call_started_on < EndTime;

        SELECT count(*) INTO LoginCount
        FROM system_login_details
        WHERE login_time >= StartTime
        AND login_time < EndTime;

        SELECT ApplicationCount, CallCount, LoginCount, EndTime;

    ELSE
        -- 24-hour interval queries (hourly aggregation)
        -- Create a temporary table to store results
        CREATE TEMPORARY TABLE HourlyData (
            Hour INT,
            ApplicationCount INT,
            CallCount INT,
            LoginCount INT,
            EndTime DATETIME
            
        );

        -- Loop through each hour in the 24-hour period
        WHILE HourCounter < 24 DO
            SET StartTime = DATE_SUB(Req_time, INTERVAL 1 DAY) + INTERVAL HourCounter HOUR;
            SET EndTime = StartTime + INTERVAL 1 HOUR - INTERVAL 1 SECOND;

            INSERT INTO HourlyData (Hour, ApplicationCount, CallCount, LoginCount, EndTime)
            SELECT 
                HourCounter,
                (SELECT count(*) FROM running_application_details WHERE started_on >= StartTime AND started_on <= EndTime),
                (SELECT count(*) FROM call_details WHERE call_started_on >= StartTime AND call_started_on <= EndTime),
                (SELECT count(*) FROM system_login_details WHERE login_time >= StartTime AND login_time <= EndTime),
                StartTime AS EndTime;

            SET HourCounter = HourCounter + 1;
        END WHILE;

        -- Select results from the temporary table
        SELECT * FROM HourlyData;

        -- Drop the temporary table
        DROP TEMPORARY TABLE HourlyData;
    END IF;
END