package packs

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"reflect"
	"sort"
	"strings"

	featureauth "github.com/nexuscrm/auth"
	pkgauth "github.com/nexuscrm/kernel/pkg/auth"
	"github.com/nexuscrm/records"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

const (
	sourceConnectedAppClientTypePublic       = "public"
	sourceConnectedAppClientTypeConfidential = "confidential"
	sourceBackedConnectedAppDeclarationTable = "_System_ConnectedAppDeclaration"
	sourceBackedConnectedAppStageTable       = "_System_ConnectedAppStage"
)

type connectedAppSQLRunner interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

type sourceBackedConnectedAppPayload struct {
	ID                      string                          `json:"id"`
	ClientID                string                          `json:"client_id,omitempty"`
	Name                    string                          `json:"name"`
	ConsumerKey             string                          `json:"consumerKey"`
	ConsumerKeySnake        string                          `json:"consumer_key,omitempty"`
	KeyID                   string                          `json:"key_id,omitempty"`
	ConsumerSecret          string                          `json:"consumerSecret,omitempty"`
	ConsumerSecretSnake     string                          `json:"consumer_secret,omitempty"`
	Secret                  string                          `json:"secret,omitempty"`
	ClientType              string                          `json:"clientType"`
	RedirectURIs            sourceBackedConnectedAppStrings `json:"redirectUris"`
	CallbackURL             string                          `json:"callbackUrl"`
	Scopes                  sourceBackedConnectedAppStrings `json:"scopes"`
	IsActive                *bool                           `json:"isActive"`
	TokenEndpointAuthMethod string                          `json:"tokenEndpointAuthMethod"`
	AllowedGrantTypes       sourceBackedConnectedAppStrings `json:"allowedGrantTypes"`
	ConsentPolicy           string                          `json:"consentPolicy"`
	ConsentVersion          *int                            `json:"consentVersion"`
	RequirePKCE             *bool                           `json:"requirePkce"`
	IssueRefreshTokens      *bool                           `json:"issueRefreshTokens"`
	RunAsUserID             string                          `json:"runAsUserId,omitempty"`
	RunAsUserIDSnake        string                          `json:"run_as_user_id,omitempty"`
	PermittedUsers          string                          `json:"permittedUsers"`
	PermittedUsersSnake     string                          `json:"permitted_users,omitempty"`
	SessionIssuerClass      string                          `json:"sessionIssuerClass,omitempty"`
	SessionIssuerClassSnake string                          `json:"session_issuer_class,omitempty"`
	PublisherName           string                          `json:"publisherName,omitempty"`
	SupportURL              string                          `json:"supportUrl,omitempty"`
	DailyAPILimit           *int                            `json:"dailyApiLimit,omitempty"`
	HourlyAPILimit          *int                            `json:"hourlyApiLimit,omitempty"`
}

type sourceBackedConnectedAppStrings []string

func (s *sourceBackedConnectedAppStrings) UnmarshalJSON(data []byte) error {
	var values []string
	if err := json.Unmarshal(data, &values); err == nil {
		*s = compactConnectedAppComponentStrings(values)
		return nil
	}
	var one string
	if err := json.Unmarshal(data, &one); err != nil {
		return err
	}
	*s = splitConnectedAppComponentStringList(one)
	return nil
}

type sourceBackedConnectedAppProjection struct {
	ID                           string   `json:"id"`
	Name                         string   `json:"name"`
	ConsumerKey                  string   `json:"consumerKey"`
	CallbackURL                  string   `json:"callbackUrl"`
	RedirectURIs                 []string `json:"redirectUris"`
	Scopes                       []string `json:"scopes"`
	IsActive                     bool     `json:"isActive"`
	ClientType                   string   `json:"clientType"`
	RequirePKCE                  bool     `json:"requirePkce"`
	TokenEndpointAuthMethod      string   `json:"tokenEndpointAuthMethod"`
	AllowedGrantTypes            []string `json:"allowedGrantTypes"`
	ConsentPolicy                string   `json:"consentPolicy"`
	ConsentVersion               int      `json:"consentVersion"`
	IssueRefreshTokens           bool     `json:"issueRefreshTokens"`
	PublisherName                string   `json:"publisherName,omitempty"`
	SupportURL                   string   `json:"supportUrl,omitempty"`
	RunAsUserID                  string   `json:"runAsUserId,omitempty"`
	PermittedUsers               string   `json:"permittedUsers"`
	SessionIssuerClass           string   `json:"sessionIssuerClass,omitempty"`
	DailyAPILimit                *int     `json:"dailyApiLimit,omitempty"`
	HourlyAPILimit               *int     `json:"hourlyApiLimit,omitempty"`
	ConsumerSecretPlaintextEmpty bool     `json:"consumerSecretPlaintextEmpty"`
	ConsumerSecretMatches        bool     `json:"consumerSecretMatches"`
}

type sourceBackedConnectedAppComponentProjection struct {
	App         sourceBackedConnectedAppProjection     `json:"app"`
	Declaration sourceBackedConnectedAppDeclarationRow `json:"declaration"`
}

type sourceBackedConnectedAppDeclarationRow struct {
	ID                string `json:"id"`
	InstalledPackName string `json:"installedPackName"`
	ConnectedAppID    string `json:"connectedAppId"`
	PayloadHash       string `json:"payloadHash"`
	IsDeleted         int    `json:"isDeleted"`
}

type sourceBackedConnectedAppStageRow struct {
	ID                string `json:"id"`
	InstalledPackName string `json:"installedPackName"`
	ConnectedAppID    string `json:"connectedAppId"`
	PayloadHash       string `json:"payloadHash"`
	RecordJSON        string `json:"recordJson"`
	IsDeleted         int    `json:"isDeleted"`
}

func newSourceBackedConnectedAppComponent(packName string, component ManifestComponent, payload []byte) (PackComponent, error) {
	return newSourceBackedConnectedAppComponentWithPolicy(packName, component, payload, nil)
}

func newSourceBackedConnectedAppComponentWithPolicy(packName string, component ManifestComponent, payload []byte, publicScopePolicy records.ConnectedAppPublicScopePolicyProvider) (PackComponent, error) {
	return newSourceBackedConnectedAppComponentWithPolicyAndDeclaredIDs(packName, component, payload, publicScopePolicy, nil)
}

func newSourceBackedConnectedAppComponentWithPolicyAndDeclaredIDs(packName string, component ManifestComponent, payload []byte, publicScopePolicy records.ConnectedAppPublicScopePolicyProvider, declaredIDs map[string]struct{}) (PackComponent, error) {
	seed, record, projection, err := sourceBackedConnectedAppComponentModel(packName, component, payload, publicScopePolicy)
	if err != nil {
		return nil, err
	}
	declaration, err := sourceBackedConnectedAppDeclarationForProjection(packName, seed.ID, projection)
	if err != nil {
		return nil, err
	}
	expected := sourceBackedConnectedAppComponentProjection{
		App:         projection,
		Declaration: declaration,
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: component,
		payload:           append([]byte(nil), payload...),
		expectedProjection: func() (any, error) {
			return expected, nil
		},
		liveProjection: func(ctx context.Context, deps VerifyDeps) (any, error) {
			return loadConnectedAppComponentProjection(ctx, deps.DB, packName, seed.ID, seed.ConsumerSecret)
		},
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applySourceBackedConnectedAppRecord(ctx, deps, record, seed.ConsumerSecret, projection, declaration, declaredIDs)
		},
	}, nil
}

