package packs

import (
	"context"
	"crypto/rand"
	"database/sql"
	_ "embed"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"os"
	"sort"
	"strings"

	featureauth "github.com/nexuscrm/auth"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/oauthroutes"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	schemacatalog "github.com/pingcap-inc/nexuscrm-contract/pkg/schemacatalog"
)

//go:embed profiles.json
var profilesJSON []byte

//go:embed bootstrap_identity.json
var bootstrapIdentityJSON []byte

// ---- Types ----

type bootstrapIdentityData struct {
	Users         []systemUserJSON        `json:"users"`
	ConnectedApps []bootstrapConnectedApp `json:"connected_apps"`
}

type bootstrapOAuthScopesData struct {
	OAuthCustomScopes []bootstrapOAuthCustomScope `json:"oauth_custom_scopes"`
}

type bootstrapOAuthCustomScope struct {
	ID                 string   `json:"id"`
	Name               string   `json:"name"`
	Description        string   `json:"description,omitempty"`
	CustomPermissionID string   `json:"custom_permission_id,omitempty"`
	AllowedClientTypes []string `json:"allowed_client_types"`
	AllowedMethods     []string `json:"allowed_methods"`
	RoutePatterns      []string `json:"route_patterns"`
	// RouteFamilyPrefix is pack-declared metadata: the app-blind route-family
	// prefix the route_patterns belong to. It is consumed only to validate the
	// patterns (see normalizeOAuthCustomScopeSeed) and is NOT persisted — it is
	// cleared after validation so it never enters the stored projection.
	RouteFamilyPrefix string   `json:"route_family_prefix,omitempty"`
	ResourceLinks     []string `json:"resource_links"`
	OwnerPack         string   `json:"ownerPack,omitempty"`
	Active            bool     `json:"active"`
	// AllowDynamicPublicClients is the scope owner's opt-in that lets RFC 7591
	// dynamically-registered public OAuth clients be issued this scope. It pairs
	// with the _System_OAuthCustomScope.AllowDynamicPublicClients column added in
	// NexusCRM#3986. Absent in the pack payload => false (backward compatible with
	// every existing scope seed).
	AllowDynamicPublicClients bool `json:"allow_dynamic_public_clients"`
}

type systemUserJSON struct {
	ID                   string  `json:"id,omitempty"`
	FirstName            string  `json:"first_name"`
	LastName             string  `json:"last_name"`
	Email                string  `json:"email"`
	Username             string  `json:"username,omitempty"`
	Password             string  `json:"password"`
	ProfileID            string  `json:"profile_id"`
	RoleID               *string `json:"role_id,omitempty"`
	ResolveAdminPassword bool    `json:"resolve_admin_password,omitempty"`
}

type bootstrapConnectedApp struct {
	ClientID           string `json:"client_id"`
	Name               string `json:"name"`
	KeyID              string `json:"key_id"`
	Secret             string `json:"secret"`
	Scopes             string `json:"scopes"`
	SessionIssuerClass string `json:"session_issuer_class,omitempty"`
}

type PlatformBootstrapConnectedApp struct {
	ClientID           string
	Name               string
	ConsumerKey        string
	Scopes             string
	SessionIssuerClass string
}

// PlatformBootstrapConnectedApps returns the connected apps owned by the
// runtime-required platform-policy pack. Clean-L0 verification uses this as the
// allowlist for _System_ConnectedApp without naming dev-sample fixtures.
func PlatformBootstrapConnectedApps() ([]PlatformBootstrapConnectedApp, error) {
	var data bootstrapIdentityData
	if err := json.Unmarshal(bootstrapIdentityJSON, &data); err != nil {
		return nil, apperrors.NewInternalError("failed to parse bootstrap_identity.json", err)
	}
	out := make([]PlatformBootstrapConnectedApp, 0, len(data.ConnectedApps))
	for _, app := range data.ConnectedApps {
		out = append(out, PlatformBootstrapConnectedApp{
			ClientID:           app.ClientID,
			Name:               app.Name,
			ConsumerKey:        app.KeyID,
			Scopes:             strings.Join(strings.Fields(app.Scopes), " "),
			SessionIssuerClass: strings.TrimSpace(app.SessionIssuerClass),
		})
	}
	return out, nil
}

// ---- Pack ----

// PlatformPolicyPack manages platform-owned security/identity metadata:
// profiles, bootstrap identity (admin user + required connected app),
// permissions, system permissions, and folder organization shares.
type PlatformPolicyPack struct{}

