PRINT N'Creating [dbo].[UpdateGuids_DTT]...';


GO
CREATE TYPE [dbo].[UpdateGuids_DTT] AS TABLE (
    [UpdateGuid] UNIQUEIDENTIFIER NOT NULL,
    PRIMARY KEY CLUSTERED ([UpdateGuid] ASC) WITH (IGNORE_DUP_KEY = ON));


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


GO
CREATE TABLE [dbo].[Asset_SMBios_MemoryDevice] (
    [SMBios_MemoryDeviceGuid] UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]              UNIQUEIDENTIFIER NOT NULL,
    [AssetTag]                NVARCHAR (100)   NULL,
    [Attributes]              TINYINT          NULL,
    [BankLocator]             NVARCHAR (100)   NULL,
    [DataWidth]               INT              NULL,
    [DeviceLocator]           NVARCHAR (100)   NULL,
    [DevSet]                  TINYINT          NULL,
    [Manufacturer]            NVARCHAR (100)   NULL,
    [MemType]                 TINYINT          NULL,
    [PartNumber]              NVARCHAR (100)   NULL,
    [SerialNumber]            NVARCHAR (100)   NULL,
    [Size]                    INT              NULL,
    [Speed]                   INT              NULL,
    [TotalWidth]              INT              NULL,
    [TypeDetails]             INT              NULL,
    [DateCreated]             DATETIME         NOT NULL,
    [DateModified]            DATETIME         NOT NULL,
    [OMRowVersion]            ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_SMBios_MemoryDevice_SMBios_MemoryDeviceGuid_PK] PRIMARY KEY NONCLUSTERED ([SMBios_MemoryDeviceGuid] ASC)
);


GO
PRINT N'Creating [dbo].[Asset_SMBios_MemoryDevice].[Asset_SMBios_MemoryDevice_DeviceGuid_IX]...';


GO
CREATE NONCLUSTERED INDEX [Asset_SMBios_MemoryDevice_DeviceGuid_IX]
    ON [dbo].[Asset_SMBios_MemoryDevice]([DeviceGuid] ASC);


GO
PRINT N'Creating [dbo].[Asset_SMBios_MemoryDevice].[Asset_SMBios_MemoryDevice_SMBios_MemoryDeviceGuid_UX]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [Asset_SMBios_MemoryDevice_SMBios_MemoryDeviceGuid_UX]
    ON [dbo].[Asset_SMBios_MemoryDevice]([SMBios_MemoryDeviceGuid] ASC);


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


GO
CREATE TABLE [dbo].[Asset_SMBios_Processor] (
    [SMBios_ProcessorGuid] UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]           UNIQUEIDENTIFIER NOT NULL,
    [AssetTag]             NVARCHAR (100)   NULL,
    [CoreCount]            TINYINT          NULL,
    [CoreEnabled]          TINYINT          NULL,
    [CpuNumber]            INT              NULL,
    [CurrentSpeed]         INT              NULL,
    [ExternalClock]        INT              NULL,
    [Manufacturer]         NVARCHAR (100)   NULL,
    [MaxSpeed]             INT              NULL,
    [ProcCharacteristics]  INT              NULL,
    [ProcessorFamily]      TINYINT          NULL,
    [ProcessorID]          BIGINT           NULL,
    [ProcessorType]        TINYINT          NULL,
    [ProcUpgrade]          TINYINT          NULL,
    [SerialNumber]         NVARCHAR (100)   NULL,
    [SocketDesignation]    NVARCHAR (100)   NULL,
    [Status]               TINYINT          NULL,
    [ThreadCount]          TINYINT          NULL,
    [Version]              NVARCHAR (100)   NULL,
    [Voltage]              TINYINT          NULL,
    [DateCreated]          DATETIME         NOT NULL,
    [DateModified]         DATETIME         NOT NULL,
    [OMRowVersion]         ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_SMBios_Processor_SMBios_Processor_PK] PRIMARY KEY NONCLUSTERED ([SMBios_ProcessorGuid] ASC)
);


