/*
	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, and had used block 6.
		 Voice and Chat 

	version : 03

	Date: 29/04/2021

*/

-------------------------------------------------------------------------------------------------------
---Static data only--- DO NOT AMEND--START----
---dbo.calls
declare @VW_CALLS_start_channel_category_chat int = 3
declare @VW_CALLS_start_channel_voice int = 14

---dbo.call_steps
declare @VW_CALL_STEPS_channel_category_chat int = 3
declare @VW_CALLS_STEPS_channel_voice int = 14

declare @chat_str varchar(50) = 'Chat calls'
declare @voice_str varchar(50) = 'Voice calls'

declare @ChatBotTurns_str varchar(50) = 'ChatBotTurns'
declare @ChatBotInteraction_str varchar(50) = 'ChatBotInteraction'

declare @sqlVers numeric(4,2)
set @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)

---Static data only--- DO NOT AMEND--END----
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
-- AMEND AS REQUIRED START----
declare @start_dateX as date = '2021-01-03 00:00:00.0'
declare @end_dateX as date = '2021-12-31 23:59:59.999'
declare @companyIDX int = 2
declare @siteIdX int = null

-- report for new customers, nlu is used.
-- default setting if enabled.
-- If set to 1 then will ignore second option. 
-- chat only 
declare @ChatBotTurns bit = 0

---report for existing customers, duration is used.
-- voice or chat
declare @ChatBotInteraction bit = 1
declare @IsChatCall bit = 1

---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

---------Display options-----------

if @ChatBotTurns = 1
begin
 set @IsChatCall = 1		-- always 1.
 set @ChatBotInteraction = 0
end
else
begin
 set @ChatBotInteraction = 1
end

select 
	@sqlVers as SQLVersion,
	@start_dateX as start_dateX,
	@end_dateX as end_dateX,
	@companyIDX as companyIDX,
	@siteIdX as siteIdX,
	case
		when @ChatBotTurns = 1 then @ChatBotTurns_str
		when @ChatBotInteraction = 1 then @ChatBotInteraction_str
	end as CustomerType,
	case
		when @IsChatCall = 1 then @chat_str
		when @IsChatCall = 0 then @voice_str
	end as CallCategoryType

declare @callType varchar(50) = 'Unknown call type';
if @IsChatCall = 1
	set @callType = 'Chat calls'
else if @IsChatCall = 0
	set @callType = 'Voice calls'

declare @reportType varchar(50) = 'Unknown Report';
if @ChatBotTurns = 1
	set @reportType = 'ChatBotTurns'
else if @ChatBotInteraction = 1
	set @reportType = 'ChatBotInteraction'

--------------------------------------------------------------------------------------------------------------
--- count of turns needed, not duration.
if @ChatBotTurns = 1
begin

	declare @approxCalls bigint = 0;

	--if @sqlVers >= 15.00
	--begin
	--select @approxCalls = APPROX_COUNT_DISTINCT(call_id)
	--	FROM VW_CALL_STEPS                      -- both historical and day call steps
	--	where 
	--		(company_id = @companyIDX) 
	--		and (channel_category = @VW_CALL_STEPS_channel_category_chat)    -- always chat
	--		and (call_start_date >= @start_dateX)
	--		and (call_start_date <= @end_dateX)
	--end
	--else
	-- begin
		select @approxCalls = count(distinct call_id)
			FROM VW_CALL_STEPS                      -- both historical and day call steps
			where 
				(company_id = @companyIDX) 
				and (channel_category = @VW_CALL_STEPS_channel_category_chat)    -- always chat
				and (call_start_date >= @start_dateX)
				and (call_start_date <= @end_dateX)
	--end


	select 
		@reportType as ReportType,
	    @callType as 'CallType',
		@approxCalls as 'Total Possible Calls',
		count(distinct call_id) as TotalCallsBloc6Usage,
		count(block_type) as TotalBloc6Usage,
		min(call_start_date) as min_date, 
		max(call_start_date) as max_date
	FROM VW_CALL_STEPS                      -- both historical and day call steps
	where 
		(company_id = @companyIDX )
		and (channel_category = @VW_CALL_STEPS_channel_category_chat)    -- always chat 3
		and (block_type = 6) 
		and (block_detail like '%0002%')						
        and (call_start_date >= @start_dateX)
        and (call_start_date <= @end_dateX)

end
else --if @ChatBotInteraction = 1, this report is for existing customers.
begin

-- if google asr/ tts, then include as channel will be 14 - good.
-- as non nlu this will be included.

-- chat do we want VW_CALL_STEPS check block_detail = 	0002, @VW_CALLS_start_channel_category_chat = 3   -- confirmed.
-- voice  ?? Jonanthan to do?? do we want VW_CALL_STEPS check block_detail = 	0002, @VW_CALLS_start_channel_category_chat = 14

-- For new customers the same rules apply.
-- chat


	select 
		@reportType as ReportType,
	    @callType as 'CallType',
		count(call_id) as TotalCalls, 
		sum(cast(call_duration as bigint)) as TotalDurationInSec,
			cast((sum(cast(call_duration as bigint)) / 3600) as varchar(10)) + ' : ' + 
			cast(((sum(cast(call_duration as bigint)) % 3600) / 60) as varchar(10)) + ' : ' + 
			cast((sum(cast(call_duration as bigint)) % 60) as varchar(10)) 	as TotalDurationInHrsMinSec,
		min(call_start_date) as min_date, 
		max(call_start_date) as max_date
	FROM VW_CALLS							-- both historical and day calls
	where 
		company_id = @companyIDX 
		and (   (@IsChatCall = 1 and (Start_channel_category = @VW_CALLS_start_channel_category_chat))		-- 3
		     or (@IsChatCall = 0 and (Start_channel = @VW_CALLS_start_channel_voice)))						-- 14
        and (call_start_date >= @start_dateX)
        and (call_start_date <= @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%')
		));

end
--------------------------------------------------------------------------------------------------------------