-- NPTR E-hailing App Provider Registration: schema and seeds (MVP without QR)
-- Creates Verified tables and seeds application types, division setup and document mappings

SET NOCOUNT ON;

/* --------------------------------------------------------------------------------------
   1) Verified tables for Platform Providers and Provider Certificates
---------------------------------------------------------------------------------------*/
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[Verified].[PlatformProviders]') AND type = 'U')
BEGIN
    CREATE TABLE [Verified].[PlatformProviders]
    (
        [PlatformProviderID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [LegalName]           VARCHAR(255) NOT NULL,
        [TradingName]         VARCHAR(255) NULL,
        [BusinessRegNumber]   VARCHAR(100) NOT NULL,
        [VatNumber]           VARCHAR(64) NULL,
        [Email]               VARCHAR(255) NULL,
        [Phone]               VARCHAR(64) NULL,
        [RegisteredAddressLine1] VARCHAR(255) NULL,
        [RegisteredAddressLine2] VARCHAR(255) NULL,
        [RegisteredAddressSuburb] VARCHAR(128) NULL,
        [RegisteredAddressCity]   VARCHAR(128) NULL,
        [RegisteredAddressProvinceID] SMALLINT NULL,
        [RegisteredAddressPostalCode] VARCHAR(16) NULL,
        [CreatedAt]           DATETIME NOT NULL DEFAULT(GETDATE()),
        [UpdatedAt]           DATETIME NOT NULL DEFAULT(GETDATE()),
        [IsCancelled]         BIT NOT NULL DEFAULT(0)
    );

    CREATE UNIQUE INDEX UX_PlatformProviders_BusinessRegNumber
        ON [Verified].[PlatformProviders]([BusinessRegNumber]);

    CREATE INDEX IX_PlatformProviders_LegalName
        ON [Verified].[PlatformProviders]([LegalName]);
END

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[Verified].[ProviderCertificates]') AND type = 'U')
BEGIN
    CREATE TABLE [Verified].[ProviderCertificates]
    (
        [ProviderCertificateID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [PlatformProviderID]    BIGINT NOT NULL,
        [CertificateNumber]     VARCHAR(50) NOT NULL,
        [IssuedAt]              DATETIME NOT NULL,
        [ExpiresAt]             DATETIME NOT NULL,
        [ReissuedFromID]        BIGINT NULL,
        [TemplateVersion]       SMALLINT NOT NULL DEFAULT(1),
        [IsCancelled]           BIT NOT NULL DEFAULT(0)
    );

    ALTER TABLE [Verified].[ProviderCertificates]
        ADD CONSTRAINT FK_ProviderCertificates_PlatformProviders
        FOREIGN KEY ([PlatformProviderID])
        REFERENCES [Verified].[PlatformProviders]([PlatformProviderID]);

    CREATE UNIQUE INDEX UX_ProviderCertificates_CertificateNumber
        ON [Verified].[ProviderCertificates]([CertificateNumber]);

    CREATE INDEX IX_ProviderCertificates_ExpiresAt
        ON [Verified].[ProviderCertificates]([ExpiresAt]);
END

/* --------------------------------------------------------------------------------------
   2) Document Types (add Type Approval Certificate if missing)
---------------------------------------------------------------------------------------*/
IF NOT EXISTS (SELECT 1 FROM [Doc].[DocumentTypes] WHERE [DocumentTypeID] = 6001)
BEGIN
    INSERT INTO [Doc].[DocumentTypes]([DocumentTypeID],[Description],[IsDocumentTitleEditable],[IsCancelled],[IsExpiryDateRequired],[IsUniqueEntityDocument])
    VALUES (6001, 'Type Approval Certificate', 0, 0, 0, 0);
END

/* --------------------------------------------------------------------------------------
   3) Application Request Types for NPTR Provider Registration
      IDs chosen to avoid conflict with existing seeds
---------------------------------------------------------------------------------------*/
DECLARE @Type_New SMALLINT = 60;
DECLARE @Type_Renew SMALLINT = 61;
DECLARE @Type_Amend SMALLINT = 62;

-- Explicitly enable identity insert to allow fixed IDs 60-62
SET IDENTITY_INSERT [Md].[ApplicationRequestTypes] ON;

IF NOT EXISTS (SELECT 1 FROM [Md].[ApplicationRequestTypes] WHERE [ApplicationRequestTypeID] = @Type_New)
BEGIN
    INSERT INTO [Md].[ApplicationRequestTypes]
        ([ApplicationRequestTypeID], [Title], [Cost], [IsLinkedApplicationType], [IncludeRenewalType], [IncludeCovType], [IncludeIncreaseInCapacityType], [IncludeDeceasedTransferType], [IncludeNormalTransferType], [IncludeNewOperatingLicenceType], [IsCancelled], [IsAllowedForOLAS], [IsAllowedForLTPS], [IsAllowedForLG], [IsLicenceDurationRequiredOnApprove])
    VALUES
        (@Type_New, 'E-hailing App Provider Registration', 5000.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
END

IF NOT EXISTS (SELECT 1 FROM [Md].[ApplicationRequestTypes] WHERE [ApplicationRequestTypeID] = @Type_Renew)
BEGIN
    INSERT INTO [Md].[ApplicationRequestTypes]
        ([ApplicationRequestTypeID], [Title], [Cost], [IsLinkedApplicationType], [IncludeRenewalType], [IncludeCovType], [IncludeIncreaseInCapacityType], [IncludeDeceasedTransferType], [IncludeNormalTransferType], [IncludeNewOperatingLicenceType], [IsCancelled], [IsAllowedForOLAS], [IsAllowedForLTPS], [IsAllowedForLG], [IsLicenceDurationRequiredOnApprove])
    VALUES
        (@Type_Renew, 'Renewal of E-hailing App Provider Registration', 5000.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
END

IF NOT EXISTS (SELECT 1 FROM [Md].[ApplicationRequestTypes] WHERE [ApplicationRequestTypeID] = @Type_Amend)
BEGIN
    INSERT INTO [Md].[ApplicationRequestTypes]
        ([ApplicationRequestTypeID], [Title], [Cost], [IsLinkedApplicationType], [IncludeRenewalType], [IncludeCovType], [IncludeIncreaseInCapacityType], [IncludeDeceasedTransferType], [IncludeNormalTransferType], [IncludeNewOperatingLicenceType], [IsCancelled], [IsAllowedForOLAS], [IsAllowedForLTPS], [IsAllowedForLG], [IsLicenceDurationRequiredOnApprove])
    VALUES
        (@Type_Amend, 'Amend Conditions – E-hailing App Provider Registration', 0.00, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
END

SET IDENTITY_INSERT [Md].[ApplicationRequestTypes] OFF;

/* --------------------------------------------------------------------------------------
   4) Division setup (NPTR only, National; no gazetting; adjudication meeting)
---------------------------------------------------------------------------------------*/
IF NOT EXISTS (SELECT 1 FROM [Md].[ApplicationRequestTypeDivisionSetup] WHERE [ApplicationRequestTypeID] = @Type_New AND [DivisionTypeID] = 2 AND [ProvinceID] = 10)
BEGIN
    INSERT INTO [Md].[ApplicationRequestTypeDivisionSetup]
        ([ApplicationRequestTypeID], [DivisionTypeID], [ProvinceID], [IsAssessorApprovalRequiredRule], [IsSeniorManagerApprovalRequiredRule], [IsFinalisationTasksRequiredRule], [IsInspectionRequiredRule], [IsReferralRequiredRule], [IsAdjudicationRequiredRule], [AdjudicationTypeRule], [IsGazettingRequiredRule], [GazetteTypeRule], [IsNew])
    VALUES
        (@Type_New, 2, 10, 'NO', 'NO', 'NO', 'NO', 'NO', 'YES', '2 /*MEETING*/', '0=1', '1 /*INTERNAL GAZETTE*/', 0);
END

IF NOT EXISTS (SELECT 1 FROM [Md].[ApplicationRequestTypeDivisionSetup] WHERE [ApplicationRequestTypeID] = @Type_Renew AND [DivisionTypeID] = 2 AND [ProvinceID] = 10)
BEGIN
    INSERT INTO [Md].[ApplicationRequestTypeDivisionSetup]
        ([ApplicationRequestTypeID], [DivisionTypeID], [ProvinceID], [IsAssessorApprovalRequiredRule], [IsSeniorManagerApprovalRequiredRule], [IsFinalisationTasksRequiredRule], [IsInspectionRequiredRule], [IsReferralRequiredRule], [IsAdjudicationRequiredRule], [AdjudicationTypeRule], [IsGazettingRequiredRule], [GazetteTypeRule], [IsNew])
    VALUES
        (@Type_Renew, 2, 10, 'NO', 'NO', 'NO', 'NO', 'NO', 'YES', '2 /*MEETING*/', '0=1', '1 /*INTERNAL GAZETTE*/', 0);
END

IF NOT EXISTS (SELECT 1 FROM [Md].[ApplicationRequestTypeDivisionSetup] WHERE [ApplicationRequestTypeID] = @Type_Amend AND [DivisionTypeID] = 2 AND [ProvinceID] = 10)
BEGIN
    INSERT INTO [Md].[ApplicationRequestTypeDivisionSetup]
        ([ApplicationRequestTypeID], [DivisionTypeID], [ProvinceID], [IsAssessorApprovalRequiredRule], [IsSeniorManagerApprovalRequiredRule], [IsFinalisationTasksRequiredRule], [IsInspectionRequiredRule], [IsReferralRequiredRule], [IsAdjudicationRequiredRule], [AdjudicationTypeRule], [IsGazettingRequiredRule], [GazetteTypeRule], [IsNew])
    VALUES
        (@Type_Amend, 2, 10, 'NO', 'NO', 'NO', 'NO', 'NO', 'YES', '2 /*MEETING*/', '0=1', '1 /*INTERNAL GAZETTE*/', 0);
END

/* --------------------------------------------------------------------------------------
   5) Document checklist (five items) per application type for NPTR
---------------------------------------------------------------------------------------*/
DECLARE @WorkflowTemplateID SMALLINT = 1; -- ApplicationRequestV1

-- Helper to insert a mapping if missing
IF OBJECT_ID('tempdb..#DocMap') IS NOT NULL DROP TABLE #DocMap;
CREATE TABLE #DocMap (DocID SMALLINT);
INSERT INTO #DocMap(DocID)
VALUES (1047), -- Completed Application Form (2A)
       (5001), -- Company Registration Certificate
       (2007), -- Company Proxy Letter
       (1059), -- Proof of Payment
       (6001); -- Type Approval Certificate

DECLARE @DocID SMALLINT;

-- Map documents for each of the three types
DECLARE @Types TABLE(TypeID SMALLINT);
INSERT INTO @Types(TypeID) VALUES (@Type_New), (@Type_Renew), (@Type_Amend);

DECLARE @T SMALLINT;
DECLARE TypeCursor CURSOR LOCAL FAST_FORWARD FOR SELECT TypeID FROM @Types;
OPEN TypeCursor;
FETCH NEXT FROM TypeCursor INTO @T;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE DocCursor CURSOR LOCAL FAST_FORWARD FOR SELECT DocID FROM #DocMap;
    OPEN DocCursor;
    FETCH NEXT FROM DocCursor INTO @DocID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF NOT EXISTS (
            SELECT 1 FROM [Md].[ApplicationRequestTypeDocumentSetup]
            WHERE [DivisionTypeID] = 2 AND [ProvinceID] = 10 AND [ApplicationRequestTypeID] = @T AND [DocumentTypeID] = @DocID AND [WorkflowTemplateID] = @WorkflowTemplateID
        )
        BEGIN
            INSERT INTO [Md].[ApplicationRequestTypeDocumentSetup]
                ([DivisionTypeID], [ProvinceID], [ApplicationRequestTypeID], [DocumentTypeID], [WorkflowTemplateID], [IsCancelled], [IsAvailableForUpload], [IsMandatory], [IsNew])
            VALUES
                (2, 10, @T, @DocID, @WorkflowTemplateID, 0, 'Yes', 'Yes', 0);
        END
        FETCH NEXT FROM DocCursor INTO @DocID;
    END
    CLOSE DocCursor;
    DEALLOCATE DocCursor;

    FETCH NEXT FROM TypeCursor INTO @T;
END
CLOSE TypeCursor;
DEALLOCATE TypeCursor;

PRINT 'NPTR E-hailing App Provider Registration: schema and seeds complete.';


