package bootstrapimpl

import (
	"context"
	"regexp"
	"strings"
	"testing"

	"database/sql"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
)

// declaredProofColumn is the verifier's real declaration, so these tests compare
// against the shipped catalog rather than a hand-written stand-in.
func declaredProofColumn(t *testing.T) schema.ColumnDefinition {
	t.Helper()
	declared, ok := declaredSessionControlProofColumn(GetSystemTableDefinitions())
	if !ok {
		t.Fatalf("catalog does not declare %s.%s", constants.TableSession, constants.FieldSysSession_ControlProofHash)
	}
	return declared
}

// declaredShape is the physical shape that matches the verifier's declaration
// exactly, built from the declaration itself so the tests cannot drift from it.
func declaredShape(declared schema.ColumnDefinition) PhysicalColumnShape {
	return PhysicalColumnShape{
		ColumnType:    strings.ToLower(DesiredColumnType(declared)),
		Nullable:      declared.Nullable,
		CollationName: sql.NullString{String: declared.CollationName, Valid: declared.CollationName != ""},
	}
}

func sessionShapes(shape *PhysicalColumnShape) map[string]map[string]PhysicalColumnShape {
	if shape == nil {
		return map[string]map[string]PhysicalColumnShape{
			strings.ToLower(constants.TableSession): {},
		}
	}
	return map[string]map[string]PhysicalColumnShape{
		strings.ToLower(constants.TableSession): {
			strings.ToLower(constants.FieldSysSession_ControlProofHash): *shape,
		},
	}
}

// sessionColumnShapes keeps the type-only helper for cases that only vary the type.
func sessionColumnShapes(t *testing.T, columnType string) map[string]map[string]PhysicalColumnShape {
	t.Helper()
	if columnType == "" {
		return sessionShapes(nil)
	}
	shape := declaredShape(declaredProofColumn(t))
	shape.ColumnType = strings.ToLower(columnType)
	return sessionShapes(&shape)
}

func expectAmbiguityCount(mock sqlmock.Sqlmock, ambiguous int) {
	mock.ExpectQuery(regexp.QuoteMeta("AND COALESCE(`" + constants.FieldSysSession_IsRevoked + "`, 0) = 0")).
		WillReturnRows(sqlmock.NewRows([]string{"ambiguous"}).AddRow(ambiguous))
}

// expectDuplicateRowCount is the second, wider count: every row sharing a verifier,
// which is what the physical unique index would see.
func expectDuplicateRowCount(mock sqlmock.Sqlmock, duplicates int) {
	mock.ExpectQuery(regexp.QuoteMeta("AS duplicate_rows")).
		WillReturnRows(sqlmock.NewRows([]string{"duplicates"}).AddRow(duplicates))
}

// TestProofColumnClassificationSeparatesAbsentFromIncompatible is the distinction
// the preflight depends on. Collapsing these two into one error is what let a
// truncating column be treated as "not deployed yet" and skipped.
func TestProofColumnClassificationSeparatesAbsentFromIncompatible(t *testing.T) {
	declared := declaredProofColumn(t)
	desired := DesiredColumnType(declared)

	notNull := declaredShape(declared)
	notNull.Nullable = false
	caseFolding := declaredShape(declared)
	caseFolding.CollationName = sql.NullString{String: "utf8mb4_general_ci", Valid: true}

	cases := map[string]struct {
		shapes map[string]map[string]PhysicalColumnShape
		want   sessionControlProofColumnState
	}{
		"column not deployed":    {sessionShapes(nil), sessionControlProofColumnAbsent},
		"table not deployed":     {map[string]map[string]PhysicalColumnShape{}, sessionControlProofColumnAbsent},
		"declared shape":         {sessionColumnShapes(t, desired), sessionControlProofColumnMatchesDeclaration},
		"narrower than declared": {sessionColumnShapes(t, "char(16)"), sessionControlProofColumnCannotHoldVerifier},
		"different family":       {sessionColumnShapes(t, "varbinary(3000)"), sessionControlProofColumnCannotHoldVerifier},
		// Both of these can hold the value but violate a declared property that the
		// verifier contract depends on.
		"NOT NULL":               {sessionShapes(&notNull), sessionControlProofColumnShapeMismatch},
		"case-folding collation": {sessionShapes(&caseFolding), sessionControlProofColumnShapeMismatch},
	}
	for name, tc := range cases {
		t.Run(name, func(t *testing.T) {
			if got := classifySessionControlProofColumn(tc.shapes, declared); got != tc.want {
				t.Fatalf("state = %d, want %d", got, tc.want)
			}
		})
	}
}

