package transfers

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

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/niels/banking-app/backend/internal/domain"
	"github.com/niels/banking-app/backend/internal/integrations/sanctions"
	"github.com/niels/banking-app/backend/internal/ledger"
	"github.com/niels/banking-app/backend/internal/payments"
	"github.com/niels/banking-app/backend/internal/risk"
	"github.com/niels/banking-app/backend/internal/routingcodes"
)

type Repository struct {
	db        *pgxpool.Pool
	ledger    *ledger.Repository
	risk      *risk.Repository
	sanctions sanctions.Provider
}

type CreateParams struct {
	UserID           string
	FromAccountID    string
	ToAccountID      string
	BeneficiaryID    string
	AmountCents      int64
	Description      string
	PaymentReference string
	IdempotencyKey   string
}

type lockedAccount struct {
	ID           string
	UserID       string
	WalletID     string
	BalanceCents int64
	Currency     string
	Status       string
}

type transferBeneficiary struct {
	ID           string
	Name         string
	IBAN         string
	BIC          string
	Currency     string
	RoutingCodes []domain.RoutingCode
}

type PaymentNotification struct {
	ID               string          `json:"id"`
	UserID           string          `json:"user_id"`
	TransferID       string          `json:"transfer_id,omitempty"`
	InboundPaymentID string          `json:"inbound_payment_id,omitempty"`
	NotificationType string          `json:"notification_type"`
	Channel          string          `json:"channel"`
	Status           string          `json:"status"`
	Title            string          `json:"title"`
	Message          string          `json:"message"`
	Payload          json.RawMessage `json:"payload"`
	SentAt           *time.Time      `json:"sent_at,omitempty"`
	CreatedAt        time.Time       `json:"created_at"`
}

func NewRepository(db *pgxpool.Pool, ledgers ...*ledger.Repository) *Repository {
	ledgerRepo := ledger.NewRepository(db)
	if len(ledgers) > 0 && ledgers[0] != nil {
		ledgerRepo = ledgers[0]
	}
	return &Repository{db: db, ledger: ledgerRepo}
}

func (r *Repository) WithRisk(riskRepo *risk.Repository) *Repository {
	r.risk = riskRepo
	return r
}

func (r *Repository) WithSanctions(provider sanctions.Provider) *Repository {
	r.sanctions = provider
	return r
}

func (r *Repository) Create(ctx context.Context, params CreateParams) (domain.Transfer, error) {
	var transfer domain.Transfer
	var err error

	for attempt := 0; attempt < 3; attempt++ {
		transfer, err = r.createOnce(ctx, params)
		if !isRetryableTxError(err) {
			return transfer, err
		}
		time.Sleep(time.Duration(attempt+1) * 25 * time.Millisecond)
	}

	return transfer, err
}