GO
PRINT N'Creating [dbo].[Asset_SMBios_Processor].[Asset_SMBios_Processor_DeviceGuid_IX]...';


GO
CREATE NONCLUSTERED INDEX [Asset_SMBios_Processor_DeviceGuid_IX]
    ON [dbo].[Asset_SMBios_Processor]([DeviceGuid] ASC);


GO
PRINT N'Creating [dbo].[Asset_SMBios_Processor].[Asset_SMBios_Processor_SMBios_ProcessorGuid_UX]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [Asset_SMBios_Processor_SMBios_ProcessorGuid_UX]
    ON [dbo].[Asset_SMBios_Processor]([SMBios_ProcessorGuid] ASC);


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


GO
CREATE TABLE [dbo].[Asset_Win32_Environment] (
    [Win32_EnvironmentGuid] UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]            UNIQUEIDENTIFIER NOT NULL,
    [Caption]               NVARCHAR (500)   NULL,
    [Description]           NVARCHAR (2000)  NULL,
    [InstallDate]           DATETIME         NULL,
    [Status]                NVARCHAR (1000)  NULL,
    [Name]                  NVARCHAR (1000)  NULL,
    [SystemVariable]        BIT              NULL,
    [UserName]              NVARCHAR (1000)  NULL,
    [VariableValue]         NVARCHAR (3000)  NULL,
    [DateCreated]           DATETIME         NOT NULL,
    [DateModified]          DATETIME         NOT NULL,
    [OMRowVersion]          ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_Win32_Environment_Win32_EnvironmentGuid_PK] PRIMARY KEY NONCLUSTERED ([Win32_EnvironmentGuid] ASC)
);


GO
PRINT N'Creating [dbo].[Asset_Win32_Environment].[Asset_Win32_Environment_DeviceGuid_IX]...';


GO
CREATE NONCLUSTERED INDEX [Asset_Win32_Environment_DeviceGuid_IX]
    ON [dbo].[Asset_Win32_Environment]([DeviceGuid] ASC);


GO
PRINT N'Creating [dbo].[Asset_Win32_Environment].[Asset_Win32_Environment_Win32_EnvironmentGuid_UX]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [Asset_Win32_Environment_Win32_EnvironmentGuid_UX]
    ON [dbo].[Asset_Win32_Environment]([Win32_EnvironmentGuid] ASC);


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


