query-orchestration/internal/database/repository/bot_migration_test.go

// Milestone 1 §M1 chatbot tables — migration shape and constraint tests.
//
// These tests are the authoritative spec for migration 131
// (00000000000131_create_chatbot_tables). They run against the real
// Postgres testcontainer that internal/test.CreateDB stands up. No mocks.
//
// Plan reference: plans/chatbot_plan_codex.v10.md §4 (Data Model).
//
// Coverage:
//   - TestMigration131_UpDownRoundtrip: down then up apply cleanly and
//     re-create every table, column, index, and CHECK constraint.
//   - TestMigration131_BotTurnsCheckConstraints: bot_turns_status_values
//     rejects unknown statuses; bot_turns_status_fields_consistent rejects
//     every illegal (status, completion, error) tuple per §4.
//   - TestMigration131_PartialUniqueIndexInflight: only one in_flight row
//     per session is allowed.
//   - TestMigration131_BotSessionsPartialIndexes: the two partial indexes
//     on bot_sessions exist with the documented WHERE clause.
//   - TestMigration131_ScopeTablesCascade: delete-cascade behavior for
//     both bot_session_documents and bot_session_folders.
//
// All five tests follow the existing migration-test convention:
//   - t.Parallel + testing.Short skip mirroring TestMigration127_*
//   - per-test client/session reset because the testcontainer DB is reused
//   - introspection via information_schema and pg_catalog
//   - SQLSTATE assertions on negative-path expectations
package repository_test

import (
	"context"
	"errors"
	"os"
	"testing"

	"queryorchestration/internal/database/repository"
	"queryorchestration/internal/serviceconfig"
	"queryorchestration/internal/test"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/stretchr/testify/require"
)

// chatbotMigrationFiles is the canonical pair of file paths the round-trip
// test reads. Migration 131 is currently the latest, so single-step
// reversal is correct (team-lead M1 dispatch confirmed this).
const (
	chatbotUp131Path   = "../migrations/00000000000131_create_chatbot_tables.up.sql"
	chatbotDown131Path = "../migrations/00000000000131_create_chatbot_tables.down.sql"
)

// resetClientForBot wipes any leftover client/session rows from prior
// runs. Mirrors resetClient in customschemas_queries_test.go: the
// Postgres container and per-test database are reused across runs, so
// fresh CreateClient calls would collide with leftover rows. Documents
// must be deleted first because clients->documents is NOT cascade.
// bot_sessions cascades via the bot_sessions FK on clients(clientId).
func resetClientForBot(t *testing.T, ctx context.Context, cfg *serviceconfig.BaseConfig, clientID, name string) {
	t.Helper()
	pool := cfg.GetDBPool()
	_, err := pool.Exec(ctx, `DELETE FROM documents WHERE clientId = $1`, clientID)
	require.NoError(t, err, "pre-test DELETE documents must succeed")
	_, err = pool.Exec(ctx, `DELETE FROM clients WHERE clientId = $1`, clientID)
	require.NoError(t, err, "pre-test DELETE clients must succeed")
	err = cfg.GetDBQueries().CreateClient(ctx, &repository.CreateClientParams{
		Name:     name,
		Clientid: clientID,
	})
	require.NoError(t, err, "CreateClient(%s) must succeed", clientID)
}

// seedBotSession inserts one bot_sessions row and returns its id. Uses
// raw SQL because the sqlc bot.sql generated layer is part of M1 and
// may not be in place when this helper is called.
func seedBotSession(t *testing.T, ctx context.Context, cfg *serviceconfig.BaseConfig, clientID, createdBy string) uuid.UUID {
	t.Helper()
	var id uuid.UUID
	err := cfg.GetDBPool().QueryRow(ctx, `
		INSERT INTO bot_sessions (client_id, created_by)
		VALUES ($1, $2)
		RETURNING id
	`, clientID, createdBy).Scan(&id)
	require.NoError(t, err, "seed bot_sessions must succeed")
	return id
}