func (p *PlatformPolicyPack) Name() string       { return "platform-policy" }
func (p *PlatformPolicyPack) Category() Category { return PlatformPolicy }

func (p *PlatformPolicyPack) Apply(ctx context.Context, deps ApplyDeps) error {
	log.Println("📦 Applying platform-policy pack...")

	// Profiles (own the data + logic)
	if err := applySystemProfiles(ctx, deps); err != nil {
		return err
	}

	// Bootstrap identity: admin user + required connected app (own the data + logic)
	if err := provisionBootstrapIdentity(ctx, deps); err != nil {
		return err
	}

	// Platform permission convergence for runtime-required metadata. App packs
	// rerun convergence after their app-owned objects and permissions land.
	if err := InitializePermissions(deps.Permissions, deps.Metadata); err != nil {
		return fmt.Errorf("initialize permissions: %w", err)
	}
	if err := SeedSystemPermissions(ctx, deps.DB); err != nil {
		return fmt.Errorf("seed system permissions: %w", err)
	}
	// NOTE: ensureFolderOrganizationShares moved to PlatformConfig pack
	// (must run AFTER applyFolders creates the default public folders)

	log.Println("✅ platform-policy pack applied")
	return nil
}

func (p *PlatformPolicyPack) Verify(ctx context.Context, deps VerifyDeps) error {
	pack, err := schemacatalog.StandardPermissions()
	if err != nil {
		return err
	}
	if err := verifySystemPermissionRows(ctx, deps.DB, pack, p.Name()); err != nil {
		return err
	}
	apps, err := PlatformBootstrapConnectedApps()
	if err != nil {
		return err
	}
	if err := verifyPlatformBootstrapConnectedApps(ctx, deps.DB, apps); err != nil {
		return err
	}
	return nil
}

func (p *PlatformPolicyPack) DeclaredComponents() ([]DeclaredComponent, error) {
	permissions, err := schemacatalog.StandardPermissions()
	if err != nil {
		return nil, err
	}
	components := make([]DeclaredComponent, 0, len(permissions))
	for _, permission := range permissions {
		components = append(components, DeclaredComponent{
			PackName:      p.Name(),
			Kind:          ComponentKindPermission,
			BusinessKey:   permission.APIName,
			Required:      true,
			Manageability: ManageabilityLocked,
		})
	}

	apps, err := PlatformBootstrapConnectedApps()
	if err != nil {
		return nil, err
	}
	for _, app := range apps {
		components = append(components, DeclaredComponent{
			PackName:      p.Name(),
			Kind:          ComponentKindConnectedApp,
			BusinessKey:   JoinComponentBusinessKey(app.Name, app.ClientID),
			Required:      true,
			Manageability: ManageabilityLocked,
		})
	}
	return components, nil
}

// ---- Implementation ----

func applySystemProfiles(ctx context.Context, deps ApplyDeps) error {
	log.Println("🔧 Applying system profiles...")

	var profiles []models.Profile
	if err := json.Unmarshal(profilesJSON, &profiles); err != nil {
		return apperrors.NewInternalError("failed to parse profiles.json", err)
	}
	for i := range profiles {
		profiles[i].IsSystem = true
	}
	if err := deps.System.BatchUpsertProfiles(ctx, profiles); err != nil {
		return apperrors.NewInternalError("failed to batch upsert profiles", err)
	}
	log.Printf("   ✅ Ensure %d system profiles (batch)", len(profiles))

	// Ensure PermissionSets for each profile
	if deps.Permissions != nil {
		for _, p := range profiles {
			if _, err := deps.Permissions.EnsureProfilePermissionSet(ctx, p.ID, p.Name); err != nil {
				log.Printf("   ⚠️  Failed to ensure PermissionSet for profile %s: %v", p.ID, err)
			}
		}
		log.Printf("   ✅ Ensured PermissionSets for %d profiles", len(profiles))
	}
	return nil
}

