-- Connect to ProxySQL admin
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

-- Enable AI features
SET ai_features_enabled='true';
SET ai_nl2sql_enabled='true';
SET ai_anomaly_detection_enabled='true';
LOAD MYSQL VARIABLES TO RUNTIME;

# Start embedding service
ollama run nomic-embed-text-v1.5

# Or via llama-server directly
llama-server --model nomic-embed-text-v1.5 --port 8013 --embedding

# Test embedding endpoint
curl -X POST http://127.0.0.1:8013/embedding \
  -H "Content-Type: application/json" \
  -d '{"content": "test embedding"}'

# Should return JSON with embedding array

cd /home/rene/proxysql-vec/test/tap

# Build vector features test
make vector_features

# Run the test
./vector_features

void test_virtual_tables_created() {
    // Checks:
    // - AI features initialized
    // - Vector DB path configured
    // - Vector dimension is 1536
}

=== Virtual vec0 Table Creation Tests ===
ok 1 - AI features initialized
ok 2 - Vector DB path configured (or default used)
ok 3 - Vector dimension is 1536 or default

void test_nl2sql_cache_config() {
    // Checks:
    // - Cache enabled by default
    // - Similarity threshold is 85
    // - Threshold can be changed
}

=== NL2SQL Vector Cache Configuration Tests ===
ok 4 - NL2SQL enabled by default
ok 5 - Cache similarity threshold is 85 or default
ok 6 - Cache threshold changed to 90
ok 7 - Cache threshold changed to 90

void test_anomaly_embedding_config() {
    // Checks:
    // - Anomaly detection enabled
    // - Similarity threshold is 85
    // - Risk threshold is 70
}

void test_status_variables() {
    // Checks:
    // - ai_detected_anomalies exists
    // - ai_blocked_queries exists
}

=== Status Variables Tests ===
ok 12 - ai_detected_anomalies status variable exists
ok 13 - ai_blocked_queries status variable exists

-- Step 1: Clear cache
DELETE FROM nl2sql_cache;

-- Step 2: First query (cache miss)
-- This will call LLM and cache the result
SELECT * FROM runtime_mysql_servers
WHERE variable_name = 'ai_nl2sql_enabled';

-- Via NL2SQL:
NL2SQL: Show all customers from USA;

-- Step 3: Similar query (should hit cache)
NL2SQL: Display USA customers;

-- Step 4: Another similar query
NL2SQL: List customers in United States;

// Check cache statistics
std::string stats = converter->get_cache_stats();
// Should show increased hit count

// Or via SQL
SELECT COUNT(*) as cache_entries,
       SUM(hit_count) as total_hits
FROM nl2sql_cache;

-- Add threat pattern
-- (Via C++ API)
detector->add_threat_pattern(
    "OR 1=1 Tautology",
    "SELECT * FROM users WHERE id=1 OR 1=1--",
    "sql_injection",
    9
);

-- Test detection
SELECT * FROM users WHERE id=5 OR 2=2--';

-- Should be BLOCKED (high similarity to OR 1=1 pattern)

-- Known pattern: "SELECT ... WHERE id=1 AND sleep(10)"
-- Test variation:
SELECT * FROM users WHERE id=5 AND SLEEP(5)--';

-- Should be FLAGGED (similar but lower severity)

-- Normal query
SELECT * FROM users WHERE id=5;

-- Should be ALLOWED

-- Enable NL2SQL
SET ai_nl2sql_enabled='true';
SET ai_nl2sql_cache_similarity_threshold='85';
LOAD MYSQL VARIABLES TO RUNTIME;

-- Clear cache
DELETE FROM nl2sql_cache;
DELETE FROM nl2sql_cache_vec;

-- Enable anomaly detection
SET ai_anomaly_detection_enabled='true';
SET ai_anomaly_similarity_threshold='85';
SET ai_anomaly_risk_threshold='70';
SET ai_anomaly_auto_block='true';
LOAD MYSQL VARIABLES TO RUNTIME;

-- Add test threat patterns (via C++ API or script)
-- See scripts/add_threat_patterns.sh

// Via C++ API
Anomaly_Detector* detector = GloAI->get_anomaly();

bool success = detector->add_threat_pattern(
    "Test Pattern",
    "SELECT * FROM test WHERE id=1",
    "test",
    5
);

if (success) {
    std::cout << "Pattern added successfully\n";
}

std::string patterns_json = detector->list_threat_patterns();
std::cout << "Patterns:\n" << patterns_json << "\n";

SELECT id, pattern_name, pattern_type, severity
FROM anomaly_patterns
ORDER BY severity DESC;

bool success = detector->remove_threat_pattern(1);

