package storage

import (
	"fmt"
	"path/filepath"
	"testing"
	"time"

	"github.com/thepeterstone/claudomator/internal/task"
)

func testDB(t *testing.T) *DB {
	t.Helper()
	dbPath := filepath.Join(t.TempDir(), "test.db")
	db, err := Open(dbPath)
	if err != nil {
		t.Fatalf("opening db: %v", err)
	}
	t.Cleanup(func() { db.Close() })
	return db
}

func TestOpen_CreatesSchema(t *testing.T) {
	db := testDB(t)
	// Should be able to query tasks table.
	_, err := db.ListTasks(TaskFilter{})
	if err != nil {
		t.Fatalf("querying tasks: %v", err)
	}
}

func TestCreateTask_AndGetTask(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)

	tk := &task.Task{
		ID:          "task-1",
		Name:        "Test Task",
		Description: "A test",
		Agent: task.AgentConfig{
			Type:         "claude",
			Model:        "sonnet",
			Instructions: "do it",
			ProjectDir:   "/tmp",
			MaxBudgetUSD: 2.5,
		},
		Priority:  task.PriorityHigh,
		Tags:      []string{"test", "alpha"},
		DependsOn: []string{},
		Retry:     task.RetryConfig{MaxAttempts: 3, Backoff: "exponential"},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}
	tk.Timeout.Duration = 10 * time.Minute

	if err := db.CreateTask(tk); err != nil {
		t.Fatalf("creating task: %v", err)
	}

	got, err := db.GetTask("task-1")
	if err != nil {
		t.Fatalf("getting task: %v", err)
	}
	if got.Name != "Test Task" {
		t.Errorf("name: want 'Test Task', got %q", got.Name)
	}
	if got.Agent.Model != "sonnet" {
		t.Errorf("model: want 'sonnet', got %q", got.Agent.Model)
	}
	if got.Agent.MaxBudgetUSD != 2.5 {
		t.Errorf("budget: want 2.5, got %f", got.Agent.MaxBudgetUSD)
	}
	if got.Priority != task.PriorityHigh {
		t.Errorf("priority: want 'high', got %q", got.Priority)
	}
	if got.Timeout.Duration != 10*time.Minute {
		t.Errorf("timeout: want 10m, got %v", got.Timeout.Duration)
	}
	if got.Retry.MaxAttempts != 3 {
		t.Errorf("retry: want 3, got %d", got.Retry.MaxAttempts)
	}
	if len(got.Tags) != 2 || got.Tags[0] != "test" {
		t.Errorf("tags: want [test alpha], got %v", got.Tags)
	}
	if got.State != task.StatePending {
		t.Errorf("state: want PENDING, got %v", got.State)
	}
}

