path: root/src/db.rs

use crate::model::Transaction;
use crate::investment::{Portfolio, Position, AccountType, AssetType};
use rusqlite::{Connection, params};
use std::collections::HashMap;
use chrono::NaiveDateTime;

pub fn insert_transactions(txns: &[Transaction]) -> anyhow::Result<usize> {
    let mut conn = Connection::open("spendr.db")?;

    // Create table with category column and unique constraint
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS transactions (
        id INTEGER PRIMARY KEY,
        date TEXT,
        description TEXT,
        amount REAL,
        account TEXT,
        category TEXT,
        UNIQUE(date, description, amount, account)
    );",
    )?;

    // Add category column if it doesn't exist (for existing databases)
    let _ = conn.execute("ALTER TABLE transactions ADD COLUMN category TEXT;", []);

    let tx = conn.transaction()?;

    let mut inserted_count = 0;
    {
        let mut stmt = tx.prepare(
            "INSERT OR IGNORE INTO transactions (date, description, amount, account, category)
         VALUES (?1, ?2, ?3, ?4, ?5)",
        )?;

        for t in txns {
            let changes = stmt.execute(params![
                t.date.format("%Y-%m-%d").to_string(),
                &t.description,
                t.amount,
                &t.account,
                &t.category,
            ])?;
            inserted_count += changes;
        }
    }

    tx.commit()?;
    Ok(inserted_count)
}

pub fn update_transaction_category(transaction_id: i32, category: &str) -> anyhow::Result<()> {
    let conn = Connection::open("spendr.db")?;
    conn.execute(
        "UPDATE transactions SET category = ?1 WHERE id = ?2",
        params![category, transaction_id],
    )?;
    Ok(())
}


pub fn get_spending_by_month() -> anyhow::Result<HashMap<String, f64>> {
    let conn = Connection::open("spendr.db")?;
    let mut stmt = conn.prepare(
        "SELECT strftime('%Y-%m', date) as month, SUM(amount) as total 
         FROM transactions 
         WHERE amount < 0 
         GROUP BY month 
         ORDER BY month DESC",
    )?;

    let mut spending = HashMap::new();
    let rows = stmt.query_map([], |row| {
        Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
    })?;

    for row in rows {
        let (month, total): (String, f64) = row?;
        spending.insert(month, total.abs());
    }

    Ok(spending)
}





pub fn set_budget(category: &str, amount: f64) -> anyhow::Result<()> {
    let conn = Connection::open("spendr.db")?;

    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS budgets (
        category TEXT PRIMARY KEY,
        amount REAL
    );",
    )?;

    conn.execute(
        "INSERT OR REPLACE INTO budgets (category, amount) VALUES (?1, ?2)",
        params![category, amount],
    )?;

    Ok(())
}

pub fn get_budget_status() -> anyhow::Result<Vec<(String, f64, f64, f64)>> {
    let conn = Connection::open("spendr.db")?;

    // Get current month spending by category
    let mut stmt = conn.prepare(
        "SELECT 
            COALESCE(b.category, t.category) as category,
            COALESCE(b.amount, 0) as budget,
            COALESCE(-SUM(t.amount), 0) as spent
         FROM budgets b
         LEFT JOIN transactions t ON b.category = t.category 
            AND strftime('%Y-%m', t.date) = strftime('%Y-%m', 'now')
            AND t.amount < 0
         GROUP BY COALESCE(b.category, t.category)
         HAVING COALESCE(b.amount, 0) > 0
         
         UNION
         
         SELECT 
            t.category,
            COALESCE(b.amount, 0) as budget,
            -SUM(t.amount) as spent
         FROM transactions t
         LEFT JOIN budgets b ON t.category = b.category
         WHERE strftime('%Y-%m', t.date) = strftime('%Y-%m', 'now')
            AND t.amount < 0
            AND b.category IS NULL
         GROUP BY t.category
         ORDER BY category",
    )?;

    let mut results = Vec::new();
    let rows = stmt.query_map([], |row| {
        let category: String = row.get(0)?;
        let budget: f64 = row.get(1)?;
        let spent: f64 = row.get(2)?;
        let remaining = budget - spent;
        Ok((category, budget, spent, remaining))
    })?;

    for row in rows {
        results.push(row?);
    }

    Ok(results)
}

