//go:build e2e

// Package e2e_test contains the AgentTeam end-to-end test suite. The
// projects_migration E2E tests pin the Directory→Project refactor's schema
// cutover (migration that drops `directories`, adds `projects`,
// `project_grants`, and `review_contexts.project_id`) against a REAL TiDB
// database driven through the registered migration FS.
//
// Schema artifacts asserted here are the frozen minimal contract the GREEN
// migration must ship — no more, no less. Column names, column order, the
// composite grants PK, and index column sequences (including the UNIQUE
// flag for projects_workspace_target_hash_uniq) are all pinned so a drift
// in GREEN surfaces as a crisp test failure rather than a silent mismatch.
//
// What this file deliberately does NOT assert:
//
//   - Any tightening of review_contexts.project_id toward NOT NULL, and
//     any historical backfill that populates project_id for legacy
//     reviews. The file DOES assert the current contract — NULL DEFAULT
//     NULL — via columnMeta in TestProjectsMigration_Cutover_E2E; what it
//     refuses to assert is the opposite (NOT NULL) or a write-time
//     migration that backfills. The C5.1 dual-approved contract keeps
//     the column NULL DEFAULT NULL and defers Project binding to the
//     writer (C4.3 wiring, C5.2 e2e). A future migration that tightens
//     to NOT NULL or backfills is a separate product/data decision
//     requiring sign-off from 龙恒.
//   - CHECK clauses on projects.visibility / project_grants.role. TiDB
//     v8.5 ships with tidb_enable_check_constraint=OFF, so CHECK clauses
//     in the migration SQL are parsed but neither enforced nor persisted
//     to information_schema.check_constraints (SHOW CREATE TABLE strips
//     them). The migration keeps the clauses as forward-compat
//     documentation; the enum contract is enforced at the application
//     layer (writer/store) and covered by unit + C5.2 writer e2e.
package e2e_test

import (
	"context"
	"database/sql"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	e2etestkit "github.com/tidbcloud/AgentTeam/e2e/testkit"
	"github.com/tidbcloud/AgentTeam/internal/coredb/testkit"
	"github.com/tidbcloud/AgentTeam/internal/tidb"
)

// Frozen contract constants — the GREEN v243 migration must register these
// exact identifiers and column sequences. Tests reference them by name so a
// drift in GREEN surfaces at compile time, not as a silent mismatch.
const (
	// projects indexes — see GREEN schema contract.
	projectsWorkspaceUpdatedIndex      = "projects_workspace_updated_idx"
	projectsWorkspaceMachineIndex      = "projects_workspace_machine_idx"
	projectsWorkspaceOwnerUpdatedIndex = "projects_workspace_owner_updated_idx"
	// projects unique identity — workspace+target_hash backs the get-or-create
	// race-safety contract (C4 revise per planner seq=628).
	projectsWorkspaceTargetHashUniqueIndex = "projects_workspace_target_hash_uniq"
	// project_grants indexes — member+updated for "grants for user" lookup.
	projectGrantsMemberUpdatedIndex = "project_grants_member_updated_idx"
	// review_contexts index — workspace+project+updated for listing reviews per project.
	reviewContextsWorkspaceProjectUpdatedIndex = "review_contexts_workspace_project_updated_idx"
)

// frozenProjectsColumns is the ordered column list the GREEN migration must
// ship on `projects`. Order is the ordinal_position the GREEN migration
// registers; tests verify the order so a column reorder is caught.
var frozenProjectsColumns = []string{
	"id", "workspace_id", "machine_id", "root_path", "target_hash",
	"name", "description",
	"visibility", "owner_user_id",
	"created_by_user_id", "updated_by_user_id",
	"created_at", "updated_at",
}

// frozenProjectGrantsColumns is the ordered column list the GREEN migration
// must ship on `project_grants`. Note: no `id`, no `workspace_id` — the
// table is keyed by the composite (project_id, member_id) PK.
var frozenProjectGrantsColumns = []string{
	"project_id", "member_id", "role", "created_at", "updated_at",
}

// frozenProjectGrantsPK is the composite primary key column sequence the
// GREEN migration must register on `project_grants`.
var frozenProjectGrantsPK = []string{"project_id", "member_id"}

