iop/apps/control-plane/internal/credentialstore/principal_test.go

package credentialstore

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"path/filepath"
	"strings"
	"sync"
	"testing"
	"time"

	_ "modernc.org/sqlite"
)

// newTestStore opens an in-memory SQLite store for tests.
func newTestStore(t *testing.T) *Store {
	t.Helper()
	ctx := context.Background()
	s, err := Open(ctx, "file:iop_test_"+strings.ReplaceAll(t.Name(), "/", "_")+".db?mode=memory&cache=shared")
	if err != nil {
		t.Fatalf("open test store: %v", err)
	}
	t.Cleanup(func() { _ = s.Close() })
	return s
}

func TestCreatePrincipalReturnsRawTokenOnce(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "edge-prod"})
	if err != nil {
		t.Fatalf("create principal: %v", err)
	}
	if issued.Principal.Alias != "edge-prod" {
		t.Fatalf("principal alias: got %q want %q", issued.Principal.Alias, "edge-prod")
	}
	if issued.Token.Status != StatusActive {
		t.Fatalf("token status: got %q want %q", issued.Token.Status, StatusActive)
	}
	if issued.RawToken == "" {
		t.Fatal("raw token must be returned once")
	}
	if len(issued.RawToken) != 64 {
		t.Fatalf("raw token length: got %d want 64", len(issued.RawToken))
	}
	// TokenRecord must NOT contain the raw token; only digest.
	if issued.Token.Digest == issued.RawToken {
		t.Fatal("token digest must not equal raw token")
	}
	// Verify digest is SHA-256 of raw.
	sum := sha256.Sum256([]byte(issued.RawToken))
	expected := hex.EncodeToString(sum[:])
	if issued.Token.Digest != expected {
		t.Fatalf("digest mismatch: got %q want %q", issued.Token.Digest, expected)
	}
	// The raw token must not appear in any DB column.
	var storedDigest string
	if err := store.db.QueryRowContext(ctx, `SELECT digest FROM tokens WHERE principal_id=?`, issued.Principal.ID).Scan(&storedDigest); err != nil {
		t.Fatalf("query digest: %v", err)
	}
	if storedDigest != expected {
		t.Fatalf("stored digest: got %q want %q", storedDigest, expected)
	}
}

func TestCreatePrincipalRejectsDuplicateAlias(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	if _, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "dup"}); err != nil {
		t.Fatalf("first create: %v", err)
	}
	var dupErr error
	if _, dupErr = store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "dup"}); dupErr != ErrPrincipalAlreadyExists {
		t.Fatalf("expected ErrPrincipalAlreadyExists, got: %v", dupErr)
	}
	// Verify no second principal was created.
	var count int
	store.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM principals`).Scan(&count)
	if count != 1 {
		t.Fatalf("principal count: got %d want 1", count)
	}
}

func TestCreateFirstPrincipalWithTokenRejectsNonEmptyStore(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	if _, err := store.CreateFirstPrincipalWithToken(ctx, CreatePrincipalInput{Alias: "first"}); err != nil {
		t.Fatalf("first create: %v", err)
	}
	var dupErr error
	if _, dupErr = store.CreateFirstPrincipalWithToken(ctx, CreatePrincipalInput{Alias: "second"}); !errors.Is(dupErr, ErrPrincipalAlreadyExists) {
		t.Fatalf("expected ErrPrincipalAlreadyExists, got: %v", dupErr)
	}
	var count int
	store.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM principals`).Scan(&count)
	if count != 1 {
		t.Fatalf("principal count: got %d want 1", count)
	}
}

