-- NEVER UPDATE THE MASTER - this happens automatically

-- This needs to be kept consistent.  This is the current version of the database
##BeginVersionInfo
<?xml version="1.0" encoding="utf-8"?>
<DatabaseVersionInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">    
    <!-- WARNING: Make sure these values are consistent to what is inserted in the Version table -->    
    <!-- When a new database comes out, increment this version -->
    <CurrentVersion>5.0.1.015</CurrentVersion>
    <!-- If older versions of the UI can't work with this DB, increment this version -->
    <MinRequiredUIVersion>6.0.0.0</MinRequiredUIVersion>
    <!-- If the new DB version requires changes to the LPS binaries or schemas, increment this version -->
    <MinRequiredLPSVersion>5.1.004</MinRequiredLPSVersion>
    <!-- If we can't alter the database anymore, we need to reset this to match the updatable version in the alter script -->
    <MinUpdatableDBVersion>5.0.1.014</MinUpdatableDBVersion>
</DatabaseVersionInfo>  
##EndVersionInfo

##BeginMigrateSection
-- This must always be the first line in any update section
##Version 5.0.1.015

-- dbo.Agent
-- dbo.Agent_DCP_DataService
-- dbo.Data_Collection_Package
-- dbo.Data_Service
-- dbo.DCP_Status

-- dbo.Application_Instances
-- dbo.Apps_Deployment_Status

-- dbo.Architecture_Preferences
-- dbo.Client_State_Details
-- dbo.Providers
-- dbo.Categories
-- dbo.SubCategories

-- dbo.Computer_Report

-- dbo.Deployment_Enabled_OS
-- dbo.Deployment_OS
-- dbo.OS
-- dbo.Deployment_Status

-- dbo.Application_Rating
-- dbo.Appication_Votes
-- dbo.Device_Rating

-- dbo.DeviceType
-- dbo.PropertyType
-- dbo.IndicatorType
-- dbo.IssueType

-- dbo.EventCategory
-- dbo.EventType
-- dbo.GeneralFeedback_Report
-- dbo.TimelineEvents
-- dbo.UserMachine
-- dbo.Users

-- dbo.trace_db
-- dbo.Trace_tbl

-- dbo.Issue_Affects_OS
-- dbo.Issues
-- dbo.Issues_Associated_With_App
-- dbo.Issues_Associated_With_Url
-- dbo.IssueSolution
-- dbo.Solution
-- dbo.IssueType
-- dbo.Localized_Issue
-- dbo.Localized_Solution

-- dbo.Mitigation_Package
-- dbo.MitigationPkg_AppFix
-- dbo.MitigationPkg_WebsiteFix
-- dbo.Website_Fix

-- Populate Machines table with relevant data.
INSERT INTO [NewAct].[dbo].[Machines]
SELECT pm.macAddress, lm.osID, pm.macAddress, lm.servicePackMinor, lm.machineName, lm.domainName,
    lm.windowsDirectory, lm.systemDirectory, lm.rootPath, lm.priority, lm.appCount, lm.deviceCount,
    pm.ram, pm.pageFile, pm.virtualMem, pm.assetTag, pm.chassisSerialNumber, pm.chassisVendorName,
    pm.processorVendorName, pm.processorName, pm.clockSpeed, pm.processorArchitecture, pm.processorFamily,
    pm.latestLog, pm.latestLogTimestamp, pm.smsGuid, pm.smsHwId
FROM [OldAct].[dbo].[Physical_Machine] pm
JOIN [OldAct].[dbo].[Logical_Machine] lm
ON pm.macAddress = lm.macAddress
GO

-- Populate Applications table with relevant data.
INSERT INTO [NewAct].[dbo].[Applications]
SELECT app.identity_hash, NULL, NEWID(), app.partial_key_hash, app.attrib_match_string,app.Type,app.appName,
       app.componentType, app.vendorName, app.version, app.language, app.osComponent, app.priority,
       app.timeFound, app.checksum, app.computerCount, app.createdDate, app.lastSyncTime
