package store_test

import (
	"context"
	"encoding/json"
	"reflect"
	"sync"
	"testing"
	"time"

	"github.com/aoagents/agent-orchestrator/backend/internal/domain"
	"github.com/aoagents/agent-orchestrator/backend/internal/ports"
	"github.com/aoagents/agent-orchestrator/backend/internal/storage/sqlite"
)

func newTestStore(t *testing.T) *sqlite.Store {
	t.Helper()
	s, err := sqlite.Open(t.TempDir())
	if err != nil {
		t.Fatalf("open: %v", err)
	}
	t.Cleanup(func() { _ = s.Close() })
	return s
}

func seedProject(t *testing.T, s *sqlite.Store, id string) {
	t.Helper()
	if err := s.UpsertProject(context.Background(), domain.ProjectRecord{
		ID: id, Path: "/tmp/" + id, RegisteredAt: time.Now().UTC().Truncate(time.Second),
	}); err != nil {
		t.Fatalf("seed project %s: %v", id, err)
	}
}

func sampleRecord(project string) domain.SessionRecord {
	now := time.Now().UTC().Truncate(time.Second)
	return domain.SessionRecord{
		ProjectID: domain.ProjectID(project),
		Kind:      domain.KindWorker,
		Harness:   domain.HarnessClaudeCode,
		Activity:  domain.Activity{State: domain.ActivityActive, LastActivityAt: now},
		Metadata:  domain.SessionMetadata{Branch: "feat/x", WorkspacePath: "/ws"},
		CreatedAt: now,
		UpdatedAt: now,
	}
}

func TestProjectCRUDAndArchive(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")

	got, ok, err := s.GetProject(ctx, "mer")
	if err != nil || !ok {
		t.Fatalf("get: ok=%v err=%v", ok, err)
	}
	if got.ID != "mer" || got.Path != "/tmp/mer" {
		t.Fatalf("project = %+v", got)
	}
	if list, _ := s.ListProjects(ctx); len(list) != 1 {
		t.Fatalf("active list = %d, want 1", len(list))
	}
	// archive hides from the active list but still resolves by id.
	if ok, err := s.ArchiveProject(ctx, "mer", time.Now().UTC()); err != nil || !ok {
		t.Fatalf("archive: ok=%v err=%v", ok, err)
	}
	if list, _ := s.ListProjects(ctx); len(list) != 0 {
		t.Fatalf("after archive, active list = %d, want 0", len(list))
	}
	if _, ok, _ := s.GetProject(ctx, "mer"); !ok {
		t.Fatal("archived project must still resolve by id")
	}
}

func TestProjectConfigRoundTrips(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Second)

	// A config with mixed field kinds (scalar, map, list, nested) survives the
	// JSON round trip.
	cfg := domain.ProjectConfig{
		DefaultBranch: "develop",
		Env:           map[string]string{"FOO": "bar"},
		Symlinks:      []string{".env"},
		PostCreate:    []string{"echo hi"},
		AgentConfig:   domain.AgentConfig{Model: "claude-opus-4-5", Permissions: domain.PermissionModeAcceptEdits},
		Worker:        domain.RoleOverride{Harness: domain.HarnessCodex},
	}
	if err := s.UpsertProject(ctx, domain.ProjectRecord{
		ID: "cfg", Path: "/tmp/cfg", RegisteredAt: now, Config: cfg,
	}); err != nil {
		t.Fatalf("upsert with config: %v", err)
	}
	got, ok, err := s.GetProject(ctx, "cfg")
	if err != nil || !ok {
		t.Fatalf("get: ok=%v err=%v", ok, err)
	}
	if !reflect.DeepEqual(got.Config, cfg) {
		t.Fatalf("config = %#v, want %#v", got.Config, cfg)
	}

	// An unset config round-trips back to a zero value rather than an empty object.
	seedProject(t, s, "nocfg")
	got, _, _ = s.GetProject(ctx, "nocfg")
	if !got.Config.IsZero() {
		t.Fatalf("unset config = %#v, want zero", got.Config)
	}

	// Clearing replaces a previously-set config with a zero value.
	if err := s.UpsertProject(ctx, domain.ProjectRecord{
		ID: "cfg", Path: "/tmp/cfg", RegisteredAt: now, Config: domain.ProjectConfig{},
	}); err != nil {
		t.Fatalf("clear config: %v", err)
	}
	if got, _, _ := s.GetProject(ctx, "cfg"); !got.Config.IsZero() {
		t.Fatalf("cleared config = %#v, want zero", got.Config)
	}
}

