USE [LegitimateNational]
GO
/****** Object:  UserDefinedFunction [App].[Fn_LicenceIssueStep2Checks]    Script Date: 2025/08/04 19:29:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER   FUNCTION [App].[Fn_LicenceIssueStep2Checks]
(
	@LicenceIssueDetailID 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
	DECLARE @LicenceID BIGINT
	DECLARE @MemberID BIGINT
	DECLARE @VehicleID BIGINT

	SELECT @ApplicationRequestTypeID = Ard.ApplicationRequestTypeID, 
		@DivisionTypeID = L.DivisionTypeID, 
		@ProvinceID = L.ProvinceID,
		@LicenceID = L.LicenceID,
		@MemberID = L.MemberID,
		@VehicleID = L.VehicleID
	FROM App.LicenceIssueDetails Lid
		INNER JOIN App.ApplicationDetails Ard ON Ard.ApplicationDetailID = Lid.ApplicationDetailID
		INNER JOIN Verified.Licences L ON Lid.VerifiedLicenceID = L.LicenceID
	WHERE Lid.LicenceIssueDetailID = @LicenceIssueDetailID

	/****************************************************************************
	Member details
	****************************************************************************/	
	IF EXISTS
		(
			SELECT * 
			FROM Verified.Members
			WHERE MemberID = @MemberID AND 
			(
			(
				IDTypeID <> 3 /*Founding Statement*/ AND IDTypeID <> 6 /*Certificate of Incorporation*/  AND IDTypeID <> 11 /*Taxi Association Registration Number*/
				AND ISNULL(FirstNames,'') = ''
			) OR 
			ISNULL(SurnameOrCompanyName,'') = '' OR 
			(
				ISNULL(TelCell,'') = '' AND 
				ISNULL(TelPhone,'') = ''
			)
			OR 
			ISNULL(PostalAddress,'') = '' OR 
			ISNULL(PostalAddressCode,'') = '' OR 
			ISNULL(PhysicalAddress,'') = '' OR 
			ISNULL(PhysicalAddressCode,'') = '' OR 
			ISNULL(SARSIncomeTaxNumber,'') = '' 
			)
		) 
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the operator details section complete?', 0, '',1)			
		END
		ELSE
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the operator details section complete?', 1, '',1)			
		END
		
	/****************************************************************************
	Vehicle details validation
	****************************************************************************/		
	-- Check 1: Vehicle ID exists
	IF ISNULL(@VehicleID,-1) = -1
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Is the licence vehicle details section complete?', 0, '',1)	
	END
	ELSE
	BEGIN
		-- Check 2: All required vehicle details are complete
		DECLARE @VehicleDetailsComplete BIT = 1
		DECLARE @ServiceTypeValid BIT = 1
		
		-- Validate vehicle details completeness
		IF EXISTS
		(
			SELECT 1 
			FROM Verified.Vehicles V
			INNER JOIN Verified.Licences L ON L.VehicleID = V.VehicleID
			INNER JOIN Verified.LicenceServiceDetails LSD ON LSD.LicenceID = L.LicenceID
			WHERE V.VehicleID = @VehicleID AND 
			(
				(ISNULL(V.VehicleMakeID,-1) = -1) OR
				(ISNULL(V.VehicleTypeID, -1) = -1) OR
				(ISNULL(V.CarryingCapacityID, -1) = -1) OR
				(ISNULL(V.VIN, '') = '') OR
				(ISNULL(V.EngineNumber, '') = '') OR
				(ISNULL(V.YearOfManufacture, '') = '') OR
				(ISNULL(V.RegistrationNumber, '') = '')  OR 
				(ISNULL(V.SeatedCapacity, 0) = 0) OR
				(ISNULL(V.StandingCapacity, -1) = -1) OR
				(ISNULL(V.GrossMass, -1) = -1) OR
				(ISNULL(V.Tare, -1) = -1) OR
				(ISNULL(V.RoadworthyCertificateDocID, -1) = -1) OR
				(((ISNULL(V.ProfessionalDriversPermitDocID, -1) = -1) AND (LSD.ServiceTypeID = 5 OR LSD.ServiceTypeID = 6)) AND @DivisionTypeID != 2) OR
				(ISNULL(V.CertificateOfRegistrationDocID, -1) = -1)
			)
		)
		BEGIN
			SET @VehicleDetailsComplete = 0
		END
		
		-- Check 3: Service type is valid for the selected vehicle type
		IF EXISTS
		(
			SELECT 1 
			FROM Verified.Licences L
			INNER JOIN Verified.Vehicles V ON V.VehicleID = L.VehicleID
			INNER JOIN Verified.LicenceServiceDetails LSD ON LSD.LicenceID = L.LicenceID
			WHERE L.LicenceID = @LicenceID AND
			ServiceTypeID NOT IN 
			(
				SELECT ServiceTypeID FROM Md.ServiceVehicleTypes WHERE VehicleTypeID = V.VehicleTypeID
			)
		)
		BEGIN
			SET @ServiceTypeValid = 0
		END
		
		-- Insert outcomes based on validation results
		IF @VehicleDetailsComplete = 0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the licence vehicle details section complete?', 0, '',1)
		END
		
		IF @ServiceTypeValid = 0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the licence service valid for the selected vehicle type?', 0, '',1)
		END
		
		-- Only insert success outcome if both checks pass
		IF @VehicleDetailsComplete = 1 AND @ServiceTypeValid = 1
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the licence vehicle details section complete?', 1, '',1)
		END
	END
	
	/****************************************************************************
	Exceptions
	****************************************************************************/
	INSERT INTO @Outcomes(Title,IsSuccessfull,OutcomeDetails,RequiredForSubmitting)
	SELECT 'Exception check',0,Exception,1
	FROM Verified.LicenceExceptions
	WHERE LicenceID = @LicenceID

			
	RETURN
END