// frozenProjectsIndexColumns maps each canonical projects SECONDARY (non-
// unique) index to its exact column sequence — GREEN must register this
// column order, and each entry is asserted non-unique via requireIndexColumns.
// Unique indexes live in frozenProjectsUniqueIndexColumns and go through
// requireUniqueIndexColumns, because the uniqueness flag is load-bearing for
// the get-or-create race-safety contract.
var frozenProjectsIndexColumns = map[string][]string{
	projectsWorkspaceUpdatedIndex:      {"workspace_id", "updated_at"},
	projectsWorkspaceMachineIndex:      {"workspace_id", "machine_id"},
	projectsWorkspaceOwnerUpdatedIndex: {"workspace_id", "owner_user_id", "updated_at"},
}

// frozenProjectsUniqueIndexColumns maps each canonical projects UNIQUE index
// to its exact column sequence. These back the get-or-create identity
// contract: UNIQUE(workspace_id, target_hash) is what makes two concurrent
// first-creates converge on one row, so the unique flag must be asserted in
// addition to the column sequence.
var frozenProjectsUniqueIndexColumns = map[string][]string{
	projectsWorkspaceTargetHashUniqueIndex: {"workspace_id", "target_hash"},
}

// frozenProjectGrantsIndexColumns maps each canonical project_grants index
// to its exact column sequence.
var frozenProjectGrantsIndexColumns = map[string][]string{
	projectGrantsMemberUpdatedIndex: {"member_id", "updated_at"},
}

// frozenReviewContextsIndexColumns maps each canonical review_contexts index
// to its exact column sequence.
var frozenReviewContextsIndexColumns = map[string][]string{
	reviewContextsWorkspaceProjectUpdatedIndex: {"workspace_id", "project_id", "updated_at"},
}

