package tests

import "context"
import "errors"
import "fmt"
import "sync"
import "testing"

import "codit/internal/db"
import "codit/internal/models"

// ---------------------------------------------------------------------------
// helpers
// ---------------------------------------------------------------------------

func createTestBoard(t *testing.T, store *db.Store, user models.User, project models.Project, title string) models.Board {
	var board models.Board
	var created models.Board
	var err error
	board = models.Board{
		ProjectID: project.ID,
		Title:     title,
		CreatedBy: user.ID,
		UpdatedBy: user.ID,
	}
	created, err = store.CreateBoard(context.Background(), board)
	if err != nil {
		t.Fatalf("create board %q: %v", title, err)
	}
	return created
}

func createTestBoardFieldValue(t *testing.T, store *db.Store, boardID string, field string, label string) models.BoardFieldValue {
	var fv models.BoardFieldValue
	var created models.BoardFieldValue
	var err error
	fv = models.BoardFieldValue{
		Field: field,
		Value: label, // Value must be unique within (board, field)
		Label: label,
		Color: "#ff0000",
	}
	created, err = store.CreateBoardFieldValue(context.Background(), boardID, fv)
	if err != nil {
		t.Fatalf("create board field value %q: %v", label, err)
	}
	return created
}

func createTestBlock(t *testing.T, store *db.Store, user models.User, board models.Board, title string) models.Block {
	var block models.Block
	var created models.Block
	var err error
	block = models.Block{
		BoardID:   board.ID,
		CreatedBy: user.ID,
		UpdatedBy: user.ID,
		Type:      "card",
		Title:     title,
		Fields:    "{}",
	}
	created, err = store.CreateBlock(context.Background(), block)
	if err != nil {
		t.Fatalf("create block %q: %v", title, err)
	}
	return created
}

func createTestSSHServer(t *testing.T, store *db.Store, user models.User, name string) models.SSHServer {
	var item models.SSHServer
	var created models.SSHServer
	var err error
	item = models.SSHServer{
		Name:                 name,
		Host:                 "10.0.0.1",
		Port:                 22,
		Enabled:              true,
		CreatedByKind:        "user",
		CreatedBySubjectID:   user.ID,
		CreatedBySubjectName: user.Username,
	}
	created, err = store.CreateSSHServer(item)
	if err != nil {
		t.Fatalf("create ssh server %q: %v", name, err)
	}
	return created
}

func createTestSSHAccessProfile(t *testing.T, store *db.Store, user models.User, server models.SSHServer, name string) models.SSHAccessProfile {
	var item models.SSHAccessProfile
	var created models.SSHAccessProfile
	var err error
	item = models.SSHAccessProfile{
		ServerID:                 server.ID,
		Name:                     name,
		RemoteUsername:           "deploy",
		AuthMethod:               "none",
		OwnerScope:               "admin_shared",
		Enabled:                  true,
		DefaultCertValidSeconds:  3600,
		MaxCertValidSeconds:      3600,
		CreatedByKind:            "user",
		CreatedBySubjectID:       user.ID,
		CreatedBySubjectName:     user.Username,
	}
	created, err = store.CreateSSHAccessProfile(context.Background(), item)
	if err != nil {
		t.Fatalf("create ssh access profile %q: %v", name, err)
	}
	return created
}

func createTestSSHCredential(t *testing.T, store *db.Store, user models.User, name string) models.SSHCredential {
	var item models.SSHCredential
	var secret models.SSHSecret
	var created models.SSHCredential
	var err error
	item = models.SSHCredential{
		Name:                 name,
		Type:                 "private_key",
		PublicKey:            "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5 test",
		Fingerprint:          "SHA256:test-" + name,
		Enabled:              true,
		OwnerScope:           "user",
		OwnerUserID:          user.ID,
		CreatedByKind:        "user",
		CreatedBySubjectID:   user.ID,
		CreatedBySubjectName: user.Username,
	}
	secret = models.SSHSecret{
		Payload: "-----BEGIN PRIVATE KEY-----\ntest\n-----END PRIVATE KEY-----",
	}
	created, err = store.CreateSSHCredential(context.Background(), item, secret)
	if err != nil {
		t.Fatalf("create ssh credential %q: %v", name, err)
	}
	return created
}