// Date-filtered versions of existing functions
pub fn get_spending_by_category_filtered(
    from_date: Option<&str>,
    to_date: Option<&str>,
) -> anyhow::Result<HashMap<String, f64>> {
    let conn = Connection::open("spendr.db")?;

    let mut spending = HashMap::new();

    match (from_date, to_date) {
        (Some(from), Some(to)) => {
            let mut stmt = conn.prepare("SELECT category, SUM(amount) as total FROM transactions WHERE amount < 0 AND date >= ? AND date <= ? GROUP BY category ORDER BY total ASC")?;
            let rows = stmt.query_map(params![from, to], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
            })?;
            for row in rows {
                let (category, total): (String, f64) = row?;
                spending.insert(category, total.abs());
            }
        }
        (Some(from), None) => {
            let mut stmt = conn.prepare("SELECT category, SUM(amount) as total FROM transactions WHERE amount < 0 AND date >= ? GROUP BY category ORDER BY total ASC")?;
            let rows = stmt.query_map(params![from], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
            })?;
            for row in rows {
                let (category, total): (String, f64) = row?;
                spending.insert(category, total.abs());
            }
        }
        (None, Some(to)) => {
            let mut stmt = conn.prepare("SELECT category, SUM(amount) as total FROM transactions WHERE amount < 0 AND date <= ? GROUP BY category ORDER BY total ASC")?;
            let rows = stmt.query_map(params![to], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
            })?;
            for row in rows {
                let (category, total): (String, f64) = row?;
                spending.insert(category, total.abs());
            }
        }
        (None, None) => {
            let mut stmt = conn.prepare("SELECT category, SUM(amount) as total FROM transactions WHERE amount < 0 GROUP BY category ORDER BY total ASC")?;
            let rows = stmt.query_map([], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
            })?;
            for row in rows {
                let (category, total): (String, f64) = row?;
                spending.insert(category, total.abs());
            }
        }
    }

    Ok(spending)
}

pub fn get_income_vs_expenses_filtered(
    from_date: Option<&str>,
    to_date: Option<&str>,
) -> anyhow::Result<(f64, f64)> {
    let conn = Connection::open("spendr.db")?;

    match (from_date, to_date) {
        (Some(from), Some(to)) => {
            let mut stmt = conn.prepare("SELECT SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income, SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) as expenses FROM transactions WHERE date >= ? AND date <= ?")?;
            let row = stmt.query_row(params![from, to], |row| {
                Ok((
                    row.get::<_, Option<f64>>(0)?.unwrap_or(0.0),
                    row.get::<_, Option<f64>>(1)?.unwrap_or(0.0),
                ))
            })?;
            Ok(row)
        }
        (Some(from), None) => {
            let mut stmt = conn.prepare("SELECT SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income, SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) as expenses FROM transactions WHERE date >= ?")?;
            let row = stmt.query_row(params![from], |row| {
                Ok((
                    row.get::<_, Option<f64>>(0)?.unwrap_or(0.0),
                    row.get::<_, Option<f64>>(1)?.unwrap_or(0.0),
                ))
            })?;
            Ok(row)
        }
        (None, Some(to)) => {
            let mut stmt = conn.prepare("SELECT SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income, SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) as expenses FROM transactions WHERE date <= ?")?;
            let row = stmt.query_row(params![to], |row| {
                Ok((
                    row.get::<_, Option<f64>>(0)?.unwrap_or(0.0),
                    row.get::<_, Option<f64>>(1)?.unwrap_or(0.0),
                ))
            })?;
            Ok(row)
        }
        (None, None) => {
            let mut stmt = conn.prepare("SELECT SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income, SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) as expenses FROM transactions")?;
            let row = stmt.query_row([], |row| {
                Ok((
                    row.get::<_, Option<f64>>(0)?.unwrap_or(0.0),
                    row.get::<_, Option<f64>>(1)?.unwrap_or(0.0),
                ))
            })?;
            Ok(row)
        }
    }
}