// TestPreflightFailsClosedOnAnIncompatibleColumn is the finding this replaced: an
// incompatible column must not be mistaken for an undeployed one, because a unique
// index over a truncating verifier turns distinct proofs into collisions.
// TestPreflightIsInertBeforeTheColumnIsDeployed keeps an old binary and a new one
// able to run against the same database during the staged window. A reconciliation
// that is about to add the column has no rows that could collide.
// TestPreflightIsInertWhenTheColumnIsNotDeclared keeps the preflight from acting on
// table sets that do not include the session table.
// TestPreflightFailsClosedOnUsableCollisionsWithoutMutating is the design's
// requirement: detect collisions and fail closed rather than choosing a row. The
// error names session Ids, never verifier values, and nothing is written.
// TestPreflightClearsOnlyUnusableDuplicates covers the other half: an expired or
// revoked row cannot authenticate but would still block the DDL, so its verifier is
// removed rather than the upgrade being unable to converge.
// TestPreflightSurfacesACollisionQueryFailure keeps a broken database from silently
// skipping the collision check before the unique index is installed.
// TestReconcileRunsTheProofPreflightBeforeAddingTheUniqueIndex is the ordering
// guard. Marking the verifier unique makes ReconcileTableColumns itself create the
// index, so a preflight that ran afterwards could never clean up or report, and an
// upgrade holding stale duplicate rows could never converge. sqlmock is ordered, so
// this fails if the preflight moves after the DDL.
// TestReconcileFailsBeforeTheIndexWhenUsableSessionsCollide states the same ordering
// as an outcome: with colliding live sessions, reconciliation must stop before the
// constraint is installed over ambiguous data.
// TestClearStatementCanOnlyTouchUnusableRows guards the most dangerous mistake
// available here. The statement removes credential material, so if its restriction
// to expired or revoked rows were dropped it would clear the verifier of live
// sessions, silently breaking every signed-in browser. A mock cannot evaluate a
// predicate, so the restriction is asserted as SQL text.
// TestAmbiguityQueryCountsOnlyUsableSessions pins the predicate rather than trusting
// the prose: an expired or revoked row cannot authenticate, so it must not count as
// an ambiguity in the lookup.
func TestAmbiguityQueryCountsOnlyUsableSessions(t *testing.T) {
	query := sessionControlProofAmbiguityQuery()
	for _, want := range []string{
		"`" + constants.FieldSysSession_ControlProofHash + "` IS NOT NULL",
		"COALESCE(`" + constants.FieldSysSession_IsRevoked + "`, 0) = 0",
		"`" + constants.FieldSysSession_ExpiresAt + "` > NOW()",
		"HAVING COUNT(*) > 1",
	} {
		if !strings.Contains(query, want) {
			t.Fatalf("ambiguity query is missing %q:\n%s", want, query)
		}
	}
}

// TestUniqueIndexNameComesFromTheReconcilersGenerator keeps the readiness check and
// the index that gets created from drifting apart on the name.
func TestUniqueIndexNameComesFromTheReconcilersGenerator(t *testing.T) {
	want := schema.GenerateUniqueIndexName(constants.TableSession, constants.FieldSysSession_ControlProofHash)
	if got := sessionControlProofUniqueIndexName(); got != want {
		t.Fatalf("index name = %q, want the generator's %q", got, want)
	}
}

func expectPhysicalShapeLoad(mock sqlmock.Sqlmock, columnType string, declared schema.ColumnDefinition) {
	rows := sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"})
	if columnType != "" {
		rows = rows.AddRow(constants.TableSession, constants.FieldSysSession_ControlProofHash,
			strings.ToLower(columnType), "YES", nil, declared.CollationName)
	}
	mock.ExpectQuery(regexp.QuoteMeta("FROM INFORMATION_SCHEMA.COLUMNS")).WillReturnRows(rows)
}

func expectProofUniqueIndex(mock sqlmock.Sqlmock, rows *sqlmock.Rows) {
	mock.ExpectQuery(regexp.QuoteMeta("SELECT NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART")).
		WithArgs(constants.TableSession, sessionControlProofUniqueIndexName()).
		WillReturnRows(rows)
}

func exactUniqueIndexRows() *sqlmock.Rows {
	return sqlmock.NewRows([]string{"NON_UNIQUE", "SEQ_IN_INDEX", "COLUMN_NAME", "SUB_PART"}).
		AddRow(0, 1, constants.FieldSysSession_ControlProofHash, nil)
}

