namespace FrameworkDAL.Query.DiagnosticsSession;

public static class DiagnosticsSessionQB
{
    // Active sessions for a tenant — used by DiagnosticsSessionMiddleware (30s cached)
    public const string GET_ACTIVE_SESSIONS = @"
        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.CREATEDBYID          AS CreatedById,
            DS.TENANTID             AS TenantId
        FROM TDIAGNOSTIC_SESSION DS
        WHERE DS.TENANTID = @TenantId
          AND DS.ISACTIVE = 1
          AND DS.EXPIRESAT > GETUTCDATE()
        ORDER BY DS.DIAGNOSTICSESSIONID DESC";

    // Single session detail (used by Close endpoint)
    public const string GET_SESSION = @"
        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.TENANTID             AS TenantId
        FROM TDIAGNOSTIC_SESSION DS
        WHERE DS.DIAGNOSTICSESSIONID = @DiagnosticSessionId
          AND DS.TENANTID = @TenantId";

    // Create a new diagnostic session
    // Index note: IX_TDIAGNOSTIC_SESSION_ACTIVE covers tenant+active+expiry lookups
    public const string SAVE_SESSION = @"
        INSERT INTO TDIAGNOSTIC_SESSION (
            SESSIONSCOPE, SCOPEID, DIAGNOSTICLEVEL,
            CAPTUREQUERYBODIES, CAPTURERESPONSEBODIES, INCREASESAMPLING,
            EXPIRESAT, ISACTIVE, CREATEDBYID, TENANTID
        )
        OUTPUT Inserted.DIAGNOSTICSESSIONID
        VALUES (
            @SessionScope, @ScopeId, @DiagnosticLevel,
            @CaptureQueryBodies, @CaptureResponseBodies, @IncreaseSampling,
            @ExpiresAt, 1, @CreatedById, @TenantId
        )";

    public const string SAVE_SESSION_POSTGRESQL = @"
        INSERT INTO TDIAGNOSTIC_SESSION (
            SESSIONSCOPE, SCOPEID, DIAGNOSTICLEVEL,
            CAPTUREQUERYBODIES, CAPTURERESPONSEBODIES, INCREASESAMPLING,
            EXPIRESAT, ISACTIVE, CREATEDBYID, TENANTID
        ) VALUES (
            @SessionScope, @ScopeId, @DiagnosticLevel,
            @CaptureQueryBodies, @CaptureResponseBodies, @IncreaseSampling,
            @ExpiresAt, TRUE, @CreatedById, @TenantId
        ) RETURNING DIAGNOSTICSESSIONID";

    // Close (soft-delete) a session
    public const string CLOSE_SESSION = @"
        UPDATE TDIAGNOSTIC_SESSION
        SET ISACTIVE   = 0,
            CLOSEDBYID = @ClosedById,
            CLOSEDON   = GETUTCDATE()
        WHERE DIAGNOSTICSESSIONID = @DiagnosticSessionId
          AND TENANTID = @TenantId
          AND ISACTIVE = 1";

    // Auto-expire sessions past ExpiresAt (called by nightly cleanup job)
    public const string EXPIRE_SESSIONS = @"
        UPDATE TDIAGNOSTIC_SESSION
        SET ISACTIVE = 0
        WHERE ISACTIVE = 1
          AND EXPIRESAT <= GETUTCDATE()";

    // ── Support tokens ────────────────────────────────────────────────────────

    public const string GET_TOKEN = @"
        SELECT
            ST.SUPPORTTOKENID       AS SupportTokenId,
            ST.TOKEN                AS Token,
            ST.DIAGNOSTICSESSIONID  AS DiagnosticSessionId,
            ST.ISSUEDTO             AS IssuedTo,
            ST.CAPABILITIES         AS Capabilities,
            ST.EXPIRESAT            AS ExpiresAt,
            ST.USEDAT               AS UsedAt,
            ST.USEDBYIP             AS UsedByIp,
            ST.ISACTIVE             AS IsActive,
            ST.TENANTID             AS TenantId
        FROM TSUPPORT_TOKEN ST
        WHERE ST.TOKEN = @Token
          AND ST.ISACTIVE = 1
          AND ST.EXPIRESAT > GETUTCDATE()";

    public const string SAVE_TOKEN = @"
        INSERT INTO TSUPPORT_TOKEN (
            TOKEN, DIAGNOSTICSESSIONID, ISSUEDTO, CAPABILITIES,
            EXPIRESAT, ISACTIVE, CREATEDBYID, TENANTID
        )
        OUTPUT Inserted.SUPPORTTOKENID
        VALUES (
            @Token, @DiagnosticSessionId, @IssuedTo, @Capabilities,
            @ExpiresAt, 1, @CreatedById, @TenantId
        )";

    public const string MARK_TOKEN_USED = @"
        UPDATE TSUPPORT_TOKEN
        SET USEDAT    = GETUTCDATE(),
            USEDBYIP  = @UsedByIp
        WHERE TOKEN = @Token
          AND USEDAT IS NULL";

    public const string REVOKE_TOKEN = @"
        UPDATE TSUPPORT_TOKEN
        SET ISACTIVE = 0
        WHERE SUPPORTTOKENID = @SupportTokenId
          AND TENANTID = @TenantId";
}