ALTER TABLE FaultEmailCriterion
ADD EmailOnReclose INT NOT NULL DEFAULT 0
GO

ALTER PROCEDURE [dbo].[GetEventEmailRecipients]
(
    @eventID INT
)
AS BEGIN
    DECLARE @lineID INT
    DECLARE @startTime DATETIME2
    DECLARE @endTime DATETIME2
    DECLARE @timeTolerance FLOAT

    SELECT
        @lineID = LineID,
        @startTime = StartTime,
        @endTime = EndTime,
        @timeTolerance = COALESCE(Value, 0.5)
    FROM
        Event LEFT OUTER JOIN
        Setting ON Setting.Name = 'TimeTolerance'
    WHERE Event.ID = @eventID

    ; WITH cte AS
    (
        SELECT
            EmailGroupID,
            UserAccountID
        FROM EmailGroupUserAccount
        UNION
        SELECT
            EmailGroupSecurityGroup.EmailGroupID,
            UserAccount.ID
        FROM
            EmailGroupSecurityGroup JOIN
            SecurityGroupUserAccount ON EmailGroupSecurityGroup.SecurityGroupID = SecurityGroupUserAccount.SecurityGroupID JOIN
            UserAccount on SecurityGroupUserAccount.UserAccountID = UserAccount.ID
    )
    SELECT DISTINCT
        EmailGroupUserAccount.UserAccountID,
        EmailType.XSLTemplateID AS TemplateID
    FROM
        GetSystemEventIDs(@startTime, @endTime, @timeTolerance) SystemEventID JOIN
        Event ON
            SystemEventID.EventID = Event.ID AND
            Event.LineID = @lineID JOIN
        EventType ON Event.EventTypeID = EventType.ID JOIN
        Meter ON Event.MeterID = Meter.ID LEFT OUTER JOIN
        MeterMeterGroup ON MeterMeterGroup.MeterID = Meter.ID LEFT OUTER JOIN
        LineLineGroup ON LineLineGroup.LineID = Event.LineID LEFT OUTER JOIN
        EmailGroupMeterGroup ON MeterMeterGroup.MeterGroupID = EmailGroupMeterGroup.MeterGroupID LEFT OUTER JOIN
        EmailGroupLineGroup ON LineLineGroup.LineGroupID = EmailGroupLineGroup.LineGroupID JOIN
        EmailGroup ON
            EmailGroupMeterGroup.EmailGroupID = EmailGroup.ID OR
            EmailGroupLineGroup.EmailGroupID = EmailGroup.ID JOIN
        cte EmailGroupUserAccount ON EmailGroupUserAccount.EmailGroupID = EmailGroup.ID JOIN
        EmailGroupType ON EmailGroupType.EmailGroupID = EmailGroup.ID JOIN
        EmailType ON EmailGroupType.EmailTypeID = EmailType.ID JOIN
        EmailCategory ON
            EmailType.EmailCategoryID = EmailCategory.ID AND
            EmailCategory.Name = 'Event'
    WHERE
        (
            NOT EXISTS
            (
                SELECT *
                FROM FaultEmailCriterion
                WHERE FaultEmailCriterion.EmailGroupID = EmailGroup.ID
            )
            AND NOT EXISTS
            (
                SELECT *
                FROM DisturbanceEmailCriterion
                WHERE DisturbanceEmailCriterion.EmailGroupID = EmailGroup.ID
            )
			AND NOT EXISTS
			(
				SELECT *
				FROM BreakerEmailCriterion
				WHERE BreakerEmailCriterion.EmailGroupID = EmailGroup.ID
			)
        )
        OR
        (
            EventType.Name = 'Fault' AND
            EXISTS
            (
                SELECT *
                FROM FaultEmailCriterion
                WHERE FaultEmailCriterion.EmailGroupID = EmailGroup.ID
            )
        )
        OR
        (
            EventType.Name = 'RecloseIntoFault' AND
            EXISTS
            (
                SELECT *
                FROM FaultEmailCriterion
                WHERE
                    FaultEmailCriterion.EmailGroupID = EmailGroup.ID AND
                    FaultEmailCriterion.EmailOnReclose <> 0
            )
        )
        OR EXISTS
        (
            SELECT *
            FROM
                Disturbance JOIN
                DisturbanceSeverity ON DisturbanceSeverity.DisturbanceID = Disturbance.ID JOIN
                DisturbanceEmailCriterion ON DisturbanceSeverity.SeverityCode = DisturbanceEmailCriterion.SeverityCode
            WHERE
                Disturbance.EventID = Event.ID AND
                DisturbanceEmailCriterion.EmailGroupID = EmailGroup.ID
        )
        OR EXISTS
        (
            SELECT *
            FROM
				BreakerOperation JOIN
				BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID CROSS JOIN
				BreakerEmailCriterion
            WHERE
				BreakerOperation.EventID = Event.ID AND
				BreakerOperationType.Name = 'Late' AND
				BreakerEmailCriterion.EmailGroupID = EmailGroup.ID
        )
