server/command/

transaction.rs

use cfg_if::cfg_if;
#[cfg(not(feature = "scripting"))]
use finance::{price::Price, split::Split};
use finance::{tag::Tag, transaction::Transaction};
#[cfg(feature = "scripting")]
use scripting::ScriptExecutor;
use sqlx::{
    Acquire,
    types::Uuid,
    types::chrono::{DateTime, Utc},
};
use std::{collections::HashMap, fmt::Debug};
use supp_macro::command;

#[cfg(feature = "scripting")]
use crate::script::TransactionState;
use crate::{config::ConfigError, user::User};

use super::{CmdError, CmdResult, FinanceEntity, PaginationInfo};

command! {
    CreateTransaction {
        #[required]
        user_id: Uuid,
        #[required]
        splits: Vec<FinanceEntity>,
        #[required]
        id: Uuid,
        #[required]
        post_date: DateTime<Utc>,
        #[required]
        enter_date: DateTime<Utc>,
        #[optional]
        prices: Vec<FinanceEntity>,
        #[optional]
        note: String,
        #[optional]
        split_tags: Vec<(Uuid, Tag)>,
    } => {


        let user = User { id: user_id };

        let mut conn = user.get_connection().await.map_err(|err| {
            log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
            ConfigError::DB
        })?;

        let tx = Transaction {
            id,
            post_date,
            enter_date,
        };

        cfg_if! {
            if #[cfg(feature = "scripting")] {
                let (transaction, splits, prices, transaction_tags, split_tags) = {
                    let scripts: Vec<(Uuid, Vec<u8>)> = sqlx::query_file!("sql/select/artifacts/enabled.sql")
                        .fetch_all(&mut *conn)
                        .await?
                        .into_iter()
                        .map(|row| (row.id, row.bytecode))
                        .collect();

                    let state = TransactionState::new(tx)
                        .with(splits)
                        .with(prices.unwrap_or_default())
                        .with_note(note)
                        .with_split_tags(split_tags.unwrap_or_default());

                    let state = if scripts.is_empty() {
                        state
                    } else {
                        let report = tokio::task::spawn_blocking(move || {
                            let executor = ScriptExecutor::new();
                            state.run_scripts(&executor, &scripts)
                        })
                        .await
                        .map_err(|e| CmdError::Script(format!("{e:?}")))?
                        .map_err(|e| {
                            log::error!("{}", t!("Script execution failed: %{err}", err = e : {:?}));
                            CmdError::Script(format!("{e:?}"))
                        })?;
                        for failure in &report.failures {
                            log::error!(
                                "{}",
                                t!(
                                    "Script %{id} failed: %{code}: %{message}",
                                    id = failure.script_id,
                                    code = failure.code,
                                    message = failure.message
                                )
                            );
                        }
                        report.state
                    };

                    (state.transaction, state.splits, state.prices, state.transaction_tags, state.split_tags)
                };
            } else {
                let (transaction, splits, prices, transaction_tags, split_tags) = {
                    let splits: Vec<Split> = splits
                        .into_iter()
                        .filter_map(|e| match e {
                            FinanceEntity::Split(s) => Some(s),
                            _ => None,
                        })
                        .collect();

                    let prices: Vec<Price> = prices
                        .map(|ps| {
                            ps.into_iter()
                                .filter_map(|e| match e {
                                    FinanceEntity::Price(p) => Some(p),
                                    _ => None,
                                })
                                .collect()
                        })
                        .unwrap_or_default();

                    let transaction_tags: Vec<Tag> = note
                        .map(|n| {
                            vec![Tag {
                                id: Uuid::new_v4(),
                                tag_name: "note".to_string(),
                                tag_value: n,
                                description: None,
                            }]
                        })
                        .unwrap_or_default();

                    let split_tags: Vec<(Uuid, Tag)> = split_tags.unwrap_or_default();

                    (tx, splits, prices, transaction_tags, split_tags)
                };
            }
        }

        // Common ticket operations
        let mut ticket = transaction.enter(&mut *conn).await?;

        let split_refs: Vec<_> = splits.iter().collect();
        ticket.add_splits(&split_refs).await?;

        if !prices.is_empty() {
            let price_refs: Vec<_> = prices.iter().collect();
            ticket.add_conversions(&price_refs).await?;
        }

        if !transaction_tags.is_empty() {
            let tag_refs: Vec<_> = transaction_tags.iter().collect();
            ticket.add_tags(&tag_refs).await?;
        }

        if !split_tags.is_empty() {
            ticket.add_split_tags(&split_tags).await?;
        }

        ticket.commit().await?;

        Ok(Some(CmdResult::Entity(FinanceEntity::Transaction(transaction))))
    }
}

command! {
    ListTransactions {
        #[required]
        user_id: Uuid,
        #[optional]
        account: Uuid,
        #[optional]
        limit: i64,
        #[optional]
        offset: i64,
        #[optional]
        date_from: DateTime<Utc>,
        #[optional]
        date_to: DateTime<Utc>,
    } => {
        let user = User { id: user_id };
        let mut conn = user.get_connection().await.map_err(|err| {
            log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
            ConfigError::DB
        })?;

        let account_uuid = account.as_ref();
        let effective_limit = limit.unwrap_or(20);
        let effective_offset = offset.unwrap_or(0);
        let date_from_ref = date_from.as_ref();
        let date_to_ref = date_to.as_ref();

        let count_result = sqlx::query_file!(
            "sql/count/transactions/filtered.sql",
            account_uuid,
            date_from_ref,
            date_to_ref
        )
        .fetch_one(&mut *conn)
        .await?;

        let total_count = count_result.count.unwrap_or(0);

        let transactions = sqlx::query_file!(
            "sql/select/transactions/paginated.sql",
            account_uuid,
            date_from_ref,
            date_to_ref,
            effective_limit,
            effective_offset
        )
        .fetch_all(&mut *conn)
        .await?;

        let mut tagged_transactions = Vec::new();
        for tx_row in transactions {
            let transaction = Transaction {
                id: tx_row.id,
                post_date: tx_row.post_date,
                enter_date: tx_row.enter_date,
            };

            let tags: HashMap<String, FinanceEntity> =
                sqlx::query_file!("sql/select/tags/by_transaction.sql", &transaction.id)
                    .fetch_all(&mut *conn)
                    .await?
                    .into_iter()
                    .map(|row| {
                        (
                            row.tag_name.clone(),
                            FinanceEntity::Tag(Tag {
                                id: row.id,
                                tag_name: row.tag_name,
                                tag_value: row.tag_value,
                                description: row.description,
                            }),
                        )
                    })
                    .collect();

            tagged_transactions.push((FinanceEntity::Transaction(transaction), tags));
        }

        let pagination = PaginationInfo {
            total_count,
            limit: effective_limit,
            offset: effective_offset,
            has_more: effective_offset + (tagged_transactions.len() as i64) < total_count,
        };

        Ok(Some(CmdResult::TaggedEntities {
            entities: tagged_transactions,
            pagination: Some(pagination),
        }))
    }
}