func (r *Repository) createOnce(ctx context.Context, params CreateParams) (domain.Transfer, error) {
	viaBeneficiary := params.BeneficiaryID != ""
	if params.ToAccountID != "" && params.FromAccountID == params.ToAccountID {
		return domain.Transfer{}, fmt.Errorf("%w: from_account_id and to_account_id must be different", domain.ErrValidation)
	}
	if (params.ToAccountID == "") == (params.BeneficiaryID == "") {
		return domain.Transfer{}, fmt.Errorf("%w: provide exactly one transfer destination", domain.ErrValidation)
	}
	if err := domain.ValidateAmount(params.AmountCents); err != nil {
		return domain.Transfer{}, err
	}

	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
	if err != nil {
		return domain.Transfer{}, err
	}
	defer tx.Rollback(ctx)

	if params.IdempotencyKey != "" {
		existing, err := findByIdempotencyKey(ctx, tx, params.UserID, params.IdempotencyKey)
		if err == nil {
			return existing, tx.Commit(ctx)
		}
		if !errors.Is(err, pgx.ErrNoRows) {
			return domain.Transfer{}, err
		}
	}

	var beneficiary *transferBeneficiary
	if params.BeneficiaryID != "" {
		found, err := findBeneficiaryOwned(ctx, tx, params.UserID, params.BeneficiaryID)
		if err != nil {
			return domain.Transfer{}, err
		}
		beneficiary = &found
		params.ToAccountID, err = findAccountIDByIBAN(ctx, tx, found.IBAN)
		if err != nil && !errors.Is(err, pgx.ErrNoRows) {
			return domain.Transfer{}, err
		}
		if errors.Is(err, pgx.ErrNoRows) {
			params.ToAccountID = ""
		}
	}
	if params.FromAccountID == params.ToAccountID {
		return domain.Transfer{}, fmt.Errorf("%w: source and beneficiary account must be different", domain.ErrValidation)
	}

	accounts, err := lockAccounts(ctx, tx, params.FromAccountID, params.ToAccountID)
	if err != nil {
		return domain.Transfer{}, err
	}

	from := accounts[params.FromAccountID]
	to := accounts[params.ToAccountID]
	if from == nil || to == nil {
		if from == nil || !viaBeneficiary {
			return domain.Transfer{}, domain.ErrNotFound
		}
	}
	if from.UserID != params.UserID {
		return domain.Transfer{}, domain.ErrForbidden
	}
	if from.Status != "active" {
		return domain.Transfer{}, fmt.Errorf("%w: source account must be active", domain.ErrValidation)
	}
	if to != nil {
		if to.Status != "active" {
			return domain.Transfer{}, fmt.Errorf("%w: destination account must be active", domain.ErrValidation)
		}
		if from.Currency != to.Currency {
			return domain.Transfer{}, fmt.Errorf("%w: accounts must use the same currency", domain.ErrValidation)
		}
	}
	if beneficiary != nil && from.Currency != beneficiary.Currency {
		return domain.Transfer{}, fmt.Errorf("%w: beneficiary and source account must use the same currency", domain.ErrValidation)
	}
	if from.BalanceCents < params.AmountCents {
		return domain.Transfer{}, domain.ErrInsufficientFunds
	}

	status := payments.StatusCompleted
	transferType := "internal"
	manualReviewRequired := false
	var paymentReason payments.ReasonMapping
	var paymentReasonProvider string
	var paymentReasonProviderCode string
	var paymentReasonNote string
	var beneficiaryScreening *sanctions.ScreeningResult
	var beneficiaryID, beneficiaryName, beneficiaryIBAN, beneficiaryBIC string
	beneficiaryRoutingCodesJSON := "[]"
	if beneficiary != nil {
		beneficiaryID = beneficiary.ID
		beneficiaryName = beneficiary.Name
		beneficiaryIBAN = beneficiary.IBAN
		beneficiaryBIC = beneficiary.BIC
		routingSnapshot, err := json.Marshal(beneficiary.RoutingCodes)
		if err != nil {
			return domain.Transfer{}, err
		}
		beneficiaryRoutingCodesJSON = string(routingSnapshot)
	}
	if to == nil {
		status = payments.StatusPending
		transferType = "sepa"
	}
	if beneficiary != nil && transferType == "sepa" {
		if err := routingcodes.ValidatePaymentRoute(ctx, tx, routingcodes.PaymentRoute{
			Provider:             "local_sepa",
			Country:              beneficiaryCountry(beneficiary.IBAN),
			Network:              "sepa",
			Currency:             from.Currency,
			RoutingCodes:         beneficiary.RoutingCodes,
			RequireContractRules: true,
		}); err != nil {
			return domain.Transfer{}, err
		}
		screening, providerCode, err := r.screenBeneficiary(ctx, params.UserID, *beneficiary)
		if err != nil {
			return domain.Transfer{}, err
		}
		beneficiaryScreening = &screening
		if providerCode != "" {
			status = payments.StatusReviewHeld
			manualReviewRequired = true
			paymentReasonProvider = screening.Provider
			paymentReasonProviderCode = providerCode
			paymentReasonNote = "Beneficiary sanctions screening requires review"
			paymentReason, err = payments.MapReason(ctx, tx, paymentReasonProvider, paymentReasonProviderCode)
			if err != nil {
				return domain.Transfer{}, err
			}
		}
	}

	if r.risk != nil {
		operation := risk.OperationTransferInternal
		if transferType == "sepa" {
			operation = risk.OperationTransferSEPA
		}
		evaluation, err := r.risk.EvaluateAndRecord(ctx, tx, risk.CheckRequest{
			UserID:      params.UserID,
			AccountID:   from.ID,
			Operation:   operation,
			SourceType:  "transfer",
			SourceID:    params.IdempotencyKey,
			Currency:    from.Currency,
			AmountCents: params.AmountCents,
			Metadata: map[string]any{
				"from_account_id":  from.ID,
				"to_account_id":    params.ToAccountID,
				"beneficiary_id":   beneficiaryID,
				"beneficiary_iban": beneficiaryIBAN,
				"transfer_type":    transferType,
			},
		})
		if err != nil {
			return domain.Transfer{}, err
		}
		if risk.IsBlocked(evaluation) {
			if err := tx.Commit(ctx); err != nil {
				return domain.Transfer{}, err
			}
			return domain.Transfer{}, fmt.Errorf("%w: %s", domain.ErrLimitExceeded, evaluation.Reason)
		}
		if transferType == "sepa" && evaluation.Decision == risk.DecisionReview && !manualReviewRequired {
			status = payments.StatusReviewHeld
			manualReviewRequired = true
			paymentReasonProvider = payments.ProviderRisk
			paymentReasonProviderCode = payments.ProviderCodeRiskReview
			paymentReasonNote = evaluation.Reason
			paymentReason, err = payments.MapReason(ctx, tx, paymentReasonProvider, paymentReasonProviderCode)
			if err != nil {
				return domain.Transfer{}, err
			}
		}
	}

	if _, err := tx.Exec(ctx, `
		UPDATE accounts
		SET balance_cents = balance_cents - $1, updated_at = now()
		WHERE id = $2
	`, params.AmountCents, from.ID); err != nil {
		return domain.Transfer{}, err
	}
	if from.WalletID != "" {
		if _, err := tx.Exec(ctx, `
			INSERT INTO wallet_balances (wallet_id, currency)
			VALUES ($1, $2)
			ON CONFLICT (wallet_id, currency) DO NOTHING
		`, from.WalletID, from.Currency); err != nil {
			return domain.Transfer{}, err
		}
		if _, err := tx.Exec(ctx, `
			UPDATE wallet_balances
			SET available_balance_cents = available_balance_cents - $1
			WHERE wallet_id = $2 AND currency = $3
		`, params.AmountCents, from.WalletID, from.Currency); err != nil {
			return domain.Transfer{}, err
		}
	}

	if to != nil {
		if _, err := tx.Exec(ctx, `
			UPDATE accounts
			SET balance_cents = balance_cents + $1, updated_at = now()
			WHERE id = $2
		`, params.AmountCents, to.ID); err != nil {
			return domain.Transfer{}, err
		}
		if to.WalletID != "" {
			if _, err := tx.Exec(ctx, `
				INSERT INTO wallet_balances (wallet_id, currency)
				VALUES ($1, $2)
				ON CONFLICT (wallet_id, currency) DO NOTHING
			`, to.WalletID, to.Currency); err != nil {
				return domain.Transfer{}, err
			}
			if _, err := tx.Exec(ctx, `
				UPDATE wallet_balances
				SET available_balance_cents = available_balance_cents + $1
				WHERE wallet_id = $2 AND currency = $3
			`, params.AmountCents, to.WalletID, to.Currency); err != nil {
				return domain.Transfer{}, err
			}
		}
	}

	row := tx.QueryRow(ctx, `
		INSERT INTO transfers (
			user_id, from_account_id, to_account_id, beneficiary_id, beneficiary_name, beneficiary_iban,
			beneficiary_bic, beneficiary_routing_codes, payment_reference, transfer_type, amount_cents, currency,
			status, description, idempotency_key, payment_reason_code, payment_reason_category,
			payment_reason_description, manual_review_required
		)
		VALUES (
			$1, $2, NULLIF($3, '')::uuid, NULLIF($4, '')::uuid, NULLIF($5, ''), NULLIF($6, ''),
			NULLIF($7, ''), $8::jsonb, NULLIF($9, ''), $10, $11, $12, $13, NULLIF($14, ''), NULLIF($15, ''),
			NULLIF($16, ''), NULLIF($17, ''), NULLIF($18, ''), $19
		)
		RETURNING id::text, user_id::text, from_account_id::text, COALESCE(to_account_id::text, ''),
			COALESCE(beneficiary_id::text, ''), COALESCE(beneficiary_name, ''), COALESCE(beneficiary_iban, ''),
			COALESCE(beneficiary_bic, ''), COALESCE(beneficiary_routing_codes, '[]'::jsonb)::text,
			COALESCE(payment_reference, ''), transfer_type, amount_cents, currency, status,
			COALESCE(description, ''), COALESCE(idempotency_key, ''), settlement_attempts,
			COALESCE(settlement_provider, ''), COALESCE(settlement_reference, ''), COALESCE(settlement_error, ''),
			settlement_next_attempt_at, settled_at, COALESCE(payment_reason_code, ''),
			COALESCE(payment_reason_category, ''), COALESCE(payment_reason_description, ''),
			manual_review_required, created_at
	`, params.UserID, from.ID, params.ToAccountID, beneficiaryID, beneficiaryName, beneficiaryIBAN,
		beneficiaryBIC, beneficiaryRoutingCodesJSON, params.PaymentReference, transferType, params.AmountCents, from.Currency, status,
		params.Description, params.IdempotencyKey, paymentReason.InternalCode, paymentReason.Category,
		paymentReason.Description, manualReviewRequired)

	transfer, err := scanTransfer(row)
	if err != nil {
		if isUniqueViolation(err) && params.IdempotencyKey != "" {
			existing, findErr := findByIdempotencyKey(ctx, tx, params.UserID, params.IdempotencyKey)
			if findErr == nil {
				return existing, tx.Commit(ctx)
			}
		}
		return domain.Transfer{}, err
	}
	if beneficiaryScreening != nil {
		if err := recordBeneficiaryScreening(ctx, tx, transfer.ID, params.UserID, beneficiaryID, *beneficiaryScreening); err != nil {
			return domain.Transfer{}, err
		}
	}

	if transfer.TransferType == "sepa" {
		switch transfer.Status {
		case payments.StatusReviewHeld:
			openedBy := "risk"
			if paymentReasonProvider != payments.ProviderRisk {
				openedBy = "system"
			}
			if err := insertPaymentReviewCase(ctx, tx, transfer.ID, paymentReason.InternalCode, paymentReasonNote, openedBy, ""); err != nil {
				return domain.Transfer{}, err
			}
			if err := insertSEPASettlementEvent(ctx, tx, transfer.ID, "review_held", "", transfer.Status, paymentReasonProvider, "", paymentReason.Description); err != nil {
				return domain.Transfer{}, err
			}
			if err := payments.RecordStatusEvent(ctx, tx, payments.StatusEventParams{
				TransferID:   transfer.ID,
				ToStatus:     transfer.Status,
				Provider:     paymentReasonProvider,
				ProviderCode: paymentReasonProviderCode,
				Reason:       paymentReason,
				Note:         paymentReasonNote,
				ActorType:    payments.ActorRisk,
			}); err != nil {
				return domain.Transfer{}, err
			}
		default:
			if err := insertSEPASettlementEvent(ctx, tx, transfer.ID, "queued", "", transfer.Status, payments.ProviderLocalSEPA, "", "External transfer queued for SEPA settlement"); err != nil {
				return domain.Transfer{}, err
			}
		}
		if err := payments.RecordNotification(ctx, tx, payments.NotificationParams{
			UserID:     transfer.UserID,
			TransferID: transfer.ID,
			Type:       "payment." + transfer.Status,
			Title:      "SEPA payment " + transfer.Status,
			Message:    sepaPaymentNotificationMessage(transfer),
			Payload: map[string]any{
				"transfer_id":      transfer.ID,
				"amount_cents":     transfer.AmountCents,
				"currency":         transfer.Currency,
				"beneficiary_name": transfer.BeneficiaryName,
				"status":           transfer.Status,
			},
		}); err != nil {
			return domain.Transfer{}, err
		}
	}

	if err := r.postLedger(ctx, tx, transfer, from, to); err != nil {
		return domain.Transfer{}, err
	}

	if err := tx.Commit(ctx); err != nil {
		return domain.Transfer{}, err
	}

	return transfer, nil
}

