-- name: ListDiscussionMessagesByTablo :many
SELECT discussion_messages.id,
       discussion_messages.tablo_id,
       discussion_messages.author_user_id,
       users.email AS author_email,
       discussion_messages.body,
       discussion_messages.created_at,
       discussion_messages.updated_at,
       discussion_messages.edited_at,
       discussion_messages.edited_by_user_id,
       discussion_messages.deleted_at,
       discussion_messages.deleted_by_user_id
FROM discussion_messages
JOIN users ON users.id = discussion_messages.author_user_id
WHERE discussion_messages.tablo_id = $1
ORDER BY discussion_messages.created_at, discussion_messages.id;

-- name: CreateDiscussionMessage :one
INSERT INTO discussion_messages (tablo_id, author_user_id, body)
VALUES ($1, $2, $3)
RETURNING id, tablo_id, author_user_id, body, created_at, updated_at, edited_at, edited_by_user_id, deleted_at, deleted_by_user_id;

-- name: GetDiscussionMessageWithAuthor :one
SELECT discussion_messages.id,
       discussion_messages.tablo_id,
       discussion_messages.author_user_id,
       users.email AS author_email,
       discussion_messages.body,
       discussion_messages.created_at,
       discussion_messages.updated_at,
       discussion_messages.edited_at,
       discussion_messages.edited_by_user_id,
       discussion_messages.deleted_at,
       discussion_messages.deleted_by_user_id
FROM discussion_messages
JOIN users ON users.id = discussion_messages.author_user_id
WHERE discussion_messages.id = $1
  AND discussion_messages.tablo_id = $2;

-- name: UpsertDiscussionReadState :one
INSERT INTO discussion_read_states (tablo_id, user_id, last_read_message_id, last_read_at)
VALUES ($1, $2, $3, now())
ON CONFLICT (tablo_id, user_id)
DO UPDATE SET last_read_message_id = EXCLUDED.last_read_message_id,
              last_read_at = now(),
              updated_at = now()
RETURNING tablo_id, user_id, last_read_message_id, last_read_at, created_at, updated_at;