package meta

import (
	"context"
	"errors"
	"strings"
	"testing"
	"time"
)

func TestFreeTenantPoolMembershipCountPinsMembershipTableFirst(t *testing.T) {
	if !strings.Contains(countFreeTenantPoolMembershipsSQL, "FROM tenant_pool_memberships m\n\tSTRAIGHT_JOIN tenants t") {
		t.Fatalf("count query does not pin tenant_pool_memberships as the driving table: %s", countFreeTenantPoolMembershipsSQL)
	}
}

func TestFreeTenantPoolBindingCountCombinesNativeAndSharedInventory(t *testing.T) {
	if strings.Count(countFreeTenantPoolBindingsSQL, "UNION ALL") != 1 {
		t.Fatalf("count query must combine native and shared inventory in one statement: %s", countFreeTenantPoolBindingsSQL)
	}
	for _, want := range []string{
		"FROM tenant_tidbcloud_org_bindings b\n\t\tSTRAIGHT_JOIN tenants t",
		"FROM tenant_pool_memberships m\n\t\tSTRAIGHT_JOIN tenants t",
	} {
		if !strings.Contains(countFreeTenantPoolBindingsSQL, want) {
			t.Fatalf("count query missing pinned branch %q: %s", want, countFreeTenantPoolBindingsSQL)
		}
	}
}

func TestTenantPoolMembershipRoundTripAndClaimCAS(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	insertTenantForPoolMembershipTest(t, s, "shared-member-1", TenantActive, now)

	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID:                "shared-member-1",
		TiDBCloudOrganizationID: "org-1",
		PoolID:                  "pool-1",
		PoolStatus:              TenantPoolBindingFree,
		CreatedAt:               now,
		UpdatedAt:               now,
	}); err != nil {
		t.Fatalf("UpsertTenantPoolMembership: %v", err)
	}

	got, err := s.GetTenantPoolMembership(ctx, "shared-member-1")
	if err != nil {
		t.Fatalf("GetTenantPoolMembership: %v", err)
	}
	if got.TenantID != "shared-member-1" || got.TiDBCloudOrganizationID != "org-1" || got.PoolID != "pool-1" || got.PoolStatus != TenantPoolBindingFree || got.UsedAt != nil {
		t.Fatalf("membership = %+v", got)
	}
	conflict := *got
	conflict.TiDBCloudOrganizationID = "org-2"
	if err := s.UpsertTenantPoolMembership(ctx, &conflict); err == nil {
		t.Fatal("conflicting organization update succeeded")
	}
	got, err = s.GetTenantPoolMembership(ctx, "shared-member-1")
	if err != nil {
		t.Fatalf("GetTenantPoolMembership after conflict: %v", err)
	}
	if got.TiDBCloudOrganizationID != "org-1" {
		t.Fatalf("organization after conflict = %q, want org-1", got.TiDBCloudOrganizationID)
	}

	claimedAt := now.Add(time.Second)
	claimed, err := s.ClaimTenantPoolMembership(ctx, "shared-member-1", "pool-1", claimedAt)
	if err != nil {
		t.Fatalf("ClaimTenantPoolMembership: %v", err)
	}
	if !claimed {
		t.Fatal("first claim lost CAS")
	}
	claimed, err = s.ClaimTenantPoolMembership(ctx, "shared-member-1", "pool-1", claimedAt.Add(time.Second))
	if err != nil {
		t.Fatalf("second ClaimTenantPoolMembership: %v", err)
	}
	if claimed {
		t.Fatal("second claim won CAS")
	}
	got, err = s.GetTenantPoolMembership(ctx, "shared-member-1")
	if err != nil {
		t.Fatalf("GetTenantPoolMembership after claim: %v", err)
	}
	if got.PoolStatus != TenantPoolBindingUsed || got.UsedAt == nil || !got.UsedAt.Equal(claimedAt) {
		t.Fatalf("claimed membership = %+v", got)
	}
}