fn transaction_from_row(row: &rusqlite::Row) -> rusqlite::Result<Transaction> {
    let date_str: String = row.get(1)?;
    Ok(Transaction {
        id: row.get(0)?,
        date: chrono::NaiveDate::parse_from_str(&date_str, "%Y-%m-%d").map_err(
            |_e| {
                rusqlite::Error::InvalidColumnType(
                    1,
                    "date".to_string(),
                    rusqlite::types::Type::Text,
                )
            },
        )?,
        description: row.get(2)?,
        amount: row.get(3)?,
        account: row.get(4)?,
        category: row.get(5)?,
    })
}

pub fn get_recent_transactions_filtered(
    limit: usize,
    from_date: Option<&str>,
    to_date: Option<&str>,
) -> anyhow::Result<Vec<Transaction>> {
    let conn = Connection::open("spendr.db")?;

    let mut transactions = Vec::new();

    match (from_date, to_date) {
        (Some(from), Some(to)) => {
            let mut stmt = conn.prepare("SELECT id, date, description, amount, account, category FROM transactions WHERE date >= ? AND date <= ? ORDER BY date DESC, rowid DESC LIMIT ?")?;
            let rows = stmt.query_map(params![from, to, limit], transaction_from_row)?;
            for row in rows {
                transactions.push(row?);
            }
        }
        (Some(from), None) => {
            let mut stmt = conn.prepare("SELECT id, date, description, amount, account, category FROM transactions WHERE date >= ? ORDER BY date DESC, rowid DESC LIMIT ?")?;
            let rows = stmt.query_map(params![from, limit], transaction_from_row)?;
            for row in rows {
                transactions.push(row?);
            }
        }
        (None, Some(to)) => {
            let mut stmt = conn.prepare("SELECT id, date, description, amount, account, category FROM transactions WHERE date <= ? ORDER BY date DESC, rowid DESC LIMIT ?")?;
            let rows = stmt.query_map(params![to, limit], transaction_from_row)?;
            for row in rows {
                transactions.push(row?);
            }
        }
        (None, None) => {
            let mut stmt = conn.prepare("SELECT id, date, description, amount, account, category FROM transactions ORDER BY date DESC, rowid DESC LIMIT ?")?;
            let rows = stmt.query_map(params![limit], transaction_from_row)?;
            for row in rows {
                transactions.push(row?);
            }
        }
    }

    Ok(transactions)
}

pub fn get_yearly_summary(
    year: Option<i32>,
) -> anyhow::Result<HashMap<String, HashMap<String, f64>>> {
    let conn = Connection::open("spendr.db")?;

    let mut yearly_data = HashMap::new();

    match year {
        Some(y) => {
            let mut stmt = conn.prepare("SELECT strftime('%Y', date) as year, category, SUM(amount) as total FROM transactions WHERE amount < 0 AND strftime('%Y', date) = ? GROUP BY year, category ORDER BY year DESC, total ASC")?;
            let rows = stmt.query_map(params![y.to_string()], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, f64>(2)?,
                ))
            })?;
            for row in rows {
                let (year, category, total): (String, String, f64) = row?;
                yearly_data
                    .entry(year)
                    .or_insert_with(HashMap::new)
                    .insert(category, total.abs());
            }
        }
        None => {
            let mut stmt = conn.prepare("SELECT strftime('%Y', date) as year, category, SUM(amount) as total FROM transactions WHERE amount < 0 GROUP BY year, category ORDER BY year DESC, total ASC")?;
            let rows = stmt.query_map([], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, f64>(2)?,
                ))
            })?;
            for row in rows {
                let (year, category, total): (String, String, f64) = row?;
                yearly_data
                    .entry(year)
                    .or_insert_with(HashMap::new)
                    .insert(category, total.abs());
            }
        }
    }

    Ok(yearly_data)
}

