-- name: ListProjects :many
SELECT * FROM projects
ORDER BY sort ASC, created_at ASC;

-- name: ListActiveProjects :many
SELECT * FROM projects
WHERE status = 'active'
ORDER BY sort ASC, created_at ASC;

-- name: ListActiveProjectsByCategory :many
SELECT * FROM projects
WHERE category_id = ? AND status = 'active'
ORDER BY sort ASC, created_at ASC;

-- name: CountProjectsByCategory :one
SELECT COUNT(*) FROM projects
WHERE category_id = ?;

-- name: GetProject :one
SELECT * FROM projects
WHERE id = ?;

-- name: CreateProject :one
INSERT INTO projects (
  id, category_id, name, subtitle, price_text, image, status, sort
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
RETURNING *;

-- name: UpdateProject :one
UPDATE projects
SET category_id = ?,
    name = ?,
    subtitle = ?,
    price_text = ?,
    image = ?,
    status = ?,
    sort = ?,
    updated_at = CURRENT_TIMESTAMP
WHERE id = ?
RETURNING *;

-- name: DeleteProject :exec
DELETE FROM projects
WHERE id = ?;