package credentialstore

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

	"github.com/google/uuid"

	"iop/packages/go/config"
)

var (
	// ErrRouteNotFound is returned when a requested route does not exist.
	ErrRouteNotFound = errors.New("credentialstore: route not found")

	// ErrIncompatibleProfile is returned when the protocol profile is incompatible with a slot's vendor and credential kind.
	ErrIncompatibleProfile = errors.New("credentialstore: profile incompatible with slot credential")

	// ErrCrossPrincipalSlot is returned when attempting to bind a route to a slot belonging to a different principal.
	ErrCrossPrincipalSlot = errors.New("credentialstore: slot belongs to a different principal")

	// ErrRouteAliasAlreadyExists is returned when a route alias is already in use for a principal.
	ErrRouteAliasAlreadyExists = errors.New("credentialstore: route alias already exists for principal")

	// ErrRouteRevoked is returned when attempting to modify a revoked route.
	ErrRouteRevoked = errors.New("credentialstore: revoked route cannot be modified")

	// ErrRouteNotActive is returned when an operation requires an active route.
	ErrRouteNotActive = errors.New("credentialstore: route is not active")
)

// CreateRouteInput holds parameters for binding a route to a credential slot.
type CreateRouteInput struct {
	PrincipalID      string
	SlotID           string
	Alias            string
	ProfileID        string
	UpstreamModel    string
	ResourceSelector string
}

// UpdateRouteInput replaces the mutable target fields of a route under a
// revision compare-and-swap. The route ID and principal ownership are stable.
type UpdateRouteInput struct {
	PrincipalID      string
	RouteID          string
	CurrentRevision  int64
	SlotID           string
	Alias            string
	ProfileID        string
	UpstreamModel    string
	ResourceSelector string
}

// RouteRecord represents a persisted route binding row.
type RouteRecord struct {
	ID               string
	PrincipalID      string
	SlotID           string
	Alias            string
	ProfileID        string
	UpstreamModel    string
	ResourceSelector string
	Status           string
	Revision         int64
	CreatedAt        time.Time
	UpdatedAt        time.Time
	RevokedAt        *time.Time
}

type profileAuthRule struct {
	header string
	scheme string
}

type credentialProfileRule map[string]profileAuthRule

// credentialProfileRules is deliberately closed. A credential slot can only
// bind to a catalog profile when its vendor, credential kind, profile ID, and
// profile authentication declaration all agree. New provider support must add
// an explicit entry here instead of inheriting a driver-level default.
var credentialProfileRules = map[string]credentialProfileRule{
	"openai/bearer": {"openai": {header: "Authorization", scheme: "Bearer"}},
	"gemini/bearer": {"gemini": {header: "Authorization", scheme: "Bearer"}},
	"vllm/bearer":   {"openai": {header: "Authorization", scheme: "Bearer"}},
	"sglang/bearer": {"openai": {header: "Authorization", scheme: "Bearer"}},
	"glm/bearer": {
		"glm":        {header: "Authorization", scheme: "Bearer"},
		"glm_coding": {header: "Authorization", scheme: "Bearer"},
	},
	"kimi/bearer":               {"kimi": {header: "Authorization", scheme: "Bearer"}},
	"grok/bearer":               {"grok": {header: "Authorization", scheme: "Bearer"}},
	"anthropic/api_key":         {"anthropic": {header: "x-api-key"}},
	"minimax/bearer":            {"minimax_chat": {header: "Authorization", scheme: "Bearer"}, "minimax_messages": {header: "Authorization", scheme: "Bearer"}},
	"mimo/bearer":               {"mimo_chat": {header: "Authorization", scheme: "Bearer"}},
	"mimo/api_key":              {"mimo_messages": {header: "api-key"}},
	"seulgi/bearer":             {"seulgi_chat": {header: "Authorization", scheme: "Bearer"}},
	"seulgi/api_key":            {"seulgi_messages": {header: "x-api-key"}},
	"seulgivibe_openai/bearer":  {"seulgi_chat": {header: "Authorization", scheme: "Bearer"}},
	"seulgivibe_claude/api_key": {"seulgi_messages": {header: "x-api-key"}},
}