func provisionBootstrapIdentity(ctx context.Context, deps ApplyDeps) error {
	log.Println("🔧 Provisioning bootstrap identity...")

	var data bootstrapIdentityData
	if err := json.Unmarshal(bootstrapIdentityJSON, &data); err != nil {
		return apperrors.NewInternalError("failed to parse bootstrap_identity.json", err)
	}

	// Users
	type explicitPwd struct {
		email    string
		password string
	}
	var explicitPasswords []explicitPwd

	users := make([]models.User, len(data.Users))
	for i, u := range data.Users {
		id := u.ID
		if id == "" {
			id = utils.GenerateID()
		}
		username := u.Email
		if u.Username != "" {
			username = u.Username
		}
		password := u.Password
		if u.ResolveAdminPassword {
			pwd, explicit, err := resolveAdminPassword(u.Password, os.Getenv("NEXUS_ADMIN_PASSWORD"), os.Getenv("NEXUS_ENV"))
			if err != nil {
				return apperrors.NewInternalError("admin password resolution failed", err)
			}
			password = pwd
			if explicit {
				explicitPasswords = append(explicitPasswords, explicitPwd{email: u.Email, password: pwd})
			}
		}
		users[i] = models.User{
			ID: id, Username: username, Email: u.Email, Password: password,
			ProfileID: u.ProfileID, UserRoleID: u.RoleID,
			FirstName: u.FirstName, LastName: u.LastName, IsActive: true,
		}
	}
	if err := deps.System.BatchUpsertUsers(ctx, users); err != nil {
		return apperrors.NewInternalError("failed to batch upsert bootstrap users", err)
	}
	log.Printf("   ✅ Ensure %d bootstrap users (batch)", len(users))

	// Force-update admin password when explicitly configured via NEXUS_ADMIN_PASSWORD.
	// BatchUpsertUsers preserves existing bcrypt hashes (correct for restarts),
	// Explicit configuration must override any previously seeded bootstrap password.
	for _, ep := range explicitPasswords {
		if err := deps.System.ForceSetUserPassword(ctx, ep.email, ep.password); err != nil {
			return fmt.Errorf("force-update admin password: %w", err)
		}
	}

	// Required connected apps
	for _, app := range data.ConnectedApps {
		if err := deps.System.UpsertConnectedApp(ctx, app.ClientID, app.Name, app.KeyID, app.Secret, app.SessionIssuerClass); err != nil {
			return fmt.Errorf("ensure bootstrap connected app %s: %w", app.ClientID, err)
		}
		if err := convergePlatformBootstrapConnectedAppScopes(ctx, deps, app); err != nil {
			return fmt.Errorf("ensure bootstrap connected app %s scopes: %w", app.ClientID, err)
		}
		log.Printf("   ✅ Ensured connected app: %s", app.ClientID)
	}
	return nil
}

func convergePlatformBootstrapConnectedAppScopes(ctx context.Context, deps ApplyDeps, app bootstrapConnectedApp) error {
	if deps.DB == nil {
		return errors.New("database is required")
	}
	scopes := strings.Join(strings.Fields(app.Scopes), " ")
	if scopes == "" {
		return errors.New("non-empty scopes are required")
	}

	tx, err := deps.DB.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin connected app scope convergence: %w", err)
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
	}()

	var stored string
	err = tx.QueryRowContext(ctx, fmt.Sprintf(
		"SELECT COALESCE(`%s`, '') FROM `%s` WHERE `%s` = ? AND `%s` = ? LIMIT 1 FOR UPDATE",
		constants.FieldSysConnectedApp_Scopes,
		constants.TableConnectedApp,
		constants.FieldSysConnectedApp_ID,
		constants.FieldSysConnectedApp_ConsumerKey,
	), app.ClientID, app.KeyID).Scan(&stored)
	if err != nil {
		return err
	}
	if strings.Join(strings.Fields(stored), " ") == scopes {
		if err := tx.Commit(); err != nil {
			return fmt.Errorf("commit unchanged connected app scopes: %w", err)
		}
		committed = true
		return nil
	}

	_, err = tx.ExecContext(ctx, fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = ?",
		constants.TableConnectedApp,
		constants.FieldSysConnectedApp_Scopes,
		constants.FieldLastModifiedDate,
		constants.FieldSysConnectedApp_ID,
		constants.FieldSysConnectedApp_ConsumerKey,
	), scopes, app.ClientID, app.KeyID)
	if err != nil {
		return err
	}
	err = tx.QueryRowContext(ctx, fmt.Sprintf(
		"SELECT COALESCE(`%s`, '') FROM `%s` WHERE `%s` = ? AND `%s` = ? LIMIT 1",
		constants.FieldSysConnectedApp_Scopes,
		constants.TableConnectedApp,
		constants.FieldSysConnectedApp_ID,
		constants.FieldSysConnectedApp_ConsumerKey,
	), app.ClientID, app.KeyID).Scan(&stored)
	if err != nil {
		return err
	}
	if strings.Join(strings.Fields(stored), " ") != scopes {
		return fmt.Errorf("connected app scopes = %q, want %q", strings.Join(strings.Fields(stored), " "), scopes)
	}
	if err := revokeConnectedAppSessions(ctx, tx, app.ClientID); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit connected app scope convergence: %w", err)
	}
	committed = true
	if deps.ConnectedAppHook != nil {
		deps.ConnectedAppHook.OnConnectedAppChanged(app.ClientID)
	}
	return nil
}

