#!/bin/bash

# Cubic Index SQL Test Script
# Tests all cubic index features

set -e

GREEN='\033[0;32m'
BLUE='\033[0;34m'
YELLOW='\033[1;33m'
NC='\033[0m'

API_URL="http://localhost:8080/api/v1/sql/execute"

echo -e "${BLUE}╔═══════════════════════════════════════════════════════╗${NC}"
echo -e "${BLUE}║     Cubic Index SQL Test Suite 🌵                    ║${NC}"
echo -e "${BLUE}╚═══════════════════════════════════════════════════════╝${NC}"
echo ""

# Function to execute SQL
execute_sql() {
    local sql="$1"
    local desc="$2"
    
    echo -e "${YELLOW}▶ ${desc}${NC}"
    echo "  SQL: $sql"
    
    response=$(curl -s -X POST "$API_URL" \
        -H "Content-Type: application/json" \
        -d "{\"sql\": \"$sql\"}")
    
    echo "  Response: $response"
    echo ""
    sleep 0.5
}

# Test 1: CREATE TABLE (automatic primary index)
echo -e "${BLUE}═══ Test 1: CREATE TABLE with Automatic Primary Index ═══${NC}"
execute_sql "CREATE TABLE test.products (sku TEXT PRIMARY KEY, name TEXT, category TEXT, price TEXT)" \
    "Create products table"

# Test 2: INSERT data
echo -e "${BLUE}═══ Test 2: INSERT Data ═══${NC}"
execute_sql "INSERT INTO test.products (sku, name, category, price) VALUES ('LAPTOP-001', 'MacBook Pro', 'Electronics', '2499.99')" \
    "Insert laptop"
    
execute_sql "INSERT INTO test.products (sku, name, category, price) VALUES ('MOUSE-001', 'Wireless Mouse', 'Accessories', '29.99')" \
    "Insert mouse"
    
execute_sql "INSERT INTO test.products (sku, name, category, price) VALUES ('KEYBOARD-001', 'Mechanical Keyboard', 'Accessories', '149.99')" \
    "Insert keyboard"

# Test 3: SELECT with primary index
echo -e "${BLUE}═══ Test 3: SELECT Using Primary Index ═══${NC}"
execute_sql "SELECT * FROM test.products WHERE sku = 'LAPTOP-001'" \
    "Query by SKU (primary key)"

# Test 4: CREATE INDEX
echo -e "${BLUE}═══ Test 4: CREATE SECONDARY INDEX ═══${NC}"
execute_sql "CREATE INDEX idx_category ON test.products(category)" \
    "Create index on category column"

# Test 5: SELECT with secondary index
echo -e "${BLUE}═══ Test 5: SELECT Using Secondary Index ═══${NC}"
execute_sql "SELECT * FROM test.products WHERE category = 'Accessories'" \
    "Query by category (uses secondary index)"

# Test 6: SHOW INDEXES
echo -e "${BLUE}═══ Test 6: SHOW INDEXES ═══${NC}"
execute_sql "SHOW INDEXES ON test.products" \
    "List all indexes on products table"

# Test 7: UPDATE (index maintained)
echo -e "${BLUE}═══ Test 7: UPDATE with Index Maintenance ═══${NC}"
execute_sql "UPDATE test.products SET price = '2299.99' WHERE sku = 'LAPTOP-001'" \
    "Update laptop price"

execute_sql "SELECT * FROM test.products WHERE sku = 'LAPTOP-001'" \
    "Verify update via primary index"

# Test 8: CREATE more indexes
echo -e "${BLUE}═══ Test 8: Multiple Secondary Indexes ═══${NC}"
execute_sql "CREATE INDEX idx_price ON test.products(price)" \
    "Create index on price column"

execute_sql "SHOW INDEXES ON test.products" \
    "Show all indexes (should have 3 now)"

# Test 9: DELETE (indexes cleaned up)
echo -e "${BLUE}═══ Test 9: DELETE with Index Cleanup ═══${NC}"
execute_sql "DELETE FROM test.products WHERE sku = 'KEYBOARD-001'" \
    "Delete keyboard"

execute_sql "SELECT * FROM test.products WHERE category = 'Accessories'" \
    "Query accessories (should return only mouse)"

# Test 10: DROP INDEX
echo -e "${BLUE}═══ Test 10: DROP INDEX ═══${NC}"
execute_sql "DROP INDEX idx_price" \
    "Drop price index"

execute_sql "SHOW INDEXES ON test.products" \
    "Show indexes after drop"

echo ""
echo -e "${GREEN}╔═══════════════════════════════════════════════════════╗${NC}"
echo -e "${GREEN}║     All Cubic Index Tests Completed! ✅               ║${NC}"
echo -e "${GREEN}╚═══════════════════════════════════════════════════════╝${NC}"
echo ""

# Test 11: Get Index Statistics
echo -e "${BLUE}═══ Test 11: Index Statistics ═══${NC}"
echo -e "${YELLOW}▶ Get index statistics${NC}"
curl -s http://localhost:8080/api/v1/index/stats | python3 -m json.tool || echo "Install python3 for formatted output"
echo ""