// TestMigration131_UpDownRoundtrip proves the down.sql is complete: it
// drops every object created by up.sql, and a subsequent up.sql re-apply
// re-creates them. Mirrors TestMigration127_UpDownRoundtrip. Best-effort
// cleanup runs the up.sql under context.Background() because t.Context()
// is canceled before t.Cleanup callbacks in Go 1.24+.
func TestMigration131_UpDownRoundtrip(t *testing.T) {
	t.Parallel()
	if testing.Short() {
		t.SkipNow()
	}
	ctx := t.Context()

	cfg := &serviceconfig.BaseConfig{}
	test.CreateDB(t, cfg)
	pool := cfg.GetDBPool()
	require.NotNil(t, pool)

	upSQL, err := os.ReadFile(chatbotUp131Path)
	require.NoError(t, err, "must read migration 131 up.sql at %s", chatbotUp131Path)
	downSQL, err := os.ReadFile(chatbotDown131Path)
	require.NoError(t, err, "must read migration 131 down.sql at %s", chatbotDown131Path)

	t.Cleanup(func() {
		// Restore migrated state on the shared container regardless of
		// test outcome. context.Background() is intentional — t.Context()
		// is canceled before Cleanup callbacks in Go 1.24+.
		_, _ = pool.Exec(context.Background(), string(upSQL)) //nolint:usetesting // see comment
	})

	// 1. Down — single-step because 131 is currently the latest migration.
	_, err = pool.Exec(ctx, string(downSQL))
	require.NoError(t, err, "down migration 131 must apply cleanly")

	// All four tables must be gone.
	for _, table := range []string{"bot_sessions", "bot_session_documents", "bot_session_folders", "bot_turns"} {
		var oid *string
		err = pool.QueryRow(ctx, `SELECT to_regclass('public.' || $1)::text`, table).Scan(&oid)
		require.NoError(t, err)
		require.Nilf(t, oid, "%s must not exist after down migration", table)
	}

	// 2. Up — re-create everything.
	_, err = pool.Exec(ctx, string(upSQL))
	require.NoError(t, err, "up migration 131 must re-apply cleanly")

	// All four tables back.
	for _, table := range []string{"bot_sessions", "bot_session_documents", "bot_session_folders", "bot_turns"} {
		var oid *string
		err = pool.QueryRow(ctx, `SELECT to_regclass('public.' || $1)::text`, table).Scan(&oid)
		require.NoError(t, err)
		require.NotNilf(t, oid, "%s must exist after up migration", table)
		require.Equal(t, table, *oid)
	}

	// bot_turns CHECK constraints reappear.
	for _, conname := range []string{"bot_turns_status_values", "bot_turns_status_fields_consistent"} {
		var contype string
		err = pool.QueryRow(ctx, `
			SELECT c.contype::text
			FROM pg_constraint c
			JOIN pg_class t ON t.oid = c.conrelid
			WHERE c.conname = $1 AND t.relname = 'bot_turns'
		`, conname).Scan(&contype)
		require.NoErrorf(t, err, "%s must exist on bot_turns", conname)
		require.Equalf(t, "c", contype, "%s must be a CHECK constraint", conname)
	}

	// Partial unique index reappears.
	var indexExists bool
	err = pool.QueryRow(ctx, `
		SELECT EXISTS (
			SELECT 1 FROM pg_indexes
			WHERE schemaname = 'public'
			  AND tablename  = 'bot_turns'
			  AND indexname  = 'bot_turns_one_inflight_per_session'
		)
	`).Scan(&indexExists)
	require.NoError(t, err)
	require.True(t, indexExists, "bot_turns_one_inflight_per_session must reappear after up")

	// Partial unique index must include the WHERE status='in_flight' clause.
	var indexDef string
	err = pool.QueryRow(ctx, `
		SELECT indexdef FROM pg_indexes
		WHERE schemaname = 'public'
		  AND indexname  = 'bot_turns_one_inflight_per_session'
	`).Scan(&indexDef)
	require.NoError(t, err)
	require.Contains(t, indexDef, "in_flight",
		"bot_turns_one_inflight_per_session must filter on status='in_flight'")

	// idx_bot_turns_session_completed reappears.
	err = pool.QueryRow(ctx, `
		SELECT EXISTS (
			SELECT 1 FROM pg_indexes
			WHERE schemaname = 'public'
			  AND tablename  = 'bot_turns'
			  AND indexname  = 'idx_bot_turns_session_completed'
		)
	`).Scan(&indexExists)
	require.NoError(t, err)
	require.True(t, indexExists, "idx_bot_turns_session_completed must reappear after up")
}

// TestMigration131_BotTurnsCheckConstraints exercises both CHECK
// constraints on bot_turns directly. The status-enum CHECK rejects any
// value outside the documented set; the status/payload consistency CHECK
// enforces the (status, completion, error) matrix from plan §4.
func TestMigration131_BotTurnsCheckConstraints(t *testing.T) {
	t.Parallel()
	if testing.Short() {
		t.SkipNow()
	}
	ctx := t.Context()

	cfg := &serviceconfig.BaseConfig{}
	test.CreateDB(t, cfg)
	pool := cfg.GetDBPool()
	require.NotNil(t, pool)

	const clientID = "MIG131_CHK"
	resetClientForBot(t, ctx, cfg, clientID, "mig131-chk")
	sessionID := seedBotSession(t, ctx, cfg, clientID, "tester@example.com")

	// Helper: assert the INSERT trips a check_violation (23514) naming
	// the expected constraint.
	assertCheckViolation := func(t *testing.T, err error, wantConstraint string) {
		t.Helper()
		require.Error(t, err, "expected CHECK violation, got success")
		var pgErr *pgconn.PgError
		require.True(t, errors.As(err, &pgErr),
			"expected *pgconn.PgError, got %T: %v", err, err)
		require.Equal(t, "23514", pgErr.Code,
			"expected check_violation SQLSTATE 23514, got %q: %s", pgErr.Code, pgErr.Message)
		require.Equal(t, wantConstraint, pgErr.ConstraintName,
			"expected %s to be the tripped constraint", wantConstraint)
	}

	t.Run("status_enum_rejects_unknown", func(t *testing.T) {
		_, err := pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, completion, error)
			VALUES ($1, $2, $3, $4, $5, NULL, NULL)
		`, sessionID, 1, "p", "frobnicated", uuid.New())
		assertCheckViolation(t, err, "bot_turns_status_values")
	})

	// Each row of this table sets the (status, completion, error) tuple
	// the consistency CHECK must reject. ordinal collisions are avoided
	// by giving each case its own ordinal.
	type fieldsRow struct {
		name          string
		ordinal       int
		status        string
		completionPtr *string
		errorJSON     *string
	}
	str := func(s string) *string { return &s }
	const errJSON = `{"code":"x","message":"y"}`

	t.Run("status_fields_consistent", func(t *testing.T) {
		cases := []fieldsRow{
			{
				name:          "in_flight_with_completion_rejected",
				ordinal:       2,
				status:        "in_flight",
				completionPtr: str("not allowed"),
				errorJSON:     nil,
			},
			{
				name:          "in_flight_with_error_rejected",
				ordinal:       3,
				status:        "in_flight",
				completionPtr: nil,
				errorJSON:     str(errJSON),
			},
			{
				name:          "completed_with_error_rejected",
				ordinal:       4,
				status:        "completed",
				completionPtr: str("answer"),
				errorJSON:     str(errJSON),
			},
			{
				name:          "completed_without_completion_rejected",
				ordinal:       5,
				status:        "completed",
				completionPtr: nil,
				errorJSON:     nil,
			},
			{
				name:          "errored_with_completion_rejected",
				ordinal:       6,
				status:        "errored",
				completionPtr: str("oops"),
				errorJSON:     str(errJSON),
			},
			{
				name:          "errored_without_error_rejected",
				ordinal:       7,
				status:        "errored",
				completionPtr: nil,
				errorJSON:     nil,
			},
			{
				name:          "abandoned_without_error_rejected",
				ordinal:       8,
				status:        "abandoned",
				completionPtr: nil,
				errorJSON:     nil,
			},
			{
				name:          "abandoned_with_completion_rejected",
				ordinal:       9,
				status:        "abandoned",
				completionPtr: str("partial"),
				errorJSON:     str(errJSON),
			},
			{
				name:          "session_deleted_without_error_rejected",
				ordinal:       10,
				status:        "session_deleted",
				completionPtr: nil,
				errorJSON:     nil,
			},
		}

		for _, c := range cases {
			t.Run(c.name, func(t *testing.T) {
				_, err := pool.Exec(ctx, `
					INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, completion, error)
					VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb)
				`,
					sessionID, c.ordinal, "prompt", c.status, uuid.New(),
					c.completionPtr, c.errorJSON)
				assertCheckViolation(t, err, "bot_turns_status_fields_consistent")
			})
		}
	})

	// Positive control: each legal tuple from plan §4 must be accepted.
	// One session can hold at most one in_flight (partial unique index),
	// so the in_flight case uses a fresh session.
	t.Run("legal_status_tuples_accepted", func(t *testing.T) {
		legalSessionID := seedBotSession(t, ctx, cfg, clientID, "legal@example.com")

		// in_flight with NULL completion and NULL error.
		_, err := pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id)
			VALUES ($1, 1, 'p', 'in_flight', $2)
		`, legalSessionID, uuid.New())
		require.NoError(t, err, "in_flight with NULLs must be accepted")

		// completed with completion non-NULL, error NULL. New session to
		// avoid colliding with the legal in_flight row above.
		completedSessionID := seedBotSession(t, ctx, cfg, clientID, "completed@example.com")
		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, completion)
			VALUES ($1, 1, 'p', 'completed', $2, 'ok')
		`, completedSessionID, uuid.New())
		require.NoError(t, err, "completed with completion must be accepted")

		// errored with error non-NULL, completion NULL.
		erroredSessionID := seedBotSession(t, ctx, cfg, clientID, "errored@example.com")
		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, error)
			VALUES ($1, 1, 'p', 'errored', $2, $3::jsonb)
		`, erroredSessionID, uuid.New(), errJSON)
		require.NoError(t, err, "errored with error must be accepted")

		// abandoned with error non-NULL.
		abandonedSessionID := seedBotSession(t, ctx, cfg, clientID, "abandoned@example.com")
		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, error)
			VALUES ($1, 1, 'p', 'abandoned', $2, $3::jsonb)
		`, abandonedSessionID, uuid.New(), errJSON)
		require.NoError(t, err, "abandoned with error must be accepted")

		// session_deleted with error non-NULL.
		deletedSessionID := seedBotSession(t, ctx, cfg, clientID, "deleted@example.com")
		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, error)
			VALUES ($1, 1, 'p', 'session_deleted', $2, $3::jsonb)
		`, deletedSessionID, uuid.New(), errJSON)
		require.NoError(t, err, "session_deleted with error must be accepted")
	})
}