command! {
    GetTransaction {
        #[required]
        user_id: Uuid,
        #[required]
        transaction_id: Uuid,
    } => {
        let user = User { id: user_id };
        let mut conn = user.get_connection().await.map_err(|err| {
            log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
            ConfigError::DB
        })?;

        let tx_row = sqlx::query_file!("sql/select/transactions/by_id.sql", transaction_id)
            .fetch_optional(&mut *conn)
            .await?;

        if let Some(tx_row) = tx_row {
            let transaction = Transaction {
                id: tx_row.id,
                post_date: tx_row.post_date,
                enter_date: tx_row.enter_date,
            };

            let tags: HashMap<String, FinanceEntity> =
                sqlx::query_file!("sql/select/tags/by_transaction.sql", &transaction.id)
                    .fetch_all(&mut *conn)
                    .await?
                    .into_iter()
                    .map(|row| {
                        (
                            row.tag_name.clone(),
                            FinanceEntity::Tag(Tag {
                                id: row.id,
                                tag_name: row.tag_name,
                                tag_value: row.tag_value,
                                description: row.description,
                            }),
                        )
                    })
                    .collect();

            Ok(Some(CmdResult::TaggedEntities {
                entities: vec![(FinanceEntity::Transaction(transaction), tags)],
                pagination: None,
            }))
        } else {
            Ok(None)
        }
    }
}

command! {
    UpdateTransaction {
        #[required]
        user_id: Uuid,
        #[required]
        transaction_id: Uuid,
        #[optional]
        splits: Vec<FinanceEntity>,
        #[optional]
        post_date: DateTime<Utc>,
        #[optional]
        enter_date: DateTime<Utc>,
        #[optional]
        note: String,
        #[optional]
        prices: Vec<FinanceEntity>,
        #[optional]
        tags: HashMap<String, FinanceEntity>,
    } => {
        let user = User { id: user_id };
        let mut conn = user.get_connection().await.map_err(|err| {
            log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
            ConfigError::DB
        })?;

        let mut tx = conn.begin().await?;

        let existing = sqlx::query_file!("sql/select/transactions/by_id.sql", transaction_id)
            .fetch_optional(&mut *tx)
            .await?
            .ok_or_else(|| CmdError::Args("Transaction not found".to_string()))?;

        let final_post_date = post_date.unwrap_or(existing.post_date);
        let final_enter_date = enter_date.unwrap_or(existing.enter_date);

        // Validate new splits if provided (before making any changes)
        if let Some(ref new_splits) = splits {
            let mut commodity_sums: std::collections::HashMap<Uuid, num_rational::Rational64> =
                std::collections::HashMap::new();

            for entity in new_splits {
                if let FinanceEntity::Split(split) = entity {
                    // Validate split belongs to this transaction
                    if split.tx_id != transaction_id {
                        return Err(CmdError::Args("Split transaction ID mismatch".to_string()));
                    }

                    let split_value =
                        num_rational::Rational64::new(split.value_num, split.value_denom);
                    *commodity_sums.entry(split.commodity_id).or_insert(
                        num_rational::Rational64::new(0, 1),
                    ) += split_value;
                } else {
                    return Err(CmdError::Args("Invalid entity type in splits".to_string()));
                }
            }

            // Ensure splits sum to zero (double-entry bookkeeping requirement)
            // For multi-currency transactions, individual currencies won't sum to zero
            // (they're balanced via the price table), so only validate single-currency
            if commodity_sums.len() == 1 {
                for sum in commodity_sums.values() {
                    if *sum != num_rational::Rational64::new(0, 1) {
                        return Err(CmdError::Args("Splits must sum to zero".to_string()));
                    }
                }
            }
        }

        // Update transaction metadata first
        sqlx::query_file!(
            "sql/update/transactions/update.sql",
            transaction_id,
            final_post_date,
            final_enter_date
        )
        .execute(&mut *tx)
        .await?;

        // Process splits update atomically: delete then insert
        if let Some(new_splits) = splits {
            // Delete existing split_tags, splits and their associated prices
            sqlx::query_file!("sql/delete/split_tags/by_transaction.sql", transaction_id)
                .execute(&mut *tx)
                .await?;

            sqlx::query_file!("sql/delete/prices/by_splits.sql", transaction_id)
                .execute(&mut *tx)
                .await?;

            sqlx::query_file!("sql/delete/splits/by_transaction.sql", transaction_id)
                .execute(&mut *tx)
                .await?;

            // Insert new splits
            for entity in new_splits {
                if let FinanceEntity::Split(split) = entity {
                    sqlx::query_file!(
                        "sql/insert/splits/split.sql",
                        split.id,
                        split.tx_id,
                        split.account_id,
                        split.commodity_id,
                        split.reconcile_state,
                        split.reconcile_date,
                        split.value_num,
                        split.value_denom,
                        split.lot_id
                    )
                    .execute(&mut *tx)
                    .await?;
                }
            }
        }

        // Validate and process prices if provided
        if let Some(ref new_prices) = prices {
            for entity in new_prices {
                if let FinanceEntity::Price(_) = entity {
                    // Price validation could be added here
                } else {
                    return Err(CmdError::Args("Invalid entity type in prices".to_string()));
                }
            }
        }

        // Validate tags if provided
        if let Some(ref new_tags) = tags {
            for entity in new_tags.values() {
                if let FinanceEntity::Tag(_) = entity {
                    // Tag validation could be added here
                } else {
                    return Err(CmdError::Args("Invalid entity type in tags".to_string()));
                }
            }
        }

        // Process prices update atomically
        if let Some(new_prices) = prices {
            for entity in new_prices {
                if let FinanceEntity::Price(price) = entity {
                    sqlx::query_file!(
                        "sql/insert/prices/price.sql",
                        price.id,
                        price.commodity_id,
                        price.currency_id,
                        price.commodity_split,
                        price.currency_split,
                        price.date,
                        price.value_num,
                        price.value_denom
                    )
                    .execute(&mut *tx)
                    .await?;
                }
            }
        }

        // Process tags update atomically: delete then insert
        if let Some(new_tags) = tags {
            sqlx::query_file!("sql/delete/transaction_tags/by_transaction.sql", transaction_id)
                .execute(&mut *tx)
                .await?;

            for (_, entity) in new_tags {
                if let FinanceEntity::Tag(tag) = entity {
                    sqlx::query_file!(
                        "sql/insert/transaction_tags/transaction_tag.sql",
                        transaction_id,
                        tag.id
                    )
                    .execute(&mut *tx)
                    .await?;
                }
            }
        }

        // Handle note field by creating/updating note tag
        if let Some(note_value) = note {
            // First delete any existing note tag for this transaction
            sqlx::query!("DELETE FROM transaction_tags WHERE tx_id = $1 AND tag_id IN (SELECT id FROM tags WHERE tag_name = 'note')", transaction_id)
                .execute(&mut *tx)
                .await?;

            if !note_value.trim().is_empty() {
                let note_tag_id = Tag {
                    id: Uuid::new_v4(),
                    tag_name: "note".to_string(),
                    tag_value: note_value,
                    description: None,
                }
                .commit(&mut *tx)
                .await?;

                sqlx::query_file!(
                    "sql/insert/transaction_tags/transaction_tag.sql",
                    transaction_id,
                    note_tag_id
                )
                .execute(&mut *tx)
                .await?;
            }
        }

        tx.commit().await?;

        let updated_transaction = Transaction {
            id: transaction_id,
            post_date: final_post_date,
            enter_date: final_enter_date,
        };

        Ok(Some(CmdResult::Entity(FinanceEntity::Transaction(updated_transaction))))
    }
}