func TestUpdateTaskState(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := &task.Task{
		ID:        "task-2",
		Name:      "Stateful",
		Agent:    task.AgentConfig{Type: "claude", Instructions: "test"},
		Priority:  task.PriorityNormal,
		Retry:     task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags:      []string{},
		DependsOn: []string{},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if err := db.CreateTask(tk); err != nil {
		t.Fatal(err)
	}

	if err := db.UpdateTaskState("task-2", task.StateQueued); err != nil {
		t.Fatalf("updating state: %v", err)
	}
	got, _ := db.GetTask("task-2")
	if got.State != task.StateQueued {
		t.Errorf("state: want QUEUED, got %v", got.State)
	}
}

func TestUpdateTaskState_NotFound(t *testing.T) {
	db := testDB(t)
	err := db.UpdateTaskState("nonexistent", task.StateQueued)
	if err == nil {
		t.Fatal("expected error for nonexistent task")
	}
}

func TestUpdateTaskState_InvalidTransition(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := &task.Task{
		ID:        "task-invalid",
		Name:      "InvalidTransition",
		Agent:    task.AgentConfig{Instructions: "test"},
		Priority:  task.PriorityNormal,
		Retry:     task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags:      []string{},
		DependsOn: []string{},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if err := db.CreateTask(tk); err != nil {
		t.Fatal(err)
	}

	// PENDING → COMPLETED is not a valid transition.
	err := db.UpdateTaskState("task-invalid", task.StateCompleted)
	if err == nil {
		t.Fatal("expected error for invalid state transition PENDING → COMPLETED")
	}
	}

	func TestGetMaxUpdatedAt(t *testing.T) {
	db := testDB(t)

	// Initial: should be zero time (SQLite might return a placeholder)
	t0, err := db.GetMaxUpdatedAt()
	if err != nil {
		t.Fatalf("GetMaxUpdatedAt (empty): %v", err)
	}
	if !t0.IsZero() && t0.Year() != 1 {
		t.Errorf("expected zero time for empty table, got %v", t0)
	}

	now := time.Now().UTC().Truncate(time.Second)
	tk := &task.Task{
		ID:        "max-1",
		Name:      "Max Updated",
		Agent:    task.AgentConfig{Type: "claude", Instructions: "test"},
		Priority:  task.PriorityNormal,
		Retry:     task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags:      []string{},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}

	if err := db.CreateTask(tk); err != nil {
		t.Fatalf("CreateTask: %v", err)
	}

	t1, err := db.GetMaxUpdatedAt()
	if err != nil {
		t.Fatalf("GetMaxUpdatedAt (1 task): %v", err)
	}
	if !t1.Equal(now) {
		t.Errorf("expected %v, got %v", now, t1)
	}

	if err := db.UpdateTaskState("max-1", task.StateQueued); err != nil {
		t.Fatalf("UpdateTaskState: %v", err)
	}
	// Note: UpdateTaskState in db.go uses time.Now().UTC() for updated_at.
	// We can't easily control it in the test without changing db.go to accept a time.
	// So we just check that it's after or equal now.
	t2, err := db.GetMaxUpdatedAt()
	if err != nil {
		t.Fatalf("GetMaxUpdatedAt (updated): %v", err)
	}
	if !t2.After(now) && !t2.Equal(now) {
		t.Errorf("expected max updated_at to be >= %v, got %v", now, t2)
	}
	}

	func TestListTasksSince(t *testing.T) {
	db := testDB(t)
	t1 := time.Date(2026, 3, 15, 10, 0, 0, 0, time.UTC)
	t2 := time.Date(2026, 3, 15, 11, 0, 0, 0, time.UTC)
	t3 := time.Date(2026, 3, 15, 12, 0, 0, 0, time.UTC)

	tasks := []*task.Task{
		{ID: "since-1", Name: "T1", CreatedAt: t1, UpdatedAt: t1, Agent: task.AgentConfig{Instructions: "t1"}},
		{ID: "since-2", Name: "T2", CreatedAt: t2, UpdatedAt: t2, Agent: task.AgentConfig{Instructions: "t2"}},
		{ID: "since-3", Name: "T3", CreatedAt: t3, UpdatedAt: t3, Agent: task.AgentConfig{Instructions: "t3"}},
	}
	for _, tk := range tasks {
		if err := db.CreateTask(tk); err != nil {
			t.Fatalf("CreateTask: %v", err)
		}
	}

	// Fetch all
	all, err := db.ListTasks(TaskFilter{})
	if err != nil || len(all) != 3 {
		t.Fatalf("ListTasks(all): err=%v, len=%d", err, len(all))
	}

	// Fetch since T1
	sinceT1, err := db.ListTasks(TaskFilter{Since: t1})
	if err != nil {
		t.Fatalf("ListTasks(since T1): %v", err)
	}
	if len(sinceT1) != 2 {
		t.Errorf("expected 2 tasks (T2, T3), got %d", len(sinceT1))
	}

	// Fetch since T2
	sinceT2, err := db.ListTasks(TaskFilter{Since: t2})
	if err != nil {
		t.Fatalf("ListTasks(since T2): %v", err)
	}
	if len(sinceT2) != 1 {
		t.Errorf("expected 1 task (T3), got %d", len(sinceT2))
	}

	// Fetch since T3
	sinceT3, err := db.ListTasks(TaskFilter{Since: t3})
	if err != nil {
		t.Fatalf("ListTasks(since T3): %v", err)
	}
	if len(sinceT3) != 0 {
		t.Errorf("expected 0 tasks, got %d", len(sinceT3))
	}
}

func TestListTasks_FilterByState(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()

	for i, state := range []task.State{task.StatePending, task.StatePending, task.StateRunning} {
		tk := &task.Task{
			ID: fmt.Sprintf("t-%d", i), Name: fmt.Sprintf("Task %d", i),
			Agent: task.AgentConfig{Type: "claude", Instructions: "x"}, Priority: task.PriorityNormal,
			Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
			Tags: []string{}, DependsOn: []string{},
			State: state, CreatedAt: now, UpdatedAt: now,
		}
		if err := db.CreateTask(tk); err != nil {
			t.Fatal(err)
		}
	}

	pending, err := db.ListTasks(TaskFilter{State: task.StatePending})
	if err != nil {
		t.Fatal(err)
	}
	if len(pending) != 2 {
		t.Errorf("want 2 pending, got %d", len(pending))
	}

	running, err := db.ListTasks(TaskFilter{State: task.StateRunning})
	if err != nil {
		t.Fatal(err)
	}
	if len(running) != 1 {
		t.Errorf("want 1 running, got %d", len(running))
	}
}

func TestListTasks_WithLimit(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	for i := 0; i < 5; i++ {
		tk := &task.Task{
			ID: fmt.Sprintf("lt-%d", i), Name: fmt.Sprintf("T%d", i),
			Agent: task.AgentConfig{Type: "claude", Instructions: "x"}, Priority: task.PriorityNormal,
			Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
			Tags: []string{}, DependsOn: []string{},
			State: task.StatePending, CreatedAt: now.Add(time.Duration(i) * time.Second), UpdatedAt: now,
		}
		db.CreateTask(tk)
	}

	tasks, err := db.ListTasks(TaskFilter{Limit: 3})
	if err != nil {
		t.Fatal(err)
	}
	if len(tasks) != 3 {
		t.Errorf("want 3, got %d", len(tasks))
	}
}

func TestCreateExecution_AndGet(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)

	// Need a task first.
	tk := &task.Task{
		ID: "etask", Name: "E", Agent: task.AgentConfig{Type: "claude", Instructions: "x"},
		Priority: task.PriorityNormal, Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags: []string{}, DependsOn: []string{},
		State: task.StatePending, CreatedAt: now, UpdatedAt: now,
	}
	db.CreateTask(tk)

	exec := &Execution{
		ID:         "exec-1",
		TaskID:     "etask",
		StartTime:  now,
		EndTime:    now.Add(5 * time.Minute),
		ExitCode:   0,
		Status:     "COMPLETED",
		StdoutPath: "/tmp/stdout.log",
		StderrPath: "/tmp/stderr.log",
		CostUSD:    0.42,
	}
	if err := db.CreateExecution(exec); err != nil {
		t.Fatalf("creating execution: %v", err)
	}

	got, err := db.GetExecution("exec-1")
	if err != nil {
		t.Fatalf("getting execution: %v", err)
	}
	if got.Status != "COMPLETED" {
		t.Errorf("status: want COMPLETED, got %q", got.Status)
	}
	if got.CostUSD != 0.42 {
		t.Errorf("cost: want 0.42, got %f", got.CostUSD)
	}
	if got.ExitCode != 0 {
		t.Errorf("exit code: want 0, got %d", got.ExitCode)
	}
}

func TestListExecutions(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := &task.Task{
		ID: "ltask", Name: "L", Agent: task.AgentConfig{Type: "claude", Instructions: "x"},
		Priority: task.PriorityNormal, Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags: []string{}, DependsOn: []string{},
		State: task.StatePending, CreatedAt: now, UpdatedAt: now,
	}
	db.CreateTask(tk)

	for i := 0; i < 3; i++ {
		db.CreateExecution(&Execution{
			ID: fmt.Sprintf("le-%d", i), TaskID: "ltask",
			StartTime: now.Add(time.Duration(i) * time.Minute), EndTime: now.Add(time.Duration(i+1) * time.Minute),
			Status: "COMPLETED",
		})
	}

	execs, err := db.ListExecutions("ltask")
	if err != nil {
		t.Fatal(err)
	}
	if len(execs) != 3 {
		t.Errorf("want 3, got %d", len(execs))
	}
}

func TestDB_UpdateTask(t *testing.T) {
	t.Run("happy path", func(t *testing.T) {
		db := testDB(t)
		now := time.Now().UTC().Truncate(time.Second)

		tk := &task.Task{
			ID:          "upd-1",
			Name:        "Original Name",
			Description: "original desc",
			Agent:      task.AgentConfig{Type: "claude", Model: "sonnet", Instructions: "original"},
			Priority:    task.PriorityNormal,
			Retry:       task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
			Tags:        []string{"old"},
			DependsOn:   []string{},
			State:       task.StateCompleted,
			CreatedAt:   now,
			UpdatedAt:   now,
		}
		tk.Timeout.Duration = 5 * time.Minute
		if err := db.CreateTask(tk); err != nil {
			t.Fatalf("creating task: %v", err)
		}

		u := TaskUpdate{
			Name:        "Updated Name",
			Description: "updated desc",
			Config:      task.AgentConfig{Type: "claude", Model: "opus", Instructions: "updated"},
			Priority:    task.PriorityHigh,
			TimeoutNS:   int64(15 * time.Minute),
			Retry:       task.RetryConfig{MaxAttempts: 3, Backoff: "exponential"},
			Tags:        []string{"new", "tag"},
			DependsOn:   []string{"dep-1"},
		}
		if err := db.UpdateTask("upd-1", u); err != nil {
			t.Fatalf("UpdateTask: %v", err)
		}

		got, err := db.GetTask("upd-1")
		if err != nil {
			t.Fatalf("GetTask: %v", err)
		}
		if got.Name != "Updated Name" {
			t.Errorf("name: want 'Updated Name', got %q", got.Name)
		}
		if got.Description != "updated desc" {
			t.Errorf("description: want 'updated desc', got %q", got.Description)
		}
		if got.Agent.Model != "opus" {
			t.Errorf("model: want 'opus', got %q", got.Agent.Model)
		}
		if got.Priority != task.PriorityHigh {
			t.Errorf("priority: want 'high', got %q", got.Priority)
		}
		if got.Timeout.Duration != 15*time.Minute {
			t.Errorf("timeout: want 15m, got %v", got.Timeout.Duration)
		}
		if got.Retry.MaxAttempts != 3 {
			t.Errorf("retry.max_attempts: want 3, got %d", got.Retry.MaxAttempts)
		}
		if got.Retry.Backoff != "exponential" {
			t.Errorf("retry.backoff: want 'exponential', got %q", got.Retry.Backoff)
		}
		if len(got.Tags) != 2 || got.Tags[0] != "new" || got.Tags[1] != "tag" {
			t.Errorf("tags: want [new tag], got %v", got.Tags)
		}
		if len(got.DependsOn) != 1 || got.DependsOn[0] != "dep-1" {
			t.Errorf("depends_on: want [dep-1], got %v", got.DependsOn)
		}
		if got.State != task.StatePending {
			t.Errorf("state: want PENDING after update, got %v", got.State)
		}
		// id and created_at must be unchanged
		if got.ID != "upd-1" {
			t.Errorf("id changed: got %q", got.ID)
		}
		if !got.CreatedAt.Equal(now) {
			t.Errorf("created_at changed: want %v, got %v", now, got.CreatedAt)
		}
	})

	t.Run("not found", func(t *testing.T) {
		db := testDB(t)
		err := db.UpdateTask("nonexistent", TaskUpdate{Name: "x"})
		if err == nil {
			t.Fatal("expected error for nonexistent task, got nil")
		}
	})
}

func TestRejectTask(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := &task.Task{
		ID: "reject-1", Name: "R", Agent: task.AgentConfig{Type: "claude", Instructions: "x"},
		Priority: task.PriorityNormal, Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags: []string{}, DependsOn: []string{},
		State: task.StateReady, CreatedAt: now, UpdatedAt: now,
	}
	if err := db.CreateTask(tk); err != nil {
		t.Fatal(err)
	}

	if err := db.RejectTask("reject-1", "needs more detail"); err != nil {
		t.Fatalf("RejectTask: %v", err)
	}

	got, err := db.GetTask("reject-1")
	if err != nil {
		t.Fatalf("GetTask: %v", err)
	}
	if got.State != task.StatePending {
		t.Errorf("state: want PENDING, got %v", got.State)
	}
	if got.RejectionComment != "needs more detail" {
		t.Errorf("rejection_comment: want 'needs more detail', got %q", got.RejectionComment)
	}
}

func TestRejectTask_NotFound(t *testing.T) {
	db := testDB(t)
	err := db.RejectTask("nonexistent", "comment")
	if err == nil {
		t.Fatal("expected error for nonexistent task")
	}
}

func TestUpdateExecution(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := &task.Task{
		ID: "utask", Name: "U", Agent: task.AgentConfig{Type: "claude", Instructions: "x"},
		Priority: task.PriorityNormal, Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags: []string{}, DependsOn: []string{},
		State: task.StatePending, CreatedAt: now, UpdatedAt: now,
	}
	db.CreateTask(tk)

	exec := &Execution{
		ID: "ue-1", TaskID: "utask", StartTime: now, EndTime: now, Status: "RUNNING",
	}
	db.CreateExecution(exec)

	exec.Status = "FAILED"
	exec.ExitCode = 1
	exec.ErrorMsg = "something broke"
	exec.EndTime = now.Add(2 * time.Minute)
	exec.StdoutPath = "/tmp/exec/stdout.log"
	exec.StderrPath = "/tmp/exec/stderr.log"
	exec.ArtifactDir = "/tmp/exec"
	if err := db.UpdateExecution(exec); err != nil {
		t.Fatal(err)
	}

	got, _ := db.GetExecution("ue-1")
	if got.Status != "FAILED" {
		t.Errorf("status: want FAILED, got %q", got.Status)
	}
	if got.ErrorMsg != "something broke" {
		t.Errorf("error: want 'something broke', got %q", got.ErrorMsg)
	}
	if got.StdoutPath != "/tmp/exec/stdout.log" {
		t.Errorf("stdout_path: want /tmp/exec/stdout.log, got %q", got.StdoutPath)
	}
	if got.StderrPath != "/tmp/exec/stderr.log" {
		t.Errorf("stderr_path: want /tmp/exec/stderr.log, got %q", got.StderrPath)
	}
	if got.ArtifactDir != "/tmp/exec" {
		t.Errorf("artifact_dir: want /tmp/exec, got %q", got.ArtifactDir)
	}
}

func makeTestTask(id string, now time.Time) *task.Task {
	return &task.Task{
		ID: id, Name: "T-" + id, Agent: task.AgentConfig{Type: "claude", Instructions: "x"},
		Priority: task.PriorityNormal, Retry: task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		Tags: []string{}, DependsOn: []string{},
		State: task.StatePending, CreatedAt: now, UpdatedAt: now,
	}
}

func TestStorage_SessionID_RoundTrip(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	db.CreateTask(makeTestTask("sid-task", now))

	exec := &Execution{
		ID: "sid-exec", TaskID: "sid-task", StartTime: now, Status: "RUNNING",
		SessionID: "550e8400-e29b-41d4-a716-446655440000",
	}
	if err := db.CreateExecution(exec); err != nil {
		t.Fatalf("create: %v", err)
	}

	got, err := db.GetExecution("sid-exec")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if got.SessionID != exec.SessionID {
		t.Errorf("session_id: want %q, got %q", exec.SessionID, got.SessionID)
	}
}

func TestStorage_UpdateTaskQuestion(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := makeTestTask("q-task", now)
	db.CreateTask(tk)

	q := `{"text":"Which branch?","options":["main","develop"]}`
	if err := db.UpdateTaskQuestion("q-task", q); err != nil {
		t.Fatalf("update question: %v", err)
	}

	got, err := db.GetTask("q-task")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if got.QuestionJSON != q {
		t.Errorf("question_json: want %q, got %q", q, got.QuestionJSON)
	}
}

func TestDeleteTask_RemovesTaskAndExecutions(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	db.CreateTask(makeTestTask("del-task", now))
	db.CreateExecution(&Execution{ID: "del-exec-1", TaskID: "del-task", StartTime: now, Status: "COMPLETED"})
	db.CreateExecution(&Execution{ID: "del-exec-2", TaskID: "del-task", StartTime: now.Add(time.Minute), Status: "COMPLETED"})

	if err := db.DeleteTask("del-task"); err != nil {
		t.Fatalf("DeleteTask: %v", err)
	}

	_, err := db.GetTask("del-task")
	if err == nil {
		t.Error("expected error getting deleted task, got nil")
	}

	execs, err := db.ListExecutions("del-task")
	if err != nil {
		t.Fatalf("ListExecutions: %v", err)
	}
	if len(execs) != 0 {
		t.Errorf("want 0 executions after delete, got %d", len(execs))
	}
}

func TestDeleteTask_CascadesSubtasks(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()

	parent := makeTestTask("parent-del", now)
	child := makeTestTask("child-del", now)
	child.ParentTaskID = "parent-del"

	db.CreateTask(parent)
	db.CreateTask(child)

	if err := db.DeleteTask("parent-del"); err != nil {
		t.Fatalf("DeleteTask: %v", err)
	}

	_, err := db.GetTask("parent-del")
	if err == nil {
		t.Error("parent should be deleted")
	}
	_, err = db.GetTask("child-del")
	if err == nil {
		t.Error("child should be deleted when parent is deleted")
	}
}

func TestDeleteTask_NotFound(t *testing.T) {
	db := testDB(t)
	err := db.DeleteTask("nonexistent")
	if err == nil {
		t.Fatal("expected error for nonexistent task, got nil")
	}
}

func TestDeleteTask_DeepSubtaskCascadeAtomic(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()

	// 3-level hierarchy: root -> child -> grandchild
	root := makeTestTask("deep-root", now)
	child := makeTestTask("deep-child", now)
	child.ParentTaskID = "deep-root"
	grandchild := makeTestTask("deep-grandchild", now)
	grandchild.ParentTaskID = "deep-child"

	for _, tk := range []*task.Task{root, child, grandchild} {
		if err := db.CreateTask(tk); err != nil {
			t.Fatalf("creating task %q: %v", tk.ID, err)
		}
	}

	// Add one execution per level.
	for i, tid := range []string{"deep-root", "deep-child", "deep-grandchild"} {
		e := &Execution{
			ID:        fmt.Sprintf("deep-exec-%d", i),
			TaskID:    tid,
			StartTime: now,
			Status:    "COMPLETED",
		}
		if err := db.CreateExecution(e); err != nil {
			t.Fatalf("creating execution for %q: %v", tid, err)
		}
	}

	if err := db.DeleteTask("deep-root"); err != nil {
		t.Fatalf("DeleteTask: %v", err)
	}

	// All three tasks must be gone.
	for _, tid := range []string{"deep-root", "deep-child", "deep-grandchild"} {
		_, err := db.GetTask(tid)
		if err == nil {
			t.Errorf("task %q should have been deleted", tid)
		}
	}

	// No executions should remain for any deleted task (no orphans).
	rows, err := db.db.Query(`
		SELECT e.id FROM executions e
		LEFT JOIN tasks t ON e.task_id = t.id
		WHERE t.id IS NULL`)
	if err != nil {
		t.Fatalf("orphan check query: %v", err)
	}
	defer rows.Close()
	var orphans []string
	for rows.Next() {
		var eid string
		if err := rows.Scan(&eid); err != nil {
			t.Fatal(err)
		}
		orphans = append(orphans, eid)
	}
	if len(orphans) != 0 {
		t.Errorf("orphaned execution rows after DeleteTask: %v", orphans)
	}
}

// TestResetTaskForRetry_ClearsQuestionJSON verifies that restarting a BLOCKED
// or FAILED task via ResetTaskForRetry clears any stale question so the frontend
// does not show a stale "waiting for input" prompt.
func TestResetTaskForRetry_ClearsQuestionJSON(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	tk := makeTestTask("retry-task", now)
	tk.State = task.StatePending
	db.CreateTask(tk)

	// Transition to BLOCKED with a question.
	db.UpdateTaskState("retry-task", task.StateQueued)
	db.UpdateTaskState("retry-task", task.StateRunning)
	db.UpdateTaskState("retry-task", task.StateBlocked)
	db.UpdateTaskQuestion("retry-task", `{"question":"which branch?"}`)

	// Simulate the task failing and being restarted.
	db.UpdateTaskState("retry-task", task.StateFailed)

	if _, err := db.ResetTaskForRetry("retry-task"); err != nil {
		t.Fatalf("ResetTaskForRetry: %v", err)
	}

	got, err := db.GetTask("retry-task")
	if err != nil {
		t.Fatalf("GetTask: %v", err)
	}
	if got.QuestionJSON != "" {
		t.Errorf("question_json should be cleared after reset, got %q", got.QuestionJSON)
	}
	if got.State != task.StateQueued {
		t.Errorf("state should be QUEUED, got %q", got.State)
	}
}

func TestStorage_GetLatestExecution(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC()
	db.CreateTask(makeTestTask("le-task", now))

	db.CreateExecution(&Execution{ID: "le-1", TaskID: "le-task", StartTime: now, Status: "COMPLETED"})
	db.CreateExecution(&Execution{ID: "le-2", TaskID: "le-task", StartTime: now.Add(time.Minute), Status: "RUNNING"})

	got, err := db.GetLatestExecution("le-task")
	if err != nil {
		t.Fatalf("get latest: %v", err)
	}
	if got.ID != "le-2" {
		t.Errorf("want le-2, got %q", got.ID)
	}
}

func TestListRecentExecutions_LargeDataset(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)

	// Create two tasks so we can also test the taskID filter.
	taskA := makeTestTask("re-task-a", now)
	taskB := makeTestTask("re-task-b", now)
	db.CreateTask(taskA)
	db.CreateTask(taskB)

	// Insert 100 executions spread across the two tasks, 1 minute apart.
	for i := 0; i < 100; i++ {
		tid := "re-task-a"
		if i%2 == 0 {
			tid = "re-task-b"
		}
		start := now.Add(time.Duration(i) * time.Minute)
		db.CreateExecution(&Execution{
			ID:        fmt.Sprintf("re-exec-%03d", i),
			TaskID:    tid,
			StartTime: start,
			EndTime:   start.Add(30 * time.Second),
			Status:    "COMPLETED",
			CostUSD:   float64(i) * 0.01,
		})
	}

	t.Run("returns all executions since epoch", func(t *testing.T) {
		results, err := db.ListRecentExecutions(now.Add(-time.Hour), 200, "")
		if err != nil {
			t.Fatalf("ListRecentExecutions: %v", err)
		}
		if len(results) != 100 {
			t.Errorf("want 100, got %d", len(results))
		}
		// Verify descending order by start_time.
		for i := 1; i < len(results); i++ {
			if results[i-1].StartedAt.Before(results[i].StartedAt) {
				t.Errorf("results not in descending order at index %d/%d", i-1, i)
				break
			}
		}
	})

	t.Run("respects limit", func(t *testing.T) {
		results, err := db.ListRecentExecutions(now.Add(-time.Hour), 10, "")
		if err != nil {
			t.Fatalf("ListRecentExecutions: %v", err)
		}
		if len(results) != 10 {
			t.Errorf("want 10, got %d", len(results))
		}
	})

	t.Run("filters by since time", func(t *testing.T) {
		// Only executions starting at index 50+ (minute 50 onward).
		since := now.Add(50 * time.Minute)
		results, err := db.ListRecentExecutions(since, 200, "")
		if err != nil {
			t.Fatalf("ListRecentExecutions: %v", err)
		}
		if len(results) != 50 {
			t.Errorf("want 50 (indices 50–99), got %d", len(results))
		}
		for _, r := range results {
			if r.StartedAt.Before(since) {
				t.Errorf("result %q has start_time %v before since %v", r.ID, r.StartedAt, since)
			}
		}
	})

	t.Run("filters by task_id", func(t *testing.T) {
		// task-a gets odd indices (1,3,5,...,99) = 50 executions.
		results, err := db.ListRecentExecutions(now.Add(-time.Hour), 200, "re-task-a")
		if err != nil {
			t.Fatalf("ListRecentExecutions: %v", err)
		}
		if len(results) != 50 {
			t.Errorf("want 50 for task-a, got %d", len(results))
		}
		for _, r := range results {
			if r.TaskID != "re-task-a" {
				t.Errorf("unexpected task_id %q in results", r.TaskID)
			}
		}
	})
}

