package persistence

import (
	"context"
	"crypto/sha256"
	"crypto/subtle"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

var ErrSessionAuthEpochConflict = errors.New("session auth epoch conflict")

const sessionTokenVerifierPrefix = "sha256:"

func sessionTokenVerifier(token string) string {
	return fmt.Sprintf("%s%x", sessionTokenVerifierPrefix, sha256.Sum256([]byte(token)))
}

func isSessionTokenVerifier(value string) bool {
	if len(value) != len(sessionTokenVerifierPrefix)+sha256.Size*2 ||
		!strings.HasPrefix(value, sessionTokenVerifierPrefix) {
		return false
	}
	decoded, err := hex.DecodeString(strings.TrimPrefix(value, sessionTokenVerifierPrefix))
	return err == nil && len(decoded) == sha256.Size
}

func resolveCurrentBearerToken(storedToken string, currentBearer sql.NullString) (string, error) {
	if currentBearer.Valid {
		expected := sessionTokenVerifier(currentBearer.String)
		if currentBearer.String == "" ||
			subtle.ConstantTimeCompare([]byte(storedToken), []byte(expected)) != 1 {
			return "", errors.New("current bearer token does not match its stored verifier")
		}
		return currentBearer.String, nil
	}
	if isSessionTokenVerifier(storedToken) {
		return "", errors.New("current bearer token is missing for a verifier-backed session")
	}
	return storedToken, nil
}

// VerifySessionBearerSchemaReadiness proves the physical column required by
// every SessionRepository read, insert, and renewal before a process begins
// serving. Browser session control has a separate optional readiness gate; the
// current bearer is part of the baseline repository shape in every deployment.
func VerifySessionBearerSchemaReadiness(ctx context.Context, db *sql.DB) error {
	if err := VerifyRequiredTableColumns(ctx, db, constants.TableSession, map[string]string{
		constants.FieldSysSession_CurrentBearerToken: "varbinary(3000)",
	}); err != nil {
		return fmt.Errorf("session bearer schema readiness: %w", err)
	}
	return nil
}

// SessionRepository handles database operations for user sessions
type SessionRepository struct {
	db                            *sql.DB
	connectedAppRuntimeVisibility connectedAppRuntimeVisibilityPolicy
	// independentIdentity is selected only by the independently serving
	// Runtime Identity composition. That process owns no L1 route surfaces, so
	// host-local product visibility cannot be part of its durable Connected App
	// check. AppInstallation/workload/capability authority is enforced
	// independently by Runtime security admission.
	independentIdentity bool
}

// NewSessionRepository creates a new SessionRepository
func NewSessionRepository(db *sql.DB) *SessionRepository {
	return &SessionRepository{db: db}
}

// NewIndependentIdentitySessionRepository builds the durable session owner for
// an independently serving Runtime Identity process. It still requires the
// exact Connected App row to exist and be active, but does not require an L1
// product route surface to be compiled into that L0 process. The ordinary host
// constructor remains fail-closed on source-backed apps until its local product
// visibility policy is installed.
func NewIndependentIdentitySessionRepository(db *sql.DB) *SessionRepository {
	return &SessionRepository{db: db, independentIdentity: true}
}

// SetConnectedAppRuntimeVisibilityPolicy gates declaration-owned Connected Apps
// through the serving process's local product OAuth capability catalog.
// Ownerless platform/admin Connected Apps keep the historical runtime path.
func (r *SessionRepository) SetConnectedAppRuntimeVisibilityPolicy(policy connectedAppRuntimeVisibilityPolicy) {
	if r == nil {
		return
	}
	r.connectedAppRuntimeVisibility = policy
}

// getExecutor returns the transactional executor if present in context, or the DB connection.
// This keeps session writes and reads on the caller's transaction when txCtx carries *sql.Tx.
func (r *SessionRepository) getExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// InsertSession creates a new session in the database
func (r *SessionRepository) InsertSession(ctx context.Context, session *models.SystemSession) error {
	authEpoch := session.AuthEpoch
	if authEpoch == 0 {
		authEpoch = 1
	}
	values := map[string]interface{}{
		constants.FieldID:                            session.ID,
		constants.FieldSysSession_UserID:             session.UserID,
		constants.FieldSysSession_Token:              sessionTokenVerifier(session.Token),
		constants.FieldSysSession_CurrentBearerToken: session.Token,
		constants.FieldSysSession_ExpiresAt:          session.ExpiresAt,
		constants.FieldSysSession_IPAddress:          session.IPAddress,
		constants.FieldSysSession_UserAgent:          session.UserAgent,
		constants.FieldSysSession_IsRevoked:          session.IsRevoked,
		constants.FieldSysSession_AuthEpoch:          authEpoch,
		constants.FieldSysSession_ControlProofHash:   session.ControlProofHash,
		constants.FieldSysSession_LastActivity:       session.LastActivity,
		constants.FieldSysSession_Scope:              session.Scope,
		constants.FieldSysSession_ConnectedAppID:     session.ConnectedAppID,
		constants.FieldSysSession_GrantType:          session.GrantType,
		constants.FieldCreatedDate:                   time.Now(),
		constants.FieldLastModifiedDate:              time.Now(),
	}

	qr := query.Insert(constants.TableSession, values).Build()

	_, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetSession retrieves a session by its ID (from JWT claim)
func (r *SessionRepository) GetSession(ctx context.Context, sessionID string) (*models.SystemSession, error) {
	qr := query.From(constants.TableSession).
		Select(sessionSelectColumns()).
		Where(constants.FieldID, sessionID).
		Limit(1).
		Build()

	session, err := scanSessionRow(r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...))
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return session, nil
}

// sessionSelectColumns is the one column list every session read projects, so a
// second read path cannot select a different shape than the scanner expects.
func sessionSelectColumns() []string {
	return []string{
		constants.FieldID, constants.FieldSysSession_UserID, constants.FieldSysSession_Token,
		constants.FieldSysSession_CurrentBearerToken,
		constants.FieldSysSession_ExpiresAt, constants.FieldSysSession_IPAddress, constants.FieldSysSession_UserAgent,
		constants.FieldSysSession_IsRevoked, constants.FieldSysSession_AuthEpoch,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldSysSession_LastActivity,
		constants.FieldSysSession_Scope, constants.FieldSysSession_ConnectedAppID,
		constants.FieldSysSession_GrantType,
		constants.FieldCreatedDate, constants.FieldLastModifiedDate,
	}
}

// sessionRowScanner is satisfied by both *sql.Row and *sql.Rows, so one scanner
// serves a single-row read and a multi-row ambiguity check.
type sessionRowScanner interface {
	Scan(dest ...any) error
}

// scanSessionRow materializes one durable session. It is the single scanner for
// the session row shape: every read path uses it, so none can disagree about
// bearer resolution, timestamp parsing, or which columns are nullable.
func scanSessionRow(row sessionRowScanner) (*models.SystemSession, error) {
	var s models.SystemSession
	var storedToken string
	var createdDateRaw, lastModifiedDateRaw, lastActivityRaw []byte
	var currentBearerRaw, controlProofHashRaw, scopeRaw, connectedAppIdRaw, grantTypeRaw sql.NullString

	if err := row.Scan(
		&s.ID,
		&s.UserID,
		&storedToken,
		&currentBearerRaw,
		&s.ExpiresAt,
		&s.IPAddress,
		&s.UserAgent,
		&s.IsRevoked,
		&s.AuthEpoch,
		&controlProofHashRaw,
		&lastActivityRaw,
		&scopeRaw,
		&connectedAppIdRaw,
		&grantTypeRaw,
		&createdDateRaw,
		&lastModifiedDateRaw,
	); err != nil {
		return nil, err
	}

	resolved, err := resolveCurrentBearerToken(storedToken, currentBearerRaw)
	if err != nil {
		return nil, fmt.Errorf("resolve bearer for session %s: %w", s.ID, err)
	}
	s.Token = resolved

	s.LastActivity = parseTime(lastActivityRaw)
	s.CreatedDate = parseTime(createdDateRaw)
	s.LastModifiedDate = parseTime(lastModifiedDateRaw)

	if controlProofHashRaw.Valid {
		controlProofHash := controlProofHashRaw.String
		s.ControlProofHash = &controlProofHash
	}
	if scopeRaw.Valid {
		scope := scopeRaw.String
		s.Scope = &scope
	}
	if connectedAppIdRaw.Valid {
		connectedAppID := connectedAppIdRaw.String
		s.ConnectedAppID = &connectedAppID
	}
	if grantTypeRaw.Valid {
		grantType := grantTypeRaw.String
		s.GrantType = &grantType
	}

	return &s, nil
}

// RevokeSession marks a session as revoked only while expectedToken is still
// the durable bearer. The compare predicate linearizes revocation with renewal:
// whichever write wins prevents a superseded token from mutating the session.
func (r *SessionRepository) RevokeSession(ctx context.Context, sessionID, expectedToken string) (bool, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NULL, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` IN (?, ?) AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysSession_Token,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(
		ctx, sqlStr, sessionID, sessionTokenVerifier(expectedToken), expectedToken,
	)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected == 1, nil
}

// RevokeSessionWithControlProof revokes an active browser session by its
// independently held proof verifier. It deliberately does not compare the
// bearer generation: a browser presenting a signed superseded bearer plus the
// matching proof must still be able to revoke the current durable session.
func (r *SessionRepository) RevokeSessionWithControlProof(ctx context.Context, sessionID, expectedControlProofHash string) (bool, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NULL, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, sessionID, expectedControlProofHash)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected == 1, nil
}

// AdvanceSessionAuthEpoch compare-and-swaps the durable delegation epoch for
// one active session. Security-authority changes that do not revoke the whole
// session use this owner so every previously issued delegation fails closed.
func (r *SessionRepository) AdvanceSessionAuthEpoch(ctx context.Context, sessionID string, expectedEpoch uint64) (uint64, error) {
	if expectedEpoch == 0 || strings.TrimSpace(sessionID) == "" {
		return 0, ErrSessionAuthEpochConflict
	}
	next := expectedEpoch + 1
	if next == 0 {
		return 0, ErrSessionAuthEpochConflict
	}
	statement := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_AuthEpoch,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysSession_AuthEpoch,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, statement, next, sessionID, expectedEpoch)
	if err != nil {
		return 0, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	if affected != 1 {
		return 0, ErrSessionAuthEpochConflict
	}
	return next, nil
}

// RevokeAllSessionsForUser revokes all active sessions for a given user
func (r *SessionRepository) RevokeAllSessionsForUser(ctx context.Context, userID string) error {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NULL, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldSysSession_UserID,
		constants.FieldSysSession_IsRevoked,
	)
	_, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, userID)
	return err
}

// RevokeAllSessionsByProfileID revokes every active session whose user is currently
// assigned to the given Profile. Used by PersistenceService.Update when admin renames
// or reassigns a Profile, to compress the staleness window for session-bound formula
// globals ($Profile.Name) per CLAUDE.md §"Salesforce Boundary / Formula context SSoT".
// Returns rows affected.
func (r *SessionRepository) RevokeAllSessionsByProfileID(ctx context.Context, profileID string) (int64, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` AS s INNER JOIN `%s` AS u ON s.`%s` = u.`%s` "+
			"SET s.`%s` = 1, s.`%s` = NULL, s.`%s` = CURRENT_TIMESTAMP "+
			"WHERE u.`%s` = ? AND s.`%s` = 0",
		constants.TableSession,
		constants.TableUser,
		constants.FieldSysSession_UserID,
		constants.FieldID,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldProfileID,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, profileID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// RevokeAllSessionsByUserRoleID revokes every active session whose user is currently
// assigned to the given UserRole. Same rationale as RevokeAllSessionsByProfileID.
// Returns rows affected.
func (r *SessionRepository) RevokeAllSessionsByUserRoleID(ctx context.Context, roleID string) (int64, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` AS s INNER JOIN `%s` AS u ON s.`%s` = u.`%s` "+
			"SET s.`%s` = 1, s.`%s` = NULL, s.`%s` = CURRENT_TIMESTAMP "+
			"WHERE u.`%s` = ? AND s.`%s` = 0",
		constants.TableSession,
		constants.TableUser,
		constants.FieldSysSession_UserID,
		constants.FieldID,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldUserRoleID,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, roleID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// RevokeAllSessionsForConnectedApp revokes all active sessions for a given Connected App.
// Returns the number of rows affected.
func (r *SessionRepository) RevokeAllSessionsForConnectedApp(ctx context.Context, appID string) (int64, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NULL, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldSysSession_ConnectedAppID,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, appID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// RevokeSessionsForUserAndConnectedApp eagerly retires bearer sessions issued
// to one user for one authorization client. The context executor preserves the
// surrounding disconnect transaction.
func (r *SessionRepository) RevokeSessionsForUserAndConnectedApp(ctx context.Context, userID, appID string) (int64, error) {
	if strings.TrimSpace(userID) == "" || strings.TrimSpace(appID) == "" {
		return 0, nil
	}
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NULL, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_ControlProofHash,
		constants.FieldLastModifiedDate,
		constants.FieldSysSession_UserID,
		constants.FieldSysSession_ConnectedAppID,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, userID, appID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// RenewSession extends one active, unexpired session and stores its replacement
// bearer token. The compare predicate prevents reviving expired/revoked rows or
// renewing a bearer that has already been superseded.
func (r *SessionRepository) RenewSession(ctx context.Context, sessionID, previousToken, token string, expiresAt, now time.Time) (bool, error) {
	updates := map[string]interface{}{
		constants.FieldSysSession_CurrentBearerToken: token,
		constants.FieldSysSession_Token:              sessionTokenVerifier(token),
		constants.FieldSysSession_ExpiresAt:          expiresAt,
		constants.FieldSysSession_LastActivity:       now,
		constants.FieldSysSession_LastModifiedDate:   now,
	}

	qr := query.Update(constants.TableSession).
		Set(updates).
		Where(constants.FieldID, sessionID).
		WhereIn(constants.FieldSysSession_Token, []interface{}{sessionTokenVerifier(previousToken), previousToken}).
		Where(constants.FieldSysSession_IsRevoked, false).
		WhereOp(constants.FieldSysSession_ExpiresAt, ">", now).
		Build()

	result, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected == 1, nil
}

// RenewSessionWithControlProof extends one active, unexpired browser session
// only when both the current durable bearer and the independent proof verifier
// still match. Browser logout clears the verifier in the same row, so either
// write ordering leaves a revoked session or prevents renewal from reviving it.
func (r *SessionRepository) RenewSessionWithControlProof(ctx context.Context, sessionID, previousToken, expectedControlProofHash, token string, expiresAt, now time.Time) (bool, error) {
	updates := map[string]interface{}{
		constants.FieldSysSession_CurrentBearerToken: token,
		constants.FieldSysSession_Token:              sessionTokenVerifier(token),
		constants.FieldSysSession_ExpiresAt:          expiresAt,
		constants.FieldSysSession_LastActivity:       now,
		constants.FieldSysSession_LastModifiedDate:   now,
	}

	qr := query.Update(constants.TableSession).
		Set(updates).
		Where(constants.FieldID, sessionID).
		WhereIn(constants.FieldSysSession_Token, []interface{}{sessionTokenVerifier(previousToken), previousToken}).
		Where(constants.FieldSysSession_ControlProofHash, expectedControlProofHash).
		Where(constants.FieldSysSession_IsRevoked, false).
		WhereOp(constants.FieldSysSession_ExpiresAt, ">", now).
		Build()

	result, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected == 1, nil
}

// UpdateLastActivity updates the last activity timestamp
func (r *SessionRepository) UpdateLastActivity(ctx context.Context, sessionID string) error {
	updates := map[string]interface{}{
		constants.FieldSysSession_LastActivity: time.Now(),
	}

	qr := query.Update(constants.TableSession).
		Set(updates).
		Where(constants.FieldID, sessionID).
		Build()

	_, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// CleanupExpiredSessions removes sessions that expired more than olderThan ago,
// plus all revoked sessions older than olderThan.
// Returns the number of deleted rows.
func (r *SessionRepository) CleanupExpiredSessions(ctx context.Context, olderThan time.Duration) (int64, error) {
	cutoff := time.Now().Add(-olderThan)
	sqlStr := fmt.Sprintf(
		"DELETE FROM `%s` WHERE (`%s` < ? OR `%s` = 1) AND `%s` < ?",
		constants.TableSession,
		constants.FieldSysSession_ExpiresAt,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_CreatedDate,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, cutoff, cutoff)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// IsConnectedAppActive checks whether a Connected App is still active by its ID.
func (r *SessionRepository) IsConnectedAppActive(ctx context.Context, appID string) (bool, error) {
	var isActive bool
	var sourcePackName sql.NullString
	var sourcePackBindingCount int64
	sqlStr := fmt.Sprintf(`
	SELECT ca.%s, COALESCE(owner.BindingCount, 0), owner.OwnerPack
	  FROM %s ca
	  LEFT JOIN (
		SELECT ConnectedAppId, COUNT(*) AS BindingCount, MAX(OwnerPack) AS OwnerPack
		  FROM (
			SELECT ConnectedAppId, InstalledPackName AS OwnerPack
			  FROM _System_ConnectedAppDeclaration
			 WHERE COALESCE(IsDeleted, 0) = 0
			UNION ALL
			SELECT ConnectedAppId, ScopeOwnerPack AS OwnerPack
			  FROM _System_OAuthDynamicClientRegistration
			 WHERE COALESCE(IsDeleted, 0) = 0 AND ScopeOwnerPack IS NOT NULL AND ScopeOwnerPack <> ''
		  ) bindings
		 GROUP BY ConnectedAppId
	) owner ON owner.ConnectedAppId = ca.%s
	 WHERE ca.%s = ?
	 LIMIT 1`,
		constants.ColSysConnectedApp_IsActive,
		constants.TableConnectedApp,
		constants.FieldID,
		constants.FieldID,
	)
	err := r.getExecutor(ctx).QueryRowContext(ctx, sqlStr, appID).Scan(&isActive, &sourcePackBindingCount, &sourcePackName)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil // App deleted = not active
		}
		return false, err
	}
	if !isActive {
		return false, nil
	}
	if sourcePackBindingCount > 1 {
		return false, nil
	}
	if sourcePackName.Valid && strings.TrimSpace(sourcePackName.String) != "" {
		if r != nil && r.independentIdentity {
			return true, nil
		}
		return r.connectedAppRuntimeVisible(strings.TrimSpace(sourcePackName.String), appID), nil
	}
	return true, nil
}

func (r *SessionRepository) connectedAppRuntimeVisible(sourcePackName, connectedAppID string) bool {
	if strings.TrimSpace(sourcePackName) == "" {
		return true
	}
	if r == nil || r.connectedAppRuntimeVisibility == nil {
		return false
	}
	return r.connectedAppRuntimeVisibility.AllowsConnectedApp(sourcePackName, connectedAppID)
}

// ErrAmbiguousSessionControlProof is returned when more than one durable session
// carries the same session-control proof verifier.
//
// It is deliberately an error rather than a choice. The browser-session lookup is
// keyed only by the proof, so picking one of two matching rows would decide which
// principal a request authenticates as. Until the verifier column carries its
// unique index this can only come from corrupt data, and after it exists it cannot
// happen at all — but the read must fail closed either way rather than depend on
// the constraint being present.
var ErrAmbiguousSessionControlProof = errors.New("more than one session carries the same control proof verifier")

// FindSessionByControlProofHash resolves the one durable session whose stored
// verifier equals controlProofHash.
//
// This is the read the browser session needs: the proof cookie hashes to a
// verifier, and the verifier selects the session directly, replacing the
// bearer -> JWT session id -> compare-proof path. It returns the row without
// judging it; expiry, revocation, auth epoch, and user state stay with the single
// authoritative validator, so this does not become a second place that decides
// whether a session is acceptable.
//
// It fails closed in both directions that could otherwise be papered over:
//
//   - a blank verifier is rejected without touching the database, so an empty
//     cookie value can never be matched against stored rows;
//   - two matching rows return ErrAmbiguousSessionControlProof. The query asks for
//     two rows precisely so ambiguity is observable — a LIMIT 1 read would silently
//     authenticate the caller as whichever row the database happened to return
//     first.
//
// No match returns (nil, nil), the same shape GetSession uses, so callers cannot
// confuse "no such session" with a failure.
//
// Nothing calls this yet.
func (r *SessionRepository) FindSessionByControlProofHash(ctx context.Context, controlProofHash string) (*models.SystemSession, error) {
	if strings.TrimSpace(controlProofHash) == "" {
		return nil, nil
	}

	statement, params := sessionControlProofLookupStatement(controlProofHash)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, statement, params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	if !rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, err
		}
		return nil, nil
	}

	session, err := scanSessionRow(rows)
	if err != nil {
		return nil, err
	}

	if rows.Next() {
		// Report nothing about either row: which sessions collide is an operator
		// question answered by the readiness check, not something a caller
		// presenting a proof should learn.
		return nil, ErrAmbiguousSessionControlProof
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return session, nil
}

// sessionControlProofLookupStatement builds the verifier-keyed read. It is a named
// function so the two-row request can be asserted directly: the limit is the
// mechanism that makes a second match observable, and a mock cannot reveal it.
func sessionControlProofLookupStatement(controlProofHash string) (string, []any) {
	qr := query.From(constants.TableSession).
		Select(sessionSelectColumns()).
		Where(constants.FieldSysSession_ControlProofHash, controlProofHash).
		Limit(2).
		Build()
	return qr.SQL, qr.Params
}