package database

import (
	"database/sql"
	"testing"

	_ "github.com/tursodatabase/go-libsql"
)

// openTestDB creates an in-memory database for testing
func openTestDB(t *testing.T) *Database {
	t.Helper()
	db, err := sql.Open("libsql", ":memory:")
	if err != nil {
		t.Fatalf("failed to open test database: %v", err)
	}
	return &Database{DB: db}
}

func TestDatabase_SyncNil(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	if db.Sync != nil {
		t.Error("expected Sync to be nil for non-remote database")
	}
}

func TestDatabase_EmbeddedDB(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	// Should be able to execute queries via embedded *sql.DB
	_, err := db.Exec("CREATE TABLE test (id TEXT)")
	if err != nil {
		t.Errorf("failed to create table: %v", err)
	}

	_, err = db.Exec("INSERT INTO test (id) VALUES (?)", "1")
	if err != nil {
		t.Errorf("failed to insert: %v", err)
	}

	var id string
	err = db.QueryRow("SELECT id FROM test").Scan(&id)
	if err != nil {
		t.Errorf("failed to query: %v", err)
	}
	if id != "1" {
		t.Errorf("expected '1', got '%s'", id)
	}
}

func TestDatabase_TableExists(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	if db.TableExists("users") {
		t.Error("expected table to not exist")
	}

	db.Exec("CREATE TABLE users (id TEXT)")

	if !db.TableExists("users") {
		t.Error("expected table to exist")
	}
}

func TestDatabase_CreateTable(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	err := db.CreateTable("users", []Column{
		{Name: "ID", Type: "TEXT", Primary: true},
		{Name: "Name", Type: "TEXT"},
		{Name: "Age", Type: "INTEGER"},
	})
	if err != nil {
		t.Fatalf("CreateTable failed: %v", err)
	}

	if !db.TableExists("users") {
		t.Error("expected table to exist after creation")
	}

	// Should be able to insert
	_, err = db.Exec("INSERT INTO users (ID, Name, Age) VALUES (?, ?, ?)", "1", "Alice", 30)
	if err != nil {
		t.Errorf("failed to insert into created table: %v", err)
	}
}

func TestDatabase_GetColumns(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	db.Exec("CREATE TABLE users (ID TEXT, Name TEXT, Age INTEGER)")

	cols := db.GetColumns("users")
	if len(cols) != 3 {
		t.Fatalf("expected 3 columns, got %d", len(cols))
	}

	expected := map[string]bool{"ID": true, "Name": true, "Age": true}
	for _, col := range cols {
		if !expected[col] {
			t.Errorf("unexpected column: %s", col)
		}
	}
}

func TestDatabase_AddColumn(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	db.Exec("CREATE TABLE users (ID TEXT)")

	err := db.AddColumn("users", Column{Name: "Email", Type: "TEXT", Default: "''"})
	if err != nil {
		t.Fatalf("AddColumn failed: %v", err)
	}

	cols := db.GetColumns("users")
	if len(cols) != 2 {
		t.Fatalf("expected 2 columns after AddColumn, got %d", len(cols))
	}

	// Verify default value works
	db.Exec("INSERT INTO users (ID) VALUES (?)", "1")
	var email string
	db.QueryRow("SELECT Email FROM users WHERE ID = ?", "1").Scan(&email)
	if email != "" {
		t.Errorf("expected empty default, got '%s'", email)
	}
}