-- Enable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-- Create indexes
If IndexProperty(Object_Id('tblClaimItems'), 'ClaimIdValidityTo', 'IndexID') Is Null
	create nonclustered index ClaimIdValidityTo on tblClaimItems (ClaimID, ValidityTo)

If IndexProperty(Object_Id('tblClaimServices'), 'ClaimIdValidityTo', 'IndexID') Is Null
	create nonclustered index ClaimIdValidityTo on tblClaimServices (ClaimID, ValidityTo)

If IndexProperty(Object_Id('tblClaim'), 'ClaimValidityTo', 'IndexID') Is Null
	create nonclustered index ClaimValidityTo on tblClaim (ValidityTo) INCLUDE (ClaimCode, ClaimStatus, ClaimUUID)

-- Step 2: Write Text File
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'C:\Dragos\projects\openIMIS AI\data\nepal\FHIR\NepalClaimResponse.json', 2, 1

-- starting the json
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, '{ "entry": ['

DECLARE @ItemSequence INT;

DECLARE @JSON nvarchar(MAX) = '';
DECLARE @PartialJSON nvarchar(MAX);

DECLARE @ItemJSON nvarchar(MAX);
DECLARE @AllItemsJSON nvarchar(MAX) = '';
 
DECLARE @Currency varchar(10) = 'USD'

DECLARE @ClaimID INT
DECLARE @ClaimStatus INT
DECLARE @ClaimItemStatus INT
DECLARE @ReviewStatus INT

DECLARE @ClaimUUID varchar(36)
DECLARE @FeedbackUUID varchar(36)
DECLARE @ItemUUID varchar(36)
DECLARE @InsureeUUID varchar(36)
DECLARE @HFUUID varchar(36)
DECLARE @ClaimAdminUUID varchar(36)
DECLARE @ICD0 varchar(36)
DECLARE @ICD1 varchar(36)

DECLARE @ClaimCode varchar(30)
DECLARE @QtyProvided varchar(30)
DECLARE @QtyApproved varchar(30)
DECLARE @PriceAsked varchar(30)
DECLARE @PriceAdjusted varchar(30)
DECLARE @PriceApproved varchar(30)
DECLARE @PriceValuated varchar(30)
DECLARE @RejectedReason varchar(30)

DECLARE @Claimed varchar(30)
DECLARE @Approved varchar(30)
DECLARE @Reinsured varchar(30)
DECLARE @Valuated varchar(30)

DECLARE @ItemType varchar(30)
DECLARE @VisitType varchar(30)
DECLARE @DateFrom DATETIME
DECLARE @DateTo DATETIME