FROM [OldAct].[dbo].[Applications] app
GO

-- Populate Application_Groups table with relevant data.
INSERT INTO [NewAct].[dbo].[Application_Groups] (groupID, headerApp, computerCount, appCount)
SELECT app.memberOf, app.appIdentity, app.computerCount, 1 
FROM [NewAct].[dbo].[Applications] app
GO

-- Populate App_Installed_On_Machine
INSERT INTO [NewAct].[dbo].[App_Installed_On_Machine]
SELECT app.appIdentity,data.osID, machine.machineID,data.servicePackMinor
FROM [OldAct].[dbo].[App_Installed_On_Machine] data
LEFT JOIN [NewAct].[dbo].[Applications] app
ON app.appID = data.appID
LEFT JOIN [NewAct].[dbo].[Machines] machine
ON data.macAddress = machine.macAddress AND data.osID = machine.osID
ORDER BY app.appIdentity
GO

-- Popultate Application_Fix
INSERT INTO [NewAct].[dbo].[Application_Fix]
SELECT data.fixID, data.solutionID, app.appIdentity,data.mitigationString
FROM [OldAct].[dbo].[Application_Fix] data
LEFT JOIN [NewAct].[dbo].[Applications] app
ON data.appID = app.appID
GO

-- Populate application indicators
INSERT INTO [NewAct].[dbo].[Application_Indicators]
SELECT data.indicatorID 
      ,data.type 
      ,data.indicatorGUID 
      ,data.indicatorOsComponent 
      ,data.indicatorUniqueId 
      ,data.indicatorName 
      ,data.timeCreatedModified 
      ,data.arpCompanyName 
      ,data.arpProductVersion 
      ,data.arpLastUsedDate 
      ,data.arpRegistryPath 
      ,data.arpUninstallString 
      ,data.arpDisplayName 
      ,data.arpTimesUsed 
      ,data.arpPath 
      ,data.arpRegistrySubKey 
      ,data.arpHotfix 
      ,data.mnftProductVersion 
      ,data.mnftDescription 
      ,data.mnftProcessorArch 
      ,data.mnftProductName 
      ,data.mnftPath 
      ,data.mnftCompanyName 
      ,data.mnftFileName 
      ,data.mnftPublicKeyToken 
      ,data.mnftType 
      ,data.mnftCreated 
      ,data.mnftModified 
      ,data.mnftFusionType 
      ,data.msiCompanyName 
      ,data.msiProductVersion 
      ,data.msiInstallDate 
      ,data.msiProductGUID 
      ,data.msiLanguageID 
      ,data.msiPackageGUID 
      ,data.msiProductName 
      ,data.dirPath 
      ,data.dirName 
      ,data.dirCreated 
      ,data.dirModified 
      ,data.regRegistryPath 
      ,data.regFileName 
      ,data.regPath 
      ,data.regName 
      ,data.regCommandLine 
      ,data.regRegistryRun 
      ,data.regexExtension 
      ,data.shellParentName 
      ,data.shellPath 
      ,data.shellTargetPath 
      ,data.shellLinkPath 
      ,data.shellName 
      ,data.shellTargetArg 
      ,data.shellLinkDirectory 
      ,data.shellFileName 
      ,data.shellHasFolderSibling 
      ,data.appRegistryPath 
      ,data.appPath 
      ,data.appFilePath 
      ,data.appName 
      ,data.scmName 
      ,data.scmPath 
      ,data.scmDisplayName 
      ,data.scmDirectory 
      ,data.scmDll 
      ,data.scmCommandLine 
      ,data.scmStartType 
      ,data.scmServiceType 
      ,data.scmSvcHost 
      ,data.scmFileName 
      ,data.winProductName 
      ,data.winInfo 
      ,data.winDescription 
      ,data.winPath 
      ,data.pevPath 
      ,data.pevName 
      ,data.pevRegistryPath 
      ,data.fexPath 
      ,data.fexRegistryPath 
      ,data.fexFile 
      ,data.fexName 
      ,data.fexExtension 
      ,app.appIdentity 
      ,data.LastUpdatedDateTime 
  FROM [OldAct].[dbo].[Application_Indicators] data
  JOIN [NewAct].[dbo].[Applications] app
  ON data.appID = app.appID
