package store

import (
	"database/sql"
	"os"
	"path/filepath"
	"sort"
	"testing"

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

// Runs migrations/032 against a database seeded with a realistic snapshot of
// the Trello Shopping board, and asserts it imports exactly the two groups
// that were asked for and leaves the rest behind.
//
// The migration is executed from the real file rather than a copy, so the
// thing under test is what actually ships.
func TestMigration032_ImportsOnlyHardwareStoreAndUncategorized(t *testing.T) {
	db := setupImportTestDB(t)
	defer func() { _ = db.Close() }()

	seedShoppingBoard(t, db, []card{
		// Unlisted cards — these render as "Uncategorized" and are imported.
		{"Broccoli", ""},
		{"Ibuprofen", ""},
		{"dog water bowl", ""},
		// Hardware Store — imported.
		{"6x8 plywood", "Hardware Store"},
		{"Wasp traps", "Hardware Store"},
		// Deliberately left behind.
		{"Pearl Waffles", "Costco"},
		{"Pita Bread", "Perennial "},
		{"Rip stop", "online"},
	})

	runMigration032(t, db)

	got := userItems(t, db)
	want := []item{
		{"6x8 plywood", "Hardware Store"},
		{"Broccoli", "Uncategorized"},
		{"Ibuprofen", "Uncategorized"},
		{"Wasp traps", "Hardware Store"},
		{"dog water bowl", "Uncategorized"},
	}
	assertItems(t, got, want)
}

// A card whose list has surrounding whitespace must not create a store that
// differs from the trimmed name only by invisible characters. "Perennial " on
// the real board was exactly this.
func TestMigration032_TrimsStoreNames(t *testing.T) {
	db := setupImportTestDB(t)
	defer func() { _ = db.Close() }()

	seedShoppingBoard(t, db, []card{
		{"Trimmed", "  Hardware Store  "},
	})
	runMigration032(t, db)

	got := userItems(t, db)
	assertItems(t, got, []item{{"Trimmed", "Hardware Store"}})
}

// Cards on other boards must not be swept in — only the Shopping board.
func TestMigration032_IgnoresOtherBoards(t *testing.T) {
	db := setupImportTestDB(t)
	defer func() { _ = db.Close() }()

	seedBoard(t, db, "camping", "Camping")
	mustExec(t, db, `INSERT INTO cards (id, name, board_id, list_name) VALUES ('c1','Tent pegs','camping','')`)
	seedShoppingBoard(t, db, []card{{"Broccoli", ""}})

	runMigration032(t, db)

	got := userItems(t, db)
	assertItems(t, got, []item{{"Broccoli", "Uncategorized"}})
}

// Replaying the migration must not duplicate rows.
func TestMigration032_IsReplaySafe(t *testing.T) {
	db := setupImportTestDB(t)
	defer func() { _ = db.Close() }()

	seedShoppingBoard(t, db, []card{{"Broccoli", ""}, {"Wasp traps", "Hardware Store"}})

	runMigration032(t, db)
	runMigration032(t, db)

	got := userItems(t, db)
	assertItems(t, got, []item{
		{"Broccoli", "Uncategorized"},
		{"Wasp traps", "Hardware Store"},
	})
}

// An item the user already added by hand must not gain a duplicate.
func TestMigration032_SkipsItemsAlreadyPresent(t *testing.T) {
	db := setupImportTestDB(t)
	defer func() { _ = db.Close() }()

	mustExec(t, db, `INSERT INTO user_shopping_items (name, store) VALUES ('Wasp traps','Hardware Store')`)
	seedShoppingBoard(t, db, []card{{"Wasp traps", "Hardware Store"}})

	runMigration032(t, db)

	assertItems(t, userItems(t, db), []item{{"Wasp traps", "Hardware Store"}})
}

// On a database with no cached Trello cards the migration is a no-op rather
// than an error — that is the state of any fresh dev database.
func TestMigration032_NoOpWithNoCards(t *testing.T) {
	db := setupImportTestDB(t)
	defer func() { _ = db.Close() }()

	runMigration032(t, db)

	if got := userItems(t, db); len(got) != 0 {
		t.Errorf("expected no items, got %v", got)
	}
}

// ── helpers ──────────────────────────────────────────────────────────────────

type card struct{ name, listName string }
type item struct{ Name, Store string }

func setupImportTestDB(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "import.db"))
	if err != nil {
		t.Fatalf("open test db: %v", err)
	}
	db.SetMaxOpenConns(1)
	mustExec(t, db, `
		CREATE TABLE boards (id TEXT PRIMARY KEY, name TEXT NOT NULL);
		CREATE TABLE cards (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			board_id TEXT NOT NULL,
			list_name TEXT
		);
		CREATE TABLE user_shopping_items (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			store TEXT NOT NULL,
			section TEXT NOT NULL DEFAULT '',
			checked INTEGER DEFAULT 0,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
	`)
	return db
}

func seedBoard(t *testing.T, db *sql.DB, id, name string) {
	t.Helper()
	mustExec(t, db, `INSERT INTO boards (id, name) VALUES (?, ?)`, id, name)
}

func seedShoppingBoard(t *testing.T, db *sql.DB, cards []card) {
	t.Helper()
	seedBoard(t, db, "shop", "Shopping")
	for i, c := range cards {
		mustExec(t, db,
			`INSERT INTO cards (id, name, board_id, list_name) VALUES (?, ?, 'shop', ?)`,
			"card-"+string(rune('a'+i)), c.name, c.listName)
	}
}

func runMigration032(t *testing.T, db *sql.DB) {
	t.Helper()
	path := filepath.Join("..", "..", "migrations", "032_import_trello_shopping_items.sql")
	sqlBytes, err := os.ReadFile(path)
	if err != nil {
		t.Fatalf("reading migration: %v", err)
	}
	if _, err := db.Exec(string(sqlBytes)); err != nil {
		t.Fatalf("executing migration: %v", err)
	}
}

func userItems(t *testing.T, db *sql.DB) []item {
	t.Helper()
	rows, err := db.Query(`SELECT name, store FROM user_shopping_items`)
	if err != nil {
		t.Fatalf("querying items: %v", err)
	}
	defer func() { _ = rows.Close() }()

	var out []item
	for rows.Next() {
		var it item
		if err := rows.Scan(&it.Name, &it.Store); err != nil {
			t.Fatalf("scan: %v", err)
		}
		out = append(out, it)
	}
	return out
}

func assertItems(t *testing.T, got, want []item) {
	t.Helper()
	sortItems(got)
	sortItems(want)
	if len(got) != len(want) {
		t.Fatalf("got %d items %v, want %d %v", len(got), got, len(want), want)
	}
	for i := range got {
		if got[i] != want[i] {
			t.Errorf("item %d: got %+v, want %+v", i, got[i], want[i])
		}
	}
}

func sortItems(items []item) {
	sort.Slice(items, func(i, j int) bool {
		if items[i].Store != items[j].Store {
			return items[i].Store < items[j].Store
		}
		return items[i].Name < items[j].Name
	})
}

func mustExec(t *testing.T, db *sql.DB, query string, args ...any) {
	t.Helper()
	if _, err := db.Exec(query, args...); err != nil {
		t.Fatalf("exec %q: %v", query, err)
	}
}