use cfg_if::cfg_if;

#[cfg(not(feature = "scripting"))]

use finance::{price::Price, split::Split};

use finance::{tag::Tag, transaction::Transaction};

#[cfg(feature = "scripting")]

use scripting::ScriptExecutor;

use sqlx::{

    Acquire,

    types::Uuid,

    types::chrono::{DateTime, Utc},

};

use std::{collections::HashMap, fmt::Debug};

use supp_macro::command;

#[cfg(feature = "scripting")]

use crate::script::TransactionState;

use crate::{config::ConfigError, user::User};

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

command! {

    CreateTransaction {

        #[required]

        user_id: Uuid,

        #[required]

        splits: Vec<FinanceEntity>,

        #[required]

        id: Uuid,

        #[required]

        post_date: DateTime<Utc>,

        #[required]

        enter_date: DateTime<Utc>,

        #[optional]

        prices: Vec<FinanceEntity>,

        #[optional]

        note: String,

        #[optional]

        split_tags: Vec<(Uuid, Tag)>,

    } => {

        let user = User { id: user_id };

        let tx = Transaction {

            id,

            post_date,

            enter_date,

        };

        cfg_if! {

            if #[cfg(feature = "scripting")] {

                let (transaction, splits, prices, transaction_tags, split_tags) = {

                    let scripts: Vec<(Uuid, Vec<u8>)> = sqlx::query_file!("sql/select/artifacts/enabled.sql")

                        .fetch_all(&mut *conn)

                        .await?

                        .into_iter()

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

                };

            } else {

                let (transaction, splits, prices, transaction_tags, split_tags) = {

                    let splits: Vec<Split> = splits

                        .into_iter()

                        .filter_map(|e| match e {

                            FinanceEntity::Split(s) => Some(s),

                            _ => None,

                        })

                        .collect();

                    let prices: Vec<Price> = prices

                        .map(|ps| {

                            ps.into_iter()

                                .filter_map(|e| match e {

                                    FinanceEntity::Price(p) => Some(p),

                                    _ => None,

                                })

                                .collect()

                        })

                        .unwrap_or_default();

                    let transaction_tags: Vec<Tag> = note

                        .map(|n| {

                            vec![Tag {

                                id: Uuid::new_v4(),

                                tag_name: "note".to_string(),

                                tag_value: n,

                                description: None,

                            }]

                        })

                        .unwrap_or_default();

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

                    (tx, splits, prices, transaction_tags, split_tags)

                };

            }

        }

        // Common ticket operations

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

        let split_refs: Vec<_> = splits.iter().collect();

        ticket.add_splits(&split_refs).await?;

        if !prices.is_empty() {

            let price_refs: Vec<_> = prices.iter().collect();

            ticket.add_conversions(&price_refs).await?;

        }

        if !transaction_tags.is_empty() {

            let tag_refs: Vec<_> = transaction_tags.iter().collect();

            ticket.add_tags(&tag_refs).await?;

        }

        if !split_tags.is_empty() {

            ticket.add_split_tags(&split_tags).await?;

        }

        ticket.commit().await?;

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

    }