func validateConnectedAppComponentWithPolicy(packName string, component ManifestComponent, payload []byte, pathPrefix string, publicScopePolicy records.ConnectedAppPublicScopePolicyProvider) []PackDiagnostic {
	if _, _, _, err := sourceBackedConnectedAppComponentModel(packName, component, payload, publicScopePolicy); err != nil {
		return []PackDiagnostic{diag("component.payload.invalid_schema", pathPrefix, err.Error())}
	}
	return nil
}

func sourceBackedConnectedAppComponentModel(packName string, component ManifestComponent, payload []byte, publicScopePolicy records.ConnectedAppPublicScopePolicyProvider) (sourceBackedConnectedAppPayload, models.SObject, sourceBackedConnectedAppProjection, error) {
	seed, err := parseConnectedAppComponentPayload(payload, componentPayloadLabel(component))
	if err != nil {
		return seed, nil, sourceBackedConnectedAppProjection{}, err
	}
	if businessKey := strings.TrimSpace(component.BusinessKey); businessKey != seed.ID {
		return seed, nil, sourceBackedConnectedAppProjection{}, fmt.Errorf("connected_app businessKey %q must match payload id %q", businessKey, seed.ID)
	}
	record, err := connectedAppComponentRecord(seed)
	if err != nil {
		return seed, nil, sourceBackedConnectedAppProjection{}, err
	}
	if err := records.NormalizeSourceBackedConnectedAppPolicy(record, records.SourceBackedConnectedAppPolicyOptions{
		PackName:                 packName,
		PublicScopePolicy:        publicScopePolicy,
		SessionIssuerClassPolicy: featureauth.ProductOAuthSessionIssuerClassPolicy{},
	}); err != nil {
		return seed, nil, sourceBackedConnectedAppProjection{}, err
	}
	projection, err := connectedAppComponentProjection(record, seed.ConsumerSecret)
	if err != nil {
		return seed, nil, sourceBackedConnectedAppProjection{}, err
	}
	return seed, record, projection, nil
}

type sourceBackedConnectedAppPublicScopePolicy map[string]map[string]struct{}

func (p sourceBackedConnectedAppPublicScopePolicy) AllowsPublicOAuthScope(scope string) bool {
	owners := p[canonicalOAuthCustomScopeName(scope)]
	return len(owners) > 0
}

func (p sourceBackedConnectedAppPublicScopePolicy) AllowsPublicOAuthScopeForPack(scope, packName string) bool {
	owners := p[canonicalOAuthCustomScopeName(scope)]
	if len(owners) == 0 {
		return false
	}
	_, ok := owners[strings.TrimSpace(packName)]
	return ok
}

func sourceBackedConnectedAppPublicScopePolicyFromSource(packName string, source *ResolvedPackSource, allowLegacyMissingRouteFamilyPrefix bool) (records.ConnectedAppPublicScopePolicyProvider, error) {
	policy := sourceBackedConnectedAppPublicScopePolicy{}
	if source == nil || source.Manifest == nil {
		return policy, nil
	}
	ownerPack := strings.TrimSpace(packName)
	for _, component := range source.Manifest.Components {
		if component.Kind != ComponentKindOAuthCustomScope {
			continue
		}
		payload, err := sourceBackedComponentPayload(source, component)
		if err != nil {
			return nil, fmt.Errorf("%s %s payload: %w", component.Kind, component.BusinessKey, err)
		}
		seed, err := parseOAuthCustomScopeComponentPayload(payload, componentPayloadLabel(component), allowLegacyMissingRouteFamilyPrefix)
		if err != nil {
			return nil, fmt.Errorf("%s %s: %w", component.Kind, component.BusinessKey, err)
		}
		if seed.Active && containsToken(seed.AllowedClientTypes, "public") {
			scope := seed.Name
			if policy[scope] == nil {
				policy[scope] = map[string]struct{}{}
			}
			policy[scope][ownerPack] = struct{}{}
		}
	}
	return policy, nil
}

func sourceBackedConnectedAppDeclaredIDSetFromSource(packName string, source *ResolvedPackSource, publicScopePolicy records.ConnectedAppPublicScopePolicyProvider) (map[string]struct{}, error) {
	declared := map[string]struct{}{}
	if source == nil || source.Manifest == nil {
		return declared, nil
	}
	for _, component := range source.Manifest.Components {
		if component.Kind != ComponentKindConnectedApp {
			continue
		}
		payload, err := sourceBackedComponentPayload(source, component)
		if err != nil {
			return nil, fmt.Errorf("%s %s payload: %w", component.Kind, component.BusinessKey, err)
		}
		_, _, projection, err := sourceBackedConnectedAppComponentModel(packName, component, payload, publicScopePolicy)
		if err != nil {
			return nil, err
		}
		if key := connectedAppDeclarationKey(projection.ID); key != "" {
			declared[key] = struct{}{}
		}
	}
	return declared, nil
}

func parseConnectedAppComponentPayload(payload []byte, source string) (sourceBackedConnectedAppPayload, error) {
	var seed sourceBackedConnectedAppPayload
	if err := json.Unmarshal(payload, &seed); err != nil {
		return seed, fmt.Errorf("parse %s: %w", source, err)
	}
	seed.ID = strings.TrimSpace(firstNonEmpty(seed.ID, seed.ClientID))
	seed.Name = strings.TrimSpace(seed.Name)
	seed.ConsumerKey = strings.TrimSpace(firstNonEmpty(seed.ConsumerKey, seed.ConsumerKeySnake, seed.KeyID))
	seed.ConsumerSecret = strings.TrimSpace(firstNonEmpty(seed.ConsumerSecret, seed.ConsumerSecretSnake, seed.Secret))
	seed.ClientType = strings.ToLower(strings.TrimSpace(seed.ClientType))
	seed.CallbackURL = strings.TrimSpace(seed.CallbackURL)
	seed.Scopes = canonicalConnectedAppComponentWordSet(seed.Scopes)
	seed.TokenEndpointAuthMethod = strings.ToLower(strings.TrimSpace(seed.TokenEndpointAuthMethod))
	seed.AllowedGrantTypes = canonicalConnectedAppComponentWordSet(seed.AllowedGrantTypes)
	seed.ConsentPolicy = strings.ToLower(strings.TrimSpace(seed.ConsentPolicy))
	seed.RunAsUserID = strings.TrimSpace(firstNonEmpty(seed.RunAsUserID, seed.RunAsUserIDSnake))
	seed.PermittedUsers = strings.TrimSpace(firstNonEmpty(seed.PermittedUsers, seed.PermittedUsersSnake))
	seed.SessionIssuerClass = featureauth.NormalizeProductCapabilityToken(firstNonEmpty(seed.SessionIssuerClass, seed.SessionIssuerClassSnake))
	seed.PublisherName = strings.TrimSpace(seed.PublisherName)
	seed.SupportURL = strings.TrimSpace(seed.SupportURL)
	seed.RedirectURIs = compactConnectedAppComponentStrings(seed.RedirectURIs)
	if err := validateConnectedAppComponentRequiredPayload(seed, source); err != nil {
		return seed, err
	}
	return seed, nil
}