pub fn get_quarterly_summary(
    year: Option<i32>,
) -> anyhow::Result<HashMap<String, HashMap<String, f64>>> {
    let conn = Connection::open("spendr.db")?;

    let mut quarterly_data = HashMap::new();

    match year {
        Some(y) => {
            let mut stmt = conn.prepare("SELECT strftime('%Y', date) as year, CASE WHEN CAST(strftime('%m', date) AS INTEGER) <= 3 THEN 'Q1' WHEN CAST(strftime('%m', date) AS INTEGER) <= 6 THEN 'Q2' WHEN CAST(strftime('%m', date) AS INTEGER) <= 9 THEN 'Q3' ELSE 'Q4' END as quarter, category, SUM(amount) as total FROM transactions WHERE amount < 0 AND strftime('%Y', date) = ? GROUP BY year, quarter, category ORDER BY year DESC, quarter, total ASC")?;
            let rows = stmt.query_map(params![y.to_string()], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, String>(2)?,
                    row.get::<_, f64>(3)?,
                ))
            })?;
            for row in rows {
                let (year, quarter, category, total): (String, String, String, f64) = row?;
                let key = format!("{} {}", year, quarter);
                quarterly_data
                    .entry(key)
                    .or_insert_with(HashMap::new)
                    .insert(category, total.abs());
            }
        }
        None => {
            let mut stmt = conn.prepare("SELECT strftime('%Y', date) as year, CASE WHEN CAST(strftime('%m', date) AS INTEGER) <= 3 THEN 'Q1' WHEN CAST(strftime('%m', date) AS INTEGER) <= 6 THEN 'Q2' WHEN CAST(strftime('%m', date) AS INTEGER) <= 9 THEN 'Q3' ELSE 'Q4' END as quarter, category, SUM(amount) as total FROM transactions WHERE amount < 0 GROUP BY year, quarter, category ORDER BY year DESC, quarter, total ASC")?;
            let rows = stmt.query_map([], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, String>(2)?,
                    row.get::<_, f64>(3)?,
                ))
            })?;
            for row in rows {
                let (year, quarter, category, total): (String, String, String, f64) = row?;
                let key = format!("{} {}", year, quarter);
                quarterly_data
                    .entry(key)
                    .or_insert_with(HashMap::new)
                    .insert(category, total.abs());
            }
        }
    }

    Ok(quarterly_data)
}

// Investment Database Functions

pub fn create_investment_tables() -> anyhow::Result<()> {
    let conn = Connection::open("spendr.db")?;
    
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS portfolios (
            id INTEGER PRIMARY KEY,
            broker TEXT NOT NULL,
            account_id TEXT NOT NULL,
            account_type TEXT NOT NULL,
            cash_balance REAL NOT NULL,
            total_market_value REAL NOT NULL,
            total_book_value REAL NOT NULL,
            total_unrealized_pnl REAL NOT NULL,
            last_updated TEXT NOT NULL,
            UNIQUE(broker, account_id)
        );
        
        CREATE TABLE IF NOT EXISTS positions (
            id INTEGER PRIMARY KEY,
            portfolio_id INTEGER NOT NULL,
            symbol TEXT NOT NULL,
            name TEXT NOT NULL,
            asset_type TEXT NOT NULL,
            quantity REAL NOT NULL,
            market_price REAL NOT NULL,
            market_value REAL NOT NULL,
            book_value REAL NOT NULL,
            average_cost REAL NOT NULL,
            unrealized_pnl REAL NOT NULL,
            unrealized_pnl_percent REAL NOT NULL,
            currency TEXT NOT NULL,
            last_updated TEXT NOT NULL,
            FOREIGN KEY (portfolio_id) REFERENCES portfolios (id),
            UNIQUE(portfolio_id, symbol)
        );
        
        CREATE TABLE IF NOT EXISTS investment_transactions (
            id INTEGER PRIMARY KEY,
            transaction_id TEXT NOT NULL,
            date TEXT NOT NULL,
            symbol TEXT NOT NULL,
            transaction_type TEXT NOT NULL,
            quantity REAL NOT NULL,
            price REAL NOT NULL,
            amount REAL NOT NULL,
            fees REAL NOT NULL,
            currency TEXT NOT NULL,
            account_id TEXT NOT NULL,
            broker TEXT NOT NULL,
            UNIQUE(transaction_id, broker)
        );"
    )?;
    
    Ok(())
}

