sql2expr_test.go

package sql2expr

import (
	"testing"
)

func TestTranspile(t *testing.T) {
	tests := []struct {
		name     string
		sql      string
		expected string
		wantErr  bool
	}{
		{
			name:     "simple select all",
			sql:      "SELECT * FROM items",
			expected: "items",
			wantErr:  false,
		},
		{
			name:     "select with where",
			sql:      "SELECT * FROM items WHERE price > 100",
			expected: "filter(items, {items.price > 100})",
			wantErr:  false,
		},
		{
			name:     "select specific fields",
			sql:      "SELECT id, name FROM items",
			expected: "map(items, items)",
			wantErr:  false,
		},
		{
			name:     "select with where and specific fields",
			sql:      "SELECT id, name FROM items WHERE price > 100",
			expected: "map(filter(items, {items.price > 100}), items)",
			wantErr:  false,
		},
		{
			name:     "select with order by",
			sql:      "SELECT * FROM items ORDER BY name",
			expected: "sortBy(items, items.name)",
			wantErr:  false,
		},
		{
			name:     "select with order by desc",
			sql:      "SELECT * FROM items ORDER BY price DESC",
			expected: "sortBy(items, -items.price)",
			wantErr:  false,
		},
		{
			name:     "select with limit",
			sql:      "SELECT * FROM items LIMIT 10",
			expected: "items[:10]",
			wantErr:  false,
		},
		{
			name:     "select with limit and offset",
			sql:      "SELECT * FROM items LIMIT 10 OFFSET 5",
			expected: "items[5:10]",
			wantErr:  false,
		},
		{
			name:     "select with where and order by and limit",
			sql:      "SELECT * FROM items WHERE price > 100 ORDER BY name LIMIT 5",
			expected: "sortBy(filter(items, {items.price > 100}), items.name)[:5]",
			wantErr:  false,
		},
		{
			name:     "select with count",
			sql:      "SELECT COUNT(*) FROM items",
			expected: "len(items)",
			wantErr:  false,
		},
		{
			name:     "select with sum",
			sql:      "SELECT SUM(price) FROM items",
			expected: "sum(map(items, items.price))",
			wantErr:  false,
		},
		{
			name:     "select with avg",
			sql:      "SELECT AVG(price) FROM items",
			expected: "sum(map(items, items.price)) / len(items)",
			wantErr:  false,
		},
		{
			name:     "select with complex where",
			sql:      "SELECT * FROM items WHERE price > 100 AND status = 'active'",
			expected: "filter(items, {items.price > 100 && items.status == \"active\"})",
			wantErr:  false,
		},
		{
			name:     "select with field aliasing",
			sql:      "SELECT name AS username FROM items",
			expected: "map(items, items.name)",
			wantErr:  false,
		},
		{
			name:     "unsupported statement type",
			sql:      "INSERT INTO items VALUES (1, 'test')",
			expected: "",
			wantErr:  true,
		},
		{
			name:     "invalid sql syntax",
			sql:      "SELECT * FROM",
			expected: "",
			wantErr:  true,
		},
		// Arithmetic Operators
		{
			name:     "arithmetic addition",
			sql:      "SELECT price + tax FROM products",
			expected: "map(products, products.price + products.tax)",
			wantErr:  false,
		},
		{
			name:     "arithmetic subtraction",
			sql:      "SELECT price - discount FROM products",
			expected: "map(products, products.price - products.discount)",
			wantErr:  false,
		},
		{
			name:     "arithmetic multiplication",
			sql:      "SELECT price * quantity FROM products",
			expected: "map(products, products.price * products.quantity)",
			wantErr:  false,
		},
		{
			name:     "arithmetic division",
			sql:      "SELECT price / quantity FROM products",
			expected: "map(products, products.price / products.quantity)",
			wantErr:  false,
		},
		{
			name:     "arithmetic modulus",
			sql:      "SELECT price % 10 FROM products",
			expected: "map(products, products.price % 10)",
			wantErr:  false,
		},
		{
			name:     "arithmetic exponentiation",
			sql:      "SELECT price ^ 2 FROM products",
			expected: "map(products, products.price ^ 2)",
			wantErr:  false,
		},
		// Advanced Comparison Operators
		{
			name:     "IN operator",
			sql:      "SELECT * FROM products WHERE category IN ('electronics', 'books')",
			expected: "filter(products, {products.category in [\"electronics\", \"books\"]})",
			wantErr:  false,
		},
		{
			name:     "NOT IN operator",
			sql:      "SELECT * FROM products WHERE category NOT IN ('electronics', 'books')",
			expected: "filter(products, {!(products.category in [\"electronics\", \"books\"])})",
			wantErr:  false,
		},
		{
			name:     "LIKE operator",
			sql:      "SELECT * FROM products WHERE name LIKE '%phone%'",
			expected: "filter(products, {.name matches \"%phone%\"})",
			wantErr:  false,
		},
		{
			name:     "NOT LIKE operator",
			sql:      "SELECT * FROM products WHERE name NOT LIKE '%test%'",
			expected: "filter(products, {!(.name matches \"%test%\")})",
			wantErr:  false,
		},
		{
			name:     "IS NULL operator",
			sql:      "SELECT * FROM products WHERE description IS NULL",
			expected: "filter(products, {products.description == null})",
			wantErr:  false,
		},
		{
			name:     "IS NOT NULL operator",
			sql:      "SELECT * FROM products WHERE description IS NOT NULL",
			expected: "filter(products, {products.description != null})",
			wantErr:  false,
		},
		// Logical Operators
		{
			name:     "NOT operator",
			sql:      "SELECT * FROM products WHERE NOT active",
			expected: "filter(products, {!products.active})",
			wantErr:  false,
		},
		// String Functions
		{
			name:     "string concatenation",
			sql:      "SELECT CONCAT(first_name, ' ', last_name) FROM users",
			expected: "map(users, {(.first_name + \" \" + .last_name)})",
			wantErr:  false,
		},
		{
			name:     "UPPER function",
			sql:      "SELECT UPPER(name) FROM users",
			expected: "map(users, upper(users.name))",
			wantErr:  false,
		},
		{
			name:     "LOWER function",
			sql:      "SELECT LOWER(email) FROM users",
			expected: "map(users, lower(users.email))",
			wantErr:  false,
		},
		{
			name:     "LENGTH function",
			sql:      "SELECT LENGTH(description) FROM products",
			expected: "map(products, len(products.description))",
			wantErr:  false,
		},
		// Aggregation Functions
		{
			name:     "MAX function",
			sql:      "SELECT MAX(price) FROM products",
			expected: "max(map(products, products.price))",
			wantErr:  false,
		},
		{
			name:     "MIN function",
			sql:      "SELECT MIN(price) FROM products",
			expected: "min(map(products, products.price))",
			wantErr:  false,
		},
		{
			name:     "COUNT with column",
			sql:      "SELECT COUNT(id) FROM products",
			expected: "count(map(products, products.id))",
			wantErr:  false,
		},
		// Multiple ORDER BY expressions
		{
			name:     "multiple ORDER BY expressions",
			sql:      "SELECT * FROM products ORDER BY category, price DESC",
			expected: "sortBy(products, [products.category, -products.price])",
			wantErr:  false,
		},
		{
			name:     "three ORDER BY expressions",
			sql:      "SELECT * FROM users ORDER BY last_name, first_name, age DESC",
			expected: "sortBy(users, [users.last_name, users.first_name, -users.age])",
			wantErr:  false,
		},
		// CASE expressions (conditional operators)
		{
			name:     "CASE expression with WHEN",
			sql:      "SELECT CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END FROM users",
			expected: "map(users, {.age > 18 ? \"adult\" : \"minor\"})",
			wantErr:  false,
		},
		{
			name:     "CASE expression with multiple WHEN",
			sql:      "SELECT CASE WHEN age < 13 THEN 'child' WHEN age < 18 THEN 'teen' ELSE 'adult' END FROM users",
			expected: "map(users, {.age < 13 ? \"child\" : .age < 18 ? \"teen\" : \"adult\"})",
			wantErr:  false,
		},
		{
			name:     "CASE expression with value",
			sql:      "SELECT CASE status WHEN 'active' THEN 1 WHEN 'inactive' THEN 0 ELSE -1 END FROM users",
			expected: "map(users, {.status == \"active\" ? 1 : .status == \"inactive\" ? 0 : -1})",
			wantErr:  false,
		},
		// String functions
		{
			name:     "TRIM function",
			sql:      "SELECT TRIM(name) FROM users",
			expected: "map(users, trim(users.name))",
			wantErr:  false,
		},
		{
			name:     "TRIM function with characters",
			sql:      "SELECT TRIM(name, '_') FROM users",
			expected: "map(users, {trim(.name, \"_\")})",
			wantErr:  false,
		},
		{
			name:     "SPLIT function",
			sql:      "SELECT SPLIT(email, '@') FROM users",
			expected: "map(users, {split(.email, \"@\")})",
			wantErr:  false,
		},
		{
			name:     "REPLACE function",
			sql:      "SELECT REPLACE(name, 'old', 'new') FROM users",
			expected: "map(users, {replace(.name, \"old\", \"new\")})",
			wantErr:  false,
		},
		{
			name:     "SUBSTRING function",
			sql:      "SELECT SUBSTRING(name, 1, 3) FROM users",
			expected: "map(users, users.name[1:3])",
			wantErr:  false,
		},
		{
			name:     "SUBSTRING function without length",
			sql:      "SELECT SUBSTRING(name, 2) FROM users",
			expected: "map(users, {.name[2:]})",
			wantErr:  false,
		},
		// Number functions
		{
			name:     "ABS function",
			sql:      "SELECT ABS(price) FROM products",
			expected: "map(products, abs(products.price))",
			wantErr:  false,
		},
		{
			name:     "CEIL function",
			sql:      "SELECT CEIL(price) FROM products",
			expected: "map(products, ceil(products.price))",
			wantErr:  false,
		},
		{
			name:     "FLOOR function",
			sql:      "SELECT FLOOR(price) FROM products",
			expected: "map(products, floor(products.price))",
			wantErr:  false,
		},
		{
			name:     "ROUND function",
			sql:      "SELECT ROUND(price) FROM products",
			expected: "map(products, round(products.price))",
			wantErr:  false,
		},
		{
			name:     "ROUND function with precision",
			sql:      "SELECT ROUND(price, 2) FROM products",
			expected: "map(products, round(products.price * pow(10, 2)) / pow(10, 2))",
			wantErr:  false,
		},
		// Type conversion functions
		{
			name:     "INT function",
			sql:      "SELECT INT(price) FROM products",
			expected: "map(products, int(products.price))",
			wantErr:  false,
		},
		{
			name:     "FLOAT function",
			sql:      "SELECT FLOAT(quantity) FROM products",
			expected: "map(products, float(products.quantity))",
			wantErr:  false,
		},
		{
			name:     "STRING function",
			sql:      "SELECT STRING(id) FROM products",
			expected: "map(products, string(products.id))",
			wantErr:  false,
		},
		// Array functions
		{
			name:     "FIRST function",
			sql:      "SELECT FIRST(prices) FROM products",
			expected: "map(products, first(products.prices))",
			wantErr:  false,
		},
		{
			name:     "LAST function",
			sql:      "SELECT LAST(prices) FROM products",
			expected: "map(products, last(products.prices))",
			wantErr:  false,
		},
		{
			name:     "SORT function",
			sql:      "SELECT SORT(prices) FROM products",
			expected: "map(products, sort(products.prices))",
			wantErr:  false,
		},
		{
			name:     "SORT function with order",
			sql:      "SELECT SORT(prices, 'desc') FROM products",
			expected: "map(products, {sort(.prices, \"desc\")})",
			wantErr:  false,
		},
		{
			name:     "REVERSE function",
			sql:      "SELECT REVERSE(prices) FROM products",
			expected: "map(products, reverse(products.prices))",
			wantErr:  false,
		},
		// Date functions
		{
			name:     "NOW function",
			sql:      "SELECT NOW() FROM users",
			expected: "now()",
			wantErr:  false,
		},
		{
			name:     "DATE function",
			sql:      "SELECT DATE(created_at) FROM users",
			expected: "map(users, date(users.created_at))",
			wantErr:  false,
		},
		{
			name:     "DURATION function",
			sql:      "SELECT DURATION('1h30m') FROM users",
			expected: "map(users, {duration(\"1h30m\")})",
			wantErr:  false,
		},
		// Bitwise functions
		{
			name:     "BITAND function",
			sql:      "SELECT BITAND(flags, 3) FROM users",
			expected: "map(users, bitand(users.flags, 3))",
			wantErr:  false,
		},
		{
			name:     "BITOR function",
			sql:      "SELECT BITOR(flags, 4) FROM users",
			expected: "map(users, bitor(users.flags, 4))",
			wantErr:  false,
		},
		{
			name:     "BITXOR function",
			sql:      "SELECT BITXOR(flags, 2) FROM users",
			expected: "map(users, bitxor(users.flags, 2))",
			wantErr:  false,
		},
		{
			name:     "BITNOT function",
			sql:      "SELECT BITNOT(flags) FROM users",
			expected: "map(users, bitnot(users.flags))",
			wantErr:  false,
		},
		// Advanced String Functions
		{
			name:     "CONTAINS function",
			sql:      "SELECT CONTAINS(name, 'test') FROM users",
			expected: "map(users, {contains(.name, \"test\")})",
			wantErr:  false,
		},
		{
			name:     "STARTSWITH function",
			sql:      "SELECT STARTSWITH(name, 'Mr.') FROM users",
			expected: "map(users, {startsWith(.name, \"Mr.\")})",
			wantErr:  false,
		},
		{
			name:     "ENDSWITH function",
			sql:      "SELECT ENDSWITH(email, '@gmail.com') FROM users",
			expected: "map(users, {endsWith(.email, \"@gmail.com\")})",
			wantErr:  false,
		},
		{
			name:     "INDEXOF function",
			sql:      "SELECT INDEXOF(name, ' ') FROM users",
			expected: "map(users, {indexOf(.name, \" \")})",
			wantErr:  false,
		},
		{
			name:     "LASTINDEXOF function",
			sql:      "SELECT LASTINDEXOF(path, '/') FROM files",
			expected: "map(files, {lastIndexOf(.path, \"/\")})",
			wantErr:  false,
		},
		// Advanced Mathematical Functions
		{
			name:     "SQRT function",
			sql:      "SELECT SQRT(area) FROM shapes",
			expected: "map(shapes, sqrt(shapes.area))",
			wantErr:  false,
		},
		{
			name:     "POW function",
			sql:      "SELECT POW(base, exponent) FROM calculations",
			expected: "map(calculations, pow(calculations.base, calculations.exponent))",
			wantErr:  false,
		},
		{
			name:     "POWER function",
			sql:      "SELECT POWER(2, 3) FROM numbers",
			expected: "map(numbers, pow(2, 3))",
			wantErr:  false,
		},
		{
			name:     "LOG function",
			sql:      "SELECT LOG(value) FROM measurements",
			expected: "map(measurements, log(measurements.value))",
			wantErr:  false,
		},
		{
			name:     "SIN function",
			sql:      "SELECT SIN(angle) FROM triangles",
			expected: "map(triangles, sin(triangles.angle))",
			wantErr:  false,
		},
		{
			name:     "COS function",
			sql:      "SELECT COS(angle) FROM triangles",
			expected: "map(triangles, cos(triangles.angle))",
			wantErr:  false,
		},
		{
			name:     "TAN function",
			sql:      "SELECT TAN(angle) FROM triangles",
			expected: "map(triangles, tan(triangles.angle))",
			wantErr:  false,
		},
		{
			name:     "PI function",
			sql:      "SELECT PI() FROM constants",
			expected: "map(constants, pi())",
			wantErr:  false,
		},
		{
			name:     "RANDOM function",
			sql:      "SELECT RANDOM() FROM random_data",
			expected: "map(random_data, random())",
			wantErr:  false,
		},
		// Advanced Conditional Logic Functions
		{
			name:     "COALESCE function",
			sql:      "SELECT COALESCE(name, email, 'unknown') FROM users",
			expected: "map(users, {coalesce(.name, .email, \"unknown\")})",
			wantErr:  false,
		},
		{
			name:     "IFNULL function",
			sql:      "SELECT IFNULL(description, 'No description') FROM products",
			expected: "map(products, {ifnull(.description, \"No description\")})",
			wantErr:  false,
		},
		{
			name:     "NULLIF function",
			sql:      "SELECT NULLIF(status, 'inactive') FROM users",
			expected: "map(users, {nullif(.status, \"inactive\")})",
			wantErr:  false,
		},
		{
			name:     "GREATEST function",
			sql:      "SELECT GREATEST(a, b, c) FROM numbers",
			expected: "map(numbers, max(numbers.a, numbers.b, numbers.c))",
			wantErr:  false,
		},
		{
			name:     "LEAST function",
			sql:      "SELECT LEAST(x, y, z) FROM coordinates",
			expected: "map(coordinates, min(coordinates.x, coordinates.y, coordinates.z))",
			wantErr:  false,
		},
		// Advanced Date/Time Functions
		{
			name:     "YEAR function",
			sql:      "SELECT YEAR(created_at) FROM orders",
			expected: "map(orders, year(orders.created_at))",
			wantErr:  false,
		},
		{
			name:     "MONTH function",
			sql:      "SELECT MONTH(created_at) FROM orders",
			expected: "map(orders, month(orders.created_at))",
			wantErr:  false,
		},
		{
			name:     "DAY function",
			sql:      "SELECT DAY(created_at) FROM orders",
			expected: "map(orders, day(orders.created_at))",
			wantErr:  false,
		},
		{
			name:     "HOUR function",
			sql:      "SELECT HOUR(created_at) FROM orders",
			expected: "map(orders, hour(orders.created_at))",
			wantErr:  false,
		},
		{
			name:     "MINUTE function",
			sql:      "SELECT MINUTE(created_at) FROM orders",
			expected: "map(orders, minute(orders.created_at))",
			wantErr:  false,
		},
		{
			name:     "SECOND function",
			sql:      "SELECT SECOND(created_at) FROM orders",
			expected: "map(orders, second(orders.created_at))",
			wantErr:  false,
		},
		// Advanced Array Functions
		{
			name:     "ARRAY_FLATTEN function",
			sql:      "SELECT ARRAY_FLATTEN(nested_array) FROM data",
			expected: "map(data, flatten(data.nested_array))",
			wantErr:  false,
		},
		{
			name:     "ARRAY_UNIQ function",
			sql:      "SELECT ARRAY_UNIQ(tags) FROM posts",
			expected: "map(posts, uniq(posts.tags))",
			wantErr:  false,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got, err := Transpile(tt.sql)
			if (err != nil) != tt.wantErr {
				t.Errorf("Transpile() error = %v, wantErr %v", err, tt.wantErr)
				return
			}
			if got != tt.expected {
				t.Errorf("Transpile() = %v, want %v", got, tt.expected)
			}
		})
	}
}