/*
 * $Id: migprep.sql 98060 2010-10-12 18:47:01Z mmarkevics $
 *
 * Prepare to migrate a ManageSoft 8.2 database into ManageSoft 8.4 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 8.4 which will create the stored procedures.
 *
 * The aim here is to make minimal changes that will allow schema.sql
 * to succeed in creating any new objects, before migpost.sql finishes
 * reshaping some tables and dropping any unused objects.
 *
 * Copyright 1995-2010 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft

IF NOT EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep')
	INSERT DatabaseConfiguration (Property, Value) VALUES ('MigrationStep', 0)
ELSE
BEGIN
	DECLARE @step int
	SELECT @step = Value FROM DatabaseConfiguration WHERE Property = 'MigrationStep'
	PRINT 'Restarting migration from step '+STR(@step + 1, 2)
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 0)
BEGIN
	--Drop unused View
	IF EXISTS (
		SELECT * FROM sysobjects WHERE xtype = 'V'
		AND name = 'V6SoftwareOccurrence'
	)
		DROP VIEW V6SoftwareOccurrence

	--Normalize ComputerOUID out of SoftwareOccurrence
	IF (Object_ID('FK_SoftwareOccurrence_ComputerOU') IS NOT NULL)
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT FK_SoftwareOccurrence_ComputerOU

	IF (Object_ID('SoftwareOccurrenceBySWComputer') IS NOT NULL)
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT SoftwareOccurrenceBySWComputer

	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'SoftwareByOrganizationComputer')
	BEGIN
		DROP INDEX SoftwareOccurrence.SoftwareByOrganizationComputer
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'ComputerOUID'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence
			DROP COLUMN ComputerOUID
	END
	
	--Normalize UserOUID out of SoftwareOccurrence
	IF (Object_ID('FK_SoftwareOccurrence_UserOU') IS NOT NULL)
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT FK_SoftwareOccurrence_UserOU
			
	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'SoftwareByOrganizationUser')
	BEGIN
		DROP INDEX SoftwareOccurrence.SoftwareByOrganizationUser
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'UserOUID'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence
			DROP COLUMN UserOUID
	END

	--Normalize ComputerOUID out of Softwarefile
	IF (Object_ID('FK_SoftwareFile_ComputerOU') IS NOT NULL)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT FK_SoftwareFile_ComputerOU

	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'SoftwareFileByComputer')
	BEGIN
		DROP INDEX SoftwareFile.SoftwareFileByComputer
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFile'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFile' AND COLUMN_NAME = 'ComputerOUID'
	)
	BEGIN
		ALTER TABLE SoftwareFile
			DROP COLUMN ComputerOUID
	END

	--Normalize ComputerOUID out of HardwareObject
	IF (Object_ID('FK_HardwareObject_ComputerOU') IS NOT NULL)
		ALTER TABLE HardwareObject
			DROP CONSTRAINT FK_HardwareObject_ComputerOU

	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'HardwareObjectByOrganization')
	BEGIN
		DROP INDEX HardwareObject.HardwareObjectByOrganization
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'HardwareObject'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'HardwareObject' AND COLUMN_NAME = 'ComputerOUID'
	)
	BEGIN
		ALTER TABLE HardwareObject
			DROP COLUMN ComputerOUID
	END
	
	UPDATE DatabaseConfiguration SET Value = 1 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	IF (Object_ID('PK_HardwareObject') IS NOT NULL)
		ALTER TABLE HardwareObject
			DROP CONSTRAINT PK_HardwareObject
			-- was PRIMARY KEY NONCLUSTERED(ComputerID, HardwareName, Occurrence)

	UPDATE DatabaseConfiguration SET Value = 2 WHERE Property = 'MigrationStep'
END

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 2)
BEGIN
	IF NOT EXISTS(
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = 'NetworkDevice' AND COLUMN_NAME = 'SQLDiscoveredBy'
	)
	BEGIN
		ALTER TABLE NetworkDevice ADD SQLDiscoveredBy NVARCHAR(128) NULL
		ALTER TABLE NetworkDevice ADD SQLPorts NVARCHAR(128) NULL
	END

	UPDATE DatabaseConfiguration SET Value = 3 WHERE Property = 'MigrationStep'
END

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 3)
BEGIN
	IF NOT EXISTS(
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = 'NetworkDeviceCalculatedMember' AND COLUMN_NAME = 'IsSQL'
)
	BEGIN
		ALTER TABLE NetworkDeviceCalculatedMember ADD IsSQL bit NULL DEFAULT 0
	END

	UPDATE DatabaseConfiguration SET Value = 4 WHERE Property = 'MigrationStep'
END

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 4)
BEGIN
	-- Add the new columns to ServiceUser
	IF NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'ServiceUser' AND COLUMN_NAME = 'AccountStatus'
	)
	BEGIN
		ALTER TABLE ServiceUser
			ADD AccountStatus nvarchar(256) NULL
	END

	IF NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'ServiceUser' AND COLUMN_NAME = 'CreationDate'
	)
	BEGIN
		ALTER TABLE ServiceUser
			ADD CreationDate datetime NULL
	END

	IF NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'ServiceUser' AND COLUMN_NAME = 'LastLogonDate'
	)
	BEGIN
		ALTER TABLE ServiceUser
			ADD LastLogonDate datetime NULL
	END

	UPDATE DatabaseConfiguration SET Value = 5 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 5)
BEGIN
	IF NOT EXISTS(
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = 'ServiceProvider' AND COLUMN_NAME = 'CreationDate'
)
	BEGIN
		ALTER TABLE ServiceProvider ADD CreationDate datetime NULL 
	END

	UPDATE DatabaseConfiguration SET Value = 6 WHERE Property = 'MigrationStep'
END

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 6)
BEGIN
	UPDATE DatabaseConfiguration SET Value = 1000 WHERE Property = 'MigrationStep'
END
GO

