namespace FrameworkDAL.Query.DataAlert;

public static class ObservabilityAlertQB
{
    // ── Alert rule queries ────────────────────────────────────────────────────
    // Returns observability-source alert rules (ALERTSOURCE=2) for a tenant.
    // FILTER1 = error rate threshold % (e.g. "5.0")
    // FILTER2 = slow transaction count threshold (e.g. "10")
    // FILTER3 = lookback window in hours (e.g. "1")
    public const string GET_ACTIVE_OBS_ALERTS = @"
        SELECT
            DA.DATAALERTID   AS DataAlertId,
            DA.DATAALERTNAME AS DataAlertName,
            DA.ALERTLEVEL    AS AlertLevel,
            DA.MESSAGE       AS Message,
            DA.FILTER1       AS Filter1,
            DA.FILTER2       AS Filter2,
            DA.FILTER3       AS Filter3
        FROM MDATAALERT DA
        WHERE DA.ALERTSOURCE = 2
          AND DA.STATUS      = 1";

    // ── TOBSERVABILITY aggregate evaluation ───────────────────────────────────
    // Evaluated against system DB. Returns per-tenant error rate and slow count
    // for the specified lookback window (in hours).
    public const string EVAL_OBSERVABILITY_METRICS = @"
        SELECT
            COUNT(*)                                                    AS TotalRequests,
            SUM(CASE WHEN ISERROR = 1 THEN 1 ELSE 0 END)              AS ErrorCount,
            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 ErrorRatePct,
            SUM(CASE WHEN ISLOWPERFORMANCE = 1 THEN 1 ELSE 0 END)     AS SlowCount
        FROM TOBSERVABILITY
        WHERE TENANTID  = @TenantId
          AND CREATEDON >= DATEADD(HOUR, -@HoursBack, GETUTCDATE())";

    // ── Notification write (per-tenant DB) ───────────────────────────────────

    // FromUserId = -1 = system-generated alert
    // NOTE: TNOTIFICATION has no CREATEDON column on the live schema (verified via
    // INFORMATION_SCHEMA on GB5DEMO 2026-08-08, while wiring SysJob's own notification insert) —
    // only MODIFIEDON. This INSERT referenced CREATEDON and would have failed every time.
    public const string INSERT_NOTIFICATION = @"
        INSERT INTO TNOTIFICATION (FROMUSERID, MESSAGE, EVENTLOGID, DATAID, MODIFIEDON)
        OUTPUT Inserted.NOTIFICATIONID
        VALUES (-1, @Message, -1, @DataAlertId, GETDATE())";

    public const string INSERT_NOTIFICATION_POSTGRESQL = @"
        INSERT INTO TNOTIFICATION (FROMUSERID, MESSAGE, EVENTLOGID, DATAID, MODIFIEDON)
        VALUES (-1, @Message, -1, @DataAlertId, NOW())
        RETURNING NOTIFICATIONID";

    public const string INSERT_NOTIFICATION_TO = @"
        INSERT INTO TNOTIFICATIONTO (NOTIFICATIONID, TOUSERID, NOTIFICATIONSTATUS)
        VALUES (@NotificationId, @ToUserId, 0)";

    // ── TDATAALERTSUMMARY upsert ──────────────────────────────────────────────
    public const string UPSERT_DATAALERT_SUMMARY = @"
        MERGE TDATAALERTSUMMARY AS T
        USING (SELECT @DataAlertId AS DATAALERTID) AS S
            ON T.DATAALERTID = S.DATAALERTID
        WHEN MATCHED THEN
            UPDATE SET
                NUMBEROFRECORDS = @NumberOfRecords,
                POSTEDON        = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (DATAALERTID, NUMBEROFRECORDS, POSTEDON)
            VALUES (@DataAlertId, @NumberOfRecords, GETDATE());";
}