func TestClaimTenantPoolMembershipRefusesDeletingTenant(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	const tenantID = "shared-member-deleting"
	insertTenantForPoolMembershipTest(t, s, tenantID, TenantDeleting, now)
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: tenantID, TiDBCloudOrganizationID: "org-deleting", PoolID: "pool-deleting",
		PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatal(err)
	}
	claimed, err := s.ClaimTenantPoolMembership(ctx, tenantID, "pool-deleting", now)
	if err != nil {
		t.Fatal(err)
	}
	if claimed {
		t.Fatal("deleting tenant membership must not be claimable")
	}
	membership, err := s.GetTenantPoolMembership(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if membership.PoolStatus != TenantPoolBindingFree {
		t.Fatalf("membership status = %s, want %s", membership.PoolStatus, TenantPoolBindingFree)
	}
}

func TestHandoffFreeSharedTenantDeleteJob(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	const tenantID = "shared-delete-handoff"
	fsID, ns := seedFreeSharedDeleteHandoff(t, s, tenantID, now)
	if err := s.EnqueueTenantDeleteJob(ctx, &TenantDeleteJob{
		TenantID: tenantID, NamespaceID: ns.ID, Backend: ns.Backend, Bucket: ns.Bucket,
		Prefix: ns.Prefix, NotBefore: now.Add(time.Hour),
	}); err != nil {
		t.Fatal(err)
	}

	handedOff, err := s.HandoffFreeSharedTenantDeleteJob(ctx, tenantID, fsID)
	if err != nil || !handedOff {
		t.Fatalf("HandoffFreeSharedTenantDeleteJob: handedOff=%v err=%v", handedOff, err)
	}
	if _, err := s.GetTenantPoolMembership(ctx, tenantID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("membership after handoff = %v, want ErrNotFound", err)
	}
	armed, err := s.TenantDeleteJobArmed(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if !armed {
		t.Fatal("tenant delete job was not made immediately eligible")
	}
	placement, err := s.GetTenantPlacement(ctx, fsID)
	if err != nil {
		t.Fatal(err)
	}
	if placement.Status != PlacementStatusDeleting {
		t.Fatalf("placement status = %s, want %s", placement.Status, PlacementStatusDeleting)
	}
}

func TestHandoffFreeSharedTenantDeleteJobPreservesTerminalState(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	const tenantID = "shared-delete-handoff-terminal"
	fsID, ns := seedFreeSharedDeleteHandoff(t, s, tenantID, now)
	if err := s.EnqueueTenantDeleteJob(ctx, &TenantDeleteJob{
		TenantID: tenantID, NamespaceID: ns.ID, Backend: ns.Backend, Bucket: ns.Bucket,
		Prefix: ns.Prefix,
	}); err != nil {
		t.Fatal(err)
	}
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenants SET status = ? WHERE id = ?`,
		TenantDeleted, tenantID); err != nil {
		t.Fatal(err)
	}
	if err := s.UpdateStorageNamespaceState(ctx, ns.ID, StorageNamespaceDeleted); err != nil {
		t.Fatal(err)
	}
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_delete_jobs
		SET state = ?, completed_at = ? WHERE tenant_id = ?`, TenantDeleteJobDeleted, now, tenantID); err != nil {
		t.Fatal(err)
	}

	handedOff, err := s.HandoffFreeSharedTenantDeleteJob(ctx, tenantID, fsID)
	if err != nil {
		t.Fatal(err)
	}
	if handedOff {
		t.Fatal("terminal tenant delete unexpectedly handed off again")
	}
	if _, err := s.GetTenantPoolMembership(ctx, tenantID); err != nil {
		t.Fatalf("terminal tenant membership changed: %v", err)
	}
	tenantRow, err := s.GetTenant(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	if tenantRow.Status != TenantDeleted {
		t.Fatalf("tenant status = %s, want %s", tenantRow.Status, TenantDeleted)
	}
	ns, err = s.GetStorageNamespace(ctx, ns.ID)
	if err != nil {
		t.Fatal(err)
	}
	if ns.State != StorageNamespaceDeleted {
		t.Fatalf("namespace state = %s, want %s", ns.State, StorageNamespaceDeleted)
	}
	var jobState TenantDeleteJobState
	if err := s.DB().QueryRowContext(ctx, `SELECT state FROM tenant_delete_jobs WHERE tenant_id = ?`,
		tenantID).Scan(&jobState); err != nil {
		t.Fatal(err)
	}
	if jobState != TenantDeleteJobDeleted {
		t.Fatalf("job state = %s, want %s", jobState, TenantDeleteJobDeleted)
	}
}

func seedFreeSharedDeleteHandoff(t *testing.T, s *Store, tenantID string, now time.Time) (int64, *StorageNamespace) {
	t.Helper()
	ctx := context.Background()
	insertTenantForPoolMembershipTest(t, s, tenantID, TenantDeleting, now)
	fsID, err := s.EnsureFsID(ctx, tenantID)
	if err != nil {
		t.Fatal(err)
	}
	dbID, err := s.RegisterSharedDB(ctx, &SharedDB{
		TiDBCloudOrganizationID: "org-" + tenantID, Host: "shared.example.com", Port: 4000,
		User: "root", PasswordCipher: []byte("cipher"), Name: tenantID, MaxTenants: 100,
	})
	if err != nil {
		t.Fatal(err)
	}
	if err := s.UpsertTenantPlacement(ctx, &TenantPlacement{
		FsID: fsID, DbID: dbID, Placement: PlacementShared, SchemaShape: SchemaShapeShared,
	}); err != nil {
		t.Fatal(err)
	}
	if _, err := s.DB().ExecContext(ctx, `UPDATE tenant_placements SET status = ? WHERE fs_id = ?`,
		PlacementStatusDeleting, fsID); err != nil {
		t.Fatal(err)
	}
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: tenantID, TiDBCloudOrganizationID: "org-" + tenantID,
		PoolID: "pool-" + tenantID, PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatal(err)
	}
	ns, err := s.EnsureTenantStorageNamespace(ctx, tenantID, "local", "", tenantID+"/")
	if err != nil {
		t.Fatal(err)
	}
	if err := s.UpdateStorageNamespaceState(ctx, ns.ID, StorageNamespaceDeleting); err != nil {
		t.Fatal(err)
	}
	return fsID, ns
}

