CREATE TABLE [Asset_Win32_EncryptableVolume] (
  [Win32_EncryptableVolumeGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [DeviceID] nvarchar(1000) NULL
, [PersistentVolumeID] nvarchar(1000) NULL
, [DriveLetter] nvarchar(1000) NULL
, [ProtectionStatus] int NULL
, [ConversionStatus] int NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
CREATE TABLE [Asset_Win32_Tpm] (
  [Win32_TpmGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [IsActivated] bit NULL
, [IsEnabled] bit NULL
, [IsOwned] bit NULL
, [SpecVersion] nvarchar(500) NULL
, [ManufacturerVersion] nvarchar(500) NULL
, [ManufacturerVersionInfo] nvarchar(500) NULL
, [ManufacturerId] bigint NULL
, [PhysicalPresenceVersionInfo] nvarchar(500) NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
CREATE TABLE [BitLockerSettings] (
  [DeviceGuid] uniqueidentifier NOT NULL
, [IsEnabled] bit DEFAULT ((0)) NOT NULL
, [RecoveryKey] nvarchar(100) NULL
, [EncryptUsedOnly] bit DEFAULT ((1)) NOT NULL
, [GenerateKey] bit DEFAULT ((1)) NOT NULL
, [DriveLetter] nchar(1) NOT NULL
, [SiteName] nvarchar(255) NOT NULL
, [DeviceName] nvarchar(255) NOT NULL
, [Modified] datetime NOT NULL
, [IVector] nvarchar(100) NULL
);
GO
ALTER TABLE [Asset_Win32_EncryptableVolume] ADD CONSTRAINT [Asset_Win32_EncryptableVolume_Win32_EncryptableVolumeGuid_PK] PRIMARY KEY ([Win32_EncryptableVolumeGuid]);
GO
ALTER TABLE [Asset_Win32_Tpm] ADD CONSTRAINT [Asset_Win32_Tpm_Win32_TpmGuid_PK] PRIMARY KEY ([Win32_TpmGuid]);
GO
ALTER TABLE [BitLockerSettings] ADD CONSTRAINT [PK_BitLockerSettings] PRIMARY KEY ([DeviceGuid]);
GO
CREATE INDEX [Asset_Win32_EncryptableVolume_DeviceGuid_IX] ON [Asset_Win32_EncryptableVolume] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_Win32_EncryptableVolume] ADD CONSTRAINT [Asset_Win32_EncryptableVolume_Win32_EncryptableVolumeGuid_UX] UNIQUE ([Win32_EncryptableVolumeGuid]);
GO
CREATE INDEX [Asset_Win32_Tpm_DeviceGuid_IX] ON [Asset_Win32_Tpm] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_Win32_Tpm] ADD CONSTRAINT [Asset_Win32_Tpm_Win32_TpmGuid_UX] UNIQUE ([Win32_TpmGuid]);
GO
ALTER TABLE [DeviceRegistryInfo] ADD [DisplayVersion] nvarchar(10) NULL;
GO

-- Add new column "FileBinaryHash", copy data from "FileBinaryMD5" to new column, drop "FileBinaryMD5"
ALTER TABLE [AutomatedTaskParameterFile] ADD [FileBinaryHash] nvarchar(64) NULL;
GO
UPDATE [AutomatedTaskParameterFile] SET [FileBinaryHash] = [FileBinaryMD5];
GO
ALTER TABLE [AutomatedTaskParameterFile] ALTER COLUMN [FileBinaryHash] nvarchar(64) NOT NULL;
GO
ALTER TABLE [AutomatedTaskParameterFile] DROP COLUMN [FileBinaryMD5];
GO

-- Add new column "FileHash", copy data from "MD5Hash" to new column, drop "MD5Hash"
ALTER TABLE [Script] ADD [FileHash] nvarchar(255) NULL;
GO
UPDATE [Script] SET [FileHash] = [MD5Hash];
GO
ALTER TABLE [Script] ALTER COLUMN [FileHash] nvarchar(255) NOT NULL;
GO
ALTER TABLE [Script] DROP COLUMN [MD5Hash];
GO
