namespace FrameworkDAL.Query.DiagnosticsCenter;

public static class DiagnosticsCenterQB
{
    // ── Health Dashboard ──────────────────────────────────────────────────────
    // Aggregates 24-hour request and error counts from TOBSERVABILITY.
    // Requires index: IX_TOBSERVABILITY_TENANT_TIME (TENANTID, CREATEDON DESC)
    public const string GET_HEALTH_DASHBOARD = @"
        SELECT
            COUNT(*)                                             AS TotalRequests24h,
            SUM(CASE WHEN ISERROR = 1 THEN 1 ELSE 0 END)       AS ErrorCount24h,
            SUM(CASE WHEN ISLOWPERFORMANCE = 1 THEN 1 ELSE 0 END) AS SlowTransactionCount24h,
            CASE WHEN COUNT(*) = 0 THEN 0.0
                 ELSE CAST(SUM(CASE WHEN ISERROR = 1 THEN 1 ELSE 0 END) AS FLOAT)
                      / COUNT(*) * 100.0
            END                                                  AS ErrorRate24hPct,
            ISNULL(AVG(DURATIONMS), 0)                          AS AvgDurationMs,
            ISNULL(
                (SELECT TOP 1 DURATIONMS
                 FROM   TOBSERVABILITY
                 WHERE  TENANTID  = @TenantId
                   AND  CREATEDON >= DATEADD(HOUR, -24, GETUTCDATE())
                 ORDER  BY DURATIONMS DESC
                 OFFSET  CAST(COUNT(*) * 0.05 AS INT) ROWS
                 FETCH NEXT 1 ROWS ONLY), 0)                    AS P95DurationMs
        FROM TOBSERVABILITY
        WHERE  TENANTID  = @TenantId
          AND  CREATEDON >= DATEADD(HOUR, -24, GETUTCDATE())";

    public const string GET_ACTIVE_SESSION_COUNT = @"
        SELECT COUNT(*)
        FROM   TDIAGNOSTIC_SESSION
        WHERE  TENANTID  = @TenantId
          AND  ISACTIVE  = 1
          AND  EXPIRESAT > GETUTCDATE()";

    // ── Slow Transactions ─────────────────────────────────────────────────────
    // Returns top N slow operations for the tenant.
    // Requires index: IX_TOBSERVABILITY_SLOW (TENANTID, ISLOWPERFORMANCE, CREATEDON DESC)
    public const string GET_SLOW_TRANSACTIONS = @"
        SELECT TOP (@TopN)
            OB.OBSERVABILITYID  AS ObservabilityId,
            OB.TRACECONTEXT     AS TraceContext,
            OB.SERVICENAME      AS ServiceName,
            OB.OPERATIONNAME    AS OperationName,
            OB.DURATIONMS       AS DurationMs,
            OB.STATUSCODE       AS StatusCode,
            OB.USERID           AS UserId,
            OB.SESSIONID        AS SessionId,
            OB.CREATEDON        AS CreatedOn
        FROM   TOBSERVABILITY OB
        WHERE  OB.TENANTID         = @TenantId
          AND  OB.ISLOWPERFORMANCE = 1
          AND  OB.CREATEDON       >= DATEADD(HOUR, -@HoursBack, GETUTCDATE())
        ORDER  BY OB.DURATIONMS DESC";

    // ── Recent Errors ─────────────────────────────────────────────────────────
    // Returns top N error records for the tenant.
    // Requires index: IX_TOBSERVABILITY_ERROR (TENANTID, ISERROR, CREATEDON DESC)
    public const string GET_RECENT_ERRORS = @"
        SELECT TOP (@TopN)
            OB.OBSERVABILITYID  AS ObservabilityId,
            OB.TRACECONTEXT     AS TraceContext,
            OB.SERVICENAME      AS ServiceName,
            OB.OPERATIONNAME    AS OperationName,
            OB.STATUSCODE       AS StatusCode,
            OB.ERRORTYPE        AS ErrorType,
            OB.DURATIONMS       AS DurationMs,
            OB.USERID           AS UserId,
            OB.CREATEDON        AS CreatedOn
        FROM   TOBSERVABILITY OB
        WHERE  OB.TENANTID  = @TenantId
          AND  OB.ISERROR   = 1
          AND  OB.CREATEDON >= DATEADD(HOUR, -@HoursBack, GETUTCDATE())
        ORDER  BY OB.CREATEDON DESC";

