package database

import (
	"database/sql"
	"testing"

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

type TestEntity struct {
	Model
	Name   string
	Status string
}

// openTestDatabase creates an in-memory database for collection tests
func openTestDatabase() *Database {
	db, _ := sql.Open("libsql", ":memory:")
	return &Database{DB: db}
}

func TestCollection_AutoCreateTable(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	// Table should be auto-created by Manage
	entities := Manage(db, new(TestEntity))

	// Verify table exists
	var tableName string
	err := db.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name='TestEntity'").Scan(&tableName)
	if err != nil {
		t.Fatalf("table was not created: %v", err)
	}
	if tableName != "TestEntity" {
		t.Errorf("expected table 'TestEntity', got '%s'", tableName)
	}

	// Should be able to insert without manual table creation
	entity := &TestEntity{Name: "Test", Status: "active"}
	_, err = entities.Insert(entity)
	if err != nil {
		t.Fatalf("Insert failed: %v", err)
	}
}

func TestCollection_AutoAddColumns(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	// Create table with only ID column
	db.Exec("CREATE TABLE TestEntity (ID TEXT PRIMARY KEY)")

	// Manage should add missing columns
	entities := Manage(db, new(TestEntity))

	// Should be able to insert with all fields
	entity := &TestEntity{Name: "Test", Status: "active"}
	_, err := entities.Insert(entity)
	if err != nil {
		t.Fatalf("Insert failed after column migration: %v", err)
	}

	// Verify we can read the data back
	found, err := entities.Get(entity.ID)
	if err != nil {
		t.Fatalf("Get failed: %v", err)
	}
	if found.Name != "Test" {
		t.Errorf("expected Name 'Test', got '%s'", found.Name)
	}
}

func TestCollection_CRUD(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity))

	// Test Insert
	entity := &TestEntity{Name: "Test", Status: "active"}
	id, err := entities.Insert(entity)
	if err != nil {
		t.Fatalf("Insert failed: %v", err)
	}
	if id == "" {
		t.Error("expected ID to be generated")
	}
	if entity.ID != id {
		t.Errorf("expected entity.ID to be %s, got %s", id, entity.ID)
	}

	// Test Get
	found, err := entities.Get(id)
	if err != nil {
		t.Fatalf("Get failed: %v", err)
	}
	if found.Name != "Test" {
		t.Errorf("expected Name 'Test', got '%s'", found.Name)
	}

	// Test Update
	found.Status = "inactive"
	err = entities.Update(found)
	if err != nil {
		t.Fatalf("Update failed: %v", err)
	}

	updated, _ := entities.Get(id)
	if updated.Status != "inactive" {
		t.Errorf("expected Status 'inactive', got '%s'", updated.Status)
	}

	// Test Search
	results, err := entities.Search("WHERE Status = ?", "inactive")
	if err != nil {
		t.Fatalf("Search failed: %v", err)
	}
	if len(results) != 1 {
		t.Errorf("expected 1 result, got %d", len(results))
	}

	// Test Count
	count := entities.Count("WHERE Status = ?", "inactive")
	if count != 1 {
		t.Errorf("expected count 1, got %d", count)
	}

	// Test Delete
	err = entities.Delete(found)
	if err != nil {
		t.Fatalf("Delete failed: %v", err)
	}

	_, err = entities.Get(id)
	if err != ErrNotFound {
		t.Errorf("expected ErrNotFound after delete, got %v", err)
	}
}

func TestCollection_First_NotFound(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity))

	_, err := entities.First("WHERE Name = ?", "nonexistent")
	if err != ErrNotFound {
		t.Errorf("expected ErrNotFound, got %v", err)
	}
}

func TestCollection_All(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity))

	// Insert a few entities
	entities.Insert(&TestEntity{Name: "One", Status: "active"})
	entities.Insert(&TestEntity{Name: "Two", Status: "active"})
	entities.Insert(&TestEntity{Name: "Three", Status: "inactive"})

	all, err := entities.All()
	if err != nil {
		t.Fatalf("All failed: %v", err)
	}
	if len(all) != 3 {
		t.Errorf("expected 3 entities, got %d", len(all))
	}
}

func TestCollection_WithIndex(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	// Test single field index via option
	Manage(db, new(TestEntity),
		WithIndex[TestEntity]("Name"),
	)

	// Verify index was created
	var indexName string
	err := db.QueryRow("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_testentity_name'").Scan(&indexName)
	if err != nil {
		t.Errorf("single field index not created: %v", err)
	}
}

func TestCollection_WithCompositeIndex(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	// Test multi-field index via option
	Manage(db, new(TestEntity),
		WithIndex[TestEntity]("Name", "Status"),
	)

	// Verify composite index was created
	var indexName string
	err := db.QueryRow("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_testentity_name_status'").Scan(&indexName)
	if err != nil {
		t.Errorf("composite index not created: %v", err)
	}
}

func TestCollection_WithUniqueIndex(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity),
		WithUniqueIndex[TestEntity]("Name"),
	)

	// Test that unique constraint works
	entities.Insert(&TestEntity{Name: "Unique", Status: "active"})
	_, err := entities.Insert(&TestEntity{Name: "Unique", Status: "active"})
	if err != ErrDuplicate {
		t.Errorf("expected ErrDuplicate, got %v", err)
	}
}

func TestCollection_DBAndTable(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity))

	if entities.DB() != db {
		t.Error("expected DB() to return the database")
	}

	if entities.Table() != "TestEntity" {
		t.Errorf("expected Table() to return 'TestEntity', got '%s'", entities.Table())
	}
}

func TestCollection_Update_NotFound(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity))

	entity := &TestEntity{Name: "Ghost", Status: "active"}
	entity.ID = "nonexistent-id"

	err := entities.Update(entity)
	if err != ErrNotFound {
		t.Errorf("expected ErrNotFound, got %v", err)
	}
}

func TestCollection_Delete_NotFound(t *testing.T) {
	db := openTestDatabase()
	defer db.Close()

	entities := Manage(db, new(TestEntity))

	entity := &TestEntity{Name: "Ghost", Status: "active"}
	entity.ID = "nonexistent-id"

	err := entities.Delete(entity)
	if err != ErrNotFound {
		t.Errorf("expected ErrNotFound, got %v", err)
	}
}