command! {
    DeleteTransaction {
        #[required]
        user_id: Uuid,
        #[required]
        transaction_id: Uuid,
    } => {
        let user = User { id: user_id };
        let mut conn = user.get_connection().await.map_err(|err| {
            log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
            ConfigError::DB
        })?;

        sqlx::query_file!("sql/select/transactions/by_id.sql", transaction_id)
            .fetch_optional(&mut *conn)
            .await?
            .ok_or_else(|| CmdError::Args("Transaction not found".to_string()))?;

        let mut tx = conn.begin().await?;

        let tag_ids_to_check: Vec<Uuid> = sqlx::query_file!(
            "sql/select/tags/by_transaction_and_splits.sql",
            transaction_id
        )
        .fetch_all(&mut *tx)
        .await?
        .into_iter()
        .filter_map(|row| row.tag_id)
        .collect();

        sqlx::query_file!("sql/delete/prices/by_splits.sql", transaction_id)
            .execute(&mut *tx)
            .await?;

        sqlx::query_file!("sql/delete/split_tags/by_transaction.sql", transaction_id)
            .execute(&mut *tx)
            .await?;

        sqlx::query_file!("sql/delete/transaction_tags/by_transaction.sql", transaction_id)
            .execute(&mut *tx)
            .await?;

        for tag_id in tag_ids_to_check {
            let is_orphaned = sqlx::query_file!("sql/check/tags/is_orphaned.sql", tag_id)
                .fetch_one(&mut *tx)
                .await?
                .is_orphaned
                .unwrap_or(false);

            if is_orphaned {
                sqlx::query_file!("sql/delete/tags/by_id.sql", tag_id)
                    .execute(&mut *tx)
                    .await?;
            }
        }

        sqlx::query_file!("sql/delete/splits/by_transaction.sql", transaction_id)
            .execute(&mut *tx)
            .await?;

        sqlx::query_file!("sql/delete/transactions/by_id.sql", transaction_id)
            .execute(&mut *tx)
            .await?;

        tx.commit().await?;

        Ok(Some(CmdResult::String("Transaction deleted successfully".to_string())))
    }
}

// Idempotent set: replace any existing (tx, tag_name) link with the
// supplied `tag_value`. Mirrors `SetSplitTag` and `SetAccountTag` so
// the script-side `set-transaction-tag` native reads symmetrically.
command! {
    SetTransactionTag {
        #[required]
        user_id: Uuid,
        #[required]
        transaction_id: Uuid,
        #[required]
        tag_name: String,
        #[required]
        tag_value: String,
        #[optional]
        description: String,
    } => {
        let user = User { id: user_id };
        let desc = description.and_then(|text| {
            if text.trim().is_empty() {
                None
            } else {
                Some(text)
            }
        });
        let tag = Tag {
            id: Uuid::new_v4(),
            tag_name,
            tag_value,
            description: desc,
        };
        user.set_transaction_tag(transaction_id, &tag)
            .await
            .map_err(|err| {
                log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
                CmdError::Args(format!("{err:?}"))
            })?;
        Ok(Some(CmdResult::String("ok".to_string())))
    }
}

// Looks up a single tag value by name on a transaction. Returns
// `CmdResult::String("")` when absent (matches `GetSplitTag`).
command! {
    GetTransactionTag {
        #[required]
        user_id: Uuid,
        #[required]
        transaction_id: Uuid,
        #[required]
        tag_name: String,
    } => {
        let user = User { id: user_id };
        let tags = user
            .get_transaction_tags(transaction_id)
            .await
            .map_err(|err| {
                log::error!("{}", t!("Database error: %{err}", err = err : {:?}));
                CmdError::Args(format!("{err:?}"))
            })?;
        let value = tags
            .into_iter()
            .find(|t| t.tag_name == tag_name)
            .map(|t| t.tag_value)
            .unwrap_or_default();
        Ok(Some(CmdResult::String(value)))
    }
}

