---version-12.1.0--- 
IF OBJECT_ID(N'[EmailTemplate]') IS NULL
BEGIN
CREATE TABLE [EmailTemplate] (
    [Id] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [Name] nvarchar(max) NULL,
    [TemplateContent] nvarchar(max) NULL,
    CONSTRAINT [PK_EmailTemplate] PRIMARY KEY ([Id])
);
END
GO

IF OBJECT_ID(N'[GlobalFileStorage]') IS NULL
BEGIN
CREATE TABLE [GlobalFileStorage] (
    [Id] uniqueidentifier NOT NULL,
    [FileName] nvarchar(max) NULL,
    [FileExtension] nvarchar(max) NULL,
    [CreationDate] datetime2 NOT NULL,
    [RelativeUrl] nvarchar(max) NULL,
    [Checksum] nvarchar(max) NULL,
    [Data] varbinary(max) NULL,
    CONSTRAINT [PK_GlobalFileStorage] PRIMARY KEY ([Id])
);
END
GO

IF OBJECT_ID(N'[JobGroup]') IS NULL
BEGIN
CREATE TABLE [JobGroup] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [TenantId] uniqueidentifier NOT NULL,
    [ImagePath] nvarchar(max) NULL,
    [Name] nvarchar(255) NOT NULL,
    [ParentGroupId] uniqueidentifier NULL,
    CONSTRAINT [PK_JobGroup] PRIMARY KEY ([Id]),
    CONSTRAINT [FKJobGroup0175584] FOREIGN KEY ([ParentGroupId]) REFERENCES [JobGroup] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[SystemLog]') IS NULL
BEGIN
CREATE TABLE [SystemLog] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Date] datetime2 NOT NULL,
    [Thread] nvarchar(255) NULL,
    [Level] nvarchar(50) NULL,
    [Logger] nvarchar(255) NULL,
    [Message] nvarchar(4000) NULL,
    [Exception] nvarchar(2000) NULL,
    CONSTRAINT [PK_SystemLog] PRIMARY KEY ([Id])
);
END
GO

IF OBJECT_ID(N'[TaskGroup]') IS NULL
BEGIN
CREATE TABLE [TaskGroup] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [TenantId] uniqueidentifier NOT NULL,
    [ImagePath] nvarchar(max) NULL,
    [Name] nvarchar(255) NOT NULL,
    [ParentGroupId] uniqueidentifier NULL,
    CONSTRAINT [PK_TaskGroup] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTaskGroup0175583] FOREIGN KEY ([ParentGroupId]) REFERENCES [TaskGroup] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[Tenant]') IS NULL
BEGIN
CREATE TABLE [Tenant] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Name] nvarchar(255) NOT NULL,
    [ImagePath] nvarchar(255) NULL,
    [EncryptionKey] nvarchar(255) NULL,
    [DatabaseName] nvarchar(255) NULL,
    [ConnectionString] nvarchar(255) NULL,
    [IsActive] tinyint NULL,
    [IsDataEncrypted] tinyint NULL,
    [ColumnEncryptionKeyName] nvarchar(255) NULL,
    CONSTRAINT [PK_Tenant] PRIMARY KEY ([Id])
);
END
GO

IF OBJECT_ID(N'[User]') IS NULL
BEGIN
CREATE TABLE [User] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [CreationDate] datetime NULL,
    [Password] nvarchar(255) NOT NULL,
    [Image] nvarchar(255) NULL,
    [UserName] nvarchar(255) NOT NULL,
    [Company] nvarchar(255) NULL,
    [Department] nvarchar(255) NULL,
    [Office] nvarchar(255) NULL,
    [Position] nvarchar(255) NULL,
    [Email] nvarchar(255) NULL,
    [Telephone] nvarchar(255) NULL,
    [SiteAdmin] tinyint NOT NULL,
    CONSTRAINT [PK_User] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_User_UserName] UNIQUE ([UserName])
);
END
GO

IF OBJECT_ID(N'[EmailNotification]') IS NULL
BEGIN
CREATE TABLE [EmailNotification] (
    [Id] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [EmailTemplateId] uniqueidentifier NOT NULL,
    [Schedule] datetime2 NOT NULL,
    [Name] nvarchar(max) NULL,
    [CronExpression] nvarchar(max) NULL,
    [isActive] tinyint NULL,
    [EmailSubject] nvarchar(max) NULL,
    CONSTRAINT [PK_EmailNotification] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_EmailNotification_EmailTemplate_EmailTemplateId] FOREIGN KEY ([EmailTemplateId]) REFERENCES [EmailTemplate] ([Id]) ON DELETE CASCADE
);
END
GO

IF OBJECT_ID(N'[Configuration]') IS NULL
BEGIN
CREATE TABLE [Configuration] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [TenantId] uniqueidentifier NULL,
    [Property] nvarchar(255) NULL,
    [Value] nvarchar(1024) NULL,
    CONSTRAINT [PK_Configuration] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant460375] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[Connector]') IS NULL
BEGIN
CREATE TABLE [Connector] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Host] nvarchar(255) NULL,
    [ConnectorIdentifier] nvarchar(255) NOT NULL,
    [Name] nvarchar(64) NULL,
    [Status] nvarchar(255) NOT NULL,
    [Version] nvarchar(50) NOT NULL,
    [LastConnection] datetime NULL,
    [Authorized] tinyint NOT NULL,
    [RegDate] datetime NOT NULL,
    [ErrorMessage] nvarchar(1024) NULL,
    [DisplayName] nvarchar(255) NOT NULL,
    [IsActive] tinyint NOT NULL DEFAULT CAST(1 AS tinyint),
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_Connector] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_Connector_ConnectorIdentifier_TenantId] UNIQUE ([ConnectorIdentifier], [TenantId]),
    CONSTRAINT [AK_Connector_DisplayName_TenantId] UNIQUE ([DisplayName], [TenantId]),
    CONSTRAINT [FKTenant338840] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[Download]') IS NULL
BEGIN
CREATE TABLE [Download] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Name] nvarchar(255) NOT NULL,
    [Description] nvarchar(1024) NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Instructions] nvarchar(max) NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_Download] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant460376] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[FileStorage]') IS NULL
BEGIN
CREATE TABLE [FileStorage] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [FileName] nvarchar(255) NOT NULL,
    [FileExtension] nvarchar(255) NOT NULL,
    [CreationDate] datetime NOT NULL,
    [RelativeUrl] nvarchar(255) NOT NULL,
    [Checksum] nvarchar(255) NOT NULL,
    [Data] varbinary(max) NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_FileStorage] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant921331] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[Task]') IS NULL
BEGIN
CREATE TABLE [Task] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [TaskGroupId] uniqueidentifier NULL,
    [Name] nvarchar(255) NOT NULL,
    [Configuration] nvarchar(max) NULL,
    [TaskType] nvarchar(255) NOT NULL,
    [TableName] nvarchar(128) NOT NULL,
    [IsActive] tinyint NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_Task] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_Task_Name_TenantId] UNIQUE ([Name], [TenantId]),
    CONSTRAINT [AK_Task_TableName_TenantId] UNIQUE ([TableName], [TenantId]),
    CONSTRAINT [FKTaskGroup0175584] FOREIGN KEY ([TaskGroupId]) REFERENCES [TaskGroup] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenantO17557] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[UserGroup]') IS NULL
BEGIN
CREATE TABLE [UserGroup] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [CreationDate] datetime NULL,
    [Role] nvarchar(24) NOT NULL,
    [Name] nvarchar(255) NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_UserGroup] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_UserGroup_Name_TenantId] UNIQUE ([Name], [TenantId]),
    CONSTRAINT [FKTenant921329] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[XmlStorage]') IS NULL
BEGIN
CREATE TABLE [XmlStorage] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Name] nvarchar(255) NOT NULL,
    [Type] nvarchar(255) NOT NULL,
    [Content] xml NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_XmlStorage] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant921330] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[ApiKey]') IS NULL
BEGIN
CREATE TABLE [ApiKey] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Name] nvarchar(255) NOT NULL,
    [KeyHash] nvarchar(255) NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [LastUsedDate] datetime NULL,
    [Created] datetime NOT NULL,
    CONSTRAINT [PK_ApiKey] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_ApiKey_Name_TenantId_UserId] UNIQUE ([Name], [TenantId], [UserId]),
    CONSTRAINT [FK_ApiKey_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_ApiKey_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[ChangeHistory]') IS NULL
BEGIN
CREATE TABLE [ChangeHistory] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Change] nvarchar(255) NULL,
    [ObjectTitle] nvarchar(255) NOT NULL,
    [ObjectId] UNIQUEIDENTIFIER NULL,
    [ObjectType] nvarchar(40) NOT NULL,
    [TimeStamp] datetime NULL,
    [UserId] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_ChangeHistory] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant663355] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKChangeHist663354] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[Login]') IS NULL
BEGIN
CREATE TABLE [Login] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [LoginDate] datetime NULL,
    [UserId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_Login] PRIMARY KEY ([Id]),
    CONSTRAINT [FKLogin920310] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[ReportingObject]') IS NULL