// A prefix index is returned by the scoped inspection and rejected by the
// shared verifier because its SUB_PART is non-NULL.
func prefixUniqueIndexRows() *sqlmock.Rows {
	return sqlmock.NewRows([]string{"NON_UNIQUE", "SEQ_IN_INDEX", "COLUMN_NAME", "SUB_PART"}).
		AddRow(0, 1, constants.FieldSysSession_ControlProofHash, 16)
}

func TestProofLookupReadinessRequiresIndexAndUnambiguity(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	declared := declaredProofColumn(t)
	expectPhysicalShapeLoad(mock, DesiredColumnType(declared), declared)
	expectProofUniqueIndex(mock, exactUniqueIndexRows())
	expectAmbiguityCount(mock, 0)
	expectDuplicateRowCount(mock, 0)

	got, err := InspectSessionControlProofLookupReadiness(context.Background(), db)
	if err != nil {
		t.Fatalf("InspectSessionControlProofLookupReadiness: %v", err)
	}
	if !got.Ready || !got.ColumnDeployed || !got.ColumnShapeCompatible || !got.UniqueIndexPresent {
		t.Fatalf("want a ready verdict recording every precondition: %+v", got)
	}
	if got.Reason != "" {
		t.Fatalf("a ready verdict must carry no reason, got %q", got.Reason)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestProofLookupReadinessRejectsAPrefixUniqueIndex is why the index check is
// delegated. A prefix unique index is unique on only the first characters: it would
// reject legitimately distinct random proofs and break session creation while
// looking like a valid verifier index. An absent index takes the same path.
func TestProofLookupReadinessRejectsAPrefixUniqueIndex(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	declared := declaredProofColumn(t)
	expectPhysicalShapeLoad(mock, DesiredColumnType(declared), declared)
	expectProofUniqueIndex(mock, prefixUniqueIndexRows())
	expectAmbiguityCount(mock, 0)
	expectDuplicateRowCount(mock, 0)

	got, err := InspectSessionControlProofLookupReadiness(context.Background(), db)
	if err != nil {
		t.Fatalf("InspectSessionControlProofLookupReadiness: %v", err)
	}
	if got.Ready || got.UniqueIndexPresent {
		t.Fatalf("a prefix or absent index must not satisfy readiness: %+v", got)
	}
	if got.Reason == "" {
		t.Fatal("a failed verdict must carry the owner's reason")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestProofLookupReadinessFailsClosedOnUsableCollisionsEvenWhenIndexed is the
// invariant the schema cannot express: a physical unique index cannot tell a live
// session from an expired or revoked one.
func TestProofLookupReadinessFailsClosedOnUsableCollisionsEvenWhenIndexed(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	declared := declaredProofColumn(t)
	expectPhysicalShapeLoad(mock, DesiredColumnType(declared), declared)
	expectProofUniqueIndex(mock, exactUniqueIndexRows())
	expectAmbiguityCount(mock, 2)
	expectDuplicateRowCount(mock, 2)

	got, err := InspectSessionControlProofLookupReadiness(context.Background(), db)
	if err != nil {
		t.Fatalf("InspectSessionControlProofLookupReadiness: %v", err)
	}
	if got.Ready {
		t.Fatal("colliding usable sessions must fail closed even with the index present")
	}
	if got.AmbiguousUsableSessions != 2 {
		t.Fatalf("AmbiguousUsableSessions = %d, want 2", got.AmbiguousUsableSessions)
	}
	if !strings.Contains(got.Reason, "revoke") {
		t.Fatalf("reason = %q, want it to tell the operator what to do", got.Reason)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

// TestProofLookupReadinessDistinguishesAnUndeployedColumnFromATruncatingOne is the
// reporting half of the same distinction the preflight acts on.
func TestProofLookupReadinessDistinguishesAnUndeployedColumnFromATruncatingOne(t *testing.T) {
	declared := declaredProofColumn(t)

	t.Run("undeployed", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()
		expectPhysicalShapeLoad(mock, "", declared)

		got, err := InspectSessionControlProofLookupReadiness(context.Background(), db)
		if err != nil {
			t.Fatalf("InspectSessionControlProofLookupReadiness: %v", err)
		}
		if got.Ready || got.ColumnDeployed {
			t.Fatalf("an undeployed column cannot be ready: %+v", got)
		}
		if !strings.Contains(got.Reason, "not deployed") {
			t.Fatalf("reason = %q, want it to report the column as undeployed", got.Reason)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("unmet sql expectations: %v", err)
		}
	})

	t.Run("truncating", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()
		expectPhysicalShapeLoad(mock, "char(16)", declared)

		got, err := InspectSessionControlProofLookupReadiness(context.Background(), db)
		if err != nil {
			t.Fatalf("InspectSessionControlProofLookupReadiness: %v", err)
		}
		if got.Ready || got.ColumnShapeCompatible {
			t.Fatalf("a truncating column must not be reported ready: %+v", got)
		}
		if !got.ColumnDeployed {
			t.Fatal("a truncating column is deployed; conflating it with absence is the defect this separates")
		}
		if !strings.Contains(got.Reason, "char(16)") {
			t.Fatalf("reason = %q, want it to name the physical type", got.Reason)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("unmet sql expectations: %v", err)
		}
	})
}

// TestSessionCatalogDeclaresTheVerifierAndNotYetItsUniqueIndex pins the two catalog
// facts this work depends on. Flipping the declaration makes ReconcileTableColumns
// install the physical index on every deployment, so it belongs to the step that
// also switches the durable lookup, not to this dormant one.
func TestSessionCatalogDeclaresTheVerifierAndNotYetItsUniqueIndex(t *testing.T) {
	declared, ok := declaredSessionControlProofColumn(GetSystemTableDefinitions())
	if !ok {
		t.Fatalf("%s has no %s column; there is nothing to inspect",
			constants.TableSession, constants.FieldSysSession_ControlProofHash)
	}
	if declared.Unique {
		t.Fatalf("%s.%s is now declared unique; the step that flips it must also switch the durable lookup and prove the migration is collision-free",
			constants.TableSession, constants.FieldSysSession_ControlProofHash)
	}
	if declared.Type == "" {
		t.Fatal("the verifier column declares no physical type, so readiness cannot check its shape")
	}
}

// TestProofLookupReadinessRefusesEveryDeclaredContractViolation is the reporting
// rule: readiness is a verdict, not a repair path, so a column that can hold the
// value but breaks a declared property must not be called ready. Nullability
// matters because a session with no browser proof stores NULL; the binary collation
// matters because verifier comparison — and therefore uniqueness — must be exact
// rather than case-folding.
func TestProofLookupReadinessRefusesEveryDeclaredContractViolation(t *testing.T) {
	declared := declaredProofColumn(t)

	notNull := declaredShape(declared)
	notNull.Nullable = false
	caseFolding := declaredShape(declared)
	caseFolding.CollationName = sql.NullString{String: "utf8mb4_general_ci", Valid: true}

	cases := map[string]struct {
		shape      PhysicalColumnShape
		wantReason string
	}{
		"NOT NULL":               {notNull, "stores NULL"},
		"case-folding collation": {caseFolding, "case-folding"},
	}
	for name, tc := range cases {
		t.Run(name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock.New: %v", err)
			}
			defer db.Close()

			rows := sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"})
			nullable := "YES"
			if !tc.shape.Nullable {
				nullable = "NO"
			}
			rows = rows.AddRow(constants.TableSession, constants.FieldSysSession_ControlProofHash,
				tc.shape.ColumnType, nullable, nil, tc.shape.CollationName.String)
			mock.ExpectQuery(regexp.QuoteMeta("FROM INFORMATION_SCHEMA.COLUMNS")).WillReturnRows(rows)

			got, err := InspectSessionControlProofLookupReadiness(context.Background(), db)
			if err != nil {
				t.Fatalf("InspectSessionControlProofLookupReadiness: %v", err)
			}
			if got.Ready || got.ColumnShapeCompatible {
				t.Fatalf("a column violating its declared contract must not be ready: %+v", got)
			}
			if !got.ColumnDeployed {
				t.Fatal("the column is deployed; conflating a contract violation with absence is the defect this separates")
			}
			if !strings.Contains(got.Reason, tc.wantReason) {
				t.Fatalf("reason = %q, want it to explain %q", got.Reason, tc.wantReason)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("unmet sql expectations: %v", err)
			}
		})
	}
}

// TestPreflightStillChecksCollisionsWhenTheShapeIsRepairable is the deliberate
// difference from readiness. This preflight runs *before* the same reconciliation
// pass repairs the column, so failing on a repairable mismatch would leave such a
// deployment permanently unable to converge: the fix runs after this point.
//
// Continuing is safe for the collision check specifically. The declared collation is
// binary, the strictest case, so counting duplicates under a laxer physical
// collation can only over-report — any pair equal under the binary declaration is
// also equal under a case-folding one, never the reverse — so the check errs toward
// failing closed.
// TestPreflightStillFailsClosedOnCollisionsWithARepairableShape keeps the
// convergence allowance above from weakening the safety property: a repairable shape
// does not excuse installing a unique index over colliding live sessions.