namespace ECPDAL.Communication.QueryBuilders;

public static class CommentMentionQB
{
    // UX_TCOMMENTMENTION_THREAD_USER dedupes repeat mentions of the same user on the same thread.
    public const string SAVE_COMMENT_MENTION = @"
        INSERT INTO TCOMMENTMENTION (
            COMMENTMENTIONID, COMMENTTHREADID, MENTIONEDUSERID, ISNOTIFIED, NOTIFIEDON,
            TENANTID, DATABASENAME, DATABASETYPE, STATUS,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @CommentMentionId, @CommentThreadId, @MentionedUserId, @IsNotified, @NotifiedOn,
            @TenantId, @DatabaseName, @DatabaseType, @Status,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    // Index: IX_TCOMMENTMENTION_MENTIONEDUSERID_ISNOTIFIED — "pending" = not yet acknowledged by
    // the client (ISNOTIFIED tracks push-delivery, not read-state; GetPendingCommentMentions
    // returns every mention for the user regardless of ISNOTIFIED so the mention inbox is
    // complete — ISNOTIFIED only gates the fallback push-notification send).
    public const string GET_PENDING_MENTIONS_FOR_USER = @"
        SELECT
            CM.COMMENTMENTIONID  AS CommentMentionId,
            CM.COMMENTTHREADID   AS CommentThreadId,
            CM.MENTIONEDUSERID   AS MentionedUserId,
            U.USERNAME           AS MentionedUserName,
            CM.ISNOTIFIED        AS IsNotified,
            CM.NOTIFIEDON        AS NotifiedOn,
            CM.TENANTID          AS TenantId,
            CM.DATABASENAME      AS DatabaseName,
            CM.DATABASETYPE      AS DatabaseType,
            CM.STATUS            AS Status,
            CM.CREATEDBYID       AS CreatedById,
            CM.CREATEDON         AS CreatedOn,
            CM.MODIFIEDBYID      AS ModifiedById,
            CM.MODIFIEDON        AS ModifiedOn,
            CT.OBJECTTYPEID      AS ObjectTypeId,
            CT.OBJECTID          AS ObjectId,
            CT.COMMENTTEXT       AS CommentText
        FROM   TCOMMENTMENTION CM
        JOIN   TCOMMENTTHREAD CT ON CM.COMMENTTHREADID = CT.COMMENTTHREADID
        LEFT JOIN MUSER U        ON CM.MENTIONEDUSERID  = U.USERID
        WHERE  CM.MENTIONEDUSERID = @UserId
          AND  CM.DATABASENAME    = @DatabaseName
          AND  CM.STATUS          = 1
        ORDER BY CM.CREATEDON DESC";

    // @IsNotified/@NotifiedOn are supplied as bound parameters (bool/DateTime), never as SQL
    // literals — Dapper's provider maps C# bool to BOOLEAN (Npgsql) or BIT (SqlClient)
    // automatically, so the same constant runs unmodified on both engines. Idempotent: safe to
    // call even if some rows are already marked notified.
    public const string MARK_MENTIONS_NOTIFIED = @"
        UPDATE TCOMMENTMENTION
        SET    ISNOTIFIED = @IsNotified,
               NOTIFIEDON = @NotifiedOn
        WHERE  COMMENTTHREADID = @CommentThreadId
          AND  DATABASENAME    = @DatabaseName";
}