func TestSessionCreateAssignsPerProjectID(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	seedProject(t, s, "ao")

	r1, err := s.CreateSession(ctx, sampleRecord("mer"))
	if err != nil {
		t.Fatal(err)
	}
	r2, _ := s.CreateSession(ctx, sampleRecord("mer"))
	r3, _ := s.CreateSession(ctx, sampleRecord("ao"))
	if r1.ID != "mer-1" || r2.ID != "mer-2" || r3.ID != "ao-1" {
		t.Fatalf("ids = %s, %s, %s; want mer-1, mer-2, ao-1", r1.ID, r2.ID, r3.ID)
	}
	got, ok, err := s.GetSession(ctx, "mer-1")
	if err != nil || !ok {
		t.Fatalf("get: ok=%v err=%v", ok, err)
	}
	if got.Activity.State != domain.ActivityActive || got.IsTerminated ||
		got.Harness != domain.HarnessClaudeCode || got.Metadata.Branch != "feat/x" {
		t.Fatalf("round-trip mismatch: %+v", got)
	}
	if list, _ := s.ListSessions(ctx, "mer"); len(list) != 2 {
		t.Fatalf("list mer = %d, want 2", len(list))
	}
	if all, _ := s.ListAllSessions(ctx); len(all) != 3 {
		t.Fatalf("list all = %d, want 3", len(all))
	}
}

// TestDeleteSessionOnlyRemovesSeedRows covers Bug 4's storage-layer guarantee:
// DeleteSession removes a session row only when the row is still in seed state
// (no workspace, no runtime handle, no agent session id, no prompt, not
// terminated). Rows that already carry spawn output are immutable so the
// no-resurrection guarantee for live sessions still holds.
func TestDeleteSessionOnlyRemovesSeedRows(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")

	// Seed row: just CreateSession output, no metadata yet.
	now := time.Now().UTC().Truncate(time.Second)
	seed := domain.SessionRecord{
		ProjectID: "mer",
		Kind:      domain.KindWorker,
		Harness:   domain.HarnessClaudeCode,
		Activity:  domain.Activity{State: domain.ActivityIdle, LastActivityAt: now},
		CreatedAt: now,
		UpdatedAt: now,
	}
	r1, err := s.CreateSession(ctx, seed)
	if err != nil {
		t.Fatalf("create seed: %v", err)
	}

	deleted, err := s.DeleteSession(ctx, r1.ID)
	if err != nil || !deleted {
		t.Fatalf("delete seed = %v %v, want true nil", deleted, err)
	}
	if _, ok, _ := s.GetSession(ctx, r1.ID); ok {
		t.Fatal("seed row still present after DeleteSession")
	}

	// A row with workspace_path populated must NOT be deleted — even if
	// !is_terminated. This is the no-resurrection guarantee for live work.
	r2, err := s.CreateSession(ctx, sampleRecord("mer"))
	if err != nil {
		t.Fatalf("create live: %v", err)
	}
	deleted, err = s.DeleteSession(ctx, r2.ID)
	if err != nil {
		t.Fatalf("delete live err = %v", err)
	}
	if deleted {
		t.Fatal("DeleteSession must be a no-op for rows with spawn output")
	}
	if _, ok, _ := s.GetSession(ctx, r2.ID); !ok {
		t.Fatal("live row was removed by DeleteSession")
	}

	// A terminated row is also out of scope: terminal-state rows hold cleanup
	// metadata users may still inspect, so the gate refuses them too.
	r3, err := s.CreateSession(ctx, seed)
	if err != nil {
		t.Fatalf("create extra seed: %v", err)
	}
	terminated := r3
	terminated.IsTerminated = true
	if err := s.UpdateSession(ctx, terminated); err != nil {
		t.Fatalf("mark terminated: %v", err)
	}
	deleted, err = s.DeleteSession(ctx, r3.ID)
	if err != nil {
		t.Fatalf("delete terminated err = %v", err)
	}
	if deleted {
		t.Fatal("DeleteSession must be a no-op for terminated rows")
	}
}

