/*
 * ER/Studio Data Architect 11.0 SQL Code Generation
 * Company :      LPI
 * Project :      MWData_TRUNK_Express.dm1
 * Author :       LPI
 *
 * Date Created : Tuesday, February 07, 2017 14:24:50
 * Target DBMS : Microsoft SQL Server 2008
 */

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_Column_CHK] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_CHK]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_CHK]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_CHK] >>>'
END
go


 
create procedure [dbo].[DBM_Conv_drop_Column_CHK] 
( 
    @tablename   sysname 
   ,@columnname  sysname 
   ,@debug       int     = -1 
) 
as 
/* 
    exec [dbo].[DBM_Conv_drop_Column_CHK] 
 
    @tablename    = '' 
   ,@columnname   = '' 
   ,@debug        = 1 
    
*/ 
begin 
    declare @constrname sysname  
           ,@sql        nvarchar(1000)      
     
    set @constrname = [dbo].[udf_CHK_Name] (@tablename, @columnname) 
   
    while (len(@constrname) > 0) begin 
     
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname  
        if (@debug = 1) begin  
            print '[DBM_Conv_drop_Column_CHK][@tablename='  + @tablename  
                                        + '][@columnname= ' + @columnname 
                                        + '][@constrname= ' + isnull(@constrname,'null') 
                                        + ']' 
           print '@sql:'       
           print @sql                                        
                                              
        end 
             
        exec (@sql) 
         
        -- more then one check constraint? 
        set @constrname = [dbo].[udf_CHK_Name] (@tablename, @columnname) 
         
    end 
     
end -- DBM_Conv_drop_Column_DF


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_Column_DF] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_DF]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_DF]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_DF] >>>'
END
go




CREATE procedure [dbo].[DBM_Conv_drop_Column_DF]
(
    @tablename   sysname
   ,@columnname  sysname
   ,@debug       int     = -1
)
as 
begin
    declare @constrname sysname 
           ,@sql        nvarchar(1000)     
    
    set @constrname = [dbo].[udf_DF_Name] (@tablename, @columnname)
  
    if (@debug = 1) begin 
        print '[DBM_Conv_drop_Column_DF][@tablename='   + @tablename 
                                    + '][@columnname= ' + @columnname
                                    + '][@constrname= ' + isnull(@constrname,'null')
                                    + ']'
    end
        
    set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
    exec (@sql)
    
end -- DBM_Conv_drop_Column_DF


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_Column_FK] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_FK]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_FK]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_FK] >>>'
END
go



CREATE procedure [dbo].[DBM_Conv_drop_Column_FK]
(
    @tablename          sysname
   ,@firstfkcolumn     sysname
   ,@reftablename       sysname
)
as 
begin
    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)     
    
    set @constrname = dbo.udf_OneColumnFK_Name
                                                (
                                                    @tablename          
                                                   ,@firstfkcolumn     
                                                   ,@reftablename      
                                                )
    set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
    exec (@sql)
    
end -- DBM_Conv_drop_Column_FK


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_DF_AllForTable] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_DF_AllForTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_DF_AllForTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_DF_AllForTable] >>>'
END
go



CREATE procedure [dbo].[DBM_Conv_drop_DF_AllForTable]
(
    @tablename sysname
)
as 
begin
    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)     
       

    declare cr cursor local fast_forward
    for
    select name 
      from   sys.default_constraints  with(nolock)
     where parent_object_id = object_id(@tablename)
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @constrname
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        exec (@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- DBM_Conv_drop_DF_AllForTable


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_FK_AllForTable] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_FK_AllForTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_FK_AllForTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_FK_AllForTable] >>>'
END
go



CREATE procedure [dbo].[DBM_Conv_drop_FK_AllForTable]
(
    @tablename sysname
)
as 
begin
    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)  
   
    declare cr cursor local fast_forward

    for
    select name 
      from sys.foreign_keys  with(nolock)
     where parent_object_id = object_id(@tablename)
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @constrname
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        exec (@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- DBM_Conv_drop_FK_AllForTable


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable]
(
    @referenced_tablename sysname
   ,@debug                int = 0
)
as 
/*
	exec dbo.DBM_Conv_drop_FK_AllTo_ReferencedTable 
					@referenced_tablename = 'AlertLog'
				   ,@debug = 1
*/
begin
    declare 
            @constrname      sysname
           ,@sql             nvarchar(1000) 
           ,@tablename       sysname 
   
    declare cr cursor local fast_forward

    for
    select 
         name
		,object_name(parent_object_id) as TableName
	--	,object_name(referenced_object_id) as ParentTableName
		 from sys.foreign_keys 
		 where type = 'F'
		 --and object_id = object_id('AlertLog')
		 and referenced_object_id = object_id(@referenced_tablename)
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @constrname,@tablename 
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        if (@debug = 1) 
			print @sql
        else
			exec (@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- [DBM_Conv_drop_FK_AllTo_ReferencedTable


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_IX_AllForTable] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_IX_AllForTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_IX_AllForTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_IX_AllForTable] >>>'
END
go



create procedure [dbo].[DBM_Conv_drop_IX_AllForTable]
(
    @tablename     sysname
   ,@excludeUnique bit = 0
)
as 
/*
-- was SC_NonclusterdIndex_dropAll
*/
begin
    declare @sql        nvarchar(1000)
           ,@IndexName  sysname
       

    declare cr cursor local fast_forward
    for
        SELECT 
              i.[name] as IndexName
        FROM
                sys.indexes AS i WITH (NOLOCK)
        JOIN 
                sys.tables  AS t WITH (NOLOCK)
        ON 
                i.[object_id] = t.[object_id]
        where 
            t.[name]   = @tablename
        and i.index_id > 1 -- nonclustered
        and is_unique = case
                             when @excludeUnique = 1 then 0
                             else is_unique
                        end                             
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @IndexName
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'drop index ' + @IndexName + ' on ' + @tablename
        
        exec(@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- DBM_Conv_drop_IX_AllForTable


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_NCIX_AllForColumn] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_NCIX_AllForColumn]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_NCIX_AllForColumn]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_NCIX_AllForColumn] >>>'
END
go


 
create procedure [dbo].[DBM_Conv_drop_NCIX_AllForColumn]  
( 
    @tablename     sysname 
   ,@columnname    sysname 
   ,@debug         int = -1 
) 
as 
/* 
    exec sp_help SnmpTrap_Old 
 
 
    begin transaction 
     
       exec  [dbo].[DBM_Conv_drop_NCIX_AllForColumn]  
                                                    @tablename     = 'SnmpTrap_Old' 
                                                   ,@columnname    = 'DeviceID' 
                                                   ,@debug         = 1        
     
    rollback 
*/ 
begin 
 
    declare @sql         nvarchar(1000) 
           ,@indexname   sysname 
     
    --------------------- 
    if @debug = 1 begin  
     
          select  
                  o.name      as tablename 
                 ,i.name      as indexname 
                 ,i.index_id    
                 ,c.name      as columnname  
                 ,o.object_id 
                 ,ic.key_ordinal  
            from    
                 sys.objects       o  
            join  
                 sys.indexes       i  
                                        on o.object_id = i.object_id  
            join sys.index_columns ic  
                                        on i.object_id = ic.object_id  
                                       and i.index_id = ic.index_id  
            join sys.columns       c  
                                        on ic.object_id = c.object_id  
                                       and ic.column_id = c.column_id  
            where  o.type       = 'U'   
              and i.index_id    > 1 
              and o.name        = @tablename 
              and c.name        = @columnname 
    end 
    --------------------- 
        
    declare cr cursor local fast_forward 
    for 
        with IndexColumns as 
        ( 
         
          select  
                  o.name      as tablename 
                 ,i.name      as indexname 
                 ,i.index_id    
                 ,c.name      as columnname  
                 ,o.object_id 
                 ,ic.key_ordinal  
            from    
                 sys.objects       o  
            join  
                 sys.indexes       i  
                                        on o.object_id = i.object_id  
            join sys.index_columns ic  
                                        on i.object_id = ic.object_id  
                                       and i.index_id = ic.index_id  
            join sys.columns       c  
                                        on ic.object_id = c.object_id  
                                       and ic.column_id = c.column_id  
            where  o.type       = 'U'   
              and i.index_id    > 1 
              and o.name        = @tablename 
              and c.name        = @columnname 
                     
          ) 
        SELECT distinct 
               indexname 
          from  
               IndexColumns      
         
    declare @ii int  
	    set @ii = 0     
    open cr 
    while (1 = 1) begin 
         
         
         
        fetch next from cr into @indexname 
         
        if (@@Fetch_Status <> 0) break 
         
        set @ii = @ii + 1 
     
        set @sql = 'drop index ' + @indexname + ' on ' + @tablename 
         
        if @debug = 1 print @sql 
         
        exec(@sql) 
     
    end -- while 
    close cr 
    deallocate cr         
 
    -- feedback 
     -- if @debug = 1 
     select  
             @tablename  as [@tablename] 
            ,@columnname as [@columnname] 
            ,@ii         as [Number of indexes found] 
     
 
end -- DBM_Conv_drop_NCIX_AllForColumn


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_PK] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_PK]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_PK]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_PK] >>>'
END
go



CREATE procedure [dbo].[DBM_Conv_drop_PK]
(
    @tablename sysname
)
as
begin

    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)  

     set @constrname = dbo.udf_PK_Name(@tablename)

        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        exec (@sql)

