package norm

import (
	"slices"
	"testing"

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

func TestInsertBuild_Success(t *testing.T) {
	tests := []struct {
		name         string
		stmt         Compiler
		expectedSql  string
		expectedArgs []any
	}{
		{
			name:         "Simple insert",
			stmt:         Insert().Into("users").Value("name", "John"),
			expectedSql:  "INSERT INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "Abort clause",
			stmt:         Insert().Or(Abort).Into("users").Value("name", "John"),
			expectedSql:  "INSERT OR ABORT INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "Ignore clause",
			stmt:         Insert().Or(Ignore).Into("users").Value("name", "John"),
			expectedSql:  "INSERT OR IGNORE INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "Fail clause",
			stmt:         Insert().Or(Fail).Into("users").Value("name", "John"),
			expectedSql:  "INSERT OR FAIL INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "Replace clause",
			stmt:         Insert().Or(Replace).Into("users").Value("name", "John"),
			expectedSql:  "INSERT OR REPLACE INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "Rollback clause",
			stmt:         Insert().Or(Rollback).Into("users").Value("name", "John"),
			expectedSql:  "INSERT OR ROLLBACK INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "Default clause",
			stmt:         Insert().Or(InsertOr(10)).Into("users").Value("name", "John"),
			expectedSql:  "INSERT INTO users (name) VALUES (?)",
			expectedArgs: []any{"John"},
		},
		{
			name:         "More values",
			stmt:         Insert().Into("users").Value("name", "John").Value("age", 35),
			expectedSql:  "INSERT INTO users (name, age) VALUES (?, ?)",
			expectedArgs: []any{"John", 35},
		},
	}

	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 '%v', got '%v' at index %d", test.expectedArgs[i], args[i], i)
				}
			}
		})
	}
}

func TestInsertValueSet_Build(t *testing.T) {
	tests := []struct {
		name           string
		stmt           Compiler
		expectedConfig []struct {
			sql  string
			args []any
		}
	}{
		{
			name: "Simple insert",
			stmt: Insert().Into("users").Values(map[string]any{
				"name": "John",
				"age":  25,
			}),
			expectedConfig: []struct {
				sql  string
				args []any
			}{
				{
					sql:  "INSERT INTO users (name, age) VALUES (?, ?)",
					args: []any{"John", 25},
				},
				{
					sql:  "INSERT INTO users (age, name) VALUES (?, ?)",
					args: []any{25, "John"},
				},
			},
		},
	}

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

			any := false
			idx := 0
			for i, config := range test.expectedConfig {
				idx = i
				equalSql := config.sql == sql
				equalArgs := slices.Equal(config.args, args)
				if equalSql && equalArgs {
					any = true
				}
			}

			if !any {
				t.Errorf("Config did not match: %d: %q; got %q, %q", idx, test.expectedConfig[idx], sql, args)
			}

		})
	}
}

func TestInsertCompileFail(t *testing.T) {
	tests := []struct {
		name          string
		stmt          Compiler
		expectedError string
	}{
		{
			name:          "No into clause",
			stmt:          Insert(),
			expectedError: "INTO clause is required",
		},
		{
			name:          "No into clause",
			stmt:          Insert().Into("users"),
			expectedError: "no values supplied",
		},
	}

	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 TestInsertIntegration(t *testing.T) {
	tests := []struct {
		name         string
		stmt         Execer
		expectedRows int64
	}{
		{
			name:         "Delete all users",
			stmt:         Delete().From("users"),
			expectedRows: 6,
		},
		{
			name: "Delete active users only",
			stmt: Delete().
				From("users").
				Where(Eq("active", true)),
			expectedRows: 4,
		},
		{
			name: "Select users in Engineering",
			stmt: Delete().
				From("users").
				Where(Eq("department", "Engineering")),
			expectedRows: 3,
		},
		{
			name: "Delete users with age > 30",
			stmt: Delete().
				From("users").
				Where(Gt("age", 30)),
			expectedRows: 2,
		},
		{
			name: "Delete users with salary between 70000 and 80000",
			stmt: Delete().
				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) {
			db := setupTestDB(t)
			defer db.Close()

			res, err := test.stmt.Exec(db)
			if err != nil {
				t.Fatalf("Failed to execute query: %v", err)
			}

			count, err := res.RowsAffected()
			if err != nil {
				t.Fatalf("Failed to execute query: %v", err)
			}

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