DECLARE CUR_CLAIM CURSOR FAST_FORWARD FOR
    SELECT C.ClaimUUID, F.FeedbackUUID, REPLACE(C.ClaimCode, '''', '') AS ClaimCode, C.ClaimID, C.ClaimStatus, I.InsureeUUID, HF.HfUUID, C.ReviewStatus, C.Claimed, C.Approved, C.Reinsured, C.Valuated, C.VisitType,
	C.DateFrom, C.DateTo, CA.ClaimAdminUUID, ICD0.ICDCode, ICD1.ICDCode
    FROM tblClaim C
	LEFT JOIN tblFeedback F ON C.ClaimID=F.ClaimID
	LEFT JOIN tblInsuree I ON C.InsureeID=I.InsureeID
	LEFT JOIN tblHF HF ON C.HFID=HF.HfID
	LEFT JOIN tblClaimAdmin CA ON C.ClaimAdminId=CA.ClaimAdminId
	LEFT JOIN tblICDCodes ICD0 ON C.ICDID=ICD0.ICDID
	LEFT JOIN tblICDCodes ICD1 ON C.ICDID1=ICD1.ICDID
	WHERE C.ValidityTo IS NULL
    ORDER BY C.ClaimId;

OPEN CUR_CLAIM
FETCH NEXT FROM CUR_CLAIM INTO @ClaimUUID, @FeedbackUUID, @ClaimCode, @ClaimID, @ClaimStatus, @InsureeUUID, @HFUUID, @ReviewStatus, @Claimed, @Approved, @Reinsured, 
	@Valuated, @VisitType, @DateFrom, @DateTo, @ClaimAdminUUID, @ICD0, @ICD1
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @PartialJSON = '{"fullUrl": "http://127.0.0.1:8000/api_fhir_r4/ClaimResponse/' + @ClaimUUID + '","resource": {"resourceType": "ClaimResponse",' +
		CASE WHEN @FeedbackUUID is not NULL THEN '"communicationRequest": [{"reference": "CommunicationRequest/' + @FeedbackUUID + '"}],' ELSE '' END + 
		'"extension": [{"url": "billablePeriod","valuePeriod": {"end": "'+FORMAT(@DateFrom, 'yyyy-MM-dd', 'en-GB')+'","start": "'+FORMAT(@DateTo, 'yyyy-MM-dd', 'en-GB')+'"}},' +
        '{"url": "icd_0","valueReference": {"reference": "Condition/'+@ICD0+'"}}'+
		CASE WHEN @ICD1 is not NULL THEN ',{"url": "icd_1","valueReference": {"reference": "Condition/'+@ICD1+'"}}' ELSE '' END +
		'],"enterer": {"reference": "Practitioner/'+@ClaimAdminUUID+'"}, "created": "'+FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd', 'en-GB')+'", "id": "'+@ClaimUUID+'",' +
		'"identifier": [{"type": {"coding": [{"code": "UUID","system": "https://hl7.org/fhir/valueset-identifier-type.html"}]},"use": "usual","value": "'+@ClaimUUID+'"},' +
					   '{"type": {"coding": [{"code": "MR","system": "https://hl7.org/fhir/valueset-identifier-type.html"}]},"use": "usual","value": "'+@ClaimCode+'"}],' +
        '"insurer": {"reference": "Organization/openIMIS"},' +
        '"item": ['

		SET @ItemSequence = 0

		DECLARE CUR_ITEM CURSOR FAST_FORWARD FOR
			SELECT QtyProvided, QtyApproved, PriceAsked, PriceAdjusted, PriceApproved, PriceValuated, ClaimServiceStatus, RejectionReason, ServiceUUID, 'ActivityDefinition'
				FROM tblClaimServices CS
				LEFT JOIN tblServices S ON CS.ServiceID=S.ServiceID
				WHERE CS.ClaimID=@ClaimID
				AND CS.ValidityTo is NULL 
			UNION
			SELECT QtyProvided, QtyApproved, PriceAsked, PriceAdjusted, PriceApproved, PriceValuated, ClaimItemStatus, RejectionReason, ItemUUID, 'Medication'
				FROM tblClaimItems CI
				LEFT JOIN tblItems I ON CI.ItemID=I.ItemID
				WHERE CI.ClaimID=@ClaimID
				AND CI.ValidityTo is NULL 
					

		OPEN CUR_ITEM
		FETCH NEXT FROM CUR_ITEM INTO @QtyProvided, @QtyApproved, @PriceAsked, @PriceAdjusted, @PriceApproved, @PriceValuated, @ClaimItemStatus, @RejectedReason, @ItemUUID, @ItemType
 
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			SET @ItemSequence = @ItemSequence + 1

			SET @ItemJSON = '{"adjudication": [{"amount": {"currency": "'+@Currency+'","value": '+@PriceAsked+'},"category": {"coding": [{"code": "2"}],"text": "'+
						CASE WHEN @ClaimItemStatus=1 THEN 'entered' ELSE 'rejected' END+'"},"reason": {"coding": [{"code": "'+@RejectedReason+'"}]},"value": '+@QtyProvided+'}' +
					CASE WHEN @ClaimItemStatus=1 THEN
						CASE WHEN @ClaimStatus>2 THEN 
							',{"amount": {"currency": "'+@Currency+'","value": ' + CASE WHEN @PriceAdjusted IS NULL THEN @PriceAsked ELSE @PriceAdjusted END+'},' +
							'"category": {"coding": [{"code": "4"}],"text": "checked"},"reason": {"coding": [{"code": "'+@RejectedReason+'"}]},"value": '+@QtyProvided+'}' +
							CASE WHEN @ClaimStatus>4 THEN
								',{"amount": {"currency": "'+@Currency+'","value": ' + CASE WHEN @PriceApproved IS NULL THEN CASE WHEN @PriceAdjusted IS NULL THEN @PriceAsked ELSE @PriceAdjusted END ELSE @PriceApproved END +
								'},"category": {"coding": [{"code": "8"}],"text": "processed"},"reason": {"coding": [{"code": "'+@RejectedReason+'"}]},"value": '+CASE WHEN @QtyApproved IS NULL THEN @QtyProvided ELSE @QtyApproved END + '}' +
								CASE WHEN @ClaimStatus>8 THEN
									',{"amount": {"currency": "'+@Currency+'","value": '+ 
									CASE WHEN @PriceValuated IS NULL THEN 
										CASE WHEN @PriceApproved IS NULL THEN 
											CASE WHEN @PriceAdjusted IS NULL THEN @PriceAsked 
											ELSE @PriceAdjusted 
											END 
										ELSE @PriceApproved 
										END 
									ELSE @PriceValuated 
									END
									+'},"category": {"coding": [{"code": "16"}],"text": "valuated"},"reason": {"coding": [{"code": "'+@RejectedReason+'"}]}}'
								ELSE '' END
							ELSE '' END
						ELSE '' END
					ELSE '' END +
					'],"extension": [{"url": "'+@ItemType+'","valueReference": {"reference": "'+@ItemType+'/'+@ItemUUID+'"}}],"itemSequence": '+CONVERT(varchar, @ItemSequence)+'},'
                    
	
				SET @AllItemsJSON = @AllItemsJSON + @ItemJSON

				FETCH NEXT FROM CUR_ITEM INTO @QtyProvided, @QtyApproved, @PriceAsked, @PriceAdjusted, @PriceApproved, @PriceValuated, @ClaimItemStatus, @RejectedReason, @ItemUUID, @ItemType

				SET @AllItemsJSON = CASE WHEN @@FETCH_STATUS = 0 THEN @AllItemsJSON ELSE LEFT(@AllItemsJSON, LEN(@AllItemsJSON) - 1) END

			END
			CLOSE CUR_ITEM
			DEALLOCATE CUR_ITEM

			SET @JSON = @PartialJSON + @AllItemsJSON

            SET @PartialJSON = '],"outcome": "'+CASE WHEN @ClaimStatus=1 THEN 'rejected' WHEN @ClaimStatus=2 THEN 'entered' WHEN @ClaimStatus=4 THEN 'checked' WHEN @ClaimStatus=8 THEN 'processed' ELSE 'valuated' END + '",' +
                '"patient": {"reference": "Patient/'+@InsureeUUID+'"},"request": {"reference": "Claim/'+@ClaimUUID+'"},"requestor": {"reference": "HealthcareService/'+@HFUUID+'"},' +
                '"status": "'+ CASE WHEN @ReviewStatus=1 THEN 'Idle' WHEN @ReviewStatus=2 THEN 'Not Selected' WHEN @ReviewStatus=4 THEN 'Selected for Review' WHEN @ReviewStatus=8 THEN 'Reviewed' ELSE 'ByPassed' END +'",' +
                '"total": [{"amount": {"currency": "'+@Currency+'","value": '+@Claimed+'},"category": {"coding": [{"code": "submitted","display": "Submitted Amount","system": "http://terminology.hl7.org/CodeSystem/adjudication.html"}],"text": "Claimed"}},' +
					'{"amount": {"currency": "'+@Currency+'","value": ' + CASE WHEN @Approved IS NULL THEN @Claimed ELSE @Approved END + '},"category": {"coding": [{"code": "benefit","display": "Benefit Amount","system": "http://terminology.hl7.org/CodeSystem/adjudication.html"}],"text": "Approved"}}],'+
                '"type": {"text": "'+@VisitType+'"},"use": "claim"}},'
	
	SET @JSON = @JSON + @PartialJSON
	
	FETCH NEXT FROM CUR_CLAIM INTO @ClaimUUID, @FeedbackUUID, @ClaimCode, @ClaimID, @ClaimStatus, @InsureeUUID, @HFUUID, @ReviewStatus, 
		@Claimed, @Approved, @Reinsured, @Valuated, @VisitType, @DateFrom, @DateTo, @ClaimAdminUUID, @ICD0, @ICD1
		
	SET @JSON = CASE WHEN @@FETCH_STATUS = 0 THEN @JSON ELSE LEFT(@JSON, LEN(@JSON) - 1) END

	EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @JSON

	SET @JSON = ''
	SET @AllItemsJSON = ''


END
CLOSE CUR_CLAIM
DEALLOCATE CUR_CLAIM

SET @JSON = ']}'

-- ending the json
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @JSON


EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
GO

-- Step 3: Disable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO