USE [$(DatabaseName)]

GO
SET IDENTITY_INSERT [dbo].[ATConnectionType] ON;

MERGE [dbo].[ATConnectionType] AS Target
USING (VALUES 
    (1, 'Email Connection'),
    (2, 'Database Connection'),
    (3, 'Fax Connection'),
    (4, 'Webservices Connection'),
    (5, 'Active Directory Connection'),
    (6, 'Printer Connection'),
    (7, 'Production Tracking Database Connection'),
    (8, 'Repository Database Connection')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET Name = Source.Name
WHEN NOT MATCHED THEN
    INSERT (Id, Name) VALUES (Source.Id, Source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATConnectionType] OFF;

GO
SET IDENTITY_INSERT ATRightObjectType ON;
MERGE ATRightObjectType AS Target
USING (VALUES 
    (0, 'System'),
    (1, 'Process'),
    (2, 'Queue'),
    (3, 'Button'),
    (4, 'FolderField'),
    (5, 'DocumentField'),
    (6, 'DocumentCabinet')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN NOT MATCHED THEN
    INSERT (Id, Name) VALUES (Source.Id, Source.Name);
SET IDENTITY_INSERT ATRightObjectType OFF;

GO
SET IDENTITY_INSERT [dbo].[ATTemplateType] ON;

MERGE [dbo].[ATTemplateType] AS Target
USING (VALUES
           (1, 'Document'),
           (2, 'Tracking'),
           (3, 'Repository'),
           (4, 'XML'),
           (5, 'DocumentExport'),
           (6, 'DocumentConversion'),
           (7, 'Trigger')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET
        Target.Name = Source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name)
    VALUES (Source.Id, Source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATTemplateType] OFF;
GO

MERGE ATRightAllowRight AS Target
USING (VALUES 
    (0,   'TheSystem'),
    (1,   'ViewFolderField'),
    (2,   'EditFolderField'),
    (3,   'ViewFieldOrButton'),
    (4,   'ListWorkitems'),
    (5,   'ForwardWorkitems'),
    (10,  'ProcessUseTemplate'),
    (11,  'ViewDocField'),
    (12,  'EditDocField'),
    (13,  'ChangeDocumentTemplate'),
    (20,  'UseQueue'),
    (21,  'ViewButton'),
    (22,  'ClickButton'),
    (30,  'UseProcess'),
    (31,  'CreateWorkitem'),
    (32,  'AttachDocument'),
    (33,  'DeleteDocument'),
    (34,  'EditDocument'),
    (35,  'DownloadDocument'),
    (36,  'ViewTracking'),
    (37,  'AddNote'),
    (38,  'CloneWorkitems'),
    (251, 'AdminaProcess'),
    (252, 'Designer'),
    (253, 'iAdmin'),
    (254, 'RapidWorkPlace'),
    (255, 'AllApplications')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN NOT MATCHED THEN
    INSERT (Id, Name) VALUES (Source.Id, Source.Name);
GO
SET IDENTITY_INSERT [dbo].[ATDocumentCabinetFiledType] ON;

MERGE [dbo].[ATDocumentCabinetFiledType] AS Target
USING (VALUES
    (1, 'Boolean'),
    (2, 'Integer'),
    (3, 'Number'),
    (4, 'Date'),
    (5, 'String')
) AS Source (Id, Name)
ON Target.Id = Source.Id

WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name)
    VALUES (Source.Id, Source.Name)

WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATDocumentCabinetFiledType] OFF;

GO

UPDATE ATDocumentCabinetDef
SET FieldDataTypeId = 
    CASE 
        WHEN FieldDataType = 'String' THEN 5
        WHEN FieldDataType = 'Integer' THEN 2
        WHEN FieldDataType = 'Date' THEN 4
        WHEN FieldDataType = 'Number' THEN 3
        ELSE FieldDataTypeId -- Keep the current value if no match
    END;

    SET IDENTITY_INSERT [dbo].[ATDocumenctReviewStatus] ON;

MERGE INTO [dbo].[ATDocumenctReviewStatus] AS target
USING (VALUES
    (1, 'Submitted'),
    (2, 'Draft'),
    (3, 'In process'),
    (4, 'Pending approval'),
    (5, 'Approved'),
    (6, 'Cancelled'),
    (7, 'Rejected'),
    (8, 'Withdrawn'),
    (9, 'Expired'),
    (10, 'Deleted')
) AS source ([Id], [Name])
ON target.[Id] = source.[Id]
WHEN MATCHED THEN
    UPDATE SET target.[Name] = source.[Name]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Id], [Name])
    VALUES (source.[Id], source.[Name])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATDocumenctReviewStatus] OFF;

SET IDENTITY_INSERT [dbo].[ATAssigmentType] ON;

MERGE INTO [dbo].[ATAssigmentType] AS target
USING (VALUES
    (0, N'NoneSelected'),
    (1, N'FolderField'),
    (2, N'SystemVariable'),
    (3, N'AssignedName')
) AS source ([Id], [Name])
ON target.[Id] = source.[Id]
WHEN MATCHED THEN
    UPDATE SET [Name] = source.[Name]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Id], [Name]) VALUES (source.[Id], source.[Name]);

SET IDENTITY_INSERT [dbo].[ATAssigmentType] OFF;

SET IDENTITY_INSERT [dbo].[ATUserSystemVariable] ON;

MERGE INTO [dbo].[ATUserSystemVariable] AS target
USING (VALUES
    (1, 'creator'),
    (2, 'lastActionUser'),
    (3, 'lockedBy'),
    (4, 'sourceUser')
) AS source ([Id], [Name])
ON target.[Id] = source.[Id]
WHEN MATCHED THEN
    UPDATE SET target.[Name] = source.[Name]
WHEN NOT MATCHED THEN
    INSERT ([Id], [Name])
    VALUES (source.[Id], source.[Name]);

SET IDENTITY_INSERT [dbo].[ATUserSystemVariable] OFF;

GO
SET IDENTITY_INSERT [dbo].[ATEventType] ON;

MERGE INTO [dbo].[ATEventType] AS target
USING (VALUES
    (0, 'Start', 'Start'),
    (1, 'Stop', 'Stop'),
    (2, 'Queue', 'Queue'),
    (3, 'User', 'User'),
    (4, 'Tracking', 'Tracking'),
    (5, 'Print', 'Print'),
    (6, 'Mail', 'Mail'),
    (7, 'Server', 'Server'),
    (8, 'Decision', 'Decision'),
    (9, 'SQL', 'SQL'),
    (10, 'Code', 'Code'),
    (11, 'Timer', 'Timer'),
    (12, 'FAX', 'FAX'),
    (13, 'Set Data', 'SetData'),
    (14, 'Rule', 'Rule'),
    (15, 'Export', 'Export'),
    (16, 'CallSubProcess', 'CallSubProcess'),
    (17, 'Convert', 'Convert')
) AS source ([Id], [Description], [ShortName])
ON target.[Id] = source.[Id]
WHEN MATCHED THEN
    UPDATE SET 
        target.[Description] = source.[Description],
        target.[ShortName] = source.[ShortName]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Id], [Description], [ShortName])
    VALUES (source.[Id], source.[Description], source.[ShortName])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATEventType] OFF;
GO

DECLARE @DefaultConnectionId INT
SELECT TOP 1 @DefaultConnectionId = [Id] FROM [dbo].[ATConnection] WHERE [Type] = 0 ORDER BY [Id]

UPDATE [dbo].[ATMail]
SET [ConnectionId] = @DefaultConnectionId
WHERE [ConnectionId] IS NULL;

SET IDENTITY_INSERT [dbo].[ATImportDocumentType] ON;

MERGE [dbo].[ATImportDocumentType] AS Target
USING (VALUES
    (1,  'pdf',  'Portable Document Format', 1),
    (2,  'xml',  'Extensible Markup Language', 1),
    (3,  'doc',  'Microsoft Word 97-2003 Document', 0),
    (4,  'docx', 'Microsoft Word Document', 1),
    (5,  'docm', 'Microsoft Word Macro-Enabled Document', 0),
    (6,  'rtf',  'Rich Text Format', 0),
    (7,  'txt',  'Plain Text File', 0),
    (8,  'md',   'Markdown File', 0),
    (9,  'html', 'HyperText Markup Language', 1),
    (10, 'xls',  'Microsoft Excel 97-2003 Spreadsheet', 0),
    (11, 'xlsx', 'Microsoft Excel Spreadsheet', 0),
    (12, 'pptx', 'Microsoft PowerPoint Presentation', 0),
    (13, 'jpg',  'JPEG Image', 1),
    (14, 'jpeg', 'JPEG Image', 1),
    (15, 'png',  'Portable Network Graphics Image', 1),
    (16, 'bmp',  'Bitmap Image', 0),
    (17, 'gif',  'Graphics Interchange Format Image', 0),
    (18, 'tif',  'Tagged Image File Format', 1),
    (19, 'tiff', 'Tagged Image File Format', 1)
) AS Source (Id, TypeName, Description, Convertible)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET 
        Target.TypeName = Source.TypeName,
        Target.Description = Source.Description,
        Target.Convertible = Source.Convertible
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, TypeName, Description, Convertible) 
    VALUES (Source.Id, Source.TypeName, Source.Description, Source.Convertible)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATImportDocumentType] OFF;


SET IDENTITY_INSERT [dbo].[ATBarcodeFont] ON;

MERGE [dbo].[ATBarcodeFont] AS Target
USING (
    VALUES
        (1, 'Libre Barccode128', 'LibreBarcode128-Regular.ttf - Standard Code 128 barcode font'),
        (2, '3 of 9 Barcode', 'LibreBarcode39-Regular.ttf / 3OF9.TTF - Standard Code 39 barcode font'),
        (3, 'Code 2 of 5 intervaled', 'code25I.ttf - Interleaved 2 of 5 (ITF) numeric-only barcode font'),
        (4, 'CodabarMedium', 'CodabarMedium.ttf - Codabar barcode font, often used in libraries or medical'),
        (5, 'CodabarLarge', 'CodabarLarge.ttf - Codabar barcode font, often used in libraries or medical'),
        (6, 'EAN13', 'LibreBarcodeEAN13Text-Regular.ttf - EAN-13 font for retail product labels')
) AS Source (Id, Name, Description)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET
        Target.Name = Source.Name,
        Target.Description = Source.Description
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name, Description)
    VALUES (Source.Id, Source.Name, Source.Description)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATBarcodeFont] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATMergeOrder] ON;

MERGE [dbo].[ATMergeOrder] AS Target
USING (VALUES
    (1, 'Document Name Asc',  'Merge documents by name in ascending order'),
    (2, 'Document Name Desc', 'Merge documents by name in descending order'),
    (3, 'Document Order Asc', 'Merge documents by assigned order ascending'),
    (4, 'Document Order Desc','Merge documents by assigned order descending')
) AS Source (Id, Name, Description)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET 
        Target.Name = Source.Name,
        Target.Description = Source.Description
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name, Description)
    VALUES (Source.Id, Source.Name, Source.Description)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATMergeOrder] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATExportFlagTxt] ON;

MERGE [dbo].[ATExportFlagTxt] AS Target
USING (VALUES
    (1, 'done.txt', 'Indicates export completion')
) AS Source (Id, Name, Description)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET
        Target.Name = Source.Name,
        Target.Description = Source.Description
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name, Description)
    VALUES (Source.Id, Source.Name, Source.Description)
 WHEN NOT MATCHED BY SOURCE THEN
     DELETE;

SET IDENTITY_INSERT [dbo].[ATExportFlagTxt] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATExportType] ON;

MERGE [dbo].[ATExportType] AS Target
USING (VALUES
    (1, 'Documents', 'Exports document files only'),
    (2, 'Fields',    'Exports fields from document and folder definitions')
) AS Source (Id, Name, Description)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET
        Target.Name = Source.Name,
        Target.Description = Source.Description
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name, Description)
    VALUES (Source.Id, Source.Name, Source.Description)
 WHEN NOT MATCHED BY SOURCE THEN
     DELETE;

SET IDENTITY_INSERT [dbo].[ATExportType] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATExternalTrackingSourceType] ON;

