server/command/

transaction.rs

use finance::{tag::Tag, transaction::Transaction};
use num_rational::Rational64;
use scripting::ScriptExecutor;
use sqlx::{
    Acquire,
    types::Uuid,
    types::chrono::{DateTime, Utc},
};
use std::{collections::HashMap, fmt::Debug};
use supp_macro::command;

pub(super) struct SplitAmountRow {
    pub(super) tx_id: Uuid,
    pub(super) commodity_id: Uuid,
    pub(super) value_num: i64,
    pub(super) value_denom: i64,
    pub(super) symbol: String,
}

/// Aggregate positive-side split values into per-commodity amounts per transaction.
///
/// Grouping is by commodity **identity**, not symbol — two distinct commodities
/// that happen to share a `symbol` tag stay separate. The symbol is carried only
/// for display. Returns a map from tx id to a formatted `"amt sym; amt sym"`
/// display string, ordered by symbol then commodity id for determinism.
pub(super) fn aggregate_split_amounts(rows: Vec<SplitAmountRow>) -> HashMap<Uuid, String> {
    let mut tx_map: HashMap<Uuid, HashMap<Uuid, (String, Rational64)>> = HashMap::new();
    for row in rows {
        if row.value_denom == 0 {
            continue;
        }
        let r = Rational64::new(row.value_num, row.value_denom);
        let entry = tx_map
            .entry(row.tx_id)
            .or_default()
            .entry(row.commodity_id)
            .or_insert_with(|| (row.symbol, Rational64::from(0)));
        entry.1 += r;
    }
    tx_map
        .into_iter()
        .map(|(tx_id, commodities)| {
            let mut pairs: Vec<(Uuid, String, Rational64)> = commodities
                .into_iter()
                .map(|(commodity_id, (symbol, amount))| (commodity_id, symbol, amount))
                .collect();
            pairs.sort_by(|a, b| a.1.cmp(&b.1).then(a.0.cmp(&b.0)));
            let formatted = pairs
                .into_iter()
                .map(|(_, symbol, amount)| format!("{} {}", format_split_amount(amount), symbol))
                .collect::<Vec<_>>()
                .join("; ");
            (tx_id, formatted)
        })
        .collect()
}

fn format_split_amount(r: Rational64) -> String {
    if *r.denom() == 1 {
        r.numer().to_string()
    } else {
        format!("{}/{}", r.numer(), r.denom())
    }
}

