nulid 0.10.1

//! Example demonstrating NULID usage with `SQLx` and `MySQL/MariaDB`.
//!
//! This example shows how to:
//! - Store NULIDs as BINARY(16) in `MySQL/MariaDB`
//! - Query records by NULID
//! - Use NULID in structs with `sqlx::FromRow`
//! - Leverage NULID's sortability for time-ordered queries
//!
//! # Setup
//!
//! 1. Start a MySQL/MariaDB server.
//!
//! 2. Set the `DATABASE_URL` environment variable:
//!    ```bash
//!    export DATABASE_URL="mysql://user:password@localhost:3306/nulid_test"
//!    ```
//!
//! 3. Run the example:
//!    ```bash
//!    cargo run --example sqlx_mysql --features sqlx-mysql
//!    ```
//!
//! # Schema
//!
//! ```sql
//! CREATE TABLE users (
//!     id BINARY(16) PRIMARY KEY,
//!     name VARCHAR(255) NOT NULL,
//!     email VARCHAR(255) NOT NULL,
//!     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
//! );
//!
//! CREATE TABLE events (
//!     id BINARY(16) PRIMARY KEY,
//!     user_id BINARY(16) NOT NULL,
//!     event_type VARCHAR(50) NOT NULL,
//!     payload TEXT,
//!     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
//!     FOREIGN KEY (user_id) REFERENCES users(id)
//! );
//!
//! CREATE INDEX idx_events_user_id ON events(user_id);
//! CREATE INDEX idx_events_created_at ON events(created_at);
//! ```

use nulid::Nulid;
use sqlx::{MySqlPool, Row, mysql::MySqlPoolOptions};

#[derive(Debug, sqlx::FromRow)]
#[allow(dead_code)]
struct User {
    id: Nulid,
    name: String,
    email: String,
}

#[derive(Debug, sqlx::FromRow)]
#[allow(dead_code)]
#[allow(clippy::struct_field_names)]
struct Event {
    id: Nulid,
    user_id: Nulid,
    event_type: String,
    payload: Option<String>,
}