// TestProjectsMigration_Cutover_E2E drives an empty TiDB to migration v242
// (which still ships the `directories` table and lacks `projects`), then runs
// ApplyMigrations to bring it forward. The post-cutover schema MUST:
//
//   - contain a `projects` table with the frozen column set in the frozen
//     order, with NO additional columns,
//   - contain a `project_grants` table with the frozen 5-column set and a
//     composite (project_id, member_id) primary key,
//   - have DROPPED the legacy `directories` table,
//   - expose `review_contexts.project_id` as NULL DEFAULT NULL (the
//     dual-approved v243 contract — binding is deferred to the writer,
//     not the migration),
//   - register each canonical index with the exact column sequence,
//     including the UNIQUE flag on projects_workspace_target_hash_uniq.
//
// What this test deliberately does NOT assert:
//
//   - CHECK clauses bounding visibility/role. TiDB v8.5 default
//     tidb_enable_check_constraint=OFF means the clauses are parsed but
//     not persisted to information_schema.check_constraints. See the
//     file-top commentary for the full rationale.
//
// RED failure mode: ApplyMigrations is a no-op at v242 (no v243 migration is
// registered yet), so `projects`/`project_grants` remain absent and the
// directories table remains. The presence assertions fail.
// TestProjectsMigration_Cutover_E2E replays the full migration chain against
// a database on the shared TiDB test runtime. It must stay serial: concurrent
// migration replays on sibling databases of the same Unistore runtime race on
// DDL and can drop a replaying test's database mid-run (CI: Error 1049).
func TestProjectsMigration_Cutover_E2E(t *testing.T) {
	ctx, cancel := context.WithTimeout(context.Background(), 2*time.Minute)
	defer cancel()

	dsn := e2etestkit.StartTiDBAtMigration(t, e2eTiDBRuntimeFixture.RootDSN(t), 242)
	db := e2etestkit.OpenE2EDB(t, dsn)
	defer db.Close()

	require.NoError(t, tidb.ApplyMigrations(ctx, db))

	require.True(t, tableExists(t, ctx, db, "projects"),
		"post-cutover schema must contain a projects table")
	require.True(t, tableExists(t, ctx, db, "project_grants"),
		"post-cutover schema must contain a project_grants table")
	require.False(t, tableExists(t, ctx, db, "directories"),
		"post-cutover schema must have dropped the legacy directories table")

	// Column set + order, no extras.
	requireColumnsExact(t, ctx, db, "projects", frozenProjectsColumns)
	requireColumnsExact(t, ctx, db, "project_grants", frozenProjectGrantsColumns)

	// review_contexts.project_id must exist as NULL DEFAULT NULL — both
	// halves are load-bearing. The dual-approved v243 contract deliberately
	// keeps pre-existing review rows un-bound: no historical backfill, no
	// NOT NULL tightening. New and updated reviews get bound to a Project
	// via the writer (asserted in C5.2 writer e2e), but legacy rows are
	// allowed to remain NULL indefinitely.
	//
	// The COLUMN_DEFAULT IS NULL check is what prevents a future migration
	// from silently shipping nullable + DEFAULT '' — that would still pass
	// an IS_NULLABLE-only check but would change the writer's zero-value
	// semantics (NULL becomes a sentinel "unbound" distinct from any
	// populated project_id, while '' could collide with malformed writes).
	// A migration that tightens to NOT NULL or backfills is a separate
	// product/data decision requiring sign-off from 龙恒.
	nullable, columnDefault, ok := columnMeta(t, ctx, db, "review_contexts", "project_id")
	require.True(t, ok, "review_contexts.project_id column must exist after cutover")
	require.Equal(t, "YES", nullable,
		"review_contexts.project_id must stay NULLABLE — the dual-approved v243 contract defers binding to the writer, not the migration")
	require.False(t, columnDefault.Valid,
		"review_contexts.project_id must keep COLUMN_DEFAULT = NULL (information_schema reports NULL as an invalid NullString); a future migration shipping nullable + DEFAULT '' would change the writer's zero-value semantics")

	// Composite primary key on project_grants.
	requirePrimaryKey(t, ctx, db, "project_grants", frozenProjectGrantsPK)

	// Canonical indexes with exact column sequences.
	for idx, wantCols := range frozenProjectsIndexColumns {
		requireIndexColumns(t, ctx, db, "projects", idx, wantCols)
	}
	for idx, wantCols := range frozenProjectsUniqueIndexColumns {
		requireUniqueIndexColumns(t, ctx, db, "projects", idx, wantCols)
	}
	for idx, wantCols := range frozenProjectGrantsIndexColumns {
		requireIndexColumns(t, ctx, db, "project_grants", idx, wantCols)
	}
	for idx, wantCols := range frozenReviewContextsIndexColumns {
		requireIndexColumns(t, ctx, db, "review_contexts", idx, wantCols)
	}

	// CHECK clauses bounding visibility and role are deliberately NOT
	// asserted at the schema layer. TiDB v8.5 ships with
	// `tidb_enable_check_constraint = OFF` by default, so the CHECK
	// clauses in the migration SQL are parsed but neither enforced nor
	// persisted to information_schema.check_constraints — SHOW CREATE
	// TABLE strips them. Asserting them via this e2e would fail on every
	// environment matching the production default. The visibility/role
	// enum contract is instead enforced at the application layer by the
	// writer/store, which has its own unit tests (see
	// internal/store/review/projects_test.go and C5.2 writer e2e). The
	// migration keeps the CHECK clauses as forward-compat documentation
	// for the day `tidb_enable_check_constraint` flips to ON by default.

	// review_contexts.project_id must NOT be a DB-level FOREIGN KEY — the
	// referential integrity is enforced in application code (GREEN writer's
	// FOR UPDATE machine lock), and a DB FK would introduce lock/cascade
	// hazards the design deliberately avoids.
	requireNoForeignKeyOnColumn(t, ctx, db, "review_contexts", "project_id")
}

