package meta

import (
	"context"
	"errors"
	"sync"
	"testing"
	"time"

	mysql "github.com/go-sql-driver/mysql"

	"github.com/mem9-ai/drive9/pkg/tenant/token"
)

func TestListAPIKeysBasics(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)

	now := time.Now().UTC().Truncate(time.Second)
	past := now.Add(-time.Hour)
	future := now.Add(time.Hour)

	active := insertTestAPIKey(t, s, tenantID, "active", APIKeyActive, APIKeyScopeKindOwner, nil)
	disabled := insertTestAPIKey(t, s, tenantID, "disabled", APIKeyDisabled, APIKeyScopeKindOwner, nil)
	_ = insertTestAPIKey(t, s, tenantID, "revoked", APIKeyRevoked, APIKeyScopeKindOwner, nil)
	expired := insertTestAPIKey(t, s, tenantID, "expired", APIKeyActive, APIKeyScopeKindOwner, &past)
	_ = insertTestAPIKey(t, s, tenantID, "future", APIKeyActive, APIKeyScopeKindOwner, &future)

	// Default: non-expired active/disabled only.
	rows, err := s.ListAPIKeys(ctx, tenantID, false, 0, 50)
	if err != nil {
		t.Fatal(err)
	}
	got := map[string]bool{}
	for _, r := range rows {
		got[r.ID] = true
		if r.Status == APIKeyRevoked {
			t.Fatalf("list returned revoked key %s", r.ID)
		}
	}
	if !got[active.ID] || !got[disabled.ID] {
		t.Fatalf("missing active/disabled: %+v", got)
	}
	if got[expired.ID] {
		t.Fatal("default list should exclude expired")
	}

	// includeExpired includes expired rows still active/disabled.
	rows2, err := s.ListAPIKeys(ctx, tenantID, true, 0, 50)
	if err != nil {
		t.Fatal(err)
	}
	got2 := map[string]bool{}
	for _, r := range rows2 {
		got2[r.ID] = true
	}
	if !got2[expired.ID] {
		t.Fatal("includeExpired should include expired key")
	}

	// Pagination: limit 1 returns next page.
	page1, err := s.ListAPIKeys(ctx, tenantID, true, 0, 1)
	if err != nil {
		t.Fatal(err)
	}
	if len(page1) != 1 {
		t.Fatalf("page1 len=%d", len(page1))
	}
	page2, err := s.ListAPIKeys(ctx, tenantID, true, 1, 1)
	if err != nil {
		t.Fatal(err)
	}
	if len(page2) != 1 {
		t.Fatalf("page2 len=%d", len(page2))
	}
	if page1[0].ID == page2[0].ID {
		t.Fatal("pagination returned same row")
	}
}

func TestSetAPIKeyStatusBasics(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	key := insertTestAPIKey(t, s, tenantID, "k", APIKeyActive, APIKeyScopeKindOwner, nil)

	// active → disabled
	cur, changed, err := s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyActive, APIKeyDisabled)
	if err != nil {
		t.Fatal(err)
	}
	if cur != APIKeyDisabled || !changed {
		t.Fatalf("cur=%s changed=%v", cur, changed)
	}

	// Idempotent replay: from=active (current is disabled) → current==to → changed=false
	cur, changed, err = s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyActive, APIKeyDisabled)
	if err != nil {
		t.Fatal(err)
	}
	if cur != APIKeyDisabled || changed {
		t.Fatalf("idempotent cur=%s changed=%v", cur, changed)
	}

	// disabled → active
	cur, changed, err = s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyDisabled, APIKeyActive)
	if err != nil {
		t.Fatal(err)
	}
	if cur != APIKeyActive || !changed {
		t.Fatalf("reactivate cur=%s changed=%v", cur, changed)
	}

	// from==to rejected
	if _, _, err := s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyActive, APIKeyActive); err == nil {
		t.Fatal("expected from==to error")
	}
	// revoked not allowed as to/from
	if _, _, err := s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyActive, APIKeyRevoked); err == nil {
		t.Fatal("expected to=revoked error")
	}

	// Terminal after revoke
	if err := s.RevokeAPIKey(ctx, tenantID, key.ID); err != nil {
		t.Fatal(err)
	}
	if _, _, err := s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyDisabled, APIKeyActive); !errors.Is(err, ErrAPIKeyTerminal) {
		t.Fatalf("want ErrAPIKeyTerminal, got %v", err)
	}

	// Missing
	if _, _, err := s.SetAPIKeyStatus(ctx, tenantID, "missing-id", APIKeyActive, APIKeyDisabled); !errors.Is(err, ErrNotFound) {
		t.Fatalf("want ErrNotFound, got %v", err)
	}
}

func TestInsertAPIKeyWithLimitBasics(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)

	// Fill to limit of 2.
	const limit = 2
	for i := 0; i < limit; i++ {
		k := newTestAPIKeyRec(tenantID, "k", APIKeyActive, APIKeyScopeKindOwner, nil)
		if err := s.InsertAPIKeyWithLimit(ctx, k, limit); err != nil {
			t.Fatalf("insert %d: %v", i, err)
		}
	}
	// Over limit
	over := newTestAPIKeyRec(tenantID, "over", APIKeyActive, APIKeyScopeKindOwner, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, over, limit); !errors.Is(err, ErrAPIKeyLimitExceeded) {
		t.Fatalf("want ErrAPIKeyLimitExceeded, got %v", err)
	}

	// Expired slots free capacity.
	past := time.Now().UTC().Add(-time.Hour)
	expired := newTestAPIKeyRec(tenantID, "expired", APIKeyActive, APIKeyScopeKindOwner, &past)
	// Insert via raw InsertAPIKey (bypasses limit) then count should exclude it...
	// Actually InsertAPIKeyWithLimit already at 2; revoke one and insert again.
	rows, err := s.ListAPIKeys(ctx, tenantID, true, 0, 10)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) < 1 {
		t.Fatal("expected rows")
	}
	if err := s.RevokeAPIKey(ctx, tenantID, rows[0].ID); err != nil {
		t.Fatal(err)
	}
	if err := s.InsertAPIKeyWithLimit(ctx, expired, limit); err != nil {
		t.Fatalf("insert after revoke: %v", err)
	}

	// fs_scoped requires expires_at
	scoped := newTestAPIKeyRec(tenantID, "scoped", APIKeyActive, APIKeyScopeKindFS, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, scoped, limit); err == nil {
		t.Fatal("fs_scoped without expires_at must fail")
	}
}

