package norm

import (
	_ "github.com/mattn/go-sqlite3"
	"testing"
)

func TestSelectCompileSuccess(t *testing.T) {
	tests := []struct {
		name         string
		stmt         Compiler
		expectedSql  string
		expectedArgs []any
	}{
		{
			name:         "Simple select",
			stmt:         Select("name", "age").From("users"),
			expectedSql:  "SELECT name, age FROM users",
			expectedArgs: nil,
		},
		{
			name: "Select with where",
			stmt: Select("*").
				From("users").
				Where(Eq("active", true)),
			expectedSql:  "SELECT * FROM users WHERE (active) = (?)",
			expectedArgs: []any{true},
		},
		{
			name: "Select with order by",
			stmt: Select("name").
				From("users").
				OrderBy("name", Ascending),
			expectedSql:  "SELECT name FROM users ORDER BY name asc",
			expectedArgs: nil,
		},
		{
			name: "Select with multiple order by",
			stmt: Select("name", "age").
				From("users").
				OrderBy("name", Ascending).
				OrderBy("age", Descending),
			expectedSql:  "SELECT name, age FROM users ORDER BY name asc, age desc",
			expectedArgs: nil,
		},
		{
			name: "Select with group by",
			stmt: Select("department", "COUNT(*)").
				From("users").
				GroupBy("department"),
			expectedSql:  "SELECT department, COUNT(*) FROM users GROUP BY department",
			expectedArgs: nil,
		},
		{
			name: "Select with limit",
			stmt: Select("*").
				From("users").
				Limit(5),
			expectedSql:  "SELECT * FROM users LIMIT 5",
			expectedArgs: nil,
		},
		{
			name: "Complex select",
			stmt: Select("name", "age", "department").
				From("users").
				Where(Eq("active", true).And(Gt("age", 18))).
				GroupBy("department").
				OrderBy("name", Ascending).
				Limit(10),
			expectedSql:  "SELECT name, age, department FROM users WHERE ((active) = (?)) AND ((age) > (?)) GROUP BY department ORDER BY name asc LIMIT 10",
			expectedArgs: []any{true, 18},
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			sql, args := test.stmt.MustCompile()

			if sql != test.expectedSql {
				t.Errorf("Expected '%s', got '%s'", test.expectedSql, sql)
			}

			if len(args) != len(test.expectedArgs) {
				t.Errorf("Expected '%d' args, got '%d' args", len(test.expectedArgs), len(args))
			}

			for i := range len(args) {
				if args[i] != test.expectedArgs[i] {
					t.Errorf("Expected '%s', got '%s' at index %d", test.expectedArgs[i], args[i], i)
				}
			}
		})
	}
}

func TestSelectCompileFail(t *testing.T) {
	tests := []struct {
		name          string
		stmt          Compiler
		expectedError string
	}{
		{
			name:          "No columns",
			stmt:          Select(),
			expectedError: "result columns empty",
		},
		{
			name:          "No from clause",
			stmt:          Select("name"),
			expectedError: "FROM clause is required",
		},
		{
			name: "Invalid limit",
			stmt: Select("name").
				From("users").
				Limit(0),
			expectedError: "LIMIT must be positive, got 0",
		},
		{
			name: "Negative limit",
			stmt: Select("name").
				From("users").
				Limit(-5),
			expectedError: "LIMIT must be positive, got -5",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			sql, args, err := test.stmt.Compile()

			if err == nil {
				t.Error("Expected error, got nil")
			}

			if err.Error() != test.expectedError {
				t.Errorf("Expected error '%s', got '%s'", test.expectedError, err.Error())
			}

			if sql != "" {
				t.Errorf("Expected empty SQL on error, got '%s'", sql)
			}

			if args != nil {
				t.Errorf("Expected empty args on error, got '%q'", args)
			}
		})
	}
}

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

	tests := []struct {
		name         string
		stmt         select_
		expectedRows int
	}{
		{
			name:         "Select all users",
			stmt:         Select("*").From("users"),
			expectedRows: 6,
		},
		{
			name: "Select active users only",
			stmt: Select("name", "email").
				From("users").
				Where(Eq("active", true)),
			expectedRows: 4,
		},
		{
			name: "Select users in Engineering",
			stmt: Select("name", "age").
				From("users").
				Where(Eq("department", "Engineering")),
			expectedRows: 3,
		},
		{
			name: "Select users with age > 30",
			stmt: Select("name", "age").
				From("users").
				Where(Gt("age", 30)),
			expectedRows: 2,
		},
		{
			name: "Select users with salary between 70000 AND 80000",
			stmt: Select("name", "salary").
				From("users").
				Where(Gte("salary", 70000.0).And(Lte("salary", 80000.0))),
			expectedRows: 3,
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			rows, err := test.stmt.Query(db)
			if err != nil {
				t.Fatalf("Failed to build query: %v", err)
			}

			count := 0
			for rows.Next() {
				count++
			}

			if count != test.expectedRows {
				t.Errorf("Expected %d rows, got %d", test.expectedRows, count)
			}
		})
	}
}