package bootstrapimpl

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
)

// SessionControlProofLookupReadiness reports whether _System_Session can serve a
// single-row session lookup keyed by the session-control proof verifier.
//
// The browser-session migration replaces the lookup
//
//	Bearer -> JWT session id -> durable session -> compare proof hash
//
// with
//
//	proof cookie -> hash -> indexed durable session -> authoritative validation
//
// The second form has one answer only if the verifier is unique and indexed. This
// reports that from the live catalog rather than from the declaration, because what
// the database actually enforces is what an operator needs to know.
type SessionControlProofLookupReadiness struct {
	// Ready reports that a proof-keyed lookup would be unique, indexed, and
	// unambiguous. Nothing consults it yet.
	Ready bool

	// Reason is a non-secret, operator-actionable explanation whenever Ready is
	// false. It never contains a verifier value.
	Reason string

	// ColumnDeployed reports that the verifier column exists physically.
	ColumnDeployed bool

	// ColumnShapeCompatible reports that its physical type matches the declared
	// one. A narrower column would silently truncate the hex verifier and make
	// distinct proofs collide, so shape is part of readiness and is reported
	// separately from mere presence.
	ColumnShapeCompatible bool

	// UniqueIndexPresent reports an exact single-column unique index on the
	// verifier.
	UniqueIndexPresent bool

	// AmbiguousUsableSessions counts sessions that both share a verifier with
	// another session and could still be accepted by an authoritative validation
	// path. Any non-zero value blocks readiness even when the index exists.
	AmbiguousUsableSessions int

	// DuplicateVerifierRows counts every row sharing a non-NULL verifier with
	// another row, expired and revoked ones included.
	//
	// Those rows cannot authenticate, so they are not an ambiguity in the lookup
	// and do not block readiness. They are reported because the physical unique
	// index sees every row: a non-zero value predicts that installing the
	// constraint will fail until those rows are cleaned up, which is the one
	// question an operator needs answered before declaring the column unique.
	DuplicateVerifierRows int
}

// InspectSessionControlProofLookupReadiness evaluates whether a proof-keyed
// session lookup is safe to depend on. It only reads, so it is safe on a verify
// path.
//
// It reports counts and session-level facts only: no verifier value is returned or
// logged, because the verifier is the stored credential comparison target.
//
// Nothing consults the verdict yet. It exists so the readiness question has one
// owner before the cookie authenticator depends on the answer.
func InspectSessionControlProofLookupReadiness(ctx context.Context, rawDB *sql.DB) (SessionControlProofLookupReadiness, error) {
	var readiness SessionControlProofLookupReadiness
	if rawDB == nil {
		return readiness, fmt.Errorf("session-control proof lookup readiness: database is required")
	}

	declared, ok := declaredSessionControlProofColumn(GetSystemTableDefinitions())
	if !ok {
		return readiness, fmt.Errorf("session-control proof lookup readiness: catalog does not declare %s.%s",
			constants.TableSession, constants.FieldSysSession_ControlProofHash)
	}

	// Physical column state comes from the same loader reconciliation uses, so
	// readiness and the reconciler cannot hold different views of the column.
	existingCols, err := LoadPhysicalColumnShapes(rawDB)
	if err != nil {
		return readiness, fmt.Errorf("session-control proof lookup readiness: %w", err)
	}

	state := classifySessionControlProofColumn(existingCols, declared)
	readiness.ColumnDeployed = state != sessionControlProofColumnAbsent
	readiness.ColumnShapeCompatible = state == sessionControlProofColumnMatchesDeclaration

	// Readiness requires the declared contract exactly. It is a verdict, not a
	// repair path, so reporting ready for a column that violates any declared
	// property would let the browser session be enabled over a verifier whose
	// storage or comparison semantics are not the ones the design assumes.
	if state != sessionControlProofColumnMatchesDeclaration {
		if state == sessionControlProofColumnAbsent {
			readiness.Reason = fmt.Sprintf("%s.%s is not deployed yet",
				constants.TableSession, constants.FieldSysSession_ControlProofHash)
		} else {
			readiness.Reason = sessionControlProofColumnShapeMismatchReason(existingCols, declared)
		}
		return readiness, nil
	}

	// The shared index-shape owner decides the index question. Its inspection
	// excludes any index carrying a SUB_PART, which is what rejects a prefix
	// unique index: a prefix index enforces uniqueness on only the first
	// characters, so it would reject legitimately distinct random proofs and fail
	// session creation while still looking like a verifier index.
	if err := persistence.VerifyExactTableIndex(
		ctx, rawDB,
		constants.TableSession,
		sessionControlProofUniqueIndexName(),
		true,
		[]string{constants.FieldSysSession_ControlProofHash},
	); err != nil {
		readiness.Reason = err.Error()
	} else {
		readiness.UniqueIndexPresent = true
	}

	ambiguous, err := countAmbiguousUsableSessionControlProofs(ctx, rawDB)
	if err != nil {
		return readiness, err
	}
	readiness.AmbiguousUsableSessions = ambiguous

	duplicates, err := countDuplicateSessionControlProofRows(ctx, rawDB)
	if err != nil {
		return readiness, err
	}
	readiness.DuplicateVerifierRows = duplicates

	// Ambiguity is reported ahead of a missing index because it is the condition
	// an operator must resolve by hand: adding the constraint cannot fix it, and
	// the physical index would refuse to install while it holds.
	switch {
	case ambiguous > 0:
		readiness.Reason = fmt.Sprintf(
			"%d usable session(s) share a %s.%s value, so a proof-keyed lookup could select the wrong session; revoke the unintended session(s) before enabling it",
			ambiguous, constants.TableSession, constants.FieldSysSession_ControlProofHash)
	case !readiness.UniqueIndexPresent && duplicates > 0:
		readiness.Reason = fmt.Sprintf(
			"%d %s.%s row(s) share a verifier with another row, so the unique index cannot be installed until they are cleaned up; "+
				"they are expired or revoked and cannot authenticate, but the physical index sees every row",
			duplicates, constants.TableSession, constants.FieldSysSession_ControlProofHash)
	case readiness.UniqueIndexPresent:
		readiness.Ready = true
	}
	return readiness, nil
}

