real-rs 0.1.0

//! PostgreSQL Demo - Real-world queries using real-rs
//!
//! This example connects to a PostgreSQL database and demonstrates
//! all relational algebra operations.
//!
//! Setup:
//! 1. Install PostgreSQL: `brew install postgresql` (macOS)
//! 2. Start server: `brew services start postgresql`
//! 3. Create database: `createdb real_rs_demo`
//! 4. Run: `cargo run --example postgres_demo --features backend-postgres`
//!
//! Or use Docker:
//! ```bash
//! docker run --name postgres-demo -e POSTGRES_PASSWORD=password \
//!   -e POSTGRES_DB=real_rs_demo -p 5432:5432 -d postgres:15
//! ```

use real_rs::algebra::{
    AggregateFunc, AggregateType, ColumnRef, CompareOp, Expr, JoinCondition, Operand, Predicate,
    SortOrder,
};
use real_rs::backends::postgres::{PostgresBackend, PostgresQuery};
use real_rs::backends::Backend;
use real_rs::schema::{DataType, Schema, Value};
use real_rs::Result;

fn main() -> Result<()> {
    println!("🚀 real-rs PostgreSQL Demo\n");
    println!("{}", "=".repeat(60));

    // Connection string - adjust as needed
    let conn_str = std::env::var("DATABASE_URL").unwrap_or_else(|_| {
        "postgresql://localhost/real_rs_demo".to_string()
    });

    println!("📡 Connecting to: {}", conn_str);

    #[cfg(feature = "backend-postgres")]
    {
        let mut client = postgres::Client::connect(&conn_str, postgres::NoTls)
            .map_err(|e| real_rs::RealError::Backend(format!("Connection failed: {}", e)))?;

        println!("✅ Connected!\n");

        // Setup: Create sample tables
        setup_database(&mut client)?;

        // Demo 1: Basic Selection
        demo_selection(&mut client)?;

        // Demo 2: Projection
        demo_projection(&mut client)?;

        // Demo 3: Join
        demo_join(&mut client)?;

        // Demo 4: Aggregation
        demo_aggregation(&mut client)?;

        // Demo 5: Complex Query
        demo_complex_query(&mut client)?;

        // Demo 6: Advanced Predicates
        demo_advanced_predicates(&mut client)?;

        println!("\n🎉 All demos completed successfully!");
        println!("\nCleanup:");
        println!("  DROP TABLE orders; DROP TABLE users;");
    }

    #[cfg(not(feature = "backend-postgres"))]
    {
        println!("❌ PostgreSQL backend not enabled!");
        println!("Run with: cargo run --example postgres_demo --features backend-postgres");
    }

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn setup_database(client: &mut postgres::Client) -> Result<()> {
    println!("📦 Setting up demo database...\n");

    // Drop tables if they exist
    let _ = client.execute("DROP TABLE IF EXISTS orders", &[]);
    let _ = client.execute("DROP TABLE IF EXISTS users", &[]);

    // Create users table
    client
        .execute(
            "CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) NOT NULL,
                age INTEGER,
                city VARCHAR(100)
            )",
            &[],
        )
        .map_err(|e| real_rs::RealError::Backend(e.to_string()))?;

    // Create orders table
    client
        .execute(
            "CREATE TABLE orders (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL,
                product VARCHAR(100) NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            &[],
        )
        .map_err(|e| real_rs::RealError::Backend(e.to_string()))?;

    // Insert sample data
    client
        .execute(
            "INSERT INTO users (name, email, age, city) VALUES
                ('Alice', 'alice@example.com', 30, 'New York'),
                ('Bob', 'bob@example.com', 25, 'San Francisco'),
                ('Charlie', 'charlie@example.com', 35, 'New York'),
                ('Diana', 'diana@example.com', 28, 'Boston'),
                ('Eve', 'eve@example.com', 32, 'San Francisco')",
            &[],
        )
        .map_err(|e| real_rs::RealError::Backend(e.to_string()))?;

    client
        .execute(
            "INSERT INTO orders (user_id, product, amount) VALUES
                (1, 'Laptop', 1200.00),
                (1, 'Mouse', 25.00),
                (2, 'Keyboard', 75.00),
                (2, 'Monitor', 300.00),
                (3, 'Laptop', 1500.00),
                (3, 'Headphones', 150.00),
                (4, 'Tablet', 500.00),
                (5, 'Phone', 800.00)",
            &[],
        )
        .map_err(|e| real_rs::RealError::Backend(e.to_string()))?;

    println!("✅ Created tables: users, orders");
    println!("✅ Inserted sample data\n");

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn demo_selection(client: &mut postgres::Client) -> Result<()> {
    println!("{}", "=".repeat(60));
    println!("Demo 1: Selection (σ) - Filter users by age > 28");
    println!("{}", "=".repeat(60));

    let backend = PostgresBackend::new();

    // Define schema
    let users_schema = Schema::new("users")
        .with_column("id", DataType::Integer)
        .with_column("name", DataType::String)
        .with_column("email", DataType::String)
        .with_column("age", DataType::Integer)
        .with_column("city", DataType::String);

    // Build query: SELECT * FROM users WHERE age > 28
    let query = Expr::relation("users", users_schema).select(Predicate::Compare {
        left: ColumnRef::new("age"),
        op: CompareOp::Gt,
        right: Operand::Literal(Value::Integer(28)),
    });

    // Compile to SQL
    let compiled = backend.compile(&query)?;
    println!("📝 Generated SQL:");
    println!("   {}", compiled.sql);
    println!("   Params: {:?}\n", compiled.params);

    // Execute
    let results = backend.execute(client, &compiled)?;
    println!("📊 Results ({} rows):", results.len());
    for row in &results {
        println!("   {:?}", row);
    }
    println!();

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn demo_projection(client: &mut postgres::Client) -> Result<()> {
    println!("{}", "=".repeat(60));
    println!("Demo 2: Projection (π) - Select only name and city");
    println!("{}", "=".repeat(60));

    let backend = PostgresBackend::new();

    let users_schema = Schema::new("users")
        .with_column("id", DataType::Integer)
        .with_column("name", DataType::String)
        .with_column("city", DataType::String);

    // SELECT name, city FROM users
    let query = Expr::relation("users", users_schema)
        .project(vec!["name".to_string(), "city".to_string()]);

    let compiled = backend.compile(&query)?;
    println!("📝 Generated SQL:");
    println!("   {}\n", compiled.sql);

    let results = backend.execute(client, &compiled)?;
    println!("📊 Results ({} rows):", results.len());
    for row in &results {
        println!("   {:?}", row);
    }
    println!();

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn demo_join(client: &mut postgres::Client) -> Result<()> {
    println!("{}", "=".repeat(60));
    println!("Demo 3: Join (⨝) - Users with their orders");
    println!("{}", "=".repeat(60));

    let backend = PostgresBackend::new();

    let users_schema = Schema::new("users")
        .with_column("id", DataType::Integer)
        .with_column("name", DataType::String);

    let orders_schema = Schema::new("orders")
        .with_column("id", DataType::Integer)
        .with_column("user_id", DataType::Integer)
        .with_column("product", DataType::String)
        .with_column("amount", DataType::Float);

    // SELECT * FROM users JOIN orders ON users.id = orders.user_id
    let query = Expr::relation("users", users_schema).join(
        Expr::relation("orders", orders_schema),
        JoinCondition::On(Predicate::Compare {
            left: ColumnRef::qualified("users", "id"),
            op: CompareOp::Eq,
            right: Operand::Column(ColumnRef::qualified("orders", "user_id")),
        }),
    );

    let compiled = backend.compile(&query)?;
    println!("📝 Generated SQL:");
    println!("   {}\n", compiled.sql);

    let results = backend.execute(client, &compiled)?;
    println!("📊 Results ({} rows):", results.len());
    for (i, row) in results.iter().take(5).enumerate() {
        println!("   {}: {:?}", i + 1, row);
    }
    if results.len() > 5 {
        println!("   ... and {} more rows", results.len() - 5);
    }
    println!();

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn demo_aggregation(client: &mut postgres::Client) -> Result<()> {
    println!("{}", "=".repeat(60));
    println!("Demo 4: Aggregation (γ) - Total orders per user");
    println!("{}", "=".repeat(60));

    let backend = PostgresBackend::new();

    let orders_schema = Schema::new("orders")
        .with_column("user_id", DataType::Integer)
        .with_column("amount", DataType::Float);

    // SELECT user_id, COUNT(*) as count, SUM(amount) as total
    // FROM orders
    // GROUP BY user_id
    let query = Expr::Aggregate {
        input: Box::new(Expr::relation("orders", orders_schema)),
        group_by: vec!["user_id".to_string()],
        aggregates: vec![
            AggregateFunc {
                name: "order_count".to_string(),
                func: AggregateType::Count,
                input: "id".to_string(),
            },
            AggregateFunc {
                name: "total_amount".to_string(),
                func: AggregateType::Sum,
                input: "amount".to_string(),
            },
        ],
    };

    let compiled = backend.compile(&query)?;
    println!("📝 Generated SQL:");
    println!("   {}\n", compiled.sql);

    let results = backend.execute(client, &compiled)?;
    println!("📊 Results ({} rows):", results.len());
    for row in &results {
        println!("   {:?}", row);
    }
    println!();

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn demo_complex_query(client: &mut postgres::Client) -> Result<()> {
    println!("{}", "=".repeat(60));
    println!("Demo 5: Complex Query - Top spenders in New York");
    println!("{}", "=".repeat(60));

    let backend = PostgresBackend::new();

    // Build a complex query step by step
    // 1. Get users from New York
    let users_schema = Schema::new("users")
        .with_column("id", DataType::Integer)
        .with_column("name", DataType::String)
        .with_column("city", DataType::String);

    let ny_users = Expr::relation("users", users_schema.clone()).select(Predicate::Compare {
        left: ColumnRef::new("city"),
        op: CompareOp::Eq,
        right: Operand::Literal(Value::String("New York".to_string())),
    });

    // 2. Join with orders
    let orders_schema = Schema::new("orders")
        .with_column("user_id", DataType::Integer)
        .with_column("amount", DataType::Float);

    let joined = ny_users.join(
        Expr::relation("orders", orders_schema),
        JoinCondition::On(Predicate::Compare {
            left: ColumnRef::qualified("users", "id"),
            op: CompareOp::Eq,
            right: Operand::Column(ColumnRef::qualified("orders", "user_id")),
        }),
    );

    // 3. Aggregate by user
    let aggregated = Expr::Aggregate {
        input: Box::new(joined),
        group_by: vec!["name".to_string()],
        aggregates: vec![AggregateFunc {
            name: "total_spent".to_string(),
            func: AggregateType::Sum,
            input: "amount".to_string(),
        }],
    };

    // 4. Sort by total spent (descending)
    let sorted = Expr::Sort {
        input: Box::new(aggregated),
        columns: vec![("total_spent".to_string(), SortOrder::Desc)],
    };

    // 5. Limit to top 3
    let limited = Expr::Limit {
        input: Box::new(sorted),
        count: 3,
    };

    let compiled = backend.compile(&limited)?;
    println!("📝 Generated SQL:");
    println!("   {}\n", compiled.sql);

    let results = backend.execute(client, &compiled)?;
    println!("📊 Results ({} rows):", results.len());
    for row in &results {
        println!("   {:?}", row);
    }
    println!();

    Ok(())
}

#[cfg(feature = "backend-postgres")]
fn demo_advanced_predicates(client: &mut postgres::Client) -> Result<()> {
    println!("{}", "=".repeat(60));
    println!("Demo 6: Advanced Predicates - IN, LIKE, BETWEEN");
    println!("{}", "=".repeat(60));

    let backend = PostgresBackend::new();

    let users_schema = Schema::new("users")
        .with_column("name", DataType::String)
        .with_column("city", DataType::String)
        .with_column("age", DataType::Integer);

    // Users in specific cities, name starts with A-C, age between 25-32
    let query = Expr::relation("users", users_schema)
        .select(Predicate::And(
            Box::new(Predicate::In {
                column: ColumnRef::new("city"),
                values: vec![
                    Value::String("New York".to_string()),
                    Value::String("San Francisco".to_string()),
                ],
            }),
            Box::new(Predicate::And(
                Box::new(Predicate::Like {
                    column: ColumnRef::new("name"),
                    pattern: "[A-C]%".to_string(),
                }),
                Box::new(Predicate::Between {
                    column: ColumnRef::new("age"),
                    low: Value::Integer(25),
                    high: Value::Integer(32),
                }),
            )),
        ))
        .project(vec!["name".to_string(), "city".to_string(), "age".to_string()]);

    let compiled = backend.compile(&query)?;
    println!("📝 Generated SQL:");
    println!("   {}", compiled.sql);
    println!("   Params: {:?}\n", compiled.params);

    let results = backend.execute(client, &compiled)?;
    println!("📊 Results ({} rows):", results.len());
    for row in &results {
        println!("   {:?}", row);
    }
    println!();

    Ok(())
}