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

use sqlx::{

    Acquire,

    types::Uuid,

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

};

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

use supp_macro::command;

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

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

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,

    } => {

        // Create new transaction

        let tx = Transaction {

            id,

            post_date,

            enter_date,

        };

        let user = User { id: user_id };

        // Get database connection

        // Start transaction ticket

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

        // Add splits using the ticket

        let mut split_refs = Vec::new();

        for entity in &splits {

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

                split_refs.push(split);

            }

        }

        ticket.add_splits(&split_refs).await?;

        if let Some(prices) = prices {

            let mut price_refs = Vec::new();

            for entity in &prices {

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

                    price_refs.push(price);

                }

            }

            ticket.add_conversions(&price_refs).await?;

        }

        if let Some(note) = note {

            let tag = Tag {

                id: Uuid::new_v4(),

                tag_name: "note".to_string(),

                tag_value: note,

                description: None,

            };

            ticket.add_tags(&[&tag]).await?;

        }

        // Commit the transaction ticket

        ticket.commit().await?;

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

    }

command! {

    ListTransactions {

        #[required]

        user_id: Uuid,

        #[optional]

        account: Uuid,

    } => {

        let user = User { id: user_id };

        let account_uuid = account.as_ref();

        let transactions = sqlx::query_file!("sql/select/transactions/all.sql", account_uuid)

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

            };

            // Get tags for this transaction

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

        }

        Ok(Some(CmdResult::TaggedEntities(tagged_transactions)))

    }

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(vec![(FinanceEntity::Transaction(transaction), tags)])))

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

                // Create a new note tag

                let note_tag = Tag {

                    id: Uuid::new_v4(),

                    tag_name: "note".to_string(),

                    tag_value: note_value,

                    description: None,

                };

                // Insert the tag into the tags table

                sqlx::query_file!(

                    "sql/insert/tags/tag.sql",

                    note_tag.id,

                    note_tag.tag_name,

                    note_tag.tag_value,

                    note_tag.description

                )

                .execute(&mut *tx)

                .await?;

                // Link the tag to the transaction

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

    }

#[cfg(test)]

mod command_tests {

    use super::*;

    use crate::{

        command::{account::CreateAccount, commodity::CreateCommodity},

        db::DB_POOL,

    };

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

            .fraction(1.into())

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

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

        {

            assert!(

                entities.is_empty(),

                "Expected no transactions in empty database"

            );

        } else {

            panic!("Expected TaggedEntities result");

        }

    }

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

            .fraction(1.into())

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

            ListTransactions::new().user_id(user.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");

        }

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

            .fraction(1.into())

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

            .fraction(1.into())

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

            .fraction(1.into())

            .symbol("TST".to_string())

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

            .user_id(user.id)

            .run()

            .await?;

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

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

        } else {

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

        };

        // Create two accounts

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

            CreateAccount::new()

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

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

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

            CreateAccount::new()

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

                .user_id(user.id)

                .run()

                .await?

        {

            account

        } else {

            panic!("Expected account entity result");

        };

        // Create a transaction

        let tx_id = Uuid::new_v4();

        let split1 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account1.id,

            commodity_id,

            value_num: -100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let split2 = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

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

        let now = Utc::now();

        CreateTransaction::new()

            .user_id(user.id)

            .splits(splits)

            .id(tx_id)

            .post_date(now)

            .enter_date(now)

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

            .run()

            .await?;

        // Test 1: Split transaction ID mismatch validation

        let wrong_tx_id = Uuid::new_v4();

        let invalid_split = Split {

            id: Uuid::new_v4(),

            tx_id: wrong_tx_id, // Wrong transaction ID

            account_id: account1.id,

            commodity_id,

            value_num: -50,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let valid_split = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 50,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let mismatched_splits = vec![

            FinanceEntity::Split(invalid_split),

            FinanceEntity::Split(valid_split),

        ];

        let result = UpdateTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .splits(mismatched_splits)

            .run()

            .await;

        assert!(

            result.is_err(),

            "Expected error for split transaction ID mismatch"

        );

        if let Err(CmdError::Args(msg)) = result {

            assert!(msg.contains("Split transaction ID mismatch"));

        } else {

            panic!("Expected CmdError::Args with transaction ID mismatch message");

        }

        // Verify original transaction is unchanged

        if let Some(CmdResult::TaggedEntities(entities)) = GetTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .run()

            .await?

        {

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

        } else {

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

        }

        // Test 2: Invalid entity type in splits

        let invalid_splits = vec![

            FinanceEntity::Account(Account {

                id: account1.id,

                parent: account1.parent,

            }), // Wrong entity type

        ];

        let result = UpdateTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .splits(invalid_splits)

            .run()

            .await;

        assert!(

            result.is_err(),

            "Expected error for invalid entity type in splits"

        );

        if let Err(CmdError::Args(msg)) = result {

            assert!(msg.contains("Invalid entity type in splits"));

        } else {

            panic!("Expected CmdError::Args with invalid entity type message");

        }

        // Test 3: Invalid entity type in prices

        let invalid_prices = vec![

            FinanceEntity::Account(Account {

                id: account1.id,

                parent: account1.parent,

            }), // Wrong entity type

        ];

        let result = UpdateTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .prices(invalid_prices)

            .run()

            .await;

        assert!(

            result.is_err(),

            "Expected error for invalid entity type in prices"

        );

        if let Err(CmdError::Args(msg)) = result {

            assert!(msg.contains("Invalid entity type in prices"));

        } else {

            panic!("Expected CmdError::Args with invalid entity type message");

        }

        // Test 4: Invalid entity type in tags

        let mut invalid_tags = HashMap::new();

        invalid_tags.insert(

            "test".to_string(),

            FinanceEntity::Account(Account {

                id: account1.id,

                parent: account1.parent,

            }),

        );

        let result = UpdateTransaction::new()

            .user_id(user.id)

            .transaction_id(tx_id)

            .tags(invalid_tags)

            .run()

            .await;

        assert!(

            result.is_err(),

            "Expected error for invalid entity type in tags"

        );

        if let Err(CmdError::Args(msg)) = result {

            assert!(msg.contains("Invalid entity type in tags"));

        } else {

            panic!("Expected CmdError::Args with invalid entity type message");

        }

        // Test 5: Database rollback verification - count splits before and after failed update

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

        let initial_split_count = sqlx::query!(

            "SELECT COUNT(*) as count FROM splits WHERE tx_id = $1",

            tx_id

        )

        .fetch_one(&mut *conn)

        .await?

        .count

        .unwrap_or(0);

        // Try an update that will fail during split insertion (invalid account ID)

        let invalid_account_split = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: Uuid::new_v4(), // Non-existent account

            commodity_id,

            value_num: -100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let balancing_split = Split {

            id: Uuid::new_v4(),

            tx_id,

            account_id: account2.id,

            commodity_id,

            value_num: 100,

            value_denom: 1,

            reconcile_state: None,

            reconcile_date: None,

            lot_id: None,

        };

        let failing_splits = vec![

            FinanceEntity::Split(invalid_account_split),

            FinanceEntity::Split(balancing_split),

        ];