func insertPaymentReviewCase(ctx context.Context, tx pgx.Tx, transferID, reasonCode, reason, openedByType, openedByUserID string) error {
	if reason == "" {
		reason = "Manual review required before payment settlement"
	}
	_, err := tx.Exec(ctx, `
		INSERT INTO payment_review_cases (
			transfer_id, status, reason_code, reason, opened_by_type, opened_by_user_id
		)
		VALUES ($1, 'open', $2, $3, $4, NULLIF($5, '')::uuid)
		ON CONFLICT (transfer_id) DO UPDATE
		SET status = 'open',
			reason_code = EXCLUDED.reason_code,
			reason = EXCLUDED.reason,
			opened_by_type = EXCLUDED.opened_by_type,
			opened_by_user_id = EXCLUDED.opened_by_user_id,
			decided_by_admin_user_id = NULL,
			decision_note = NULL,
			decided_at = NULL
	`, transferID, reasonCode, reason, openedByType, openedByUserID)
	return err
}

func (r *Repository) screenBeneficiary(ctx context.Context, userID string, beneficiary transferBeneficiary) (sanctions.ScreeningResult, string, error) {
	if r.sanctions == nil {
		return sanctions.ScreeningResult{
			Provider:            "not_configured",
			ExternalScreeningID: "",
			Status:              "clear",
			RiskScore:           0,
			Matched:             false,
			MatchDetails:        map[string]any{"source": "screening_provider_not_configured"},
		}, "", nil
	}

	result, err := r.sanctions.ScreenPerson(ctx, sanctions.ScreenPersonParams{
		UserID:    userID,
		LegalName: beneficiary.Name,
		Country:   beneficiaryCountry(beneficiary.IBAN),
	})
	if err != nil {
		return sanctions.ScreeningResult{}, "", err
	}

	switch result.Status {
	case "hit":
		return result, "BENEFICIARY_SANCTIONS_HIT", nil
	case "review":
		return result, "BENEFICIARY_SANCTIONS_REVIEW", nil
	default:
		return result, "", nil
	}
}

