/*
	Description:
		Bot Usage Report
		see https://jira.genesys.com/browse/SPCHSTRM-3398

	Notes:
		 3 possible outputs are required.

		 For new customers, could the number of hits on NLU hits
		 Chat only.

		 For existing customer, show the call duration.
		 Voice and Chat 

	version : 04

	Date: 29/04/2021

*/

--------------------------------------------------------------------------------
---Static data only--- DO NOT AMEND--START----
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';
declare
    ChanCategoryChat number(10) := 3;
    ChanVoice number(10) := 14;

    sqlVersion varchar2(200);

    chat_str varchar2(100) := 'Chat calls';
    voice_str varchar2(100) := 'Voice calls';

    ChatBotTurns_str varchar2(100) := 'ChatBotTurns';
    ChatBotInteraction_str varchar2(100) := 'ChatBotInteraction';

    callType varchar(100) := 'Unknown call type';
    reportType varchar(100) := 'Unknown Report';

    TotalCalls NUMBER(19) := 0;
    
    TotalDurationInSec NUMBER(20) := 0;

    TotalCallsBloc6Usage NUMBER(19) := 0;
    TotalBloc6Usage NUMBER(19) := 0;
--------------------------------------------------------------------------------
------- AMEND AS REQUIRED START----
    start_dateX DATE := TO_DATE('01/01/2021', 'DD/MM/YYYY');
    end_dateX date := TO_DATE('30/12/2021', 'DD/MM/YYYY');
    companyIDX int := 2;
    siteIdX int := 0;

--  report for new customers, nlu is used.
--  default setting if enabled.
--  If set to 1 then will ignore second option. 
--  chat only 
    ChatBotTurns boolean := true;

---report for existing customers, duration is used.
-- voice or chat
    -- ChatBotInteraction boolean := true;   -- not needed
    IsChatCall number(1) := 0;
-------- AMEND AS REQUIRED END----
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
begin
    SELECT banner INTO sqlVersion 
    FROM v$version
    WHERE banner LIKE 'Oracle%';

    dbms_output.put_line('Version:' || sqlVersion);
    dbms_output.put_line('Start Date:' ||  to_char(start_dateX));
    dbms_output.put_line('End Date:' ||  to_char(end_dateX));
    dbms_output.put_line('companyID:' || to_char(companyIDX));
    dbms_output.put_line('siteId:' || siteIdX);
    
    if ChatBotTurns = true then
        IsChatCall := 1;		-- always chat
        -- ChatBotInteraction := false;
        dbms_output.put_line('CustomerType:' || ChatBotTurns_str);        
    else
        -- ChatBotInteraction := true;
        dbms_output.put_line('CustomerType:' ||ChatBotInteraction_str);        
    end if;    
    
    if IsChatCall = 1 then
        dbms_output.put_line('CallType:' || chat_str);
	else   
        dbms_output.put_line('CallType:' || voice_str);
	end if;    
--------------------------------------------------------------------------------
--- count of turns needed, not duration.

    if ChatBotTurns = true
    -- New Customers.
    then
        TotalCalls:= 0;
       
        select
            count(DISTINCT call_id)
        INTO
            TotalCalls        
		FROM VW_CALL_STEPS                 -- both historical and day call steps
        WHERE 
			(company_id = companyIDX) 
			and (channel_category = ChanCategoryChat)    -- always chat
            and (call_start_date BETWEEN start_dateX AND end_dateX);

       dbms_output.put_line('Total Possible Calls:' ||
                                to_char(TotalCalls));

        select 
            count(distinct call_id)
            ,count(block_type)
        INTO
            TotalCallsBloc6Usage
            ,TotalBloc6Usage
        FROM 
            VW_CALL_STEPS                  -- both historical and day call steps
        where 
            (company_id = companyIDX )
            and (channel_category = ChanCategoryChat)    -- always chat
            and (block_type = 6)
            and (block_detail like '%0002%')						
            and (call_start_date BETWEEN start_dateX AND end_dateX);
       
        dbms_output.put_line(
            'TotalCallsBloc6Usage:' || to_char(TotalCallsBloc6Usage));
        dbms_output.put_line(
            'TotalBloc6Usage:' ||  to_char(TotalBloc6Usage));
                                
    else 
    -- this report is for existing customers.
        TotalCalls := 0;
        TotalDurationInSec := 0;
    
        select 
            count(call_id)
            ,sum(call_duration)
        INTO
            TotalCalls
            ,TotalDurationInSec
        FROM
            VW_CALLS							-- both historical and day calls
        WHERE
            (company_id = companyIDX) 
            and (   ( (IsChatCall = 1) and 
                      (Start_channel_category = ChanCategoryChat)  )
                 or ( (IsChatCall = 0) and 
                      (Start_channel = ChanVoice) ) )
            and (call_start_date BETWEEN start_dateX AND end_dateX)
            and (call_duration > 0)
            and (call_id in
            (
                select 
                    call_id
                FROM VW_CALL_STEPS   -- both historical and day call steps - check block 6 is used.
                where 
                    (block_type = 6)
                    and (block_detail like '%0002%')
            ));
         
        dbms_output.put_line('TotalCount:' || to_char(TotalCalls));
        dbms_output.put_line(
            'TotalDurationInSec:' || to_char(TotalDurationInSec));
        dbms_output.put_line(
            'TotalDurationInHrsMinSec: ' || 
            'Hrs:' || to_char( floor(TotalDurationInSec / 3600)) ||
            ' Mins:' || to_char(floor(MOD(TotalDurationInSec, 3600) / 60)) || 
            ' Secs:' || to_char(MOD(TotalDurationInSec, 60)) );
    end if;
 end; 