func TestSessionRenameUpdatesDisplayName(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))

	renamedAt := r.UpdatedAt.Add(time.Minute)
	ok, err := s.RenameSession(ctx, r.ID, "Fix flaky tests", renamedAt)
	if err != nil || !ok {
		t.Fatalf("rename: ok=%v err=%v", ok, err)
	}
	got, _, _ := s.GetSession(ctx, r.ID)
	if got.DisplayName != "Fix flaky tests" || !got.UpdatedAt.Equal(renamedAt) {
		t.Fatalf("rename not persisted: %+v", got)
	}

	ok, err = s.RenameSession(ctx, "mer-missing", "Missing", renamedAt)
	if err != nil {
		t.Fatalf("rename missing: %v", err)
	}
	if ok {
		t.Fatal("rename missing ok=true, want false")
	}
}

func TestSessionUpdateActivityAndTermination(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))

	r.Activity = domain.Activity{State: domain.ActivityWaitingInput, LastActivityAt: r.CreatedAt}
	r.IsTerminated = true
	if err := s.UpdateSession(ctx, r); err != nil {
		t.Fatal(err)
	}
	got, _, _ := s.GetSession(ctx, r.ID)
	if got.Activity.State != domain.ActivityWaitingInput || !got.IsTerminated {
		t.Fatalf("update not persisted: %+v", got)
	}

	got.IsTerminated = false
	got.Activity.State = domain.ActivityActive
	_ = s.UpdateSession(ctx, got)
	again, _, _ := s.GetSession(ctx, r.ID)
	if again.IsTerminated || again.Activity.State != domain.ActivityActive {
		t.Fatalf("activity/termination should update, got %+v", again)
	}
}

func TestSessionFirstSignalRoundTrip(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))

	// Fresh sessions have no signal receipt: NULL round-trips as zero time.
	got, _, _ := s.GetSession(ctx, r.ID)
	if !got.FirstSignalAt.IsZero() {
		t.Fatalf("fresh session has receipt: %v", got.FirstSignalAt)
	}

	stamp := time.Now().UTC().Truncate(time.Second)
	got.FirstSignalAt = stamp
	if err := s.UpdateSession(ctx, got); err != nil {
		t.Fatal(err)
	}
	again, _, _ := s.GetSession(ctx, r.ID)
	if !again.FirstSignalAt.Equal(stamp) {
		t.Fatalf("receipt not persisted: got %v want %v", again.FirstSignalAt, stamp)
	}

	// Clearing it (spawn/restore re-proves the hook pipeline) round-trips too.
	again.FirstSignalAt = time.Time{}
	if err := s.UpdateSession(ctx, again); err != nil {
		t.Fatal(err)
	}
	final, _, _ := s.GetSession(ctx, r.ID)
	if !final.FirstSignalAt.IsZero() {
		t.Fatalf("receipt not cleared: %v", final.FirstSignalAt)
	}
}

func TestPRCRUD(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)

	pr := domain.PullRequest{
		URL: "https://gh/pr/1", SessionID: r.ID, Number: 1,
		Review: domain.ReviewRequired, CI: domain.CIFailing, Mergeability: domain.MergeBlocked, UpdatedAt: now,
	}
	if err := s.WritePR(ctx, pr, nil, nil); err != nil {
		t.Fatal(err)
	}
	got, ok, err := s.GetPR(ctx, pr.URL)
	if err != nil || !ok || got != pr {
		t.Fatalf("get pr: ok=%v err=%v got=%+v", ok, err, got)
	}
	if list, _ := s.ListPRsBySession(ctx, r.ID); len(list) != 1 {
		t.Fatalf("list prs = %d, want 1", len(list))
	}
}

