namespace FrameworkDAL.Query.GOP
{
    // ============================================================
    // GopReportQB — Aggregate report queries for the GOP engine.
    //
    // Transactional tables (TENANTID-scoped, UPPERCASE — the C# parameter stays
    // named @ClientId by this codebase's own convention, bound from
    // loginDTO.ClientId, but the real SQL Server column is TENANTID, not
    // ClientId; see GopQueueQB.cs's header comment for the same convention):
    //   LGOPEXECUTIONMETRICS, LGOPEXECUTIONNODEMETRICS,
    //   LGOPEXECUTIONNODELOG, TGOPDEADLETTER
    //
    // Master tables (TENANTID-scoped, UPPERCASE):
    //   MGOPFLOW, MGOPFLOWSNAPSHOT,
    //   MQUALIFIERDEFINITION, MQUALIFIERENTITY, MQUALIFIERVERSION
    //
    // All queries are read-only aggregations — no N+1.
    // DateFrom/DateTo are optional; NULL = no filter applied.
    //
    // Required indexes (add to migration script):
    //   IX_LGOPEXECUTIONMETRICS_TenantId_RecordedOn (TENANTID, RECORDEDON)
    //   IX_TGOPDEADLETTER_TenantId_Resolved (TENANTID, ISRESOLVED, CREATEDON)
    //   IX_LGOPEXECUTIONNODEMETRICS_TenantId_NodeCode (TENANTID, NODECODE, RECORDEDON)
    // ============================================================
    public static class GopReportQB
    {
        // ── 1. Execution Summary — per-flow rollup ─────────────────────────────────

        public const string GET_EXECUTION_SUMMARY = @"
            SELECT  m.GOPFLOWCODE                               AS FlowCode,
                    COUNT(*)                                    AS TotalCount,
                    SUM(CASE WHEN m.ISSUCCESS = 1 THEN 1 ELSE 0 END) AS SuccessCount,
                    SUM(CASE WHEN m.ISSUCCESS = 0 THEN 1 ELSE 0 END) AS FailureCount,
                    SUM(m.TOTALRETRIES)                         AS RetryCount,
                    AVG(CAST(m.TOTALDURATIONMS AS FLOAT))       AS AvgDurationMs,
                    MAX(m.TOTALDURATIONMS)                      AS MaxDurationMs,
                    MAX(m.RECORDEDON)                           AS LastRunAt
            FROM    LGOPEXECUTIONMETRICS m
            WHERE   m.TENANTID = @ClientId
              AND   (@DateFrom IS NULL OR m.RECORDEDON >= @DateFrom)
              AND   (@DateTo   IS NULL OR m.RECORDEDON <= @DateTo)
            GROUP BY m.GOPFLOWCODE
            ORDER BY TotalCount DESC;";

        // ── 2. DLQ Aging — unresolved dead-letter items with age ──────────────────

        public const string GET_DLQ_AGING = @"
            SELECT  dl.GOPDEADLETTERID       AS DlqId,
                    dl.GOPEXECUTIONID        AS ExecutionId,
                    dl.GOPFLOWCODE           AS FlowCode,
                    dl.FAILEDNODECODE        AS FailedNodeCode,
                    dl.ERRORMESSAGE          AS ErrorMessage,
                    dl.FAILURECATEGORY       AS FailureCategory,
                    dl.RETRYCOUNT            AS RetryCount,
                    dl.CREATEDON             AS MovedToDlqAt,
                    DATEDIFF(day, dl.CREATEDON, SYSUTCDATETIME()) AS AgeDays
            FROM    TGOPDEADLETTER dl
            WHERE   dl.TENANTID   = @ClientId
              AND   dl.ISRESOLVED = 0
              AND   (@MinAgeDays IS NULL
                     OR DATEDIFF(day, dl.CREATEDON, SYSUTCDATETIME()) >= @MinAgeDays)
            ORDER BY dl.CREATEDON ASC;";

