package admin

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

	"github.com/jackc/pgx/v5"
	"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"
)

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

type DashboardSummary struct {
	GeneratedAt time.Time         `json:"generated_at"`
	Platform    PlatformMetrics   `json:"platform"`
	Compliance  ComplianceMetrics `json:"compliance"`
	Money       []MoneyMetric     `json:"money"`
	Cards       CardMetrics       `json:"cards"`
	Crypto      CryptoMetrics     `json:"crypto"`
	Ledger      LedgerMetrics     `json:"ledger"`
}

type SEPASettlementDashboard struct {
	Metrics   SEPASettlementMetrics    `json:"metrics"`
	Transfers []SEPASettlementTransfer `json:"transfers"`
	Events    []SEPASettlementEvent    `json:"events"`
}

type SEPASettlementMetrics struct {
	Pending      int64 `json:"pending"`
	Processing   int64 `json:"processing"`
	ReviewHeld   int64 `json:"review_held"`
	Failed       int64 `json:"failed"`
	Retried      int64 `json:"retried"`
	Completed24h int64 `json:"completed_24h"`
}

type PlatformMetrics struct {
	Users        int64 `json:"users"`
	Customers    int64 `json:"customers"`
	Admins       int64 `json:"admins"`
	Accounts     int64 `json:"accounts"`
	Wallets      int64 `json:"wallets"`
	SavingsGoals int64 `json:"savings_goals"`
}

type ComplianceMetrics struct {
	KYCPending                       int64 `json:"kyc_pending"`
	KYCManualReview                  int64 `json:"kyc_manual_review"`
	KYCVerified                      int64 `json:"kyc_verified"`
	AMLOpenCases                     int64 `json:"aml_open_cases"`
	AMLReviewing                     int64 `json:"aml_reviewing_cases"`
	AMLEscalated                     int64 `json:"aml_escalated_cases"`
	WalletAdjustmentApprovalsPending int64 `json:"wallet_adjustment_approvals_pending"`
}

type MoneyMetric struct {
	Currency                    string `json:"currency"`
	AccountBalanceCents         int64  `json:"account_balance_cents"`
	WalletAvailableBalanceCents int64  `json:"wallet_available_balance_cents"`
	WalletReservedBalanceCents  int64  `json:"wallet_reserved_balance_cents"`
	SavingsReservedCents        int64  `json:"savings_reserved_cents"`
	TransferVolume24hCents      int64  `json:"transfer_volume_24h_cents"`
	TransferCount24h            int64  `json:"transfer_count_24h"`
}

type CardMetrics struct {
	Active               int64 `json:"active"`
	Frozen               int64 `json:"frozen"`
	Canceled             int64 `json:"canceled"`
	ApprovedAuth24h      int64 `json:"approved_authorizations_24h"`
	DeclinedAuth24h      int64 `json:"declined_authorizations_24h"`
	Authorized24hCents   int64 `json:"authorized_24h_cents"`
	FailedLimitSyncCards int64 `json:"failed_limit_sync_cards"`
}

type CryptoMetrics struct {
	AssetsEnabled   int64 `json:"assets_enabled"`
	WalletsActive   int64 `json:"wallets_active"`
	AddressesActive int64 `json:"addresses_active"`
}

type LedgerMetrics struct {
	Accounts       int64 `json:"accounts"`
	JournalEntries int64 `json:"journal_entries"`
	JournalLines   int64 `json:"journal_lines"`
}

type ActivityItem struct {
	Kind        string          `json:"kind"`
	ID          string          `json:"id"`
	Title       string          `json:"title"`
	ActorUserID string          `json:"actor_user_id,omitempty"`
	Status      string          `json:"status,omitempty"`
	AmountCents int64           `json:"amount_cents,omitempty"`
	Currency    string          `json:"currency,omitempty"`
	Metadata    json.RawMessage `json:"metadata"`
	CreatedAt   time.Time       `json:"created_at"`
}

type QueueItem struct {
	Queue     string          `json:"queue"`
	ID        string          `json:"id"`
	UserID    string          `json:"user_id,omitempty"`
	Title     string          `json:"title"`
	Status    string          `json:"status"`
	Severity  string          `json:"severity,omitempty"`
	Metadata  json.RawMessage `json:"metadata"`
	CreatedAt time.Time       `json:"created_at"`
	UpdatedAt time.Time       `json:"updated_at"`
}

type SEPASettlementTransfer struct {
	ID                       string               `json:"id"`
	UserID                   string               `json:"user_id"`
	UserEmail                string               `json:"user_email"`
	UserFullName             string               `json:"user_full_name"`
	FromAccountID            string               `json:"from_account_id"`
	BeneficiaryID            string               `json:"beneficiary_id,omitempty"`
	BeneficiaryName          string               `json:"beneficiary_name,omitempty"`
	BeneficiaryIBAN          string               `json:"beneficiary_iban,omitempty"`
	BeneficiaryBIC           string               `json:"beneficiary_bic,omitempty"`
	BeneficiaryRoutingCodes  []domain.RoutingCode `json:"beneficiary_routing_codes,omitempty"`
	PaymentReference         string               `json:"payment_reference,omitempty"`
	AmountCents              int64                `json:"amount_cents"`
	Currency                 string               `json:"currency"`
	Status                   string               `json:"status"`
	Description              string               `json:"description,omitempty"`
	SettlementAttempts       int                  `json:"settlement_attempts"`
	SettlementProvider       string               `json:"settlement_provider,omitempty"`
	SettlementReference      string               `json:"settlement_reference,omitempty"`
	SettlementError          string               `json:"settlement_error,omitempty"`
	SettlementNextAttemptAt  *time.Time           `json:"settlement_next_attempt_at,omitempty"`
	SettledAt                *time.Time           `json:"settled_at,omitempty"`
	PaymentReasonCode        string               `json:"payment_reason_code,omitempty"`
	PaymentReasonCategory    string               `json:"payment_reason_category,omitempty"`
	PaymentReasonDescription string               `json:"payment_reason_description,omitempty"`
	ManualReviewRequired     bool                 `json:"manual_review_required"`
	RetriedEvents            int64                `json:"retried_events"`
	LatestEventType          string               `json:"latest_event_type,omitempty"`
	LatestEventReason        string               `json:"latest_event_reason,omitempty"`
	LatestEventAt            *time.Time           `json:"latest_event_at,omitempty"`
	CreatedAt                time.Time            `json:"created_at"`
}