func TestCreateFirstPrincipalWithTokenAllowsExactlyOneConcurrentCaller(t *testing.T) {
	ctx := context.Background()
	tmpFile := filepath.Join(t.TempDir(), "concurrent_first.db")
	initializer, err := Open(ctx, tmpFile)
	if err != nil {
		t.Fatalf("initialize store: %v", err)
	}
	if err := initializer.Close(); err != nil {
		t.Fatalf("close initialized store: %v", err)
	}

	const numCallers = 5
	stores := openIndependentTestStores(t, ctx, tmpFile, numCallers)
	start := make(chan struct{})
	type result struct {
		issued *IssuedPrincipal
		err    error
	}
	results := make(chan result, numCallers)
	var wg sync.WaitGroup

	for i := 0; i < numCallers; i++ {
		wg.Add(1)
		alias := fmt.Sprintf("alias-%d", i)
		store := stores[i]
		go func(a string, s *Store) {
			defer wg.Done()
			<-start
			issued, err := s.CreateFirstPrincipalWithToken(ctx, CreatePrincipalInput{Alias: a})
			results <- result{issued: issued, err: err}
		}(alias, store)
	}
	close(start)
	wg.Wait()
	close(results)

	var successes, alreadyExists, tokenOutputs int
	for result := range results {
		if result.err == nil {
			successes++
			if result.issued == nil || result.issued.RawToken == "" {
				t.Fatal("successful first bootstrap must return one raw token")
			}
			tokenOutputs++
		} else if errors.Is(result.err, ErrPrincipalAlreadyExists) {
			alreadyExists++
		} else {
			t.Fatalf("unexpected error from concurrent caller: %v", result.err)
		}
	}

	if successes != 1 || tokenOutputs != 1 || alreadyExists != numCallers-1 {
		t.Fatalf("concurrent results: successes=%d tokenOutputs=%d alreadyExists=%d; want 1, 1, and %d", successes, tokenOutputs, alreadyExists, numCallers-1)
	}

	var principalCount, tokenCount int
	if err := stores[0].db.QueryRowContext(ctx, `SELECT COUNT(*) FROM principals`).Scan(&principalCount); err != nil {
		t.Fatalf("count principals: %v", err)
	}
	if err := stores[0].db.QueryRowContext(ctx, `SELECT COUNT(*) FROM tokens`).Scan(&tokenCount); err != nil {
		t.Fatalf("count tokens: %v", err)
	}
	if principalCount != 1 || tokenCount != 1 {
		t.Fatalf("persisted counts: principals=%d tokens=%d; want 1 and 1", principalCount, tokenCount)
	}
}

func openIndependentTestStores(t *testing.T, ctx context.Context, databaseURL string, count int) []*Store {
	t.Helper()
	stores := make([]*Store, 0, count)
	for range count {
		store, err := Open(ctx, databaseURL)
		if err != nil {
			for _, opened := range stores {
				_ = opened.Close()
			}
			t.Fatalf("open independent store: %v", err)
		}
		stores = append(stores, store)
	}
	t.Cleanup(func() {
		for _, store := range stores {
			_ = store.Close()
		}
	})
	return stores
}

func TestPrincipalTokenLifecycleUsesRevisionCAS(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "cas-test"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}

	// Disable with correct revision.
	_, err = store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0)
	if err != nil {
		t.Fatalf("disable: %v", err)
	}

	// Verify revision incremented.
	var rev int64
	store.db.QueryRowContext(ctx, `SELECT revision FROM tokens WHERE principal_id=?`, issued.Principal.ID).Scan(&rev)
	if rev != 1 {
		t.Fatalf("revision after disable: got %d want 1", rev)
	}

	// A stale revision takes precedence over the inactive status.
	_, err = store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0)
	if !errors.Is(err, ErrRevisionMismatch) {
		t.Fatalf("expected ErrRevisionMismatch, got: %v", err)
	}

	// Verify revision is still 1 (no change from failed disable).
	store.db.QueryRowContext(ctx, `SELECT revision FROM tokens WHERE principal_id=?`, issued.Principal.ID).Scan(&rev)
	if rev != 1 {
		t.Fatalf("revision after failed disable: got %d want 1", rev)
	}
}