        // ── 3. Qualifier Finding Frequency — executions per qualifier ────────────
        //
        // Joins LGOPEXECUTIONNODELOG (TENANTID-scoped) to MQUALIFIERDEFINITION
        // on QUALIFIERCODE = NODECODE.  This surfaces only nodes that correspond
        // to a registered qualifier definition. NODESTATUS is a TINYINT
        // (1=Running, 2=Success, 3=Failed) — not a string.

        public const string GET_QUALIFIER_FINDING_FREQUENCY = @"
            SELECT  qd.QUALIFIERCODE        AS QualifierCode,
                    qd.QUALIFIERNAME        AS QualifierName,
                    qd.ENTITYCODE           AS EntityCode,
                    COUNT(nl.GOPEXECUTIONNODELOGID) AS ExecutionCount,
                    SUM(CASE WHEN nl.NODESTATUS = 2 THEN 1 ELSE 0 END) AS SuccessCount,
                    SUM(CASE WHEN nl.NODESTATUS = 3 THEN 1 ELSE 0 END) AS FailureCount,
                    MAX(nl.COMPLETEDON)     AS LastSeenAt
            FROM    MQUALIFIERDEFINITION qd
            LEFT JOIN LGOPEXECUTIONNODELOG nl
                    ON  nl.NODECODE  = qd.QUALIFIERCODE
                    AND nl.TENANTID  = @TenantId
                    AND (@DateFrom IS NULL OR nl.STARTEDON >= @DateFrom)
                    AND (@DateTo   IS NULL OR nl.STARTEDON <= @DateTo)
            WHERE   qd.TENANTID = @TenantId
              AND   qd.STATUS  <> 2
            GROUP BY qd.QUALIFIERCODE, qd.QUALIFIERNAME, qd.ENTITYCODE
            ORDER BY ExecutionCount DESC;";

        // ── 4. Qualifier Performance — timing stats per qualifier ─────────────────
        //
        // Joins LGOPEXECUTIONNODEMETRICS to MQUALIFIERDEFINITION to filter
        // to qualifier-type nodes only.

        public const string GET_QUALIFIER_PERFORMANCE = @"
            SELECT  qd.QUALIFIERCODE                            AS QualifierCode,
                    qd.QUALIFIERNAME                            AS QualifierName,
                    COUNT(nm.NODECODE)                          AS EvalCount,
                    AVG(CAST(nm.DURATIONMS AS FLOAT))           AS AvgDurationMs,
                    MAX(nm.DURATIONMS)                          AS MaxDurationMs,
                    MIN(nm.DURATIONMS)                          AS MinDurationMs,
                    CAST(
                        SUM(CASE WHEN nm.ISSUCCESS = 1 THEN 1.0 ELSE 0.0 END)
                        / NULLIF(COUNT(nm.NODECODE), 0) * 100
                    AS DECIMAL(5,2))                            AS SuccessRate
            FROM    MQUALIFIERDEFINITION qd
            LEFT JOIN LGOPEXECUTIONNODEMETRICS nm
                    ON  nm.NODECODE  = qd.QUALIFIERCODE
                    AND nm.TENANTID  = @TenantId
                    AND (@DateFrom IS NULL OR nm.RECORDEDON >= @DateFrom)
                    AND (@DateTo   IS NULL OR nm.RECORDEDON <= @DateTo)
            WHERE   qd.TENANTID = @TenantId
              AND   qd.STATUS  <> 2
            GROUP BY qd.QUALIFIERCODE, qd.QUALIFIERNAME
            ORDER BY AvgDurationMs DESC;";

        // ── 5. Flow Drift — flows with unactivated newer snapshots ────────────────
        //
        // SNAPSHOTVERSION is a free-text NVARCHAR(20) label the publisher types in
        // (e.g. "v1", "v1-e2e-2647") — NOT a sequential integer — so "latest" can
        // only be determined by GOPFLOWSNAPSHOTID (identity PK, insert order) or
        // PUBLISHEDON, never by comparing/MAX()-ing the version label itself.
        // IsDrifted = 1 when the flow has no active snapshot at all, or the most
        // recently published snapshot isn't the currently active one.