func validateConnectedAppComponentRequiredPayload(seed sourceBackedConnectedAppPayload, source string) error {
	required := []struct {
		name  string
		empty bool
	}{
		{"id", seed.ID == ""},
		{"name", seed.Name == ""},
		{"consumerKey", seed.ConsumerKey == ""},
		{"callbackUrl", seed.CallbackURL == ""},
		{"clientType", seed.ClientType == ""},
		{"redirectUris", len(seed.RedirectURIs) == 0},
		{"scopes", len(seed.Scopes) == 0},
		{"tokenEndpointAuthMethod", seed.TokenEndpointAuthMethod == ""},
		{"allowedGrantTypes", len(seed.AllowedGrantTypes) == 0},
		{"consentPolicy", seed.ConsentPolicy == ""},
		{"permittedUsers", seed.PermittedUsers == ""},
		{"isActive", seed.IsActive == nil},
		{"consentVersion", seed.ConsentVersion == nil},
		{"requirePkce", seed.RequirePKCE == nil},
		{"issueRefreshTokens", seed.IssueRefreshTokens == nil},
	}
	for _, field := range required {
		if field.empty {
			return fmt.Errorf("%s %s is required", source, field.name)
		}
	}
	switch seed.ClientType {
	case sourceConnectedAppClientTypeConfidential:
		if seed.ConsumerSecret == "" {
			return fmt.Errorf("%s consumerSecret is required for confidential clients", source)
		}
	case sourceConnectedAppClientTypePublic:
		if seed.ConsumerSecret != "" {
			return fmt.Errorf("%s consumerSecret is forbidden for public clients", source)
		}
		if seed.SessionIssuerClass != "" {
			return fmt.Errorf("%s sessionIssuerClass is forbidden for public clients", source)
		}
	default:
		return fmt.Errorf("%s clientType must be public or confidential", source)
	}
	return nil
}

func connectedAppComponentRecord(seed sourceBackedConnectedAppPayload) (models.SObject, error) {
	redirects, err := connectedAppComponentRedirectsJSON(seed.RedirectURIs)
	if err != nil {
		return nil, err
	}
	record := models.SObject{
		constants.ColSysConnectedApp_ID:                      seed.ID,
		constants.ColSysConnectedApp_Name:                    seed.Name,
		constants.ColSysConnectedApp_ConsumerKey:             seed.ConsumerKey,
		constants.ColSysConnectedApp_ConsumerSecret:          seed.ConsumerSecret,
		constants.ColSysConnectedApp_CallbackURL:             seed.CallbackURL,
		constants.ColSysConnectedApp_RedirectURIs:            redirects,
		constants.ColSysConnectedApp_Scopes:                  strings.Join(seed.Scopes, " "),
		constants.ColSysConnectedApp_IsActive:                boolValue(seed.IsActive),
		constants.ColSysConnectedApp_ClientType:              seed.ClientType,
		constants.ColSysConnectedApp_RequirePkce:             boolValue(seed.RequirePKCE),
		constants.ColSysConnectedApp_TokenEndpointAuthMethod: seed.TokenEndpointAuthMethod,
		constants.ColSysConnectedApp_AllowedGrantTypes:       strings.Join(seed.AllowedGrantTypes, " "),
		constants.ColSysConnectedApp_ConsentPolicy:           seed.ConsentPolicy,
		constants.ColSysConnectedApp_ConsentVersion:          intValue(seed.ConsentVersion),
		constants.ColSysConnectedApp_IssueRefreshTokens:      boolValue(seed.IssueRefreshTokens),
		constants.ColSysConnectedApp_PublisherName:           seed.PublisherName,
		constants.ColSysConnectedApp_SupportURL:              seed.SupportURL,
		constants.ColSysConnectedApp_RunAsUserID:             seed.RunAsUserID,
		constants.ColSysConnectedApp_PermittedUsers:          seed.PermittedUsers,
		constants.ColSysConnectedApp_SessionIssuerClass:      seed.SessionIssuerClass,
	}
	if seed.DailyAPILimit != nil {
		record[constants.ColSysConnectedApp_DailyAPILimit] = *seed.DailyAPILimit
	}
	if seed.HourlyAPILimit != nil {
		record[constants.ColSysConnectedApp_HourlyAPILimit] = *seed.HourlyAPILimit
	}
	return record, nil
}

func connectedAppComponentProjection(record models.SObject, expectedSecret string) (sourceBackedConnectedAppProjection, error) {
	redirects, err := connectedAppComponentRedirects(record[constants.ColSysConnectedApp_RedirectURIs])
	if err != nil {
		return sourceBackedConnectedAppProjection{}, err
	}
	return sourceBackedConnectedAppProjection{
		ID:                           connectedAppComponentString(record[constants.ColSysConnectedApp_ID]),
		Name:                         connectedAppComponentString(record[constants.ColSysConnectedApp_Name]),
		ConsumerKey:                  connectedAppComponentString(record[constants.ColSysConnectedApp_ConsumerKey]),
		CallbackURL:                  connectedAppComponentString(record[constants.ColSysConnectedApp_CallbackURL]),
		RedirectURIs:                 redirects,
		Scopes:                       splitConnectedAppComponentStringList(connectedAppComponentString(record[constants.ColSysConnectedApp_Scopes])),
		IsActive:                     connectedAppComponentBool(record[constants.ColSysConnectedApp_IsActive]),
		ClientType:                   strings.ToLower(connectedAppComponentString(record[constants.ColSysConnectedApp_ClientType])),
		RequirePKCE:                  connectedAppComponentBool(record[constants.ColSysConnectedApp_RequirePkce]),
		TokenEndpointAuthMethod:      strings.ToLower(connectedAppComponentString(record[constants.ColSysConnectedApp_TokenEndpointAuthMethod])),
		AllowedGrantTypes:            splitConnectedAppComponentStringList(connectedAppComponentString(record[constants.ColSysConnectedApp_AllowedGrantTypes])),
		ConsentPolicy:                strings.ToLower(connectedAppComponentString(record[constants.ColSysConnectedApp_ConsentPolicy])),
		ConsentVersion:               connectedAppComponentInt(record[constants.ColSysConnectedApp_ConsentVersion]),
		IssueRefreshTokens:           connectedAppComponentBool(record[constants.ColSysConnectedApp_IssueRefreshTokens]),
		PublisherName:                connectedAppComponentString(record[constants.ColSysConnectedApp_PublisherName]),
		SupportURL:                   connectedAppComponentString(record[constants.ColSysConnectedApp_SupportURL]),
		RunAsUserID:                  connectedAppComponentString(record[constants.ColSysConnectedApp_RunAsUserID]),
		PermittedUsers:               connectedAppComponentString(record[constants.ColSysConnectedApp_PermittedUsers]),
		SessionIssuerClass:           strings.ToLower(connectedAppComponentString(record[constants.ColSysConnectedApp_SessionIssuerClass])),
		DailyAPILimit:                connectedAppComponentIntPtr(record[constants.ColSysConnectedApp_DailyAPILimit]),
		HourlyAPILimit:               connectedAppComponentIntPtr(record[constants.ColSysConnectedApp_HourlyAPILimit]),
		ConsumerSecretPlaintextEmpty: connectedAppComponentString(record[constants.ColSysConnectedApp_ConsumerSecret]) == "",
		ConsumerSecretMatches:        expectedSecret == "" || connectedAppComponentString(record[constants.ColSysConnectedApp_ConsumerSecretHash]) != "",
	}, nil
}