pub fn save_portfolios(portfolios: &[Portfolio]) -> anyhow::Result<()> {
    let mut conn = Connection::open("spendr.db")?;
    create_investment_tables()?;
    
    let tx = conn.transaction()?;
    
    // Clear existing data
    tx.execute("DELETE FROM positions", [])?;
    tx.execute("DELETE FROM portfolios", [])?;
    
    for portfolio in portfolios {
        // Insert portfolio
        let portfolio_id: i64 = tx.query_row(
            "INSERT INTO portfolios (broker, account_id, account_type, cash_balance, 
                total_market_value, total_book_value, total_unrealized_pnl, last_updated)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8) RETURNING id",
            params![
                portfolio.broker,
                portfolio.account_id,
                account_type_to_string(&portfolio.account_type),
                portfolio.cash_balance,
                portfolio.total_market_value,
                portfolio.total_book_value,
                portfolio.total_unrealized_pnl,
                portfolio.last_updated.format("%Y-%m-%d %H:%M:%S").to_string(),
            ],
            |row| Ok(row.get(0)?)
        )?;
        
        // Insert positions for this portfolio
        for position in &portfolio.positions {
            tx.execute(
                "INSERT INTO positions (portfolio_id, symbol, name, asset_type, quantity,
                    market_price, market_value, book_value, average_cost, unrealized_pnl,
                    unrealized_pnl_percent, currency, last_updated)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)",
                params![
                    portfolio_id,
                    position.symbol,
                    position.name,
                    asset_type_to_string(&position.asset_type),
                    position.quantity,
                    position.market_price,
                    position.market_value,
                    position.book_value,
                    position.average_cost,
                    position.unrealized_pnl,
                    position.unrealized_pnl_percent,
                    position.currency,
                    chrono::Utc::now().naive_utc().format("%Y-%m-%d %H:%M:%S").to_string(),
                ]
            )?;
        }
    }
    
    tx.commit()?;
    Ok(())
}

