/*
_____________________________________________

	Update from DB Schema 6.0.9 to 8.0.0, add new fields to Tieline2Group table
	Incremental update within 8.0.0 from SV0238-08A0 to SV0238-08B0 add new fields to PhysConn table and add two new tables: GvNodeFrame and GvNodePhysConn for GvNode support
	Incrementatl update within 8.0.0 from SV0238-08B0 to SV0238-08C0 one more field to PhysConn and one more field to GvNodePhysConn. Correct typo of column name
*/

use control

DECLARE @UpdateNeeded INT;
SET @UpdateNeeded = 0;

PRINT 'Executing ControlSchemaUpdate_609to800.sql.'

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
                     Table_name = 'Tieline2Group'
                     and Column_name = 'Tieline2Group_SourceVirtualLevel')

	BEGIN
		PRINT 'Adding Tieline2Group_SourceVirtualLevel column'
		ALTER TABLE Tieline2Group ADD Tieline2Group_SourceVirtualLevel int REFERENCES VirtualLevel(VirtualLevel_ID);		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT' Tieline2Group_SourceVirtualLevel column already exists'

	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'Tieline2Group'
                     and Column_name = 'Tieline2Group_UpstreamTielineRequired')

	BEGIN
		PRINT 'Adding Tieline2Group_UpstreamTielineRequired column'
		ALTER TABLE Tieline2Group ADD Tieline2Group_UpstreamTielineRequired int DEFAULT 0;	
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT' Tieline2Group_UpstreamTielineRequired column already exists'

	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'Tieline2Group'
                     and Column_name = 'Tieline2Group_Downstreamport')
	BEGIN
		PRINT 'Adding Tieline2Group_Downstreamport column'
		ALTER TABLE Tieline2Group ADD Tieline2Group_Downstreamport int DEFAULT 0;		
        SET @UpdateNeeded = 1;		
	END
	ELSE
		PRINT' Tieline2Group_Downstreamport column already exists'
	
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'Tieline2Group'
                     and Column_name = 'Tieline2Group_DownstreamTielineRequired')
	BEGIN
		PRINT 'Adding Tieline2Group_DownstreamTielineRequired column'
		ALTER TABLE Tieline2Group ADD Tieline2Group_DownstreamTielineRequired int DEFAULT 0;	
        SET @UpdateNeeded = 1;		
	END
	ELSE
		PRINT' Tieline2Group_DownstreamTielineRequired column already exists'

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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[PhysConn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'PhysConn'
                     and Column_name = 'PhysConn_IPAddress')	
	BEGIN
		PRINT 'Adding PhysConn_IPAddress column'
		ALTER TABLE PhysConn ADD PhysConn_IPAddress varchar(20);
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT' PhysConn_IPAddress column already exists'
		
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'PhysConn'
                     and Column_name = 'PhysConn_IPPort')	
	BEGIN
		PRINT 'Adding PhysConn_IPPort column'
		ALTER TABLE PhysConn ADD PhysConn_IPPort int NOT NULL DEFAULT 0;				/* alter table required NOT NULL columns to contain a default value */		
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT' PhysConn_IPPort column already exists'
		
    IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'PhysConn'
                     and Column_name = 'PhysConn_MulticastGroupIPAddress')
    BEGIN
        PRINT 'Adding PhysConn_MulticastGroupIPAddress column'        ALTER TABLE PhysConn ADD PhysConn_MulticastGroupIPAddress varchar(20);
        SET @UpdateNeeded = 1;
    END
    ELSE
        PRINT 'PhysConn_MulticastGroupIPAddress column already exists'

END
ELSE
	PRINT 'Error: PhysConn table does not exist'	
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[LevelSetDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN   
    IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'LevelSetDetail'
                     and Column_name = 'LevelSetDetail_Coherent') 
    BEGIN
        PRINT 'Adding LevelSetDetail_Coherent column'
        ALTER TABLE LevelSetDetail ADD LevelSetDetail_Coherent int NOT NULL DEFAULT 0;          /* alter table required NOT NULL
columns to contain a default value */
        SET @UpdateNeeded = 1;
    END 
    ELSE
        PRINT' LevelSetDetail_Coherent column already exists'
END 
ELSE
    PRINT 'Error: LevelSetDetail table does not exist'  
	
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[GvNodeFrame]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Adding GvNodeFrame table'
	CREATE TABLE GvNodeFrame (GvNodeFrame_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			GvNodeFrame_Description nvarchar (255) NULL,
			GvNodeFrame_Group int NOT NULL,
			GvNodeFrame_Number int NOT NULL,

			CONSTRAINT ucGVNodeFrame UNIQUE (GvNodeFrame_Group, GvNodeFrame_Number)

		)
	SET @UpdateNeeded = 1;
END
ELSE
	PRINT 'GvNodeFrame table already exists.'

/*
	New table for versions SV0238-08A0 & SV0238-08B0, but we've added one more column for version SV0238-08C0, just drop the table and
	re-add, no one should have made any entries in it yet. Only do this if we're dealing with an older
    version of the schema, the one where GvNodeFrame's primary key is GvNodeFrame_ID
 */
IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'GvNodeFrame'
                     and Column_name = 'GvNodeFrame_ID') 
BEGIN
	IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[GvNodePhysConn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE GvNodePhysConn
	END

	PRINT 'Adding GvNodePhysConn table'
	CREATE TABLE GvNodePhysConn (
		GvNodePhysConn_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,		
		GvNodePhysConn_GvNodeFrame int NOT NULL REFERENCES GvNodeFrame(GvNodeFrame_ID),
		GvNodePhysConn_AggregationPortStreamID int NOT NULL,
		GvNodePhysConn_AggregationPort int NOT NULL,
		GvNodePhysConn_PortDir int NOT NULL CHECK (GvNodePhysConn_PortDir  IN (1, 2, 3)),
		GvNodePhysConn_IPAddress varchar(20) NOT NULL,
		GvNodePhysConn_IPPort int NOT NULL,
		GvNodePhysConn_RouterPort int NOT NULL,

		CONSTRAINT ucGVNodePhysConn UNIQUE (GvNodePhysConn_GvNodeFrame, GvNodePhysConn_AggregationPortStreamID, GvNodePhysConn_AggregationPort, GvNodePhysConn_PortDir, GvNodePhysConn_IPAddress, GvNodePhysConn_IPPort)
		)
		SET @UpdateNeeded = 1;
END
ELSE
	PRINT 'GvNodePhysConn table not created because GvNodeFrame table has a newer schema since GvNodeFrame_ID 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.0.0','SV0238-08C0', 'Update of NV Version 6.0.9 to Version 8.0.0 database schema.')
END

GO

