petal/internal/db/db_test.go

package db

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

func TestOpenMigratesAndSeeds(t *testing.T) {
	path := filepath.Join(t.TempDir(), "test.db")

	d, err := Open(path)
	if err != nil {
		t.Fatalf("first open: %v", err)
	}

	// Local user is seeded.
	var email string
	if err := d.QueryRow(`SELECT email FROM users WHERE id = ?`, LocalUserID).Scan(&email); err != nil {
		t.Fatalf("local user not seeded: %v", err)
	}

	// All expected tables exist.
	for _, table := range []string{"users", "documents", "suggestions", "plagiarism_reports", "schema_migrations"} {
		var name string
		err := d.QueryRow(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, table).Scan(&name)
		if err != nil {
			t.Errorf("table %q missing: %v", table, err)
		}
	}

	// The voice suggestion type is permitted by the CHECK constraint.
	if _, err := d.Exec(`INSERT INTO documents (id, user_id) VALUES ('d1', ?)`, LocalUserID); err != nil {
		t.Fatalf("insert document: %v", err)
	}
	if _, err := d.Exec(
		`INSERT INTO suggestions (doc_id, from_pos, to_pos, original, replacement, explanation, type)
		 VALUES ('d1', 0, 4, 'teh', '', 'voice flag', 'voice')`,
	); err != nil {
		t.Fatalf("insert voice suggestion: %v", err)
	}

	// The collocation type (added by migration 0005's table rebuild) is permitted.
	if _, err := d.Exec(
		`INSERT INTO suggestions (doc_id, from_pos, to_pos, original, replacement, explanation, type)
		 VALUES ('d1', 0, 9, 'do a decision', 'make a decision', 'natives usually say…', 'collocation')`,
	); err != nil {
		t.Fatalf("insert collocation suggestion: %v", err)
	}

	// An invalid type is rejected.
	if _, err := d.Exec(
		`INSERT INTO suggestions (doc_id, from_pos, to_pos, original, replacement, explanation, type)
		 VALUES ('d1', 0, 4, 'teh', 'the', 'x', 'nonsense')`,
	); err == nil {
		t.Error("expected CHECK constraint to reject invalid suggestion type")
	}

	// Cascade delete removes child suggestions (foreign keys enabled).
	if _, err := d.Exec(`DELETE FROM documents WHERE id = 'd1'`); err != nil {
		t.Fatalf("delete document: %v", err)
	}
	var n int
	if err := d.QueryRow(`SELECT COUNT(*) FROM suggestions WHERE doc_id = 'd1'`).Scan(&n); err != nil {
		t.Fatal(err)
	}
	if n != 0 {
		t.Errorf("expected cascade delete, got %d orphan suggestions", n)
	}
	d.Close()

	// Re-opening is idempotent: migrations and seed don't double-apply or error.
	d2, err := Open(path)
	if err != nil {
		t.Fatalf("second open: %v", err)
	}
	defer d2.Close()

	var users int
	if err := d2.QueryRow(`SELECT COUNT(*) FROM users WHERE id = ?`, LocalUserID).Scan(&users); err != nil {
		t.Fatal(err)
	}
	if users != 1 {
		t.Errorf("expected exactly 1 local user after reopen, got %d", users)
	}
}

