package store_test

import (
	"database/sql"
	"os"
	"strings"
	"testing"

	_ "github.com/mattn/go-sqlite3"
	migrate "krafttrainer/internal/migrate"
	"krafttrainer/internal/store"
	"krafttrainer/migrations"
)

// newTestStore creates a temporary SQLite database file, runs all migrations,
// and returns a fully initialised *store.Store. A t.Cleanup removes the temp
// file and closes the store after the test finishes.
//
// We cannot use ":memory:" directly because store.New appends query parameters
// to the path string with "?", which would corrupt a URI already containing "?".
// A temp file avoids this and provides the same isolation guarantee.
func newTestStore(t *testing.T) *store.Store {
	t.Helper()

	// Create a temp file; store.New will open it by path.
	f, err := os.CreateTemp("", "krafttrainer-test-*.db")
	if err != nil {
		t.Fatalf("create temp db file: %v", err)
	}
	f.Close()
	dbPath := f.Name()

	s, err := store.New(dbPath)
	if err != nil {
		os.Remove(dbPath)
		t.Fatalf("store.New: %v", err)
	}
	t.Cleanup(func() {
		s.Close()
		os.Remove(dbPath)
	})

	if err := migrate.Run(s.DB(), migrations.FS); err != nil {
		t.Fatalf("migrate.Run: %v", err)
	}
	return s
}

// seedUser inserts a user row and returns the new id.
func seedUser(t *testing.T, db *sql.DB, name string) int64 {
	t.Helper()
	res, err := db.Exec(`INSERT INTO users (name) VALUES (?)`, name)
	if err != nil {
		t.Fatalf("seedUser: %v", err)
	}
	id, _ := res.LastInsertId()
	return id
}

// seedExercise inserts a minimal exercise row and returns the new id.
func seedExercise(t *testing.T, db *sql.DB, name string, userID int64) int64 {
	t.Helper()
	res, err := db.Exec(
		`INSERT INTO exercises (name, muscle_group, weight_step_kg, user_id) VALUES (?, 'brust', 2.5, ?)`,
		name, userID,
	)
	if err != nil {
		t.Fatalf("seedExercise %q: %v", name, err)
	}
	id, _ := res.LastInsertId()
	return id
}

// seedTrainingSet inserts a training set and returns its id.
func seedTrainingSet(t *testing.T, db *sql.DB, name string, userID int64) int64 {
	t.Helper()
	res, err := db.Exec(
		`INSERT INTO training_sets (name, user_id) VALUES (?, ?)`,
		name, userID,
	)
	if err != nil {
		t.Fatalf("seedTrainingSet %q: %v", name, err)
	}
	id, _ := res.LastInsertId()
	return id
}

// seedSetExercise links an exercise to a training set at the given position.
func seedSetExercise(t *testing.T, db *sql.DB, setID, exerciseID int64, position int) {
	t.Helper()
	_, err := db.Exec(
		`INSERT INTO set_exercises (set_id, exercise_id, position) VALUES (?, ?, ?)`,
		setID, exerciseID, position,
	)
	if err != nil {
		t.Fatalf("seedSetExercise set=%d exercise=%d: %v", setID, exerciseID, err)
	}
}

// openSession inserts a session that has no ended_at (open).
func openSession(t *testing.T, db *sql.DB, setID, userID int64) int64 {
	t.Helper()
	res, err := db.Exec(
		`INSERT INTO sessions (set_id, user_id) VALUES (?, ?)`,
		setID, userID,
	)
	if err != nil {
		t.Fatalf("openSession: %v", err)
	}
	id, _ := res.LastInsertId()
	return id
}

// closedSession inserts a session with ended_at set.
func closedSession(t *testing.T, db *sql.DB, setID, userID int64) int64 {
	t.Helper()
	res, err := db.Exec(
		`INSERT INTO sessions (set_id, user_id, ended_at) VALUES (?, ?, CURRENT_TIMESTAMP)`,
		setID, userID,
	)
	if err != nil {
		t.Fatalf("closedSession: %v", err)
	}
	id, _ := res.LastInsertId()
	return id
}

// ---------------------------------------------------------------------------
// GetActiveSession tests
// ---------------------------------------------------------------------------

