-- Create New Tables And Views --
-- Note Compare to openXDA master on 03/30/2020 --

CREATE TABLE ChannelData
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL,
    RuntimeID INT NOT NULL,
    TimeDomainData VARBINARY(MAX) NULL,
    MarkedForDeletion INT NOT NULL,
	SeriesID INT NOT NULL,
	EventID INT NOT NULL,
	EventDataID INT NULL
)
GO


CREATE TABLE AccessLog(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    UserName varchar(200) NOT NULL,
    AccessGranted bit NOT NULL,
    CreatedOn datetime NOT NULL CONSTRAINT [DF_AccessLog_Timestamp]  DEFAULT (getutcdate())
)
GO

CREATE TABLE AssetType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(MAX) NULL
)
GO

CREATE TABLE Asset
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetTypeID INT NOT NULL REFERENCES AssetType(ID),
    AssetKey VARCHAR(50) NOT NULL UNIQUE,
    Description VARCHAR(MAX) NULL,
	AssetName VARCHAR(200) NOT NULL,
	VoltageKV FLOAT NOT NULL,
	Spare Bit NULL Default 0
)
GO

CREATE TABLE AssetSpare
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT UNIQUE NOT NULL REFERENCES Asset(ID),
    SpareAssetID INT NOT NULL REFERENCES Asset(ID)
)
GO

CREATE VIEW AssetSpareView AS
	SELECT 
	AssetSpare.ID AS ID,
	AssetSpare.AssetID AS AssetID,
	AssetSpare.SpareAssetID AS SpareAssetID,
	Parent.AssetName AS Assetname,
	Parent.AssetKey AS AssetKey,
	Child.AssetName AS SpareName,
	Child.AssetKey AS SpareKey,
	AssetType.Name AS AssetType
FROM
	AssetSpare LEFT JOIN Asset Child ON Child.ID = AssetSpare.SpareAssetID LEFT JOIN
	Asset Parent ON Parent.ID = AssetSpare.AssetID LEFT JOIN
	AssetType ON Parent.AssetTypeID = AssetType.ID
GO

CREATE TABLE Customer
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    CustomerKey VARCHAR(50) NOT NULL UNIQUE,
    Name VARCHAR(200) NULL
)
GO

CREATE TABLE CustomerAsset
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    CustomerID INT NOT NULL REFERENCES Customer(ID),
    AssetID INT NOT NULL REFERENCES Asset(ID)
)
GO

Create View CustomerAssetDetail AS
SELECT 
	CustomerAsset.ID AS ID,
	Customer.CustomerKey AS CustomerKey,
	Customer.Name AS CustomerName,
	Asset.AssetKey AS AssetKey,
	Asset.AssetName AS AssetName,
	AssetType.Name AS AssetType,
	Customer.ID AS CustomerID,
	Asset.ID AS AssetID
FROM
	CustomerAsset LEFT JOIN Asset ON Asset.ID = CustomerAsset.AssetID LEFT JOIN
	Customer ON Customer.ID = CustomerAsset.CustomerID LEFT JOIN
	AssetType ON Asset.AssetTypeID = AssetType.ID
GO


CREATE TABLE AssetRelationshipType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(MAX) NULL,
	BiDirectional BIT NOT NULL DEFAULT 0,
	JumpConnection VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0',
	PassThrough VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0',
)
GO

CREATE TABLE AssetRelationship
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetRelationshipTypeID INT NOT NULL REFERENCES AssetRelationshipType(ID),
    ParentID INT NOT NULL REFERENCES Asset(ID),
    ChildID INT NOT NULL REFERENCES Asset(ID)
)
GO

/* View with Procedures Due To Spare Logic */

CREATE VIEW AssetConnection AS 
SELECT
	AssetRelationship.ID AS ID,
	AssetRelationship.AssetRelationshipTypeID AS AssetRelationshipTypeID,
	(SELECT (CASE WHEN Child.Spare = 1 THEN SpareChild.SpareAssetID ELSE AssetRelationship.ChildID END)) AS ChildID,
	(SELECT (CASE WHEN Parent.Spare = 1 THEN SpareParent.SpareAssetID ELSE AssetRelationship.ParentID END)) AS ParentID