// TestProjectsMigration_LegacyReviewsStayNull_E2E seeds a workspace +
// machine + active-human owner at the v242 baseline and inserts six
// review_contexts rows BEFORE applying v243. The dual-approved v243
// contract deliberately does NOT backfill: legacy review rows MUST keep
// project_id = NULL after the cutover, no projects rows MUST be minted
// for them, and the writer-side get-or-create (asserted separately under
// C5.2) is the only path that binds a review to a project.
//
// What this pins:
//   - Six seeded review_contexts rows survive the cutover unchanged.
//   - Every one of them has project_id IS NULL after ApplyMigrations.
//   - Zero projects rows exist for the seeded workspace — the migration
//     does not speculatively mint projects for legacy reviews.
//   - The migration does NOT fail just because reviews exist without
//     projects (no owner preflight, no backfill preflight).
//
// Why this replaced the previous backfill test:
//   - Planner seq=629 / Reviewer2 concur: C5.1 contract is "nullable,
//     no backfill". Strong backfill + NOT NULL tightening is a separate
//     product/data-migration decision requiring explicit sign-off from
//     龙恒. The C5.1 e2e MUST validate the actual contract, not the
//     aspirational one.
//
// What it does NOT pin:
//   - Writer-side binding of new reviews (covered by C5.2 writer e2e).
//   - Project-delete unlink behavior (covered by C5.2 writer e2e).
//   - NULL review exclusion from Project Reviews tab (covered by C5.2).
//
// TestProjectsMigration_LegacyReviewsStayNull_E2E replays the same full
// migration chain and must stay serial for the same Unistore DDL-race reason
// as TestProjectsMigration_Cutover_E2E above.
func TestProjectsMigration_LegacyReviewsStayNull_E2E(t *testing.T) {
	ctx, cancel := context.WithTimeout(context.Background(), 2*time.Minute)
	defer cancel()

	dsn := e2etestkit.StartTiDBAtMigration(t, e2eTiDBRuntimeFixture.RootDSN(t), 242)
	db := e2etestkit.OpenE2EDB(t, dsn)
	defer db.Close()

	ws := e2etestkit.SeedWorkspaceWithMachineAndOwner(t, ctx, db, "no-backfill")
	seedSixLegacyReviewContexts(t, ctx, db, ws)

	require.NoError(t, tidb.ApplyMigrations(ctx, db),
		"v243 must NOT fail when legacy reviews exist without projects — no owner preflight, no backfill preflight")

	// Six review_contexts survive; each one has project_id IS NULL.
	rows, err := db.QueryContext(ctx, `
SELECT id, project_id FROM review_contexts
WHERE workspace_id = ?
ORDER BY id
`, ws.WorkspaceID)
	require.NoError(t, err)
	defer rows.Close()

	type review struct {
		ID        string
		ProjectID sql.NullString
	}
	var got []review
	for rows.Next() {
		var r review
		require.NoError(t, rows.Scan(&r.ID, &r.ProjectID))
		require.False(t, r.ProjectID.Valid,
			"legacy review %q must keep project_id = NULL after cutover — v243 does not backfill", r.ID)
		got = append(got, r)
	}
	require.NoError(t, rows.Err())
	require.Len(t, got, 6, "six legacy review_contexts rows must survive cutover unchanged")

	// Zero projects minted for the seeded workspace — the migration is
	// schema-only and does not speculatively create projects for legacy
	// reviews. Project minting is the writer's responsibility (C4.3 +
	// C5.2). This is the load-bearing assertion: a future regression
	// that adds eager backfill would surface here, not silently pass.
	var projectCount int
	require.NoError(t,
		db.QueryRowContext(ctx,
			`SELECT COUNT(*) FROM projects WHERE workspace_id = ?`,
			ws.WorkspaceID,
		).Scan(&projectCount),
	)
	require.Equal(t, 0, projectCount,
		"v243 MUST NOT speculatively mint projects for legacy reviews — got %d rows; backfill would violate the dual-approved nullable contract", projectCount)
}

// TestProjectsMigration_FailBeforeDDL_E2E was removed under C5.1: the
// dual-approved v243 contract has NO owner preflight, because there is no
// backfill. ApplyMigrations only adds schema (tables + columns + indexes),
// and schema-only migrations have nothing to "fail before DDL" on. The
// owner-resolution path is enforced at writer time via GetOrCreateProject-
// For{User,Agent}, which is covered by C5.2 writer e2e. Keeping this test
// would force a preflight that the contract explicitly excludes.

// TestVerifySchema_FreshBootstrapPasses_E2E runs a fresh bootstrap +
// ApplyMigrations against a clean TiDB and asserts both that the
// post-migration schema advertises the projects table and that
// admin.VerifySchema accepts the resulting schema.
//
// RED failure mode: at v242 the projects table does not exist; the
// existence assertion fails before VerifySchema is consulted.
func TestVerifySchema_FreshBootstrapPasses_E2E(t *testing.T) {
	t.Parallel()

	ctx, cancel := context.WithTimeout(context.Background(), 2*time.Minute)
	defer cancel()

	dsn := startE2EInitializedTiDB(t)
	db := testkit.OpenTiDB(t, dsn)
	defer db.Close()

	require.True(t, tableExists(t, ctx, db, "projects"),
		"fresh bootstrap + ApplyMigrations must produce a projects table")
	require.True(t, tableExists(t, ctx, db, "project_grants"),
		"fresh bootstrap + ApplyMigrations must produce a project_grants table")

	// Baseline: VerifySchema MUST accept the freshly-migrated schema. Each
	// negative case below starts from this green baseline before dropping a
	// single artifact.
	admin := tidb.NewAdmin()
	require.NoError(t, admin.VerifySchema(ctx, db),
		"VerifySchema must accept the fully-migrated fresh-bootstrap schema (baseline)")
}