/// Aggregate per-transaction amount summaries for the given ids in one query.
async fn load_split_amounts(
    conn: &mut sqlx::PgConnection,
    tx_ids: &[Uuid],
) -> Result<HashMap<Uuid, String>, sqlx::Error> {
    if tx_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let rows = sqlx::query_file!("sql/select/splits/for_list.sql", tx_ids)
        .fetch_all(conn)
        .await?
        .into_iter()
        .map(|r| SplitAmountRow {
            tx_id: r.tx_id,
            commodity_id: r.commodity_id,
            value_num: r.value_num,
            value_denom: r.value_denom,
            symbol: r.symbol,
        })
        .collect();
    Ok(aggregate_split_amounts(rows))
}

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,
        };

        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::try_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)
        };

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

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

        // Validate prices before inserting: every linked split a price references
        // must belong to THIS transaction's own split set. Mirrors the B1 check on
        // UpdateTransaction — the FK alone would accept a foreign/stale split id.
        if !prices.is_empty() {
            let valid_split_ids: std::collections::HashSet<Uuid> =
                splits.iter().map(|split| split.id).collect();
            for price in &prices {
                for split_id in [price.commodity_split, price.currency_split]
                    .into_iter()
                    .flatten()
                {
                    if !valid_split_ids.contains(&split_id) {
                        return Err(CmdError::Args(
                            "Price references a split that is not part of this transaction"
                                .to_string(),
                        ));
                    }
                }
            }
            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 tx_ids: Vec<Uuid> = transactions.iter().map(|r| r.id).collect();
        let split_amounts = load_split_amounts(&mut conn, &tx_ids).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();

            let amount = split_amounts.get(&transaction.id).cloned();
            tagged_transactions.push((FinanceEntity::Transaction(transaction), tags, amount));
        }

        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::TaggedTransactions {
            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();

            let amount = load_split_amounts(&mut conn, &[transaction.id])
                .await?
                .remove(&transaction.id);

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

command! {
    GetTransactionDetail {
        #[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();

            let split_entities: Vec<(FinanceEntity, HashMap<String, FinanceEntity>)> =
                sqlx::query_file!("sql/select/splits/by_transaction.sql", transaction_id)
                    .fetch_all(&mut *conn)
                    .await?
                    .into_iter()
                    .map(|row| {
                        (
                            FinanceEntity::Split(finance::split::Split {
                                id: row.id,
                                tx_id: row.tx_id,
                                account_id: row.account_id,
                                commodity_id: row.commodity_id,
                                value_num: row.value_num,
                                value_denom: row.value_denom,
                                reconcile_state: row.reconcile_state,
                                reconcile_date: row.reconcile_date,
                                lot_id: row.lot_id,
                            }),
                            HashMap::new(),
                        )
                    })
                    .collect();

            let price_entities: Vec<(FinanceEntity, HashMap<String, FinanceEntity>)> =
                sqlx::query_file!("sql/select/prices/by_transaction.sql", transaction_id)
                    .fetch_all(&mut *conn)
                    .await?
                    .into_iter()
                    .map(|row| {
                        (
                            FinanceEntity::Price(finance::price::Price {
                                id: row.id,
                                date: row.price_date,
                                commodity_id: row.commodity_id,
                                currency_id: row.currency_id,
                                commodity_split: row.commodity_split_id,
                                currency_split: row.currency_split_id,
                                value_num: row.value_num,
                                value_denom: row.value_denom,
                            }),
                            HashMap::new(),
                        )
                    })
                    .collect();

            let mut entities: Vec<(FinanceEntity, HashMap<String, FinanceEntity>)> =
                vec![(FinanceEntity::Transaction(transaction), tags)];
            entities.extend(split_entities);
            entities.extend(price_entities);

            Ok(Some(CmdResult::TaggedEntities {
                entities,
                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);

        // The split ids that will belong to this transaction after the update.
        // Captured here so prices can be checked against the replacement set
        // before splits is consumed by the delete/insert below.
        let mut new_split_ids: Option<std::collections::HashSet<Uuid>> = None;

        // 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();
            let mut ids = std::collections::HashSet::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()));
                    }
                    ids.insert(split.id);

                    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()));
                    }
                }
            }

            new_split_ids = Some(ids);
        }

        // 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 prices if provided: every price must link two splits that are
        // part of this transaction's (post-update) split set. Without this a crafted
        // price could reference a foreign/stale split id that the FK alone accepts.
        if let Some(ref new_prices) = prices {
            let valid_split_ids: std::collections::HashSet<Uuid> = match &new_split_ids {
                Some(ids) => ids.clone(),
                None => sqlx::query_file!("sql/select/splits/by_transaction.sql", transaction_id)
                    .fetch_all(&mut *tx)
                    .await?
                    .into_iter()
                    .map(|row| row.id)
                    .collect(),
            };
            for entity in new_prices {
                let FinanceEntity::Price(price) = entity else {
                    return Err(CmdError::Args("Invalid entity type in prices".to_string()));
                };
                // A linked split ref (Some) must belong to this transaction; an
                // unlinked standalone price (None) carries no split to validate.
                for split_id in [price.commodity_split, price.currency_split].into_iter().flatten() {
                    if !valid_split_ids.contains(&split_id) {
                        return Err(CmdError::Args(
                            "Price references a split that is not part of this transaction"
                                .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::TaggedTransactions {
            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 TaggedTransactions 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::TaggedTransactions {
            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, _amount) = &entities[0];
            if let FinanceEntity::Transaction(tx) = entity {
                assert_eq!(tx.id, tx_id);
            } else {
                panic!("Expected Transaction entity");
            }
        } else {
            panic!("Expected TaggedTransactions result with pagination");
        }

        // List transactions filtered by account
        if let Some(CmdResult::TaggedTransactions { 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 TaggedTransactions result");
        }

        // List transactions for non-existent account
        if let Some(CmdResult::TaggedTransactions { 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 TaggedTransactions 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::TaggedTransactions { entities, .. }) = GetTransaction::new()
            .user_id(user.id)
            .transaction_id(tx_id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one transaction");
            let (entity, _tags, _amount) = &entities[0];
            if let FinanceEntity::Transaction(tx) = entity {
                assert_eq!(tx.id, tx_id);
            } else {
                panic!("Expected Transaction entity");
            }
        } else {
            panic!("Expected TaggedTransactions 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::TaggedTransactions { 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::TaggedTransactions { 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::TaggedTransactions { 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();

        // A liveness guard, not a performance budget: it exists so a script that
        // deadlocks fails the suite instead of hanging it. The bound has to hold
        // on the slowest machine that runs this, which is a 4-vCPU CI node under
        // -Cinstrument-coverage executing two wasm scripts — 10s failed there
        // while passing everywhere else, which is a flaky test, not a finding.
        let result = tokio::time::timeout(
            std::time::Duration::from_secs(120),
            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 hung (>120s) — a script or lock is stuck"
        );
        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::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }

        // Test limit=5 returns exactly 5 transactions
        if let Some(CmdResult::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }

        // Test limit=100 returns all 25 transactions (limit > total)
        if let Some(CmdResult::TaggedTransactions {
            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 TaggedTransactions 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::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }

        // Test offset=20, limit=10 returns last page (only 5 remaining)
        if let Some(CmdResult::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }

        // Test offset beyond total returns empty
        if let Some(CmdResult::TaggedTransactions {
            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 TaggedTransactions 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::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }

        // Test date_to filter (older than 20 days)
        let date_to = base_time - Duration::days(20);
        if let Some(CmdResult::TaggedTransactions {
            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 TaggedTransactions 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::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }

        // Test date range with pagination
        let date_from = base_time - Duration::days(29);
        if let Some(CmdResult::TaggedTransactions {
            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 TaggedTransactions result with pagination");
        }
    }

    #[local_db_sqlx_test]
    async fn test_create_transaction_rejects_foreign_split_price(
        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 now = Utc::now();
        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,
        };

        // Price whose currency_split points at a split id NOT in this transaction.
        let foreign_split_id = Uuid::new_v4();
        let price = Price {
            id: Uuid::new_v4(),
            commodity_id,
            currency_id: commodity_id,
            commodity_split: Some(split1_id),
            currency_split: Some(foreign_split_id),
            date: now,
            value_num: 100,
            value_denom: 100,
        };

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

        assert!(
            result.is_err(),
            "Expected error for price referencing a foreign split"
        );

        let mut conn = user.get_connection().await?;
        let tx_count = 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_count, 0,
            "No transaction must persist on validation error"
        );

        let 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!(split_count, 0, "No splits must persist on validation error");
    }
}

#[cfg(test)]
mod aggregate_tests {
    use super::{SplitAmountRow, aggregate_split_amounts};
    use sqlx::types::Uuid;

    /// Deterministic commodity id per symbol so same-symbol rows aggregate.
    fn commodity_for(symbol: &str) -> Uuid {
        let n = symbol.bytes().fold(0u128, |acc, b| {
            acc.wrapping_mul(31).wrapping_add(u128::from(b))
        });
        Uuid::from_u128(n | 1)
    }

    fn row(tx_id: Uuid, num: i64, denom: i64, symbol: &str) -> SplitAmountRow {
        row_cid(tx_id, commodity_for(symbol), num, denom, symbol)
    }

    fn row_cid(
        tx_id: Uuid,
        commodity_id: Uuid,
        num: i64,
        denom: i64,
        symbol: &str,
    ) -> SplitAmountRow {
        SplitAmountRow {
            tx_id,
            commodity_id,
            value_num: num,
            value_denom: denom,
            symbol: symbol.to_string(),
        }
    }

    #[test]
    fn same_symbol_distinct_commodities_stay_separate() {
        let tx = Uuid::new_v4();
        let result = aggregate_split_amounts(vec![
            row_cid(tx, Uuid::from_u128(1), 100, 1, "USD"),
            row_cid(tx, Uuid::from_u128(2), 50, 1, "USD"),
        ]);
        assert_eq!(result.get(&tx).map(String::as_str), Some("100 USD; 50 USD"));
    }

    #[test]
    fn empty_input_returns_empty_map() {
        assert!(aggregate_split_amounts(vec![]).is_empty());
    }

    #[test]
    fn single_commodity_sums_correctly() {
        let id = Uuid::new_v4();
        let result = aggregate_split_amounts(vec![row(id, 50, 1, "USD"), row(id, 50, 1, "USD")]);
        assert_eq!(result.get(&id).map(String::as_str), Some("100 USD"));
    }

    #[test]
    fn multi_commodity_formats_sorted() {
        let id = Uuid::new_v4();
        let result = aggregate_split_amounts(vec![row(id, 100, 1, "USD"), row(id, 50, 1, "EUR")]);
        assert_eq!(result.get(&id).map(String::as_str), Some("50 EUR; 100 USD"));
    }

    #[test]
    fn fractional_amount_formatted() {
        let id = Uuid::new_v4();
        let result = aggregate_split_amounts(vec![row(id, 1, 3, "BTC")]);
        assert_eq!(result.get(&id).map(String::as_str), Some("1/3 BTC"));
    }

    #[test]
    fn multiple_transactions_independent() {
        let id1 = Uuid::new_v4();
        let id2 = Uuid::new_v4();
        let result =
            aggregate_split_amounts(vec![row(id1, 100, 1, "USD"), row(id2, 200, 1, "EUR")]);
        assert_eq!(result.get(&id1).map(String::as_str), Some("100 USD"));
        assert_eq!(result.get(&id2).map(String::as_str), Some("200 EUR"));
    }
}