package savings

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

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

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

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

type CreateGoalParams struct {
	UserID            string
	AccountID         string
	Name              string
	TargetAmountCents int64
	TargetDate        string
	CategoryID        string
	Icon              string
	Color             string
}

type UpdateGoalParams struct {
	UserID            string
	GoalID            string
	Name              *string
	TargetAmountCents *int64
	TargetDate        *string
	CategoryID        *string
	Icon              *string
	Color             *string
}

type MovementParams struct {
	UserID         string
	GoalID         string
	AmountCents    int64
	Description    string
	IdempotencyKey string
}

type Operation struct {
	Goal        domain.SavingsGoal            `json:"goal"`
	Transaction domain.SavingsGoalTransaction `json:"transaction,omitempty"`
}

type lockedGoal struct {
	ID                 string
	UserID             string
	AccountID          string
	Name               string
	Currency           string
	TargetAmountCents  int64
	CurrentAmountCents int64
	Status             string
	TargetDate         string
	CategoryID         string
	Icon               string
	Color              string
	PocketType         string
	Visibility         string
	ProviderName       string
	ExternalPocketID   string
	ProviderSyncStatus string
	ProviderSyncError  string
	ProviderSyncedAt   sql.NullTime
	CompletedAt        sql.NullTime
	ClosedAt           sql.NullTime
	CreatedAt          time.Time
	UpdatedAt          time.Time
}

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

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}
}

const goalColumns = `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,
	COALESCE(category_id::text, ''), icon, color, pocket_type, visibility, provider_name, external_pocket_id,
	provider_sync_status, provider_sync_error, provider_synced_at`

func (r *Repository) Create(ctx context.Context, params CreateGoalParams) (domain.SavingsGoal, error) {
	if err := domain.ValidateAmount(params.TargetAmountCents); err != nil {
		return domain.SavingsGoal{}, err
	}
	params.CategoryID = strings.TrimSpace(params.CategoryID)
	params.Icon = strings.TrimSpace(params.Icon)
	params.Color = strings.TrimSpace(params.Color)
	if params.CategoryID != "" {
		if err := domain.ValidateUUID("category_id", params.CategoryID); err != nil {
			return domain.SavingsGoal{}, err
		}
	}

	row := r.db.QueryRow(ctx, `
		INSERT INTO savings_goals (user_id, account_id, name, currency, target_amount_cents, target_date, category_id, icon, color)
		SELECT $1, a.id, $3, a.currency, $4, NULLIF($5, '')::date, NULLIF($6, '')::uuid, $7, $8
		FROM accounts a
		LEFT JOIN savings_pocket_categories c
			ON c.id = NULLIF($6, '')::uuid
			AND c.status = 'active'
			AND (c.user_id IS NULL OR c.user_id = $1)
		WHERE a.id = $2 AND a.user_id = $1 AND a.status = 'active'
			AND ($6 = '' OR c.id IS NOT NULL)
		RETURNING `+goalColumns,
		params.UserID, params.AccountID, params.Name, params.TargetAmountCents, params.TargetDate,
		params.CategoryID, params.Icon, params.Color)

	goal, err := scanGoal(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.SavingsGoal{}, domain.ErrNotFound
	}
	return goal, err
}

