sqlw 0.1.0

//! End-to-end CRUD usecases demonstrating the idiomatic library usage pattern:
//!
//!   schema! → associated methods → query_qmark! → QueryExecutor
//!
//! Schema types own their CRUD logic as associated methods, accept
//! backend-agnostic `&impl QueryExecutor`, and return domain newtypes.
//! All schema fields use newtypes — no raw primitives on the struct.
//! `IntoValue` derive emits both `From<T> for Value` and `From<&T> for Value`,
//! so owned and borrowed values work directly in `{placeholder}` bindings.

use sqlw::{
    FromRow, IntoValue, Query, QueryExecutor, TryFromValueRef, query_qmark as query, schema,
};
use sqlw_backend::turso::TursoExecutor;

type StdResult<T = ()> = Result<T, Box<dyn std::error::Error>>;

#[derive(Debug)]
pub struct Paginated<T> {
    pub next: Option<ID>,
    pub items: Vec<T>,
}

impl<T> Paginated<T> {
    pub fn new(next: Option<ID>, items: Vec<T>) -> Self {
        Self { next, items }
    }
}

// ---------------------------------------------------------------------------
// Domain newtypes — these are used both as schema field types (so FromRow
// extracts them directly) and as parameters in query methods (so `{val}`
// works directly without unwrapping).
// ---------------------------------------------------------------------------

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, TryFromValueRef, IntoValue)]
pub struct ID(pub i64);

#[derive(Debug, Clone, PartialEq, Eq, Default, TryFromValueRef, IntoValue)]
pub struct ConversationTitle(pub String);

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, TryFromValueRef, IntoValue)]
pub struct Timestamp(pub i64);

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct Limit(pub usize);

#[derive(Debug, Clone, Copy, PartialEq, Eq, TryFromValueRef, IntoValue)]
pub enum Role {
    #[value = "user"]
    User,
    #[value = "assistant"]
    Assistant,
}

impl Default for Role {
    fn default() -> Self {
        Role::User
    }
}

// ---------------------------------------------------------------------------
// Manual FromRow structs for non-schema queries (computed columns, aliases).
// ---------------------------------------------------------------------------

#[derive(Debug, PartialEq, FromRow)]
pub struct ConversationTitleView {
    id: i64,
    title: String,
    title_length: usize,
}

#[derive(Debug, PartialEq, sqlw::FromRow)]
pub struct MessageCount {
    #[field = "id"]
    conv_id: i64,
    #[field = "title"]
    conv_title: ConversationTitle,
    #[field = "message_count"]
    count: i64,
}

// ---------------------------------------------------------------------------
// Schema definitions — field types are domain newtypes, so the auto-generated
// FromRow extracts them directly and query constants carry type info.
// ---------------------------------------------------------------------------

schema! {
    /// Chat conversations containing messages
    Conversation "conversation" {
        /// Unique identifier
        ID: ID "id",
        /// Human-readable conversation title
        TITLE: ConversationTitle "title",
        /// Creation timestamp (epoch seconds)
        CREATED_AT: Timestamp "created_at",
        /// Last update timestamp (epoch seconds)
        UPDATED_AT: Timestamp "updated_at",
    }
}

impl Conversation {
    pub async fn create(
        db: &impl QueryExecutor,
        id: ID,
        title: ConversationTitle,
        now: Timestamp,
    ) -> StdResult<ID> {
        let query = query! {
            INSERT INTO Conversation::TABLE
                (Conversation::ID, Conversation::TITLE,
                 Conversation::CREATED_AT, Conversation::UPDATED_AT)
            VALUES ({id}, {title}, {now}, {now})
        };

        db.query_void(query).await?;
        Ok(id)
    }

    pub async fn load(db: &impl QueryExecutor, id: &ID) -> StdResult<Option<Conversation>> {
        let query = query! {
            SELECT Conversation::ID,
                   Conversation::TITLE,
                   Conversation::CREATED_AT,
                   Conversation::UPDATED_AT
            FROM Conversation::TABLE
            WHERE Conversation::ID = {id}
        };
        Ok(db.query_one::<Conversation>(query).await?)
    }

    pub async fn paginated(
        db: &impl QueryExecutor,
        after_id: Option<ID>,
        limit: Limit,
    ) -> StdResult<Paginated<Conversation>> {
        let query = query! {
            SELECT Conversation::ID,
                   Conversation::TITLE,
                   Conversation::CREATED_AT,
                   Conversation::UPDATED_AT
            FROM Conversation::TABLE
            WHERE {after_id} IS NULL OR Conversation::ID > {after_id}
            ORDER BY Conversation::ID ASC
            LIMIT {limit.0}
        };

        let conversations = db.query_list::<Conversation>(query).await?;
        let next = conversations.last().map(|c| ID(c.id.0));

        Ok(Paginated::new(next, conversations))
    }

