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

-- name: ListTasksByTablo :many
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
FROM tasks
WHERE tablo_id = $1
ORDER BY status, position, created_at;

-- name: InsertTask :one
INSERT INTO tasks (tablo_id, title, description, status, position, etape_id)
VALUES ($1, $2, $3, $4, $5, $6)
RETURNING id, tablo_id, title, description, status, position, created_at, updated_at, etape_id;

-- name: GetTaskByID :one
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
FROM tasks
WHERE id = $1 AND tablo_id = $2;

-- name: UpdateTask :one
UPDATE tasks
SET title = $2, description = $3, status = $4, position = $5, etape_id = $6, updated_at = now()
WHERE id = $1
RETURNING id, tablo_id, title, description, status, position, created_at, updated_at, etape_id;

-- name: DeleteTask :exec
DELETE FROM tasks WHERE id = $1 AND tablo_id = $2;

-- name: MaxPositionByTabloAndStatus :one
SELECT COALESCE(MAX(position), 0)::integer AS max_position
FROM tasks
WHERE tablo_id = $1 AND status = $2;

-- name: ListTasksByTabloAndEtape :many
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
FROM tasks
WHERE tablo_id = $1 AND etape_id = $2
ORDER BY status, position, created_at;

-- name: ListUnassignedTasksByTablo :many
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
FROM tasks
WHERE tablo_id = $1 AND etape_id IS NULL
ORDER BY status, position, created_at;