package norm

import (
	"database/sql"
	"testing"

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

func TestCreateTableCompileSuccess(t *testing.T) {
	tests := []struct {
		name        string
		stmt        Compiler
		expectedSql string
	}{
		{
			name: "Simple table",
			stmt: CreateTable("users").
				Column("id", Integer),
			expectedSql: "CREATE TABLE users (id INTEGER)",
		},
		{
			name: "Table with primary key",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY)",
		},
		{
			name: "Table with autoincrement",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey, AutoIncrement),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT)",
		},
		{
			name: "Table with multiple columns",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("name", Text, NotNull).
				Column("age", Integer),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER)",
		},
		{
			name: "Table with IF NOT EXISTS",
			stmt: CreateTable("users").
				IfNotExists().
				Column("id", Integer, PrimaryKey),
			expectedSql: "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY)",
		},
		{
			name: "Table with unique constraint",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("email", Text, Unique),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)",
		},
		{
			name: "Table with default value",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("active", Integer, Default(1)),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, active INTEGER DEFAULT 1)",
		},
		{
			name: "Table with check constraint",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("age", Integer, Check("age >= 18")),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, age INTEGER CHECK (age >= 18))",
		},
		{
			name: "Table with collate",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("name", Text, Collate("NOCASE")),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE)",
		},
		{
			name: "Table with composite primary key",
			stmt: CreateTable("user_roles").
				Column("user_id", Integer).
				Column("role_id", Integer).
				PrimaryKey("user_id", "role_id"),
			expectedSql: "CREATE TABLE user_roles (user_id INTEGER, role_id INTEGER, PRIMARY KEY (user_id, role_id))",
		},
		{
			name: "Table with table-level unique constraint",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("email", Text).
				Column("username", Text).
				UniqueConstraint("email", "username"),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, username TEXT, UNIQUE (email, username))",
		},
		{
			name: "Table with table-level check constraint",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("age", Integer).
				CheckConstraint("age >= 0 AND age <= 150"),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, age INTEGER, CHECK (age >= 0 AND age <= 150))",
		},
		{
			name: "Table with foreign key",
			stmt: CreateTable("posts").
				Column("id", Integer, PrimaryKey).
				Column("user_id", Integer, NotNull).
				ForeignKey("user_id", "users", "id"),
			expectedSql: "CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id))",
		},
		{
			name: "Table WITHOUT ROWID",
			stmt: CreateTable("cache").
				Column("key", Text, PrimaryKey).
				Column("value", Blob).
				WithoutRowid(),
			expectedSql: "CREATE TABLE cache (key TEXT PRIMARY KEY, value BLOB) WITHOUT ROWID",
		},
		{
			name: "Table STRICT",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey).
				Column("name", Text).
				Strict(),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT) STRICT",
		},
		{
			name: "Table STRICT WITHOUT ROWID",
			stmt: CreateTable("cache").
				Column("key", Text, PrimaryKey).
				Column("value", Blob).
				Strict().
				WithoutRowid(),
			expectedSql: "CREATE TABLE cache (key TEXT PRIMARY KEY, value BLOB) STRICT WITHOUT ROWID",
		},
		{
			name: "All data types",
			stmt: CreateTable("types_test").
				Column("col_int", Integer).
				Column("col_text", Text).
				Column("col_real", Real).
				Column("col_blob", Blob).
				Column("col_numeric", Numeric),
			expectedSql: "CREATE TABLE types_test (col_int INTEGER, col_text TEXT, col_real REAL, col_blob BLOB, col_numeric NUMERIC)",
		},
		{
			name: "Multiple constraints on column",
			stmt: CreateTable("users").
				Column("id", Integer, PrimaryKey, AutoIncrement).
				Column("email", Text, NotNull, Unique),
			expectedSql: "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE)",
		},
		{
			name: "Complex table",
			stmt: CreateTable("users").
				IfNotExists().
				Column("id", Integer, PrimaryKey, AutoIncrement).
				Column("email", Text, NotNull).
				Column("username", Text, NotNull).
				Column("age", Integer, Check("age >= 18")).
				Column("created_at", Text, Default("CURRENT_TIMESTAMP")).
				UniqueConstraint("email", "username").
				CheckConstraint("age < 150"),
			expectedSql: "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL, username TEXT NOT NULL, age INTEGER CHECK (age >= 18), created_at TEXT DEFAULT CURRENT_TIMESTAMP, UNIQUE (email, username), CHECK (age < 150))",
		},
	}

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

			if sql != test.expectedSql {
				t.Errorf("Expected SQL:\n%s\nGot:\n%s", test.expectedSql, sql)
			}

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