// countDuplicateSessionControlProofRows counts every row sharing a non-NULL
// verifier with another row, without regard to whether it could authenticate. This
// is what the physical unique index would see, so it predicts whether the
// constraint can be installed at all.
func countDuplicateSessionControlProofRows(ctx context.Context, rawDB *sql.DB) (int, error) {
	query := fmt.Sprintf(`
SELECT COALESCE(SUM(duplicate_rows), 0)
  FROM (
        SELECT COUNT(*) AS duplicate_rows
          FROM %s
         WHERE %s IS NOT NULL
         GROUP BY %s
        HAVING COUNT(*) > 1
       ) AS collisions`,
		quoteIdent(constants.TableSession),
		quoteIdent(constants.FieldSysSession_ControlProofHash),
		quoteIdent(constants.FieldSysSession_ControlProofHash),
	)
	var duplicates int
	if err := rawDB.QueryRowContext(ctx, query).Scan(&duplicates); err != nil {
		return 0, fmt.Errorf("counting rows sharing a %s.%s value: %w",
			constants.TableSession, constants.FieldSysSession_ControlProofHash, err)
	}
	return duplicates, nil
}

// sessionControlProofColumnState is the physical state of the verifier column, as
// four cases that must be handled differently rather than one error.
type sessionControlProofColumnState int

const (
	// sessionControlProofColumnAbsent means the column is not deployed. During the
	// staged window that is expected, and this reconciliation is what adds it; a
	// freshly added column holds no rows, so there is nothing to collide.
	sessionControlProofColumnAbsent sessionControlProofColumnState = iota

	// sessionControlProofColumnCannotHoldVerifier means the physical type cannot
	// store the declared value. It is reported separately from a mere shape
	// mismatch because it is the one violation no later repair undoes: a narrower
	// column has already truncated the hex verifier, so distinct proofs are
	// already stored as equal.
	sessionControlProofColumnCannotHoldVerifier

	// sessionControlProofColumnShapeMismatch means the type can hold the value but
	// some other declared property does not match. Both properties the catalog
	// declares are load-bearing: nullability, because a session with no browser
	// proof stores NULL, and the binary collation, because verifier comparison and
	// therefore uniqueness must be exact rather than case-folding.
	sessionControlProofColumnShapeMismatch

	// sessionControlProofColumnMatchesDeclaration means the column matches its
	// declaration completely.
	sessionControlProofColumnMatchesDeclaration
)

// declaredSessionControlProofColumn finds the verifier's declaration among the
// definitions being reconciled, so the expected shape has one source.
func declaredSessionControlProofColumn(tableDefs []schema.TableDefinition) (schema.ColumnDefinition, bool) {
	for _, def := range tableDefs {
		if !strings.EqualFold(def.TableName, constants.TableSession) {
			continue
		}
		for _, col := range def.Columns {
			if strings.EqualFold(col.Name, constants.FieldSysSession_ControlProofHash) {
				return col, true
			}
		}
	}
	return schema.ColumnDefinition{}, false
}