func TestRevokeDisabledToken(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "revoke-disabled"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}
	if _, err := store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0); err != nil {
		t.Fatalf("disable: %v", err)
	}
	revoked, err := store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1)
	if err != nil {
		t.Fatalf("revoke disabled token: %v", err)
	}
	if revoked.Status != StatusRevoked || revoked.Revision != 2 || revoked.RevokedAt == nil {
		t.Fatalf("unexpected revoked record: %+v", revoked)
	}
}

func TestRevokedTokenPersistsAcrossReopen(t *testing.T) {
	ctx := context.Background()
	databaseURL := t.TempDir() + "/revoked.db"
	store, err := Open(ctx, databaseURL)
	if err != nil {
		t.Fatalf("open: %v", err)
	}
	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "reopen-revoked"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}
	if _, err := store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0); err != nil {
		t.Fatalf("disable: %v", err)
	}
	if _, err := store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1); err != nil {
		t.Fatalf("revoke: %v", err)
	}
	if err := store.Close(); err != nil {
		t.Fatalf("close: %v", err)
	}

	reopened, err := Open(ctx, databaseURL)
	if err != nil {
		t.Fatalf("reopen: %v", err)
	}
	defer reopened.Close()
	if _, err := reopened.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 2); !errors.Is(err, ErrTokenRevoked) {
		t.Fatalf("revoked state did not persist: %v", err)
	}
}

func TestTokenMutationLosingCASDoesNotSucceed(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	// Serializing the in-memory SQLite pool avoids lock errors while the two
	// callers still race for the same revision through the public API.
	store.db.SetMaxOpenConns(1)
	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "concurrent-cas"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}

	start := make(chan struct{})
	errs := make(chan error, 2)
	var wg sync.WaitGroup
	for range 2 {
		wg.Add(1)
		go func() {
			defer wg.Done()
			<-start
			_, err := store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0)
			errs <- err
		}()
	}
	close(start)
	wg.Wait()
	close(errs)

	var successes, revisionMismatches int
	for err := range errs {
		if err == nil {
			successes++
			continue
		}
		if errors.Is(err, ErrRevisionMismatch) {
			revisionMismatches++
			continue
		}
		t.Fatalf("CAS loser returned an untyped error: %v", err)
	}
	if successes != 1 || revisionMismatches != 1 {
		t.Fatalf("CAS results: successes=%d revision_mismatches=%d", successes, revisionMismatches)
	}
}

func TestRevokedTokenCannotBeReenabled(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "revoke-test"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}

	// Revoke the token.
	_, err = store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0)
	if err != nil {
		t.Fatalf("revoke: %v", err)
	}

	// Attempt to re-enable (disable then active) must fail.
	_, err = store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1)
	if err != ErrTokenRevoked {
		t.Fatalf("expected ErrTokenRevoked on disable, got: %v", err)
	}

	// Attempt to re-revoke must also fail.
	_, err = store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1)
	if err != ErrTokenRevoked {
		t.Fatalf("expected ErrTokenRevoked on revoke, got: %v", err)
	}

	// Verify status is revoked and revoked_at is set.
	var status string
	var revokedAt sql.NullString
	store.db.QueryRowContext(ctx, `SELECT status, revoked_at FROM tokens WHERE principal_id=?`, issued.Principal.ID).Scan(&status, &revokedAt)
	if status != StatusRevoked {
		t.Fatalf("status: got %q want %q", status, StatusRevoked)
	}
	if !revokedAt.Valid {
		t.Fatal("revoked_at must be set")
	}
}

