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

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

            // Build the account locally (you still have `commodity` in a local variable).

            let account = Account::builder()

                .id(row.id)

                .build()

                .expect("Account built with all required fields");

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

                    .collect();

            // Now push them, no need to borrow from the vector.

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

        }

        Ok(Some(CmdResult::TaggedEntities(tagged_accounts)))

    }

command! {

    GetAccount {

        #[required]

        user_id: Uuid,

        #[optional]

        account_id: Uuid,

        #[optional]

        account_name: String,

    } => {

        let user = User { id: user_id };

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

                .id(row.id)

                .build()

                .expect("Account built with all required fields");

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

                    .collect();

            let tagged_account = vec![(FinanceEntity::Account(account), tags)];

            Ok(Some(CmdResult::TaggedEntities(tagged_account)))

        } else {

            // No account found

            Ok(Some(CmdResult::TaggedEntities(vec![])))

        }

    }

command! {

    GetAccountCommodities {

        #[required]

        user_id: Uuid,

        #[required]

        account_id: Uuid,

    } => {

        let user = User { id: user_id };

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

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

                        match (split_data.price_num, split_data.price_denom) {

                            (Some(price_num), Some(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;

                            }

                            _ => {

                                // 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?

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

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

                    }

                    // Convert to sorted vector (sort by symbol)

                    let mut result: Vec<(Commodity, Rational64)> = balances_by_commodity

                        .into_values()

                        .collect();

                        // Sort by commodity_id since we don't have symbol in Commodity struct

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

                #[cfg(feature = "testlog")]

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

            .fraction(1.into())

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

            fraction: 1,

        };

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

            .fraction(1.into())

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

            fraction: 1,

        };

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

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

            .fraction(1.into())

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

            .fraction(1.into())

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

            .fraction(1.into())

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

            .fraction(1.into())

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

            .fraction(1.into())

            .symbol("USD".to_string())

            .name("US Dollar".to_string())

            .user_id(user.id)

            .run()

            .await

            .unwrap();

        let eur_result = CreateCommodity::new()

            .fraction(1.into())

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

            .fraction(1.into())

            .symbol("USD".to_string())

            .name("US Dollar".to_string())

            .user_id(user.id)

            .run()

            .await

            .unwrap();

        let eur_result = CreateCommodity::new()

            .fraction(1.into())

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