func TestOldestFreeTenantPoolMembershipRequiresActiveTenant(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	insertTenantForPoolMembershipTest(t, s, "shared-member-provisioning", TenantProvisioning, now)
	insertTenantForPoolMembershipTest(t, s, "shared-member-active", TenantActive, now.Add(time.Second))
	for i, tenantID := range []string{"shared-member-provisioning", "shared-member-active"} {
		createdAt := now.Add(time.Duration(i) * time.Second)
		if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
			TenantID: tenantID, TiDBCloudOrganizationID: "org-1", PoolID: "pool-1",
			PoolStatus: TenantPoolBindingFree, CreatedAt: createdAt, UpdatedAt: createdAt,
		}); err != nil {
			t.Fatalf("UpsertTenantPoolMembership(%s): %v", tenantID, err)
		}
	}

	got, err := s.GetOldestFreeTenantPoolMembership(ctx, "pool-1")
	if err != nil {
		t.Fatalf("GetOldestFreeTenantPoolMembership: %v", err)
	}
	if got.Tenant.ID != "shared-member-active" {
		t.Fatalf("oldest eligible tenant = %q, want shared-member-active", got.Tenant.ID)
	}
}

func TestTenantPoolMembershipRejectsNativeTenant(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	if err := s.InsertTenant(ctx, &Tenant{
		ID: "native-member-rejected", Status: TenantActive, Provider: tidbCloudNativeProvider,
		DBPasswordCipher: []byte{}, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant: %v", err)
	}
	err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: "native-member-rejected", TiDBCloudOrganizationID: "org-1", PoolID: "pool-1",
		PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	})
	if err == nil {
		t.Fatal("native tenant was inserted into shared membership table")
	}
	if _, getErr := s.GetTenantPoolMembership(ctx, "native-member-rejected"); !errors.Is(getErr, ErrNotFound) {
		t.Fatalf("membership after rejected write = %v, want ErrNotFound", getErr)
	}
}