    // ── Workflow Diagnostics ──────────────────────────────────────────────────
    // Returns recent workflow-related events (approvals, rejections, escalations)
    // from TEVENTLOG for the tenant's database. Tenant isolation via QueryExecutor routing.
    // Filters on EventTypeCode patterns: APPROV%, REJECT%, WIP_%, WORKFLOW_%
    public const string GET_WORKFLOW_DIAGNOSTICS = @"
        SELECT TOP (@TopN)
            EL.EVENTLOGID       AS EventLogId,
            EL.EVENTTEXT        AS EventText,
            ET.EVENTTYPECODE    AS EventTypeCode,
            ET.EVENTTYPENAME    AS EventTypeName,
            EL.USERID           AS UserId,
            U.USERNAME          AS UserName,
            EL.SOURCEID         AS SourceId,
            EL.TIMESTAMP        AS Timestamp
        FROM   TEVENTLOG EL
        JOIN   MEVENTTYPE ET ON EL.EVENTTYPEID = ET.EVENTTYPEID
        LEFT JOIN MUSER U   ON EL.USERID = U.USERID
        WHERE  EL.TIMESTAMP >= DATEADD(HOUR, -@HoursBack, GETUTCDATE())
          AND  ET.EVENTTYPECODE LIKE '%APPROV%'
            OR ET.EVENTTYPECODE LIKE '%REJECT%'
            OR ET.EVENTTYPECODE LIKE '%WIP%'
            OR ET.EVENTTYPECODE LIKE '%WORKFLOW%'
        ORDER  BY EL.TIMESTAMP DESC";

    // ── Integration Diagnostics ───────────────────────────────────────────────
    // Returns Dapr pub/sub failure events and integration-flagged events.
    // ISTELEMETRY=1 marks events emitted by the integration/messaging layer.
    public const string GET_INTEGRATION_DIAGNOSTICS = @"
        SELECT TOP (@TopN)
            EL.EVENTLOGID       AS EventLogId,
            EL.EVENTTEXT        AS EventText,
            ET.EVENTTYPECODE    AS EventTypeCode,
            EL.MACHINEIP        AS MachineIp,
            EL.TAGS             AS Tags,
            EL.TIMESTAMP        AS Timestamp
        FROM   TEVENTLOG EL
        JOIN   MEVENTTYPE ET ON EL.EVENTTYPEID = ET.EVENTTYPEID
        WHERE  ET.ISTELEMETRY   = 1
          AND  EL.TIMESTAMP    >= DATEADD(HOUR, -@HoursBack, GETUTCDATE())
          AND (EL.EVENTTEXT LIKE '%fail%' OR EL.EVENTTEXT LIKE '%error%'
               OR EL.EVENTTEXT LIKE '%timeout%' OR EL.EVENTTEXT LIKE '%retry%')
        ORDER  BY EL.TIMESTAMP DESC";

    // ── Session Diagnostics ───────────────────────────────────────────────────
    // Returns all diagnostic sessions for the tenant (active + recently closed).
    public const string GET_SESSION_DIAGNOSTICS = @"
        SELECT
            DS.DIAGNOSTICSESSIONID  AS DiagnosticSessionId,
            DS.SESSIONSCOPE         AS SessionScope,
            DS.SCOPEID              AS ScopeId,
            DS.DIAGNOSTICLEVEL      AS DiagnosticLevel,
            DS.CAPTUREQUERYBODIES   AS CaptureQueryBodies,
            DS.CAPTURERESPONSEBODIES AS CaptureResponseBodies,
            DS.INCREASESAMPLING     AS IncreaseSampling,
            DS.EXPIRESAT            AS ExpiresAt,
            DS.ISACTIVE             AS IsActive,
            DS.CREATEDON            AS CreatedOn,
            DS.CLOSEDON             AS ClosedOn,
            U.USERNAME              AS CreatedByName
        FROM   TDIAGNOSTIC_SESSION DS
        LEFT JOIN MUSER U ON DS.CREATEDBYID = U.USERID
        WHERE  DS.TENANTID = @TenantId
          AND  DS.CREATEDON >= DATEADD(DAY, -7, GETUTCDATE())
        ORDER  BY DS.CREATEDON DESC";

    // ── Module Health ─────────────────────────────────────────────────────────
    // Groups TOBSERVABILITY by service name, computing error rate and perf metrics.
    // Requires index: IX_TOBSERVABILITY_TENANT_TIME (TENANTID, CREATEDON DESC)
    public const string GET_MODULE_HEALTH = @"
        SELECT
            OB.SERVICENAME                                          AS ServiceName,
            COUNT(*)                                                AS TotalRequests,
            SUM(CASE WHEN OB.ISERROR = 1 THEN 1 ELSE 0 END)       AS ErrorCount,
            CASE WHEN COUNT(*) = 0 THEN 0.0
                 ELSE CAST(SUM(CASE WHEN OB.ISERROR = 1 THEN 1 ELSE 0 END) AS FLOAT)
                      / COUNT(*) * 100.0
            END                                                     AS ErrorRatePct,
            ISNULL(AVG(OB.DURATIONMS), 0)                          AS AvgDurationMs,
            ISNULL(MAX(OB.DURATIONMS), 0)                          AS MaxDurationMs,
            SUM(CASE WHEN OB.ISLOWPERFORMANCE = 1 THEN 1 ELSE 0 END) AS SlowCount,
            MAX(OB.CREATEDON)                                       AS LastSeenAt
        FROM   TOBSERVABILITY OB
        WHERE  OB.TENANTID  = @TenantId
          AND  OB.CREATEDON >= DATEADD(HOUR, -@HoursBack, GETUTCDATE())
        GROUP  BY OB.SERVICENAME
        ORDER  BY ErrorCount DESC, TotalRequests DESC";
}