END
GO




ALTER FUNCTION [dbo].[GetSystemEventIDs]
(
    @startTime DATETIME2,
    @endTime DATETIME2,
    @timeTolerance FLOAT
)
RETURNS @systemEvent TABLE
(
    EventID INT
)
AS BEGIN
    DECLARE @adjustedStartTime DATETIME2 = dbo.AdjustDateTime2(@startTime, -@timeTolerance)
    DECLARE @adjustedEndTime DATETIME2 = dbo.AdjustDateTime2(@endTime, @timeTolerance)
    DECLARE @minStartTime DATETIME2
    DECLARE @maxEndTime DATETIME2

    SELECT
        @minStartTime = MIN(dbo.AdjustDateTime2(StartTime, -@timeTolerance)),
        @maxEndTime = MAX(dbo.AdjustDateTime2(EndTime, @timeTolerance))
    FROM
        Event JOIN
        FileGroup ON Event.FileGroupID = FileGroup.ID
    WHERE
        StartTime <= @adjustedEndTime AND
        @adjustedStartTime <= EndTime AND
        ProcessingEndTime > '0001-01-01'

    WHILE @startTime != @minStartTime OR @endTime != @maxEndTime
    BEGIN
        SET @startTime = @minStartTime
        SET @endTime = @maxEndTime
        SET @adjustedStartTime = dbo.AdjustDateTime2(@startTime, -@timeTolerance)
        SET @adjustedEndTime = dbo.AdjustDateTime2(@endTime, @timeTolerance)

        SELECT
            @minStartTime = MIN(dbo.AdjustDateTime2(StartTime, -@timeTolerance)),
            @maxEndTime = MAX(dbo.AdjustDateTime2(EndTime, @timeTolerance))
        FROM
            Event JOIN
            FileGroup ON Event.FileGroupID = FileGroup.ID
        WHERE
            StartTime <= @adjustedEndTime AND
            @adjustedStartTime <= EndTime AND
            ProcessingEndTime > '0001-01-01'
    END

    INSERT INTO @systemEvent
    SELECT ID
    FROM Event
    WHERE @adjustedStartTime <= StartTime AND EndTime <= @adjustedEndTime

    RETURN
END
GO

ALTER TABLE DoubleEndedFaultDistance ADD UNIQUE(LocalFaultSummaryID)
GO

ALTER TABLE DoubleEndedFaultDistance ADD UNIQUE(RemoteFaultSummaryID)
GO

ALTER TABLE EmailGroupLineGroup ADD UNIQUE(EmailGroupID, LineGroupID)
GO

ALTER TABLE EmailGroupMeterGroup ADD UNIQUE(EmailGroupID, MeterGroupID)
GO

ALTER TABLE EmailGroupSecurityGroup ADD UNIQUE(EmailGroupID, SecurityGroupID)
GO

ALTER TABLE EmailGroupType ADD UNIQUE(EmailGroupID, EmailTypeID)
GO