func verifyPlatformBootstrapConnectedApps(ctx context.Context, db *sql.DB, expected []PlatformBootstrapConnectedApp) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}
	var problems []string
	for _, app := range expected {
		consumerKey := strings.TrimSpace(app.ConsumerKey)
		if consumerKey == "" {
			problems = append(problems, fmt.Sprintf("platform bootstrap connected app %q has empty consumer key", app.ClientID))
			continue
		}
		var id, name, scopes, sessionIssuerClass string
		var isActive bool
		err := db.QueryRowContext(ctx, fmt.Sprintf(
			"SELECT `%s`, `%s`, `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, '') "+
				"FROM `%s` "+
				"WHERE `%s` = ? "+
				"LIMIT 1",
			constants.FieldSysConnectedApp_ID,
			constants.FieldSysConnectedApp_Name,
			constants.FieldSysConnectedApp_IsActive,
			constants.FieldSysConnectedApp_Scopes,
			constants.FieldSysConnectedApp_SessionIssuerClass,
			constants.TableConnectedApp,
			constants.FieldSysConnectedApp_ConsumerKey,
		), consumerKey).Scan(&id, &name, &isActive, &scopes, &sessionIssuerClass)
		if err == sql.ErrNoRows {
			problems = append(problems, fmt.Sprintf("consumer key %q is missing", consumerKey))
			continue
		}
		if err != nil {
			return fmt.Errorf("query platform bootstrap connected app %q: %w", consumerKey, err)
		}
		if strings.TrimSpace(id) != strings.TrimSpace(app.ClientID) {
			problems = append(problems, fmt.Sprintf("consumer key %q id = %q, want %q", consumerKey, strings.TrimSpace(id), strings.TrimSpace(app.ClientID)))
		}
		if strings.TrimSpace(name) != strings.TrimSpace(app.Name) {
			problems = append(problems, fmt.Sprintf("consumer key %q name = %q, want %q", consumerKey, strings.TrimSpace(name), strings.TrimSpace(app.Name)))
		}
		if !isActive {
			problems = append(problems, fmt.Sprintf("consumer key %q is inactive", consumerKey))
		}
		if strings.Join(strings.Fields(scopes), " ") != strings.Join(strings.Fields(app.Scopes), " ") {
			problems = append(problems, fmt.Sprintf("consumer key %q scopes = %q, want %q", consumerKey, strings.Join(strings.Fields(scopes), " "), strings.Join(strings.Fields(app.Scopes), " ")))
		}
		if strings.TrimSpace(app.SessionIssuerClass) != "" && strings.TrimSpace(sessionIssuerClass) != strings.TrimSpace(app.SessionIssuerClass) {
			problems = append(problems, fmt.Sprintf("consumer key %q SessionIssuerClass = %q, want %q", consumerKey, strings.TrimSpace(sessionIssuerClass), strings.TrimSpace(app.SessionIssuerClass)))
		}
	}
	if len(problems) == 0 {
		return nil
	}
	sort.Strings(problems)
	return fmt.Errorf("platform-policy bootstrap connected apps are not converged: %s", strings.Join(problems, "; "))
}

func parseOAuthCustomScopeSeeds(payload []byte, source string) ([]bootstrapOAuthCustomScope, error) {
	var data bootstrapOAuthScopesData
	if err := json.Unmarshal(payload, &data); err != nil {
		return nil, apperrors.NewInternalError(fmt.Sprintf("failed to parse %s", source), err)
	}
	seeds := make([]bootstrapOAuthCustomScope, 0, len(data.OAuthCustomScopes))
	seen := map[string]struct{}{}
	for i, seed := range data.OAuthCustomScopes {
		normalized, err := normalizeOAuthCustomScopeSeed(seed, false)
		if err != nil {
			return nil, fmt.Errorf("%s oauth_custom_scopes[%d]: %w", source, i, err)
		}
		key := normalized.Name
		if _, ok := seen[key]; ok {
			return nil, fmt.Errorf("%s oauth_custom_scopes[%d]: duplicate scope %q", source, i, normalized.Name)
		}
		seen[key] = struct{}{}
		seeds = append(seeds, normalized)
	}
	return seeds, nil
}