// TestMigration131_PartialUniqueIndexInflight proves bot_turns_one_inflight_per_session
// rejects a second in_flight row for the same session, but accepts mixed
// states (in_flight + completed) and multiple completed rows.
func TestMigration131_PartialUniqueIndexInflight(t *testing.T) {
	t.Parallel()
	if testing.Short() {
		t.SkipNow()
	}
	ctx := t.Context()

	cfg := &serviceconfig.BaseConfig{}
	test.CreateDB(t, cfg)
	pool := cfg.GetDBPool()
	require.NotNil(t, pool)

	const clientID = "MIG131_UNIQ"
	resetClientForBot(t, ctx, cfg, clientID, "mig131-uniq")
	sessionID := seedBotSession(t, ctx, cfg, clientID, "uniq@example.com")

	t.Run("two_inflight_same_session_rejected", func(t *testing.T) {
		_, err := pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id)
			VALUES ($1, 1, 'p1', 'in_flight', $2)
		`, sessionID, uuid.New())
		require.NoError(t, err, "first in_flight row must succeed")

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id)
			VALUES ($1, 2, 'p2', 'in_flight', $2)
		`, sessionID, uuid.New())
		require.Error(t, err, "second in_flight row for same session must be rejected")

		var pgErr *pgconn.PgError
		require.True(t, errors.As(err, &pgErr),
			"expected *pgconn.PgError, got %T: %v", err, err)
		require.Equal(t, "23505", pgErr.Code,
			"expected unique_violation SQLSTATE 23505, got %q", pgErr.Code)
		require.Equal(t, "bot_turns_one_inflight_per_session", pgErr.ConstraintName,
			"expected bot_turns_one_inflight_per_session to be the tripped constraint")
	})

	t.Run("inflight_plus_completed_allowed", func(t *testing.T) {
		// Fresh session so we don't collide with the previous subtest.
		mixedSession := seedBotSession(t, ctx, cfg, clientID, "mixed@example.com")
		_, err := pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, completion)
			VALUES ($1, 1, 'p1', 'completed', $2, 'ok')
		`, mixedSession, uuid.New())
		require.NoError(t, err, "completed row must succeed")

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id)
			VALUES ($1, 2, 'p2', 'in_flight', $2)
		`, mixedSession, uuid.New())
		require.NoError(t, err, "in_flight after completed must be accepted (different status)")
	})

	t.Run("two_completed_allowed", func(t *testing.T) {
		complSession := seedBotSession(t, ctx, cfg, clientID, "completed-twice@example.com")
		_, err := pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, completion)
			VALUES ($1, 1, 'p1', 'completed', $2, 'ok1')
		`, complSession, uuid.New())
		require.NoError(t, err)

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_turns (session_id, ordinal, prompt, status, attempt_id, completion)
			VALUES ($1, 2, 'p2', 'completed', $2, 'ok2')
		`, complSession, uuid.New())
		require.NoError(t, err, "two completed rows for the same session must be accepted")
	})
}