// ---------------------------------------------------------------------------
// TestNestedTransactionReuse: a method that calls beginContext inside a
// BeginStore tx should reuse the outer transaction, not start a new one.
// Verified by checking that a rollback of the outer tx discards all inner work.
// ---------------------------------------------------------------------------

func TestNestedTransactionReuse(t *testing.T) {
	var store *db.Store
	var txStore *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	var fv models.BoardFieldValue
	var err error

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "nested-tx-user")
	project = createTestProject(t, store, user, "nested-tx-proj")
	board = createTestBoard(t, store, user, project, "board-nested")

	// Open an outer transaction and create a field value inside it.
	txStore, err = store.BeginStore(context.Background())
	if err != nil {
		t.Fatalf("begin outer tx: %v", err)
	}

	// CreateBoardFieldValue calls beginContext internally.
	// It must reuse the outer txStore transaction, not start its own.
	fv, err = txStore.CreateBoardFieldValue(context.Background(), board.ID, models.BoardFieldValue{
		Field: "status",
		Label: "In Progress",
		Color: "#0000ff",
	})
	if err != nil {
		_ = txStore.Rollback()
		t.Fatalf("create field value inside outer tx: %v", err)
	}

	// Roll back the outer transaction — the field value must not be committed.
	err = txStore.Rollback()
	if err != nil {
		t.Fatalf("rollback outer tx: %v", err)
	}

	// Verify the field value is not visible.
	var values []models.BoardFieldValue
	values, err = store.ListBoardFieldValues(board.ID, "status")
	if err != nil {
		t.Fatalf("list field values: %v", err)
	}
	var i int
	for i = 0; i < len(values); i++ {
		if values[i].ID == fv.ID {
			t.Fatal("field value created inside rolled-back outer tx is visible: inner tx was not reusing the outer tx")
		}
	}
}

// ---------------------------------------------------------------------------
// TestDeleteBoardFieldValueConcurrent: two goroutines delete the same field
// value simultaneously. Exactly one must succeed; the other must get
// ErrFieldValueNotFound. No panic, no double-delete.
// ---------------------------------------------------------------------------

func TestDeleteBoardFieldValueConcurrent(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	var fv models.BoardFieldValue

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "del-fv-user")
	project = createTestProject(t, store, user, "del-fv-proj")
	board = createTestBoard(t, store, user, project, "board-del-fv")
	fv = createTestBoardFieldValue(t, store, board.ID, "status", "todo")

	var wg sync.WaitGroup
	var successes int
	var notFounds int
	var mu sync.Mutex

	var run func()
	run = func() {
		defer wg.Done()
		var err error
		err = store.DeleteBoardFieldValue(context.Background(), board.ID, fv.ID)
		mu.Lock()
		defer mu.Unlock()
		if err == nil {
			successes++
		} else if errors.Is(err, db.ErrFieldValueNotFound) {
			notFounds++
		} else {
			t.Errorf("unexpected error from DeleteBoardFieldValue: %v", err)
		}
	}

	wg.Add(2)
	go run()
	go run()
	wg.Wait()

	if successes != 1 {
		t.Errorf("expected exactly 1 successful delete, got %d", successes)
	}
	if notFounds != 1 {
		t.Errorf("expected exactly 1 not-found error, got %d", notFounds)
	}
}

// ---------------------------------------------------------------------------
// TestDeleteBoardFieldValueInUseRollsBack: deleting a field value that is used
// by block_properties must fail and must leave the field value intact.
// ---------------------------------------------------------------------------

