namespace FrameworkDAL.Query.SIP
{
    // ============================================================
    // SIP Pattern Entry (Interaction Pattern Ledger) Query Builder
    //
    // Backs TSIPPATTERNENTRY — a pure append-only, immutable log (ISIPSignalService never updates
    // or deletes a row here). IDENTITY PK, same pattern as GEFOutcomeFeedbackQB / this repo's
    // existing GOP append-only logs — see the migration file's header comment.
    //
    // Required index: IX_TSIPPATTERNENTRY_LOOKUP (TENANTID, DOMAIN, ENTITYCODE, ENTITYID, SIGNALTYPE)
    // — GET_PATTERN_SUMMARY's WHERE + GROUP BY filters on exactly this column combination.
    // ============================================================
    public static class SIPPatternEntryQB
    {
        public const string INSERT_PATTERN_ENTRY = @"
            INSERT INTO TSIPPATTERNENTRY
                (TENANTID, DOMAIN, ENTITYCODE, ENTITYID, SIGNALTYPE, DETAIL, STRENGTH,
                 ACTORROLE, ACTORID, DETECTEDON)
            VALUES
                (@ClientId, @Domain, @EntityCode, @EntityId, @SignalType, @Detail, @Strength,
                 @ActorRole, @ActorId, @DetectedOn);";

        // Single query, no N+1: aggregates every SignalType's OccurrenceCount/FirstSeen/LastSeen
        // in one GROUP BY. IsRecurring is computed in FrameworkBLL.SIP.SIPSignalService, not here.
        public const string GET_PATTERN_SUMMARY = @"
            SELECT  SIGNALTYPE      AS SignalType,
                    COUNT(*)        AS OccurrenceCount,
                    MIN(DETECTEDON) AS FirstSeen,
                    MAX(DETECTEDON) AS LastSeen
            FROM    TSIPPATTERNENTRY
            WHERE   TENANTID   = @ClientId
              AND   DOMAIN     = @Domain
              AND   ENTITYCODE = @EntityCode
              AND   ENTITYID   = @EntityId
            GROUP BY SIGNALTYPE
            ORDER BY MAX(DETECTEDON) DESC;";
    }
}