type SEPASettlementEvent struct {
	ID                string    `json:"id"`
	TransferID        string    `json:"transfer_id"`
	EventType         string    `json:"event_type"`
	StatusBefore      string    `json:"status_before,omitempty"`
	StatusAfter       string    `json:"status_after,omitempty"`
	Provider          string    `json:"provider"`
	ProviderReference string    `json:"provider_reference,omitempty"`
	Reason            string    `json:"reason,omitempty"`
	TransferStatus    string    `json:"transfer_status"`
	UserID            string    `json:"user_id"`
	UserEmail         string    `json:"user_email"`
	BeneficiaryName   string    `json:"beneficiary_name,omitempty"`
	BeneficiaryIBAN   string    `json:"beneficiary_iban,omitempty"`
	AmountCents       int64     `json:"amount_cents"`
	Currency          string    `json:"currency"`
	CreatedAt         time.Time `json:"created_at"`
}

type UserListItem struct {
	ID                string    `json:"id"`
	Email             string    `json:"email"`
	FullName          string    `json:"full_name"`
	Role              string    `json:"role"`
	KYCStatus         string    `json:"kyc_status"`
	AccountCount      int64     `json:"account_count"`
	WalletCount       int64     `json:"wallet_count"`
	SavingsGoalCount  int64     `json:"savings_goal_count"`
	CryptoWalletCount int64     `json:"crypto_wallet_count"`
	OpenAMLCaseCount  int64     `json:"open_aml_case_count"`
	CreatedAt         time.Time `json:"created_at"`
}

type UserDetail struct {
	User                     domain.User                             `json:"user"`
	KYCProfile               *domain.KYCProfile                      `json:"kyc_profile,omitempty"`
	Accounts                 []domain.Account                        `json:"accounts"`
	Wallets                  []domain.Wallet                         `json:"wallets"`
	SavingsGoals             []domain.SavingsGoal                    `json:"savings_goals"`
	VirtualCards             []domain.VirtualCard                    `json:"virtual_cards"`
	CryptoWallets            []domain.CryptoWallet                   `json:"crypto_wallets"`
	AMLCases                 []domain.AMLCase                        `json:"aml_cases"`
	RecentAuditEvents        []domain.AuditEvent                     `json:"recent_audit_events"`
	LedgerEntries            []domain.LedgerJournalEntry             `json:"ledger_entries"`
	WalletAdjustments        []domain.WalletBalanceAdjustment        `json:"wallet_balance_adjustments"`
	WalletAdjustmentRequests []domain.WalletBalanceAdjustmentRequest `json:"wallet_balance_adjustment_requests"`
}

type AccountDetail struct {
	Account       domain.Account              `json:"account"`
	Owner         domain.User                 `json:"owner"`
	Wallet        *domain.Wallet              `json:"wallet,omitempty"`
	SavingsGoals  []domain.SavingsGoal        `json:"savings_goals"`
	VirtualCards  []domain.VirtualCard        `json:"virtual_cards"`
	Transfers     []domain.Transfer           `json:"transfers"`
	LedgerEntries []domain.LedgerJournalEntry `json:"ledger_entries"`
}

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) WithSanctions(provider sanctions.Provider) *Repository {
	r.sanctions = provider
	return r
}

