package infra

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

	_ "github.com/mattn/go-sqlite3"
)

func newTestDB(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatalf("open db: %v", err)
	}
	t.Cleanup(func() { db.Close() })
	return db
}

func TestApplyVersionedMigrations(t *testing.T) {
	t.Run("aplica todas desde cero y registra schema_migrations", func(t *testing.T) {
		db := newTestDB(t)
		fsys := fstest.MapFS{
			"migrations/001_init.sql":      {Data: []byte("CREATE TABLE users (id INTEGER PRIMARY KEY);")},
			"migrations/002_add_email.sql": {Data: []byte("ALTER TABLE users ADD COLUMN email TEXT;")},
		}

		if err := ApplyVersionedMigrations(db, fsys, "migrations"); err != nil {
			t.Fatalf("unexpected error: %v", err)
		}

		// Verify schema_migrations has 2 rows
		var count int
		if err := db.QueryRow("SELECT COUNT(*) FROM schema_migrations").Scan(&count); err != nil {
			t.Fatalf("count schema_migrations: %v", err)
		}
		if count != 2 {
			t.Errorf("schema_migrations rows: got %d, want 2", count)
		}

		// Verify MAX(version) == 2
		var maxV int
		if err := db.QueryRow("SELECT MAX(version) FROM schema_migrations").Scan(&maxV); err != nil {
			t.Fatalf("max version: %v", err)
		}
		if maxV != 2 {
			t.Errorf("max version: got %d, want 2", maxV)
		}

		// Verify the table from migration 001 exists
		if _, err := db.Exec("INSERT INTO users (id) VALUES (1)"); err != nil {
			t.Errorf("users table not created: %v", err)
		}
	})

	t.Run("idempotente por version, no vuelve a aplicar", func(t *testing.T) {
		db := newTestDB(t)
		fsys := fstest.MapFS{
			"migrations/001_init.sql": {Data: []byte("CREATE TABLE things (id INTEGER PRIMARY KEY);")},
		}

		// First run
		if err := ApplyVersionedMigrations(db, fsys, "migrations"); err != nil {
			t.Fatalf("first run: %v", err)
		}
		// Second run — must not error even though CREATE TABLE would fail normally
		if err := ApplyVersionedMigrations(db, fsys, "migrations"); err != nil {
			t.Fatalf("second run: %v", err)
		}

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

	t.Run("migracion intermedia falla, version anterior no avanza", func(t *testing.T) {
		db := newTestDB(t)
		fsys := fstest.MapFS{
			"migrations/001_init.sql": {Data: []byte("CREATE TABLE ok (id INTEGER PRIMARY KEY);")},
			"migrations/002_bad.sql":  {Data: []byte("THIS IS NOT VALID SQL !!!;")},
			"migrations/003_more.sql": {Data: []byte("CREATE TABLE more (id INTEGER PRIMARY KEY);")},
		}

		err := ApplyVersionedMigrations(db, fsys, "migrations")
		if err == nil {
			t.Fatal("expected error from bad migration, got nil")
		}

		// Only version 1 should be recorded
		var maxV int
		if err2 := db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&maxV); err2 != nil {
			t.Fatalf("read version: %v", err2)
		}
		if maxV != 1 {
			t.Errorf("max version after failure: got %d, want 1", maxV)
		}

		// Migration 003 table must NOT exist
		if _, err2 := db.Exec("INSERT INTO more (id) VALUES (1)"); err2 == nil {
			t.Error("table 'more' should not exist after failed migration 002")
		}
	})

	t.Run("archivos sin prefijo numerico se ignoran", func(t *testing.T) {
		db := newTestDB(t)
		fsys := fstest.MapFS{
			"migrations/README.sql":   {Data: []byte("-- this should be ignored")},
			"migrations/init.sql":     {Data: []byte("CREATE TABLE bad (id INTEGER PRIMARY KEY);")},
			"migrations/001_good.sql": {Data: []byte("CREATE TABLE good (id INTEGER PRIMARY KEY);")},
		}

		if err := ApplyVersionedMigrations(db, fsys, "migrations"); err != nil {
			t.Fatalf("unexpected error: %v", err)
		}

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

		// Only 'good' table should exist
		if _, err := db.Exec("INSERT INTO good (id) VALUES (1)"); err != nil {
			t.Errorf("table 'good' should exist: %v", err)
		}
		if _, err := db.Exec("INSERT INTO bad (id) VALUES (1)"); err == nil {
			t.Error("table 'bad' should NOT exist")
		}
	})

	t.Run("dir vacio no error y no crea schema_migrations", func(t *testing.T) {
		db := newTestDB(t)
		fsys := fstest.MapFS{
			"migrations/.keep": {Data: []byte("")},
		}

		if err := ApplyVersionedMigrations(db, fsys, "migrations"); err != nil {
			t.Fatalf("unexpected error on empty dir: %v", err)
		}

		// schema_migrations IS created (the CREATE TABLE IF NOT EXISTS runs regardless)
		// but it must be empty
		var count int
		if err := db.QueryRow("SELECT COUNT(*) FROM schema_migrations").Scan(&count); err != nil {
			t.Fatalf("count schema_migrations: %v", err)
		}
		if count != 0 {
			t.Errorf("expected 0 rows in schema_migrations, got %d", count)
		}
	})
}