namespace CollabDAL.Query.CollabParticipant;

public static class CollabParticipantQB
{
    // Index: IX_CollabParticipant_Session_Active
    public const string GET_PARTICIPANTS_BY_SESSION = @"
        SELECT p.ParticipantId, p.SessionId, p.UserId, p.DisplayName,
               p.Role, p.IsActive, p.HasControl, p.IsFollowingHost,
               p.CursorColor, p.ConnectionId, p.JoinedAt, p.LeftAt,
               p.DatabaseName
        FROM   collab.CollabParticipant p
        WHERE  p.SessionId    = @SessionId
        AND    p.IsActive     = 1
        AND    p.DatabaseName = @DatabaseName
        ORDER BY p.JoinedAt ASC";

    // Index: IX_CollabParticipant_ConnectionId
    public const string GET_PARTICIPANT_BY_CONNECTION = @"
        SELECT p.ParticipantId, p.SessionId, p.UserId, p.DisplayName,
               p.Role, p.IsActive, p.HasControl, p.IsFollowingHost,
               p.CursorColor, p.ConnectionId, p.JoinedAt, p.LeftAt,
               p.DatabaseName
        FROM   collab.CollabParticipant p
        WHERE  p.ConnectionId = @ConnectionId";

    // Index: UX_CollabParticipant_Session_User_Active
    public const string GET_ACTIVE_PARTICIPANT = @"
        SELECT p.ParticipantId, p.SessionId, p.UserId, p.DisplayName,
               p.Role, p.IsActive, p.HasControl, p.IsFollowingHost,
               p.CursorColor, p.ConnectionId, p.JoinedAt, p.LeftAt,
               p.DatabaseName
        FROM   collab.CollabParticipant p
        WHERE  p.SessionId    = @SessionId
        AND    p.UserId       = @UserId
        AND    p.IsActive     = 1
        AND    p.DatabaseName = @DatabaseName";

    // Index: IX_CollabParticipant_Session_Active (HasControl filter)
    public const string GET_CONTROL_HOLDER = @"
        SELECT TOP 1
               p.ParticipantId, p.SessionId, p.UserId, p.DisplayName,
               p.Role, p.HasControl, p.IsActive, p.IsFollowingHost,
               p.CursorColor, p.ConnectionId, p.JoinedAt, p.LeftAt,
               p.DatabaseName
        FROM   collab.CollabParticipant p
        WHERE  p.SessionId    = @SessionId
        AND    p.HasControl   = 1
        AND    p.IsActive     = 1
        AND    p.DatabaseName = @DatabaseName";

    public const string ADD_PARTICIPANT = @"
        INSERT INTO collab.CollabParticipant
               (SessionId, UserId, DisplayName, Role,
                IsActive, HasControl, IsFollowingHost,
                CursorColor, JoinedAt, DatabaseName)
        VALUES (@SessionId, @UserId, @DisplayName, @Role,
                1, @HasControl, 1,
                @CursorColor, GETUTCDATE(), @DatabaseName)";

    public const string UPDATE_PARTICIPANT_LEFT = @"
        UPDATE collab.CollabParticipant
        SET    IsActive     = 0,
               LeftAt       = GETUTCDATE(),
               ConnectionId = NULL
        WHERE  SessionId    = @SessionId
        AND    UserId       = @UserId
        AND    DatabaseName = @DatabaseName";

    public const string UPDATE_PARTICIPANT_CONNECTION = @"
        UPDATE collab.CollabParticipant
        SET    ConnectionId = @ConnectionId,
               IsActive     = @IsActive
        WHERE  SessionId    = @SessionId
        AND    UserId       = @UserId
        AND    DatabaseName = @DatabaseName";

    // Atomic single UPDATE — sets target to HasControl=1, all others in session to 0
    public const string GRANT_CONTROL = @"
        UPDATE collab.CollabParticipant
        SET    HasControl   = CASE WHEN UserId = @TargetUserId THEN 1 ELSE 0 END
        WHERE  SessionId    = @SessionId
        AND    IsActive     = 1
        AND    DatabaseName = @DatabaseName";

    public const string REVOKE_ALL_CONTROL = @"
        UPDATE collab.CollabParticipant
        SET    HasControl   = 0
        WHERE  SessionId    = @SessionId
        AND    DatabaseName = @DatabaseName";

    public const string UPDATE_HEARTBEAT = @"
        UPDATE collab.CollabParticipant
        SET    LastHeartbeatAt = GETUTCDATE()
        WHERE  SessionId       = @SessionId
        AND    UserId          = @UserId
        AND    DatabaseName    = @DatabaseName";
}