func TestUpdateTenantPoolMembershipOrganizationFillsOnce(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	insertTenantForPoolMembershipTest(t, s, "shared-member-org-fill", TenantProvisioning, now)
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: "shared-member-org-fill", PoolID: "pool-org-fill",
		PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantPoolMembership: %v", err)
	}

	if err := s.UpdateTenantPoolMembershipOrganization(ctx, "pool-org-fill", "org-1"); err != nil {
		t.Fatalf("initial organization fill: %v", err)
	}
	got, err := s.GetTenantPoolMembership(ctx, "shared-member-org-fill")
	if err != nil {
		t.Fatalf("GetTenantPoolMembership after fill: %v", err)
	}
	if got.TiDBCloudOrganizationID != "org-1" {
		t.Fatalf("organization after fill = %q, want org-1", got.TiDBCloudOrganizationID)
	}

	if err := s.UpdateTenantPoolMembershipOrganization(ctx, "pool-org-fill", "org-2"); err == nil {
		t.Fatal("conflicting organization fill succeeded")
	}
	got, err = s.GetTenantPoolMembership(ctx, "shared-member-org-fill")
	if err != nil {
		t.Fatalf("GetTenantPoolMembership after conflict: %v", err)
	}
	if got.TiDBCloudOrganizationID != "org-1" {
		t.Fatalf("organization after conflict = %q, want org-1", got.TiDBCloudOrganizationID)
	}
}

