package bootstrapimpl

import (
	"regexp"
	"strings"
	"testing"

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

const catalogGateColumnQuery = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND LOWER(TABLE_NAME) = LOWER(?) AND LOWER(COLUMN_NAME) = LOWER(?)"

// catalogDefsDeclaring builds a minimal catalog fixture declaring the given
// columns on the OAuth custom-scope table. Fixture-driven so these tests do not
// depend on the current pinned catalog's skew-window state and need no rewrite
// when a later catalog bump declares AllowDynamicPublicClients.
func catalogDefsDeclaring(columns ...string) []schema.TableDefinition {
	cols := make([]schema.ColumnDefinition, 0, len(columns))
	for _, c := range columns {
		cols = append(cols, schema.ColumnDefinition{Name: c})
	}
	return []schema.TableDefinition{{TableName: constants.TableOAuthCustomScope, Columns: cols}}
}

// TestCatalogDeclaresManagedColumnMatchesDeclarations pins the self-healing
// trigger purely against fixtures: the matcher reports true exactly when the
// supplied catalog declares the column (by logical Name or explicit
// PhysicalName), on the right table, and false otherwise.
func TestCatalogDeclaresManagedColumnMatchesDeclarations(t *testing.T) {
	col := "AllowDynamicPublicClients"

	if !catalogDeclaresManagedColumn(catalogDefsDeclaring("Id", col), constants.TableOAuthCustomScope, col) {
		t.Fatal("want declared column to match")
	}
	if catalogDeclaresManagedColumn(catalogDefsDeclaring("Id", "IsActive"), constants.TableOAuthCustomScope, col) {
		t.Fatal("want undeclared column to not match")
	}
	// Right column name, wrong table => no match.
	otherTable := []schema.TableDefinition{{TableName: "_System_SomethingElse", Columns: []schema.ColumnDefinition{{Name: col}}}}
	if catalogDeclaresManagedColumn(otherTable, constants.TableOAuthCustomScope, col) {
		t.Fatal("want no match when the declaring table differs")
	}
	// Explicit physical name should also match.
	physical := []schema.TableDefinition{{TableName: constants.TableOAuthCustomScope, Columns: []schema.ColumnDefinition{{Name: "logical", PhysicalName: col}}}}
	if !catalogDeclaresManagedColumn(physical, constants.TableOAuthCustomScope, col) {
		t.Fatal("want match against explicit PhysicalName")
	}
}

// TestCollectCatalogGatedMissingColumnsDormantWhenCatalogOmitsColumn proves that
// when the catalog does not declare the gated column the gate issues no probe
// and reports nothing missing — so a warm-upgrade DB lacking the column still
// starts (the seed-path fallback bridges convergence) instead of bricking.
// Fixture-driven: uses a catalog that omits the column regardless of the pinned
// catalog's current state.
func TestCollectCatalogGatedMissingColumnsDormantWhenCatalogOmitsColumn(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Catalog omits AllowDynamicPublicClients. No ExpectQuery registered: any
	// probe issued for a skew-window column is an unexpected query and fails.
	missing, err := collectCatalogGatedMissingColumns(db, catalogDefsDeclaring("Id", "IsActive"))
	if err != nil {
		t.Fatalf("collectCatalogGatedMissingColumns: %v", err)
	}
	if len(missing) != 0 {
		t.Fatalf("skew-window missing = %v, want empty", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("skew-window gate issued an unexpected schema probe: %v", err)
	}
}

// TestCollectCatalogGatedMissingColumnsFailsClosedOnceCatalogDeclaresColumn
// models the post-migration world: a catalog that declares
// AllowDynamicPublicClients activates the gate. A live schema still missing the
// column must fail closed (readiness reports it missing, forcing an apply before
// serving); a live schema that has the column must pass. This is the
// belt-and-suspenders that stops lifecycle convergence from reporting ready
// while the dynamic-client runtime read path would hit unknown-column.
func TestCollectCatalogGatedMissingColumnsFailsClosedOnceCatalogDeclaresColumn(t *testing.T) {
	catalogWithColumn := catalogDefsDeclaring("Id", "AllowDynamicPublicClients")
	columnQuery := regexp.QuoteMeta(catalogGateColumnQuery)
	want := constants.TableOAuthCustomScope + ".AllowDynamicPublicClients"

	t.Run("live schema missing the declared column fails closed", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()

		mock.ExpectQuery(columnQuery).
			WithArgs(constants.TableOAuthCustomScope, "AllowDynamicPublicClients").
			WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))

		missing, err := collectCatalogGatedMissingColumns(db, catalogWithColumn)
		if err != nil {
			t.Fatalf("collectCatalogGatedMissingColumns: %v", err)
		}
		if len(missing) != 1 || missing[0] != want {
			t.Fatalf("missing = %v, want [%s]", missing, want)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations unmet: %v", err)
		}
	})

	t.Run("live schema with the declared column converges", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()

		mock.ExpectQuery(columnQuery).
			WithArgs(constants.TableOAuthCustomScope, "AllowDynamicPublicClients").
			WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

		missing, err := collectCatalogGatedMissingColumns(db, catalogWithColumn)
		if err != nil {
			t.Fatalf("collectCatalogGatedMissingColumns: %v", err)
		}
		if len(missing) != 0 {
			t.Fatalf("missing = %v, want empty when the column exists", missing)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations unmet: %v", err)
		}
	})
}

