namespace KmsDAL.Query.KmGap
{
    public static class KmGapQB
    {
        // MERGE to upsert: increment count if same query+tenant already exists
        public const string UPSERT_KMGAP = @"
            MERGE KMS.TKMKNOWLEDGEGAP AS target
            USING (SELECT @QueryHash AS QH, @TenantId AS TID) AS source
                ON (target.QUERYHASH = source.QH AND target.TENANTID = source.TID)
            WHEN MATCHED THEN
                UPDATE SET
                    OCCURRENCECOUNT  = target.OCCURRENCECOUNT + 1,
                    LASTQUERIEDON    = GETDATE(),
                    BESTSCORERETURNED = CASE WHEN @BestScoreReturned > target.BESTSCORERETURNED
                                             THEN @BestScoreReturned ELSE target.BESTSCORERETURNED END,
                    MODIFIEDBYID     = @ModifiedById,
                    MODIFIEDON       = GETDATE()
            WHEN NOT MATCHED THEN
                INSERT
                    (GAPID, QUERYTEXT, QUERYHASH, BESTSCORERETURNED, BESTARTIFACTID,
                     CALLERJSON, GAPSTATUS, OCCURRENCECOUNT, LASTQUERIEDON,
                     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                     MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
                VALUES
                    (@GapId, @QueryText, @QueryHash, @BestScoreReturned, @BestArtifactId,
                     @CallerJson, 0, 1, GETDATE(),
                     0, 1, 9999, @CreatedById, GETDATE(),
                     @ModifiedById, GETDATE(), 5, @TenantId);";

        public const string GET_KMGAPS_PAGED = @"
            SELECT  GAPID            AS GapId,
                    QUERYTEXT        AS QueryText,
                    GAPSTATUS        AS GapStatus,
                    OCCURRENCECOUNT  AS OccurrenceCount,
                    LASTQUERIEDON    AS LastQueriedOn,
                    BESTSCORERETURNED AS BestScoreReturned,
                    TENANTID         AS TenantId,
                    CREATEDON        AS CreatedOn
            FROM    KMS.TKMKNOWLEDGEGAP
            WHERE   TENANTID  = @TenantId
            AND     STATUS    NOT IN (2)
            AND     (@GapStatus = 255 OR GAPSTATUS = @GapStatus)
            ORDER BY OCCURRENCECOUNT DESC, LASTQUERIEDON DESC
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string UPDATE_KMGAP_STATUS = @"
            UPDATE KMS.TKMKNOWLEDGEGAP
            SET    GAPSTATUS      = @NewStatus,
                   RESOLUTIONNOTES = @ResolutionNotes,
                   RESOLVEDBYID   = @ResolvedById,
                   RESOLVEDON     = GETDATE(),
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE()
            WHERE  GAPID     = @GapId
            AND    TENANTID  = @TenantId";
    }
}