naco-api/internal/store/integration_test.go

package store

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"reflect"
	"strings"
	"testing"
	"time"

	_ "github.com/lib/pq"
)

// setupTestDB handles connection pooling, migrations, and cleanup per test run.
func setupTestDB(t *testing.T) (*sql.DB, *Queries) {
	t.Helper()

	dsn := os.Getenv("TEST_DATABASE_URL")
	if dsn == "" {
		t.Skip("TEST_DATABASE_URL not set; skipping store integration test")
	}

	ctx := context.Background()
	conn, err := sql.Open("postgres", dsn)
	if err != nil {
		t.Fatalf("failed to open db: %v", err)
	}

	if err := conn.PingContext(ctx); err != nil {
		_ = conn.Close()
		t.Fatalf("cannot reach test database: %v", err)
	}

	t.Cleanup(func() {
		_ = conn.Close()
	})

	if err := Migrate(ctx, conn, "sql/schema"); err != nil {
		t.Fatalf("migrate: %v", err)
	}

	return conn, New(conn)
}

func makeEmbedding(dim int, val string) string {
	return "[" + strings.TrimSuffix(strings.Repeat(val+",", dim), ",") + "]"
}

func TestStoreRoundTrip(t *testing.T) {
	conn, q := setupTestDB(t)
	ctx := context.Background()

	uid := fmt.Sprintf("test_%d", time.Now().UnixNano())
	platform := "Bluesky"
	emb1 := makeEmbedding(1024, "0.1")

	// Clean up after the test so leftover records do not pollute subsequent runs
	t.Cleanup(func() {
		_, _ = conn.ExecContext(context.Background(), "DELETE FROM style_profiles WHERE user_id = $1", uid)
	})

	// 1. Test INSERT path of Upsert
	err := q.UpsertStyleProfile(ctx, UpsertStyleProfileParams{
		UserID:       uid,
		Platform:     platform,
		Embedding:    emb1,
		TopKeywords:  []string{"alpha", "beta"},
		StyleSummary: "original summary",
		PostCount:    5,
	})
	if err != nil {
		t.Fatalf("initial upsert failed: %v", err)
	}

	// 2. Test GET and verify fields
	p, err := q.GetStyleProfile(ctx, GetStyleProfileParams{UserID: uid, Platform: platform})
	if err != nil {
		t.Fatalf("get failed: %v", err)
	}

	if p.UserID != uid || p.Platform != platform || p.PostCount != 5 {
		t.Errorf("mismatched basic fields: %+v", p)
	}
	if p.StyleSummary != "original summary" {
		t.Errorf("got summary %q, want %q", p.StyleSummary, "original summary")
	}
	if !reflect.DeepEqual(p.TopKeywords, []string{"alpha", "beta"}) {
		t.Errorf("got keywords %v, want %v", p.TopKeywords, []string{"alpha", "beta"})
	}

	// 3. Test UPDATE path of Upsert (Verify conflict resolution works)
	emb2 := makeEmbedding(1024, "0.2")
	err = q.UpsertStyleProfile(ctx, UpsertStyleProfileParams{
		UserID:       uid,
		Platform:     platform,
		Embedding:    emb2,
		TopKeywords:  []string{"gamma"},
		StyleSummary: "updated summary",
		PostCount:    10,
	})
	if err != nil {
		t.Fatalf("secondary upsert (update) failed: %v", err)
	}

	updated, err := q.GetStyleProfile(ctx, GetStyleProfileParams{UserID: uid, Platform: platform})
	if err != nil {
		t.Fatalf("get updated profile failed: %v", err)
	}
	if updated.PostCount != 10 || updated.StyleSummary != "updated summary" {
		t.Errorf("upsert failed to update existing record: %+v", updated)
	}

	// 4. Test pgvector similarity search
	rows, err := q.FindSimilarUsers(ctx, FindSimilarUsersParams{
		QueryEmbedding: emb2,
		LimitVal:       10,
	})
	if err != nil {
		t.Fatalf("find similar failed: %v", err)
	}

	found := false
	for _, r := range rows {
		if r.UserID == uid {
			found = true
			break
		}
	}
	if !found {
		t.Errorf("expected user %s in similarity results, but was not found", uid)
	}
}

func TestStoreSaveTransaction(t *testing.T) {
	conn, q := setupTestDB(t)
	ctx := context.Background()

	txHash := fmt.Sprintf("0x_%d", time.Now().UnixNano())
	amount := "10.0"
	asset := "ETH"
	network := "eip155:1"
	recipient := "0x1234567890abcdef"
	sender := "0xabcdef1234567890"

	t.Cleanup(func() {
		_, _ = conn.ExecContext(context.Background(), "DELETE FROM transactions WHERE x402_transaction_hash = $1", txHash)
	})

	ret, err := q.SaveTransaction(ctx, SaveTransactionParams{
		X402TransactionHash: txHash,
		Amount:              amount,
		Asset:               asset,
		Network:             network,
		Recipient:           recipient,
		Sender:              sender,
	})
	if err != nil {
		t.Fatalf("SaveTransaction error: %v", err)
	}

	if len(ret) != 1 {
		t.Fatalf("expected 1 result row returned, got %d", len(ret))
	}

	saved := ret[0]
	if saved.ID <= 0 {
		t.Errorf("expected positive ID, got %d", saved.ID)
	}
	if !saved.CreatedAt.Valid {
		t.Error("expected CreatedAt to be set")
	}

	// Since the sqlc query only returns `id` and `created_at`,
	// verify directly that PostgreSQL stored the remaining columns accurately:
	var (
		gotHash, gotAmount, gotAsset, gotNetwork, gotRecipient, gotSender string
	)
	err = conn.QueryRowContext(ctx, `
		SELECT x402_transaction_hash, amount, asset, network, recipient, sender
		FROM transactions
		WHERE id = $1
	`, saved.ID).Scan(&gotHash, &gotAmount, &gotAsset, &gotNetwork, &gotRecipient, &gotSender)
	if err != nil {
		t.Fatalf("failed to query inserted transaction from db: %v", err)
	}

	if gotHash != txHash || gotAmount != amount || gotAsset != asset ||
		gotNetwork != network || gotRecipient != recipient || gotSender != sender {
		t.Errorf("stored values do not match input params: got (%s, %s, %s, %s, %s, %s)",
			gotHash, gotAmount, gotAsset, gotNetwork, gotRecipient, gotSender)
	}
}