FROM
	AssetRelationship JOIN ASSET CHILD ON AssetRelationship.ChildID = Child.ID JOIN
	Asset Parent ON AssetRelationship.ParentID = Parent.ID LEFT JOIN
	AssetSpare SpareChild ON Child.ID = SpareChild.AssetID LEFT JOIN
	AssetSpare SpareParent ON Parent.ID = SpareParent.AssetID
GO

CREATE TRIGGER TR_INSERT_AssetConnection ON AssetConnection
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO AssetRelationship (ChildID, ParentID, AssetRelationshipTypeID)
		SELECT 
			ChildID AS ChildID,
			ParentID AS ParentID,
			AssetRelationshipTypeID AS AssetRelationshipTypeID
	FROM INSERTED
END
GO

CREATE TRIGGER TR_UPDATE_AssetConnection ON AssetConnection
INSTEAD OF UPDATE AS
BEGIN

		UPDATE AssetRelationship
		SET
			AssetRelationship.ChildID = AssetRelationship.ChildID,
			AssetRelationship.ParentID = AssetRelationship.ParentID,
			AssetRelationship.AssetRelationshipTypeID = AssetRelationship.AssetRelationshipTypeID
		FROM
			AssetRelationship INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = AssetRelationship.ID;
END
GO

CREATE TRIGGER TR_DELETE_AssetConnection ON AssetConnection
INSTEAD OF DELETE AS 
BEGIN
	DELETE FROM AssetRelationship WHERE ID IN (SELECT DELETED.ID FROM DELETED)
END
GO
/* End Spare Logic */

CREATE TABLE BusAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

CREATE TABLE BreakerAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
	ThermalRating FLOAT NOT NULL DEFAULT(0),
	Speed FLOAT NOT NULL DEFAULT(0),
	TripTime INT NULL DEFAULT(0),
	PickupTime INT NULL DEFAULT(0),
	TripCoilCondition FLOAT NULL DEFAULT(0)
)
GO

CREATE TABLE CapacitorBankAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    NumberOfBanks INT NOT NULL DEFAULT(1),
    CansPerBank INT NOT NULL DEFAULT(1),
    CapacitancePerBank INT NOT NULL DEFAULT(0)
)
GO

CREATE TABLE LineAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    MaxFaultDistance FLOAT NULL,
    MinFaultDistance FLOAT NULL,
)
GO

CREATE TABLE LineSegmentAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    Length FLOAT NOT NULL,
    R0 FLOAT NOT NULL,
    X0 FLOAT NOT NULL,
    R1 FLOAT NOT NULL,
    X1 FLOAT NOT NULL,
	ThermalRating FLOAT NOT NULL,
	IsEnd BIT NOT NULL DEFAULT(0)
)
GO

CREATE TABLE TransformerAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    R0 FLOAT NOT NULL DEFAULT(0),
    X0 FLOAT NOT NULL DEFAULT(0),
    R1 FLOAT NOT NULL DEFAULT(0),
    X1 FLOAT NOT NULL DEFAULT(0),
	ThermalRating FLOAT NOT NULL,
	SecondaryVoltageKV FLOAT NULL,
	PrimaryVoltageKV FLOAT NULL,
	Tap FLOAT NULL
)
GO



/* Correspoding Views and Trigger */
CREATE VIEW Line AS
	SELECT 
		AssetID AS ID,
		MaxFaultDistance,
		MinFaultDistance,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN LineAttributes ON Asset.ID = LineAttributes.AssetID
GO

CREATE VIEW AssetView AS
	SELECT 
		Asset.ID AS ID,
		AssetKey,
		VoltageKV,
		Asset.Description,
		AssetName,
		AssetType.Name AS AssetType,
		AssetTypeID,
		Spare
	FROM Asset JOIN	AssetType ON AssetType.ID = Asset.AssetTypeID WHERE AssetType.ID != 5  


GO

CREATE TRIGGER TR_INSERT_Line ON Line
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName,VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Line') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO LineAttributes (AssetID, MinFaultDistance, MaxFaultDistance)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			MinFaultDistance AS MinFaultDistance,
			MaxFaultDistance AS MaxFaultDistance
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Line ON Line
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE LineAttributes
		SET
			LineAttributes.MaxFaultDistance = INSERTED.MaxFaultDistance,
			LineAttributes.MinFaultDistance = INSERTED.MinFaultDistance
		FROM
			LineAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = LineAttributes.AssetID;
