package crypto

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"math/big"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"

	"github.com/niels/banking-app/backend/internal/domain"
	"github.com/niels/banking-app/backend/internal/integrations/blockchainanalytics"
)

type CustodyScopeDecision struct {
	ID                   string     `json:"id"`
	ProductScope         string     `json:"product_scope"`
	DecisionStatus       string     `json:"decision_status"`
	RealMovementEnabled  bool       `json:"real_movement_enabled"`
	JurisdictionScope    []string   `json:"jurisdiction_scope"`
	Rationale            string     `json:"rationale"`
	DecidedByAdminUserID string     `json:"decided_by_admin_user_id,omitempty"`
	DecidedAt            *time.Time `json:"decided_at,omitempty"`
	EvidenceReference    string     `json:"evidence_reference,omitempty"`
	CreatedAt            time.Time  `json:"created_at"`
	UpdatedAt            time.Time  `json:"updated_at"`
}

type LegalMemo struct {
	ID                    string     `json:"id"`
	Jurisdiction          string     `json:"jurisdiction"`
	Activity              string     `json:"activity"`
	Status                string     `json:"status"`
	LicensingRequired     *bool      `json:"licensing_required,omitempty"`
	RegistrationRequired  *bool      `json:"registration_required,omitempty"`
	CounselName           string     `json:"counsel_name,omitempty"`
	MemoReference         string     `json:"memo_reference,omitempty"`
	Summary               string     `json:"summary"`
	ValidFrom             string     `json:"valid_from,omitempty"`
	ValidUntil            string     `json:"valid_until,omitempty"`
	ApprovedByAdminUserID string     `json:"approved_by_admin_user_id,omitempty"`
	ApprovedAt            *time.Time `json:"approved_at,omitempty"`
	CreatedAt             time.Time  `json:"created_at"`
	UpdatedAt             time.Time  `json:"updated_at"`
}

type CustodyProviderConfig struct {
	ID                    string    `json:"id"`
	Provider              string    `json:"provider"`
	ProviderType          string    `json:"provider_type"`
	Status                string    `json:"status"`
	RealMovementEnabled   bool      `json:"real_movement_enabled"`
	ContractReference     string    `json:"contract_reference,omitempty"`
	APIEnvironment        string    `json:"api_environment"`
	WebhookSigningEnabled bool      `json:"webhook_signing_enabled"`
	KeyManagementModel    string    `json:"key_management_model"`
	Notes                 string    `json:"notes,omitempty"`
	CreatedAt             time.Time `json:"created_at"`
	UpdatedAt             time.Time `json:"updated_at"`
}

type AddressScreening struct {
	ID                  string          `json:"id"`
	UserID              string          `json:"user_id,omitempty"`
	CryptoWalletID      string          `json:"crypto_wallet_id,omitempty"`
	CryptoAssetID       string          `json:"crypto_asset_id,omitempty"`
	Address             string          `json:"address"`
	AddressTag          string          `json:"address_tag,omitempty"`
	Network             string          `json:"network"`
	Context             string          `json:"context"`
	Provider            string          `json:"provider"`
	ExternalScreeningID string          `json:"external_screening_id,omitempty"`
	Decision            string          `json:"decision"`
	RiskScore           int             `json:"risk_score"`
	Category            string          `json:"category"`
	Matched             bool            `json:"matched"`
	Details             json.RawMessage `json:"details"`
	CreatedAt           time.Time       `json:"created_at"`
}

type ChainTransaction struct {
	ID                      string          `json:"id"`
	UserID                  string          `json:"user_id"`
	CryptoWalletID          string          `json:"crypto_wallet_id"`
	CryptoAssetID           string          `json:"crypto_asset_id"`
	Symbol                  string          `json:"symbol,omitempty"`
	AssetType               string          `json:"asset_type,omitempty"`
	CryptoAddressID         string          `json:"crypto_address_id,omitempty"`
	AddressScreeningID      string          `json:"address_screening_id,omitempty"`
	Direction               string          `json:"direction"`
	Status                  string          `json:"status"`
	Network                 string          `json:"network"`
	Address                 string          `json:"address"`
	AddressTag              string          `json:"address_tag,omitempty"`
	TxHash                  string          `json:"tx_hash,omitempty"`
	AmountBaseUnits         string          `json:"amount_base_units"`
	FeeBaseUnits            string          `json:"fee_base_units"`
	Confirmations           int             `json:"confirmations"`
	RequiredConfirmations   int             `json:"required_confirmations"`
	Provider                string          `json:"provider"`
	ProviderReference       string          `json:"provider_reference,omitempty"`
	TravelRuleRequired      bool            `json:"travel_rule_required"`
	TravelRuleStatus        string          `json:"travel_rule_status"`
	FailureReason           string          `json:"failure_reason,omitempty"`
	ReversalOfTransactionID string          `json:"reversal_of_transaction_id,omitempty"`
	Metadata                json.RawMessage `json:"metadata"`
	CreatedByAdminUserID    string          `json:"created_by_admin_user_id,omitempty"`
	CreatedAt               time.Time       `json:"created_at"`
	UpdatedAt               time.Time       `json:"updated_at"`
	ConfirmedAt             *time.Time      `json:"confirmed_at,omitempty"`
	FailedAt                *time.Time      `json:"failed_at,omitempty"`
	ReversedAt              *time.Time      `json:"reversed_at,omitempty"`
}

type TravelRuleTransfer struct {
	ID                        string          `json:"id"`
	CryptoChainTransactionID  string          `json:"crypto_chain_transaction_id"`
	Status                    string          `json:"status"`
	OriginatorName            string          `json:"originator_name,omitempty"`
	OriginatorCountry         string          `json:"originator_country,omitempty"`
	OriginatorWalletProvider  string          `json:"originator_wallet_provider,omitempty"`
	BeneficiaryName           string          `json:"beneficiary_name,omitempty"`
	BeneficiaryCountry        string          `json:"beneficiary_country,omitempty"`
	BeneficiaryWalletProvider string          `json:"beneficiary_wallet_provider,omitempty"`
	BeneficiaryWalletType     string          `json:"beneficiary_wallet_type"`
	Provider                  string          `json:"provider"`
	ExternalReference         string          `json:"external_reference,omitempty"`
	Payload                   json.RawMessage `json:"payload"`
	RejectionReason           string          `json:"rejection_reason,omitempty"`
	CreatedByAdminUserID      string          `json:"created_by_admin_user_id,omitempty"`
	DecidedByAdminUserID      string          `json:"decided_by_admin_user_id,omitempty"`
	DecidedAt                 *time.Time      `json:"decided_at,omitempty"`
	CreatedAt                 time.Time       `json:"created_at"`
	UpdatedAt                 time.Time       `json:"updated_at"`
}

type CustodyResponsibility struct {
	ID                      string     `json:"id"`
	Provider                string     `json:"provider"`
	CustodyModel            string     `json:"custody_model"`
	KeyOwner                string     `json:"key_owner"`
	SigningAuthority        string     `json:"signing_authority"`
	BackupOwner             string     `json:"backup_owner"`
	RecoveryOwner           string     `json:"recovery_owner"`
	HSMOrKMS                string     `json:"hsm_or_kms"`
	PolicyDocumentReference string     `json:"policy_document_reference"`
	Status                  string     `json:"status"`
	ApprovedByAdminUserID   string     `json:"approved_by_admin_user_id,omitempty"`
	ApprovedAt              *time.Time `json:"approved_at,omitempty"`
	CreatedAt               time.Time  `json:"created_at"`
	UpdatedAt               time.Time  `json:"updated_at"`
}

type StablecoinIssuerMonitoring struct {
	ID                    string     `json:"id"`
	CryptoAssetID         string     `json:"crypto_asset_id"`
	Symbol                string     `json:"symbol,omitempty"`
	Network               string     `json:"network,omitempty"`
	IssuerName            string     `json:"issuer_name"`
	PegCurrency           string     `json:"peg_currency"`
	PegStatus             string     `json:"peg_status"`
	RiskRating            string     `json:"risk_rating"`
	ReserveAttestationURL string     `json:"reserve_attestation_url,omitempty"`
	ReserveAttestationAt  *time.Time `json:"reserve_attestation_at,omitempty"`
	NextReviewAt          *time.Time `json:"next_review_at,omitempty"`
	Notes                 string     `json:"notes,omitempty"`
	UpdatedByAdminUserID  string     `json:"updated_by_admin_user_id,omitempty"`
	CreatedAt             time.Time  `json:"created_at"`
	UpdatedAt             time.Time  `json:"updated_at"`
}