MERGE [dbo].[ATExternalTrackingSourceType] AS Target
USING (VALUES
           (1, 'PDSystemValue'),
           (2, 'FolderField'),
           (3, 'TextEntry')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET
        Target.Name = Source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name)
    VALUES (Source.Id, Source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATExternalTrackingSourceType] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATTriggerHook] ON;

MERGE [dbo].[ATTriggerHook] AS Target
USING (VALUES
           (1, N'OnArrival'),
           (2, N'OnError'),
           (3, N'OnOpenWorkItem'),
           (4, N'OnForwardWorkItem'),
           (5, N'OnCloseWorkItem'),
           (6, N'OnExpireWorkItem')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name) VALUES (Source.Id, Source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATTriggerHook] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATTriggerAction] ON;

MERGE [dbo].[ATTriggerAction] AS Target
USING (VALUES
           (1, N'SendEmail'),
           (2, N'SendToEvent'),
           (3, N'SentToProcess'),
           (4, N'RunSqlQueryWithResultInFolderField'),
           (5, N'RunCustomScript')
) AS Source (Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name) VALUES (Source.Id, Source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].[ATTriggerAction] OFF;
GO

IF NOT EXISTS (
    SELECT 1
    FROM [dbo].[ATDocumentCabinet]
    WHERE [CabinetName] = N'DefaultTemplate'
)
BEGIN
    INSERT INTO [dbo].[ATDocumentCabinet] (
        [CabinetName],
        [CabinetDescription],
        [TemplateTypeId],
        [DbConnectionId],
        [DbName],
        [ObjectId],
        [CreateDate],
        [ModifyDate]
    )
    VALUES (
        N'DefaultTemplate',
        N'This is a DefaultTemplate',
        1,
        NULL,
        NULL,
        NEWID(),
        GETDATE(),
        GETDATE()
        );
END
GO

MERGE INTO [dbo].[ATSearchTypeDefinition] AS target
USING (VALUES
    (N'Tracking'),
    (N'User'),
    (N'UserGroup'),
    (N'Document'),
    (N'Folder'),
    (N'Other')
) AS source ([TypeName])
ON target.[TypeName] = source.[TypeName]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([TypeName]) VALUES (source.[TypeName]);
GO

DECLARE @SystemGuid UNIQUEIDENTIFIER = NULL;

SELECT @SystemGuid = [Guid]
FROM [dbo].[ATSystem]
WHERE [ApplicationName] = N'Process Director';