func loadConnectedAppComponentProjection(ctx context.Context, db *sql.DB, packName, id, expectedSecret string) (sourceBackedConnectedAppComponentProjection, error) {
	if installing, err := sourceBackedConnectedAppPackIsInstalling(ctx, db, packName); err != nil {
		return sourceBackedConnectedAppComponentProjection{}, err
	} else if installing {
		stage, ok, err := loadSourceBackedConnectedAppStage(ctx, db, packName, id)
		if err != nil {
			return sourceBackedConnectedAppComponentProjection{}, err
		}
		if ok {
			if stage.IsDeleted != 0 {
				return sourceBackedConnectedAppComponentProjection{}, fmt.Errorf("%s row for pack %s connected_app %s is staged for deletion", sourceBackedConnectedAppStageTable, strings.TrimSpace(packName), strings.TrimSpace(id))
			}
			app, err := connectedAppProjectionFromStage(stage, expectedSecret)
			if err != nil {
				return sourceBackedConnectedAppComponentProjection{}, err
			}
			declaration, err := loadSourceBackedConnectedAppDeclaration(ctx, db, packName, id)
			if err != nil {
				return sourceBackedConnectedAppComponentProjection{}, err
			}
			return sourceBackedConnectedAppComponentProjection{
				App:         app,
				Declaration: declaration,
			}, nil
		}
	}
	app, err := loadConnectedAppProjection(ctx, db, id, expectedSecret)
	if err != nil {
		return sourceBackedConnectedAppComponentProjection{}, err
	}
	declaration, err := loadSourceBackedConnectedAppDeclaration(ctx, db, packName, id)
	if err != nil {
		return sourceBackedConnectedAppComponentProjection{}, err
	}
	return sourceBackedConnectedAppComponentProjection{
		App:         app,
		Declaration: declaration,
	}, nil
}

func loadConnectedAppProjection(ctx context.Context, db *sql.DB, id, expectedSecret string) (sourceBackedConnectedAppProjection, error) {
	projection, ok, err := loadConnectedAppProjectionIfExists(ctx, db, id, expectedSecret)
	if err != nil {
		return projection, err
	}
	if !ok {
		return projection, fmt.Errorf("_System_ConnectedApp %s is missing", id)
	}
	return projection, nil
}

func loadConnectedAppProjectionIfExists(ctx context.Context, db connectedAppSQLRunner, id, expectedSecret string) (sourceBackedConnectedAppProjection, bool, error) {
	var projection sourceBackedConnectedAppProjection
	if db == nil {
		return projection, false, fmt.Errorf("connected_app projection requires database")
	}
	var callbackURL, redirectURIs, scopes, tokenAuth, allowedGrants, consentPolicy sql.NullString
	var publisherName, supportURL, runAsUserID, permittedUsers, sessionIssuerClass, consumerSecret, consumerSecretHash sql.NullString
	var dailyLimit, hourlyLimit sql.NullInt64
	err := db.QueryRowContext(ctx, `
SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,
       TokenEndpointAuthMethod, AllowedGrantTypes, ConsentPolicy, ConsentVersion, IssueRefreshTokens,
       PublisherName, SupportUrl, RunAsUserId, PermittedUsers, SessionIssuerClass, DailyApiLimit, HourlyApiLimit, ConsumerSecret, ConsumerSecretHash
FROM `+constants.TableConnectedApp+`
WHERE Id = ?`, id).
		Scan(
			&projection.ID,
			&projection.Name,
			&projection.ConsumerKey,
			&callbackURL,
			&redirectURIs,
			&scopes,
			&projection.IsActive,
			&projection.ClientType,
			&projection.RequirePKCE,
			&tokenAuth,
			&allowedGrants,
			&consentPolicy,
			&projection.ConsentVersion,
			&projection.IssueRefreshTokens,
			&publisherName,
			&supportURL,
			&runAsUserID,
			&permittedUsers,
			&sessionIssuerClass,
			&dailyLimit,
			&hourlyLimit,
			&consumerSecret,
			&consumerSecretHash,
		)
	if err == sql.ErrNoRows {
		return projection, false, nil
	}
	if err != nil {
		return projection, false, fmt.Errorf("query _System_ConnectedApp %s: %w", id, err)
	}
	redirects, err := connectedAppComponentRedirects(redirectURIs.String)
	if err != nil {
		return projection, false, err
	}
	projection.CallbackURL = callbackURL.String
	projection.RedirectURIs = redirects
	projection.Scopes = splitConnectedAppComponentStringList(scopes.String)
	projection.ClientType = strings.ToLower(strings.TrimSpace(projection.ClientType))
	projection.TokenEndpointAuthMethod = strings.ToLower(strings.TrimSpace(tokenAuth.String))
	projection.AllowedGrantTypes = splitConnectedAppComponentStringList(allowedGrants.String)
	projection.ConsentPolicy = strings.ToLower(strings.TrimSpace(consentPolicy.String))
	projection.PublisherName = publisherName.String
	projection.SupportURL = supportURL.String
	projection.RunAsUserID = runAsUserID.String
	projection.PermittedUsers = permittedUsers.String
	projection.SessionIssuerClass = strings.ToLower(strings.TrimSpace(sessionIssuerClass.String))
	if dailyLimit.Valid {
		value := int(dailyLimit.Int64)
		projection.DailyAPILimit = &value
	}
	if hourlyLimit.Valid {
		value := int(hourlyLimit.Int64)
		projection.HourlyAPILimit = &value
	}
	projection.ConsumerSecretPlaintextEmpty = !consumerSecret.Valid || strings.TrimSpace(consumerSecret.String) == ""
	if expectedSecret == "" {
		projection.ConsumerSecretMatches = !consumerSecretHash.Valid || strings.TrimSpace(consumerSecretHash.String) == ""
	} else if consumerSecretHash.Valid {
		projection.ConsumerSecretMatches = pkgauth.VerifyPassword(expectedSecret, consumerSecretHash.String)
	}
	return projection, true, nil
}