// legacyOAuthRouteFamilyPrefix is the historical Salesforce data-API route
// family prefix. It is used ONLY to reconstruct installed-row payloads from
// revisions authored before route_family_prefix became pack metadata; fresh
// authoring must declare the prefix and never uses this default.
const legacyOAuthRouteFamilyPrefix = "/services/data/"

func normalizeOAuthCustomScopeSeed(seed bootstrapOAuthCustomScope, allowLegacyMissingRouteFamilyPrefix bool) (bootstrapOAuthCustomScope, error) {
	seed.ID = strings.TrimSpace(seed.ID)
	seed.Name = canonicalOAuthCustomScopeName(seed.Name)
	seed.Description = strings.TrimSpace(seed.Description)
	seed.CustomPermissionID = strings.TrimSpace(seed.CustomPermissionID)
	seed.OwnerPack = strings.TrimSpace(seed.OwnerPack)
	if seed.ID == "" {
		return seed, fmt.Errorf("id is required")
	}
	if seed.Name == "" {
		return seed, fmt.Errorf("name is required")
	}
	if isReservedOAuthScope(seed.Name) {
		return seed, fmt.Errorf("scope %q must not shadow a reserved OAuth scope", seed.Name)
	}
	seed.AllowedClientTypes = normalizedPolicyTokens(seed.AllowedClientTypes, strings.ToLower)
	seed.AllowedMethods = normalizedPolicyTokens(seed.AllowedMethods, strings.ToUpper)
	seed.RoutePatterns = normalizedPolicyTokens(seed.RoutePatterns, func(v string) string { return v })
	// The route-family prefix is supplied by the pack as seed metadata, NOT
	// derived from the owning pack identity: seed.OwnerPack is not yet set at this
	// point (the source-backed builder sets it only after normalize), so reading
	// it would resolve an empty prefix and fail OPEN — leaving the patterns
	// unvalidated. Fail closed instead: a seed that declares route patterns must
	// declare the family they belong to.
	routeFamilyPrefix := strings.TrimSpace(seed.RouteFamilyPrefix)
	if len(seed.RoutePatterns) > 0 && routeFamilyPrefix == "" {
		if !allowLegacyMissingRouteFamilyPrefix {
			return seed, fmt.Errorf("oauth_custom_scope %q declares route_patterns but no route_family_prefix", seed.Name)
		}
		// Installed-row reconstruction compat: historical payloads (revisions
		// before route_family_prefix was declared as pack metadata) validated their
		// route_patterns under the legacy Salesforce default. Reconstruct them with
		// that default so warm-upgrade orgs still resolve. Fresh authoring passes
		// allowLegacyMissingRouteFamilyPrefix=false and fails closed above.
		routeFamilyPrefix = legacyOAuthRouteFamilyPrefix
	}
	routeFamily := oauthroutes.NewFamily(routeFamilyPrefix)
	for _, pattern := range seed.RoutePatterns {
		if err := routeFamily.ValidatePattern(pattern); err != nil {
			return seed, err
		}
	}
	// RouteFamilyPrefix is validation-only pack metadata; clear it so it never
	// enters the persisted projection (the stored row has no such column, and the
	// host re-derives the prefix from the active product at serve time).
	seed.RouteFamilyPrefix = ""
	seed.ResourceLinks = normalizedPolicyTokens(seed.ResourceLinks, strings.ToLower)
	if containsToken(seed.AllowedClientTypes, "public") {
		if len(seed.AllowedMethods) == 0 || len(seed.RoutePatterns) == 0 {
			return seed, fmt.Errorf("public scope %q must declare allowed methods and route patterns", seed.Name)
		}
		for _, method := range seed.AllowedMethods {
			if method != "GET" && method != "HEAD" {
				return seed, fmt.Errorf("public scope %q may only declare GET/HEAD methods", seed.Name)
			}
		}
	}
	return seed, nil
}

func canonicalOAuthCustomScopeName(name string) string {
	return strings.ToLower(strings.TrimSpace(name))
}