func (r *Repository) ListByUser(ctx context.Context, userID string) ([]domain.SavingsGoal, error) {
	rows, err := r.db.Query(ctx, `
		SELECT `+goalColumns+`
		FROM savings_goals
		WHERE user_id = $1
			OR EXISTS (
				SELECT 1
				FROM savings_pocket_members spm
				WHERE spm.savings_goal_id = savings_goals.id
					AND spm.member_user_id = $1
					AND spm.status = 'active'
			)
		ORDER BY created_at DESC
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

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

func (r *Repository) FindOwned(ctx context.Context, userID, goalID string) (domain.SavingsGoal, error) {
	row := r.db.QueryRow(ctx, `
		SELECT `+goalColumns+`
		FROM savings_goals
		WHERE id = $1 AND (
			user_id = $2
			OR EXISTS (
				SELECT 1
				FROM savings_pocket_members spm
				WHERE spm.savings_goal_id = savings_goals.id
					AND spm.member_user_id = $2
					AND spm.status = 'active'
			)
		)
	`, goalID, userID)

	goal, err := scanGoal(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.SavingsGoal{}, domain.ErrNotFound
	}
	return goal, err
}

func (r *Repository) Update(ctx context.Context, params UpdateGoalParams) (domain.SavingsGoal, error) {
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	defer tx.Rollback(ctx)

	goal, err := lockGoal(ctx, tx, params.UserID, params.GoalID)
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	if goal.Status == "closed" {
		return domain.SavingsGoal{}, fmt.Errorf("%w: savings goal is closed", domain.ErrValidation)
	}

	name := goal.Name
	if params.Name != nil {
		name = *params.Name
	}

	targetAmount := goal.TargetAmountCents
	if params.TargetAmountCents != nil {
		targetAmount = *params.TargetAmountCents
	}
	if err := domain.ValidateAmount(targetAmount); err != nil {
		return domain.SavingsGoal{}, err
	}
	if targetAmount < goal.CurrentAmountCents {
		return domain.SavingsGoal{}, fmt.Errorf("%w: target_amount_cents cannot be lower than current_amount_cents", domain.ErrValidation)
	}

	var targetDate any
	if params.TargetDate != nil {
		if *params.TargetDate != "" {
			targetDate = *params.TargetDate
		}
	} else if goal.TargetDate != "" {
		targetDate = goal.TargetDate
	}

	categoryID := goal.CategoryID
	if params.CategoryID != nil {
		categoryID = strings.TrimSpace(*params.CategoryID)
		if categoryID != "" {
			if err := domain.ValidateUUID("category_id", categoryID); err != nil {
				return domain.SavingsGoal{}, err
			}
			if err := ensureCategoryAccessible(ctx, tx, params.UserID, categoryID); err != nil {
				return domain.SavingsGoal{}, err
			}
		}
	}
	var categoryIDValue any
	if categoryID != "" {
		categoryIDValue = categoryID
	}

	icon := goal.Icon
	if params.Icon != nil {
		icon = strings.TrimSpace(*params.Icon)
	}
	color := goal.Color
	if params.Color != nil {
		color = strings.TrimSpace(*params.Color)
	}

	status := goal.Status
	var completedAt any
	if goal.CompletedAt.Valid {
		completedAt = goal.CompletedAt.Time
	}
	if goal.CurrentAmountCents == targetAmount {
		status = "completed"
		if completedAt == nil {
			completedAt = time.Now().UTC()
		}
	} else if status == "completed" {
		status = "active"
		completedAt = nil
	}

	row := tx.QueryRow(ctx, `
		UPDATE savings_goals
		SET name = $3,
			target_amount_cents = $4,
			target_date = $5::date,
			status = $6,
			completed_at = $7::timestamptz,
			category_id = $8::uuid,
			icon = $9,
			color = $10
		WHERE id = $1 AND user_id = $2
		RETURNING `+goalColumns,
		params.GoalID, params.UserID, name, targetAmount, targetDate, status, completedAt, categoryIDValue, icon, color)

	updated, err := scanGoal(row)
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return domain.SavingsGoal{}, err
	}
	return updated, nil
}

func (r *Repository) Pause(ctx context.Context, userID, goalID string) (domain.SavingsGoal, error) {
	return r.transitionStatus(ctx, userID, goalID, "active", "paused")
}

func (r *Repository) Resume(ctx context.Context, userID, goalID string) (domain.SavingsGoal, error) {
	return r.transitionStatus(ctx, userID, goalID, "paused", "active")
}

func (r *Repository) Contribute(ctx context.Context, params MovementParams) (Operation, error) {
	var op Operation
	var err error
	for attempt := 0; attempt < 3; attempt++ {
		op, err = r.move(ctx, params, "contribution")
		if !isRetryableTxError(err) {
			return op, err
		}
		time.Sleep(time.Duration(attempt+1) * 25 * time.Millisecond)
	}
	return op, err
}

func (r *Repository) Withdraw(ctx context.Context, params MovementParams) (Operation, error) {
	var op Operation
	var err error
	for attempt := 0; attempt < 3; attempt++ {
		op, err = r.move(ctx, params, "withdrawal")
		if !isRetryableTxError(err) {
			return op, err
		}
		time.Sleep(time.Duration(attempt+1) * 25 * time.Millisecond)
	}
	return op, err
}

func (r *Repository) Close(ctx context.Context, userID, goalID string) (domain.SavingsGoal, error) {
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	defer tx.Rollback(ctx)

	goal, err := lockGoal(ctx, tx, userID, goalID)
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	if goal.Status == "closed" {
		return goal.toDomain(), tx.Commit(ctx)
	}

	account, err := lockAccount(ctx, tx, goal.AccountID)
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	if account.UserID != userID || account.Status != "active" {
		return domain.SavingsGoal{}, domain.ErrForbidden
	}

	if goal.CurrentAmountCents > 0 {
		if err := increaseAccount(ctx, tx, account, goal.CurrentAmountCents); err != nil {
			return domain.SavingsGoal{}, err
		}
		transaction, err := insertTransaction(ctx, tx, goal, account, "close_return", goal.CurrentAmountCents, "closed savings goal", "")
		if err != nil {
			return domain.SavingsGoal{}, err
		}
		if err := r.postLedger(ctx, tx, goal, account, transaction); err != nil {
			return domain.SavingsGoal{}, err
		}
	}

	row := tx.QueryRow(ctx, `
		UPDATE savings_goals
		SET current_amount_cents = 0,
			status = 'closed',
			completed_at = NULL,
			closed_at = now()
		WHERE id = $1 AND user_id = $2
		RETURNING `+goalColumns+`
	`, goalID, userID)

	closed, err := scanGoal(row)
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return domain.SavingsGoal{}, err
	}
	return closed, nil
}

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

	rows, err := r.db.Query(ctx, `
		SELECT sgt.id::text, sgt.savings_goal_id::text, sgt.user_id::text, sgt.account_id::text,
			sgt.transaction_type, sgt.amount_cents, sgt.currency, COALESCE(sgt.description, ''),
			COALESCE(sgt.idempotency_key, ''), sgt.created_at
		FROM savings_goal_transactions sgt
		JOIN savings_goals sg ON sg.id = sgt.savings_goal_id
		WHERE sgt.savings_goal_id = $1 AND sg.user_id = $2
		ORDER BY sgt.created_at DESC
		LIMIT $3
	`, goalID, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	transactions := []domain.SavingsGoalTransaction{}
	for rows.Next() {
		transaction, err := scanTransaction(rows)
		if err != nil {
			return nil, err
		}
		transactions = append(transactions, transaction)
	}
	return transactions, rows.Err()
}

func (r *Repository) transitionStatus(ctx context.Context, userID, goalID, fromStatus, toStatus string) (domain.SavingsGoal, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE savings_goals
		SET status = $3
		WHERE id = $1 AND user_id = $2 AND status = $4
		RETURNING `+goalColumns+`
	`, goalID, userID, toStatus, fromStatus)

	goal, err := scanGoal(row)
	if errors.Is(err, pgx.ErrNoRows) {
		existing, findErr := r.FindOwned(ctx, userID, goalID)
		if findErr != nil {
			return domain.SavingsGoal{}, findErr
		}
		return domain.SavingsGoal{}, fmt.Errorf("%w: savings goal status is %s", domain.ErrValidation, existing.Status)
	}
	return goal, err
}