GO
CREATE TABLE [dbo].[Asset_Win32_Printer] (
    [Win32_PrinterGuid]           UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]                  UNIQUEIDENTIFIER NOT NULL,
    [Attributes]                  BIGINT           NULL,
    [Availability]                INT              NULL,
    [AvailableJobSheets]          NVARCHAR (3000)  NULL,
    [AveragePagesPerMinute]       BIGINT           NULL,
    [Capabilities]                NVARCHAR (3000)  NULL,
    [CapabilityDescriptions]      NVARCHAR (3000)  NULL,
    [Caption]                     NVARCHAR (500)   NULL,
    [CharSetsSupported]           NVARCHAR (3000)  NULL,
    [Comment]                     NVARCHAR (1000)  NULL,
    [ConfigManagerErrorCode]      BIGINT           NULL,
    [ConfigManagerUserConfig]     BIT              NULL,
    [CreationClassName]           NVARCHAR (1000)  NULL,
    [CurrentCapabilities]         NVARCHAR (3000)  NULL,
    [CurrentCharSet]              NVARCHAR (1000)  NULL,
    [CurrentLanguage]             INT              NULL,
    [CurrentMimeType]             NVARCHAR (1000)  NULL,
    [CurrentNaturalLanguage]      NVARCHAR (1000)  NULL,
    [CurrentPaperType]            NVARCHAR (1000)  NULL,
    [Default]                     BIT              NULL,
    [DefaultCapabilities]         NVARCHAR (3000)  NULL,
    [DefaultCopies]               BIGINT           NULL,
    [DefaultLanguage]             INT              NULL,
    [DefaultMimeType]             NVARCHAR (1000)  NULL,
    [DefaultNumberUp]             BIGINT           NULL,
    [DefaultPaperType]            NVARCHAR (1000)  NULL,
    [DefaultPriority]             BIGINT           NULL,
    [Description]                 NVARCHAR (2000)  NULL,
    [DetectedErrorState]          INT              NULL,
    [DeviceID]                    NVARCHAR (1000)  NULL,
    [Direct]                      BIT              NULL,
    [DoCompleteFirst]             BIT              NULL,
    [DriverName]                  NVARCHAR (1000)  NULL,
    [EnableBIDI]                  BIT              NULL,
    [EnableDevQueryPrint]         BIT              NULL,
    [ErrorCleared]                BIT              NULL,
    [ErrorDescription]            NVARCHAR (2000)  NULL,
    [ErrorInformation]            NVARCHAR (3000)  NULL,
    [ExtendedDetectedErrorState]  INT              NULL,
    [ExtendedPrinterStatus]       INT              NULL,
    [Hidden]                      BIT              NULL,
    [HorizontalResolution]        BIGINT           NULL,
    [InstallDate]                 DATETIME         NULL,
    [JobCountSinceLastReset]      BIGINT           NULL,
    [KeepPrintedJobs]             BIT              NULL,
    [LanguagesSupported]          NVARCHAR (3000)  NULL,
    [LastErrorCode]               BIGINT           NULL,
    [Local]                       BIT              NULL,
    [Location]                    NVARCHAR (1000)  NULL,
    [MarkingTechnology]           INT              NULL,
    [MaxCopies]                   BIGINT           NULL,
    [MaxNumberUp]                 BIGINT           NULL,
    [MaxSizeSupported]            BIGINT           NULL,
    [MimeTypesSupported]          NVARCHAR (3000)  NULL,
    [Name]                        NVARCHAR (1000)  NULL,
    [NaturalLanguagesSupported]   NVARCHAR (3000)  NULL,
    [Network]                     BIT              NULL,
    [PaperSizesSupported]         NVARCHAR (3000)  NULL,
    [PaperTypesAvailable]         NVARCHAR (3000)  NULL,
    [Parameters]                  NVARCHAR (1000)  NULL,
    [PNPDeviceID]                 NVARCHAR (1000)  NULL,
    [PortName]                    NVARCHAR (1000)  NULL,
    [PowerManagementCapabilities] NVARCHAR (3000)  NULL,
    [PowerManagementSupported]    BIT              NULL,
    [PrinterPaperNames]           NVARCHAR (1000)  NULL,
    [PrinterState]                BIGINT           NULL,
    [PrinterStatus]               INT              NULL,
    [PrintJobDataType]            NVARCHAR (1000)  NULL,
    [PrintProcessor]              NVARCHAR (1000)  NULL,
    [Priority]                    BIGINT           NULL,
    [Published]                   BIT              NULL,
    [Queued]                      BIT              NULL,
    [RawOnly]                     BIT              NULL,
    [SeparatorFile]               NVARCHAR (1000)  NULL,
    [ServerName]                  NVARCHAR (1000)  NULL,
    [Shared]                      BIT              NULL,
    [ShareName]                   NVARCHAR (1000)  NULL,
    [SpoolEnabled]                BIT              NULL,
    [StartTime]                   DATETIME         NULL,
    [Status]                      NVARCHAR (1000)  NULL,
    [StatusInfo]                  INT              NULL,
    [SystemCreationClassName]     NVARCHAR (1000)  NULL,
    [SystemName]                  NVARCHAR (1000)  NULL,
    [TimeOfLastReset]             DATETIME         NULL,
    [UntilTime]                   DATETIME         NULL,
    [VerticalResolution]          BIGINT           NULL,
    [WorkOffline]                 BIT              NULL,
    [DateCreated]                 DATETIME         NOT NULL,
    [DateModified]                DATETIME         NOT NULL,
    [OMRowVersion]                ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_Win32_Printer_Win32_PrinterGuid_PK] PRIMARY KEY NONCLUSTERED ([Win32_PrinterGuid] ASC)
);