// TestResolvedAtBackfill runs migration 0012 against a database that predates
// it, which is the only shape that matters: on the live box the suggestions
// table is years of settled edits with no resolved_at to their name. Backfilling
// to created_at is exactly the approximation the growth journal would otherwise
// have had to make, and a pending row must stay NULL — nothing has been decided.
func TestResolvedAtBackfill(t *testing.T) {
	path := filepath.Join(t.TempDir(), "old.db")
	d, err := Open(path)
	if err != nil {
		t.Fatalf("open: %v", err)
	}

	// Rewind to the state before 0012: drop the column and forget the migration.
	if _, err := d.Exec(`DROP INDEX idx_suggestions_resolved`); err != nil {
		t.Fatalf("rewind index: %v", err)
	}
	if _, err := d.Exec(`ALTER TABLE suggestions DROP COLUMN resolved_at`); err != nil {
		t.Fatalf("rewind schema: %v", err)
	}
	if _, err := d.Exec(`DELETE FROM schema_migrations WHERE name = '0012_suggestion_resolved_at'`); err != nil {
		t.Fatalf("rewind migration record: %v", err)
	}
	if _, err := d.Exec(`INSERT INTO documents (id, user_id) VALUES ('d1', ?)`, LocalUserID); err != nil {
		t.Fatalf("insert document: %v", err)
	}
	for _, s := range []struct{ id, status string }{
		{"s-old", "accepted"},
		{"s-open", "pending"},
	} {
		if _, err := d.Exec(
			`INSERT INTO suggestions (id, doc_id, from_pos, to_pos, original, replacement, explanation, type, status, created_at)
			 VALUES (?, 'd1', 0, 3, 'teh', 'the', 'x', 'grammar', ?, '2026-01-02 03:04:05')`,
			s.id, s.status,
		); err != nil {
			t.Fatalf("seed %s: %v", s.id, err)
		}
	}
	d.Close()

	d2, err := Open(path)
	if err != nil {
		t.Fatalf("reopen (migrate): %v", err)
	}
	defer d2.Close()

	// Compared against created_at read back the same way: the driver renders a
	// DATETIME column itself, so the assertion is "the same instant", not a
	// particular text format.
	var settled, created *string
	if err := d2.QueryRow(
		`SELECT resolved_at, created_at FROM suggestions WHERE id = 's-old'`,
	).Scan(&settled, &created); err != nil {
		t.Fatalf("read settled row: %v", err)
	}
	if settled == nil || created == nil || *settled != *created {
		t.Errorf("resolved_at = %v, want it backfilled from created_at (%v)", settled, created)
	}

	var pending *string
	if err := d2.QueryRow(`SELECT resolved_at FROM suggestions WHERE id = 's-open'`).Scan(&pending); err != nil {
		t.Fatalf("read pending row: %v", err)
	}
	if pending != nil {
		t.Errorf("pending row got resolved_at = %v, want NULL — nothing was decided", *pending)
	}
}

// TestSuggestionSourceBackfill runs migration 0013 against a database that
// predates it — the shape the live box is actually in. `source` is the column
// that lets the offline rule pack and the model share the collocation family
// without deleting each other's rows, and it can only do that if the existing
// rows are labelled correctly on the way in: everything the old deterministic
// pass wrote is local, and everything else came from a model.
func TestSuggestionSourceBackfill(t *testing.T) {
	path := filepath.Join(t.TempDir(), "old.db")
	d, err := Open(path)
	if err != nil {
		t.Fatalf("open: %v", err)
	}

	// Rewind to the state before 0013.
	if _, err := d.Exec(`ALTER TABLE suggestions DROP COLUMN source`); err != nil {
		t.Fatalf("rewind schema: %v", err)
	}
	if _, err := d.Exec(`DELETE FROM schema_migrations WHERE name = '0013_suggestion_source'`); err != nil {
		t.Fatalf("rewind migration record: %v", err)
	}
	if _, err := d.Exec(`INSERT INTO documents (id, user_id) VALUES ('d1', ?)`, LocalUserID); err != nil {
		t.Fatalf("insert document: %v", err)
	}
	for _, s := range []struct{ id, typ string }{
		{"s-mech", SuggestionTypeMechanics},
		{"s-gram", SuggestionTypeGrammar},
		{"s-coll", SuggestionTypeCollocation},
	} {
		if _, err := d.Exec(
			`INSERT INTO suggestions (id, doc_id, from_pos, to_pos, original, replacement, explanation, type)
			 VALUES (?, 'd1', 0, 3, 'teh', 'the', 'x', ?)`,
			s.id, s.typ,
		); err != nil {
			t.Fatalf("seed %s: %v", s.id, err)
		}
	}
	d.Close()

	d2, err := Open(path)
	if err != nil {
		t.Fatalf("reopen (migrate): %v", err)
	}
	defer d2.Close()

	// A pre-0013 collocation row can only have come from the coach — the offline
	// miscollocation list did not exist yet — so it must NOT be claimed as local.
	for id, want := range map[string]string{
		"s-mech": SuggestionSourceLocal,
		"s-gram": SuggestionSourceLLM,
		"s-coll": SuggestionSourceLLM,
	} {
		var got string
		if err := d2.QueryRow(`SELECT source FROM suggestions WHERE id = ?`, id).Scan(&got); err != nil {
			t.Fatalf("read %s: %v", id, err)
		}
		if got != want {
			t.Errorf("%s: source = %q, want %q", id, got, want)
		}
	}

	// And a row written after the migration defaults to the model, so a code path
	// that forgets to name a source can never silently claim to be offline.
	if _, err := d2.Exec(
		`INSERT INTO suggestions (id, doc_id, from_pos, to_pos, original, replacement, explanation, type)
		 VALUES ('s-new', 'd1', 0, 3, 'teh', 'the', 'x', 'grammar')`,
	); err != nil {
		t.Fatalf("insert new row: %v", err)
	}
	var fresh string
	if err := d2.QueryRow(`SELECT source FROM suggestions WHERE id = 's-new'`).Scan(&fresh); err != nil {
		t.Fatalf("read new row: %v", err)
	}
	if fresh != SuggestionSourceLLM {
		t.Errorf("default source = %q, want %q", fresh, SuggestionSourceLLM)
	}
}