func TestUpdateTaskSummary(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)
	tk := &task.Task{
		ID:        "sum-task",
		Name:      "Summary Task",
		Agent:     task.AgentConfig{Type: "claude", Instructions: "do it"},
		Priority:  task.PriorityNormal,
		Tags:      []string{},
		DependsOn: []string{},
		Retry:     task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if err := db.CreateTask(tk); err != nil {
		t.Fatalf("creating task: %v", err)
	}

	// Initially summary should be empty.
	got, err := db.GetTask("sum-task")
	if err != nil {
		t.Fatalf("getting task: %v", err)
	}
	if got.Summary != "" {
		t.Errorf("initial summary: want empty, got %q", got.Summary)
	}

	// Set summary.
	if err := db.UpdateTaskSummary("sum-task", "All done successfully."); err != nil {
		t.Fatalf("UpdateTaskSummary: %v", err)
	}

	got, err = db.GetTask("sum-task")
	if err != nil {
		t.Fatalf("getting task after summary update: %v", err)
	}
	if got.Summary != "All done successfully." {
		t.Errorf("summary: want 'All done successfully.', got %q", got.Summary)
	}
}

func TestAppendTaskInteraction(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)
	tk := &task.Task{
		ID:        "qa-task",
		Name:      "QA Task",
		Agent:     task.AgentConfig{Type: "claude", Instructions: "do it"},
		Priority:  task.PriorityNormal,
		Tags:      []string{},
		DependsOn: []string{},
		Retry:     task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if err := db.CreateTask(tk); err != nil {
		t.Fatalf("creating task: %v", err)
	}

	// Initially interactions should be empty.
	got, err := db.GetTask("qa-task")
	if err != nil {
		t.Fatalf("getting task: %v", err)
	}
	if len(got.Interactions) != 0 {
		t.Errorf("initial interactions: want 0, got %d", len(got.Interactions))
	}

	// Append first interaction.
	i1 := task.Interaction{
		QuestionText: "Which color?",
		Options:      []string{"red", "blue"},
		Answer:       "blue",
		AskedAt:      now,
	}
	if err := db.AppendTaskInteraction("qa-task", i1); err != nil {
		t.Fatalf("AppendTaskInteraction #1: %v", err)
	}

	got, err = db.GetTask("qa-task")
	if err != nil {
		t.Fatalf("getting task after first append: %v", err)
	}
	if len(got.Interactions) != 1 {
		t.Fatalf("interactions count: want 1, got %d", len(got.Interactions))
	}
	if got.Interactions[0].QuestionText != "Which color?" {
		t.Errorf("question: want 'Which color?', got %q", got.Interactions[0].QuestionText)
	}
	if got.Interactions[0].Answer != "blue" {
		t.Errorf("answer: want 'blue', got %q", got.Interactions[0].Answer)
	}

	// Append second interaction — verify it accumulates.
	i2 := task.Interaction{
		QuestionText: "Which size?",
		Answer:       "large",
		AskedAt:      now.Add(time.Minute),
	}
	if err := db.AppendTaskInteraction("qa-task", i2); err != nil {
		t.Fatalf("AppendTaskInteraction #2: %v", err)
	}

	got, err = db.GetTask("qa-task")
	if err != nil {
		t.Fatalf("getting task after second append: %v", err)
	}
	if len(got.Interactions) != 2 {
		t.Fatalf("interactions count: want 2, got %d", len(got.Interactions))
	}
	if got.Interactions[1].QuestionText != "Which size?" {
		t.Errorf("second question: want 'Which size?', got %q", got.Interactions[1].QuestionText)
	}
}

