package db

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

	_ "modernc.org/sqlite"
)

func openTestDB(t *testing.T) *sql.DB {
	t.Helper()
	dbPath := filepath.Join(t.TempDir(), "test.db")
	db, err := OpenSQLite(dbPath)
	if err != nil {
		t.Fatalf("OpenSQLite: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	return db
}

func TestLoadMigrationsParses(t *testing.T) {
	fsys := fstest.MapFS{
		"001_initial.sql":              {Data: []byte("SELECT 1;")},
		"002_add_thing.sql":            {Data: []byte("SELECT 2;")},
		"README.md":                    {Data: []byte("ignore me")},
		"10_out_of_order_filename.sql": {Data: []byte("SELECT 10;")},
	}
	ms, err := LoadMigrations(fsys)
	if err != nil {
		t.Fatalf("LoadMigrations: %v", err)
	}
	if len(ms) != 3 {
		t.Fatalf("got %d migrations, want 3", len(ms))
	}
	versions := []int{ms[0].Version, ms[1].Version, ms[2].Version}
	want := []int{1, 2, 10}
	for i, v := range versions {
		if v != want[i] {
			t.Errorf("migration %d: version %d, want %d", i, v, want[i])
		}
	}
}

func TestLoadMigrationsDetectsDuplicateVersion(t *testing.T) {
	fsys := fstest.MapFS{
		"001_a.sql": {Data: []byte("")},
		"001_b.sql": {Data: []byte("")},
	}
	if _, err := LoadMigrations(fsys); err == nil {
		t.Fatal("expected duplicate-version error")
	}
}

func TestApplyAndIdempotent(t *testing.T) {
	db := openTestDB(t)
	fsys := fstest.MapFS{
		"001_create.sql": {Data: []byte("CREATE TABLE foo(id INTEGER PRIMARY KEY);")},
		"002_seed.sql":   {Data: []byte("INSERT INTO foo(id) VALUES (1);")},
	}
	ms, err := LoadMigrations(fsys)
	if err != nil {
		t.Fatalf("LoadMigrations: %v", err)
	}

	applied, err := Apply(db, ms)
	if err != nil {
		t.Fatalf("Apply (first): %v", err)
	}
	if len(applied) != 2 {
		t.Fatalf("first apply: got %d, want 2", len(applied))
	}

	// Re-apply should be a no-op.
	applied, err = Apply(db, ms)
	if err != nil {
		t.Fatalf("Apply (second): %v", err)
	}
	if len(applied) != 0 {
		t.Fatalf("second apply: got %d, want 0", len(applied))
	}

	v, err := CurrentVersion(db)
	if err != nil {
		t.Fatalf("CurrentVersion: %v", err)
	}
	if v != 2 {
		t.Errorf("CurrentVersion: got %d, want 2", v)
	}

	var count int
	if err := db.QueryRow(`SELECT COUNT(*) FROM foo`).Scan(&count); err != nil {
		t.Fatalf("count foo: %v", err)
	}
	if count != 1 {
		t.Errorf("foo row count: got %d, want 1", count)
	}
}

func TestApplyRollsBackOnFailure(t *testing.T) {
	db := openTestDB(t)
	fsys := fstest.MapFS{
		"001_ok.sql":  {Data: []byte("CREATE TABLE keep(id INTEGER PRIMARY KEY);")},
		"002_bad.sql": {Data: []byte("CREATE TABLE keep(id INTEGER); -- duplicate, will fail")},
	}
	ms, _ := LoadMigrations(fsys)
	_, err := Apply(db, ms)
	if err == nil {
		t.Fatal("expected migration 002 to fail")
	}

	// Migration 1 should be applied; 2 should not.
	v, _ := CurrentVersion(db)
	if v != 1 {
		t.Errorf("after partial failure: version %d, want 1", v)
	}
}

func TestApplyEmpty(t *testing.T) {
	db := openTestDB(t)
	applied, err := Apply(db, nil)
	if err != nil {
		t.Fatalf("Apply(nil): %v", err)
	}
	if len(applied) != 0 {
		t.Errorf("Apply(nil): %d applied, want 0", len(applied))
	}
	v, _ := CurrentVersion(db)
	if v != 0 {
		t.Errorf("CurrentVersion after empty Apply: %d, want 0", v)
	}
}