// TestVerifySchema_Negative_E2E asserts that admin.VerifySchema catches
// missing schema artifacts by removing each one in turn from a fresh,
// fully-migrated database and expecting VerifySchema to fail with an error
// that names the missing artifact.
//
// Each subtest follows the same single-variable pattern:
//
//  1. Baseline pass: confirm VerifySchema accepts the freshly-migrated
//     schema before anything is removed.
//  2. Single artifact removal: DROP one index, DROP one table, or DROP one
//     column.
//  3. Error contains the artifact name: the GREEN VerifySchema error must
//     identify which artifact is missing, not a generic "schema invalid".
//
// Cases cover the five canonical indexes (three on projects, one on
// project_grants, one on review_contexts) plus missing projects table,
// missing project_grants table, and missing review_contexts.project_id
// column — so a drift in any of those GREEN contract dimensions surfaces
// here.
//
// RED failure mode: at v242 the setup cannot drop the projects/project_grants
// tables or the project_id column because none exist; the IF EXISTS guard
// makes the DROP a no-op, but the baseline VerifySchema call fails (or the
// post-drop existence pre-check fails).
func TestVerifySchema_Negative_E2E(t *testing.T) {
	t.Parallel()

	cases := []struct {
		name       string
		arrange    func(t *testing.T, ctx context.Context, db *sql.DB)
		wantSubstr string
	}{
		{
			name: "ProjectsWorkspaceUpdatedIndexMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP INDEX `+projectsWorkspaceUpdatedIndex+` ON projects`)
				require.NoError(t, err, "drop %s", projectsWorkspaceUpdatedIndex)
			},
			wantSubstr: projectsWorkspaceUpdatedIndex,
		},
		{
			name: "ProjectsWorkspaceMachineIndexMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP INDEX `+projectsWorkspaceMachineIndex+` ON projects`)
				require.NoError(t, err, "drop %s", projectsWorkspaceMachineIndex)
			},
			wantSubstr: projectsWorkspaceMachineIndex,
		},
		{
			name: "ProjectsWorkspaceOwnerUpdatedIndexMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP INDEX `+projectsWorkspaceOwnerUpdatedIndex+` ON projects`)
				require.NoError(t, err, "drop %s", projectsWorkspaceOwnerUpdatedIndex)
			},
			wantSubstr: projectsWorkspaceOwnerUpdatedIndex,
		},
		{
			name: "ProjectGrantsMemberUpdatedIndexMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP INDEX `+projectGrantsMemberUpdatedIndex+` ON project_grants`)
				require.NoError(t, err, "drop %s", projectGrantsMemberUpdatedIndex)
			},
			wantSubstr: projectGrantsMemberUpdatedIndex,
		},
		{
			name: "ReviewContextsWorkspaceProjectUpdatedIndexMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP INDEX `+reviewContextsWorkspaceProjectUpdatedIndex+` ON review_contexts`)
				require.NoError(t, err, "drop %s", reviewContextsWorkspaceProjectUpdatedIndex)
			},
			wantSubstr: reviewContextsWorkspaceProjectUpdatedIndex,
		},
		{
			name: "ProjectsTableMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP TABLE projects`)
				require.NoError(t, err, "drop projects table")
			},
			wantSubstr: "projects",
		},
		{
			name: "ProjectGrantsTableMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				_, err := db.ExecContext(ctx, `DROP TABLE project_grants`)
				require.NoError(t, err, "drop project_grants table")
			},
			wantSubstr: "project_grants",
		},
		{
			name: "ReviewContextsProjectIDColumnMissing",
			arrange: func(t *testing.T, ctx context.Context, db *sql.DB) {
				// The composite review_contexts_workspace_project_updated_idx
				// index covers project_id; TiDB refuses to drop a column that
				// a composite index depends on, so the arrange must drop the
				// index first. The test's intent is "VerifySchema catches a
				// missing column", and the index drop is just the necessary
				// TiDB unlock — VerifySchema would catch the missing index
				// separately via the dedicated index negative case.
				_, err := db.ExecContext(ctx, `ALTER TABLE review_contexts DROP INDEX `+reviewContextsWorkspaceProjectUpdatedIndex)
				require.NoError(t, err, "drop %s before dropping project_id", reviewContextsWorkspaceProjectUpdatedIndex)
				_, err = db.ExecContext(ctx, `ALTER TABLE review_contexts DROP COLUMN project_id`)
				require.NoError(t, err, "drop review_contexts.project_id")
			},
			wantSubstr: "project_id",
		},
	}

	for _, tc := range cases {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()

			ctx, cancel := context.WithTimeout(context.Background(), 2*time.Minute)
			defer cancel()

			dsn := startE2EInitializedTiDB(t)
			db := testkit.OpenTiDB(t, dsn)
			defer db.Close()

			// Baseline: the freshly-migrated schema MUST pass VerifySchema
			// before we touch it. Without this guard the negative case can
			// be falsely "passing" via an unrelated error.
			admin := tidb.NewAdmin()
			require.NoError(t, admin.VerifySchema(ctx, db),
				"baseline: VerifySchema must accept the freshly-migrated schema before %s breaks it", tc.name)

			// Remove exactly one artifact.
			tc.arrange(t, ctx, db)

			// VerifySchema must fail AND the error must name the artifact
			// that was removed — a generic "schema invalid" error is not
			// acceptable.
			err := admin.VerifySchema(ctx, db)
			require.Error(t, err, "VerifySchema must reject a schema missing %s", tc.name)
			require.Contains(t, err.Error(), tc.wantSubstr,
				"VerifySchema error must name the missing artifact (%s)", tc.wantSubstr)
		})
	}
}

