server/command/

account.rs

use finance::{account::Account, commodity::Commodity, error::FinanceError, tag::Tag};
use num_rational::Rational64;
use sqlx::types::Uuid;
use std::{collections::HashMap, fmt::Debug};
use supp_macro::command;

use crate::{command::CommodityInfo, config::ConfigError, user::User};
use finance::error::BalanceError;

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

command! {
    CreateAccount {
        #[required]
        name: String,
        #[required]
        user_id: Uuid,
        #[optional]
        parent: Uuid,
    } => {
        let user = User { id: user_id };

        Ok(Some(CmdResult::Entity(FinanceEntity::Account(
            user.create_account(
                &name,
                parent,
            )
            .await?,
        ))))
    }
}

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

        // Get all accounts with their commodities
        let mut tagged_accounts = Vec::new();
        let rows = sqlx::query_file!("sql/select/accounts/all.sql")
            .fetch_all(&mut *conn)
            .await?;

        for row in rows {
            let account = Account {
                id: row.id,
                parent: row.parent_id,
            };

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

            // Now push them, no need to borrow from the vector.
            tagged_accounts.push((FinanceEntity::Account(account), tags));
        }

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

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

        let rows = sqlx::query_file!("sql/select/accounts/manage_tree.sql")
            .fetch_all(&mut *conn)
            .await?;

        let mut tagged_accounts = Vec::new();
        for row in rows {
            let account = Account {
                id: row.id,
                parent: row.parent_id,
            };

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

            tagged_accounts.push((FinanceEntity::Account(account), tags));
        }

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

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

        let row = sqlx::query_file!("sql/select/accounts/manage_details.sql", &account_id)
            .fetch_optional(&mut *conn)
            .await?;

        let Some(row) = row else {
            return Ok(Some(CmdResult::TaggedEntities {
                entities: vec![],
                pagination: None,
            }));
        };

        let account = Account {
            id: row.id,
            parent: row.parent_id,
        };

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

        Ok(Some(CmdResult::TaggedEntities {
            entities: vec![(FinanceEntity::Account(account), tags)],
            pagination: None,
        }))
    }
}

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

        let account_row = sqlx::query_file!("sql/select/accounts/by_id.sql", &account_id)
            .fetch_optional(&mut *conn)
            .await?;

        let Some(account_row) = account_row else {
            return Err(CmdError::Args("Account not found".to_string()));
        };

        let account = Account {
            id: account_row.id,
            parent: account_row.parent,
        };

        let desc = description.and_then(|text| {
            if text.trim().is_empty() {
                None
            } else {
                Some(text)
            }
        });

        let tag = Tag {
            id: Uuid::new_v4(),
            tag_name,
            tag_value,
            description: desc,
        };

        user.set_account_tag(&account, &tag).await?;

        Ok(Some(CmdResult::String("ok".to_string())))
    }
}

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

        // Get account by ID or name
        let mut account_id_option: Option<Uuid> = None;

        if let Some(aid) = account_id {
            account_id_option = Some(aid);
        } else if let Some(name) = account_name {
            // Search by name tag
            let account_row = sqlx::query_file!("sql/select/accounts/by_name.sql", &name)
                .fetch_optional(&mut *conn)
                .await?;

            if let Some(row) = account_row {
                account_id_option = Some(row.id);
            }
        } else {
            return Err(CmdError::Args(
                "Either account_id or account_name must be provided".to_string(),
            ));
        }

        // Now fetch the account by ID if we found one
        let account_query = if let Some(account_id) = account_id_option {
            sqlx::query_file!("sql/select/accounts/by_id.sql", &account_id)
                .fetch_optional(&mut *conn)
                .await?
        } else {
            None
        };

        // If account found, get its tags
        if let Some(row) = account_query {
            let account = Account {
                id: row.id,
                parent: row.parent,
            };

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

            let tagged_account = vec![(FinanceEntity::Account(account), tags)];
            Ok(Some(CmdResult::TaggedEntities {
                entities: tagged_account,
                pagination: None,
            }))
        } else {
            // No account found
            Ok(Some(CmdResult::TaggedEntities {
                entities: vec![],
                pagination: None,
            }))
        }
    }
}

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

        // Get unique commodities for this account
        let rows = sqlx::query_file!("sql/select/accounts/commodities.sql", &account_id)
            .fetch_all(&mut *conn)
            .await?;

        // Return the commodity information as structured data
        let mut commodity_infos = Vec::new();
        for row in rows {
            commodity_infos.push(CommodityInfo {
                commodity_id: row.commodity_id,
                symbol: row.symbol,
                name: row.commodity_name,
            });
        }

        Ok(Some(CmdResult::CommodityInfoList(commodity_infos)))
    }
}

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

        // Get all splits with their conversion information
        let splits_data = sqlx::query_file!(
            "sql/balance/accounts/splits/all_with_conversion.sql",
            &account_id,
            commodity_id.as_ref()
        )
        .fetch_all(&mut *conn)
        .await?;

        if splits_data.is_empty() {
            // No splits - return zero balance as rational regardless of currency request
            return Ok(Some(CmdResult::Rational(Rational64::new(0, 1))));
        }

        // Check if all splits use the same commodity
        let unique_commodities: std::collections::HashSet<_> = splits_data.iter().map(|s| s.commodity_id).collect();

        match commodity_id {
            Some(target_commodity_id) => {
                // Single currency mode - convert everything to target commodity
                let mut total_balance = Rational64::new(0, 1);

                for split_data in splits_data {
                    let split_value = Rational64::new(split_data.value_num, split_data.value_denom);

                    if split_data.commodity_id == target_commodity_id {
                        // Same commodity, add directly
                        total_balance += split_value;
                    } else {
                        // Different commodity, need conversion
                        if let (Some(price_num), Some(price_denom)) = (split_data.price_num, split_data.price_denom) {
                            // Conversion data available
                            let price_ratio = Rational64::new(price_num, price_denom);
                            let converted_value = split_value * price_ratio;
                            total_balance += converted_value;
                        } else {
                            // Missing conversion data - get target commodity symbol for error
                            let to_symbol = sqlx::query_file_scalar!(
                                "sql/select/commodities/symbol.sql",
                                &target_commodity_id
                            )
                            .fetch_optional(&mut *conn)
                            .await?
                            .unwrap_or_else(|| target_commodity_id.to_string());

                            return Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
                                split_id: split_data.split_id,
                                from_commodity: split_data.commodity_symbol.clone(),
                                to_commodity: to_symbol,
                            })));
                        }
                    }
                }

                Ok(Some(CmdResult::Rational(total_balance)))
            },
            None => {
                // No specific currency requested
                if unique_commodities.len() == 1 {
                    // Single currency - return simple rational balance
                    let total_balance = splits_data.iter()
                        .map(|split_data| Rational64::new(split_data.value_num, split_data.value_denom))
                        .sum();
                    Ok(Some(CmdResult::Rational(total_balance)))
                } else {
                    // Multi-currency mode - return balance for each commodity
                    use std::collections::HashMap;
                    let mut balances_by_commodity: HashMap<Uuid, (Commodity, Rational64, String)> = HashMap::new();

                    for split_data in splits_data {
                        let split_value = Rational64::new(split_data.value_num, split_data.value_denom);

                        balances_by_commodity
                            .entry(split_data.commodity_id)
                            .and_modify(|(_, balance, _)| *balance += split_value)
                            .or_insert_with(|| {
                                let commodity = Commodity {
                                    id: split_data.commodity_id,
                                };
                                (commodity, split_value, split_data.commodity_symbol.clone())
                            });
                    }

                    // Convert to sorted vector (sort by symbol)
                    let mut result: Vec<(Commodity, Rational64)> = balances_by_commodity
                        .into_values()
                        .map(|(commodity, balance, _symbol)| (commodity, balance))
                        .collect();
                    result.sort_by(|a, b| {
                        // Sort by commodity_id since we don't have symbol in Commodity struct
                        a.0.id.cmp(&b.0.id)
                    });

                    Ok(Some(CmdResult::MultiCurrencyBalance(result)))
                }
            }
        }
    }
}

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

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

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

    #[local_db_sqlx_test]
    async fn test_create_account(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 and create a commodity entity
        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };
        let _commodity = Commodity { id: commodity_id };

        // Now create an account
        if let Some(CmdResult::Entity(FinanceEntity::Account(account))) = CreateAccount::new()
            .name("Test Account".to_string())
            .user_id(user.id)
            .run()
            .await?
        {
            assert!(!account.id.is_nil());
        } else {
            panic!("Expected account ID string result");
        }
    }

    #[local_db_sqlx_test]
    async fn test_list_accounts_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, .. }) =
            ListAccounts::new().user_id(user.id).run().await?
        {
            assert!(
                entities.is_empty(),
                "Expected no accounts in empty database"
            );
        } else {
            panic!("Expected TaggedEntities result");
        }
    }

    #[local_db_sqlx_test]
    async fn test_list_accounts_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 and create a commodity entity
        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            uuid::Uuid::parse_str(&id)?
        } else {
            panic!("Expected commodity ID string result");
        };
        let _commodity = Commodity { id: commodity_id };

        // Create an account
        CreateAccount::new()
            .name("Test Account".to_string())
            .user_id(user.id)
            .run()
            .await?;

        // List accounts
        if let Some(CmdResult::TaggedEntities { entities, .. }) =
            ListAccounts::new().user_id(user.id).run().await?
        {
            assert_eq!(entities.len(), 1, "Expected one account");

            let (entity, tags) = &entities[0];
            if let FinanceEntity::Account(_) = entity {
                // Check tags
                assert_eq!(tags.len(), 1); // name tag
                if let FinanceEntity::Tag(tag) = &tags["name"] {
                    assert_eq!(tag.tag_name, "name");
                    assert_eq!(tag.tag_value, "Test Account");
                } else {
                    panic!("Expected Tag entity");
                }
            } else {
                panic!("Expected Account entity");
            }
        } else {
            panic!("Expected TaggedEntities result");
        }
    }

    #[local_db_sqlx_test]
    async fn test_get_account(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 an account
        let account_name = "Test Account";
        let account = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name(account_name.to_string())
                .user_id(user.id)
                .run()
                .await?
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Test GetAccount by ID
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetAccount::new()
            .user_id(user.id)
            .account_id(account.id)
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one account");

            let (entity, tags) = &entities[0];
            if let FinanceEntity::Account(a) = entity {
                assert_eq!(a.id, account.id);

                // Check tags
                assert_eq!(tags.len(), 1); // name tag
                if let FinanceEntity::Tag(tag) = &tags["name"] {
                    assert_eq!(tag.tag_name, "name");
                    assert_eq!(tag.tag_value, account_name);
                } else {
                    panic!("Expected Tag entity");
                }
            } else {
                panic!("Expected Account entity");
            }
        } else {
            panic!("Expected TaggedEntities result");
        }

        // Test GetAccount by name
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetAccount::new()
            .user_id(user.id)
            .account_name(account_name.to_string())
            .run()
            .await?
        {
            assert_eq!(entities.len(), 1, "Expected one account");

            let (entity, _) = &entities[0];
            if let FinanceEntity::Account(a) = entity {
                assert_eq!(a.id, account.id);
            } else {
                panic!("Expected Account entity");
            }
        } else {
            panic!("Expected TaggedEntities result");
        }

        // Test with non-existent account ID
        let non_existent_id = Uuid::new_v4();
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetAccount::new()
            .user_id(user.id)
            .account_id(non_existent_id)
            .run()
            .await?
        {
            assert_eq!(
                entities.len(),
                0,
                "Expected no accounts for non-existent ID"
            );
        } else {
            panic!("Expected empty TaggedEntities result");
        }

        // Test with non-existent account name
        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetAccount::new()
            .user_id(user.id)
            .account_name("Non-existent Account".to_string())
            .run()
            .await?
        {
            assert_eq!(
                entities.len(),
                0,
                "Expected no accounts for non-existent name"
            );
        } else {
            panic!("Expected empty TaggedEntities result");
        }
    }

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

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

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

        if let Some(CmdResult::TaggedEntities { entities, .. }) = GetAccount::new()
            .user_id(user.id)
            .account_id(child.id)
            .run()
            .await?
        {
            let (entity, _) = &entities[0];
            if let FinanceEntity::Account(a) = entity {
                assert_eq!(
                    a.parent,
                    Some(parent.id),
                    "get-account must return the child's parent"
                );
            } else {
                panic!("Expected Account entity");
            }
        } else {
            panic!("Expected TaggedEntities result");
        }
    }

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

        // Create a commodity
        let _commodity_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await?;

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

        // Test GetAccountCommodities on account with no transactions
        if let Some(CmdResult::CommodityInfoList(commodities)) = GetAccountCommodities::new()
            .user_id(user.id)
            .account_id(account.id)
            .run()
            .await?
        {
            assert_eq!(
                commodities.len(),
                0,
                "Expected no commodities for account with no transactions"
            );
        } else {
            panic!("Expected CommodityInfoList result");
        }
    }

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

        // Create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("EUR".to_string())
            .name("Euro".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 with single commodity (EUR)
        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: -500,
            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: 500,
            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?;

        // Test GetAccountCommodities on account1 (should have one commodity)
        if let Some(CmdResult::CommodityInfoList(commodities)) = GetAccountCommodities::new()
            .user_id(user.id)
            .account_id(account1.id)
            .run()
            .await?
        {
            assert_eq!(
                commodities.len(),
                1,
                "Expected one commodity for account with single currency"
            );

            let commodity_info = &commodities[0];
            assert_eq!(commodity_info.commodity_id, commodity_id);
            assert_eq!(commodity_info.symbol, "EUR");
            assert_eq!(commodity_info.name, "Euro");
        } else {
            panic!("Expected CommodityInfoList result");
        }

        // Test GetAccountCommodities on account2 (should also have one commodity)
        if let Some(CmdResult::CommodityInfoList(commodities)) = GetAccountCommodities::new()
            .user_id(user.id)
            .account_id(account2.id)
            .run()
            .await?
        {
            assert_eq!(
                commodities.len(),
                1,
                "Expected one commodity for account with single currency"
            );

            let commodity_info = &commodities[0];
            assert_eq!(commodity_info.commodity_id, commodity_id);
            assert_eq!(commodity_info.symbol, "EUR");
            assert_eq!(commodity_info.name, "Euro");
        } else {
            panic!("Expected CommodityInfoList result");
        }
    }

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

        // Create two commodities
        let usd_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await?;

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

        let eur_result = CreateCommodity::new()
            .symbol("EUR".to_string())
            .name("Euro".to_string())
            .user_id(user.id)
            .run()
            .await?;

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

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

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

        // Create first transaction with USD
        let tx1_id = Uuid::new_v4();
        let now = Utc::now();

        let splits1 = vec![
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx1_id,
                account_id: mixed_account.id,
                commodity_id: usd_id,
                value_num: 100,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx1_id,
                account_id: other_account.id,
                commodity_id: usd_id,
                value_num: -100,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
        ];

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

        // Create second transaction with EUR
        let tx2_id = Uuid::new_v4();

        let splits2 = vec![
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx2_id,
                account_id: mixed_account.id,
                commodity_id: eur_id,
                value_num: 200,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx2_id,
                account_id: other_account.id,
                commodity_id: eur_id,
                value_num: -200,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
        ];

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

        // Test GetAccountCommodities on mixed_account (should have two commodities)
        if let Some(CmdResult::CommodityInfoList(commodities)) = GetAccountCommodities::new()
            .user_id(user.id)
            .account_id(mixed_account.id)
            .run()
            .await?
        {
            assert_eq!(
                commodities.len(),
                2,
                "Expected two commodities for account with mixed currencies"
            );

            // Should be sorted by symbol (EUR comes before USD alphabetically)
            let eur_info = &commodities[0];
            assert_eq!(eur_info.commodity_id, eur_id);
            assert_eq!(eur_info.symbol, "EUR");
            assert_eq!(eur_info.name, "Euro");

            let usd_info = &commodities[1];
            assert_eq!(usd_info.commodity_id, usd_id);
            assert_eq!(usd_info.symbol, "USD");
            assert_eq!(usd_info.name, "US Dollar");
        } else {
            panic!("Expected CommodityInfoList result");
        }

        // Test GetAccountCommodities on other_account (should also have two commodities)
        if let Some(CmdResult::CommodityInfoList(commodities)) = GetAccountCommodities::new()
            .user_id(user.id)
            .account_id(other_account.id)
            .run()
            .await?
        {
            assert_eq!(
                commodities.len(),
                2,
                "Expected two commodities for account with mixed currencies"
            );
        } else {
            panic!("Expected CommodityInfoList result");
        }
    }

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

        // Test with non-existent account ID
        let non_existent_account_id = Uuid::new_v4();
        if let Some(CmdResult::CommodityInfoList(commodities)) = GetAccountCommodities::new()
            .user_id(user.id)
            .account_id(non_existent_account_id)
            .run()
            .await?
        {
            assert_eq!(
                commodities.len(),
                0,
                "Expected no commodities for non-existent account"
            );
        } else {
            panic!("Expected CommodityInfoList result");
        }

        // Test with non-existent user ID
        let non_existent_user_id = Uuid::new_v4();
        let result = GetAccountCommodities::new()
            .user_id(non_existent_user_id)
            .account_id(Uuid::new_v4())
            .run()
            .await;

        // Should succeed but return empty list since user isolation prevents access
        if let Ok(Some(CmdResult::CommodityInfoList(commodities))) = result {
            assert_eq!(
                commodities.len(),
                0,
                "Expected no commodities for non-existent user"
            );
        } else {
            // Or it might fail due to connection issues, which is also acceptable
            assert!(
                result.is_err(),
                "Expected error or empty result for non-existent user"
            );
        }
    }

    #[local_db_sqlx_test]
    async fn test_multi_currency_account_balance_with_price_conversion(pool: PgPool) {
        setup().await;
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Step 1: Create two commodities (USD and EUR)
        let usd_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let eur_result = CreateCommodity::new()
            .symbol("EUR".to_string())
            .name("Euro".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        // Extract commodity IDs
        let usd_id = if let Some(CmdResult::String(id)) = usd_result {
            Uuid::parse_str(&id).unwrap()
        } else {
            panic!("Expected USD commodity ID");
        };

        let eur_id = if let Some(CmdResult::String(id)) = eur_result {
            Uuid::parse_str(&id).unwrap()
        } else {
            panic!("Expected EUR commodity ID");
        };

        // Step 2: Create two accounts
        let account1_result = CreateAccount::new()
            .name("USD Account".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let account2_result = CreateAccount::new()
            .name("EUR Account".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        // Extract account IDs
        let account1_id =
            if let Some(CmdResult::Entity(FinanceEntity::Account(acc))) = account1_result {
                acc.id
            } else {
                panic!("Expected USD account");
            };

        let account2_id =
            if let Some(CmdResult::Entity(FinanceEntity::Account(acc))) = account2_result {
                acc.id
            } else {
                panic!("Expected EUR account");
            };

        // Step 3: Create single-currency transactions first to test basic functionality
        let tx1_id = Uuid::new_v4();
        let now = DateTime::<Utc>::from_timestamp(1640995200, 0).unwrap(); // Fixed timestamp

        let tx1_split1 = Split {
            id: Uuid::new_v4(),
            tx_id: tx1_id,
            account_id: account1_id,
            commodity_id: usd_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let tx1_split2 = Split {
            id: Uuid::new_v4(),
            tx_id: tx1_id,
            account_id: account2_id,
            commodity_id: usd_id, // Same currency to make transaction balance
            value_num: 100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let tx1_splits = vec![
            FinanceEntity::Split(tx1_split1),
            FinanceEntity::Split(tx1_split2),
        ];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(tx1_splits)
            .id(tx1_id)
            .post_date(now)
            .enter_date(now)
            .note("First USD transaction".to_string())
            .run()
            .await
            .unwrap();

        // Create second transaction in EUR currency
        let tx2_id = Uuid::new_v4();
        let later = DateTime::<Utc>::from_timestamp(1640995800, 0).unwrap(); // 10 minutes later

        let tx2_split1 = Split {
            id: Uuid::new_v4(),
            tx_id: tx2_id,
            account_id: account1_id,
            commodity_id: eur_id,
            value_num: -85,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let tx2_split2 = Split {
            id: Uuid::new_v4(),
            tx_id: tx2_id,
            account_id: account2_id,
            commodity_id: eur_id,
            value_num: 85,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let tx2_splits = vec![
            FinanceEntity::Split(tx2_split1),
            FinanceEntity::Split(tx2_split2),
        ];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(tx2_splits)
            .id(tx2_id)
            .post_date(later)
            .enter_date(later)
            .note("Second EUR transaction".to_string())
            .run()
            .await
            .unwrap();

        // Create price conversion data between EUR and USD
        let price1 = Price {
            id: Uuid::new_v4(),
            date: now,
            commodity_id: eur_id,
            currency_id: usd_id,
            commodity_split: None, // General price, not tied to specific splits
            currency_split: None,
            value_num: 1176, // 1.176 USD per EUR (as rational: 1176/1000)
            value_denom: 1000,
        };

        // Insert price manually using raw SQL since we don't have a CreatePrice command
        let mut conn = user.get_connection().await.unwrap();
        sqlx::query_file!(
            "sql/insert/prices/price.sql",
            price1.id,
            price1.commodity_id,
            price1.currency_id,
            price1.commodity_split,
            price1.currency_split,
            price1.date,
            price1.value_num,
            price1.value_denom
        )
        .execute(&mut *conn)
        .await
        .unwrap();

        // Test Account1 balance (mixed currencies - should return MultiCurrencyBalance without commodity_id)
        let balance_result1 = GetBalance::new()
            .user_id(user.id)
            .account_id(account1_id)
            .run()
            .await
            .unwrap();

        // Should return MultiCurrencyBalance due to mixed currencies
        match balance_result1 {
            Some(CmdResult::MultiCurrencyBalance(balances)) => {
                assert_eq!(
                    balances.len(),
                    2,
                    "Account1 should have two currency balances"
                );
            }
            _ => panic!("Expected MultiCurrencyBalance result for account1"),
        }

        // Test Account1 balance in USD (should fail due to missing split-specific conversion)
        let balance_result2 = GetBalance::new()
            .user_id(user.id)
            .account_id(account1_id)
            .commodity_id(usd_id)
            .run()
            .await;

        // Should fail because the price record is not split-specific
        assert!(
            balance_result2.is_err(),
            "Expected error for missing split-specific EUR->USD conversion"
        );

        // Verify it's the right kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance_result2
        {
            assert_eq!(from_commodity, "EUR");
            assert_eq!(to_commodity, "USD");
        } else {
            panic!("Expected MissingConversion error");
        }

        // Test Account2 balance (mixed currencies - should return MultiCurrencyBalance without commodity_id)
        let balance_result3 = GetBalance::new()
            .user_id(user.id)
            .account_id(account2_id)
            .run()
            .await
            .unwrap();

        // Should return MultiCurrencyBalance due to mixed currencies
        match balance_result3 {
            Some(CmdResult::MultiCurrencyBalance(balances)) => {
                assert_eq!(
                    balances.len(),
                    2,
                    "Account2 should have two currency balances"
                );
            }
            _ => panic!("Expected MultiCurrencyBalance result for account2"),
        }

        // Test Account2 balance in EUR (should fail due to missing split-specific conversion)
        let balance_result4 = GetBalance::new()
            .user_id(user.id)
            .account_id(account2_id)
            .commodity_id(eur_id)
            .run()
            .await;

        // Should fail because USD->EUR conversion is not available (split-specific)
        assert!(
            balance_result4.is_err(),
            "Expected error for missing split-specific USD->EUR conversion"
        );

        // Verify it's the right kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance_result4
        {
            assert_eq!(from_commodity, "USD");
            assert_eq!(to_commodity, "EUR");
        } else {
            panic!("Expected MissingConversion error");
        }

        // Test currency conversion - get Account2 balance in USD (should fail due to missing split-specific conversion)
        let balance_result5 = GetBalance::new()
            .user_id(user.id)
            .account_id(account2_id)
            .commodity_id(usd_id) // Convert to USD
            .run()
            .await;

        // Should fail because EUR->USD conversion is not available (split-specific)
        assert!(
            balance_result5.is_err(),
            "Expected error for missing split-specific EUR->USD conversion"
        );

        // Verify it's the right kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance_result5
        {
            assert_eq!(from_commodity, "EUR");
            assert_eq!(to_commodity, "USD");
        } else {
            panic!("Expected MissingConversion error for USD conversion");
        }
    }

    #[local_db_sqlx_test]
    async fn test_account_balance_without_price_data(pool: PgPool) {
        setup().await;
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Create commodities and accounts
        let usd_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let eur_result = CreateCommodity::new()
            .symbol("EUR".to_string())
            .name("Euro".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let usd_id = if let Some(CmdResult::String(id)) = usd_result {
            Uuid::parse_str(&id).unwrap()
        } else {
            panic!("Expected USD commodity ID");
        };

        let eur_id = if let Some(CmdResult::String(id)) = eur_result {
            Uuid::parse_str(&id).unwrap()
        } else {
            panic!("Expected EUR commodity ID");
        };

        let account_result = CreateAccount::new()
            .name("Mixed Account".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let account_id =
            if let Some(CmdResult::Entity(FinanceEntity::Account(acc))) = account_result {
                acc.id
            } else {
                panic!("Expected account");
            };

        // Create a second account to make balanced transactions
        let account2_result = CreateAccount::new()
            .name("Second Account".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let account2_id =
            if let Some(CmdResult::Entity(FinanceEntity::Account(acc))) = account2_result {
                acc.id
            } else {
                panic!("Expected second account");
            };

        // Create first transaction (USD) WITHOUT price data
        let tx1_id = Uuid::new_v4();
        let now = DateTime::<Utc>::from_timestamp(1640995200, 0).unwrap();

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

        let split1_balance = Split {
            id: Uuid::new_v4(),
            tx_id: tx1_id,
            account_id: account2_id,
            commodity_id: usd_id,
            value_num: -100,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let splits1 = vec![
            FinanceEntity::Split(split1),
            FinanceEntity::Split(split1_balance),
        ];
        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits1)
            .id(tx1_id)
            .post_date(now)
            .enter_date(now)
            .note("USD transaction without price data".to_string())
            .run()
            .await
            .unwrap();

        // Create second transaction (EUR) WITHOUT price data
        let tx2_id = Uuid::new_v4();

        let split2 = Split {
            id: Uuid::new_v4(),
            tx_id: tx2_id,
            account_id,
            commodity_id: eur_id,
            value_num: -85,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let split2_balance = Split {
            id: Uuid::new_v4(),
            tx_id: tx2_id,
            account_id: account2_id,
            commodity_id: eur_id,
            value_num: 85,
            value_denom: 1,
            reconcile_state: None,
            reconcile_date: None,
            lot_id: None,
        };

        let splits2 = vec![
            FinanceEntity::Split(split2),
            FinanceEntity::Split(split2_balance),
        ];
        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits2)
            .id(tx2_id)
            .post_date(now)
            .enter_date(now)
            .note("EUR transaction without price data".to_string())
            .run()
            .await
            .unwrap();

        // Test balance calculation - should fail due to missing conversion
        let balance_result = GetBalance::new()
            .user_id(user.id)
            .account_id(account_id)
            .commodity_id(usd_id) // Try to get balance in USD
            .run()
            .await;

        // Should fail due to missing EUR->USD price conversion
        assert!(
            balance_result.is_err(),
            "Expected error for missing EUR->USD conversion"
        );

        // Verify it's the right kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance_result
        {
            assert_eq!(from_commodity, "EUR");
            assert_eq!(to_commodity, "USD");
        } else {
            panic!("Expected MissingConversion error");
        }
    }

    #[local_db_sqlx_test]
    async fn test_get_balance_single_currency(pool: PgPool) {
        setup().await;
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Create a commodity
        let commodity_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await
            .expect("Failed to create commodity");

        let commodity_id = if let Some(CmdResult::String(id)) = commodity_result {
            Uuid::parse_str(&id).expect("Failed to parse commodity ID")
        } else {
            panic!("Expected commodity ID string result");
        };

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

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

        // Create a transaction: 100 USD from account1 to account2
        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
            .expect("Test operation failed");

        // Test balance for account1 (should be -100)
        let balance_result1 = GetBalance::new()
            .user_id(user.id)
            .account_id(account1.id)
            .run()
            .await
            .expect("Test operation failed");

        if let Some(CmdResult::Rational(balance)) = balance_result1 {
            assert_eq!(
                balance,
                Rational64::new(-100, 1),
                "Account1 balance should be -100, got: {balance}"
            );
        } else {
            panic!("Expected rational balance result for account1");
        }

        // Test balance for account2 (should be 100)
        let balance_result2 = GetBalance::new()
            .user_id(user.id)
            .account_id(account2.id)
            .run()
            .await
            .expect("Test operation failed");

        if let Some(CmdResult::Rational(balance)) = balance_result2 {
            assert_eq!(
                balance,
                Rational64::new(100, 1),
                "Account2 balance should be 100, got: {balance}"
            );
        } else {
            panic!("Expected rational balance result for account2");
        }

        // Test balance with explicit commodity_id (should work the same)
        let balance_result3 = GetBalance::new()
            .user_id(user.id)
            .account_id(account1.id)
            .commodity_id(commodity_id)
            .run()
            .await
            .expect("Test operation failed");

        if let Some(CmdResult::Rational(balance)) = balance_result3 {
            assert_eq!(
                balance,
                Rational64::new(-100, 1),
                "Account1 balance with explicit commodity_id should be -100, got: {balance}"
            );
        } else {
            panic!("Expected rational balance result with explicit commodity_id");
        }
    }

    #[local_db_sqlx_test]
    async fn test_get_balance_empty_account(pool: PgPool) {
        setup().await;
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Create an account with no transactions
        let account = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Empty Account".to_string())
                .user_id(user.id)
                .run()
                .await
                .expect("Test operation failed")
        {
            account
        } else {
            panic!("Expected account entity result");
        };

        // Test balance for empty account (should be 0)
        let balance_result = GetBalance::new()
            .user_id(user.id)
            .account_id(account.id)
            .run()
            .await
            .expect("Test operation failed");

        if let Some(CmdResult::Rational(balance)) = balance_result {
            assert_eq!(
                balance,
                Rational64::new(0, 1),
                "Empty account balance should be 0, got: {balance}"
            );
        } else {
            panic!("Expected rational balance result for empty account");
        }
    }

    #[local_db_sqlx_test]
    async fn test_get_balance_mixed_currencies_error(pool: PgPool) {
        setup().await;
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Create two commodities
        let usd_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await
            .expect("Test operation failed");

        let eur_result = CreateCommodity::new()
            .symbol("EUR".to_string())
            .name("Euro".to_string())
            .user_id(user.id)
            .run()
            .await
            .expect("Test operation failed");

        let usd_id = if let Some(CmdResult::String(id)) = usd_result {
            Uuid::parse_str(&id).expect("Failed to parse USD commodity ID")
        } else {
            panic!("Expected USD commodity ID");
        };

        let eur_id = if let Some(CmdResult::String(id)) = eur_result {
            Uuid::parse_str(&id).expect("Failed to parse EUR commodity ID")
        } else {
            panic!("Expected EUR commodity ID");
        };

        // Create three accounts
        let mixed_account = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("Mixed Currency Account".to_string())
                .user_id(user.id)
                .run()
                .await
                .expect("Test operation failed")
        {
            account
        } else {
            panic!("Expected mixed account entity result");
        };

        let usd_account = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("USD Account".to_string())
                .user_id(user.id)
                .run()
                .await
                .expect("Test operation failed")
        {
            account
        } else {
            panic!("Expected USD account entity result");
        };

        let eur_account = if let Some(CmdResult::Entity(FinanceEntity::Account(account))) =
            CreateAccount::new()
                .name("EUR Account".to_string())
                .user_id(user.id)
                .run()
                .await
                .expect("Test operation failed")
        {
            account
        } else {
            panic!("Expected EUR account entity result");
        };

        // Create first transaction: 100 USD to mixed_account
        let tx1_id = Uuid::new_v4();
        let now = Utc::now();

        let splits1 = vec![
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx1_id,
                account_id: mixed_account.id,
                commodity_id: usd_id,
                value_num: 100,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx1_id,
                account_id: usd_account.id,
                commodity_id: usd_id,
                value_num: -100,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
        ];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits1)
            .id(tx1_id)
            .post_date(now)
            .enter_date(now)
            .run()
            .await
            .expect("Test operation failed");

        // Create second transaction: 50 EUR to mixed_account
        let tx2_id = Uuid::new_v4();

        let splits2 = vec![
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx2_id,
                account_id: mixed_account.id,
                commodity_id: eur_id,
                value_num: 50,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx2_id,
                account_id: eur_account.id,
                commodity_id: eur_id,
                value_num: -50,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
        ];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits2)
            .id(tx2_id)
            .post_date(now)
            .enter_date(now)
            .run()
            .await
            .expect("Test operation failed");

        // Test balance for mixed_account without commodity_id (should return MultiCurrencyBalance)
        let balance_result = GetBalance::new()
            .user_id(user.id)
            .account_id(mixed_account.id)
            .run()
            .await
            .expect("Test operation failed");

        if let Some(CmdResult::MultiCurrencyBalance(balances)) = balance_result {
            assert_eq!(balances.len(), 2, "Expected two currency balances");
            // USD balance should be 100, EUR balance should be 50
            for (commodity, balance) in balances {
                match balance.to_integer() {
                    100 => {
                        // USD balance
                        assert_eq!(commodity.id, usd_id);
                    }
                    50 => {
                        // EUR balance
                        assert_eq!(commodity.id, eur_id);
                    }
                    _ => panic!("Unexpected balance: {balance}"),
                }
            }
        } else {
            panic!("Expected MultiCurrencyBalance result for mixed currencies");
        }

        // Test balance with specific commodity_id (should error due to missing conversion)
        let balance_result_usd = GetBalance::new()
            .user_id(user.id)
            .account_id(mixed_account.id)
            .commodity_id(usd_id)
            .run()
            .await;

        // Should fail due to missing EUR->USD price conversion
        assert!(
            balance_result_usd.is_err(),
            "Expected error for missing EUR->USD conversion"
        );

        // Verify it's the right kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance_result_usd
        {
            assert_eq!(from_commodity, "EUR");
            assert_eq!(to_commodity, "USD");
        } else {
            panic!("Expected MissingConversion error");
        }
    }

    #[local_db_sqlx_test]
    async fn test_cross_account_balance_isolation(pool: PgPool) {
        setup().await;
        let user = USER.get().unwrap();
        user.commit()
            .await
            .expect("Failed to commit user to database");

        // Create commodities
        let usd_result = CreateCommodity::new()
            .symbol("USD".to_string())
            .name("US Dollar".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let eur_result = CreateCommodity::new()
            .symbol("EUR".to_string())
            .name("Euro".to_string())
            .user_id(user.id)
            .run()
            .await
            .unwrap();

        let usd_id = if let Some(CmdResult::String(id)) = usd_result {
            Uuid::parse_str(&id).unwrap()
        } else {
            panic!("Expected USD commodity ID");
        };

        let eur_id = if let Some(CmdResult::String(id)) = eur_result {
            Uuid::parse_str(&id).unwrap()
        } else {
            panic!("Expected EUR commodity ID");
        };

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

        let account2 = if let Some(CmdResult::Entity(FinanceEntity::Account(acc))) =
            CreateAccount::new()
                .name("Account2".to_string())
                .user_id(user.id)
                .run()
                .await
                .unwrap()
        {
            acc
        } else {
            panic!("Expected account2");
        };

        // Transaction 1: 100 USD from account1 to account2
        let tx1_id = Uuid::new_v4();
        let now = DateTime::<Utc>::from_timestamp(1640995200, 0).unwrap();

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

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits1)
            .id(tx1_id)
            .post_date(now)
            .enter_date(now)
            .run()
            .await
            .unwrap();

        // Check initial balances (should be simple single-currency)
        let balance1_initial = GetBalance::new()
            .user_id(user.id)
            .account_id(account1.id)
            .run()
            .await
            .unwrap();

        let balance2_initial = GetBalance::new()
            .user_id(user.id)
            .account_id(account2.id)
            .run()
            .await
            .unwrap();

        // Both should have simple USD balances
        match balance1_initial {
            Some(CmdResult::Rational(balance)) => {
                assert_eq!(balance, Rational64::new(-100, 1));
            }
            _ => panic!("Expected Rational balance result for account1"),
        }
        match balance2_initial {
            Some(CmdResult::Rational(balance)) => {
                assert_eq!(balance, Rational64::new(100, 1));
            }
            _ => panic!("Expected Rational balance result for account2"),
        }

        // Transaction 2: Add EUR transaction - 50 EUR from account1 to account2
        let tx2_id = Uuid::new_v4();
        let later = DateTime::<Utc>::from_timestamp(1640995800, 0).unwrap(); // 10 minutes later

        let splits2 = vec![
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx2_id,
                account_id: account1.id,
                commodity_id: eur_id,
                value_num: -50,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx2_id,
                account_id: account2.id,
                commodity_id: eur_id,
                value_num: 50,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
        ];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits2)
            .id(tx2_id)
            .post_date(later)
            .enter_date(later)
            .run()
            .await
            .unwrap();

        // Now both accounts have mixed currencies and should return MultiCurrencyBalance
        let balance1_after = GetBalance::new()
            .user_id(user.id)
            .account_id(account1.id)
            .run()
            .await
            .unwrap();

        let balance2_after = GetBalance::new()
            .user_id(user.id)
            .account_id(account2.id)
            .run()
            .await
            .unwrap();

        // Both should return MultiCurrencyBalance due to mixed currencies
        match balance1_after {
            Some(CmdResult::MultiCurrencyBalance(balances)) => {
                assert_eq!(
                    balances.len(),
                    2,
                    "Account1 should have two currency balances"
                );
            }
            _ => panic!("Expected MultiCurrencyBalance result for account1"),
        }
        match balance2_after {
            Some(CmdResult::MultiCurrencyBalance(balances)) => {
                assert_eq!(
                    balances.len(),
                    2,
                    "Account2 should have two currency balances"
                );
            }
            _ => panic!("Expected MultiCurrencyBalance result for account2"),
        }

        // Test Account2 balance in EUR before adding account1-only transaction
        let balance2_eur_before = GetBalance::new()
            .user_id(user.id)
            .account_id(account2.id)
            .commodity_id(eur_id)
            .run()
            .await;

        // Should fail due to missing USD->EUR price conversion
        assert!(
            balance2_eur_before.is_err(),
            "Expected error for missing USD->EUR conversion"
        );

        // Verify it's the right kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance2_eur_before
        {
            assert_eq!(from_commodity, "USD");
            assert_eq!(to_commodity, "EUR");
        } else {
            panic!("Expected MissingConversion error");
        }

        // Now add a third transaction affecting only account1 (not account2)
        let tx3_id = Uuid::new_v4();
        let even_later = DateTime::<Utc>::from_timestamp(1640996400, 0).unwrap(); // 20 minutes later

        let account3 = if let Some(CmdResult::Entity(FinanceEntity::Account(acc))) =
            CreateAccount::new()
                .name("Account3".to_string())
                .user_id(user.id)
                .run()
                .await
                .unwrap()
        {
            acc
        } else {
            panic!("Expected account3");
        };

        let splits3 = vec![
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx3_id,
                account_id: account1.id,
                commodity_id: usd_id,
                value_num: -25,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
            FinanceEntity::Split(Split {
                id: Uuid::new_v4(),
                tx_id: tx3_id,
                account_id: account3.id,
                commodity_id: usd_id,
                value_num: 25,
                value_denom: 1,
                reconcile_state: None,
                reconcile_date: None,
                lot_id: None,
            }),
        ];

        CreateTransaction::new()
            .user_id(user.id)
            .splits(splits3)
            .id(tx3_id)
            .post_date(even_later)
            .enter_date(even_later)
            .run()
            .await
            .unwrap();

        // CRITICAL TEST: Account2's balance should still fail due to missing USD->EUR conversion
        // This verifies that account1's new transaction doesn't affect account2's error condition
        let balance2_eur_after = GetBalance::new()
            .user_id(user.id)
            .account_id(account2.id)
            .commodity_id(eur_id)
            .run()
            .await;

        // Should still fail due to missing USD->EUR price conversion, unchanged by account1's new transaction
        assert!(
            balance2_eur_after.is_err(),
            "Expected error for missing USD->EUR conversion (account isolation test)"
        );

        // Verify it's still the same kind of error
        if let Err(CmdError::Finance(FinanceError::Balance(BalanceError::MissingConversion {
            from_commodity,
            to_commodity,
            ..
        }))) = balance2_eur_after
        {
            assert_eq!(from_commodity, "USD");
            assert_eq!(to_commodity, "EUR");
        } else {
            panic!("Expected MissingConversion error");
        }
    }
}