END
GO

/* END Line Model Triggers */
/* Bus Model */
CREATE VIEW Bus AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN BusAttributes ON Asset.ID = BusAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Bus ON BUS
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Bus') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO BusAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Bus ON BUS
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO


/* Breaker Model */
CREATE VIEW Breaker AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		ThermalRating,
		Speed,
		Description,
		AssetName,
		AssetTypeID,
		TripTime,
		PickupTime,
		TripCoilCondition,
		Spare
	FROM Asset JOIN BreakerAttributes ON Asset.ID = BreakerAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Breaker ON Breaker
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, VoltageKV, AssetName, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Breaker') AS AssetTypeID,
			Description AS Description,
			VoltageKV AS VoltageKV,
			AssetName AS AssetName,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO BreakerAttributes (AssetID, ThermalRating, Speed, TripTime, PickupTime, TripCoilCondition)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			ThermalRating AS ThermalRating,
			Speed AS Speed,
			TripTime AS TripTime,
			PickupTime AS PickupTime,
			TripCoilCondition AS TripCoilCondition
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Breaker ON Breaker
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE BreakerAttributes
		SET
			BreakerAttributes.ThermalRating = INSERTED.ThermalRating,
			BreakerAttributes.Speed = INSERTED.Speed,
			BreakerAttributes.TripTime = INSERTED.TripTime,
			BreakerAttributes.PickupTime = INSERTED.PickupTime,
			BreakerAttributes.TripCoilCondition = INSERTED.TripCoilCondition
		FROM
			BreakerAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = BreakerAttributes.AssetID;
END
GO


/* Capacitor Bank Model */
CREATE VIEW CapBank AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		NumberofBanks,
		CansPerBank,
		CapacitancePerBank,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN CapacitorBankAttributes ON Asset.ID = CapacitorBankAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_CapBank ON CapBank
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO CapacitorBankAttributes (AssetID, NumberOfBanks, CansPerBank, CapacitancePerBank)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			NumberOfBanks AS NumberOfBanks,
			CansPerBank AS CansPerBank,
			CapacitancePerBank AS CapacitancePerBank
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_CapBank ON CapBank
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR Update(Spare) )
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE CapacitorBankAttributes
		SET
			CapacitorBankAttributes.NumberOfBanks = INSERTED.NumberOfBanks,
			CapacitorBankAttributes.CansPerBank = INSERTED.CansPerBank,
			CapacitorBankAttributes.CapacitancePerBank = INSERTED.CapacitancePerBank
		FROM
			CapacitorBankAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = CapacitorBankAttributes.AssetID;
END
GO