func TestInsertAPIKeyFSScopedRequiresExpiresAt(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)

	k := newTestAPIKeyRec(tenantID, "scoped", APIKeyActive, APIKeyScopeKindFS, nil)
	if err := s.InsertAPIKey(ctx, k); err == nil {
		t.Fatal("expected error for fs_scoped without expires_at")
	}
}

func TestRevokeAffectsDisabled(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	key := insertTestAPIKey(t, s, tenantID, "dis", APIKeyDisabled, APIKeyScopeKindOwner, nil)

	if err := s.RevokeAPIKey(ctx, tenantID, key.ID); err != nil {
		t.Fatal(err)
	}
	got, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	if got.Status != APIKeyRevoked {
		t.Fatalf("status=%s, want revoked", got.Status)
	}
	// Already revoked → ErrNotFound
	if err := s.RevokeAPIKey(ctx, tenantID, key.ID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("want ErrNotFound, got %v", err)
	}
}

func TestInsertAPIKeyWithLimitRequiresActiveTenant(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)

	// Flip tenant to deleting while holding no lock; insert must re-check under FOR UPDATE.
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenants SET status = ? WHERE id = ?`, TenantDeleting, tenantID); err != nil {
		t.Fatal(err)
	}
	k := newTestAPIKeyRec(tenantID, "after-delete", APIKeyActive, APIKeyScopeKindOwner, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, k, 10); !errors.Is(err, ErrTenantNotActive) {
		t.Fatalf("want ErrTenantNotActive, got %v", err)
	}
	// No key should have been inserted.
	rows, err := s.ListAPIKeys(ctx, tenantID, true, 0, 10)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 0 {
		t.Fatalf("expected no keys after rejected insert, got %d", len(rows))
	}
}

func TestRotateIssuerAPIKeyRejectsNonIssuableTenant(t *testing.T) {
	for _, status := range []TenantStatus{TenantDeleting, TenantDeleted, TenantSuspended, TenantFailed, TenantPending} {
		t.Run(string(status), func(t *testing.T) {
			s, cleanup := newTestStore(t)
			defer cleanup()
			ctx := context.Background()
			tenantID := insertTestTenant(t, s)
			existing := insertTestAPIKey(t, s, tenantID, "issuer", APIKeyActive, APIKeyScopeKindOwner, nil)
			if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
				"local", "sub", existing.ID); err != nil {
				t.Fatal(err)
			}
			if _, err := s.DB().ExecContext(ctx, `UPDATE tenants SET status = ? WHERE id = ?`, status, tenantID); err != nil {
				t.Fatal(err)
			}
			next := newTestAPIKeyRec(tenantID, "rotated", APIKeyActive, APIKeyScopeKindOwner, nil)
			next.IssuedByProvider = "local"
			next.IssuedBySubjectKey = "sub"
			if err := s.RotateIssuerAPIKey(ctx, tenantID, "local", "sub", next); !errors.Is(err, ErrTenantNotActive) {
				t.Fatalf("want ErrTenantNotActive, got %v", err)
			}
			got, err := s.GetAPIKey(ctx, tenantID, existing.ID)
			if err != nil {
				t.Fatal(err)
			}
			if got.Status != APIKeyActive {
				t.Fatalf("existing key status=%s, want still active (rotate aborted)", got.Status)
			}
		})
	}
}

func TestRotateIssuerAPIKeyAllowsProvisioningTenant(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	existing := insertTestAPIKey(t, s, tenantID, "issuer", APIKeyActive, APIKeyScopeKindOwner, nil)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"local", "sub", existing.ID); err != nil {
		t.Fatal(err)
	}
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenants SET status = ? WHERE id = ?`, TenantProvisioning, tenantID); err != nil {
		t.Fatal(err)
	}
	next := newTestAPIKeyRec(tenantID, "rotated", APIKeyActive, APIKeyScopeKindOwner, nil)
	next.IssuedByProvider = "local"
	next.IssuedBySubjectKey = "sub"
	if err := s.RotateIssuerAPIKey(ctx, tenantID, "local", "sub", next); err != nil {
		t.Fatalf("rotate on provisioning tenant: %v", err)
	}
	old, err := s.GetAPIKey(ctx, tenantID, existing.ID)
	if err != nil {
		t.Fatal(err)
	}
	if old.Status != APIKeyRevoked {
		t.Fatalf("old key status=%s, want revoked", old.Status)
	}
	got, err := s.GetActiveAPIKeyByIssuer(ctx, tenantID, "local", "sub")
	if err != nil {
		t.Fatal(err)
	}
	if got.ID != next.ID || got.Status != APIKeyActive {
		t.Fatalf("active issuer key = %+v, want id=%s active", got, next.ID)
	}
}

