/*
_____________________________________________

	Update from DB Schema 3.6 to 5.0.0, to add tieline2 tables

*/

use master

PRINT 'Dropping database control_edit'
IF EXISTS (select * from dbo.sysdatabases where name = 'control_edit')
BEGIN
DROP DATABASE control_edit
END
GO

PRINT 'Setting max database size'
ALTER DATABASE CONTROL
MODIFY FILE
(name = control_dat,
 MAXSIZE = 100MB)
GO 

use control

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[TLClassDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE TLClassDetail
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[InterLSMapDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE InterLSMapDetail
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE Tieline
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[TielineClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE TielineClass
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[InterLvlsetMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE InterLvlsetMap
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[AppPair]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE AppPair
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[ConfigIdentity]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE ConfigIdentity
END
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2 table.'
	EXEC('CREATE TABLE dbo.Tieline2 (
		Tieline2_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2_Cost INT NOT NULL DEFAULT 1,
		Tieline2_Name varchar (24) NOT NULL,
		Tieline2_Description varchar (255)
	)')

END
ELSE
	PRINT 'Not creating Tieline2 table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[SignalType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating SignalType table.'
	EXEC('CREATE TABLE dbo.SignalType (
		SignalType_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		SignalType_Name varchar (24) NOT NULL,
		SignalType_Description varchar (255)
	)')

END
ELSE
	PRINT 'Not creating SignalType table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Remote]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Remote table.'
	EXEC('CREATE TABLE Remote (
		Remote_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Remote_Name varchar (24) NOT NULL,
		Remote_IP varchar(24) NOT NULL,
		Remote_Description varchar (255)
	)')

END
ELSE
	PRINT 'Not creating Remote table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2PortBinding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2PortBinding table.'
	EXEC('CREATE TABLE dbo.Tieline2PortBinding (
		Tieline2PortBinding_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2PortBinding_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2PortBinding_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2PortBinding_Direction int NOT NULL DEFAULT 1	
			CHECK(Tieline2PortBinding_Direction in (1, 2)),
		Tieline2PortBinding_Port int NOT NULL DEFAULT 0,
		Tieline2PortBinding_Remote int NULL REFERENCES Remote(Remote_ID),
		CONSTRAINT UNIQUE_TIELINE2PORTBINDING UNIQUE NONCLUSTERED
		(
			Tieline2PortBinding_PhysLevel,
			Tieline2PortBinding_Direction,
			Tieline2PortBinding_Port
		)
	)')

END
ELSE
	PRINT 'Not creating Tieline2PortBinding table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2SignalBinding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2SignalBinding table.'
	EXEC('CREATE TABLE dbo.Tieline2SignalBinding (
		Tieline2SignalBinding_PortBinding int NOT NULL REFERENCES Tieline2PortBinding(Tieline2PortBinding_ID),
		Tieline2SignalBinding_SignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		CONSTRAINT PK_TIELINE2SIGNALBINDING PRIMARY KEY
		(
			Tieline2SignalBinding_PortBinding,
			Tieline2SignalBinding_SignalType
		)
	)')

END
ELSE
	PRINT 'Not creating Tieline2SignalBinding table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[VirtualLevelSignalBinding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating VirtualLevelSignalBinding table.'
	EXEC('CREATE TABLE dbo.VirtualLevelSignalBinding (
		VirtualLevelSignalBinding_VirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
		VirtualLevelSignalBinding_SignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		CONSTRAINT PK_VIRTUALLEVELSIGNALBINDING PRIMARY KEY
		(
			VirtualLevelSignalBinding_VirtualLevel,
			VirtualLevelSignalBinding_SignalType
		)
	)')

END
ELSE
	PRINT 'Not creating VirtualLevelSignalBinding table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Path]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2Path table.'
	EXEC('CREATE TABLE dbo.Tieline2Path (
		Tieline2Path_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2Path_SourcePhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Path_SourceSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2Path_DestinationPhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Path_DestinationSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2Path_HopCount int NOT NULL DEFAULT 1,
		Tieline2Path_Cost int NOT NULL DEFAULT 1
		)')
	EXEC('create index IDX_T2PATH 
		on Tieline2Path ( Tieline2Path_SourcePhysLevel, 
		Tieline2Path_SourceSignalType, 
		Tieline2Path_DestinationPhysLevel, 
		Tieline2Path_DestinationSignalType)')
END
ELSE
	PRINT 'Not creating Tieline2Path table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2PathDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2PathDetail table.'
	EXEC('CREATE TABLE dbo.Tieline2PathDetail (
		Tieline2PathDetail_Tieline2Path int NOT NULL REFERENCES Tieline2Path(Tieline2Path_ID),
		Tieline2PathDetail_SignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2PathDetail_InputTieline int REFERENCES Tieline2(Tieline2_ID),
		Tieline2PathDetail_OutputTieline int REFERENCES Tieline2(Tieline2_ID),
		Tieline2PathDetail_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2PathDetail_InputPort int NOT NULL,
		Tieline2PathDetail_OutputPort int NOT NULL,
		Tieline2PathDetail_SegmentIndex int NOT NULL,
		CONSTRAINT PK_TIELINEPATHDETAILS PRIMARY KEY
		(
			Tieline2PathDetail_Tieline2Path,
			Tieline2PathDetail_PhysLevel,
			Tieline2PathDetail_InputPort,
			Tieline2PathDetail_OutputPort
		)
	)')

END
ELSE
	PRINT 'Not creating TielinePathDetail table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Reservation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2Reservation table.'
	EXEC('CREATE TABLE dbo.Tieline2Reservation (
		Tieline2Reservation_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2Reservation_User int NOT NULL REFERENCES NvUser(NvUser_ID),
		Tieline2Reservation_IPAddress varchar (20) NOT NULL,
		Tieline2Reservation_LTC varchar(20) NOT NULL,
		Tieline2Reservation_Timestamp datetime NOT NULL DEFAULT GETDATE(),
		Tieline2Reservation_SourcePhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Reservation_SourceSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2Reservation_SourcePort int NOT NULL,
		Tieline2Reservation_DestinationPhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Reservation_DestinationSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2Reservation_DestinationPort int NOT NULL,
		Tieline2Reservation_DownstreamPort int NOT NULL,
		Tieline2Reservation_UpstreamPort int NOT NULL
		CONSTRAINT PK_TIELINE2RESERVATION PRIMARY KEY
		(
			Tieline2Reservation_Tieline2,
			Tieline2Reservation_User,
			Tieline2Reservation_DestinationPhysLevel,
			Tieline2Reservation_DestinationPort
		)
	)')
END
ELSE
	PRINT 'Not creating Tieline2Reservation table, it already exists'
GO	

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2ReservationHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2ReservationHistory table.'
	EXEC('CREATE TABLE dbo.Tieline2ReservationHistory (
		Tieline2ReservationHistory_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2ReservationHistory_User int NOT NULL REFERENCES NvUser(NvUser_ID),
		Tieline2ReservationHistory_IPAddress varchar (20) NOT NULL,
		Tieline2ReservationHistory_LTC varchar(20) NOT NULL,
		Tieline2ReservationHistory_Timestamp datetime NOT NULL DEFAULT GETDATE(),
		Tieline2ReservationHistory_SourcePhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2ReservationHistory_SourceSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2ReservationHistory_SourcePort int NOT NULL,
		Tieline2ReservationHistory_DestinationPhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2ReservationHistory_DestinationSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2ReservationHistory_DestinationPort int NOT NULL,
		Tieline2ReservationHistory_DownstreamPort int NOT NULL,
		Tieline2ReservationHistory_UpstreamPort int NOT NULL,
		Tieline2ReservationHistory_ReleaseTimestamp datetime DEFAULT GETDATE()
	)')
END
ELSE
	PRINT 'Not creating Tieline2ReservationHistory table, it already exists'
GO	

if (@@error<>0)
	print 'bad.'
else
begin
PRINT 'Inserting version information.'
INSERT INTO Version(Version_SWVersion,Version_SVNumber, Version_Description) VALUES( 
	'5.0.0','SV0238-05A0', 'Update of NV Version 3.6 to Version 5.0.0 database schema.')
end
go

