package integration

import (
	"context"
	"net/url"
	"os"
	"path/filepath"
	"runtime"
	"strconv"
	"strings"
	"testing"
	"time"

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

	"github.com/niels/banking-app/backend/internal/accounts"
	"github.com/niels/banking-app/backend/internal/ledger"
	"github.com/niels/banking-app/backend/internal/platform/migrations"
	"github.com/niels/banking-app/backend/internal/transfers"
)

func TestPostgreSQLMoneyMovementLedgerInvariants(t *testing.T) {
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	db := openTestDatabase(t, ctx)
	resetDatabase(t, ctx, db)
	runMigrations(t, ctx, db)

	accountRepo := accounts.NewRepository(db)
	ledgerRepo := ledger.NewRepository(db)
	transferRepo := transfers.NewRepository(db, ledgerRepo)

	sourceUserID := createUser(t, ctx, db, "integration-source@example.test", "Integration Source")
	destinationUserID := createUser(t, ctx, db, "integration-destination@example.test", "Integration Destination")

	sourceAccount, err := accountRepo.Create(ctx, accounts.CreateParams{
		UserID:        sourceUserID,
		AccountNumber: "1000000001",
		Currency:      "EUR",
	})
	if err != nil {
		t.Fatalf("create source account: %v", err)
	}
	destinationAccount, err := accountRepo.Create(ctx, accounts.CreateParams{
		UserID:        destinationUserID,
		AccountNumber: "1000000002",
		Currency:      "EUR",
	})
	if err != nil {
		t.Fatalf("create destination account: %v", err)
	}

	if _, err := db.Exec(ctx, `
		UPDATE accounts
		SET balance_cents = 10000
		WHERE id = $1
	`, sourceAccount.ID); err != nil {
		t.Fatalf("fund source account row: %v", err)
	}
	postInitialFunding(t, ctx, db, ledgerRepo, sourceUserID, sourceAccount.ID, "EUR", 10000)

	transfer, err := transferRepo.Create(ctx, transfers.CreateParams{
		UserID:         sourceUserID,
		FromAccountID:  sourceAccount.ID,
		ToAccountID:    destinationAccount.ID,
		AmountCents:    2500,
		Description:    "integration invariant transfer",
		IdempotencyKey: "integration-transfer-001",
	})
	if err != nil {
		t.Fatalf("create transfer: %v", err)
	}
	if transfer.Status != "completed" {
		t.Fatalf("expected completed transfer, got %s", transfer.Status)
	}

	assertLedgerJournalEntriesBalanced(t, ctx, db)
	assertAccountBalancesMatchLedger(t, ctx, db)
	assertWalletAvailableBalancesMatchAccounts(t, ctx, db)
	assertWalletReservedBalancesMatchSavings(t, ctx, db)
}

func openTestDatabase(t *testing.T, ctx context.Context) *pgxpool.Pool {
	t.Helper()

	rawURL := strings.TrimSpace(os.Getenv("BANKING_TEST_DATABASE_URL"))
	if rawURL == "" {
		t.Skip("BANKING_TEST_DATABASE_URL is not set; skipping PostgreSQL integration tests")
	}
	guardTestDatabaseURL(t, rawURL)

	db, err := pgxpool.New(ctx, rawURL)
	if err != nil {
		t.Fatalf("connect test database: %v", err)
	}
	if err := db.Ping(ctx); err != nil {
		db.Close()
		t.Fatalf("ping test database: %v", err)
	}
	t.Cleanup(db.Close)
	return db
}

func guardTestDatabaseURL(t *testing.T, rawURL string) {
	t.Helper()

	parsed, err := url.Parse(rawURL)
	if err != nil {
		t.Fatalf("parse BANKING_TEST_DATABASE_URL: %v", err)
	}
	databaseName := strings.TrimPrefix(parsed.Path, "/")
	schemaName := parsed.Query().Get("search_path")
	guardValue := strings.ToLower(databaseName + " " + schemaName)
	if !strings.Contains(guardValue, "test") && !strings.Contains(guardValue, "ci") {
		t.Fatalf("BANKING_TEST_DATABASE_URL must point to a database or schema containing 'test' or 'ci'; got database %q schema %q", databaseName, schemaName)
	}
}

func resetDatabase(t *testing.T, ctx context.Context, db *pgxpool.Pool) {
	t.Helper()

	if _, err := db.Exec(ctx, `
		DROP SCHEMA IF EXISTS public CASCADE;
		CREATE SCHEMA public;
		GRANT ALL ON SCHEMA public TO PUBLIC;
	`); err != nil {
		t.Fatalf("reset test database schema: %v", err)
	}
}

func runMigrations(t *testing.T, ctx context.Context, db *pgxpool.Pool) {
	t.Helper()

	result, err := migrations.Run(ctx, db, migrationsDir(t), nil)
	if err != nil {
		t.Fatalf("run migrations: %v", err)
	}
	if result.Applied == 0 {
		t.Fatal("expected migrations to be applied")
	}
}

