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.

    }

    } else {

    }

/// Aggregate per-transaction amount summaries for the given ids in one query.

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

                .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 {

                .await

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

                    .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 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()

                    .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 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()

                    .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()

                    .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()

                    .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 tx = conn.begin().await?;

        let existing = sqlx::query_file!("sql/select/transactions/by_id.sql", transaction_id)

            .fetch_optional(&mut *tx)

            .await?

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

        sqlx::query_file!("sql/select/transactions/by_id.sql", transaction_id)

            .fetch_optional(&mut *conn)

            .await?

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

            } else {

            }

        let tag = Tag {

            id: Uuid::new_v4(),

            tag_name,

            tag_value,

            description: desc,

        };

        user.set_transaction_tag(transaction_id, &tag)

            .await

        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

        let value = tags

            .into_iter()

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

                #[cfg(feature = "testlog")]

    #[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))) =