// TestConnectionRevocationGateDormantThenEnforced covers the
// _System_OAuthConnectionRevocation table entry across both catalog regimes.
// While the catalog omits the table the gate must stay dormant (no probes, so
// a warm-upgrade DB without the table still starts); once the catalog declares
// it, a live schema missing the table must fail closed — the probes report
// every gated column missing, because a missing table surfaces as missing
// columns in INFORMATION_SCHEMA.
func TestConnectionRevocationGateDormantThenEnforced(t *testing.T) {
	const revocationTable = "_System_OAuthConnectionRevocation"
	gatedColumns := []string{"UserId", "ConnectedAppId", "RevokedAt"}
	columnQuery := regexp.QuoteMeta(catalogGateColumnQuery)

	t.Run("dormant while the catalog omits the table", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()

		// Catalog declares only the scope table; no probe may be issued for
		// the revocation table's columns.
		missing, err := collectCatalogGatedMissingColumns(db, catalogDefsDeclaring("Id", "IsActive"))
		if err != nil {
			t.Fatalf("collectCatalogGatedMissingColumns: %v", err)
		}
		for _, m := range missing {
			if strings.HasPrefix(m, revocationTable+".") {
				t.Fatalf("skew-window gate reported %s missing while the catalog omits the table", m)
			}
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("skew-window gate issued an unexpected schema probe: %v", err)
		}
	})

	t.Run("fails closed once the catalog declares the table", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()

		cols := make([]schema.ColumnDefinition, 0, len(gatedColumns))
		for _, c := range gatedColumns {
			cols = append(cols, schema.ColumnDefinition{Name: c})
		}
		catalog := []schema.TableDefinition{{TableName: revocationTable, Columns: cols}}

		want := make([]string, 0, len(gatedColumns))
		for _, c := range gatedColumns {
			mock.ExpectQuery(columnQuery).
				WithArgs(revocationTable, c).
				WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
			want = append(want, revocationTable+"."+c)
		}

		missing, err := collectCatalogGatedMissingColumns(db, catalog)
		if err != nil {
			t.Fatalf("collectCatalogGatedMissingColumns: %v", err)
		}
		got := map[string]bool{}
		for _, m := range missing {
			got[m] = true
		}
		for _, w := range want {
			if !got[w] {
				t.Fatalf("missing = %v, want it to include %s (absent table must fail closed)", missing, w)
			}
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations unmet: %v", err)
		}
	})
}

// TestCatalogGatedRequirementConsistentWithLiveCatalog binds the production
// wiring to the real embedded catalog WITHOUT hardcoding the skew-window state,
// so a future catalog bump needs no assertion rewrite. It reads whether the live
// catalog declares each gated column and asserts the gate behaves correctly for
// whichever regime the pinned catalog is in: enforced (fails closed on a missing
// live column) when declared, dormant (no probe) when not.
func TestCatalogGatedRequirementConsistentWithLiveCatalog(t *testing.T) {
	defs := GetSystemTableDefinitions()
	columnQuery := regexp.QuoteMeta(catalogGateColumnQuery)

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

	// Set up a "column absent" probe for exactly the gated columns the live
	// catalog declares; declared-but-absent must fail closed, and undeclared
	// columns must issue no probe (dormant). Track expected-missing to assert on.
	wantMissing := map[string]bool{}
	for _, entry := range catalogGatedManagedColumnRequirements {
		for _, columnName := range entry.columns {
			if !catalogDeclaresManagedColumn(defs, entry.tableName, columnName) {
				continue
			}
			mock.ExpectQuery(columnQuery).
				WithArgs(entry.tableName, columnName).
				WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
			wantMissing[entry.tableName+"."+columnName] = true
		}
	}

	missing, err := collectCatalogGatedMissingColumns(db, defs)
	if err != nil {
		t.Fatalf("collectCatalogGatedMissingColumns: %v", err)
	}
	gotMissing := map[string]bool{}
	for _, m := range missing {
		gotMissing[m] = true
	}
	for _, entry := range catalogGatedManagedColumnRequirements {
		for _, columnName := range entry.columns {
			key := entry.tableName + "." + columnName
			declared := catalogDeclaresManagedColumn(defs, entry.tableName, columnName)
			if declared && !gotMissing[key] {
				t.Fatalf("catalog declares %s but gate did not fail closed on a missing live column; missing=%v", key, missing)
			}
			if !declared && gotMissing[key] {
				t.Fatalf("catalog does not declare %s but gate reported it missing; missing=%v", key, missing)
			}
		}
	}
	if len(gotMissing) != len(wantMissing) {
		t.Fatalf("missing set = %v, want exactly the declared-but-absent gated columns %v", missing, wantMissing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}