-- Note: This is for testing only, use C++ API in production
DELETE FROM anomaly_patterns WHERE id=1;
DELETE FROM anomaly_patterns_vec WHERE rowid=1;

# Create test script
cat > test_performance.sh <<'EOF'
#!/bin/bash

echo "=== NL2SQL Performance Test ==="

# Test 1: Cold cache (no similar queries)
time mysql -h 127.0.0.1 -P 6033 -u test -ptest \
  -e "NL2SQL: Show all products from electronics category;"

sleep 1

# Test 2: Warm cache (similar query)
time mysql -h 127.0.0.1 -P 6033 -u test -ptest \
  -e "NL2SQL: Display electronics products;"

echo ""
echo "=== Anomaly Detection Performance Test ==="

# Test 3: Anomaly check
time mysql -h 127.0.0.1 -P 6033 -u test -ptest \
  -e "SELECT * FROM users WHERE id=1 OR 1=1--';"

EOF

chmod +x test_performance.sh
./test_performance.sh

# Test concurrent queries
for i in {1..100}; do
  mysql -h 127.0.0.1 -P 6033 -u test -ptest \
    -e "NL2SQL: Show customer $i;" &
done
wait

# Check statistics
SHOW STATUS LIKE 'ai_%';

// In ProxySQL configuration
proxysql-debug-level 3

# NL2SQL logs
tail -f proxysql.log | grep NL2SQL

# Anomaly logs
tail -f proxysql.log | grep Anomaly

# GenAI/Embedding logs
tail -f proxysql.log | grep GenAI

# Vector DB logs
tail -f proxysql.log | grep "vec"

# All AI logs
tail -f proxysql.log | grep -E "(NL2SQL|Anomaly|GenAI|AI:)"

# Open vector database
sqlite3 /var/lib/proxysql/ai_features.db

# Check schema
.schema

# View cache entries
SELECT id, natural_language, hit_count, created_at FROM nl2sql_cache;

# View threat patterns
SELECT id, pattern_name, pattern_type, severity FROM anomaly_patterns;

# Check virtual tables
SELECT rowid FROM nl2sql_cache_vec LIMIT 10;

# Count embeddings
SELECT COUNT(*) FROM nl2sql_cache WHERE embedding IS NOT NULL;

#!/bin/bash
# run_vector_tests.sh

set -e

echo "=== Vector Features Test Suite ==="

# 1. Unit tests
echo "Running unit tests..."
cd test/tap
make vector_features
./vector_features

# 2. Integration tests
echo "Running integration tests..."
# Add integration test commands here

# 3. Performance tests
echo "Running performance tests..."
# Add performance test commands here

# 4. Cleanup
echo "Cleaning up..."
# Clear test data

echo "=== All tests passed ==="

# Example GitHub Actions workflow
name: Vector Features Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - name: Start llama-server
        run: ollama run nomic-embed-text-v1.5 &
      - name: Build ProxySQL
        run: make
      - name: Run unit tests
        run: cd test/tap && make vector_features && ./vector_features
      - name: Run integration tests
        run: ./scripts/mcp/test_nl2sql_e2e.sh --mock

-- Recreate virtual tables
-- (Requires restarting ProxySQL)

# Check llama-server is running
curl http://127.0.0.1:8013/embedding

# Check ProxySQL logs
tail -f proxysql.log | grep GenAI

-- Lower threshold for testing
SET ai_anomaly_similarity_threshold='75';

-- Lower cache threshold
SET ai_nl2sql_cache_similarity_threshold='75';

-- Simple queries
NL2SQL: Show all customers;
NL2SQL: Display all users;
NL2SQL: List all customers;  -- Should hit cache

-- Conditional queries
NL2SQL: Find customers from USA;
NL2SQL: Display USA customers;  -- Should hit cache
NL2SQL: Show users in United States;  -- Should hit cache

-- Aggregation
NL2SQL: Count customers by country;
NL2SQL: How many customers per country?;  -- Should hit cache

## Vector Features Test Results - [Date]

### Environment
- ProxySQL version: [version]
- Vector dimension: 1536
- Similarity threshold: 85
- llama-server status: [running/not running]

### Unit Tests
- Total: 20
- Passed: XX
- Failed: XX
- Skipped: XX

### Integration Tests
- NL2SQL cache: [PASS/FAIL]
- Anomaly detection: [PASS/FAIL]

### Performance
- Embedding generation: XXXms
- Cache search: XXms
- Similarity check: XXms
- Cold cache query: X.Xs
- Warm cache query: XXms

### Issues Found
1. [Description]
2. [Description]

### Notes
[Additional observations]