real-rs 0.1.0

//! Simple PostgreSQL Demo - Shows SQL compilation without execution
//!
//! Run: cargo run --example postgres_simple --features backend-postgres

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

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

    let backend = PostgresBackend::new();

    // Demo 1: Simple Selection
    demo_selection(&backend)?;

    // Demo 2: Projection
    demo_projection(&backend)?;

    // Demo 3: Join
    demo_join(&backend)?;

    // Demo 4: Aggregation
    demo_aggregation(&backend)?;

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

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

    println!("\n🎉 All SQL compilation demos completed!");
    println!("\nKey Achievement: Same relational algebra compiles to PostgreSQL SQL!");

    Ok(())
}

fn demo_selection(backend: &PostgresBackend) -> Result<()> {
    println!("\n📌 Demo 1: Selection (σ) - WHERE clause");
    println!("{}", "-".repeat(70));

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

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

    let compiled = backend.compile(&query)?;
    println!("🔧 Algebra: σ(age > 28) users");
    println!("📝 SQL:     {}", compiled.sql);
    println!("📦 Params:  {:?}", compiled.params);

    Ok(())
}

fn demo_projection(backend: &PostgresBackend) -> Result<()> {
    println!("\n📌 Demo 2: Projection (π) - SELECT specific columns");
    println!("{}", "-".repeat(70));

    let schema = Schema::new("users")
        .with_column("name", DataType::String)
        .with_column("email", DataType::String);

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

    let compiled = backend.compile(&query)?;
    println!("🔧 Algebra: π(name, email) users");
    println!("📝 SQL:     {}", compiled.sql);

    Ok(())
}

fn demo_join(backend: &PostgresBackend) -> Result<()> {
    println!("\n📌 Demo 3: Join (⨝) - JOIN tables");
    println!("{}", "-".repeat(70));

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

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

    // SELECT * FROM users JOIN orders ON users.id = orders.user_id
    let query = Expr::relation("users", users).join(
        Expr::relation("orders", orders),
        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!("🔧 Algebra: users ⨝ orders ON users.id = orders.user_id");
    println!("📝 SQL:     {}", compiled.sql);

    Ok(())
}

fn demo_aggregation(backend: &PostgresBackend) -> Result<()> {
    println!("\n📌 Demo 4: Aggregation (γ) - GROUP BY");
    println!("{}", "-".repeat(70));

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

    // SELECT user_id, COUNT(*), SUM(amount) FROM orders GROUP BY user_id
    let query = Expr::Aggregate {
        input: Box::new(Expr::relation("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_spent".to_string(),
                func: AggregateType::Sum,
                input: "amount".to_string(),
            },
        ],
    };

    let compiled = backend.compile(&query)?;
    println!("🔧 Algebra: γ(user_id; COUNT(id), SUM(amount)) orders");
    println!("📝 SQL:     {}", compiled.sql);

    Ok(())
}

fn demo_complex_query(backend: &PostgresBackend) -> Result<()> {
    println!("\n📌 Demo 5: Complex Query - Composition");
    println!("{}", "-".repeat(70));
    println!("Query: Top 5 NY users by spending, ordered descending\n");

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

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

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

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

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

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

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

    let compiled = backend.compile(&limited)?;
    println!("🔧 Steps:");
    println!("   1. σ(city = 'New York') users");
    println!("   2. ⨝ orders");
    println!("   3. γ(name; SUM(amount))");
    println!("   4. Sort DESC");
    println!("   5. LIMIT 5");
    println!("\n📝 Generated SQL:");
    println!("   {}", compiled.sql);
    println!("\n📦 Params: {:?}", compiled.params);

    Ok(())
}

fn demo_advanced_predicates(backend: &PostgresBackend) -> Result<()> {
    println!("\n📌 Demo 6: Advanced Predicates - IN, LIKE, BETWEEN");
    println!("{}", "-".repeat(70));

    let schema = Schema::new("products")
        .with_column("name", DataType::String)
        .with_column("category", DataType::String)
        .with_column("price", DataType::Float);

    // Complex predicate: category IN ('Electronics', 'Books') AND
    //                    name LIKE 'Pro%' AND
    //                    price BETWEEN 50 AND 500
    let query = Expr::relation("products", schema).select(Predicate::And(
        Box::new(Predicate::In {
            column: ColumnRef::new("category"),
            values: vec![
                Value::String("Electronics".to_string()),
                Value::String("Books".to_string()),
            ],
        }),
        Box::new(Predicate::And(
            Box::new(Predicate::Like {
                column: ColumnRef::new("name"),
                pattern: "Pro%".to_string(),
            }),
            Box::new(Predicate::Between {
                column: ColumnRef::new("price"),
                low: Value::Integer(50),
                high: Value::Integer(500),
            }),
        )),
    ));

    let compiled = backend.compile(&query)?;
    println!("🔧 Predicates:");
    println!("   • category IN ('Electronics', 'Books')");
    println!("   • name LIKE 'Pro%'");
    println!("   • price BETWEEN 50 AND 500");
    println!("\n📝 SQL:");
    println!("   {}", compiled.sql);
    println!("\n📦 Params: {:?}", compiled.params);

    Ok(())
}