GO

-- Populate deployment_Status and user rating in Application_Report
  UPDATE [NewAct].[dbo].[Application_Report]
  SET myRating32 = CASE WHEN data.myRating32 IS NOT NULL
                        THEN data.myRating32
                        ELSE 0
                        END, 
     deploymentStatus = CASE WHEN data.deploymentStatus IS NOT NULL
                        THEN data.deploymentStatus
                        ELSE 0
                        END, 
     myRating64 = CASE WHEN data.myRating64 IS NOT NULL
                  THEN data.myRating64
                  ELSE 0
                  END
      FROM [NewAct].[dbo].[Application_Report] report
      LEFT JOIN [NewAct].[dbo].[Applications] app
      ON report.appIdentity = app.appIdentity
      LEFT JOIN [OldAct].[dbo].[Application_Report] data      
      ON data.appID = app.appID AND data.osID = report.osID
GO

-- Populate categories
DELETE FROM [NewAct].[dbo].[SubCategories]
GO

DELETE FROM [NewAct].[dbo].[Categories]
GO

SET IDENTITY_INSERT [NewAct].[dbo].[Categories] ON
GO

INSERT INTO [NewAct].[dbo].[Categories] (categoryId, category)
SELECT categoryId, category FROM [OldAct].[dbo].[Categories] ORDER BY categoryId
GO

SET IDENTITY_INSERT [NewAct].[dbo].[Categories] OFF
GO

SET IDENTITY_INSERT [NewAct].[dbo].[SubCategories] ON
GO

INSERT INTO [NewAct].[dbo].[SubCategories] (subCategoryId, subCategory,categoryId)
SELECT subCategoryId, subCategory,categoryId FROM [OldAct].[dbo].[SubCategories] ORDER BY subCategoryId
GO


SET IDENTITY_INSERT [NewAct].[dbo].[SubCategories] OFF
GO

INSERT INTO [NewAct].[dbo].[Categorized_Applications]
SELECT app.appIdentity,data.categoryId, data.subCategoryId, data.publishedDate
FROM [OldAct].[dbo].[Categorized_Applications] data
LEFT JOIN [NewAct].[dbo].[Applications] app
ON app.appID = data.objectID
GO

INSERT INTO [NewAct].[dbo].[Categorized_Machines]
SELECT machine.machineID,data.osID, data.categoryId, data.subCategoryId
FROM [OldAct].[dbo].[Categorized_Machines] data
LEFT JOIN [NewAct].[dbo].[Machines] machine
ON machine.machineID = data.macAddress

GO

-- Populate IPAddress-Machine
INSERT INTO [NewAct].[dbo].[IPAddress_Machine]
SELECT data.IPAddress, machine.machineID,machine.osID
FROM [OldAct].[dbo].[IPAddress_Machine] data
JOIN [NewAct].[dbo].[Machines] machine
ON machine.machineID = data.macAddress

GO

-- Populate device related tables

INSERT INTO [NewAct].[dbo].[Devices]
SELECT * FROM [OldAct].[dbo].[Devices]
GO

INSERT INTO [NewAct].[dbo].[Categorized_Devices]
SELECT * FROM [OldAct].[dbo].[Categorized_Devices]
GO

INSERT INTO [NewAct].[dbo].[Machines_Devices]
SELECT machine.machineID, machine.osID, data.deviceId, data.deviceType
FROM [OldAct].[dbo].[Physical_Machine_Devices] data
JOIN [NewAct].[dbo].[Machines] machine
ON data.macAddress = machine.macAddress
GO

INSERT INTO [NewAct].[dbo].[PnPDevice]
SELECT * FROM [OldAct].[dbo].[PnPDevice]
GO

INSERT INTO [NewAct].[dbo].[PnPDevice_Installed_Driver]
SELECT * FROM [OldAct].[dbo].[PnPDevice_Installed_Driver]
GO