func recordBeneficiaryScreening(ctx context.Context, tx pgx.Tx, transferID, userID, beneficiaryID string, result sanctions.ScreeningResult) error {
	matchDetails, err := json.Marshal(result.MatchDetails)
	if err != nil {
		return err
	}
	_, err = tx.Exec(ctx, `
		INSERT INTO payment_beneficiary_screenings (
			transfer_id, beneficiary_id, user_id, provider, external_screening_id,
			status, risk_score, matched, match_details
		)
		VALUES ($1, NULLIF($2, '')::uuid, $3, $4, $5, $6, $7, $8, $9::jsonb)
	`, transferID, beneficiaryID, userID, result.Provider, result.ExternalScreeningID,
		result.Status, result.RiskScore, result.Matched, string(matchDetails))
	return err
}

func sepaPaymentNotificationMessage(transfer domain.Transfer) string {
	switch transfer.Status {
	case payments.StatusReviewHeld:
		return "Your SEPA payment is being reviewed before execution."
	case payments.StatusPending:
		return "Your SEPA payment has been queued for settlement."
	default:
		return "Your SEPA payment status changed to " + transfer.Status + "."
	}
}

func (r *Repository) postLedger(ctx context.Context, tx pgx.Tx, transfer domain.Transfer, from, to *lockedAccount) error {
	fromLedger, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		OwnerUserID:   from.UserID,
		ReferenceType: "account",
		ReferenceID:   from.ID,
		Currency:      from.Currency,
		NormalBalance: "credit",
	})
	if err != nil {
		return err
	}
	var destinationLedger domain.LedgerAccount
	if to != nil {
		destinationLedger, err = r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
			OwnerUserID:   to.UserID,
			ReferenceType: "account",
			ReferenceID:   to.ID,
			Currency:      to.Currency,
			NormalBalance: "credit",
		})
	} else {
		destinationLedger, err = r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
			ReferenceType: "external_clearing",
			ReferenceID:   "sepa_outbound",
			Currency:      from.Currency,
			NormalBalance: "credit",
		})
	}
	if err != nil {
		return err
	}

	_, err = r.ledger.Post(ctx, tx, ledger.PostParams{
		EventType:      "transfer." + transfer.Status,
		SourceType:     "transfer",
		SourceID:       transfer.ID,
		IdempotencyKey: transfer.IdempotencyKey,
		Description:    transfer.Description,
		Metadata: map[string]any{
			"from_account_id":  transfer.FromAccountID,
			"to_account_id":    transfer.ToAccountID,
			"beneficiary_iban": transfer.BeneficiaryIBAN,
			"transfer_type":    transfer.TransferType,
			"reason_code":      transfer.PaymentReasonCode,
		},
		Lines: []ledger.LineParams{
			{
				LedgerAccountID: fromLedger.ID,
				Direction:       "debit",
				AmountCents:     transfer.AmountCents,
				Currency:        transfer.Currency,
			},
			{
				LedgerAccountID: destinationLedger.ID,
				Direction:       "credit",
				AmountCents:     transfer.AmountCents,
				Currency:        transfer.Currency,
			},
		},
	})
	return err
}

