package database

import (
	"database/sql"
	"path/filepath"
	"testing"
)

func TestNewDB_RunsMigrationsAndSupportsQueries(t *testing.T) {
	dbPath := filepath.Join(t.TempDir(), "test.db")
	db, err := NewDB(dbPath)
	if err != nil {
		t.Fatalf("NewDB failed: %v", err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		t.Fatalf("Ping failed: %v", err)
	}

	var exists bool
	err = db.With(func(conn *sql.DB) error {
		return conn.QueryRow("SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type='table' AND name='settings')").Scan(&exists)
	})
	if err != nil {
		t.Fatalf("query failed: %v", err)
	}
	if !exists {
		t.Fatal("expected settings table after migrations")
	}
}

func TestWithTx_RollbackOnError(t *testing.T) {
	dbPath := filepath.Join(t.TempDir(), "tx.db")
	db, err := NewDB(dbPath)
	if err != nil {
		t.Fatalf("NewDB failed: %v", err)
	}
	defer db.Close()

	_ = db.WithTx(func(tx *sql.Tx) error {
		if _, err := tx.Exec("INSERT INTO settings (key, value) VALUES (?, ?)", "rollback_key", "x"); err != nil {
			return err
		}
		return sql.ErrTxDone
	})

	var count int
	if err := db.With(func(conn *sql.DB) error {
		return conn.QueryRow("SELECT COUNT(*) FROM settings WHERE key = ?", "rollback_key").Scan(&count)
	}); err != nil {
		t.Fatalf("count query failed: %v", err)
	}
	if count != 0 {
		t.Fatalf("expected rollback, found %d rows", count)
	}
}