func (r *Repository) move(ctx context.Context, params MovementParams, movementType string) (Operation, error) {
	if err := domain.ValidateAmount(params.AmountCents); err != nil {
		return Operation{}, err
	}

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

	if params.IdempotencyKey != "" {
		existing, err := findTransactionByIdempotencyKey(ctx, tx, params.UserID, params.IdempotencyKey)
		if err == nil {
			if existing.SavingsGoalID != params.GoalID || existing.Type != movementType || existing.AmountCents != params.AmountCents {
				return Operation{}, domain.ErrConflict
			}
			goal, err := findGoal(ctx, tx, params.UserID, existing.SavingsGoalID)
			if err != nil {
				return Operation{}, err
			}
			return Operation{Goal: goal, Transaction: existing}, tx.Commit(ctx)
		}
		if !errors.Is(err, pgx.ErrNoRows) {
			return Operation{}, err
		}
	}

	goal, err := lockGoal(ctx, tx, params.UserID, params.GoalID)
	if err != nil {
		return Operation{}, err
	}
	if movementType == "contribution" && goal.Status != "active" {
		return Operation{}, fmt.Errorf("%w: savings goal must be active", domain.ErrValidation)
	}
	if movementType == "withdrawal" && goal.Status != "active" && goal.Status != "completed" {
		return Operation{}, fmt.Errorf("%w: savings goal must be active or completed", domain.ErrValidation)
	}

	account, err := lockAccount(ctx, tx, goal.AccountID)
	if err != nil {
		return Operation{}, err
	}
	if account.UserID != params.UserID || account.Status != "active" || account.Currency != goal.Currency {
		return Operation{}, domain.ErrForbidden
	}

	switch movementType {
	case "contribution":
		if goal.CurrentAmountCents+params.AmountCents > goal.TargetAmountCents {
			return Operation{}, fmt.Errorf("%w: contribution exceeds target_amount_cents", domain.ErrValidation)
		}
		if account.BalanceCents < params.AmountCents {
			return Operation{}, domain.ErrInsufficientFunds
		}
		if err := decreaseAccount(ctx, tx, account, params.AmountCents); err != nil {
			return Operation{}, err
		}
	case "withdrawal":
		if goal.CurrentAmountCents < params.AmountCents {
			return Operation{}, domain.ErrInsufficientFunds
		}
		if err := increaseAccount(ctx, tx, account, params.AmountCents); err != nil {
			return Operation{}, err
		}
	default:
		return Operation{}, fmt.Errorf("%w: unsupported savings movement type", domain.ErrValidation)
	}

	newCurrent := goal.CurrentAmountCents
	newStatus := goal.Status
	var completedAt any
	if goal.CompletedAt.Valid {
		completedAt = goal.CompletedAt.Time
	}
	if movementType == "contribution" {
		newCurrent += params.AmountCents
		if newCurrent == goal.TargetAmountCents {
			newStatus = "completed"
			completedAt = time.Now().UTC()
		}
	} else {
		newCurrent -= params.AmountCents
		if goal.Status == "completed" && newCurrent < goal.TargetAmountCents {
			newStatus = "active"
			completedAt = nil
		}
	}

	row := tx.QueryRow(ctx, `
		UPDATE savings_goals
		SET current_amount_cents = $3,
			status = $4,
			completed_at = $5::timestamptz
		WHERE id = $1 AND user_id = $2
		RETURNING `+goalColumns+`
	`, goal.ID, params.UserID, newCurrent, newStatus, completedAt)

	updatedGoal, err := scanGoal(row)
	if err != nil {
		return Operation{}, err
	}

	transaction, err := insertTransaction(ctx, tx, goal, account, movementType, params.AmountCents, params.Description, params.IdempotencyKey)
	if err != nil {
		if isUniqueViolation(err) {
			return Operation{}, domain.ErrConflict
		}
		return Operation{}, err
	}
	if err := r.postLedger(ctx, tx, goal, account, transaction); err != nil {
		return Operation{}, err
	}

	if err := tx.Commit(ctx); err != nil {
		return Operation{}, err
	}
	return Operation{Goal: updatedGoal, Transaction: transaction}, nil
}