    pub async fn update_title(
        db: &impl QueryExecutor,
        id: &ID,
        title: &ConversationTitle,
        now: Timestamp,
    ) -> StdResult<()> {
        let query = query! {
            UPDATE Conversation::TABLE
            SET Conversation::TITLE = {title},
                Conversation::UPDATED_AT = {now}
            WHERE Conversation::ID = {id}
        };

        db.query_void(query).await?;
        Ok(())
    }

    pub async fn delete(db: &impl QueryExecutor, id: &ID) -> StdResult<()> {
        let query = query! {
            DELETE FROM Conversation::TABLE
            WHERE Conversation::ID = {id}
        };
        db.query_void(query).await?;
        Ok(())
    }

    pub fn create_table() -> Query {
        query! {
            CREATE TABLE IF NOT EXISTS Conversation::TABLE (
                Conversation::ID INTEGER PRIMARY KEY,
                Conversation::TITLE TEXT NOT NULL,
                Conversation::CREATED_AT INTEGER NOT NULL,
                Conversation::UPDATED_AT INTEGER NOT NULL
            )
        }
    }
}

schema! {
    /// Messages within a conversation
    Message "message" {
        /// Unique identifier
        ID: ID "id",
        /// Owning conversation
        CONVERSATION_ID: ID "conversation_id",
        /// Message role: "user" or "assistant"
        ROLE: Role "role",
        /// Message body
        CONTENT: String "content",
        /// Creation timestamp (epoch seconds)
        CREATED_AT: Timestamp "created_at",
    }
}

impl Message {
    pub async fn create(
        db: &impl QueryExecutor,
        id: ID,
        conversation_id: ID,
        role: Role,
        content: &str,
        now: Timestamp,
    ) -> StdResult<ID> {
        let query = query! {
            INSERT INTO Message::TABLE
                (Message::ID, Message::CONVERSATION_ID,
                 Message::ROLE, Message::CONTENT,
                 Message::CREATED_AT)
            VALUES ({id}, {conversation_id}, {role}, {content}, {now})
        };

        db.query_void(query).await?;
        Ok(id)
    }

    pub async fn list_for_conversation(
        db: &impl QueryExecutor,
        conversation_id: &ID,
    ) -> StdResult<Vec<Message>> {
        let query = query! {
            SELECT Message::ID,
                   Message::CONVERSATION_ID,
                   Message::ROLE,
                   Message::CONTENT,
                   Message::CREATED_AT
            FROM Message::TABLE
            WHERE Message::CONVERSATION_ID = {conversation_id}
            ORDER BY Message::ID ASC
        };

        Ok(db.query_list::<Message>(query).await?)
    }

    pub async fn delete(db: &impl QueryExecutor, id: &ID) -> StdResult<()> {
        let query = query! {
            DELETE FROM Message::TABLE
            WHERE Message::ID = {id}
        };
        db.query_void(query).await?;
        Ok(())
    }

    pub fn create_table() -> Query {
        query! {
            CREATE TABLE IF NOT EXISTS Message::TABLE (
                Message::ID INTEGER PRIMARY KEY,
                Message::CONVERSATION_ID INTEGER NOT NULL,
                Message::ROLE TEXT NOT NULL,
                Message::CONTENT TEXT NOT NULL,
                Message::CREATED_AT INTEGER NOT NULL,
                FOREIGN KEY (Message::CONVERSATION_ID) REFERENCES Conversation::TABLE(Conversation::ID)
            )
        }
    }

    pub fn create_indexes() -> Query {
        query! {
            CREATE INDEX IF NOT EXISTS idx_message_conversation
                ON Message::TABLE(Message::CONVERSATION_ID);
            CREATE INDEX IF NOT EXISTS idx_message_created
                ON Message::TABLE(Message::CREATED_AT);
        }
    }
}

// --------------------------------------------------------------------------
// Test helpers
// ---------------------------------------------------------------------------

async fn create_executor() -> TursoExecutor {
    let connector = || async {
        let db = ::turso::Builder::new_local(":memory:").build().await?;
        db.connect()
    };
    TursoExecutor::new(connector).await.unwrap()
}

async fn setup(db: &TursoExecutor) {
    db.query_void(Conversation::create_table()).await.unwrap();
    db.query_void(Message::create_table()).await.unwrap();
    db.query_void(Message::create_indexes()).await.unwrap();
}

// ---------------------------------------------------------------------------
// Tests
// ---------------------------------------------------------------------------