func TestPrincipalTokenPersistsAcrossReopen(t *testing.T) {
	ctx := context.Background()
	tmpFile := t.TempDir() + "/persist.db"

	// Create and issue.
	store1, err := Open(ctx, tmpFile)
	if err != nil {
		t.Fatalf("open 1: %v", err)
	}
	issued, err := store1.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "persist"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}
	rawToken := issued.RawToken
	_ = store1.Close()

	// Reopen.
	store2, err := Open(ctx, tmpFile)
	if err != nil {
		t.Fatalf("open 2: %v", err)
	}
	defer store2.Close()

	// Verify principal exists.
	p, err := store2.GetPrincipal(ctx, "persist")
	if err != nil {
		t.Fatalf("get principal: %v", err)
	}
	if p == nil {
		t.Fatal("principal must persist across reopen")
	}

	// Verify token lookup by digest works after reopen.
	sum := sha256.Sum256([]byte(rawToken))
	digest := hex.EncodeToString(sum[:])
	_, token, err := store2.LookupTokenByDigest(ctx, digest)
	if err != nil {
		t.Fatalf("lookup by digest after reopen: %v", err)
	}
	if token == nil {
		t.Fatal("token must persist across reopen")
	}
	if token.Status != StatusActive {
		t.Fatalf("token status after reopen: got %q want %q", token.Status, StatusActive)
	}
}

func TestLookupTokenByDigestReturnsOnlyActive(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "active-only"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}
	sum := sha256.Sum256([]byte(issued.RawToken))
	digest := hex.EncodeToString(sum[:])

	// Active lookup succeeds.
	_, _, err = store.LookupTokenByDigest(ctx, digest)
	if err != nil {
		t.Fatalf("active lookup: %v", err)
	}

	// Disable and verify lookup fails.
	_, err = store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0)
	if err != nil {
		t.Fatalf("disable: %v", err)
	}
	_, _, err = store.LookupTokenByDigest(ctx, digest)
	if err != ErrTokenNotFound {
		t.Fatalf("expected ErrTokenNotFound after disable, got: %v", err)
	}

	// Re-enable and verify lookup works again.
	// Note: DisableToken transitions active->disabled, but we need a path
	// to re-enable. For this test, we revoke and check that path.
	// Actually the test verifies disable blocks lookup; re-enable is a
	// separate concern tested elsewhere.
}

func TestListPrincipalsReturnsMetadataOnly(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "list-test"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}

	principals, err := store.ListPrincipals(ctx)
	if err != nil {
		t.Fatalf("list: %v", err)
	}
	if len(principals) != 1 {
		t.Fatalf("principal count: got %d want 1", len(principals))
	}
	p := principals[0]
	if p.Principal.Alias != "list-test" {
		t.Fatalf("alias: got %q want %q", p.Principal.Alias, "list-test")
	}
	// TokenRecord must not expose raw token.
	if p.Token.TokenRef == issued.RawToken {
		t.Fatal("list must not expose raw token in token_ref")
	}
}

func TestListPrincipalsWithoutActiveToken(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "inactive-list"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}
	if _, err := store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0); err != nil {
		t.Fatalf("disable: %v", err)
	}

	principals, err := store.ListPrincipals(ctx)
	if err != nil {
		t.Fatalf("list after disable: %v", err)
	}
	if len(principals) != 1 || principals[0].Token.TokenRef != "" || principals[0].Token.Revision != 0 {
		t.Fatalf("inactive token metadata must be zero: %+v", principals)
	}
	if _, err := store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1); err != nil {
		t.Fatalf("revoke: %v", err)
	}
	principals, err = store.ListPrincipals(ctx)
	if err != nil {
		t.Fatalf("list after revoke: %v", err)
	}
	if len(principals) != 1 || principals[0].Token.TokenRef != "" {
		t.Fatalf("revoked token metadata must be zero: %+v", principals)
	}
}

func TestCreatePrincipalRequiresAlias(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	_, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{})
	if err == nil {
		t.Fatal("expected error for empty alias")
	}
}