func TestDeleteBoardFieldValueInUseRollsBack(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	var block models.Block
	var fv models.BoardFieldValue
	var values []models.BoardFieldValue
	var err error
	var found bool
	var i int

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "del-fv-in-use-user")
	project = createTestProject(t, store, user, "del-fv-in-use-proj")
	board = createTestBoard(t, store, user, project, "board-del-fv-in-use")
	block = createTestBlock(t, store, user, board, "card-del-fv-in-use")
	fv = createTestBoardFieldValue(t, store, board.ID, "status", "todo")

	_, err = store.UpsertBlockProperties(context.Background(), board.ID, block.ID, models.BlockProperties{Status: fv.Value})
	if err != nil {
		t.Fatalf("upsert block properties: %v", err)
	}

	err = store.DeleteBoardFieldValue(context.Background(), board.ID, fv.ID)
	if !errors.Is(err, db.ErrFieldValueInUse) {
		t.Fatalf("expected ErrFieldValueInUse, got %v", err)
	}

	values, err = store.ListBoardFieldValues(board.ID, "status")
	if err != nil {
		t.Fatalf("list field values: %v", err)
	}
	found = false
	for i = 0; i < len(values); i++ {
		if values[i].ID == fv.ID {
			found = true
		}
	}
	if !found {
		t.Fatal("field value disappeared after failed delete")
	}
}

// ---------------------------------------------------------------------------
// TestDeleteSSHCredentialConcurrent: two goroutines delete the same SSH
// credential. Exactly one succeeds; the other gets a "not found" SQL error.
// ---------------------------------------------------------------------------

func TestDeleteSSHCredentialConcurrent(t *testing.T) {
	var store *db.Store
	var user models.User
	var cred models.SSHCredential

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "del-cred-user")
	cred = createTestSSHCredential(t, store, user, "my-key")

	var wg sync.WaitGroup
	var successes int
	var failures int
	var mu sync.Mutex

	var run func()
	run = func() {
		defer wg.Done()
		var err error
		err = store.DeleteSSHCredential(context.Background(), cred.ID)
		mu.Lock()
		defer mu.Unlock()
		if err == nil {
			successes++
		} else {
			failures++
		}
		// the second delete gets a sql.ErrNoRows or similar — not counted as success
	}

	wg.Add(2)
	go run()
	go run()
	wg.Wait()

	if successes != 1 {
		t.Errorf("expected exactly 1 successful delete, got %d", successes)
	}
	if failures != 1 {
		t.Errorf("expected exactly 1 failed delete, got %d", failures)
	}

	// Verify the credential is gone.
	var _, err = store.GetSSHCredential(cred.ID)
	if err == nil {
		t.Error("credential still exists after concurrent deletes")
	}
}

// ---------------------------------------------------------------------------
// TestDeleteSSHCredentialInUseBlocked: deleting a credential referenced by an
// access profile must fail and leave the credential intact.
// ---------------------------------------------------------------------------

func TestDeleteSSHCredentialInUseBlocked(t *testing.T) {
	var store *db.Store
	var user models.User
	var server models.SSHServer
	var cred models.SSHCredential
	var profile models.SSHAccessProfile
	var loaded models.SSHCredential
	var err error

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "del-cred-used-user")
	server = createTestSSHServer(t, store, user, "credential-target")
	cred = createTestSSHCredential(t, store, user, "used-key")
	profile = createTestSSHAccessProfile(t, store, user, server, "profile-uses-credential")
	profile.AuthMethod = "stored_private_key"
	profile.SSHCredentialID = cred.ID
	profile, err = store.UpdateSSHAccessProfile(context.Background(), profile)
	if err != nil {
		t.Fatalf("attach credential to profile: %v", err)
	}

	err = store.DeleteSSHCredential(context.Background(), cred.ID)
	if err == nil {
		t.Fatal("expected delete credential to fail while profile references it")
	}
	loaded, err = store.GetSSHCredential(cred.ID)
	if err != nil {
		t.Fatalf("credential missing after failed delete: %v", err)
	}
	if loaded.ID != cred.ID {
		t.Fatalf("unexpected credential loaded after failed delete: %s", loaded.ID)
	}
}

