#!/bin/bash
# Test suite for execute_sql tool
# Tests various SQL queries including safety validation

DB_PATH="${1:-../db/skraak.duckdb}"

{
  # Test 1: Initialize MCP connection
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'
  sleep 0.2

  # Test 2: Simple SELECT without LIMIT (should auto-append LIMIT 1000)
  echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name"}}}'
  sleep 0.2

  # Test 3: SELECT with explicit limit parameter (5 rows)
  echo '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name FROM location WHERE active = true ORDER BY name","limit":5}}}'
  sleep 0.2

  # Test 4: Parameterized query with ? placeholder
  echo '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true","parameters":["vgIr9JSH_lFj"]}}}'
  sleep 0.2

  # Test 5: Complex JOIN query across multiple tables
  echo '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name","limit":20}}}'
  sleep 0.2

  # Test 6: Aggregate query with GROUP BY
  echo '{"jsonrpc":"2.0","id":6,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type"}}}'
  sleep 0.2

  # Test 7: INSERT attempt - should FAIL with validation error
  echo '{"jsonrpc":"2.0","id":7,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"INSERT INTO dataset (id, name) VALUES (\"test\", \"test\")"}}}'
  sleep 0.2

  # Test 8: SQL injection attempt with forbidden keywords - should FAIL
  echo '{"jsonrpc":"2.0","id":8,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT * FROM dataset; DROP TABLE dataset;"}}}'
  sleep 0.2

} | ../skraak_mcp "$DB_PATH" 2>/dev/null