func TestDeleteTenantPoolAndDetachUsedMembersHandlesBothProviders(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	if err := s.CreateTenantPool(ctx, &TenantPool{
		PoolID: "pool-delete-mixed", OrganizationID: "org-delete-mixed", Size: 2,
		Status: TenantPoolDeleting, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("CreateTenantPool: %v", err)
	}
	if err := s.InsertTenant(ctx, &Tenant{
		ID: "native-used", Status: TenantActive, Provider: tidbCloudNativeProvider,
		DBPasswordCipher: []byte{}, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant native-used: %v", err)
	}
	if err := s.UpsertTenantTiDBCloudOrgBinding(ctx, &TenantTiDBCloudOrgBinding{
		TenantID: "native-used", OrganizationID: "org-delete-mixed", ClusterID: "cluster-native-used",
		PoolID: "pool-delete-mixed", PoolStatus: TenantPoolBindingUsed, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantTiDBCloudOrgBinding: %v", err)
	}
	insertTenantForPoolMembershipTest(t, s, "shared-used", TenantActive, now)
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: "shared-used", TiDBCloudOrganizationID: "org-delete-mixed",
		PoolID: "pool-delete-mixed", PoolStatus: TenantPoolBindingUsed, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantPoolMembership: %v", err)
	}
	insertTenantForPoolMembershipTest(t, s, "shared-free", TenantActive, now)
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: "shared-free", TiDBCloudOrganizationID: "org-delete-mixed",
		PoolID: "pool-delete-mixed", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantPoolMembership shared-free: %v", err)
	}

	if err := s.DeleteTenantPoolAndDetachUsedMembers(ctx, "pool-delete-mixed"); err == nil {
		t.Fatal("pool deletion succeeded while a free member remained")
	}
	pool, err := s.GetTenantPoolByID(ctx, "pool-delete-mixed")
	if err != nil || pool.PoolID != "pool-delete-mixed" {
		t.Fatalf("pool after rejected delete = %+v, %v", pool, err)
	}
	nativeBinding, err := s.GetTenantTiDBCloudOrgBinding(ctx, "native-used")
	if err != nil {
		t.Fatalf("GetTenantTiDBCloudOrgBinding after rejected delete: %v", err)
	}
	if nativeBinding.PoolID != "pool-delete-mixed" {
		t.Fatalf("native binding detached by rejected delete: %+v", nativeBinding)
	}
	if _, err := s.GetTenantPoolMembership(ctx, "shared-used"); err != nil {
		t.Fatalf("shared used membership removed by rejected delete: %v", err)
	}
	if err := s.DeleteTenantPoolMembership(ctx, "shared-free"); err != nil {
		t.Fatalf("DeleteTenantPoolMembership shared-free: %v", err)
	}

	if err := s.DeleteTenantPoolAndDetachUsedMembers(ctx, "pool-delete-mixed"); err != nil {
		t.Fatalf("DeleteTenantPoolAndDetachUsedMembers: %v", err)
	}
	if _, err := s.GetTenantPoolByID(ctx, "pool-delete-mixed"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("GetTenantPoolByID after delete = %v, want ErrNotFound", err)
	}
	nativeBinding, err = s.GetTenantTiDBCloudOrgBinding(ctx, "native-used")
	if err != nil {
		t.Fatalf("GetTenantTiDBCloudOrgBinding: %v", err)
	}
	if nativeBinding.PoolID != "" || nativeBinding.PoolStatus != TenantPoolBindingUsed {
		t.Fatalf("native binding after detach = %+v", nativeBinding)
	}
	if _, err := s.GetTenantPoolMembership(ctx, "shared-used"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("shared membership after detach = %v, want ErrNotFound", err)
	}
}

func TestDeleteTenantPoolAndDetachUsedMembersDropsConfirmedNativeAndDeletedSharedMembers(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	if err := s.CreateTenantPool(ctx, &TenantPool{
		PoolID: "pool-delete-orphan", OrganizationID: "org-delete-orphan", Size: 2,
		Status: TenantPoolDeleting, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("CreateTenantPool: %v", err)
	}
	if err := s.InsertTenant(ctx, &Tenant{
		ID: "native-orphan", Status: TenantDeleting, Provider: tidbCloudNativeProvider,
		DBPasswordCipher: []byte{}, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant native-orphan: %v", err)
	}
	if err := s.UpsertTenantTiDBCloudOrgBinding(ctx, &TenantTiDBCloudOrgBinding{
		TenantID: "native-orphan", OrganizationID: "org-delete-orphan", ClusterID: "cluster-native-orphan",
		PoolID: "pool-delete-orphan", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantTiDBCloudOrgBinding: %v", err)
	}
	if err := s.EnqueueTenantDeleteJob(ctx, &TenantDeleteJob{
		TenantID: "native-orphan", NamespaceID: "native-orphan-ns", Backend: "local",
		Prefix: "tenant/native-orphan",
	}); err != nil {
		t.Fatalf("EnqueueTenantDeleteJob native-orphan: %v", err)
	}
	insertTenantForPoolMembershipTest(t, s, "shared-orphan", TenantDeleted, now)
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: "shared-orphan", TiDBCloudOrganizationID: "org-delete-orphan",
		PoolID: "pool-delete-orphan", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantPoolMembership shared-orphan: %v", err)
	}

	if err := s.DeleteTenantPoolAndDetachUsedMembers(ctx, "pool-delete-orphan"); err != nil {
		t.Fatalf("DeleteTenantPoolAndDetachUsedMembers: %v", err)
	}
	if _, err := s.GetTenantPoolByID(ctx, "pool-delete-orphan"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("GetTenantPoolByID after delete = %v, want ErrNotFound", err)
	}
	if _, err := s.GetTenantTiDBCloudOrgBinding(ctx, "native-orphan"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("orphan native binding after delete = %v, want ErrNotFound", err)
	}
	if _, err := s.GetTenantPoolMembership(ctx, "shared-orphan"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("orphan shared membership after delete = %v, want ErrNotFound", err)
	}
}

func TestDeleteTenantPoolAndDetachUsedMembersStillBlocksOnLiveFreeNativeMember(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	if err := s.CreateTenantPool(ctx, &TenantPool{
		PoolID: "pool-delete-blocked", OrganizationID: "org-delete-blocked", Size: 1,
		Status: TenantPoolDeleting, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("CreateTenantPool: %v", err)
	}
	if err := s.InsertTenant(ctx, &Tenant{
		ID: "native-free-live", Status: TenantActive, Provider: tidbCloudNativeProvider,
		DBPasswordCipher: []byte{}, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant native-free-live: %v", err)
	}
	if err := s.UpsertTenantTiDBCloudOrgBinding(ctx, &TenantTiDBCloudOrgBinding{
		TenantID: "native-free-live", OrganizationID: "org-delete-blocked", ClusterID: "cluster-native-free-live",
		PoolID: "pool-delete-blocked", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantTiDBCloudOrgBinding: %v", err)
	}

	if err := s.DeleteTenantPoolAndDetachUsedMembers(ctx, "pool-delete-blocked"); err == nil {
		t.Fatal("pool deletion succeeded while a live free native member remained")
	}
	if _, err := s.GetTenantPoolByID(ctx, "pool-delete-blocked"); err != nil {
		t.Fatalf("pool after rejected delete = %v, want present", err)
	}
}

func TestDeleteTenantPoolAndDetachUsedMembersBlocksSuspendedFreeMember(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	if err := s.CreateTenantPool(ctx, &TenantPool{
		PoolID: "pool-delete-suspended", OrganizationID: "org-delete-suspended", Size: 1,
		Status: TenantPoolDeleting, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("CreateTenantPool: %v", err)
	}
	if err := s.InsertTenant(ctx, &Tenant{
		ID: "native-free-suspended", Status: TenantSuspended, Provider: tidbCloudNativeProvider,
		DBPasswordCipher: []byte{}, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant native-free-suspended: %v", err)
	}
	if err := s.UpsertTenantTiDBCloudOrgBinding(ctx, &TenantTiDBCloudOrgBinding{
		TenantID: "native-free-suspended", OrganizationID: "org-delete-suspended", ClusterID: "cluster-suspended",
		PoolID: "pool-delete-suspended", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantTiDBCloudOrgBinding: %v", err)
	}

	err := s.DeleteTenantPoolAndDetachUsedMembers(ctx, "pool-delete-suspended")
	if !errors.Is(err, ErrTenantPoolHasFreeMembers) {
		t.Fatalf("DeleteTenantPoolAndDetachUsedMembers = %v, want ErrTenantPoolHasFreeMembers", err)
	}
	if _, err := s.GetTenantPoolByID(ctx, "pool-delete-suspended"); err != nil {
		t.Fatalf("pool after rejected delete = %v, want present", err)
	}
	binding, err := s.GetTenantTiDBCloudOrgBinding(ctx, "native-free-suspended")
	if err != nil {
		t.Fatalf("GetTenantTiDBCloudOrgBinding: %v", err)
	}
	if binding.PoolID != "pool-delete-suspended" || binding.PoolStatus != TenantPoolBindingFree {
		t.Fatalf("suspended free binding after rejected delete = %+v, want untouched", binding)
	}
}

func TestDeleteTenantPoolAndDetachUsedMembersDropsOrphansBeforeGuards(t *testing.T) {
	s := newControlStore(t)
	ctx := context.Background()
	now := time.Now().UTC().Truncate(time.Millisecond)
	if err := s.CreateTenantPool(ctx, &TenantPool{
		PoolID: "pool-delete-progress", OrganizationID: "org-delete-progress", Size: 2,
		Status: TenantPoolDeleting, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("CreateTenantPool: %v", err)
	}
	if err := s.InsertTenant(ctx, &Tenant{
		ID: "native-free-blocking", Status: TenantActive, Provider: tidbCloudNativeProvider,
		DBPasswordCipher: []byte{}, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant native-free-blocking: %v", err)
	}
	if err := s.UpsertTenantTiDBCloudOrgBinding(ctx, &TenantTiDBCloudOrgBinding{
		TenantID: "native-free-blocking", OrganizationID: "org-delete-progress", ClusterID: "cluster-blocking",
		PoolID: "pool-delete-progress", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantTiDBCloudOrgBinding: %v", err)
	}
	insertTenantForPoolMembershipTest(t, s, "shared-orphan-before-guard", TenantDeleted, now)
	if err := s.UpsertTenantPoolMembership(ctx, &TenantPoolMembership{
		TenantID: "shared-orphan-before-guard", TiDBCloudOrganizationID: "org-delete-progress",
		PoolID: "pool-delete-progress", PoolStatus: TenantPoolBindingFree, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("UpsertTenantPoolMembership: %v", err)
	}

	err := s.DeleteTenantPoolAndDetachUsedMembers(ctx, "pool-delete-progress")
	if !errors.Is(err, ErrTenantPoolHasFreeMembers) {
		t.Fatalf("DeleteTenantPoolAndDetachUsedMembers = %v, want ErrTenantPoolHasFreeMembers", err)
	}
	if _, err := s.GetTenantPoolMembership(ctx, "shared-orphan-before-guard"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("orphan membership after rejected delete = %v, want dropped before the guard", err)
	}
	if _, err := s.GetTenantPoolByID(ctx, "pool-delete-progress"); err != nil {
		t.Fatalf("pool after rejected delete = %v, want present", err)
	}
}

func insertTenantForPoolMembershipTest(t *testing.T, s *Store, tenantID string, status TenantStatus, now time.Time) {
	t.Helper()
	if err := s.InsertTenant(context.Background(), &Tenant{
		ID: tenantID, Status: status, DBHost: "", DBPort: 0, DBUser: "", DBPasswordCipher: []byte{},
		DBName: "", Provider: "tidb_cloud_native_shared", SchemaVersion: 0, CreatedAt: now, UpdatedAt: now,
	}); err != nil {
		t.Fatalf("InsertTenant(%s): %v", tenantID, err)
	}
}