/* Line Segment Model */
CREATE VIEW LineSegment AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		Length,
		R0,
		X0,
		R1,
		X1,
		ThermalRating,
		Description,
		AssetName,
		VoltageKV,
		AssetTypeID,
		Spare,
		IsEnd
	FROM Asset JOIN LineSegmentAttributes ON Asset.ID = LineSegmentAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_LineSegment ON LineSegment
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'LineSegment') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO LineSegmentAttributes (AssetID, Length, R0, X0, R1, X1, ThermalRating, IsEnd)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			Length AS Length,
			R0 AS R0,
			X0 AS X0,
			R1 AS R1,
			X1 AS X1,
			ThermalRating AS ThermalRating,
			IsEnd AS IsEnd
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_LineSegment ON LineSegment
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR Update(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV  = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE LineSegmentAttributes
		SET
			LineSegmentAttributes.R0 = INSERTED.R0,
			LineSegmentAttributes.X0 = INSERTED.X0,
			LineSegmentAttributes.R1 = INSERTED.R1,
			LineSegmentAttributes.X1 = INSERTED.X1,
			LineSegmentAttributes.ThermalRating = INSERTED.ThermalRating,
			LineSegmentAttributes.IsEnd = INSERTED.IsEnd
		FROM
			LineSegmentAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = LineSegmentAttributes.AssetID;
END
GO


/* Transformers */
CREATE VIEW Transformer AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		R0,
		X0,
		R1,
		X1,
		ThermalRating,
		SecondaryVoltageKV,
		PrimaryVoltageKV,
		TAP,
		Description,
		AssetName,
		VoltageKV,
		AssetTypeID,
		Spare
	FROM Asset JOIN TransformerAttributes ON Asset.ID = TransformerAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Tranformer ON Transformer
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Transformer') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO TransformerAttributes (AssetID, R0, X0, R1, X1, ThermalRating, SecondaryVoltageKV, PrimaryVoltageKV, Tap )
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			R0 AS R0,
			X0 AS X0,
			R1 AS R1,
			X1 AS X1,
			ThermalRating AS ThermalRating,
			SecondaryVoltageKV AS SecondaryVoltageKV,
			PrimaryVoltageKV AS PrimaryVoltageKV,
			Tap AS Tap

	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Tranformer ON Transformer
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE TransformerAttributes
		SET
			TransformerAttributes.R0 = INSERTED.R0,
			TransformerAttributes.X0 = INSERTED.X0,
			TransformerAttributes.R1 = INSERTED.R1,
			TransformerAttributes.X1 = INSERTED.X1,
			TransformerAttributes.ThermalRating = INSERTED.ThermalRating,
			TransformerAttributes.SecondaryVoltageKV = INSERTED.SecondaryVoltageKV,
			TransformerAttributes.PrimaryVoltageKV = INSERTED.PrimaryVoltageKV,
			TransformerAttributes.Tap = INSERTED.Tap
		FROM
			TransformerAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = TransformerAttributes.AssetID;
END
GO

/* *************** End Model Section ********* */

CREATE TABLE AssetChannel
(
    ID INT IDENTITY(1, 1) NOT NULL,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    ChannelID INT NOT NULL REFERENCES Channel(ID)
)
GO

CREATE TABLE ChannelData
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL,
    RuntimeID INT NOT NULL,
    TimeDomainData VARBINARY(MAX) NOT NULL,
    MarkedForDeletion INT NOT NULL,
	SeriesID INT NOT NULL,
	EventID INT NOT NULL,
	EventDataID INT NULL
)
GO

CREATE TABLE LightningStrike(
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	EventID int NOT NULL FOREIGN KEY REFERENCES Event(ID),
	Service varchar(50) NOT NULL,
	UTCTime datetime2(7) NOT NULL,
	DisplayTime varchar(50) NOT NULL,
	Amplitude float NOT NULL,
	Latitude float NOT NULL,
	Longitude float NOT NULL
)

GO


CREATE VIEW MeterAssetDetail AS
	SELECT 
		MeterAsset.ID,
		MeterAsset.AssetID,
		MeterAsset.MeterID,
		Meter.AssetKey AS MeterKey,
		Asset.AssetKey AS AssetKey,
		AssetType.Name AS AssetType,
		FaultDetectionLogic.Expression AS FaultDetectionLogic,
		Asset.AssetName AS AssetName
	FROM
		MeterAsset LEFT JOIN Meter ON MeterAsset.MeterID = Meter.ID LEFT JOIN
		ASSET ON MeterAsset.AssetID = Asset.ID LEFT JOIN 
		AssetType ON Asset.AssetTypeID = AssetType.ID LEFT JOIN
		FaultDetectionLogic ON FaultDetectionLogic.MeterAssetID = MeterAsset.ID
GO

CREATE VIEW AssetConnectionDetail AS
SELECT
	AssetRelationship.ID,
	AssetRelationship.ChildID,
	Asset1.AssetKey AS ChildKey,
	AssetRelationship.ParentID,
	Asset2.AssetKey AS ParentKey,
	AssetRelationship.AssetRelationshipTypeID,
	AssetRelationshipType.Name AS AssetRelationshipType
FROM
	AssetRelationship LEFT JOIN Asset Asset1 ON Asset1.ID = AssetRelationship.ChildID LEFT JOIN
	Asset Asset2 ON Asset2.ID = AssetRelationship.ParentID LEFT JOIN
	AssetRelationshipType ON AssetRelationship.AssetRelationshipTypeID = AssetRelationshipType.ID
GO