// ---------------------------------------------------------------------------
// TestDeleteSSHServerWhileProfileAdded: goroutine A checks references (zero),
// goroutine B concurrently adds a profile for the same server, then A deletes.
// With BEGIN IMMEDIATE the delete must either fail (reference found) or fully
// succeed atomically — it must never leave an orphaned access profile.
// ---------------------------------------------------------------------------

func TestDeleteSSHServerWhileProfileAdded(t *testing.T) {
	var store *db.Store
	var user models.User
	var server models.SSHServer
	var deleteErr error
	var profileErr error
	var wg sync.WaitGroup

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "del-server-user")
	server = createTestSSHServer(t, store, user, "target-server")

	// Barrier ensures both goroutines start at roughly the same time.
	var ready = make(chan struct{})

	// Goroutine A: delete the server.
	wg.Add(1)
	go func() {
		defer wg.Done()
		<-ready
		deleteErr = store.DeleteSSHServer(context.Background(), server.ID)
	}()

	// Goroutine B: create an access profile for the same server.
	wg.Add(1)
	go func() {
		defer wg.Done()
		<-ready
		var _, err = store.CreateSSHAccessProfile(context.Background(), models.SSHAccessProfile{
			ServerID:                 server.ID,
			Name:                     "profile-concurrent",
			RemoteUsername:           "deploy",
			AuthMethod:               "none",
			OwnerScope:               "admin_shared",
			Enabled:                  true,
			DefaultCertValidSeconds:  3600,
			MaxCertValidSeconds:      3600,
			CreatedByKind:            "user",
			CreatedBySubjectID:       user.ID,
			CreatedBySubjectName:     user.Username,
		})
		profileErr = err
	}()

	close(ready)
	wg.Wait()

	// Regardless of which operation won:
	// - If delete succeeded, the server is gone and the profile must not exist.
	// - If profile creation succeeded, delete must have failed with a reference error.
	// What must never happen: both succeed, leaving an orphaned profile record.

	if deleteErr == nil && profileErr == nil {
		// Both claimed success — verify no orphaned profile exists (server is gone,
		// so a profile referencing it would be orphaned).
		var profiles []models.SSHAccessProfile
		var err error
		profiles, err = store.ListSSHAccessProfiles()
		if err != nil {
			t.Fatalf("list profiles after concurrent ops: %v", err)
		}
		var i int
		for i = 0; i < len(profiles); i++ {
			if profiles[i].ServerID == server.ID {
				t.Errorf("orphaned access profile exists for deleted server %s", server.ID)
			}
		}
	}

	// At least one operation must have succeeded.
	if deleteErr != nil && profileErr != nil {
		t.Errorf("both operations failed — deleteErr=%v profileErr=%v", deleteErr, profileErr)
	}
}

// ---------------------------------------------------------------------------
// TestDeleteSSHServerWithProfileBlocked: the reference checks in DeleteSSHServer
// must run in the same transaction as the delete.
// ---------------------------------------------------------------------------

func TestDeleteSSHServerWithProfileBlocked(t *testing.T) {
	var store *db.Store
	var user models.User
	var server models.SSHServer
	var profiles []models.SSHAccessProfile
	var err error

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "del-server-ref-user")
	server = createTestSSHServer(t, store, user, "target-server-ref")
	_ = createTestSSHAccessProfile(t, store, user, server, "profile-server-ref")

	err = store.DeleteSSHServer(context.Background(), server.ID)
	if err == nil {
		t.Fatal("expected delete server to fail while profile references it")
	}
	profiles, err = store.ListSSHAccessProfiles()
	if err != nil {
		t.Fatalf("list profiles after failed delete: %v", err)
	}
	if len(profiles) != 1 {
		t.Fatalf("expected profile to remain after failed delete, got %d", len(profiles))
	}
	if profiles[0].ServerID != server.ID {
		t.Fatalf("profile references unexpected server after failed delete: %s", profiles[0].ServerID)
	}
}

// ---------------------------------------------------------------------------
// TestReorderBoardFieldValuesConcurrent: two goroutines concurrently reorder
// the same field's values. Both use BEGIN IMMEDIATE, so they serialize.
// Both must succeed, and the final ordering must be a valid permutation with
// no duplicate display_order values.
// ---------------------------------------------------------------------------

