USE [LegitimateKZN_QA_SO];
GO
/*===========================================================
= 1) Add E-hailing document types (if missing)
===========================================================*/

SELECT * FROM Doc.DocumentTypes
SELECT * FROM Doc.DocumentTypeQuestions

-- 6000: Platform provider agreement
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypes
    WHERE DocumentTypeID = 6000
       OR Description = 'Signed agreement between applicant and platform provider(s)'
)
BEGIN
    INSERT INTO Doc.DocumentTypes
        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
    VALUES
        (6000, 'Signed agreement between applicant and platform provider(s)', 0, 0, 0);
END;

-- 6001: Labour-law compliance statement (section 62(c) NLTA)
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypes
    WHERE DocumentTypeID = 6001
       OR Description = 'Signed statement that the operator will comply with labour laws and sectoral determinations (section 62(c) NLTA)'
)
BEGIN
    INSERT INTO Doc.DocumentTypes
        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
    VALUES
        (6001, 'Signed statement that the operator will comply with labour laws and sectoral determinations (section 62(c) NLTA)', 0, 0, 0);
END;

-- 6002: SAPS criminal record report
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypes
    WHERE DocumentTypeID = 6002
       OR Description = 'Criminal record report of the driver from SAPS'
)
BEGIN
    INSERT INTO Doc.DocumentTypes
        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
    VALUES
        (6002, 'Criminal record report of the driver from SAPS', 0, 0, 0);
END;

-- 6003: No-pending-investigations declaration
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypes
    WHERE DocumentTypeID = 6003
       OR Description = 'Signed declaration by the driver that there are no pending criminal investigations'
)
BEGIN
    INSERT INTO Doc.DocumentTypes
        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
    VALUES
        (6003, 'Signed declaration by the driver that there are no pending criminal investigations', 0, 0, 0);
END;

-- 6004: NTCMS / traffic offences report
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypes
    WHERE DocumentTypeID = 6004
       OR Description = 'Report of road traffic offences for the driver from NTCMS or other relevant systems'
)
BEGIN
    INSERT INTO Doc.DocumentTypes
        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
    VALUES
        (6004, 'Report of road traffic offences for the driver from NTCMS or other relevant systems', 0, 0, 0);
END;

-- 6005: Tax Compliance Status Pin from SARS
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypes
    WHERE DocumentTypeID = 6005
       OR Description = 'Tax Compliance Status Pin from SARS in the name of the applicant'
)
BEGIN
    INSERT INTO Doc.DocumentTypes
        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
    VALUES
        (6005, 'Tax Compliance Status Pin from SARS in the name of the applicant', 0, 0, 0);
END;

---- 6006: Optional dedicated licence disc document
--IF NOT EXISTS (
--    SELECT 1 FROM Doc.DocumentTypes
--    WHERE DocumentTypeID = 6006
--       OR Description = 'Copy of vehicle licence disc'
--)
--BEGIN
--    INSERT INTO Doc.DocumentTypes
--        (DocumentTypeID, Description, IsDocumentTemplate, IsDocumentTitleEditable, IsCancelled)
--    VALUES
--        (6006, 'Copy of vehicle licence disc', 0, 0, 0);
--END;
GO

/*===========================================================
= 2) Add questions for the new document types (if missing)
   CaptureFieldID = 25 (Yes/No style), OrderBy set high so
   they appear after existing checks.
===========================================================*/

-- 6000: Platform provider agreement
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypeQuestions
    WHERE DocumentTypeID = 6000
      AND Question = 'Is a signed agreement between the applicant and the platform provider(s) attached and valid?'
      AND IsCancelled = 0
)
BEGIN
    INSERT INTO Doc.DocumentTypeQuestions
        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
    VALUES
        (6000, 25,
         'Is a signed agreement between the applicant and the platform provider(s) attached and valid?',
         NULL, NULL, 100, NULL, NULL, 0);
END;

-- 6001: Labour-law compliance statement
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypeQuestions
    WHERE DocumentTypeID = 6001
      AND Question = 'Is a signed statement attached confirming that the operator will comply with all labour laws and sectoral determinations in terms of section 62(c) of the NLTA?'
      AND IsCancelled = 0
)
BEGIN
    INSERT INTO Doc.DocumentTypeQuestions
        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
    VALUES
        (6001, 25,
         'Is a signed statement attached confirming that the operator will comply with all labour laws and sectoral determinations in terms of section 62(c) of the NLTA?',
         NULL, NULL, 110, NULL, NULL, 0);
