

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

IF OBJECT_ID('DEL.GetDelaysForPareto','P') IS NOT NULL
    DROP PROCEDURE DEL.GetDelaysForPareto;
GO

CREATE PROCEDURE DEL.GetDelaysForPareto
    @Start               DATETIME = NULL,
    @End                 DATETIME = NULL,
    @DelayCodeId         INT      = NULL,
    @DelayCategoryId     INT      = NULL,
    @DelayAreaAssetId    INT      = NULL,
    @IncludeMicroDelays  BIT      = 0,
    @IncludeUndeclared   BIT      = 0,
    @IncludeInactives    BIT      = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- default window = last 30 days
    IF @End   IS NULL SET @End   = GETUTCDATE();
    IF @Start IS NULL SET @Start = DATEADD(DAY, -30, @End);

    ----------------------------------------------------------------
    -- 1) build temp‐table of delays (NULL→-1)
    ----------------------------------------------------------------
    SELECT
        COALESCE(d.DelayCategoryId, -1)    AS DelayCategoryId,
        COALESCE(d.DelayCodeId,     -1)    AS DelayCodeId,
        d.AssetId                          AS DelayAreaAssetId,
        DATEDIFF(MINUTE, d.[Start], d.[End]) AS DurationMinutes
    INTO #DelayData
    FROM DEL.Delays AS d
    WHERE
        d.[Start] >= @Start
        AND d.[End]   <= @End
        AND (@DelayCategoryId  IS NULL OR d.DelayCategoryId  = @DelayCategoryId)
        AND (@DelayCodeId      IS NULL OR d.DelayCodeId      = @DelayCodeId)
        AND (@DelayAreaAssetId IS NULL OR d.AssetId           = @DelayAreaAssetId)
        AND (
             (d.IsMicroDelay = 1 AND @IncludeMicroDelays = 1)
          OR (d.IsMicroDelay = 0)
        )
        AND (
             (d.DelayCodeId IS NULL AND @IncludeUndeclared = 1)
          OR (d.DelayCodeId IS NOT NULL)
        );
        -- add IsActive filter here if needed

    ----------------------------------------------------------------
    -- 2A) by category (with name, color, count & total minutes)
    ----------------------------------------------------------------
    SELECT
        dd.DelayCategoryId,
        dd.DelayAreaAssetId,
        COALESCE(cat.DelayCategoryName, 'Non Declared') AS DelayCategoryName,
        COUNT(*)                                     AS DelayCount,
        SUM(dd.DurationMinutes)                      AS TotalDelayMinutes,
        COALESCE(cat.DelayCategoryColor, '#e82323')  AS DelayCategoryColor
    FROM #DelayData AS dd
    LEFT JOIN DEL.catDelayCategories AS cat
      ON cat.DelayCategoryId   = dd.DelayCategoryId
     AND cat.DelayAreaAssetId  = dd.DelayAreaAssetId
    GROUP BY
        dd.DelayCategoryId,
        dd.DelayAreaAssetId,
        COALESCE(cat.DelayCategoryName, 'Non Declared'),
        COALESCE(cat.DelayCategoryColor, '#e82323')
    ORDER BY TotalDelayMinutes DESC;

    ----------------------------------------------------------------
    -- 2B) by code (with code name, category name, color, count & total minutes)
    ----------------------------------------------------------------
    SELECT
        dd.DelayCodeId,
        COALESCE(cc.DelayCodeName, 'Non Declared')     AS DelayCodeName,
        COALESCE(catC.DelayCategoryName, 'Non Declared')AS DelayCategoryName,
        COALESCE(catC.DelayCategoryColor, '#e82323')   AS DelayCategoryColor,
		dd.DelayAreaAssetId,  
        COUNT(*)                                      AS DelayCount,
        SUM(dd.DurationMinutes)                       AS TotalDelayMinutes
    FROM #DelayData AS dd
    LEFT JOIN DEL.catDelayCodes      AS cc
      ON cc.DelayCodeId = dd.DelayCodeId
    LEFT JOIN DEL.catDelayCategories AS catC
      ON catC.DelayCategoryId = cc.ParentDelayCodeId
    GROUP BY
        dd.DelayCodeId,
        COALESCE(cc.DelayCodeName, 'Non Declared'),
        COALESCE(catC.DelayCategoryName, 'Non Declared'),
        COALESCE(catC.DelayCategoryColor, '#e82323'),
		dd.DelayAreaAssetId
    ORDER BY TotalDelayMinutes DESC;
END
GO
