#!/bin/bash
# Test skraak sql command 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 skraak sql ==="
echo "Database: $DB_PATH"
echo ""

check_binary

# Helper to run CLI command and capture JSON output
run_cli() {
    "$PROJECT_DIR/skraak" "$@" 2>/dev/null || true
}

# Test 1: Simple SELECT
echo "Test 1: Simple SELECT query"
result=$(run_cli sql --db "$DB_PATH" "SELECT id, name FROM dataset WHERE active = true LIMIT 5")
row_count=$(echo "$result" | jq -r '.row_count // -1')
if [ "$row_count" -ge 0 ]; then
    echo -e "${GREEN}✓${NC} Simple SELECT returns results (row_count=$row_count)"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} Simple SELECT failed"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 2: SELECT with --limit parameter
echo ""
echo "Test 2: SELECT with --limit parameter"
result=$(run_cli sql --db "$DB_PATH" --limit 3 "SELECT id, name FROM location WHERE active = true")
row_count=$(echo "$result" | jq -r '.row_count // -1')
if [ "$row_count" -ge 0 ] && [ "$row_count" -le 3 ]; then
    echo -e "${GREEN}✓${NC} SELECT with --limit works (row_count=$row_count)"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} SELECT with --limit failed (row_count=$row_count)"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 3: JOIN query
echo ""
echo "Test 3: JOIN query across tables"
result=$(run_cli sql --db "$DB_PATH" "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")
row_count=$(echo "$result" | jq -r '.row_count // -1')
if [ "$row_count" -ge 0 ]; then
    echo -e "${GREEN}✓${NC} JOIN query works (row_count=$row_count)"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} JOIN query failed"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 4: Aggregate with GROUP BY
echo ""
echo "Test 4: Aggregate with GROUP BY"
result=$(run_cli sql --db "$DB_PATH" "SELECT type, COUNT(*) as cnt FROM dataset WHERE active = true GROUP BY type")
row_count=$(echo "$result" | jq -r '.row_count // -1')
if [ "$row_count" -ge 0 ]; then
    echo -e "${GREEN}✓${NC} Aggregate query works (row_count=$row_count)"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} Aggregate query failed"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 5: CTE (WITH clause)
echo ""
echo "Test 5: CTE with WITH clause"
result=$(run_cli sql --db "$DB_PATH" "WITH active_datasets AS (SELECT id, name FROM dataset WHERE active = true) SELECT * FROM active_datasets LIMIT 3")
row_count=$(echo "$result" | jq -r '.row_count // -1')
if [ "$row_count" -ge 0 ]; then
    echo -e "${GREEN}✓${NC} CTE query works (row_count=$row_count)"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} CTE query failed"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 6: INSERT attempt (should fail)
echo ""
echo "Test 6: INSERT blocked (security)"
result=$("$PROJECT_DIR/skraak" sql --db "$DB_PATH" "INSERT INTO dataset (id, name) VALUES ('test', 'test')" 2>&1 || true)
if echo "$result" | grep -qi "error\|forbidden\|only SELECT\|only WITH"; then
    echo -e "${GREEN}✓${NC} INSERT correctly rejected"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} INSERT should have been rejected"
    echo "  Output: $result"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 7: SQL injection attempt (should fail)
echo ""
echo "Test 7: SQL injection blocked (security)"
result=$("$PROJECT_DIR/skraak" sql --db "$DB_PATH" "SELECT * FROM dataset; DROP TABLE dataset;" 2>&1 || true)
if echo "$result" | grep -qi "error\|forbidden\|only SELECT\|only WITH"; then
    echo -e "${GREEN}✓${NC} SQL injection correctly rejected"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} SQL injection should have been rejected"
    echo "  Output: $result"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 8: DELETE attempt (should fail)
echo ""
echo "Test 8: DELETE blocked (security)"
result=$("$PROJECT_DIR/skraak" sql --db "$DB_PATH" "DELETE FROM dataset WHERE id = 'test'" 2>&1 || true)
if echo "$result" | grep -qi "error\|forbidden\|only SELECT\|only WITH"; then
    echo -e "${GREEN}✓${NC} DELETE correctly rejected"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} DELETE should have been rejected"
    echo "  Output: $result"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

# Test 9: DROP attempt (should fail)
echo ""
echo "Test 9: DROP blocked (security)"
result=$("$PROJECT_DIR/skraak" sql --db "$DB_PATH" "DROP TABLE dataset" 2>&1 || true)
if echo "$result" | grep -qi "error\|forbidden\|only SELECT\|only WITH"; then
    echo -e "${GREEN}✓${NC} DROP correctly rejected"
    ((TESTS_RUN++)) || true
    ((TESTS_PASSED++)) || true
else
    echo -e "${RED}✗${NC} DROP should have been rejected"
    echo "  Output: $result"
    ((TESTS_RUN++)) || true
    ((TESTS_FAILED++)) || true
fi

echo ""
print_summary