func migrationsDir(t *testing.T) string {
	t.Helper()

	_, file, _, ok := runtime.Caller(0)
	if !ok {
		t.Fatal("resolve integration test path")
	}
	return filepath.Clean(filepath.Join(filepath.Dir(file), "..", "migrations"))
}

func createUser(t *testing.T, ctx context.Context, db *pgxpool.Pool, email, fullName string) string {
	t.Helper()

	var id string
	err := db.QueryRow(ctx, `
		INSERT INTO users (email, full_name, password_hash)
		VALUES ($1, $2, 'integration-test-password-hash')
		RETURNING id::text
	`, email, fullName).Scan(&id)
	if err != nil {
		t.Fatalf("create user %s: %v", email, err)
	}
	return id
}

func postInitialFunding(
	t *testing.T,
	ctx context.Context,
	db *pgxpool.Pool,
	ledgerRepo *ledger.Repository,
	userID string,
	accountID string,
	currency string,
	amountCents int64,
) {
	t.Helper()

	tx, err := db.Begin(ctx)
	if err != nil {
		t.Fatalf("begin funding ledger transaction: %v", err)
	}
	defer tx.Rollback(ctx)

	accountLedger, err := ledgerRepo.EnsureAccount(ctx, tx, ledger.AccountParams{
		OwnerUserID:   userID,
		ReferenceType: "account",
		ReferenceID:   accountID,
		Currency:      currency,
		NormalBalance: "credit",
	})
	if err != nil {
		t.Fatalf("ensure account ledger: %v", err)
	}
	fundingLedger, err := ledgerRepo.EnsureAccount(ctx, tx, ledger.AccountParams{
		ReferenceType: "external_funding",
		ReferenceID:   "integration_test",
		Currency:      currency,
		NormalBalance: "debit",
	})
	if err != nil {
		t.Fatalf("ensure funding ledger: %v", err)
	}
	if _, err := ledgerRepo.Post(ctx, tx, ledger.PostParams{
		EventType:   "account.funded",
		SourceType:  "integration_test",
		SourceID:    "initial-funding-001",
		Description: "Initial integration-test funding",
		Lines: []ledger.LineParams{
			{
				LedgerAccountID: fundingLedger.ID,
				Direction:       "debit",
				AmountCents:     amountCents,
				Currency:        currency,
			},
			{
				LedgerAccountID: accountLedger.ID,
				Direction:       "credit",
				AmountCents:     amountCents,
				Currency:        currency,
			},
		},
	}); err != nil {
		t.Fatalf("post funding ledger entry: %v", err)
	}
	if err := tx.Commit(ctx); err != nil {
		t.Fatalf("commit funding ledger transaction: %v", err)
	}
}

func assertLedgerJournalEntriesBalanced(t *testing.T, ctx context.Context, db *pgxpool.Pool) {
	t.Helper()

	rows, err := db.Query(ctx, `
		SELECT journal_entry_id::text, TRIM(currency)::text,
			COALESCE(SUM(CASE WHEN direction = 'debit' THEN amount_cents ELSE 0 END), 0)::bigint AS debits,
			COALESCE(SUM(CASE WHEN direction = 'credit' THEN amount_cents ELSE 0 END), 0)::bigint AS credits,
			COUNT(*)::bigint AS line_count
		FROM ledger_journal_lines
		GROUP BY journal_entry_id, TRIM(currency)::text
		HAVING COALESCE(SUM(CASE WHEN direction = 'debit' THEN amount_cents ELSE 0 END), 0)
			<> COALESCE(SUM(CASE WHEN direction = 'credit' THEN amount_cents ELSE 0 END), 0)
			OR COUNT(*) < 2
	`)
	if err != nil {
		t.Fatalf("query journal balance invariant: %v", err)
	}
	defer rows.Close()

	var failures []string
	for rows.Next() {
		var entryID, currency string
		var debits, credits, lineCount int64
		if err := rows.Scan(&entryID, &currency, &debits, &credits, &lineCount); err != nil {
			t.Fatalf("scan journal balance invariant: %v", err)
		}
		failures = append(failures, entryID+" "+currency+" debits="+itoa(debits)+" credits="+itoa(credits)+" lines="+itoa(lineCount))
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("read journal balance invariant: %v", err)
	}
	if len(failures) > 0 {
		t.Fatalf("unbalanced ledger journal entries: %s", strings.Join(failures, "; "))
	}
}