func TestReorderBoardFieldValuesConcurrent(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	var fv1, fv2, fv3 models.BoardFieldValue
	var orderAB []string
	var orderBA []string

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "reorder-user")
	project = createTestProject(t, store, user, "reorder-proj")
	board = createTestBoard(t, store, user, project, "board-reorder")

	fv1 = createTestBoardFieldValue(t, store, board.ID, "status", "todo")
	fv2 = createTestBoardFieldValue(t, store, board.ID, "status", "in-progress")
	fv3 = createTestBoardFieldValue(t, store, board.ID, "status", "done")

	orderAB = []string{fv1.ID, fv2.ID, fv3.ID}
	orderBA = []string{fv3.ID, fv2.ID, fv1.ID}

	var wg sync.WaitGroup
	var errA, errB error

	var ready = make(chan struct{})

	wg.Add(1)
	go func() {
		defer wg.Done()
		<-ready
		_, errA = store.ReorderBoardFieldValues(context.Background(), board.ID, "status", orderAB)
	}()

	wg.Add(1)
	go func() {
		defer wg.Done()
		<-ready
		_, errB = store.ReorderBoardFieldValues(context.Background(), board.ID, "status", orderBA)
	}()

	close(ready)
	wg.Wait()

	if errA != nil {
		t.Errorf("reorder AB failed: %v", errA)
	}
	if errB != nil {
		t.Errorf("reorder BA failed: %v", errB)
	}

	// Final state must have unique display_order values for all three field values.
	var values []models.BoardFieldValue
	var err error
	values, err = store.ListBoardFieldValues(board.ID, "status")
	if err != nil {
		t.Fatalf("list field values after concurrent reorder: %v", err)
	}
	if len(values) != 3 {
		t.Fatalf("expected 3 field values, got %d", len(values))
	}
	var seen = map[int]bool{}
	var i int
	for i = 0; i < len(values); i++ {
		if seen[values[i].DisplayOrder] {
			t.Errorf("duplicate display_order %d after concurrent reorder", values[i].DisplayOrder)
		}
		seen[values[i].DisplayOrder] = true
	}
}

// ---------------------------------------------------------------------------
// TestReorderBoardFieldValuesUnknownIDRollsBack: if one ID is unknown, the
// reorder must fail and keep the previous order intact.
// ---------------------------------------------------------------------------

func TestReorderBoardFieldValuesUnknownIDRollsBack(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	var fv1 models.BoardFieldValue
	var fv2 models.BoardFieldValue
	var before []models.BoardFieldValue
	var after []models.BoardFieldValue
	var beforeOrder map[string]int
	var err error
	var i int

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "reorder-missing-user")
	project = createTestProject(t, store, user, "reorder-missing-proj")
	board = createTestBoard(t, store, user, project, "board-reorder-missing")
	fv1 = createTestBoardFieldValue(t, store, board.ID, "status", "todo")
	fv2 = createTestBoardFieldValue(t, store, board.ID, "status", "done")

	before, err = store.ListBoardFieldValues(board.ID, "status")
	if err != nil {
		t.Fatalf("list field values before reorder: %v", err)
	}
	beforeOrder = map[string]int{}
	for i = 0; i < len(before); i++ {
		beforeOrder[before[i].ID] = before[i].DisplayOrder
	}

	_, err = store.ReorderBoardFieldValues(context.Background(), board.ID, "status", []string{fv2.ID, "missing-field-value", fv1.ID})
	if !errors.Is(err, db.ErrFieldValueNotFound) {
		t.Fatalf("expected ErrFieldValueNotFound, got %v", err)
	}

	after, err = store.ListBoardFieldValues(board.ID, "status")
	if err != nil {
		t.Fatalf("list field values after failed reorder: %v", err)
	}
	if len(after) != len(before) {
		t.Fatalf("field value count changed after failed reorder: before=%d after=%d", len(before), len(after))
	}
	for i = 0; i < len(after); i++ {
		if beforeOrder[after[i].ID] != after[i].DisplayOrder {
			t.Fatalf("display_order changed after failed reorder for %s: before=%d after=%d", after[i].ID, beforeOrder[after[i].ID], after[i].DisplayOrder)
		}
	}
}