type StablecoinNetworkControl struct {
	ID                   string    `json:"id"`
	CryptoAssetID        string    `json:"crypto_asset_id"`
	Symbol               string    `json:"symbol,omitempty"`
	Network              string    `json:"network"`
	DepositsEnabled      bool      `json:"deposits_enabled"`
	WithdrawalsEnabled   bool      `json:"withdrawals_enabled"`
	MinConfirmations     int       `json:"min_confirmations"`
	ContractAddress      string    `json:"contract_address,omitempty"`
	Status               string    `json:"status"`
	Notes                string    `json:"notes,omitempty"`
	UpdatedByAdminUserID string    `json:"updated_by_admin_user_id,omitempty"`
	CreatedAt            time.Time `json:"created_at"`
	UpdatedAt            time.Time `json:"updated_at"`
}

type ChainTransactionParams struct {
	AdminUserID           string
	UserID                string
	CryptoWalletID        string
	CryptoAssetID         string
	CryptoAddressID       string
	AddressScreeningID    string
	Direction             string
	Address               string
	AddressTag            string
	AmountBaseUnits       string
	FeeBaseUnits          string
	Provider              string
	ProviderReference     string
	TxHash                string
	Confirmations         int
	TravelRuleRequired    bool
	Metadata              json.RawMessage
	ScreeningDecision     string
	RequiredConfirmations int
}

type ChainTransactionStatusParams struct {
	AdminUserID       string
	TransactionID     string
	Status            string
	TxHash            string
	Confirmations     int
	FailureReason     string
	ProviderReference string
	Metadata          json.RawMessage
}

type AddressScreeningParams struct {
	UserID         string
	CryptoWalletID string
	CryptoAssetID  string
	Address        string
	AddressTag     string
	Network        string
	Context        string
	Result         blockchainanalytics.ScreeningResult
}

type TravelRuleParams struct {
	AdminUserID               string
	CryptoChainTransactionID  string
	Status                    string
	OriginatorName            string
	OriginatorCountry         string
	OriginatorWalletProvider  string
	BeneficiaryName           string
	BeneficiaryCountry        string
	BeneficiaryWalletProvider string
	BeneficiaryWalletType     string
	Provider                  string
	ExternalReference         string
	Payload                   json.RawMessage
	RejectionReason           string
}