func TestInsertAPIKeyWithLimitRejectsProvisioningTenant(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenants SET status = ? WHERE id = ?`, TenantProvisioning, tenantID); err != nil {
		t.Fatal(err)
	}
	k := newTestAPIKeyRec(tenantID, "during-provision", APIKeyActive, APIKeyScopeKindOwner, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, k, 10); !errors.Is(err, ErrTenantNotActive) {
		t.Fatalf("want ErrTenantNotActive, got %v", err)
	}
}

func TestRotateIssuerAPIKeyRejectsTenantMismatch(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantA := insertTestTenant(t, s)
	tenantB := insertTestTenant(t, s)
	existingA := insertTestAPIKey(t, s, tenantA, "issuer-a", APIKeyActive, APIKeyScopeKindOwner, nil)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"local", "sub", existingA.ID); err != nil {
		t.Fatal(err)
	}
	// next claims tenant B while locking A.
	next := newTestAPIKeyRec(tenantB, "cross", APIKeyActive, APIKeyScopeKindOwner, nil)
	next.IssuedByProvider = "local"
	next.IssuedBySubjectKey = "sub"
	if err := s.RotateIssuerAPIKey(ctx, tenantA, "local", "sub", next); !errors.Is(err, ErrAPIKeyTenantMismatch) {
		t.Fatalf("want ErrAPIKeyTenantMismatch, got %v", err)
	}
	// Tenant A: existing issuer key still active, next not present.
	gotA, err := s.GetAPIKey(ctx, tenantA, existingA.ID)
	if err != nil {
		t.Fatal(err)
	}
	if gotA.Status != APIKeyActive {
		t.Fatalf("tenant A key status=%s, want active", gotA.Status)
	}
	if _, err := s.GetAPIKey(ctx, tenantA, next.ID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("next must not be inserted into A: %v", err)
	}
	// Tenant B: no key inserted.
	if _, err := s.GetAPIKey(ctx, tenantB, next.ID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("next must not be inserted into B: %v", err)
	}
	nA, err := s.CountActiveAPIKeys(ctx, tenantA)
	if err != nil {
		t.Fatal(err)
	}
	nB, err := s.CountActiveAPIKeys(ctx, tenantB)
	if err != nil {
		t.Fatal(err)
	}
	if nA != 1 || nB != 0 {
		t.Fatalf("counts A=%d B=%d, want 1 and 0", nA, nB)
	}
}

func TestRotateIssuerAPIKeyEmptyTenantIDUsesLockedTenant(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	existing := insertTestAPIKey(t, s, tenantID, "issuer", APIKeyActive, APIKeyScopeKindOwner, nil)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"local", "sub", existing.ID); err != nil {
		t.Fatal(err)
	}
	next := newTestAPIKeyRec(tenantID, "rotated", APIKeyActive, APIKeyScopeKindOwner, nil)
	next.TenantID = "" // force path: empty must land under locked tenantID
	next.IssuedByProvider = "local"
	next.IssuedBySubjectKey = "sub"
	if err := s.RotateIssuerAPIKey(ctx, tenantID, "local", "sub", next); err != nil {
		t.Fatalf("empty TenantID rotate: %v", err)
	}
	got, err := s.GetAPIKey(ctx, tenantID, next.ID)
	if err != nil {
		t.Fatalf("next must be inserted under locked tenant: %v", err)
	}
	if got.TenantID != tenantID {
		t.Fatalf("got.TenantID=%q, want %q", got.TenantID, tenantID)
	}
	if got.Status != APIKeyActive {
		t.Fatalf("status=%s, want active", got.Status)
	}
	// Caller pointer must not be mutated by the store.
	if next.TenantID != "" {
		t.Fatalf("store must not mutate caller's next.TenantID, got %q", next.TenantID)
	}
	// Previous issuer key revoked under the same tenant.
	old, err := s.GetAPIKey(ctx, tenantID, existing.ID)
	if err != nil {
		t.Fatal(err)
	}
	if old.Status != APIKeyRevoked {
		t.Fatalf("old issuer status=%s, want revoked", old.Status)
	}
}

func TestInsertAPIKeyWithLimitConcurrentCap(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	const limit = 100
	const n = 150
	results := make(chan error, n)
	var start sync.WaitGroup
	start.Add(1)
	var wg sync.WaitGroup
	for i := 0; i < n; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			start.Wait()
			k := newTestAPIKeyRec(tenantID, "c", APIKeyActive, APIKeyScopeKindOwner, nil)
			results <- s.InsertAPIKeyWithLimit(ctx, k, limit)
		}()
	}
	start.Done()
	wg.Wait()
	close(results)
	var ok, limited, other int
	for err := range results {
		switch {
		case err == nil:
			ok++
		case errors.Is(err, ErrAPIKeyLimitExceeded):
			limited++
		default:
			other++
			t.Errorf("unexpected error: %v", err)
		}
	}
	if ok != limit {
		t.Fatalf("successes=%d, want %d", ok, limit)
	}
	if limited != n-limit {
		t.Fatalf("limit exceeded=%d, want %d", limited, n-limit)
	}
	if other != 0 {
		t.Fatalf("other errors=%d", other)
	}
	count, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if count != limit {
		t.Fatalf("CountActiveAPIKeys=%d, want %d", count, limit)
	}
}

func TestRotateIssuerAPIKeyConcurrentWithGenerateNeverExceedsCap(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	// Fill to 99 non-issuer keys so room for one more.
	const fill = DefaultMaxActiveAPIKeys - 1
	for i := 0; i < fill; i++ {
		k := newTestAPIKeyRec(tenantID, "filler", APIKeyActive, APIKeyScopeKindOwner, nil)
		if err := s.InsertAPIKeyWithLimit(ctx, k, DefaultMaxActiveAPIKeys); err != nil {
			t.Fatalf("fill %d: %v", i, err)
		}
	}
	// Seed an issuer key that will be rotated (revoked in the same tx as insert).
	issuer := insertTestAPIKey(t, s, tenantID, "issuer", APIKeyActive, APIKeyScopeKindOwner, nil)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"local", "sub", issuer.ID); err != nil {
		t.Fatal(err)
	}
	// Now active count is 100. Concurrent generates + issuer rotates share the
	// tenant-row lock: each rotate revoke+insert is atomic, so generates never
	// observe a free slot and must hit the cap; every rotate should succeed.
	const nGen = 40
	const nRot = 40
	genErrs := make(chan error, nGen)
	rotErrs := make(chan error, nRot)
	var start sync.WaitGroup
	start.Add(1)
	var wg sync.WaitGroup
	for i := 0; i < nGen; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			start.Wait()
			k := newTestAPIKeyRec(tenantID, "gen", APIKeyActive, APIKeyScopeKindOwner, nil)
			genErrs <- s.InsertAPIKeyWithLimit(ctx, k, DefaultMaxActiveAPIKeys)
		}()
	}
	for i := 0; i < nRot; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			start.Wait()
			next := newTestAPIKeyRec(tenantID, "rotated", APIKeyActive, APIKeyScopeKindOwner, nil)
			next.IssuedByProvider = "local"
			next.IssuedBySubjectKey = "sub"
			rotErrs <- s.RotateIssuerAPIKey(ctx, tenantID, "local", "sub", next)
		}()
	}
	start.Done()
	wg.Wait()
	close(genErrs)
	close(rotErrs)

	var genOK, genLimited, genOther int
	for err := range genErrs {
		switch {
		case err == nil:
			genOK++
		case errors.Is(err, ErrAPIKeyLimitExceeded):
			genLimited++
		default:
			genOther++
			t.Errorf("generate unexpected error: %v", err)
		}
	}
	var rotOK, rotOther int
	for err := range rotErrs {
		switch err {
		case nil:
			rotOK++
		default:
			rotOther++
			t.Errorf("rotate unexpected error: %v", err)
		}
	}
	if genOther != 0 || rotOther != 0 {
		t.Fatalf("unexpected errors: genOther=%d rotOther=%d", genOther, rotOther)
	}
	if genOK != 0 {
		t.Fatalf("generate successes=%d, want 0 (slot never free outside rotate tx)", genOK)
	}
	if genLimited != nGen {
		t.Fatalf("generate limit exceeded=%d, want %d", genLimited, nGen)
	}
	if rotOK != nRot {
		t.Fatalf("rotate successes=%d, want %d", rotOK, nRot)
	}
	count, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if count != DefaultMaxActiveAPIKeys {
		t.Fatalf("CountActiveAPIKeys=%d, want %d (99 fillers + 1 issuer)", count, DefaultMaxActiveAPIKeys)
	}
}

// TestInsertAPIKeyWithLimitDeleteBeforeIssuanceHandshake forces the delete-first
// schedule: all issuers pause before the tenant-row lock, delete commits
// status=deleting + revoke, then issuers resume. Without the under-lock status
// check the inserts would succeed after delete; with it they must all fail closed.
func TestInsertAPIKeyWithLimitDeleteBeforeIssuanceHandshake(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)

	const n = 20
	var entered sync.WaitGroup
	entered.Add(n)
	deleteDone := make(chan struct{})
	testHookBeforeTenantKeyIssuanceLock = func(id string) {
		if id != tenantID {
			return
		}
		entered.Done()
		<-deleteDone
	}
	t.Cleanup(func() { testHookBeforeTenantKeyIssuanceLock = nil })

	errs := make(chan error, n)
	var wg sync.WaitGroup
	for i := 0; i < n; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			k := newTestAPIKeyRec(tenantID, "race", APIKeyActive, APIKeyScopeKindOwner, nil)
			errs <- s.InsertAPIKeyWithLimit(ctx, k, DefaultMaxActiveAPIKeys)
		}()
	}
	entered.Wait()
	// No insert has taken the lock yet; delete can fully complete.
	if err := s.UpdateTenantStatus(ctx, tenantID, TenantDeleting); err != nil {
		t.Fatal(err)
	}
	if err := s.RevokeTenantAPIKeys(ctx, tenantID); err != nil {
		t.Fatal(err)
	}
	close(deleteDone)
	wg.Wait()
	close(errs)
	for err := range errs {
		if !errors.Is(err, ErrTenantNotActive) {
			t.Errorf("want ErrTenantNotActive after delete, got %v", err)
		}
	}
	count, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if count != 0 {
		t.Fatalf("CountActiveAPIKeys=%d, want 0", count)
	}
}

// TestInsertAPIKeyWithLimitIssuanceBeforeDeleteHandshake forces insert-first:
// one insert holds the tenant lock (after status check), delete blocks on the
// row until insert commits, then delete revokes. Final active count is 0.
func TestInsertAPIKeyWithLimitIssuanceBeforeDeleteHandshake(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)

	held := make(chan struct{})
	release := make(chan struct{})
	testHookAfterTenantKeyIssuanceLock = func(id string) {
		if id != tenantID {
			return
		}
		close(held)
		<-release
	}
	t.Cleanup(func() { testHookAfterTenantKeyIssuanceLock = nil })

	insertErr := make(chan error, 1)
	go func() {
		k := newTestAPIKeyRec(tenantID, "first", APIKeyActive, APIKeyScopeKindOwner, nil)
		insertErr <- s.InsertAPIKeyWithLimit(ctx, k, DefaultMaxActiveAPIKeys)
	}()
	<-held

	deleteErr := make(chan error, 1)
	go func() {
		if err := s.UpdateTenantStatus(ctx, tenantID, TenantDeleting); err != nil {
			deleteErr <- err
			return
		}
		deleteErr <- s.RevokeTenantAPIKeys(ctx, tenantID)
	}()
	// Delete must not finish while the insert still holds the tenant row lock.
	select {
	case err := <-deleteErr:
		t.Fatalf("delete completed while issuance held tenant lock: %v", err)
	case <-time.After(150 * time.Millisecond):
	}
	close(release)
	if err := <-insertErr; err != nil {
		t.Fatalf("insert: %v", err)
	}
	if err := <-deleteErr; err != nil {
		t.Fatalf("delete: %v", err)
	}
	count, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if count != 0 {
		t.Fatalf("after delete CountActiveAPIKeys=%d, want 0", count)
	}
	// Post-delete insert still rejected.
	k := newTestAPIKeyRec(tenantID, "after", APIKeyActive, APIKeyScopeKindOwner, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, k, 10); !errors.Is(err, ErrTenantNotActive) {
		t.Fatalf("post-delete insert: want ErrTenantNotActive, got %v", err)
	}
}

func TestRotateIssuerAPIKeyDeleteBeforeIssuanceHandshake(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	existing := insertTestAPIKey(t, s, tenantID, "issuer", APIKeyActive, APIKeyScopeKindOwner, nil)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"local", "sub", existing.ID); err != nil {
		t.Fatal(err)
	}

	const n = 15
	var entered sync.WaitGroup
	entered.Add(n)
	deleteDone := make(chan struct{})
	testHookBeforeTenantKeyIssuanceLock = func(id string) {
		if id != tenantID {
			return
		}
		entered.Done()
		<-deleteDone
	}
	t.Cleanup(func() { testHookBeforeTenantKeyIssuanceLock = nil })

	errs := make(chan error, n)
	var wg sync.WaitGroup
	for i := 0; i < n; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			next := newTestAPIKeyRec(tenantID, "rot", APIKeyActive, APIKeyScopeKindOwner, nil)
			next.IssuedByProvider = "local"
			next.IssuedBySubjectKey = "sub"
			errs <- s.RotateIssuerAPIKey(ctx, tenantID, "local", "sub", next)
		}()
	}
	entered.Wait()
	if err := s.UpdateTenantStatus(ctx, tenantID, TenantDeleting); err != nil {
		t.Fatal(err)
	}
	if err := s.RevokeTenantAPIKeys(ctx, tenantID); err != nil {
		t.Fatal(err)
	}
	close(deleteDone)
	wg.Wait()
	close(errs)
	for err := range errs {
		if !errors.Is(err, ErrTenantNotActive) {
			t.Errorf("want ErrTenantNotActive after delete, got %v", err)
		}
	}
	count, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if count != 0 {
		t.Fatalf("CountActiveAPIKeys=%d, want 0", count)
	}
}

func TestRotateIssuerAPIKeyIssuanceBeforeDeleteHandshake(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	existing := insertTestAPIKey(t, s, tenantID, "issuer", APIKeyActive, APIKeyScopeKindOwner, nil)
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"local", "sub", existing.ID); err != nil {
		t.Fatal(err)
	}

	held := make(chan struct{})
	release := make(chan struct{})
	testHookAfterTenantKeyIssuanceLock = func(id string) {
		if id != tenantID {
			return
		}
		close(held)
		<-release
	}
	t.Cleanup(func() { testHookAfterTenantKeyIssuanceLock = nil })

	rotErr := make(chan error, 1)
	go func() {
		next := newTestAPIKeyRec(tenantID, "rot", APIKeyActive, APIKeyScopeKindOwner, nil)
		next.IssuedByProvider = "local"
		next.IssuedBySubjectKey = "sub"
		rotErr <- s.RotateIssuerAPIKey(ctx, tenantID, "local", "sub", next)
	}()
	<-held

	deleteErr := make(chan error, 1)
	go func() {
		if err := s.UpdateTenantStatus(ctx, tenantID, TenantDeleting); err != nil {
			deleteErr <- err
			return
		}
		deleteErr <- s.RevokeTenantAPIKeys(ctx, tenantID)
	}()
	select {
	case err := <-deleteErr:
		t.Fatalf("delete completed while rotate held tenant lock: %v", err)
	case <-time.After(150 * time.Millisecond):
	}
	close(release)
	if err := <-rotErr; err != nil {
		t.Fatalf("rotate: %v", err)
	}
	if err := <-deleteErr; err != nil {
		t.Fatalf("delete: %v", err)
	}
	count, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if count != 0 {
		t.Fatalf("after delete CountActiveAPIKeys=%d, want 0", count)
	}
}

func TestSetAPIKeyStatusActivateExpiredIsConflict(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	past := time.Now().UTC().Add(-time.Hour)
	key := insertTestAPIKey(t, s, tenantID, "expired-disabled", APIKeyDisabled, APIKeyScopeKindFS, &past)

	cur, changed, err := s.SetAPIKeyStatus(ctx, tenantID, key.ID, APIKeyDisabled, APIKeyActive)
	if !errors.Is(err, ErrAPIKeyExpired) {
		t.Fatalf("want ErrAPIKeyExpired, got %v (cur=%s changed=%v)", err, cur, changed)
	}
	got, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	if got.Status != APIKeyDisabled {
		t.Fatalf("status=%s, want still disabled", got.Status)
	}
}

func TestRotateAPIKeyRejectsUnchangedVersion(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	exp := time.Now().UTC().Add(time.Hour)
	key := insertTestAPIKey(t, s, tenantID, "rot", APIKeyActive, APIKeyScopeKindFS, &exp)

	before, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	// Same token_version as the optimistic lock: must not rewrite hash/ciphertext.
	next := *before
	next.JWTHash = "collision-hash"
	next.JWTCiphertext = []byte("collision-cipher")
	next.TokenVersion = before.TokenVersion
	next.ExpiresAt = &exp
	if err := s.RotateAPIKey(ctx, tenantID, key.ID, before.TokenVersion, &next); !errors.Is(err, ErrAPIKeyVersionUnchanged) {
		t.Fatalf("want ErrAPIKeyVersionUnchanged, got %v", err)
	}
	after, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	if after.JWTHash != before.JWTHash || after.TokenVersion != before.TokenVersion {
		t.Fatalf("same-version rotate mutated row: before hash=%s ver=%d after hash=%s ver=%d",
			before.JWTHash, before.TokenVersion, after.JWTHash, after.TokenVersion)
	}
}

func TestRotateAPIKeyRejectsRegressedVersion(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	exp := time.Now().UTC().Add(time.Hour)
	key := insertTestAPIKey(t, s, tenantID, "hist", APIKeyActive, APIKeyScopeKindFS, &exp)
	before, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	// Legitimate advance v → v+1.
	advanced := *before
	advanced.JWTHash = "advanced-hash"
	advanced.JWTCiphertext = []byte("advanced-cipher")
	advanced.TokenVersion = before.TokenVersion + 1
	advanced.ExpiresAt = &exp
	if err := s.RotateAPIKey(ctx, tenantID, key.ID, before.TokenVersion, &advanced); err != nil {
		t.Fatalf("legit advance: %v", err)
	}
	live, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	// Historical reuse: next.TokenVersion = original v while locking on live v+1.
	regressed := *live
	regressed.JWTHash = "historical-reuse"
	regressed.JWTCiphertext = []byte("historical-cipher")
	regressed.TokenVersion = before.TokenVersion // historical
	regressed.ExpiresAt = &exp
	if err := s.RotateAPIKey(ctx, tenantID, key.ID, live.TokenVersion, &regressed); !errors.Is(err, ErrAPIKeyVersionUnchanged) {
		t.Fatalf("want ErrAPIKeyVersionUnchanged for historical version, got %v", err)
	}
	after, err := s.GetAPIKey(ctx, tenantID, key.ID)
	if err != nil {
		t.Fatal(err)
	}
	if after.JWTHash != live.JWTHash || after.TokenVersion != live.TokenVersion {
		t.Fatalf("historical rotate mutated row: want hash/ver %s/%d got %s/%d",
			live.JWTHash, live.TokenVersion, after.JWTHash, after.TokenVersion)
	}
}

func TestCountActiveAPIKeysBasics(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	now := time.Now().UTC()
	past := now.Add(-time.Hour)

	_ = insertTestAPIKey(t, s, tenantID, "a", APIKeyActive, APIKeyScopeKindOwner, nil)
	_ = insertTestAPIKey(t, s, tenantID, "d", APIKeyDisabled, APIKeyScopeKindOwner, nil)
	_ = insertTestAPIKey(t, s, tenantID, "r", APIKeyRevoked, APIKeyScopeKindOwner, nil)
	_ = insertTestAPIKey(t, s, tenantID, "e", APIKeyActive, APIKeyScopeKindOwner, &past)
	// NULL owner expires_at counted; expired owner not counted
	n, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if n != 2 {
		t.Fatalf("count=%d, want 2 (active+disabled non-expired)", n)
	}

	// Legacy fs_scoped with expires_at NULL must NOT count (avoid lockout).
	// Bypass InsertAPIKey guard with a direct insert for the legacy shape.
	_, err = s.DB().ExecContext(ctx, `INSERT INTO tenant_api_keys
		(id, tenant_id, key_name, jwt_ciphertext, jwt_hash, token_version, status, scope_kind,
		 issued_at, created_at, updated_at)
		VALUES (?, ?, 'legacy', ?, ?, 1, ?, ?, ?, ?, ?)`,
		"legacy-scoped", tenantID, []byte("c"), "legacy-hash", APIKeyActive, APIKeyScopeKindFS,
		now, now, now)
	if err != nil {
		t.Fatal(err)
	}
	n2, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if n2 != 2 {
		t.Fatalf("count after legacy scoped=%d, want 2 (legacy NULL scoped excluded)", n2)
	}
}

func TestListAPIKeysOmitsSecretMaterial(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	k := insertTestAPIKey(t, s, tenantID, "sec", APIKeyActive, APIKeyScopeKindOwner, nil)
	// Ensure the row actually has ciphertext/hash in the table.
	if len(k.JWTCiphertext) == 0 || k.JWTHash == "" {
		t.Fatal("test fixture missing secret material")
	}
	rows, err := s.ListAPIKeys(ctx, tenantID, false, 0, 50)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 1 {
		t.Fatalf("rows=%d", len(rows))
	}
	if len(rows[0].JWTCiphertext) != 0 || rows[0].JWTHash != "" {
		t.Fatalf("list leaked secret material: ciphertext=%d hash=%q", len(rows[0].JWTCiphertext), rows[0].JWTHash)
	}
}

func TestListAPIKeysAllowsOrgWideTruncationProbe(t *testing.T) {
	// Org-wide list needs limit=501 (500+1) to detect the design's per-tenant
	// truncation. Store max must not silently clamp that probe to 200.
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()
	tenantID := insertTestTenant(t, s)
	// Insert 3 keys; request limit 501 and ensure we get all 3 (not clamped to 0).
	for i := 0; i < 3; i++ {
		_ = insertTestAPIKey(t, s, tenantID, "k"+string(rune('a'+i)), APIKeyActive, APIKeyScopeKindOwner, nil)
	}
	rows, err := s.ListAPIKeys(ctx, tenantID, false, 0, 501)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 3 {
		t.Fatalf("list with limit 501 got %d rows, want 3", len(rows))
	}
	// Over-cap is clamped to 501, not rejected.
	rows2, err := s.ListAPIKeys(ctx, tenantID, false, 0, 1000)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows2) != 3 {
		t.Fatalf("list with limit 1000 got %d rows, want 3", len(rows2))
	}
}

// TestAPIKeyExpiryPredicatesUnderNonUTCSession proves list/count/issuer filters
// use UTC wall time (UTC_TIMESTAMP), not session-local NOW(). With session
// time_zone = +08:00, a key expiring only one hour in UTC future is still in
// the past relative to session NOW(); a NOW(3) predicate would exclude it.
func TestAPIKeyExpiryPredicatesUnderNonUTCSession(t *testing.T) {
	s, cleanup := newTestStore(t)
	defer cleanup()
	ctx := context.Background()

	// Pin the pool to one connection so SET time_zone applies to all store ops.
	db := s.DB()
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	if _, err := db.ExecContext(ctx, `SET time_zone = '+08:00'`); err != nil {
		t.Fatalf("SET time_zone: %v", err)
	}
	var sessionTZ string
	if err := db.QueryRowContext(ctx, `SELECT @@session.time_zone`).Scan(&sessionTZ); err != nil {
		t.Fatal(err)
	}
	if sessionTZ != "+08:00" {
		t.Fatalf("session time_zone=%q, want +08:00", sessionTZ)
	}

	tenantID := insertTestTenant(t, s)
	// Use second truncation to match DATETIME(3) binding stability.
	now := time.Now().UTC().Truncate(time.Second)
	// 1h future UTC is still "past" relative to NOW() in +08:00 (8h ahead).
	futureUTC := now.Add(time.Hour)
	pastUTC := now.Add(-time.Hour)

	futureKey := insertTestAPIKey(t, s, tenantID, "future-utc", APIKeyActive, APIKeyScopeKindOwner, &futureUTC)
	if _, err := db.ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"tz", "future", futureKey.ID); err != nil {
		t.Fatal(err)
	}
	pastKey := insertTestAPIKey(t, s, tenantID, "past-utc", APIKeyActive, APIKeyScopeKindOwner, &pastUTC)
	if _, err := db.ExecContext(ctx, `UPDATE tenant_api_keys SET issued_by_provider = ?, issued_by_subject_key = ? WHERE id = ?`,
		"tz", "past", pastKey.ID); err != nil {
		t.Fatal(err)
	}
	// Sanity: session NOW is ahead of the UTC future expiry wall clock.
	var nowSession, futureWall string
	if err := db.QueryRowContext(ctx, `SELECT DATE_FORMAT(NOW(3), '%Y-%m-%d %H:%i:%s'), DATE_FORMAT(?, '%Y-%m-%d %H:%i:%s')`, futureUTC).Scan(&nowSession, &futureWall); err != nil {
		t.Fatal(err)
	}
	if nowSession <= futureWall {
		t.Fatalf("session NOW(%s) should be after future UTC wall(%s) under +08:00 so NOW(3) would false-expire", nowSession, futureWall)
	}

	// List: future visible, past excluded.
	rows, err := s.ListAPIKeys(ctx, tenantID, false, 0, 50)
	if err != nil {
		t.Fatal(err)
	}
	got := map[string]bool{}
	for _, r := range rows {
		got[r.ID] = true
	}
	if !got[futureKey.ID] {
		t.Fatal("ListAPIKeys excluded UTC-future key under non-UTC session (predicate likely uses NOW(3))")
	}
	if got[pastKey.ID] {
		t.Fatal("ListAPIKeys included UTC-past key")
	}

	// CountActiveAPIKeys: only the future key.
	n, err := s.CountActiveAPIKeys(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if n != 1 {
		t.Fatalf("CountActiveAPIKeys=%d, want 1 (future only)", n)
	}

	// GetActiveAPIKeyByIssuer: future resolvable, past not.
	issuer, err := s.GetActiveAPIKeyByIssuer(ctx, tenantID, "tz", "future")
	if err != nil {
		t.Fatalf("GetActiveAPIKeyByIssuer future: %v", err)
	}
	if issuer.ID != futureKey.ID {
		t.Fatalf("issuer id=%s, want %s", issuer.ID, futureKey.ID)
	}
	if _, err := s.GetActiveAPIKeyByIssuer(ctx, tenantID, "tz", "past"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("past issuer: want ErrNotFound, got %v", err)
	}

	// Insert-with-limit tx count path: room for one more under cap=2 (future already counted).
	extra := newTestAPIKeyRec(tenantID, "extra", APIKeyActive, APIKeyScopeKindOwner, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, extra, 2); err != nil {
		t.Fatalf("InsertAPIKeyWithLimit at cap room: %v", err)
	}
	// Cap filled (future + extra); another insert must fail.
	blocked := newTestAPIKeyRec(tenantID, "blocked", APIKeyActive, APIKeyScopeKindOwner, nil)
	if err := s.InsertAPIKeyWithLimit(ctx, blocked, 2); !errors.Is(err, ErrAPIKeyLimitExceeded) {
		t.Fatalf("want ErrAPIKeyLimitExceeded, got %v", err)
	}
}

// TestAPIKeyExpiryWriteUsesUTCWallClockDespiteNonUTCDSNLoc proves metadb opens
// force loc=UTC (and session UTC) so a Shanghai-loc DSN still stores UTC wall
// clock into expires_at (matching UTC_TIMESTAMP reads).
func TestAPIKeyExpiryWriteUsesUTCWallClockDespiteNonUTCDSNLoc(t *testing.T) {
	if testDSN == "" {
		t.Skip("no test database available")
	}
	// Build a DSN that would shift wall clocks without the meta time contract.
	cfg, err := mysql.ParseDSN(testDSN)
	if err != nil {
		t.Fatalf("ParseDSN: %v", err)
	}
	loc, err := time.LoadLocation("Asia/Shanghai")
	if err != nil {
		t.Fatal(err)
	}
	cfg.Loc = loc
	cfg.ParseTime = true
	if cfg.Params == nil {
		cfg.Params = make(map[string]string)
	}
	// Would also shift CURRENT_TIMESTAMP if the meta contract did not override it.
	cfg.Params["time_zone"] = "'+08:00'"
	shanghaiDSN := cfg.FormatDSN()

	s, err := Open(shanghaiDSN)
	if err != nil {
		t.Fatalf("Open with loc=Asia/Shanghai: %v", err)
	}
	t.Cleanup(func() {
		_, _ = s.DB().Exec("DELETE FROM tenant_api_key_fs_scopes")
		_, _ = s.DB().Exec("DELETE FROM tenant_api_keys")
		_, _ = s.DB().Exec("DELETE FROM tenants")
		_ = s.Close()
	})
	_, _ = s.DB().Exec("DELETE FROM tenant_api_key_fs_scopes")
	_, _ = s.DB().Exec("DELETE FROM tenant_api_keys")
	_, _ = s.DB().Exec("DELETE FROM tenants")

	ctx := context.Background()
	var sessionTZ string
	if err := s.DB().QueryRowContext(ctx, `SELECT @@session.time_zone`).Scan(&sessionTZ); err != nil {
		t.Fatal(err)
	}
	if sessionTZ != "+00:00" {
		t.Fatalf("meta session time_zone=%q, want +00:00 (contract must override DSN time_zone=+08:00)", sessionTZ)
	}

	tenantID := insertTestTenant(t, s)
	// Relative future instant (not a hard-coded calendar year).
	expires := time.Now().UTC().Add(24 * time.Hour).Truncate(time.Second)
	key := insertTestAPIKey(t, s, tenantID, "loc-utc", APIKeyActive, APIKeyScopeKindOwner, &expires)

	var stored string
	if err := s.DB().QueryRowContext(ctx,
		`SELECT DATE_FORMAT(expires_at, '%Y-%m-%d %H:%i:%s') FROM tenant_api_keys WHERE id = ?`, key.ID,
	).Scan(&stored); err != nil {
		t.Fatal(err)
	}
	want := expires.Format("2006-01-02 15:04:05")
	if stored != want {
		t.Fatalf("stored expires_at wall=%q, want UTC %q (DSN loc was Asia/Shanghai; non-UTC loc would shift the wall clock)", stored, want)
	}

	// Still resolvable via UTC_TIMESTAMP predicates.
	rows, err := s.ListAPIKeys(ctx, tenantID, false, 0, 10)
	if err != nil {
		t.Fatal(err)
	}
	found := false
	for _, r := range rows {
		if r.ID == key.ID {
			found = true
			break
		}
	}
	if !found {
		t.Fatal("ListAPIKeys missing key written under non-UTC DSN loc")
	}

	// CURRENT_TIMESTAMP under the forced UTC session matches UTC_TIMESTAMP wall
	// (mixed loc/time_zone cannot silently diverge on metadb).
	var curWall, utcWall string
	if err := s.DB().QueryRowContext(ctx,
		`SELECT DATE_FORMAT(CURRENT_TIMESTAMP(3), '%Y-%m-%d %H:%i:%s'), DATE_FORMAT(UTC_TIMESTAMP(3), '%Y-%m-%d %H:%i:%s')`,
	).Scan(&curWall, &utcWall); err != nil {
		t.Fatal(err)
	}
	if curWall != utcWall {
		t.Fatalf("CURRENT_TIMESTAMP wall=%q UTC_TIMESTAMP wall=%q; meta session must be UTC", curWall, utcWall)
	}
}

// --- helpers ---

func newTestStore(t *testing.T) (*Store, func()) {
	t.Helper()
	if testDSN == "" {
		t.Skip("no test database available")
	}
	s, err := Open(testDSN)
	if err != nil {
		t.Fatal(err)
	}
	_, _ = s.DB().Exec("DELETE FROM tenant_api_key_fs_scopes")
	_, _ = s.DB().Exec("DELETE FROM tenant_api_keys")
	_, _ = s.DB().Exec("DELETE FROM tenants")
	_, _ = s.DB().Exec("DELETE FROM tenant_extract_configs")
	return s, func() {
		_, _ = s.DB().Exec("DELETE FROM tenant_api_key_fs_scopes")
		_, _ = s.DB().Exec("DELETE FROM tenant_api_keys")
		_, _ = s.DB().Exec("DELETE FROM tenants")
		_, _ = s.DB().Exec("DELETE FROM tenant_extract_configs")
		_ = s.Close()
	}
}

func insertTestTenant(t *testing.T, s *Store) string {
	t.Helper()
	id := token.NewID()
	now := time.Now().UTC()
	if err := s.InsertTenant(context.Background(), &Tenant{
		ID:               id,
		Status:           TenantActive,
		DBHost:           "127.0.0.1",
		DBPort:           3306,
		DBUser:           "u",
		DBPasswordCipher: []byte("x"),
		DBName:           "db",
		Provider:         "db9",
		SchemaVersion:    1,
		CreatedAt:        now,
		UpdatedAt:        now,
	}); err != nil {
		t.Fatal(err)
	}
	return id
}

func newTestAPIKeyRec(tenantID, name string, status APIKeyStatus, kind APIKeyScopeKind, expiresAt *time.Time) *APIKey {
	now := time.Now().UTC()
	return &APIKey{
		ID:            token.NewID(),
		TenantID:      tenantID,
		KeyName:       name,
		JWTCiphertext: []byte("cipher-" + token.NewID()),
		JWTHash:       token.HashToken("raw-" + token.NewID()),
		TokenVersion:  1,
		Status:        status,
		ScopeKind:     kind,
		IssuedAt:      now,
		ExpiresAt:     expiresAt,
		CreatedAt:     now,
		UpdatedAt:     now,
	}
}

func insertTestAPIKey(t *testing.T, s *Store, tenantID, name string, status APIKeyStatus, kind APIKeyScopeKind, expiresAt *time.Time) *APIKey {
	t.Helper()
	// InsertAPIKey accepts the given status directly (active/disabled/revoked).
	k := newTestAPIKeyRec(tenantID, name, status, kind, expiresAt)
	if err := s.InsertAPIKey(context.Background(), k); err != nil {
		t.Fatal(err)
	}
	return k
}