/*
Upgrade work for discovery

1)	Need to fill DiscoveryDevice 
    a.	Every row in Device table needs to exist in DiscoveryDevice
    b.	DeviceGuid comes from Device table
    c.	DeviceGuidTag comes from Device table
    d.	IsLocal needs to be set to true only for the DiscoveryDevice that has the DeviceGuid of the Device that has the 127.0.0.1 IP
    e.	Everything else should remain null

*/

insert DiscoveryDevice
(
	 DeviceGuid
	,IsLocal
)
select 
		 DV.DeviceGuid
		,0
  from Device          DV
  left
  join DiscoveryDevice DD
                            on DV.DeviceGuid = DD.DeviceGuid
 where
       DD.DeviceGuid is null                              
go

update DiscoveryDevice
   set IsLocal = 1
      ,DeviceGuidTag = IA.DeviceGuidTag
  from 
        DiscoveryDevice DD   
   join 
        Device          DV  on DD.DeviceGuid = DV.DeviceGuid
   cross
   apply
   ( 
     select top(1) DeviceGuidTag
        from IPAddress   IPA    
       where IPA.DeviceID = DV.DeviceID
         and IPA.IP       = '127.0.0.1'
       order
          by DeviceGuidTag desc
   )   IA                        
go   
        
update DiscoveryDevice
   set 
        DeviceGuidTag = IA.DeviceGuidTag
  from 
        DiscoveryDevice DD
   join 
        Device          DV  on DD.DeviceGuid = DV.DeviceGuid
   cross
   apply
   ( 
      select top(1) DeviceGuidTag
        from IPAddress   IPA    
       where IPA.DeviceID = DV.DeviceID
       order
          by DeviceGuidTag desc
   )   IA                        
  where
        DD.DeviceGuidTag    is     null
   
go        
        
        
/*
3)	Need to fill in DiscoveryDeviceWmiData 

    a.	Every row in Device table that has a MachineName that is not null or empty needs to exist in DiscoveryDeviceWmiData
    b.	MachineName needs to come from Device.MachineName
    c.	OSType needs to come from Device.OSType
    d.	All other fields should be null
*/

insert DiscoveryDeviceWmiData
(
	 DeviceGuid
	,OSType
	,MachineName
	,IsWmiEnabled
	---
	,PrimaryChassisType 
)
select 
		 DV.DeviceGuid
		,DV.OSType
		,DV.MachineName
		,DV.WmiEnabled
		---
		,2 -- Unknown
  from 
        Device                DV
  left
  join DiscoveryDeviceWmiData DD
                                     on DV.DeviceGuid = DD.DeviceGuid
 where
       DD.DeviceGuid                     is null 
   and  nullif(rtrim(DV.MachineName),'') is not null



/*
4)	Need to fill in DiscoveryDeviceWmiNetworkAdapter
    a.	Every row in DeviceMacLink where Device.Wmi is enabled needs to exist DiscoveryDeviceWmiNetworkAdapter
    b.	MacAddress needs to come from DeviceMacLink     
*/
insert DiscoveryDeviceWmiNetworkAdapter
(
	 DeviceGuid
	,MacAddress
)
select
         DV.DeviceGuid
        ,DMA.MAC
  from 
        Device           DV  
  join 
        DiscoveryDeviceWmiData      DDWD  -- 2013-04-24 bug 11195  FK_DiscoveryDeviceWmiNetworkAdapter_DiscoveryDeviceWmiData
                                          on DV.DeviceGuid = DDWD.DeviceGuid
  join 
        DeviceMacLink   DMA               on DV.DeviceID = DMA.DeviceID
  left
  join DiscoveryDeviceWmiNetworkAdapter DD
                                         on DV.DeviceGuid = DD.DeviceGuid
 where
       DD.DeviceGuid is null  
  and DV.WmiEnabled  = 1                          