func TestRawTokenDoesNotLeakIntoDatabase(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "leak-test"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}

	// Scan every column in both tables and verify raw token is absent.
	var principalAlias, principalID string
	store.db.QueryRowContext(ctx, `SELECT id, alias FROM principals WHERE id=?`, issued.Principal.ID).Scan(&principalID, &principalAlias)
	if principalAlias == issued.RawToken {
		t.Fatal("raw token leaked into principals.alias")
	}

	var tokenRef, digest string
	store.db.QueryRowContext(ctx, `SELECT token_ref, digest FROM tokens WHERE principal_id=?`, issued.Principal.ID).Scan(&tokenRef, &digest)
	if strings.Contains(tokenRef, issued.RawToken) {
		t.Fatal("raw token leaked into tokens.token_ref")
	}
	if strings.Contains(digest, issued.RawToken) {
		t.Fatal("raw token leaked into tokens.digest")
	}
	// Verify no column contains the raw token.
	var allText string
	rows, err := store.db.QueryContext(ctx, `SELECT * FROM tokens WHERE principal_id=?`, issued.Principal.ID)
	if err != nil {
		t.Fatalf("scan tokens: %v", err)
	}
	defer rows.Close()
	cols, _ := rows.Columns()
	for rows.Next() {
		vals := make([]interface{}, len(cols))
		ptrs := make([]interface{}, len(cols))
		for i := range vals {
			ptrs[i] = &vals[i]
		}
		if err := rows.Scan(ptrs...); err != nil {
			t.Fatalf("scan row: %v", err)
		}
		for _, v := range vals {
			if s, ok := v.(string); ok {
				allText += s
			}
		}
	}
	if strings.Contains(allText, issued.RawToken) {
		t.Fatal("raw token found in tokens table columns")
	}
}

func TestRevocationIsIrreversible(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	issued, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "irreversible"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}

	// Revoke.
	_, err = store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 0)
	if err != nil {
		t.Fatalf("revoke: %v", err)
	}

	// Attempt to disable (which would be a step toward re-enabling).
	_, err = store.DisableToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1)
	if err != ErrTokenRevoked {
		t.Fatalf("expected ErrTokenRevoked, got: %v", err)
	}

	// Attempt to revoke again.
	_, err = store.RevokeToken(ctx, issued.Principal.ID, issued.Token.TokenRef, 1)
	if err != ErrTokenRevoked {
		t.Fatalf("expected ErrTokenRevoked on double revoke, got: %v", err)
	}

	// Lookup must fail (only active tokens are returned).
	sum := sha256.Sum256([]byte(issued.RawToken))
	_, _, err = store.LookupTokenByDigest(ctx, hex.EncodeToString(sum[:]))
	if err != ErrTokenNotFound {
		t.Fatalf("expected ErrTokenNotFound for revoked token, got: %v", err)
	}
}

func TestOpenReturnsNilForEmptyURL(t *testing.T) {
	ctx := context.Background()
	s, err := Open(ctx, "")
	if err != nil {
		t.Fatalf("open empty: %v", err)
	}
	if s != nil {
		t.Fatal("expected nil store for empty URL")
	}
}

func TestDialectFromURLRejectsUnknownSchemeWithDBSuffix(t *testing.T) {
	if _, err := dialectFromURL("memdb://credentials.db"); err == nil {
		t.Fatal("unknown URL scheme containing .db must not select SQLite")
	}
	for _, tc := range []struct {
		url, want string
	}{
		{"credentials.db", dialectSQLite},
		{"file:credentials.db", dialectSQLite},
		{"postgres://db/credentials", dialectPostgres},
		{"postgresql://db/credentials", dialectPostgres},
	} {
		got, err := dialectFromURL(tc.url)
		if err != nil || got != tc.want {
			t.Fatalf("dialectFromURL(%q) = %q, %v; want %q", tc.url, got, err, tc.want)
		}
	}
}

func TestBindQueryUsesPostgresOrdinals(t *testing.T) {
	query := "SELECT * FROM tokens WHERE principal_id=? AND token_ref=? AND status='active'"
	if got, want := bindQuery(dialectPostgres, query), "SELECT * FROM tokens WHERE principal_id=$1 AND token_ref=$2 AND status='active'"; got != want {
		t.Fatalf("PostgreSQL binding: got %q want %q", got, want)
	}
	if got := bindQuery(dialectSQLite, query); got != query {
		t.Fatalf("SQLite binding changed query: %q", got)
	}
}