// credentialProtocolProfiles is a process-owned catalog for credential
// routing. The exported config snapshot is intentionally mutable for legacy
// callers, so credential compatibility must not depend on it after startup.
var credentialProtocolProfiles = config.BuiltInProtocolProfileCatalog()

func compatibilityKey(vendor, credentialKind string) string {
	return normalizeCredentialPart(vendor) + "/" + normalizeCredentialPart(credentialKind)
}

func checkSlotProfileCompatibility(slot CredentialSlotRecord, profile config.ConcreteProtocolProfile) error {
	rule, ok := credentialProfileRules[compatibilityKey(slot.Vendor, slot.CredentialKind)]
	if !ok {
		return fmt.Errorf("%w: unknown vendor/credential kind %s/%s", ErrIncompatibleProfile, slot.Vendor, slot.CredentialKind)
	}
	auth, ok := rule[profile.ID]
	if !ok || !strings.EqualFold(strings.TrimSpace(profile.Auth.Header), auth.header) || !strings.EqualFold(strings.TrimSpace(profile.Auth.Scheme), auth.scheme) {
		return fmt.Errorf("%w: %s/%s cannot use profile %s", ErrIncompatibleProfile, slot.Vendor, slot.CredentialKind, profile.ID)
	}
	return nil
}

// CreateRoute creates a route binding for a principal and slot.
func (s *Store) CreateRoute(ctx context.Context, in CreateRouteInput) (*RouteRecord, error) {
	if strings.TrimSpace(in.PrincipalID) == "" {
		return nil, fmt.Errorf("credentialstore: CreateRoute: principal_id is required")
	}
	if strings.TrimSpace(in.SlotID) == "" {
		return nil, fmt.Errorf("credentialstore: CreateRoute: slot_id is required")
	}
	if strings.TrimSpace(in.UpstreamModel) == "" {
		return nil, fmt.Errorf("credentialstore: CreateRoute: upstream_model is required")
	}

	alias := normalizeAlias(in.Alias)
	resSelector := normalizeResourceSelector(in.ResourceSelector)
	upstreamModel := strings.TrimSpace(in.UpstreamModel)
	routeID := uuid.New().String()
	now := time.Now().UTC()

	var record RouteRecord
	err := s.RunInTransaction(ctx, func(ctx context.Context, tx *sql.Tx) error {
		slot, err := s.routeSlotTx(ctx, tx, in.PrincipalID, in.SlotID)
		if err != nil {
			return err
		}
		if slot.Status == StatusRevoked {
			return ErrSlotRevoked
		}
		if slot.Status == StatusDisabled {
			return ErrSlotNotActive
		}
		profile, err := resolveSlotProfile(slot, in.ProfileID)
		if err != nil {
			return err
		}
		if err := s.ensureRouteIDAvailableTx(ctx, tx, in.PrincipalID, routeID); err != nil {
			return err
		}
		if err := s.ensureRouteAliasAvailableTx(ctx, tx, in.PrincipalID, routeID, alias, ""); err != nil {
			return err
		}
		var aliasArg any
		if alias != "" {
			aliasArg = alias
		}
		if _, err := tx.ExecContext(ctx, s.bind(`
			INSERT INTO routes (id, principal_id, slot_id, alias, profile_id, upstream_model, resource_selector, status, revision, created_at, updated_at)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		`), routeID, in.PrincipalID, slot.ID, aliasArg, profile.ID, upstreamModel, resSelector, StatusActive, 0, now, now); err != nil {
			return fmt.Errorf("credentialstore: insert route: %w", err)
		}
		if slot.Status == StatusDraft {
			if err := s.activateDraftSlotTx(ctx, tx, slot); err != nil {
				return err
			}
		}
		if err := s.bumpProjectionGenerationTx(ctx, tx); err != nil {
			return err
		}
		record = RouteRecord{ID: routeID, PrincipalID: in.PrincipalID, SlotID: slot.ID, Alias: alias, ProfileID: profile.ID, UpstreamModel: upstreamModel, ResourceSelector: resSelector, Status: StatusActive, Revision: 0, CreatedAt: now, UpdatedAt: now}
		return nil
	})

	if err != nil {
		return nil, err
	}
	return &record, nil
}