// seedSixLegacyReviewContexts inserts six review_contexts rows at the v242
// baseline. The shape is deliberately varied across the dedup dimensions
// (machine/root pairs + byte-exact trailing-space variant) so a regression
// that adds eager backfill would surface as a non-zero projectCount in
// TestProjectsMigration_LegacyReviewsStayNull_E2E. Under the dual-approved
// nullable contract, none of these rows receive a project_id during the
// migration — they stay NULL until the writer-side get-or-create binds them
// on next create/update (asserted in C5.2 writer e2e).
func seedSixLegacyReviewContexts(t *testing.T, ctx context.Context, db *sql.DB, ws e2etestkit.OwnershipFixture) {
	t.Helper()

	const insert = `
INSERT INTO review_contexts
  (id, workspace_id, target_hash, machine_id, root_path, base_ref, head_ref,
   display_name, created_by_member_id, updated_by_member_id, created_at, updated_at)
VALUES
  -- alpha x2: same machine/root, distinct head_ref.
  ('rev_alpha_1', ?, 'hash_alpha_a', ?, '/repo/alpha', 'main', 'feature-a',
   'alpha-a', ?, ?, NOW(), NOW()),
  ('rev_alpha_2', ?, 'hash_alpha_b', ?, '/repo/alpha', 'main', 'feature-b',
   'alpha-b', ?, ?, NOW(), NOW()),

  -- beta x2: same machine/root, distinct head_ref.
  ('rev_beta_1', ?, 'hash_beta_a', ?, '/repo/beta', 'main', 'feature-a',
   'beta-a', ?, ?, NOW(), NOW()),
  ('rev_beta_2', ?, 'hash_beta_b', ?, '/repo/beta', 'main', 'feature-b',
   'beta-b', ?, ?, NOW(), NOW()),

  -- gamma: trailing-space variant MUST be byte-exact distinct from gamma.
  ('rev_gamma_1', ?, 'hash_gamma', ?, '/repo/gamma', 'main', 'feature',
   'gamma', ?, ?, NOW(), NOW()),
  ('rev_gamma_2', ?, 'hash_gamma_trailing', ?, '/repo/gamma ', 'main', 'feature',
   'gamma-trailing', ?, ?, NOW(), NOW())
`
	_, err := db.ExecContext(ctx, insert,
		ws.WorkspaceID, ws.MachineID, ws.OwnerMemberID, ws.OwnerMemberID,
		ws.WorkspaceID, ws.MachineID, ws.OwnerMemberID, ws.OwnerMemberID,
		ws.WorkspaceID, ws.MachineID, ws.OwnerMemberID, ws.OwnerMemberID,
		ws.WorkspaceID, ws.MachineID, ws.OwnerMemberID, ws.OwnerMemberID,
		ws.WorkspaceID, ws.MachineID, ws.OwnerMemberID, ws.OwnerMemberID,
		ws.WorkspaceID, ws.MachineID, ws.OwnerMemberID, ws.OwnerMemberID,
	)
	require.NoError(t, err, "seed six legacy review_contexts rows")
}

