USE [IHBoxSystem]
GO
CREATE ROLE powerPeaks_read AUTHORIZATION dbo 
GO  

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'powerPeaks_read', 'admin'
GO

USE [IHBoxSystem]
GO
EXEC sp_addrolemember 'powerPeaks_read', 'SDI/australtek'
GO




EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [powerPeaks_read] -- The role
,@name = N'securedsp-PWR.GetPeaksWeb' -- the procedure
,@value = N'' 
GO


declare @parent int, @result_routeId INT
SELECT TOP 1 @parent=Id FROM FrontEnd.Menu WHERE [Name] = 'Power'

INSERT INTO [FrontEnd].[Routes]
		   ([Route]
		   ,[ModuleName]
		   ,[ModulePath]
		   ,[RequiredRole]
	   )
	 VALUES
		   (
		   '!/power-peaks(/)'
		   , 'power-peaks'
		   , 'custom-screens/power-peaks/'
		   , 'powerPeaks_read'           
		   )
		   SET @result_routeId = @@IDENTITY
INSERT INTO [FrontEnd].[Menu]
		   ([Parent]
		   ,[Name]
		   ,[RouteID]
		   ,[SortOrder]
		   ,[Enabled]
		   ,[Visible]
		   ,[RequiredRole]
		   ,[Group]
		  )
	 VALUES
		   (
		   @parent
		   ,'Power Peaks'
		   ,@result_routeId 
		   ,1
		   ,1
		   ,1
		   ,'powerPeaks_read'
		   ,NULL
		 )
GO

/*------------------------------------------------------Profiles------------------------------------------------------*/
USE [IHBoxSystem]
GO

DECLARE @ModuleId INT

INSERT INTO [SECURITY].[Modules]
		   ([Code]
		   ,[Name])
	 VALUES
		   ('power_peaks'
		   ,'Power Peaks')

		  SET @ModuleId = @@IDENTITY
		   		   
INSERT INTO [SECURITY].[ModuleActions]
		   ([ModuleId]
		   ,[Code]
		   ,[Name]
		   ,[Role])
	 VALUES
		   (@ModuleId
		   ,'read'
		   ,'Read'
		   ,'powerPeaks_read')
GO

/*---------------------------------------------------------------------------------------------------------------------------------------------*/


USE [MES]
GO
/****** Object:  StoredProcedure [PWR].[GetPeaksWeb]    Script Date: 8/1/2023 11:00:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [PWR].[GetPeaksWeb]
as
begin
        declare @date datetime = getdate()
        declare @aepStart Date 
        declare @aepEnd Date
        declare @rtoStart Date 
        declare @rtoEnd Date
        
        -- RTO PERIOD
        IF MONTH(@date) >= 6
        BEGIN
            SET @rtoStart = DATEFROMPARTS(YEAR(@date), 6, 1)
            SET @rtoEnd = DATEFROMPARTS(YEAR(@date), 9, 30)
        END
        -- Otherwise, return June 1st of the previous year
        ELSE
        BEGIN
            SET @rtoStart = DATEFROMPARTS(YEAR(@date) - 1, 6, 1)
            SET @rtoEnd = DATEFROMPARTS(YEAR(@date)-1, 9, 30)
        END

        -- AEP PERIOD
        IF MONTH(@date) >= 11
        BEGIN
            SET @aepStart = DATEFROMPARTS(YEAR(@date), 11, 1)
            SET @aepEnd = DATEFROMPARTS(YEAR(@date)+1, 10, 31)
        END
        ELSE
        BEGIN
            SET @aepStart = DATEFROMPARTS(YEAR(@date) - 1, 11, 1)
            SET @aepEnd = DATEFROMPARTS(YEAR(@date), 10, 31)
        END

    ----------------- RTO (PJM) -------------------------------
    ;WITH MaxLoadData AS (
    SELECT 
        h.[Date],
        h.Hour,
        h.RTOMeteredLoad,
        ROW_NUMBER() OVER(PARTITION BY h.[Date] ORDER BY h.RTOMeteredLoad DESC) as RowNumber
    FROM 
        PWR.HourData h
    WHERE 
        h.[Date] BETWEEN @rtoStart AND @rtoEnd
        AND PWR.IsNercHoliday(h.[Date]) = 0)

    SELECT 
    TOP 5
        rn = ROW_NUMBER() over (order by m.RTOMeteredLoad desc),
        [Date] =m.[Date],
        [Hour] = m.[Hour],
        EventTime =cast(h.[Date] as varchar) +' HE'+right('00'+cast(h.[Hour] +1 as varchar),2),
        RTOPeak = m.RTOMeteredLoad,
        h.SDIMeteredLoad,
        h.[AEPMeteredLoad],
        h.[RTOMeteredLoad],
        h.AveragePrice
    FROM MaxLoadData m
    left join PWR.HourData h on h.[Date]=m.[Date] and h.[Hour]=m.[Hour]
    WHERE m.RowNumber = 1
    order by m.RTOMeteredLoad desc

        ----------------- AEP -------------------------------
    ;WITH MaxLoadData AS (
    SELECT 
        h.[Date],
        h.[Hour],
        h.AEPMeteredLoad,
        ROW_NUMBER() OVER(PARTITION BY h.[Date] ORDER BY h.AEPMeteredLoad DESC) as RowNumber
    FROM 
        PWR.HourData h
    WHERE 
        h.[Date] BETWEEN @rtoStart AND @rtoEnd
        AND PWR.IsNercHoliday(h.[Date]) = 0)

    SELECT 
    TOP 1
        rn = ROW_NUMBER() over (order by m.AEPMeteredLoad desc),
        [Date] =m.[Date],
        [Hour] = m.[Hour],
        EventTime =cast(h.[Date] as varchar) +' HE'+right('00'+cast(h.[Hour] +1 as varchar),2),
        RTOPeak = m.AEPMeteredLoad,
        h.SDIMeteredLoad,
        h.[AEPMeteredLoad],
        h.[RTOMeteredLoad],
        h.AveragePrice
    FROM MaxLoadData m
    left join PWR.HourData h on h.[Date]=m.[Date] and h.[Hour]=m.[Hour]
    WHERE m.RowNumber = 1
    order by m.AEPMeteredLoad desc


	-- Last updated Metered Datetime  CHECK if is local or UTC--
	SELECT 
	top 1
		LastUpdated = StartDateTime from PWR.MeteredLoad order By StartDateTime desc

end
    