// classifySessionControlProofColumn compares the physical column against its
// declaration using the same comparisons reconciliation uses, so the two cannot
// disagree about whether the column is acceptable.
func classifySessionControlProofColumn(
	existingCols map[string]map[string]PhysicalColumnShape,
	declared schema.ColumnDefinition,
) sessionControlProofColumnState {
	existing, ok := existingCols[strings.ToLower(constants.TableSession)][strings.ToLower(declared.Name)]
	if !ok {
		return sessionControlProofColumnAbsent
	}
	if !ColumnTypesMatch(existing.ColumnType, DesiredColumnType(declared)) {
		return sessionControlProofColumnCannotHoldVerifier
	}
	if !ColumnShapeMatchesForTable(constants.TableSession, existing, declared) {
		return sessionControlProofColumnShapeMismatch
	}
	return sessionControlProofColumnMatchesDeclaration
}

// sessionControlProofColumnShapeMismatchReason names every declared property the
// physical column violates. None of these values is secret, and an operator cannot
// act without knowing which one is wrong.
func sessionControlProofColumnShapeMismatchReason(
	existingCols map[string]map[string]PhysicalColumnShape,
	declared schema.ColumnDefinition,
) string {
	existing := existingCols[strings.ToLower(constants.TableSession)][strings.ToLower(declared.Name)]
	desiredType := DesiredColumnType(declared)

	var violations []string
	if !ColumnTypesMatch(existing.ColumnType, desiredType) {
		violations = append(violations, fmt.Sprintf(
			"type is %s but %s is declared, and a column that cannot hold the full value truncates distinct proofs into collisions",
			existing.ColumnType, desiredType))
	}
	if existing.Nullable != declared.Nullable {
		violations = append(violations, fmt.Sprintf(
			"nullable is %t but %t is declared, and a session with no browser proof stores NULL",
			existing.Nullable, declared.Nullable))
	}
	if !collationShapeMatches(existing.CollationName, declared.CollationName) {
		violations = append(violations, fmt.Sprintf(
			"collation is %q but %q is declared, and verifier comparison must be exact rather than case-folding",
			existing.CollationName.String, declared.CollationName))
	}
	if !defaultShapeMatches(existing.DefaultVal, declared.Default, desiredType) {
		violations = append(violations, fmt.Sprintf(
			"default is %q but %q is declared", existing.DefaultVal.String, declared.Default))
	}
	if len(violations) == 0 {
		violations = append(violations, "physical shape does not match its declaration")
	}

	return fmt.Sprintf("%s.%s violates its declared verifier contract: %s",
		constants.TableSession, constants.FieldSysSession_ControlProofHash, strings.Join(violations, "; "))
}

// sessionControlProofUniqueIndexName derives the index name from the same
// generator the reconciler uses to create it, so the name is never spelled twice.
func sessionControlProofUniqueIndexName() string {
	return schema.GenerateUniqueIndexName(constants.TableSession, constants.FieldSysSession_ControlProofHash)
}

// usableSessionPredicate matches rows an authoritative validation path could still
// accept. A NULL IsRevoked counts as not revoked, matching the column's nullable
// declaration and the reads that compare it to zero.
func usableSessionPredicate(alias string) string {
	return fmt.Sprintf("COALESCE(%s%s, 0) = 0 AND %s%s > NOW()",
		alias, quoteIdent(constants.FieldSysSession_IsRevoked),
		alias, quoteIdent(constants.FieldSysSession_ExpiresAt))
}

func sessionControlProofAmbiguityQuery() string {
	return fmt.Sprintf(`
SELECT COALESCE(SUM(usable_rows), 0)
  FROM (
        SELECT COUNT(*) AS usable_rows
          FROM %s
         WHERE %s IS NOT NULL
           AND %s
         GROUP BY %s
        HAVING COUNT(*) > 1
       ) AS collisions`,
		quoteIdent(constants.TableSession),
		quoteIdent(constants.FieldSysSession_ControlProofHash),
		usableSessionPredicate(""),
		quoteIdent(constants.FieldSysSession_ControlProofHash),
	)
}

func countAmbiguousUsableSessionControlProofs(ctx context.Context, rawDB *sql.DB) (int, error) {
	var ambiguous int
	if err := rawDB.QueryRowContext(ctx, sessionControlProofAmbiguityQuery()).Scan(&ambiguous); err != nil {
		return 0, fmt.Errorf("counting usable sessions sharing a %s.%s value: %w",
			constants.TableSession, constants.FieldSysSession_ControlProofHash, err)
	}
	return ambiguous, nil
}