func normalizeResourceSelector(value string) string {
	if normalized := strings.TrimSpace(value); normalized != "" {
		return normalized
	}
	return "default"
}

func resolveSlotProfile(slot CredentialSlotRecord, profileID string) (config.ConcreteProtocolProfile, error) {
	profile, err := config.ResolveProtocolProfile(profileID, slot.Vendor, credentialProtocolProfiles)
	if err != nil {
		return config.ConcreteProtocolProfile{}, fmt.Errorf("%w: %v", ErrIncompatibleProfile, err)
	}
	if err := checkSlotProfileCompatibility(slot, profile); err != nil {
		return config.ConcreteProtocolProfile{}, err
	}
	return profile, nil
}

func (s *Store) routeSlotTx(ctx context.Context, tx *sql.Tx, principalID, slotID string) (CredentialSlotRecord, error) {
	var slot CredentialSlotRecord
	if err := s.loadSlotTx(ctx, tx, principalID, slotID, &slot); err == nil {
		return slot, nil
	} else if !errors.Is(err, sql.ErrNoRows) {
		return CredentialSlotRecord{}, err
	}
	var actualPrincipalID string
	err := tx.QueryRowContext(ctx, s.bind(`SELECT principal_id FROM credential_slots WHERE id=?`), slotID).Scan(&actualPrincipalID)
	if err == nil && actualPrincipalID != principalID {
		return CredentialSlotRecord{}, ErrCrossPrincipalSlot
	}
	if err != nil && !errors.Is(err, sql.ErrNoRows) {
		return CredentialSlotRecord{}, err
	}
	return CredentialSlotRecord{}, ErrSlotNotFound
}

func (s *Store) ensureRouteAliasAvailableTx(ctx context.Context, tx *sql.Tx, principalID, routeID, alias, excludeRouteID string) error {
	if alias == "" {
		return nil
	}
	query := `SELECT COUNT(*) FROM routes WHERE principal_id=? AND (alias=? OR id=?)`
	args := []any{principalID, alias, alias}
	if excludeRouteID != "" {
		query += ` AND id<>?`
		args = append(args, excludeRouteID)
	}
	var count int
	if err := tx.QueryRowContext(ctx, s.bind(query), args...).Scan(&count); err != nil {
		return fmt.Errorf("credentialstore: check route alias: %w", err)
	}
	if count > 0 || alias == routeID {
		return ErrRouteAliasAlreadyExists
	}
	return nil
}

func (s *Store) ensureRouteIDAvailableTx(ctx context.Context, tx *sql.Tx, principalID, routeID string) error {
	var count int
	if err := tx.QueryRowContext(ctx, s.bind(`SELECT COUNT(*) FROM routes WHERE principal_id=? AND (id=? OR alias=?)`), principalID, routeID, routeID).Scan(&count); err != nil {
		return fmt.Errorf("credentialstore: check route id: %w", err)
	}
	if count > 0 {
		return ErrRouteAliasAlreadyExists
	}
	return nil
}