func applySourceBackedConnectedAppRecord(ctx context.Context, deps ApplyDeps, record models.SObject, expectedSecret string, expected sourceBackedConnectedAppProjection, declaration sourceBackedConnectedAppDeclarationRow, declaredIDs map[string]struct{}) error {
	if deps.DB == nil {
		return fmt.Errorf("connected_app apply requires database")
	}
	tx, err := deps.DB.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin connected_app %s apply transaction: %w", connectedAppComponentString(record[constants.ColSysConnectedApp_ID]), err)
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
	}()

	id := connectedAppComponentString(record[constants.ColSysConnectedApp_ID])
	before, exists, err := loadConnectedAppProjectionIfExists(ctx, tx, id, expectedSecret)
	if err != nil {
		return err
	}
	if err := validateSourceBackedConnectedAppOwnershipClaim(ctx, tx, declaration, expected, exists, declaredIDs); err != nil {
		return err
	}
	if err := upsertSourceBackedConnectedAppDeclaration(ctx, tx, declaration); err != nil {
		return err
	}
	stage := sourceBackedConnectedAppStageRow{
		ID:                declaration.ID,
		InstalledPackName: declaration.InstalledPackName,
		ConnectedAppID:    declaration.ConnectedAppID,
		PayloadHash:       declaration.PayloadHash,
		IsDeleted:         0,
	}
	changed := !exists || !reflect.DeepEqual(before, expected)
	if changed {
		if err := upsertSourceBackedConnectedAppStage(ctx, tx, stage, record); err != nil {
			return err
		}
	} else if err := deleteSourceBackedConnectedAppStage(ctx, tx, stage); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit connected_app %s apply transaction: %w", id, err)
	}
	committed = true
	return nil
}

func PublishSourceBackedConnectedAppsForPackInTx(ctx context.Context, tx *sql.Tx, packName string) ([]string, error) {
	if tx == nil {
		return nil, fmt.Errorf("connected_app publish requires transaction")
	}
	staged, err := loadSourceBackedConnectedAppStagesForPack(ctx, tx, packName)
	if err != nil {
		return nil, err
	}
	if len(staged) == 0 {
		return nil, nil
	}
	sort.Slice(staged, func(i, j int) bool {
		if staged[i].ConnectedAppID != staged[j].ConnectedAppID {
			return staged[i].ConnectedAppID < staged[j].ConnectedAppID
		}
		return staged[i].ID < staged[j].ID
	})

	changed := map[string]struct{}{}
	for _, row := range staged {
		appID := strings.TrimSpace(row.ConnectedAppID)
		if appID == "" {
			continue
		}
		if row.IsDeleted == 0 {
			continue
		}
		if err := tombstoneConnectedAppRecord(ctx, tx, appID); err != nil {
			return nil, err
		}
		declaration := sourceBackedConnectedAppDeclarationRow{
			ID:                row.ID,
			InstalledPackName: row.InstalledPackName,
			ConnectedAppID:    row.ConnectedAppID,
			PayloadHash:       row.PayloadHash,
			IsDeleted:         1,
		}
		if err := upsertSourceBackedConnectedAppDeclarationExact(ctx, tx, declaration); err != nil {
			return nil, err
		}
		if err := revokeConnectedAppSessions(ctx, tx, appID); err != nil {
			return nil, err
		}
		changed[connectedAppDeclarationKey(appID)] = struct{}{}
	}
	for _, row := range staged {
		appID := strings.TrimSpace(row.ConnectedAppID)
		if appID == "" || row.IsDeleted != 0 {
			continue
		}
		record, err := connectedAppRecordFromStage(row)
		if err != nil {
			return nil, err
		}
		expected, err := connectedAppComponentProjection(record, "")
		if err != nil {
			return nil, err
		}
		legacyIDs, err := ownerlessConnectedAppAdoptionIDs(ctx, tx, row.InstalledPackName, expected)
		if err != nil {
			return nil, err
		}
		for _, legacyID := range legacyIDs {
			if err := tombstoneConnectedAppRecord(ctx, tx, legacyID); err != nil {
				return nil, err
			}
			if err := revokeConnectedAppSessions(ctx, tx, legacyID); err != nil {
				return nil, err
			}
			changed[connectedAppDeclarationKey(legacyID)] = struct{}{}
		}
		exists, err := connectedAppRowExists(ctx, tx, appID)
		if err != nil {
			return nil, err
		}
		if err := writeConnectedAppRecord(ctx, tx, record, exists); err != nil {
			return nil, err
		}
		if err := revokeConnectedAppSessions(ctx, tx, appID); err != nil {
			return nil, err
		}
		changed[connectedAppDeclarationKey(appID)] = struct{}{}
	}
	if err := deleteSourceBackedConnectedAppStagesForPack(ctx, tx, packName); err != nil {
		return nil, err
	}
	appIDs := make([]string, 0, len(changed))
	for appID := range changed {
		if appID != "" {
			appIDs = append(appIDs, appID)
		}
	}
	sort.Strings(appIDs)
	return appIDs, nil
}

func tombstoneConnectedAppRecord(ctx context.Context, db connectedAppSQLRunner, appID string) error {
	appID = strings.TrimSpace(appID)
	if appID == "" {
		return nil
	}
	name := sourceBackedConnectedAppTombstoneValue("deleted connected_app", appID)
	consumerKey := sourceBackedConnectedAppTombstoneValue("deleted_consumer_key", appID)
	if _, err := db.ExecContext(ctx, `
UPDATE `+constants.TableConnectedApp+`
   SET Name = ?,
       ConsumerKey = ?,
       ConsumerSecret = NULL,
       ConsumerSecretHash = NULL,
       IsActive = 0,
       LastModifiedDate = CURRENT_TIMESTAMP
 WHERE Id = ?`, name, consumerKey, appID); err != nil {
		return fmt.Errorf("tombstone ConnectedApp %s: %w", appID, err)
	}
	return nil
}

func sourceBackedConnectedAppTombstoneValue(prefix, appID string) string {
	appID = strings.TrimSpace(appID)
	sum := sha256.Sum256([]byte(strings.ToLower(appID)))
	suffix := hex.EncodeToString(sum[:8])
	value := strings.TrimSpace(prefix) + "_" + appID + "_" + suffix
	if len(value) <= 255 {
		return value
	}
	keep := 255 - len(prefix) - len(suffix) - 2
	if keep < 0 {
		return suffix
	}
	trimmed := appID
	if len(trimmed) > keep {
		trimmed = trimmed[:keep]
	}
	return strings.TrimSpace(prefix) + "_" + trimmed + "_" + suffix
}

func NotifySourceBackedConnectedAppChanges(deps ApplyDeps, appIDs []string) {
	if deps.ConnectedAppHook == nil {
		return
	}
	seen := map[string]struct{}{}
	for _, appID := range appIDs {
		appID = strings.TrimSpace(appID)
		if appID == "" {
			continue
		}
		key := connectedAppDeclarationKey(appID)
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		deps.ConnectedAppHook.OnConnectedAppChanged(appID)
	}
}

func writeConnectedAppRecord(ctx context.Context, db connectedAppSQLRunner, record models.SObject, exists bool) error {
	if db == nil {
		return fmt.Errorf("connected_app apply requires database")
	}
	if exists {
		return updateConnectedAppRecordByID(ctx, db, record)
	}
	return insertConnectedAppRecord(ctx, db, record)
}