// seedReviewForBlocker + latestGooseVersion were removed under C5.1: both
// were used only by TestProjectsMigration_FailBeforeDDL_E2E, which is gone
// (no owner preflight under the nullable/no-backfill contract). If a future
// migration re-introduces a preflight, restore these helpers alongside the
// test that needs them rather than leaving them unused in the file.

// requireColumnsExact asserts the table's column set and order exactly match
// `want`. A missing column, an extra column, or a column-order drift all
// surface here.
func requireColumnsExact(t *testing.T, ctx context.Context, db *sql.DB, table string, want []string) {
	t.Helper()

	rows, err := db.QueryContext(ctx, `
SELECT column_name FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = ?
ORDER BY ordinal_position
`, table)
	require.NoError(t, err, "query columns for %s", table)
	defer rows.Close()

	var got []string
	for rows.Next() {
		var c string
		require.NoError(t, rows.Scan(&c))
		got = append(got, c)
	}
	require.NoError(t, rows.Err(), "iterate columns for %s", table)

	require.Equal(t, want, got,
		"%s columns must match the frozen contract exactly (set AND order)", table)
}

// requirePrimaryKey asserts the table's primary key column sequence exactly
// matches `wantCols`. Used to pin the composite (project_id, member_id) PK
// on project_grants.
func requirePrimaryKey(t *testing.T, ctx context.Context, db *sql.DB, table string, wantCols []string) {
	t.Helper()

	rows, err := db.QueryContext(ctx, `
SELECT column_name FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = ? AND index_name = 'PRIMARY'
ORDER BY seq_in_index
`, table)
	require.NoError(t, err, "query PK for %s", table)
	defer rows.Close()

	var got []string
	for rows.Next() {
		var c string
		require.NoError(t, rows.Scan(&c))
		got = append(got, c)
	}
	require.NoError(t, rows.Err(), "iterate PK columns for %s", table)

	require.Equal(t, wantCols, got,
		"%s primary key column sequence must match the frozen contract", table)
}

// requireIndexColumns asserts the named index on the named table exists with
// the exact column sequence `wantCols` AND is registered NON-UNIQUE
// (non_unique = 1). A missing index, an index with extra/missing columns, a
// column-order drift, or an unexpected UNIQUE constraint all surface here.
//
// The non_unique assertion is what prevents the GREEN migration from
// silently upgrading a secondary KEY to UNIQUE — every frozen secondary
// index is non-unique by design (uniqueness is reserved for the composite
// PK on project_grants, asserted separately via requirePrimaryKey).
func requireIndexColumns(t *testing.T, ctx context.Context, db *sql.DB, table, index string, wantCols []string) {
	t.Helper()

	rows, err := db.QueryContext(ctx, `
SELECT column_name, non_unique FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?
ORDER BY seq_in_index
`, table, index)
	require.NoError(t, err, "query index %s.%s", table, index)
	defer rows.Close()

	var got []string
	var nonUnique int
	for rows.Next() {
		var c string
		var nu int
		require.NoError(t, rows.Scan(&c, &nu))
		got = append(got, c)
		nonUnique = nu
	}
	require.NoError(t, rows.Err(), "iterate index columns for %s.%s", table, index)

	require.Equal(t, wantCols, got,
		"index %s on %s must match the frozen column sequence", index, table)
	require.Equal(t, 1, nonUnique,
		"secondary index %s on %s must be non-unique (non_unique=1); a UNIQUE upgrade would silently change the contract",
		index, table)
}

