BEGIN TRY
	IF COL_LENGTH('App.ApplicationReferrals', 'ApplicationLicenceID') IS NULL
	BEGIN
		ALTER TABLE [App].[ApplicationReferrals] ADD [ApplicationLicenceID] BIGINT
	END

	ALTER TABLE [App].[ApplicationReferrals]  WITH CHECK ADD  CONSTRAINT [FK_ApplicationReferrals_ApplicationLicences] FOREIGN KEY([ApplicationLicenceID])
	REFERENCES [App].[ApplicationLicences] ([ApplicationLicenceID])

	ALTER TABLE [App].[ApplicationReferrals] CHECK CONSTRAINT [FK_ApplicationReferrals_ApplicationLicences]

	/****** Object:  Index [IX_ApplicationObjections_ApplicationLicenceID]    Script Date: 26/08/2024 19:12:21 ******/
	CREATE NONCLUSTERED INDEX [IX_ApplicationReferrals_ApplicationLicenceID] ON [App].[ApplicationReferrals]
	(
		[ApplicationLicenceID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
END TRY
BEGIN CATCH
END CATCH
GO

CREATE OR ALTER FUNCTION [App].[Fn_ApplicationStep12Checks]
(
	@ApplicationDetailID BIGINT,
	@UserID BIGINT
)
RETURNS @Outcomes TABLE(Title VARCHAR(500) NOT NULL, IsSuccessfull BIT NOT NULL, OutcomeDetails VARCHAR(2000) NULL, RequiredForSubmitting BIT NOT NULL)
AS
BEGIN
	
	DECLARE @ApplicationRequestTypeID SMALLINT
	DECLARE @DivisionTypeID SMALLINT
	DECLARE @ProvinceID SMALLINT

	SELECT @ApplicationRequestTypeID = Ard.ApplicationRequestTypeID, @DivisionTypeID = Ard.OfficeLocationDivisionTypeID, @ProvinceID = Ard.ApplicationProvinceID
	FROM App.ApplicationDetails Ard 
		INNER JOIN App.ApplicationApplicantDetails Aad ON Ard.ApplicationDetailID = Aad.ApplicationDetailID
	WHERE Ard.ApplicationDetailID = @ApplicationDetailID 

	/****************************************************************************
	Applicant Inspection details
	****************************************************************************/	
	IF EXISTS (SELECT TOP 1 1
			FROM App.ApplicationReferrals 
			WHERE ApplicationDetailID = @ApplicationDetailID AND IsCancelled = 0 AND 
			(
				DateReferralSent IS NULL OR 
				DateFeedbackReceived IS NULL OR 			
				ISNULL(FeedbackDocID,-1) = -1 OR 
				ISNULL(ReferralSentTo,'') = '' OR 
				ISNULL(ReferralAddress,'') = ''
			)
		) 
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the referral details section complete?', 0, '',1)			
		END
		ELSE
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the referral details section complete?', 1, '',1)			
		END
				
	RETURN
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationSubmitToNextStepChecks]
	@ApplicationDetailID BIGINT,
	@TaskID BIGINT,
	@UserID BIGINT
AS
-- exec [Wf].[Pr_WorkflowInstanceSubmitToNextStepChecks] @WorkflowInstanceID=10567,@TaskID=20841,@UserID=1001
BEGIN
	SET NOCOUNT ON;

	DECLARE @Outcomes TABLE([Title] [VARCHAR](500) NOT NULL, [IsSuccessfull] [BIT] NOT NULL,[OutcomeDetails] [VARCHAR](2000) NULL, RequiredForSubmitting BIT NOT NULL)
	DECLARE @DoChecks BIT = 1
	DECLARE @CurrentWorkflowStep FLOAT

	IF ISNULL(@TaskID,-1) <> -1
	BEGIN
		IF NOT EXISTS(SELECT * FROM Wf.WorkflowInstanceTasks WHERE WorkflowInstanceTaskID = @TaskID AND AssignedToID = @UserID AND WorkflowInstanceTaskStatusID = 1 /*in progress*/)
		BEGIN			
			SET @DoChecks = 0
		END
		ELSE
        BEGIN
			SELECT @CurrentWorkflowStep = Wts.Step
			FROM Wf.WorkflowInstanceTasks Wit 
				INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wit .WorkflowTemplateStepID
			WHERE Wit.WorkflowInstanceTaskID = @TaskID
		END
	END
	ELSE
	BEGIN
		SELECT @CurrentWorkflowStep = Wts.Step
		FROM App.ApplicationDetails Ard
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID			
			INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wi.WorkflowTemplateStepID
		WHERE Ard.ApplicationDetailID = @ApplicationDetailID
	END

	IF @DoChecks = 1
	BEGIN
		IF @CurrentWorkflowStep = 1.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep1Checks(@ApplicationDetailID, @UserID)
		END
		ELSE IF @CurrentWorkflowStep = 2.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep2Checks(@ApplicationDetailID, @UserID)
		END	
		ELSE IF @CurrentWorkflowStep = 3.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep3Checks(@ApplicationDetailID, @UserID)
		END	
		ELSE IF @CurrentWorkflowStep = 11.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep11Checks(@ApplicationDetailID, @UserID)
		END	
		ELSE IF @CurrentWorkflowStep = 12.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep12Checks(@ApplicationDetailID, @UserID)
		END	
	END
    
	SELECT * 
	FROM @Outcomes	
END
GO