package analytics

import (
	"testing"
)

func TestIsValidSQLFilter(t *testing.T) {
	tests := []struct {
		name     string
		filter   string
		expected bool
	}{
		// Valid filters
		{"simple equality", "status = 'active'", true},
		{"comparison", "amount > 100", true},
		{"AND condition", "status = 'active' AND amount > 0", true},
		{"OR condition", "type = 'A' OR type = 'B'", true},
		{"IN clause", "status IN ('active', 'pending')", true},
		{"BETWEEN", "amount BETWEEN 100 AND 500", true},
		{"IS NULL", "email IS NULL", true},
		{"IS NOT NULL", "phone IS NOT NULL", true},
		{"LIKE", "name LIKE '%test%'", true},
		{"NOT", "NOT is_deleted", true},

		// Invalid filters - injection attempts
		{"empty", "", false},
		{"semicolon", "status = 'a'; DROP TABLE users", false},
		{"double dash comment", "status = 'a' -- comment", false},
		{"block comment", "status = 'a' /* comment */", false},
		{"DROP", "drop table users", false},
		{"DELETE", "delete from users", false},
		{"INSERT", "insert into users", false},
		{"UPDATE", "update users set", false},
		{"TRUNCATE", "truncate table users", false},
		{"ALTER", "alter table users", false},
		{"CREATE", "create table evil", false},
		{"EXEC", "exec sp_executesql", false},
		{"EXECUTE", "execute immediate", false},
		{"UNION", "1=1 union select * from users", false},
		{"information_schema", "SELECT * FROM information_schema.tables", false},
		{"sleep", "status = 'a' AND sleep(5)", false},
		{"benchmark", "benchmark(1000000, MD5('test'))", false},
		{"load_file", "load_file('/etc/passwd')", false},
		{"into outfile", "SELECT * INTO OUTFILE '/tmp/x'", false},
		{"into dumpfile", "SELECT * INTO DUMPFILE '/tmp/x'", false},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := isValidSQLFilter(tt.filter)
			if result != tt.expected {
				t.Errorf("isValidSQLFilter(%q) = %v, want %v", tt.filter, result, tt.expected)
			}
		})
	}
}

func TestIsValidSQLFilter_EdgeCases(t *testing.T) {
	// Edge cases that should be valid
	validCases := []string{
		"field_name = 'value with spaces'",
		"created_date >= '2024-01-01'",
		"(status = 'active' OR status = 'pending') AND amount > 0",
		"field1 = 'test' AND field2 = 'test2' AND field3 = 'test3'",
	}

	for _, filter := range validCases {
		t.Run(filter, func(t *testing.T) {
			if !isValidSQLFilter(filter) {
				t.Errorf("isValidSQLFilter(%q) should be true", filter)
			}
		})
	}

	// Edge cases that look dangerous but might sneak through
	// These should be caught by the filter
	sneakyAttempts := []string{
		"status='active';--",
		"DROP TABLE users --",
	}

	for _, filter := range sneakyAttempts {
		t.Run("sneaky_"+filter[:10], func(t *testing.T) {
			if isValidSQLFilter(filter) {
				t.Errorf("isValidSQLFilter(%q) should be false (sneaky injection)", filter)
			}
		})
	}
}