package db

import (
	"context"
	"testing"
)

// TestMigrate0011SessionPersonaColumn covers design/22 acceptance 6: after upgrading a
// database that only has the 0010 schema, both new columns exist with the ''
// default, and every pre-existing row reads back as unbound — '' means "inherit the
// global default role", so no backfill is needed and the old behavior is preserved.
// It also locks the down migration (both columns dropped) and the idempotent re-up.
func TestMigrate0011SessionPersonaColumn(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	conn := openMemory(t)

	if err := MigrateUp(ctx, conn, testLogger()); err != nil {
		t.Fatalf("MigrateUp: %v", err)
	}
	for _, col := range []struct{ table, column string }{
		{"chat_sessions", "persona_id"},
		{"chat_messages", "persona_id"},
	} {
		if !columnExists(t, ctx, conn, col.table, col.column) {
			t.Fatalf("%s.%s should exist after MigrateUp", col.table, col.column)
		}
	}

	// A row written before 0011 was added (here: written without the column, exactly
	// what the ALTER's DEFAULT produces) must read back as the inherit-the-default
	// value, and a legacy session must still be usable by the store.
	legacySession := `INSERT INTO chat_sessions (id, type, title, pinned, created_at) VALUES ('web:legacy', 'sub', '', 0, 100)`
	if _, err := conn.ExecContext(ctx, legacySession); err != nil {
		t.Fatalf("insert legacy session: %v", err)
	}
	legacyMessage := `INSERT INTO chat_messages (id, session_id, role, content, created_at) VALUES ('m1', 'web:legacy', 'user', 'hi', 200)`
	if _, err := conn.ExecContext(ctx, legacyMessage); err != nil {
		t.Fatalf("insert legacy message: %v", err)
	}
	var sessionPersona, messagePersona string
	if err := conn.QueryRowContext(ctx, `SELECT persona_id FROM chat_sessions WHERE id = 'web:legacy'`).Scan(&sessionPersona); err != nil {
		t.Fatalf("read legacy session persona: %v", err)
	}
	if err := conn.QueryRowContext(ctx, `SELECT persona_id FROM chat_messages WHERE id = 'm1'`).Scan(&messagePersona); err != nil {
		t.Fatalf("read legacy message persona: %v", err)
	}
	if sessionPersona != "" || messagePersona != "" {
		t.Fatalf("legacy rows persona_id = (%q, %q), want ('', '') — inherit the global default", sessionPersona, messagePersona)
	}

	// Up twice: the recorded migration is skipped and nothing errors.
	if err := MigrateUp(ctx, conn, testLogger()); err != nil {
		t.Fatalf("MigrateUp second call: %v", err)
	}

	// Roll back everything newer than 0010 (0011 itself plus any future migration),
	// so this test keeps validating the 0011 down no matter how the repo grows.
	steps := len(upMigrationIDs(t)) - 10
	if err := MigrateDown(ctx, conn, steps, testLogger()); err != nil {
		t.Fatalf("MigrateDown(%d): %v", steps, err)
	}
	for _, col := range []struct{ table, column string }{
		{"chat_sessions", "persona_id"},
		{"chat_messages", "persona_id"},
	} {
		if columnExists(t, ctx, conn, col.table, col.column) {
			t.Fatalf("%s.%s should be dropped by the 0011 down", col.table, col.column)
		}
	}

	// Re-applying restores both columns.
	if err := MigrateUp(ctx, conn, testLogger()); err != nil {
		t.Fatalf("re-MigrateUp: %v", err)
	}
	for _, col := range []struct{ table, column string }{
		{"chat_sessions", "persona_id"},
		{"chat_messages", "persona_id"},
	} {
		if !columnExists(t, ctx, conn, col.table, col.column) {
			t.Fatalf("%s.%s should exist again after re-MigrateUp", col.table, col.column)
		}
	}
}