-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE ctemp_read AUTHORIZATION dbo 
	GO  

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

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



-------------------
-- Menu and Route
-------------------
	declare @parent int, @result_routeId INT
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'Caster' and Parent is null

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/covers-temperatures'
			   , 'covers-temperatures'
			   , 'pages/caster/covers-temperatures/'
			   , 'ctemp_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Covers Temperatures'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'ctemp_read'
			   ,NULL
			 )
	GO


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [ctemp_read] -- The role
,@name = N'securedsp-CAS.GetLastCoverTemperatures' -- the procedure
,@value = N'' 
GO


-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('ctemp_read','Covers Temperatures')

	SET @ModuleId = @@IDENTITY


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','ctemp_read')




-- NOTE: reference snapshot only. The canonical, deployed version lives at
-- mes-db/CAS/Stored Procedures/GetLastCoverTemperatures.sql -- keep them in
-- sync. Single thermocouple per cover; long-format output pivoted client-side.
USE [MES]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [CAS].[GetLastCoverTemperatures]
    @timeZoneCode  CHAR(10) = NULL,
    @bucketMinutes INT      = 10,
    @bucketsBack   INT      = 12
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @lookbackMinutes INT = @bucketMinutes * @bucketsBack;
    DECLARE @cutoff DATETIME = DATEADD(MINUTE, -@lookbackMinutes, GETUTCDATE());

    ;WITH Bucketed AS (
        SELECT
            DATEADD(
                MINUTE,
                (DATEDIFF(MINUTE, 0, [Timestamp]) / @bucketMinutes) * @bucketMinutes,
                0
            ) AS BucketUtc,
            CoverNumber,
            Temperature,
            [Status]
        FROM CAS.CoverTemps
        WHERE [Timestamp] >= @cutoff
          AND (Temperature IS NOT NULL OR [Status] IS NOT NULL)
    ),
    TopBuckets AS (
        SELECT TOP (@bucketsBack) BucketUtc
        FROM (SELECT DISTINCT BucketUtc FROM Bucketed) d
        ORDER BY BucketUtc DESC
    )
    SELECT
        [Timestamp]   = IH.ToLocal(b.BucketUtc, @timeZoneCode),
        TimestampUtc  = b.BucketUtc,
        CoverNumber   = b.CoverNumber,
        Temperature   = MAX(b.Temperature),
        [Status]      = MAX(b.[Status])
    FROM Bucketed b
    INNER JOIN TopBuckets t ON t.BucketUtc = b.BucketUtc
    GROUP BY b.BucketUtc, b.CoverNumber
    ORDER BY b.BucketUtc DESC, b.CoverNumber;

    RETURN 0;
END
