real-rs 0.1.0

//! Query Examples - How Queries Look in real-rs
//!
//! This shows the different ways to build queries using relational algebra.
//!
//! Run: cargo run --example query_examples --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!("📚 QUERY EXAMPLES - How Queries Look in real-rs\n");
    println!("{}", "=".repeat(80));

    let backend = PostgresBackend::new();

    // Example 1: Simple SELECT
    example_simple_select(&backend)?;

    // Example 2: WHERE with conditions
    example_where_clause(&backend)?;

    // Example 3: SELECT specific columns
    example_projection(&backend)?;

    // Example 4: Complex WHERE (AND/OR)
    example_complex_where(&backend)?;

    // Example 5: JOIN
    example_join(&backend)?;

    // Example 6: GROUP BY with aggregates
    example_group_by(&backend)?;

    // Example 7: ORDER BY and LIMIT
    example_sorting(&backend)?;

    // Example 8: Complex composed query
    example_composed(&backend)?;

    // Example 9: Using the builder pattern
    example_builder_pattern(&backend)?;

    println!("\n{}", "=".repeat(80));
    println!("✅ All examples shown!");
    println!("\nKey takeaway: Queries are built as composable expressions,");
    println!("not as strings, giving you type safety and compile-time checking.");

    Ok(())
}

fn example_simple_select(backend: &PostgresBackend) -> Result<()> {
    println!("\n1️⃣  Simple SELECT - Get all users");
    println!("{}", "-".repeat(80));

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

    // Build the query
    let query = Expr::relation("users", users_schema);

    // Compile
    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code:");
    println!(r#"    let query = Expr::relation("users", users_schema);"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);

    Ok(())
}

fn example_where_clause(backend: &PostgresBackend) -> Result<()> {
    println!("\n2️⃣  WHERE Clause - Filter by condition");
    println!("{}", "-".repeat(80));

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

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

    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code:");
    println!(r#"    let query = Expr::relation("users", users_schema)
        .select(Predicate::Compare {{
            left: ColumnRef::new("age"),
            op: CompareOp::Gt,
            right: Operand::Literal(Value::Integer(25)),
        }});"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);
    println!("    Params: {:?}", compiled.params);

    Ok(())
}

fn example_projection(backend: &PostgresBackend) -> Result<()> {
    println!("\n3️⃣  Projection - SELECT specific columns");
    println!("{}", "-".repeat(80));

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

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

    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code:");
    println!(r#"    let query = Expr::relation("users", users_schema)
        .project(vec!["name".to_string(), "email".to_string()]);"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);

    Ok(())
}

fn example_complex_where(backend: &PostgresBackend) -> Result<()> {
    println!("\n4️⃣  Complex WHERE - Multiple conditions with AND/OR");
    println!("{}", "-".repeat(80));

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

    // SELECT * FROM users
    // WHERE (age > 18 AND age < 65) AND (city = 'NYC' OR city = 'SF')
    let query = Expr::relation("users", users_schema).select(Predicate::And(
        Box::new(Predicate::And(
            Box::new(Predicate::Compare {
                left: ColumnRef::new("age"),
                op: CompareOp::Gt,
                right: Operand::Literal(Value::Integer(18)),
            }),
            Box::new(Predicate::Compare {
                left: ColumnRef::new("age"),
                op: CompareOp::Lt,
                right: Operand::Literal(Value::Integer(65)),
            }),
        )),
        Box::new(Predicate::Or(
            Box::new(Predicate::Compare {
                left: ColumnRef::new("city"),
                op: CompareOp::Eq,
                right: Operand::Literal(Value::String("NYC".to_string())),
            }),
            Box::new(Predicate::Compare {
                left: ColumnRef::new("city"),
                op: CompareOp::Eq,
                right: Operand::Literal(Value::String("SF".to_string())),
            }),
        )),
    ));

    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code:");
    println!(r#"    let query = Expr::relation("users", users_schema)
        .select(Predicate::And(
            Box::new(Predicate::And(
                Box::new(age > 18),
                Box::new(age < 65),
            )),
            Box::new(Predicate::Or(
                Box::new(city = 'NYC'),
                Box::new(city = 'SF'),
            )),
        ));"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);
    println!("    Params: {:?}", compiled.params);

    Ok(())
}

fn example_join(backend: &PostgresBackend) -> Result<()> {
    println!("\n5️⃣  JOIN - Combine two tables");
    println!("{}", "-".repeat(80));

    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("total", 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!("🔧 Rust Code:");
    println!(r#"    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")
                ),
            }}),
        );"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);

    Ok(())
}

fn example_group_by(backend: &PostgresBackend) -> Result<()> {
    println!("\n6️⃣  GROUP BY - Aggregate with grouping");
    println!("{}", "-".repeat(80));

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

    // SELECT user_id, COUNT(*) as order_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".to_string(),
                func: AggregateType::Sum,
                input: "amount".to_string(),
            },
            AggregateFunc {
                name: "average".to_string(),
                func: AggregateType::Avg,
                input: "amount".to_string(),
            },
        ],
    };

    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code:");
    println!(r#"    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".to_string(),
                func: AggregateType::Sum,
                input: "amount".to_string(),
            }},
        ],
    }};"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);

    Ok(())
}