// requireUniqueIndexColumns asserts the named index on the named table exists
// with the exact column sequence `wantCols` AND is registered UNIQUE
// (non_unique = 0). This is the UNIQUE counterpart to requireIndexColumns:
// the same column-sequence pin, the opposite uniqueness flag. It backs the
// get-or-create identity invariants — UNIQUE(workspace_id, target_hash) is
// load-bearing for the C4 revise race-safety contract, so a drift to a
// regular non-unique index must surface here, not silently pass.
func requireUniqueIndexColumns(t *testing.T, ctx context.Context, db *sql.DB, table, index string, wantCols []string) {
	t.Helper()

	rows, err := db.QueryContext(ctx, `
SELECT column_name, non_unique FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?
ORDER BY seq_in_index
`, table, index)
	require.NoError(t, err, "query unique index %s.%s", table, index)
	defer rows.Close()

	var got []string
	var nonUnique int
	for rows.Next() {
		var c string
		var nu int
		require.NoError(t, rows.Scan(&c, &nu))
		got = append(got, c)
		nonUnique = nu
	}
	require.NoError(t, rows.Err(), "iterate unique index columns for %s.%s", table, index)

	require.Equal(t, wantCols, got,
		"unique index %s on %s must match the frozen column sequence", index, table)
	require.Equal(t, 0, nonUnique,
		"unique index %s on %s must be UNIQUE (non_unique=0); a non-unique downgrade would silently break the get-or-create identity contract",
		index, table)
}

// requireCheckEnumBounds, quoteEnum, normalizeCheckClause, charsetIntroducerRe,
// and TestNormalizeCheckClause were removed under C5.1. TiDB v8.5 default
// `tidb_enable_check_constraint = OFF` means CHECK clauses are parsed but
// neither enforced nor persisted to information_schema.check_constraints, so
// no e2e assertion against the runtime schema can pass on the production
// default. The migration keeps the CHECK clauses as forward-compat
// documentation; the enum contract itself is enforced at the application
// layer (writer/store), covered by unit tests and C5.2 writer e2e.

// requireNoForeignKeyOnColumn asserts that the named column does NOT
// participate in a database-level FOREIGN KEY constraint. Used to pin the
// contract that review_contexts.project_id is NOT a DB FK — the
// referential integrity is enforced in application code (the GREEN writer
// ensures project_id always points at an existing projects.id via the
// FOR UPDATE machine lock), and adding a DB FK would introduce lock
// ordering and cascade hazards that the design deliberately avoids.
func requireNoForeignKeyOnColumn(t *testing.T, ctx context.Context, db *sql.DB, table, column string) {
	t.Helper()

	var n int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*) FROM information_schema.key_column_usage
WHERE table_schema = DATABASE()
  AND table_name = ?
  AND column_name = ?
  AND referenced_table_name IS NOT NULL
`, table, column).Scan(&n),
		"query key_column_usage for FK on %s.%s", table, column)
	require.Zero(t, n,
		"%s.%s must NOT participate in a DB-level FOREIGN KEY — referential integrity is enforced in application code",
		table, column)
}

// tableExists reports whether the named table exists in the current database.
func tableExists(t *testing.T, ctx context.Context, db *sql.DB, table string) bool {
	t.Helper()
	var n int
	err := db.QueryRowContext(ctx, `
SELECT 1 FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = ?
`, table).Scan(&n)
	if err == sql.ErrNoRows {
		return false
	}
	require.NoError(t, err, "query information_schema.tables for %s", table)
	return true
}

// columnMeta reports both the IS_NULLABLE flag ("YES"/"NO") and the
// COLUMN_DEFAULT (raw textual form as stored in information_schema.columns,
// with the same convention MySQL uses: NULL for an explicit NULL default,
// the literal text for everything else). exists is false when the column is
// absent.
//
// The default is the load-bearing half of the
// review_contexts.project_id NULL DEFAULT NULL contract: a future
// migration that ships nullable + DEFAULT ” would still pass an
// IS_NULLABLE-only check but would silently change the writer's
// zero-value semantics. Both halves must be asserted together.
func columnMeta(t *testing.T, ctx context.Context, db *sql.DB, table, column string) (nullable string, columnDefault sql.NullString, exists bool) {
	t.Helper()
	err := db.QueryRowContext(ctx, `
SELECT is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?
`, table, column).Scan(&nullable, &columnDefault)
	if err == sql.ErrNoRows {
		return "", sql.NullString{}, false
	}
	require.NoError(t, err, "query information_schema.columns (is_nullable, column_default) for %s.%s", table, column)
	return nullable, columnDefault, true
}