-- App.Pr_ConversionApplicationCreate_updated: full procedure for type 63 (Conversion of Metered Taxi to E-hailing).
-- Reference / baseline aligned with: docs/Convert to E-hailing functionality investigation/01_App.Pr_ConversionApplicationCreate.sql
--
-- Changes vs that baseline (section 4c):
--   - After child seeding and before workflow start, inserts App.ApplicationLicences for the NEW application
--     (ApplicationLicenceReferenceNumber = ApplicationNumber + '-1'), VehicleTypeID = 1 (Motor Car).
--   - Inserts App.ApplicationLicenceServiceDetails with ServiceTypeID = 9 (E-Hailing); description from Md.ServiceTypes.
--   - Copies App.ApplicationLicenceVehicleDetails from source (VehicleTypeID = 1 on copy).
--   - Copies App.ApplicationLicenceRouteDetails from source; ServiceTypeID 10 -> 9; RelatedApplicationLicenceRouteDetailID = NULL.
--
USE [LegitimateNational]
GO

IF OBJECT_ID('App.Pr_ConversionApplicationCreate', 'P') IS NOT NULL
    DROP PROCEDURE App.Pr_ConversionApplicationCreate;
GO

/****** Object:  StoredProcedure [App].[Pr_ConversionApplicationCreate] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [App].[Pr_ConversionApplicationCreate]
(
    @SourceApplicationDetailID    BIGINT,
    @SourceApplicationLicenceID   BIGINT,
    @PlatformProviderID           BIGINT,
    @UserID                       BIGINT
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ApplicationDetailID BIGINT;
    DECLARE @DivisionTypeID SMALLINT;
    DECLARE @ApplicationProvinceID SMALLINT;
    DECLARE @ApplicationRegionID BIGINT;
    DECLARE @OfficeLocationID SMALLINT;
    DECLARE @SourceApplicationID SMALLINT;
    DECLARE @GazetteTypeID SMALLINT;

    ----------------------------------------------------------------------
    -- 1) Validate source application & licence
    ----------------------------------------------------------------------
    IF NOT EXISTS (SELECT 1 FROM App.ApplicationDetails WHERE ApplicationDetailID = @SourceApplicationDetailID)
    BEGIN
        RAISERROR('#EXPOSE_ERROR#Source application not found.', 16, 1);
        RETURN;
    END;

    IF NOT EXISTS (
        SELECT 1
        FROM App.ApplicationLicences
        WHERE ApplicationLicenceID = @SourceApplicationLicenceID
          AND ApplicationDetailID = @SourceApplicationDetailID)
    BEGIN
        RAISERROR('#EXPOSE_ERROR#Source licence not found for this application.', 16, 1);
        RETURN;
    END;

    SELECT
        @DivisionTypeID        = OfficeLocationDivisionTypeID,
        @ApplicationProvinceID = ApplicationProvinceID,
        @ApplicationRegionID   = ApplicationRegionID,
        @OfficeLocationID      = OfficeLocationID,
        @SourceApplicationID   = SourceApplicationID,
        @GazetteTypeID         = GazetteTypeID
    FROM App.ApplicationDetails
    WHERE ApplicationDetailID = @SourceApplicationDetailID;

    ----------------------------------------------------------------------
    -- 2) Create base ApplicationDetails row (type 63)
    ----------------------------------------------------------------------
    INSERT INTO App.ApplicationDetails
    (
        ApplicationRequestTypeID,
        SourceApplicationID,
        OfficeLocationID,
        OfficeLocationDivisionTypeID,
        ApplicationProvinceID,
        ApplicationRegionID,
        CreatedByID,
        CreatedDate,
        UpdatedByID,
        UpdatedDate,
        LicenceCount,
        IsBulkApplication,
        IsCancelled,
        ApplicationStatusID,
        ApplicationSubStatusID,
        GazetteTypeID,
        IsReferralRequired,
        IsGazettingRequired,
        IsInspectionRequired,
        IsAdjudicationRequired,
        CopiedFromApplicationDetailID,
        CopiedFromApplicationLicenceID
    )
    SELECT
        63 /*Conversion of Metered Taxi to E-hailing*/,
        @SourceApplicationID,
        @OfficeLocationID,
        @DivisionTypeID,
        @ApplicationProvinceID,
        @ApplicationRegionID,
        @UserID,
        GETDATE(),
        @UserID,
        GETDATE(),
        1,          -- single licence per conversion application
        0,          -- IsBulkApplication
        0,          -- IsCancelled
        2,          -- Data Capture
        2,          -- Awaiting Data Capture
        ISNULL(@GazetteTypeID, 1), -- default to Internal Gazette if not set
        1,          -- IsReferralRequired
        1,          -- IsGazettingRequired
        1,          -- IsInspectionRequired
        1,          -- IsAdjudicationRequired
        @SourceApplicationDetailID,
        @SourceApplicationLicenceID;

    SET @ApplicationDetailID = SCOPE_IDENTITY();

    ----------------------------------------------------------------------
    -- 3) Generate ApplicationNumber (pattern as in Pr_ApplicationCreate)
    ----------------------------------------------------------------------
    DECLARE @ApplicationNumberTable TABLE(ApplicationNumber VARCHAR(20));
    DECLARE @ApplicationNumber VARCHAR(20);

    ;WITH Cte AS
    (
        SELECT TOP (1) ReferenceNumber, IsUsed
        FROM System.ReferenceNumbers WITH (ROWLOCK, READPAST)
        WHERE
            DivisionTypeID = @DivisionTypeID
            AND ProvinceID = @ApplicationProvinceID
            AND EntityID = 1000 /*ApplicationDetails*/
            AND IsUsed = 0
        ORDER BY ReferenceNumberID
    )
    UPDATE Cte
    SET IsUsed = 1
    OUTPUT Inserted.ReferenceNumber INTO @ApplicationNumberTable;

    SELECT @ApplicationNumber = ApplicationNumber
    FROM @ApplicationNumberTable;

    UPDATE App.ApplicationDetails
    SET ApplicationNumber = @ApplicationNumber
    WHERE ApplicationDetailID = @ApplicationDetailID;

    ----------------------------------------------------------------------
    -- 4) Seed minimal child records and workflow so the console & views
    --    behave the same way as a normal application created via
    --    App.Pr_ApplicationCreate.
    ----------------------------------------------------------------------

    -- Copy applicant from the source application so that the
    -- ApplicationApplicantDetails inner-join in App.Vw_ApplicationDetails_Light
    -- succeeds. If, for some reason, the source has no applicant row,
    -- this INSERT will simply do nothing and the view will still hide
    -- the conversion app (which matches existing behaviour).
    INSERT INTO App.ApplicationApplicantDetails
    (
        ApplicationDetailID,
        SurnameOrCompanyName,
        FirstNames,
        IDTypeID,
        IDTypeDescription,
        IDNumberOrBusinessRegistrationNo,
        PostalAddress,
        PostalAddressCode,
        PhysicalAddress,
        PhysicalAddressCode,
        TelPhone,
        TelCell,
        TelFax,
        Email,
        SARSIncomeTaxNumber,
        TradeName,
        TypeOfBusiness,
        BusinessTypeID,
        VerifiedMemberID,
        ProvinceID,
        RegionID,
        IsApplicantIndividual
    )
    SELECT
        @ApplicationDetailID,
        SurnameOrCompanyName,
        FirstNames,
        IDTypeID,
        IDTypeDescription,
        IDNumberOrBusinessRegistrationNo,
        PostalAddress,
        PostalAddressCode,
        PhysicalAddress,
        PhysicalAddressCode,
        TelPhone,
        TelCell,
        TelFax,
        Email,
        SARSIncomeTaxNumber,
        TradeName,
        TypeOfBusiness,
        BusinessTypeID,
        VerifiedMemberID,
        ProvinceID,
        RegionID,
        IsApplicantIndividual
    FROM App.ApplicationApplicantDetails
    WHERE ApplicationDetailID = @SourceApplicationDetailID;

    -- Copy proxy details from the source so that the
    -- ApplicationProxyDetails inner-join (RequiresProxy) is satisfied.
    INSERT INTO App.ApplicationProxyDetails
    (
        ApplicationDetailID,
        Surname,
        FirstNames,
        IDTypeID,
        IDTypeDescription,
        TelPhone,
        TelCell,
        TelFax,
        Email,
        IDNumberOrBusinessRegistrationNo,
        RequiresProxy
    )
    SELECT
        @ApplicationDetailID,
        Surname,
        FirstNames,
        IDTypeID,
        IDTypeDescription,
        TelPhone,
        TelCell,
        TelFax,
        Email,
        IDNumberOrBusinessRegistrationNo,
        RequiresProxy
    FROM App.ApplicationProxyDetails
    WHERE ApplicationDetailID = @SourceApplicationDetailID;

    -- Bypass business rules
    INSERT INTO App.ApplicationBypassBusinessRules(ApplicationDetailID)
    SELECT @ApplicationDetailID;

    -- Contract
    INSERT INTO App.ApplicationContractDetails(ApplicationDetailID)
    VALUES (@ApplicationDetailID);

    -- TAT
    INSERT INTO App.ApplicationTATDetails(ApplicationDetailID)
    VALUES (@ApplicationDetailID);

    -- Previous licence holder (blank – conversion app refers to existing licence)
    INSERT INTO App.ApplicationPreviousLicenceHolder(ApplicationDetailID)
    VALUES (@ApplicationDetailID);

    -- NPTR detail (store the selected platform provider so that the
    -- finalisation hook can reuse it without asking the user again).
    INSERT INTO App.ApplicationNPTRDetails(ApplicationDetailID, PlatformProviderID)
    VALUES (@ApplicationDetailID, @PlatformProviderID);

    -- Inspection details
    INSERT INTO App.ApplicationInspectionDetails(ApplicationDetailID, InspectionStatusID)
    VALUES (@ApplicationDetailID, 1 /*Awaiting inspection date*/);

    -- Referral (tourism vs planning) – follow same pattern as Pr_ApplicationCreate
    IF @DivisionTypeID = 2 /*NPTR*/
    BEGIN
        INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
        VALUES (@ApplicationDetailID, 1 /*Tourism Authority*/);
    END
    ELSE
    BEGIN
        INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
        VALUES (@ApplicationDetailID, 2 /*Planning Authority*/);
    END

    -- Status history
    INSERT INTO App.ApplicationStatusHistory(ApplicationDetailID, ApplicationStatusID, UpdatedByID, UpdatedDate)
    VALUES (@ApplicationDetailID, 2 /*Data Capture*/, @UserID, GETDATE());

    -- Sub-status history
    INSERT INTO App.ApplicationSubStatusHistory
    (
        ApplicationDetailID,
        ApplicationSubStatusID,
        UpdatedByID,
        UpdatedDate
    )
    VALUES (@ApplicationDetailID, 2 /*Awaiting Data Capture*/, @UserID, GETDATE());

    ----------------------------------------------------------------------
    -- 4c) Licence row for console: Motor Car + E-Hailing (ServiceType 9)
    --     Copy OLN/vehicle/routes from source; do not keep metered (10) as primary service.
    ----------------------------------------------------------------------
    DECLARE @NewApplicationLicenceID BIGINT;
    DECLARE @ApplicationLicenceReferenceNumber VARCHAR(50) = @ApplicationNumber + '-1';
    DECLARE @EhailingServiceDescription NVARCHAR(500);

    SELECT @EhailingServiceDescription = [Description]
    FROM Md.ServiceTypes WITH (NOLOCK)
    WHERE ServiceTypeID = 9;

    IF @EhailingServiceDescription IS NULL
        SET @EhailingServiceDescription = N'E-Hailing service';

    INSERT INTO App.ApplicationLicences
    (
        ApplicationDetailID,
        ApplicationLicenceReferenceNumber,
        ApplicationLicenceDescription,
        ApplicationLicenceRequestTypeID,
        OperatingLicenceNumber,
        LicencingBoardID,
        OperatingLicenceIssueDate,
        OperatingLicenceExpiryDate,
        ReasonForAmendment,
        SpecialLicenceDetails,
        SpecialLicenceDateFrom,
        SpecialLicenceDateTo,
        LongDistanceReason,
        ApplicationFormDocID,
        OperatingLicenceDocID,
        VehicleTypeID,
        CurrentVehicleTypeID,
        CurrentVerifiedLicenceID,
        CurrentVerifiedVehicleID,
        IsExistingLicence,
        IsAdjudicationFinalised,
        ApplicationAdjudicationID,
        SplitIntoNewApplication,
        NewApplicationDetailID,
        IsCancelled,
        LicenseSpecificConditions,
        ApplicationAllowedNewLicenceID,
        OlasId,
        CleanOperatingLicenceNumber
    )
    SELECT
        @ApplicationDetailID,
        @ApplicationLicenceReferenceNumber,
        ISNULL(src.ApplicationLicenceDescription, N'Conversion of Metered Taxi to E-hailing'),
        63,
        src.OperatingLicenceNumber,
        src.LicencingBoardID,
        src.OperatingLicenceIssueDate,
        src.OperatingLicenceExpiryDate,
        src.ReasonForAmendment,
        src.SpecialLicenceDetails,
        src.SpecialLicenceDateFrom,
        src.SpecialLicenceDateTo,
        src.LongDistanceReason,
        src.ApplicationFormDocID,
        src.OperatingLicenceDocID,
        1 /* VehicleTypeEnum.MotorCar — UI: Motor Car */,
        NULL,
        src.CurrentVerifiedLicenceID,
        src.CurrentVerifiedVehicleID,
        src.IsExistingLicence,
        0,
        NULL,
        0,
        NULL,
        0,
        src.LicenseSpecificConditions,
        NULL,
        src.OlasId,
        src.CleanOperatingLicenceNumber
    FROM App.ApplicationLicences AS src
    WHERE src.ApplicationLicenceID = @SourceApplicationLicenceID;

    SET @NewApplicationLicenceID = SCOPE_IDENTITY();

    INSERT INTO App.ApplicationLicenceServiceDetails (ApplicationLicenceID, ServiceTypeID, ServiceTypeDescription)
    VALUES (@NewApplicationLicenceID, 9, @EhailingServiceDescription);

    IF EXISTS (SELECT 1 FROM App.ApplicationLicenceVehicleDetails WHERE ApplicationLicenceID = @SourceApplicationLicenceID)
    BEGIN
        INSERT INTO App.ApplicationLicenceVehicleDetails
        (
            ApplicationLicenceID, IsStillToBeAcquired, VehicleMakeID, VehicleMakeDescription, VehicleTypeID,
            VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity,
            GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare,
            RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID,
            CertifiedServiceRecordsDocID, CertifiedPublicPassangerLiabilityDocID, eNatisCheckPassed
        )
        SELECT
            @NewApplicationLicenceID,
            v.IsStillToBeAcquired,
            v.VehicleMakeID,
            v.VehicleMakeDescription,
            1 /* Motor Car — match ApplicationLicences.VehicleTypeID */,
            v.VIN,
            v.EngineNumber,
            v.YearOfManufacture,
            v.VehicleRegistrationNumber,
            v.SeatedCapacity,
            v.StandingCapacity,
            v.GrossMass,
            v.VerifiedVehicleID,
            v.CarryingCapacityID,
            v.CurrentOdometerReading,
            v.Tare,
            v.RoadworthyCertificateDocID,
            v.ProfessionalDriversPermitDocID,
            v.RankPermitDocID,
            v.CertificateOfRegistrationDocID,
            v.VehicleModelID,
            v.CertifiedServiceRecordsDocID,
            v.CertifiedPublicPassangerLiabilityDocID,
            v.eNatisCheckPassed
        FROM App.ApplicationLicenceVehicleDetails AS v
        WHERE v.ApplicationLicenceID = @SourceApplicationLicenceID;
    END;

    IF EXISTS (SELECT 1 FROM App.ApplicationLicenceRouteDetails WHERE ApplicationLicenceID = @SourceApplicationLicenceID)
    BEGIN
        INSERT INTO App.ApplicationLicenceRouteDetails
        (
            ApplicationLicenceID,
            ApplicationRouteDetailStatusID,
            ServiceTypeID,
            VerifiedRouteID,
            NewRouteRequestDetailID,
            RouteOrigin,
            RouteDestination,
            RouteDescription,
            NationalRouteNumber,
            BoardRouteNumber,
            RouteLength,
            TimeTableDocumentID,
            FareScheduleDocumentID,
            RouteNumber,
            RouteChangeTypeID,
            IsExistingLtpsOrOlasRoute,
            IsOriginSameAsPhysicalAddress,
            IsDestinationArea,
            RelatedApplicationLicenceRouteDetailID
        )
        SELECT
            @NewApplicationLicenceID,
            r.ApplicationRouteDetailStatusID,
            CASE WHEN r.ServiceTypeID = 10 THEN 9 ELSE r.ServiceTypeID END,
            r.VerifiedRouteID,
            r.NewRouteRequestDetailID,
            r.RouteOrigin,
            r.RouteDestination,
            r.RouteDescription,
            r.NationalRouteNumber,
            r.BoardRouteNumber,
            r.RouteLength,
            r.TimeTableDocumentID,
            r.FareScheduleDocumentID,
            r.RouteNumber,
            r.RouteChangeTypeID,
            r.IsExistingLtpsOrOlasRoute,
            r.IsOriginSameAsPhysicalAddress,
            r.IsDestinationArea,
            NULL /* avoid FK to source route row IDs */
        FROM App.ApplicationLicenceRouteDetails AS r
        WHERE r.ApplicationLicenceID = @SourceApplicationLicenceID;
    END;

    -- Start the standard application workflow so that
    -- WorkflowInstanceID is populated and the console behaves like
    -- a normal application.
    DECLARE @ErrorMessage VARCHAR(MAX);
    DECLARE @Username VARCHAR(255) = Security.Fn_GetUserNameByID(@UserID);

    EXEC Wf.Pr_WorkflowInstanceStart
        1     /*application workflow*/,
        1000  /*ApplicationDetails*/,
        @ApplicationDetailID,
        NULL,
        @Username,
        @ApplicationNumber,
        @ErrorMessage OUTPUT;

    ----------------------------------------------------------------------
    -- 5) Return the new application via the standard light view
    ----------------------------------------------------------------------
    SELECT *
    FROM App.Vw_ApplicationDetails_Light
    WHERE ApplicationDetailID = @ApplicationDetailID;
END;
GO
