#!/usr/bin/env bash
# Purpose: Query the claudomator SQLite database
# Usage:
#   ./claudomator-db.sh task <short-id-or-uuid>   -- task + dependencies
#   ./claudomator-db.sh deps <short-id-or-uuid>   -- blocking dependencies
#   ./claudomator-db.sh blocked                   -- all BLOCKED tasks
#   ./claudomator-db.sh list [STATE]              -- list tasks, optionally filter by state

DB=/site/doot.terst.org/data/claudomator.db
CMD=${1:-list}

resolve_id() {
  local id=$1
  sqlite3 "$DB" "SELECT id FROM tasks WHERE id LIKE '${id}%' LIMIT 1;"
}

case "$CMD" in
  task)
    full=$(resolve_id "$2")
    [ -z "$full" ] && { echo "Task not found: $2"; exit 1; }
    sqlite3 -column -header "$DB" "SELECT id, name, state, priority, created_at FROM tasks WHERE id = '$full';"
    ;;
  deps)
    full=$(resolve_id "$2")
    [ -z "$full" ] && { echo "Task not found: $2"; exit 1; }
    echo "=== Blocking dependencies of ${full} ==="
    sqlite3 -column -header "$DB" \
      "SELECT t.id, t.name, t.state FROM tasks t
       WHERE t.id IN (
         SELECT value FROM tasks, json_each(tasks.depends_on_json) WHERE tasks.id = '$full'
       );"
    echo ""
    echo "=== Tasks blocked by ${full} ==="
    sqlite3 -column -header "$DB" \
      "SELECT t.id, t.name, t.state FROM tasks t
       WHERE EXISTS (
         SELECT 1 FROM json_each(t.depends_on_json) WHERE value = '$full'
       );"
    ;;
  blocked)
    sqlite3 -column -header "$DB" "SELECT id, name, state, priority FROM tasks WHERE state = 'BLOCKED' ORDER BY created_at DESC;"
    ;;
  list)
    STATE=${2:-}
    if [ -n "$STATE" ]; then
      sqlite3 -column -header "$DB" "SELECT id, name, state, priority, created_at FROM tasks WHERE state = '$STATE' ORDER BY created_at DESC;"
    else
      sqlite3 -column -header "$DB" "SELECT id, name, state, priority, created_at FROM tasks ORDER BY created_at DESC;"
    fi
    ;;
  *)
    echo "Unknown command: $CMD"
    exit 1
    ;;
esac