DECLARE
@FeedbackWebhookEvents TABLE
                           (
                               [Id]                     [uniqueidentifier]  NOT NULL,
                               [EventId]                [uniqueidentifier]  NOT NULL,
                               [WorkspaceId]            [uniqueidentifier]  NOT NULL,
                               [Source]                 [nvarchar](max)     NOT NULL,
                               [EventType]              [nvarchar](max)     NOT NULL,
                               [Severity]               [int]               NOT NULL,
                               [Summary]                [nvarchar](max)     NOT NULL,
                               [OccurredAt]             [datetimeoffset](7) NOT NULL,
                               [ReceivedAt]             [datetimeoffset](7) NOT NULL,
                               [PayloadJson]            [nvarchar](max)     NOT NULL,
                               [TaskId]                 [uniqueidentifier]  NULL,
                               [RelatedTaskId]          [uniqueidentifier]  NULL,
                               [RelatedTaskExecutionId] [uniqueidentifier]  NULL,
                               [ExternalReference]      [nvarchar](max)     NULL,
                               [ExternalUrl]            [nvarchar](max)     NULL,
                               [AgentName]              [nvarchar](max)     NULL,
                               [Reporter]               [nvarchar](max)     NULL,
                               [ReporterEmail]          [nvarchar](max)     NULL
                           )

INSERT INTO
    @FeedbackWebhookEvents
SELECT [Id], [EventId], [WorkspaceId], [Source], [EventType], [Severity], [Summary], [OccurredAt], [ReceivedAt], [PayloadJson], [TaskId], [RelatedTaskId], [RelatedTaskExecutionId], [ExternalReference], [ExternalUrl], [AgentName], [Reporter], [ReporterEmail]
FROM [dbo].[FeedbackWebhookEvents]
WHERE [WorkspaceId] = @WorkspaceId
  AND (@StartUtc IS NULL
   OR [ReceivedAt] >= @StartUtc)
  AND (@EndUtc IS NULL
   OR [ReceivedAt] <= @EndUtc)

SELECT COUNT(*) AS [TotalEvents],
       COUNT(DISTINCT [Source])                                   AS [DistinctSources],
       SUM(CASE WHEN [TaskId] IS NOT NULL THEN 1 ELSE 0 END)      AS [FollowUpTaskCount],
       SUM(DATEDIFF_BIG(MILLISECOND, [OccurredAt], [ReceivedAt])) AS [TotalLatencyMilliseconds],
       MIN([ReceivedAt])                                          AS [FirstReceivedAt],
       MAX([ReceivedAt])                                          AS [LastReceivedAt]
FROM @FeedbackWebhookEvents;

SELECT CAST([ReceivedAt] AS date) AS [EventDate],
       COUNT(*)                                              AS [TotalEvents],
       SUM(CASE WHEN [TaskId] IS NOT NULL THEN 1 ELSE 0 END) AS [FollowUpTaskCount]
FROM @FeedbackWebhookEvents
GROUP BY CAST ([ReceivedAt] AS date)
ORDER BY [EventDate] DESC
OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY;

SELECT [Severity], COUNT (*) AS [Total]
FROM @FeedbackWebhookEvents
GROUP BY [Severity];

SELECT [Source], COUNT (*) AS [Total]
FROM @FeedbackWebhookEvents
GROUP BY [Source]
ORDER BY [Total] DESC;