// ---------------------------------------------------------------------------
// TestConcurrentBoardMemberUpsert: two goroutines concurrently upsert the same
// user as a board member. The result must be exactly one member row (no
// duplicate) and no error.
// ---------------------------------------------------------------------------

func TestConcurrentBoardMemberUpsert(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "upsert-member-user")
	project = createTestProject(t, store, user, "upsert-member-proj")
	board = createTestBoard(t, store, user, project, "board-upsert-member")

	var wg sync.WaitGroup
	var mu sync.Mutex
	var errs []error
	var ready = make(chan struct{})

	var run func()
	run = func() {
		defer wg.Done()
		<-ready
		var err error
		err = store.UpsertBoardMember(context.Background(), models.BoardMember{
			BoardID: board.ID,
			UserID:  user.ID,
			Role:    models.RoleWriter,
		})
		if err != nil {
			mu.Lock()
			errs = append(errs, err)
			mu.Unlock()
		}
	}

	wg.Add(3)
	go run()
	go run()
	go run()
	close(ready)
	wg.Wait()

	if len(errs) > 0 {
		t.Errorf("unexpected errors from concurrent UpsertBoardMember: %v", errs)
	}

	// Must be exactly one member row.
	var members []models.BoardMember
	var err error
	members, err = store.ListBoardMembers(board.ID)
	if err != nil {
		t.Fatalf("list board members: %v", err)
	}
	if len(members) != 1 {
		t.Errorf("expected 1 board member after concurrent upserts, got %d", len(members))
	}
}

// ---------------------------------------------------------------------------
// TestUpsertBlockPropertiesInvalidAssigneeRollsBack: the assignee rewrite
// deletes existing assignees before inserting the new set. An invalid assignee
// must roll the whole transaction back and keep existing assignees.
// ---------------------------------------------------------------------------

func TestUpsertBlockPropertiesInvalidAssigneeRollsBack(t *testing.T) {
	var store *db.Store
	var user models.User
	var other models.User
	var project models.Project
	var board models.Board
	var block models.Block
	var props models.BlockProperties
	var loaded models.BlockProperties
	var err error

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "props-owner-user")
	other = createTestUser(t, store, "props-assignee-user")
	project = createTestProject(t, store, user, "props-rollback-proj")
	board = createTestBoard(t, store, user, project, "board-props-rollback")
	block = createTestBlock(t, store, user, board, "card-props-rollback")

	props = models.BlockProperties{
		Status:      "todo",
		AssigneeIDs: []string{other.ID},
	}
	_, err = store.UpsertBlockProperties(context.Background(), board.ID, block.ID, props)
	if err != nil {
		t.Fatalf("initial upsert block properties: %v", err)
	}

	props = models.BlockProperties{
		Status:      "done",
		AssigneeIDs: []string{other.ID, "missing-user"},
	}
	_, err = store.UpsertBlockProperties(context.Background(), board.ID, block.ID, props)
	if !errors.Is(err, db.ErrUserNotFound) {
		t.Fatalf("expected ErrUserNotFound, got %v", err)
	}

	loaded, err = store.GetBlockProperties(board.ID, block.ID)
	if err != nil {
		t.Fatalf("get block properties after failed upsert: %v", err)
	}
	if loaded.Status != "todo" {
		t.Fatalf("status changed after failed upsert: %s", loaded.Status)
	}
	if len(loaded.AssigneeIDs) != 1 || loaded.AssigneeIDs[0] != other.ID {
		t.Fatalf("assignees changed after failed upsert: %#v", loaded.AssigneeIDs)
	}
}

// ---------------------------------------------------------------------------
// TestDeleteBlockAttachmentSoftDeletedParent: DeleteBlockAttachment must treat
// attachments under soft-deleted blocks as not found consistently.
// ---------------------------------------------------------------------------