BEGIN
CREATE TABLE [ReportingObject] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [CreatedByUserId] uniqueidentifier NOT NULL,
    [ParentFolderId] uniqueidentifier NULL,
    [CreationDate] datetime NULL,
    [ImagePath] nvarchar(max) NULL,
    [Name] nvarchar(255) NOT NULL,
    [NavigationName] nvarchar(255) NOT NULL,
    [Type] nvarchar(64) NULL,
    [StorageId] nvarchar(255) NULL,
    [IsActive] tinyint NULL,
    [Featured] tinyint NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_ReportingObject] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_ReportingObject_Name_TenantId] UNIQUE ([Name], [TenantId]),
    CONSTRAINT [AK_ReportingObject_NavigationName_TenantId] UNIQUE ([NavigationName], [TenantId]),
    CONSTRAINT [FKReportingO472041] FOREIGN KEY ([CreatedByUserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKReportingO175574] FOREIGN KEY ([ParentFolderId]) REFERENCES [ReportingObject] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenantO175575] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[TenantAssignment]') IS NULL
BEGIN
CREATE TABLE [TenantAssignment] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [MemberSince] datetime NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_TenantAssignment] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_TenantAssignment_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_TenantAssignment_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[UserToken]') IS NULL
BEGIN
CREATE TABLE [UserToken] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [ExpirationDate] datetime NULL,
    [RefreshToken] nvarchar(1024) NULL,
    [Fingerprint] nvarchar(255) NULL,
    [UserId] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [RememberMe] tinyint NULL,
    CONSTRAINT [PK_UserToken] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant460374] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKToken460373] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[EmailUserAssignment]') IS NULL
BEGIN
CREATE TABLE [EmailUserAssignment] (
    [Id] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [EmailConfigurationId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_EmailUserAssignment] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_EmailUserAssignment_EmailNotification_EmailConfigurationId] FOREIGN KEY ([EmailConfigurationId]) REFERENCES [EmailNotification] ([Id]),
    CONSTRAINT [FK_EmailUserAssignment_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE CASCADE
);
END
GO

IF OBJECT_ID(N'[ConnectorChanges]') IS NULL
BEGIN
CREATE TABLE [ConnectorChanges] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [DisplayName] nvarchar(255) NULL,
    [Name] nvarchar(64) NULL,
    [Host] nvarchar(255) NULL,
    [Authorized] tinyint NULL,
    [IsActive] tinyint NULL,
    [ConnectorId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [Timestamp] datetime NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_ConnectorChanges] PRIMARY KEY ([Id]),
    CONSTRAINT [FKConnector9603749] FOREIGN KEY ([ConnectorId]) REFERENCES [Connector] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenant921328] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKUser921327] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[Job]') IS NULL
BEGIN
CREATE TABLE [Job] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [ConnectorId] uniqueidentifier NOT NULL,
    [Name] nvarchar(255) NOT NULL,
    [QueueBehaviour] nvarchar(24) NOT NULL,
    [Cron] nvarchar(1024) NULL,
    [JobGroupId] uniqueidentifier NULL,
    [IsActive] tinyint NULL,
    [StartDate] datetime NULL,
    [Configuration] nvarchar(max) NULL,
    [CommandTimeout] int NOT NULL,
    [TargetType] nvarchar(255) NOT NULL,
    [TargetHost] nvarchar(1024) NULL,
    [CleanupTable] tinyint NULL DEFAULT CAST(0 AS tinyint),
    [IgnoreDuplicates] tinyint NULL DEFAULT CAST(0 AS tinyint),
    [TaskId] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_Job] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_Job_Name_TenantId] UNIQUE ([Name], [TenantId]),
    CONSTRAINT [FKJob520887] FOREIGN KEY ([ConnectorId]) REFERENCES [Connector] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKJobGroup0175585] FOREIGN KEY ([JobGroupId]) REFERENCES [JobGroup] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKJob331365] FOREIGN KEY ([TaskId]) REFERENCES [Task] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenant520888] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[TaskChanges]') IS NULL
BEGIN
CREATE TABLE [TaskChanges] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Name] nvarchar(255) NULL,
    [TableName] nvarchar(128) NULL,
    [Configuration] nvarchar(max) NULL,
    [IsActive] tinyint NULL,
    [TaskId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [Timestamp] datetime NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_TaskChanges] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTaskChange331367] FOREIGN KEY ([TaskId]) REFERENCES [Task] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenant331369] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTaskChange331368] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[GroupAssignment]') IS NULL
BEGIN
CREATE TABLE [GroupAssignment] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [MemberSince] datetime NULL,
    [UserGroupId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_GroupAssignment] PRIMARY KEY ([Id]),
    CONSTRAINT [FKTenant176460] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_GroupAssignment_UserGroup_UserGroupId] FOREIGN KEY ([UserGroupId]) REFERENCES [UserGroup] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_GroupAssignment_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE CASCADE
);
END
GO

IF OBJECT_ID(N'[EmailReportAssignment]') IS NULL
BEGIN
CREATE TABLE [EmailReportAssignment] (
    [Id] uniqueidentifier NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    [EmailConfigurationId] uniqueidentifier NOT NULL,
    [ReportingObjectId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_EmailReportAssignment] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_EmailReportAssignment_EmailNotification_EmailConfigurationId] FOREIGN KEY ([EmailConfigurationId]) REFERENCES [EmailNotification] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_EmailReportAssignment_ReportingObject_ReportingObjectId] FOREIGN KEY ([ReportingObjectId]) REFERENCES [ReportingObject] ([Id]) ON DELETE CASCADE
);
END
GO

IF OBJECT_ID(N'[PermissionAssignment]') IS NULL
BEGIN
CREATE TABLE [PermissionAssignment] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [Inheritance] tinyint NULL,
    [ObjectDepth] int NOT NULL,
    [Permission] nvarchar(24) NOT NULL,
    [ReportingObjectId] uniqueidentifier NOT NULL,
    [UserGroupId] uniqueidentifier NOT NULL,
    [InferredPermissionId] uniqueidentifier NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_PermissionAssignment] PRIMARY KEY ([Id]),
    CONSTRAINT [FKPermission13328] FOREIGN KEY ([InferredPermissionId]) REFERENCES [PermissionAssignment] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKPermission776454] FOREIGN KEY ([ReportingObjectId]) REFERENCES [ReportingObject] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenant13329] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKPermission41918] FOREIGN KEY ([UserGroupId]) REFERENCES [UserGroup] ([Id]) ON DELETE NO ACTION
);
END
GO

IF OBJECT_ID(N'[JobHistory]') IS NULL
BEGIN
CREATE TABLE [JobHistory] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [JobId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NULL,
    [JobStatus] nvarchar(24) NOT NULL,
    [Progress] float(10) NULL,
    [LastUpdated] datetime NULL,
    [PlannedStartDate] datetime NOT NULL,
    [StartDate] datetime NULL,
    [EndDate] datetime NULL,
    [ErrorMessage] nvarchar(1024) NULL,
    [TaskId] uniqueidentifier NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_JobHistory] PRIMARY KEY ([Id]),
    CONSTRAINT [FKJobHistory287490] FOREIGN KEY ([JobId]) REFERENCES [Job] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKJobHistory287492] FOREIGN KEY ([TaskId]) REFERENCES [Task] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKTenant287493] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FKJobHistory287491] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ApiKey') AND name='IX_ApiKey_KeyHash')