// TestTranslateTypeMigrationPreservesRows runs migration 0015 against a database
// that predates it. Unlike the two backfills above, 0015 *rebuilds the table* —
// SQLite can't ALTER a CHECK constraint — so it copies every row across by hand,
// and a column left out of that copy list silently loses her data. Every test
// elsewhere starts from a fresh database and would never notice; the live box has
// years of rows in it.
func TestTranslateTypeMigrationPreservesRows(t *testing.T) {
	path := filepath.Join(t.TempDir(), "old.db")
	d, err := Open(path)
	if err != nil {
		t.Fatalf("open: %v", err)
	}

	// Rewind to the pre-0015 table: the same shape, minus 'translate' in the CHECK.
	if _, err := d.Exec(`
CREATE TABLE suggestions_old (
    id           TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    doc_id       TEXT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    from_pos     INTEGER NOT NULL,
    to_pos       INTEGER NOT NULL,
    original     TEXT NOT NULL,
    replacement  TEXT NOT NULL,
    explanation  TEXT NOT NULL,
    type         TEXT NOT NULL CHECK(type IN ('grammar','phrasing','idiom','clarity','voice','collocation','mechanics')),
    status       TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','rejected')),
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    resolved_at  DATETIME,
    source       TEXT NOT NULL DEFAULT 'llm',
    chunk_hash   TEXT NOT NULL DEFAULT ''
);
DROP TABLE suggestions;
ALTER TABLE suggestions_old RENAME TO suggestions;
CREATE INDEX idx_suggestions_doc_id ON suggestions(doc_id);
CREATE INDEX idx_suggestions_resolved ON suggestions(status, resolved_at);
DELETE FROM schema_migrations WHERE name = '0015_translate_suggestion_type';
`); err != nil {
		t.Fatalf("rewind schema: %v", err)
	}

	if _, err := d.Exec(`INSERT INTO documents (id, user_id) VALUES ('d1', ?)`, LocalUserID); err != nil {
		t.Fatalf("insert document: %v", err)
	}
	// One row with every column carrying a distinguishable value, so a dropped
	// column shows up as a changed value rather than as a passing test.
	if _, err := d.Exec(
		`INSERT INTO suggestions (id, doc_id, from_pos, to_pos, original, replacement, explanation, type, status, created_at, resolved_at, source, chunk_hash)
		 VALUES ('s-1', 'd1', 7, 11, 'by foots', 'on foot', 'idiom advice she has read', 'idiom', 'accepted', '2026-01-02 03:04:05', '2026-01-02 03:05:00', 'local', 'abc123')`,
	); err != nil {
		t.Fatalf("seed row: %v", err)
	}
	d.Close()

	d2, err := Open(path)
	if err != nil {
		t.Fatalf("reopen (migrate): %v", err)
	}
	defer d2.Close()

	var (
		docID, original, replacement, explanation string
		typ, status, source, chunkHash            string
		from, to                                  int
		// Scanned as instants, not strings: the driver renders a DATETIME column in
		// its own format, so the claim is "the same moment", not the same text.
		createdAt, resolvedAt time.Time
	)
	if err := d2.QueryRow(
		`SELECT doc_id, from_pos, to_pos, original, replacement, explanation, type, status, created_at, resolved_at, source, chunk_hash
		   FROM suggestions WHERE id = 's-1'`,
	).Scan(&docID, &from, &to, &original, &replacement, &explanation,
		&typ, &status, &createdAt, &resolvedAt, &source, &chunkHash); err != nil {
		t.Fatalf("read migrated row: %v", err)
	}
	for _, c := range []struct{ name, got, want string }{
		{"doc_id", docID, "d1"},
		{"original", original, "by foots"},
		{"replacement", replacement, "on foot"},
		{"explanation", explanation, "idiom advice she has read"},
		{"type", typ, SuggestionTypeIdiom},
		{"status", status, SuggestionStatusAccepted},
		{"source", source, SuggestionSourceLocal},
		{"chunk_hash", chunkHash, "abc123"},
	} {
		if c.got != c.want {
			t.Errorf("%s = %q, want %q", c.name, c.got, c.want)
		}
	}
	if from != 7 || to != 11 {
		t.Errorf("offsets = (%d, %d), want (7, 11)", from, to)
	}
	// created_at and resolved_at must survive: the rail's arrival chime keys on
	// created_at, and the growth journal counts by resolved_at. A rebuild that
	// reset either would re-chime her whole document and rewrite her history.
	for _, c := range []struct {
		name string
		got  time.Time
		want string
	}{
		{"created_at", createdAt, "2026-01-02 03:04:05"},
		{"resolved_at", resolvedAt, "2026-01-02 03:05:00"},
	} {
		want, err := time.Parse("2006-01-02 15:04:05", c.want)
		if err != nil {
			t.Fatalf("parse want: %v", err)
		}
		if !c.got.Equal(want) {
			t.Errorf("%s = %v, want the original instant %v", c.name, c.got, want)
		}
	}

	// The point of the rebuild: the new type is now insertable, and a bogus one
	// still isn't.
	if _, err := d2.Exec(
		`INSERT INTO suggestions (id, doc_id, from_pos, to_pos, original, replacement, explanation, type)
		 VALUES ('s-2', 'd1', 0, 3, '苹果', 'apple', 'x', ?)`, SuggestionTypeTranslate,
	); err != nil {
		t.Fatalf("insert translate row: %v", err)
	}
	if _, err := d2.Exec(
		`INSERT INTO suggestions (id, doc_id, from_pos, to_pos, original, replacement, explanation, type)
		 VALUES ('s-3', 'd1', 0, 3, 'x', 'y', 'x', 'nonsense')`,
	); err == nil {
		t.Error("CHECK constraint accepted an unknown type after the rebuild")
	}

	// Both indexes must come back, or every document load starts table-scanning.
	for _, idx := range []string{"idx_suggestions_doc_id", "idx_suggestions_resolved"} {
		var name string
		if err := d2.QueryRow(
			`SELECT name FROM sqlite_master WHERE type = 'index' AND name = ?`, idx,
		).Scan(&name); err != nil {
			t.Errorf("index %s missing after rebuild: %v", idx, err)
		}
	}
}