--DROP PROCEDURE AGENT_TEST_PRC(INT);
--DROP TABLE FRIST_LEVEL_TEMP;
--DROP TABLE SECOND_LEVEL_TEMP;
--DROP TABLE THIRD_LEVEL_TEMP;
--DROP TABLE FINAL_RESULT_TEMP_TABLE;
-----------------------------------------------------------------------
CREATE FUNCTION AGENT_TEST_PRC(agent_id INT DEFAULT 0)
RETURNING INT,DATETIME,DATETIME,DATETIME,INT,INT;
DEFINE 1_agentid AS INT;
DEFINE 1_eventdatetime AS DATETIME;
DEFINE 1_agentLoginTime AS DATETIME;
DEFINE 1_agentLogoutTime AS DATETIME;
DEFINE 1_TOTAL_NOT_READY_TIME AS INT;
DEFINE 1_TOTAL_READY_TIME AS INT;
--first level query
select 
     agentid
    ,eventdatetime
    ,eventtype
    ,(select MIN(CAST(A2.eventdatetime AS DATETIME HOUR TO SECOND))
        from AgentStateDetail A2 
       where A2.agentid = A1.agentid
         and A2.eventtype = 1
         --and DATE(A2.eventdatetime) = DATE(A1.eventdatetime)
     ) as agentLoginTime
    ,(select MAX(CAST(A2.eventdatetime AS DATETIME HOUR TO SECOND))
        from AgentStateDetail A2 
       where A2.agentid = A1.agentid
         and A2.eventtype = 7
         --and DATE(A2.eventdatetime) = DATE(A1.eventdatetime)
      ) as agentLogoutTime
       ,(SELECT MIN(eventdatetime) 
           FROM AgentStateDetail A2 
          WHERE A2.agentid = A1.agentid 
            AND A2.eventdatetime > A1.eventdatetime
            AND DATE(A2.eventdatetime) = DATE(A1.eventdatetime) 
            AND A2.eventtype = 3
            AND (A1.eventtype = 2 OR A1.eventtype = 1)) NextReadyDateTime
       ,(SELECT MIN(eventdatetime) 
           FROM AgentStateDetail A2 
          WHERE A2.agentid = A1.agentid 
            AND A2.eventdatetime > A1.eventdatetime
            AND DATE(A2.eventdatetime) = DATE(A1.eventdatetime) 
            AND A2.eventtype = 2
            AND (A1.eventtype = 3 OR A1.eventtype = 7)) NextNotReadyDateTime
from AgentStateDetail A1 
WHERE A1.agentid = 4
ORDER BY A1.eventdatetime ASC INTO FRIST_LEVEL_TEMP;
-- end the first level 

-- second level query 

select TBL.*
      , (CASE WHEN TBL.eventtype = 1 or TBL.eventtype = 2
              THEN TBL.NextReadyDateTime - TBL.eventdatetime
              ELSE NULL
           END  
        ) NotReadyTime
      , (CASE WHEN TBL.eventtype = 3
              THEN TBL.NextNotReadyDateTime - TBL.eventdatetime
              ELSE NULL
           END  
        ) ReadyTime
FROM FRIST_LEVEL_TEMP TBL INTO SECOND_LEVEL_TEMP;
--end secnd level
SELECT agentid,DATE(eventdatetime),agentLoginTime,agentLogoutTime FROM SECOND_LEVEL_TEMP GROUP BY agentid,eventdatetime,agentLoginTime,agentLogoutTime INTO THIRD_LEVEL_TEMP;
CREATE TABLE IF NOT EXISTS FINAL_RESULT_TEMP_TABLE AS
FOREACH
  SELECT THIRD_LEVEL_TEMP.*
     ,(SELECT SUM(ST.NotReadyTime) 
         FROM SECOND_LEVEL_TEMP ST 
        WHERE ST.agentid = THIRD_LEVEL_TEMP.agentid 
          AND ST.eventdatetime=DATE(THIRD_LEVEL_TEMP.eventdatetime)) TOTAL_NOT_READY_TIME
     ,(SELECT SUM(ST.ReadyTime) 
         FROM SECOND_LEVEL_TEMP ST 
        WHERE ST.agentid = THIRD_LEVEL_TEMP.agentid 
          AND ST.eventdatetime=DATE(THIRD_LEVEL_TEMP.eventdatetime)) TOTAL_READY_TIME
  INTO 1_agentid,1_eventdatetime,1_agentLoginTime,1_agentLogoutTime,1_TOTAL_NOT_READY_TIME,1_TOTAL_READY_TIME
  FROM THIRD_LEVEL_TEMP
  RETURN 1_agentid,1_eventdatetime,1_agentLoginTime,1_agentLogoutTime,1_TOTAL_NOT_READY_TIME,1_TOTAL_READY_TIME
  WITH RESUME;
END FOREACH
END FUNCTION;

--EXECUTE PROCEDURE AGENT_TEST_PRC (4);