command! {

    ListTransactions {

        #[required]

        user_id: Uuid,

        #[optional]

        account: Uuid,

        #[optional]

        limit: i64,

        #[optional]

        offset: i64,

        #[optional]

        date_from: DateTime<Utc>,

        #[optional]

        date_to: DateTime<Utc>,

    } => {

        let user = User { id: user_id };

        let account_uuid = account.as_ref();

        let effective_limit = limit.unwrap_or(20);

        let effective_offset = offset.unwrap_or(0);

        let date_from_ref = date_from.as_ref();

        let date_to_ref = date_to.as_ref();

        let count_result = sqlx::query_file!(

            "sql/count/transactions/filtered.sql",

            account_uuid,

            date_from_ref,

            date_to_ref

        )

        .fetch_one(&mut *conn)

        .await?;

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

        let transactions = sqlx::query_file!(

            "sql/select/transactions/paginated.sql",

            account_uuid,

            date_from_ref,

            date_to_ref,

            effective_limit,

            effective_offset

        )

        .fetch_all(&mut *conn)

        .await?;

        let mut tagged_transactions = Vec::new();

        for tx_row in transactions {

            let transaction = Transaction {

                id: tx_row.id,

                post_date: tx_row.post_date,

                enter_date: tx_row.enter_date,

            };

            let tags: HashMap<String, FinanceEntity> =

                sqlx::query_file!("sql/select/tags/by_transaction.sql", &transaction.id)

                    .fetch_all(&mut *conn)

                    .await?

                    .into_iter()

                    .collect();

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

        }

        let pagination = PaginationInfo {

            total_count,

            limit: effective_limit,

            offset: effective_offset,

            has_more: effective_offset + (tagged_transactions.len() as i64) < total_count,

        };

        Ok(Some(CmdResult::TaggedEntities {

            entities: tagged_transactions,

            pagination: Some(pagination),

        }))

    }

command! {

    GetTransaction {

        #[required]

        user_id: Uuid,

        #[required]

        transaction_id: Uuid,

    } => {

        let user = User { id: user_id };

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

            Ok(Some(CmdResult::TaggedEntities {

                entities: vec![(FinanceEntity::Transaction(transaction), tags)],

                pagination: None,

            }))

        } else {

            Ok(None)

        }

    }

command! {

    UpdateTransaction {

        #[required]

        user_id: Uuid,

        #[required]

        transaction_id: Uuid,

        #[optional]

        splits: Vec<FinanceEntity>,

        #[optional]

        post_date: DateTime<Utc>,

        #[optional]

        enter_date: DateTime<Utc>,

        #[optional]

        note: String,

        #[optional]

        prices: Vec<FinanceEntity>,

        #[optional]

        tags: HashMap<String, FinanceEntity>,

    } => {

        let user = User { id: user_id };

        let mut 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);

        // Validate new splits if provided (before making any changes)

        if let Some(ref new_splits) = splits {

            let mut commodity_sums: std::collections::HashMap<Uuid, num_rational::Rational64> =

                std::collections::HashMap::new();

            for entity in new_splits {

                if let FinanceEntity::Split(split) = entity {

                    // Validate split belongs to this transaction

                    if split.tx_id != transaction_id {

                        return Err(CmdError::Args("Split transaction ID mismatch".to_string()));

                    }

                    let split_value =

                        num_rational::Rational64::new(split.value_num, split.value_denom);

                    *commodity_sums.entry(split.commodity_id).or_insert(

                        num_rational::Rational64::new(0, 1),

                    ) += split_value;

                } else {

                    return Err(CmdError::Args("Invalid entity type in splits".to_string()));

                }

            }

            // Ensure splits sum to zero (double-entry bookkeeping requirement)

            // For multi-currency transactions, individual currencies won't sum to zero

            // (they're balanced via the price table), so only validate single-currency

            if commodity_sums.len() == 1 {

                for sum in commodity_sums.values() {

                    if *sum != num_rational::Rational64::new(0, 1) {

                        return Err(CmdError::Args("Splits must sum to zero".to_string()));

                    }

                }

            }

        }

        // Update transaction metadata first

        sqlx::query_file!(

            "sql/update/transactions/update.sql",

            transaction_id,

            final_post_date,

            final_enter_date

        )

        .execute(&mut *tx)

        .await?;

        // Process splits update atomically: delete then insert

        if let Some(new_splits) = splits {

            // Delete existing split_tags, splits and their associated prices

            sqlx::query_file!("sql/delete/split_tags/by_transaction.sql", transaction_id)

                .execute(&mut *tx)

                .await?;

            sqlx::query_file!("sql/delete/prices/by_splits.sql", transaction_id)

                .execute(&mut *tx)

                .await?;

            sqlx::query_file!("sql/delete/splits/by_transaction.sql", transaction_id)

                .execute(&mut *tx)

                .await?;

            // Insert new splits

            for entity in new_splits {

                if let FinanceEntity::Split(split) = entity {

                    sqlx::query_file!(

                        "sql/insert/splits/split.sql",

                        split.id,

                        split.tx_id,

                        split.account_id,

                        split.commodity_id,

                        split.reconcile_state,

                        split.reconcile_date,

                        split.value_num,

                        split.value_denom,

                        split.lot_id

                    )

                    .execute(&mut *tx)

                    .await?;

                }

            }

        }

        // Validate and process prices if provided

        if let Some(ref new_prices) = prices {

            for entity in new_prices {

                if let FinanceEntity::Price(_) = entity {

                    // Price validation could be added here

                } else {

                    return Err(CmdError::Args("Invalid entity type in prices".to_string()));

                }

            }

        }

        // Validate tags if provided

        if let Some(ref new_tags) = tags {

            for entity in new_tags.values() {

                if let FinanceEntity::Tag(_) = entity {

                    // Tag validation could be added here

                } else {

                    return Err(CmdError::Args("Invalid entity type in tags".to_string()));

                }

            }

        }

        // Process prices update atomically

        if let Some(new_prices) = prices {

            for entity in new_prices {

                if let FinanceEntity::Price(price) = entity {

                    sqlx::query_file!(

                        "sql/insert/prices/price.sql",

                        price.id,

                        price.commodity_id,

                        price.currency_id,

                        price.commodity_split,

                        price.currency_split,

                        price.date,

                        price.value_num,

                        price.value_denom

                    )

                    .execute(&mut *tx)

                    .await?;

                }

            }

        }

        // Process tags update atomically: delete then insert

        if let Some(new_tags) = tags {

            sqlx::query_file!("sql/delete/transaction_tags/by_transaction.sql", transaction_id)

                .execute(&mut *tx)

                .await?;

            for (_, entity) in new_tags {

                if let FinanceEntity::Tag(tag) = entity {

                    sqlx::query_file!(

                        "sql/insert/transaction_tags/transaction_tag.sql",

                        transaction_id,

                        tag.id

                    )

                    .execute(&mut *tx)

                    .await?;

                }

            }

        }

        // Handle note field by creating/updating note tag

        if let Some(note_value) = note {

            // First delete any existing note tag for this transaction

            sqlx::query!("DELETE FROM transaction_tags WHERE tx_id = $1 AND tag_id IN (SELECT id FROM tags WHERE tag_name = 'note')", transaction_id)

                .execute(&mut *tx)

                .await?;

            if !note_value.trim().is_empty() {

                let note_tag_id = Tag {

                    id: Uuid::new_v4(),

                    tag_name: "note".to_string(),

                    tag_value: note_value,

                    description: None,

                }

                .commit(&mut *tx)

                .await?;

                sqlx::query_file!(

                    "sql/insert/transaction_tags/transaction_tag.sql",

                    transaction_id,

                    note_tag_id

                )

                .execute(&mut *tx)

                .await?;

            }

        }

        tx.commit().await?;

        let updated_transaction = Transaction {

            id: transaction_id,

            post_date: final_post_date,

            enter_date: final_enter_date,

        };

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

    }

command! {

    DeleteTransaction {

        #[required]

        user_id: Uuid,

        #[required]

        transaction_id: Uuid,

    } => {

        let user = User { id: user_id };

        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::TaggedEntities {

            entities,

            pagination: Some(pagination),

        }) = ListTransactions::new().user_id(user.id).run().await?

        {

            assert!(

                entities.is_empty(),

                "Expected no transactions in empty database"

            );

            assert_eq!(pagination.total_count, 0);

            assert_eq!(pagination.limit, 20);

            assert_eq!(pagination.offset, 0);

            assert!(!pagination.has_more);

        } else {

            panic!("Expected TaggedEntities result with pagination");

        }

    }

    #[local_db_sqlx_test]

    async fn test_list_transactions_with_data(pool: PgPool) -> anyhow::Result<()> {

        let user = USER.get().unwrap();

        user.commit()

            .await

            .expect("Failed to commit user to database");

        // First create a commodity

        let commodity_result = CreateCommodity::new()

            .symbol("TST".to_string())

            .name("Test Commodity".to_string())

            .user_id(user.id)

            .run()

            .await?;

        // Get the commodity ID

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {

            uuid::Uuid::parse_str(&id)?

        } else {

            panic!("Expected commodity ID string result");

        };

        // Create two accounts

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 1".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 2".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        // Create a transaction between the accounts

        let tx_id = Uuid::new_v4();

        let now = Utc::now();

        let split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

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

        CreateTransaction::new()

            .user_id(user.id)

            .splits(splits)

            .id(tx_id)

            .post_date(now)

            .enter_date(now)

            .run()

            .await?;

        // List all transactions

        if let Some(CmdResult::TaggedEntities {

            entities,

            pagination: Some(pagination),

        }) = ListTransactions::new().user_id(user.id).run().await?

        {

            assert_eq!(entities.len(), 1, "Expected one transaction");

            assert_eq!(pagination.total_count, 1);

            let (entity, _tags) = &entities[0];

            if let FinanceEntity::Transaction(tx) = entity {

                assert_eq!(tx.id, tx_id);

            } else {

                panic!("Expected Transaction entity");

            }

        } else {

            panic!("Expected TaggedEntities result with pagination");

        }

        // List transactions filtered by account

        if let Some(CmdResult::TaggedEntities { entities, .. }) = ListTransactions::new()

            .user_id(user.id)

            .account(account1.id)

            .run()

            .await?

        {

            assert_eq!(entities.len(), 1, "Expected one transaction for account1");

        } else {

            panic!("Expected TaggedEntities result");

        }

        // List transactions for non-existent account

        if let Some(CmdResult::TaggedEntities { entities, .. }) = ListTransactions::new()

            .user_id(user.id)

            .account(Uuid::new_v4())

            .run()

            .await?

        {

            assert_eq!(

                entities.len(),

                0,

                "Expected no transactions for non-existent account"

            );

        } else {

            panic!("Expected TaggedEntities result");

        }

    }

    #[local_db_sqlx_test]

    async fn test_get_transaction(pool: PgPool) -> anyhow::Result<()> {

        let user = USER.get().unwrap();

        user.commit()

            .await

            .expect("Failed to commit user to database");

        // First create a commodity

        let commodity_result = CreateCommodity::new()

            .symbol("TST".to_string())

            .name("Test Commodity".to_string())

            .user_id(user.id)

            .run()

            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {

            uuid::Uuid::parse_str(&id)?

        } else {

            panic!("Expected commodity ID string result");

        };

        // Create two accounts

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 1".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 2".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        // Create a transaction

        let tx_id = Uuid::new_v4();

        let split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

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

        let now = Utc::now();

        CreateTransaction::new()

            .user_id(user.id)

            .splits(splits)

            .id(tx_id)

            .post_date(now)

            .enter_date(now)

            .note("Test transaction".to_string())

            .run()

            .await?;

        // Test GetTransaction

        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .run()

            .await?

        {

            assert_eq!(entities.len(), 1, "Expected one transaction");

            let (entity, _tags) = &entities[0];

            if let FinanceEntity::Transaction(tx) = entity {

                assert_eq!(tx.id, tx_id);

            } else {

                panic!("Expected Transaction entity");

            }

        } else {

            panic!("Expected TaggedEntities result");

        }

        // Test GetTransaction with non-existent ID

        let result = GetTransaction::new()

            .user_id(user.id)

            .transaction_id(Uuid::new_v4())

            .run()

            .await?;

        assert!(

            result.is_none(),

            "Expected None for non-existent transaction"

        );

    }

    #[local_db_sqlx_test]

    async fn test_update_transaction(pool: PgPool) -> anyhow::Result<()> {

        let user = USER.get().unwrap();

        user.commit()

            .await

            .expect("Failed to commit user to database");

        // First create a commodity

        let commodity_result = CreateCommodity::new()

            .symbol("TST".to_string())

            .name("Test Commodity".to_string())

            .user_id(user.id)

            .run()

            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {

            uuid::Uuid::parse_str(&id)?

        } else {

            panic!("Expected commodity ID string result");

        };

        // Create two accounts

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 1".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 2".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        // Create a transaction

        let tx_id = Uuid::new_v4();

        let split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

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

        let now = Utc::now();

        CreateTransaction::new()

            .user_id(user.id)

            .splits(splits)

            .id(tx_id)

            .post_date(now)

            .enter_date(now)

            .note("Original note".to_string())

            .run()

            .await?;

        // Test UpdateTransaction with only note change

        let new_note = "Updated note".to_string();

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =

            UpdateTransaction::new()

                .user_id(user.id)

                .transaction_id(tx_id)

                .note(new_note.clone())

                .run()

                .await?

        {

            assert_eq!(updated_tx.id, tx_id);

        } else {

            panic!("Expected Transaction entity result");

        }

        // Test UpdateTransaction with new splits

        let new_split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -200,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let new_split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 200,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let new_splits = vec![

            FinanceEntity::Split(new_split1),

            FinanceEntity::Split(new_split2),

        ];

        if let Some(CmdResult::Entity(FinanceEntity::Transaction(updated_tx))) =

            UpdateTransaction::new()

                .user_id(user.id)

                .transaction_id(tx_id)

                .splits(new_splits)

                .run()

                .await?

        {

            assert_eq!(updated_tx.id, tx_id);

        } else {

            panic!("Expected Transaction entity result");

        }

        // Test UpdateTransaction atomicity: unbalanced splits should fail

        let unbalanced_split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -100, // This doesn't balance with split2

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let unbalanced_split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 50, // Should be 100 to balance

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let unbalanced_splits = vec![

            FinanceEntity::Split(unbalanced_split1),

            FinanceEntity::Split(unbalanced_split2),

        ];

        let result = UpdateTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .splits(unbalanced_splits)

            .run()

            .await;

        assert!(result.is_err(), "Expected error for unbalanced splits");

        // Verify original transaction is unchanged after failed update

        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .run()

            .await?

        {

            assert_eq!(entities.len(), 1, "Expected one transaction");

            // Transaction should still exist and be unchanged

        } else {

            panic!("Expected transaction to still exist after failed update");

        }

        // Test UpdateTransaction with non-existent transaction

        let result = UpdateTransaction::new()

            .user_id(user.id)

            .transaction_id(Uuid::new_v4())

            .note("Should fail".to_string())

            .run()

            .await;

        assert!(

            result.is_err(),

            "Expected error for non-existent transaction"

        );

    }

    #[local_db_sqlx_test]

    async fn test_update_transaction_atomicity(pool: PgPool) -> anyhow::Result<()> {

        let user = USER.get().unwrap();

        user.commit()

            .await

            .expect("Failed to commit user to database");

        // First create a commodity

        let commodity_result = CreateCommodity::new()

            .symbol("TST".to_string())

            .name("Test Commodity".to_string())

            .user_id(user.id)

            .run()

            .await?;

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {

            uuid::Uuid::parse_str(&id)?

        } else {

            panic!("Expected commodity ID string result");

        };

        // Create two accounts

        let account1 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 1".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =

            CreateAccount::new()

                .name("Account 2".to_string())

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        // Create a transaction

        let tx_id = Uuid::new_v4();

        let split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

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

        let now = Utc::now();

        CreateTransaction::new()