func TestAppendTaskInteraction_NotFound(t *testing.T) {
	db := testDB(t)
	err := db.AppendTaskInteraction("nonexistent", task.Interaction{QuestionText: "hi"})
	if err == nil {
		t.Error("expected error for nonexistent task")
	}
}

func TestCreateTask_Project_RoundTrip(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)

	tk := &task.Task{
		ID:        "proj-1",
		Name:      "Project Task",
		Project:   "my-project",
		Agent:     task.AgentConfig{Type: "claude", Instructions: "do it"},
		Priority:  task.PriorityNormal,
		Tags:      []string{},
		DependsOn: []string{},
		Retry:     task.RetryConfig{MaxAttempts: 1, Backoff: "linear"},
		State:     task.StatePending,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if err := db.CreateTask(tk); err != nil {
		t.Fatalf("creating task: %v", err)
	}

	got, err := db.GetTask("proj-1")
	if err != nil {
		t.Fatalf("getting task: %v", err)
	}
	if got.Project != "my-project" {
		t.Errorf("project: want %q, got %q", "my-project", got.Project)
	}
}

func TestExecution_StoreAndRetrieveChangestats(t *testing.T) {
	db := testDB(t)
	now := time.Now().UTC().Truncate(time.Second)
	db.CreateTask(makeTestTask("cs-task", now))

	exec := &Execution{
		ID:        "cs-exec",
		TaskID:    "cs-task",
		StartTime: now,
		Status:    "COMPLETED",
	}
	if err := db.CreateExecution(exec); err != nil {
		t.Fatalf("CreateExecution: %v", err)
	}

	stats := &task.Changestats{
		FilesChanged: 5,
		LinesAdded:   127,
		LinesRemoved: 43,
	}
	if err := db.UpdateExecutionChangestats("cs-exec", stats); err != nil {
		t.Fatalf("UpdateExecutionChangestats: %v", err)
	}

	got, err := db.GetExecution("cs-exec")
	if err != nil {
		t.Fatalf("GetExecution: %v", err)
	}
	if got.Changestats == nil {
		t.Fatal("Changestats: want non-nil, got nil")
	}
	if got.Changestats.FilesChanged != 5 {
		t.Errorf("FilesChanged: want 5, got %d", got.Changestats.FilesChanged)
	}
	if got.Changestats.LinesAdded != 127 {
		t.Errorf("LinesAdded: want 127, got %d", got.Changestats.LinesAdded)
	}
	if got.Changestats.LinesRemoved != 43 {
		t.Errorf("LinesRemoved: want 43, got %d", got.Changestats.LinesRemoved)
	}
}