async fn setup_database(pool: &MySqlPool) -> Result<(), sqlx::Error> {
    // Drop tables if they exist (for clean example runs)
    sqlx::query("DROP TABLE IF EXISTS events")
        .execute(pool)
        .await?;
    sqlx::query("DROP TABLE IF EXISTS users")
        .execute(pool)
        .await?;

    // Create users table
    sqlx::query(
        r"
        CREATE TABLE users (
            id BINARY(16) PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        ",
    )
    .execute(pool)
    .await?;

    // Create events table
    sqlx::query(
        r"
        CREATE TABLE events (
            id BINARY(16) PRIMARY KEY,
            user_id BINARY(16) NOT NULL,
            event_type VARCHAR(50) NOT NULL,
            payload TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
        ",
    )
    .execute(pool)
    .await?;

    // Create indexes
    sqlx::query("CREATE INDEX idx_events_user_id ON events(user_id)")
        .execute(pool)
        .await?;

    sqlx::query("CREATE INDEX idx_events_created_at ON events(created_at)")
        .execute(pool)
        .await?;

    Ok(())
}

async fn insert_user(
    pool: &MySqlPool,
    id: Nulid,
    name: &str,
    email: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query("INSERT INTO users (id, name, email) VALUES (?, ?, ?)")
        .bind(id)
        .bind(name)
        .bind(email)
        .execute(pool)
        .await?;

    println!("Inserted user: {name} ({id})");
    Ok(())
}

async fn get_user(pool: &MySqlPool, id: Nulid) -> Result<User, sqlx::Error> {
    sqlx::query_as::<_, User>("SELECT id, name, email FROM users WHERE id = ?")
        .bind(id)
        .fetch_one(pool)
        .await
}

async fn insert_event(
    pool: &MySqlPool,
    id: Nulid,
    user_id: Nulid,
    event_type: &str,
    payload: Option<String>,
) -> Result<(), sqlx::Error> {
    sqlx::query("INSERT INTO events (id, user_id, event_type, payload) VALUES (?, ?, ?, ?)")
        .bind(id)
        .bind(user_id)
        .bind(event_type)
        .bind(payload)
        .execute(pool)
        .await?;

    println!("Inserted event: {event_type} for user {user_id}");
    Ok(())
}

async fn get_user_events(pool: &MySqlPool, user_id: Nulid) -> Result<Vec<Event>, sqlx::Error> {
    sqlx::query_as::<_, Event>(
        "SELECT id, user_id, event_type, payload FROM events WHERE user_id = ? ORDER BY id",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await
}

async fn get_recent_events(pool: &MySqlPool, limit: i64) -> Result<Vec<Event>, sqlx::Error> {
    sqlx::query_as::<_, Event>(
        "SELECT id, user_id, event_type, payload FROM events ORDER BY id DESC LIMIT ?",
    )
    .bind(limit)
    .fetch_all(pool)
    .await
}

async fn count_users(pool: &MySqlPool) -> Result<i64, sqlx::Error> {
    let row = sqlx::query("SELECT COUNT(*) as count FROM users")
        .fetch_one(pool)
        .await?;
    Ok(row.get("count"))
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn core::error::Error>> {
    println!("NULID + SQLx + MySQL Example\n");

    // Get database URL from environment variable
    let database_url = std::env::var("DATABASE_URL").unwrap_or_else(|_| {
        println!("DATABASE_URL not set, using default connection string.");
        println!("Set DATABASE_URL=mysql://user:password@localhost:3306/nulid_test\n");
        "mysql://root@localhost:3306/nulid_test".to_string()
    });

    println!("Connecting to MySQL database...\n");

    // Create connection pool
    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect(&database_url)
        .await?;

    // Setup database schema
    println!("Setting up database schema...");
    setup_database(&pool).await?;
    println!();

    // Generate NULIDs for users
    println!("Creating users...");
    let user1_id = Nulid::new()?;
    let user2_id = Nulid::new()?;

    insert_user(&pool, user1_id, "Alice Smith", "alice@example.com").await?;
    insert_user(&pool, user2_id, "Bob Jones", "bob@example.com").await?;
    println!();

    // Retrieve user
    println!("Fetching user...");
    let user = get_user(&pool, user1_id).await?;
    println!("Found user: {user:?}\n");

    // Generate events with NULIDs (naturally sorted by time)
    println!("Creating events...");
    for i in 0..5 {
        let event_id = Nulid::new()?;
        let event_type = if i % 2 == 0 { "login" } else { "page_view" };
        let payload =
            format!("{{\"ip\": \"192.168.1.1\", \"user_agent\": \"Mozilla/5.0\", \"index\": {i}}}");

        insert_event(&pool, event_id, user1_id, event_type, Some(payload)).await?;

        // Small delay to ensure different timestamps
        tokio::time::sleep(tokio::time::Duration::from_millis(10)).await;
    }

    // Create events for second user
    for _i in 0..3 {
        let event_id = Nulid::new()?;
        insert_event(&pool, event_id, user2_id, "api_call", None).await?;
        tokio::time::sleep(tokio::time::Duration::from_millis(10)).await;
    }
    println!();

    // Query user events (sorted by NULID = sorted by time)
    println!("Fetching user events (sorted by NULID)...");
    let events = get_user_events(&pool, user1_id).await?;
    for (i, event) in events.iter().enumerate() {
        println!("  Event {}: {} at {}", i + 1, event.event_type, event.id);
    }
    println!();

    // Query recent events
    println!("Fetching recent events (DESC)...");
    let recent = get_recent_events(&pool, 5).await?;
    for event in &recent {
        println!(
            "  {} - {} (user: {})",
            event.id, event.event_type, event.user_id
        );
    }
    println!();

    // Count users
    let user_count = count_users(&pool).await?;
    println!("Total users: {user_count}\n");

    // Demonstrate NULID storage
    println!("NULID Storage:");
    println!("  NULID:  {user1_id}");
    println!("  Bytes:  {:02X?}", user1_id.to_bytes());
    println!("  Stored as BINARY(16) in MySQL, queried as NULID in Rust!");
    println!();

    // Demonstrate sortability
    println!("NULID Benefits:");
    println!("  - Stored as BINARY(16) in MySQL (16 bytes, optimal storage)");
    println!("  - Automatically sorted by creation time");
    println!("  - No need for separate created_at columns for ordering");
    println!("  - Nanosecond precision prevents collisions");
    println!("  - Compatible with existing UUID-based systems via conversion");
    println!();

    // Cleanup
    println!("Cleaning up...");
    sqlx::query("DROP TABLE IF EXISTS events")
        .execute(&pool)
        .await?;
    sqlx::query("DROP TABLE IF EXISTS users")
        .execute(&pool)
        .await?;
    println!("Tables dropped\n");

    println!("Example completed successfully!");

    Ok(())
}

#[cfg(not(feature = "sqlx-mysql"))]
fn main() {
    println!("This example requires the 'sqlx-mysql' feature to be enabled.");
    println!("Run with: cargo run --example sqlx_mysql --features sqlx-mysql");
}