-- 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;

-- name: ListOpenTasksByUser :many
SELECT
    tasks.id,
    tasks.tablo_id,
    tasks.title,
    tasks.description,
    tasks.status,
    tasks.position,
    tasks.created_at,
    tasks.updated_at,
    tasks.etape_id,
    tablos.title AS tablo_title
FROM tasks
JOIN tablos ON tablos.id = tasks.tablo_id
WHERE tablos.user_id = $1
  AND tasks.status <> 'done'
ORDER BY tasks.created_at DESC
LIMIT 30;