package settlement

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/ledger"
	"github.com/niels/banking-app/backend/internal/payments"
)

type ProviderReportParams struct {
	Provider        string
	ReportReference string
	ReportType      string
	RawPayload      json.RawMessage
	Items           []ProviderReportItemParams
}

type ProviderReportItemParams struct {
	TransferID         string          `json:"transfer_id"`
	ProviderReference  string          `json:"provider_reference"`
	ProviderStatus     string          `json:"provider_status"`
	ProviderReasonCode string          `json:"provider_reason_code"`
	ProviderReason     string          `json:"provider_reason"`
	AmountCents        int64           `json:"amount_cents"`
	Currency           string          `json:"currency"`
	RawPayload         json.RawMessage `json:"raw_payload"`
}

type ProviderReport struct {
	ID              string               `json:"id"`
	Provider        string               `json:"provider"`
	ReportReference string               `json:"report_reference"`
	ReportType      string               `json:"report_type"`
	Status          string               `json:"status"`
	ItemCount       int                  `json:"item_count"`
	MatchedCount    int                  `json:"matched_count"`
	ProcessedCount  int                  `json:"processed_count"`
	ErrorCount      int                  `json:"error_count"`
	ReceivedAt      time.Time            `json:"received_at"`
	ProcessedAt     *time.Time           `json:"processed_at,omitempty"`
	CreatedAt       time.Time            `json:"created_at"`
	UpdatedAt       time.Time            `json:"updated_at"`
	Items           []ProviderReportItem `json:"items,omitempty"`
}

type ProviderReportItem struct {
	ID                 string          `json:"id"`
	ReportID           string          `json:"report_id"`
	TransferID         string          `json:"transfer_id,omitempty"`
	ProviderReference  string          `json:"provider_reference,omitempty"`
	ProviderStatus     string          `json:"provider_status"`
	ProviderReasonCode string          `json:"provider_reason_code,omitempty"`
	ProviderReason     string          `json:"provider_reason,omitempty"`
	AmountCents        int64           `json:"amount_cents"`
	Currency           string          `json:"currency,omitempty"`
	Matched            bool            `json:"matched"`
	Processed          bool            `json:"processed"`
	ProcessingError    string          `json:"processing_error,omitempty"`
	RawPayload         json.RawMessage `json:"raw_payload"`
	CreatedAt          time.Time       `json:"created_at"`
}

type InboundPaymentParams struct {
	Provider          string
	ProviderReference string
	DebtorName        string
	DebtorIBAN        string
	CreditorIBAN      string
	AmountCents       int64
	Currency          string
	RawPayload        json.RawMessage
}

type InboundPayment struct {
	ID                string          `json:"id"`
	Provider          string          `json:"provider"`
	ProviderReference string          `json:"provider_reference"`
	AccountID         string          `json:"account_id,omitempty"`
	UserID            string          `json:"user_id,omitempty"`
	DebtorName        string          `json:"debtor_name,omitempty"`
	DebtorIBAN        string          `json:"debtor_iban,omitempty"`
	CreditorIBAN      string          `json:"creditor_iban"`
	AmountCents       int64           `json:"amount_cents"`
	Currency          string          `json:"currency"`
	Status            string          `json:"status"`
	RawPayload        json.RawMessage `json:"raw_payload"`
	ProcessedAt       *time.Time      `json:"processed_at,omitempty"`
	CreatedAt         time.Time       `json:"created_at"`
}