func (r *Repository) postLedger(ctx context.Context, tx pgx.Tx, goal lockedGoal, account lockedAccount, transaction domain.SavingsGoalTransaction) error {
	accountLedger, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		OwnerUserID:   account.UserID,
		ReferenceType: "account",
		ReferenceID:   account.ID,
		Currency:      account.Currency,
		NormalBalance: "credit",
	})
	if err != nil {
		return err
	}
	goalLedger, err := r.ledger.EnsureAccount(ctx, tx, ledger.AccountParams{
		OwnerUserID:   goal.UserID,
		ReferenceType: "savings_goal",
		ReferenceID:   goal.ID,
		Currency:      goal.Currency,
		NormalBalance: "credit",
	})
	if err != nil {
		return err
	}

	lines := []ledger.LineParams{
		{
			LedgerAccountID: accountLedger.ID,
			Direction:       "debit",
			AmountCents:     transaction.AmountCents,
			Currency:        transaction.Currency,
		},
		{
			LedgerAccountID: goalLedger.ID,
			Direction:       "credit",
			AmountCents:     transaction.AmountCents,
			Currency:        transaction.Currency,
		},
	}
	if transaction.Type == "withdrawal" || transaction.Type == "close_return" {
		lines[0].Direction = "credit"
		lines[1].Direction = "debit"
	}

	_, err = r.ledger.Post(ctx, tx, ledger.PostParams{
		EventType:      "savings_goal." + transaction.Type,
		SourceType:     "savings_goal_transaction",
		SourceID:       transaction.ID,
		IdempotencyKey: transaction.IdempotencyKey,
		Description:    transaction.Description,
		Metadata: map[string]any{
			"savings_goal_id": goal.ID,
			"account_id":      account.ID,
		},
		Lines: lines,
	})
	return err
}

