MySQL statement to get data from 'user' table and last message from 'chat' table

I have a ‘users’ table for general information of every user (primary key is ‘user_id’) and a table ‘chat’ to store messages (primary key is ‘msg_id’) that have ‘incoming_msg_id’ (which eventually corresponds with ‘users.user_id’) and ‘outgoing_msg_id’ (which eventually corresponds with ‘users.user_id’).
‘chat.read_at’ is ‘null’ as long as the message is not read but is a date/time field.

I wish to list all users and their corresponding last message, if there is any.

With the following statement I have errors as I get also messages that are not from or to the corresponding user.

I am pretty new to SQL and trying to dig my way through it and did several attempts to do it with (INNER) JOIN but never got so far as with this statement:

    SELECT user_id, username, image, status, active,
    CASE WHEN status IN ("Active Now") THEN true ELSE false END AS isonline,
    (SELECT msg 
    FROM chat
    WHERE
    (incoming_msg_id = users.user_id) OR
    (outgoing_msg_id = users.user_id)
    ORDER BY msg_id DESC LIMIT 1
    ) AS last_msg,

    (SELECT msg_id
    FROM chat
    WHERE
    (incoming_msg_id = users.user_id) OR
    (outgoing_msg_id = users.user_id)
    ORDER BY msg_id DESC LIMIT 1 ) AS last_msg_id,

    (SELECT outgoing_msg_id
    FROM chat
    WHERE msg_id = last_msg_id
    ) AS outgoing_msg_id,

    (SELECT incoming_msg_id
    FROM chat
    WHERE msg_id = last_msg_id
    ) AS incoming_msg_id,

    (SELECT created_at
    FROM chat
    WHERE msg_id = last_msg_id
    ) AS last_interaction, 

    (SELECT IF(read_at = null, false, true)
    FROM chat
    WHERE msg_id = last_msg_id
    ) AS has_unread_msg,

    (SELECT read_at
    FROM chat
    WHERE msg_id = last_msg_id
    ) AS read_at,

    (SELECT IF(user_id = incoming_msg_id, true, false) AS isme
    FROM chat
    WHERE msg_id = last_msg_id
    ) AS self 

    FROM users
    WHERE
    (NOT user_id = ?) AND 
    (users.username LIKE ?)
    ORDER BY active DESC, status ASC, has_unread_msg DESC, last_interaction DESC

My code after the first comment with the exact same result (showing messages that do not belong to me and some of the other users listed:

   WITH 
   cte1 AS (
      SELECT *, incoming_msg_id as user_id FROM chat
      UNION ALL
      SELECT *, outgoing_msg_id FROM chat
      ),
   cte2 AS (
      SELECT *, IF(ISNULL(read_at), true, false) AS has_unread_msg, msg AS last_msg, IF(user_id = incoming_msg_id, true, false) AS self, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
      FROM cte1
      )
   SELECT *, IF(status = "Active now", true, false) AS isonline
   FROM users
   JOIN cte2 USING (user_id)
   WHERE rn = 1

Create and inserts of the tables:

CREATE TABLE `chat` (
  `msg_id` int(11) NOT NULL,
  `incoming_msg_id` int(255) NOT NULL,
  `outgoing_msg_id` int(255) NOT NULL,
  `msg` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `chat` (`msg_id`, `incoming_msg_id`, `outgoing_msg_id`, `msg`, `read_at`, `created_at`) VALUES
(3, 1, 5, 'no way', '2022-10-23 17:53:13', '2022-10-23 17:53:13'),
(4, 1, 5, 'I am sitting here and nothing happens!', '2022-10-23 17:53:13', '2022-10-23 17:53:13'),
(5, 1, 5, 'Still sitting here...', '2022-10-23 17:53:13', '2022-10-23 17:53:13'),
(9, 5, 1, 'Hi there', '2022-10-23 16:13:25', '2022-10-23 16:13:25'),
...
(45, 5, 25, 'Hey there. How is ya doing??', '2022-10-22 08:16:18', '2022-10-22 08:16:18'),
(46, 3, 25, 'hello there! Are you available?', '2022-10-23 18:16:08', '2022-10-23 18:16:08'),
(47, 3, 25, 'Something?', '2022-10-23 18:16:08', '2022-10-23 18:16:08'),
(48, 1, 25, 'hi!', '2022-10-24 11:45:13', '2022-10-24 11:45:13'),
(49, 1, 25, 'hi!', '2022-10-24 11:45:13', '2022-10-24 11:45:13'),
...
(63, 25, 5, 'nothing special here!', '2022-10-23 21:29:04', '2022-10-23 21:29:04'),
(64, 5, 25, 'What if we meet next week?', '2022-10-22 08:16:18', '2022-10-22 08:16:18'),
(65, 5, 25, 'What if we meet next week?', '2022-10-22 08:16:18', '2022-10-22 08:16:18'),
...
(365, 2, 5, 'sechs', NULL, '2022-10-21 20:49:09'),
(366, 2, 5, 'sieben', NULL, '2022-10-21 20:49:10'),
(367, 2, 5, 'acht', NULL, '2022-10-21 20:50:18'),
(368, 2, 5, 'neun', NULL, '2022-10-21 20:50:19'),

msg_id is primary and incremental

This is the users table:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET armscii8 NOT NULL,
  ...
  `image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  ...
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  ...
  `active` tinyint(1) DEFAULT 0,
  `status` varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Not active',
  `last_chat_notification_id` int(11) DEFAULT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The first users are correct (talking to me as I am logged in with the user_id 25)
But from the 6th user on it shows messages that are not between us.

The list shows every existing user but only the last message between them and the currently logged in user (or non if not applicable).
The current user should also be excluded from the list.