GO
PRINT N'Creating [dbo].[Asset_Win32_Printer].[Asset_Win32_Printer_DeviceGuid_IX]...';


GO
CREATE NONCLUSTERED INDEX [Asset_Win32_Printer_DeviceGuid_IX]
    ON [dbo].[Asset_Win32_Printer]([DeviceGuid] ASC);


GO
PRINT N'Creating [dbo].[Asset_Win32_Printer].[Asset_Win32_Printer_Win32_PrinterGuid_UX]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [Asset_Win32_Printer_Win32_PrinterGuid_UX]
    ON [dbo].[Asset_Win32_Printer]([Win32_PrinterGuid] ASC);


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


GO
CREATE TABLE [dbo].[IBUDeploymentStatus] (
    [DeploymentId]        INT              NOT NULL,
    [DeviceGuid]          UNIQUEIDENTIFIER NOT NULL,
    [CountryCode]         NVARCHAR (4)     NOT NULL,
    [IBUCustomerUsername] NVARCHAR (255)   NOT NULL,
    [IBUCustomerPassword] NVARCHAR (255)   NOT NULL,
    [ComputerId]          CHAR (4)         NOT NULL,
    [Status]              TINYINT          NOT NULL,
    [StatusDetails]       NVARCHAR (MAX)   NULL,
    [DateCreated]         DATETIME2 (7)    NOT NULL,
    [LastUpdate]          DATETIME2 (7)    NULL,
    CONSTRAINT [PK_IBUDeploymentStatus] PRIMARY KEY CLUSTERED ([DeploymentId] ASC),

	CONSTRAINT [FK_IBUDeploymentStatus_Device] FOREIGN KEY ([DeviceGuid])
	REFERENCES [Device]([DeviceGuid]) ON DELETE CASCADE ON UPDATE CASCADE
);


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


GO
ALTER TABLE [dbo].[Asset_SMBios_MemoryDevice] WITH NOCHECK
    ADD CONSTRAINT [Asset_SMBios_MemoryDevice_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]) ON DELETE CASCADE;


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


GO
ALTER TABLE [dbo].[Asset_SMBios_Processor] WITH NOCHECK
    ADD CONSTRAINT [Asset_SMBios_Processor_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]) ON DELETE CASCADE;


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


GO
ALTER TABLE [dbo].[Asset_Win32_Environment] WITH NOCHECK
    ADD CONSTRAINT [Asset_Win32_Environment_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]) ON DELETE CASCADE;


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


GO
ALTER TABLE [dbo].[Asset_Win32_Printer] WITH NOCHECK
    ADD CONSTRAINT [Asset_Win32_Printer_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]) ON DELETE CASCADE;


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


GO

CREATE PROCEDURE [dbo].[Mwus_GetNonApplicableSupersededUpdatesByDeviceGuid]
(
   @DeviceGuid uniqueidentifier,
   @UpdateGuids_DTT dbo.UpdateGuids_DTT READONLY
)
AS