        public const string GET_FLOW_DRIFT = @"
            SELECT  f.TENANTID                  AS TenantId,
                    f.GOPFLOWID                 AS FlowId,
                    f.GOPFLOWCODE               AS FlowCode,
                    f.GOPFLOWNAME               AS FlowName,
                    a.SNAPSHOTVERSION           AS ActiveSnapshotVersion,
                    a.SPECAGGREGATEHASH         AS ActiveSpecHash,
                    l.SNAPSHOTVERSION           AS LatestSnapshotVersion,
                    l.SPECAGGREGATEHASH         AS LatestSpecHash,
                    CASE
                        WHEN a.GOPFLOWSNAPSHOTID IS NULL THEN 1
                        WHEN l.GOPFLOWSNAPSHOTID <> a.GOPFLOWSNAPSHOTID THEN 1
                        ELSE 0
                    END                         AS IsDrifted,
                    l.PUBLISHEDON               AS LatestPublishedOn
            FROM    MGOPFLOW f
            LEFT JOIN MGOPFLOWSNAPSHOT a
                    ON  a.TENANTID  = f.TENANTID
                    AND a.GOPFLOWID = f.GOPFLOWID
                    AND a.ISACTIVE  = 1
            LEFT JOIN (
                SELECT  TENANTID, GOPFLOWID, MAX(GOPFLOWSNAPSHOTID) AS MaxId
                FROM    MGOPFLOWSNAPSHOT
                WHERE   TENANTID = @TenantId
                GROUP BY TENANTID, GOPFLOWID
            ) lv ON  lv.TENANTID  = f.TENANTID
                 AND lv.GOPFLOWID = f.GOPFLOWID
            LEFT JOIN MGOPFLOWSNAPSHOT l
                    ON  l.GOPFLOWSNAPSHOTID = lv.MaxId
            WHERE   f.TENANTID = @TenantId
              AND   f.STATUS  <> 2
            ORDER BY IsDrifted DESC, f.GOPFLOWCODE;";

        // ── 6. Qualifier Coverage — bindings per entity/stage/scope ───────────────
        //
        // ISACTIVE = 1 — active bindings (an earlier version of this query used
        // = 0, which only ever surfaced inactive bindings; fixed).

        public const string GET_QUALIFIER_COVERAGE = @"
            SELECT  qe.TENANTID                 AS TenantId,
                    qe.ENTITYCODE               AS EntityCode,
                    qe.STAGE                    AS Stage,
                    qe.SCOPE                    AS Scope,
                    COUNT(DISTINCT qe.QUALIFIERID)  AS TotalQualifiers,
                    COUNT(DISTINCT CASE WHEN qd.STATUS = 1 THEN qe.QUALIFIERID END) AS ActiveQualifiers,
                    MAX(qv.ACTIVATEDON)         AS LastActivatedOn
            FROM    MQUALIFIERENTITY qe
            INNER JOIN MQUALIFIERDEFINITION qd
                    ON  qd.TENANTID     = qe.TENANTID
                    AND qd.QUALIFIERID  = qe.QUALIFIERID
            LEFT JOIN MQUALIFIERVERSION qv
                    ON  qv.TENANTID     = qe.TENANTID
                    AND qv.QUALIFIERID  = qe.QUALIFIERID
                    AND qv.VERSIONSTATUS = 1
            WHERE   qe.TENANTID  = @TenantId
              AND   qe.ISACTIVE  = 1
              AND   qd.STATUS   <> 2
            GROUP BY qe.TENANTID, qe.ENTITYCODE, qe.STAGE, qe.SCOPE
            ORDER BY qe.ENTITYCODE, qe.STAGE, qe.SCOPE;";
    }
}