func (r *Repository) ListForUser(ctx context.Context, userID string, limit int) ([]domain.Transfer, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}

	rows, err := r.db.Query(ctx, `
		SELECT DISTINCT t.id::text, t.user_id::text, t.from_account_id::text, COALESCE(t.to_account_id::text, ''),
			COALESCE(t.beneficiary_id::text, ''), COALESCE(t.beneficiary_name, ''), COALESCE(t.beneficiary_iban, ''),
			COALESCE(t.beneficiary_bic, ''), COALESCE(t.beneficiary_routing_codes, '[]'::jsonb)::text,
			COALESCE(t.payment_reference, ''), t.transfer_type, t.amount_cents, t.currency, t.status,
			COALESCE(t.description, ''), COALESCE(t.idempotency_key, ''), t.settlement_attempts,
			COALESCE(t.settlement_provider, ''), COALESCE(t.settlement_reference, ''),
			COALESCE(t.settlement_error, ''), t.settlement_next_attempt_at, t.settled_at,
			COALESCE(t.payment_reason_code, ''), COALESCE(t.payment_reason_category, ''),
			COALESCE(t.payment_reason_description, ''), t.manual_review_required, t.created_at
		FROM transfers t
		JOIN accounts a ON a.id = t.from_account_id OR a.id = t.to_account_id
		WHERE a.user_id = $1
		ORDER BY t.created_at DESC
		LIMIT $2
	`, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	transfers := []domain.Transfer{}
	for rows.Next() {
		transfer, err := scanTransfer(rows)
		if err != nil {
			return nil, err
		}
		transfers = append(transfers, transfer)
	}

	return transfers, rows.Err()
}

