package norm

import (
	"database/sql"
	"testing"
	"time"

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

type User struct {
	ID         int
	Name       string
	Age        int
	Email      string
	Department sql.NullString
	Active     bool
	Salary     sql.NullFloat64
	CreatedAt  time.Time
}

type UserBasic struct {
	ID    int
	Name  string
	Email string
}

type Department struct {
	ID     int
	Name   string
	Budget float64
}

type SimpleStruct struct {
	Value int
}

func TestScannerWithDepartments(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, budget FROM departments ORDER BY id")
	if err != nil {
		t.Fatalf("Failed to query departments: %v", err)
	}

	var departments []Department
	err = ScanAll(rows, &departments)
	if err != nil {
		t.Fatalf("Failed to scan departments: %v", err)
	}

	if len(departments) != 3 {
		t.Errorf("Expected 3 departments, got %d", len(departments))
	}

	d1 := departments[0]
	if d1.ID != 1 || d1.Name != "Engineering" || d1.Budget != 500000.0 {
		t.Errorf("Engineering department data incorrect: %+v", d1)
	}

	d2 := departments[1]
	if d2.ID != 2 || d2.Name != "Marketing" || d2.Budget != 300000.0 {
		t.Errorf("Marketing department data incorrect: %+v", d2)
	}
}

func TestScannerBasicFunctionality(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, email FROM users ORDER BY id")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	var users []UserBasic
	err = ScanAll(rows, &users)
	if err != nil {
		t.Fatalf("Failed to scan users: %v", err)
	}

	if len(users) != 6 {
		t.Errorf("Expected 6 users, got %d", len(users))
	}

	if users[0].ID != 1 || users[0].Name != "John Doe" || users[0].Email != "john@example.com" {
		t.Errorf("Expected first user to be John Doe, got %+v", users[0])
	}

	if users[5].Name != "Diana Prince" || users[5].Email != "diana@example.com" {
		t.Errorf("Expected last user to be Diana Prince, got %+v", users[5])
	}
}

func TestScannerWithAllFields(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, age, email, department, active, salary, created_at FROM users ORDER BY id")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	var users []User
	err = ScanAll(rows, &users)
	if err != nil {
		t.Fatalf("Failed to scan users: %v", err)
	}

	if len(users) != 6 {
		t.Errorf("Expected 6 users, got %d", len(users))
	}

	u1 := users[0]
	if u1.ID != 1 || u1.Name != "John Doe" || u1.Age != 30 || u1.Email != "john@example.com" || !u1.Active {
		t.Errorf("User 1 basic data incorrect: %+v", u1)
	}
	if !u1.Department.Valid || u1.Department.String != "Engineering" {
		t.Errorf("User 1 department incorrect: %v", u1.Department)
	}
	if !u1.Salary.Valid || u1.Salary.Float64 != 75000.0 {
		t.Errorf("User 1 salary incorrect: %v", u1.Salary)
	}

	u3 := users[2]
	if u3.Name != "Bob Johnson" || u3.Active {
		t.Errorf("User 3 should be inactive Bob Johnson: %+v", u3)
	}

	if u1.CreatedAt.IsZero() {
		t.Error("User 1 created_at should not be zero")
	}
}

func TestScannerEarlyTermination(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, email FROM users ORDER BY id")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	scanner := NewScanner[UserBasic](rows)
	defer scanner.Close()
	var users []UserBasic
	count := 0

	for user := range scanner.Scan() {
		users = append(users, user)
		count++
		if count == 1 {
			break
		}
	}

	if len(users) != 1 {
		t.Errorf("Expected 1 user due to early termination, got %d", len(users))
	}

	if users[0].ID != 1 || users[0].Name != "John Doe" {
		t.Errorf("Expected first user to be John Doe, got %+v", users[0])
	}
}

func TestScannerEmptyResults(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, email FROM users WHERE id = 999")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	scanner := NewScanner[UserBasic](rows)
	defer scanner.Close()
	var users []UserBasic

	for user := range scanner.Scan() {
		users = append(users, user)
	}

	if len(users) != 0 {
		t.Errorf("Expected 0 users from empty query, got %d", len(users))
	}
}

func TestScannerMultipleIterations(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, email FROM users ORDER BY id LIMIT 1")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	scanner := NewScanner[UserBasic](rows)
	defer scanner.Close()
	seq := scanner.Scan()

	var users1 []UserBasic
	for user := range seq {
		users1 = append(users1, user)
	}

	var users2 []UserBasic
	for user := range seq {
		users2 = append(users2, user)
	}

	if len(users1) != 1 {
		t.Errorf("Expected 1 user from first iteration, got %d", len(users1))
	}

	if len(users2) != 0 {
		t.Errorf("Expected 0 users from second iteration, got %d", len(users2))
	}
}

func TestScannerResourceCleanup(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, email FROM users")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	scanner := NewScanner[UserBasic](rows)

	for user := range scanner.Scan() {
		_ = user
		break
	}

	scanner.Close()
	if err := rows.Err(); err != nil {
		t.Errorf("Expected no error, got %s", err)
	}
}

func TestScannerFilterByDepartment(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT id, name, age, email, department, active, salary, created_at FROM users WHERE department = 'Engineering' ORDER BY id")
	if err != nil {
		t.Fatalf("Failed to query users: %v", err)
	}

	scanner := NewScanner[User](rows)
	defer scanner.Close()
	var users []User

	for user := range scanner.Scan() {
		users = append(users, user)
	}

	if len(users) != 3 {
		t.Errorf("Expected 3 Engineering users, got %d", len(users))
	}

	for i, user := range users {
		if !user.Department.Valid || user.Department.String != "Engineering" {
			t.Errorf("User %d should be from Engineering: %+v", i, user)
		}
	}

	expectedNames := []string{"John Doe", "Bob Johnson", "Charlie Wilson"}
	for i, user := range users {
		if user.Name != expectedNames[i] {
			t.Errorf("Expected user %d to be %s, got %s", i, expectedNames[i], user.Name)
		}
	}
}

func TestScanOne(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	row := db.QueryRow("SELECT * FROM users WHERE id = 1")

	var u User
	err := Scan(row, &u)
	if err != nil {
		t.Fatalf("Failed to scan user: %v", err)
	}

	if u.ID != 1 || u.Name != "John Doe" {
		t.Errorf("user data incorrect: %+v", u)
	}
}