func TestWritePRRejectsSessionReassignment(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	first, _ := s.CreateSession(ctx, sampleRecord("mer"))
	second, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)

	pr := domain.PullRequest{URL: "https://gh/pr/1", SessionID: first.ID, Number: 1, UpdatedAt: now}
	if err := s.WritePR(ctx, pr, nil, nil); err != nil {
		t.Fatal(err)
	}
	pr.SessionID = second.ID
	if err := s.WritePR(ctx, pr, nil, nil); err == nil {
		t.Fatal("expected reassignment to fail")
	}
	got, ok, err := s.GetPR(ctx, pr.URL)
	if err != nil || !ok {
		t.Fatalf("get pr: ok=%v err=%v", ok, err)
	}
	if got.SessionID != first.ID {
		t.Fatalf("pr moved to %s, want %s", got.SessionID, first.ID)
	}
}

func TestDisplayPRFactsPrefersActivePR(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)

	if err := s.WritePR(ctx, domain.PullRequest{URL: "closed", SessionID: r.ID, Number: 1, Closed: true, UpdatedAt: now.Add(time.Minute)}, nil, nil); err != nil {
		t.Fatal(err)
	}
	if err := s.WritePR(ctx, domain.PullRequest{URL: "open", SessionID: r.ID, Number: 2, CI: domain.CIFailing, UpdatedAt: now}, nil, nil); err != nil {
		t.Fatal(err)
	}
	got, ok, err := s.GetDisplayPRFactsForSession(ctx, r.ID)
	if err != nil || !ok {
		t.Fatalf("display pr: ok=%v err=%v", ok, err)
	}
	if got.URL != "open" || got.CI != domain.CIFailing {
		t.Fatalf("display pr = %+v", got)
	}
}

func TestPRCommentsReplace(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)
	_ = s.WritePR(ctx, domain.PullRequest{URL: "pr1", SessionID: r.ID, UpdatedAt: now}, nil, []domain.PullRequestComment{
		{ID: "c1", Author: "a", File: "a.go", Line: 1, Body: "nit", CreatedAt: now},
		{ID: "c2", Author: "b", File: "b.go", Line: 2, Body: "bug", Resolved: true, CreatedAt: now.Add(time.Second)},
	})
	if list, _ := s.ListPRComments(ctx, "pr1"); len(list) != 2 {
		t.Fatalf("comments = %d, want 2", len(list))
	}
	// replace with a smaller set drops the rest.
	_ = s.WritePR(ctx, domain.PullRequest{URL: "pr1", SessionID: r.ID, UpdatedAt: now}, nil, []domain.PullRequestComment{{ID: "c1", Body: "x", CreatedAt: now}})
	if list, _ := s.ListPRComments(ctx, "pr1"); len(list) != 1 {
		t.Fatalf("after replace, comments = %d, want 1", len(list))
	}
}