func (r *Repository) ListNotificationsForUser(ctx context.Context, userID string, limit int) ([]PaymentNotification, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(transfer_id::text, ''), COALESCE(inbound_payment_id::text, ''),
			notification_type, channel, status, title, message, payload, sent_at, created_at
		FROM payment_notifications
		WHERE user_id = $1
		ORDER BY created_at DESC
		LIMIT $2
	`, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	notifications := []PaymentNotification{}
	for rows.Next() {
		notification, err := scanPaymentNotification(rows)
		if err != nil {
			return nil, err
		}
		notifications = append(notifications, notification)
	}
	return notifications, rows.Err()
}

func lockAccounts(ctx context.Context, tx pgx.Tx, fromID, toID string) (map[string]*lockedAccount, error) {
	rows, err := tx.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(wallet_id::text, ''), balance_cents, currency, status
		FROM accounts
		WHERE id = $1 OR id = $2
		ORDER BY id
		FOR UPDATE
	`, fromID, toID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	accounts := map[string]*lockedAccount{}
	for rows.Next() {
		var account lockedAccount
		if err := rows.Scan(&account.ID, &account.UserID, &account.WalletID, &account.BalanceCents, &account.Currency, &account.Status); err != nil {
			return nil, err
		}
		accounts[account.ID] = &account
	}

	return accounts, rows.Err()
}

