SQLX PostgreSQL Compile Checked Query

use sqlx::postgres::PgPoolOptions;
use sqlx::{query_as, PgPool, Row};
use std::time::Duration;

#[derive(Debug, sqlx::FromRow)]
struct Invoice {
    id: i64,
    customer: String,
    amount_cents: i64,
    status: String,
}

async fn fetch_open_invoices(pool: &PgPool, customer: &str) -> Result<Vec<Invoice>, sqlx::Error> {
    // query! 宏在编译期连库校验 SQL 与列类型（此处用 query_as 演示运行时等价物）
    query_as::<_, Invoice>(
        "SELECT id, customer, amount_cents, status \
         FROM invoices \
         WHERE customer = $1 AND status = 'open' \
         ORDER BY amount_cents DESC LIMIT $2",
    )
    .bind(customer)
    .bind(20i32)
    .fetch_all(pool)
    .await
}

async fn settle(pool: &PgPool, invoice_id: i64) -> Result<u64, sqlx::Error> {
    let mut tx = pool.begin().await?;

    let updated = sqlx::query("UPDATE invoices SET status = 'paid' WHERE id = $1")
        .bind(invoice_id)
        .execute(&mut *tx)
        .await?
        .rows_affected();

    sqlx::query("INSERT INTO ledger (invoice_id, kind) VALUES ($1, 'settle')")
        .bind(invoice_id)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(updated)
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .min_connections(2)
        .acquire_timeout(Duration::from_secs(3))
        .connect("postgres://user:pass@localhost/billing")
        .await?;

    let invoices = fetch_open_invoices(&pool, "acme").await?;
    println!("open invoices: {}", invoices.len());

    for invoice in &invoices {
        let affected = settle(&pool, invoice.id).await?;
        println!("invoice {} settled ({} row)", invoice.id, affected);
    }

    let total: i64 = sqlx::query("SELECT coalesce(sum(amount_cents), 0) FROM invoices")
        .fetch_one(&pool)
        .await?
        .get(0);
    println!("ledger total cents: {total}");

    pool.close().await;
    Ok(())
}