func TestWriteSCMObservationPersistsMetadataChecksReviewsAndComments(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)

	pr := domain.PullRequest{
		URL: "https://github.com/o/r/pull/1", SessionID: r.ID, Number: 1,
		Provider: "github", Host: "github.com", Repo: "o/r",
		SourceBranch: "feat/75", TargetBranch: "main", HeadSHA: "h1",
		Title: "SCM observer", Additions: 10, Deletions: 2, ChangedFiles: 3,
		Author: "dev", BaseSHA: "b1", MergeCommitSHA: "m1",
		ProviderState: "OPEN", ProviderMergeable: "MERGEABLE", ProviderMergeStateStatus: "CLEAN",
		HTMLURL: "https://github.com/o/r/pull/1",
		CI:      domain.CIFailing, Review: domain.ReviewChangesRequest, Mergeability: domain.MergeBlocked,
		MetadataHash: "mh", CIHash: "ch", ReviewHash: "rh",
		UpdatedAt: now, ObservedAt: now, CIObservedAt: now, ReviewObservedAt: now,
	}
	checks := []domain.PullRequestCheck{{Name: "build", CommitHash: "h1", Status: domain.PRCheckFailed, Conclusion: "failure", URL: "ci", Details: "99", LogTail: "boom", CreatedAt: now}}
	threads := []domain.PullRequestReviewThread{{ThreadID: "t1", Path: "main.go", Line: 7, SemanticHash: "th", UpdatedAt: now}}
	comments := []domain.PullRequestComment{{ThreadID: "t1", ID: "c1", Author: "reviewer", File: "main.go", Line: 7, Body: "fix", URL: "comment", CreatedAt: now}}

	if err := s.WriteSCMObservation(ctx, pr, checks, threads, comments, ports.ReviewWriteReplace); err != nil {
		t.Fatal(err)
	}
	got, ok, err := s.GetPR(ctx, pr.URL)
	if err != nil || !ok {
		t.Fatalf("get pr: ok=%v err=%v", ok, err)
	}
	if got.Provider != "github" || got.HeadSHA != "h1" || got.MetadataHash != "mh" || got.CIHash != "ch" || got.ReviewHash != "rh" {
		t.Fatalf("SCM metadata not persisted: %+v", got)
	}
	gotChecks, _ := s.ListChecks(ctx, pr.URL)
	if len(gotChecks) != 1 || gotChecks[0].Conclusion != "failure" || gotChecks[0].Details != "99" || gotChecks[0].LogTail != "boom" {
		t.Fatalf("checks not persisted: %+v", gotChecks)
	}
	gotThreads, _ := s.ListPRReviewThreads(ctx, pr.URL)
	if len(gotThreads) != 1 || gotThreads[0].ThreadID != "t1" || gotThreads[0].SemanticHash != "th" {
		t.Fatalf("threads not persisted: %+v", gotThreads)
	}
	gotComments, _ := s.ListPRComments(ctx, pr.URL)
	if len(gotComments) != 1 || gotComments[0].ThreadID != "t1" || gotComments[0].URL != "comment" {
		t.Fatalf("comments not persisted: %+v", gotComments)
	}
}

func TestWriteSCMObservationMergeUpdatesFetchedReviewWindow(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)
	pr := domain.PullRequest{URL: "https://github.com/o/r/pull/1", SessionID: r.ID, Number: 1, UpdatedAt: now}

	initialThreads := []domain.PullRequestReviewThread{
		{ThreadID: "older", Path: "old.go", Line: 1, Resolved: false, SemanticHash: "old", UpdatedAt: now},
		{ThreadID: "latest", Path: "main.go", Line: 7, Resolved: false, SemanticHash: "latest-v1", UpdatedAt: now},
	}
	initialComments := []domain.PullRequestComment{
		{ThreadID: "older", ID: "older-c1", Author: "ann", Body: "old", CreatedAt: now},
		{ThreadID: "latest", ID: "latest-c1", Author: "bob", Body: "before", CreatedAt: now},
	}
	if err := s.WriteSCMObservation(ctx, pr, nil, initialThreads, initialComments, ports.ReviewWriteReplace); err != nil {
		t.Fatal(err)
	}

	mergedThreads := []domain.PullRequestReviewThread{
		{ThreadID: "latest", Path: "main.go", Line: 8, Resolved: true, SemanticHash: "latest-v2", UpdatedAt: now.Add(time.Second)},
		{ThreadID: "new", Path: "new.go", Line: 2, Resolved: false, SemanticHash: "new", UpdatedAt: now.Add(time.Second)},
	}
	mergedComments := []domain.PullRequestComment{
		{ThreadID: "latest", ID: "latest-c2", Author: "bob", Body: "after", CreatedAt: now.Add(time.Second)},
		{ThreadID: "new", ID: "new-c1", Author: "cat", Body: "new", CreatedAt: now.Add(time.Second)},
	}
	if err := s.WriteSCMObservation(ctx, pr, nil, mergedThreads, mergedComments, ports.ReviewWriteMerge); err != nil {
		t.Fatal(err)
	}

	gotThreads, err := s.ListPRReviewThreads(ctx, pr.URL)
	if err != nil {
		t.Fatal(err)
	}
	if len(gotThreads) != 3 {
		t.Fatalf("threads = %#v, want older preserved plus latest/new", gotThreads)
	}
	byThread := map[string]domain.PullRequestReviewThread{}
	for _, th := range gotThreads {
		byThread[th.ThreadID] = th
	}
	if byThread["older"].SemanticHash != "old" {
		t.Fatalf("older thread not preserved: %#v", byThread["older"])
	}
	if byThread["latest"].SemanticHash != "latest-v2" || !byThread["latest"].Resolved || byThread["latest"].Line != 8 {
		t.Fatalf("latest thread not updated: %#v", byThread["latest"])
	}
	if byThread["new"].SemanticHash != "new" {
		t.Fatalf("new thread not inserted: %#v", byThread["new"])
	}

	gotComments, err := s.ListPRComments(ctx, pr.URL)
	if err != nil {
		t.Fatal(err)
	}
	ids := map[string]bool{}
	for _, c := range gotComments {
		ids[c.ID] = true
	}
	if !ids["older-c1"] || !ids["latest-c2"] || !ids["new-c1"] {
		t.Fatalf("comments after merge = %#v, want older preserved and fetched threads replaced", gotComments)
	}
	if ids["latest-c1"] {
		t.Fatalf("stale fetched-thread comment was preserved: %#v", gotComments)
	}
}