BEGIN
CREATE UNIQUE INDEX [IX_ApiKey_KeyHash] ON [ApiKey] ([KeyHash]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ApiKey') AND name='IX_ApiKey_TenantId')
BEGIN
CREATE INDEX [IX_ApiKey_TenantId] ON [ApiKey] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ApiKey') AND name='IX_ApiKey_UserId')
BEGIN
CREATE INDEX [IX_ApiKey_UserId] ON [ApiKey] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ChangeHistory') AND name='IX_ChangeHistory_TenantId')
BEGIN
CREATE INDEX [IX_ChangeHistory_TenantId] ON [ChangeHistory] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ChangeHistory') AND name='IX_ChangeHistory_UserId')
BEGIN
CREATE INDEX [IX_ChangeHistory_UserId] ON [ChangeHistory] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Configuration') AND name='IX_Configuration_TenantId')
BEGIN
CREATE INDEX [IX_Configuration_TenantId] ON [Configuration] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Connector') AND name='IX_Connector_TenantId')
BEGIN
CREATE INDEX [IX_Connector_TenantId] ON [Connector] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ConnectorChanges') AND name='IX_ConnectorChanges_ConnectorId')
BEGIN
CREATE INDEX [IX_ConnectorChanges_ConnectorId] ON [ConnectorChanges] ([ConnectorId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ConnectorChanges') AND name='IX_ConnectorChanges_TenantId')
BEGIN
CREATE INDEX [IX_ConnectorChanges_TenantId] ON [ConnectorChanges] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ConnectorChanges') AND name='IX_ConnectorChanges_UserId')
BEGIN
CREATE INDEX [IX_ConnectorChanges_UserId] ON [ConnectorChanges] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Download') AND name='IX_Download_TenantId')
BEGIN
CREATE INDEX [IX_Download_TenantId] ON [Download] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('EmailNotification') AND name='IX_EmailNotification_EmailTemplateId')
BEGIN
CREATE INDEX [IX_EmailNotification_EmailTemplateId] ON [EmailNotification] ([EmailTemplateId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('EmailReportAssignment') AND name='IX_EmailReportAssignment_EmailConfigurationId')
BEGIN
CREATE INDEX [IX_EmailReportAssignment_EmailConfigurationId] ON [EmailReportAssignment] ([EmailConfigurationId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('EmailReportAssignment') AND name='IX_EmailReportAssignment_ReportingObjectId')
BEGIN
CREATE INDEX [IX_EmailReportAssignment_ReportingObjectId] ON [EmailReportAssignment] ([ReportingObjectId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('EmailUserAssignment') AND name='IX_EmailUserAssignment_EmailConfigurationId')
BEGIN
CREATE INDEX [IX_EmailUserAssignment_EmailConfigurationId] ON [EmailUserAssignment] ([EmailConfigurationId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('EmailUserAssignment') AND name='IX_EmailUserAssignment_UserId')
BEGIN
CREATE INDEX [IX_EmailUserAssignment_UserId] ON [EmailUserAssignment] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('FileStorage') AND name='IX_FileStorage_TenantId')
BEGIN
CREATE INDEX [IX_FileStorage_TenantId] ON [FileStorage] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('GroupAssignment') AND name='IX_GroupAssignment_TenantId')
BEGIN
CREATE INDEX [IX_GroupAssignment_TenantId] ON [GroupAssignment] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('GroupAssignment') AND name='IX_GroupAssignment_UserGroupId')
BEGIN
CREATE INDEX [IX_GroupAssignment_UserGroupId] ON [GroupAssignment] ([UserGroupId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('GroupAssignment') AND name='IX_GroupAssignment_UserId')
BEGIN
CREATE INDEX [IX_GroupAssignment_UserId] ON [GroupAssignment] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Job') AND name='IX_Job_ConnectorId')
BEGIN
CREATE INDEX [IX_Job_ConnectorId] ON [Job] ([ConnectorId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Job') AND name='IX_Job_JobGroupId')
BEGIN
CREATE INDEX [IX_Job_JobGroupId] ON [Job] ([JobGroupId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Job') AND name='IX_Job_TaskId')
BEGIN
CREATE INDEX [IX_Job_TaskId] ON [Job] ([TaskId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Job') AND name='IX_Job_TenantId')
BEGIN
CREATE INDEX [IX_Job_TenantId] ON [Job] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('JobGroup') AND name='IX_JobGroup_ParentGroupId')
BEGIN
CREATE INDEX [IX_JobGroup_ParentGroupId] ON [JobGroup] ([ParentGroupId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('JobHistory') AND name='IX_JobHistory_JobId')
BEGIN
CREATE INDEX [IX_JobHistory_JobId] ON [JobHistory] ([JobId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('JobHistory') AND name='IX_JobHistory_TaskId')
BEGIN
CREATE INDEX [IX_JobHistory_TaskId] ON [JobHistory] ([TaskId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('JobHistory') AND name='IX_JobHistory_TenantId')
BEGIN
CREATE INDEX [IX_JobHistory_TenantId] ON [JobHistory] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('JobHistory') AND name='IX_JobHistory_UserId')
BEGIN
CREATE INDEX [IX_JobHistory_UserId] ON [JobHistory] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Login') AND name='IX_Login_UserId')
BEGIN
CREATE INDEX [IX_Login_UserId] ON [Login] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('PermissionAssignment') AND name='IX_PermissionAssignment_InferredPermissionId')
BEGIN
CREATE INDEX [IX_PermissionAssignment_InferredPermissionId] ON [PermissionAssignment] ([InferredPermissionId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('PermissionAssignment') AND name='IX_PermissionAssignment_ReportingObjectId')
BEGIN
CREATE INDEX [IX_PermissionAssignment_ReportingObjectId] ON [PermissionAssignment] ([ReportingObjectId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('PermissionAssignment') AND name='IX_PermissionAssignment_TenantId')
BEGIN
CREATE INDEX [IX_PermissionAssignment_TenantId] ON [PermissionAssignment] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('PermissionAssignment') AND name='IX_PermissionAssignment_UserGroupId')
BEGIN
CREATE INDEX [IX_PermissionAssignment_UserGroupId] ON [PermissionAssignment] ([UserGroupId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ReportingObject') AND name='IX_ReportingObject_CreatedByUserId')
BEGIN
CREATE INDEX [IX_ReportingObject_CreatedByUserId] ON [ReportingObject] ([CreatedByUserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ReportingObject') AND name='IX_ReportingObject_ParentFolderId')
BEGIN
CREATE INDEX [IX_ReportingObject_ParentFolderId] ON [ReportingObject] ([ParentFolderId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ReportingObject') AND name='IX_ReportingObject_TenantId')
BEGIN
CREATE INDEX [IX_ReportingObject_TenantId] ON [ReportingObject] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Task') AND name='IX_Task_TaskGroupId')
BEGIN
CREATE INDEX [IX_Task_TaskGroupId] ON [Task] ([TaskGroupId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Task') AND name='IX_Task_TenantId')
BEGIN
CREATE INDEX [IX_Task_TenantId] ON [Task] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('TaskChanges') AND name='IX_TaskChanges_TaskId')
BEGIN
CREATE INDEX [IX_TaskChanges_TaskId] ON [TaskChanges] ([TaskId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('TaskChanges') AND name='IX_TaskChanges_TenantId')
BEGIN
CREATE INDEX [IX_TaskChanges_TenantId] ON [TaskChanges] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('TaskChanges') AND name='IX_TaskChanges_UserId')
BEGIN
CREATE INDEX [IX_TaskChanges_UserId] ON [TaskChanges] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('TaskGroup') AND name='IX_TaskGroup_ParentGroupId')
BEGIN
CREATE INDEX [IX_TaskGroup_ParentGroupId] ON [TaskGroup] ([ParentGroupId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Tenant') AND name='IX_Tenant_Name')
BEGIN
CREATE UNIQUE INDEX [IX_Tenant_Name] ON [Tenant] ([Name]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('TenantAssignment') AND name='IX_TenantAssignment_TenantId')
BEGIN
CREATE INDEX [IX_TenantAssignment_TenantId] ON [TenantAssignment] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('TenantAssignment') AND name='IX_TenantAssignment_UserId')
BEGIN
CREATE INDEX [IX_TenantAssignment_UserId] ON [TenantAssignment] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('UserGroup') AND name='IX_UserGroup_TenantId')
BEGIN
CREATE INDEX [IX_UserGroup_TenantId] ON [UserGroup] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('UserToken') AND name='IX_UserToken_TenantId')
BEGIN
CREATE INDEX [IX_UserToken_TenantId] ON [UserToken] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('UserToken') AND name='IX_UserToken_UserId')
BEGIN
CREATE INDEX [IX_UserToken_UserId] ON [UserToken] ([UserId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('XmlStorage') AND name='IX_XmlStorage_TenantId')
BEGIN
CREATE INDEX [IX_XmlStorage_TenantId] ON [XmlStorage] ([TenantId]);
END
GO

---version-12.2.0---

IF OBJECT_ID('JobHistory', 'U') IS NOT NULL AND OBJECT_ID('Job', 'U') IS NOT NULL
BEGIN
PRINT N'Deduplicating the content of the Job table'
DELETE FROM JobHIstory WHERE JobId NOT IN
    (
        SELECT MAX(ID) AS MaxID
        FROM [Job]
        GROUP BY [TaskID]
    );

	DELETE FROM Job WHERE Id NOT IN
    (
        SELECT MAX(ID) AS MaxID
        FROM [Job]
        GROUP BY [TaskID]
    );
END

GO

IF OBJECT_ID('Task', 'U') IS NOT NULL AND OBJECT_ID('Task_old', 'U') IS NULL
  exec sp_rename 'Task', 'Task_old' 

PRINT N'Renaming table Task to Task_old'
GO 

-------------------------------------------------------------------------------------- 
  IF OBJECT_ID('FKJobHistory287492') IS NOT NULL 
  ALTER TABLE JobHistory  DROP CONSTRAINT FKJobHistory287492
  IF OBJECT_ID('FKTaskChange331367') IS NOT NULL 
  ALTER TABLE TaskChanges DROP CONSTRAINT FKTaskChange331367
  IF OBJECT_ID('FKJob331365') IS NOT NULL 
  ALTER TABLE Job		  DROP CONSTRAINT FKJob331365
  IF OBJECT_ID('FKTaskHistory287490') IS NOT NULL 
  ALTER TABLE TaskHistory		  DROP CONSTRAINT FKTaskHistory287490
  IF OBJECT_ID('PK_Task') IS NOT NULL 
  ALTER TABLE task_old	  DROP CONSTRAINT   [PK_Task] 

  PRINT N'Dropping  constraint on task_old...';
GO
---------------------------------------------------------------------------------------
IF OBJECT_ID('Task', 'U') IS NULL 
CREATE TABLE [dbo].[Task](
	[Id] [uniqueidentifier] NOT NULL,
	[TaskGroupId] [uniqueidentifier] NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Configuration] [nvarchar](max) NULL,
	[TaskType] [nvarchar](255) NOT NULL,
	[TableName] [nvarchar](128) NOT NULL,
	[IsActive] [tinyint] NULL,
	[TenantId] [uniqueidentifier] NOT NULL,
	[CleanupTable] [tinyint] NULL,
	[CommandTimeout] [int] NULL,
	[ConnectorId] [uniqueidentifier] NULL,
	[Cron] [nvarchar](1024) NULL,
	[IgnoreDuplicates] [tinyint] NULL,
	[IsDeleted] [tinyint] NOT NULL,
	[JobConfiguration] [nvarchar](max) NULL,
	[QueueBehaviour] [nvarchar](24) NOT NULL,
	[StartDate] [datetime] NULL,
	[TargetHost] [nvarchar](1024) NULL,
	[TargetType] [nvarchar](255) NOT NULL)

PRINT N'Creating [dbo].[Task]...';
GO
---------------------------------------------------------------------------------------
 

IF OBJECT_ID('Task', 'U') IS NOT NULL AND OBJECT_ID('Task_old', 'U') IS NOT NULL AND NOT EXISTS (SELECT * FROM Task)
INSERT INTO Task
SELECT  Task_old.Id , 
		Task_old.TaskGroupId,
		Task_old.[Name],
		Task_old.[Configuration],
		Task_old.TaskType,
		Task_old.TableName,
		ISNULL(job.IsActive, 0),
		Task_old.TenantId, 
        ISNULL(Job.CleanupTable, 1) ,               
		ISNULL(Job.CommandTimeout,	0),
		Job.ConnectorId,
		Job.Cron, 
		ISNULL(Job.IgnoreDuplicates, 0),
		ISNULL(Task_old.IsActive, 0) ^ 1,
		ISNULL(Job.[Configuration], ''),
		ISNULL(Job.QueueBehaviour,'Wait'),
		Job.StartDate,
		Job.TargetHost, 
		ISNULL(Job.TargetType,'REST')
FROM       Task_old 
LEFT JOIN Job ON Task_old.Id = Job.TaskId
GO
---------------------------------------------------------------------------------------
IF OBJECT_ID('TaskHistory', 'U') IS NULL 
CREATE TABLE [dbo].[TaskHistory](
	[Id] [uniqueidentifier] NOT NULL,
	[TaskId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NULL,
	[TaskStatus] [nvarchar](24) NOT NULL,
	[Progress] [real] NULL,
	[LastUpdated] [datetime] NULL,
	[PlannedStartDate] [datetime] NOT NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[ErrorMessage] [nvarchar](max) NULL,
	[TenantId] [uniqueidentifier] NOT NULL)

PRINT N'Creating [dbo].[TaskHistory]...';
GO

IF OBJECT_ID('Task', 'U') IS NOT NULL AND OBJECT_ID('TaskHistory', 'U') IS NOT NULL 
INSERT INTO TaskHistory
SELECT JobHistory.[Id]
      ,Job.[TaskId]
      ,JobHistory.[UserId]
      ,JobHistory.[JobStatus]
      ,JobHistory.[Progress]
      ,JobHistory.[LastUpdated]
      ,JobHistory.[PlannedStartDate]
      ,JobHistory.[StartDate]
      ,JobHistory.[EndDate]
      ,JobHistory.[ErrorMessage]
      ,JobHistory.[TenantId]
  FROM    JobHistory INNER JOIN
          Job ON JobHistory.JobId = Job.Id 
GO
------------------------------------------------------------------------------------


IF OBJECT_ID('Task', 'U') IS NOT NULL AND OBJECT_ID('Task_old', 'U') IS NOT NULL 
DROP TABLE dbo.[Task_old]
PRINT N'Dropping table [Task_old]...'
GO
---------------------------------------------------------------------------------------

IF OBJECT_ID('TaskHistory', 'U') IS NOT NULL AND OBJECT_ID('JobHistory', 'U') IS NOT NULL 
DROP TABLE dbo.JobHistory
PRINT N'Dropping table [TaskHistory]...'
GO
---------------------------------------------------------------------------------------
PRINT N'Dropping FK_EmailConfiguration_EmailTemplate_EmailTemplateId on [dbo].[EmailConfiguration]...';

GO
IF OBJECT_ID('FK_EmailConfiguration_EmailTemplate_EmailTemplateId') IS NOT NULL 
ALTER TABLE [EmailConfiguration]  DROP CONSTRAINT [FK_EmailConfiguration_EmailTemplate_EmailTemplateId]
GO

PRINT N'Dropping FK_EmailReportAssignment_EmailConfiguration_EmailConfigurationId on [dbo].[EmailReportAssignment]...';

GO
IF OBJECT_ID('FK_EmailReportAssignment_EmailConfiguration_EmailConfigurationId') IS NOT NULL
ALTER TABLE [EmailReportAssignment] DROP CONSTRAINT [FK_EmailReportAssignment_EmailConfiguration_EmailConfigurationId]
GO

PRINT N'Dropping FK_EmailUserAssignment_EmailConfiguration_EmailConfigurationId on [dbo].[EmailUserAssignment]...';

GO
IF OBJECT_ID('FK_EmailUserAssignment_EmailConfiguration_EmailConfigurationId') IS NOT NULL 
ALTER TABLE [EmailUserAssignment] DROP CONSTRAINT [FK_EmailUserAssignment_EmailConfiguration_EmailConfigurationId]
GO

IF OBJECT_ID('EmailConfiguration', 'U') IS NOT NULL
DROP TABLE dbo.[EmailConfiguration]
PRINT N'Dropping table [EmailConfiguration]...'
GO
------------------

PRINT N'Adding FK_EmailReportAssignment_EmailNotification_EmailConfigurationId on [dbo].[EmailReportAssignment]...';

GO
IF OBJECT_ID('FK_EmailReportAssignment_EmailNotification_EmailConfigurationId') IS NULL
ALTER TABLE [dbo].[EmailReportAssignment]  WITH CHECK ADD  CONSTRAINT [FK_EmailReportAssignment_EmailNotification_EmailConfigurationId] FOREIGN KEY([EmailConfigurationId])
REFERENCES [dbo].[EmailNotification] ([Id])
GO

PRINT N'Adding FK_EmailUserAssignment_EmailNotification_EmailConfigurationId on [dbo].[EmailUserAssignment]...';

GO
IF OBJECT_ID('FK_EmailUserAssignment_EmailNotification_EmailConfigurationId') IS NULL
ALTER TABLE [dbo].[EmailUserAssignment]  WITH CHECK ADD  CONSTRAINT [FK_EmailUserAssignment_EmailNotification_EmailConfigurationId] FOREIGN KEY([EmailConfigurationId])
REFERENCES [dbo].[EmailNotification] ([Id])
GO

---------------------------------------------------------------------------------------



PRINT N'Dropping unnamed constraint on [dbo].[Download]...';

GO
DECLARE @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects WHERE name LIKE 'DF__Download__Id%' and type = 'D'
IF @name IS NOT NULL EXEC('ALTER TABLE [dbo].[Download] DROP CONSTRAINT ' + @name)


GO
PRINT N'Dropping unnamed constraint on [dbo].[Job]...';


GO
DECLARE @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects WHERE name LIKE 'DF__Job__Id__%' and type = 'D'
IF @name IS NOT NULL EXEC('ALTER TABLE [dbo].[Job] DROP CONSTRAINT ' + @name)


GO
PRINT N'Dropping unnamed constraint on [dbo].[Job]...';


GO
DECLARE @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects WHERE name LIKE 'DF__Job__CleanupTab%' and type = 'D'
IF @name IS NOT NULL EXEC('ALTER TABLE [dbo].[Job] DROP CONSTRAINT ' + @name)


GO
PRINT N'Dropping unnamed constraint on [dbo].[Job]...';


GO
DECLARE @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects WHERE name LIKE 'DF__Job__IgnoreDupli__%' and type = 'D'
IF @name IS NOT NULL EXEC('ALTER TABLE [dbo].[Job] DROP CONSTRAINT ' + @name)


GO
PRINT N'Dropping unnamed constraint on [dbo].[JobGroup]...';


GO
DECLARE @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects WHERE name LIKE 'DF__JobGroup__Id%' and type = 'D'
IF @name IS NOT NULL EXEC('ALTER TABLE [dbo].[JobGroup] DROP CONSTRAINT ' + @name)


GO
PRINT N'Dropping [dbo].[FKJobGroup0175585]...';


GO
ALTER TABLE [dbo].[Job] DROP CONSTRAINT [FKJobGroup0175585];


GO
PRINT N'Dropping [dbo].[FKJobGroup0175584]...';


GO
ALTER TABLE [dbo].[JobGroup] DROP CONSTRAINT [FKJobGroup0175584];


GO
PRINT N'Dropping [dbo].[FKTenant460376]...';


GO
ALTER TABLE [dbo].[Download] DROP CONSTRAINT [FKTenant460376];


GO
PRINT N'Dropping [dbo].[FKJob520887]...';


GO
ALTER TABLE [dbo].[Job] DROP CONSTRAINT [FKJob520887];


GO
PRINT N'Dropping [dbo].[FKTenant520888]...';


GO
ALTER TABLE [dbo].[Job] DROP CONSTRAINT [FKTenant520888];


GO
PRINT N'Dropping [dbo].[FKTaskGroup0175583]...';


GO
ALTER TABLE [dbo].[TaskGroup] DROP CONSTRAINT [FKTaskGroup0175583];


GO
PRINT N'Dropping [dbo].[AK_ReportingObject_Name_TenantId]...';


GO
ALTER TABLE [dbo].[ReportingObject] DROP CONSTRAINT [AK_ReportingObject_Name_TenantId];


GO
PRINT N'Dropping [dbo].[AK_User_UserName]...';


GO
ALTER TABLE [dbo].[User] DROP CONSTRAINT [AK_User_UserName];


GO
PRINT N'Dropping [dbo].[Download]...';


GO
DROP TABLE [dbo].[Download];


GO
PRINT N'Dropping [dbo].[Job]...';


GO
DROP TABLE [dbo].[Job];


GO
PRINT N'Dropping [dbo].[JobGroup]...';


GO
DROP TABLE [dbo].[JobGroup];


GO
PRINT N'Altering [dbo].[Configuration]...';


GO
ALTER TABLE [dbo].[Configuration] ALTER COLUMN [Value] NVARCHAR (2048) NULL;


GO
PRINT N'Altering [dbo].[ReportingObject]...';


GO
ALTER TABLE [dbo].[ReportingObject] ALTER COLUMN [Name] NVARCHAR (255) NULL;


GO
ALTER TABLE [dbo].[ReportingObject]
    ADD [SortingNumber] INT NULL;


GO
PRINT N'Altering [dbo].[User]...';


GO
ALTER TABLE [dbo].[User]
    ADD [Name]    NVARCHAR (255) NULL,
        [Surname] NVARCHAR (255) NULL;


GO
PRINT N'Creating [dbo].[User].[IX_User_UserName]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_UserName]
    ON [dbo].[User]([UserName] ASC);


GO
PRINT N'Creating [dbo].[CollectorConfiguration]...';


GO
CREATE TABLE [dbo].[CollectorConfiguration] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [Name]      NVARCHAR (255)   NOT NULL,
    [IsEnabled] BIT              NULL,
    [License]   NVARCHAR (MAX)   NULL,
    [TenantId]  UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Collector] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[CollectorConfiguration].[IX_CollectorConfiguration_TenantId]...';


GO
CREATE NONCLUSTERED INDEX [IX_CollectorConfiguration_TenantId]
    ON [dbo].[CollectorConfiguration]([TenantId] ASC);


GO
PRINT N'Creating [dbo].[ExtendedLicense]...';


GO
CREATE TABLE [dbo].[ExtendedLicense] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [TenantId]  UNIQUEIDENTIFIER NOT NULL,
    [Activated] DATETIME         NOT NULL,
    [License]   NVARCHAR (MAX)   NOT NULL,
    CONSTRAINT [PK_ExtendedLicense] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[ExtendedLicense].[IX_ExtendedLicense_TenantId]...';


GO
CREATE NONCLUSTERED INDEX [IX_ExtendedLicense_TenantId]
    ON [dbo].[ExtendedLicense]([TenantId] ASC);


GO
PRINT N'Creating [dbo].[Variable]...';


GO
CREATE TABLE [dbo].[Variable] (
    [Id]           UNIQUEIDENTIFIER NOT NULL,
    [TenantId]     UNIQUEIDENTIFIER NOT NULL,
    [CreationDate] DATETIME         NULL,
    [Key]          NVARCHAR (255)   NOT NULL,
    [Value]        NVARCHAR (255)   NULL,
    [IsProtected]  TINYINT          NOT NULL,
    CONSTRAINT [PK_Variable] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [AK_Variable_Key_TenantId] UNIQUE NONCLUSTERED ([Key] ASC, [TenantId] ASC)
);


GO
PRINT N'Creating [dbo].[Variable].[IX_Variable_TenantId]...';


GO
CREATE NONCLUSTERED INDEX [IX_Variable_TenantId]
    ON [dbo].[Variable]([TenantId] ASC);


GO
PRINT N'Creating [dbo].[PK_Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED ([Id] ASC);


GO
PRINT N'Creating [dbo].[AK_Task_Name_TenantId]...';


GO
ALTER TABLE [dbo].[Task]
    ADD CONSTRAINT [AK_Task_Name_TenantId] UNIQUE NONCLUSTERED ([Name] ASC, [TenantId] ASC);


GO
PRINT N'Creating [dbo].[AK_Task_TableName_TenantId]...';


GO
ALTER TABLE [dbo].[Task]
    ADD CONSTRAINT [AK_Task_TableName_TenantId] UNIQUE NONCLUSTERED ([TableName] ASC, [TenantId] ASC);


GO
PRINT N'Creating [dbo].[Task].[IX_Task_ConnectorId]...';


GO
CREATE NONCLUSTERED INDEX [IX_Task_ConnectorId]
    ON [dbo].[Task]([ConnectorId] ASC);


GO
PRINT N'Creating [dbo].[Task].[IX_Task_TaskGroupId]...';


GO
CREATE NONCLUSTERED INDEX [IX_Task_TaskGroupId]
    ON [dbo].[Task]([TaskGroupId] ASC);


GO
PRINT N'Creating [dbo].[Task].[IX_Task_TenantId]...';


GO
CREATE NONCLUSTERED INDEX [IX_Task_TenantId]
    ON [dbo].[Task]([TenantId] ASC);


GO
PRINT N'Creating [dbo].[PK_TaskHistory]...';


GO
ALTER TABLE [dbo].[TaskHistory]
    ADD CONSTRAINT [PK_TaskHistory] PRIMARY KEY CLUSTERED ([Id] ASC);


GO
PRINT N'Creating [dbo].[TaskHistory].[IX_TaskHistory_TaskId]...';


GO
CREATE NONCLUSTERED INDEX [IX_TaskHistory_TaskId]
    ON [dbo].[TaskHistory]([TaskId] ASC);


GO
PRINT N'Creating [dbo].[TaskHistory].[IX_TaskHistory_TenantId]...';


GO
CREATE NONCLUSTERED INDEX [IX_TaskHistory_TenantId]
    ON [dbo].[TaskHistory]([TenantId] ASC);


GO
PRINT N'Creating [dbo].[TaskHistory].[IX_TaskHistory_UserId]...';


GO
CREATE NONCLUSTERED INDEX [IX_TaskHistory_UserId]
    ON [dbo].[TaskHistory]([UserId] ASC);


GO
PRINT N'Creating unnamed constraint on [dbo].[CollectorConfiguration]...';


GO
ALTER TABLE [dbo].[CollectorConfiguration]
    ADD DEFAULT (newsequentialid()) FOR [Id];


GO
PRINT N'Creating unnamed constraint on [dbo].[CollectorConfiguration]...';


GO
ALTER TABLE [dbo].[CollectorConfiguration]
    ADD DEFAULT (CONVERT([bit],(1))) FOR [IsEnabled];


GO
PRINT N'Creating unnamed constraint on [dbo].[ExtendedLicense]...';


GO
ALTER TABLE [dbo].[ExtendedLicense]
    ADD DEFAULT (newsequentialid()) FOR [Id];


GO
PRINT N'Creating unnamed constraint on [dbo].[Variable]...';


GO
ALTER TABLE [dbo].[Variable]
    ADD DEFAULT (newsequentialid()) FOR [Id];


GO
PRINT N'Creating unnamed constraint on [dbo].[Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD DEFAULT (newsequentialid()) FOR [Id];


GO
PRINT N'Creating unnamed constraint on [dbo].[Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD DEFAULT (CONVERT([tinyint],(0))) FOR [IsDeleted];


GO
PRINT N'Creating unnamed constraint on [dbo].[Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD DEFAULT (N'') FOR [QueueBehaviour];


GO
PRINT N'Creating unnamed constraint on [dbo].[Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD DEFAULT (N'') FOR [TargetType];


GO
PRINT N'Creating unnamed constraint on [dbo].[Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD DEFAULT (CONVERT([tinyint],(0))) FOR [CleanupTable];


GO
PRINT N'Creating unnamed constraint on [dbo].[Task]...';


GO
ALTER TABLE [dbo].[Task]
    ADD DEFAULT (CONVERT([tinyint],(0))) FOR [IgnoreDuplicates];


GO
PRINT N'Creating unnamed constraint on [dbo].[TaskHistory]...';


GO
ALTER TABLE [dbo].[TaskHistory]
    ADD DEFAULT (newsequentialid()) FOR [Id];


GO
PRINT N'Creating [dbo].[FKTenant460377]...';


GO
ALTER TABLE [dbo].[CollectorConfiguration] WITH NOCHECK
    ADD CONSTRAINT [FKTenant460377] FOREIGN KEY ([TenantId]) REFERENCES [dbo].[Tenant] ([Id]);


GO
PRINT N'Creating [dbo].[FKTenant460378]...';


GO
ALTER TABLE [dbo].[ExtendedLicense] WITH NOCHECK
    ADD CONSTRAINT [FKTenant460378] FOREIGN KEY ([TenantId]) REFERENCES [dbo].[Tenant] ([Id]);


GO
PRINT N'Creating [dbo].[FKTenant921999]...';


GO
ALTER TABLE [dbo].[Variable] WITH NOCHECK
    ADD CONSTRAINT [FKTenant921999] FOREIGN KEY ([TenantId]) REFERENCES [dbo].[Tenant] ([Id]);


GO
PRINT N'Creating [dbo].[FKTask520887]...';


GO
ALTER TABLE [dbo].[Task] WITH NOCHECK
    ADD CONSTRAINT [FKTask520887] FOREIGN KEY ([ConnectorId]) REFERENCES [dbo].[Connector] ([Id]);


GO
PRINT N'Creating [dbo].[FKTaskGroup0175585]...';


GO
ALTER TABLE [dbo].[Task] WITH NOCHECK
    ADD CONSTRAINT [FKTaskGroup0175585] FOREIGN KEY ([TaskGroupId]) REFERENCES [dbo].[TaskGroup] ([Id]);


GO
PRINT N'Creating [dbo].[FKTaskChange331367]...';


GO
ALTER TABLE [dbo].[TaskChanges] WITH NOCHECK
    ADD CONSTRAINT [FKTaskChange331367] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task] ([Id]);


GO
PRINT N'Creating [dbo].[FKTaskGroup0175584]...';


GO
ALTER TABLE [dbo].[TaskGroup] WITH NOCHECK
    ADD CONSTRAINT [FKTaskGroup0175584] FOREIGN KEY ([ParentGroupId]) REFERENCES [dbo].[TaskGroup] ([Id]);


GO
PRINT N'Creating [dbo].[FKTaskHistory287490]...';


GO
ALTER TABLE [dbo].[TaskHistory] WITH NOCHECK
    ADD CONSTRAINT [FKTaskHistory287490] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task] ([Id]);


GO
PRINT N'Creating [dbo].[FKTaskHistory287491]...';


GO
ALTER TABLE [dbo].[TaskHistory] WITH NOCHECK
    ADD CONSTRAINT [FKTaskHistory287491] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]);


GO
PRINT N'Creating [dbo].[FKTenant287493]...';


GO
ALTER TABLE [dbo].[TaskHistory] WITH NOCHECK
    ADD CONSTRAINT [FKTenant287493] FOREIGN KEY ([TenantId]) REFERENCES [dbo].[Tenant] ([Id]);


GO
PRINT N'Checking existing data against newly created constraints';
 

GO
ALTER TABLE [dbo].[CollectorConfiguration] WITH CHECK CHECK CONSTRAINT [FKTenant460377];

ALTER TABLE [dbo].[ExtendedLicense] WITH CHECK CHECK CONSTRAINT [FKTenant460378];

ALTER TABLE [dbo].[Variable] WITH CHECK CHECK CONSTRAINT [FKTenant921999];

ALTER TABLE [dbo].[Task] WITH CHECK CHECK CONSTRAINT [FKTask520887];

ALTER TABLE [dbo].[Task] WITH CHECK CHECK CONSTRAINT [FKTaskGroup0175585];

ALTER TABLE [dbo].[TaskChanges] WITH CHECK CHECK CONSTRAINT [FKTaskChange331367];

ALTER TABLE [dbo].[TaskGroup] WITH CHECK CHECK CONSTRAINT [FKTaskGroup0175584];

ALTER TABLE [dbo].[TaskHistory] WITH CHECK CHECK CONSTRAINT [FKTaskHistory287490];

ALTER TABLE [dbo].[TaskHistory] WITH CHECK CHECK CONSTRAINT [FKTaskHistory287491];

ALTER TABLE [dbo].[TaskHistory] WITH CHECK CHECK CONSTRAINT [FKTenant287493];


GO

---version-12.2.2850---
ALTER TABLE [Variable] ALTER COLUMN [Value] nvarchar(max) null 
GO


---version-12.2.3242---
IF OBJECT_ID('AK_Task_Name_TenantId') IS NOT NULL
ALTER TABLE [dbo].[Task] DROP CONSTRAINT [AK_Task_Name_TenantId]
GO

---version-12.2.3288---
IF OBJECT_ID(N'[UserConfiguration]') IS NULL
BEGIN
CREATE TABLE [UserConfiguration] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [TenantId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [Property] nvarchar(255) NULL,
    [Value] nvarchar(2048) NULL,
    CONSTRAINT [PK_UserConfiguration] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Tenant_UserConfig] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_User_UserConfig] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]) ON DELETE NO ACTION
);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('UserConfiguration') AND name='IX_UserConfiguration_TenantId')
BEGIN
CREATE INDEX [IX_UserConfiguration_TenantId] ON [UserConfiguration] ([TenantId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('UserConfiguration') AND name='IX_UserConfiguration_UserId')
BEGIN
CREATE INDEX [IX_UserConfiguration_UserId] ON [UserConfiguration] ([UserId]);
END
GO

---version-12.2.3337---
IF OBJECT_ID(N'[Transformation]') IS NULL
BEGIN
CREATE TABLE [Transformation] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [TenantId] uniqueidentifier NOT NULL,
    [Name] nvarchar(255) NULL,
    [Description] nvarchar(255) NULL,
    [JsonEtlDefinition] nvarchar(max) NULL,
    [SqlMacros] nvarchar(max) NULL,
    [IsDeleted] tinyint NOT NULL,
    CONSTRAINT [PK_Transformation] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Tenant_Transformations] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE NO ACTION
);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('Transformation') AND name='IX_Transformation_TenantId')
BEGIN
CREATE INDEX [IX_Transformation_TenantId] ON [Transformation] ([TenantId]);
END
GO

--version-12.2.3242---
--MIGRATE Report (ComponentStorage)--

DECLARE @MyCursor CURSOR;
DECLARE @storageId VARCHAR(255);
DECLARE @TableBase64 table(encoded_base64_original VARCHAR(max), storageId VARCHAR(255));
DECLARE @TableTableNamesOfDecodedBase64 table(encoded_base64_original VARCHAR(max), decoded_base64 XML, detected_tablenames VARCHAR(MAX),storageId NVARCHAR(max));
DECLARE @NewDelimiter VARCHAR(1) = '-';

BEGIN
    SET @MyCursor = CURSOR FOR
    select StorageId from ReportingObject where [Type] LIKE 'Report'

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @storageId

    WHILE @@FETCH_STATUS = 0
    BEGIN
----------------------------CURSOR LOOP START (loops through all xmlstorages of type report)------------------------------------
	
--Get all base64 strings of a single Report
insert into @TableBase64

select base64strings.encoded_base64,
	   @storageId
	FROM
	(	
		--Select Base64 attribute from ItemX--
		select base.items.value('.', 'varchar(max)') as [encoded_base64]
		from XmlStorage as s outer apply s.Content.nodes('/XtraReportsLayoutSerializer/ComponentStorage/*/@Base64') as base(items) where [name] like @storageId
	) as base64strings

------------------------------------------------------------------------- 
IF OBJECT_ID(N'tempdb..#tempDistinctBase64') IS NOT NULL
	BEGIN
		DROP TABLE #tempDistinctBase64
	END

select distinct encoded_base64_original into 
#tempDistinctBase64
From @TableBase64 where encoded_base64_original IS NOT NULL

Declare @currEncodedBase64 VARCHAR(MAX);
Declare @currDecodedBase64 XML;
Declare @newEncodedBase64 VARCHAR(MAX);

--Iterate over each found base64 and find all its tablenames
IF (SELECT COUNT(*) FROM #tempDistinctBase64) != 0
BEGIN
	While EXISTS(select 1 from #tempDistinctBase64)
	BEGIN
		select top 1 @currEncodedBase64 = encoded_base64_original from #tempDistinctBase64
		--Decode the base64 string of the Base64 attribute --
		SELECT top 1 @currDecodedBase64 = CONVERT(XML, CAST('' AS XML).value('xs:base64Binary(sql:column("[encoded_base64_original]"))', 'VARBINARY(MAX)')) from #tempDistinctBase64

		--Contains all tablenames per DecodedBase64
		 insert into @TableTableNamesOfDecodedBase64

		select  @currEncodedBase64,
				@currDecodedBase64,	
				tbls.items.value('.', 'varchar(max)'),
				@storageId
		
		FROM #tempDistinctBase64 outer apply @currDecodedBase64.nodes('SqlDataSource/Query/Tables/*/@Name') as tbls(items)
	
		--select * from @TableTableNamesOfDecodedBase64 --TODO SELECT only for debug purposes

		--For each found tablename do a replace on the decoded base64 string
		IF OBJECT_ID(N'tempdb..#TempTableTableNamesOfDecodedBase64') IS NOT NULL
			BEGIN
				DROP TABLE #TempTableTableNamesOfDecodedBase64
			END

		select distinct detected_tablenames into 
		#TempTableTableNamesOfDecodedBase64
		From @TableTableNamesOfDecodedBase64 where detected_tablenames IS NOT NULL AND detected_tablenames LIKE 'dbo.%.%' OR (detected_tablenames LIKE '%.%' AND detected_tablenames NOT LIKE 'dbo.%.%')--TODO Regex to detect tblnames with dbo.%.% or %.%
		
		--select * from #TempTableTableNamesOfDecodedBase64
		
		Declare @originaCurrlTblName varchar(max);
		Declare @currTblNameReplacement varchar(max);

		IF (SELECT COUNT(*) FROM #TempTableTableNamesOfDecodedBase64) != 0
		BEGIN
			While EXISTS(select 1 from #TempTableTableNamesOfDecodedBase64)
			BEGIN
				select top 1 @originaCurrlTblName = detected_tablenames from #TempTableTableNamesOfDecodedBase64
				set @currTblNameReplacement = @originaCurrlTblName

				set @currTblNameReplacement = REPLACE(@currTblNameReplacement, '.', @NewDelimiter)
				set @currTblNameReplacement = REPLACE(@currTblNameReplacement, 'dbo' + @NewDelimiter, '')

				set @currDecodedBase64 = CAST(REPLACE(CAST(@currDecodedBase64 AS VARCHAR(MAX)),'"' + @originaCurrlTblName + '"', '"' + @currTblNameReplacement + '"') AS XML)

				DELETE FROM #TempTableTableNamesOfDecodedBase64 where detected_tablenames = @originaCurrlTblName
			END
		END

		
		IF @currTblNameReplacement IS NOT NULL
		BEGIN
			--select @currDecodedBase64 as decoded_replacementsDone --TODO SELECT only for debug purposes
		
			set @newEncodedBase64 = (SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:column("bin"))' , 'VARCHAR(MAX)') 
			FROM (
				SELECT CAST(CAST(@currDecodedBase64 AS varchar(max)) AS VARBINARY(MAX)) AS bin
		   ) as RetVal )
		
			select @currDecodedBase64, @currEncodedBase64, @newEncodedBase64  --TODO SELECT only for debug purposes

			DECLARE @text xml;
			select @text = Content from XmlStorage Where [Name] LIKE @storageId
			set @text.modify('replace value of (/XtraReportsLayoutSerializer/ComponentStorage/*[@Base64 eq sql:variable("@currEncodedBase64")]/@Base64)[1] with sql:variable("@newEncodedBase64")')
			update XmlStorage set Content = @text Where [Name] LIKE @storageId
		END


		DELETE FROM #tempDistinctBase64 where encoded_base64_original = @currEncodedBase64
	END
END
--select Content, [name], @currEncodedBase64 as currenc, @newEncodedBase64 as newenc from XmlStorage where [Name] LIKE @storageId --TODO SELECT only for debug purposes
--clean tables for next rpa 
DELETE FROM @TableBase64
DELETE FROM @TableTableNamesOfDecodedBase64
SET @currTblNameReplacement = null

----------------------------CURSOR LOOP END------------------------------------
      FETCH NEXT FROM @MyCursor 
      INTO @storageId 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

--DECLARE @MyCursor CURSOR;
--DECLARE @storageId VARCHAR(255);
--DECLARE @TableBase64 table(encoded_base64_original VARCHAR(max), storageId VARCHAR(255));
--DECLARE @TableTableNamesOfDecodedBase64 table(encoded_base64_original VARCHAR(max), decoded_base64 XML, detected_tablenames VARCHAR(MAX),storageId NVARCHAR(max));

--MIGRATE Report (ObjectStorage)--
BEGIN
    SET @MyCursor = CURSOR FOR
    select StorageId from ReportingObject where [Type] LIKE 'Report'

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @storageId

    WHILE @@FETCH_STATUS = 0
    BEGIN
----------------------------CURSOR LOOP START (loops through all xmlstorages of type report)------------------------------------
	
--Get all base64 strings of a single Report
insert into @TableBase64

select base64strings.encoded_base64,
	   @storageId
	FROM
	(	
		--Select Base64 attribute from ItemX--
		select base.items.value('.', 'varchar(max)') as [encoded_base64]
		from XmlStorage as s outer apply s.Content.nodes('/XtraReportsLayoutSerializer/ObjectStorage/*/@Base64') as base(items) where [name] like @storageId --TODO same for ObjectStorage
	) as base64strings

------------------------------------------------------------------------- 
IF OBJECT_ID(N'tempdb..#tempDistinctBase64ObjectStorage') IS NOT NULL
	BEGIN
		DROP TABLE #tempDistinctBase64ObjectStorage
	END

select distinct encoded_base64_original into 
#tempDistinctBase64ObjectStorage
From @TableBase64 where encoded_base64_original IS NOT NULL

--Declare @currEncodedBase64 VARCHAR(MAX);
--Declare @currDecodedBase64 XML;
--Declare @newEncodedBase64 VARCHAR(MAX);

--Iterate over each found base64 and find all its tablenames
IF (SELECT COUNT(*) FROM #tempDistinctBase64ObjectStorage) != 0
BEGIN
	While EXISTS(select 1 from #tempDistinctBase64ObjectStorage)
	BEGIN
		select top 1 @currEncodedBase64 = encoded_base64_original from #tempDistinctBase64ObjectStorage
		--Decode the base64 string of the Base64 attribute --
		SELECT top 1 @currDecodedBase64 = CONVERT(XML, CAST('' AS XML).value('xs:base64Binary(sql:column("[encoded_base64_original]"))', 'VARBINARY(MAX)')) from #tempDistinctBase64

		--Contains all tablenames per DecodedBase64
		 insert into @TableTableNamesOfDecodedBase64

		select  @currEncodedBase64,
				@currDecodedBase64,	
				tbls.items.value('.', 'varchar(max)'),
				@storageId
		
		FROM #tempDistinctBase64ObjectStorage outer apply @currDecodedBase64.nodes('SqlDataSource/Query/Tables/*/@Name') as tbls(items)
	
		--select * from @TableTableNamesOfDecodedBase64 --TODO SELECT only for debug purposes

		--For each found tablename do a replace on the decoded base64 string
		IF OBJECT_ID(N'tempdb..#TempTableTableNamesOfDecodedBase64ObjectStorage') IS NOT NULL
			BEGIN
				DROP TABLE #TempTableTableNamesOfDecodedBase64ObjectStorage
			END

		select distinct detected_tablenames into 
		#TempTableTableNamesOfDecodedBase64ObjectStorage
		From @TableTableNamesOfDecodedBase64 where detected_tablenames IS NOT NULL AND detected_tablenames LIKE 'dbo.%.%' OR (detected_tablenames LIKE '%.%' AND detected_tablenames NOT LIKE 'dbo.%.%')--TODO Regex to detect tblnames with dbo.%.% or %.%
		
		--select * from #TempTableTableNamesOfDecodedBase64
		
--		Declare @originaCurrlTblName varchar(max);
--		Declare @currTblNameReplacement varchar(max);

		IF (SELECT COUNT(*) FROM #TempTableTableNamesOfDecodedBase64ObjectStorage) != 0
		BEGIN
			While EXISTS(select 1 from #TempTableTableNamesOfDecodedBase64ObjectStorage)
			BEGIN
				select top 1 @originaCurrlTblName = detected_tablenames from #TempTableTableNamesOfDecodedBase64ObjectStorage
				set @currTblNameReplacement = @originaCurrlTblName

				set @currTblNameReplacement = REPLACE(@currTblNameReplacement, '.', @NewDelimiter)
				set @currTblNameReplacement = REPLACE(@currTblNameReplacement, 'dbo' + @NewDelimiter, '')

				set @currDecodedBase64 = CAST(REPLACE(CAST(@currDecodedBase64 AS VARCHAR(MAX)),'"' + @originaCurrlTblName + '"', '"' + @currTblNameReplacement + '"') AS XML)

				DELETE FROM #TempTableTableNamesOfDecodedBase64ObjectStorage where detected_tablenames = @originaCurrlTblName
			END
		END

		
		IF @currTblNameReplacement IS NOT NULL
		BEGIN
			--select @currDecodedBase64 as decoded_replacementsDone --TODO SELECT only for debug purposes
		
			set @newEncodedBase64 = (SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:column("bin"))' , 'VARCHAR(MAX)') 
			FROM (
				SELECT CAST(CAST(@currDecodedBase64 AS varchar(max)) AS VARBINARY(MAX)) AS bin
		   ) as RetVal )
		
			--select @currDecodedBase64, @currEncodedBase64, @newEncodedBase64  --TODO SELECT only for debug purposes
			
			--DECLARE @text xml;
			select @text = Content from XmlStorage Where [Name] LIKE @storageId
			set @text.modify('replace value of (/XtraReportsLayoutSerializer/ObjectStorage/*[@Base64 eq sql:variable("@currEncodedBase64")]/@Base64)[1] with sql:variable("@newEncodedBase64")')
			update XmlStorage set Content = @text Where [Name] LIKE @storageId
		END


		DELETE FROM #tempDistinctBase64ObjectStorage where encoded_base64_original = @currEncodedBase64
	END
END
--select Content, [name], @currEncodedBase64 as currenc, @newEncodedBase64 as newenc from XmlStorage where [Name] LIKE @storageId --TODO SELECT only for debug purposes
--clean tables for next rpa 
DELETE FROM @TableBase64
DELETE FROM @TableTableNamesOfDecodedBase64
SET @currTblNameReplacement = null

----------------------------CURSOR LOOP END------------------------------------
      FETCH NEXT FROM @MyCursor 
      INTO @storageId 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

--DECLARE @MyCursor CURSOR;
--DECLARE @storageId VARCHAR(255);
DECLARE @TableTableNamesOfDecodedBase64Dashboard table(detected_tablenames VARCHAR(MAX),storageId NVARCHAR(max));
--MIGRATE Dashboard--
BEGIN
    SET @MyCursor = CURSOR FOR
    select StorageId from ReportingObject where [Type] LIKE 'Dashboard'

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @storageId

    WHILE @@FETCH_STATUS = 0
    BEGIN
----------------------------CURSOR LOOP START (loops through all xmlstorages of type dashboard)------------------------------------

	--Contains all tablenames per DecodedBase64
	insert into @TableTableNamesOfDecodedBase64Dashboard

	select  tbls.items.value('.', 'varchar(max)'),
			@storageId
		
	FROM XmlStorage outer apply Content.nodes('Dashboard/DataSources/SqlDataSource/Query/Tables/*/@Name') as tbls(items) where [name] LIKE @storageId
	
	--select * from @TableTableNamesOfDecodedBase64 --TODO SELECT only for debug purposes

	--For each found tablename do a replace on the decoded base64 string
	IF OBJECT_ID(N'tempdb..#TempTableTableNamesOfDecodedBase64Dashboard') IS NOT NULL
		BEGIN
			DROP TABLE #TempTableTableNamesOfDecodedBase64Dashboard
		END

	select distinct detected_tablenames into 
	#TempTableTableNamesOfDecodedBase64Dashboard
	From @TableTableNamesOfDecodedBase64Dashboard where detected_tablenames IS NOT NULL AND detected_tablenames LIKE 'dbo.%.%' OR (detected_tablenames LIKE '%.%' AND detected_tablenames NOT LIKE 'dbo.%.%')--TODO Regex to detect tblnames with dbo.%.% or %.%
		
	--select * from #TempTableTableNamesOfDecodedBase64
		
--	Declare @originaCurrlTblName varchar(max);
--	Declare @currTblNameReplacement varchar(max);
--	DECLARE @text xml;
	IF (SELECT COUNT(*) FROM #TempTableTableNamesOfDecodedBase64Dashboard) != 0
	BEGIN
		select @text = Content from XmlStorage Where [Name] LIKE @storageId
		While EXISTS(select 1 from #TempTableTableNamesOfDecodedBase64Dashboard)
		BEGIN
			select top 1 @originaCurrlTblName = detected_tablenames from #TempTableTableNamesOfDecodedBase64Dashboard
			set @currTblNameReplacement = @originaCurrlTblName

			set @currTblNameReplacement = REPLACE(@currTblNameReplacement, '.', @NewDelimiter)
			set @currTblNameReplacement = REPLACE(@currTblNameReplacement, 'dbo' + @NewDelimiter, '')
            
			set @text = CAST(REPLACE(CAST(@text AS VARCHAR(MAX)), '"' + @originaCurrlTblName + '"', '"' + @currTblNameReplacement + '"') AS XML)

			DELETE FROM #TempTableTableNamesOfDecodedBase64Dashboard where detected_tablenames = @originaCurrlTblName
		END
		update XmlStorage set Content = @text Where [Name] LIKE @storageId
		--select @text as modifiedText, @storageId --TODO Debug purpose print
END
--select Content, [name], @currEncodedBase64 as currenc, @newEncodedBase64 as newenc from XmlStorage where [Name] LIKE @storageId --TODO SELECT only for debug purposes
--clean tables for next rpa 

DELETE FROM @TableTableNamesOfDecodedBase64Dashboard
SET @currTblNameReplacement = null

----------------------------CURSOR LOOP END------------------------------------
      FETCH NEXT FROM @MyCursor 
      INTO @storageId 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;
---CLEAN UP---
IF OBJECT_ID(N'tempdb..#tempDistinctBase64') IS NOT NULL
	BEGIN
		DROP TABLE #tempDistinctBase64
	END
IF OBJECT_ID(N'tempdb..#TempTableTableNamesOfDecodedBase64') IS NOT NULL
    BEGIN
        DROP TABLE #TempTableTableNamesOfDecodedBase64
    END
IF OBJECT_ID(N'tempdb..#tempDistinctBase64ObjectStorage') IS NOT NULL
    BEGIN
	    DROP TABLE #tempDistinctBase64ObjectStorage
    END
IF OBJECT_ID(N'tempdb..#TempTableTableNamesOfDecodedBase64ObjectStorage') IS NOT NULL
	BEGIN
		DROP TABLE #TempTableTableNamesOfDecodedBase64ObjectStorage
	END
IF OBJECT_ID(N'tempdb..#TempTableTableNamesOfDecodedBase64Dashboard') IS NOT NULL
	BEGIN
		DROP TABLE #TempTableTableNamesOfDecodedBase64Dashboard
	END
GO
--version-12.2.3769---

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'TaskHistory' AND COLUMN_NAME = 'TaskLog')
BEGIN
	ALTER TABLE [TaskHistory] ADD [TaskLog] nvarchar(max) NULL;
END;


IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'ReportingObject' AND COLUMN_NAME = 'ReportingObjectState')
BEGIN
	ALTER TABLE [ReportingObject] ADD [ReportingObjectState] nvarchar(64) NULL DEFAULT N'Custom';
END;
GO
UPDATE ReportingObject SET ReportingObjectState = 'StandardEdited';
GO

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'Task' AND COLUMN_NAME = 'State')
BEGIN
	ALTER TABLE [Task] ADD [State] nvarchar(64) NULL DEFAULT N'Custom';
END;
GO
UPDATE Task SET [State] = 'StandardEdited';

GO

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'Transformation' AND COLUMN_NAME = 'State')
BEGIN
	ALTER TABLE [Transformation] ADD [State] nvarchar(64) NULL DEFAULT N'Custom';
END;
GO
UPDATE [Transformation] SET [State] = 'StandardEdited';
GO


--version-12.2.4004---
IF OBJECT_ID(N'[ReportingObjectMetaData]') IS NULL
BEGIN
CREATE TABLE [ReportingObjectMetaData] (
    [Id] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
    [ReportingObjectId] uniqueidentifier NOT NULL,
    [TableName] nvarchar(128) NOT NULL,
    [TenantId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_ReportingObjectMetaData] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_ReportingObjectMetaData_TableName_ReportingObjectId] UNIQUE ([TableName], [ReportingObjectId]),
    CONSTRAINT [FK_ReportingObject_ReportingObjectMetadata] FOREIGN KEY ([ReportingObjectId]) REFERENCES [ReportingObject] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_ReportingObjectMetaData_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant] ([Id]) ON DELETE CASCADE
);
END
GO


IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ReportingObjectMetaData') AND name='IX_ReportingObjectMetaData_ReportingObjectId')
BEGIN
CREATE INDEX [IX_ReportingObjectMetaData_ReportingObjectId] ON [ReportingObjectMetaData] ([ReportingObjectId]);
END
GO

IF NOT EXISTS(SELECT [index_id] FROM sys.indexes WHERE object_id = OBJECT_ID('ReportingObjectMetaData') AND name='IX_ReportingObjectMetaData_TenantId')
BEGIN
CREATE INDEX [IX_ReportingObjectMetaData_TenantId] ON [ReportingObjectMetaData] ([TenantId]);
END
GO

--version-12.2.4351---
GO
PRINT N'Altering [dbo].[Transformation]...';
GO
ALTER TABLE [dbo].[Transformation] ALTER COLUMN [Description] NVARCHAR (1024) NULL;


--version-12.2.4358---
GO
PRINT N'Update task type ''microsoft.m365''';
GO
UPDATE [dbo].[Task] SET TaskType = 'microsoft.m365' WHERE TaskType = 'microsoft.office365';

--version-12.3.4457.1438--
GO
PRINT N'Add column DeleteTable to Task table';
GO
IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'Task' AND COLUMN_NAME = 'DeleteTable')
BEGIN
	ALTER TABLE [Task] ADD [DeleteTable] tinyint NULL DEFAULT CAST(0 AS tinyint);
END;

--version-12.3.4464--
GO
		
INSERT INTO [dbo].[Transformation] (TenantId, Name, Description, JsonEtlDefinition, SqlMacros, IsDeleted, State)
		
SELECT [TenantId], [Name], [ID] 
,  REPLACE(REPLACE((SUBSTRING([Configuration],26,CHARINDEX('","sqlMacros":"', [Configuration])-26)),'\n',''), '\','')
,  REPLACE(REPLACE((SUBSTRING([Configuration],CHARINDEX('","sqlMacros":"', [Configuration])+15,LEN([Configuration])-CHARINDEX('","sqlMacros":"', [Configuration])-16)),'\n',''), '\','')
, [IsDeleted], [State]
FROM [dbo].[Task] WHERE TaskType = 'Transform' AND [Configuration] LIKE '{"transformationScript":%' AND [Configuration] LIKE '%","sqlMacros":"%'
UNION ALL
SELECT [TenantId], [Name], [ID] 
, REPLACE(REPLACE(REPLACE(REPLACE([Configuration],'{"transformationScript":"',''),'"}',''),'\n',''), '\','')
, ''
, [IsDeleted], [State]
FROM [dbo].[Task] WHERE TaskType = 'Transform' AND [Configuration] LIKE '{"transformationScript":%' AND [Configuration] NOT LIKE '%","sqlMacros":"%'

UPDATE  [Task]
SET [Task].[Configuration] = '{"Ids":["' + [Transformation].[Description] + '"],"Verbosity":"standard"}'
FROM [dbo].[Transformation] 
INNER JOIN [dbo].[Task] ON CONVERT(Nvarchar(36),[Task].Id) = [Transformation].[Description]

UPDATE  [Transformation] SET [Description] = NULL

--version-12.3.4519---
GO
PRINT N'Update task type ''microsoft.m365''';
GO
UPDATE [dbo].[Task] SET TaskType = 'microsoft.office365' WHERE TaskType = 'microsoft.m365';

--version-12.3.4520--

PRINT N'Add column LDAPDistinguishedName to UserGroup table';
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'UserGroup' AND COLUMN_NAME = 'LDAPDistinguishedName')
BEGIN
    ALTER TABLE [UserGroup] ADD [LDAPDistinguishedName] NVARCHAR (512) NULL;
END;

GO
PRINT N'Add column LDAPUserName to User table';
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'User' AND COLUMN_NAME = 'LDAPUserName')
BEGIN
    ALTER TABLE [User] ADD [LDAPUserName] NVARCHAR (512) NULL;
END;

GO
PRINT N'Delete all existing user tokens'
DELETE UserToken;
GO

--version-12.3.4521--
PRINT N'Altering table TaskHistory column [TaskLog]';

UPDATE [TaskHistory] SET TaskLog =
  CASE WHEN LEN([TaskLog]) < 2999 
    THEN 
        [TaskLog]
    ELSE 
        '[...]  ' + SUBSTRING([TaskLog], LEN([TaskLog])-2992, 2992)
END

GO
ALTER TABLE [dbo].[TaskHistory] ALTER COLUMN [TaskLog] NVARCHAR (3000) NULL;
GO

--version-12.3.4522--

PRINT N'Add column [LogAvailable] to [TaskHistory] table';
GO
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TaskHistory' AND COLUMN_NAME = 'LogAvailable')
BEGIN
    ALTER TABLE [dbo].[TaskHistory] ADD [LogAvailable] TINYINT DEFAULT (1);
END;