#[tokio::test]
async fn test_conversation_create_and_load() {
    let db = create_executor().await;
    setup(&db).await;

    let id = ID(1);
    let title = ConversationTitle("Hello World".into());
    let now = Timestamp(1_736_880_000);

    Conversation::create(&db, id, title.clone(), now)
        .await
        .unwrap();

    let loaded = Conversation::load(&db, &id).await.unwrap().unwrap();
    assert_eq!(loaded.id, id);
    assert_eq!(loaded.title, title);
    assert_eq!(loaded.created_at, now);
    assert_eq!(loaded.updated_at, now);
}

#[tokio::test]
async fn test_conversation_not_found() {
    let db = create_executor().await;
    setup(&db).await;

    let loaded = Conversation::load(&db, &ID(999)).await.unwrap();
    assert!(loaded.is_none());
}

#[tokio::test]
async fn test_conversation_update_title() {
    let db = create_executor().await;
    setup(&db).await;

    let id = ID(1);
    Conversation::create(&db, id, ConversationTitle("Old".into()), Timestamp(100))
        .await
        .unwrap();

    Conversation::update_title(
        &db,
        &id,
        &ConversationTitle("Updated".into()),
        Timestamp(200),
    )
    .await
    .unwrap();

    let loaded = Conversation::load(&db, &id).await.unwrap().unwrap();
    assert_eq!(loaded.title, ConversationTitle("Updated".into()));
    assert_eq!(loaded.updated_at, Timestamp(200));
    assert_eq!(loaded.created_at, Timestamp(100));
}

#[tokio::test]
async fn test_conversation_paginated() {
    let db = create_executor().await;
    setup(&db).await;

    for i in 1..=5_i64 {
        Conversation::create(
            &db,
            ID(i),
            ConversationTitle(format!("Chat {i}")),
            Timestamp(i),
        )
        .await
        .unwrap();
    }

    let page1 = Conversation::paginated(&db, None, Limit(2)).await.unwrap();
    assert_eq!(page1.items.len(), 2);
    assert_eq!(page1.items[0].id, ID(1));
    assert_eq!(page1.items[1].id, ID(2));
    assert_eq!(page1.next, Some(ID(2)));

    let page2 = Conversation::paginated(&db, Some(ID(2)), Limit(2))
        .await
        .unwrap();
    assert_eq!(page2.items.len(), 2);
    assert_eq!(page2.items[0].id, ID(3));
    assert_eq!(page2.items[1].id, ID(4));
    assert_eq!(page2.next, Some(ID(4)));

    let page3 = Conversation::paginated(&db, Some(ID(4)), Limit(2))
        .await
        .unwrap();
    assert_eq!(page3.items.len(), 1);
    assert_eq!(page3.items[0].id, ID(5));
    assert_eq!(page3.next, Some(ID(5)));

    let page4 = Conversation::paginated(&db, Some(ID(5)), Limit(2))
        .await
        .unwrap();
    assert_eq!(page4.items.len(), 0);
    assert_eq!(page4.next, None);
}

#[tokio::test]
async fn test_message_crud_within_conversation() {
    let db = create_executor().await;
    setup(&db).await;

    let conv_id = ID(10);
    let now = Timestamp(1_736_900_000);

    Conversation::create(&db, conv_id, ConversationTitle("SQL Chat".into()), now)
        .await
        .unwrap();

    let msg1 = Message::create(&db, ID(1), conv_id, Role::User, "What is sqlw?", now)
        .await
        .unwrap();
    let msg2 = Message::create(
        &db,
        ID(2),
        conv_id,
        Role::Assistant,
        "A compile-time SQL query builder.",
        now,
    )
    .await
    .unwrap();

    assert_eq!(msg1, ID(1));
    assert_eq!(msg2, ID(2));

    let messages = Message::list_for_conversation(&db, &conv_id).await.unwrap();
    assert_eq!(messages.len(), 2);
    assert_eq!(messages[0].role, Role::User);
    assert_eq!(messages[1].role, Role::Assistant);

    Message::delete(&db, &ID(1)).await.unwrap();

    let remaining = Message::list_for_conversation(&db, &conv_id).await.unwrap();
    assert_eq!(remaining.len(), 1);
    assert_eq!(remaining[0].id, ID(2));
}

#[tokio::test]
async fn test_conversation_delete_cascades() {
    let db = create_executor().await;
    setup(&db).await;

    let conv_id = ID(20);
    let now = Timestamp(1_737_000_000);

    Conversation::create(&db, conv_id, ConversationTitle("To Delete".into()), now)
        .await
        .unwrap();

    Message::create(&db, ID(1), conv_id, Role::User, "Hello!", now)
        .await
        .unwrap();

    Conversation::delete(&db, &conv_id).await.unwrap();

    let loaded = Conversation::load(&db, &conv_id).await.unwrap();
    assert!(loaded.is_none());
}