func normalizedPolicyTokens(tokens []string, transform func(string) string) []string {
	seen := map[string]struct{}{}
	out := make([]string, 0, len(tokens))
	for _, token := range tokens {
		for _, part := range strings.FieldsFunc(token, func(r rune) bool {
			return r == ',' || r == '\n' || r == '\r' || r == '\t' || r == ' '
		}) {
			trimmed := strings.TrimSpace(part)
			if trimmed == "" {
				continue
			}
			if transform != nil {
				trimmed = transform(trimmed)
			}
			if _, ok := seen[trimmed]; ok {
				continue
			}
			seen[trimmed] = struct{}{}
			out = append(out, trimmed)
		}
	}
	return out
}

func containsToken(tokens []string, want string) bool {
	for _, token := range tokens {
		if token == want {
			return true
		}
	}
	return false
}

func isReservedOAuthScope(scope string) bool {
	switch canonicalOAuthCustomScopeName(scope) {
	case "api", "full", "api.read":
		return true
	default:
		return featureauth.ProductOAuthRouteScopePolicy{}.AllowsOAuthRouteScope(scope)
	}
}

// colOAuthCustomScopeAllowDynamicPublicClients is the physical column added by
// NexusCRM#3986. It is addressed as a literal (not via the generated
// `constants` package) because the seed path already names this table's columns
// as literals AND because the generated schema catalog / column constants lag
// the L0 migration during the schema-catalog skew window: controlplane's own
// ReconcileTableColumns cannot provision this column yet, so the seed path must
// tolerate its absence rather than assume it exists.
//
// This seed-path fallback is one half of a two-part safety contract; the other
// half is the catalog-gated schema-readiness requirement in
// bootstrapimpl/schema_readiness.go (catalogGatedManagedColumnRequirements).
// While the catalog lags, this fallback lets opt-in-false scopes converge and
// rejects explicit opt-ins loudly. Once a catalog bump declares the column,
// ReconcileTableColumns provisions it AND the readiness gate begins requiring
// it, so lifecycle convergence never reports ready while the paired
// dynamic-client runtime read path could still hit an unknown-column error.
const colOAuthCustomScopeAllowDynamicPublicClients = "AllowDynamicPublicClients"

// oauthCustomScopeHasAllowDynamicColumn reports whether the live schema carries
// the AllowDynamicPublicClients column. Warm-upgrade databases (and any org
// whose L0 has not yet applied NexusCRM#3986) will not have it; the seed path
// gates every reference to the column on this probe so it fails closed with a
// clear message instead of an opaque unknown-column error at write/read time.
func oauthCustomScopeHasAllowDynamicColumn(ctx context.Context, db *sql.DB) (bool, error) {
	if db == nil {
		return false, fmt.Errorf("oauth_custom_scope schema probe requires database")
	}
	return sourceLessTableHasColumn(ctx, db, constants.TableOAuthCustomScope, colOAuthCustomScopeAllowDynamicPublicClients)
}