func TestGetActiveSession_NoOpenSession(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Alice")

	got, err := s.GetActiveSession(userID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got != nil {
		t.Errorf("expected nil, got session id=%d", got.ID)
	}
}

func TestGetActiveSession_OnlyClosedSessions(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Bob")
	setID := seedTrainingSet(t, s.DB(), "Set A", userID)
	closedSession(t, s.DB(), setID, userID)

	got, err := s.GetActiveSession(userID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got != nil {
		t.Errorf("expected nil for user with only closed sessions, got id=%d", got.ID)
	}
}

func TestGetActiveSession_ReturnsOpenSession(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Carol")
	setID := seedTrainingSet(t, s.DB(), "Brust-Tag", userID)
	sessionID := openSession(t, s.DB(), setID, userID)

	got, err := s.GetActiveSession(userID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got == nil {
		t.Fatal("expected non-nil session, got nil")
	}
	if got.ID != sessionID {
		t.Errorf("session ID: want %d, got %d", sessionID, got.ID)
	}
	if got.SetID != setID {
		t.Errorf("set ID: want %d, got %d", setID, got.SetID)
	}
	if got.SetName != "Brust-Tag" {
		t.Errorf("set name: want %q, got %q", "Brust-Tag", got.SetName)
	}
	if got.EndedAt != nil {
		t.Errorf("expected EndedAt to be nil for open session")
	}
}

func TestGetActiveSession_IsolatedByUser(t *testing.T) {
	// An open session belonging to another user must not be returned.
	s := newTestStore(t)
	user1 := seedUser(t, s.DB(), "User1")
	user2 := seedUser(t, s.DB(), "User2")
	setID := seedTrainingSet(t, s.DB(), "Set", user1)
	openSession(t, s.DB(), setID, user1)

	got, err := s.GetActiveSession(user2)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got != nil {
		t.Errorf("expected nil for user2, got session id=%d (belongs to user1)", got.ID)
	}
}

func TestGetActiveSession_ReturnsLatestWhenMultipleOpen(t *testing.T) {
	// If (by some data anomaly) two open sessions exist, the latest one is
	// returned (ORDER BY started_at DESC LIMIT 1).
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Dave")
	setID := seedTrainingSet(t, s.DB(), "Set", userID)

	firstID := openSession(t, s.DB(), setID, userID)
	secondID := openSession(t, s.DB(), setID, userID)

	got, err := s.GetActiveSession(userID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got == nil {
		t.Fatal("expected a session, got nil")
	}
	// The second insert has a higher id; SQLite CURRENT_TIMESTAMP resolution
	// may produce equal timestamps, so we accept either the second or the one
	// with the higher id (both reflect "latest").
	if got.ID != secondID && got.ID != firstID {
		t.Errorf("unexpected session id %d (want %d or %d)", got.ID, firstID, secondID)
	}
}

// ---------------------------------------------------------------------------
// CreateSession / SESSION_OPEN guard tests
// ---------------------------------------------------------------------------

func TestCreateSession_RejectsWhenOpenSessionExists(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Eve")
	setID := seedTrainingSet(t, s.DB(), "Leg Day", userID)

	// Create a first session via the store so the guard logic runs.
	first, err := s.CreateSession(userID, setID)
	if err != nil {
		t.Fatalf("first CreateSession: %v", err)
	}
	if first == nil {
		t.Fatal("first CreateSession returned nil")
	}

	// A second call must be rejected with SESSION_OPEN.
	_, err = s.CreateSession(userID, setID)
	if err == nil {
		t.Fatal("expected SESSION_OPEN error, got nil")
	}
	if !strings.Contains(err.Error(), "SESSION_OPEN") {
		t.Errorf("expected error to contain SESSION_OPEN, got: %v", err)
	}
}

func TestCreateSession_AllowsNewSessionAfterClose(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Frank")
	setID := seedTrainingSet(t, s.DB(), "Push", userID)

	first, err := s.CreateSession(userID, setID)
	if err != nil {
		t.Fatalf("CreateSession: %v", err)
	}

	_, err = s.EndSession(first.ID, userID, "")
	if err != nil {
		t.Fatalf("EndSession: %v", err)
	}

	second, err := s.CreateSession(userID, setID)
	if err != nil {
		t.Fatalf("CreateSession after close: %v", err)
	}
	if second == nil {
		t.Fatal("expected new session, got nil")
	}
	if second.ID == first.ID {
		t.Errorf("expected a new session ID, got the same id=%d", second.ID)
	}
}

func TestCreateSession_OpenSessionOfOtherUserDoesNotBlock(t *testing.T) {
	// An open session for user1 must not prevent user2 from starting one.
	s := newTestStore(t)
	user1 := seedUser(t, s.DB(), "Greg")
	user2 := seedUser(t, s.DB(), "Hanna")
	set1 := seedTrainingSet(t, s.DB(), "Set1", user1)
	set2 := seedTrainingSet(t, s.DB(), "Set2", user2)

	if _, err := s.CreateSession(user1, set1); err != nil {
		t.Fatalf("CreateSession user1: %v", err)
	}

	sess2, err := s.CreateSession(user2, set2)
	if err != nil {
		t.Fatalf("CreateSession user2 should succeed: %v", err)
	}
	if sess2 == nil {
		t.Fatal("expected session for user2, got nil")
	}
}

func TestCreateSession_ErrorOnNonExistentSet(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Iris")

	_, err := s.CreateSession(userID, 9999)
	if err == nil {
		t.Fatal("expected error for non-existent set, got nil")
	}
	if !strings.Contains(err.Error(), "existiert nicht") {
		t.Errorf("expected 'existiert nicht' in error, got: %v", err)
	}
}

// ---------------------------------------------------------------------------
// GetSetExercises tests
// ---------------------------------------------------------------------------

func TestGetSetExercises_EmptySet(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Jack")
	setID := seedTrainingSet(t, s.DB(), "Empty Set", userID)

	exercises, err := s.GetSetExercises(setID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(exercises) != 0 {
		t.Errorf("expected 0 exercises, got %d", len(exercises))
	}
}

func TestGetSetExercises_ReturnsExercisesInPositionOrder(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Kira")
	setID := seedTrainingSet(t, s.DB(), "Full Body", userID)

	e1 := seedExercise(t, s.DB(), "Bankdrücken", userID)
	e2 := seedExercise(t, s.DB(), "Kniebeugen", userID)
	e3 := seedExercise(t, s.DB(), "Kreuzheben", userID)

	// Insert in non-sequential position order to verify ORDER BY position.
	seedSetExercise(t, s.DB(), setID, e3, 2)
	seedSetExercise(t, s.DB(), setID, e1, 0)
	seedSetExercise(t, s.DB(), setID, e2, 1)

	exercises, err := s.GetSetExercises(setID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(exercises) != 3 {
		t.Fatalf("expected 3 exercises, got %d", len(exercises))
	}
	wantOrder := []int64{e1, e2, e3}
	for i, ex := range exercises {
		if ex.ID != wantOrder[i] {
			t.Errorf("position %d: want exercise id=%d, got id=%d", i, wantOrder[i], ex.ID)
		}
	}
}

func TestGetSetExercises_ExcludesSoftDeletedExercises(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Leo")
	setID := seedTrainingSet(t, s.DB(), "Set", userID)

	active := seedExercise(t, s.DB(), "Aktive Übung", userID)
	deleted := seedExercise(t, s.DB(), "Gelöschte Übung", userID)

	seedSetExercise(t, s.DB(), setID, active, 0)
	seedSetExercise(t, s.DB(), setID, deleted, 1)

	// Soft-delete the second exercise.
	if _, err := s.DB().Exec(`UPDATE exercises SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?`, deleted); err != nil {
		t.Fatalf("soft-delete exercise: %v", err)
	}

	exercises, err := s.GetSetExercises(setID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(exercises) != 1 {
		t.Fatalf("expected 1 exercise, got %d", len(exercises))
	}
	if exercises[0].ID != active {
		t.Errorf("expected active exercise id=%d, got id=%d", active, exercises[0].ID)
	}
}

func TestGetSetExercises_NonExistentSet(t *testing.T) {
	s := newTestStore(t)

	exercises, err := s.GetSetExercises(9999)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(exercises) != 0 {
		t.Errorf("expected 0 exercises for non-existent set, got %d", len(exercises))
	}
}

func TestGetSetExercises_PopulatesAllFields(t *testing.T) {
	s := newTestStore(t)
	userID := seedUser(t, s.DB(), "Mia")
	setID := seedTrainingSet(t, s.DB(), "Detail Set", userID)
	exID := seedExercise(t, s.DB(), "Rudern", userID)
	seedSetExercise(t, s.DB(), setID, exID, 0)

	exercises, err := s.GetSetExercises(setID)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(exercises) != 1 {
		t.Fatalf("expected 1 exercise, got %d", len(exercises))
	}
	ex := exercises[0]
	if ex.ID != exID {
		t.Errorf("ID: want %d, got %d", exID, ex.ID)
	}
	if ex.Name != "Rudern" {
		t.Errorf("Name: want %q, got %q", "Rudern", ex.Name)
	}
	if ex.MuscleGroup != "brust" {
		t.Errorf("MuscleGroup: want %q, got %q", "brust", ex.MuscleGroup)
	}
	if ex.WeightStepKg != 2.5 {
		t.Errorf("WeightStepKg: want 2.5, got %f", ex.WeightStepKg)
	}
	if ex.CreatedAt.IsZero() {
		t.Error("CreatedAt must not be zero")
	}
}