func TestWritePRPreservesSCMReviewThreads(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)
	observedAt := now.Add(-time.Minute)
	pr := domain.PullRequest{
		URL: "https://github.com/o/r/pull/1", SessionID: r.ID, Number: 1, UpdatedAt: now,
		Provider: "github", Host: "github.com", Repo: "o/r", HeadSHA: "head-1",
		MetadataHash: "metadata-v1", CIHash: "ci-v1", ReviewHash: "review-v1",
		ObservedAt: observedAt, CIObservedAt: observedAt, ReviewObservedAt: observedAt,
	}
	threads := []domain.PullRequestReviewThread{{ThreadID: "t1", Path: "main.go", Line: 7, SemanticHash: "thread-v1", UpdatedAt: now}}
	comments := []domain.PullRequestComment{{ThreadID: "t1", ID: "c1", Author: "reviewer", Body: "scm", URL: "https://example/comment/c1", CreatedAt: now}}

	if err := s.WriteSCMObservation(ctx, pr, nil, threads, comments, ports.ReviewWriteReplace); err != nil {
		t.Fatal(err)
	}
	legacyComments := []domain.PullRequestComment{
		{ID: "c1", Author: "legacy", Body: "duplicate legacy row must not clear thread metadata", CreatedAt: now.Add(time.Second)},
		{ID: "legacy-only", Author: "legacy", Body: "legacy", CreatedAt: now.Add(time.Second)},
	}
	if err := s.WritePR(ctx, domain.PullRequest{URL: pr.URL, SessionID: r.ID, Number: 1, CI: domain.CIPassing, UpdatedAt: now.Add(time.Second)}, nil, legacyComments); err != nil {
		t.Fatal(err)
	}

	gotPR, ok, err := s.GetPR(ctx, pr.URL)
	if err != nil || !ok {
		t.Fatalf("get pr: ok=%v err=%v", ok, err)
	}
	if gotPR.Provider != "github" || gotPR.Host != "github.com" || gotPR.Repo != "o/r" || gotPR.HeadSHA != "head-1" ||
		gotPR.MetadataHash != "metadata-v1" || gotPR.CIHash != "ci-v1" || gotPR.ReviewHash != "review-v1" {
		t.Fatalf("legacy WritePR must preserve SCM-owned metadata and hashes, got %+v", gotPR)
	}
	if !gotPR.ObservedAt.Equal(observedAt) || !gotPR.CIObservedAt.Equal(observedAt) || !gotPR.ReviewObservedAt.Equal(observedAt) {
		t.Fatalf("legacy WritePR must preserve SCM observation timestamps, got observed=%s ci=%s review=%s", gotPR.ObservedAt, gotPR.CIObservedAt, gotPR.ReviewObservedAt)
	}
	if gotPR.CI != domain.CIPassing {
		t.Fatalf("legacy WritePR should still update legacy scalar CI state, got %s", gotPR.CI)
	}
	gotThreads, err := s.ListPRReviewThreads(ctx, pr.URL)
	if err != nil {
		t.Fatal(err)
	}
	if len(gotThreads) != 1 || gotThreads[0].ThreadID != "t1" || gotThreads[0].SemanticHash != "thread-v1" {
		t.Fatalf("legacy WritePR must preserve SCM-owned review threads, got %+v", gotThreads)
	}
	gotComments, err := s.ListPRComments(ctx, pr.URL)
	if err != nil {
		t.Fatal(err)
	}
	byID := map[string]domain.PullRequestComment{}
	for _, c := range gotComments {
		byID[c.ID] = c
	}
	scmComment, ok := byID["c1"]
	if !ok || scmComment.ThreadID != "t1" || scmComment.URL != "https://example/comment/c1" || scmComment.Body != "scm" {
		t.Fatalf("legacy WritePR must not clear SCM comment metadata, got %+v", scmComment)
	}
	legacyOnly, ok := byID["legacy-only"]
	if !ok || legacyOnly.ThreadID != "" {
		t.Fatalf("legacy-only comment should remain unthreaded, got %+v", legacyOnly)
	}

	mergedThreads := []domain.PullRequestReviewThread{{ThreadID: "t1", Path: "main.go", Line: 8, Resolved: true, SemanticHash: "thread-v2", UpdatedAt: now.Add(2 * time.Second)}}
	mergedComments := []domain.PullRequestComment{{ThreadID: "t1", ID: "c2", Author: "reviewer", Body: "updated", URL: "https://example/comment/c2", CreatedAt: now.Add(2 * time.Second)}}
	if err := s.WriteSCMObservation(ctx, pr, nil, mergedThreads, mergedComments, ports.ReviewWriteMerge); err != nil {
		t.Fatal(err)
	}
	gotComments, err = s.ListPRComments(ctx, pr.URL)
	if err != nil {
		t.Fatal(err)
	}
	byID = map[string]domain.PullRequestComment{}
	for _, c := range gotComments {
		byID[c.ID] = c
	}
	if _, ok := byID["c1"]; ok {
		t.Fatalf("SCM merge should delete stale fetched-thread comment c1, comments=%+v", gotComments)
	}
	replacement, ok := byID["c2"]
	if !ok || replacement.ThreadID != "t1" {
		t.Fatalf("SCM merge did not insert replacement threaded comment, comments=%+v", gotComments)
	}
}