BEGIN
	;WITH InstalledUpdates 
	AS
	(
		SELECT DISTINCT E.UpdateGuid
		FROM [dbo].[MwusDevicePatchStatus] E
		INNER JOIN @UpdateGuids_DTT U ON U.UpdateGuid = E.UpdateGuid  -- Select only the UpdateGuids passed in the parameters.
		OUTER APPLY (SELECT TOP (1) B.[EventID]
					 FROM (SELECT A.[DeviceGuid], 
								  A.[EventID], 
								  A.[LastChangeTime], 
								  A.[RowID] 
							  FROM [dbo].[MwusDevicePatchStatus] AS A
							  WHERE A.[DeviceGuid] = E.DeviceGuid 
							  AND A.[UpdateGuid] = E.UpdateGuid) AS B
					 ORDER BY B.[LastChangeTime] DESC, B.[RowID] DESC) AS LastEvent
		WHERE E.DeviceGuid = @DeviceGuid
		AND LastEvent.EventID IN (101, 183, 190, 197, 223, 225, 102, 184, 191, 199) -- 'Installed' and 'Installed with reboot pending' events.
	)
	SELECT DISTINCT S.SupersededUpdateGuid,
					LastEvent.EventID AS LatestEventID
	FROM [dbo].[MwusRevisionSupersedesUpdate] S
	INNER JOIN [dbo].[MwusRevision] AS R ON R.[RevisionID] = S.[RevisionID] -- To get the superseding UpdateGuid
	INNER JOIN [dbo].[MwusRevision] AS R1 ON R1.UpdateGuid = S.SupersededUpdateGuid -- Check if the superseded update guid exists in the revisions table.
	INNER JOIN InstalledUpdates U ON U.UpdateGuid = R.UpdateGuid -- Check if the superseding update is installed
	OUTER APPLY (SELECT TOP (1) B.[EventID]
				 FROM (SELECT A.[DeviceGuid], 
						 	  A.[EventID], 
							  A.[LastChangeTime], 
							  A.[RowID] 
					   FROM [dbo].[MwusDevicePatchStatus] AS A
					   WHERE A.[DeviceGuid] = @DeviceGuid 
					   AND A.[UpdateGuid] = S.SupersededUpdateGuid) AS B
				 ORDER BY B.[LastChangeTime] DESC, B.[RowID] DESC) AS LastEvent -- Get the latest event for each superseded guid
	OPTION (RECOMPILE) -- To prevent parameter sniffing. A new execution plan will be estimated every time this query runs.
END
GO
PRINT N'Creating [dbo].[Mwus_GetNonApplicableUpdatesByDeviceGuid]...';


GO

CREATE PROCEDURE [dbo].[Mwus_GetNonApplicableUpdatesByDeviceGuid]
(
   @DeviceGuid UNIQUEIDENTIFIER,
   @UpdateGuids_DTT dbo.UpdateGuids_DTT READONLY
)
AS

BEGIN

	SELECT DISTINCT DPS.[UpdateGuid],
		            C.LastEventID AS LatestEventID
	FROM [dbo].[MwusDevicePatchStatus] AS DPS
	OUTER APPLY (SELECT TOP (1) B.[EventID] AS LastEventID
				 FROM (SELECT A.[DeviceGuid], 
							  A.[UpdateGuid], 
							  A.[RevisionNumber], 
							  A.[EventID], 
							  A.[LastChangeTime], 
							  A.[RowID] 
						FROM [dbo].[MwusDevicePatchStatus] AS A
						WHERE (A.[DeviceGuid] = DPS.DeviceGuid) AND (A.[UpdateGuid] = DPS.UpdateGuid)
						) AS B
				 ORDER BY B.[LastChangeTime] DESC, B.[RowID] DESC) C
	WHERE (DPS.[DeviceGuid] = @DeviceGuid) 
	AND DPS.[EventID] <> 105 -- Different from 'NotApplicable'
	AND NOT EXISTS(SELECT * FROM @UpdateGuids_DTT U WHERE U.UpdateGuid = DPS.UpdateGuid) -- Select only the UpdateGuids passed in the parameters.
	OPTION (RECOMPILE) -- To prevent parameter sniffing. A new execution plan will be estimated every time this query runs.
END
GO
PRINT N'Checking existing data against newly created constraints';


GO
ALTER TABLE [dbo].[Asset_SMBios_MemoryDevice] WITH CHECK CHECK CONSTRAINT [Asset_SMBios_MemoryDevice_Device_FK];

ALTER TABLE [dbo].[Asset_SMBios_Processor] WITH CHECK CHECK CONSTRAINT [Asset_SMBios_Processor_Device_FK];

ALTER TABLE [dbo].[Asset_Win32_Environment] WITH CHECK CHECK CONSTRAINT [Asset_Win32_Environment_Device_FK];

ALTER TABLE [dbo].[Asset_Win32_Printer] WITH CHECK CHECK CONSTRAINT [Asset_Win32_Printer_Device_FK];


GO
PRINT N'Update complete.';


GO
