/*
_____________________________________________

	Update from DB Schema 8.1.0 to 8.2.0, add new field to Tieline2Group table. Moved fields from Tieline2Group to Tieline2GroupBinding
    Added VirtualLevelExplicitMapping and Tieline2Embedded tables.
	Removed LevelSetDetail_Coherent column added PhysLevel_Coherent and PhysLevel_CoherentDelay fields.
*/

use control

DECLARE @UpdateNeeded INT;
SET @UpdateNeeded = 0;

DECLARE @DESC_SIZE nvarchar(4)
set @DESC_SIZE = '255'

DECLARE @returnStatus  int;
DECLARE @tableName varchar(100);
DECLARE @columnName varchar(100);

PRINT 'Executing ControlSchemaUpdate_810to820.sql.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2_CleanSwitch' )	
	BEGIN
		PRINT 'Adding Tieline2_CleanSwitch column'
		ALTER TABLE Tieline2 ADD Tieline2_CleanSwitch int;		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2.Tieline2_CleanSwitch column already exists.'
END
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Group]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2Group_Type' )	
	BEGIN
		PRINT 'Adding Tieline2Group_Type column'
		ALTER TABLE Tieline2Group ADD Tieline2Group_Type int DEFAULT 1;		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2Group.Tieline2Group_Type column already exists.'
	
	SET @returnStatus  = 0;
	SET @tableName = 'Tieline2Group';
	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2Group_SourceVirtualLevel')	
	BEGIN
		/* Drop foreign key constraint before dropping the column */
		SET @columnName = 'Tieline2Group_SourceVirtualLevel';
		EXEC NV_DropColumnConstraints @tableName, @columnName, @returnStatus OUTPUT;
		IF ( @returnStatus  != 1 )
			PRINT 'Dropping foreign key constraint on ' + @columnName+ ' failed. Trying to drop column anyway';

		PRINT 'Deleting Tieline2Group.Tieline2Group_SourceVirtualLevel column';
		ALTER TABLE Tieline2Group DROP COLUMN Tieline2Group_SourceVirtualLevel;
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2Group.Tieline2Group_SourceVirtualLevel column already deleted.';
		
	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2Group_UpstreamTielineRequired')	
	BEGIN
		/* Drop default constraint before dropping the column */
		SET @columnName = 'Tieline2Group_UpstreamTielineRequired';
		EXEC NV_DropColumnDefaultConstraint @tableName, @columnName, @returnStatus OUTPUT;
		IF ( @returnStatus  != 1 )
			PRINT 'Dropping default constraint on ' + @columnName+ ' failed. Trying to drop column anyway';
			
		PRINT 'Deleting Tieline2Group.Tieline2Group_UpstreamTielineRequired column';
		ALTER TABLE Tieline2Group DROP COLUMN Tieline2Group_UpstreamTielineRequired;
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2Group.Tieline2Group_UpstreamTielineRequired column already deleted.';

	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2Group_DownstreamPort')	
	BEGIN
		/* Drop default constraint before dropping the column */
		SET @columnName = 'Tieline2Group_DownstreamPort';
		EXEC NV_DropColumnDefaultConstraint @tableName, @columnName, @returnStatus OUTPUT;
		IF ( @returnStatus  != 1 )
			PRINT 'Dropping default constraint on ' + @columnName+ ' failed. Trying to drop column anyway';
			
		PRINT 'Deleting Tieline2Group.Tieline2Group_DownstreamPort column';
		ALTER TABLE Tieline2Group DROP COLUMN Tieline2Group_DownstreamPort;
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2Group.Tieline2Group_DownstreamPort column already deleted.';
		
	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2Group_DownstreamTielineRequired')	
	BEGIN
		/* Drop default constraint before dropping the column */
		SET @columnName = 'Tieline2Group_DownstreamTielineRequired';
		EXEC NV_DropColumnDefaultConstraint @tableName, @columnName, @returnStatus OUTPUT;
		IF ( @returnStatus  != 1 )
			PRINT 'Dropping default constraint on ' + @columnName+ ' failed. Trying to drop column anyway';
			
		PRINT 'Deleting Tieline2Group.Tieline2Group_DownstreamTielineRequired column';
		ALTER TABLE Tieline2Group DROP COLUMN Tieline2Group_DownstreamTielineRequired;
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2Group.Tieline2Group_DownstreamTielineRequired column already deleted.';

	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2Group_UpstreamPort')	
	BEGIN
		/* Drop default constraint before dropping the column */
		SET @columnName = 'Tieline2Group_UpstreamPort';
		EXEC NV_DropColumnDefaultConstraint @tableName, @columnName, @returnStatus OUTPUT;
		IF ( @returnStatus  != 1 )
			PRINT 'Dropping default constraint on ' + @columnName+ ' failed. Trying to drop column anyway';
			
		PRINT 'Deleting Tieline2Group.Tieline2Group_UpstreamPort column';
		ALTER TABLE Tieline2Group DROP COLUMN Tieline2Group_UpstreamPort;
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2Group.Tieline2Group_UpstreamPort column already deleted.';
		