ALTER TABLE EmailGroupUserAccount ADD UNIQUE(EmailGroupID, UserAccountID)
GO

ALTER TABLE EventType ADD UNIQUE(Name)
GO


CREATE TABLE [dbo].[FaultCurveStatistic](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FaultCurveID] [int] NOT NULL,
	[FaultNumber] [int] NOT NULL,
	[Maximum] [float] NOT NULL,
	[Minimum] [float] NOT NULL,
	[Average] [float] NOT NULL,
	[StandardDeviation] [float] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE FileBlob ADD PRIMARY KEY(ID)
GO

ALTER TABLE LineImpedance ADD UNIQUE(LineID)
GO

ALTER TABLE Meter DROP COLUMN MeterTypeID
GO

CREATE TABLE [dbo].[PQIResult](
	[ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

DROP TABLE SavedViews
GO

CREATE TABLE [dbo].[SavedViews](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserAccount] [varchar](500) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[DateRange] [int] NOT NULL,
	[FromDate] [datetime] NOT NULL,
	[ToDate] [datetime] NOT NULL,
	[Tab] [nvarchar](20) NOT NULL,
	[DeviceFilterID] [int] NOT NULL,
	[MapGrid] [nvarchar](5) NOT NULL,
	[IsDefault] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE SegmentType ADD UNIQUE(Name)
GO

ALTER VIEW [dbo].[EmailGroupUserAccountView] AS
SELECT
    EmailGroupUserAccount.ID,
    EmailGroupUserAccount.EmailGroupID,
    EmailGroupUserAccount.UserAccountID,
    EmailGroup.Name AS EmailGroup,
    COALESCE(UserAccount.FirstName + ' ' + UserAccount.LastName, UserAccount.FirstName, UserAccount.Name) AS UserName
FROM
    EmailGroupUserAccount JOIN
    EmailGroup ON EmailGroupUserAccount.EmailGroupID = EmailGroup.ID JOIN
    UserAccount ON EmailGroupUserAccount.UserAccountID = UserAccount.ID
GO

ALTER VIEW [dbo].[LineLineGroupView]
AS
SELECT
    LineLineGroup.ID,
    Line.AssetKey AS LineName,
    (SELECT TOP 1 LineName FROM MeterLine Where LineID = Line.ID) AS LongLineName,
    Line.ID AS LineID,
    LineGroupID
FROM
    LineLineGroup JOIN
    Line ON LineLineGroup.LineID = Line.ID
GO

ALTER VIEW [dbo].[MeterDetail]
AS
SELECT
    Meter.ID,
    Meter.AssetKey,
    Meter.MeterLocationID,
    MeterLocation.AssetKey AS LocationKey,
    MeterLocation.Name AS Location,
    MeterLocation.Latitude,
    MeterLocation.Longitude,
    Meter.Name,
    Meter.Alias,
    Meter.ShortName,
    Meter.Make,
    Meter.Model,
    CASE COALESCE(Meter.TimeZone, '')
        WHEN '' THEN COALESCE(Setting.Value, 'UTC')
        ELSE Meter.TimeZone
    END AS TimeZone,
    Meter.Description
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    Setting ON Setting.Name = 'DefaultMeterTimeZone'
GO

ALTER VIEW [dbo].[UserMeter]
AS
SELECT DISTINCT
    UserAccount.Name AS UserName,
    Meter.ID AS MeterID
FROM
    Meter JOIN
    MeterMeterGroup ON MeterMeterGroup.MeterID = Meter.ID JOIN
    UserAccountMeterGroup ON MeterMeterGroup.MeterGroupID = UserAccountMeterGroup.MeterGroupID JOIN
    UserAccount ON UserAccountMeterGroup.UserAccountID = UserAccount.ID

GO

ALTER TABLE [dbo].[FaultCurveStatistic]  WITH CHECK ADD FOREIGN KEY([FaultCurveID])
REFERENCES [dbo].[FaultCurve] ([ID])
GO