pub fn load_portfolios() -> anyhow::Result<Vec<Portfolio>> {
    let conn = Connection::open("spendr.db")?;
    create_investment_tables()?;
    
    let mut portfolios = Vec::new();
    
    // Load portfolios
    let mut stmt = conn.prepare(
        "SELECT id, broker, account_id, account_type, cash_balance,
            total_market_value, total_book_value, total_unrealized_pnl, last_updated
         FROM portfolios ORDER BY account_id"
    )?;
    
    let portfolio_rows = stmt.query_map([], |row| {
        Ok((
            row.get::<_, i64>(0)?,
            row.get::<_, String>(1)?,
            row.get::<_, String>(2)?,
            row.get::<_, String>(3)?,
            row.get::<_, f64>(4)?,
            row.get::<_, f64>(5)?,
            row.get::<_, f64>(6)?,
            row.get::<_, f64>(7)?,
            row.get::<_, String>(8)?,
        ))
    })?;
    
    for portfolio_row in portfolio_rows {
        let (portfolio_id, broker, account_id, account_type_str, cash_balance,
             total_market_value, total_book_value, total_unrealized_pnl, last_updated_str) = portfolio_row?;
        
        // Load positions for this portfolio
        let mut positions = Vec::new();
        let mut pos_stmt = conn.prepare(
            "SELECT symbol, name, asset_type, quantity, market_price, market_value,
                book_value, average_cost, unrealized_pnl, unrealized_pnl_percent, currency
             FROM positions WHERE portfolio_id = ?1 ORDER BY symbol"
        )?;
        
        let position_rows = pos_stmt.query_map(params![portfolio_id], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, f64>(3)?,
                row.get::<_, f64>(4)?,
                row.get::<_, f64>(5)?,
                row.get::<_, f64>(6)?,
                row.get::<_, f64>(7)?,
                row.get::<_, f64>(8)?,
                row.get::<_, f64>(9)?,
                row.get::<_, String>(10)?,
            ))
        })?;
        
        for position_row in position_rows {
            let (symbol, name, asset_type_str, quantity, market_price, market_value,
                 book_value, average_cost, unrealized_pnl, unrealized_pnl_percent, currency) = position_row?;
            
            positions.push(Position {
                symbol,
                name,
                asset_type: string_to_asset_type(&asset_type_str),
                quantity,
                market_price,
                market_value,
                book_value,
                average_cost,
                unrealized_pnl,
                unrealized_pnl_percent,
                currency,
            });
        }
        
        portfolios.push(Portfolio {
            broker,
            account_id,
            account_type: string_to_account_type(&account_type_str),
            positions,
            cash_balance,
            total_market_value,
            total_book_value,
            total_unrealized_pnl,
            last_updated: NaiveDateTime::parse_from_str(&last_updated_str, "%Y-%m-%d %H:%M:%S")?,
        });
    }
    
    Ok(portfolios)
}

fn account_type_to_string(account_type: &AccountType) -> String {
    match account_type {
        AccountType::Taxable => "Taxable".to_string(),
        AccountType::RRSP => "RRSP".to_string(),
        AccountType::TFSA => "TFSA".to_string(),
        AccountType::RESP => "RESP".to_string(),
        AccountType::Margin => "Margin".to_string(),
        AccountType::Cash => "Cash".to_string(),
        AccountType::Other(s) => format!("Other({})", s),
    }
}

fn string_to_account_type(s: &str) -> AccountType {
    match s {
        "Taxable" => AccountType::Taxable,
        "RRSP" => AccountType::RRSP,
        "TFSA" => AccountType::TFSA,
        "RESP" => AccountType::RESP,
        "Margin" => AccountType::Margin,
        "Cash" => AccountType::Cash,
        s if s.starts_with("Other(") => {
            let inner = &s[6..s.len()-1];
            AccountType::Other(inner.to_string())
        },
        _ => AccountType::Other(s.to_string()),
    }
}

fn asset_type_to_string(asset_type: &AssetType) -> String {
    match asset_type {
        AssetType::Stock => "Stock".to_string(),
        AssetType::ETF => "ETF".to_string(),
        AssetType::MutualFund => "MutualFund".to_string(),
        AssetType::Bond => "Bond".to_string(),
        AssetType::Option => "Option".to_string(),
        AssetType::Crypto => "Crypto".to_string(),
        AssetType::Cash => "Cash".to_string(),
        AssetType::Other(s) => format!("Other({})", s),
    }
}