IF @SystemGuid IS NULL
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        BEGIN
            SET @SystemGuid = NEWID();

            INSERT INTO [dbo].[ATSystem] (
                [Guid],
                [ApplicationName],
                [TrackingTableName],
                [Link],
                [LinkType]
            )
            VALUES (
                @SystemGuid,
                N'Process Director',
                N'ATTrackingRecord',
                NULL,
                NULL
            );
        END;

        MERGE INTO [dbo].[ATUser] AS target
        USING (VALUES
                   (N'PDAdminGroup',    NEWID(), N'PD Administrator Group', 3, 2, N'System PD Administrator', 0, NULL),
                   (N'PDUserGroup',     NEWID(), N'PD User Group',          3, 2, N'System PD User Group',    0, NULL),
                   (N'PDDesignerGroup', NEWID(), N'PD Designer Group',      3, 2, N'System PD Designer',      0, NULL),
                   (N'IAdminGroup',     NEWID(), N'IAdmin Group',           3, 2, N'System IAdmin',           0, NULL),
                   (N'PDAdmin',         NEWID(), N'PD Admin',               3, 1, N'PD Admin',                0, NULL)
        ) AS source (
                     [UserName],
                     [ObjectId],
                     [DisplayName],
                     [UserStore],
                     [Type],
                     [Description],
                     [Status],
                     [ADDomain]
            )
        ON target.[UserName] = source.[UserName]
        WHEN MATCHED THEN
            UPDATE SET
                       target.[DisplayName] = source.[DisplayName],
                       target.[UserStore]   = source.[UserStore],
                       target.[Type]        = source.[Type],
                       target.[Description] = source.[Description],
                       target.[Status]      = source.[Status]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT ([UserName], [ObjectId], [DisplayName], [UserStore], [Type], [Description], [Status], [ADDomain])
            VALUES (source.[UserName], source.[ObjectId], source.[DisplayName], source.[UserStore], source.[Type], source.[Description], source.[Status], source.[ADDomain]);

        DECLARE @PDAdminGroupGuid UNIQUEIDENTIFIER;
        DECLARE @PDUserGroupGuid UNIQUEIDENTIFIER;
        DECLARE @PDDesignerGroupGuid UNIQUEIDENTIFIER;
        DECLARE @IAdminGroupGuid UNIQUEIDENTIFIER;
        DECLARE @PDAdminGuid UNIQUEIDENTIFIER;

        SELECT @PDAdminGroupGuid = [ObjectId] FROM [dbo].[ATUser] WHERE [UserName] = N'PDAdminGroup';
        SELECT @PDUserGroupGuid = [ObjectId] FROM [dbo].[ATUser] WHERE [UserName] = N'PDUserGroup';
        SELECT @PDDesignerGroupGuid = [ObjectId] FROM [dbo].[ATUser] WHERE [UserName] = N'PDDesignerGroup';
        SELECT @IAdminGroupGuid = [ObjectId] FROM [dbo].[ATUser] WHERE [UserName] = N'IAdminGroup';
        SELECT @PDAdminGuid = [ObjectId] FROM [dbo].[ATUser] WHERE [UserName] = N'PDAdmin';

        MERGE INTO [dbo].[ATUserGroup] AS target
        USING (VALUES
                   (@PDAdminGuid, @PDAdminGroupGuid),
                   (@PDAdminGuid, @PDUserGroupGuid),
                   (@PDAdminGuid, @PDDesignerGroupGuid),
                   (@PDAdminGuid, @IAdminGroupGuid)
        ) AS source ([ObjectId], [ParentId])
        ON target.[ObjectId] = source.[ObjectId] AND target.[ParentId] = source.[ParentId]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT ([ObjectId], [ParentId])
            VALUES (source.[ObjectId], source.[ParentId]);

        IF NOT EXISTS (
            SELECT 1 FROM [dbo].[ATRight]
            WHERE [ObjectGuid] = @SystemGuid
        )
        BEGIN
            INSERT INTO [dbo].[ATRight] ([UserGuid], [ObjectType], [AllowRights], [ObjectGuid])
            VALUES
                (@PDUserGroupGuid, 0, 254, @SystemGuid),
                (@PDAdminGroupGuid, 0, 252, @SystemGuid),
                (@PDAdminGroupGuid, 0, 253, @SystemGuid),
                (@PDAdminGroupGuid, 0, 255, @SystemGuid),
                (@PDDesignerGroupGuid, 0, 252, @SystemGuid),
                (@IAdminGroupGuid, 0, 253, @SystemGuid);
        END;

        IF NOT EXISTS (
            SELECT 1 FROM [dbo].[ATUserProperty]
            WHERE [UserName] = N'PDAdmin' AND [PropertyName] = N'Password'
        )
        BEGIN
            INSERT INTO [dbo].[ATUserProperty] ([UserName], [PropertyName], [PropertyValue])
            VALUES (N'PDAdmin', N'Password', N'4cb9c8a8048fd02294477fcb1a41191a');
        END;

        IF NOT EXISTS (
            SELECT 1 FROM [dbo].[ATReport]
        )
        BEGIN
            INSERT INTO [dbo].[ATReport] ([Name], [Description], [SqlCode], [IsActive])
            VALUES (N'Users List', N'Lists the users currently registered in the Process Director system.', N'SELECT * FROM ATUser', 1);
        END;

        MERGE INTO [dbo].[ATSystemParameter] AS target
        USING (VALUES
            (1,   N'NextEventID',                         N'5'),
            (8,   N'LockTest',                            N'TEST1'),
            (16,  N'DocumentID',                          N'5793'),
            (17,  N'LabelID',                             N'46'),
            (99,  N'Created',                             N'7/13/2018 12:10:01 PM'),
            (100, N'REVISION',                            N'40064'),
            (23,  N'NextReportId',                        N'7'),
            (102, N'ConfigFilePath',                      N'C:\Program Files\Process Director\Bin\config.xml'),
            (103, N'FileUploadPath',                      N'C:\Program Files\Process Director\Bin\UPLOAD'),
            (104, N'ServerLogMaxSizeInMB',                N'5'),
            (1001,N'SystemGuid',                          N'{AD673351-36C3-46EF-B668-0475461C6C68}'),
            (201, N'SchedulerLogfilepath',                N'C:\PROGRAM FILES\PROCESS DIRECTOR\Logs\CPMScheduler.log'),
            (202, N'SchedulerEnterpriseDsn',              N'Intellectus_Enterprise'),
            (203, N'SchedulerProcessDirectorDsn',         N'PD2012'),
            (204, N'SchedulerContentDirectorDsn',         N'CDDATABASE'),
            (205, N'SchedulerEnterpriseURL',              N'http://iws4pd2015.intellectus.com/enterprise.asmx'),
            (206, N'SchedulerProcessDirectorURL',         N'http://iws4pd2015.intellectus.com.com/processdirector.asmx'),
            (207, N'SchedulerUserID',                     N'DefaultPDScheduler'),
            (208, N'SchedulerPassword',                   N'W+(Ö°'),
            (209, N'SchedulerLogLevel',                   N'2'),
            (105, N'AD.Path',                             N''),
            (106, N'AD.DomainName',                       N''),
            (107, N'AD.UserName',                         N''),
            (108, N'AD.Password',                         N''),
            (109, N'AD.UserContainer',                    N''),
            (110, N'AD.Available',                        N'False'),
            (210, N'SchedulerEnterpriseUserName',         N'sa'),
            (211, N'SchedulerEnterprisePassword',         N'Bºê‡âæÏz'),
            (212, N'SchedulerProcessDirectorUserName',    N'sa'),
            (213, N'SchedulerProcessDirectorPassword',    N'Bºê‡âæÏz'),
            (214, N'SchedulerContentDirectorUserName',    N'sa'),
            (215, N'SchedulerContentDirectorPassword',    N'Bºê‡âæÏz'),
            (216, N'SchedulerEnterpriseServer',           N'idb\idb'),
            (217, N'SchedulerProcessDirectorServer',      N'IDB\IDB'),
            (218, N'SchedulerContentDirectorServer',      N'idb\idb'),
            (301, N'PrintableFileExtension',              N''),
            (219, N'SchedulerEnterpriseConnectionName',   N'Process Director'),
            (1002,N'DefaultPassword',                     N'4cb9c8a8048fd02294477fcb1a41191a'),
            (27,  N'SupportEmailAddress',                 N'helpdesk@intellectus.com'),
            (28,  N'RapidURL',                            N'http://workplace.intellectus.com'),
            (220, N'SchedulerDomain',                     N''),
            (221, N'SchedulerWorkplaceDsn',               N'Workplace'),
            (222, N'SchedulerWorkplaceServer',            N'idb\idb'),
            (223, N'SchedulerWorkplaceUserName',          N'sa'),
            (224, N'SchedulerWorkplacePassword',          N'iCorp@10'),
            (226, N'SchedulerContentDirectorURL',         N'http://cd2015.intellectus.com'),
            (29,  N'NextFieldId',                         N'2917'),
            (227, N'HelpURL',                             N''),
            (1003,N'HomePage',                            N'1')
        ) AS source ([ParamID], [ParamName], [ParamValue])
        ON target.[ParamID] = source.[ParamID]
        WHEN MATCHED THEN
            UPDATE SET
                target.[ParamName]  = source.[ParamName],
                target.[ParamValue] = source.[ParamValue]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT ([ParamID], [ParamName], [ParamValue])
            VALUES (source.[ParamID], source.[ParamName], source.[ParamValue]);

        COMMIT TRANSACTION;
        PRINT 'Process Director initialization completed successfully.';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        PRINT 'Error during Process Director initialization:';
        PRINT ERROR_MESSAGE();
    END CATCH;