#[cfg(test)]
mod command_tests {
    use super::*;
    use crate::{
        command::{account::CreateAccount, commodity::CreateCommodity},
        db::DB_POOL,
    };
    use chrono::Duration;
    use finance::{account::Account, price::Price, split::Split};
    use sqlx::PgPool;
    use supp_macro::local_db_sqlx_test;
    use tokio::sync::OnceCell;

    /// Context for keeping environment intact
    static CONTEXT: OnceCell<()> = OnceCell::const_new();
    static USER: OnceCell<User> = OnceCell::const_new();

    async fn setup() {
        CONTEXT
            .get_or_init(|| async {
                #[cfg(feature = "testlog")]
                let _ = env_logger::builder()
                    .is_test(true)
                    .filter_level(log::LevelFilter::Trace)
                    .try_init();
            })
            .await;
        USER.get_or_init(|| async { User { id: Uuid::new_v4() } })
            .await;
    }

    #[local_db_sqlx_test]
    async fn test_create_transaction(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // First create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        // Get the commodity ID
        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        // Create two accounts
        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();

        // Create splits
        let split1 = Split::builder()
            .id(Uuid::new_v4())
            .tx_id(tx_id)
            .account_id(account1.id)
            .commodity_id(commodity_id)
            .value_num(100)
            .value_denom(1)
            .build()?;

        let split2 = Split::builder()
            .id(Uuid::new_v4())
            .tx_id(tx_id)
            .account_id(account2.id)
            .commodity_id(commodity_id)
            .value_num(-100)
            .value_denom(1)
            .build()?;

        // Create transaction with splits
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(tx))) = CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .run()
            .await?
        {
            assert!(!tx.id.is_nil());

            // Verify splits were created
            let mut conn = user.get_connection().await?;
            let splits = sqlx::query_file!("sql/count/splits/by_transaction.sql", tx.id)
                .fetch_one(&mut *conn)
                .await?;
            assert_eq!(splits.count, Some(2));
        } else {
            panic!("Expected transaction entity result");
        }
    }
    #[local_db_sqlx_test]
    async fn test_list_transactions_empty(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new().user_id(user.id).run().await?
        {
            assert!(
                entities.is_empty(),
                "Expected no transactions in empty database"
            );
            assert_eq!(pagination.total_count, 0);
            assert_eq!(pagination.limit, 20);
            assert_eq!(pagination.offset, 0);
            assert!(!pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }
    }

    #[local_db_sqlx_test]
    async fn test_list_transactions_with_data(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // First create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        // Get the commodity ID
        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        // Create two accounts
        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Create a transaction between the accounts
        let tx_id = Uuid::new_v4();
        let now = Utc::now();

        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .run()
            .await?;

        // List all transactions
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new().user_id(user.id).run().await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction");
            assert_eq!(pagination.total_count, 1);

            let (entity, _tags) = &entities[0];
            if let FinanceEntity::Transaction(tx) = entity {
                assert_eq!(tx.id, tx_id);
            } else {
                panic!("Expected Transaction entity");
            }
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // List transactions filtered by account
        if let Some(CmdResult::TaggedEntities { entities, .. }) = ListTransactions::new()
            .user_id(user.id)
            .account(account1.id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction for account1");
        } else {
            panic!("Expected TaggedEntities result");
        }

        // List transactions for non-existent account
        if let Some(CmdResult::TaggedEntities { entities, .. }) = ListTransactions::new()
            .user_id(user.id)
            .account(Uuid::new_v4())
            .run()
            .await?
        {
            assert_eq!(
                entities.len(),
                0,
                "Expected no transactions for non-existent account"
            );
        } else {
            panic!("Expected TaggedEntities result");
        }
    }

    #[local_db_sqlx_test]
    async fn test_get_transaction(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // First create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        // Create two accounts
        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Create a transaction
        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("Test transaction".to_string())
            .run()
            .await?;

        // Test GetTransaction
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction");
            let (entity, _tags) = &entities[0];
            if let FinanceEntity::Transaction(tx) = entity {
                assert_eq!(tx.id, tx_id);
            } else {
                panic!("Expected Transaction entity");
            }
        } else {
            panic!("Expected TaggedEntities result");
        }

        // Test GetTransaction with non-existent ID
        let result = GetTransaction::new()
            .user_id(user.id)
            .transaction_id(Uuid::new_v4())
            .run()
            .await?;
        assert!(
            result.is_none(),
            "Expected None for non-existent transaction"
        );
    }

    #[local_db_sqlx_test]
    async fn test_update_transaction(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // First create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        // Create two accounts
        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Create a transaction
        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("Original note".to_string())
            .run()
            .await?;

        // Test UpdateTransaction with only note change
        let new_note = "Updated note".to_string();
        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =
            UpdateTransaction::new()
                .user_id(user.id)
                .transaction_id(tx_id)
                .note(new_note.clone())
                .run()
                .await?
        {
            assert_eq!(updated_tx.id, tx_id);
        } else {
            panic!("Expected Transaction entity result");
        }

        // Test UpdateTransaction with new splits
        let new_split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -200,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let new_split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 200,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let new_splits = vec![
            FinanceEntity::Split(new_split1),
            FinanceEntity::Split(new_split2),
        ];

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =
            UpdateTransaction::new()
                .user_id(user.id)
                .transaction_id(tx_id)
                .splits(new_splits)
                .run()
                .await?
        {
            assert_eq!(updated_tx.id, tx_id);
        } else {
            panic!("Expected Transaction entity result");
        }

        // Test UpdateTransaction atomicity: unbalanced splits should fail
        let unbalanced_split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100, // This doesn't balance with split2
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let unbalanced_split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 50, // Should be 100 to balance
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let unbalanced_splits = vec![
            FinanceEntity::Split(unbalanced_split1),
            FinanceEntity::Split(unbalanced_split2),
        ];

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .splits(unbalanced_splits)
            .run()
            .await;
        assert!(result.is_err(), "Expected error for unbalanced splits");

        // Verify original transaction is unchanged after failed update
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction");
            // Transaction should still exist and be unchanged
        } else {
            panic!("Expected transaction to still exist after failed update");
        }

        // Test UpdateTransaction with non-existent transaction
        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(Uuid::new_v4())
            .note("Should fail".to_string())
            .run()
            .await;
        assert!(
            result.is_err(),
            "Expected error for non-existent transaction"
        );
    }

    #[local_db_sqlx_test]
    async fn test_update_transaction_atomicity(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // First create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        // Create two accounts
        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Create a transaction
        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("Original transaction".to_string())
            .run()
            .await?;

        // Test 1: Split transaction ID mismatch validation
        let wrong_tx_id = Uuid::new_v4();
        let invalid_split = Split {
            id: Uuid::new_v4(),
            tx_id: wrong_tx_id, // Wrong transaction ID
            account_id: account1.id,
            commodity_id,
            value_num: -50,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let valid_split = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 50,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let mismatched_splits = vec![
            FinanceEntity::Split(invalid_split),
            FinanceEntity::Split(valid_split),
        ];

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .splits(mismatched_splits)
            .run()
            .await;

        assert!(
            result.is_err(),
            "Expected error for split transaction ID mismatch"
        );
        if let Err(CmdError::Args(msg)) = result {
            assert!(msg.contains("Split transaction ID mismatch"));
        } else {
            panic!("Expected CmdError::Args with transaction ID mismatch message");
        }

        // Verify original transaction is unchanged
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction");
        } else {
            panic!("Expected transaction to still exist after failed update");
        }

        // Test 2: Invalid entity type in splits
        let invalid_splits = vec![
            FinanceEntity::Account(Account {
                id: account1.id,
                parent: account1.parent,
            }), // Wrong entity type
        ];

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .splits(invalid_splits)
            .run()
            .await;

        assert!(
            result.is_err(),
            "Expected error for invalid entity type in splits"
        );
        if let Err(CmdError::Args(msg)) = result {
            assert!(msg.contains("Invalid entity type in splits"));
        } else {
            panic!("Expected CmdError::Args with invalid entity type message");
        }

        // Test 3: Invalid entity type in prices
        let invalid_prices = vec![
            FinanceEntity::Account(Account {
                id: account1.id,
                parent: account1.parent,
            }), // Wrong entity type
        ];

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .prices(invalid_prices)
            .run()
            .await;

        assert!(
            result.is_err(),
            "Expected error for invalid entity type in prices"
        );
        if let Err(CmdError::Args(msg)) = result {
            assert!(msg.contains("Invalid entity type in prices"));
        } else {
            panic!("Expected CmdError::Args with invalid entity type message");
        }

        // Test 4: Invalid entity type in tags
        let mut invalid_tags = HashMap::new();
        invalid_tags.insert(
            "test".to_string(),
            FinanceEntity::Account(Account {
                id: account1.id,
                parent: account1.parent,
            }),
        );

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .tags(invalid_tags)
            .run()
            .await;

        assert!(
            result.is_err(),
            "Expected error for invalid entity type in tags"
        );
        if let Err(CmdError::Args(msg)) = result {
            assert!(msg.contains("Invalid entity type in tags"));
        } else {
            panic!("Expected CmdError::Args with invalid entity type message");
        }

        // Test 5: Database rollback verification - count splits before and after failed update
        let mut conn = user.get_connection().await?;
        let initial_split_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM splits WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);

        // Try an update that will fail during split insertion (invalid account ID)
        let invalid_account_split = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: Uuid::new_v4(), // Non-existent account
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let balancing_split = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let failing_splits = vec![
            FinanceEntity::Split(invalid_account_split),
            FinanceEntity::Split(balancing_split),
        ];

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .splits(failing_splits)
            .run()
            .await;

        assert!(result.is_err(), "Expected error for non-existent account");

        // Verify splits count is unchanged (rollback occurred)
        let final_split_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM splits WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);

        assert_eq!(
            initial_split_count, final_split_count,
            "Split count should be unchanged after failed update due to rollback"
        );

        // Verify original transaction is still intact
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction");
        } else {
            panic!("Expected transaction to still exist after failed database operation");
        }
    }

    #[local_db_sqlx_test]
    async fn test_update_transaction_prices_and_tags(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Create a simple commodity for testing
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        // Create accounts
        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Create tags for testing manually
        let tag1_id = Uuid::new_v4();
        let tag2_id = Uuid::new_v4();
        let mut conn = user.get_connection().await?;

        sqlx::query!(
            "INSERT INTO tags (id, tag_name, tag_value, description) VALUES ($1, $2, $3, $4)",
            tag1_id,
            "category",
            "expense",
            Some("Expense category".to_string())
        )
        .execute(&mut *conn)
        .await?;

        sqlx::query!(
            "INSERT INTO tags (id, tag_name, tag_value, description) VALUES ($1, $2, $3, $4)",
            tag2_id,
            "project",
            "finance_app",
            Some("Finance app project".to_string())
        )
        .execute(&mut *conn)
        .await?;

        // Create initial transaction with simple same-commodity splits
        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("Initial transaction".to_string())
            .run()
            .await?;

        // Test 1: Update transaction with standalone prices (not linked to splits)
        let price1 = Price {
            id: Uuid::new_v4(),
            commodity_id,
            currency_id: commodity_id, // Same commodity for simplicity
            commodity_split: None,     // Not linked to specific splits
            currency_split: None,
            date: now,
            value_num: 100,
            value_denom: 100,
        };

        let prices = vec![FinanceEntity::Price(price1)];

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =
            UpdateTransaction::new()
                .user_id(user.id)
                .transaction_id(tx_id)
                .prices(prices)
                .run()
                .await?
        {
            assert_eq!(updated_tx.id, tx_id);
        } else {
            panic!("Expected Transaction entity result for price update");
        }

        // Verify prices were inserted
        let price_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM prices WHERE commodity_id = $1 AND currency_id = $2",
            commodity_id,
            commodity_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(price_count, 1, "Expected one price record");

        // Test 2: Update transaction with tags
        let mut tags = HashMap::new();
        tags.insert(
            "category".to_string(),
            FinanceEntity::Tag(Tag {
                id: tag1_id,
                tag_name: "category".to_string(),
                tag_value: "expense".to_string(),
                description: Some("Expense category".to_string()),
            }),
        );
        tags.insert(
            "project".to_string(),
            FinanceEntity::Tag(Tag {
                id: tag2_id,
                tag_name: "project".to_string(),
                tag_value: "finance_app".to_string(),
                description: Some("Finance app project".to_string()),
            }),
        );

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =
            UpdateTransaction::new()
                .user_id(user.id)
                .transaction_id(tx_id)
                .tags(tags)
                .run()
                .await?
        {
            assert_eq!(updated_tx.id, tx_id);
        } else {
            panic!("Expected Transaction entity result for tag update");
        }

        // Verify tags were inserted
        let tag_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM transaction_tags WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(tag_count, 2, "Expected two tag records");

        // Test 3: Combined update (new splits, new prices, and new tags)
        let new_split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -200,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let new_split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 200,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let new_splits = vec![
            FinanceEntity::Split(new_split1),
            FinanceEntity::Split(new_split2),
        ];

        let new_price = Price {
            id: Uuid::new_v4(),
            commodity_id,
            currency_id: commodity_id,
            commodity_split: None,
            currency_split: None,
            date: now,
            value_num: 110,
            value_denom: 100,
        };
        let new_prices = vec![FinanceEntity::Price(new_price)];

        let mut new_tags = HashMap::new();
        new_tags.insert(
            "category".to_string(),
            FinanceEntity::Tag(Tag {
                id: tag1_id,
                tag_name: "category".to_string(),
                tag_value: "income".to_string(), // Changed value
                description: Some("Expense category".to_string()),
            }),
        );

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =
            UpdateTransaction::new()
                .user_id(user.id)
                .transaction_id(tx_id)
                .splits(new_splits)
                .prices(new_prices)
                .tags(new_tags)
                .run()
                .await?
        {
            assert_eq!(updated_tx.id, tx_id);
        } else {
            panic!("Expected Transaction entity result for combined update");
        }

        // Verify all updates were applied atomically
        let final_split_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM splits WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(final_split_count, 2, "Expected two splits after update");

        let final_price_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM prices WHERE commodity_id = $1",
            commodity_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(final_price_count, 2, "Expected two prices after update");

        let final_tag_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM transaction_tags WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(final_tag_count, 1, "Expected one tag after update");

        // Test 4: Tag validation failure with non-existent tag
        let mut invalid_tags = HashMap::new();
        invalid_tags.insert(
            "invalid".to_string(),
            FinanceEntity::Tag(Tag {
                id: Uuid::new_v4(), // Non-existent tag
                tag_name: "invalid".to_string(),
                tag_value: "value".to_string(),
                description: Some("Invalid tag".to_string()),
            }),
        );

        let result = UpdateTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .tags(invalid_tags)
            .run()
            .await;

        assert!(
            result.is_err(),
            "Expected error for invalid tag with non-existent tag ID"
        );

        // Verify original tags are unchanged after failed tag update
        let unchanged_tag_count = sqlx::query!(
            "SELECT COUNT(*) as count FROM transaction_tags WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(
            unchanged_tag_count, 1,
            "Tag count should be unchanged after failed update"
        );
    }

    #[local_db_sqlx_test]
    async fn test_delete_transaction_simple(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .run()
            .await?;

        let result = DeleteTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?;

        assert!(result.is_some(), "Expected successful deletion");

        let mut conn = user.get_connection().await?;
        let tx_exists = sqlx::query!(
            "SELECT COUNT(*) as count FROM transactions WHERE id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(tx_exists, 0, "Transaction should be deleted");

        let splits_exist = sqlx::query!(
            "SELECT COUNT(*) as count FROM splits WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(splits_exist, 0, "Splits should be deleted");
    }

    #[local_db_sqlx_test]
    async fn test_delete_transaction_with_tags_and_prices(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();
        let split1_id = Uuid::new_v4();
        let split2_id = Uuid::new_v4();
        let split1 = Split {
            id: split1_id,
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: split2_id,
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        let price = Price {
            id: Uuid::new_v4(),
            commodity_id,
            currency_id: commodity_id,
            commodity_split: Some(split1_id),
            currency_split: Some(split2_id),
            date: now,
            value_num: 100,
            value_denom: 100,
        };

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .prices(vec![FinanceEntity::Price(price)])
            .note("Test note".to_string())
            .run()
            .await?;

        let mut conn = user.get_connection().await?;

        let tag_count_before = sqlx::query!("SELECT COUNT(*) as count FROM tags")
            .fetch_one(&mut *conn)
            .await?
            .count
            .unwrap_or(0);
        assert!(tag_count_before > 0, "Should have tags before deletion");

        let price_count_before = sqlx::query!(
            "SELECT COUNT(*) as count FROM prices WHERE commodity_split_id = $1 OR currency_split_id = $2",
            split1_id,
            split2_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(
            price_count_before, 1,
            "Should have one price before deletion"
        );

        let result = DeleteTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?;

        assert!(result.is_some(), "Expected successful deletion");

        let tx_exists = sqlx::query!(
            "SELECT COUNT(*) as count FROM transactions WHERE id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(tx_exists, 0, "Transaction should be deleted");

        let splits_exist = sqlx::query!(
            "SELECT COUNT(*) as count FROM splits WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(splits_exist, 0, "Splits should be deleted");

        let tx_tags_exist = sqlx::query!(
            "SELECT COUNT(*) as count FROM transaction_tags WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(
            tx_tags_exist, 0,
            "Transaction tags associations should be deleted"
        );

        let prices_exist = sqlx::query!(
            "SELECT COUNT(*) as count FROM prices WHERE commodity_split_id = $1 OR currency_split_id = $2",
            split1_id,
            split2_id
        )
        .fetch_one(&mut *conn)
        .await?
        .count
        .unwrap_or(0);
        assert_eq!(prices_exist, 0, "Prices should be deleted");
    }

    #[local_db_sqlx_test]
    async fn test_delete_transaction_nonexistent(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let nonexistent_id = Uuid::new_v4();
        let result = DeleteTransaction::new()
            .user_id(user.id)
            .transaction_id(nonexistent_id)
            .run()
            .await;

        assert!(
            result.is_err(),
            "Expected error for non-existent transaction"
        );
        if let Err(CmdError::Args(msg)) = result {
            assert!(msg.contains("Transaction not found"));
        } else {
            panic!("Expected CmdError::Args with 'Transaction not found' message");
        }
    }

    #[local_db_sqlx_test]
    async fn test_delete_transaction_orphaned_tags(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("Orphaned tag test".to_string())
            .run()
            .await?;

        let mut conn = user.get_connection().await?;

        let tag_id = sqlx::query!(
            "SELECT tag_id FROM transaction_tags WHERE tx_id = $1",
            tx_id
        )
        .fetch_one(&mut *conn)
        .await?
        .tag_id;

        DeleteTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?;

        let orphaned_tag_exists =
            sqlx::query!("SELECT COUNT(*) as count FROM tags WHERE id = $1", tag_id)
                .fetch_one(&mut *conn)
                .await?
                .count
                .unwrap_or(0);
        assert_eq!(orphaned_tag_exists, 0, "Orphaned tag should be deleted");
    }

    const GROCERIES_SCRIPT_WASM: &[u8] =
        include_bytes!("../../../web/static/wasm/groceries_markup.wasm");

    const TAG_SYNC_SCRIPT_WASM: &[u8] = include_bytes!("../../../web/static/wasm/tag_sync.wasm");

    #[local_db_sqlx_test]
    async fn test_create_transaction_with_all_scripts_completes(
        pool: PgPool,
    ) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let mut conn = user.get_connection().await?;

        let groceries_script_id = user
            .create_script(GROCERIES_SCRIPT_WASM.to_vec(), None)
            .await?;
        let tag_sync_script_id = user
            .create_script(TAG_SYNC_SCRIPT_WASM.to_vec(), None)
            .await?;

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();
        let split1_id = Uuid::new_v4();
        let split2_id = Uuid::new_v4();

        let split1 = Split {
            id: split1_id,
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -5000,
            value_denom: 100,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let split2 = Split {
            id: split2_id,
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 5000,
            value_denom: 100,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        let result = tokio::time::timeout(
            std::time::Duration::from_secs(10),
            CreateTransaction::new()
                .user_id(user.id)
                .splits(splits)
                .id(tx_id)
                .post_date(now)
                .enter_date(now)
                .note("groceries".to_string())
                .run(),
        )
        .await;

        assert!(
            result.is_ok(),
            "Transaction creation with scripts must complete within 10 seconds"
        );
        result.unwrap()?;

        let split1_tags = sqlx::query_file!("sql/select/tags/by_split.sql", split1_id)
            .fetch_all(&mut *conn)
            .await?;

        let split1_has_category = split1_tags
            .iter()
            .any(|t| t.tag_name == "category" && t.tag_value == "groceries");
        assert!(
            split1_has_category,
            "Split 1 should have category=groceries tag from groceries script"
        );

        user.delete_script(groceries_script_id).await?;
        user.delete_script(tag_sync_script_id).await?;
    }

    #[local_db_sqlx_test]
    async fn test_create_transaction_with_script(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let mut conn = user.get_connection().await?;

        // Insert the groceries script into the database
        let script_id = user
            .create_script(GROCERIES_SCRIPT_WASM.to_vec(), None)
            .await?;

        // Create commodity and accounts
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();
        let split1_id = Uuid::new_v4();
        let split2_id = Uuid::new_v4();

        let split1 = Split {
            id: split1_id,
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -5000,
            value_denom: 100,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let split2 = Split {
            id: split2_id,
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 5000,
            value_denom: 100,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        // Create transaction with note="groceries" - this should trigger the script
        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("groceries".to_string())
            .run()
            .await?;

        // Verify that the script added "category=groceries" tags to both splits
        let split1_tags = sqlx::query_file!("sql/select/tags/by_split.sql", split1_id)
            .fetch_all(&mut *conn)
            .await?;

        let split2_tags = sqlx::query_file!("sql/select/tags/by_split.sql", split2_id)
            .fetch_all(&mut *conn)
            .await?;

        // Check split1 has the category tag
        let split1_has_category = split1_tags
            .iter()
            .any(|t| t.tag_name == "category" && t.tag_value == "groceries");
        assert!(
            split1_has_category,
            "Split 1 should have category=groceries tag from script. Tags: {:?}",
            split1_tags
                .iter()
                .map(|t| format!("{}={}", t.tag_name, t.tag_value))
                .collect::<Vec<_>>()
        );

        // Check split2 has the category tag
        let split2_has_category = split2_tags
            .iter()
            .any(|t| t.tag_name == "category" && t.tag_value == "groceries");
        assert!(
            split2_has_category,
            "Split 2 should have category=groceries tag from script. Tags: {:?}",
            split2_tags
                .iter()
                .map(|t| format!("{}={}", t.tag_name, t.tag_value))
                .collect::<Vec<_>>()
        );

        // Clean up the script
        user.delete_script(script_id).await?;
    }

    #[local_db_sqlx_test]
    async fn test_create_transaction_script_skips_non_matching(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let mut conn = user.get_connection().await?;

        // Insert the groceries script into the database
        let script_id = user
            .create_script(GROCERIES_SCRIPT_WASM.to_vec(), None)
            .await?;

        // Create commodity and accounts
        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let tx_id = Uuid::new_v4();
        let split1_id = Uuid::new_v4();
        let split2_id = Uuid::new_v4();

        let split1 = Split {
            id: split1_id,
            tx_id,
            account_id: account1.id,
            commodity_id,
            value_num: -5000,
            value_denom: 100,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let split2 = Split {
            id: split2_id,
            tx_id,
            account_id: account2.id,
            commodity_id,
            value_num: 5000,
            value_denom: 100,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];
        let now = Utc::now();

        // Create transaction with note="other" - this should NOT trigger the script
        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(now)
            .enter_date(now)
            .note("other".to_string())
            .run()
            .await?;

        // Verify that the script did NOT add any tags to splits
        let split1_tags = sqlx::query_file!("sql/select/tags/by_split.sql", split1_id)
            .fetch_all(&mut *conn)
            .await?;

        let split2_tags = sqlx::query_file!("sql/select/tags/by_split.sql", split2_id)
            .fetch_all(&mut *conn)
            .await?;

        // Neither split should have the category tag
        let split1_has_category = split1_tags
            .iter()
            .any(|t| t.tag_name == "category" && t.tag_value == "groceries");
        assert!(
            !split1_has_category,
            "Split 1 should NOT have category tag for non-groceries transaction"
        );

        let split2_has_category = split2_tags
            .iter()
            .any(|t| t.tag_name == "category" && t.tag_value == "groceries");
        assert!(
            !split2_has_category,
            "Split 2 should NOT have category tag for non-groceries transaction"
        );

        // Clean up the script
        user.delete_script(script_id).await?;
    }

    async fn create_test_transaction(
        user: &User,
        account1_id: Uuid,
        account2_id: Uuid,
        commodity_id: Uuid,
        post_date: DateTime<Utc>,
        amount: i64,
    ) -> anyhow::Result<Uuid> {
        let tx_id = Uuid::new_v4();
        let split1 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account1_id,
            commodity_id,
            value_num: -amount,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id,
            account_id: account2_id,
            commodity_id,
            value_num: amount,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };
        let splits = vec![FinanceEntity::Split(split1), FinanceEntity::Split(split2)];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits)
            .id(tx_id)
            .post_date(post_date)
            .enter_date(Utc::now())
            .run()
            .await?;

        Ok(tx_id)
    }

    #[local_db_sqlx_test]
    async fn test_pagination_limit(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let base_time = Utc::now();
        for i in 0..25 {
            let post_date = base_time - Duration::days(i);
            create_test_transaction(
                user,
                account1.id,
                account2.id,
                commodity_id,
                post_date,
                100 + i,
            )
            .await?;
        }

        // Test limit=10 returns exactly 10 transactions
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .limit(10)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 10, "Expected exactly 10 transactions");
            assert_eq!(pagination.total_count, 25);
            assert_eq!(pagination.limit, 10);
            assert_eq!(pagination.offset, 0);
            assert!(pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test limit=5 returns exactly 5 transactions
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .limit(5)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 5, "Expected exactly 5 transactions");
            assert_eq!(pagination.total_count, 25);
            assert!(pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test limit=100 returns all 25 transactions (limit > total)
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .limit(100)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 25, "Expected all 25 transactions");
            assert_eq!(pagination.total_count, 25);
            assert!(!pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }
    }

    #[local_db_sqlx_test]
    async fn test_pagination_offset(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let base_time = Utc::now();
        for i in 0..25 {
            let post_date = base_time - Duration::days(i);
            create_test_transaction(
                user,
                account1.id,
                account2.id,
                commodity_id,
                post_date,
                100 + i,
            )
            .await?;
        }

        // Test offset=10, limit=10 returns second page
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .limit(10)
            .offset(10)
            .run()
            .await?
        {
            assert_eq!(
                entities.len(),
                10,
                "Expected 10 transactions on second page"
            );
            assert_eq!(pagination.total_count, 25);
            assert_eq!(pagination.offset, 10);
            assert!(pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test offset=20, limit=10 returns last page (only 5 remaining)
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .limit(10)
            .offset(20)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 5, "Expected 5 transactions on last page");
            assert_eq!(pagination.total_count, 25);
            assert!(!pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test offset beyond total returns empty
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .limit(10)
            .offset(100)
            .run()
            .await?
        {
            assert!(entities.is_empty(), "Expected no transactions beyond total");
            assert_eq!(pagination.total_count, 25);
            assert!(!pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }
    }

    #[local_db_sqlx_test]
    async fn test_pagination_date_filter(pool: PgPool) -> anyhow::Result<()> {
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        let commodity_result = CreateCommodity::new()
            .symbol("TST".to_string())
            .name("Test Commodity".to_string())
            .user_id(user.id)
            .run()
            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 1".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Account 2".to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        let base_time = Utc::now();
        for i in 0..30 {
            let post_date = base_time - Duration::days(i);
            create_test_transaction(
                user,
                account1.id,
                account2.id,
                commodity_id,
                post_date,
                100 + i,
            )
            .await?;
        }

        // Test date_from filter (last 10 days)
        let date_from = base_time - Duration::days(9);
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .date_from(date_from)
            .run()
            .await?
        {
            assert_eq!(
                entities.len(),
                10,
                "Expected 10 transactions from last 10 days"
            );
            assert_eq!(pagination.total_count, 10);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test date_to filter (older than 20 days)
        let date_to = base_time - Duration::days(20);
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .date_to(date_to)
            .run()
            .await?
        {
            assert_eq!(
                entities.len(),
                10,
                "Expected 10 transactions older than 20 days"
            );
            assert_eq!(pagination.total_count, 10);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test date range filter (days 10-19)
        let date_from = base_time - Duration::days(19);
        let date_to = base_time - Duration::days(10);
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .date_from(date_from)
            .date_to(date_to)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 10, "Expected 10 transactions in date range");
            assert_eq!(pagination.total_count, 10);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }

        // Test date range with pagination
        let date_from = base_time - Duration::days(29);
        if let Some(CmdResult::TaggedEntities {
            entities,
            pagination: Some(pagination),
        }) = ListTransactions::new()
            .user_id(user.id)
            .date_from(date_from)
            .limit(5)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 5, "Expected 5 transactions with limit");
            assert_eq!(pagination.total_count, 30);
            assert!(pagination.has_more);
        } else {
            panic!("Expected TaggedEntities result with pagination");
        }
    }
}