/*
5)	Need to fill in DiscoveryDeviceSnmpData 

    a.	Every row in Device table that has a SnmpName that is not null or empty needs to exist in DiscoveryDeviceSnmpData
    b.	SysName needs to come from Device. SnmpName
    c.	All other fields should be null
*/
insert DiscoveryDeviceSnmpData
(
	 DeviceGuid
	,[SysName]
)
select 
		 DV.DeviceGuid
		,DV.SnmpName
  from 
        Device                DV
  join
       DiscoveryDevice        PRNT     on DV.DeviceGuid = PRNT.DeviceGuid 
  left
  join DiscoveryDeviceSnmpData DD
                                     on DV.DeviceGuid = DD.DeviceGuid
 where
       DD.DeviceGuid                is null 
  -- and  nullif(rtrim(DV.SnmpName),'') is not null
  and DV.SnmpEnabled  = 1                          


/*
6)	Need to fill in DiscoveryDeviceSnmpNetworkAdapter
    a.	Every row in DeviceMacLink where Device.IsSnmpEnabled is enabled needs to exist DiscoveryDeviceWmiNetworkAdapter
    b.	MacAddress needs to come from DeviceMacLink 
*/
insert DiscoveryDeviceSnmpNetworkAdapter
(
	 DeviceGuid
	,MacAddress
)
select
         DV.DeviceGuid
        ,DMA.MAC
  from 
        Device          DV  
  join 
        DeviceMacLink   DMA              on DV.DeviceID = DMA.DeviceID
  join 
        DiscoveryDeviceSnmpData   PRNT   on DV.DeviceGuid = PRNT.DeviceGuid
  left
  join DiscoveryDeviceSnmpNetworkAdapter DD
                                         on DV.DeviceGuid = DD.DeviceGuid
 where
       DD.DeviceGuid is null  
  and DV.SnmpEnabled  = 1                          



/*
7)	Need to fill in DiscoveryDeviceDnsData
    a.	Every row in Device table that has a DNSName that is not null or empty needs to exist in DiscoveryDeviceDnsData. 
        DNSName is actually comma separated in the Device table. 
        We would need to split that and insert multiple rows
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Conv_SplitIntString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Conv_SplitIntString]
GO

CREATE FUNCTION [dbo].[udf_Conv_SplitIntString]
(
     @StringVersion       VARCHAR(max)
    ,@Separator           varchar(1)
)
RETURNS @tbl TABLE
(
     Member     varchar(100) not null
    ,Position   int          not null
)

AS
/*

    select * from [dbo].[udf_Conv_SplitIntString]
                                                (
                                                     '10.33.11111.444.5555.66.7777.888', '.' 
                                                     
                                                )
   -------------------------------------------

   declare @tt  table
    (ScriptID int, Version varchar(100))
    
   insert @tt (ScriptID , Version)
   values (1,'1.11.111.1111.')
   
   insert @tt (ScriptID , Version)
   values (2,'2.22.222.2222')
   
   select tt.ScriptID
         ,splt.Member
         ,splt.Position
     from @tt tt
     cross
     apply [dbo].[udf_Conv_SplitIntString] (tt.Version) splt
   
   --------
   
   ; with SourceTable as
   (
       select tt.ScriptID
             ,splt.Member
             ,splt.Position
         from @tt tt
         cross
         apply [dbo].[udf_Conv_SplitIntString] (tt.Version, '.') splt
   )

     select 
             ScriptID
            ,MajorVersion = isnull(cast([1] as int),1)
            ,MinorVersion = isnull(cast([2] as int),0)
            ,Build        = isnull(cast([3] as int), 1)
            ,Revision     = isnull(cast([4] as int), 0)
       from SourceTable
       pivot 
       (
            max(Member) for Position in
            (
               [1], [2], [3], [4]
            )
       ) T

   
   

*/
BEGIN

    DECLARE @pos        INT
    DECLARE @piece      VARCHAR(100)
    declare @pattern    varchar(20)
           ,@ii         int
           --,@pieceInt   int
           ,@prevPos    int
           
    set @pattern = @Separator -- '.'

    -- Need to tack a delimiter onto the end of the input string if one doesn't exist
    IF RIGHT(RTRIM(@StringVersion),1)     <> @pattern
    SET @StringVersion = @StringVersion  + @pattern

    SET @pos     = 0 -- charindex(@pattern, @StringVersion)
    set @prevPos = 0
    
    set   @ii = 0
    WHILE (1 = 1)
    BEGIN
    
     set @ii    = @ii + 1
     
     SET @pos   = charindex(@pattern, @StringVersion, @prevPos)
     if @pos = 0 break
     --if @pos = 0 begin
     --   set @StringVersion = @StringVersion + '0.'
     --   SET @pos   = charindex(@pattern, @StringVersion, @prevPos)
     --   --set @pos = @prevPos + 1
     --end
     
     SET @piece = substring(@StringVersion, @prevPos, @pos - @prevPos) 
    -- set @pieceInt = cast(@piece as int)
     set @prevPos = @pos + 1     
     
     insert @tbl
        (
             Member     
            ,Position   
       )
      values
      (
         @piece
        ,@ii
      )

    END



    RETURN
    