END
ELSE
BEGIN
    PRINT 'No Process Director system record found in ATSystem. Skipping initialization.';
END;
GO

MERGE INTO [dbo].[ATScheduleStatusType] AS target
USING (VALUES
    (1, 'Pending'),
    (2, 'Running'),
    (3, 'Completed'),
    (4, 'Failed')
) AS source ([StatusId], [StatusName])
ON target.[StatusId] = source.[StatusId]
WHEN MATCHED THEN
    UPDATE SET target.[StatusName] = source.[StatusName]
WHEN NOT MATCHED THEN
    INSERT ([StatusId], [StatusName]) VALUES (source.[StatusId], source.[StatusName]);
GO

SET IDENTITY_INSERT [dbo].[ATRunStatusType] ON;
MERGE INTO [dbo].[ATRunStatusType] AS target
USING (VALUES
    (1, 'Success'),
    (2, 'Failed'),
    (3, 'Skipped')
) AS source ([StatusId], [StatusName])
ON target.[StatusId] = source.[StatusId]
WHEN MATCHED THEN
    UPDATE SET target.[StatusName] = source.[StatusName]
WHEN NOT MATCHED THEN
    INSERT ([StatusId], [StatusName]) VALUES (source.[StatusId], source.[StatusName]);
SET IDENTITY_INSERT [dbo].[ATRunStatusType] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATScheduleType] ON;
MERGE INTO [dbo].[ATScheduleType] AS target
USING (VALUES
    (1, 'OneTime'),
    (2, 'Recurring')
) AS source ([ScheduleTypeId], [ScheduleTypeName])
ON target.[ScheduleTypeId] = source.[ScheduleTypeId]
WHEN MATCHED THEN
    UPDATE SET target.[ScheduleTypeName] = source.[ScheduleTypeName]