func (r *Repository) IngestProviderReport(ctx context.Context, params ProviderReportParams) (ProviderReport, error) {
	params.Provider = strings.TrimSpace(params.Provider)
	if params.Provider == "" {
		params.Provider = localSEPAProvider
	}
	params.ReportReference = strings.TrimSpace(params.ReportReference)
	if params.ReportReference == "" {
		return ProviderReport{}, fmt.Errorf("%w: report_reference is required", domain.ErrValidation)
	}
	params.ReportType = strings.TrimSpace(params.ReportType)
	if params.ReportType == "" {
		params.ReportType = "settlement"
	}
	rawPayload := jsonOrEmpty(params.RawPayload)

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

	var reportID string
	err = tx.QueryRow(ctx, `
		INSERT INTO sepa_provider_reports (provider, report_reference, report_type, status, raw_payload, item_count)
		VALUES ($1, $2, $3, 'received', $4::jsonb, $5)
		ON CONFLICT (provider, report_reference) DO NOTHING
		RETURNING id::text
	`, params.Provider, params.ReportReference, params.ReportType, string(rawPayload), len(params.Items)).Scan(&reportID)
	if errors.Is(err, pgx.ErrNoRows) {
		report, findErr := r.findProviderReportTx(ctx, tx, params.Provider, params.ReportReference)
		if findErr != nil {
			return ProviderReport{}, findErr
		}
		return report, tx.Commit(ctx)
	}
	if err != nil {
		return ProviderReport{}, err
	}

	matched, processed, failed := 0, 0, 0
	for _, item := range params.Items {
		result := r.applyProviderReportItem(ctx, tx, params.Provider, item)
		if result.Matched {
			matched++
		}
		if result.Processed {
			processed++
		}
		if result.ProcessingError != "" {
			failed++
		}
		if err := insertProviderReportItem(ctx, tx, reportID, result); err != nil {
			return ProviderReport{}, err
		}
	}

	status := "processed"
	if failed > 0 {
		status = "failed"
	}
	if _, err := tx.Exec(ctx, `
		UPDATE sepa_provider_reports
		SET status = $2, matched_count = $3, processed_count = $4, error_count = $5, processed_at = now()
		WHERE id = $1
	`, reportID, status, matched, processed, failed); err != nil {
		return ProviderReport{}, err
	}

	report, err := findProviderReportByID(ctx, tx, reportID)
	if err != nil {
		return ProviderReport{}, err
	}
	report.Items, err = listProviderReportItemsTx(ctx, tx, report.ID)
	if err != nil {
		return ProviderReport{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return ProviderReport{}, err
	}
	return report, nil
}

func (r *Repository) ListProviderReports(ctx context.Context, limit int) ([]ProviderReport, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider, report_reference, report_type, status, item_count,
			matched_count, processed_count, error_count, received_at, processed_at, created_at, updated_at
		FROM sepa_provider_reports
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	reports := []ProviderReport{}
	for rows.Next() {
		report, err := scanProviderReport(rows)
		if err != nil {
			return nil, err
		}
		reports = append(reports, report)
	}
	return reports, rows.Err()
}

func (r *Repository) ProcessInboundPayment(ctx context.Context, params InboundPaymentParams) (InboundPayment, error) {
	params.Provider = strings.TrimSpace(params.Provider)
	if params.Provider == "" {
		params.Provider = localSEPAProvider
	}
	params.ProviderReference = strings.TrimSpace(params.ProviderReference)
	params.CreditorIBAN = strings.ToUpper(strings.ReplaceAll(strings.TrimSpace(params.CreditorIBAN), " ", ""))
	params.DebtorIBAN = strings.ToUpper(strings.ReplaceAll(strings.TrimSpace(params.DebtorIBAN), " ", ""))
	params.Currency = strings.ToUpper(strings.TrimSpace(params.Currency))
	if params.ProviderReference == "" || params.CreditorIBAN == "" {
		return InboundPayment{}, fmt.Errorf("%w: provider_reference and creditor_iban are required", domain.ErrValidation)
	}
	if err := domain.ValidateAmount(params.AmountCents); err != nil {
		return InboundPayment{}, err
	}
	if err := domain.ValidateCurrency(params.Currency); err != nil {
		return InboundPayment{}, err
	}

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

	existing, err := findInboundPaymentByReference(ctx, tx, params.Provider, params.ProviderReference)
	if err == nil {
		return existing, tx.Commit(ctx)
	}
	if !errors.Is(err, pgx.ErrNoRows) {
		return InboundPayment{}, err
	}

	account, err := lockInboundAccount(ctx, tx, params.CreditorIBAN, params.Currency)
	status := "completed"
	if errors.Is(err, pgx.ErrNoRows) {
		status = "failed"
	} else if err != nil {
		return InboundPayment{}, err
	}

	inbound, err := insertInboundPayment(ctx, tx, params, account, status)
	if err != nil {
		return InboundPayment{}, err
	}
	if status == "completed" {
		if err := creditInboundPayment(ctx, tx, account, inbound.AmountCents); err != nil {
			return InboundPayment{}, err
		}
		if err := r.postInboundLedger(ctx, tx, inbound); err != nil {
			return InboundPayment{}, err
		}
		if err := payments.RecordNotification(ctx, tx, payments.NotificationParams{
			UserID:           inbound.UserID,
			InboundPaymentID: inbound.ID,
			Type:             "payment.inbound.completed",
			Title:            "Inbound payment received",
			Message:          "An inbound SEPA payment has been credited to your account.",
			Payload: map[string]any{
				"inbound_payment_id": inbound.ID,
				"amount_cents":       inbound.AmountCents,
				"currency":           inbound.Currency,
				"debtor_name":        inbound.DebtorName,
			},
		}); err != nil {
			return InboundPayment{}, err
		}
	}
	if err := tx.Commit(ctx); err != nil {
		return InboundPayment{}, err
	}
	return inbound, nil
}

func (r *Repository) ListInboundPayments(ctx context.Context, limit int) ([]InboundPayment, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider, provider_reference, COALESCE(account_id::text, ''), COALESCE(user_id::text, ''),
			debtor_name, debtor_iban, creditor_iban, amount_cents, currency, status, raw_payload, processed_at, created_at
		FROM inbound_payments
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	payments := []InboundPayment{}
	for rows.Next() {
		inbound, err := scanInboundPayment(rows)
		if err != nil {
			return nil, err
		}
		payments = append(payments, inbound)
	}
	return payments, rows.Err()
}

func (r *Repository) applyProviderReportItem(ctx context.Context, tx pgx.Tx, provider string, item ProviderReportItemParams) ProviderReportItem {
	result := ProviderReportItem{
		TransferID:         strings.TrimSpace(item.TransferID),
		ProviderReference:  strings.TrimSpace(item.ProviderReference),
		ProviderStatus:     normalizeProviderStatus(item.ProviderStatus),
		ProviderReasonCode: strings.ToUpper(strings.TrimSpace(item.ProviderReasonCode)),
		ProviderReason:     strings.TrimSpace(item.ProviderReason),
		AmountCents:        item.AmountCents,
		Currency:           strings.ToUpper(strings.TrimSpace(item.Currency)),
		RawPayload:         jsonOrEmpty(item.RawPayload),
	}
	if result.ProviderStatus == "" {
		result.ProviderStatus = "unknown"
		result.ProcessingError = "provider_status is required"
		return result
	}

	transfer, err := selectTransferForProviderReport(ctx, tx, result.TransferID, provider, result.ProviderReference)
	if errors.Is(err, pgx.ErrNoRows) {
		result.ProcessingError = "transfer not found"
		return result
	}
	if err != nil {
		result.ProcessingError = err.Error()
		return result
	}
	result.Matched = true
	result.TransferID = transfer.ID
	if result.AmountCents > 0 && result.AmountCents != transfer.AmountCents {
		result.ProcessingError = "amount does not match transfer"
		return result
	}
	if result.Currency != "" && result.Currency != transfer.Currency {
		result.ProcessingError = "currency does not match transfer"
		return result
	}

	switch result.ProviderStatus {
	case "completed":
		err = r.completeFromProviderReport(ctx, tx, transfer, provider, result)
	case "failed":
		err = r.failFromProviderReport(ctx, tx, transfer, provider, result)
	default:
		err = insertProviderEvent(ctx, tx, transfer.ID, "provider_reported", transfer.Status, transfer.Status, provider, result.ProviderReference, result.ProviderReason)
	}
	if err != nil {
		result.ProcessingError = err.Error()
		return result
	}
	result.Processed = true
	return result
}

func (r *Repository) completeFromProviderReport(ctx context.Context, tx pgx.Tx, transfer dueTransfer, provider string, item ProviderReportItem) error {
	if transfer.Status == payments.StatusCompleted {
		return nil
	}
	if transfer.Status == payments.StatusReviewHeld || transfer.Status == payments.StatusRejected || transfer.Status == payments.StatusFailed {
		return fmt.Errorf("transfer status %s cannot be completed from provider report", transfer.Status)
	}
	if transfer.Status == payments.StatusPending {
		if err := markTransferProcessingFromProvider(ctx, tx, transfer, provider, item); err != nil {
			return err
		}
		transfer.Status = payments.StatusProcessing
	}
	if transfer.Status != payments.StatusProcessing {
		return fmt.Errorf("transfer status %s cannot be completed from provider report", transfer.Status)
	}
	if err := r.postSettlementLedger(ctx, tx, transfer, item.ProviderReference); err != nil {
		return err
	}
	code := item.ProviderReasonCode
	if code == "" {
		code = "PROVIDER_REPORT_COMPLETED"
	}
	reason, err := payments.MapReason(ctx, tx, provider, code)
	if err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
		UPDATE transfers
		SET status = 'completed',
			settlement_provider = $2,
			settlement_reference = NULLIF($3, ''),
			settlement_error = NULL,
			settlement_next_attempt_at = NULL,
			settled_at = now(),
			payment_reason_code = $4,
			payment_reason_category = $5,
			payment_reason_description = $6,
			manual_review_required = false
		WHERE id = $1
	`, transfer.ID, provider, item.ProviderReference, reason.InternalCode, reason.Category, reason.Description); err != nil {
		return err
	}
	if err := insertProviderEvent(ctx, tx, transfer.ID, "completed", payments.StatusProcessing, payments.StatusCompleted, provider, item.ProviderReference, item.ProviderReason); err != nil {
		return err
	}
	if err := payments.RecordStatusEvent(ctx, tx, payments.StatusEventParams{
		TransferID:   transfer.ID,
		FromStatus:   payments.StatusProcessing,
		ToStatus:     payments.StatusCompleted,
		Provider:     provider,
		ProviderCode: code,
		Reason:       reason,
		Note:         item.ProviderReason,
		ActorType:    payments.ActorProvider,
	}); err != nil {
		return err
	}
	return payments.RecordNotification(ctx, tx, payments.NotificationParams{
		UserID:     transfer.UserID,
		TransferID: transfer.ID,
		Type:       "payment.completed",
		Title:      "SEPA payment completed",
		Message:    "Your SEPA payment has been completed.",
		Payload: map[string]any{
			"transfer_id": transfer.ID,
			"provider":    provider,
		},
	})
}

func (r *Repository) failFromProviderReport(ctx context.Context, tx pgx.Tx, transfer dueTransfer, provider string, item ProviderReportItem) error {
	if transfer.Status == payments.StatusFailed || transfer.Status == payments.StatusRejected {
		return nil
	}
	if transfer.Status == payments.StatusCompleted {
		return fmt.Errorf("completed transfer cannot be failed from provider report")
	}
	if transfer.Status == payments.StatusReviewHeld {
		return fmt.Errorf("review-held transfer cannot be failed from provider report")
	}
	if transfer.Status == payments.StatusPending {
		if err := markTransferProcessingFromProvider(ctx, tx, transfer, provider, item); err != nil {
			return err
		}
		transfer.Status = payments.StatusProcessing
	}
	code := item.ProviderReasonCode
	if code == "" {
		code = "PROVIDER_REPORT_FAILED"
	}
	return r.failAndRefund(ctx, tx, transfer, item.ProviderReference, providerDecision{
		Outcome: providerPermanentFailure,
		Code:    code,
		Reason:  item.ProviderReason,
	})
}

func markTransferProcessingFromProvider(ctx context.Context, tx pgx.Tx, transfer dueTransfer, provider string, item ProviderReportItem) error {
	reason := payments.ReasonMapping{
		Provider:     provider,
		ProviderCode: "PROVIDER_REPORT_PROCESSING",
		InternalCode: "provider_report_processing",
		Category:     "settlement",
		Severity:     "low",
		Description:  "Provider report matched payment before processing event was recorded",
	}
	if _, err := tx.Exec(ctx, `
		UPDATE transfers
		SET status = 'processing',
			settlement_provider = $2,
			settlement_reference = NULLIF($3, ''),
			settlement_error = NULL,
			settlement_next_attempt_at = NULL,
			payment_reason_code = $4,
			payment_reason_category = $5,
			payment_reason_description = $6,
			manual_review_required = false
		WHERE id = $1
	`, transfer.ID, provider, item.ProviderReference, reason.InternalCode, reason.Category, reason.Description); err != nil {
		return err
	}
	if err := insertProviderEvent(ctx, tx, transfer.ID, "processing", payments.StatusPending, payments.StatusProcessing, provider, item.ProviderReference, reason.Description); err != nil {
		return err
	}
	return payments.RecordStatusEvent(ctx, tx, payments.StatusEventParams{
		TransferID:   transfer.ID,
		FromStatus:   payments.StatusPending,
		ToStatus:     payments.StatusProcessing,
		Provider:     provider,
		ProviderCode: reason.ProviderCode,
		Reason:       reason,
		Note:         reason.Description,
		ActorType:    payments.ActorProvider,
	})
}

func selectTransferForProviderReport(ctx context.Context, tx pgx.Tx, transferID, provider, providerReference string) (dueTransfer, error) {
	var transfer dueTransfer
	err := tx.QueryRow(ctx, `
		SELECT t.id::text, t.user_id::text, t.from_account_id::text, COALESCE(a.wallet_id::text, ''),
			t.amount_cents, t.currency, t.status, t.settlement_attempts, COALESCE(t.beneficiary_iban, ''),
			COALESCE(t.payment_reference, ''), COALESCE(t.description, '')
		FROM transfers t
		JOIN accounts a ON a.id = t.from_account_id
		WHERE t.transfer_type = 'sepa'
			AND (
				(NULLIF($1, '') IS NOT NULL AND t.id = NULLIF($1, '')::uuid)
				OR (
					NULLIF($2, '') IS NOT NULL
					AND t.settlement_provider = $3
					AND t.settlement_reference = $2
				)
			)
		ORDER BY t.created_at DESC
		LIMIT 1
		FOR UPDATE OF t
	`, strings.TrimSpace(transferID), strings.TrimSpace(providerReference), provider).Scan(
		&transfer.ID,
		&transfer.UserID,
		&transfer.FromAccountID,
		&transfer.WalletID,
		&transfer.AmountCents,
		&transfer.Currency,
		&transfer.Status,
		&transfer.Attempts,
		&transfer.BeneficiaryIBAN,
		&transfer.PaymentReference,
		&transfer.Description,
	)
	return transfer, err
}

type inboundAccount struct {
	ID           string
	UserID       string
	WalletID     string
	Currency     string
	BalanceCents int64
}

func lockInboundAccount(ctx context.Context, tx pgx.Tx, iban, currency string) (inboundAccount, error) {
	var account inboundAccount
	err := tx.QueryRow(ctx, `
		SELECT id::text, user_id::text, COALESCE(wallet_id::text, ''), currency, balance_cents
		FROM accounts
		WHERE iban = $1 AND currency = $2 AND status = 'active'
		FOR UPDATE
	`, iban, currency).Scan(&account.ID, &account.UserID, &account.WalletID, &account.Currency, &account.BalanceCents)
	return account, err
}

func insertInboundPayment(ctx context.Context, tx pgx.Tx, params InboundPaymentParams, account inboundAccount, status string) (InboundPayment, error) {
	processedAt := "NULL"
	if status == "completed" {
		processedAt = "now()"
	}
	row := tx.QueryRow(ctx, fmt.Sprintf(`
		INSERT INTO inbound_payments (
			provider, provider_reference, account_id, user_id, debtor_name, debtor_iban,
			creditor_iban, amount_cents, currency, status, raw_payload, processed_at
		)
		VALUES (
			$1, $2, NULLIF($3, '')::uuid, NULLIF($4, '')::uuid, $5, $6, $7, $8, $9, $10, $11::jsonb, %s
		)
		RETURNING id::text, provider, provider_reference, COALESCE(account_id::text, ''), COALESCE(user_id::text, ''),
			debtor_name, debtor_iban, creditor_iban, amount_cents, currency, status, raw_payload, processed_at, created_at
	`, processedAt), params.Provider, params.ProviderReference, account.ID, account.UserID, strings.TrimSpace(params.DebtorName),
		params.DebtorIBAN, params.CreditorIBAN, params.AmountCents, params.Currency, status, string(jsonOrEmpty(params.RawPayload)))
	return scanInboundPayment(row)
}

func creditInboundPayment(ctx context.Context, tx pgx.Tx, account inboundAccount, amountCents int64) error {
	if _, err := tx.Exec(ctx, `
		UPDATE accounts
		SET balance_cents = balance_cents + $1, updated_at = now()
		WHERE id = $2
	`, amountCents, account.ID); err != nil {
		return err
	}
	if account.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
	`, account.WalletID, account.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
	`, amountCents, account.WalletID, account.Currency)
	return err
}

func (r *Repository) postInboundLedger(ctx context.Context, tx pgx.Tx, inbound InboundPayment) error {
	settlementCash, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		ReferenceType: "settlement_cash",
		ReferenceID:   "sepa_inbound",
		Currency:      inbound.Currency,
		NormalBalance: "debit",
	})
	if err != nil {
		return err
	}
	customerAccount, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		OwnerUserID:   inbound.UserID,
		ReferenceType: "account",
		ReferenceID:   inbound.AccountID,
		Currency:      inbound.Currency,
		NormalBalance: "credit",
	})
	if err != nil {
		return err
	}
	_, err = r.ledger.Post(ctx, tx, ledger.PostParams{
		EventType:      "sepa.inbound.completed",
		SourceType:     "inbound_payment",
		SourceID:       inbound.ID,
		IdempotencyKey: "inbound-payment-" + inbound.ID,
		Description:    "Inbound SEPA payment credited",
		Metadata: map[string]any{
			"provider":           inbound.Provider,
			"provider_reference": inbound.ProviderReference,
			"creditor_iban":      inbound.CreditorIBAN,
			"debtor_iban":        inbound.DebtorIBAN,
		},
		Lines: []ledger.LineParams{
			{
				LedgerAccountID: settlementCash.ID,
				Direction:       "debit",
				AmountCents:     inbound.AmountCents,
				Currency:        inbound.Currency,
			},
			{
				LedgerAccountID: customerAccount.ID,
				Direction:       "credit",
				AmountCents:     inbound.AmountCents,
				Currency:        inbound.Currency,
			},
		},
	})
	return err
}

func insertProviderReportItem(ctx context.Context, tx pgx.Tx, reportID string, item ProviderReportItem) error {
	_, err := tx.Exec(ctx, `
		INSERT INTO sepa_provider_report_items (
			report_id, transfer_id, provider_reference, provider_status, provider_reason_code,
			provider_reason, amount_cents, currency, matched, processed, processing_error, raw_payload
		)
		VALUES (
			$1, NULLIF($2, '')::uuid, $3, $4, $5, $6, $7, NULLIF($8, '')::char(3), $9, $10, $11, $12::jsonb
		)
	`, reportID, item.TransferID, item.ProviderReference, item.ProviderStatus, item.ProviderReasonCode,
		item.ProviderReason, item.AmountCents, item.Currency, item.Matched, item.Processed, item.ProcessingError, string(jsonOrEmpty(item.RawPayload)))
	return err
}

func (r *Repository) findProviderReportTx(ctx context.Context, tx pgx.Tx, provider, reportReference string) (ProviderReport, error) {
	report, err := scanProviderReport(tx.QueryRow(ctx, `
		SELECT id::text, provider, report_reference, report_type, status, item_count,
			matched_count, processed_count, error_count, received_at, processed_at, created_at, updated_at
		FROM sepa_provider_reports
		WHERE provider = $1 AND report_reference = $2
	`, provider, reportReference))
	if err != nil {
		return ProviderReport{}, err
	}
	report.Items, err = listProviderReportItemsTx(ctx, tx, report.ID)
	return report, err
}

func findProviderReportByID(ctx context.Context, tx pgx.Tx, reportID string) (ProviderReport, error) {
	return scanProviderReport(tx.QueryRow(ctx, `
		SELECT id::text, provider, report_reference, report_type, status, item_count,
			matched_count, processed_count, error_count, received_at, processed_at, created_at, updated_at
		FROM sepa_provider_reports
		WHERE id = $1
	`, reportID))
}

func listProviderReportItemsTx(ctx context.Context, tx pgx.Tx, reportID string) ([]ProviderReportItem, error) {
	rows, err := tx.Query(ctx, `
		SELECT id::text, report_id::text, COALESCE(transfer_id::text, ''), provider_reference,
			provider_status, provider_reason_code, provider_reason, amount_cents,
			COALESCE(currency::text, ''), matched, processed, processing_error, raw_payload, created_at
		FROM sepa_provider_report_items
		WHERE report_id = $1
		ORDER BY created_at, id
	`, reportID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	items := []ProviderReportItem{}
	for rows.Next() {
		item, err := scanProviderReportItem(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func findInboundPaymentByReference(ctx context.Context, tx pgx.Tx, provider, reference string) (InboundPayment, error) {
	return scanInboundPayment(tx.QueryRow(ctx, `
		SELECT id::text, provider, provider_reference, COALESCE(account_id::text, ''), COALESCE(user_id::text, ''),
			debtor_name, debtor_iban, creditor_iban, amount_cents, currency, status, raw_payload, processed_at, created_at
		FROM inbound_payments
		WHERE provider = $1 AND provider_reference = $2
	`, provider, reference))
}

func insertProviderEvent(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 normalizeProviderStatus(status string) string {
	switch strings.ToLower(strings.TrimSpace(status)) {
	case "accepted", "completed", "complete", "settled", "success", "succeeded":
		return "completed"
	case "failed", "failure", "rejected", "returned", "reject":
		return "failed"
	case "pending", "processing":
		return "pending"
	default:
		return strings.ToLower(strings.TrimSpace(status))
	}
}

func jsonOrEmpty(raw json.RawMessage) json.RawMessage {
	if len(raw) == 0 {
		return json.RawMessage(`{}`)
	}
	if !json.Valid(raw) {
		return json.RawMessage(`{}`)
	}
	return raw
}

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

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

func scanProviderReport(row scanner) (ProviderReport, error) {
	var report ProviderReport
	var processedAt sql.NullTime
	err := row.Scan(
		&report.ID,
		&report.Provider,
		&report.ReportReference,
		&report.ReportType,
		&report.Status,
		&report.ItemCount,
		&report.MatchedCount,
		&report.ProcessedCount,
		&report.ErrorCount,
		&report.ReceivedAt,
		&processedAt,
		&report.CreatedAt,
		&report.UpdatedAt,
	)
	if processedAt.Valid {
		report.ProcessedAt = &processedAt.Time
	}
	return report, err
}

func scanProviderReportItem(row scanner) (ProviderReportItem, error) {
	var item ProviderReportItem
	err := row.Scan(
		&item.ID,
		&item.ReportID,
		&item.TransferID,
		&item.ProviderReference,
		&item.ProviderStatus,
		&item.ProviderReasonCode,
		&item.ProviderReason,
		&item.AmountCents,
		&item.Currency,
		&item.Matched,
		&item.Processed,
		&item.ProcessingError,
		&item.RawPayload,
		&item.CreatedAt,
	)
	return item, err
}

func scanInboundPayment(row scanner) (InboundPayment, error) {
	var inbound InboundPayment
	var processedAt sql.NullTime
	err := row.Scan(
		&inbound.ID,
		&inbound.Provider,
		&inbound.ProviderReference,
		&inbound.AccountID,
		&inbound.UserID,
		&inbound.DebtorName,
		&inbound.DebtorIBAN,
		&inbound.CreditorIBAN,
		&inbound.AmountCents,
		&inbound.Currency,
		&inbound.Status,
		&inbound.RawPayload,
		&processedAt,
		&inbound.CreatedAt,
	)
	if processedAt.Valid {
		inbound.ProcessedAt = &processedAt.Time
	}
	return inbound, err
}