func TestCreateTableCompileFail(t *testing.T) {
	tests := []struct {
		name          string
		stmt          Compiler
		expectedError string
	}{
		{
			name:          "No table name",
			stmt:          CreateTable(""),
			expectedError: "table name is required",
		},
		{
			name:          "No columns",
			stmt:          CreateTable("users"),
			expectedError: "at least one column is required",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			_, _, 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())
			}
		})
	}
}

func TestCreateTableIntegration(t *testing.T) {
	tests := []struct {
		name       string
		createStmt Execer
		insertStmt Execer
		selectStmt Querier
		verify     func(t *testing.T, db *sql.DB)
	}{
		{
			name: "Create simple table and insert",
			createStmt: CreateTable("test_users").
				Column("id", Integer, PrimaryKey).
				Column("name", Text),
			insertStmt: Insert().Into("test_users").Value("id", 1).Value("name", "Alice"),
			selectStmt: Select("id", "name").From("test_users"),
			verify: func(t *testing.T, db *sql.DB) {
				var count int
				err := db.QueryRow("SELECT COUNT(*) FROM test_users").Scan(&count)
				if err != nil {
					t.Fatalf("Failed to count rows: %v", err)
				}
				if count != 1 {
					t.Errorf("Expected 1 row, got %d", count)
				}
			},
		},
		{
			name: "Create table with IF NOT EXISTS",
			createStmt: CreateTable("test_users2").
				IfNotExists().
				Column("id", Integer, PrimaryKey),
			verify: func(t *testing.T, db *sql.DB) {
				_, err := CreateTable("test_users2").
					IfNotExists().
					Column("id", Integer, PrimaryKey).
					Exec(db)
				if err != nil {
					t.Errorf("Second create with IF NOT EXISTS should not fail: %v", err)
				}
			},
		},
		{
			name: "Create table with autoincrement",
			createStmt: CreateTable("test_users3").
				Column("id", Integer, PrimaryKey, AutoIncrement).
				Column("name", Text),
			insertStmt: Insert().Into("test_users3").Value("name", "Bob"),
			verify: func(t *testing.T, db *sql.DB) {
				var id int
				var name string
				err := db.QueryRow("SELECT id, name FROM test_users3").Scan(&id, &name)
				if err != nil {
					t.Fatalf("Failed to query: %v", err)
				}
				if id != 1 {
					t.Errorf("Expected auto-incremented id=1, got %d", id)
				}
				if name != "Bob" {
					t.Errorf("Expected name=Bob, got %s", name)
				}
			},
		},
		{
			name: "Create table with default value",
			createStmt: CreateTable("test_users4").
				Column("id", Integer, PrimaryKey).
				Column("active", Integer, Default(1)),
			insertStmt: Insert().Into("test_users4").Value("id", 1),
			verify: func(t *testing.T, db *sql.DB) {
				var active int
				err := db.QueryRow("SELECT active FROM test_users4 WHERE id = 1").Scan(&active)
				if err != nil {
					t.Fatalf("Failed to query: %v", err)
				}
				if active != 1 {
					t.Errorf("Expected default active=1, got %d", active)
				}
			},
		},
		{
			name: "Create table with unique constraint",
			createStmt: CreateTable("test_users5").
				Column("id", Integer, PrimaryKey).
				Column("email", Text, Unique),
			verify: func(t *testing.T, db *sql.DB) {
				_, err := Insert().Into("test_users5").
					Value("id", 1).
					Value("email", "test@example.com").
					Exec(db)
				if err != nil {
					t.Fatalf("First insert should succeed: %v", err)
				}

				_, err = Insert().Into("test_users5").
					Value("id", 2).
					Value("email", "test@example.com").
					Exec(db)
				if err == nil {
					t.Error("Expected unique constraint violation, got nil")
				}
			},
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db := setupTestDB(t)
			defer db.Close()

			_, err := test.createStmt.Exec(db)
			if err != nil {
				t.Fatalf("Failed to create table: %v", err)
			}

			if test.insertStmt != nil {
				_, err = test.insertStmt.Exec(db)
				if err != nil {
					t.Fatalf("Failed to insert: %v", err)
				}
			}

			if test.verify != nil {
				test.verify(t, db)
			}
		})
	}
}