// TestMigration131_BotSessionsPartialIndexes asserts the two partial
// indexes on bot_sessions exist with the documented WHERE clause
// (is_deleted = false). Plan §4: idx_bot_sessions_client_user_active and
// idx_bot_sessions_client_active are both partial indexes.
func TestMigration131_BotSessionsPartialIndexes(t *testing.T) {
	t.Parallel()
	if testing.Short() {
		t.SkipNow()
	}
	ctx := t.Context()

	cfg := &serviceconfig.BaseConfig{}
	test.CreateDB(t, cfg)
	pool := cfg.GetDBPool()
	require.NotNil(t, pool)

	expected := map[string][]string{
		// Map index name -> ordered column list per plan §4.
		"idx_bot_sessions_client_user_active": {"client_id", "created_by", "updated_at"},
		"idx_bot_sessions_client_active":      {"client_id", "updated_at"},
	}

	for indexName, wantCols := range expected {
		t.Run(indexName, func(t *testing.T) {
			var indexDef string
			err := pool.QueryRow(ctx, `
				SELECT indexdef FROM pg_indexes
				WHERE schemaname = 'public'
				  AND tablename  = 'bot_sessions'
				  AND indexname  = $1
			`, indexName).Scan(&indexDef)
			require.NoErrorf(t, err, "index %s must exist on bot_sessions", indexName)

			// WHERE clause must filter is_deleted = false. Postgres
			// normalises the clause text, so a substring match is enough.
			require.Contains(t, indexDef, "is_deleted = false",
				"index %s must be partial on is_deleted = false; got %q",
				indexName, indexDef)

			// Each expected column must appear in the indexdef text.
			for _, col := range wantCols {
				require.Containsf(t, indexDef, col,
					"index %s definition must reference column %s; got %q",
					indexName, col, indexDef)
			}
		})
	}
}