func TestDeleteBlockAttachmentSoftDeletedParent(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	var block models.Block
	var attachment models.BlockAttachment
	var err error

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "attachment-delete-user")
	project = createTestProject(t, store, user, "attachment-delete-proj")
	board = createTestBoard(t, store, user, project, "board-attachment-delete")
	block = createTestBlock(t, store, user, board, "card-attachment-delete")
	attachment = models.BlockAttachment{
		ID:          "attachment-public-id",
		Filename:    "report.txt",
		ContentType: "text/plain",
		Size:        12,
		StoragePath: "/tmp/report.txt",
		CreatedBy:   user.ID,
	}
	attachment, err = store.CreateBlockAttachment(context.Background(), board.ID, block.ID, attachment)
	if err != nil {
		t.Fatalf("create block attachment: %v", err)
	}
	err = store.DeleteBlock(context.Background(), board.ID, block.ID, user.ID)
	if err != nil {
		t.Fatalf("soft-delete block: %v", err)
	}
	_, err = store.DeleteBlockAttachment(context.Background(), board.ID, block.ID, attachment.ID)
	if !errors.Is(err, db.ErrBlockAttachmentNotFound) {
		t.Fatalf("expected ErrBlockAttachmentNotFound, got %v", err)
	}
}

// ---------------------------------------------------------------------------
// TestConcurrentCreateBoardFieldValue: multiple goroutines create field values
// for the same board/field simultaneously. All should succeed and produce
// distinct IDs with no constraint violations.
// ---------------------------------------------------------------------------

func TestConcurrentCreateBoardFieldValue(t *testing.T) {
	var store *db.Store
	var user models.User
	var project models.Project
	var board models.Board
	const n = 8

	store = openTestStore(t)
	defer store.Close()

	user = createTestUser(t, store, "concurrent-create-fv-user")
	project = createTestProject(t, store, user, "concurrent-create-fv-proj")
	board = createTestBoard(t, store, user, project, "board-concurrent-fv")

	var wg sync.WaitGroup
	var mu sync.Mutex
	var ids []string
	var errs []error
	var values []models.BoardFieldValue
	var ready = make(chan struct{})
	var seen map[string]bool
	var seenOrder map[int]bool
	var id string
	var err error

	var i int
	for i = 0; i < n; i++ {
		wg.Add(1)
		go func(idx int) {
			defer wg.Done()
			<-ready
			var fv models.BoardFieldValue
			var err error
			fv, err = store.CreateBoardFieldValue(context.Background(), board.ID, models.BoardFieldValue{
				Field: "status",
				Value: fmt.Sprintf("value-%d", idx),
				Label: fmt.Sprintf("value-%d", idx),
				Color: "#aabbcc",
			})
			mu.Lock()
			defer mu.Unlock()
			if err != nil {
				errs = append(errs, err)
			} else {
				ids = append(ids, fv.ID)
			}
		}(i)
	}

	close(ready)
	wg.Wait()

	if len(errs) > 0 {
		t.Errorf("unexpected errors creating field values concurrently: %v", errs)
	}
	if len(ids) != n {
		t.Errorf("expected %d created field values, got %d", n, len(ids))
	}

	// All IDs must be unique.
	seen = map[string]bool{}
	for _, id = range ids {
		if seen[id] {
			t.Errorf("duplicate field value ID %s from concurrent creates", id)
		}
		seen[id] = true
	}

	values, err = store.ListBoardFieldValues(board.ID, "status")
	if err != nil {
		t.Fatalf("list field values after concurrent create: %v", err)
	}
	if len(values) != n {
		t.Fatalf("expected %d stored field values, got %d", n, len(values))
	}
	seenOrder = map[int]bool{}
	for i = 0; i < len(values); i++ {
		if seenOrder[values[i].DisplayOrder] {
			t.Fatalf("duplicate display_order %d after concurrent creates", values[i].DisplayOrder)
		}
		seenOrder[values[i].DisplayOrder] = true
	}
}