#[tokio::test]
async fn test_batch_create_conversation_and_messages() {
    let db = create_executor().await;
    setup(&db).await;

    fn create_conv() -> Query {
        query! {
            INSERT INTO Conversation::TABLE
                (Conversation::ID, Conversation::TITLE,
                 Conversation::CREATED_AT, Conversation::UPDATED_AT)
            VALUES ({ID(1)}, {ConversationTitle("Batch Chat".into())}, {Timestamp(1000)}, {Timestamp(1000)})
        }
    }

    fn add_msg1() -> Query {
        query! {
            INSERT INTO Message::TABLE
                (Message::ID, Message::CONVERSATION_ID,
                 Message::ROLE, Message::CONTENT,
                 Message::CREATED_AT)
            VALUES ({ID(1)}, {ID(1)}, {Role::User}, {"First"}, {Timestamp(1001)})
        }
    }

    fn add_msg2() -> Query {
        query! {
            INSERT INTO Message::TABLE
                (Message::ID, Message::CONVERSATION_ID,
                 Message::ROLE, Message::CONTENT,
                 Message::CREATED_AT)
            VALUES ({ID(2)}, {ID(1)}, {Role::Assistant}, {"Second"}, {Timestamp(1002)})
        }
    }

    let scripts: &[fn() -> Query] = &[create_conv, add_msg1, add_msg2];
    db.batch(scripts).await.unwrap();

    let conv = Conversation::load(&db, &ID(1)).await.unwrap().unwrap();
    assert_eq!(conv.title, ConversationTitle("Batch Chat".into()));

    let msgs = Message::list_for_conversation(&db, &ID(1)).await.unwrap();
    assert_eq!(msgs.len(), 2);
    assert_eq!(msgs[0].content, "First");
    assert_eq!(msgs[1].content, "Second");
}

#[tokio::test]
async fn test_computed_column_with_derived_fromrow() {
    let db = create_executor().await;
    setup(&db).await;

    Conversation::create(
        &db,
        ID(200),
        ConversationTitle("Compute field".into()),
        Timestamp(100),
    )
    .await
    .unwrap();

    let query = query! {
        SELECT Conversation::ID, Conversation::TITLE, LENGTH(Conversation::TITLE) AS title_length
        FROM Conversation::TABLE
        WHERE Conversation::ID = {ID(200)}
    };

    let view = db
        .query_one::<ConversationTitleView>(query)
        .await
        .unwrap()
        .expect("expected a result");

    assert_eq!(view.id, 200);
    assert_eq!(view.title, "Compute field");
    assert_eq!(view.title_length, 13);
}

#[tokio::test]
async fn test_derived_from_row_with_field_attrs() {
    let db = create_executor().await;
    setup(&db).await;

    Conversation::create(
        &db,
        ID(300),
        ConversationTitle("Derived".into()),
        Timestamp(200),
    )
    .await
    .unwrap();

    Message::create(&db, ID(10), ID(300), Role::User, "A", Timestamp(201))
        .await
        .unwrap();
    Message::create(&db, ID(11), ID(300), Role::Assistant, "B", Timestamp(202))
        .await
        .unwrap();

    let query = query! {
        SELECT
            Conversation::ID,
            Conversation::TITLE,
            (SELECT COUNT(*) FROM Message::TABLE
             WHERE Message::CONVERSATION_ID = {ID(300)}) AS message_count
        FROM Conversation::TABLE
        WHERE Conversation::ID = {ID(300)}
    };

    let result = db
        .query_one::<MessageCount>(query)
        .await
        .unwrap()
        .expect("expected a result");

    assert_eq!(result.conv_id, 300);
    assert_eq!(result.conv_title, ConversationTitle("Derived".into()));
    assert_eq!(result.count, 2);
}

#[tokio::test]
async fn test_typed_binding_through_where_clause() {
    let db = create_executor().await;
    setup(&db).await;

    Conversation::create(
        &db,
        ID(100),
        ConversationTitle("Typed Binding".into()),
        Timestamp(999),
    )
    .await
    .unwrap();

    let query = query! {
        SELECT Conversation::ID,
               Conversation::TITLE,
               Conversation::CREATED_AT,
               Conversation::UPDATED_AT
        FROM Conversation::TABLE
        WHERE Conversation::ID = {ID(100)}
    };

    let conv = db.query_one::<Conversation>(query).await.unwrap().unwrap();
    assert_eq!(conv.id, ID(100));
    assert_eq!(conv.title, ConversationTitle("Typed Binding".into()));
}