func insertConnectedAppRecord(ctx context.Context, db connectedAppSQLRunner, record models.SObject) error {
	_, err := db.ExecContext(ctx, `
INSERT INTO _System_ConnectedApp
  (Id, Name, ConsumerKey, ConsumerSecret, ConsumerSecretHash, CallbackUrl, RedirectUris, Scopes, IsActive,
   ClientType, RequirePkce, TokenEndpointAuthMethod, AllowedGrantTypes, ConsentPolicy, ConsentVersion,
   IssueRefreshTokens, PublisherName, SupportUrl, RunAsUserId, PermittedUsers, SessionIssuerClass,
   DailyApiLimit, HourlyApiLimit, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
		record[constants.ColSysConnectedApp_ID],
		record[constants.ColSysConnectedApp_Name],
		record[constants.ColSysConnectedApp_ConsumerKey],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ConsumerSecret]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ConsumerSecretHash]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_CallbackURL]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_RedirectURIs]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_Scopes]),
		record[constants.ColSysConnectedApp_IsActive],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ClientType]),
		record[constants.ColSysConnectedApp_RequirePkce],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_TokenEndpointAuthMethod]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_AllowedGrantTypes]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ConsentPolicy]),
		record[constants.ColSysConnectedApp_ConsentVersion],
		record[constants.ColSysConnectedApp_IssueRefreshTokens],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_PublisherName]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_SupportURL]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_RunAsUserID]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_PermittedUsers]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_SessionIssuerClass]),
		nullableConnectedAppInt(record[constants.ColSysConnectedApp_DailyAPILimit]),
		nullableConnectedAppInt(record[constants.ColSysConnectedApp_HourlyAPILimit]),
	)
	if err != nil {
		return fmt.Errorf("insert _System_ConnectedApp %s: %w", connectedAppComponentString(record[constants.ColSysConnectedApp_ID]), err)
	}
	return nil
}

func updateConnectedAppRecordByID(ctx context.Context, db connectedAppSQLRunner, record models.SObject) error {
	_, err := db.ExecContext(ctx, `
UPDATE _System_ConnectedApp
   SET Name = ?,
       ConsumerKey = ?,
       ConsumerSecret = ?,
       ConsumerSecretHash = ?,
       CallbackUrl = ?,
       RedirectUris = ?,
       Scopes = ?,
       IsActive = ?,
       ClientType = ?,
       RequirePkce = ?,
       TokenEndpointAuthMethod = ?,
       AllowedGrantTypes = ?,
       ConsentPolicy = ?,
       ConsentVersion = ?,
       IssueRefreshTokens = ?,
       PublisherName = ?,
       SupportUrl = ?,
       RunAsUserId = ?,
       PermittedUsers = ?,
       SessionIssuerClass = ?,
       DailyApiLimit = ?,
       HourlyApiLimit = ?,
       LastModifiedDate = NOW()
 WHERE Id = ?`,
		record[constants.ColSysConnectedApp_Name],
		record[constants.ColSysConnectedApp_ConsumerKey],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ConsumerSecret]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ConsumerSecretHash]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_CallbackURL]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_RedirectURIs]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_Scopes]),
		record[constants.ColSysConnectedApp_IsActive],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ClientType]),
		record[constants.ColSysConnectedApp_RequirePkce],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_TokenEndpointAuthMethod]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_AllowedGrantTypes]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_ConsentPolicy]),
		record[constants.ColSysConnectedApp_ConsentVersion],
		record[constants.ColSysConnectedApp_IssueRefreshTokens],
		nullableConnectedAppString(record[constants.ColSysConnectedApp_PublisherName]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_SupportURL]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_RunAsUserID]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_PermittedUsers]),
		nullableConnectedAppString(record[constants.ColSysConnectedApp_SessionIssuerClass]),
		nullableConnectedAppInt(record[constants.ColSysConnectedApp_DailyAPILimit]),
		nullableConnectedAppInt(record[constants.ColSysConnectedApp_HourlyAPILimit]),
		record[constants.ColSysConnectedApp_ID],
	)
	if err != nil {
		return fmt.Errorf("update _System_ConnectedApp %s: %w", connectedAppComponentString(record[constants.ColSysConnectedApp_ID]), err)
	}
	return nil
}

func validateSourceBackedConnectedAppOwnershipClaim(ctx context.Context, db connectedAppSQLRunner, row sourceBackedConnectedAppDeclarationRow, expected sourceBackedConnectedAppProjection, appExists bool, declaredIDs map[string]struct{}) error {
	if db == nil {
		return fmt.Errorf("connected_app ownership claim requires database")
	}
	var existingID string
	var existingPack sql.NullString
	var existingDeleted int
	hasSamePackDeclaration := false
	hasSamePackTombstone := false
	err := db.QueryRowContext(ctx, `
SELECT Id, InstalledPackName, COALESCE(IsDeleted, 0)
  FROM `+sourceBackedConnectedAppDeclarationTable+`
 WHERE ConnectedAppId = ?
 LIMIT 1`, row.ConnectedAppID).Scan(&existingID, &existingPack, &existingDeleted)
	if err == nil {
		gotPack := ""
		if existingPack.Valid {
			gotPack = strings.TrimSpace(existingPack.String)
		}
		if existingID != row.ID || gotPack != row.InstalledPackName {
			return fmt.Errorf("source-backed connected_app %s is already declared by pack %q (row %s), refusing to install for pack %q (row %s)", row.ConnectedAppID, gotPack, existingID, row.InstalledPackName, row.ID)
		}
		if existingDeleted == 0 {
			hasSamePackDeclaration = true
		} else {
			hasSamePackTombstone = true
		}
	} else if err != sql.ErrNoRows {
		return fmt.Errorf("query %s ownership for connected_app %s: %w", sourceBackedConnectedAppDeclarationTable, row.ConnectedAppID, err)
	}
	uniqueKeyClaim, err := validateConnectedAppUniqueKeyClaim(ctx, db, row.InstalledPackName, expected, declaredIDs)
	if err != nil {
		return err
	}
	if appExists && !hasSamePackDeclaration {
		if hasSamePackTombstone && uniqueKeyClaim.sameIDRowExists && !uniqueKeyClaim.sameIDRowActive {
			return nil
		}
		if hasSamePackTombstone {
			return fmt.Errorf("source-backed connected_app %s has a soft-deleted declaration for pack %q but the _System_ConnectedApp row is active, refusing to install", row.ConnectedAppID, row.InstalledPackName)
		}
		return fmt.Errorf("source-backed connected_app %s collides with existing _System_ConnectedApp row without source-backed declaration, refusing to install for pack %q", row.ConnectedAppID, row.InstalledPackName)
	}
	return nil
}

type connectedAppUniqueKeyClaim struct {
	sameIDRowExists     bool
	sameIDRowActive     bool
	ownerlessAdoptionID []string
}

type connectedAppUniqueKeyRow struct {
	id          string
	name        string
	consumerKey string
}

func validateConnectedAppUniqueKeyClaim(ctx context.Context, db connectedAppSQLRunner, packName string, expected sourceBackedConnectedAppProjection, declaredIDs map[string]struct{}) (connectedAppUniqueKeyClaim, error) {
	var claim connectedAppUniqueKeyClaim
	rows, err := db.QueryContext(ctx, `
SELECT Id, Name, ConsumerKey, COALESCE(IsActive, 0)
  FROM `+constants.TableConnectedApp+`
 WHERE Id = ? OR Name = ? OR ConsumerKey = ?
 ORDER BY Id`, expected.ID, expected.Name, expected.ConsumerKey)
	if err != nil {
		return claim, fmt.Errorf("query _System_ConnectedApp unique keys for source-backed connected_app %s: %w", expected.ID, err)
	}
	defer rows.Close()

	var conflicts []connectedAppUniqueKeyRow
	for rows.Next() {
		var id, name, consumerKey string
		var isActive bool
		if err := rows.Scan(&id, &name, &consumerKey, &isActive); err != nil {
			return claim, fmt.Errorf("scan _System_ConnectedApp unique key row for source-backed connected_app %s: %w", expected.ID, err)
		}
		if strings.TrimSpace(id) == expected.ID {
			claim.sameIDRowExists = true
			claim.sameIDRowActive = isActive
			continue
		}
		conflicts = append(conflicts, connectedAppUniqueKeyRow{id: id, name: name, consumerKey: consumerKey})
	}
	if err := rows.Err(); err != nil {
		return claim, fmt.Errorf("iterate _System_ConnectedApp unique key rows for source-backed connected_app %s: %w", expected.ID, err)
	}
	if err := rows.Close(); err != nil {
		return claim, fmt.Errorf("close _System_ConnectedApp unique key rows for source-backed connected_app %s: %w", expected.ID, err)
	}
	for _, conflict := range conflicts {
		id := strings.TrimSpace(conflict.id)
		ownership, err := sourceBackedConnectedAppReplacementOwnership(ctx, db, packName, id, declaredIDs)
		if err != nil {
			return claim, err
		}
		if ownership.samePackReplacement {
			continue
		}
		if ownership.ownerless {
			adoptable, err := sourceBackedConnectedAppOwnerlessAdoptionCandidate(ctx, db, expected, conflict)
			if err != nil {
				return claim, err
			}
			if adoptable {
				claim.ownerlessAdoptionID = append(claim.ownerlessAdoptionID, id)
				continue
			}
		}
		switch {
		case strings.TrimSpace(conflict.name) == expected.Name:
			return claim, fmt.Errorf("source-backed connected_app %s Name %q collides with existing _System_ConnectedApp row %s, refusing to install", expected.ID, expected.Name, id)
		case strings.TrimSpace(conflict.consumerKey) == expected.ConsumerKey:
			return claim, fmt.Errorf("source-backed connected_app %s ConsumerKey %q collides with existing _System_ConnectedApp row %s, refusing to install", expected.ID, expected.ConsumerKey, id)
		default:
			return claim, fmt.Errorf("source-backed connected_app %s collides with existing _System_ConnectedApp row %s, refusing to install", expected.ID, id)
		}
	}
	return claim, nil
}

type sourceBackedConnectedAppReplacementOwnershipResult struct {
	samePackReplacement bool
	ownerless           bool
}

func sourceBackedConnectedAppReplacementOwnership(ctx context.Context, db connectedAppSQLRunner, packName, existingAppID string, declaredIDs map[string]struct{}) (sourceBackedConnectedAppReplacementOwnershipResult, error) {
	var result sourceBackedConnectedAppReplacementOwnershipResult
	existingAppID = strings.TrimSpace(existingAppID)
	if existingAppID == "" {
		return result, nil
	}
	if _, ok := declaredIDs[connectedAppDeclarationKey(existingAppID)]; ok {
		return result, nil
	}
	var owner sql.NullString
	var deleted int
	err := db.QueryRowContext(ctx, `
SELECT InstalledPackName, COALESCE(IsDeleted, 0)
  FROM `+sourceBackedConnectedAppDeclarationTable+`
 WHERE ConnectedAppId = ?
 LIMIT 1`, existingAppID).Scan(&owner, &deleted)
	if err == sql.ErrNoRows {
		result.ownerless = true
		return result, nil
	}
	if err != nil {
		return result, fmt.Errorf("query %s replacement ownership for connected_app %s: %w", sourceBackedConnectedAppDeclarationTable, existingAppID, err)
	}
	if deleted != 0 || !owner.Valid {
		return result, nil
	}
	result.samePackReplacement = strings.TrimSpace(owner.String) == strings.TrimSpace(packName)
	return result, nil
}

func sourceBackedConnectedAppOwnerlessAdoptionCandidate(ctx context.Context, db connectedAppSQLRunner, expected sourceBackedConnectedAppProjection, conflict connectedAppUniqueKeyRow) (bool, error) {
	conflictID := strings.TrimSpace(conflict.id)
	expectedKey := connectedAppOwnershipKeyFromProjection(expected)
	if conflictID == "" || !expectedKey.OwnerlessLegacyAdoptionEligible() {
		return false, nil
	}
	if strings.TrimSpace(conflict.consumerKey) != strings.TrimSpace(expected.ConsumerKey) {
		return false, nil
	}
	candidateProjection, ok, err := loadConnectedAppProjectionIfExists(ctx, db, conflictID, "")
	if err != nil {
		return false, err
	}
	if !ok {
		return false, fmt.Errorf("source-backed connected_app %s ConsumerKey %q matched _System_ConnectedApp row %s but the row disappeared before adoption validation", expected.ID, expected.ConsumerKey, conflictID)
	}
	candidateKey := connectedAppOwnershipKeyFromProjection(candidateProjection)
	if mismatch := expectedKey.OwnerlessLegacyAdoptionMismatch(candidateKey); mismatch != "" {
		return false, fmt.Errorf("source-backed connected_app %s ConsumerKey %q matches ownerless _System_ConnectedApp row %s but row is not safe to adopt: %s", expected.ID, expected.ConsumerKey, conflictID, mismatch)
	}
	return true, nil
}

func ownerlessConnectedAppAdoptionIDs(ctx context.Context, db connectedAppSQLRunner, packName string, expected sourceBackedConnectedAppProjection) ([]string, error) {
	if !connectedAppOwnershipKeyFromProjection(expected).OwnerlessLegacyAdoptionEligible() {
		return nil, nil
	}
	claim, err := validateConnectedAppUniqueKeyClaim(ctx, db, packName, expected, nil)
	if err != nil {
		return nil, err
	}
	out := append([]string(nil), claim.ownerlessAdoptionID...)
	sort.Strings(out)
	return out, nil
}

func loadSourceBackedConnectedAppDeclaration(ctx context.Context, db *sql.DB, packName, connectedAppID string) (sourceBackedConnectedAppDeclarationRow, error) {
	var row sourceBackedConnectedAppDeclarationRow
	if db == nil {
		return row, fmt.Errorf("connected_app declaration projection requires database")
	}
	err := db.QueryRowContext(ctx, `
SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, COALESCE(IsDeleted, 0)
  FROM `+sourceBackedConnectedAppDeclarationTable+`
 WHERE InstalledPackName = ? AND ConnectedAppId = ? AND COALESCE(IsDeleted, 0) = 0
 LIMIT 1`, strings.TrimSpace(packName), strings.TrimSpace(connectedAppID)).Scan(
		&row.ID,
		&row.InstalledPackName,
		&row.ConnectedAppID,
		&row.PayloadHash,
		&row.IsDeleted,
	)
	if err == sql.ErrNoRows {
		return row, fmt.Errorf("%s row for pack %s connected_app %s is missing", sourceBackedConnectedAppDeclarationTable, strings.TrimSpace(packName), strings.TrimSpace(connectedAppID))
	}
	if err != nil {
		return row, fmt.Errorf("query %s for pack %s connected_app %s: %w", sourceBackedConnectedAppDeclarationTable, strings.TrimSpace(packName), strings.TrimSpace(connectedAppID), err)
	}
	return row, nil
}

func postApplyConnectedAppSecurityChange(ctx context.Context, deps ApplyDeps, appID string) error {
	appID = strings.TrimSpace(appID)
	if appID == "" {
		return nil
	}
	if deps.SessionRepo != nil {
		if _, err := deps.SessionRepo.RevokeAllSessionsForConnectedApp(ctx, appID); err != nil {
			return fmt.Errorf("revoke sessions for ConnectedApp %s: %w", appID, err)
		}
	} else {
		if deps.DB == nil {
			return fmt.Errorf("connected_app post-apply session revocation requires session repository or database")
		}
		if _, err := deps.DB.ExecContext(ctx, "UPDATE `"+constants.TableSession+"` SET `"+constants.FieldSysSession_IsRevoked+"` = 1, `"+constants.FieldLastModifiedDate+"` = CURRENT_TIMESTAMP WHERE `"+constants.FieldSysSession_ConnectedAppID+"` = ? AND `"+constants.FieldSysSession_IsRevoked+"` = 0", appID); err != nil {
			return fmt.Errorf("revoke sessions for ConnectedApp %s: %w", appID, err)
		}
	}
	if deps.ConnectedAppHook != nil {
		deps.ConnectedAppHook.OnConnectedAppChanged(appID)
	}
	return nil
}

func revokeConnectedAppSessions(ctx context.Context, db connectedAppSQLRunner, appID string) error {
	appID = strings.TrimSpace(appID)
	if appID == "" {
		return nil
	}
	if db == nil {
		return fmt.Errorf("connected_app session revocation requires database")
	}
	if _, err := db.ExecContext(ctx, "UPDATE `"+constants.TableSession+"` SET `"+constants.FieldSysSession_IsRevoked+"` = 1, `"+constants.FieldLastModifiedDate+"` = CURRENT_TIMESTAMP WHERE `"+constants.FieldSysSession_ConnectedAppID+"` = ? AND `"+constants.FieldSysSession_IsRevoked+"` = 0", appID); err != nil {
		return fmt.Errorf("revoke sessions for ConnectedApp %s: %w", appID, err)
	}
	return nil
}

func connectedAppComponentRedirectsJSON(values []string) (string, error) {
	compact := compactConnectedAppComponentStrings(values)
	if len(compact) == 0 {
		return "", nil
	}
	encoded, err := json.Marshal(compact)
	if err != nil {
		return "", err
	}
	return string(encoded), nil
}

func connectedAppComponentRedirects(value interface{}) ([]string, error) {
	raw := connectedAppComponentString(value)
	if raw == "" {
		return nil, nil
	}
	if strings.HasPrefix(raw, "[") {
		var parsed []string
		if err := json.Unmarshal([]byte(raw), &parsed); err != nil {
			return nil, fmt.Errorf("parse connected_app redirectUris: %w", err)
		}
		return compactConnectedAppComponentStrings(parsed), nil
	}
	return splitConnectedAppComponentRedirectList(raw), nil
}

func splitConnectedAppComponentStringList(raw string) []string {
	return canonicalConnectedAppComponentWordSet(compactConnectedAppComponentStrings(strings.FieldsFunc(raw, func(r rune) bool {
		return r == ',' || r == '\n' || r == '\r' || r == '\t' || r == ' '
	})))
}

func splitConnectedAppComponentRedirectList(raw string) []string {
	return compactConnectedAppComponentStrings(strings.FieldsFunc(raw, func(r rune) bool {
		return r == ',' || r == '\n' || r == '\r' || r == '\t'
	}))
}

func compactConnectedAppComponentStrings(values []string) []string {
	out := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed != "" {
			out = append(out, trimmed)
		}
	}
	return out
}

func canonicalConnectedAppComponentWordSet(values []string) []string {
	seen := map[string]struct{}{}
	out := []string{}
	for _, value := range values {
		for _, part := range splitConnectedAppComponentWords(value) {
			token := strings.ToLower(strings.TrimSpace(part))
			if token == "" {
				continue
			}
			if _, ok := seen[token]; ok {
				continue
			}
			seen[token] = struct{}{}
			out = append(out, token)
		}
	}
	sort.Strings(out)
	return out
}

func splitConnectedAppComponentWords(raw string) []string {
	return strings.FieldsFunc(raw, func(r rune) bool {
		return r == ',' || r == '\n' || r == '\r' || r == '\t' || r == ' '
	})
}

func boolValue(value *bool) bool {
	return value != nil && *value
}

func intValue(value *int) int {
	if value == nil {
		return 0
	}
	return *value
}

func cloneIntPtr(value *int) *int {
	if value == nil {
		return nil
	}
	copied := *value
	return &copied
}

func firstNonEmpty(values ...string) string {
	for _, value := range values {
		if strings.TrimSpace(value) != "" {
			return value
		}
	}
	return ""
}

func connectedAppComponentString(value interface{}) string {
	switch v := value.(type) {
	case nil:
		return ""
	case string:
		return strings.TrimSpace(v)
	case sql.NullString:
		if !v.Valid {
			return ""
		}
		return strings.TrimSpace(v.String)
	default:
		return strings.TrimSpace(fmt.Sprint(v))
	}
}

func connectedAppComponentBool(value interface{}) bool {
	switch v := value.(type) {
	case bool:
		return v
	case int:
		return v != 0
	case int64:
		return v != 0
	case string:
		return strings.EqualFold(strings.TrimSpace(v), "true") || strings.TrimSpace(v) == "1"
	default:
		return false
	}
}

func connectedAppComponentInt(value interface{}) int {
	switch v := value.(type) {
	case int:
		return v
	case int64:
		return int(v)
	case sql.NullInt64:
		if v.Valid {
			return int(v.Int64)
		}
	case float64:
		return int(v)
	}
	return 0
}

func connectedAppComponentIntPtr(value interface{}) *int {
	if value == nil {
		return nil
	}
	switch v := value.(type) {
	case int:
		return cloneIntPtr(&v)
	case int64:
		value := int(v)
		return &value
	case float64:
		value := int(v)
		return &value
	case sql.NullInt64:
		if v.Valid {
			value := int(v.Int64)
			return &value
		}
	}
	return nil
}

func nullableConnectedAppString(value interface{}) interface{} {
	str := connectedAppComponentString(value)
	if str == "" {
		return nil
	}
	return str
}

func nullableConnectedAppInt(value interface{}) interface{} {
	if value == nil {
		return nil
	}
	switch v := value.(type) {
	case int:
		return v
	case int64:
		return v
	case float64:
		return int(v)
	default:
		return nil
	}
}