CREATE PROCEDURE [dbo].[spEnquiryConversation]
(	@EnquiryId BIGINT,
	@BrandGroupId TINYINT = 1
)	
AS
BEGIN

	--Declare @EnquiryId BIGINT =111,
	--Declare	@BrandGroupId TINYINT = 1
	SELECT ResponseId,EnquiryId,CustomerContactId,EmployeeId, [Name],ActionOn,Attachments,[Description],IsReply,ProfileImageUrl,UserImage,MessageType,[Receivers] FROM
	(
		select ECON.ConversationId[ResponseId], ECON.EnquiryId, E.CustomerContactId,ECON.EmployeeId,
		IIF(ECON.IsReply=1,(E.CustomerTitle +' '+ E.CustomerFirstName+' '+E.CustomerLastName),(emp.FirstName +' '+emp.LastName)) Name, ECON.ActionOn [ActionOn],
		IIF(ECON.IsReply=1,ECON.AttachmentName,
		STUFF((select '|'+ EA.AttachmentName
			from ConversationAttachment AS EA With(NoLock) where EA.EnquiryConversationId=ECON.ConversationId FOR XML PATH('')),1,1,'')) AS [Attachments],
		STUFF((select ','+ ECR.Email
			from WBEnquiryConversationReceivers AS ECR With(NoLock) where ECR.ConversationId=ECON.ConversationId FOR XML PATH('')),1,1,'') AS [Receivers] ,
		CONVERT(varchar(max),ECON.Description,2) [Description],Cast(1 as bit) IsReply,CC.ProfileImageUrl,'' UserImage,CAST(1 As tinyint) MessageType
		from EnquiryConversation ECON With(NoLock)
		Inner JOIN Enquiry E With(NoLock) ON ECON.EnquiryId =E.EnquiryId
		LEFT JOIN CustomerContact CC With(NoLock) ON E.CustomerContactId = CC.ContactId
		LEFT JOIN Employee emp  With(NoLock) ON ECON.EmployeeId = emp.EmployeeId
		Where ECON.IsG2BTemplate=0 AND ECON.IsTemplate=0 AND E.BrandId in (Select BrandId from Brand With(NoLock) where BrandGroupId=@BrandGroupId)	

		Union All

		select EI.SMSId [ResponseId], E.EnquiryId,E.CustomerContactId, '' AS EmployeeId, (CC.CustomerTitle + ' '+ CC.FirstName +' '+CC.LastName) Name, EI.AddDate,
		'' AS [Attachments],'' AS [Receivers], CONCAT(EI.Message,' ', '<span class="full-width text-right bold">[Received]</span>')  [Description],	Cast(1 as bit) IsReply,	CC.ProfileImageUrl,	'' UserImage, CAST(2 As tinyint) MessageType
		 from  EnquiryIncomingSMS EI With(NoLock)
		 LEFT JOIN Enquiry E With(NoLock) ON EI.EnquiryId =E.EnquiryId		
		 LEFT JOIN CustomerContact CC With(NoLock) ON E.CustomerContactId = CC.ContactId
		 Where E.BrandId in (Select BrandId from Brand With(NoLock) where BrandGroupId=@BrandGroupId)

		 Union All

		 select ES.SMSId [ResponseId], ES.EnquiryId,E.CustomerContactId,ES.EmployeeId,(EMP.FirstName +' '+EMP.LastName) Name, ES.AddDate [ActionOn],'' AS [Attachments],'' AS [Receivers],
		 CONCAT(ES.Message,' ', '<span class="full-width text-right bold">['+ES.MessageStatus+']</span>') [Description],Cast(0 as bit) IsReply,CC.ProfileImageUrl,	EMP.UserImage,CAST(2 As tinyint) MessageType
		 from  EnquirySMS ES With(NoLock)
		 LEFT JOIN Enquiry E With(NoLock) ON ES.EnquiryId =E.EnquiryId
		 LEFT JOIN CustomerContact CC With(NoLock) ON E.CustomerContactId = CC.ContactId
		 LEFT JOIN Employee EMP With(NoLock) ON EMP.EmployeeId = ES.EmployeeId
		 Where (ES.MessageTypeId not in(5,11,15) and IsCTUser =0) AND E.BrandId in (Select BrandId from Brand where BrandGroupId=@BrandGroupId)


	) tbl WHERE EnquiryId =@EnquiryId order by tbl.ActionOn asc

END

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




CREATE TABLE [dbo].[WBEnquiryConversationReceivers]
(
Id BIGINT NOT NULL IDENTITY(1,1),
ConversationId BIGINT NOT NULL,
AddDate DATETIME2 NOT NULL,
Email NVARCHAR(200) NOT NULL,
IPAddress NVARCHAR(15) NOT NULL,
CONSTRAINT PK_WBEnquiryConversationReceivers PRIMARY KEY CLUSTERED(Id ASC),
CONSTRAINT FK_WBEnquiryConversationReceivers_EnquiryConversation FOREIGN KEY (ConversationId) REFERENCES dbo.EnquiryConversation(ConversationId)
)