fn string_to_asset_type(s: &str) -> AssetType {
    match s {
        "Stock" => AssetType::Stock,
        "ETF" => AssetType::ETF,
        "MutualFund" => AssetType::MutualFund,
        "Bond" => AssetType::Bond,
        "Option" => AssetType::Option,
        "Crypto" => AssetType::Crypto,
        "Cash" => AssetType::Cash,
        s if s.starts_with("Other(") => {
            let inner = &s[6..s.len()-1];
            AssetType::Other(inner.to_string())
        },
        _ => AssetType::Other(s.to_string()),
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use chrono::NaiveDate;

    fn create_test_transactions() -> Vec<Transaction> {
        vec![
            Transaction {
                id: 0,
                date: NaiveDate::from_ymd_opt(2024, 6, 15).unwrap(),
                description: "WAL-MART #3011 CALGARY, AB".to_string(),
                amount: -27.26,
                account: "CIBC Mastercard".to_string(),
                category: Some("Groceries".to_string()),
            },
            Transaction {
                id: 0,
                date: NaiveDate::from_ymd_opt(2024, 6, 15).unwrap(),
                description: "SQ *EARLS DALHOUSIE Calgary, AB".to_string(),
                amount: -10.89,
                account: "CIBC Mastercard".to_string(),
                category: Some("Dining".to_string()),
            },
            Transaction {
                id: 0,
                date: NaiveDate::from_ymd_opt(2024, 6, 14).unwrap(),
                description: "PAYROLL DEPOSIT Gitlab Canada".to_string(),
                amount: 5578.40,
                account: "Simplii Financial".to_string(),
                category: Some("Income".to_string()),
            },
        ]
    }

    #[test]
    fn test_insert_transactions() {
        // Test with current implementation using default database
        let transactions = create_test_transactions();
        let result = insert_transactions(&transactions);

        assert!(result.is_ok());
        // Don't assert exact count since database may have existing data
        // Test just checks that the function doesn't panic
    }

    #[test]
    fn test_get_spending_by_category() {
        let transactions = create_test_transactions();
        let _ = insert_transactions(&transactions);

        let spending = get_spending_by_category_filtered(None, None).unwrap();

        // Should contain our test categories - amounts may be higher due to existing data
        assert!(spending.contains_key("Groceries"));
        assert!(spending.contains_key("Dining"));
        assert!(!spending.contains_key("Income")); // Income is positive, so not in spending

        // Amounts should be at least our test amounts (may be higher due to existing data)
        assert!(spending.get("Groceries").unwrap() >= &27.26);
        assert!(spending.get("Dining").unwrap() >= &10.89);
    }

    #[test]
    fn test_get_income_vs_expenses() {
        let transactions = create_test_transactions();
        let _ = insert_transactions(&transactions);

        let (income, expenses) = get_income_vs_expenses_filtered(None, None).unwrap();

        // Income should be the positive amount
        assert!(income >= 5578.40);

        // Expenses should be the sum of negative amounts (made positive)
        assert!(expenses >= 38.15); // 27.26 + 10.89
    }

    #[test]
    fn test_get_recent_transactions() {
        let transactions = create_test_transactions();
        let _ = insert_transactions(&transactions);

        let recent = get_recent_transactions_filtered(2, None, None).unwrap();

        // Should get 2 most recent transactions
        assert_eq!(recent.len(), 2);

        // Should be ordered by date DESC
        assert!(recent[0].date >= recent[1].date);
    }

    #[test]
    fn test_set_and_get_budget() {
        let result = set_budget("Groceries", 500.0);
        assert!(result.is_ok());

        let result = set_budget("Dining", 300.0);
        assert!(result.is_ok());

        // Note: get_budget_status requires current month data, so this test might not show budgets
        // unless we have transactions in the current month
        let budget_status = get_budget_status().unwrap();
        // Just check that it doesn't error
        assert!(budget_status.is_empty() || !budget_status.is_empty());
    }

    #[test]
    fn test_empty_database_operations() {
        // Note: These tests use the same database as the application,
        // so they may not be empty if real data exists
        let spending = get_spending_by_category_filtered(None, None).unwrap();
        // Just check that it doesn't error
        assert!(spending.is_empty() || !spending.is_empty());

        let (income, expenses) = get_income_vs_expenses_filtered(None, None).unwrap();
        assert!(income >= 0.0);
        assert!(expenses >= 0.0);

        let recent = get_recent_transactions_filtered(10, None, None).unwrap();
        // Just check that it doesn't error
        assert!(recent.is_empty() || !recent.is_empty());

        let budget_status = get_budget_status().unwrap();
        // Just check that it doesn't error
        assert!(budget_status.is_empty() || !budget_status.is_empty());
    }
}