func (s *Store) activateDraftSlotTx(ctx context.Context, tx *sql.Tx, slot CredentialSlotRecord) error {
	if err := s.validateEnvelopeKey(ctx, slot.Envelope); err != nil {
		return err
	}
	res, err := tx.ExecContext(ctx, s.bind(`UPDATE credential_slots SET status=?, revision=revision+1, updated_at=? WHERE id=? AND revision=? AND status=?`), StatusActive, time.Now().UTC(), slot.ID, slot.Revision, StatusDraft)
	if err != nil {
		return err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if affected != 1 {
		return fmt.Errorf("%w: draft slot activation lost", ErrRevisionMismatch)
	}
	return nil
}

// GetRoute retrieves a route binding by principal ID and route ID.
func (s *Store) GetRoute(ctx context.Context, principalID, routeID string) (*RouteRecord, error) {
	var r RouteRecord
	var alias sql.NullString
	var createdAt, updatedAt string
	var revokedAt sql.NullString
	err := s.db.QueryRowContext(ctx, s.bind(`
		SELECT id, principal_id, slot_id, alias, profile_id, upstream_model, resource_selector, status, revision, created_at, updated_at, revoked_at
		FROM routes
		WHERE principal_id=? AND id=?
	`), principalID, routeID).Scan(
		&r.ID, &r.PrincipalID, &r.SlotID, &alias, &r.ProfileID, &r.UpstreamModel, &r.ResourceSelector, &r.Status, &r.Revision,
		&createdAt, &updatedAt, &revokedAt,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, ErrRouteNotFound
		}
		return nil, err
	}
	if alias.Valid {
		r.Alias = alias.String
	}
	r.CreatedAt, _ = parseTime(createdAt)
	r.UpdatedAt, _ = parseTime(updatedAt)
	if revokedAt.Valid {
		parsed, _ := parseTime(revokedAt.String)
		r.RevokedAt = &parsed
	}
	return &r, nil
}

// GetRouteByAlias retrieves a route binding by principal ID and alias.
func (s *Store) GetRouteByAlias(ctx context.Context, principalID, alias string) (*RouteRecord, error) {
	var r RouteRecord
	var aliasVal sql.NullString
	var createdAt, updatedAt string
	var revokedAt sql.NullString
	err := s.db.QueryRowContext(ctx, s.bind(`
		SELECT id, principal_id, slot_id, alias, profile_id, upstream_model, resource_selector, status, revision, created_at, updated_at, revoked_at
		FROM routes
		WHERE principal_id=? AND alias=?
	`), principalID, alias).Scan(
		&r.ID, &r.PrincipalID, &r.SlotID, &aliasVal, &r.ProfileID, &r.UpstreamModel, &r.ResourceSelector, &r.Status, &r.Revision,
		&createdAt, &updatedAt, &revokedAt,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, ErrRouteNotFound
		}
		return nil, err
	}
	if aliasVal.Valid {
		r.Alias = aliasVal.String
	}
	r.CreatedAt, _ = parseTime(createdAt)
	r.UpdatedAt, _ = parseTime(updatedAt)
	if revokedAt.Valid {
		parsed, _ := parseTime(revokedAt.String)
		r.RevokedAt = &parsed
	}
	return &r, nil
}