END -- [udf_Conv_SplitIntString]

GO

insert DiscoveryDeviceDnsData
(
	 DeviceGuid
	,DnsName
	,IsConfirmed  
)
select distinct
		 DV.DeviceGuid
		,DNS.Member
		,1 
  from 
        Device                DV
   join DiscoveryDevice       PRNT on DV.DeviceGuid = PRNT.DeviceGuid
   cross
   apply (
            select U.Member
              from udf_Conv_SplitIntString(DV.DNSName,',') U
         ) DNS                                     
 where
       not exists
       (
         select * 
           from  DiscoveryDeviceDnsData DD
          where DD.DeviceGuid = DV.DeviceGuid 
       ) 
   and  nullif(rtrim(DV.DNSName),'') is not null
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Conv_SplitIntString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Conv_SplitIntString]
GO

/*
	DiscoveryIpAddress - Defect 11392
*/

INSERT [DiscoveryIpAddress]
           (
            [Address]
           ,[IsLocalIP]
           ,[IsGatewareAddress]
           ,[IsInLocalSubnet]
           ,[DeviceGuid]
           ,[ArpMacAddress]
           ,[ArpType]
           ,[DnsException]
           ,[IcmpStatus]
           ,[IcmpHopCount]
           ,[IcmpMilliseconds]
           ,[MwDiscoveryDeviceGuid]
           ,[NetbiosName]
           ,[NetbiosGroup]
           ,[NetbiosMacAddress]
           ,[EndpointCreator]
           )
    select           
            IP
           ,0
           ,0
           ,0
           ,D.[DeviceGuid]
           ,ArpMac
           ,null
           ,null
           ,[IcmpStatus] = case when lower(A.Status) = 'up' then 1 else 0 end
           ,null
           ,null
           ,null
           ,A.NetbiosName
           ,null
           ,null
           ,null
      from 
            IPAddress   A
       join Device      D 
                          on A.DeviceID = D.DeviceID         

GO


/*

bug 12150 8/0/1 HF1

1) On the OM upgrade,  determine which device is the OM by reading the IPAddress table and seeing which device has 127.0.0.1
2) Set the "IsLocal" flag to true for the device and all of its associated IPs in: DiscoveryDevice and DiscoveryIpAddress
 
This will ensure that the OM will always be maintained on upgrade.
 
Hans - June 21, 2013

	DiscoveryIpAddress
	
  update DiscoveryIpAddress
  set IsLocalIP = 0
  
  select * from DiscoveryIpAddress
  where IsLocalIP = 1

*/
update TRGT
   set IsLocalIP = IA.IsLocal_1
  from 
        DiscoveryIpAddress TRGT   
   join 
        Device          DV  on TRGT.DeviceGuid = DV.DeviceGuid
   cross
   apply
   ( 
     select top(1)
	         IsLocal_1 = cast(1 as bit)
        from IPAddress   IPA    
       where IPA.DeviceID = DV.DeviceID
         and IPA.IP       = '127.0.0.1'
       order
          by DeviceGuidTag desc
   )   IA  
                   
go   



