
ALTER PROCEDURE [App].[Pr_Purification_InsertAllowedNewLicence]
	@ApplicationAllowedNewLicenceID BIGINT,
	@MemberName VARCHAR(255),
	@IdNumberOrBusinessRegistrationNumber VARCHAR(50),
	@AssociationID BIGINT,
	@RegistrationNumber VARCHAR(50),	
	@ApplicationAllowedNewLicenceTypeID SMALLINT,
	@OperatingLicenceNumber VARCHAR(20),
	@ApplicationReferenceNumber VARCHAR(20),
	@UserID BIGINT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @IsUsed BIT = 0
	DECLARE @UsedOn VARCHAR(1000) 
	DECLARE @ErrorReason VARCHAR(2000)

	SET @IdNumberOrBusinessRegistrationNumber = UPPER(REPLACE(@IdNumberOrBusinessRegistrationNumber,' ',''))
	SET @RegistrationNumber = UPPER(REPLACE(@RegistrationNumber,' ',''))
	
	/*
	Check to make sure the id/registration number has not been added already
	*/	
	IF @ApplicationAllowedNewLicenceID = -1 AND EXISTS(
		SELECT * 
		FROM App.ApplicationAllowedNewLicences
		WHERE RegistrationNumber LIKE @RegistrationNumber
			AND IdNumberOrBusinessRegistrationNumber LIKE @IdNumberOrBusinessRegistrationNumber
			AND ApplicationAllowedNewLicenceID <> @ApplicationAllowedNewLicenceID
		)
	BEGIN
		RAISERROR('This id number/registration number has already been added to the list', 16, 1)
		RETURN
	END

	/*
	Check to make sure the id/registration number is not being used on an in progress application
	*/	
	IF @ApplicationAllowedNewLicenceID = -1 AND EXISTS(
				SELECT *
				FROM App.ApplicationRequestDetails Ard
					INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN App.ApplicationVehicleDetails Avd ON Avd.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN Wf.WorkflowInstances Wi ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
				WHERE Ard.ApplicationRequestTypeID = 1 /*New operating licence*/
					AND  REPLACE(Aad.IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumberOrBusinessRegistrationNumber,' ','')
					AND REPLACE(Avd.VehicleRegistrationNumber,' ','') LIKE REPLACE(@RegistrationNumber,' ','')
					AND Wi.WorkflowInstanceStatusID IN (1 /*In Progress*/)
		)
	BEGIN		
		SELECT @UsedOn = COALESCE(@UsedOn + ',', '') + Wi.ReferenceNumber
		FROM App.ApplicationRequestDetails Ard
					INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN App.ApplicationVehicleDetails Avd ON Avd.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN Wf.WorkflowInstances Wi ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
				WHERE Ard.ApplicationRequestTypeID = 1 /*New operating licence*/
					AND  REPLACE(Aad.IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumberOrBusinessRegistrationNumber,' ','')
					AND REPLACE(Avd.VehicleRegistrationNumber,' ','') LIKE REPLACE(@RegistrationNumber,' ','')
					AND Wi.WorkflowInstanceStatusID IN (1 /*In Progress*/)

		SET @ErrorReason = 'This id number/registration number has already been used to start a new application: ' + ISNULL(@UsedOn,'')

		RAISERROR(@ErrorReason, 16, 1)
		RETURN
	END

	/*
	Check to make sure the id/registration number is not being used on a current licence
	*/	
	IF @ApplicationAllowedNewLicenceTypeID IN (1,2,4) /*Phase 2, 3 and 3b application*/  AND @ApplicationAllowedNewLicenceID = -1 AND EXISTS(
				SELECT *
				FROM Verified.Licences L
					INNER JOIN App.ApplicationRequestDetails Ard ON L.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
					INNER JOIN Verified.Vehicles V ON L.VehicleID = V.VehicleID
				WHERE Ard.ApplicationRequestTypeID = 1 /*New operating licence*/
					AND  REPLACE(M.IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumberOrBusinessRegistrationNumber,' ','')
					AND REPLACE(V.RegistrationNumber,' ','') LIKE REPLACE(@RegistrationNumber,' ','')					
		)
	BEGIN
		SELECT @UsedOn = COALESCE(@UsedOn + ',', '') + L.OperatingLicenceNumber
		FROM Verified.Licences L
			INNER JOIN App.ApplicationRequestDetails Ard ON L.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
			INNER JOIN Verified.Vehicles V ON L.VehicleID = V.VehicleID
		WHERE Ard.ApplicationRequestTypeID = 1 /*New operating licence*/
			AND  REPLACE(M.IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumberOrBusinessRegistrationNumber,' ','')
			AND REPLACE(V.RegistrationNumber,' ','') LIKE REPLACE(@RegistrationNumber,' ','')		
			           
		SET @ErrorReason = 'This id number/registration number has already been used on a licence: ' + ISNULL(@UsedOn,'')

		RAISERROR(@ErrorReason, 16, 1)
		RETURN
	END

	--disabling obsolete check
	--IF @UserID <> 1014 --Bypassing verified vehicle check for Mark Lehrer as per PCR70
	--BEGIN
	--	/*
	--	Check to make sure the registration number has not been used already
	--	*/	
	--	IF EXISTS(
	--		SELECT * 
	--		FROM Verified.Vehicles			
	--		WHERE UPPER(REPLACE(RegistrationNumber,' ','')) LIKE @RegistrationNumber				
	--		) AND @ApplicationAllowedNewLicenceTypeID IN (1,2,4)
	--	BEGIN
	--		RAISERROR('This vehicle already exists in the verified vehicles table', 16, 1)
	--		RETURN
	--	END
	--END
	
	IF @ApplicationAllowedNewLicenceTypeID = 3 --Application started before bypass list was implemented
	BEGIN
		IF NOT EXISTS(
			SELECT * 
			FROM App.Vw_ApplicationRequestDetails
			WHERE ApplicationNumber LIKE @ApplicationReferenceNumber
				AND ApplicationRequestTypeID = 1 /*new app*/)
		BEGIN		
			RAISERROR('Invalid application reference number', 16, 1)
			RETURN
		END
		ELSE
		BEGIN
			DECLARE @ApplicationRequestDetailID BIGINT
			DECLARE @LicenceID BIGINT

			SELECT @ApplicationRequestDetailID = ApplicationRequestDetailID
			FROM App.Vw_ApplicationRequestDetails
			WHERE ApplicationNumber LIKE @ApplicationReferenceNumber
				AND ApplicationRequestTypeID = 1 /*new app*/

			SELECT @LicenceID = LicenceID
			FROM Verified.Licences
			WHERE ApplicationRequestDetailID = @ApplicationRequestDetailID

			IF ISNULL(@LicenceID,-1) = -1
			BEGIN
				IF NOT EXISTS(
					SELECT * 
					FROM App.Vw_ApplicationRequestDetails Ard
						INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
						INNER JOIN App.ApplicationVehicleDetails Avd ON	Avd.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					WHERE Aad.VerifiedAssociationID = @AssociationID 
						AND Aad.IdNumberOrBusinessRegistrationNo = @IdNumberOrBusinessRegistrationNumber 
						AND UPPER(REPLACE(Avd.VehicleRegistrationNumber, ' ','')) LIKE @RegistrationNumber)
				BEGIN					
					RAISERROR('The application''s association/member/vehicle registration number does not match the specified details', 16, 1)
					RETURN                    
				END
				ELSE
				BEGIN
					SET @IsUsed = 1
				END
			END
			ELSE
			BEGIN
				IF NOT EXISTS(
					SELECT * 
					FROM Verified.Licences L
						INNER JOIN Verified.Members M ON M.MemberID = L.MemberID
						INNER JOIN Verified.AssociationMembers Am ON Am.MemberID = M.MemberID
						INNER JOIN Verified.Vehicles V ON V.VehicleID = L.VehicleID
					WHERE Am.AssociationID = @AssociationID 
						AND M.IdNumberOrBusinessRegistrationNo = @IdNumberOrBusinessRegistrationNumber 
						AND UPPER(REPLACE(V.RegistrationNumber, ' ','')) LIKE @RegistrationNumber)
				BEGIN					
					RAISERROR('The licence''s association/member/vehicle registration number does not match the specified details', 16, 1)
					RETURN                    
				END
				ELSE
				BEGIN
					SET @IsUsed = 1
				END
			END
		END
	END

	IF @ApplicationAllowedNewLicenceID = -1
	BEGIN
		INSERT INTO App.ApplicationAllowedNewLicences(MemberName,IdNumberOrBusinessRegistrationNumber,AssociationID,RegistrationNumber,CreatedDate,CreatedByID,IsUsed, ApplicationAllowedNewLicenceTypeID, OperatingLicenceNumber , ApplicationReferenceNumber)
		VALUES (@MemberName, @IdNumberOrBusinessRegistrationNumber, @AssociationID, @RegistrationNumber, GETDATE(), @UserID,@IsUsed, @ApplicationAllowedNewLicenceTypeID, @OperatingLicenceNumber , @ApplicationReferenceNumber)
	END
	ELSE
    BEGIN
		DECLARE @CurrentApplicationRequestDetailID BIGINT
		DECLARE @CurrentLicenceRegistrationNumber VARCHAR(255)
		DECLARE @CurrentLicenceIDNumber VARCHAR(255)

		SELECT @CurrentApplicationRequestDetailID = 
			(	
				SELECT ApplicationRequestDetailID 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID
			)

		IF ISNULL(@CurrentApplicationRequestDetailID, -1) <> -1
		BEGIN			
			SELECT @CurrentLicenceRegistrationNumber = V.RegistrationNumber, @CurrentLicenceIDNumber = M.IDNumberOrBusinessRegistrationNo
			FROM Verified.Licences L
				INNER JOIN Verified.Vehicles V ON V.VehicleID = L.VehicleID
				INNER JOIN Verified.Members M ON M.MemberID = L.MemberID
			WHERE L.ApplicationRequestDetailID = @CurrentApplicationRequestDetailID
		END

		--new constraint 20250208
		--updates only allowed if the record has never been linked to an application
		--or
		--if the record has been linked and teh new id/registration number matches the linked licence (for TBA applications)
		IF ISNULL(@CurrentApplicationRequestDetailID,-1) = -1 
			OR (@CurrentLicenceRegistrationNumber = @RegistrationNumber AND @CurrentLicenceIDNumber = @IdNumberOrBusinessRegistrationNumber)
		BEGIN
			--New auditing 20250208 as per SUP0007230  
			DECLARE @OldData NVARCHAR(MAX), @NewData NVARCHAR(MAX);

			SELECT @OldData = STRING_AGG(ColumnData, ', ')
			FROM (
				SELECT 'MemberName: ' + COALESCE(CAST(MemberName AS NVARCHAR(MAX)), 'NULL') AS ColumnData 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'IdNumberOrBusinessRegistrationNumber: ' + COALESCE(CAST(IdNumberOrBusinessRegistrationNumber AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'AssociationID: ' + COALESCE(CAST(AssociationID AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'RegistrationNumber: ' + COALESCE(CAST(RegistrationNumber AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'ApplicationAllowedNewLicenceTypeID: ' + COALESCE(CAST(ApplicationAllowedNewLicenceTypeID AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'OperatingLicenceNumber: ' + COALESCE(CAST(OperatingLicenceNumber AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'ApplicationReferenceNumber: ' + COALESCE(CAST(ApplicationReferenceNumber AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
				UNION ALL
				SELECT 'IsUsed: ' + COALESCE(CAST(IsUsed AS NVARCHAR(MAX)), 'NULL') 
				FROM App.ApplicationAllowedNewLicences 
				WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID 
			) AS OldDataList;

			SET @OldData = 'Data before update: ' + ISNULL(@OldData,'')

			INSERT INTO Audit.History(SourceID,ActionID,Description,IsCritical,IsException,EntityID,ItemID,CreatedByID,CreatedDate)
			VALUES(2 /*Application*/,2 /*Updated*/,@OldData,0,0,1025 /*ApplicationAllowedNewLicences*/, @ApplicationAllowedNewLicenceID, @UserID, GETDATE())

			UPDATE App.ApplicationAllowedNewLicences
			SET
				MemberName = @MemberName,
				IdNumberOrBusinessRegistrationNumber = @IdNumberOrBusinessRegistrationNumber,
				AssociationID = @AssociationID,
				RegistrationNumber = @RegistrationNumber,			
				ApplicationAllowedNewLicenceTypeID = @ApplicationAllowedNewLicenceTypeID, 
				OperatingLicenceNumber = @OperatingLicenceNumber, 
				ApplicationReferenceNumber = @ApplicationReferenceNumber,
				IsUsed = @IsUsed
			WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID				
		END
		ELSE
		BEGIN
			DECLARE @ErrorMessage VARCHAR(255) = 'The record has already been linked to an application and can''t be updated.'

			SET @ErrorMessage = @ErrorMessage + 'Current Licence Registration Number=' + ISNULL(@CurrentLicenceRegistrationNumber,'') + ', Current Licence ID Number=' + ISNULL(@CurrentLicenceIDNumber,'')
			--New constraint 20250208 as per SUP0007230  
			RAISERROR(@ErrorMessage, 16, 1)
			RETURN 
		END
	END
END