end -- DBM_Conv_drop_PK


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_rename_Table] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_rename_Table]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_rename_Table]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_rename_Table] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_rename_Table]
(
	@tablename          sysname
   ,@newname            sysname      = null
   ,@suffix             nvarchar(20) = '_2009R2'  -- never '', use NULL
   ,@RenamePK           bit = 1
   ,@DropDF             bit = 1
   ,@DropFK             bit = 1
   ,@DropPK             bit = 0
   ,@DropNCIndex        bit = 0
   ,@OnlyIfTable        bit = 1
   ,@NotIfNewNameExists bit = 0
   ,@debug              BIT  = 0
)
AS
/*

                select * from INFORMATION_SCHEMA.TABLES
              where TABLE_NAME = 'MwusDevicePatchStatus'

*/

begin
    if (@OnlyIfTable = 1) begin
      -- check it is not a view:
        if not exists
        (
            select * from sys.tables t
              where t.name = @tablename
              --  and TABLE_TYPE = 'BASE TABLE'
        ) 
        BEGIN
            PRINT @tablename + ' not found. Exit [DBM_Conv_rename_Table] '
            RETURN
        end
    end        
    ----------------------------------

    declare @curname    sysname
           ,@nextname   sysname
           ,@sql        nvarchar(1000)

    ------------------------------
    if (@newname is null) set @newname = @tablename + @suffix
    ------------------------------

     if (@NotIfNewNameExists = 1) begin
      -- check if @newname table/view exists
        if   exists
        (
            select * from sys.tables t
              where t.name = @newname
        ) 
        return
    end        
    ----------------------------------
    -------------------------------------

    if (@DropNCIndex = 1)   exec dbo.DBM_Conv_drop_IX_AllForTable   @tablename = @tablename
    if (@DropDF      = 1)   exec dbo.DBM_Conv_drop_DF_AllForTable   @tablename = @tablename
    if (@DropFK      = 1)   exec dbo.DBM_Conv_drop_FK_AllForTable   @tablename = @tablename

    if (@RenamePK = 1 )   begin

        set @curname = dbo.udf_PK_Name(@tablename)
        if (@curname is not null) begin
            set @nextname = @curname + isnull(@suffix,dbo.udf_TimestampSuffixLocalTime())
            
            IF @debug = 1 BEGIN
                PRINT 'exec dbo.DBM_Conv_rename_ObjectRcsv  @objname = ' + ISNULL(@curname,'null')
                                                       +  ',@newname = ' + ISNULL(@nextname,'null')
                                                       +  ',@objtype = OBJECT'  
            END
            
            exec dbo.DBM_Conv_rename_ObjectRcsv 
                                                 @objname = @curname
                                                ,@newname = @nextname
                                                ,@objtype = 'OBJECT'  -- for constraint
                                                
        end
    end
    else if (@DropPK = 1 )   begin
        exec dbo.DBM_Conv_drop_PK @tablename = @tablename
    end

    -- FINALLY:
    IF @debug = 1 BEGIN
        PRINT 'exec dbo.DBM_Conv_rename_ObjectRcsv  @objname='    + ISNULL(@tablename,'null')
                                                + ' ,@newname = ' + ISNULL( @newname,'null')
                                                +  ',@objtype = null/TABLE' 
        
    END
    exec dbo.DBM_Conv_rename_ObjectRcsv 
                                         @objname = @tablename
                                        ,@newname = @newname
                                        ,@objtype = NULL -- for table
                                        ,@debug   = @debug


end -- DBM_Conv_rename_Table


go

/* 
 * PROCEDURE: [dbo].[MakeNtAutotitySystemDBO] 
 */

