# Chopin ORM Advanced Guide

This guide provides deep-dive examples covering the advanced features of the `chopin-orm` library, showcasing how you can build performant and robust database access layers.

---

## 1. Relationships

Declare associations directly on your models using `#[model(...)]` attributes on the struct and fields. Relationships are defined through:

- **`has_many(Target, fk = "column")`** — on the parent struct
- **`#[model(belongs_to(Parent))]`** — on the foreign key field

### Defining Relationships

```rust
use chopin_orm::{Model, Validate, builder::ColumnTrait};

#[derive(Model, Debug, Clone, PartialEq)]
#[model(table_name = "users", has_many(Post, fk = "user_id"))]
pub struct User {
    #[model(primary_key)]
    pub id: i32,
    pub name: String,
}

impl Validate for User {}

#[derive(Model, Debug, Clone, PartialEq)]
#[model(table_name = "posts")]
pub struct Post {
    #[model(primary_key)]
    pub id: i32,
    #[model(belongs_to(User))]
    pub user_id: i32,
    pub title: String,
}

impl Validate for Post {}
```

### Lazy Loading

The `#[derive(Model)]` macro generates convenience methods for fetching related records:

```rust
// Load all posts for a user (generated by `has_many`)
let posts: Vec<Post> = user.fetch_posts(&mut pool)?;

// Load the parent user for a post (generated by `belongs_to`)
let author: Option<User> = post.fetch_user_id(&mut pool)?;
```

### JOIN Queries

Use `join_child` and `join_parent` for SQL JOINs:

```rust
// JOIN from parent to child (User -> Post)
let users_with_posts: Vec<User> = User::find()
    .join_child::<Post>()
    .all(&mut pool)?;

// JOIN from child to parent (Post -> User)
let posts_with_authors: Vec<Post> = Post::find()
    .join_parent::<User>()
    .all(&mut pool)?;
```

---

## 2. Advanced Condition DSL

Chopin ORM uses a strongly-typed `Condition` tree via the `ColumnTrait` derive, preventing SQL injection and simplifying dynamic queries.

```rust
use chopin_orm::builder::ColumnTrait;

// Type-safe column expressions (generated by #[derive(Model)])
let name_filter = UserColumn::name.eq("Alice");
let age_filter = UserColumn::age.gt(18);
let name_list = UserColumn::name.is_in(vec!["Alice", "Bob", "Charlie"]);

// Combine with AND / OR combinators
let complex_filter = name_filter.and(age_filter.or(name_list));

// Pass to the query builder
let users = User::find()
    .filter(complex_filter)
    .all(&mut pool)?;
```

### Available Column Operations

| Method | SQL | Example |
|---|---|---|
| `.eq(val)` | `= $N` | `UserColumn::name.eq("Alice")` |
| `.neq(val)` | `!= $N` | `UserColumn::name.neq("Bob")` |
| `.gt(val)` | `> $N` | `UserColumn::age.gt(18)` |
| `.gte(val)` | `>= $N` | `UserColumn::age.gte(18)` |
| `.lt(val)` | `< $N` | `UserColumn::age.lt(65)` |
| `.lte(val)` | `<= $N` | `UserColumn::age.lte(65)` |
| `.is_null()` | `IS NULL` | `UserColumn::age.is_null()` |
| `.is_not_null()` | `IS NOT NULL` | `UserColumn::age.is_not_null()` |
| `.like(val)` | `LIKE $N` | `UserColumn::name.like("%ali%")` |
| `.ilike(val)` | `ILIKE $N` | `UserColumn::name.ilike("%ALI%")` |
| `.is_in(vec)` | `IN ($N, ...)` | `UserColumn::id.is_in(vec![1, 2, 3])` |
| `.count()` | `COUNT(col)` | `UserColumn::id.count()` |
| `.sum()` | `SUM(col)` | `UserColumn::age.sum()` |
| `.max()` | `MAX(col)` | `UserColumn::age.max()` |
| `.min()` | `MIN(col)` | `UserColumn::age.min()` |

### Raw Condition Fallback

For complex SQL that the DSL doesn't cover, use tuple-based raw conditions:

```rust
use chopin_pg::PgValue;

let users = User::find()
    .filter(("age BETWEEN {} AND {}", vec![PgValue::Int4(18), PgValue::Int4(65)]))
    .all(&mut pool)?;
```

---

## 3. ActiveModel for Partial Updates

`ActiveModel<M>` tracks which fields have changed, generating minimal `UPDATE` queries that only modify the staged columns.

```rust
use chopin_orm::ActiveModel;

// For new entities (INSERT)
let user = User { id: 0, name: "Alice".into() };
let mut active = ActiveModel::new_insert(user);
active.insert(&mut pool)?;

// For existing entities (UPDATE)
let user = User::find().filter(UserColumn::id.eq(1)).one(&mut pool)?.unwrap();
let mut active = ActiveModel::from_model(user);
// Or equivalently: let mut active: ActiveModel<User> = user.into();

// Stage specific changes
active.set("name", "New Name");

// Fire the targeted UPDATE: UPDATE users SET name = $1 WHERE id = $2
active.save(&mut pool)?;
```

The `save()` method auto-detects whether to `INSERT` (if `is_new`) or `UPDATE` (if existing).

---

## 4. Pagination

Fetch paginated results with automatic total count calculation:

```rust
let page = User::find()
    .filter(UserColumn::age.gt(18))
    .order_by("name ASC")
    .paginate(20)        // 20 items per page
    .page(2)             // fetch page 2
    .fetch(&mut pool)?;

println!("Page {}/{}", page.page, page.total_pages);
println!("Showing {} of {} total items", page.items.len(), page.total);

for user in &page.items {
    println!("  {} ({})", user.name, user.id);
}
```

The `Page<M>` struct contains:
- `items: Vec<M>` — the records for this page
- `total: i64` — total matching records across all pages
- `page: usize` — current page number
- `page_size: usize` — requested page size
- `total_pages: usize` — computed total page count

---

## 5. Migrations

Transition smoothly from auto-syncing development schemas to deterministic production environments using `Migration` and `MigrationManager`.

### Development: Auto-Migration

During development, use `sync_schema()` to automatically diff and migrate table columns:

```rust
User::sync_schema(&mut pool)?;  // Creates table or adds missing columns
Post::sync_schema(&mut pool)?;
```

### Production: Explicit Migrations

For production, use the `Migration` trait and `MigrationManager`:

```rust
use chopin_orm::{Executor, OrmResult};
use chopin_orm::migrations::{Migration, MigrationManager};

struct AddEmailColumn;

impl Migration for AddEmailColumn {
    fn name(&self) -> &'static str {
        "2024_01_15_add_email_column"
    }

    fn up(&self, executor: &mut dyn Executor) -> OrmResult<()> {
        executor.execute("ALTER TABLE users ADD COLUMN email TEXT", &[])?;
        Ok(())
    }

    fn down(&self, executor: &mut dyn Executor) -> OrmResult<()> {
        executor.execute("ALTER TABLE users DROP COLUMN email", &[])?;
        Ok(())
    }
}

// Apply all pending migrations
fn migrate(pool: &mut impl Executor) -> OrmResult<()> {
    MigrationManager::ensure_migrations_table(pool)?;
    let migrations: Vec<&dyn Migration> = vec![&AddEmailColumn];
    MigrationManager::up(pool, &migrations)
}

// Rollback
fn rollback(pool: &mut impl Executor) -> OrmResult<()> {
    let migrations: Vec<&dyn Migration> = vec![&AddEmailColumn];
    MigrationManager::down(pool, &migrations)
}
```

---

## 6. MockExecutor (Testing)

Isolate your domain logic from PostgreSQL using `MockExecutor` and the `mock_row!` macro.

```rust
#[cfg(test)]
mod tests {
    use super::*;
    use chopin_orm::MockExecutor;
    use chopin_orm::mock_row;

    #[test]
    fn test_user_retrieval() {
        let mut executor = MockExecutor::new();

        // Push mock results that will be returned for the next query
        executor.push_result(vec![
            mock_row!("id" => 1, "name" => "Alice"),
            mock_row!("id" => 2, "name" => "Bob"),
        ]);

        let users = User::find().all(&mut executor).unwrap();
        assert_eq!(users.len(), 2);

        // Verify the generated SQL
        let (query, param_count) = &executor.executed_queries[0];
        assert!(query.contains("SELECT id, name FROM users"));
        assert_eq!(*param_count, 0);
    }
}
```

> **Note:** `mock_row!` is a `#[macro_export]` macro — use it directly or import via `use chopin_orm::mock_row;`.

---

## 7. Logging and Tracing

Wrap any executor with `LoggedExecutor` to trace generated queries:

```rust
use chopin_orm::LoggedExecutor;

let mut logged = LoggedExecutor::new(&mut pool);
let items = User::find().all(&mut logged)?;
// Prints: [SQL] SELECT id, name FROM users (0 params) — 1.2ms
```

---

## 8. Transactions

Use the ORM `Transaction` wrapper for RAII-style transaction management:

```rust
use chopin_orm::Transaction;

let mut conn = pool.get()?;
let mut tx = Transaction::begin(&mut conn)?;

let mut user = User { id: 0, name: "Alice".into() };
user.insert(&mut tx)?;

let mut post = Post { id: 0, user_id: user.id, title: "Hello".into() };
post.insert(&mut tx)?;

tx.commit()?;  // or tx.rollback()?
```

Alternatively, use `chopin-pg`'s closure-based transactions directly:

```rust
let mut conn = pool.get()?;
conn.transaction(|tx| {
    tx.execute("INSERT INTO users (name) VALUES ($1)", &[&"Bob"])?;
    Ok(())
})?;
```

---

## 9. Aggregations with GROUP BY / HAVING

Use `select_only`, `group_by`, and `having` for aggregate queries:

```rust
use chopin_pg::PgValue;

// Get raw rows for aggregate queries
let rows = User::find()
    .select_only(vec![
        UserColumn::age.count(),
    ])
    .group_by("age")
    .having(UserColumn::age.gt(18))
    .into_raw(&mut pool)?;

for row in &rows {
    let count: i64 = row.get_typed(0)?;
    println!("Count: {}", count);
}
```

---

## 10. Validation

All models must implement `Validate` (it's a supertrait of `Model`). The default implementation passes all validation:

```rust
// Simple: use the default (always passes)
impl Validate for User {}

// Custom validation
impl Validate for User {
    fn validate(&self) -> Result<(), Vec<String>> {
        let mut errors = Vec::new();
        if self.name.is_empty() {
            errors.push("Name cannot be empty".to_string());
        }
        if let Some(age) = self.age {
            if age < 0 || age > 150 {
                errors.push("Age must be between 0 and 150".to_string());
            }
        }
        if errors.is_empty() { Ok(()) } else { Err(errors) }
    }
}
```