END;

-- 6002: SAPS criminal record report
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypeQuestions
    WHERE DocumentTypeID = 6002
      AND Question = 'Is a recent criminal record report for the driver from SAPS attached and valid?'
      AND IsCancelled = 0
)
BEGIN
    INSERT INTO Doc.DocumentTypeQuestions
        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
    VALUES
        (6002, 25,
         'Is a recent criminal record report for the driver from SAPS attached and valid?',
         NULL, NULL, 120, NULL, NULL, 0);
END;

-- 6003: No-pending-investigations declaration
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypeQuestions
    WHERE DocumentTypeID = 6003
      AND Question = 'Is a signed declaration from the driver attached confirming that there are no pending criminal investigations in South Africa or another country?'
      AND IsCancelled = 0
)
BEGIN
    INSERT INTO Doc.DocumentTypeQuestions
        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
    VALUES
        (6003, 25,
         'Is a signed declaration from the driver attached confirming that there are no pending criminal investigations in South Africa or another country?',
         NULL, NULL, 130, NULL, NULL, 0);
END;

-- 6004: NTCMS / traffic offences report
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypeQuestions
    WHERE DocumentTypeID = 6004
      AND Question = 'Is a current report of road traffic offences for the driver from NTCMS or another relevant system attached?'
      AND IsCancelled = 0
)
BEGIN
    INSERT INTO Doc.DocumentTypeQuestions
        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
    VALUES
        (6004, 25,
         'Is a current report of road traffic offences for the driver from NTCMS or another relevant system attached?',
         NULL, NULL, 140, NULL, NULL, 0);
END;

-- 6005: Tax Compliance Status Pin
IF NOT EXISTS (
    SELECT 1 FROM Doc.DocumentTypeQuestions
    WHERE DocumentTypeID = 6005
      AND Question = 'Is a valid Tax Compliance Status Pin from SARS, in the name of the applicant, provided?'
      AND IsCancelled = 0
)
BEGIN
    INSERT INTO Doc.DocumentTypeQuestions
        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
    VALUES
        (6005, 25,
         'Is a valid Tax Compliance Status Pin from SARS, in the name of the applicant, provided?',
         NULL, NULL, 150, NULL, NULL, 0);
END;

---- 6006: Vehicle licence disc (optional)
--IF NOT EXISTS (
--    SELECT 1 FROM Doc.DocumentTypeQuestions
--    WHERE DocumentTypeID = 6006
--      AND Question = 'Is a copy of the vehicle licence disc attached and valid for the vehicle used in this application?'
--      AND IsCancelled = 0
--)
--BEGIN
--    INSERT INTO Doc.DocumentTypeQuestions
--        (DocumentTypeID, CaptureFieldID, Question, CommentLabel, RelatedLinks,
--         OrderBy, DataSourceName, DataSourceCriteria, IsCancelled)
--    VALUES
--        (6006, 25,
--         'Is a copy of the vehicle licence disc attached and valid for the vehicle used in this application?',
--         NULL, NULL, 160, NULL, NULL, 0);
--END;
GO

SELECT * FROM App.ApplicationRequestTypeDocuments

DECLARE @EhalingAppTypes TABLE (ApplicationRequestTypeID SMALLINT);
INSERT INTO @EhalingAppTypes (ApplicationRequestTypeID)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(13),(30);  -- as requested

DECLARE @NewDocTypes TABLE (DocumentTypeID SMALLINT);
INSERT INTO @NewDocTypes (DocumentTypeID)
VALUES (6000),(6001),(6002),(6003),(6004),(6005);  -- core E-hailing docs (6006 is optional licence disc)

/* 
   Insert required-document mappings for workflow 1 (application),
   only where they do not already exist.
*/
INSERT INTO App.ApplicationRequestTypeDocuments
    (ApplicationRequestTypeID, WorkflowID, DocumentTypeID, IsMandatory, IsCancelled)
SELECT
    at.ApplicationRequestTypeID,
    1            AS WorkflowID,
    dt.DocumentTypeID,
    0            AS IsMandatory,
    0            AS IsCancelled
FROM @EhalingAppTypes at
CROSS JOIN @NewDocTypes dt
WHERE NOT EXISTS (
    SELECT 1
    FROM App.ApplicationRequestTypeDocuments x
    WHERE x.ApplicationRequestTypeID = at.ApplicationRequestTypeID
      AND x.WorkflowID = 1
      AND x.DocumentTypeID = dt.DocumentTypeID
);
GO