xtablo-source/backend/internal/db/queries/tablos.sql

-- name: ListTablosByUser :many
SELECT id, user_id, title, description, color, created_at, updated_at, status
FROM tablos
WHERE user_id = $1
ORDER BY created_at DESC;

-- name: ListTablosByUserWithDiscussionUnread :many
SELECT tablos.id,
       tablos.user_id,
       tablos.title,
       tablos.description,
       tablos.color,
       tablos.created_at,
       tablos.updated_at,
       tablos.status,
       COALESCE(count(unread_messages.id), 0)::bigint AS discussion_unread_count
FROM tablos
LEFT JOIN discussion_read_states AS read_state
  ON read_state.tablo_id = tablos.id
 AND read_state.user_id = sqlc.arg(user_id)
LEFT JOIN discussion_messages AS last_read_message
  ON last_read_message.id = read_state.last_read_message_id
LEFT JOIN discussion_messages AS unread_messages
  ON unread_messages.tablo_id = tablos.id
 AND (
      read_state.last_read_message_id IS NULL
      OR last_read_message.id IS NULL
      OR unread_messages.created_at > last_read_message.created_at
 )
WHERE tablos.user_id = sqlc.arg(user_id)
GROUP BY tablos.id, tablos.user_id, tablos.title, tablos.description, tablos.color, tablos.created_at, tablos.updated_at, tablos.status
ORDER BY tablos.created_at DESC;

-- name: GetTabloByID :one
SELECT id, user_id, title, description, color, created_at, updated_at, status
FROM tablos
WHERE id = $1 AND user_id = $2;

-- name: InsertTablo :one
INSERT INTO tablos (user_id, title, description, color)
VALUES ($1, $2, $3, $4)
RETURNING id, user_id, title, description, color, created_at, updated_at, status;

-- name: UpdateTablo :one
UPDATE tablos
SET title = $2, description = $3, color = $4, updated_at = now()
WHERE id = $1
RETURNING id, user_id, title, description, color, created_at, updated_at, status;

-- name: DeleteTablo :exec
DELETE FROM tablos WHERE id = $1 AND user_id = $2;

-- name: ListTabloProgressByIDs :many
-- Batch aggregation: one query for all tablos on the dashboard (D-06).
-- Counts all tasks regardless of etape assignment.
SELECT
    tablo_id,
    COUNT(*) FILTER (WHERE status = 'done')::int AS done_tasks,
    COUNT(*)::int                                 AS total_tasks
FROM tasks
WHERE tablo_id = ANY(@tablo_ids::uuid[])
GROUP BY tablo_id;