END
ELSE
	PRINT 'Error: Tieline2Group table does not exist';
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2GroupBinding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	

	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'Tieline2GroupBinding_VirtualLevel' )	
	BEGIN
		PRINT 'Adding Tieline2GroupBinding_VirtualLevel column'
		ALTER TABLE Tieline2GroupBinding ADD Tieline2GroupBinding_VirtualLevel int REFERENCES VirtualLevel(VirtualLevel_ID);		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'Tieline2GroupBinding.Tieline2GroupBinding_VirtualLevel column already exists.'
END
ELSE
	PRINT 'Error: Tieline2GroupBinding table does not exist';
	
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Embedded]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	PRINT 'Creating table Tieline2Embedded.'
	CREATE TABLE dbo.Tieline2Embedded (
			Tieline2Embedded_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
			Tieline2Embedded_DownstreamPort int NOT NULL,
			CONSTRAINT PK_TIELINE2Embedded PRIMARY KEY
		(
			Tieline2Embedded_Tieline2
		)
	);
	SET @UpdateNeeded = 1;
END
ELSE
	PRINT 'Tieline2Embedded table already exists';

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[VirtualLevelExplicitMapping]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	PRINT 'Creating table VirtualLevelExplicitMapping.'
	CREATE TABLE dbo.VirtualLevelExplicitMapping (
			VirtualLevelExplicitMapping_SourceVirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),	
			VirtualLevelExplicitMapping_DestinationVirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
			CONSTRAINT PK_VIRTUALLEVELEXPLICITMAPPING PRIMARY KEY
			(
				VirtualLevelExplicitMapping_SourceVirtualLevel,
				VirtualLevelExplicitMapping_DestinationVirtualLevel
			)
		)
	SET @UpdateNeeded = 1;
END
ELSE
	PRINT 'VirtualLevelExplicitMapping table already exists';
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[LevelSetDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'LevelSetDetail_Coherent' )	
	BEGIN
		SET @columnName = 'LevelSetDetail_Coherent';
		SET @tableName = 'LevelSetDetail';
		EXEC NV_DropColumnDefaultConstraint @tableName, @columnName, @returnStatus OUTPUT;
		IF ( @returnStatus  != 1 )
			PRINT 'Dropping default constraint on ' + @columnName+ ' failed. Trying to drop column anyway';
		PRINT 'Deleting LevelSetDetail_Coherent column'
		ALTER TABLE LevelSetDetail DROP COLUMN LevelSetDetail_Coherent;		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'LevelSetDetail. LevelSetDetail_Coherent column already exists.'
END
ELSE
	PRINT 'Error: LevelSetDetail table does not exist';
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[PhysLevel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	

	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'PhysLevel_Coherent' )	
	BEGIN
		PRINT 'Adding PhysLevel_Coherent column'
		ALTER TABLE PhysLevel ADD PhysLevel_Coherent int DEFAULT(0);		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'PhysLevel.PhysLevel_Coherent column already exists.'
		
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where Column_name = 'PhysLevel_CoherentDelay' )	
	BEGIN
		PRINT 'Adding PhysLevel_CoherentDelay column'
		ALTER TABLE PhysLevel ADD PhysLevel_CoherentDelay int DEFAULT(0);		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'PhysLevel.PhysLevel_CoherentDelay column already exists.'
END
ELSE
	PRINT 'Error: PhysLevel table does not exist';

SET @UpdateNeeded = 1;

IF ( @UpdateNeeded = 1 )
BEGIN
	PRINT 'Inserting version information.'
	INSERT INTO Version(Version_SWVersion,Version_SVNumber, Version_Description) VALUES( 
		'8.2.0','SV0238-08F0', 'Update of NV Version 8.1.0 to Version 8.2.0 database schema.')
END

GO