func decreaseAccount(ctx context.Context, tx pgx.Tx, account lockedAccount, amountCents int64) error {
	tag, err := tx.Exec(ctx, `
		UPDATE accounts
		SET balance_cents = balance_cents - $1, updated_at = now()
		WHERE id = $2 AND balance_cents >= $1
	`, amountCents, account.ID)
	if err != nil {
		return err
	}
	if tag.RowsAffected() != 1 {
		return domain.ErrInsufficientFunds
	}

	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
	}
	tag, err = tx.Exec(ctx, `
		UPDATE wallet_balances
		SET available_balance_cents = available_balance_cents - $1,
			reserved_balance_cents = reserved_balance_cents + $1
		WHERE wallet_id = $2 AND currency = $3 AND available_balance_cents >= $1
	`, amountCents, account.WalletID, account.Currency)
	if err != nil {
		return err
	}
	if tag.RowsAffected() != 1 {
		return domain.ErrInsufficientFunds
	}

	return nil
}

func increaseAccount(ctx context.Context, tx pgx.Tx, account lockedAccount, 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
	}
	tag, err := tx.Exec(ctx, `
		UPDATE wallet_balances
		SET available_balance_cents = available_balance_cents + $1,
			reserved_balance_cents = reserved_balance_cents - $1
		WHERE wallet_id = $2 AND currency = $3 AND reserved_balance_cents >= $1
	`, amountCents, account.WalletID, account.Currency)
	if err != nil {
		return err
	}
	if tag.RowsAffected() != 1 {
		return fmt.Errorf("%w: reserved wallet balance is lower than savings amount", domain.ErrValidation)
	}

	return nil
}

func lockGoal(ctx context.Context, tx pgx.Tx, userID, goalID string) (lockedGoal, error) {
	row := tx.QueryRow(ctx, `
		SELECT `+goalColumns+`
		FROM savings_goals
		WHERE id = $1 AND user_id = $2
		FOR UPDATE
	`, goalID, userID)

	goal, err := scanLockedGoal(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return lockedGoal{}, domain.ErrNotFound
	}
	return goal, err
}

func lockAccount(ctx context.Context, tx pgx.Tx, accountID string) (lockedAccount, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, user_id::text, COALESCE(wallet_id::text, ''), balance_cents, currency, status
		FROM accounts
		WHERE id = $1
		FOR UPDATE
	`, accountID)

	var account lockedAccount
	err := row.Scan(&account.ID, &account.UserID, &account.WalletID, &account.BalanceCents, &account.Currency, &account.Status)
	if errors.Is(err, pgx.ErrNoRows) {
		return lockedAccount{}, domain.ErrNotFound
	}
	return account, err
}

func ensureCategoryAccessible(ctx context.Context, tx pgx.Tx, userID, categoryID string) error {
	var exists bool
	err := tx.QueryRow(ctx, `
		SELECT EXISTS (
			SELECT 1
			FROM savings_pocket_categories
			WHERE id = $1
				AND status = 'active'
				AND (user_id IS NULL OR user_id = $2)
		)
	`, categoryID, userID).Scan(&exists)
	if err != nil {
		return err
	}
	if !exists {
		return domain.ErrNotFound
	}
	return nil
}

func findGoal(ctx context.Context, tx pgx.Tx, userID, goalID string) (domain.SavingsGoal, error) {
	row := tx.QueryRow(ctx, `
		SELECT `+goalColumns+`
		FROM savings_goals
		WHERE id = $1 AND user_id = $2
	`, goalID, userID)
	return scanGoal(row)
}

func findTransactionByIdempotencyKey(ctx context.Context, tx pgx.Tx, userID, key string) (domain.SavingsGoalTransaction, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, savings_goal_id::text, user_id::text, account_id::text,
			transaction_type, amount_cents, currency, COALESCE(description, ''),
			COALESCE(idempotency_key, ''), created_at
		FROM savings_goal_transactions
		WHERE user_id = $1 AND idempotency_key = $2
	`, userID, key)
	return scanTransaction(row)
}