INSERT INTO [NewAct].[dbo].[PnPDevice_PnPID]
SELECT * FROM [OldAct].[dbo].[PnPDevice_PnPID]
GO

INSERT INTO [NewAct].[dbo].[PnPDevice_Machines]
SELECT data.deviceId, machine.machineID, machine.osID
FROM [OldAct].[dbo].[PnPDevice_Physical_Machine] data
JOIN [NewAct].[dbo].[Machines] machine
ON data.macAddress = machine.macAddress

GO

EXEC [NewAct].[dbo].[GenDeviceReports_sp]
GO


-- Website Related Tables

INSERT INTO [NewAct].[dbo].[File_Opens_Url] 
SELECT ieceUrlPath, priority FROM [OldAct].[dbo].[File_Opens_Url]
GO

INSERT INTO [NewAct].[dbo].[Categorized_WebSites]
SELECT iece.ieceUrlId,data.categoryID, data.subCategoryId
FROM [OldAct].[dbo].[Categorized_WebSites] data
LEFT JOIN [OldAct].[dbo].[File_Opens_Url] oldIece
ON data.ieceUrlId = oldIece.ieceUrlId
LEFT JOIN [NewAct].[dbo].[File_Opens_Url] iece
ON iece.ieceUrlPath = oldIece.ieceUrlPath

GO

-- Tagging related

INSERT INTO [NewAct].[dbo].[Tagged_Machines]
SELECT data.macAddress, data.osID, data.tag
FROM [OldAct].[dbo].[Tagged_Machines] data
GO

INSERT INTO [NewAct].[dbo].[Tagged_Applications]
SELECT app.appIdentity, data.tag
FROM [OldAct].[dbo].[Tagged_Applications] data
LEFT JOIN [NewAct].[dbo].[Applications] app
ON data.appID = app.appID

GO 

-- Populate unmatched apps.

INSERT INTO [NewAct].[dbo].[Unmatched_AppIds]
    SELECT CASE WHEN msi.ID IS NOT NULL 
                THEN msi.ID
                ELSE arp.ID
           END as appIdentity,
           CASE WHEN msi.indicatorId IS NOT NULL
                THEN msi.indicatorID
                ELSE ''
           END as msiIndicatorId,
           CASE WHEN arp.indicatorId IS NOT NULL
                THEN arp.indicatorID
                ELSE ''
           END as arpIndicatorID,
           msi.Name, 
           msi.Version, 
           msi.Vendor, 
           msi.Language, 
           arp.AltName, 
           arp.AltVersion, 
           arp.AltVendor, 
           arp.AltLanguage   
    FROM
    (
        SELECT *                      
        FROM
        (
            SELECT appIdentity as ID
            FROM [NewAct].[dbo].[Applications]
            WHERE appID IS NOT NULL AND programID IS NULL
        ) app
        JOIN 
        (
            SELECT DISTINCT appIdentity,
                   indicatorId,
                   msiProductName as Name,
                   msiProductVersion as Version,
                   msiCompanyName as Vendor,
                   msiLanguageID as Language
            FROM [NewAct].[dbo].[Application_Indicators]
            WHERE msiProductName IS NOT NULL
        ) aimsi
        ON app.ID = aimsi.appIdentity 
    ) msi
    FULL JOIN
    (          
        SELECT *                      
        FROM
        (
            SELECT appIdentity as ID
            FROM [NewAct].[dbo].[Applications]
            WHERE appID IS NOT NULL AND programID IS NULL
        ) app    
        JOIN 
        (
            SELECT DISTINCT appIdentity,
                   indicatorId,
                   arpDisplayName as AltName,
                   arpProductVersion as AltVersion,
                   arpCompanyName as AltVendor,
                   0 as AltLanguage
            FROM [NewAct].[dbo].[Application_Indicators]
            WHERE arpDisplayName IS NOT NULL
        ) aiarp
        ON app.ID = aiarp.appIdentity     
    ) arp
    ON msi.ID = arp.ID
    ORDER by appIdentity
GO

##EndMigrateSection