func (r *Repository) Summary(ctx context.Context) (DashboardSummary, error) {
	summary := DashboardSummary{GeneratedAt: time.Now().UTC()}

	if err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM users),
			(SELECT COUNT(*) FROM users WHERE role = 'customer'),
			(SELECT COUNT(*) FROM users WHERE role = 'admin'),
			(SELECT COUNT(*) FROM accounts),
			(SELECT COUNT(*) FROM wallets),
			(SELECT COUNT(*) FROM savings_goals)
	`).Scan(
		&summary.Platform.Users,
		&summary.Platform.Customers,
		&summary.Platform.Admins,
		&summary.Platform.Accounts,
		&summary.Platform.Wallets,
		&summary.Platform.SavingsGoals,
	); err != nil {
		return DashboardSummary{}, err
	}

	if err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM kyc_profiles WHERE status = 'pending'),
			(SELECT COUNT(*) FROM kyc_profiles WHERE status = 'manual_review'),
			(SELECT COUNT(*) FROM kyc_profiles WHERE status = 'verified'),
			(SELECT COUNT(*) FROM aml_cases WHERE status = 'open'),
			(SELECT COUNT(*) FROM aml_cases WHERE status = 'reviewing'),
			(SELECT COUNT(*) FROM aml_cases WHERE status = 'escalated'),
			(SELECT COUNT(*) FROM wallet_balance_adjustment_requests WHERE status = 'pending')
	`).Scan(
		&summary.Compliance.KYCPending,
		&summary.Compliance.KYCManualReview,
		&summary.Compliance.KYCVerified,
		&summary.Compliance.AMLOpenCases,
		&summary.Compliance.AMLReviewing,
		&summary.Compliance.AMLEscalated,
		&summary.Compliance.WalletAdjustmentApprovalsPending,
	); err != nil {
		return DashboardSummary{}, err
	}

	money, err := r.moneyMetrics(ctx)
	if err != nil {
		return DashboardSummary{}, err
	}
	summary.Money = money

	if err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM virtual_cards WHERE status = 'active'),
			(SELECT COUNT(*) FROM virtual_cards WHERE status = 'frozen'),
			(SELECT COUNT(*) FROM virtual_cards WHERE status = 'canceled'),
			(SELECT COUNT(*) FROM card_authorizations WHERE status = 'approved' AND created_at >= now() - interval '24 hours'),
			(SELECT COUNT(*) FROM card_authorizations WHERE status = 'declined' AND created_at >= now() - interval '24 hours'),
			(SELECT COALESCE(SUM(amount_cents), 0)::bigint FROM card_authorizations WHERE status = 'approved' AND created_at >= now() - interval '24 hours'),
			(SELECT COUNT(*) FROM virtual_cards WHERE limit_sync_status = 'failed')
	`).Scan(
		&summary.Cards.Active,
		&summary.Cards.Frozen,
		&summary.Cards.Canceled,
		&summary.Cards.ApprovedAuth24h,
		&summary.Cards.DeclinedAuth24h,
		&summary.Cards.Authorized24hCents,
		&summary.Cards.FailedLimitSyncCards,
	); err != nil {
		return DashboardSummary{}, err
	}

	if err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM crypto_assets WHERE enabled = true),
			(SELECT COUNT(*) FROM crypto_wallets WHERE status = 'active'),
			(SELECT COUNT(*) FROM crypto_addresses WHERE status = 'active')
	`).Scan(
		&summary.Crypto.AssetsEnabled,
		&summary.Crypto.WalletsActive,
		&summary.Crypto.AddressesActive,
	); err != nil {
		return DashboardSummary{}, err
	}

	if err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM ledger_accounts),
			(SELECT COUNT(*) FROM ledger_journal_entries),
			(SELECT COUNT(*) FROM ledger_journal_lines)
	`).Scan(
		&summary.Ledger.Accounts,
		&summary.Ledger.JournalEntries,
		&summary.Ledger.JournalLines,
	); err != nil {
		return DashboardSummary{}, err
	}

	return summary, nil
}

func (r *Repository) Activity(ctx context.Context, limit int) ([]ActivityItem, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT kind, id, title, actor_user_id, status, amount_cents, currency, metadata, created_at
		FROM (
			SELECT 'user.registered'::text AS kind, id::text, email AS title, id::text AS actor_user_id,
				role AS status, 0::bigint AS amount_cents, ''::text AS currency,
				jsonb_build_object('full_name', full_name) AS metadata, created_at
			FROM users
			UNION ALL
			SELECT 'kyc.profile'::text, id::text, legal_name, user_id::text,
				status, 0::bigint, ''::text,
				jsonb_build_object('country', country, 'provider', provider), updated_at
			FROM kyc_profiles
			UNION ALL
			SELECT 'aml.case'::text, id::text, reason, user_id::text,
				status, 0::bigint, ''::text,
				jsonb_build_object('severity', severity, 'case_type', case_type), updated_at
			FROM aml_cases
			UNION ALL
			SELECT ('transfer.' || status)::text, id::text, COALESCE(beneficiary_name, description, 'transfer'), user_id::text,
				status, amount_cents, TRIM(currency)::text,
				jsonb_build_object(
					'from_account_id', from_account_id,
					'to_account_id', to_account_id,
					'beneficiary_iban', beneficiary_iban,
					'transfer_type', transfer_type
				), created_at
			FROM transfers
			UNION ALL
			SELECT 'savings_goal.transaction'::text, id::text, transaction_type, user_id::text,
				transaction_type, amount_cents, TRIM(currency)::text,
				jsonb_build_object('savings_goal_id', savings_goal_id, 'account_id', account_id), created_at
			FROM savings_goal_transactions
			UNION ALL
			SELECT 'card.authorization'::text, ca.id::text, COALESCE(ca.merchant_name, 'card authorization'), vc.user_id::text,
				ca.status, ca.amount_cents, TRIM(ca.currency)::text,
				jsonb_build_object('virtual_card_id', ca.virtual_card_id, 'merchant_country', ca.merchant_country), ca.created_at
			FROM card_authorizations ca
			JOIN virtual_cards vc ON vc.id = ca.virtual_card_id
			UNION ALL
			SELECT 'ledger.journal_entry'::text, id::text, event_type, ''::text,
				source_type, 0::bigint, ''::text,
				jsonb_build_object('source_id', source_id), created_at
			FROM ledger_journal_entries
			UNION ALL
			SELECT 'audit.event'::text, id::text, event_type, COALESCE(actor_user_id::text, ''),
				target_type, 0::bigint, ''::text, metadata, created_at
			FROM audit_events
			UNION ALL
			SELECT 'wallet.balance_adjustment'::text, id::text, reason, admin_user_id::text,
				direction, amount_cents, TRIM(currency)::text,
				jsonb_build_object(
					'target_user_id', target_user_id,
					'wallet_id', wallet_id,
					'account_id', account_id,
					'wallet_balance_before_cents', wallet_balance_before_cents,
					'wallet_balance_after_cents', wallet_balance_after_cents
				), created_at
			FROM wallet_balance_adjustments
			UNION ALL
			SELECT 'wallet.balance_adjustment_request'::text, id::text, reason, requester_admin_user_id::text,
				status, amount_cents, TRIM(currency)::text,
				jsonb_build_object(
					'target_user_id', target_user_id,
					'wallet_id', wallet_id,
					'account_id', account_id,
					'direction', direction,
					'reviewer_admin_user_id', reviewer_admin_user_id
				), created_at
			FROM wallet_balance_adjustment_requests
		) activity
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	items := []ActivityItem{}
	for rows.Next() {
		var item ActivityItem
		if err := rows.Scan(
			&item.Kind,
			&item.ID,
			&item.Title,
			&item.ActorUserID,
			&item.Status,
			&item.AmountCents,
			&item.Currency,
			&item.Metadata,
			&item.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) ComplianceQueue(ctx context.Context, limit int) ([]QueueItem, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT queue, id, user_id, title, status, severity, metadata, created_at, updated_at
		FROM (
			SELECT 'kyc'::text AS queue, id::text, user_id::text, legal_name AS title, status,
				''::text AS severity, jsonb_build_object('country', country, 'provider', provider) AS metadata,
				created_at, updated_at
			FROM kyc_profiles
			WHERE status IN ('pending', 'manual_review')
			UNION ALL
			SELECT 'aml'::text, id::text, user_id::text, reason, status,
				severity, jsonb_build_object('case_type', case_type, 'aml_screening_id', aml_screening_id),
				created_at, updated_at
			FROM aml_cases
			WHERE status IN ('open', 'reviewing', 'escalated')
			UNION ALL
			SELECT 'card_limit_sync'::text, id::text, user_id::text, cardholder_name, limit_sync_status,
				'medium'::text, jsonb_build_object('last4', last4, 'limit_sync_error', COALESCE(limit_sync_error, '')),
				created_at, updated_at
			FROM virtual_cards
			WHERE limit_sync_status = 'failed'
			UNION ALL
			SELECT 'wallet_adjustment_approval'::text, id::text, target_user_id::text, reason, status,
				'high'::text, jsonb_build_object(
					'requester_admin_user_id', requester_admin_user_id,
					'wallet_id', wallet_id,
					'account_id', account_id,
					'direction', direction,
					'amount_cents', amount_cents,
					'currency', currency
				), created_at, updated_at
			FROM wallet_balance_adjustment_requests
			WHERE status = 'pending'
		) queue
		ORDER BY updated_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	items := []QueueItem{}
	for rows.Next() {
		var item QueueItem
		if err := rows.Scan(
			&item.Queue,
			&item.ID,
			&item.UserID,
			&item.Title,
			&item.Status,
			&item.Severity,
			&item.Metadata,
			&item.CreatedAt,
			&item.UpdatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) SEPASettlementDashboard(ctx context.Context, status string, limit int) (SEPASettlementDashboard, error) {
	metrics, err := r.SEPASettlementMetrics(ctx)
	if err != nil {
		return SEPASettlementDashboard{}, err
	}
	transfers, err := r.ListSEPASettlementTransfers(ctx, status, limit)
	if err != nil {
		return SEPASettlementDashboard{}, err
	}
	events, err := r.ListSEPASettlementEvents(ctx, "", "", limit)
	if err != nil {
		return SEPASettlementDashboard{}, err
	}
	return SEPASettlementDashboard{
		Metrics:   metrics,
		Transfers: transfers,
		Events:    events,
	}, nil
}

func (r *Repository) SEPASettlementMetrics(ctx context.Context) (SEPASettlementMetrics, error) {
	var metrics SEPASettlementMetrics
	err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM transfers WHERE transfer_type = 'sepa' AND status = 'pending')::bigint,
			(SELECT COUNT(*) FROM transfers WHERE transfer_type = 'sepa' AND status = 'processing')::bigint,
			(SELECT COUNT(*) FROM transfers WHERE transfer_type = 'sepa' AND status = 'review_held')::bigint,
			(SELECT COUNT(*) FROM transfers WHERE transfer_type = 'sepa' AND status = 'failed')::bigint,
			(SELECT COUNT(DISTINCT transfer_id) FROM sepa_settlement_events WHERE event_type = 'retried')::bigint,
			(SELECT COUNT(*) FROM transfers WHERE transfer_type = 'sepa' AND status = 'completed' AND settled_at >= now() - interval '24 hours')::bigint
	`).Scan(
		&metrics.Pending,
		&metrics.Processing,
		&metrics.ReviewHeld,
		&metrics.Failed,
		&metrics.Retried,
		&metrics.Completed24h,
	)
	return metrics, err
}