func insertTransaction(
	ctx context.Context,
	tx pgx.Tx,
	goal lockedGoal,
	account lockedAccount,
	transactionType string,
	amountCents int64,
	description string,
	idempotencyKey string,
) (domain.SavingsGoalTransaction, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO savings_goal_transactions (
			savings_goal_id, user_id, account_id, transaction_type, amount_cents, currency, description, idempotency_key
		)
		VALUES ($1, $2, $3, $4, $5, $6, NULLIF($7, ''), NULLIF($8, ''))
		RETURNING id::text, savings_goal_id::text, user_id::text, account_id::text,
			transaction_type, amount_cents, currency, COALESCE(description, ''),
			COALESCE(idempotency_key, ''), created_at
	`, goal.ID, goal.UserID, account.ID, transactionType, amountCents, goal.Currency, description, idempotencyKey)
	return scanTransaction(row)
}

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

func scanGoal(row scanner) (domain.SavingsGoal, error) {
	goal, err := scanLockedGoal(row)
	if err != nil {
		return domain.SavingsGoal{}, err
	}
	return goal.toDomain(), nil
}

func scanLockedGoal(row scanner) (lockedGoal, error) {
	var goal lockedGoal
	err := row.Scan(
		&goal.ID,
		&goal.UserID,
		&goal.AccountID,
		&goal.Name,
		&goal.Currency,
		&goal.TargetAmountCents,
		&goal.CurrentAmountCents,
		&goal.Status,
		&goal.TargetDate,
		&goal.CompletedAt,
		&goal.ClosedAt,
		&goal.CreatedAt,
		&goal.UpdatedAt,
		&goal.CategoryID,
		&goal.Icon,
		&goal.Color,
		&goal.PocketType,
		&goal.Visibility,
		&goal.ProviderName,
		&goal.ExternalPocketID,
		&goal.ProviderSyncStatus,
		&goal.ProviderSyncError,
		&goal.ProviderSyncedAt,
	)
	return goal, err
}

func scanTransaction(row scanner) (domain.SavingsGoalTransaction, error) {
	var transaction domain.SavingsGoalTransaction
	err := row.Scan(
		&transaction.ID,
		&transaction.SavingsGoalID,
		&transaction.UserID,
		&transaction.AccountID,
		&transaction.Type,
		&transaction.AmountCents,
		&transaction.Currency,
		&transaction.Description,
		&transaction.IdempotencyKey,
		&transaction.CreatedAt,
	)
	return transaction, err
}

func (g lockedGoal) toDomain() domain.SavingsGoal {
	goal := domain.SavingsGoal{
		ID:                 g.ID,
		UserID:             g.UserID,
		AccountID:          g.AccountID,
		Name:               g.Name,
		Currency:           g.Currency,
		TargetAmountCents:  g.TargetAmountCents,
		CurrentAmountCents: g.CurrentAmountCents,
		Status:             g.Status,
		TargetDate:         g.TargetDate,
		CategoryID:         g.CategoryID,
		Icon:               g.Icon,
		Color:              g.Color,
		PocketType:         g.PocketType,
		Visibility:         g.Visibility,
		ProviderName:       g.ProviderName,
		ExternalPocketID:   g.ExternalPocketID,
		ProviderSyncStatus: g.ProviderSyncStatus,
		ProviderSyncError:  g.ProviderSyncError,
		CreatedAt:          g.CreatedAt,
		UpdatedAt:          g.UpdatedAt,
	}
	if g.ProviderSyncedAt.Valid {
		goal.ProviderSyncedAt = &g.ProviderSyncedAt.Time
	}
	if g.CompletedAt.Valid {
		goal.CompletedAt = &g.CompletedAt.Time
	}
	if g.ClosedAt.Valid {
		goal.ClosedAt = &g.ClosedAt.Time
	}
	return goal
}

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

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