#!/bin/bash
# Test execute_sql tool with various queries
# Usage: ./test_sql.sh [db_path]
# Default: uses test.duckdb (read-only tests)

source "$(dirname "$0")/test_lib.sh"

DB_PATH="${1:-$DEFAULT_TEST_DB}"

if [ ! -f "$DB_PATH" ]; then
    echo -e "${RED}Error: Database not found at $DB_PATH${NC}"
    exit 1
fi

echo "=== Testing execute_sql Tool ==="
echo "Database: $DB_PATH"
echo ""

check_binary

# Test 1: Simple SELECT
echo "Test 1: Simple SELECT query"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"SELECT id, name FROM dataset WHERE active = true LIMIT 5"}}' "$DB_PATH")
run_test "Simple SELECT" "true" "$result"

# Test 2: SELECT with explicit limit
echo ""
echo "Test 2: SELECT with limit parameter"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"SELECT id, name FROM location WHERE active = true","limit":3}}' "$DB_PATH")
run_test "SELECT with limit" "true" "$result"

# Test 3: Parameterized query
echo ""
echo "Test 3: Parameterized query with ? placeholder"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"SELECT id, name FROM location WHERE dataset_id = ? AND active = true LIMIT 1","parameters":["vgIr9JSH_lFj"]}}' "$DB_PATH")
run_test "Parameterized query" "true" "$result"

# Test 4: JOIN query
echo ""
echo "Test 4: JOIN query across tables"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"SELECT d.name, COUNT(l.id) as cnt FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name LIMIT 5"}}' "$DB_PATH")
run_test "JOIN query" "true" "$result"

# Test 5: Aggregate query
echo ""
echo "Test 5: Aggregate with GROUP BY"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"SELECT type, COUNT(*) as cnt FROM dataset WHERE active = true GROUP BY type"}}' "$DB_PATH")
run_test "Aggregate query" "true" "$result"

# Test 6: CTE (WITH clause)
echo ""
echo "Test 6: CTE with WITH clause"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"WITH active_datasets AS (SELECT id, name FROM dataset WHERE active = true) SELECT * FROM active_datasets LIMIT 3"}}' "$DB_PATH")
run_test "CTE query" "true" "$result"

# Test 7: INSERT attempt (should fail)
echo ""
echo "Test 7: INSERT blocked (security)"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"INSERT INTO dataset (id, name) VALUES (\"test\", \"test\")"}}' "$DB_PATH")
run_test "INSERT blocked" "false" "$result"

# Test 8: SQL injection attempt (should fail)
echo ""
echo "Test 8: SQL injection blocked (security)"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"SELECT * FROM dataset; DROP TABLE dataset;"}}' "$DB_PATH")
run_test "SQL injection blocked" "false" "$result"

# Test 9: DELETE attempt (should fail)
echo ""
echo "Test 9: DELETE blocked (security)"
result=$(send_request "tools/call" '{"name":"execute_sql","arguments":{"query":"DELETE FROM dataset WHERE id = \"test\""}}' "$DB_PATH")
run_test "DELETE blocked" "false" "$result"

echo ""
print_summary