WHEN NOT MATCHED THEN
    INSERT ([ScheduleTypeId], [ScheduleTypeName])
    VALUES (source.[ScheduleTypeId], source.[ScheduleTypeName]);
SET IDENTITY_INSERT [dbo].[ATScheduleType] OFF;
GO

SET IDENTITY_INSERT [dbo].[ATTaskType] ON;
MERGE INTO [dbo].[ATTaskType] AS target
USING (VALUES
    (1, 'FileExecute',    'Executes a file or script on the server (EXE, BAT, PowerShell).'),
    (2, 'SqlScript',      'Runs a SQL script or stored procedure.'),
    (3, 'Email',          'Sends an email notification or report.'),
    (4, 'Unknown',        'Unknown')
) AS source ([TaskTypeId], [TaskTypeName], [Description])
ON target.[TaskTypeId] = source.[TaskTypeId]
WHEN MATCHED THEN
    UPDATE SET target.[TaskTypeName] = source.[TaskTypeName], target.[Description] = source.[Description]
WHEN NOT MATCHED THEN
    INSERT ([TaskTypeId], [TaskTypeName], [Description])
    VALUES (source.[TaskTypeId], source.[TaskTypeName], source.[Description]);
SET IDENTITY_INSERT [dbo].[ATTaskType] OFF;
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes 
    WHERE name = 'UQ_ATTemplates_ProcessCabinetType' 
      AND object_id = OBJECT_ID('dbo.ATTemplates')
)
BEGIN
    PRINT 'Creating filtered index: UQ_ATTemplates_ProcessCabinetType';
    CREATE UNIQUE INDEX [UQ_ATTemplates_ProcessCabinetType]
    ON [dbo].[ATTemplates] ([ProcessId], [DocumentCabinetId], [TemplateTypeId])
    WHERE [TemplateTypeId] <> 7;
END
GO

SET IDENTITY_INSERT [dbo].[ATTaskActionType] ON;
MERGE INTO [dbo].[ATTaskActionType] AS target
USING (VALUES
    (1, 'Next', 'Continue to next task'),
    (2, 'Retry', 'Retry this task once'),
    (3, 'Stop', 'Stop the whole schedule'),
    (4, 'Notify', 'Send email notification'),
    (5, 'Skip', 'Skip this task')
) AS source ([ActionId], [ActionName], [Description])
ON target.[ActionId] = source.[ActionId]
WHEN MATCHED THEN
    UPDATE SET target.[ActionName] = source.[ActionName], target.[Description] = source.[Description]
WHEN NOT MATCHED THEN
    INSERT ([ActionId], [ActionName], [Description])
    VALUES (source.[ActionId], source.[ActionName], source.[Description]);
SET IDENTITY_INSERT [dbo].[ATTaskActionType] OFF;
GO