// TestMigration131_ScopeTablesCascade exercises the FK-cascade behavior
// declared in plan §4: deleting a bot_sessions row cascades through
// bot_session_documents and bot_session_folders; deleting a referenced
// document or folder cascades the linkage row in those join tables.
func TestMigration131_ScopeTablesCascade(t *testing.T) {
	t.Parallel()
	if testing.Short() {
		t.SkipNow()
	}
	ctx := t.Context()

	cfg := &serviceconfig.BaseConfig{}
	test.CreateDB(t, cfg)
	pool := cfg.GetDBPool()
	require.NotNil(t, pool)

	const clientID = "MIG131_CSC"
	resetClientForBot(t, ctx, cfg, clientID, "mig131-csc")

	// Use the sqlc generated CreateFolder/CreateDocument to seed
	// scope-table fixtures. CreateFolder accepts parentId=NULL because
	// the migration declares parentId as a nullable FK on folders.
	queries := cfg.GetDBQueries()
	require.NotNil(t, queries)

	t.Run("session_delete_cascades_documents", func(t *testing.T) {
		// Seed: one session, one document, one bot_session_documents row.
		sessionID := seedBotSession(t, ctx, cfg, clientID, "csc-doc@example.com")

		docID, err := queries.CreateDocument(ctx, &repository.CreateDocumentParams{
			Clientid: clientID,
			Hash:     "mig131_csc_doc_hash_session",
		})
		require.NoError(t, err, "CreateDocument must succeed")

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_session_documents (session_id, document_id) VALUES ($1, $2)
		`, sessionID, docID)
		require.NoError(t, err, "INSERT bot_session_documents must succeed")

		// Sanity check the link is present.
		var linkCount int
		err = pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM bot_session_documents
			WHERE session_id = $1 AND document_id = $2
		`, sessionID, docID).Scan(&linkCount)
		require.NoError(t, err)
		require.Equal(t, 1, linkCount, "link row must exist before delete")

		// Delete the session; the link must vanish via cascade.
		_, err = pool.Exec(ctx, `DELETE FROM bot_sessions WHERE id = $1`, sessionID)
		require.NoError(t, err, "DELETE bot_sessions must succeed")

		err = pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM bot_session_documents WHERE session_id = $1
		`, sessionID).Scan(&linkCount)
		require.NoError(t, err)
		require.Equal(t, 0, linkCount, "link rows must cascade away with parent session")
	})

	t.Run("session_delete_cascades_folders", func(t *testing.T) {
		sessionID := seedBotSession(t, ctx, cfg, clientID, "csc-fld@example.com")

		folder, err := queries.CreateFolder(ctx, &repository.CreateFolderParams{
			Path:      "/csc-folder-session",
			Parentid:  nil,
			Clientid:  clientID,
			Createdby: "tester",
		})
		require.NoError(t, err, "CreateFolder must succeed")

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_session_folders (session_id, folder_id) VALUES ($1, $2)
		`, sessionID, folder.ID)
		require.NoError(t, err, "INSERT bot_session_folders must succeed")

		var linkCount int
		err = pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM bot_session_folders
			WHERE session_id = $1 AND folder_id = $2
		`, sessionID, folder.ID).Scan(&linkCount)
		require.NoError(t, err)
		require.Equal(t, 1, linkCount, "folder link must exist before delete")

		_, err = pool.Exec(ctx, `DELETE FROM bot_sessions WHERE id = $1`, sessionID)
		require.NoError(t, err)

		err = pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM bot_session_folders WHERE session_id = $1
		`, sessionID).Scan(&linkCount)
		require.NoError(t, err)
		require.Equal(t, 0, linkCount, "folder link must cascade away with parent session")
	})

	t.Run("document_delete_cascades_link", func(t *testing.T) {
		sessionID := seedBotSession(t, ctx, cfg, clientID, "csc-doc2@example.com")

		docID, err := queries.CreateDocument(ctx, &repository.CreateDocumentParams{
			Clientid: clientID,
			Hash:     "mig131_csc_doc_hash_doc",
		})
		require.NoError(t, err)

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_session_documents (session_id, document_id) VALUES ($1, $2)
		`, sessionID, docID)
		require.NoError(t, err)

		// Delete the document; the link row must cascade.
		_, err = pool.Exec(ctx, `DELETE FROM documents WHERE id = $1`, docID)
		require.NoError(t, err)

		var linkCount int
		err = pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM bot_session_documents WHERE document_id = $1
		`, docID).Scan(&linkCount)
		require.NoError(t, err)
		require.Equal(t, 0, linkCount,
			"deleting referenced document must cascade away the link row")
	})

	t.Run("folder_delete_cascades_link", func(t *testing.T) {
		sessionID := seedBotSession(t, ctx, cfg, clientID, "csc-fld2@example.com")

		folder, err := queries.CreateFolder(ctx, &repository.CreateFolderParams{
			Path:      "/csc-folder-folder",
			Parentid:  nil,
			Clientid:  clientID,
			Createdby: "tester",
		})
		require.NoError(t, err)

		_, err = pool.Exec(ctx, `
			INSERT INTO bot_session_folders (session_id, folder_id) VALUES ($1, $2)
		`, sessionID, folder.ID)
		require.NoError(t, err)

		_, err = pool.Exec(ctx, `DELETE FROM folders WHERE id = $1`, folder.ID)
		require.NoError(t, err)

		var linkCount int
		err = pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM bot_session_folders WHERE folder_id = $1
		`, folder.ID).Scan(&linkCount)
		require.NoError(t, err)
		require.Equal(t, 0, linkCount,
			"deleting referenced folder must cascade away the link row")
	})
}