package admin

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

	"github.com/jackc/pgx/v5"

	"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"
)

type PaymentReviewCase struct {
	ID                       string     `json:"id"`
	TransferID               string     `json:"transfer_id"`
	Status                   string     `json:"status"`
	ReasonCode               string     `json:"reason_code"`
	Reason                   string     `json:"reason"`
	OpenedByType             string     `json:"opened_by_type"`
	OpenedByUserID           string     `json:"opened_by_user_id,omitempty"`
	AssignedAdminUserID      string     `json:"assigned_admin_user_id,omitempty"`
	DecidedByAdminUserID     string     `json:"decided_by_admin_user_id,omitempty"`
	DecisionNote             string     `json:"decision_note,omitempty"`
	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"`
	PaymentReference         string     `json:"payment_reference,omitempty"`
	TransferType             string     `json:"transfer_type"`
	TransferStatus           string     `json:"transfer_status"`
	AmountCents              int64      `json:"amount_cents"`
	Currency                 string     `json:"currency"`
	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"`
	SettlementAttempts       int        `json:"settlement_attempts"`
	SettlementError          string     `json:"settlement_error,omitempty"`
	SettlementNextAttemptAt  *time.Time `json:"settlement_next_attempt_at,omitempty"`
	OpenedAt                 time.Time  `json:"opened_at"`
	DecidedAt                *time.Time `json:"decided_at,omitempty"`
	CreatedAt                time.Time  `json:"created_at"`
	UpdatedAt                time.Time  `json:"updated_at"`
	TransferCreatedAt        time.Time  `json:"transfer_created_at"`
}

type PaymentHoldParams struct {
	TransferID   string
	ActorAdminID string
	ReasonCode   string
	Note         string
}

type PaymentReviewDecisionParams struct {
	ReviewID     string
	ActorAdminID string
	Action       string
	DecisionNote string
}

type paymentReviewRecord struct {
	Case     PaymentReviewCase
	WalletID string
}