func TestSQLiteDataSourceWithBusyTimeoutPreservesDataSourceShape(t *testing.T) {
	for _, tc := range []struct {
		name, input, want string
	}{
		{"plain path", "credentials.db", "credentials.db?_pragma=busy_timeout%3d5000"},
		{"file URL", "file:credentials.db", "file:credentials.db?_pragma=busy_timeout%3d5000"},
		{"query", "file:credentials.db?mode=memory&cache=shared", "file:credentials.db?mode=memory&cache=shared&_pragma=busy_timeout%3d5000"},
		{"memory", ":memory:", ":memory:?_pragma=busy_timeout%3d5000"},
		{"fragment", "file:credentials.db?mode=rwc#fragment", "file:credentials.db?mode=rwc&_pragma=busy_timeout%3d5000#fragment"},
	} {
		t.Run(tc.name, func(t *testing.T) {
			if got := sqliteDataSourceWithBusyTimeout(tc.input); got != tc.want {
				t.Fatalf("sqliteDataSourceWithBusyTimeout(%q) = %q, want %q", tc.input, got, tc.want)
			}
		})
	}
}

func TestSchemaStatementsMatchDialect(t *testing.T) {
	postgres := strings.Join(schemaStatements(dialectPostgres), "\n")
	if strings.Contains(postgres, "DATETIME") || strings.Contains(postgres, "?") || !strings.Contains(postgres, "TIMESTAMPTZ") {
		t.Fatalf("PostgreSQL schema is not executable: %s", postgres)
	}
	sqlite := strings.Join(schemaStatements(dialectSQLite), "\n")
	if !strings.Contains(sqlite, "created_at TEXT") {
		t.Fatalf("SQLite schema must use text timestamps: %s", sqlite)
	}
}

func TestOpenRejectsUnsupportedScheme(t *testing.T) {
	ctx := context.Background()
	_, err := Open(ctx, "memdb://something")
	if err == nil {
		t.Fatal("expected error for unsupported scheme")
	}
}

func TestOpenRejectsUnreachablePostgres(t *testing.T) {
	ctx := context.Background()
	_, err := Open(ctx, "postgres://localhost:99999/nonexistent")
	if err == nil {
		t.Fatal("expected error for unreachable postgres")
	}
}

func TestSQLiteReopenPreservesSchema(t *testing.T) {
	ctx := context.Background()
	tmpFile := t.TempDir() + "/schema.db"

	// First open creates schema.
	s1, err := Open(ctx, tmpFile)
	if err != nil {
		t.Fatalf("open 1: %v", err)
	}
	_, err = s1.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "schema-test"})
	if err != nil {
		t.Fatalf("create: %v", err)
	}
	_ = s1.Close()

	// Second open must not re-fail on schema.
	s2, err := Open(ctx, tmpFile)
	if err != nil {
		t.Fatalf("open 2: %v", err)
	}
	defer s2.Close()

	// Data must persist.
	p, err := s2.GetPrincipal(ctx, "schema-test")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if p == nil {
		t.Fatal("principal must persist")
	}
}