func (r *Repository) ListCustodyScopeDecisions(ctx context.Context, limit int) ([]CustodyScopeDecision, error) {
	limit = normalizeOpsLimit(limit, 100)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, product_scope, decision_status, real_movement_enabled, jurisdiction_scope,
			rationale, COALESCE(decided_by_admin_user_id::text, ''), decided_at,
			COALESCE(evidence_reference, ''), created_at, updated_at
		FROM crypto_custody_scope_decisions
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []CustodyScopeDecision{}
	for rows.Next() {
		item, err := scanCustodyScopeDecision(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) CreateCustodyScopeDecision(ctx context.Context, adminUserID, productScope, status string, realMovement bool, jurisdictions []string, rationale, evidence string) (CustodyScopeDecision, error) {
	productScope = strings.ToLower(strings.TrimSpace(productScope))
	status = strings.ToLower(strings.TrimSpace(status))
	rationale = strings.TrimSpace(rationale)
	evidence = strings.TrimSpace(evidence)
	if adminUserID != "" {
		if err := domain.ValidateUUID("admin_user_id", adminUserID); err != nil {
			return CustodyScopeDecision{}, err
		}
	}
	if productScope != "disabled" && productScope != "simulated_only" && productScope != "real_custody" {
		return CustodyScopeDecision{}, fmt.Errorf("%w: invalid product_scope", domain.ErrValidation)
	}
	if status == "" {
		status = "draft"
	}
	if status != "draft" && status != "approved" && status != "rejected" {
		return CustodyScopeDecision{}, fmt.Errorf("%w: invalid decision_status", domain.ErrValidation)
	}
	if productScope != "real_custody" && realMovement {
		return CustodyScopeDecision{}, fmt.Errorf("%w: real movement requires real_custody product scope", domain.ErrValidation)
	}
	if len(rationale) < 12 || len(rationale) > 2000 {
		return CustodyScopeDecision{}, fmt.Errorf("%w: rationale must be between 12 and 2000 characters", domain.ErrValidation)
	}
	var decidedAt any
	var decidedBy any
	if status == "approved" || status == "rejected" {
		decidedAt = time.Now().UTC()
		decidedBy = adminUserID
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO crypto_custody_scope_decisions (
			product_scope, decision_status, real_movement_enabled, jurisdiction_scope,
			rationale, decided_by_admin_user_id, decided_at, evidence_reference
		)
		VALUES ($1, $2, $3, $4, $5, NULLIF($6, '')::uuid, $7::timestamptz, NULLIF($8, ''))
		RETURNING id::text, product_scope, decision_status, real_movement_enabled, jurisdiction_scope,
			rationale, COALESCE(decided_by_admin_user_id::text, ''), decided_at,
			COALESCE(evidence_reference, ''), created_at, updated_at
	`, productScope, status, realMovement, normalizeStringSlice(jurisdictions), rationale, decidedBy, decidedAt, evidence)
	return scanCustodyScopeDecision(row)
}

func (r *Repository) ListLegalMemos(ctx context.Context, status, jurisdiction string, limit int) ([]LegalMemo, error) {
	status = strings.ToLower(strings.TrimSpace(status))
	jurisdiction = strings.ToUpper(strings.TrimSpace(jurisdiction))
	limit = normalizeOpsLimit(limit, 100)
	rows, err := r.db.Query(ctx, legalMemoSelect()+`
		WHERE ($1 = '' OR status = $1)
			AND ($2 = '' OR jurisdiction = $2)
		ORDER BY updated_at DESC
		LIMIT $3
	`, status, jurisdiction, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []LegalMemo{}
	for rows.Next() {
		item, err := scanLegalMemo(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertLegalMemo(ctx context.Context, params LegalMemo) (LegalMemo, error) {
	params.Jurisdiction = strings.ToUpper(strings.TrimSpace(params.Jurisdiction))
	params.Activity = strings.ToLower(strings.TrimSpace(params.Activity))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.Summary = strings.TrimSpace(params.Summary)
	if params.Jurisdiction == "" || len(params.Jurisdiction) > 32 {
		return LegalMemo{}, fmt.Errorf("%w: jurisdiction is required", domain.ErrValidation)
	}
	if params.Activity == "" || len(params.Activity) > 80 {
		return LegalMemo{}, fmt.Errorf("%w: activity is required", domain.ErrValidation)
	}
	if params.Status == "" {
		params.Status = "draft"
	}
	if params.Status != "draft" && params.Status != "in_review" && params.Status != "approved" && params.Status != "rejected" && params.Status != "expired" {
		return LegalMemo{}, fmt.Errorf("%w: invalid legal memo status", domain.ErrValidation)
	}
	if len(params.Summary) < 12 || len(params.Summary) > 4000 {
		return LegalMemo{}, fmt.Errorf("%w: summary must be between 12 and 4000 characters", domain.ErrValidation)
	}
	validFrom, err := optionalDate(params.ValidFrom)
	if err != nil {
		return LegalMemo{}, err
	}
	validUntil, err := optionalDate(params.ValidUntil)
	if err != nil {
		return LegalMemo{}, err
	}
	var approvedAt any
	var approvedBy any
	if params.Status == "approved" && params.ApprovedByAdminUserID != "" {
		if err := domain.ValidateUUID("approved_by_admin_user_id", params.ApprovedByAdminUserID); err != nil {
			return LegalMemo{}, err
		}
		approvedBy = params.ApprovedByAdminUserID
		approvedAt = time.Now().UTC()
	}
	row := r.db.QueryRow(ctx, `
		WITH upsert AS (
			INSERT INTO crypto_legal_memos (
				jurisdiction, activity, status, licensing_required, registration_required,
				counsel_name, memo_reference, summary, valid_from, valid_until,
				approved_by_admin_user_id, approved_at
			)
			VALUES ($1, $2, $3, $4, $5, NULLIF($6, ''), NULLIF($7, ''), $8, $9, $10,
				$11::uuid, $12::timestamptz)
			ON CONFLICT (jurisdiction, activity) DO UPDATE
			SET status = EXCLUDED.status,
				licensing_required = EXCLUDED.licensing_required,
				registration_required = EXCLUDED.registration_required,
				counsel_name = EXCLUDED.counsel_name,
				memo_reference = EXCLUDED.memo_reference,
				summary = EXCLUDED.summary,
				valid_from = EXCLUDED.valid_from,
				valid_until = EXCLUDED.valid_until,
				approved_by_admin_user_id = EXCLUDED.approved_by_admin_user_id,
				approved_at = EXCLUDED.approved_at
			RETURNING id
		)
		SELECT lm.id::text, lm.jurisdiction, lm.activity, lm.status, lm.licensing_required, lm.registration_required,
			COALESCE(lm.counsel_name, ''), COALESCE(lm.memo_reference, ''), lm.summary,
			COALESCE(lm.valid_from::text, ''), COALESCE(lm.valid_until::text, ''),
			COALESCE(lm.approved_by_admin_user_id::text, ''), lm.approved_at, lm.created_at, lm.updated_at
		FROM crypto_legal_memos lm
		JOIN upsert u ON u.id = lm.id
	`, params.Jurisdiction, params.Activity, params.Status, params.LicensingRequired, params.RegistrationRequired,
		params.CounselName, params.MemoReference, params.Summary, validFrom, validUntil, approvedBy, approvedAt)
	return scanLegalMemo(row)
}

func (r *Repository) ListCustodyProviderConfigs(ctx context.Context) ([]CustodyProviderConfig, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider, provider_type, status, real_movement_enabled,
			COALESCE(contract_reference, ''), api_environment, webhook_signing_enabled,
			key_management_model, notes, created_at, updated_at
		FROM crypto_custody_provider_configs
		ORDER BY provider
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []CustodyProviderConfig{}
	for rows.Next() {
		item, err := scanCustodyProviderConfig(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertCustodyProviderConfig(ctx context.Context, params CustodyProviderConfig) (CustodyProviderConfig, error) {
	if err := normalizeCustodyProviderConfig(&params); err != nil {
		return CustodyProviderConfig{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO crypto_custody_provider_configs (
			provider, provider_type, status, real_movement_enabled, contract_reference,
			api_environment, webhook_signing_enabled, key_management_model, notes
		)
		VALUES ($1, $2, $3, $4, NULLIF($5, ''), $6, $7, $8, $9)
		ON CONFLICT (provider) DO UPDATE
		SET provider_type = EXCLUDED.provider_type,
			status = EXCLUDED.status,
			real_movement_enabled = EXCLUDED.real_movement_enabled,
			contract_reference = EXCLUDED.contract_reference,
			api_environment = EXCLUDED.api_environment,
			webhook_signing_enabled = EXCLUDED.webhook_signing_enabled,
			key_management_model = EXCLUDED.key_management_model,
			notes = EXCLUDED.notes
		RETURNING id::text, provider, provider_type, status, real_movement_enabled,
			COALESCE(contract_reference, ''), api_environment, webhook_signing_enabled,
			key_management_model, notes, created_at, updated_at
	`, params.Provider, params.ProviderType, params.Status, params.RealMovementEnabled,
		params.ContractReference, params.APIEnvironment, params.WebhookSigningEnabled,
		params.KeyManagementModel, params.Notes)
	return scanCustodyProviderConfig(row)
}

func (r *Repository) RecordAddressScreening(ctx context.Context, params AddressScreeningParams) (AddressScreening, error) {
	params.Address = strings.TrimSpace(params.Address)
	params.Network = strings.ToLower(strings.TrimSpace(params.Network))
	params.Context = strings.ToLower(strings.TrimSpace(params.Context))
	if params.Context == "" {
		params.Context = "transaction"
	}
	if params.Address == "" || params.Network == "" {
		return AddressScreening{}, fmt.Errorf("%w: address and network are required", domain.ErrValidation)
	}
	if params.Context != "deposit" && params.Context != "withdrawal" && params.Context != "transaction" && params.Context != "manual_review" {
		return AddressScreening{}, fmt.Errorf("%w: invalid screening context", domain.ErrValidation)
	}
	if params.Result.Decision != "clear" && params.Result.Decision != "review" && params.Result.Decision != "block" {
		return AddressScreening{}, fmt.Errorf("%w: invalid screening decision", domain.ErrValidation)
	}
	if params.Result.RiskScore < 0 || params.Result.RiskScore > 100 {
		return AddressScreening{}, fmt.Errorf("%w: risk_score must be between 0 and 100", domain.ErrValidation)
	}
	details, err := json.Marshal(params.Result.Details)
	if err != nil {
		return AddressScreening{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO crypto_address_screenings (
			user_id, crypto_wallet_id, crypto_asset_id, address, address_tag, network,
			context, provider, external_screening_id, decision, risk_score, category,
			matched, details
		)
		VALUES (
			NULLIF($1, '')::uuid, NULLIF($2, '')::uuid, NULLIF($3, '')::uuid,
			$4, NULLIF($5, ''), $6, $7, $8, NULLIF($9, ''), $10, $11, $12, $13, $14
		)
		RETURNING id::text, COALESCE(user_id::text, ''), COALESCE(crypto_wallet_id::text, ''),
			COALESCE(crypto_asset_id::text, ''), address, COALESCE(address_tag, ''), network,
			context, provider, COALESCE(external_screening_id, ''), decision, risk_score,
			category, matched, details, created_at
	`, params.UserID, params.CryptoWalletID, params.CryptoAssetID, params.Address, params.AddressTag,
		params.Network, params.Context, params.Result.Provider, params.Result.ExternalScreeningID,
		params.Result.Decision, params.Result.RiskScore, params.Result.Category, params.Result.Matched, details)
	return scanAddressScreening(row)
}

func (r *Repository) ListAddressScreenings(ctx context.Context, decision string, limit int) ([]AddressScreening, error) {
	decision = strings.ToLower(strings.TrimSpace(decision))
	limit = normalizeOpsLimit(limit, 100)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, COALESCE(user_id::text, ''), COALESCE(crypto_wallet_id::text, ''),
			COALESCE(crypto_asset_id::text, ''), address, COALESCE(address_tag, ''), network,
			context, provider, COALESCE(external_screening_id, ''), decision, risk_score,
			category, matched, details, created_at
		FROM crypto_address_screenings
		WHERE ($1 = '' OR decision = $1)
		ORDER BY created_at DESC
		LIMIT $2
	`, decision, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []AddressScreening{}
	for rows.Next() {
		item, err := scanAddressScreening(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) CreateChainTransaction(ctx context.Context, params ChainTransactionParams) (ChainTransaction, error) {
	if err := normalizeChainTransactionParams(&params); err != nil {
		return ChainTransaction{}, err
	}
	amount, err := parseBaseUnits(params.AmountBaseUnits)
	if err != nil {
		return ChainTransaction{}, err
	}
	fee, err := parseBaseUnitsAllowZero(params.FeeBaseUnits)
	if err != nil {
		return ChainTransaction{}, err
	}
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
	if err != nil {
		return ChainTransaction{}, err
	}
	defer tx.Rollback(ctx)

	asset, err := r.findAssetForTransaction(ctx, tx, params.CryptoAssetID)
	if err != nil {
		return ChainTransaction{}, err
	}
	if err := r.ensureStablecoinNetworkAllowed(ctx, tx, params.CryptoAssetID, params.Direction); err != nil {
		return ChainTransaction{}, err
	}
	wallet, err := r.findWalletForTransaction(ctx, tx, params.UserID, params.CryptoWalletID)
	if err != nil {
		return ChainTransaction{}, err
	}
	if wallet.Status != "active" {
		return ChainTransaction{}, fmt.Errorf("%w: crypto wallet must be active", domain.ErrValidation)
	}

	status := "pending"
	failureReason := ""
	if params.ScreeningDecision == "block" {
		status = "failed"
		failureReason = "address_screening_block"
	}
	if params.Direction == "withdrawal" && status == "pending" {
		if err := reserveWithdrawalBalance(ctx, tx, params.CryptoWalletID, params.CryptoAssetID, amount, fee); err != nil {
			return ChainTransaction{}, err
		}
	}
	travelStatus := "not_required"
	if params.TravelRuleRequired {
		travelStatus = "pending"
	}
	requiredConfirmations := params.RequiredConfirmations
	if requiredConfirmations <= 0 {
		requiredConfirmations = 1
	}
	row := tx.QueryRow(ctx, `
		WITH inserted AS (
			INSERT INTO crypto_chain_transactions (
				user_id, crypto_wallet_id, crypto_asset_id, crypto_address_id, address_screening_id,
				direction, status, network, address, address_tag, tx_hash, amount_base_units,
				fee_base_units, confirmations, required_confirmations, provider, provider_reference,
				travel_rule_required, travel_rule_status, failure_reason, metadata, created_by_admin_user_id
			)
			VALUES (
				$1, $2, $3, NULLIF($4, '')::uuid, NULLIF($5, '')::uuid, $6, $7, $8, $9,
				NULLIF($10, ''), NULLIF($11, ''), $12::numeric, $13::numeric, $14, $15,
				$16, NULLIF($17, ''), $18, $19, NULLIF($20, ''), $21, NULLIF($22, '')::uuid
			)
			RETURNING id
		)
		`+chainTransactionSelect()+`
		JOIN inserted i ON i.id = ct.id
	`, params.UserID, params.CryptoWalletID, params.CryptoAssetID, params.CryptoAddressID,
		params.AddressScreeningID, params.Direction, status, asset.Network, params.Address,
		params.AddressTag, params.TxHash, amount.String(), fee.String(), params.Confirmations,
		requiredConfirmations, params.Provider, params.ProviderReference, params.TravelRuleRequired,
		travelStatus, failureReason, jsonOrEmpty(params.Metadata), params.AdminUserID)
	chainTx, err := scanChainTransaction(row)
	if err != nil {
		return ChainTransaction{}, err
	}
	if err := insertChainEvent(ctx, tx, chainTx.ID, "created", "", chainTx.Status, chainTx.Provider, chainTx.ProviderReference, params.AdminUserID, map[string]any{
		"direction": chainTx.Direction,
		"symbol":    asset.Symbol,
	}); err != nil {
		return ChainTransaction{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return ChainTransaction{}, err
	}
	return chainTx, nil
}

func (r *Repository) UpdateChainTransactionStatus(ctx context.Context, params ChainTransactionStatusParams) (ChainTransaction, error) {
	if err := normalizeChainStatusParams(&params); err != nil {
		return ChainTransaction{}, err
	}
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
	if err != nil {
		return ChainTransaction{}, err
	}
	defer tx.Rollback(ctx)

	current, err := lockChainTransaction(ctx, tx, params.TransactionID)
	if err != nil {
		return ChainTransaction{}, err
	}
	if current.Status == params.Status {
		if err := tx.Commit(ctx); err != nil {
			return ChainTransaction{}, err
		}
		return current, nil
	}
	if !validChainStatusTransition(current.Status, params.Status) {
		return ChainTransaction{}, fmt.Errorf("%w: invalid crypto transaction status transition", domain.ErrValidation)
	}
	if params.Status == "confirmed" && current.TravelRuleRequired && current.TravelRuleStatus != "accepted" && current.TravelRuleStatus != "not_required" {
		return ChainTransaction{}, fmt.Errorf("%w: travel rule record must be accepted before confirmation", domain.ErrValidation)
	}
	amount, _ := parseBaseUnits(current.AmountBaseUnits)
	fee, _ := parseBaseUnitsAllowZero(current.FeeBaseUnits)
	switch params.Status {
	case "confirmed":
		if current.Direction == "deposit" {
			if err := creditCryptoBalance(ctx, tx, current.CryptoWalletID, current.CryptoAssetID, amount); err != nil {
				return ChainTransaction{}, err
			}
		} else {
			if err := settleWithdrawalReserve(ctx, tx, current.CryptoWalletID, current.CryptoAssetID, amount, fee); err != nil {
				return ChainTransaction{}, err
			}
		}
	case "failed":
		if current.Direction == "withdrawal" {
			if err := releaseWithdrawalReserve(ctx, tx, current.CryptoWalletID, current.CryptoAssetID, amount, fee); err != nil {
				return ChainTransaction{}, err
			}
		}
	case "reversed":
		if current.Direction == "deposit" {
			if err := debitCryptoBalance(ctx, tx, current.CryptoWalletID, current.CryptoAssetID, amount); err != nil {
				return ChainTransaction{}, err
			}
		} else {
			if err := creditCryptoBalance(ctx, tx, current.CryptoWalletID, current.CryptoAssetID, amount); err != nil {
				return ChainTransaction{}, err
			}
		}
	}
	row := tx.QueryRow(ctx, `
		WITH updated AS (
			UPDATE crypto_chain_transactions
			SET status = $2,
				tx_hash = COALESCE(NULLIF($3, ''), tx_hash),
				confirmations = GREATEST(confirmations, $4),
				provider_reference = COALESCE(NULLIF($5, ''), provider_reference),
				failure_reason = CASE WHEN $2 = 'failed' THEN NULLIF($6, '') ELSE failure_reason END,
				confirmed_at = CASE WHEN $2 = 'confirmed' THEN now() ELSE confirmed_at END,
				failed_at = CASE WHEN $2 = 'failed' THEN now() ELSE failed_at END,
				reversed_at = CASE WHEN $2 = 'reversed' THEN now() ELSE reversed_at END,
				metadata = CASE WHEN $7::jsonb = '{}'::jsonb THEN metadata ELSE metadata || $7::jsonb END
			WHERE id = $1
			RETURNING id
		)
		`+chainTransactionSelect()+`
		JOIN updated u ON u.id = ct.id
	`, current.ID, params.Status, params.TxHash, params.Confirmations, params.ProviderReference,
		params.FailureReason, jsonOrEmpty(params.Metadata))
	updated, err := scanChainTransaction(row)
	if err != nil {
		return ChainTransaction{}, err
	}
	if err := insertChainEvent(ctx, tx, updated.ID, "status_changed", current.Status, updated.Status, updated.Provider, updated.ProviderReference, params.AdminUserID, map[string]any{
		"tx_hash":        updated.TxHash,
		"confirmations":  updated.Confirmations,
		"failure_reason": updated.FailureReason,
	}); err != nil {
		return ChainTransaction{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return ChainTransaction{}, err
	}
	return updated, nil
}

func (r *Repository) ListChainTransactions(ctx context.Context, userID, status string, limit int) ([]ChainTransaction, error) {
	status = strings.ToLower(strings.TrimSpace(status))
	limit = normalizeOpsLimit(limit, 100)
	rows, err := r.db.Query(ctx, chainTransactionSelect()+`
		WHERE ($1 = '' OR ct.user_id = $1::uuid)
			AND ($2 = '' OR ct.status = $2)
		ORDER BY ct.created_at DESC
		LIMIT $3
	`, userID, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []ChainTransaction{}
	for rows.Next() {
		item, err := scanChainTransaction(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertTravelRuleTransfer(ctx context.Context, params TravelRuleParams) (TravelRuleTransfer, error) {
	if err := normalizeTravelRuleParams(&params); err != nil {
		return TravelRuleTransfer{}, err
	}
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return TravelRuleTransfer{}, err
	}
	defer tx.Rollback(ctx)

	var decidedBy any
	var decidedAt any
	if params.Status == "accepted" || params.Status == "rejected" || params.Status == "not_required" || params.Status == "expired" {
		decidedBy = params.AdminUserID
		decidedAt = time.Now().UTC()
	}
	row := tx.QueryRow(ctx, `
		WITH upsert AS (
			INSERT INTO crypto_travel_rule_transfers (
				crypto_chain_transaction_id, status, originator_name, originator_country,
				originator_wallet_provider, beneficiary_name, beneficiary_country,
				beneficiary_wallet_provider, beneficiary_wallet_type, provider, external_reference,
				payload, rejection_reason, created_by_admin_user_id, decided_by_admin_user_id, decided_at
			)
			VALUES (
				$1, $2, NULLIF($3, ''), NULLIF($4, '')::char(2), NULLIF($5, ''),
				NULLIF($6, ''), NULLIF($7, '')::char(2), NULLIF($8, ''), $9, $10,
				NULLIF($11, ''), $12, NULLIF($13, ''), NULLIF($14, '')::uuid,
				NULLIF($15, '')::uuid, $16::timestamptz
			)
			ON CONFLICT (crypto_chain_transaction_id) DO UPDATE
			SET status = EXCLUDED.status,
				originator_name = EXCLUDED.originator_name,
				originator_country = EXCLUDED.originator_country,
				originator_wallet_provider = EXCLUDED.originator_wallet_provider,
				beneficiary_name = EXCLUDED.beneficiary_name,
				beneficiary_country = EXCLUDED.beneficiary_country,
				beneficiary_wallet_provider = EXCLUDED.beneficiary_wallet_provider,
				beneficiary_wallet_type = EXCLUDED.beneficiary_wallet_type,
				provider = EXCLUDED.provider,
				external_reference = EXCLUDED.external_reference,
				payload = EXCLUDED.payload,
				rejection_reason = EXCLUDED.rejection_reason,
				decided_by_admin_user_id = EXCLUDED.decided_by_admin_user_id,
				decided_at = EXCLUDED.decided_at
			RETURNING id
		)
		`+travelRuleSelect()+`
		JOIN upsert u ON u.id = tr.id
	`, params.CryptoChainTransactionID, params.Status, params.OriginatorName, params.OriginatorCountry,
		params.OriginatorWalletProvider, params.BeneficiaryName, params.BeneficiaryCountry,
		params.BeneficiaryWalletProvider, params.BeneficiaryWalletType, params.Provider,
		params.ExternalReference, jsonOrEmpty(params.Payload), params.RejectionReason, params.AdminUserID,
		decidedBy, decidedAt)
	item, err := scanTravelRuleTransfer(row)
	if err != nil {
		return TravelRuleTransfer{}, err
	}
	if _, err := tx.Exec(ctx, `
		UPDATE crypto_chain_transactions
		SET travel_rule_required = CASE WHEN $2 = 'not_required' THEN false ELSE true END,
			travel_rule_status = $2
		WHERE id = $1
	`, params.CryptoChainTransactionID, params.Status); err != nil {
		return TravelRuleTransfer{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return TravelRuleTransfer{}, err
	}
	return item, nil
}

func (r *Repository) ListTravelRuleTransfers(ctx context.Context, status string, limit int) ([]TravelRuleTransfer, error) {
	status = strings.ToLower(strings.TrimSpace(status))
	limit = normalizeOpsLimit(limit, 100)
	rows, err := r.db.Query(ctx, travelRuleSelect()+`
		WHERE ($1 = '' OR tr.status = $1)
		ORDER BY tr.created_at DESC
		LIMIT $2
	`, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []TravelRuleTransfer{}
	for rows.Next() {
		item, err := scanTravelRuleTransfer(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) ListCustodyResponsibilities(ctx context.Context) ([]CustodyResponsibility, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider, custody_model, key_owner, signing_authority, backup_owner,
			recovery_owner, hsm_or_kms, policy_document_reference, status,
			COALESCE(approved_by_admin_user_id::text, ''), approved_at, created_at, updated_at
		FROM crypto_custody_responsibility_matrix
		ORDER BY provider, custody_model
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []CustodyResponsibility{}
	for rows.Next() {
		item, err := scanCustodyResponsibility(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertCustodyResponsibility(ctx context.Context, params CustodyResponsibility) (CustodyResponsibility, error) {
	if err := normalizeCustodyResponsibility(&params); err != nil {
		return CustodyResponsibility{}, err
	}
	var approvedBy any
	var approvedAt any
	if params.Status == "approved" {
		if params.ApprovedByAdminUserID == "" {
			return CustodyResponsibility{}, fmt.Errorf("%w: approved_by_admin_user_id is required", domain.ErrValidation)
		}
		approvedBy = params.ApprovedByAdminUserID
		approvedAt = time.Now().UTC()
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO crypto_custody_responsibility_matrix (
			provider, custody_model, key_owner, signing_authority, backup_owner,
			recovery_owner, hsm_or_kms, policy_document_reference, status,
			approved_by_admin_user_id, approved_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::uuid, $11::timestamptz)
		ON CONFLICT (provider, custody_model) DO UPDATE
		SET key_owner = EXCLUDED.key_owner,
			signing_authority = EXCLUDED.signing_authority,
			backup_owner = EXCLUDED.backup_owner,
			recovery_owner = EXCLUDED.recovery_owner,
			hsm_or_kms = EXCLUDED.hsm_or_kms,
			policy_document_reference = EXCLUDED.policy_document_reference,
			status = EXCLUDED.status,
			approved_by_admin_user_id = EXCLUDED.approved_by_admin_user_id,
			approved_at = EXCLUDED.approved_at
		RETURNING id::text, provider, custody_model, key_owner, signing_authority, backup_owner,
			recovery_owner, hsm_or_kms, policy_document_reference, status,
			COALESCE(approved_by_admin_user_id::text, ''), approved_at, created_at, updated_at
	`, params.Provider, params.CustodyModel, params.KeyOwner, params.SigningAuthority, params.BackupOwner,
		params.RecoveryOwner, params.HSMOrKMS, params.PolicyDocumentReference, params.Status, approvedBy, approvedAt)
	return scanCustodyResponsibility(row)
}

func (r *Repository) ListStablecoinIssuerMonitoring(ctx context.Context) ([]StablecoinIssuerMonitoring, error) {
	rows, err := r.db.Query(ctx, stablecoinIssuerSelect()+` ORDER BY sim.risk_rating DESC, ca.symbol`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []StablecoinIssuerMonitoring{}
	for rows.Next() {
		item, err := scanStablecoinIssuerMonitoring(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertStablecoinIssuerMonitoring(ctx context.Context, params StablecoinIssuerMonitoring) (StablecoinIssuerMonitoring, error) {
	if err := normalizeStablecoinIssuer(&params); err != nil {
		return StablecoinIssuerMonitoring{}, err
	}
	row := r.db.QueryRow(ctx, `
		WITH upsert AS (
			INSERT INTO stablecoin_issuer_monitoring (
				crypto_asset_id, issuer_name, peg_currency, peg_status, risk_rating,
				reserve_attestation_url, reserve_attestation_at, next_review_at, notes,
				updated_by_admin_user_id
			)
			VALUES ($1, $2, $3, $4, $5, NULLIF($6, ''), $7::timestamptz, $8::timestamptz, $9, NULLIF($10, '')::uuid)
			ON CONFLICT (crypto_asset_id) DO UPDATE
			SET issuer_name = EXCLUDED.issuer_name,
				peg_currency = EXCLUDED.peg_currency,
				peg_status = EXCLUDED.peg_status,
				risk_rating = EXCLUDED.risk_rating,
				reserve_attestation_url = EXCLUDED.reserve_attestation_url,
				reserve_attestation_at = EXCLUDED.reserve_attestation_at,
				next_review_at = EXCLUDED.next_review_at,
				notes = EXCLUDED.notes,
				updated_by_admin_user_id = EXCLUDED.updated_by_admin_user_id
			RETURNING id
		)
		`+stablecoinIssuerSelect()+`
		JOIN upsert u ON u.id = sim.id
	`, params.CryptoAssetID, params.IssuerName, params.PegCurrency, params.PegStatus, params.RiskRating,
		params.ReserveAttestationURL, params.ReserveAttestationAt, params.NextReviewAt, params.Notes,
		params.UpdatedByAdminUserID)
	return scanStablecoinIssuerMonitoring(row)
}

func (r *Repository) ListStablecoinNetworkControls(ctx context.Context) ([]StablecoinNetworkControl, error) {
	rows, err := r.db.Query(ctx, stablecoinNetworkControlSelect()+` ORDER BY ca.symbol, snc.network`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []StablecoinNetworkControl{}
	for rows.Next() {
		item, err := scanStablecoinNetworkControl(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertStablecoinNetworkControl(ctx context.Context, params StablecoinNetworkControl) (StablecoinNetworkControl, error) {
	if err := normalizeStablecoinNetworkControl(&params); err != nil {
		return StablecoinNetworkControl{}, err
	}
	row := r.db.QueryRow(ctx, `
		WITH upsert AS (
			INSERT INTO stablecoin_network_controls (
				crypto_asset_id, network, deposits_enabled, withdrawals_enabled, min_confirmations,
				contract_address, status, notes, updated_by_admin_user_id
			)
			VALUES ($1, $2, $3, $4, $5, NULLIF($6, ''), $7, $8, NULLIF($9, '')::uuid)
			ON CONFLICT (crypto_asset_id, network) DO UPDATE
			SET deposits_enabled = EXCLUDED.deposits_enabled,
				withdrawals_enabled = EXCLUDED.withdrawals_enabled,
				min_confirmations = EXCLUDED.min_confirmations,
				contract_address = EXCLUDED.contract_address,
				status = EXCLUDED.status,
				notes = EXCLUDED.notes,
				updated_by_admin_user_id = EXCLUDED.updated_by_admin_user_id
			RETURNING id
		)
		`+stablecoinNetworkControlSelect()+`
		JOIN upsert u ON u.id = snc.id
	`, params.CryptoAssetID, params.Network, params.DepositsEnabled, params.WithdrawalsEnabled,
		params.MinConfirmations, params.ContractAddress, params.Status, params.Notes, params.UpdatedByAdminUserID)
	return scanStablecoinNetworkControl(row)
}

func (r *Repository) ensureStablecoinNetworkAllowed(ctx context.Context, tx pgx.Tx, assetID, direction string) error {
	var assetType, network string
	err := tx.QueryRow(ctx, `SELECT asset_type, network FROM crypto_assets WHERE id = $1`, assetID).Scan(&assetType, &network)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.ErrNotFound
	}
	if err != nil || assetType != "stablecoin" {
		return err
	}
	var depositsEnabled, withdrawalsEnabled bool
	var status string
	err = tx.QueryRow(ctx, `
		SELECT deposits_enabled, withdrawals_enabled, status
		FROM stablecoin_network_controls
		WHERE crypto_asset_id = $1 AND network = $2
	`, assetID, network).Scan(&depositsEnabled, &withdrawalsEnabled, &status)
	if errors.Is(err, pgx.ErrNoRows) {
		return fmt.Errorf("%w: stablecoin network control is required", domain.ErrValidation)
	}
	if err != nil {
		return err
	}
	if status != "active" {
		return fmt.Errorf("%w: stablecoin network is not active", domain.ErrValidation)
	}
	if direction == "deposit" && !depositsEnabled {
		return fmt.Errorf("%w: stablecoin deposits are disabled for network", domain.ErrValidation)
	}
	if direction == "withdrawal" && !withdrawalsEnabled {
		return fmt.Errorf("%w: stablecoin withdrawals are disabled for network", domain.ErrValidation)
	}
	return nil
}

func (r *Repository) EnsureStablecoinOperationAllowed(ctx context.Context, assetID, direction string) error {
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)
	if err := r.ensureStablecoinNetworkAllowed(ctx, tx, assetID, direction); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

func (r *Repository) findAssetForTransaction(ctx context.Context, tx pgx.Tx, assetID string) (domain.CryptoAsset, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, symbol, name, network, asset_type, COALESCE(contract_address, ''), decimals::int, enabled, created_at
		FROM crypto_assets
		WHERE id = $1 AND enabled = true
	`, assetID)
	asset, err := scanAsset(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.CryptoAsset{}, domain.ErrNotFound
	}
	return asset, err
}

func (r *Repository) findWalletForTransaction(ctx context.Context, tx pgx.Tx, userID, walletID string) (domain.CryptoWallet, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, user_id::text, name, custody_provider, external_wallet_id, status, created_at, updated_at
		FROM crypto_wallets
		WHERE id = $1 AND user_id = $2
		FOR UPDATE
	`, walletID, userID)
	wallet, err := scanWallet(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.CryptoWallet{}, domain.ErrNotFound
	}
	return wallet, err
}

func reserveWithdrawalBalance(ctx context.Context, tx pgx.Tx, walletID, assetID string, amount, fee *big.Int) error {
	total := new(big.Int).Add(amount, fee)
	row := tx.QueryRow(ctx, `
		SELECT available_amount_base_units::text, reserved_amount_base_units::text
		FROM crypto_wallet_balances
		WHERE crypto_wallet_id = $1 AND crypto_asset_id = $2
		FOR UPDATE
	`, walletID, assetID)
	var availableRaw, reservedRaw string
	if err := row.Scan(&availableRaw, &reservedRaw); errors.Is(err, pgx.ErrNoRows) {
		return domain.ErrInsufficientFunds
	} else if err != nil {
		return err
	}
	available, _ := parseBaseUnitsAllowZero(availableRaw)
	if available.Cmp(total) < 0 {
		return domain.ErrInsufficientFunds
	}
	_, err := tx.Exec(ctx, `
		UPDATE crypto_wallet_balances
		SET available_amount_base_units = available_amount_base_units - $3::numeric,
			reserved_amount_base_units = reserved_amount_base_units + $3::numeric
		WHERE crypto_wallet_id = $1 AND crypto_asset_id = $2
	`, walletID, assetID, total.String())
	return err
}

func settleWithdrawalReserve(ctx context.Context, tx pgx.Tx, walletID, assetID string, amount, fee *big.Int) error {
	total := new(big.Int).Add(amount, fee)
	tag, err := tx.Exec(ctx, `
		UPDATE crypto_wallet_balances
		SET reserved_amount_base_units = reserved_amount_base_units - $3::numeric
		WHERE crypto_wallet_id = $1 AND crypto_asset_id = $2
			AND reserved_amount_base_units >= $3::numeric
	`, walletID, assetID, total.String())
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return domain.ErrInsufficientFunds
	}
	return nil
}

func releaseWithdrawalReserve(ctx context.Context, tx pgx.Tx, walletID, assetID string, amount, fee *big.Int) error {
	total := new(big.Int).Add(amount, fee)
	tag, err := tx.Exec(ctx, `
		UPDATE crypto_wallet_balances
		SET reserved_amount_base_units = reserved_amount_base_units - $3::numeric,
			available_amount_base_units = available_amount_base_units + $3::numeric
		WHERE crypto_wallet_id = $1 AND crypto_asset_id = $2
			AND reserved_amount_base_units >= $3::numeric
	`, walletID, assetID, total.String())
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return domain.ErrInsufficientFunds
	}
	return nil
}

func creditCryptoBalance(ctx context.Context, tx pgx.Tx, walletID, assetID string, amount *big.Int) error {
	_, err := tx.Exec(ctx, `
		INSERT INTO crypto_wallet_balances (crypto_wallet_id, crypto_asset_id, available_amount_base_units)
		VALUES ($1, $2, $3::numeric)
		ON CONFLICT (crypto_wallet_id, crypto_asset_id) DO UPDATE
		SET available_amount_base_units = crypto_wallet_balances.available_amount_base_units + EXCLUDED.available_amount_base_units
	`, walletID, assetID, amount.String())
	return err
}

func debitCryptoBalance(ctx context.Context, tx pgx.Tx, walletID, assetID string, amount *big.Int) error {
	tag, err := tx.Exec(ctx, `
		UPDATE crypto_wallet_balances
		SET available_amount_base_units = available_amount_base_units - $3::numeric
		WHERE crypto_wallet_id = $1 AND crypto_asset_id = $2
			AND available_amount_base_units >= $3::numeric
	`, walletID, assetID, amount.String())
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return domain.ErrInsufficientFunds
	}
	return nil
}

func lockChainTransaction(ctx context.Context, tx pgx.Tx, transactionID string) (ChainTransaction, error) {
	row := tx.QueryRow(ctx, chainTransactionSelect()+`
		WHERE ct.id = $1
		FOR UPDATE OF ct
	`, transactionID)
	item, err := scanChainTransaction(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return ChainTransaction{}, domain.ErrNotFound
	}
	return item, err
}

func insertChainEvent(ctx context.Context, tx pgx.Tx, transactionID, eventType, before, after, provider, providerRef, adminID string, details map[string]any) error {
	raw, err := json.Marshal(details)
	if err != nil {
		return err
	}
	_, err = tx.Exec(ctx, `
		INSERT INTO crypto_chain_transaction_events (
			crypto_chain_transaction_id, event_type, status_before, status_after,
			provider, provider_reference, details, actor_admin_user_id
		)
		VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, ''), NULLIF($5, ''), NULLIF($6, ''), $7, NULLIF($8, '')::uuid)
	`, transactionID, eventType, before, after, provider, providerRef, raw, adminID)
	return err
}

func chainTransactionSelect() string {
	return `
		SELECT ct.id::text, ct.user_id::text, ct.crypto_wallet_id::text, ct.crypto_asset_id::text,
			ca.symbol, ca.asset_type, COALESCE(ct.crypto_address_id::text, ''),
			COALESCE(ct.address_screening_id::text, ''), ct.direction, ct.status, ct.network,
			ct.address, COALESCE(ct.address_tag, ''), COALESCE(ct.tx_hash, ''),
			ct.amount_base_units::text, ct.fee_base_units::text, ct.confirmations,
			ct.required_confirmations, ct.provider, COALESCE(ct.provider_reference, ''),
			ct.travel_rule_required, ct.travel_rule_status, COALESCE(ct.failure_reason, ''),
			COALESCE(ct.reversal_of_transaction_id::text, ''), ct.metadata,
			COALESCE(ct.created_by_admin_user_id::text, ''), ct.created_at, ct.updated_at,
			ct.confirmed_at, ct.failed_at, ct.reversed_at
		FROM crypto_chain_transactions ct
		JOIN crypto_assets ca ON ca.id = ct.crypto_asset_id
	`
}

func travelRuleSelect() string {
	return `
		SELECT tr.id::text, tr.crypto_chain_transaction_id::text, tr.status,
			COALESCE(tr.originator_name, ''), COALESCE(tr.originator_country::text, ''),
			COALESCE(tr.originator_wallet_provider, ''), COALESCE(tr.beneficiary_name, ''),
			COALESCE(tr.beneficiary_country::text, ''), COALESCE(tr.beneficiary_wallet_provider, ''),
			tr.beneficiary_wallet_type, tr.provider, COALESCE(tr.external_reference, ''),
			tr.payload, COALESCE(tr.rejection_reason, ''), COALESCE(tr.created_by_admin_user_id::text, ''),
			COALESCE(tr.decided_by_admin_user_id::text, ''), tr.decided_at, tr.created_at, tr.updated_at
		FROM crypto_travel_rule_transfers tr
	`
}

func legalMemoSelect() string {
	return `
		SELECT id::text, jurisdiction, activity, status, licensing_required, registration_required,
			COALESCE(counsel_name, ''), COALESCE(memo_reference, ''), summary,
			COALESCE(valid_from::text, ''), COALESCE(valid_until::text, ''),
			COALESCE(approved_by_admin_user_id::text, ''), approved_at, created_at, updated_at
		FROM crypto_legal_memos
	`
}

func stablecoinIssuerSelect() string {
	return `
		SELECT sim.id::text, sim.crypto_asset_id::text, ca.symbol, ca.network, sim.issuer_name,
			sim.peg_currency, sim.peg_status, sim.risk_rating, COALESCE(sim.reserve_attestation_url, ''),
			sim.reserve_attestation_at, sim.next_review_at, sim.notes,
			COALESCE(sim.updated_by_admin_user_id::text, ''), sim.created_at, sim.updated_at
		FROM stablecoin_issuer_monitoring sim
		JOIN crypto_assets ca ON ca.id = sim.crypto_asset_id
	`
}

func stablecoinNetworkControlSelect() string {
	return `
		SELECT snc.id::text, snc.crypto_asset_id::text, ca.symbol, snc.network,
			snc.deposits_enabled, snc.withdrawals_enabled, snc.min_confirmations,
			COALESCE(snc.contract_address, ''), snc.status, snc.notes,
			COALESCE(snc.updated_by_admin_user_id::text, ''), snc.created_at, snc.updated_at
		FROM stablecoin_network_controls snc
		JOIN crypto_assets ca ON ca.id = snc.crypto_asset_id
	`
}

func scanCustodyScopeDecision(row scanner) (CustodyScopeDecision, error) {
	var item CustodyScopeDecision
	var decidedAt sql.NullTime
	err := row.Scan(&item.ID, &item.ProductScope, &item.DecisionStatus, &item.RealMovementEnabled,
		&item.JurisdictionScope, &item.Rationale, &item.DecidedByAdminUserID, &decidedAt,
		&item.EvidenceReference, &item.CreatedAt, &item.UpdatedAt)
	if decidedAt.Valid {
		item.DecidedAt = &decidedAt.Time
	}
	return item, err
}

func scanLegalMemo(row scanner) (LegalMemo, error) {
	var item LegalMemo
	var approvedAt sql.NullTime
	var licensingRequired, registrationRequired sql.NullBool
	err := row.Scan(&item.ID, &item.Jurisdiction, &item.Activity, &item.Status,
		&licensingRequired, &registrationRequired, &item.CounselName,
		&item.MemoReference, &item.Summary, &item.ValidFrom, &item.ValidUntil,
		&item.ApprovedByAdminUserID, &approvedAt, &item.CreatedAt, &item.UpdatedAt)
	if licensingRequired.Valid {
		value := licensingRequired.Bool
		item.LicensingRequired = &value
	}
	if registrationRequired.Valid {
		value := registrationRequired.Bool
		item.RegistrationRequired = &value
	}
	if approvedAt.Valid {
		item.ApprovedAt = &approvedAt.Time
	}
	return item, err
}

func scanCustodyProviderConfig(row scanner) (CustodyProviderConfig, error) {
	var item CustodyProviderConfig
	err := row.Scan(&item.ID, &item.Provider, &item.ProviderType, &item.Status, &item.RealMovementEnabled,
		&item.ContractReference, &item.APIEnvironment, &item.WebhookSigningEnabled,
		&item.KeyManagementModel, &item.Notes, &item.CreatedAt, &item.UpdatedAt)
	return item, err
}

func scanAddressScreening(row scanner) (AddressScreening, error) {
	var item AddressScreening
	err := row.Scan(&item.ID, &item.UserID, &item.CryptoWalletID, &item.CryptoAssetID,
		&item.Address, &item.AddressTag, &item.Network, &item.Context, &item.Provider,
		&item.ExternalScreeningID, &item.Decision, &item.RiskScore, &item.Category,
		&item.Matched, &item.Details, &item.CreatedAt)
	return item, err
}

func scanChainTransaction(row scanner) (ChainTransaction, error) {
	var item ChainTransaction
	var confirmedAt, failedAt, reversedAt sql.NullTime
	err := row.Scan(&item.ID, &item.UserID, &item.CryptoWalletID, &item.CryptoAssetID,
		&item.Symbol, &item.AssetType, &item.CryptoAddressID, &item.AddressScreeningID,
		&item.Direction, &item.Status, &item.Network, &item.Address, &item.AddressTag,
		&item.TxHash, &item.AmountBaseUnits, &item.FeeBaseUnits, &item.Confirmations,
		&item.RequiredConfirmations, &item.Provider, &item.ProviderReference,
		&item.TravelRuleRequired, &item.TravelRuleStatus, &item.FailureReason,
		&item.ReversalOfTransactionID, &item.Metadata, &item.CreatedByAdminUserID,
		&item.CreatedAt, &item.UpdatedAt, &confirmedAt, &failedAt, &reversedAt)
	if confirmedAt.Valid {
		item.ConfirmedAt = &confirmedAt.Time
	}
	if failedAt.Valid {
		item.FailedAt = &failedAt.Time
	}
	if reversedAt.Valid {
		item.ReversedAt = &reversedAt.Time
	}
	return item, err
}

func scanTravelRuleTransfer(row scanner) (TravelRuleTransfer, error) {
	var item TravelRuleTransfer
	var decidedAt sql.NullTime
	err := row.Scan(&item.ID, &item.CryptoChainTransactionID, &item.Status,
		&item.OriginatorName, &item.OriginatorCountry, &item.OriginatorWalletProvider,
		&item.BeneficiaryName, &item.BeneficiaryCountry, &item.BeneficiaryWalletProvider,
		&item.BeneficiaryWalletType, &item.Provider, &item.ExternalReference, &item.Payload,
		&item.RejectionReason, &item.CreatedByAdminUserID, &item.DecidedByAdminUserID,
		&decidedAt, &item.CreatedAt, &item.UpdatedAt)
	if decidedAt.Valid {
		item.DecidedAt = &decidedAt.Time
	}
	return item, err
}

func scanCustodyResponsibility(row scanner) (CustodyResponsibility, error) {
	var item CustodyResponsibility
	var approvedAt sql.NullTime
	err := row.Scan(&item.ID, &item.Provider, &item.CustodyModel, &item.KeyOwner,
		&item.SigningAuthority, &item.BackupOwner, &item.RecoveryOwner, &item.HSMOrKMS,
		&item.PolicyDocumentReference, &item.Status, &item.ApprovedByAdminUserID,
		&approvedAt, &item.CreatedAt, &item.UpdatedAt)
	if approvedAt.Valid {
		item.ApprovedAt = &approvedAt.Time
	}
	return item, err
}

func scanStablecoinIssuerMonitoring(row scanner) (StablecoinIssuerMonitoring, error) {
	var item StablecoinIssuerMonitoring
	var reserveAt, nextReviewAt sql.NullTime
	err := row.Scan(&item.ID, &item.CryptoAssetID, &item.Symbol, &item.Network,
		&item.IssuerName, &item.PegCurrency, &item.PegStatus, &item.RiskRating,
		&item.ReserveAttestationURL, &reserveAt, &nextReviewAt, &item.Notes,
		&item.UpdatedByAdminUserID, &item.CreatedAt, &item.UpdatedAt)
	if reserveAt.Valid {
		item.ReserveAttestationAt = &reserveAt.Time
	}
	if nextReviewAt.Valid {
		item.NextReviewAt = &nextReviewAt.Time
	}
	return item, err
}

func scanStablecoinNetworkControl(row scanner) (StablecoinNetworkControl, error) {
	var item StablecoinNetworkControl
	err := row.Scan(&item.ID, &item.CryptoAssetID, &item.Symbol, &item.Network,
		&item.DepositsEnabled, &item.WithdrawalsEnabled, &item.MinConfirmations,
		&item.ContractAddress, &item.Status, &item.Notes, &item.UpdatedByAdminUserID,
		&item.CreatedAt, &item.UpdatedAt)
	return item, err
}

func normalizeChainTransactionParams(params *ChainTransactionParams) error {
	if err := domain.ValidateUUID("user_id", params.UserID); err != nil {
		return err
	}
	if err := domain.ValidateUUID("crypto_wallet_id", params.CryptoWalletID); err != nil {
		return err
	}
	if err := domain.ValidateUUID("crypto_asset_id", params.CryptoAssetID); err != nil {
		return err
	}
	if params.CryptoAddressID != "" {
		if err := domain.ValidateUUID("crypto_address_id", params.CryptoAddressID); err != nil {
			return err
		}
	}
	if params.AddressScreeningID != "" {
		if err := domain.ValidateUUID("address_screening_id", params.AddressScreeningID); err != nil {
			return err
		}
	}
	params.Direction = strings.ToLower(strings.TrimSpace(params.Direction))
	if params.Direction != "deposit" && params.Direction != "withdrawal" {
		return fmt.Errorf("%w: direction must be deposit or withdrawal", domain.ErrValidation)
	}
	params.Address = strings.TrimSpace(params.Address)
	if params.Address == "" || len(params.Address) > 256 {
		return fmt.Errorf("%w: address is required", domain.ErrValidation)
	}
	params.Provider = strings.TrimSpace(params.Provider)
	if params.Provider == "" {
		params.Provider = "local_custody"
	}
	params.ScreeningDecision = strings.ToLower(strings.TrimSpace(params.ScreeningDecision))
	if params.ScreeningDecision != "" && params.ScreeningDecision != "clear" && params.ScreeningDecision != "review" && params.ScreeningDecision != "block" {
		return fmt.Errorf("%w: invalid screening decision", domain.ErrValidation)
	}
	return nil
}

func normalizeChainStatusParams(params *ChainTransactionStatusParams) error {
	if err := domain.ValidateUUID("id", params.TransactionID); err != nil {
		return err
	}
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	if params.Status != "pending" && params.Status != "confirmed" && params.Status != "failed" && params.Status != "reversed" {
		return fmt.Errorf("%w: invalid crypto transaction status", domain.ErrValidation)
	}
	params.FailureReason = strings.TrimSpace(params.FailureReason)
	if params.Status == "failed" && len(params.FailureReason) < 4 {
		return fmt.Errorf("%w: failure_reason is required for failed status", domain.ErrValidation)
	}
	return nil
}

func validChainStatusTransition(from, to string) bool {
	switch from {
	case "pending":
		return to == "confirmed" || to == "failed"
	case "confirmed":
		return to == "reversed"
	default:
		return false
	}
}

func normalizeTravelRuleParams(params *TravelRuleParams) error {
	if err := domain.ValidateUUID("crypto_chain_transaction_id", params.CryptoChainTransactionID); err != nil {
		return err
	}
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	if params.Status == "" {
		params.Status = "pending"
	}
	switch params.Status {
	case "not_required", "pending", "submitted", "accepted", "rejected", "expired":
	default:
		return fmt.Errorf("%w: invalid travel rule status", domain.ErrValidation)
	}
	params.BeneficiaryWalletType = strings.ToLower(strings.TrimSpace(params.BeneficiaryWalletType))
	if params.BeneficiaryWalletType == "" {
		params.BeneficiaryWalletType = "unknown"
	}
	if params.BeneficiaryWalletType != "hosted" && params.BeneficiaryWalletType != "unhosted" && params.BeneficiaryWalletType != "unknown" {
		return fmt.Errorf("%w: invalid beneficiary_wallet_type", domain.ErrValidation)
	}
	params.Provider = strings.TrimSpace(params.Provider)
	if params.Provider == "" {
		params.Provider = "manual"
	}
	if params.Status == "rejected" && strings.TrimSpace(params.RejectionReason) == "" {
		return fmt.Errorf("%w: rejection_reason is required", domain.ErrValidation)
	}
	return nil
}

func normalizeCustodyProviderConfig(params *CustodyProviderConfig) error {
	params.Provider = strings.TrimSpace(params.Provider)
	params.ProviderType = strings.ToLower(strings.TrimSpace(params.ProviderType))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.APIEnvironment = strings.ToLower(strings.TrimSpace(params.APIEnvironment))
	params.KeyManagementModel = strings.TrimSpace(params.KeyManagementModel)
	params.Notes = strings.TrimSpace(params.Notes)
	if params.Provider == "" || len(params.Provider) > 80 {
		return fmt.Errorf("%w: provider is required", domain.ErrValidation)
	}
	switch params.ProviderType {
	case "local_simulator", "custodian", "exchange", "self_custody", "disabled":
	default:
		return fmt.Errorf("%w: invalid provider_type", domain.ErrValidation)
	}
	if params.Status == "" {
		params.Status = "draft"
	}
	switch params.Status {
	case "draft", "sandbox", "contracted", "disabled", "terminated":
	default:
		return fmt.Errorf("%w: invalid provider status", domain.ErrValidation)
	}
	if params.APIEnvironment == "" {
		params.APIEnvironment = "sandbox"
	}
	switch params.APIEnvironment {
	case "local", "sandbox", "production":
	default:
		return fmt.Errorf("%w: invalid api_environment", domain.ErrValidation)
	}
	if params.RealMovementEnabled && (params.Status != "contracted" || params.APIEnvironment != "production") {
		return fmt.Errorf("%w: real movement requires contracted production provider", domain.ErrValidation)
	}
	if params.KeyManagementModel == "" {
		params.KeyManagementModel = "provider"
	}
	return nil
}

func normalizeCustodyResponsibility(params *CustodyResponsibility) error {
	params.Provider = strings.TrimSpace(params.Provider)
	params.CustodyModel = strings.ToLower(strings.TrimSpace(params.CustodyModel))
	params.KeyOwner = strings.ToLower(strings.TrimSpace(params.KeyOwner))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	if params.Provider == "" || params.SigningAuthority == "" || params.BackupOwner == "" ||
		params.RecoveryOwner == "" || params.HSMOrKMS == "" || params.PolicyDocumentReference == "" {
		return fmt.Errorf("%w: custody responsibility fields are required", domain.ErrValidation)
	}
	switch params.CustodyModel {
	case "omnibus", "segregated", "mpc", "self_custody", "non_custodial":
	default:
		return fmt.Errorf("%w: invalid custody_model", domain.ErrValidation)
	}
	switch params.KeyOwner {
	case "provider", "platform", "customer", "shared_mpc":
	default:
		return fmt.Errorf("%w: invalid key_owner", domain.ErrValidation)
	}
	if params.Status == "" {
		params.Status = "draft"
	}
	if params.Status != "draft" && params.Status != "approved" && params.Status != "retired" {
		return fmt.Errorf("%w: invalid responsibility status", domain.ErrValidation)
	}
	return nil
}

func normalizeStablecoinIssuer(params *StablecoinIssuerMonitoring) error {
	if err := domain.ValidateUUID("crypto_asset_id", params.CryptoAssetID); err != nil {
		return err
	}
	params.IssuerName = strings.TrimSpace(params.IssuerName)
	params.PegCurrency = domain.NormalizeCurrency(params.PegCurrency)
	params.PegStatus = strings.ToLower(strings.TrimSpace(params.PegStatus))
	params.RiskRating = strings.ToLower(strings.TrimSpace(params.RiskRating))
	if params.IssuerName == "" {
		return fmt.Errorf("%w: issuer_name is required", domain.ErrValidation)
	}
	if err := domain.ValidateCurrency(params.PegCurrency); err != nil {
		return err
	}
	if params.PegStatus == "" {
		params.PegStatus = "unknown"
	}
	if params.RiskRating == "" {
		params.RiskRating = "medium"
	}
	switch params.PegStatus {
	case "on_peg", "watch", "depegged", "unknown":
	default:
		return fmt.Errorf("%w: invalid peg_status", domain.ErrValidation)
	}
	switch params.RiskRating {
	case "low", "medium", "high", "critical":
	default:
		return fmt.Errorf("%w: invalid risk_rating", domain.ErrValidation)
	}
	return nil
}

func normalizeStablecoinNetworkControl(params *StablecoinNetworkControl) error {
	if err := domain.ValidateUUID("crypto_asset_id", params.CryptoAssetID); err != nil {
		return err
	}
	params.Network = strings.ToLower(strings.TrimSpace(params.Network))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	if params.Network == "" {
		return fmt.Errorf("%w: network is required", domain.ErrValidation)
	}
	if params.MinConfirmations < 0 {
		return fmt.Errorf("%w: min_confirmations must be zero or greater", domain.ErrValidation)
	}
	if params.Status == "" {
		params.Status = "disabled"
	}
	if params.Status != "active" && params.Status != "disabled" && params.Status != "sunset" {
		return fmt.Errorf("%w: invalid stablecoin network status", domain.ErrValidation)
	}
	return nil
}

func parseBaseUnits(raw string) (*big.Int, error) {
	value, err := parseBaseUnitsAllowZero(raw)
	if err != nil {
		return nil, err
	}
	if value.Sign() <= 0 {
		return nil, fmt.Errorf("%w: amount_base_units must be greater than zero", domain.ErrValidation)
	}
	return value, nil
}

func parseBaseUnitsAllowZero(raw string) (*big.Int, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		raw = "0"
	}
	value := new(big.Int)
	if _, ok := value.SetString(raw, 10); !ok || value.Sign() < 0 {
		return nil, fmt.Errorf("%w: base unit amount must be a non-negative integer string", domain.ErrValidation)
	}
	if len(value.String()) > 78 {
		return nil, fmt.Errorf("%w: base unit amount exceeds supported precision", domain.ErrValidation)
	}
	return value, nil
}

func jsonOrEmpty(raw json.RawMessage) []byte {
	if len(raw) == 0 || !json.Valid(raw) {
		return []byte("{}")
	}
	return raw
}

func optionalDate(raw string) (any, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		return nil, nil
	}
	parsed, err := time.Parse("2006-01-02", raw)
	if err != nil {
		return nil, domain.ErrValidation
	}
	return parsed, nil
}

func normalizeStringSlice(values []string) []string {
	seen := map[string]struct{}{}
	out := []string{}
	for _, value := range values {
		value = strings.ToUpper(strings.TrimSpace(value))
		if value == "" {
			continue
		}
		if _, ok := seen[value]; ok {
			continue
		}
		seen[value] = struct{}{}
		out = append(out, value)
	}
	return out
}

func normalizeOpsLimit(limit, fallback int) int {
	if limit <= 0 || limit > 500 {
		return fallback
	}
	return limit
}