IF OBJECT_ID('[dbo].[MakeNtAutotitySystemDBO]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[MakeNtAutotitySystemDBO]
    PRINT '<<< DROPPED PROCEDURE [dbo].[MakeNtAutotitySystemDBO] >>>'
END
go


create procedure [dbo].MakeNtAutotitySystemDBO
(
    @NtasWinAccountName nvarchar(128) = N'NT AUTHORITY\SYSTEM'
)
as
/*
sp_helpsrvrole 
[sys].[sp_helplogins]

exec MakeNtAutotitySystemDBO 'NT AUTHORITY\SYSTEM'

*/
begin

    --declare @quotedBR nvarchar(130) 
    --set @quotedBR = quotename (@NtasWinAccountName)

    declare @quotedSQ nvarchar(130) 
    set @quotedSQ = quotename (@NtasWinAccountName,'''')
    
    declare @sql nvarchar(max)
           ,@shemaName  sysname

    
    if not exists (select * from sys.syslogins where name = @NtasWinAccountName) begin
        
        set @sql = 'CREATE LOGIN ' + quotename(@NtasWinAccountName) + ' FROM WINDOWS'
        print @sql
        exec (@sql)
    end

    
    --set @sql = 'ALTER SERVER ROLE sysadmin ADD MEMBER '  + @quoted
    set @sql = 'EXEC master..sp_addsrvrolemember @loginame = N' + quotename (@NtasWinAccountName,'''') + '
                               , @rolename = N''sysadmin'''
    print @sql
    exec (@sql)
    
   
   
     if exists
     (
        select *
          from sys.database_principals
        where 
              name = @NtasWinAccountName
      )
      begin
			---------------------- take care of owned schemas ---------------
			  declare @sqlSchema nvarchar(max)
			  declare cr cursor local fast_forward
			  for
				 SELECT s.name
				 FROM sys.schemas s
				 WHERE s.principal_id = USER_ID(@NtasWinAccountName);

			 open cr
			 while (1 = 1) begin

				fetch next from cr into @shemaName

				if @@Fetch_Status <> 0 break

				set @sqlSchema = 'ALTER AUTHORIZATION ON SCHEMA::' + quotename (@shemaName) + ' TO [dbo]'
				print @sqlSchema
				exec (@sqlSchema)

			 end
			 close cr
			 deallocate cr
 
			----------------------------------------------------------------
            declare @sqlUsr      nvarchar(4000)
            set @sqlUsr = 'drop user ' +  quotename (@NtasWinAccountName) 
            
            print @sqlUsr
            exec(@sqlUsr)
            
      end
   
     
    set @sql = 'alter authorization on database::' + db_name() + ' to ' +  quotename (@NtasWinAccountName)   
    print @sql
    exec (@sql)

    select db_name()
end -- MakeNtAutotitySystemDBO


go

/* 
 * PROCEDURE: [dbo].[Mwus_GetEligibleRevisionsForDownload] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetEligibleRevisionsForDownload]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_GetEligibleRevisionsForDownload]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetEligibleRevisionsForDownload] >>>'
END
go


CREATE PROCEDURE [dbo].[Mwus_GetEligibleRevisionsForDownload] 
(
	@SinceLastNeededTime AS DATETIME
)
as
begin
 ;WITH REVISIONS AS
 (
	-- These is the Patch Status enum
	----------------------------------
	-- status = 5; // Failed
	-- status = 4; // Installed
	-- status = 3; // Downloaded
	-- status = 2; // Not installed
	-- status = 1; // Not applicable

	-- (Needed: PatchDeviceStatusID IN (2, 3, 5))
    -- (Installed: PatchDeviceStatusID = 4)

	SELECT R.RevisionID, 
		   D1.OSLocale
	FROM   PatchDeviceLink DL 
		   INNER JOIN MwusRevision R ON DL.PatchGuid = R.UpdateGuid 
		   INNER JOIN MwusDeployment DM ON DM.RevisionID = R.RevisionID
		   INNER JOIN Device D ON D.DeviceID = DL.DeviceID
		   INNER JOIN MwusDevice D1 ON D1.DeviceGuid = D.DeviceGuid
	AND DM.IsDeleted = 0 
	AND (DM.TargetGroupGuid = D1.TargetGroupGuid OR DM.TargetGroupGuid = '00000000-7381-4E17-9C41-000000000000' ) 
	AND (R.UpdateGuid NOT IN (SELECT R1.UpdateGuid 
	                          FROM MwusRevision R1 
							  WHERE R1.UpdateGuid = R.UpdateGuid 
							  AND R1.PublicationState = 'Expired')) -- Non-expired
	AND DM.[Action] = 0 -- Approved 
	AND -- Any of the below conditions
		-- 'Needed' AND 'any approval time' AND 'last needed time' within the threshold.
		(DL.PatchDeviceStatusID IN (2, 3, 5) AND DL.TimeLastUpdate >= @SinceLastNeededTime
		-- 'Needed' AND 'approval time' within the threshold AND 'last needed time' greater than the threshold.
		OR DL.PatchDeviceStatusID IN (2, 3, 5) AND DM.LastChangeTime >= @SinceLastNeededTime AND DL.TimeLastUpdate < @SinceLastNeededTime
		-- 'Installed' AND 'released time' within the threshold.
		OR DL.PatchDeviceStatusID = 4 AND R.TimeCreated > @SinceLastNeededTime)
	UNION 
	
	SELECT R.RevisionID, 
	       D.OSLocale
	FROM PatchNow         PN
	INNER JOIN MwusRevision R ON R.UpdateGuid = PN.PatchGuid AND R.RevisionNumber = PN.RevisionNumber
	INNER JOIN MwusDevice   D ON D.DeviceGuid = PN.DeviceGuid
    WHERE NOT EXISTS
    (
	    SELECT * FROM MwusDevicePatchStatus DPS
	    WHERE	DPS.DeviceGuid = PN.DeviceGuid and
			    DPS.UpdateGuid = PN.PatchGuid and
			    DPS.RevisionNumber = PN.RevisionNumber and
			    DPS.LastChangeTime > PN.DateCreated
    )
 )
 SELECT R.RevisionID
       ,L.LanguageID
 FROM REVISIONS        R
 INNER JOIN dbo.MwusLanguage L on R.OSLocale = L.Code    
    
end -- Mwus_GetEligibleRevisionsForDownload

go

/* 
 * PROCEDURE: [dbo].[Mwus_GetFiles] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetFiles]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_GetFiles]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetFiles] >>>'
END
go


create procedure [dbo].Mwus_GetFiles
(
	 @RevisionIDs nvarchar(max)	
)
AS
begin

with BundleHierarchy
as
(
-- Anchor member definition
	select cast([String] as int) as RevisionID
		, 0 as Level
		, cast([String] as int) as ParentRevisionID
	from [dbo].[fn_CSVToTable] (@RevisionIDs)
      
    union all

-- Recursive member definition    
	select B.BundledRevisionID
	      --,H.BundledRevisionID
	      , Level + 1
		  , B.RevisionID as ParentRevisionID
	from MwusBundle      B
	join BundleHierarchy H on B.RevisionID = H.RevisionID
)
select  BH.RevisionID
      --, BH.BundledRevisionID
      , FR.MwusFileGuid
	  , FR.PatchType
      , FL.MUURL
	  , FL.CacheStatus
      , Level
	  , L.LanguageID
	  , ParentRevisionID
from 
       BundleHierarchy      BH
 left join (
          MwusFileForRevision  FR
      join MwusFile             FL on FL.MwusFileGuid = FR.MwusFileGuid
  ) on BH.RevisionID   = FR.RevisionID
  join MwusRevisionLanguage L on BH.RevisionID = L.RevisionID
  
  end -- Mwus_GetFiles

GO

/* 
 * PROCEDURE: [dbo].[Mwus_GetFilesByFileID] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetFilesByFileID]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_GetFilesByFileID]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetFilesByFileID] >>>'
END
go

CREATE PROCEDURE [dbo].[Mwus_GetFilesByFileID]
  @fileIDs [MwusFileIDList_DTT] READONLY
AS
BEGIN
  SET NOCOUNT ON;

  -- OPTION (RECOMPILE) is used, so the optimizer can make a correct cardinality estimation 
  -- for @fileIDs table variable when creating the execution plan
  SELECT RevisionID, MwusFileGuid, PatchType 
  FROM dbo.MwusFileForRevision  
  WHERE MwusFileGuid IN (SELECT FileID 
                         FROM @fileIDs)
  OPTION (RECOMPILE)

END -- Mwus_GetFilesByFileID

GO

/* 
 * PROCEDURE: [dbo].[Mwus_GetFileUrlsByFileID] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetFileUrlsByFileID]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].Mwus_GetFileUrlsByFileID
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetFileUrlsByFileID] >>>'
END
go

CREATE PROCEDURE [dbo].Mwus_GetFileUrlsByFileID
  @fileIDs [MwusFileIDList_DTT] READONLY
AS
BEGIN
  SET NOCOUNT ON;

  -- OPTION (RECOMPILE) is used, so the optimizer can make a correct cardinality estimation 
  -- for @fileIDs table variable when creating the execution plan
  SELECT MwusFileGuid, FileDigest, MUURL
  FROM dbo.MwusFile
  WHERE MwusFileGuid IN (SELECT FileID 
                         FROM @fileIDs)
  OPTION (RECOMPILE)

END -- Mwus_GetFileUrlsByFileID

GO

/* 
 * PROCEDURE: [dbo].[Mwus_GetFragmentXmlForUpdates] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetFragmentXmlForUpdates]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_GetFragmentXmlForUpdates]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetFragmentXmlForUpdates] >>>'
END
go


create procedure [dbo].Mwus_GetFragmentXmlForUpdates
(
    @RevisionIDs   varchar(max) 
    ,@FragmentType tinyint      
    ,@delim        varchar(20) = ','
    ,@debug        int = -1
)
as 
/*

exec dbo.Mwus_GetFragmentXmlForUpdates
    @RevisionIDs    = '22222,33333,99999,123,567,234,9000'
    ,@FragmentType  = 2      
    ,@delim         = ','
    ,@debug         = 1
    
*/
begin

    declare @xml xml
    set @xml = cast('<root><r>' + replace(@RevisionIDs,@delim,'</r><r>') + '</r></root>' as xml)

    if @debug = 1 begin
        select @xml  as [@xml]
    
        select 
               T.c.query('.').value('r[1]', 'int') as RevisionID
          from @xml.nodes('/root/r') as T(c)

    end
    else
        set nocount on
        
    
      select 
           T.c.query('.').value('r[1]', 'int') as RevisionID
      into #Rev
      from @xml.nodes('/root/r') as T(c)
  
      
    SELECT M.RevisionID
         , M.XmlFragment
    FROM 
         dbo.MwusMetadata M
    join 
         #Rev             R on M.RevisionID = R.RevisionID
    WHERE 
         M.FragmentType = @FragmentType
     option(recompile) 


end -- Mwus_GetFragmentXmlForUpdates


go

/* 
 * PROCEDURE: [dbo].[Mwus_GetInstallableRevisions] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetInstallableRevisions]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_GetInstallableRevisions]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetInstallableRevisions] >>>'
END
go

create procedure [dbo].[Mwus_GetInstallableRevisions]
(
    @TargetGroupGuid           uniqueidentifier --  Restrict the set of revisions to those that are deployed to the clients computer's target 
                                                --  group combined with any dependencies (prerequisites or bundle) of such updates
   ,@CategoryRevisionIDs       nvarchar(4000)  = null -- comma separated list of RevisionIDs to filter on MwusUpdateCategoryLink 
   ,@InstalledNonLeafIDs       nvarchar(4000)  = null -- comma separated list of RevisionIDs int already installed on the device for which we need the output
   ----
   ,@debug int = -1
)
as
/*
select newid()
exec dbo.Mwus_GetInstallableRevisions
    @TargetGroupGuid           = 'A31CC40F-8700-4ABF-9707-6347535A7FFD'
   ,@CategoryRevisionIDs        = null 
   ,@InstalledNonLeafIDs        = 
   '227133,227147,227229,227333,227360,227442,227461,227630,227656,227767,227904,227936,228233,228238,228396,228462,228542,228586,228744'
   --
   ,@debug = 1

*/
/*
         ---  The basic algorithm           ---
         --------------------------------------

         Must return a list of RevisionIDs to be installed and IsLeaf if there are no revisions have depend on it

    -- 1. Restrict the set of revisions to those that are deployed to the clients computer's target
    -- group COMBINED with any dependencies (prerequisites OR bundle) of such updates  
    -- ????? so return (1) all that are satisfied PLUS? (2) those which are satisfied by (1)?
    --
    SELECT RevisionID FROM MwusRevision JOIN MwusDeployment ON RevisionID
    WHERE TargetGroupGuid == TargetGroupGuid

    --
    -- 2. Restrict the resulting set further to those revisions whos prerequisites are satisfied by
    -- the updates whose revision IDs are specified in the InstalledNonLeafUpdatesIDs (list of ints)  -- ClausID use!!!


    ?              -- 2.2and belong to provided UpdateCategories - @CategoryRevisionIDs (list if ints).  
         If no categories are provided, return all catergories
        If none are provided, return all RevisionID from above query that have not prerequisites


    --
    -- 3. Restrict the resulting set further to UpdateType == Software
            --
            SELECT * FROM (above result) WHERE UpdateType == Software == 3 ??
            ----------------------------------------
*/

/*            OUTPUT:      

int              revision;             -- RevisionID
DeploymentAction action;               -- Action in MwusDeployment table
DateTime         deployChangeTime;     -- LastChangeTime in MwusDeployment table
bool             isLeaf;               -- IsLeaf in MwusRevision table
DateTime         isLeafChangeTime;     -- isLeafChange in MwusRevision table
string           coreXml;              -- XmlFragment WHERE FragmentType = 0 in MwusMetadata table

*/

/*
    declare @TargetGroupGuid uniqueidentifier
    
    exec Mwus_GetInstallableRevisions @TargetGroupGuid

*/
begin
    
        declare @CategCount     bit
               ,@InstalledCount bit
        declare @Driver int 
            set @Driver = 0
        
     if nullif(rtrim(isnull(@InstalledNonLeafIDs,'')),'') is null begin  -- no @InstalledNonLeafIDs
        -----------------
        if @debug = 1 begin
            select 'NEXT SatisfiedRoots OUTPUT and exit' as [NEXT]
        end
        -----------------
        ;  
        with SatisfiedRoots as
        (
            select 
                   *         
              from 
                   dbo.MwusRevision MR     
             where 
                   MR.Hidden              = 0
               and IsRoot                 = 1
               and IsLatestRevisionNumber = 1
               and UpdateType            <> @Driver
        ) 
         select 
                 UPD.RevisionID 
                ,UPD.IsLeaf
                ,UPD.UpdateType
                ,UPD.IsRoot
                ,UPD.IsLeafChange
                --,DEP.Action            as DeploymentAction
                --,DEP.LastChangeTime    as DeployChangeTime
                ,CAM.XmlFragment
           from 
                 SatisfiedRoots UPD
            --join
            --      dbo.MwusDeployment DEP   on UPD.RevisionID     = DEP.RevisionID  
            ----                             and DEP.TargetGroupGuid = @TargetGroupGuid
                              -- ??and MM.FragementType = 0                                         
            cross
            apply (select top (1) 
                           MM.*
                      from dbo.MwusMetadata MM
                     where
                           MM.RevisionID    = UPD.RevisionID  
                       and MM.FragmentType = 1 -- NOT 0
                     
                   ) CAM
    
        RETURN -- exit
    end -- no @InstalledNonLeafIDs
 
    ---------------------------------------------------  @InstalledNonLeafIDs is not null --------------
    

   
    
    create table #InstalledNonLeafIDs
    (
        RevisionID      int  primary key with(IGNORE_DUP_KEY = ON)
       ,UpdateGuid      uniqueidentifier null
    )
    ----------------  
    insert #InstalledNonLeafIDs (RevisionID)
    select cast([String] as int) as ID
      from [dbo].[fn_CSVToTable] (@InstalledNonLeafIDs)
    ----------------  
    update I
       set I.UpdateGuid = R.UpdateGuid
      from #InstalledNonLeafIDs I
      join dbo.MwusRevision     R on I.RevisionID = R.RevisionID
    ------------- FutureUpdateGuid ---  
    
    create table #InstalledNonLeafGuids
    (
        UpdateGuid      uniqueidentifier  primary key with(IGNORE_DUP_KEY = ON)
    )
    insert #InstalledNonLeafGuids
    select UpdateGuid
      from #InstalledNonLeafIDs
              
      
      
    -----------
    set @InstalledCount  = case when exists (select * from #InstalledNonLeafIDs) then 1 else 0 end
    -----------------
    if @debug = 1 begin
        select 'NEXT #InstalledNonLeafIDs' as [NEXT]
        select * from  #InstalledNonLeafIDs 
    end
    -----------------
    
    /*
    create table #CategoryRevisionIDs
    (
        CategoryID int primary key with(IGNORE_DUP_KEY = ON)
    )
    -- insert #CategoryRevisionIDs    
    insert #CategoryRevisionIDs
    select cast([String] as int) as ID
      from [dbo].[fn_CSVToTable] (@CategoryRevisionIDs)
    -----------
    set @CategCount  = case when exists (select * from #CategoryRevisionIDs) then 1 else 0 end
    
    -----------------
    if @debug = 1 begin
        select 
               @InstalledCount as [@InstalledCount]
              ,@CategCount     as [@CategCount]
    end
    -----------------
    */

    
    ------------------------------ FEB-12 -------------------------------------------
    print '--------------------- MATRIX NEXT ------------------'
    ;
    with Matrix as
    (
       select
                P1.RevisionID         
               ,IsSatisfied = 
                   case 
                       when exists
                           (
                             select * 
                               from dbo.MwusPrerequisite      P2       
                               join  #InstalledNonLeafGuids   INSTALLED   
                                          on INSTALLED.UpdateGuid = P2.PrerequisiteUpdateGuid     
                              where 
                                    P2.RevisionID     = P1.RevisionID    
                                and P2.ClauseGuid     = P1.ClauseGuid
                           )                            
                        then 1 
                     else -1 
                  end
               
        from 
              dbo.MwusPrerequisite P1                 
    )
    ,MatrixGroupped as
    (
        select distinct 
               R1.UpdateGuid
     ,M.IsSatisfied
        from Matrix                M
         join
             dbo.MwusRevision      R1  on M.RevisionID = R1.RevisionID     
         --join  -- narrow down
         --(
         --   select
         --       distinct P.RevisionID
         --   from #InstalledNonLeafGuids  I
         --   join dbo.MwusPrerequisite    P on I.UpdateGuid = P.PrerequisiteUpdateGuid
         --)    FUTURE   
         --                                   on FUTURE.RevisionID = R1.RevisionID     
             
		where
			   R1.IsLatestRevisionNumber = 1
           and R1.Hidden      = 0                                     
           and R1.UpdateType <> @Driver                                     
       
    )
    select UpdateGuid
         -- ,IsSatisfied
    into #MatrixFinal
    from MatrixGroupped MG
    group by UpdateGuid
   having count(IsSatisfied) = sum(IsSatisfied) -- true only for UpdateGuid having ALL Clauses with IsSatisfied=1
    
    
        -----------------
        if @debug = 1 begin
            select 'NEXT: select * from #Matrix' as [NEXT]
            select * from #MatrixFinal
        end
        -----------------
 
     -- OUTPUT
    select 
             UPD.RevisionID 
            ,UPD.IsLeaf
            ,UPD.UpdateType
            ,UPD.IsRoot
            ,UPD.IsLeafChange
            --,DEP.Action            as DeploymentAction
            --,DEP.LastChangeTime    as DeployChangeTime
            ,CAM.XmlFragment
      from 
           dbo.MwusRevision UPD  
      join
           #MatrixFinal     SG    on UPD.UpdateGuid            = SG.UpdateGuid
                                 and UPD.Hidden                = 0
                               -----and IsRoot                 = 1
                               and  UPD.IsLatestRevisionNumber = 1
                               and  UPD.UpdateType            <> @Driver
                         
    cross
    apply (select top (1) 
                   MM.*
              from dbo.MwusMetadata MM
             where
                   MM.RevisionID    = UPD.RevisionID  
               and MM.FragmentType = 1 -- NOT 0
             
           ) CAM
           
    order by RevisionID
             
    
end -- Mwus_GetInstallableRevisions


go

/* 
 * PROCEDURE: [dbo].[Mwus_GetLastChangeTime] 
 */

IF OBJECT_ID('[dbo].[Mwus_GetLastChangeTime]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_GetLastChangeTime]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_GetLastChangeTime] >>>'
END
go


CREATE PROCEDURE [dbo].Mwus_GetLastChangeTime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @UtcNow DateTime 
	set @UtcNow = GETUTCDATE()

	declare @LastChangeTime DateTime
	SET @LastChangeTime = (SELECT MAX(LastChangeTime) FROM MwusDeployment)
	if (@LastChangeTime is null)
		SET @LastChangeTime = @UtcNow

	declare @IsLeafChange DateTime
	SET @IsLeafChange = (SELECT MAX(IsLeafChange) FROM MwusRevision)
	if (@IsLeafChange is null)
		SET @IsLeafChange = @UtcNow

	if (@LastChangeTime > @IsLeafChange)
		select @LastChangeTime
	else
		select @IsLeafChange
	

END


go

/* 
 * PROCEDURE: [dbo].[Mwus_IsRoot_IsLeaf_set] 
 */

IF OBJECT_ID('[dbo].[Mwus_IsRoot_IsLeaf_set]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_IsRoot_IsLeaf_set]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_IsRoot_IsLeaf_set] >>>'
END
go

create procedure [dbo].[Mwus_IsRoot_IsLeaf_set]
as
begin

    -- 1 IsRoot - does not have prerequisits
    ;
    with RootCheck as
    (
      select  MREV.RevisionID
             ,IsRoot = case      
                        when exists
                                 (
                                    select top(1) 1
                                      from dbo.MwusPrerequisite PREQ
                                     where 
                                           PREQ.RevisionID = MREV.RevisionID
                                 )
                             then 0 -- needs prereq -> not a root
                         else 1
                     end
     from 
          dbo.MwusRevision MREV
    )
    update MREV
      set MREV.IsRoot = RCH.IsRoot
     from 
          dbo.MwusRevision MREV
     join RootCheck        RCH   on MREV.RevisionID = RCH.RevisionID
                                and MREV.IsRoot    != RCH.IsRoot

    -- 2 IsLeaf - is not a Prerequisite for any other: nothing points to its UpdateGuid from MwusPrerequisite
    ;
    with RevGuids as
    (
        select distinct R.UpdateGuid
          from dbo.MwusRevision R
    )
    ,PReqGuids as
    (
        select distinct P.PrerequisiteUpdateGuid
          from dbo.MwusPrerequisite P
    )
     ,LeafGuids as
    (
        select RG.UpdateGuid
          from RevGuids  RG
          left
          join PReqGuids PG on RG.UpdateGuid = PG.PrerequisiteUpdateGuid
         where
               PG.PrerequisiteUpdateGuid is null
    )
    ,IsLeafCheck as
    (
      select 
            MREV.RevisionID
           ,IsLeaf = case      
                         when LG.UpdateGuid is not null then 1
                         else 0
                     end
            
     from 
           dbo.MwusRevision MREV
     left
     join LeafGuids         LG    on MREV.UpdateGuid = LG.UpdateGuid
    )
    update MREV
      set MREV.IsLeaf = ILF.IsLeaf
         ,MREV.IsLeafChange  = getutcdate()
     from 
          dbo.MwusRevision MREV
     join 
          IsLeafCheck      ILF    on MREV.RevisionID = ILF.RevisionID
                                  and MREV.IsLeaf  != ILF.IsLeaf        -- changing
                                  
                                  
  -- 3 IsLatestRevisionNumber
    ;                   
   with LastRN as
    (
        select R.UpdateGuid
              ,max(RevisionNumber) as RevisionNumber
          from dbo.MwusRevision R
         group
            by UpdateGuid
    )
    ,RevisionNumberCheck as
    (
      select 
            MREV1.UpdateGuid
           ,MREV1.RevisionID
           ,IsLatestRevisionNumber = 
                     case      
                         when LRN.RevisionNumber is not null then 1
                         else 0
                     end
            
     from 
           dbo.MwusRevision MREV1
     left
     join LastRN            LRN    on MREV1.UpdateGuid     = LRN.UpdateGuid
                                  and MREV1.RevisionNumber = LRN.RevisionNumber
    )
    update MREV
      set MREV.IsLatestRevisionNumber = RNC.IsLatestRevisionNumber
     from 
          dbo.MwusRevision     MREV
     join 
          RevisionNumberCheck  RNC                   
                                    on MREV.RevisionID               = RNC.RevisionID
                                   and MREV.IsLatestRevisionNumber  != RNC.IsLatestRevisionNumber        -- changing                                                            

	-- Update MwusUpdateCategoryLink table
	--truncate table MwusUpdateCategoryLink

	;with CAT as
	(
	select	P.RevisionID as UpdateRevisionID,
			P.PrerequisiteUpdateGuid as CategoryGuid
	from	MwusPrerequisite P
	where	IsCategory = 1
	)
	insert MwusUpdateCategoryLink (UpdateRevisionID, CategoryGuid)
	select CAT.UpdateRevisionID
	,CAT.CategoryGuid
	from CAT
	left join MwusUpdateCategoryLink LINK on CAT.CategoryGuid = LINK.CategoryGuid
										 and CAT.UpdateRevisionID = LINK.UpdateRevisionID
	where LINK.CategoryGuid is null

    --- bug 19055
	--update MwusBundle
	--set BundledRevisionID = (select RevisionID from MwusRevision R where R.UpdateGuid = BundledUpdateGuid and R.RevisionNumber = BundledRevisionNumber)
	--where BundledRevisionID = 0 and exists (select RevisionID from MwusRevision R where R.UpdateGuid = BundledUpdateGuid and R.RevisionNumber = BundledRevisionNumber)

    -- bug 19055
	update MB
	set MB.BundledRevisionID = coalesce(R.RevisionID,LastRevision.RevisionID,0) 
	from dbo.MwusBundle     MB
	left
	join dbo.MwusRevision    R   on MB.BundledUpdateGuid     = R.UpdateGuid
	                            and MB.BundledRevisionNumber = R.RevisionNumber
	outer
	apply (select top(1) RevisionID
	         from dbo.MwusRevision HGST
	        where
	              HGST.UpdateGuid     = MB.BundledUpdateGuid
	        order 
	           by HGST.RevisionNumber desc  
	      ) LastRevision
	where 
	      MB.BundledRevisionID = 0 

	-----------------------------------------------------------------------------------------------------
	-- We need to do this after truncating all mwus metadata. See stored procedure: Mwus_TruncateTables.
	-- If there is a 'MwusDeployment_Backup' table, it means we need to repopulate MwusDeployment using this backup data.
	-----------------------------------------------------------------------------------------------------
	IF OBJECT_ID(N'[dbo].[MwusDeployment_Backup]', 'U') IS NOT NULL
	BEGIN
		INSERT INTO dbo.MwusDeployment
		SELECT R.[RevisionID]
			  ,D.[TargetGroupGuid]
			  ,D.[Action]
			  ,D.[Deadline]
			  ,D.[LastChangeTime]
			  ,D.[DownloadPriority]
			  ,D.[IsDeleted]
		FROM dbo.MwusDeployment_Backup D
		INNER JOIN dbo.MwusRevision R ON R.UpdateGuid = D.UpdateGuid AND R.RevisionNumber = D.RevisionNumber
		LEFT OUTER JOIN dbo.MwusDeployment D1 ON D1.RevisionID = R.RevisionID AND D1.TargetGroupGuid = D.TargetGroupGuid
		WHERE D1.RevisionID IS NULL -- This should not be necessary, just in case for whatever reason MwusDeployment is not empty.
		
		DROP TABLE dbo.MwusDeployment_Backup
	END

end -- Mwus_IsRoot_IsLeaf_set


go

/* 
 * PROCEDURE: [dbo].[Mwus_UpdatePatchApprovals] 
 */

IF OBJECT_ID('[dbo].[Mwus_UpdatePatchApprovals]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[Mwus_UpdatePatchApprovals]
    PRINT '<<< DROPPED PROCEDURE [dbo].[Mwus_UpdatePatchApprovals] >>>'
END
go


CREATE PROCEDURE [dbo].[Mwus_UpdatePatchApprovals]
AS
BEGIN

declare @tran int
set  @tran = @@trancount

begin try
    if @tran = 0 begin transaction

	--lets sync up groups
	Declare @groups table
	(
	TargetGroupGuid uniqueidentifier,
	Name nvarchar(500)
	)

	INSERT INTO @groups
	select distinct TargetGroupGuid, TargetGroupName 
	FROM MwusUpdatePatchApprovals 

	UPDATE MwusTargetGroup
	SET TargetGroupName = G.Name
	FROM MwusTargetGroup JOIN @groups G ON MwusTargetGroup.TargetGroupGuid = G.TargetGroupGuid

	INSERT INTO MwusTargetGroup
	select distinct TargetGroupGuid, Name 
	FROM @groups G
	WHERE G.TargetGroupGuid NOT IN (SELECT TargetGroupGuid FROM MwusTargetGroup)

	--let's sync up revision IDs (maybe not needed)
	Update MwusUpdatePatchApprovals
	SET MappedRevisionID = RevisionID
	FROM MwusUpdatePatchApprovals JOIN MwusRevision ON MwusUpdatePatchApprovals.PatchGuid = MwusRevision.UpdateGuid and MwusUpdatePatchApprovals.RevisionNumber = MwusRevision.RevisionNumber

	--let's expand bundles
	INSERT INTO MwusUpdatePatchApprovals
	SELECT	MwusBundle.BundledUpdateGuid, Max(MwusBundle.BundledRevisionNumber), Source.TargetGroupGuid, Source.TargetGroupName, 5, Min(Source.Deadline), MwusBundle.BundledRevisionID
	FROM	MwusUpdatePatchApprovals Source JOIN 
			MwusBundle ON Source.MappedRevisionID = MwusBundle.RevisionID LEFT JOIN
			MwusUpdatePatchApprovals Destination ON MwusBundle.BundledRevisionID = Destination.MappedRevisionID
	WHERE	Destination.MappedRevisionID IS NULL AND Source.MappedRevisionID IS NOT NULL AND BundledRevisionID > 0
	GROUP BY MwusBundle.BundledUpdateGuid, Source.TargetGroupGuid, Source.TargetGroupName
	      --       , Source.Deadline -- bug 127170 : having this in Group By still generates duplicates
	           , MwusBundle.BundledRevisionID

	-- now let's make the two tables match, pretty much
	UPDATE MwusDeployment
	SET		MwusDeployment.LastChangeTime = 
				CASE WHEN	MwusDeployment.Action <> MwusUpdatePatchApprovals.Action OR -- Actions did not match
							MwusDeployment.Deadline <> MwusUpdatePatchApprovals.Deadline OR -- Deadlines did not match
							(MwusUpdatePatchApprovals.MappedRevisionID IS NULL AND IsDeleted = 0) OR -- Deleted but was not marked as deleted
							(MwusUpdatePatchApprovals.MappedRevisionID IS NOT NULL AND IsDeleted = 1) -- Reactivated but was delete
							THEN GETUTCDATE() ELSE MwusDeployment.LastChangeTime END,
			MwusDeployment.Action =  CASE WHEN MwusUpdatePatchApprovals.MappedRevisionID IS NULL THEN MwusDeployment.Action ELSE MwusUpdatePatchApprovals.Action END,
			MwusDeployment.Deadline =  CASE WHEN MwusUpdatePatchApprovals.MappedRevisionID IS NULL THEN MwusDeployment.Deadline ELSE MwusUpdatePatchApprovals.Deadline END,
			IsDeleted = CASE WHEN MwusUpdatePatchApprovals.MappedRevisionID IS NULL THEN 1 ELSE 0 END
	FROM	MwusDeployment LEFT JOIN
			MwusUpdatePatchApprovals ON MwusDeployment.RevisionID = MwusUpdatePatchApprovals.MappedRevisionID AND MwusDeployment.TargetGroupGuid = MwusUpdatePatchApprovals.TargetGroupGuid


	INSERT INTO MwusDeployment
           (
                [RevisionID]
               ,[TargetGroupGuid]
               ,[Action]
               ,[Deadline]
               ,[LastChangeTime]
               ,[DownloadPriority]
               ,[IsDeleted]
             )	
	SELECT 
	       apr.MappedRevisionID
	     , apr.TargetGroupGuid
	     , apr.Action
	     , apr.Deadline
	     , GETUTCDATE()
	     , 1
	     , 0
	FROM MwusUpdatePatchApprovals  apr
	join   -- bugs 142226 and 139563  
	       -- INSERT statement conflicted with the FOREIGN KEY constraint "FK_MwusDeployment_MwusRevision".
	       --> ensure MappedRevisionID values exsits in MwusRevision
	     dbo.MwusRevision          rvsn  on apr.MappedRevisionID = rvsn.RevisionID
	LEFT 
	JOIN MwusDeployment            depl
	                                     ON apr.MappedRevisionID  = depl.RevisionID
	        AND apr.TargetGroupGuid   = depl.TargetGroupGuid
	WHERE 
	      depl.RevisionID IS NULL 

	if @tran = 0 commit
end try
begin catch
	if (XACT_STATE() != 0) 
        and @tran = 0 
            rollback transaction

	declare @Error varchar(max) 
	set @Error = dbo.udf_ErrorInfo() 

	raiserror(@Error, 16, 255)
    --return
End Catch
return
END


go

/* 
 * PROCEDURE: [dbo].[SC_BLSB_Dialog_cleanupAll] 
 */

IF OBJECT_ID('[dbo].[SC_BLSB_Dialog_cleanupAll]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[SC_BLSB_Dialog_cleanupAll]
    PRINT '<<< DROPPED PROCEDURE [dbo].[SC_BLSB_Dialog_cleanupAll] >>>'
END
go



create procedure [dbo].[SC_BLSB_Dialog_cleanupAll]
as
begin


    DECLARE @handle AS UNIQUEIDENTIFIER;
    DECLARE CONV CURSOR FOR 
      SELECT conversation_handle 
      FROM sys.conversation_endpoints      
      --WHERE far_service = 'WindowsEvent_service';
     order by is_initiator asc -- clean up recepient first
      
    OPEN CONV;
    FETCH NEXT FROM CONV INTO @handle;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      END Conversation @handle WITH CLEANUP;
      FETCH NEXT FROM CONV INTO @handle;
    END
    CLOSE CONV;
    DEALLOCATE CONV;
    
    
    exec('
     begin try
        truncate table dbo.DBM_ServiceBrokerDialog
     end try 
     begin catch
     end catch')        
    
end -- SC_BLSB_Dialog_cleanupAll


go

/* 
 * PROCEDURE: [dbo].[SC_DB_ERROR_HANDLER] 
 */

IF OBJECT_ID('[dbo].[SC_DB_ERROR_HANDLER]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[SC_DB_ERROR_HANDLER]
    PRINT '<<< DROPPED PROCEDURE [dbo].[SC_DB_ERROR_HANDLER] >>>'
END
go


 
CREATE  PROCEDURE [dbo].[SC_DB_ERROR_HANDLER] 
( 
    @ErrorNumber         int             output 
   ,@ErrorMessage        nvarchar(3000)  output 
   ,@ErrorInfo           nvarchar(1000)  output 
   ,@debug               int             = 0 
)  
as 
begin 
 
    set @ErrorNumber  = ERROR_NUMBER(); 
     
    set @ErrorMessage = ERROR_MESSAGE();  
     
    set @ErrorInfo =      
                      '[PROCEDURE=' +                     ERROR_PROCEDURE()     + ']'     
                    + '[LINE='      + convert(varchar(10),ERROR_LINE     ())    + ']'     
                    + '[SEVERITY='  + convert(varchar(10),ERROR_SEVERITY ())    + ']'     
                    + '[STATE='     + convert(varchar(10),ERROR_STATE    ())    + ']';  
     
    set @ErrorInfo = ISNULL(@ErrorInfo,'') 
                     
    if (@debug = 1) begin 
        print convert(varchar(30),getDate(),121) + ' -- [SC_DB_ERROR_HANDLER]' 
        print @ErrorMessage 
        print @ErrorNumber 
        print @ErrorInfo 
        select  
               @ErrorMessage      as ERROR_MESSAGE 
              ,@ErrorNumber       as ERROR_NUMBER 
              ,@ErrorInfo         as ERROR_INFO 
    end 
     
end -- SC_DB_ERROR_HANDLER


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN] >>>'
END
go


 
create procedure [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN] 
( 
    @tablename   sysname 
   ,@columnname  sysname 
   ,@debug       int     = -1 
) 
as  
/* 
/* 
    exec sp_help SnmpTrap_Old 
 
 
    begin transaction 
     
       exec  [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN]  
                                                    @tablename     = 'SnmpTrap_Old' 
                                                   ,@columnname    = 'DeviceID' 
                                                   ,@debug         = 1        
     
    rollback 
*/ 
     
*/ 
begin 
     
    declare @sql nvarchar(4000) 
     
    exec dbo.DBM_Conv_drop_Column_DF  
                                         @tablename    = @tablename 
                                        ,@columnname   = @columnname 
                                        ,@debug        = @debug 
                                       
    exec dbo.DBM_Conv_drop_Column_FK   
                                         @tablename       = @tablename 
                                        ,@firstfkcolumn   = @columnname 
                                        ,@reftablename    = '' 
            
   exec [dbo].[DBM_Conv_drop_Column_CHK] 
                                         @tablename    = @tablename 
                                        ,@columnname   = @columnname 
                                        ,@debug        = @debug 
     
 
   exec  [dbo].[DBM_Conv_drop_NCIX_AllForColumn]  
                                                     @tablename    = @tablename 
                                                    ,@columnname   = @columnname 
                                                    ,@debug        = @debug 
 
    -- drop column 
    set @sql =  
    'if exists 
        ( 
	        select * from INFORMATION_SCHEMA.COLUMNS 
             where TABLE_NAME  = ''' + @tablename  + ''' 
               and COLUMN_NAME = ''' + @columnname + ''' 
        ) 
	        ALTER TABLE [dbo].' + @tablename + ' drop column ' + @columnname 
	         
   if (@debug = 1) begin  
        print @sql                                     
   end     
   exec (@sql) 
 
 
end -- [DBM_Conv_drop_Column_DF_FK_COLUMN]


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_drop_Column_DF_FK_COLUMN] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_DF_FK_COLUMN]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_DF_FK_COLUMN]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_DF_FK_COLUMN] >>>'
END
go

create procedure [dbo].[DBM_Conv_drop_Column_DF_FK_COLUMN]
(
    @tablename   sysname
   ,@columnname  sysname
   ,@debug       int     = -1
)
as 
/*
    exec [DBM_Conv_drop_Column_DF_FK_COLUMN]
                                                @tablename    = 'TX'
                                               ,@columnname   = 'CY'
                                               ,@debug        = 1
    
*/
begin
    
    declare @sql nvarchar(4000)
    exec dbo.DBM_Conv_drop_Column_DF 
                                         @tablename    = @tablename
                                        ,@columnname   = @columnname
                                        ,@debug        = @debug
                                      
    exec dbo.DBM_Conv_drop_Column_FK  
                                         @tablename       = @tablename
                                        ,@firstfkcolumn   = @columnname
                                        ,@reftablename    = ''
                                      
    if (@debug = 1) begin 
        print '[DBM_Conv_drop_Column_DF][@tablename='   + @tablename 
                                    + '][@columnname= ' + @columnname
                                    + ']'
    end
        
    set @sql = 
    'if exists
        (
	        select * from INFORMATION_SCHEMA.COLUMNS
             where TABLE_NAME  = ''' + @tablename  + '''
               and COLUMN_NAME = ''' + @columnname + '''
        )
	        ALTER TABLE [dbo].' + @tablename + ' drop column ' + @columnname
 
  if (@debug = 1) begin 
        print '[DBM_Conv_drop_Column_DF_FK_COLUMN][@tablename='   + @tablename 
                                    + '][@columnname= ' + @columnname
                                    + ']'
        print @sql                                    
   end    
   exec (@sql)
    
end -- [DBM_Conv_drop_Column_DF_FK_COLUMN]


go

/* 
 * PROCEDURE: [dbo].[DBM_Conv_rename_ObjectRcsv] 
 */

IF OBJECT_ID('[dbo].[DBM_Conv_rename_ObjectRcsv]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_rename_ObjectRcsv]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_rename_ObjectRcsv] >>>'
END
go



CREATE procedure [dbo].[DBM_Conv_rename_ObjectRcsv]
(
	@objname            sysname
   ,@newname            sysname
   ,@objtype            sysname = NULL -- null for table
   ,@debug              BIT  = 0
)
as
begin
    if object_id(@objname) is null  RETURN
    ---------------------------------------
	if object_id(@newname) is NOT null begin -- the new name is already in use --> rename it first

        declare @TempName sysname
        
        waitfor delay '00:00:01';

		set @TempName = @newname + dbo.udf_TimestampSuffixLocalTime()

        IF @debug = 1 BEGIN
            PRINT 'exec dbo.DBM_Conv_rename_ObjectRcsv  @objname = ' + ISNULL(@newname,'null')
                                                    +  ',@newname = ' + ISNULL(@TempName,'null') 
                                                    +  ',@objtype = ' + ISNULL(@objtype,'null/TABLE') 
                                                    +  '  
                @@NESTLEVEL =  ' + CAST (@@NESTLEVEL AS NVARCHAR(10))
        END


        EXEC dbo.DBM_Conv_rename_ObjectRcsv 
                                                @objname = @newname
                                               ,@newname = @TempName
                                               ,@objtype = @objtype
                                               ,@debug   = @debug

        
	end
 
        IF @debug = 1 BEGIN
        PRINT 'exec dbo.sp_rename    @objname = ' + ISNULL(@objname,'null')
                                +  ',@newname = ' + ISNULL(@newname,'null') 
                                +  ',@objtype = ' + ISNULL(@objtype,'null/TABLE') 
                                +  '  
            @@NESTLEVEL =  ' + CAST (@@NESTLEVEL AS NVARCHAR(10))
    END
   ---------------------
   
    IF @objtype IS NOT NULL begin

         exec sp_rename 
                          @objname = @objname
                         ,@newname = @newname
                         ,@objtype = @objtype
    END
    ELSE BEGIN
         exec sp_rename 
                          @objname = @objname
                         ,@newname = @newname
                       --  ,@objtype = @objtype
    
    END


end -- DBM_Conv_rename_ObjectRcsv


go
IF OBJECT_ID('DeleteAllExpiredPatchRevisions') IS NOT NULL
    DROP PROCEDURE dbo.DeleteAllExpiredPatchRevisions
GO
CREATE PROCEDURE dbo.DeleteAllExpiredPatchRevisions
(
   @batch_size INT = 10000
   ,@debug      BIT = 0
)
AS
BEGIN

	IF OBJECT_ID('tempdb..#ExpiredRevisions') IS NOT NULL
		DROP TABLE #ExpiredRevisions

	declare @breakFlag bit
	set @breakFlag = 0;

	WHILE 1 = 1 BEGIN

		SELECT TOP (@batch_size) RevisionID INTO #ExpiredRevisions
		FROM dbo.MwusRevision R
		WHERE R.UpdateGuid IN (SELECT DISTINCT UpdateGuid 
								FROM dbo.MwusRevision EX 
								WHERE EX.PublicationState = 'Expired') 

		IF @@ROWCOUNT < @batch_size 
			set @breakFlag = 1;


		declare @tran int = @@trancount
		begin try

		if @tran = 0
		BEGIN TRANSACTION  
	   
			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusMetadata  
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusPrerequisite   
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusFileForRevision   
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusBundle   
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusRevisionLanguage   
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusUpdateCategoryLink    
					WHERE UpdateRevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusDeployment    
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

			WHILE 1 = 1 BEGIN
        
				DELETE TOP (@batch_size)
					FROM dbo.MwusRevision    
					WHERE RevisionID IN (SELECT RevisionID FROM #ExpiredRevisions)
        
				IF @@ROWCOUNT < @batch_size 
					BREAK
                
				WAITFOR DELAY '00:00:01'
			END

		if @tran = 0  
				COMMIT  

		DROP TABLE #ExpiredRevisions

	end try
	---------------------
	begin catch

			declare @ErrorMessage       nvarchar(max)
			set @ErrorMessage = [dbo].[udf_ErrorInfo]()

			if (XACT_STATE() != 0) and @tran = 0 
				rollback transaction

			RaisError (@ErrorMessage,16,255)

	end catch

	if @breakFlag = 1
		BREAK

	WAITFOR DELAY '00:00:01'
	END  -- Outer loop

END
GO

IF COL_LENGTH('MwusFile','DecryptionKey') IS NOT NULL 
BEGIN
	PRINT N'Create Procedure [dbo].[Mwus_GetFileEncryptionData]...';
	IF OBJECT_ID('dbo.Mwus_GetFileEncryptionData') IS NOT NULL
		DROP PROCEDURE [dbo].[Mwus_GetFileEncryptionData] 

	DECLARE @SQLString nvarchar(500); 
	SET @SQLString = N'CREATE PROCEDURE [dbo].[Mwus_GetFileEncryptionData] (@EncryptedUpdates_DTT AS dbo.EncryptedUpdates_DTT READONLY)
						AS
						BEGIN
							SELECT mr.RevisionID
								,mf.FileDigest
								,mf.DecryptionKey
							FROM MwusFile mf
							INNER JOIN MwusFileForRevision mffr ON mf.MwusFileGuid = mffr.MwusFileGuid
							INNER JOIN MwusRevision mr ON mr.RevisionID = mffr.RevisionID
							INNER JOIN @EncryptedUpdates_DTT eu ON mr.UpdateGuid = eu.UpdateGuid
						END';
	EXECUTE sp_executesql @SQLString;
END
GO

IF OBJECT_ID('MwusDeployment_select_Changes') IS NOT NULL
    DROP PROCEDURE dbo.MwusDeployment_select_Changes
GO
CREATE PROCEDURE [dbo].[MwusDeployment_select_Changes]
(
   @DeviceGuid      uniqueidentifier
  ,@TargetGroupGuid uniqueidentifier
  ,@RevisionIDs   dbo.RevisionID_DTT READONLY
)
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @AllComputerGroupGuid uniqueidentifier
	SET @AllComputerGroupGuid = '00000000-7381-4E17-9C41-000000000000'

	DECLARE @ChangedDeployments TABLE (
	  RevisionID int NOT NULL,
	  TargetGroupGuid uniqueidentifier NOT NULL,
	  Action tinyint NOT NULL,
	  Deadline datetime NULL,
	  LastChangeTime datetime NOT NULL,
	  DownloadPriority tinyint NOT NULL,
	  IsDeleted bit NOT NULL
	)

	INSERT INTO @ChangedDeployments
	  SELECT D.[RevisionID]
			,D.[TargetGroupGuid]
			,D.[Action]
			,D.[Deadline]
			,D.[LastChangeTime]
			,D.[DownloadPriority]
			,D.[IsDeleted]
	  FROM dbo.MwusDeployment D
	  INNER JOIN @RevisionIDs R ON R.RevisionID = D.RevisionID
	  WHERE D.TargetGroupGuid = @TargetGroupGuid

	SELECT RevisionID,
		   TargetGroupGuid,
		   [Action],
		   Deadline,
		   LastChangeTime,
		   DownloadPriority,
		   IsDeleted
	FROM @ChangedDeployments

	SELECT D.[RevisionID]
		  ,D.[TargetGroupGuid]
		  ,D.[Action]
		  ,D.[Deadline]
		  ,D.[LastChangeTime]
		  ,D.[DownloadPriority]
		  ,D.[IsDeleted]
	FROM MwusDeployment D
	INNER JOIN @RevisionIDs R ON R.RevisionID = D.RevisionID
	WHERE D.TargetGroupGuid = @AllComputerGroupGuid

	DECLARE @PatchNows TABLE 
	(
	  RevisionID int NOT NULL
	)

	INSERT INTO @PatchNows
	  SELECT R.RevisionID
	  FROM PatchNow PN
	  INNER JOIN MwusRevision R ON PN.PatchGuid = R.UpdateGuid
		AND PN.RevisionNumber = R.RevisionNumber
	  WHERE PN.DeviceGuid = @DeviceGuid

	SELECT RevisionID
	FROM @PatchNows

	SELECT B.BundledRevisionID
	FROM MwusBundle B
	WHERE B.RevisionID IN (SELECT RevisionID
						   FROM @PatchNows)
END

GO
IF OBJECT_ID('MwusDevicePatchLatestStatus_select_ByDevice') IS NOT NULL
    DROP PROCEDURE dbo.MwusDevicePatchLatestStatus_select_ByDevice
GO
CREATE PROCEDURE [dbo].[MwusDevicePatchLatestStatus_select_ByDevice]
(
   @DeviceGuid uniqueidentifier
)
AS

BEGIN 

	SET NOCOUNT ON;
------------------------------------------------------------------------------
-- Returns the latest event of each patch for the specified device.
------------------------------------------------------------------------------

	;WITH LatestEventByPatch AS (
	SELECT DeviceGuid, UpdateGuid, MAX(RowID) Max_RowID
	FROM dbo.MwusDevicePatchStatus
	WHERE DeviceGuid = @DeviceGuid
	GROUP BY  DeviceGuid, UpdateGuid) 
	SELECT S.[DeviceGuid]
		  ,S.[UpdateGuid]
		  ,S.[RevisionNumber]
		  ,S.[EventID]
		  ,S.[LastChangeTime]
		  ,S.[RowID]
	FROM dbo.MwusDevicePatchStatus S
	INNER JOIN LatestEventByPatch G ON G.DeviceGuid = S.DeviceGuid 
			AND G.UpdateGuid = S.UpdateGuid 
			AND G.Max_RowID = S.RowID

END -- MwusDevicePatchLatestStatus_select_ByDevice 
GO

IF OBJECT_ID('Mwus_GetNonExistingRevisionUpdates') IS NOT NULL
    DROP PROCEDURE dbo.Mwus_GetNonExistingRevisionUpdates
GO
-- =======================================================================================================
-- Given a list of UpdateGuid and RevisionNumber, return the revisions that do not exists in the database
-- =======================================================================================================
CREATE PROCEDURE dbo.Mwus_GetNonExistingRevisionUpdates 
(
	@RevisionIDs_DTT dbo.ClientUpdateCache_DTT READONLY
)
AS
BEGIN
	SELECT R.UpdateGuid
	      ,R.RevisionNumber
	FROM @RevisionIDs_DTT R
	LEFT OUTER JOIN dbo.MwusRevision R1 ON R1.UpdateGuid = R.UpdateGuid AND R1.RevisionNumber = R.RevisionNumber
	WHERE R1.UpdateGuid IS NULL
END
GO

IF OBJECT_ID('Mwus_TruncateTables') IS NOT NULL
    DROP PROCEDURE dbo.Mwus_TruncateTables
GO
CREATE PROCEDURE [dbo].[Mwus_TruncateTables]
AS
BEGIN
-- =======================================================================================================
-- Truncates all Mwus related tables and reset the DSS sync anchor and cookies.
-- Note: The MwusDeployment table will be re-populated after the next 'UpdatePatchApprovals' OM command.
-- =======================================================================================================

	------------------------------------
	-- Drop all foreign keys temporarily
	------------------------------------	
	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusFileForRevision_MwusRevision') AND parent_object_id = OBJECT_ID(N'dbo.MwusFileForRevision'))
		ALTER TABLE [dbo].[MwusFileForRevision] DROP CONSTRAINT [FK_MwusFileForRevision_MwusRevision]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusFileForRevision_MwusFile') AND parent_object_id = OBJECT_ID(N'dbo.MwusFileForRevision'))
		ALTER TABLE [dbo].[MwusFileForRevision] DROP CONSTRAINT [FK_MwusFileForRevision_MwusFile]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusRevisionLanguage_MwusLanguage') AND parent_object_id = OBJECT_ID(N'dbo.MwusRevisionLanguage'))
		ALTER TABLE [dbo].[MwusRevisionLanguage] DROP CONSTRAINT [FK_MwusRevisionLanguage_MwusLanguage]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusRevisionLanguage_MwusRevision') AND parent_object_id = OBJECT_ID(N'dbo.MwusRevisionLanguage'))
		ALTER TABLE [dbo].[MwusRevisionLanguage] DROP CONSTRAINT [FK_MwusRevisionLanguage_MwusRevision]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusBundle_MwusRevision_RevisionID') AND parent_object_id = OBJECT_ID(N'dbo.MwusBundle'))
		ALTER TABLE [dbo].[MwusBundle] DROP CONSTRAINT [FK_MwusBundle_MwusRevision_RevisionID]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusDeployment_MwusRevision') AND parent_object_id = OBJECT_ID(N'dbo.MwusDeployment'))
		ALTER TABLE [dbo].[MwusDeployment] DROP CONSTRAINT [FK_MwusDeployment_MwusRevision]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusDeployment_MwusTargetGroup') AND parent_object_id = OBJECT_ID(N'dbo.MwusDeployment'))
		ALTER TABLE [dbo].[MwusDeployment] DROP CONSTRAINT [FK_MwusDeployment_MwusTargetGroup]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusMetadata_MwusRevision') AND parent_object_id = OBJECT_ID(N'dbo.MwusMetadata'))
		ALTER TABLE [dbo].[MwusMetadata] DROP CONSTRAINT [FK_MwusMetadata_MwusRevision]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusPrerequisite_MwusRevision') AND parent_object_id = OBJECT_ID(N'dbo.MwusPrerequisite'))
		ALTER TABLE [dbo].[MwusPrerequisite] DROP CONSTRAINT [FK_MwusPrerequisite_MwusRevision]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusSupersedesUpdate_MwusRevision') AND parent_object_id = OBJECT_ID(N'dbo.MwusRevisionSupersedesUpdate'))
		ALTER TABLE [dbo].[MwusRevisionSupersedesUpdate] DROP CONSTRAINT [FK_MwusSupersedesUpdate_MwusRevision]

	IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_MwusUpdateCategoryLink_MwusRevision_UpdateRevisionID') AND parent_object_id = OBJECT_ID(N'dbo.MwusUpdateCategoryLink'))
		ALTER TABLE [dbo].[MwusUpdateCategoryLink] DROP CONSTRAINT [FK_MwusUpdateCategoryLink_MwusRevision_UpdateRevisionID]

	-------------------------------------------------------------------------------------------------------------------------
	-- Create a backup of the existing MwusDeployment table. The next DSS sync will use it to recalculate the new RevisionIDs
	-------------------------------------------------------------------------------------------------------------------------
	IF OBJECT_ID(N'[dbo].[MwusDeployment_Backup]', 'U') IS NULL
	BEGIN
		SELECT R.[UpdateGuid]
			  ,R.[RevisionNumber]
			  ,D.[TargetGroupGuid]
			  ,D.[Action]
			  ,D.[Deadline]
			  ,D.[LastChangeTime]
			  ,D.[DownloadPriority]
			  ,D.[IsDeleted]
		INTO dbo.MwusDeployment_Backup
		FROM dbo.MwusDeployment D
		INNER JOIN dbo.MwusRevision R ON R.RevisionID = D.RevisionID

		CREATE NONCLUSTERED INDEX [MwusDeployment_Backup_UpdateGuid_RevisionNumber_IX] ON [dbo].[MwusDeployment_Backup]
		(
			[UpdateGuid] ASC,
			[RevisionNumber] ASC
		)
	END

	TRUNCATE TABLE [dbo].[MwusDeployment]; 
	TRUNCATE TABLE [dbo].[MwusFileForRevision];
	TRUNCATE TABLE [dbo].[MwusFile]
	TRUNCATE TABLE [dbo].[MwusMetadata]; 
	TRUNCATE TABLE [dbo].[MwusPrerequisite]; 
	TRUNCATE TABLE [dbo].[MwusRevisionLanguage];
	TRUNCATE TABLE [dbo].[MwusLanguage];
	TRUNCATE TABLE [dbo].[MwusRevisionSupersedesUpdate]
	TRUNCATE TABLE [dbo].[MwusBundle]; 
	TRUNCATE TABLE [dbo].[MwusUpdateCategoryLink];
	TRUNCATE TABLE [dbo].[MwusRevision];

	----------------------------------------
	-- Reset the DSS Sync anchor and cookies
	----------------------------------------
    UPDATE[dbo].[MwusParentUss]
        SET[LastAuthorizationCookie] = NULL
		,[NewConfigAnchor] = NULL
        ,[LastCookie] = NULL
        ,[LastConfigAnchor] = NULL
        ,[LastSyncAnchor] = NULL

	-----------------------------
	-- Recreate all foreign keys
	-----------------------------
	ALTER TABLE [dbo].[MwusFileForRevision]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusFileForRevision_MwusRevision] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusFileForRevision]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusFileForRevision_MwusFile] FOREIGN KEY([MwusFileGuid])
	REFERENCES [dbo].[MwusFile] ([MwusFileGuid])

	ALTER TABLE [dbo].[MwusRevisionLanguage]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusRevisionLanguage_MwusLanguage] FOREIGN KEY([LanguageID])
	REFERENCES [dbo].[MwusLanguage] ([LanguageID])

	ALTER TABLE [dbo].[MwusRevisionLanguage]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusRevisionLanguage_MwusRevision] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])
	ON DELETE CASCADE

	ALTER TABLE [dbo].[MwusBundle]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusBundle_MwusRevision_RevisionID] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusDeployment]  WITH CHECK ADD  CONSTRAINT [FK_MwusDeployment_MwusRevision] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusDeployment]  WITH CHECK ADD  CONSTRAINT [FK_MwusDeployment_MwusTargetGroup] FOREIGN KEY([TargetGroupGuid])
	REFERENCES [dbo].[MwusTargetGroup] ([TargetGroupGuid])

	ALTER TABLE [dbo].[MwusMetadata]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusMetadata_MwusRevision] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusPrerequisite]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusPrerequisite_MwusRevision] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusRevisionSupersedesUpdate]  WITH NOCHECK ADD  CONSTRAINT [FK_MwusSupersedesUpdate_MwusRevision] FOREIGN KEY([RevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusUpdateCategoryLink]  WITH CHECK ADD  CONSTRAINT [FK_MwusUpdateCategoryLink_MwusRevision_UpdateRevisionID] FOREIGN KEY([UpdateRevisionID])
	REFERENCES [dbo].[MwusRevision] ([RevisionID])

	ALTER TABLE [dbo].[MwusFileForRevision] CHECK CONSTRAINT [FK_MwusFileForRevision_MwusRevision]
	ALTER TABLE [dbo].[MwusFileForRevision] CHECK CONSTRAINT [FK_MwusFileForRevision_MwusFile]
	ALTER TABLE [dbo].[MwusRevisionLanguage] CHECK CONSTRAINT [FK_MwusRevisionLanguage_MwusLanguage]
	ALTER TABLE [dbo].[MwusRevisionLanguage] CHECK CONSTRAINT [FK_MwusRevisionLanguage_MwusRevision]
	ALTER TABLE [dbo].[MwusBundle] CHECK CONSTRAINT [FK_MwusBundle_MwusRevision_RevisionID]
	ALTER TABLE [dbo].[MwusDeployment] CHECK CONSTRAINT [FK_MwusDeployment_MwusRevision]
	ALTER TABLE [dbo].[MwusDeployment] CHECK CONSTRAINT [FK_MwusDeployment_MwusTargetGroup]
	ALTER TABLE [dbo].[MwusMetadata] CHECK CONSTRAINT [FK_MwusMetadata_MwusRevision]
	ALTER TABLE [dbo].[MwusPrerequisite] CHECK CONSTRAINT [FK_MwusPrerequisite_MwusRevision]
	ALTER TABLE [dbo].[MwusRevisionSupersedesUpdate] CHECK CONSTRAINT [FK_MwusSupersedesUpdate_MwusRevision]

END
GO

IF OBJECT_ID('Mwus_GetNonApplicableUpdatesByDeviceGuid') IS NOT NULL
    DROP PROCEDURE 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

IF OBJECT_ID('Mwus_GetNonApplicableSupersededUpdatesByDeviceGuid') IS NOT NULL
    DROP PROCEDURE 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