// ListRoutes returns all route bindings for a principal.
func (s *Store) ListRoutes(ctx context.Context, principalID string) ([]RouteRecord, error) {
	rows, err := s.db.QueryContext(ctx, s.bind(`
		SELECT id, principal_id, slot_id, alias, profile_id, upstream_model, resource_selector, status, revision, created_at, updated_at, revoked_at
		FROM routes
		WHERE principal_id=?
		ORDER BY created_at ASC
	`), principalID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []RouteRecord
	for rows.Next() {
		var r RouteRecord
		var alias sql.NullString
		var createdAt, updatedAt string
		var revokedAt sql.NullString
		if err := rows.Scan(
			&r.ID, &r.PrincipalID, &r.SlotID, &alias, &r.ProfileID, &r.UpstreamModel, &r.ResourceSelector, &r.Status, &r.Revision,
			&createdAt, &updatedAt, &revokedAt,
		); err != nil {
			return nil, err
		}
		if alias.Valid {
			r.Alias = alias.String
		}
		r.CreatedAt, _ = parseTime(createdAt)
		r.UpdatedAt, _ = parseTime(updatedAt)
		if revokedAt.Valid {
			parsed, _ := parseTime(revokedAt.String)
			r.RevokedAt = &parsed
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// ListRoutesBySlot returns all route bindings attached to a specific slot.
func (s *Store) ListRoutesBySlot(ctx context.Context, principalID, slotID string) ([]RouteRecord, error) {
	rows, err := s.db.QueryContext(ctx, s.bind(`
		SELECT id, principal_id, slot_id, alias, profile_id, upstream_model, resource_selector, status, revision, created_at, updated_at, revoked_at
		FROM routes
		WHERE principal_id=? AND slot_id=?
		ORDER BY created_at ASC
	`), principalID, slotID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []RouteRecord
	for rows.Next() {
		var r RouteRecord
		var alias sql.NullString
		var createdAt, updatedAt string
		var revokedAt sql.NullString
		if err := rows.Scan(
			&r.ID, &r.PrincipalID, &r.SlotID, &alias, &r.ProfileID, &r.UpstreamModel, &r.ResourceSelector, &r.Status, &r.Revision,
			&createdAt, &updatedAt, &revokedAt,
		); err != nil {
			return nil, err
		}
		if alias.Valid {
			r.Alias = alias.String
		}
		r.CreatedAt, _ = parseTime(createdAt)
		r.UpdatedAt, _ = parseTime(updatedAt)
		if revokedAt.Valid {
			parsed, _ := parseTime(revokedAt.String)
			r.RevokedAt = &parsed
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// UpdateRoute atomically replaces a route's slot/profile/model/selector/alias
// when the caller presents the current route revision.
func (s *Store) UpdateRoute(ctx context.Context, in UpdateRouteInput) (*RouteRecord, error) {
	if strings.TrimSpace(in.PrincipalID) == "" || strings.TrimSpace(in.RouteID) == "" || strings.TrimSpace(in.SlotID) == "" {
		return nil, fmt.Errorf("credentialstore: UpdateRoute: principal_id, route_id, and slot_id are required")
	}
	if strings.TrimSpace(in.UpstreamModel) == "" {
		return nil, fmt.Errorf("credentialstore: UpdateRoute: upstream_model is required")
	}
	alias := normalizeAlias(in.Alias)
	resSelector := normalizeResourceSelector(in.ResourceSelector)
	upstreamModel := strings.TrimSpace(in.UpstreamModel)

	var updated RouteRecord
	err := s.RunInTransaction(ctx, func(ctx context.Context, tx *sql.Tx) error {
		var route RouteRecord
		if err := s.loadRouteTx(ctx, tx, in.PrincipalID, in.RouteID, &route); err != nil {
			if errors.Is(err, sql.ErrNoRows) {
				return ErrRouteNotFound
			}
			return err
		}
		if route.Status == StatusRevoked {
			return ErrRouteRevoked
		}
		if route.Revision != in.CurrentRevision {
			return fmt.Errorf("%w: have %d want %d", ErrRevisionMismatch, route.Revision, in.CurrentRevision)
		}
		slot, err := s.routeSlotTx(ctx, tx, in.PrincipalID, in.SlotID)
		if err != nil {
			return err
		}
		if slot.Status != StatusActive {
			return ErrSlotNotActive
		}
		profile, err := resolveSlotProfile(slot, in.ProfileID)
		if err != nil {
			return err
		}
		if err := s.ensureRouteAliasAvailableTx(ctx, tx, in.PrincipalID, in.RouteID, alias, in.RouteID); err != nil {
			return err
		}
		var aliasArg any
		if alias != "" {
			aliasArg = alias
		}
		now := time.Now().UTC()
		res, err := tx.ExecContext(ctx, s.bind(`
			UPDATE routes
			SET slot_id=?, alias=?, profile_id=?, upstream_model=?, resource_selector=?, revision=revision+1, updated_at=?
			WHERE principal_id=? AND id=? AND revision=? AND status<>?
		`), slot.ID, aliasArg, profile.ID, upstreamModel, resSelector, now, in.PrincipalID, in.RouteID, in.CurrentRevision, StatusRevoked)
		if err != nil {
			return err
		}
		affected, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if affected != 1 {
			return fmt.Errorf("%w: failed to update route", ErrRevisionMismatch)
		}
		if err := s.loadRouteTx(ctx, tx, in.PrincipalID, in.RouteID, &updated); err != nil {
			return err
		}
		return s.bumpProjectionGenerationTx(ctx, tx)
	})
	if err != nil {
		return nil, err
	}
	return &updated, nil
}

func (s *Store) slotHasCompatibleBindingTx(ctx context.Context, tx *sql.Tx, slot CredentialSlotRecord) (bool, error) {
	rows, err := tx.QueryContext(ctx, s.bind(`SELECT profile_id FROM routes WHERE principal_id=? AND slot_id=? AND status<>?`), slot.PrincipalID, slot.ID, StatusRevoked)
	if err != nil {
		return false, err
	}
	defer rows.Close()
	for rows.Next() {
		var profileID string
		if err := rows.Scan(&profileID); err != nil {
			return false, err
		}
		if _, err := resolveSlotProfile(slot, profileID); err == nil {
			return true, nil
		}
	}
	return false, rows.Err()
}

// DisableRoute disables an active route binding using CAS on revision.
func (s *Store) DisableRoute(ctx context.Context, principalID, routeID string, currentRevision int64) (*RouteRecord, error) {
	return s.casRouteStatus(ctx, principalID, routeID, currentRevision, []string{StatusActive}, StatusDisabled)
}

// EnableRoute enables a disabled route binding using CAS on revision.
func (s *Store) EnableRoute(ctx context.Context, principalID, routeID string, currentRevision int64) (*RouteRecord, error) {
	return s.enableRoute(ctx, principalID, routeID, currentRevision)
}

func (s *Store) enableRoute(ctx context.Context, principalID, routeID string, currentRevision int64) (*RouteRecord, error) {
	var updated RouteRecord
	err := s.RunInTransaction(ctx, func(ctx context.Context, tx *sql.Tx) error {
		var route RouteRecord
		if err := s.loadRouteTx(ctx, tx, principalID, routeID, &route); err != nil {
			if errors.Is(err, sql.ErrNoRows) {
				return ErrRouteNotFound
			}
			return err
		}
		if route.Revision != currentRevision {
			return fmt.Errorf("%w: have %d want %d", ErrRevisionMismatch, route.Revision, currentRevision)
		}
		if route.Status == StatusRevoked {
			return ErrRouteRevoked
		}
		if route.Status != StatusDisabled {
			return ErrRouteNotActive
		}
		slot, err := s.routeSlotTx(ctx, tx, principalID, route.SlotID)
		if err != nil {
			return err
		}
		if slot.Status != StatusActive {
			return ErrSlotNotActive
		}
		if _, err := resolveSlotProfile(slot, route.ProfileID); err != nil {
			return err
		}
		now := time.Now().UTC()
		res, err := tx.ExecContext(ctx, s.bind(`UPDATE routes SET status=?, revision=revision+1, updated_at=?, revoked_at=NULL WHERE principal_id=? AND id=? AND revision=? AND status=?`), StatusActive, now, principalID, routeID, currentRevision, StatusDisabled)
		if err != nil {
			return err
		}
		affected, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if affected != 1 {
			return fmt.Errorf("%w: failed to enable route", ErrRevisionMismatch)
		}
		if err := s.loadRouteTx(ctx, tx, principalID, routeID, &updated); err != nil {
			return err
		}
		return s.bumpProjectionGenerationTx(ctx, tx)
	})
	if err != nil {
		return nil, err
	}
	return &updated, nil
}

// RevokeRoute revokes a route binding permanently using CAS on revision.
func (s *Store) RevokeRoute(ctx context.Context, principalID, routeID string, currentRevision int64) (*RouteRecord, error) {
	return s.casRouteStatus(ctx, principalID, routeID, currentRevision, []string{StatusActive, StatusDisabled}, StatusRevoked)
}

func (s *Store) casRouteStatus(ctx context.Context, principalID, routeID string, currentRevision int64, fromStatuses []string, toStatus string) (*RouteRecord, error) {
	var updated RouteRecord
	err := s.RunInTransaction(ctx, func(ctx context.Context, tx *sql.Tx) error {
		now := time.Now().UTC()
		placeholders := strings.TrimRight(strings.Repeat("?,", len(fromStatuses)), ",")
		query := `UPDATE routes SET status=?, revision=revision+1, updated_at=?, revoked_at=?
			WHERE principal_id=? AND id=? AND revision=? AND status IN (` + placeholders + `)`
		var revokedArg interface{}
		if toStatus == StatusRevoked {
			revokedArg = now
		}
		args := []any{toStatus, now, revokedArg, principalID, routeID, currentRevision}
		for _, st := range fromStatuses {
			args = append(args, st)
		}
		res, err := tx.ExecContext(ctx, s.bind(query), args...)
		if err != nil {
			return err
		}
		affected, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if affected != 1 {
			return s.classifyRouteTransitionMiss(ctx, tx, principalID, routeID, currentRevision, fromStatuses)
		}
		if err := s.loadRouteTx(ctx, tx, principalID, routeID, &updated); err != nil {
			return err
		}
		return s.bumpProjectionGenerationTx(ctx, tx)
	})
	if err != nil {
		return nil, err
	}
	return &updated, nil
}

func (s *Store) classifyRouteTransitionMiss(ctx context.Context, tx *sql.Tx, principalID, routeID string, currentRevision int64, fromStatuses []string) error {
	var status string
	var revision int64
	err := tx.QueryRowContext(ctx, s.bind(`
		SELECT status, revision FROM routes WHERE principal_id=? AND id=?
	`), principalID, routeID).Scan(&status, &revision)
	if err == sql.ErrNoRows {
		return ErrRouteNotFound
	}
	if err != nil {
		return err
	}
	if revision != currentRevision {
		return fmt.Errorf("%w: have %d want %d", ErrRevisionMismatch, revision, currentRevision)
	}
	if status == StatusRevoked {
		return ErrRouteRevoked
	}
	return fmt.Errorf("%w: have %s want one of %s", ErrRouteNotActive, status, strings.Join(fromStatuses, ","))
}

func (s *Store) loadRouteTx(ctx context.Context, tx *sql.Tx, principalID, routeID string, target *RouteRecord) error {
	var alias sql.NullString
	var createdAt, updatedAt string
	var revokedAt sql.NullString
	err := tx.QueryRowContext(ctx, s.bind(`
		SELECT id, principal_id, slot_id, alias, profile_id, upstream_model, resource_selector, status, revision, created_at, updated_at, revoked_at
		FROM routes
		WHERE principal_id=? AND id=?
	`), principalID, routeID).Scan(
		&target.ID, &target.PrincipalID, &target.SlotID, &alias, &target.ProfileID, &target.UpstreamModel, &target.ResourceSelector, &target.Status, &target.Revision,
		&createdAt, &updatedAt, &revokedAt,
	)
	if err != nil {
		return err
	}
	if alias.Valid {
		target.Alias = alias.String
	}
	target.CreatedAt, _ = parseTime(createdAt)
	target.UpdatedAt, _ = parseTime(updatedAt)
	if revokedAt.Valid {
		parsed, _ := parseTime(revokedAt.String)
		target.RevokedAt = &parsed
	}
	return nil
}