func findByIdempotencyKey(ctx context.Context, tx pgx.Tx, userID, key string) (domain.Transfer, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, user_id::text, from_account_id::text, COALESCE(to_account_id::text, ''),
			COALESCE(beneficiary_id::text, ''), COALESCE(beneficiary_name, ''), COALESCE(beneficiary_iban, ''),
			COALESCE(beneficiary_bic, ''), COALESCE(beneficiary_routing_codes, '[]'::jsonb)::text,
			COALESCE(payment_reference, ''), transfer_type, amount_cents, currency, status,
			COALESCE(description, ''), COALESCE(idempotency_key, ''), settlement_attempts,
			COALESCE(settlement_provider, ''), COALESCE(settlement_reference, ''),
			COALESCE(settlement_error, ''), settlement_next_attempt_at, settled_at,
			COALESCE(payment_reason_code, ''), COALESCE(payment_reason_category, ''),
			COALESCE(payment_reason_description, ''), manual_review_required, created_at
		FROM transfers
		WHERE user_id = $1 AND idempotency_key = $2
	`, userID, key)
	return scanTransfer(row)
}

type scanner interface {
	Scan(dest ...any) error
}

func scanTransfer(row scanner) (domain.Transfer, error) {
	var transfer domain.Transfer
	var ignoredKey sql.NullString
	var nextAttemptAt sql.NullTime
	var settledAt sql.NullTime
	var beneficiaryRoutingCodes string
	err := row.Scan(
		&transfer.ID,
		&transfer.UserID,
		&transfer.FromAccountID,
		&transfer.ToAccountID,
		&transfer.BeneficiaryID,
		&transfer.BeneficiaryName,
		&transfer.BeneficiaryIBAN,
		&transfer.BeneficiaryBIC,
		&beneficiaryRoutingCodes,
		&transfer.PaymentReference,
		&transfer.TransferType,
		&transfer.AmountCents,
		&transfer.Currency,
		&transfer.Status,
		&transfer.Description,
		&ignoredKey,
		&transfer.SettlementAttempts,
		&transfer.SettlementProvider,
		&transfer.SettlementReference,
		&transfer.SettlementError,
		&nextAttemptAt,
		&settledAt,
		&transfer.PaymentReasonCode,
		&transfer.PaymentReasonCategory,
		&transfer.PaymentReasonDescription,
		&transfer.ManualReviewRequired,
		&transfer.CreatedAt,
	)
	if err != nil {
		return transfer, err
	}
	if ignoredKey.Valid {
		transfer.IdempotencyKey = ignoredKey.String
	}
	if nextAttemptAt.Valid {
		transfer.SettlementNextAttemptAt = &nextAttemptAt.Time
	}
	if settledAt.Valid {
		transfer.SettledAt = &settledAt.Time
	}
	if beneficiaryRoutingCodes != "" {
		if err := json.Unmarshal([]byte(beneficiaryRoutingCodes), &transfer.BeneficiaryRoutingCodes); err != nil {
			return transfer, err
		}
	}
	return transfer, nil
}

func scanPaymentNotification(row scanner) (PaymentNotification, error) {
	var notification PaymentNotification
	var sentAt sql.NullTime
	err := row.Scan(
		&notification.ID,
		&notification.UserID,
		&notification.TransferID,
		&notification.InboundPaymentID,
		&notification.NotificationType,
		&notification.Channel,
		&notification.Status,
		&notification.Title,
		&notification.Message,
		&notification.Payload,
		&sentAt,
		&notification.CreatedAt,
	)
	if sentAt.Valid {
		notification.SentAt = &sentAt.Time
	}
	return notification, err
}

func insertSEPASettlementEvent(ctx context.Context, tx pgx.Tx, transferID, eventType, statusBefore, statusAfter, provider, providerReference, reason string) error {
	_, err := tx.Exec(ctx, `
		INSERT INTO sepa_settlement_events (
			transfer_id, event_type, status_before, status_after, provider, provider_reference, reason
		)
		VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, ''), $5, NULLIF($6, ''), NULLIF($7, ''))
	`, transferID, eventType, statusBefore, statusAfter, provider, providerReference, reason)
	return err
}

func findBeneficiaryOwned(ctx context.Context, tx pgx.Tx, userID, beneficiaryID string) (transferBeneficiary, error) {
	var beneficiary transferBeneficiary
	err := tx.QueryRow(ctx, `
		SELECT id::text, name, iban, COALESCE(bic, ''), currency
		FROM beneficiaries
		WHERE id = $1 AND user_id = $2 AND status = 'active'
	`, beneficiaryID, userID).Scan(
		&beneficiary.ID, &beneficiary.Name, &beneficiary.IBAN, &beneficiary.BIC, &beneficiary.Currency,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return transferBeneficiary{}, domain.ErrNotFound
	}
	if err != nil {
		return transferBeneficiary{}, err
	}
	beneficiary.RoutingCodes, err = listBeneficiaryRoutingCodesForTransfer(ctx, tx, beneficiary.ID)
	if err != nil {
		return transferBeneficiary{}, err
	}
	return beneficiary, nil
}

func listBeneficiaryRoutingCodesForTransfer(ctx context.Context, tx pgx.Tx, beneficiaryID string) ([]domain.RoutingCode, error) {
	rows, err := tx.Query(ctx, `
		SELECT id::text, beneficiary_id::text, code_type, country::text, network, code, status, created_at, updated_at
		FROM beneficiary_routing_codes
		WHERE beneficiary_id = $1 AND status = 'active'
		ORDER BY country, network, code_type, code
	`, beneficiaryID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	codes := []domain.RoutingCode{}
	for rows.Next() {
		var code domain.RoutingCode
		if err := rows.Scan(
			&code.ID,
			&code.OwnerID,
			&code.CodeType,
			&code.Country,
			&code.Network,
			&code.Code,
			&code.Status,
			&code.CreatedAt,
			&code.UpdatedAt,
		); err != nil {
			return nil, err
		}
		code.OwnerType = "beneficiary"
		codes = append(codes, code)
	}
	return codes, rows.Err()
}

func findAccountIDByIBAN(ctx context.Context, tx pgx.Tx, iban string) (string, error) {
	var accountID string
	err := tx.QueryRow(ctx, `SELECT id::text FROM accounts WHERE iban = $1`, iban).Scan(&accountID)
	return accountID, err
}

func beneficiaryCountry(iban string) string {
	if len(iban) < 2 {
		return ""
	}
	return iban[:2]
}

func isUniqueViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23505"
}

func isRetryableTxError(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && (pgErr.Code == "40001" || pgErr.Code == "40P01")
}