func TestAdditionalTokenIssueAndList(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)

	gen0, err := store.ProjectionGeneration(ctx)
	if err != nil {
		t.Fatalf("ProjectionGeneration 0: %v", err)
	}

	issuedP, err := store.CreatePrincipalWithToken(ctx, CreatePrincipalInput{Alias: "add-token-principal"})
	if err != nil {
		t.Fatalf("CreatePrincipalWithToken: %v", err)
	}
	gen1, err := store.ProjectionGeneration(ctx)
	if err != nil {
		t.Fatalf("ProjectionGeneration 1: %v", err)
	}
	if gen1 != gen0+1 {
		t.Fatalf("ProjectionGeneration after CreatePrincipalWithToken: got %d want %d", gen1, gen0+1)
	}

	dig0 := digestOf(issuedP.RawToken)
	if issuedP.Token.Digest != dig0 {
		t.Fatalf("issuedP token digest mismatch: got %q want %q", issuedP.Token.Digest, dig0)
	}
	if issuedP.Token.TokenRef != tokenRefFor(dig0) {
		t.Fatalf("issuedP tokenRef mismatch: got %q want %q", issuedP.Token.TokenRef, tokenRefFor(dig0))
	}

	tok1, err := store.CreateToken(ctx, issuedP.Principal.ID)
	if err != nil {
		t.Fatalf("CreateToken 1: %v", err)
	}
	gen2, err := store.ProjectionGeneration(ctx)
	if err != nil {
		t.Fatalf("ProjectionGeneration 2: %v", err)
	}
	if gen2 != gen1+1 {
		t.Fatalf("ProjectionGeneration after CreateToken 1: got %d want %d", gen2, gen1+1)
	}
	if tok1.RawToken == "" {
		t.Fatal("RawToken must be returned on CreateToken")
	}
	if tok1.Token.PrincipalID != issuedP.Principal.ID {
		t.Fatalf("PrincipalID mismatch: got %q want %q", tok1.Token.PrincipalID, issuedP.Principal.ID)
	}
	dig1 := digestOf(tok1.RawToken)
	if tok1.Token.Digest != dig1 {
		t.Fatalf("tok1 digest mismatch: got %q want %q", tok1.Token.Digest, dig1)
	}
	if tok1.Token.TokenRef != tokenRefFor(dig1) {
		t.Fatalf("tok1 tokenRef mismatch: got %q want %q", tok1.Token.TokenRef, tokenRefFor(dig1))
	}

	tok2, err := store.CreateToken(ctx, issuedP.Principal.ID)
	if err != nil {
		t.Fatalf("CreateToken 2: %v", err)
	}
	gen3, err := store.ProjectionGeneration(ctx)
	if err != nil {
		t.Fatalf("ProjectionGeneration 3: %v", err)
	}
	if gen3 != gen2+1 {
		t.Fatalf("ProjectionGeneration after CreateToken 2: got %d want %d", gen3, gen2+1)
	}
	if tok2.RawToken == "" {
		t.Fatal("RawToken must be returned on CreateToken 2")
	}
	if tok2.Token.PrincipalID != issuedP.Principal.ID {
		t.Fatalf("PrincipalID mismatch: got %q want %q", tok2.Token.PrincipalID, issuedP.Principal.ID)
	}
	dig2 := digestOf(tok2.RawToken)
	if tok2.Token.Digest != dig2 {
		t.Fatalf("tok2 digest mismatch: got %q want %q", tok2.Token.Digest, dig2)
	}
	if tok2.Token.TokenRef != tokenRefFor(dig2) {
		t.Fatalf("tok2 tokenRef mismatch: got %q want %q", tok2.Token.TokenRef, tokenRefFor(dig2))
	}

	rows, err := store.db.QueryContext(ctx, store.bind(`SELECT id, principal_id, token_ref, digest, status, revision FROM tokens WHERE principal_id=? ORDER BY created_at ASC, id ASC`), issuedP.Principal.ID)
	if err != nil {
		t.Fatalf("query tokens: %v", err)
	}
	defer rows.Close()

	expectedTokens := []struct {
		id       string
		tokenRef string
		digest   string
		raw      string
		status   string
		revision int64
	}{
		{id: issuedP.Token.ID, tokenRef: issuedP.Token.TokenRef, digest: dig0, raw: issuedP.RawToken, status: StatusActive, revision: 0},
		{id: tok1.Token.ID, tokenRef: tok1.Token.TokenRef, digest: dig1, raw: tok1.RawToken, status: StatusActive, revision: 0},
		{id: tok2.Token.ID, tokenRef: tok2.Token.TokenRef, digest: dig2, raw: tok2.RawToken, status: StatusActive, revision: 0},
	}

	var rowCount int
	for rows.Next() {
		if rowCount >= len(expectedTokens) {
			t.Fatalf("too many persisted token rows: count > %d", len(expectedTokens))
		}
		exp := expectedTokens[rowCount]
		var id, pid, tref, dig, status string
		var rev int64
		if err := rows.Scan(&id, &pid, &tref, &dig, &status, &rev); err != nil {
			t.Fatalf("scan token row %d: %v", rowCount, err)
		}
		if id != exp.id {
			t.Errorf("row %d ID mismatch: got %q want %q", rowCount, id, exp.id)
		}
		if pid != issuedP.Principal.ID {
			t.Errorf("row %d PrincipalID mismatch: got %q want %q", rowCount, pid, issuedP.Principal.ID)
		}
		if tref != exp.tokenRef {
			t.Errorf("row %d TokenRef mismatch: got %q want %q", rowCount, tref, exp.tokenRef)
		}
		if dig != exp.digest {
			t.Errorf("row %d Digest mismatch: got %q want %q", rowCount, dig, exp.digest)
		}
		if status != exp.status {
			t.Errorf("row %d Status mismatch: got %q want %q", rowCount, status, exp.status)
		}
		if rev != exp.revision {
			t.Errorf("row %d Revision mismatch: got %d want %d", rowCount, rev, exp.revision)
		}
		if strings.Contains(dig, exp.raw) || strings.Contains(tref, exp.raw) {
			t.Errorf("row %d leaks raw token %q in digest/token_ref", rowCount, exp.raw)
		}
		rowCount++
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("token rows iteration: %v", err)
	}
	if rowCount != len(expectedTokens) {
		t.Fatalf("persisted token rows count: got %d want %d", rowCount, len(expectedTokens))
	}

	list, err := store.ListTokens(ctx, issuedP.Principal.ID)
	if err != nil {
		t.Fatalf("ListTokens: %v", err)
	}
	if len(list) != 3 {
		t.Fatalf("ListTokens count: got %d want 3", len(list))
	}
	for i, exp := range expectedTokens {
		if list[i].ID != exp.id {
			t.Errorf("list[%d] ID mismatch: got %q want %q", i, list[i].ID, exp.id)
		}
		if list[i].TokenRef != exp.tokenRef {
			t.Errorf("list[%d] TokenRef mismatch: got %q want %q", i, list[i].TokenRef, exp.tokenRef)
		}
		if list[i].Digest != exp.digest {
			t.Errorf("list[%d] Digest mismatch: got %q want %q", i, list[i].Digest, exp.digest)
		}
		if list[i].Status != exp.status {
			t.Errorf("list[%d] Status mismatch: got %q want %q", i, list[i].Status, exp.status)
		}
		if list[i].Revision != exp.revision {
			t.Errorf("list[%d] Revision mismatch: got %d want %d", i, list[i].Revision, exp.revision)
		}
	}

	genBeforeFail, err := store.ProjectionGeneration(ctx)
	if err != nil {
		t.Fatalf("ProjectionGeneration before fail: %v", err)
	}
	countBeforeFail := len(list)

	_, err = store.CreateToken(ctx, "non-existent-principal-id")
	if !errors.Is(err, ErrPrincipalNotFound) {
		t.Fatalf("expected ErrPrincipalNotFound for non-existent principal, got: %v", err)
	}

	genAfterFail, err := store.ProjectionGeneration(ctx)
	if err != nil {
		t.Fatalf("ProjectionGeneration after fail: %v", err)
	}
	if genAfterFail != genBeforeFail {
		t.Fatalf("ProjectionGeneration changed on rejected CreateToken: got %d want %d", genAfterFail, genBeforeFail)
	}

	listAfterFail, err := store.ListTokens(ctx, issuedP.Principal.ID)
	if err != nil {
		t.Fatalf("ListTokens after fail: %v", err)
	}
	if len(listAfterFail) != countBeforeFail {
		t.Fatalf("ListTokens count changed on rejected CreateToken: got %d want %d", len(listAfterFail), countBeforeFail)
	}
}

// Compile-time interface check.
var _ = (*Store)(nil)

// Ensure time import is used.
var _ = time.Now