package integration

import (
	"context"
	"errors"
	"testing"
	"time"

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

func TestPostgreSQLDatabaseIntegrityControls(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)

	assertPositiveCount(t, ctx, db, `
		SELECT COUNT(*)
		FROM database_index_reviews
		WHERE decision = 'add'
	`)
	assertPositiveCount(t, ctx, db, `
		SELECT COUNT(*)
		FROM data_archival_policies
		WHERE status = 'active'
	`)
	assertPositiveCount(t, ctx, db, `
		SELECT COUNT(*)
		FROM financial_state_machine_transitions
		WHERE machine_name = 'savings_goal' AND active = true
	`)

	userID := createUser(t, ctx, db, "database-integrity@example.test", "Database Integrity")

	var accountID string
	if err := db.QueryRow(ctx, `
		INSERT INTO accounts (
			user_id, account_number, iban, bic, bank_provider, external_account_id, currency
		)
		VALUES (
			$1, '1000000999', 'NL91ABNA0417164300', 'ABNANL2A', 'local_test', 'database-integrity-account', 'EUR'
		)
		RETURNING id::text
	`, userID).Scan(&accountID); err != nil {
		t.Fatalf("create account for integrity test: %v", err)
	}

	var savingsGoalID string
	if err := db.QueryRow(ctx, `
		INSERT INTO savings_goals (user_id, account_id, name, currency, target_amount_cents)
		VALUES ($1, $2, 'Integrity goal', 'EUR', 10000)
		RETURNING id::text
	`, userID, accountID).Scan(&savingsGoalID); err != nil {
		t.Fatalf("create savings goal: %v", err)
	}

	if _, err := db.Exec(ctx, `
		UPDATE savings_goals
		SET status = 'paused'
		WHERE id = $1
	`, savingsGoalID); err != nil {
		t.Fatalf("expected active to paused transition to pass: %v", err)
	}

	_, err := db.Exec(ctx, `
		UPDATE savings_goals
		SET status = 'completed'
		WHERE id = $1
	`, savingsGoalID)
	if err == nil {
		t.Fatal("expected paused to completed transition to fail")
	}
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) || pgErr.Code != "23514" {
		t.Fatalf("expected check violation 23514 for invalid transition, got %v", err)
	}
}

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

	var count int64
	if err := db.QueryRow(ctx, query).Scan(&count); err != nil {
		t.Fatalf("count query failed: %v", err)
	}
	if count <= 0 {
		t.Fatalf("expected positive count for query %q, got %d", query, count)
	}
}