func (r *Repository) ListSEPASettlementTransfers(ctx context.Context, status string, limit int) ([]SEPASettlementTransfer, error) {
	limit = normalizeLimit(limit)
	status = strings.ToLower(strings.TrimSpace(status))
	if status == "" {
		status = "watchlist"
	}

	rows, err := r.db.Query(ctx, `
		SELECT t.id::text, t.user_id::text, u.email, u.full_name, t.from_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.amount_cents, TRIM(t.currency)::text, t.status,
			COALESCE(t.description, ''), 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,
			(SELECT COUNT(*)::bigint FROM sepa_settlement_events retry_events WHERE retry_events.transfer_id = t.id AND retry_events.event_type = 'retried'),
			COALESCE(latest.event_type, ''), COALESCE(latest.reason, ''), latest.created_at, t.created_at
		FROM transfers t
		JOIN users u ON u.id = t.user_id
		LEFT JOIN LATERAL (
			SELECT event_type, reason, created_at
			FROM sepa_settlement_events
			WHERE transfer_id = t.id
			ORDER BY created_at DESC, id DESC
			LIMIT 1
		) latest ON true
		WHERE t.transfer_type = 'sepa'
			AND (
				$1 = 'watchlist' AND (
					t.status IN ('pending', 'processing', 'review_held', 'failed')
					OR EXISTS (
						SELECT 1 FROM sepa_settlement_events retry_filter
						WHERE retry_filter.transfer_id = t.id AND retry_filter.event_type = 'retried'
					)
				)
				OR $1 = 'retried' AND EXISTS (
					SELECT 1 FROM sepa_settlement_events retry_filter
					WHERE retry_filter.transfer_id = t.id AND retry_filter.event_type = 'retried'
				)
				OR $1 IN ('pending', 'processing', 'review_held', 'completed', 'failed') AND t.status = $1
			)
		ORDER BY
			CASE t.status
				WHEN 'failed' THEN 0
				WHEN 'processing' THEN 1
				WHEN 'review_held' THEN 2
				WHEN 'pending' THEN 3
				ELSE 4
			END,
			COALESCE(latest.created_at, t.created_at) DESC
		LIMIT $2
	`, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	transfers := []SEPASettlementTransfer{}
	for rows.Next() {
		transfer, err := scanSEPASettlementTransfer(rows)
		if err != nil {
			return nil, err
		}
		transfers = append(transfers, transfer)
	}
	return transfers, rows.Err()
}

func (r *Repository) ListSEPASettlementEvents(ctx context.Context, transferID, eventType string, limit int) ([]SEPASettlementEvent, error) {
	limit = normalizeLimit(limit)
	transferID = strings.TrimSpace(transferID)
	eventType = strings.ToLower(strings.TrimSpace(eventType))

	rows, err := r.db.Query(ctx, `
		SELECT e.id::text, e.transfer_id::text, e.event_type, COALESCE(e.status_before, ''),
			COALESCE(e.status_after, ''), e.provider, COALESCE(e.provider_reference, ''), COALESCE(e.reason, ''),
			t.status, t.user_id::text, u.email, COALESCE(t.beneficiary_name, ''), COALESCE(t.beneficiary_iban, ''),
			t.amount_cents, TRIM(t.currency)::text, e.created_at
		FROM sepa_settlement_events e
		JOIN transfers t ON t.id = e.transfer_id
		JOIN users u ON u.id = t.user_id
		WHERE (NULLIF($1, '') IS NULL OR e.transfer_id = NULLIF($1, '')::uuid)
			AND (NULLIF($2, '') IS NULL OR e.event_type = NULLIF($2, ''))
		ORDER BY e.created_at DESC, e.id DESC
		LIMIT $3
	`, transferID, eventType, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	events := []SEPASettlementEvent{}
	for rows.Next() {
		event, err := scanSEPASettlementEvent(rows)
		if err != nil {
			return nil, err
		}
		events = append(events, event)
	}
	return events, rows.Err()
}

func (r *Repository) ListUsers(ctx context.Context, query, role, kycStatus string, limit int) ([]UserListItem, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT u.id::text, u.email, u.full_name, u.role, COALESCE(k.status, 'not_started') AS kyc_status,
			(SELECT COUNT(*) FROM accounts a WHERE a.user_id = u.id)::bigint,
			(SELECT COUNT(*) FROM wallets w WHERE w.user_id = u.id)::bigint,
			(SELECT COUNT(*) FROM savings_goals sg WHERE sg.user_id = u.id)::bigint,
			(SELECT COUNT(*) FROM crypto_wallets cw WHERE cw.user_id = u.id)::bigint,
			(SELECT COUNT(*) FROM aml_cases ac WHERE ac.user_id = u.id AND ac.status IN ('open', 'reviewing', 'escalated'))::bigint,
			u.created_at
		FROM users u
		LEFT JOIN kyc_profiles k ON k.user_id = u.id
		WHERE (
				NULLIF($1, '') IS NULL
				OR lower(u.email) LIKE '%' || lower($1) || '%'
				OR lower(u.full_name) LIKE '%' || lower($1) || '%'
				OR u.id::text = $1
			)
			AND (NULLIF($2, '') IS NULL OR u.role = $2)
			AND (NULLIF($3, '') IS NULL OR COALESCE(k.status, 'not_started') = $3)
		ORDER BY u.created_at DESC
		LIMIT $4
	`, query, role, kycStatus, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	users := []UserListItem{}
	for rows.Next() {
		var user UserListItem
		if err := rows.Scan(
			&user.ID,
			&user.Email,
			&user.FullName,
			&user.Role,
			&user.KYCStatus,
			&user.AccountCount,
			&user.WalletCount,
			&user.SavingsGoalCount,
			&user.CryptoWalletCount,
			&user.OpenAMLCaseCount,
			&user.CreatedAt,
		); err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	return users, rows.Err()
}

func (r *Repository) UserDetail(ctx context.Context, userID string) (UserDetail, error) {
	user, err := r.findUser(ctx, userID)
	if err != nil {
		return UserDetail{}, err
	}

	kycProfile, err := r.findKYCProfile(ctx, userID)
	if err != nil {
		return UserDetail{}, err
	}
	accounts, err := r.listAccounts(ctx, userID, "")
	if err != nil {
		return UserDetail{}, err
	}
	wallets, err := r.listWallets(ctx, userID)
	if err != nil {
		return UserDetail{}, err
	}
	savingsGoals, err := r.listSavingsGoals(ctx, userID, "")
	if err != nil {
		return UserDetail{}, err
	}
	virtualCards, err := r.listVirtualCards(ctx, userID, "")
	if err != nil {
		return UserDetail{}, err
	}
	cryptoWallets, err := r.listCryptoWallets(ctx, userID)
	if err != nil {
		return UserDetail{}, err
	}
	amlCases, err := r.listAMLCases(ctx, userID)
	if err != nil {
		return UserDetail{}, err
	}
	auditEvents, err := r.listAuditEvents(ctx, userID, 25)
	if err != nil {
		return UserDetail{}, err
	}
	ledgerEntries, err := r.LedgerEntriesForUser(ctx, userID, 25)
	if err != nil {
		return UserDetail{}, err
	}
	walletAdjustments, err := r.ListWalletAdjustmentsForUser(ctx, userID, 25)
	if err != nil {
		return UserDetail{}, err
	}
	walletAdjustmentRequests, err := r.ListWalletAdjustmentRequestsForUser(ctx, userID, 25)
	if err != nil {
		return UserDetail{}, err
	}

	return UserDetail{
		User:                     user,
		KYCProfile:               kycProfile,
		Accounts:                 accounts,
		Wallets:                  wallets,
		SavingsGoals:             savingsGoals,
		VirtualCards:             virtualCards,
		CryptoWallets:            cryptoWallets,
		AMLCases:                 amlCases,
		RecentAuditEvents:        auditEvents,
		LedgerEntries:            ledgerEntries,
		WalletAdjustments:        walletAdjustments,
		WalletAdjustmentRequests: walletAdjustmentRequests,
	}, nil
}

func (r *Repository) AccountDetail(ctx context.Context, accountID string) (AccountDetail, error) {
	accounts, err := r.listAccounts(ctx, "", accountID)
	if err != nil {
		return AccountDetail{}, err
	}
	if len(accounts) == 0 {
		return AccountDetail{}, domain.ErrNotFound
	}
	account := accounts[0]

	owner, err := r.findUser(ctx, account.UserID)
	if err != nil {
		return AccountDetail{}, err
	}

	var wallet *domain.Wallet
	if account.WalletID != "" {
		found, err := r.findWallet(ctx, account.WalletID)
		if err != nil {
			return AccountDetail{}, err
		}
		wallet = &found
	}
	savingsGoals, err := r.listSavingsGoals(ctx, "", account.ID)
	if err != nil {
		return AccountDetail{}, err
	}
	virtualCards, err := r.listVirtualCards(ctx, "", account.ID)
	if err != nil {
		return AccountDetail{}, err
	}
	transfers, err := r.listTransfersForAccount(ctx, account.ID, 50)
	if err != nil {
		return AccountDetail{}, err
	}
	ledgerEntries, err := r.LedgerEntriesForAccount(ctx, account.ID, 50)
	if err != nil {
		return AccountDetail{}, err
	}

	return AccountDetail{
		Account:       account,
		Owner:         owner,
		Wallet:        wallet,
		SavingsGoals:  savingsGoals,
		VirtualCards:  virtualCards,
		Transfers:     transfers,
		LedgerEntries: ledgerEntries,
	}, nil
}

func (r *Repository) LedgerEntriesForUser(ctx context.Context, userID string, limit int) ([]domain.LedgerJournalEntry, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT DISTINCT je.id::text, je.event_type, je.source_type, je.source_id, COALESCE(je.idempotency_key, ''),
			COALESCE(je.description, ''), je.metadata, je.created_at
		FROM ledger_journal_entries je
		JOIN ledger_journal_lines jl ON jl.journal_entry_id = je.id
		JOIN ledger_accounts la ON la.id = jl.ledger_account_id
		WHERE la.owner_user_id = $1::uuid
		ORDER BY je.created_at DESC
		LIMIT $2
	`, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return r.scanLedgerEntries(ctx, rows)
}

func (r *Repository) LedgerEntriesForAccount(ctx context.Context, accountID string, limit int) ([]domain.LedgerJournalEntry, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT DISTINCT je.id::text, je.event_type, je.source_type, je.source_id, COALESCE(je.idempotency_key, ''),
			COALESCE(je.description, ''), je.metadata, je.created_at
		FROM ledger_journal_entries je
		JOIN ledger_journal_lines jl ON jl.journal_entry_id = je.id
		JOIN ledger_accounts la ON la.id = jl.ledger_account_id
		WHERE la.reference_type = 'account' AND la.reference_id = $1
		ORDER BY je.created_at DESC
		LIMIT $2
	`, accountID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return r.scanLedgerEntries(ctx, rows)
}

func (r *Repository) moneyMetrics(ctx context.Context) ([]MoneyMetric, error) {
	rows, err := r.db.Query(ctx, `
		WITH currency_set AS (
			SELECT TRIM(code)::text AS currency FROM currencies WHERE enabled = true
			UNION SELECT DISTINCT TRIM(currency)::text FROM accounts
			UNION SELECT DISTINCT TRIM(currency)::text FROM wallet_balances
			UNION SELECT DISTINCT TRIM(currency)::text FROM savings_goals
			UNION SELECT DISTINCT TRIM(currency)::text FROM transfers
		),
		account_totals AS (
			SELECT TRIM(currency)::text AS currency, COALESCE(SUM(balance_cents), 0)::bigint AS amount
			FROM accounts
			GROUP BY TRIM(currency)::text
		),
		wallet_totals AS (
			SELECT TRIM(currency)::text AS currency,
				COALESCE(SUM(available_balance_cents), 0)::bigint AS available,
				COALESCE(SUM(reserved_balance_cents), 0)::bigint AS reserved
			FROM wallet_balances
			GROUP BY TRIM(currency)::text
		),
		savings_totals AS (
			SELECT TRIM(currency)::text AS currency, COALESCE(SUM(current_amount_cents), 0)::bigint AS amount
			FROM savings_goals
			WHERE status <> 'closed'
			GROUP BY TRIM(currency)::text
		),
		transfer_24 AS (
			SELECT TRIM(currency)::text AS currency, COALESCE(SUM(amount_cents), 0)::bigint AS amount, COUNT(*)::bigint AS count
			FROM transfers
			WHERE status = 'completed' AND created_at >= now() - interval '24 hours'
			GROUP BY TRIM(currency)::text
		)
		SELECT c.currency,
			COALESCE(a.amount, 0),
			COALESCE(w.available, 0),
			COALESCE(w.reserved, 0),
			COALESCE(s.amount, 0),
			COALESCE(t.amount, 0),
			COALESCE(t.count, 0)
		FROM currency_set c
		LEFT JOIN account_totals a ON a.currency = c.currency
		LEFT JOIN wallet_totals w ON w.currency = c.currency
		LEFT JOIN savings_totals s ON s.currency = c.currency
		LEFT JOIN transfer_24 t ON t.currency = c.currency
		ORDER BY c.currency
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	metrics := []MoneyMetric{}
	for rows.Next() {
		var metric MoneyMetric
		if err := rows.Scan(
			&metric.Currency,
			&metric.AccountBalanceCents,
			&metric.WalletAvailableBalanceCents,
			&metric.WalletReservedBalanceCents,
			&metric.SavingsReservedCents,
			&metric.TransferVolume24hCents,
			&metric.TransferCount24h,
		); err != nil {
			return nil, err
		}
		metrics = append(metrics, metric)
	}
	return metrics, rows.Err()
}

func (r *Repository) findUser(ctx context.Context, userID string) (domain.User, error) {
	row := r.db.QueryRow(ctx, `
		SELECT id::text, email, full_name, role, password_hash, created_at
		FROM users
		WHERE id = $1
	`, userID)

	var user domain.User
	if err := row.Scan(&user.ID, &user.Email, &user.FullName, &user.Role, &user.PasswordHash, &user.CreatedAt); err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return domain.User{}, domain.ErrNotFound
		}
		return domain.User{}, err
	}
	return user, nil
}