func assertAccountBalancesMatchLedger(t *testing.T, ctx context.Context, db *pgxpool.Pool) {
	t.Helper()

	rows, err := db.Query(ctx, `
		WITH ledger_balances AS (
			SELECT la.reference_id AS account_id, TRIM(la.currency)::text AS currency,
				COALESCE(SUM(CASE WHEN jl.direction = 'credit' THEN jl.amount_cents ELSE -jl.amount_cents END), 0)::bigint AS ledger_balance
			FROM ledger_accounts la
			LEFT JOIN ledger_journal_lines jl ON jl.ledger_account_id = la.id
			WHERE la.reference_type = 'account'
			GROUP BY la.reference_id, TRIM(la.currency)::text
		)
		SELECT a.id::text, TRIM(a.currency)::text, a.balance_cents::bigint, COALESCE(lb.ledger_balance, 0)::bigint
		FROM accounts a
		LEFT JOIN ledger_balances lb ON lb.account_id = a.id::text AND lb.currency = TRIM(a.currency)::text
		WHERE a.balance_cents <> COALESCE(lb.ledger_balance, 0)
	`)
	if err != nil {
		t.Fatalf("query account ledger invariant: %v", err)
	}
	defer rows.Close()

	var failures []string
	for rows.Next() {
		var accountID, currency string
		var accountBalance, ledgerBalance int64
		if err := rows.Scan(&accountID, &currency, &accountBalance, &ledgerBalance); err != nil {
			t.Fatalf("scan account ledger invariant: %v", err)
		}
		failures = append(failures, accountID+" "+currency+" account="+itoa(accountBalance)+" ledger="+itoa(ledgerBalance))
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("read account ledger invariant: %v", err)
	}
	if len(failures) > 0 {
		t.Fatalf("account balances do not match ledger: %s", strings.Join(failures, "; "))
	}
}

func assertWalletAvailableBalancesMatchAccounts(t *testing.T, ctx context.Context, db *pgxpool.Pool) {
	t.Helper()

	rows, err := db.Query(ctx, `
		WITH keys AS (
			SELECT wallet_id, TRIM(currency)::text AS currency FROM wallet_balances
			UNION
			SELECT wallet_id, TRIM(currency)::text AS currency FROM accounts WHERE wallet_id IS NOT NULL
		),
		account_balances AS (
			SELECT wallet_id, TRIM(currency)::text AS currency, COALESCE(SUM(balance_cents), 0)::bigint AS balance_cents
			FROM accounts
			WHERE wallet_id IS NOT NULL
			GROUP BY wallet_id, TRIM(currency)::text
		)
		SELECT k.wallet_id::text, k.currency, COALESCE(wb.available_balance_cents, 0)::bigint, COALESCE(ab.balance_cents, 0)::bigint
		FROM keys k
		LEFT JOIN wallet_balances wb ON wb.wallet_id = k.wallet_id AND TRIM(wb.currency)::text = k.currency
		LEFT JOIN account_balances ab ON ab.wallet_id = k.wallet_id AND ab.currency = k.currency
		WHERE COALESCE(wb.available_balance_cents, 0) <> COALESCE(ab.balance_cents, 0)
	`)
	if err != nil {
		t.Fatalf("query wallet available invariant: %v", err)
	}
	defer rows.Close()
	assertNoRows(t, rows, "wallet available balances do not match linked accounts")
}

func assertWalletReservedBalancesMatchSavings(t *testing.T, ctx context.Context, db *pgxpool.Pool) {
	t.Helper()

	rows, err := db.Query(ctx, `
		WITH keys AS (
			SELECT wallet_id, TRIM(currency)::text AS currency FROM wallet_balances
			UNION
			SELECT a.wallet_id, TRIM(sg.currency)::text AS currency
			FROM savings_goals sg
			JOIN accounts a ON a.id = sg.account_id
			WHERE a.wallet_id IS NOT NULL
		),
		savings_balances AS (
			SELECT a.wallet_id, TRIM(sg.currency)::text AS currency, COALESCE(SUM(sg.current_amount_cents), 0)::bigint AS reserved_cents
			FROM savings_goals sg
			JOIN accounts a ON a.id = sg.account_id
			WHERE sg.status <> 'closed' AND a.wallet_id IS NOT NULL
			GROUP BY a.wallet_id, TRIM(sg.currency)::text
		)
		SELECT k.wallet_id::text, k.currency, COALESCE(wb.reserved_balance_cents, 0)::bigint, COALESCE(sb.reserved_cents, 0)::bigint
		FROM keys k
		LEFT JOIN wallet_balances wb ON wb.wallet_id = k.wallet_id AND TRIM(wb.currency)::text = k.currency
		LEFT JOIN savings_balances sb ON sb.wallet_id = k.wallet_id AND sb.currency = k.currency
		WHERE COALESCE(wb.reserved_balance_cents, 0) <> COALESCE(sb.reserved_cents, 0)
	`)
	if err != nil {
		t.Fatalf("query wallet reserved invariant: %v", err)
	}
	defer rows.Close()
	assertNoRows(t, rows, "wallet reserved balances do not match open savings goals")
}

func assertNoRows(t *testing.T, rows interface {
	Next() bool
	Scan(dest ...any) error
	Err() error
}, message string) {
	t.Helper()

	var failures []string
	for rows.Next() {
		var id, currency string
		var actual, expected int64
		if err := rows.Scan(&id, &currency, &actual, &expected); err != nil {
			t.Fatalf("scan invariant failure: %v", err)
		}
		failures = append(failures, id+" "+currency+" actual="+itoa(actual)+" expected="+itoa(expected))
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("read invariant failures: %v", err)
	}
	if len(failures) > 0 {
		t.Fatalf("%s: %s", message, strings.Join(failures, "; "))
	}
}

func itoa(value int64) string {
	return strconv.FormatInt(value, 10)
}