fn example_sorting(backend: &PostgresBackend) -> Result<()> {
    println!("\n7️⃣  ORDER BY and LIMIT - Sorting and pagination");
    println!("{}", "-".repeat(80));

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

    // SELECT * FROM products
    // ORDER BY rating DESC, price ASC
    // LIMIT 10
    let query = Expr::Limit {
        input: Box::new(Expr::Sort {
            input: Box::new(Expr::relation("products", products_schema)),
            columns: vec![
                ("rating".to_string(), SortOrder::Desc),
                ("price".to_string(), SortOrder::Asc),
            ],
        }),
        count: 10,
    };

    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code:");
    println!(r#"    let query = Expr::Limit {{
        input: Box::new(Expr::Sort {{
            input: Box::new(Expr::relation("products", products_schema)),
            columns: vec![
                ("rating".to_string(), SortOrder::Desc),
                ("price".to_string(), SortOrder::Asc),
            ],
        }}),
        count: 10,
    }};"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);

    Ok(())
}

fn example_composed(backend: &PostgresBackend) -> Result<()> {
    println!("\n8️⃣  Composed Query - Chaining multiple operations");
    println!("{}", "-".repeat(80));
    println!("\nSQL Goal: Top 5 expensive orders from active users in NY\n");

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

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

    // Build step by step
    // 1. Filter active users in NYC
    let active_ny_users = Expr::relation("users", users_schema).select(Predicate::And(
        Box::new(Predicate::Compare {
            left: ColumnRef::new("active"),
            op: CompareOp::Eq,
            right: Operand::Literal(Value::Boolean(true)),
        }),
        Box::new(Predicate::Compare {
            left: ColumnRef::new("city"),
            op: CompareOp::Eq,
            right: Operand::Literal(Value::String("NYC".to_string())),
        }),
    ));

    // 2. Join with orders
    let user_orders = active_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. Filter expensive orders (> $100)
    let expensive_orders = user_orders.select(Predicate::Compare {
        left: ColumnRef::new("amount"),
        op: CompareOp::Gt,
        right: Operand::Literal(Value::Integer(100)),
    });

    // 4. Select specific columns
    let projected = expensive_orders.project(vec![
        "name".to_string(),
        "amount".to_string(),
    ]);

    // 5. Sort by amount descending
    let sorted = Expr::Sort {
        input: Box::new(projected),
        columns: vec![("amount".to_string(), SortOrder::Desc)],
    };

    // 6. Take top 5
    let final_query = Expr::Limit {
        input: Box::new(sorted),
        count: 5,
    };

    let compiled = backend.compile(&final_query)?;

    println!("🔧 Rust Code (step by step):");
    println!(r#"    // 1. Filter users
    let active_ny_users = Expr::relation("users", schema)
        .select(active AND city = 'NYC');

    // 2. Join with orders
    let user_orders = active_ny_users.join(orders, ON users.id = orders.user_id);

    // 3. Filter expensive orders
    let expensive = user_orders.select(amount > 100);

    // 4. Select columns
    let projected = expensive.project(vec!["name", "amount"]);

    // 5. Sort descending
    let sorted = Expr::Sort {{ input: projected, columns: ["amount DESC"] }};

    // 6. Take top 5
    let query = Expr::Limit {{ input: sorted, count: 5 }};"#);

    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);
    println!("\n    Params: {:?}", compiled.params);

    Ok(())
}

fn example_builder_pattern(backend: &PostgresBackend) -> Result<()> {
    println!("\n9️⃣  Builder Pattern - Fluent method chaining");
    println!("{}", "-".repeat(80));

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

    // Fluent API style
    let query = Expr::relation("users", users_schema)
        .select(Predicate::Compare {
            left: ColumnRef::new("age"),
            op: CompareOp::Gt,
            right: Operand::Literal(Value::Integer(21)),
        })
        .project(vec!["name".to_string(), "city".to_string()]);

    let compiled = backend.compile(&query)?;

    println!("🔧 Rust Code (fluent style):");
    println!(r#"    let query = Expr::relation("users", users_schema)
        .select(Predicate::Compare {{
            left: ColumnRef::new("age"),
            op: CompareOp::Gt,
            right: Operand::Literal(Value::Integer(21)),
        }})
        .project(vec!["name".to_string(), "city".to_string()]);"#);
    println!("\n📝 Compiles to SQL:");
    println!("    {}", compiled.sql);

    println!("\n💡 Note: The builder pattern methods (select, project, join)");
    println!("    are defined in src/algebra.rs as impl methods on Expr.");

    Ok(())
}