func (r *Repository) findKYCProfile(ctx context.Context, userID string) (*domain.KYCProfile, error) {
	row := r.db.QueryRow(ctx, `
		SELECT id::text, user_id::text, legal_name, date_of_birth::text, country, address_line1, city, postal_code,
			status, provider, COALESCE(external_verification_id, ''), COALESCE(rejection_reason, ''),
			submitted_at, verified_at, created_at, updated_at
		FROM kyc_profiles
		WHERE user_id = $1
	`, userID)

	profile, err := scanKYCProfile(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &profile, nil
}

func (r *Repository) listAccounts(ctx context.Context, userID, accountID string) ([]domain.Account, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(wallet_id::text, ''), account_number, iban, bic,
			bank_provider, external_account_id, currency, balance_cents, status, created_at, updated_at
		FROM accounts
		WHERE (NULLIF($1, '') IS NULL OR user_id = NULLIF($1, '')::uuid)
			AND (NULLIF($2, '') IS NULL OR id = NULLIF($2, '')::uuid)
		ORDER BY created_at DESC
	`, userID, accountID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	accounts := []domain.Account{}
	for rows.Next() {
		account, err := scanAccount(rows)
		if err != nil {
			return nil, err
		}
		accounts = append(accounts, account)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return r.attachAccountRoutingCodes(ctx, accounts)
}

func (r *Repository) attachAccountRoutingCodes(ctx context.Context, accounts []domain.Account) ([]domain.Account, error) {
	if len(accounts) == 0 {
		return accounts, nil
	}
	ids := make([]string, 0, len(accounts))
	for _, account := range accounts {
		ids = append(ids, account.ID)
	}

	rows, err := r.db.Query(ctx, `
		SELECT id::text, account_id::text, code_type, country::text, network, code, status, created_at, updated_at
		FROM account_routing_codes
		WHERE account_id::text = ANY($1::text[]) AND status = 'active'
		ORDER BY country, network, code_type, code
	`, ids)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	byAccount := map[string][]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 = "account"
		byAccount[code.OwnerID] = append(byAccount[code.OwnerID], code)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	for i := range accounts {
		accounts[i].RoutingCodes = byAccount[accounts[i].ID]
	}
	return accounts, nil
}

func (r *Repository) listWallets(ctx context.Context, userID string) ([]domain.Wallet, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, name, status, created_at, updated_at
		FROM wallets
		WHERE user_id = $1
		ORDER BY created_at DESC
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	wallets := []domain.Wallet{}
	for rows.Next() {
		var wallet domain.Wallet
		if err := rows.Scan(&wallet.ID, &wallet.UserID, &wallet.Name, &wallet.Status, &wallet.CreatedAt, &wallet.UpdatedAt); err != nil {
			return nil, err
		}
		wallets = append(wallets, wallet)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return nil, err
	}
	rows.Close()
	for i := range wallets {
		balances, err := r.listWalletBalances(ctx, wallets[i].ID)
		if err != nil {
			return nil, err
		}
		wallets[i].Balances = balances
	}
	return wallets, nil
}

func (r *Repository) findWallet(ctx context.Context, walletID string) (domain.Wallet, error) {
	row := r.db.QueryRow(ctx, `
		SELECT id::text, user_id::text, name, status, created_at, updated_at
		FROM wallets
		WHERE id = $1
	`, walletID)

	var wallet domain.Wallet
	if err := row.Scan(&wallet.ID, &wallet.UserID, &wallet.Name, &wallet.Status, &wallet.CreatedAt, &wallet.UpdatedAt); err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return domain.Wallet{}, domain.ErrNotFound
		}
		return domain.Wallet{}, err
	}
	balances, err := r.listWalletBalances(ctx, wallet.ID)
	if err != nil {
		return domain.Wallet{}, err
	}
	wallet.Balances = balances
	return wallet, nil
}

func (r *Repository) listWalletBalances(ctx context.Context, walletID string) ([]domain.WalletBalance, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, wallet_id::text, currency, available_balance_cents, reserved_balance_cents, created_at, updated_at
		FROM wallet_balances
		WHERE wallet_id = $1
		ORDER BY currency
	`, walletID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	balances := []domain.WalletBalance{}
	for rows.Next() {
		var balance domain.WalletBalance
		if err := rows.Scan(
			&balance.ID,
			&balance.WalletID,
			&balance.Currency,
			&balance.AvailableBalanceCents,
			&balance.ReservedBalanceCents,
			&balance.CreatedAt,
			&balance.UpdatedAt,
		); err != nil {
			return nil, err
		}
		balances = append(balances, balance)
	}
	return balances, rows.Err()
}

func (r *Repository) listSavingsGoals(ctx context.Context, userID, accountID string) ([]domain.SavingsGoal, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, account_id::text, name, currency, target_amount_cents,
			current_amount_cents, status, COALESCE(target_date::text, ''), completed_at, closed_at, created_at, updated_at
		FROM savings_goals
		WHERE (NULLIF($1, '') IS NULL OR user_id = NULLIF($1, '')::uuid)
			AND (NULLIF($2, '') IS NULL OR account_id = NULLIF($2, '')::uuid)
		ORDER BY created_at DESC
	`, userID, accountID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	goals := []domain.SavingsGoal{}
	for rows.Next() {
		goal, err := scanSavingsGoal(rows)
		if err != nil {
			return nil, err
		}
		goals = append(goals, goal)
	}
	return goals, rows.Err()
}

func (r *Repository) listVirtualCards(ctx context.Context, userID, accountID string) ([]domain.VirtualCard, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''), status, created_at, updated_at
		FROM virtual_cards
		WHERE (NULLIF($1, '') IS NULL OR user_id = NULLIF($1, '')::uuid)
			AND (NULLIF($2, '') IS NULL OR account_id = NULLIF($2, '')::uuid)
		ORDER BY created_at DESC
	`, userID, accountID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	cards := []domain.VirtualCard{}
	for rows.Next() {
		card, err := scanVirtualCard(rows)
		if err != nil {
			return nil, err
		}
		cards = append(cards, card)
	}
	return cards, rows.Err()
}

func (r *Repository) listCryptoWallets(ctx context.Context, userID string) ([]domain.CryptoWallet, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, name, custody_provider, external_wallet_id, status, created_at, updated_at
		FROM crypto_wallets
		WHERE user_id = $1
		ORDER BY created_at DESC
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	wallets := []domain.CryptoWallet{}
	for rows.Next() {
		var wallet domain.CryptoWallet
		if err := rows.Scan(&wallet.ID, &wallet.UserID, &wallet.Name, &wallet.CustodyProvider, &wallet.ExternalWalletID, &wallet.Status, &wallet.CreatedAt, &wallet.UpdatedAt); err != nil {
			return nil, err
		}
		wallets = append(wallets, wallet)
	}
	return wallets, rows.Err()
}

func (r *Repository) listAMLCases(ctx context.Context, userID string) ([]domain.AMLCase, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(aml_screening_id::text, ''), case_type, status, severity,
			reason, COALESCE(resolution_note, ''), created_at, updated_at
		FROM aml_cases
		WHERE user_id = $1
		ORDER BY updated_at DESC
		LIMIT 50
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	cases := []domain.AMLCase{}
	for rows.Next() {
		var amlCase domain.AMLCase
		if err := rows.Scan(
			&amlCase.ID,
			&amlCase.UserID,
			&amlCase.AMLScreeningID,
			&amlCase.CaseType,
			&amlCase.Status,
			&amlCase.Severity,
			&amlCase.Reason,
			&amlCase.ResolutionNote,
			&amlCase.CreatedAt,
			&amlCase.UpdatedAt,
		); err != nil {
			return nil, err
		}
		cases = append(cases, amlCase)
	}
	return cases, rows.Err()
}

func (r *Repository) listAuditEvents(ctx context.Context, userID string, limit int) ([]domain.AuditEvent, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, COALESCE(actor_user_id::text, ''), event_type, target_type, target_id, metadata, remote_ip, user_agent, created_at
		FROM audit_events
		WHERE actor_user_id = $1::uuid
		ORDER BY created_at DESC
		LIMIT $2
	`, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	events := []domain.AuditEvent{}
	for rows.Next() {
		var event domain.AuditEvent
		if err := rows.Scan(&event.ID, &event.ActorUserID, &event.EventType, &event.TargetType, &event.TargetID, &event.Metadata, &event.RemoteIP, &event.UserAgent, &event.CreatedAt); err != nil {
			return nil, err
		}
		events = append(events, event)
	}
	return events, rows.Err()
}

func (r *Repository) listTransfersForAccount(ctx context.Context, accountID string, limit int) ([]domain.Transfer, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(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 from_account_id = $1::uuid OR to_account_id = $1::uuid
		ORDER BY created_at DESC
		LIMIT $2
	`, accountID, 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) scanLedgerEntries(ctx context.Context, rows pgx.Rows) ([]domain.LedgerJournalEntry, error) {
	entries := []domain.LedgerJournalEntry{}
	for rows.Next() {
		entry, err := scanLedgerEntry(rows)
		if err != nil {
			return nil, err
		}
		entry.Lines, err = r.listLedgerLines(ctx, entry.ID)
		if err != nil {
			return nil, err
		}
		entries = append(entries, entry)
	}
	return entries, rows.Err()
}

func (r *Repository) listLedgerLines(ctx context.Context, entryID string) ([]domain.LedgerJournalLine, error) {
	rows, err := r.db.Query(ctx, `
		SELECT jl.id::text, jl.journal_entry_id::text, jl.ledger_account_id::text,
			la.reference_type, la.reference_id, jl.direction, jl.amount_cents, jl.currency, jl.created_at
		FROM ledger_journal_lines jl
		JOIN ledger_accounts la ON la.id = jl.ledger_account_id
		WHERE jl.journal_entry_id = $1
		ORDER BY jl.created_at, jl.id
	`, entryID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	lines := []domain.LedgerJournalLine{}
	for rows.Next() {
		var line domain.LedgerJournalLine
		if err := rows.Scan(
			&line.ID,
			&line.JournalEntryID,
			&line.LedgerAccountID,
			&line.LedgerAccountReferenceType,
			&line.LedgerAccountReferenceID,
			&line.Direction,
			&line.AmountCents,
			&line.Currency,
			&line.CreatedAt,
		); err != nil {
			return nil, err
		}
		lines = append(lines, line)
	}
	return lines, rows.Err()
}

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

func scanKYCProfile(row scanner) (domain.KYCProfile, error) {
	var profile domain.KYCProfile
	var submittedAt sql.NullTime
	var verifiedAt sql.NullTime
	err := row.Scan(
		&profile.ID,
		&profile.UserID,
		&profile.LegalName,
		&profile.DateOfBirth,
		&profile.Country,
		&profile.AddressLine1,
		&profile.City,
		&profile.PostalCode,
		&profile.Status,
		&profile.Provider,
		&profile.ExternalVerificationID,
		&profile.RejectionReason,
		&submittedAt,
		&verifiedAt,
		&profile.CreatedAt,
		&profile.UpdatedAt,
	)
	if submittedAt.Valid {
		profile.SubmittedAt = &submittedAt.Time
	}
	if verifiedAt.Valid {
		profile.VerifiedAt = &verifiedAt.Time
	}
	return profile, err
}

func scanAccount(row scanner) (domain.Account, error) {
	var account domain.Account
	err := row.Scan(
		&account.ID,
		&account.UserID,
		&account.WalletID,
		&account.AccountNumber,
		&account.IBAN,
		&account.BIC,
		&account.BankProvider,
		&account.ExternalAccountID,
		&account.Currency,
		&account.BalanceCents,
		&account.Status,
		&account.CreatedAt,
		&account.UpdatedAt,
	)
	return account, err
}

func scanSavingsGoal(row scanner) (domain.SavingsGoal, error) {
	var goal domain.SavingsGoal
	var completedAt sql.NullTime
	var closedAt sql.NullTime
	err := row.Scan(
		&goal.ID,
		&goal.UserID,
		&goal.AccountID,
		&goal.Name,
		&goal.Currency,
		&goal.TargetAmountCents,
		&goal.CurrentAmountCents,
		&goal.Status,
		&goal.TargetDate,
		&completedAt,
		&closedAt,
		&goal.CreatedAt,
		&goal.UpdatedAt,
	)
	if completedAt.Valid {
		goal.CompletedAt = &completedAt.Time
	}
	if closedAt.Valid {
		goal.ClosedAt = &closedAt.Time
	}
	return goal, err
}

func scanVirtualCard(row scanner) (domain.VirtualCard, error) {
	var card domain.VirtualCard
	var syncedAt sql.NullTime
	err := row.Scan(
		&card.ID,
		&card.UserID,
		&card.AccountID,
		&card.ExternalCardID,
		&card.Nickname,
		&card.CardholderName,
		&card.Network,
		&card.CardType,
		&card.Last4,
		&card.ExpMonth,
		&card.ExpYear,
		&card.SpendingLimitCents,
		&card.LimitSyncStatus,
		&syncedAt,
		&card.LimitSyncError,
		&card.Status,
		&card.CreatedAt,
		&card.UpdatedAt,
	)
	if syncedAt.Valid {
		card.LimitSyncedAt = &syncedAt.Time
	}
	return card, err
}

func scanTransfer(row scanner) (domain.Transfer, error) {
	var transfer domain.Transfer
	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,
		&transfer.IdempotencyKey,
		&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 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 scanSEPASettlementTransfer(row scanner) (SEPASettlementTransfer, error) {
	var transfer SEPASettlementTransfer
	var nextAttemptAt sql.NullTime
	var settledAt sql.NullTime
	var latestEventAt sql.NullTime
	var beneficiaryRoutingCodes string
	err := row.Scan(
		&transfer.ID,
		&transfer.UserID,
		&transfer.UserEmail,
		&transfer.UserFullName,
		&transfer.FromAccountID,
		&transfer.BeneficiaryID,
		&transfer.BeneficiaryName,
		&transfer.BeneficiaryIBAN,
		&transfer.BeneficiaryBIC,
		&beneficiaryRoutingCodes,
		&transfer.PaymentReference,
		&transfer.AmountCents,
		&transfer.Currency,
		&transfer.Status,
		&transfer.Description,
		&transfer.SettlementAttempts,
		&transfer.SettlementProvider,
		&transfer.SettlementReference,
		&transfer.SettlementError,
		&nextAttemptAt,
		&settledAt,
		&transfer.PaymentReasonCode,
		&transfer.PaymentReasonCategory,
		&transfer.PaymentReasonDescription,
		&transfer.ManualReviewRequired,
		&transfer.RetriedEvents,
		&transfer.LatestEventType,
		&transfer.LatestEventReason,
		&latestEventAt,
		&transfer.CreatedAt,
	)
	if err != nil {
		return transfer, err
	}
	if nextAttemptAt.Valid {
		transfer.SettlementNextAttemptAt = &nextAttemptAt.Time
	}
	if settledAt.Valid {
		transfer.SettledAt = &settledAt.Time
	}
	if latestEventAt.Valid {
		transfer.LatestEventAt = &latestEventAt.Time
	}
	if beneficiaryRoutingCodes != "" {
		if err := json.Unmarshal([]byte(beneficiaryRoutingCodes), &transfer.BeneficiaryRoutingCodes); err != nil {
			return transfer, err
		}
	}
	return transfer, nil
}

func scanSEPASettlementEvent(row scanner) (SEPASettlementEvent, error) {
	var event SEPASettlementEvent
	err := row.Scan(
		&event.ID,
		&event.TransferID,
		&event.EventType,
		&event.StatusBefore,
		&event.StatusAfter,
		&event.Provider,
		&event.ProviderReference,
		&event.Reason,
		&event.TransferStatus,
		&event.UserID,
		&event.UserEmail,
		&event.BeneficiaryName,
		&event.BeneficiaryIBAN,
		&event.AmountCents,
		&event.Currency,
		&event.CreatedAt,
	)
	return event, err
}

func scanLedgerEntry(row scanner) (domain.LedgerJournalEntry, error) {
	var entry domain.LedgerJournalEntry
	err := row.Scan(
		&entry.ID,
		&entry.EventType,
		&entry.SourceType,
		&entry.SourceID,
		&entry.IdempotencyKey,
		&entry.Description,
		&entry.Metadata,
		&entry.CreatedAt,
	)
	return entry, err
}

func normalizeLimit(limit int) int {
	if limit <= 0 || limit > 100 {
		return 50
	}
	return limit
}