#!/bin/bash

# Basic Rails test demonstrating key features
set -e

RED='\033[0;31m'
GREEN='\033[0;32m'
BLUE='\033[0;34m'
NC='\033[0m'

PGSQLITE_PORT=5433
DB_NAME="rails_basic.db"

cleanup() {
    if [ ! -z "$PGSQLITE_PID" ]; then
        kill $PGSQLITE_PID 2>/dev/null || true
        wait $PGSQLITE_PID 2>/dev/null || true
    fi
    rm -f "$DB_NAME"
}

trap cleanup EXIT

echo -e "${BLUE}=== Rails + pgsqlite Basic Test ===${NC}"

# Start pgsqlite
echo "Starting pgsqlite..."
../../target/release/pgsqlite --database "$DB_NAME" --port "$PGSQLITE_PORT" > /dev/null 2>&1 &
PGSQLITE_PID=$!
sleep 3

# Test 1: IDENTITY columns (Rails 5.1+ identity columns)
echo "Testing GENERATED BY DEFAULT AS IDENTITY..."
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "CREATE TABLE authors (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT NOT NULL);"
echo -e "${GREEN}✓ IDENTITY column created${NC}"

# Test 2: Insert with auto-increment
echo "Testing auto-increment INSERT..."
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "INSERT INTO authors (name) VALUES ('Jane Austen'), ('Mark Twain');"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "SELECT * FROM authors;"
echo -e "${GREEN}✓ Auto-increment working${NC}"

# Test 3: Basic JSONB
echo "Testing JSONB support..."
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, metadata JSONB DEFAULT '{}');"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "INSERT INTO books (title, metadata) VALUES ('Pride and Prejudice', '{\"genre\": \"Romance\", \"year\": 1813}');"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "SELECT title, metadata->>'genre' as genre FROM books;"
echo -e "${GREEN}✓ JSONB working${NC}"

# Test 4: Basic arrays
echo "Testing PostgreSQL arrays..."
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "ALTER TABLE books ADD COLUMN tags TEXT[] DEFAULT '{}';"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "UPDATE books SET tags = '{\"classic\", \"romance\"}' WHERE title = 'Pride and Prejudice';"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "SELECT title, tags FROM books WHERE tags @> '{\"classic\"}';"
echo -e "${GREEN}✓ Arrays with @> operator working${NC}"

# Test 5: Foreign keys and joins
echo "Testing foreign keys and joins..."
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "ALTER TABLE books ADD COLUMN author_id BIGINT REFERENCES authors(id);"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "UPDATE books SET author_id = 1 WHERE title = 'Pride and Prejudice';"
psql -h localhost -p "$PGSQLITE_PORT" -d "$DB_NAME" -c "SELECT b.title, a.name as author FROM books b JOIN authors a ON b.author_id = a.id;"
echo -e "${GREEN}✓ Foreign keys and joins working${NC}"

echo ""
echo -e "${GREEN}🎉 All Rails core features working with pgsqlite!${NC}"
echo ""
echo "✅ Rails Features Tested:"
echo "  - GENERATED BY DEFAULT AS IDENTITY (Rails 5.1+ identity columns)"
echo "  - JSONB fields with operators (Rails 4.2+ native JSONB)"
echo "  - PostgreSQL arrays with operators (Rails native array support)"
echo "  - Foreign key constraints (Rails associations)"
echo "  - Complex joins (Rails includes/joins)"
echo ""
echo "This demonstrates that pgsqlite can handle Rails applications with advanced PostgreSQL features!"