USE dw_bi;

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_CRM_Outcome_Ticket_Campaign`$$

CREATE PROCEDURE `sp_CRM_Outcome_Ticket_Campaign`()
BEGIN

### Temporary tables to build final report table

	DROP TEMPORARY TABLE IF EXISTS CRM_Outcome_Ticket_Campaign_initial;

##

	CREATE TEMPORARY TABLE CRM_Outcome_Ticket_Campaign_initial AS 
			SELECT	  tc.Campaign_Name
						, tc.Campaign_Batch_Name
						, tc.Ticket_id
						, tc.merchant_account_id
						, tc.mid
						, tc.Company_Name
						, tc.Client_Name
						, tc.Ticket_Status
						, a.call_type		AS Activity_Type
						, a.Outcome			AS Activity_Outcome
						, a.Date_Created
			FROM		exago.vw_tickets_campaigns tc
			LEFT JOIN	exago.vw_inout_activities a
				ON				a.Ticket_Id = tc.ticket_id
				AND			a.Call_Type NOT IN ('Outbound email')
	;

##

	CREATE INDEX idx01_ticket 	ON CRM_Outcome_Ticket_Campaign_initial(Ticket_id);

####################
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Outcomes_ranks;

	CREATE TEMPORARY TABLE CRM_Outcomes_ranks
			SELECT	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, a.Date_Created
						, COUNT(b.date_created) + (CASE WHEN a.date_created IS NULL THEN 0 ELSE 1 END)  AS RANK
			FROM		CRM_Outcome_Ticket_Campaign_initial a 
				LEFT JOIN	CRM_Outcome_Ticket_Campaign_initial b
					ON				IFNULL(a.Date_Created, '1900-01-01') > IFNULL(b.Date_Created, '1900-01-01')
					AND 			a.Campaign_Name=b.Campaign_Name 
					AND 			a.Campaign_Batch_Name=b.Campaign_Batch_Name 
					AND 			a.Ticket_id=b.Ticket_id 
					AND 			a.mid=b.mid 
					AND 			a.Company_Name=b.Company_Name 
					AND 			a.Client_Name=b.Client_Name 
			GROUP BY	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, a.Date_Created
	;
	
##

	CREATE INDEX idx01_ticket 	ON CRM_Outcomes_ranks(Ticket_id);

##############
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Add_Activity_1 ;
	
	CREATE TEMPORARY TABLE CRM_Add_Activity_1 AS 
			SELECT	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, Act.Activity_Type				AS Activity_Type_1
						, Act.Activity_Outcome			AS Activity_Outcome_1
			FROM		CRM_Outcomes_ranks	a
				LEFT JOIN CRM_Outcome_Ticket_Campaign_initial Act
					ON				a.Campaign_Name=Act.Campaign_Name 
					AND 			a.Campaign_Batch_Name=Act.Campaign_Batch_Name 
					AND 			a.Ticket_id=Act.Ticket_id 
					AND 			a.mid=Act.mid 
					AND 			a.Company_Name=Act.Company_Name 
					AND 			a.Client_Name=Act.Client_Name 
					AND			a.Date_Created = Act.Date_Created
			WHERE	a.RANK = 1
	;
	
	CREATE INDEX idx01_ticket 	ON CRM_Add_Activity_1(Ticket_id);

##############
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Add_Activity_2 ;
	
	CREATE TEMPORARY TABLE CRM_Add_Activity_2 AS 
			SELECT	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, Act.Activity_Type				AS Activity_Type_2
						, Act.Activity_Outcome			AS Activity_Outcome_2
			FROM		CRM_Outcomes_ranks	a
				LEFT JOIN CRM_Outcome_Ticket_Campaign_initial Act
					ON				a.Campaign_Name=Act.Campaign_Name 
					AND 			a.Campaign_Batch_Name=Act.Campaign_Batch_Name 
					AND 			a.Ticket_id=Act.Ticket_id 
					AND 			a.mid=Act.mid 
					AND 			a.Company_Name=Act.Company_Name 
					AND 			a.Client_Name=Act.Client_Name 
					AND			a.Date_Created = Act.Date_Created
			WHERE	a.RANK = 2
	;
	
	CREATE INDEX idx01_ticket 	ON CRM_Add_Activity_2(Ticket_id);

##############
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Add_Activity_3 ;
	
	CREATE TEMPORARY TABLE CRM_Add_Activity_3 AS 
			SELECT	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, Act.Activity_Type				AS Activity_Type_3
						, Act.Activity_Outcome			AS Activity_Outcome_3
			FROM		CRM_Outcomes_ranks	a
				LEFT JOIN CRM_Outcome_Ticket_Campaign_initial Act
					ON				a.Campaign_Name=Act.Campaign_Name 
					AND 			a.Campaign_Batch_Name=Act.Campaign_Batch_Name 
					AND 			a.Ticket_id=Act.Ticket_id 
					AND 			a.mid=Act.mid 
					AND 			a.Company_Name=Act.Company_Name 
					AND 			a.Client_Name=Act.Client_Name 
					AND			a.Date_Created = Act.Date_Created
			WHERE	a.RANK = 3
	;
	
	CREATE INDEX idx01_ticket 	ON CRM_Add_Activity_3(Ticket_id);

##############
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Add_Activity_4 ;
	
	CREATE TEMPORARY TABLE CRM_Add_Activity_4 AS 
			SELECT	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, Act.Activity_Type				AS Activity_Type_4
						, Act.Activity_Outcome			AS Activity_Outcome_4
			FROM		CRM_Outcomes_ranks	a
				LEFT JOIN CRM_Outcome_Ticket_Campaign_initial Act
					ON				a.Campaign_Name=Act.Campaign_Name 
					AND 			a.Campaign_Batch_Name=Act.Campaign_Batch_Name 
					AND 			a.Ticket_id=Act.Ticket_id 
					AND 			a.mid=Act.mid 
					AND 			a.Company_Name=Act.Company_Name 
					AND 			a.Client_Name=Act.Client_Name 
					AND			a.Date_Created = Act.Date_Created
			WHERE	a.RANK = 4
	;
	
	CREATE INDEX idx01_ticket 	ON CRM_Add_Activity_4(Ticket_id);

##############
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Activities_Totals ;
	
	CREATE TEMPORARY TABLE CRM_Activities_Totals AS 
			SELECT	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.Ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
						, MAX(RANK)				AS Activities_Count
						, CASE 
								WHEN a.Ticket_status = 'CLOSED' THEN	MAX(RANK)
								ELSE												0
						  END						AS ACtivities_Count_To_Close_Ticket
			FROM		CRM_Outcomes_ranks  a
			GROUP BY	  a.Campaign_Name
						, a.Campaign_Batch_Name
						, a.Ticket_id
						, a.Ticket_status
						, a.mid
						, a.Company_Name
						, a.Client_Name
	;	
	
	CREATE INDEX idx01_ticket 	ON CRM_Activities_Totals(Ticket_id);

##############
		
	DROP TEMPORARY TABLE IF EXISTS CRM_Outcome_Ticket_Campaign_final ;
	
	CREATE TEMPORARY TABLE CRM_Outcome_Ticket_Campaign_final AS 
		SELECT	  a.Campaign_Name
					, a.Campaign_Batch_Name
					, a.Ticket_id
					, a.Ticket_Status
					, a.mid
					, a.Company_Name
					, a.Client_Name
					, CAST(NULL AS CHAR(50)) AS Activity_Type_1
					, CAST(NULL AS CHAR(50)) AS Activity_Outcome_1
					, CAST(NULL AS CHAR(50)) AS Activity_Type_2
					, CAST(NULL AS CHAR(50)) AS Activity_Outcome_2
					, CAST(NULL AS CHAR(50)) AS Activity_Type_3
					, CAST(NULL AS CHAR(50)) AS Activity_Outcome_3
					, CAST(NULL AS CHAR(50)) AS Activity_Type_4
					, CAST(NULL AS CHAR(50)) AS Activity_Outcome_4
					, CAST(NULL AS INT) AS Activities_Count
					, CAST(NULL AS INT) AS Activities_Count_To_Close_Ticket
		FROM		CRM_Outcome_Ticket_Campaign_initial a			
		GROUP BY   a.Campaign_Name
					, a.Campaign_Batch_Name
					, a.Ticket_id
					, a.Ticket_Status
					, a.mid
					, a.Company_Name
					, a.Client_Name
		;

	CREATE INDEX idx01_ticket 	ON CRM_Outcome_Ticket_Campaign_final(Ticket_id);

####################
		
	UPDATE 	CRM_Outcome_Ticket_Campaign_final	a
		LEFT JOIN	CRM_Add_Activity_1 b
			ON				a.Campaign_Name=b.Campaign_Name 
			AND 			a.Campaign_Batch_Name=b.Campaign_Batch_Name 
			AND 			a.Ticket_id=b.Ticket_id 
			AND 			a.Ticket_Status=b.Ticket_Status
			AND 			a.mid=b.mid 
			AND 			a.Company_Name=b.Company_Name 
			AND 			a.Client_Name=b.Client_Name 
	SET a.Activity_Type_1 = b.Activity_Type_1
		,a.Activity_Outcome_1 = b.Activity_Outcome_1
;		

####################
		
	UPDATE 	CRM_Outcome_Ticket_Campaign_final	a
		LEFT JOIN	CRM_Add_Activity_2 b
			ON				a.Campaign_Name=b.Campaign_Name 
			AND 			a.Campaign_Batch_Name=b.Campaign_Batch_Name 
			AND 			a.Ticket_id=b.Ticket_id 
			AND 			a.Ticket_Status=b.Ticket_Status
			AND 			a.mid=b.mid 
			AND 			a.Company_Name=b.Company_Name 
			AND 			a.Client_Name=b.Client_Name 
	SET a.Activity_Type_2 = b.Activity_Type_2
		,a.Activity_Outcome_2 = b.Activity_Outcome_2
;		

####################
		
	UPDATE 	CRM_Outcome_Ticket_Campaign_final	a
		LEFT JOIN	CRM_Add_Activity_3 b
			ON				a.Campaign_Name=b.Campaign_Name 
			AND 			a.Campaign_Batch_Name=b.Campaign_Batch_Name 
			AND 			a.Ticket_id=b.Ticket_id 
			AND 			a.Ticket_Status=b.Ticket_Status
			AND 			a.mid=b.mid 
			AND 			a.Company_Name=b.Company_Name 
			AND 			a.Client_Name=b.Client_Name 
	SET a.Activity_Type_3 = b.Activity_Type_3
		,a.Activity_Outcome_3 = b.Activity_Outcome_3
;

####################
		
	UPDATE 	CRM_Outcome_Ticket_Campaign_final a
		LEFT JOIN	CRM_Add_Activity_4 b
			ON				a.Campaign_Name=b.Campaign_Name 
			AND 			a.Campaign_Batch_Name=b.Campaign_Batch_Name 
			AND 			a.Ticket_id=b.Ticket_id 
			AND 			a.Ticket_Status=b.Ticket_Status
			AND 			a.mid=b.mid 
			AND 			a.Company_Name=b.Company_Name 
			AND 			a.Client_Name=b.Client_Name 
	SET a.Activity_Type_4 = b.Activity_Type_4
		,a.Activity_Outcome_4 = b.Activity_Outcome_4
;		

####################
		
	UPDATE 	CRM_Outcome_Ticket_Campaign_final	a
		LEFT JOIN	CRM_Activities_Totals b
			ON				a.Campaign_Name=b.Campaign_Name 
			AND 			a.Campaign_Batch_Name=b.Campaign_Batch_Name 
			AND 			a.Ticket_id=b.Ticket_id 
			AND 			a.Ticket_Status=b.Ticket_Status
			AND 			a.mid=b.mid 
			AND 			a.Company_Name=b.Company_Name 
			AND 			a.Client_Name=b.Client_Name 
	SET	  a.Activities_Count = b.Activities_Count
			, a.Activities_Count_To_Close_Ticket = b.Activities_Count_To_Close_Ticket		
;
			
####################
		
	
### Table tbl_CRM_Outcome_Ticket_Campaign
	DROP TABLE IF EXISTS dw_bi.tbl_CRM_Outcome_Ticket_Campaign;
	
	CREATE TABLE dw_bi.tbl_CRM_Outcome_Ticket_Campaign AS 
	SELECT * FROM 	CRM_Outcome_Ticket_Campaign_final
;	

	CREATE INDEX idx01_ticket ON dw_bi.tbl_CRM_Outcome_Ticket_Campaign(Ticket_id);

END$$

DELIMITER ;

# CALL sp_CRM_Outcome_Ticket_Campaign();

#select * from dw_bi.tbl_CRM_Outcome_Ticket_Campaign 