func (r *Repository) ListPaymentReviewCases(ctx context.Context, status string, limit int) ([]PaymentReviewCase, error) {
	status = strings.ToLower(strings.TrimSpace(status))
	if status == "" {
		status = "open"
	}
	if status != "all" && status != "open" && status != "released" && status != "rejected" && status != "canceled" {
		return nil, fmt.Errorf("%w: invalid payment review status", domain.ErrValidation)
	}
	limit = normalizeLimit(limit)

	rows, err := r.db.Query(ctx, paymentReviewCaseSelect+`
		WHERE ($1 = 'all' OR prc.status = $1)
		ORDER BY
			CASE WHEN prc.status = 'open' THEN 0 ELSE 1 END,
			prc.updated_at DESC,
			prc.created_at DESC
		LIMIT $2
	`, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	cases := []PaymentReviewCase{}
	for rows.Next() {
		record, err := scanPaymentReviewRecord(rows)
		if err != nil {
			return nil, err
		}
		cases = append(cases, record.Case)
	}
	return cases, rows.Err()
}

func (r *Repository) HoldPaymentForReview(ctx context.Context, params PaymentHoldParams) (PaymentReviewCase, error) {
	var review PaymentReviewCase
	var err error
	for attempt := 0; attempt < 3; attempt++ {
		review, err = r.holdPaymentForReviewOnce(ctx, params)
		if !isAdjustmentRetryable(err) {
			return review, err
		}
		time.Sleep(time.Duration(attempt+1) * 25 * time.Millisecond)
	}
	return review, err
}

func (r *Repository) holdPaymentForReviewOnce(ctx context.Context, params PaymentHoldParams) (PaymentReviewCase, error) {
	if err := domain.ValidateUUID("transfer_id", strings.TrimSpace(params.TransferID)); err != nil {
		return PaymentReviewCase{}, err
	}
	note, err := normalizePaymentReviewNote(params.Note, false)
	if err != nil {
		return PaymentReviewCase{}, err
	}
	providerCode := strings.ToUpper(strings.TrimSpace(params.ReasonCode))
	if providerCode == "" {
		providerCode = payments.ProviderCodeManualReviewRequired
	}

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

	if err := requireLiveAdmin(ctx, tx, params.ActorAdminID); err != nil {
		return PaymentReviewCase{}, err
	}
	transfer, err := selectReviewTransfer(ctx, tx, params.TransferID)
	if err != nil {
		return PaymentReviewCase{}, err
	}
	if transfer.TransferType != "sepa" {
		return PaymentReviewCase{}, fmt.Errorf("%w: only SEPA transfers can be held for payment review", domain.ErrValidation)
	}
	if transfer.TransferStatus != payments.StatusPending && transfer.TransferStatus != payments.StatusReviewHeld {
		return PaymentReviewCase{}, fmt.Errorf("%w: only pending payments can be held for review", domain.ErrValidation)
	}
	reason, err := payments.MapReason(ctx, tx, payments.ProviderAdmin, providerCode)
	if err != nil {
		return PaymentReviewCase{}, err
	}

	if transfer.TransferStatus == payments.StatusPending {
		if err := payments.RequireTransition(payments.StatusPending, payments.StatusReviewHeld); err != nil {
			return PaymentReviewCase{}, err
		}
		if _, err := tx.Exec(ctx, `
			UPDATE transfers
			SET status = 'review_held',
				payment_reason_code = $2,
				payment_reason_category = $3,
				payment_reason_description = $4,
				manual_review_required = true,
				settlement_next_attempt_at = NULL
			WHERE id = $1
		`, transfer.TransferID, reason.InternalCode, reason.Category, reason.Description); err != nil {
			return PaymentReviewCase{}, err
		}
		if err := insertPaymentReviewSettlementEvent(ctx, tx, transfer.TransferID, "review_held", payments.StatusPending, payments.StatusReviewHeld, payments.ProviderAdmin, "", reason.Description); err != nil {
			return PaymentReviewCase{}, err
		}
		if err := payments.RecordStatusEvent(ctx, tx, payments.StatusEventParams{
			TransferID:   transfer.TransferID,
			FromStatus:   payments.StatusPending,
			ToStatus:     payments.StatusReviewHeld,
			Provider:     payments.ProviderAdmin,
			ProviderCode: providerCode,
			Reason:       reason,
			Note:         note,
			ActorType:    payments.ActorAdmin,
			ActorUserID:  params.ActorAdminID,
		}); err != nil {
			return PaymentReviewCase{}, err
		}
	}

	if err := upsertPaymentReviewCase(ctx, tx, transfer.TransferID, reason.InternalCode, noteOrDefault(note, reason.Description), "admin", params.ActorAdminID); err != nil {
		return PaymentReviewCase{}, err
	}
	record, err := findPaymentReviewCaseByTransfer(ctx, tx, transfer.TransferID, false)
	if err != nil {
		return PaymentReviewCase{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return PaymentReviewCase{}, err
	}
	return record.Case, nil
}

func (r *Repository) DecidePaymentReview(ctx context.Context, params PaymentReviewDecisionParams) (PaymentReviewCase, error) {
	var review PaymentReviewCase
	var err error
	for attempt := 0; attempt < 3; attempt++ {
		review, err = r.decidePaymentReviewOnce(ctx, params)
		if !isAdjustmentRetryable(err) {
			return review, err
		}
		time.Sleep(time.Duration(attempt+1) * 25 * time.Millisecond)
	}
	return review, err
}

func (r *Repository) decidePaymentReviewOnce(ctx context.Context, params PaymentReviewDecisionParams) (PaymentReviewCase, error) {
	if err := domain.ValidateUUID("id", strings.TrimSpace(params.ReviewID)); err != nil {
		return PaymentReviewCase{}, err
	}
	params.Action = strings.ToLower(strings.TrimSpace(params.Action))
	if params.Action != "release" && params.Action != "reject" {
		return PaymentReviewCase{}, fmt.Errorf("%w: action must be release or reject", domain.ErrValidation)
	}
	note, err := normalizePaymentReviewNote(params.DecisionNote, params.Action == "reject")
	if err != nil {
		return PaymentReviewCase{}, err
	}

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

	if err := requireLiveAdmin(ctx, tx, params.ActorAdminID); err != nil {
		return PaymentReviewCase{}, err
	}
	record, err := findPaymentReviewCase(ctx, tx, params.ReviewID, true)
	if err != nil {
		return PaymentReviewCase{}, err
	}
	if record.Case.Status != "open" {
		return PaymentReviewCase{}, fmt.Errorf("%w: only open payment reviews can be decided", domain.ErrValidation)
	}
	if record.Case.TransferStatus != payments.StatusReviewHeld {
		return PaymentReviewCase{}, fmt.Errorf("%w: transfer must be review_held", domain.ErrValidation)
	}

	switch params.Action {
	case "release":
		if err := r.releasePaymentReview(ctx, tx, record, params.ActorAdminID, note); err != nil {
			return PaymentReviewCase{}, err
		}
	case "reject":
		if err := r.rejectPaymentReview(ctx, tx, record, params.ActorAdminID, note); err != nil {
			return PaymentReviewCase{}, err
		}
	}

	updated, err := findPaymentReviewCase(ctx, tx, params.ReviewID, false)
	if err != nil {
		return PaymentReviewCase{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return PaymentReviewCase{}, err
	}
	return updated.Case, nil
}

func (r *Repository) releasePaymentReview(ctx context.Context, tx pgx.Tx, record paymentReviewRecord, adminID, note string) error {
	if err := r.screenBeneficiaryBeforeRelease(ctx, tx, record); err != nil {
		return err
	}
	if err := payments.RequireTransition(payments.StatusReviewHeld, payments.StatusPending); err != nil {
		return err
	}
	reason, err := payments.MapReason(ctx, tx, payments.ProviderAdmin, payments.ProviderCodeManualReviewReleased)
	if err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
		UPDATE transfers
		SET status = 'pending',
			payment_reason_code = $2,
			payment_reason_category = $3,
			payment_reason_description = $4,
			manual_review_required = false,
			settlement_next_attempt_at = NULL
		WHERE id = $1
	`, record.Case.TransferID, reason.InternalCode, reason.Category, reason.Description); err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
		UPDATE payment_review_cases
		SET status = 'released',
			assigned_admin_user_id = COALESCE(assigned_admin_user_id, $2),
			decided_by_admin_user_id = $2,
			decision_note = NULLIF($3, ''),
			decided_at = now()
		WHERE id = $1
	`, record.Case.ID, adminID, note); err != nil {
		return err
	}
	if err := insertPaymentReviewSettlementEvent(ctx, tx, record.Case.TransferID, "review_released", payments.StatusReviewHeld, payments.StatusPending, payments.ProviderAdmin, "", reason.Description); err != nil {
		return err
	}
	if err := payments.RecordStatusEvent(ctx, tx, payments.StatusEventParams{
		TransferID:   record.Case.TransferID,
		FromStatus:   payments.StatusReviewHeld,
		ToStatus:     payments.StatusPending,
		Provider:     payments.ProviderAdmin,
		ProviderCode: payments.ProviderCodeManualReviewReleased,
		Reason:       reason,
		Note:         note,
		ActorType:    payments.ActorAdmin,
		ActorUserID:  adminID,
	}); err != nil {
		return err
	}
	return payments.RecordNotification(ctx, tx, payments.NotificationParams{
		UserID:     record.Case.UserID,
		TransferID: record.Case.TransferID,
		Type:       "payment.review_released",
		Title:      "SEPA payment released",
		Message:    "Your SEPA payment review has been released for settlement.",
		Payload: map[string]any{
			"transfer_id": record.Case.TransferID,
			"review_id":   record.Case.ID,
		},
	})
}

func (r *Repository) rejectPaymentReview(ctx context.Context, tx pgx.Tx, record paymentReviewRecord, adminID, note string) error {
	if err := payments.RequireTransition(payments.StatusReviewHeld, payments.StatusRejected); err != nil {
		return err
	}
	reason, err := payments.MapReason(ctx, tx, payments.ProviderAdmin, payments.ProviderCodeManualReviewRejected)
	if err != nil {
		return err
	}
	if err := refundPaymentReviewTransfer(ctx, tx, record); err != nil {
		return err
	}
	if err := r.postPaymentReviewRejectRefundLedger(ctx, tx, record, note); err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
		UPDATE transfers
		SET status = 'rejected',
			settlement_error = NULLIF($2, ''),
			settlement_next_attempt_at = NULL,
			payment_reason_code = $3,
			payment_reason_category = $4,
			payment_reason_description = $5,
			manual_review_required = false
		WHERE id = $1
	`, record.Case.TransferID, note, reason.InternalCode, reason.Category, reason.Description); err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
		UPDATE payment_review_cases
		SET status = 'rejected',
			assigned_admin_user_id = COALESCE(assigned_admin_user_id, $2),
			decided_by_admin_user_id = $2,
			decision_note = $3,
			decided_at = now()
		WHERE id = $1
	`, record.Case.ID, adminID, note); err != nil {
		return err
	}
	if err := insertPaymentReviewSettlementEvent(ctx, tx, record.Case.TransferID, "review_rejected", payments.StatusReviewHeld, payments.StatusRejected, payments.ProviderAdmin, "", reason.Description); err != nil {
		return err
	}
	if err := payments.RecordStatusEvent(ctx, tx, payments.StatusEventParams{
		TransferID:   record.Case.TransferID,
		FromStatus:   payments.StatusReviewHeld,
		ToStatus:     payments.StatusRejected,
		Provider:     payments.ProviderAdmin,
		ProviderCode: payments.ProviderCodeManualReviewRejected,
		Reason:       reason,
		Note:         note,
		ActorType:    payments.ActorAdmin,
		ActorUserID:  adminID,
	}); err != nil {
		return err
	}
	return payments.RecordNotification(ctx, tx, payments.NotificationParams{
		UserID:     record.Case.UserID,
		TransferID: record.Case.TransferID,
		Type:       "payment.review_rejected",
		Title:      "SEPA payment rejected",
		Message:    "Your SEPA payment was rejected after review and funds were returned.",
		Payload: map[string]any{
			"transfer_id": record.Case.TransferID,
			"review_id":   record.Case.ID,
			"reason":      note,
		},
	})
}

func (r *Repository) screenBeneficiaryBeforeRelease(ctx context.Context, tx pgx.Tx, record paymentReviewRecord) error {
	if r.sanctions == nil || strings.TrimSpace(record.Case.BeneficiaryName) == "" {
		return nil
	}
	result, err := r.sanctions.ScreenPerson(ctx, sanctions.ScreenPersonParams{
		UserID:    record.Case.UserID,
		LegalName: record.Case.BeneficiaryName,
		Country:   beneficiaryCountry(record.Case.BeneficiaryIBAN),
	})
	if err != nil {
		return err
	}
	if err := recordReleaseBeneficiaryScreening(ctx, tx, record, result); err != nil {
		return err
	}
	if result.Status == "hit" || result.Status == "review" || result.Matched {
		return fmt.Errorf("%w: beneficiary sanctions screening is not clear", domain.ErrValidation)
	}
	return nil
}

func recordReleaseBeneficiaryScreening(ctx context.Context, tx pgx.Tx, record paymentReviewRecord, 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)
	`, record.Case.TransferID, record.Case.BeneficiaryID, record.Case.UserID, result.Provider,
		result.ExternalScreeningID, result.Status, result.RiskScore, result.Matched, string(matchDetails))
	return err
}

func beneficiaryCountry(iban string) string {
	iban = strings.TrimSpace(iban)
	if len(iban) < 2 {
		return ""
	}
	return strings.ToUpper(iban[:2])
}

func refundPaymentReviewTransfer(ctx context.Context, tx pgx.Tx, record paymentReviewRecord) error {
	if _, err := tx.Exec(ctx, `
		UPDATE accounts
		SET balance_cents = balance_cents + $1, updated_at = now()
		WHERE id = $2
	`, record.Case.AmountCents, record.Case.FromAccountID); err != nil {
		return err
	}
	if record.WalletID == "" {
		return nil
	}
	if _, err := tx.Exec(ctx, `
		INSERT INTO wallet_balances (wallet_id, currency)
		VALUES ($1, $2)
		ON CONFLICT (wallet_id, currency) DO NOTHING
	`, record.WalletID, record.Case.Currency); err != nil {
		return err
	}
	_, err := tx.Exec(ctx, `
		UPDATE wallet_balances
		SET available_balance_cents = available_balance_cents + $1
		WHERE wallet_id = $2 AND currency = $3
	`, record.Case.AmountCents, record.WalletID, record.Case.Currency)
	return err
}

func (r *Repository) postPaymentReviewRejectRefundLedger(ctx context.Context, tx pgx.Tx, record paymentReviewRecord, note string) error {
	clearing, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		ReferenceType: "external_clearing",
		ReferenceID:   "sepa_outbound",
		Currency:      record.Case.Currency,
		NormalBalance: "credit",
	})
	if err != nil {
		return err
	}
	customerAccount, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		OwnerUserID:   record.Case.UserID,
		ReferenceType: "account",
		ReferenceID:   record.Case.FromAccountID,
		Currency:      record.Case.Currency,
		NormalBalance: "credit",
	})
	if err != nil {
		return err
	}

	_, err = r.ledger.Post(ctx, tx, ledger.PostParams{
		EventType:      "payment_review.rejected_refund",
		SourceType:     "payment_review_reject_refund",
		SourceID:       record.Case.TransferID,
		IdempotencyKey: "payment-review-reject-" + record.Case.TransferID,
		Description:    "Payment review rejected and funds were refunded",
		Metadata: map[string]any{
			"transfer_id": record.Case.TransferID,
			"review_id":   record.Case.ID,
			"reason":      note,
		},
		Lines: []ledger.LineParams{
			{
				LedgerAccountID: clearing.ID,
				Direction:       "debit",
				AmountCents:     record.Case.AmountCents,
				Currency:        record.Case.Currency,
			},
			{
				LedgerAccountID: customerAccount.ID,
				Direction:       "credit",
				AmountCents:     record.Case.AmountCents,
				Currency:        record.Case.Currency,
			},
		},
	})
	return err
}

func selectReviewTransfer(ctx context.Context, tx pgx.Tx, transferID string) (PaymentReviewCase, error) {
	record, err := scanPaymentReviewRecord(tx.QueryRow(ctx, paymentReviewTransferSelect+`
		WHERE t.id = $1
		FOR UPDATE OF t
	`, transferID))
	if errors.Is(err, pgx.ErrNoRows) {
		return PaymentReviewCase{}, domain.ErrNotFound
	}
	return record.Case, err
}

func upsertPaymentReviewCase(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 insertPaymentReviewSettlementEvent(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 findPaymentReviewCase(ctx context.Context, tx pgx.Tx, reviewID string, forUpdate bool) (paymentReviewRecord, error) {
	query := paymentReviewCaseSelect + ` WHERE prc.id = $1`
	if forUpdate {
		query += ` FOR UPDATE OF prc, t`
	}
	record, err := scanPaymentReviewRecord(tx.QueryRow(ctx, query, reviewID))
	if errors.Is(err, pgx.ErrNoRows) {
		return paymentReviewRecord{}, domain.ErrNotFound
	}
	return record, err
}

func findPaymentReviewCaseByTransfer(ctx context.Context, tx pgx.Tx, transferID string, forUpdate bool) (paymentReviewRecord, error) {
	query := paymentReviewCaseSelect + ` WHERE prc.transfer_id = $1`
	if forUpdate {
		query += ` FOR UPDATE OF prc, t`
	}
	record, err := scanPaymentReviewRecord(tx.QueryRow(ctx, query, transferID))
	if errors.Is(err, pgx.ErrNoRows) {
		return paymentReviewRecord{}, domain.ErrNotFound
	}
	return record, err
}

func normalizePaymentReviewNote(raw string, required bool) (string, error) {
	note := strings.TrimSpace(raw)
	if required && (len(note) < 8 || len(note) > 500) {
		return "", fmt.Errorf("%w: decision_note must be between 8 and 500 characters", domain.ErrValidation)
	}
	if !required && len(note) > 500 {
		return "", fmt.Errorf("%w: note must be 500 characters or fewer", domain.ErrValidation)
	}
	return note, nil
}

func noteOrDefault(note, fallback string) string {
	if strings.TrimSpace(note) != "" {
		return strings.TrimSpace(note)
	}
	return fallback
}

const paymentReviewCaseSelect = `
	SELECT prc.id::text, prc.transfer_id::text, prc.status, prc.reason_code, prc.reason,
		prc.opened_by_type, COALESCE(prc.opened_by_user_id::text, ''),
		COALESCE(prc.assigned_admin_user_id::text, ''), COALESCE(prc.decided_by_admin_user_id::text, ''),
		COALESCE(prc.decision_note, ''),
		t.user_id::text, u.email, u.full_name, t.from_account_id::text, COALESCE(a.wallet_id::text, ''),
		COALESCE(t.beneficiary_id::text, ''), COALESCE(t.beneficiary_name, ''), COALESCE(t.beneficiary_iban, ''),
		COALESCE(t.payment_reference, ''), t.transfer_type, t.status, t.amount_cents, TRIM(t.currency)::text,
		COALESCE(t.payment_reason_code, ''), COALESCE(t.payment_reason_category, ''),
		COALESCE(t.payment_reason_description, ''), t.manual_review_required, t.settlement_attempts,
		COALESCE(t.settlement_error, ''), t.settlement_next_attempt_at,
		prc.opened_at, prc.decided_at, prc.created_at, prc.updated_at, t.created_at
	FROM payment_review_cases prc
	JOIN transfers t ON t.id = prc.transfer_id
	JOIN users u ON u.id = t.user_id
	JOIN accounts a ON a.id = t.from_account_id
`

const paymentReviewTransferSelect = `
	SELECT gen_random_uuid()::text, t.id::text, 'open'::text, ''::text, ''::text,
		''::text, ''::text, ''::text, ''::text, ''::text,
		t.user_id::text, u.email, u.full_name, t.from_account_id::text, COALESCE(a.wallet_id::text, ''),
		COALESCE(t.beneficiary_id::text, ''), COALESCE(t.beneficiary_name, ''), COALESCE(t.beneficiary_iban, ''),
		COALESCE(t.payment_reference, ''), t.transfer_type, t.status, t.amount_cents, TRIM(t.currency)::text,
		COALESCE(t.payment_reason_code, ''), COALESCE(t.payment_reason_category, ''),
		COALESCE(t.payment_reason_description, ''), t.manual_review_required, t.settlement_attempts,
		COALESCE(t.settlement_error, ''), t.settlement_next_attempt_at,
		now(), NULL::timestamptz, now(), now(), t.created_at
	FROM transfers t
	JOIN users u ON u.id = t.user_id
	JOIN accounts a ON a.id = t.from_account_id
`

func scanPaymentReviewRecord(row scanner) (paymentReviewRecord, error) {
	var record paymentReviewRecord
	var decidedAt sql.NullTime
	var settlementNextAttemptAt sql.NullTime
	err := row.Scan(
		&record.Case.ID,
		&record.Case.TransferID,
		&record.Case.Status,
		&record.Case.ReasonCode,
		&record.Case.Reason,
		&record.Case.OpenedByType,
		&record.Case.OpenedByUserID,
		&record.Case.AssignedAdminUserID,
		&record.Case.DecidedByAdminUserID,
		&record.Case.DecisionNote,
		&record.Case.UserID,
		&record.Case.UserEmail,
		&record.Case.UserFullName,
		&record.Case.FromAccountID,
		&record.WalletID,
		&record.Case.BeneficiaryID,
		&record.Case.BeneficiaryName,
		&record.Case.BeneficiaryIBAN,
		&record.Case.PaymentReference,
		&record.Case.TransferType,
		&record.Case.TransferStatus,
		&record.Case.AmountCents,
		&record.Case.Currency,
		&record.Case.PaymentReasonCode,
		&record.Case.PaymentReasonCategory,
		&record.Case.PaymentReasonDescription,
		&record.Case.ManualReviewRequired,
		&record.Case.SettlementAttempts,
		&record.Case.SettlementError,
		&settlementNextAttemptAt,
		&record.Case.OpenedAt,
		&decidedAt,
		&record.Case.CreatedAt,
		&record.Case.UpdatedAt,
		&record.Case.TransferCreatedAt,
	)
	if err != nil {
		return record, err
	}
	if decidedAt.Valid {
		record.Case.DecidedAt = &decidedAt.Time
	}
	if settlementNextAttemptAt.Valid {
		record.Case.SettlementNextAttemptAt = &settlementNextAttemptAt.Time
	}
	return record, nil
}