func upsertOAuthCustomScopeSeed(ctx context.Context, db *sql.DB, seed bootstrapOAuthCustomScope) error {
	if err := validateOAuthCustomScopeOwnerClaim(ctx, db, seed); err != nil {
		return err
	}
	hasDynamicCol, err := oauthCustomScopeHasAllowDynamicColumn(ctx, db)
	if err != nil {
		return err
	}
	// A pack that explicitly opts a scope in to dynamic public-client
	// registration cannot be honored until L0 has added the backing column
	// (NexusCRM#3986). Fail loudly rather than silently dropping the opt-in
	// (the whole point of the field) or emitting an unknown-column write.
	if seed.AllowDynamicPublicClients && !hasDynamicCol {
		return fmt.Errorf(
			"oauth_custom_scope %s opts in to allow_dynamic_public_clients but %s.%s is missing; apply the NexusCRM#3986 schema migration before installing this scope",
			seed.Name, constants.TableOAuthCustomScope, colOAuthCustomScopeAllowDynamicPublicClients,
		)
	}

	insertCols := []string{"Id", "Name", "Description", "CustomPermissionId", "AllowedClientTypes", "AllowedMethods", "RoutePatterns", "ResourceLinks", "OwnerPack", "IsActive"}
	valuePlaceholders := []string{"?", "?", "?", "?", "?", "?", "?", "?", "?", "?"}
	setClauses := []string{
		"Description = VALUES(Description)",
		"CustomPermissionId = VALUES(CustomPermissionId)",
		"AllowedClientTypes = VALUES(AllowedClientTypes)",
		"AllowedMethods = VALUES(AllowedMethods)",
		"RoutePatterns = VALUES(RoutePatterns)",
		"ResourceLinks = VALUES(ResourceLinks)",
		"OwnerPack = VALUES(OwnerPack)",
		"IsActive = VALUES(IsActive)",
	}
	args := []interface{}{
		seed.ID,
		seed.Name,
		nullableOAuthScopeString(seed.Description),
		nullableOAuthScopeString(seed.CustomPermissionID),
		strings.Join(seed.AllowedClientTypes, " "),
		strings.Join(seed.AllowedMethods, " "),
		strings.Join(seed.RoutePatterns, " "),
		strings.Join(seed.ResourceLinks, " "),
		nullableOAuthScopeString(seed.OwnerPack),
		seed.Active,
	}
	// Persist the opt-in only when the schema can hold it. Absent => the DB
	// default (0/false) stands, which is backward compatible with every
	// existing scope seed (all of which are opt-in false).
	if hasDynamicCol {
		insertCols = append(insertCols, colOAuthCustomScopeAllowDynamicPublicClients)
		valuePlaceholders = append(valuePlaceholders, "?")
		setClauses = append(setClauses, colOAuthCustomScopeAllowDynamicPublicClients+" = VALUES("+colOAuthCustomScopeAllowDynamicPublicClients+")")
		args = append(args, seed.AllowDynamicPublicClients)
	}
	insertCols = append(insertCols, "CreatedDate", "LastModifiedDate")
	valuePlaceholders = append(valuePlaceholders, "NOW()", "NOW()")
	setClauses = append(setClauses, "LastModifiedDate = NOW()")

	query := fmt.Sprintf(
		"INSERT INTO _System_OAuthCustomScope (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s",
		strings.Join(insertCols, ", "),
		strings.Join(valuePlaceholders, ", "),
		strings.Join(setClauses, ", "),
	)
	if _, err := db.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("upsert _System_OAuthCustomScope %s: %w", seed.Name, err)
	}
	return nil
}

func validateOAuthCustomScopeOwnerClaim(ctx context.Context, db *sql.DB, seed bootstrapOAuthCustomScope) error {
	if db == nil {
		return fmt.Errorf("oauth_custom_scope apply requires database")
	}
	var existingOwner sql.NullString
	err := db.QueryRowContext(ctx, `
SELECT OwnerPack
  FROM _System_OAuthCustomScope
 WHERE Name = ?
 LIMIT 1`, seed.Name).Scan(&existingOwner)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return fmt.Errorf("query _System_OAuthCustomScope %s owner: %w", seed.Name, err)
	}
	gotOwner := strings.TrimSpace(existingOwner.String)
	wantOwner := strings.TrimSpace(seed.OwnerPack)
	if gotOwner == "" && wantOwner != "" {
		live, err := loadOAuthCustomScopeSeed(ctx, db, seed.Name)
		if err != nil {
			return err
		}
		live.OwnerPack = wantOwner
		if diff := diffOAuthCustomScopeSeed(seed, live); diff != "" {
			return fmt.Errorf("oauth_custom_scope %s ownerless legacy row does not match pack %q payload: %s", seed.Name, wantOwner, diff)
		}
		return nil
	}
	if gotOwner != wantOwner {
		return fmt.Errorf("oauth_custom_scope %s is already owned by pack %q, refusing to install for pack %q", seed.Name, gotOwner, wantOwner)
	}
	return nil
}

func nullableOAuthScopeString(value string) interface{} {
	value = strings.TrimSpace(value)
	if value == "" {
		return nil
	}
	return value
}