func TestWritePRReplacesLegacyCommentBodies(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")
	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	now := time.Now().UTC().Truncate(time.Second)
	pr := domain.PullRequest{URL: "https://github.com/o/r/pull/2", SessionID: r.ID, Number: 2, UpdatedAt: now}

	if err := s.WritePR(ctx, pr, nil, []domain.PullRequestComment{{ID: "legacy", Author: "reviewer", Body: "before", CreatedAt: now}}); err != nil {
		t.Fatal(err)
	}
	if err := s.WritePR(ctx, pr, nil, []domain.PullRequestComment{{ID: "legacy", Author: "reviewer", Body: "after edit", CreatedAt: now.Add(time.Second)}}); err != nil {
		t.Fatal(err)
	}
	got, err := s.ListPRComments(ctx, pr.URL)
	if err != nil {
		t.Fatal(err)
	}
	if len(got) != 1 || got[0].Body != "after edit" || !got[0].CreatedAt.Equal(now.Add(time.Second)) {
		t.Fatalf("legacy comment replacement did not persist edited row: %+v", got)
	}
}

func TestCDCTriggersPopulateChangeLog(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")

	r, _ := s.CreateSession(ctx, sampleRecord("mer"))
	// a real state change logs; a metadata-only change does not (WHEN guard).
	r.Activity.State = domain.ActivityIdle
	_ = s.UpdateSession(ctx, r)
	r.Metadata.Prompt = "only metadata changed"
	_ = s.UpdateSession(ctx, r)
	// a PR insert logs too.
	_ = s.WritePR(ctx, domain.PullRequest{URL: "pr1", SessionID: r.ID, UpdatedAt: r.UpdatedAt}, nil, nil)

	evs, err := s.EventsAfter(ctx, 0, 100)
	if err != nil {
		t.Fatal(err)
	}
	var types []string
	for _, e := range evs {
		if e.ProjectID != "mer" {
			t.Fatalf("event project = %s, want mer", e.ProjectID)
		}
		types = append(types, string(e.Type))
	}
	want := []string{"session_created", "session_updated", "pr_created"}
	if len(types) != 3 || types[0] != want[0] || types[1] != want[1] || types[2] != want[2] {
		t.Fatalf("change_log event types = %v, want %v (metadata-only update suppressed)", types, want)
	}
	var payload map[string]any
	if err := json.Unmarshal([]byte(evs[0].Payload), &payload); err != nil {
		t.Fatalf("session payload JSON: %v", err)
	}
	if _, ok := payload["isTerminated"].(bool); !ok {
		t.Fatalf("isTerminated payload type = %T, want bool", payload["isTerminated"])
	}
	maxSeq, _ := s.LatestSeq(ctx)
	if maxSeq != int64(len(evs)) {
		t.Fatalf("max seq = %d, want %d", maxSeq, len(evs))
	}
}