func loadOAuthCustomScopeSeed(ctx context.Context, db *sql.DB, name string) (bootstrapOAuthCustomScope, error) {
	var live bootstrapOAuthCustomScope
	// Mirror the write path: only project AllowDynamicPublicClients when the
	// live schema carries it. A warm-upgrade DB missing the column reads back
	// the backward-compatible default (false) instead of failing with an
	// unknown-column error, so Verify still converges for opt-in-false seeds.
	hasDynamicCol, err := oauthCustomScopeHasAllowDynamicColumn(ctx, db)
	if err != nil {
		return live, err
	}
	var description, customPermissionID, allowedClientTypes, allowedMethods, routePatterns, resourceLinks, ownerPack sql.NullString
	scanTargets := []interface{}{
		&live.ID,
		&live.Name,
		&description,
		&customPermissionID,
		&allowedClientTypes,
		&allowedMethods,
		&routePatterns,
		&resourceLinks,
		&ownerPack,
		&live.Active,
	}
	selectColumns := "Id, Name, Description, CustomPermissionId, AllowedClientTypes, AllowedMethods, RoutePatterns, ResourceLinks, OwnerPack, IsActive"
	if hasDynamicCol {
		selectColumns += ", " + colOAuthCustomScopeAllowDynamicPublicClients
		scanTargets = append(scanTargets, &live.AllowDynamicPublicClients)
	}
	err = db.QueryRowContext(ctx,
		"SELECT "+selectColumns+" FROM _System_OAuthCustomScope WHERE Name = ? LIMIT 1",
		name,
	).Scan(scanTargets...)
	if err == sql.ErrNoRows {
		return live, fmt.Errorf("_System_OAuthCustomScope %s is missing", name)
	}
	if err != nil {
		return live, fmt.Errorf("query _System_OAuthCustomScope %s: %w", name, err)
	}
	live.Description = description.String
	live.CustomPermissionID = customPermissionID.String
	live.AllowedClientTypes = normalizedPolicyTokens([]string{allowedClientTypes.String}, strings.ToLower)
	live.AllowedMethods = normalizedPolicyTokens([]string{allowedMethods.String}, strings.ToUpper)
	live.RoutePatterns = normalizedPolicyTokens([]string{routePatterns.String}, func(v string) string { return v })
	live.ResourceLinks = normalizedPolicyTokens([]string{resourceLinks.String}, strings.ToLower)
	live.OwnerPack = strings.TrimSpace(ownerPack.String)
	return live, nil
}

func diffOAuthCustomScopeSeed(want, got bootstrapOAuthCustomScope) string {
	if want.ID != got.ID ||
		want.Name != got.Name ||
		want.Description != got.Description ||
		want.CustomPermissionID != got.CustomPermissionID ||
		!oauthStringSlicesEqual(want.AllowedClientTypes, got.AllowedClientTypes) ||
		!oauthStringSlicesEqual(want.AllowedMethods, got.AllowedMethods) ||
		!oauthStringSlicesEqual(want.RoutePatterns, got.RoutePatterns) ||
		!oauthStringSlicesEqual(want.ResourceLinks, got.ResourceLinks) ||
		want.OwnerPack != got.OwnerPack ||
		want.Active != got.Active ||
		want.AllowDynamicPublicClients != got.AllowDynamicPublicClients {
		wantJSON, _ := json.Marshal(want)
		gotJSON, _ := json.Marshal(got)
		return fmt.Sprintf("_System_OAuthCustomScope %s drifted: expected %s, live %s", want.Name, wantJSON, gotJSON)
	}
	return ""
}

func oauthStringSlicesEqual(a, b []string) bool {
	if len(a) != len(b) {
		return false
	}
	for i := range a {
		if a[i] != b[i] {
			return false
		}
	}
	return true
}

func resolveAdminPassword(seedPassword, envPassword, nexusEnv string) (string, bool, error) {
	nexusEnv = strings.ToLower(strings.TrimSpace(nexusEnv))
	envPassword = strings.TrimSpace(envPassword)
	if envPassword != "" {
		return envPassword, true, nil
	}
	if nexusEnv == "production" || nexusEnv == "prod" || nexusEnv == "staging" {
		return "", false, fmt.Errorf("NEXUS_ADMIN_PASSWORD must be set (NEXUS_ENV=%q) — refusing to bootstrap without an explicit admin password", nexusEnv)
	}
	// Dev/CI/test: generate a random password so bootstrap doesn't break
	b := make([]byte, 12)
	if _, err := rand.Read(b); err != nil {
		return "", false, fmt.Errorf("failed to generate random admin password: %w", err)
	}
	generated := hex.EncodeToString(b)
	log.Printf("⚠️  NEXUS_ADMIN_PASSWORD not set — generated random admin password for non-production environment (NEXUS_ENV=%q). Password: %s", nexusEnv, generated)
	log.Printf("   Set NEXUS_ADMIN_PASSWORD for a stable password. Note: existing accounts keep their current password.")
	return generated, false, nil
}