func TestConcurrentSessionCreateAssignsUniqueNums(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "mer")

	const n = 20
	var wg sync.WaitGroup
	ids := make([]string, n)
	for i := 0; i < n; i++ {
		wg.Add(1)
		go func(i int) {
			defer wg.Done()
			r, err := s.CreateSession(ctx, sampleRecord("mer"))
			if err != nil {
				t.Errorf("create: %v", err)
				return
			}
			ids[i] = string(r.ID)
		}(i)
	}
	wg.Wait()

	seen := map[string]bool{}
	for _, id := range ids {
		if id == "" || seen[id] {
			t.Fatalf("duplicate or empty id: %q in %v", id, ids)
		}
		seen[id] = true
	}
	if all, _ := s.ListAllSessions(ctx); len(all) != n {
		t.Fatalf("created %d sessions, want %d", len(all), n)
	}
}

func TestSessionWorktreesRoundTrip(t *testing.T) {
	s := newTestStore(t)
	ctx := context.Background()
	seedProject(t, s, "ws")
	rec, err := s.CreateSession(ctx, sampleRecord("ws"))
	if err != nil {
		t.Fatalf("create session: %v", err)
	}
	rows := []domain.SessionWorktreeRecord{
		{SessionID: rec.ID, RepoName: domain.RootWorkspaceRepoName, Branch: "ao/ws-1", BaseSHA: "root-base", WorktreePath: "/managed/ws/ws-1", State: "active"},
		{SessionID: rec.ID, RepoName: "api", Branch: "ao/ws-1", BaseSHA: "api-base", WorktreePath: "/managed/ws/ws-1/api", PreservedRef: "refs/ao/preserved/ws-1", State: "removed"},
	}
	for _, row := range rows {
		if err := s.UpsertSessionWorktree(ctx, row); err != nil {
			t.Fatalf("upsert worktree %s: %v", row.RepoName, err)
		}
	}
	got, err := s.ListSessionWorktrees(ctx, rec.ID)
	if err != nil {
		t.Fatalf("list worktrees: %v", err)
	}
	if !reflect.DeepEqual(got, rows) {
		t.Fatalf("worktrees = %#v, want %#v", got, rows)
	}
	one, ok, err := s.GetSessionWorktree(ctx, rec.ID, "api")
	if err != nil || !ok || one.PreservedRef != "refs/ao/preserved/ws-1" {
		t.Fatalf("get api = %#v ok=%v err=%v", one, ok, err)
	}
	rows[1].State = "active"
	rows[1].PreservedRef = ""
	if err := s.UpsertSessionWorktree(ctx, rows[1]); err != nil {
		t.Fatalf("update api worktree: %v", err)
	}
	one, ok, err = s.GetSessionWorktree(ctx, rec.ID, "api")
	if err != nil || !ok || one.State != "active" || one.PreservedRef != "" {
		t.Fatalf("updated api = %#v ok=%v err=%v", one, ok, err)
	}
	if err := s.DeleteSessionWorktrees(ctx, rec.ID); err != nil {
		t.Fatalf("delete worktrees: %v", err)
	}
	got, err = s.ListSessionWorktrees(ctx, rec.ID)
	if err != nil || len(got) != 0 {
		t.Fatalf("after delete = %#v err=%v", got, err)
	}
}
