rustledger_query/

executor.rs

//! BQL Query Executor.
//!
//! Executes parsed BQL queries against a set of Beancount directives.

use std::cell::RefCell;
use std::collections::{HashMap, HashSet};
use std::hash::{Hash, Hasher};

use chrono::Datelike;
use regex::Regex;
use rust_decimal::Decimal;
use rustledger_core::{
    Amount, Directive, InternedStr, Inventory, NaiveDate, Position, Transaction,
};

use crate::ast::{
    BalancesQuery, BinaryOp, BinaryOperator, Expr, FromClause, FunctionCall, JournalQuery, Literal,
    OrderSpec, PrintQuery, Query, SelectQuery, SortDirection, Target, UnaryOp, UnaryOperator,
    WindowFunction,
};
use crate::error::QueryError;

/// A value that can result from evaluating a BQL expression.
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum Value {
    /// String value.
    String(String),
    /// Numeric value.
    Number(Decimal),
    /// Integer value.
    Integer(i64),
    /// Date value.
    Date(NaiveDate),
    /// Boolean value.
    Boolean(bool),
    /// Amount (number + currency).
    Amount(Amount),
    /// Position (amount + optional cost).
    Position(Position),
    /// Inventory (aggregated positions).
    Inventory(Inventory),
    /// Set of strings (tags, links).
    StringSet(Vec<String>),
    /// NULL value.
    Null,
}

impl Value {
    /// Compute a hash for this value.
    ///
    /// Note: This is not the standard Hash trait because some contained types
    /// (Decimal, Inventory) don't implement Hash. We use byte representations
    /// for those types.
    fn hash_value<H: Hasher>(&self, state: &mut H) {
        std::mem::discriminant(self).hash(state);
        match self {
            Self::String(s) => s.hash(state),
            Self::Number(d) => d.serialize().hash(state),
            Self::Integer(i) => i.hash(state),
            Self::Date(d) => {
                d.year().hash(state);
                d.month().hash(state);
                d.day().hash(state);
            }
            Self::Boolean(b) => b.hash(state),
            Self::Amount(a) => {
                a.number.serialize().hash(state);
                a.currency.as_str().hash(state);
            }
            Self::Position(p) => {
                p.units.number.serialize().hash(state);
                p.units.currency.as_str().hash(state);
                if let Some(cost) = &p.cost {
                    cost.number.serialize().hash(state);
                    cost.currency.as_str().hash(state);
                }
            }
            Self::Inventory(inv) => {
                for pos in inv.positions() {
                    pos.units.number.serialize().hash(state);
                    pos.units.currency.as_str().hash(state);
                    if let Some(cost) = &pos.cost {
                        cost.number.serialize().hash(state);
                        cost.currency.as_str().hash(state);
                    }
                }
            }
            Self::StringSet(ss) => {
                // Hash StringSet in a canonical, order-independent way by sorting first.
                let mut sorted = ss.clone();
                sorted.sort();
                for s in &sorted {
                    s.hash(state);
                }
            }
            Self::Null => {}
        }
    }
}

/// Compute a hash for a row (for DISTINCT deduplication).
fn hash_row(row: &Row) -> u64 {
    use std::collections::hash_map::DefaultHasher;
    let mut hasher = DefaultHasher::new();
    for value in row {
        value.hash_value(&mut hasher);
    }
    hasher.finish()
}

/// Compute a hash for a single value (for PIVOT lookups).
fn hash_single_value(value: &Value) -> u64 {
    use std::collections::hash_map::DefaultHasher;
    let mut hasher = DefaultHasher::new();
    value.hash_value(&mut hasher);
    hasher.finish()
}

/// A row of query results.
pub type Row = Vec<Value>;

/// Query result containing column names and rows.
#[derive(Debug, Clone)]
pub struct QueryResult {
    /// Column names.
    pub columns: Vec<String>,
    /// Result rows.
    pub rows: Vec<Row>,
}

impl QueryResult {
    /// Create a new empty result.
    pub const fn new(columns: Vec<String>) -> Self {
        Self {
            columns,
            rows: Vec::new(),
        }
    }

    /// Add a row to the result.
    pub fn add_row(&mut self, row: Row) {
        self.rows.push(row);
    }

    /// Number of rows.
    pub fn len(&self) -> usize {
        self.rows.len()
    }

    /// Whether the result is empty.
    pub fn is_empty(&self) -> bool {
        self.rows.is_empty()
    }
}

/// Context for a single posting being evaluated.
#[derive(Debug)]
pub struct PostingContext<'a> {
    /// The transaction this posting belongs to.
    pub transaction: &'a Transaction,
    /// The posting index within the transaction.
    pub posting_index: usize,
    /// Running balance after this posting (optional).
    pub balance: Option<Inventory>,
}

/// Context for window function evaluation.
#[derive(Debug, Clone)]
pub struct WindowContext {
    /// Row number within the partition (1-based).
    pub row_number: usize,
    /// Rank within the partition (1-based, ties get same rank).
    pub rank: usize,
    /// Dense rank within the partition (1-based, no gaps after ties).
    pub dense_rank: usize,
}

/// Query executor.
pub struct Executor<'a> {
    /// All directives to query over.
    directives: &'a [Directive],
    /// Account balances (built up during query).
    balances: HashMap<InternedStr, Inventory>,
    /// Price database for `VALUE()` conversions.
    price_db: crate::price::PriceDatabase,
    /// Target currency for `VALUE()` conversions.
    target_currency: Option<String>,
    /// Cache for compiled regex patterns.
    regex_cache: RefCell<HashMap<String, Option<Regex>>>,
}

impl<'a> Executor<'a> {
    /// Create a new executor with the given directives.
    pub fn new(directives: &'a [Directive]) -> Self {
        let price_db = crate::price::PriceDatabase::from_directives(directives);
        Self {
            directives,
            balances: HashMap::new(),
            price_db,
            target_currency: None,
            regex_cache: RefCell::new(HashMap::new()),
        }
    }

    /// Get or compile a regex pattern from the cache.
    ///
    /// Returns `Some(Regex)` if the pattern is valid, `None` if it's invalid.
    /// Invalid patterns are cached as `None` to avoid repeated compilation attempts.
    fn get_or_compile_regex(&self, pattern: &str) -> Option<Regex> {
        let mut cache = self.regex_cache.borrow_mut();
        if let Some(cached) = cache.get(pattern) {
            return cached.clone();
        }
        let compiled = Regex::new(pattern).ok();
        cache.insert(pattern.to_string(), compiled.clone());
        compiled
    }

    /// Get or compile a regex pattern, returning an error if invalid.
    fn require_regex(&self, pattern: &str) -> Result<Regex, QueryError> {
        self.get_or_compile_regex(pattern)
            .ok_or_else(|| QueryError::Type(format!("invalid regex: {pattern}")))
    }

    /// Set the target currency for `VALUE()` conversions.
    pub fn set_target_currency(&mut self, currency: impl Into<String>) {
        self.target_currency = Some(currency.into());
    }

    /// Execute a query and return the results.
    ///
    /// # Errors
    ///
    /// Returns [`QueryError`] in the following cases:
    ///
    /// - [`QueryError::UnknownColumn`] - A referenced column name doesn't exist
    /// - [`QueryError::UnknownFunction`] - An unknown function is called
    /// - [`QueryError::InvalidArguments`] - Function called with wrong arguments
    /// - [`QueryError::Type`] - Type mismatch in expression (e.g., comparing string to number)
    /// - [`QueryError::Aggregation`] - Error in aggregate function (SUM, COUNT, etc.)
    /// - [`QueryError::Evaluation`] - General expression evaluation error
    pub fn execute(&mut self, query: &Query) -> Result<QueryResult, QueryError> {
        match query {
            Query::Select(select) => self.execute_select(select),
            Query::Journal(journal) => self.execute_journal(journal),
            Query::Balances(balances) => self.execute_balances(balances),
            Query::Print(print) => self.execute_print(print),
        }
    }

    /// Execute a SELECT query.
    fn execute_select(&self, query: &SelectQuery) -> Result<QueryResult, QueryError> {
        // Check if we have a subquery
        if let Some(from) = &query.from {
            if let Some(subquery) = &from.subquery {
                return self.execute_select_from_subquery(query, subquery);
            }
        }

        // Determine column names
        let column_names = self.resolve_column_names(&query.targets)?;
        let mut result = QueryResult::new(column_names.clone());

        // Collect matching postings
        let postings = self.collect_postings(query.from.as_ref(), query.where_clause.as_ref())?;

        // Check if this is an aggregate query
        let is_aggregate = query
            .targets
            .iter()
            .any(|t| Self::is_aggregate_expr(&t.expr));

        if is_aggregate {
            // Group and aggregate
            let grouped = self.group_postings(&postings, query.group_by.as_ref())?;
            for (_, group) in grouped {
                let row = self.evaluate_aggregate_row(&query.targets, &group)?;

                // Apply HAVING filter on aggregated row
                if let Some(having_expr) = &query.having {
                    if !self.evaluate_having_filter(
                        having_expr,
                        &row,
                        &column_names,
                        &query.targets,
                        &group,
                    )? {
                        continue;
                    }
                }

                result.add_row(row);
            }
        } else {
            // Check if query has window functions
            let has_windows = Self::has_window_functions(&query.targets);
            let window_contexts = if has_windows {
                if let Some(wf) = Self::find_window_function(&query.targets) {
                    Some(self.compute_window_contexts(&postings, wf)?)
                } else {
                    None
                }
            } else {
                None
            };

            // Simple query - one row per posting
            // Use HashSet for O(1) DISTINCT deduplication instead of O(n) contains()
            let mut seen_hashes: HashSet<u64> = if query.distinct {
                HashSet::with_capacity(postings.len())
            } else {
                HashSet::new()
            };

            for (i, ctx) in postings.iter().enumerate() {
                let row = if let Some(ref wctxs) = window_contexts {
                    self.evaluate_row_with_window(&query.targets, ctx, Some(&wctxs[i]))?
                } else {
                    self.evaluate_row(&query.targets, ctx)?
                };
                if query.distinct {
                    // O(1) hash-based deduplication
                    let row_hash = hash_row(&row);
                    if seen_hashes.insert(row_hash) {
                        result.add_row(row);
                    }
                } else {
                    result.add_row(row);
                }
            }
        }

        // Apply PIVOT BY transformation
        if let Some(pivot_exprs) = &query.pivot_by {
            result = self.apply_pivot(&result, pivot_exprs, &query.targets)?;
        }

        // Apply ORDER BY
        if let Some(order_by) = &query.order_by {
            self.sort_results(&mut result, order_by)?;
        }

        // Apply LIMIT
        if let Some(limit) = query.limit {
            result.rows.truncate(limit as usize);
        }

        Ok(result)
    }

    /// Execute a SELECT query that sources from a subquery.
    fn execute_select_from_subquery(
        &self,
        outer_query: &SelectQuery,
        inner_query: &SelectQuery,
    ) -> Result<QueryResult, QueryError> {
        // Execute the inner query first
        let inner_result = self.execute_select(inner_query)?;

        // Build a column name -> index mapping for the inner result
        let inner_column_map: HashMap<String, usize> = inner_result
            .columns
            .iter()
            .enumerate()
            .map(|(i, name)| (name.to_lowercase(), i))
            .collect();

        // Determine outer column names
        let outer_column_names =
            self.resolve_subquery_column_names(&outer_query.targets, &inner_result.columns)?;
        let mut result = QueryResult::new(outer_column_names);

        // Use HashSet for O(1) DISTINCT deduplication
        let mut seen_hashes: HashSet<u64> = if outer_query.distinct {
            HashSet::with_capacity(inner_result.rows.len())
        } else {
            HashSet::new()
        };

        // Process each row from the inner result
        for inner_row in &inner_result.rows {
            // Apply outer WHERE clause if present
            if let Some(where_expr) = &outer_query.where_clause {
                if !self.evaluate_subquery_filter(where_expr, inner_row, &inner_column_map)? {
                    continue;
                }
            }

            // Evaluate outer targets
            let outer_row =
                self.evaluate_subquery_row(&outer_query.targets, inner_row, &inner_column_map)?;

            if outer_query.distinct {
                // O(1) hash-based deduplication
                let row_hash = hash_row(&outer_row);
                if seen_hashes.insert(row_hash) {
                    result.add_row(outer_row);
                }
            } else {
                result.add_row(outer_row);
            }
        }

        // Apply ORDER BY
        if let Some(order_by) = &outer_query.order_by {
            self.sort_results(&mut result, order_by)?;
        }

        // Apply LIMIT
        if let Some(limit) = outer_query.limit {
            result.rows.truncate(limit as usize);
        }

        Ok(result)
    }

    /// Resolve column names for a query from a subquery.
    fn resolve_subquery_column_names(
        &self,
        targets: &[Target],
        inner_columns: &[String],
    ) -> Result<Vec<String>, QueryError> {
        let mut names = Vec::new();
        for (i, target) in targets.iter().enumerate() {
            if let Some(alias) = &target.alias {
                names.push(alias.clone());
            } else if matches!(target.expr, Expr::Wildcard) {
                // Expand wildcard to all inner columns
                names.extend(inner_columns.iter().cloned());
            } else {
                names.push(self.expr_to_name(&target.expr, i));
            }
        }
        Ok(names)
    }

    /// Evaluate a filter expression against a subquery row.
    fn evaluate_subquery_filter(
        &self,
        expr: &Expr,
        row: &[Value],
        column_map: &HashMap<String, usize>,
    ) -> Result<bool, QueryError> {
        let val = self.evaluate_subquery_expr(expr, row, column_map)?;
        self.to_bool(&val)
    }

    /// Evaluate an expression against a subquery row.
    fn evaluate_subquery_expr(
        &self,
        expr: &Expr,
        row: &[Value],
        column_map: &HashMap<String, usize>,
    ) -> Result<Value, QueryError> {
        match expr {
            Expr::Wildcard => Err(QueryError::Evaluation(
                "Wildcard not allowed in expression context".to_string(),
            )),
            Expr::Column(name) => {
                let lower = name.to_lowercase();
                if let Some(&idx) = column_map.get(&lower) {
                    Ok(row.get(idx).cloned().unwrap_or(Value::Null))
                } else {
                    Err(QueryError::Evaluation(format!(
                        "Unknown column '{name}' in subquery result"
                    )))
                }
            }
            Expr::Literal(lit) => self.evaluate_literal(lit),
            Expr::Function(func) => {
                // Evaluate function arguments
                let args: Vec<Value> = func
                    .args
                    .iter()
                    .map(|a| self.evaluate_subquery_expr(a, row, column_map))
                    .collect::<Result<Vec<_>, _>>()?;
                self.evaluate_function_on_values(&func.name, &args)
            }
            Expr::BinaryOp(op) => {
                let left = self.evaluate_subquery_expr(&op.left, row, column_map)?;
                let right = self.evaluate_subquery_expr(&op.right, row, column_map)?;
                self.binary_op_on_values(op.op, &left, &right)
            }
            Expr::UnaryOp(op) => {
                let val = self.evaluate_subquery_expr(&op.operand, row, column_map)?;
                self.unary_op_on_value(op.op, &val)
            }
            Expr::Paren(inner) => self.evaluate_subquery_expr(inner, row, column_map),
            Expr::Window(_) => Err(QueryError::Evaluation(
                "Window functions not supported in subquery expressions".to_string(),
            )),
        }
    }

    /// Evaluate a row of targets against a subquery row.
    fn evaluate_subquery_row(
        &self,
        targets: &[Target],
        inner_row: &[Value],
        column_map: &HashMap<String, usize>,
    ) -> Result<Row, QueryError> {
        let mut row = Vec::new();
        for target in targets {
            if matches!(target.expr, Expr::Wildcard) {
                // Expand wildcard to all values from inner row
                row.extend(inner_row.iter().cloned());
            } else {
                row.push(self.evaluate_subquery_expr(&target.expr, inner_row, column_map)?);
            }
        }
        Ok(row)
    }

    /// Execute a JOURNAL query.
    fn execute_journal(&mut self, query: &JournalQuery) -> Result<QueryResult, QueryError> {
        // JOURNAL is a shorthand for SELECT with specific columns
        let account_pattern = &query.account_pattern;

        // Try to compile as regex (using cache)
        let account_regex = self.get_or_compile_regex(account_pattern);

        let columns = vec![
            "date".to_string(),
            "flag".to_string(),
            "payee".to_string(),
            "narration".to_string(),
            "account".to_string(),
            "position".to_string(),
            "balance".to_string(),
        ];
        let mut result = QueryResult::new(columns);

        // Filter transactions that touch the account
        for directive in self.directives {
            if let Directive::Transaction(txn) = directive {
                // Apply FROM clause filter if present
                if let Some(from) = &query.from {
                    if let Some(filter) = &from.filter {
                        if !self.evaluate_from_filter(filter, txn)? {
                            continue;
                        }
                    }
                }

                for posting in &txn.postings {
                    // Match account using regex or substring
                    let matches = if let Some(ref regex) = account_regex {
                        regex.is_match(&posting.account)
                    } else {
                        posting.account.contains(account_pattern)
                    };

                    if matches {
                        // Build the row
                        let balance = self.balances.entry(posting.account.clone()).or_default();

                        // Only process complete amounts
                        if let Some(units) = posting.amount() {
                            let pos = if let Some(cost_spec) = &posting.cost {
                                if let Some(cost) = cost_spec.resolve(units.number, txn.date) {
                                    Position::with_cost(units.clone(), cost)
                                } else {
                                    Position::simple(units.clone())
                                }
                            } else {
                                Position::simple(units.clone())
                            };
                            balance.add(pos.clone());
                        }

                        // Apply AT function if specified
                        let position_value = if let Some(at_func) = &query.at_function {
                            match at_func.to_uppercase().as_str() {
                                "COST" => {
                                    if let Some(units) = posting.amount() {
                                        if let Some(cost_spec) = &posting.cost {
                                            if let Some(cost) =
                                                cost_spec.resolve(units.number, txn.date)
                                            {
                                                let total = units.number * cost.number;
                                                Value::Amount(Amount::new(total, &cost.currency))
                                            } else {
                                                Value::Amount(units.clone())
                                            }
                                        } else {
                                            Value::Amount(units.clone())
                                        }
                                    } else {
                                        Value::Null
                                    }
                                }
                                "UNITS" => posting
                                    .amount()
                                    .map_or(Value::Null, |u| Value::Amount(u.clone())),
                                _ => posting
                                    .amount()
                                    .map_or(Value::Null, |u| Value::Amount(u.clone())),
                            }
                        } else {
                            posting
                                .amount()
                                .map_or(Value::Null, |u| Value::Amount(u.clone()))
                        };

                        let row = vec![
                            Value::Date(txn.date),
                            Value::String(txn.flag.to_string()),
                            Value::String(
                                txn.payee
                                    .as_ref()
                                    .map_or_else(String::new, ToString::to_string),
                            ),
                            Value::String(txn.narration.to_string()),
                            Value::String(posting.account.to_string()),
                            position_value,
                            Value::Inventory(balance.clone()),
                        ];
                        result.add_row(row);
                    }
                }
            }
        }

        Ok(result)
    }

    /// Execute a BALANCES query.
    fn execute_balances(&mut self, query: &BalancesQuery) -> Result<QueryResult, QueryError> {
        // Build up balances by processing all transactions (with FROM filtering)
        self.build_balances_with_filter(query.from.as_ref())?;

        let columns = vec!["account".to_string(), "balance".to_string()];
        let mut result = QueryResult::new(columns);

        // Sort accounts for consistent output
        let mut accounts: Vec<_> = self.balances.keys().collect();
        accounts.sort();

        for account in accounts {
            // Safety: account comes from self.balances.keys(), so it's guaranteed to exist
            let Some(balance) = self.balances.get(account) else {
                continue; // Defensive: skip if somehow the key disappeared
            };

            // Apply AT function if specified
            let balance_value = if let Some(at_func) = &query.at_function {
                match at_func.to_uppercase().as_str() {
                    "COST" => {
                        // Sum up cost basis
                        let cost_inventory = balance.at_cost();
                        Value::Inventory(cost_inventory)
                    }
                    "UNITS" => {
                        // Just the units (remove cost info)
                        let units_inventory = balance.at_units();
                        Value::Inventory(units_inventory)
                    }
                    _ => Value::Inventory(balance.clone()),
                }
            } else {
                Value::Inventory(balance.clone())
            };

            let row = vec![Value::String(account.to_string()), balance_value];
            result.add_row(row);
        }

        Ok(result)
    }

    /// Execute a PRINT query.
    fn execute_print(&self, query: &PrintQuery) -> Result<QueryResult, QueryError> {
        // PRINT outputs directives in Beancount format
        let columns = vec!["directive".to_string()];
        let mut result = QueryResult::new(columns);

        for directive in self.directives {
            // Apply FROM clause filter if present
            if let Some(from) = &query.from {
                if let Some(filter) = &from.filter {
                    // PRINT filters at transaction level
                    if let Directive::Transaction(txn) = directive {
                        if !self.evaluate_from_filter(filter, txn)? {
                            continue;
                        }
                    }
                }
            }

            // Format the directive as a string
            let formatted = self.format_directive(directive);
            result.add_row(vec![Value::String(formatted)]);
        }

        Ok(result)
    }

    /// Format a directive for PRINT output.
    fn format_directive(&self, directive: &Directive) -> String {
        match directive {
            Directive::Transaction(txn) => {
                let mut out = format!("{} {} ", txn.date, txn.flag);
                if let Some(payee) = &txn.payee {
                    out.push_str(&format!("\"{payee}\" "));
                }
                out.push_str(&format!("\"{}\"", txn.narration));

                for tag in &txn.tags {
                    out.push_str(&format!(" #{tag}"));
                }
                for link in &txn.links {
                    out.push_str(&format!(" ^{link}"));
                }
                out.push('\n');

                for posting in &txn.postings {
                    out.push_str(&format!("  {}", posting.account));
                    if let Some(units) = posting.amount() {
                        out.push_str(&format!("  {} {}", units.number, units.currency));
                    }
                    out.push('\n');
                }
                out
            }
            Directive::Balance(bal) => {
                format!(
                    "{} balance {} {} {}\n",
                    bal.date, bal.account, bal.amount.number, bal.amount.currency
                )
            }
            Directive::Open(open) => {
                let mut out = format!("{} open {}", open.date, open.account);
                if !open.currencies.is_empty() {
                    out.push_str(&format!(" {}", open.currencies.join(",")));
                }
                out.push('\n');
                out
            }
            Directive::Close(close) => {
                format!("{} close {}\n", close.date, close.account)
            }
            Directive::Commodity(comm) => {
                format!("{} commodity {}\n", comm.date, comm.currency)
            }
            Directive::Pad(pad) => {
                format!("{} pad {} {}\n", pad.date, pad.account, pad.source_account)
            }
            Directive::Event(event) => {
                format!(
                    "{} event \"{}\" \"{}\"\n",
                    event.date, event.event_type, event.value
                )
            }
            Directive::Query(query) => {
                format!(
                    "{} query \"{}\" \"{}\"\n",
                    query.date, query.name, query.query
                )
            }
            Directive::Note(note) => {
                format!("{} note {} \"{}\"\n", note.date, note.account, note.comment)
            }
            Directive::Document(doc) => {
                format!("{} document {} \"{}\"\n", doc.date, doc.account, doc.path)
            }
            Directive::Price(price) => {
                format!(
                    "{} price {} {} {}\n",
                    price.date, price.currency, price.amount.number, price.amount.currency
                )
            }
            Directive::Custom(custom) => {
                format!("{} custom \"{}\"\n", custom.date, custom.custom_type)
            }
        }
    }

    /// Build up account balances with optional FROM filtering.
    fn build_balances_with_filter(&mut self, from: Option<&FromClause>) -> Result<(), QueryError> {
        for directive in self.directives {
            if let Directive::Transaction(txn) = directive {
                // Apply FROM filter if present
                if let Some(from_clause) = from {
                    if let Some(filter) = &from_clause.filter {
                        if !self.evaluate_from_filter(filter, txn)? {
                            continue;
                        }
                    }
                }

                for posting in &txn.postings {
                    if let Some(units) = posting.amount() {
                        let balance = self.balances.entry(posting.account.clone()).or_default();

                        let pos = if let Some(cost_spec) = &posting.cost {
                            if let Some(cost) = cost_spec.resolve(units.number, txn.date) {
                                Position::with_cost(units.clone(), cost)
                            } else {
                                Position::simple(units.clone())
                            }
                        } else {
                            Position::simple(units.clone())
                        };
                        balance.add(pos);
                    }
                }
            }
        }
        Ok(())
    }

    /// Collect postings matching the FROM and WHERE clauses.
    fn collect_postings(
        &self,
        from: Option<&FromClause>,
        where_clause: Option<&Expr>,
    ) -> Result<Vec<PostingContext<'a>>, QueryError> {
        let mut postings = Vec::new();
        // Track running balance per account
        let mut running_balances: HashMap<InternedStr, Inventory> = HashMap::new();

        for directive in self.directives {
            if let Directive::Transaction(txn) = directive {
                // Check FROM clause (transaction-level filter)
                if let Some(from) = from {
                    // Apply date filters
                    if let Some(open_date) = from.open_on {
                        if txn.date < open_date {
                            // Update balances but don't include in results
                            for posting in &txn.postings {
                                if let Some(units) = posting.amount() {
                                    let balance = running_balances
                                        .entry(posting.account.clone())
                                        .or_default();
                                    balance.add(Position::simple(units.clone()));
                                }
                            }
                            continue;
                        }
                    }
                    if let Some(close_date) = from.close_on {
                        if txn.date > close_date {
                            continue;
                        }
                    }
                    // Apply filter expression
                    if let Some(filter) = &from.filter {
                        if !self.evaluate_from_filter(filter, txn)? {
                            continue;
                        }
                    }
                }

                // Add postings with running balance
                for (i, posting) in txn.postings.iter().enumerate() {
                    // Update running balance for this account
                    if let Some(units) = posting.amount() {
                        let balance = running_balances.entry(posting.account.clone()).or_default();
                        balance.add(Position::simple(units.clone()));
                    }

                    let ctx = PostingContext {
                        transaction: txn,
                        posting_index: i,
                        balance: running_balances.get(&posting.account).cloned(),
                    };

                    // Check WHERE clause (posting-level filter)
                    if let Some(where_expr) = where_clause {
                        if self.evaluate_predicate(where_expr, &ctx)? {
                            postings.push(ctx);
                        }
                    } else {
                        postings.push(ctx);
                    }
                }
            }
        }

        Ok(postings)
    }

    /// Evaluate a FROM filter on a transaction.
    fn evaluate_from_filter(&self, filter: &Expr, txn: &Transaction) -> Result<bool, QueryError> {
        // Handle special FROM predicates
        match filter {
            Expr::Function(func) => {
                if func.name.to_uppercase().as_str() == "HAS_ACCOUNT" {
                    if func.args.len() != 1 {
                        return Err(QueryError::InvalidArguments(
                            "has_account".to_string(),
                            "expected 1 argument".to_string(),
                        ));
                    }
                    let pattern = match &func.args[0] {
                        Expr::Literal(Literal::String(s)) => s.clone(),
                        Expr::Column(s) => s.clone(),
                        _ => {
                            return Err(QueryError::Type(
                                "has_account expects a string pattern".to_string(),
                            ));
                        }
                    };
                    // Check if any posting matches the account pattern (using cache)
                    let regex = self.require_regex(&pattern)?;
                    for posting in &txn.postings {
                        if regex.is_match(&posting.account) {
                            return Ok(true);
                        }
                    }
                    Ok(false)
                } else {
                    // For other functions, create a dummy context and evaluate
                    let dummy_ctx = PostingContext {
                        transaction: txn,
                        posting_index: 0,
                        balance: None,
                    };
                    self.evaluate_predicate(filter, &dummy_ctx)
                }
            }
            Expr::BinaryOp(op) => {
                // Handle YEAR = N, MONTH = N, etc.
                match (&op.left, &op.right) {
                    (Expr::Column(col), Expr::Literal(lit)) if col.to_uppercase() == "YEAR" => {
                        if let Literal::Integer(n) = lit {
                            let matches = txn.date.year() == *n as i32;
                            Ok(if op.op == BinaryOperator::Eq {
                                matches
                            } else {
                                !matches
                            })
                        } else {
                            Ok(false)
                        }
                    }
                    (Expr::Column(col), Expr::Literal(lit)) if col.to_uppercase() == "MONTH" => {
                        if let Literal::Integer(n) = lit {
                            let matches = txn.date.month() == *n as u32;
                            Ok(if op.op == BinaryOperator::Eq {
                                matches
                            } else {
                                !matches
                            })
                        } else {
                            Ok(false)
                        }
                    }
                    (Expr::Column(col), Expr::Literal(Literal::Date(d)))
                        if col.to_uppercase() == "DATE" =>
                    {
                        let matches = match op.op {
                            BinaryOperator::Eq => txn.date == *d,
                            BinaryOperator::Ne => txn.date != *d,
                            BinaryOperator::Lt => txn.date < *d,
                            BinaryOperator::Le => txn.date <= *d,
                            BinaryOperator::Gt => txn.date > *d,
                            BinaryOperator::Ge => txn.date >= *d,
                            _ => false,
                        };
                        Ok(matches)
                    }
                    _ => {
                        // Fall back to posting-level evaluation
                        let dummy_ctx = PostingContext {
                            transaction: txn,
                            posting_index: 0,
                            balance: None,
                        };
                        self.evaluate_predicate(filter, &dummy_ctx)
                    }
                }
            }
            _ => {
                // For other expressions, create a dummy context
                let dummy_ctx = PostingContext {
                    transaction: txn,
                    posting_index: 0,
                    balance: None,
                };
                self.evaluate_predicate(filter, &dummy_ctx)
            }
        }
    }

    /// Evaluate a predicate expression in the context of a posting.
    fn evaluate_predicate(&self, expr: &Expr, ctx: &PostingContext) -> Result<bool, QueryError> {
        let value = self.evaluate_expr(expr, ctx)?;
        match value {
            Value::Boolean(b) => Ok(b),
            Value::Null => Ok(false),
            _ => Err(QueryError::Type("expected boolean expression".to_string())),
        }
    }

    /// Evaluate an expression in the context of a posting.
    fn evaluate_expr(&self, expr: &Expr, ctx: &PostingContext) -> Result<Value, QueryError> {
        match expr {
            Expr::Wildcard => Ok(Value::Null), // Wildcard isn't really an expression
            Expr::Column(name) => self.evaluate_column(name, ctx),
            Expr::Literal(lit) => self.evaluate_literal(lit),
            Expr::Function(func) => self.evaluate_function(func, ctx),
            Expr::Window(_) => {
                // Window functions are evaluated at the query level, not per-posting
                // This case should not be reached; window values are pre-computed
                Err(QueryError::Evaluation(
                    "Window function cannot be evaluated in posting context".to_string(),
                ))
            }
            Expr::BinaryOp(op) => self.evaluate_binary_op(op, ctx),
            Expr::UnaryOp(op) => self.evaluate_unary_op(op, ctx),
            Expr::Paren(inner) => self.evaluate_expr(inner, ctx),
        }
    }

    /// Evaluate a column reference.
    fn evaluate_column(&self, name: &str, ctx: &PostingContext) -> Result<Value, QueryError> {
        let posting = &ctx.transaction.postings[ctx.posting_index];

        match name {
            "date" => Ok(Value::Date(ctx.transaction.date)),
            "account" => Ok(Value::String(posting.account.to_string())),
            "narration" => Ok(Value::String(ctx.transaction.narration.to_string())),
            "payee" => Ok(ctx
                .transaction
                .payee
                .as_ref()
                .map_or(Value::Null, |p| Value::String(p.to_string()))),
            "flag" => Ok(Value::String(ctx.transaction.flag.to_string())),
            "tags" => Ok(Value::StringSet(
                ctx.transaction
                    .tags
                    .iter()
                    .map(ToString::to_string)
                    .collect(),
            )),
            "links" => Ok(Value::StringSet(
                ctx.transaction
                    .links
                    .iter()
                    .map(ToString::to_string)
                    .collect(),
            )),
            "position" | "units" => Ok(posting
                .amount()
                .map_or(Value::Null, |u| Value::Amount(u.clone()))),
            "cost" => {
                // Get the cost of the posting
                if let Some(units) = posting.amount() {
                    if let Some(cost) = &posting.cost {
                        if let Some(number_per) = &cost.number_per {
                            if let Some(currency) = &cost.currency {
                                let total = units.number.abs() * number_per;
                                return Ok(Value::Amount(Amount::new(total, currency.clone())));
                            }
                        }
                    }
                }
                Ok(Value::Null)
            }
            "weight" => {
                // Weight is the amount used for transaction balancing
                // With cost: units × cost currency
                // Without cost: units amount
                if let Some(units) = posting.amount() {
                    if let Some(cost) = &posting.cost {
                        if let Some(number_per) = &cost.number_per {
                            if let Some(currency) = &cost.currency {
                                let total = units.number * number_per;
                                return Ok(Value::Amount(Amount::new(total, currency.clone())));
                            }
                        }
                    }
                    // No cost, use units
                    Ok(Value::Amount(units.clone()))
                } else {
                    Ok(Value::Null)
                }
            }
            "balance" => {
                // Running balance for this account
                if let Some(ref balance) = ctx.balance {
                    Ok(Value::Inventory(balance.clone()))
                } else {
                    Ok(Value::Null)
                }
            }
            "year" => Ok(Value::Integer(ctx.transaction.date.year().into())),
            "month" => Ok(Value::Integer(ctx.transaction.date.month().into())),
            "day" => Ok(Value::Integer(ctx.transaction.date.day().into())),
            _ => Err(QueryError::UnknownColumn(name.to_string())),
        }
    }

    /// Evaluate a literal.
    fn evaluate_literal(&self, lit: &Literal) -> Result<Value, QueryError> {
        Ok(match lit {
            Literal::String(s) => Value::String(s.clone()),
            Literal::Number(n) => Value::Number(*n),
            Literal::Integer(i) => Value::Integer(*i),
            Literal::Date(d) => Value::Date(*d),
            Literal::Boolean(b) => Value::Boolean(*b),
            Literal::Null => Value::Null,
        })
    }

    /// Evaluate a function call.
    ///
    /// Dispatches to specialized helper methods based on function category.
    fn evaluate_function(
        &self,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        let name = func.name.to_uppercase();
        match name.as_str() {
            // Date functions
            "YEAR" | "MONTH" | "DAY" | "WEEKDAY" | "QUARTER" | "YMONTH" | "TODAY" => {
                self.eval_date_function(&name, func, ctx)
            }
            // String functions
            "LENGTH" | "UPPER" | "LOWER" | "SUBSTR" | "SUBSTRING" | "TRIM" | "STARTSWITH"
            | "ENDSWITH" => self.eval_string_function(&name, func, ctx),
            // Account functions
            "PARENT" | "LEAF" | "ROOT" | "ACCOUNT_DEPTH" | "ACCOUNT_SORTKEY" => {
                self.eval_account_function(&name, func, ctx)
            }
            // Math functions
            "ABS" | "NEG" | "ROUND" | "SAFEDIV" => self.eval_math_function(&name, func, ctx),
            // Amount/Position functions
            "NUMBER" | "CURRENCY" | "GETITEM" | "GET" | "UNITS" | "COST" | "WEIGHT" | "VALUE" => {
                self.eval_position_function(&name, func, ctx)
            }
            // Utility functions
            "COALESCE" => self.eval_coalesce(func, ctx),
            // Aggregate functions return Null when evaluated on a single row
            // They're handled specially in aggregate evaluation
            "SUM" | "COUNT" | "MIN" | "MAX" | "FIRST" | "LAST" | "AVG" => Ok(Value::Null),
            _ => Err(QueryError::UnknownFunction(func.name.clone())),
        }
    }

    /// Evaluate date functions: `YEAR`, `MONTH`, `DAY`, `WEEKDAY`, `QUARTER`, `YMONTH`, `TODAY`.
    fn eval_date_function(
        &self,
        name: &str,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        if name == "TODAY" {
            if !func.args.is_empty() {
                return Err(QueryError::InvalidArguments(
                    "TODAY".to_string(),
                    "expected 0 arguments".to_string(),
                ));
            }
            return Ok(Value::Date(chrono::Local::now().date_naive()));
        }

        // All other date functions expect exactly 1 argument
        if func.args.len() != 1 {
            return Err(QueryError::InvalidArguments(
                name.to_string(),
                "expected 1 argument".to_string(),
            ));
        }

        let val = self.evaluate_expr(&func.args[0], ctx)?;
        let date = match val {
            Value::Date(d) => d,
            _ => return Err(QueryError::Type(format!("{name} expects a date"))),
        };

        match name {
            "YEAR" => Ok(Value::Integer(date.year().into())),
            "MONTH" => Ok(Value::Integer(date.month().into())),
            "DAY" => Ok(Value::Integer(date.day().into())),
            "WEEKDAY" => Ok(Value::Integer(date.weekday().num_days_from_monday().into())),
            "QUARTER" => {
                let quarter = (date.month() - 1) / 3 + 1;
                Ok(Value::Integer(quarter.into()))
            }
            "YMONTH" => Ok(Value::String(format!(
                "{:04}-{:02}",
                date.year(),
                date.month()
            ))),
            _ => unreachable!(),
        }
    }

    /// Evaluate string functions: `LENGTH`, `UPPER`, `LOWER`, `SUBSTR`, `TRIM`, `STARTSWITH`, `ENDSWITH`.
    fn eval_string_function(
        &self,
        name: &str,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        match name {
            "LENGTH" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => Ok(Value::Integer(s.len() as i64)),
                    Value::StringSet(s) => Ok(Value::Integer(s.len() as i64)),
                    _ => Err(QueryError::Type(
                        "LENGTH expects a string or set".to_string(),
                    )),
                }
            }
            "UPPER" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => Ok(Value::String(s.to_uppercase())),
                    _ => Err(QueryError::Type("UPPER expects a string".to_string())),
                }
            }
            "LOWER" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => Ok(Value::String(s.to_lowercase())),
                    _ => Err(QueryError::Type("LOWER expects a string".to_string())),
                }
            }
            "TRIM" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => Ok(Value::String(s.trim().to_string())),
                    _ => Err(QueryError::Type("TRIM expects a string".to_string())),
                }
            }
            "SUBSTR" | "SUBSTRING" => self.eval_substr(func, ctx),
            "STARTSWITH" => {
                Self::require_args(name, func, 2)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                let prefix = self.evaluate_expr(&func.args[1], ctx)?;
                match (val, prefix) {
                    (Value::String(s), Value::String(p)) => Ok(Value::Boolean(s.starts_with(&p))),
                    _ => Err(QueryError::Type(
                        "STARTSWITH expects two strings".to_string(),
                    )),
                }
            }
            "ENDSWITH" => {
                Self::require_args(name, func, 2)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                let suffix = self.evaluate_expr(&func.args[1], ctx)?;
                match (val, suffix) {
                    (Value::String(s), Value::String(p)) => Ok(Value::Boolean(s.ends_with(&p))),
                    _ => Err(QueryError::Type("ENDSWITH expects two strings".to_string())),
                }
            }
            _ => unreachable!(),
        }
    }

    /// Evaluate SUBSTR/SUBSTRING function.
    fn eval_substr(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        if func.args.len() < 2 || func.args.len() > 3 {
            return Err(QueryError::InvalidArguments(
                "SUBSTR".to_string(),
                "expected 2 or 3 arguments".to_string(),
            ));
        }

        let val = self.evaluate_expr(&func.args[0], ctx)?;
        let start = self.evaluate_expr(&func.args[1], ctx)?;
        let len = if func.args.len() == 3 {
            Some(self.evaluate_expr(&func.args[2], ctx)?)
        } else {
            None
        };

        match (val, start, len) {
            (Value::String(s), Value::Integer(start), None) => {
                let start = start.max(0) as usize;
                if start >= s.len() {
                    Ok(Value::String(String::new()))
                } else {
                    Ok(Value::String(s[start..].to_string()))
                }
            }
            (Value::String(s), Value::Integer(start), Some(Value::Integer(len))) => {
                let start = start.max(0) as usize;
                let len = len.max(0) as usize;
                if start >= s.len() {
                    Ok(Value::String(String::new()))
                } else {
                    let end = (start + len).min(s.len());
                    Ok(Value::String(s[start..end].to_string()))
                }
            }
            _ => Err(QueryError::Type(
                "SUBSTR expects (string, int, [int])".to_string(),
            )),
        }
    }

    /// Evaluate account functions: `PARENT`, `LEAF`, `ROOT`, `ACCOUNT_DEPTH`, `ACCOUNT_SORTKEY`.
    fn eval_account_function(
        &self,
        name: &str,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        match name {
            "PARENT" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => {
                        if let Some(idx) = s.rfind(':') {
                            Ok(Value::String(s[..idx].to_string()))
                        } else {
                            Ok(Value::Null)
                        }
                    }
                    _ => Err(QueryError::Type(
                        "PARENT expects an account string".to_string(),
                    )),
                }
            }
            "LEAF" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => {
                        if let Some(idx) = s.rfind(':') {
                            Ok(Value::String(s[idx + 1..].to_string()))
                        } else {
                            Ok(Value::String(s))
                        }
                    }
                    _ => Err(QueryError::Type(
                        "LEAF expects an account string".to_string(),
                    )),
                }
            }
            "ROOT" => self.eval_root(func, ctx),
            "ACCOUNT_DEPTH" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => {
                        let depth = s.chars().filter(|c| *c == ':').count() + 1;
                        Ok(Value::Integer(depth as i64))
                    }
                    _ => Err(QueryError::Type(
                        "ACCOUNT_DEPTH expects an account string".to_string(),
                    )),
                }
            }
            "ACCOUNT_SORTKEY" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::String(s) => Ok(Value::String(s)),
                    _ => Err(QueryError::Type(
                        "ACCOUNT_SORTKEY expects an account string".to_string(),
                    )),
                }
            }
            _ => unreachable!(),
        }
    }

    /// Evaluate ROOT function (takes 1-2 arguments).
    fn eval_root(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        if func.args.is_empty() || func.args.len() > 2 {
            return Err(QueryError::InvalidArguments(
                "ROOT".to_string(),
                "expected 1 or 2 arguments".to_string(),
            ));
        }

        let val = self.evaluate_expr(&func.args[0], ctx)?;
        let n = if func.args.len() == 2 {
            match self.evaluate_expr(&func.args[1], ctx)? {
                Value::Integer(i) => i as usize,
                _ => {
                    return Err(QueryError::Type(
                        "ROOT second arg must be integer".to_string(),
                    ));
                }
            }
        } else {
            1
        };

        match val {
            Value::String(s) => {
                let parts: Vec<&str> = s.split(':').collect();
                if n >= parts.len() {
                    Ok(Value::String(s))
                } else {
                    Ok(Value::String(parts[..n].join(":")))
                }
            }
            _ => Err(QueryError::Type(
                "ROOT expects an account string".to_string(),
            )),
        }
    }

    /// Evaluate math functions: `ABS`, `NEG`, `ROUND`, `SAFEDIV`.
    fn eval_math_function(
        &self,
        name: &str,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        match name {
            "ABS" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::Number(n) => Ok(Value::Number(n.abs())),
                    Value::Integer(i) => Ok(Value::Integer(i.abs())),
                    _ => Err(QueryError::Type("ABS expects a number".to_string())),
                }
            }
            "NEG" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::Number(n) => Ok(Value::Number(-n)),
                    Value::Integer(i) => Ok(Value::Integer(-i)),
                    _ => Err(QueryError::Type("NEG expects a number".to_string())),
                }
            }
            "ROUND" => self.eval_round(func, ctx),
            "SAFEDIV" => self.eval_safediv(func, ctx),
            _ => unreachable!(),
        }
    }

    /// Evaluate ROUND function (takes 1-2 arguments).
    fn eval_round(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        if func.args.is_empty() || func.args.len() > 2 {
            return Err(QueryError::InvalidArguments(
                "ROUND".to_string(),
                "expected 1 or 2 arguments".to_string(),
            ));
        }

        let val = self.evaluate_expr(&func.args[0], ctx)?;
        let decimals = if func.args.len() == 2 {
            match self.evaluate_expr(&func.args[1], ctx)? {
                Value::Integer(i) => i as u32,
                _ => {
                    return Err(QueryError::Type(
                        "ROUND second arg must be integer".to_string(),
                    ));
                }
            }
        } else {
            0
        };

        match val {
            Value::Number(n) => Ok(Value::Number(n.round_dp(decimals))),
            Value::Integer(i) => Ok(Value::Integer(i)),
            _ => Err(QueryError::Type("ROUND expects a number".to_string())),
        }
    }

    /// Evaluate SAFEDIV function.
    fn eval_safediv(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        Self::require_args("SAFEDIV", func, 2)?;
        let num = self.evaluate_expr(&func.args[0], ctx)?;
        let den = self.evaluate_expr(&func.args[1], ctx)?;

        match (num, den) {
            (Value::Number(n), Value::Number(d)) => {
                if d.is_zero() {
                    Ok(Value::Number(Decimal::ZERO))
                } else {
                    Ok(Value::Number(n / d))
                }
            }
            (Value::Integer(n), Value::Integer(d)) => {
                if d == 0 {
                    Ok(Value::Integer(0))
                } else {
                    Ok(Value::Integer(n / d))
                }
            }
            _ => Err(QueryError::Type("SAFEDIV expects two numbers".to_string())),
        }
    }

    /// Evaluate position/amount functions: `NUMBER`, `CURRENCY`, `GETITEM`, `UNITS`, `COST`, `WEIGHT`, `VALUE`.
    fn eval_position_function(
        &self,
        name: &str,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        match name {
            "NUMBER" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::Amount(a) => Ok(Value::Number(a.number)),
                    Value::Position(p) => Ok(Value::Number(p.units.number)),
                    Value::Number(n) => Ok(Value::Number(n)),
                    Value::Integer(i) => Ok(Value::Number(Decimal::from(i))),
                    _ => Err(QueryError::Type(
                        "NUMBER expects an amount or position".to_string(),
                    )),
                }
            }
            "CURRENCY" => {
                Self::require_args(name, func, 1)?;
                let val = self.evaluate_expr(&func.args[0], ctx)?;
                match val {
                    Value::Amount(a) => Ok(Value::String(a.currency.to_string())),
                    Value::Position(p) => Ok(Value::String(p.units.currency.to_string())),
                    _ => Err(QueryError::Type(
                        "CURRENCY expects an amount or position".to_string(),
                    )),
                }
            }
            "GETITEM" | "GET" => self.eval_getitem(func, ctx),
            "UNITS" => self.eval_units(func, ctx),
            "COST" => self.eval_cost(func, ctx),
            "WEIGHT" => self.eval_weight(func, ctx),
            "VALUE" => self.eval_value(func, ctx),
            _ => unreachable!(),
        }
    }

    /// Evaluate GETITEM/GET function.
    fn eval_getitem(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        Self::require_args("GETITEM", func, 2)?;
        let val = self.evaluate_expr(&func.args[0], ctx)?;
        let key = self.evaluate_expr(&func.args[1], ctx)?;

        match (val, key) {
            (Value::Inventory(inv), Value::String(currency)) => {
                let total = inv.units(&currency);
                if total.is_zero() {
                    Ok(Value::Null)
                } else {
                    Ok(Value::Amount(Amount::new(total, currency)))
                }
            }
            _ => Err(QueryError::Type(
                "GETITEM expects (inventory, string)".to_string(),
            )),
        }
    }

    /// Evaluate UNITS function.
    fn eval_units(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        Self::require_args("UNITS", func, 1)?;
        let val = self.evaluate_expr(&func.args[0], ctx)?;

        match val {
            Value::Position(p) => Ok(Value::Amount(p.units)),
            Value::Amount(a) => Ok(Value::Amount(a)),
            Value::Inventory(inv) => {
                let positions: Vec<String> = inv
                    .positions()
                    .iter()
                    .map(|p| format!("{} {}", p.units.number, p.units.currency))
                    .collect();
                Ok(Value::String(positions.join(", ")))
            }
            _ => Err(QueryError::Type(
                "UNITS expects a position or inventory".to_string(),
            )),
        }
    }

    /// Evaluate COST function.
    fn eval_cost(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        Self::require_args("COST", func, 1)?;
        let val = self.evaluate_expr(&func.args[0], ctx)?;

        match val {
            Value::Position(p) => {
                if let Some(cost) = &p.cost {
                    let total = p.units.number.abs() * cost.number;
                    Ok(Value::Amount(Amount::new(total, cost.currency.clone())))
                } else {
                    Ok(Value::Null)
                }
            }
            Value::Amount(a) => Ok(Value::Amount(a)),
            Value::Inventory(inv) => {
                let mut total = Decimal::ZERO;
                let mut currency: Option<InternedStr> = None;
                for pos in inv.positions() {
                    if let Some(cost) = &pos.cost {
                        total += pos.units.number.abs() * cost.number;
                        if currency.is_none() {
                            currency = Some(cost.currency.clone());
                        }
                    }
                }
                if let Some(curr) = currency {
                    Ok(Value::Amount(Amount::new(total, curr)))
                } else {
                    Ok(Value::Null)
                }
            }
            _ => Err(QueryError::Type(
                "COST expects a position or inventory".to_string(),
            )),
        }
    }

    /// Evaluate WEIGHT function.
    fn eval_weight(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        Self::require_args("WEIGHT", func, 1)?;
        let val = self.evaluate_expr(&func.args[0], ctx)?;

        match val {
            Value::Position(p) => {
                if let Some(cost) = &p.cost {
                    let total = p.units.number * cost.number;
                    Ok(Value::Amount(Amount::new(total, cost.currency.clone())))
                } else {
                    Ok(Value::Amount(p.units))
                }
            }
            Value::Amount(a) => Ok(Value::Amount(a)),
            _ => Err(QueryError::Type(
                "WEIGHT expects a position or amount".to_string(),
            )),
        }
    }

    /// Evaluate VALUE function (market value conversion).
    fn eval_value(&self, func: &FunctionCall, ctx: &PostingContext) -> Result<Value, QueryError> {
        if func.args.is_empty() || func.args.len() > 2 {
            return Err(QueryError::InvalidArguments(
                "VALUE".to_string(),
                "expected 1-2 arguments".to_string(),
            ));
        }

        let target_currency = if func.args.len() == 2 {
            match self.evaluate_expr(&func.args[1], ctx)? {
                Value::String(s) => s,
                _ => {
                    return Err(QueryError::Type(
                        "VALUE second argument must be a currency string".to_string(),
                    ));
                }
            }
        } else {
            self.target_currency.clone().ok_or_else(|| {
                QueryError::InvalidArguments(
                    "VALUE".to_string(),
                    "no target currency set; either call set_target_currency() on the executor \
                     or pass the currency as VALUE(amount, 'USD')"
                        .to_string(),
                )
            })?
        };

        let val = self.evaluate_expr(&func.args[0], ctx)?;
        let date = ctx.transaction.date;

        match val {
            Value::Position(p) => {
                if p.units.currency == target_currency {
                    Ok(Value::Amount(p.units))
                } else if let Some(converted) =
                    self.price_db.convert(&p.units, &target_currency, date)
                {
                    Ok(Value::Amount(converted))
                } else {
                    Ok(Value::Amount(p.units))
                }
            }
            Value::Amount(a) => {
                if a.currency == target_currency {
                    Ok(Value::Amount(a))
                } else if let Some(converted) = self.price_db.convert(&a, &target_currency, date) {
                    Ok(Value::Amount(converted))
                } else {
                    Ok(Value::Amount(a))
                }
            }
            Value::Inventory(inv) => {
                let mut total = Decimal::ZERO;
                for pos in inv.positions() {
                    if pos.units.currency == target_currency {
                        total += pos.units.number;
                    } else if let Some(converted) =
                        self.price_db.convert(&pos.units, &target_currency, date)
                    {
                        total += converted.number;
                    }
                }
                Ok(Value::Amount(Amount::new(total, &target_currency)))
            }
            _ => Err(QueryError::Type(
                "VALUE expects a position or inventory".to_string(),
            )),
        }
    }

    /// Evaluate COALESCE function.
    fn eval_coalesce(
        &self,
        func: &FunctionCall,
        ctx: &PostingContext,
    ) -> Result<Value, QueryError> {
        for arg in &func.args {
            let val = self.evaluate_expr(arg, ctx)?;
            if !matches!(val, Value::Null) {
                return Ok(val);
            }
        }
        Ok(Value::Null)
    }

    /// Evaluate a function with pre-evaluated arguments (for subquery context).
    fn evaluate_function_on_values(&self, name: &str, args: &[Value]) -> Result<Value, QueryError> {
        let name_upper = name.to_uppercase();
        match name_upper.as_str() {
            // Date functions
            "TODAY" => Ok(Value::Date(chrono::Local::now().date_naive())),
            "YEAR" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::Date(d) => Ok(Value::Integer(d.year().into())),
                    _ => Err(QueryError::Type("YEAR expects a date".to_string())),
                }
            }
            "MONTH" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::Date(d) => Ok(Value::Integer(d.month().into())),
                    _ => Err(QueryError::Type("MONTH expects a date".to_string())),
                }
            }
            "DAY" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::Date(d) => Ok(Value::Integer(d.day().into())),
                    _ => Err(QueryError::Type("DAY expects a date".to_string())),
                }
            }
            // String functions
            "LENGTH" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::String(s) => Ok(Value::Integer(s.len() as i64)),
                    _ => Err(QueryError::Type("LENGTH expects a string".to_string())),
                }
            }
            "UPPER" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::String(s) => Ok(Value::String(s.to_uppercase())),
                    _ => Err(QueryError::Type("UPPER expects a string".to_string())),
                }
            }
            "LOWER" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::String(s) => Ok(Value::String(s.to_lowercase())),
                    _ => Err(QueryError::Type("LOWER expects a string".to_string())),
                }
            }
            "TRIM" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::String(s) => Ok(Value::String(s.trim().to_string())),
                    _ => Err(QueryError::Type("TRIM expects a string".to_string())),
                }
            }
            // Math functions
            "ABS" => {
                Self::require_args_count(&name_upper, args, 1)?;
                match &args[0] {
                    Value::Number(n) => Ok(Value::Number(n.abs())),
                    Value::Integer(i) => Ok(Value::Integer(i.abs())),
                    _ => Err(QueryError::Type("ABS expects a number".to_string())),
                }
            }
            "ROUND" => {
                if args.is_empty() || args.len() > 2 {
                    return Err(QueryError::InvalidArguments(
                        "ROUND".to_string(),
                        "expected 1 or 2 arguments".to_string(),
                    ));
                }
                match &args[0] {
                    Value::Number(n) => {
                        let scale = if args.len() == 2 {
                            match &args[1] {
                                Value::Integer(i) => *i as u32,
                                _ => 0,
                            }
                        } else {
                            0
                        };
                        Ok(Value::Number(n.round_dp(scale)))
                    }
                    Value::Integer(i) => Ok(Value::Integer(*i)),
                    _ => Err(QueryError::Type("ROUND expects a number".to_string())),
                }
            }
            // Utility functions
            "COALESCE" => {
                for arg in args {
                    if !matches!(arg, Value::Null) {
                        return Ok(arg.clone());
                    }
                }
                Ok(Value::Null)
            }
            // Aggregate functions return Null when evaluated on a single row
            "SUM" | "COUNT" | "MIN" | "MAX" | "FIRST" | "LAST" | "AVG" => Ok(Value::Null),
            _ => Err(QueryError::UnknownFunction(name.to_string())),
        }
    }

    /// Helper to require a specific number of arguments (for pre-evaluated args).
    fn require_args_count(name: &str, args: &[Value], expected: usize) -> Result<(), QueryError> {
        if args.len() != expected {
            return Err(QueryError::InvalidArguments(
                name.to_string(),
                format!("expected {} argument(s), got {}", expected, args.len()),
            ));
        }
        Ok(())
    }

    /// Helper to require a specific number of arguments.
    fn require_args(name: &str, func: &FunctionCall, expected: usize) -> Result<(), QueryError> {
        if func.args.len() != expected {
            return Err(QueryError::InvalidArguments(
                name.to_string(),
                format!("expected {expected} argument(s)"),
            ));
        }
        Ok(())
    }

    /// Evaluate a binary operation.
    fn evaluate_binary_op(&self, op: &BinaryOp, ctx: &PostingContext) -> Result<Value, QueryError> {
        let left = self.evaluate_expr(&op.left, ctx)?;
        let right = self.evaluate_expr(&op.right, ctx)?;

        match op.op {
            BinaryOperator::Eq => Ok(Value::Boolean(self.values_equal(&left, &right))),
            BinaryOperator::Ne => Ok(Value::Boolean(!self.values_equal(&left, &right))),
            BinaryOperator::Lt => self.compare_values(&left, &right, std::cmp::Ordering::is_lt),
            BinaryOperator::Le => self.compare_values(&left, &right, std::cmp::Ordering::is_le),
            BinaryOperator::Gt => self.compare_values(&left, &right, std::cmp::Ordering::is_gt),
            BinaryOperator::Ge => self.compare_values(&left, &right, std::cmp::Ordering::is_ge),
            BinaryOperator::And => {
                let l = self.to_bool(&left)?;
                let r = self.to_bool(&right)?;
                Ok(Value::Boolean(l && r))
            }
            BinaryOperator::Or => {
                let l = self.to_bool(&left)?;
                let r = self.to_bool(&right)?;
                Ok(Value::Boolean(l || r))
            }
            BinaryOperator::Regex => {
                // ~ operator: string matches regex pattern
                let s = match left {
                    Value::String(s) => s,
                    _ => {
                        return Err(QueryError::Type(
                            "regex requires string left operand".to_string(),
                        ));
                    }
                };
                let pattern = match right {
                    Value::String(p) => p,
                    _ => {
                        return Err(QueryError::Type(
                            "regex requires string pattern".to_string(),
                        ));
                    }
                };
                // Simple contains check (full regex would need regex crate)
                Ok(Value::Boolean(s.contains(&pattern)))
            }
            BinaryOperator::In => {
                // Check if left value is in right set
                match right {
                    Value::StringSet(set) => {
                        let needle = match left {
                            Value::String(s) => s,
                            _ => {
                                return Err(QueryError::Type(
                                    "IN requires string left operand".to_string(),
                                ));
                            }
                        };
                        Ok(Value::Boolean(set.contains(&needle)))
                    }
                    _ => Err(QueryError::Type(
                        "IN requires set right operand".to_string(),
                    )),
                }
            }
            BinaryOperator::Add => self.arithmetic_op(&left, &right, |a, b| a + b),
            BinaryOperator::Sub => self.arithmetic_op(&left, &right, |a, b| a - b),
            BinaryOperator::Mul => self.arithmetic_op(&left, &right, |a, b| a * b),
            BinaryOperator::Div => self.arithmetic_op(&left, &right, |a, b| a / b),
        }
    }

    /// Evaluate a unary operation.
    fn evaluate_unary_op(&self, op: &UnaryOp, ctx: &PostingContext) -> Result<Value, QueryError> {
        let val = self.evaluate_expr(&op.operand, ctx)?;
        self.unary_op_on_value(op.op, &val)
    }

    /// Apply a unary operator to a value.
    fn unary_op_on_value(&self, op: UnaryOperator, val: &Value) -> Result<Value, QueryError> {
        match op {
            UnaryOperator::Not => {
                let b = self.to_bool(val)?;
                Ok(Value::Boolean(!b))
            }
            UnaryOperator::Neg => match val {
                Value::Number(n) => Ok(Value::Number(-*n)),
                Value::Integer(i) => Ok(Value::Integer(-*i)),
                _ => Err(QueryError::Type(
                    "negation requires numeric value".to_string(),
                )),
            },
        }
    }

    /// Check if two values are equal.
    fn values_equal(&self, left: &Value, right: &Value) -> bool {
        // BQL treats NULL = NULL as TRUE
        match (left, right) {
            (Value::Null, Value::Null) => true,
            (Value::String(a), Value::String(b)) => a == b,
            (Value::Number(a), Value::Number(b)) => a == b,
            (Value::Integer(a), Value::Integer(b)) => a == b,
            (Value::Number(a), Value::Integer(b)) => *a == Decimal::from(*b),
            (Value::Integer(a), Value::Number(b)) => Decimal::from(*a) == *b,
            (Value::Date(a), Value::Date(b)) => a == b,
            (Value::Boolean(a), Value::Boolean(b)) => a == b,
            _ => false,
        }
    }

    /// Compare two values.
    fn compare_values<F>(&self, left: &Value, right: &Value, pred: F) -> Result<Value, QueryError>
    where
        F: FnOnce(std::cmp::Ordering) -> bool,
    {
        let ord = match (left, right) {
            (Value::Number(a), Value::Number(b)) => a.cmp(b),
            (Value::Integer(a), Value::Integer(b)) => a.cmp(b),
            (Value::Number(a), Value::Integer(b)) => a.cmp(&Decimal::from(*b)),
            (Value::Integer(a), Value::Number(b)) => Decimal::from(*a).cmp(b),
            (Value::String(a), Value::String(b)) => a.cmp(b),
            (Value::Date(a), Value::Date(b)) => a.cmp(b),
            _ => return Err(QueryError::Type("cannot compare values".to_string())),
        };
        Ok(Value::Boolean(pred(ord)))
    }

    /// Check if left value is less than right value.
    fn value_less_than(&self, left: &Value, right: &Value) -> Result<bool, QueryError> {
        let ord = match (left, right) {
            (Value::Number(a), Value::Number(b)) => a.cmp(b),
            (Value::Integer(a), Value::Integer(b)) => a.cmp(b),
            (Value::Number(a), Value::Integer(b)) => a.cmp(&Decimal::from(*b)),
            (Value::Integer(a), Value::Number(b)) => Decimal::from(*a).cmp(b),
            (Value::String(a), Value::String(b)) => a.cmp(b),
            (Value::Date(a), Value::Date(b)) => a.cmp(b),
            _ => return Err(QueryError::Type("cannot compare values".to_string())),
        };
        Ok(ord.is_lt())
    }

    /// Perform arithmetic operation.
    fn arithmetic_op<F>(&self, left: &Value, right: &Value, op: F) -> Result<Value, QueryError>
    where
        F: FnOnce(Decimal, Decimal) -> Decimal,
    {
        let (a, b) = match (left, right) {
            (Value::Number(a), Value::Number(b)) => (*a, *b),
            (Value::Integer(a), Value::Integer(b)) => (Decimal::from(*a), Decimal::from(*b)),
            (Value::Number(a), Value::Integer(b)) => (*a, Decimal::from(*b)),
            (Value::Integer(a), Value::Number(b)) => (Decimal::from(*a), *b),
            _ => {
                return Err(QueryError::Type(
                    "arithmetic requires numeric values".to_string(),
                ));
            }
        };
        Ok(Value::Number(op(a, b)))
    }

    /// Convert a value to boolean.
    fn to_bool(&self, val: &Value) -> Result<bool, QueryError> {
        match val {
            Value::Boolean(b) => Ok(*b),
            Value::Null => Ok(false),
            _ => Err(QueryError::Type("expected boolean".to_string())),
        }
    }

    /// Check if an expression contains aggregate functions.
    fn is_aggregate_expr(expr: &Expr) -> bool {
        match expr {
            Expr::Function(func) => {
                matches!(
                    func.name.to_uppercase().as_str(),
                    "SUM" | "COUNT" | "MIN" | "MAX" | "FIRST" | "LAST" | "AVG"
                )
            }
            Expr::BinaryOp(op) => {
                Self::is_aggregate_expr(&op.left) || Self::is_aggregate_expr(&op.right)
            }
            Expr::UnaryOp(op) => Self::is_aggregate_expr(&op.operand),
            Expr::Paren(inner) => Self::is_aggregate_expr(inner),
            _ => false,
        }
    }

    /// Check if an expression is a window function.
    const fn is_window_expr(expr: &Expr) -> bool {
        matches!(expr, Expr::Window(_))
    }

    /// Check if any target contains a window function.
    fn has_window_functions(targets: &[Target]) -> bool {
        targets.iter().any(|t| Self::is_window_expr(&t.expr))
    }

    /// Resolve column names from targets.
    fn resolve_column_names(&self, targets: &[Target]) -> Result<Vec<String>, QueryError> {
        let mut names = Vec::new();
        for (i, target) in targets.iter().enumerate() {
            if let Some(alias) = &target.alias {
                names.push(alias.clone());
            } else {
                names.push(self.expr_to_name(&target.expr, i));
            }
        }
        Ok(names)
    }

    /// Convert an expression to a column name.
    fn expr_to_name(&self, expr: &Expr, index: usize) -> String {
        match expr {
            Expr::Wildcard => "*".to_string(),
            Expr::Column(name) => name.clone(),
            Expr::Function(func) => func.name.clone(),
            Expr::Window(wf) => wf.name.clone(),
            _ => format!("col{index}"),
        }
    }

    /// Evaluate a row of results for non-aggregate query.
    fn evaluate_row(&self, targets: &[Target], ctx: &PostingContext) -> Result<Row, QueryError> {
        self.evaluate_row_with_window(targets, ctx, None)
    }

    /// Evaluate a row with optional window context.
    fn evaluate_row_with_window(
        &self,
        targets: &[Target],
        ctx: &PostingContext,
        window_ctx: Option<&WindowContext>,
    ) -> Result<Row, QueryError> {
        let mut row = Vec::new();
        for target in targets {
            if matches!(target.expr, Expr::Wildcard) {
                // Expand wildcard to default columns
                row.push(Value::Date(ctx.transaction.date));
                row.push(Value::String(ctx.transaction.flag.to_string()));
                row.push(
                    ctx.transaction
                        .payee
                        .as_ref()
                        .map_or(Value::Null, |p| Value::String(p.to_string())),
                );
                row.push(Value::String(ctx.transaction.narration.to_string()));
                let posting = &ctx.transaction.postings[ctx.posting_index];
                row.push(Value::String(posting.account.to_string()));
                row.push(
                    posting
                        .amount()
                        .map_or(Value::Null, |u| Value::Amount(u.clone())),
                );
            } else if let Expr::Window(wf) = &target.expr {
                // Handle window function
                row.push(self.evaluate_window_function(wf, window_ctx)?);
            } else {
                row.push(self.evaluate_expr(&target.expr, ctx)?);
            }
        }
        Ok(row)
    }

    /// Evaluate a window function.
    fn evaluate_window_function(
        &self,
        wf: &WindowFunction,
        window_ctx: Option<&WindowContext>,
    ) -> Result<Value, QueryError> {
        let ctx = window_ctx.ok_or_else(|| {
            QueryError::Evaluation("Window function requires window context".to_string())
        })?;

        match wf.name.to_uppercase().as_str() {
            "ROW_NUMBER" => Ok(Value::Integer(ctx.row_number as i64)),
            "RANK" => Ok(Value::Integer(ctx.rank as i64)),
            "DENSE_RANK" => Ok(Value::Integer(ctx.dense_rank as i64)),
            _ => Err(QueryError::Evaluation(format!(
                "Window function '{}' not yet implemented",
                wf.name
            ))),
        }
    }

    /// Compute window contexts for all postings based on the window spec.
    fn compute_window_contexts(
        &self,
        postings: &[PostingContext],
        wf: &WindowFunction,
    ) -> Result<Vec<WindowContext>, QueryError> {
        let spec = &wf.over;

        // Compute partition keys for each posting
        let mut partition_keys: Vec<String> = Vec::with_capacity(postings.len());
        for ctx in postings {
            if let Some(partition_exprs) = &spec.partition_by {
                let mut key_values = Vec::new();
                for expr in partition_exprs {
                    key_values.push(self.evaluate_expr(expr, ctx)?);
                }
                partition_keys.push(Self::make_group_key(&key_values));
            } else {
                // No partition - all rows in one partition
                partition_keys.push(String::new());
            }
        }

        // Group posting indices by partition key
        let mut partitions: HashMap<String, Vec<usize>> = HashMap::new();
        for (idx, key) in partition_keys.iter().enumerate() {
            partitions.entry(key.clone()).or_default().push(idx);
        }

        // Compute order values for sorting within partitions
        let mut order_values: Vec<Vec<Value>> = Vec::with_capacity(postings.len());
        for ctx in postings {
            if let Some(order_specs) = &spec.order_by {
                let mut values = Vec::new();
                for order_spec in order_specs {
                    values.push(self.evaluate_expr(&order_spec.expr, ctx)?);
                }
                order_values.push(values);
            } else {
                order_values.push(Vec::new());
            }
        }

        // Initialize window contexts
        let mut window_contexts: Vec<WindowContext> = vec![
            WindowContext {
                row_number: 0,
                rank: 0,
                dense_rank: 0,
            };
            postings.len()
        ];

        // Process each partition
        for indices in partitions.values() {
            // Sort indices within partition by order values
            let mut sorted_indices: Vec<usize> = indices.clone();
            if spec.order_by.is_some() {
                let order_specs = spec.order_by.as_ref().unwrap();
                sorted_indices.sort_by(|&a, &b| {
                    let vals_a = &order_values[a];
                    let vals_b = &order_values[b];
                    for (i, (va, vb)) in vals_a.iter().zip(vals_b.iter()).enumerate() {
                        let cmp = self.compare_values_for_sort(va, vb);
                        if cmp != std::cmp::Ordering::Equal {
                            return if order_specs
                                .get(i)
                                .is_some_and(|s| s.direction == SortDirection::Desc)
                            {
                                cmp.reverse()
                            } else {
                                cmp
                            };
                        }
                    }
                    std::cmp::Ordering::Equal
                });
            }

            // Assign ranks within the partition
            let mut row_num = 1;
            let mut rank = 1;
            let mut dense_rank = 1;
            let mut prev_values: Option<&Vec<Value>> = None;

            for (position, &original_idx) in sorted_indices.iter().enumerate() {
                let current_values = &order_values[original_idx];

                // Check if this row has the same order values as the previous row
                let is_tie = if let Some(prev) = prev_values {
                    current_values == prev
                } else {
                    false
                };

                if !is_tie && position > 0 {
                    // New value - update ranks
                    rank = position + 1;
                    dense_rank += 1;
                }
                window_contexts[original_idx] = WindowContext {
                    row_number: row_num,
                    rank,
                    dense_rank,
                };

                row_num += 1;
                prev_values = Some(current_values);
            }
        }

        Ok(window_contexts)
    }

    /// Extract the first window function from targets (for getting the window spec).
    fn find_window_function(targets: &[Target]) -> Option<&WindowFunction> {
        for target in targets {
            if let Expr::Window(wf) = &target.expr {
                return Some(wf);
            }
        }
        None
    }

    /// Generate a hashable key from a vector of Values.
    /// Used for O(1) grouping instead of O(n) linear search.
    fn make_group_key(values: &[Value]) -> String {
        use std::fmt::Write;
        let mut key = String::new();
        for (i, v) in values.iter().enumerate() {
            if i > 0 {
                key.push('\x00'); // Null separator between values
            }
            match v {
                Value::String(s) => {
                    key.push('S');
                    key.push_str(s);
                }
                Value::Number(n) => {
                    key.push('N');
                    let _ = write!(key, "{n}");
                }
                Value::Integer(n) => {
                    key.push('I');
                    let _ = write!(key, "{n}");
                }
                Value::Date(d) => {
                    key.push('D');
                    let _ = write!(key, "{d}");
                }
                Value::Boolean(b) => {
                    key.push(if *b { 'T' } else { 'F' });
                }
                Value::Amount(a) => {
                    key.push('A');
                    let _ = write!(key, "{} {}", a.number, a.currency);
                }
                Value::Position(p) => {
                    key.push('P');
                    let _ = write!(key, "{} {}", p.units.number, p.units.currency);
                }
                Value::Inventory(_) => {
                    // Inventories are complex; use a placeholder
                    // (unlikely to be used as GROUP BY key)
                    key.push('V');
                }
                Value::StringSet(ss) => {
                    key.push('Z');
                    for s in ss {
                        key.push_str(s);
                        key.push(',');
                    }
                }
                Value::Null => {
                    key.push('0');
                }
            }
        }
        key
    }

    /// Group postings by the GROUP BY expressions.
    /// Uses `HashMap` for O(1) key lookup instead of O(n) linear search.
    fn group_postings<'b>(
        &self,
        postings: &'b [PostingContext<'a>],
        group_by: Option<&Vec<Expr>>,
    ) -> Result<Vec<(Vec<Value>, Vec<&'b PostingContext<'a>>)>, QueryError> {
        if let Some(group_exprs) = group_by {
            // Use HashMap for O(1) grouping
            let mut group_map: HashMap<String, (Vec<Value>, Vec<&PostingContext<'a>>)> =
                HashMap::new();

            for ctx in postings {
                let mut key_values = Vec::with_capacity(group_exprs.len());
                for expr in group_exprs {
                    key_values.push(self.evaluate_expr(expr, ctx)?);
                }
                let hash_key = Self::make_group_key(&key_values);

                group_map
                    .entry(hash_key)
                    .or_insert_with(|| (key_values, Vec::new()))
                    .1
                    .push(ctx);
            }

            Ok(group_map.into_values().collect())
        } else {
            // No GROUP BY - all postings in one group
            Ok(vec![(Vec::new(), postings.iter().collect())])
        }
    }

    /// Evaluate a row of aggregate results.
    fn evaluate_aggregate_row(
        &self,
        targets: &[Target],
        group: &[&PostingContext],
    ) -> Result<Row, QueryError> {
        let mut row = Vec::new();
        for target in targets {
            row.push(self.evaluate_aggregate_expr(&target.expr, group)?);
        }
        Ok(row)
    }

    /// Evaluate an expression in an aggregate context.
    fn evaluate_aggregate_expr(
        &self,
        expr: &Expr,
        group: &[&PostingContext],
    ) -> Result<Value, QueryError> {
        match expr {
            Expr::Function(func) => {
                match func.name.to_uppercase().as_str() {
                    "COUNT" => {
                        // COUNT(*) counts all rows
                        Ok(Value::Integer(group.len() as i64))
                    }
                    "SUM" => {
                        if func.args.len() != 1 {
                            return Err(QueryError::InvalidArguments(
                                "SUM".to_string(),
                                "expected 1 argument".to_string(),
                            ));
                        }
                        let mut total = Inventory::new();
                        for ctx in group {
                            let val = self.evaluate_expr(&func.args[0], ctx)?;
                            match val {
                                Value::Amount(amt) => {
                                    let pos = Position::simple(amt);
                                    total.add(pos);
                                }
                                Value::Position(pos) => {
                                    total.add(pos);
                                }
                                Value::Number(n) => {
                                    // Sum as raw number
                                    let pos =
                                        Position::simple(Amount::new(n, "__NUMBER__".to_string()));
                                    total.add(pos);
                                }
                                Value::Null => {}
                                _ => {
                                    return Err(QueryError::Type(
                                        "SUM requires numeric or position value".to_string(),
                                    ));
                                }
                            }
                        }
                        Ok(Value::Inventory(total))
                    }
                    "FIRST" => {
                        if func.args.len() != 1 {
                            return Err(QueryError::InvalidArguments(
                                "FIRST".to_string(),
                                "expected 1 argument".to_string(),
                            ));
                        }
                        if let Some(ctx) = group.first() {
                            self.evaluate_expr(&func.args[0], ctx)
                        } else {
                            Ok(Value::Null)
                        }
                    }
                    "LAST" => {
                        if func.args.len() != 1 {
                            return Err(QueryError::InvalidArguments(
                                "LAST".to_string(),
                                "expected 1 argument".to_string(),
                            ));
                        }
                        if let Some(ctx) = group.last() {
                            self.evaluate_expr(&func.args[0], ctx)
                        } else {
                            Ok(Value::Null)
                        }
                    }
                    "MIN" => {
                        if func.args.len() != 1 {
                            return Err(QueryError::InvalidArguments(
                                "MIN".to_string(),
                                "expected 1 argument".to_string(),
                            ));
                        }
                        let mut min_val: Option<Value> = None;
                        for ctx in group {
                            let val = self.evaluate_expr(&func.args[0], ctx)?;
                            if matches!(val, Value::Null) {
                                continue;
                            }
                            min_val = Some(match min_val {
                                None => val,
                                Some(current) => {
                                    if self.value_less_than(&val, &current)? {
                                        val
                                    } else {
                                        current
                                    }
                                }
                            });
                        }
                        Ok(min_val.unwrap_or(Value::Null))
                    }
                    "MAX" => {
                        if func.args.len() != 1 {
                            return Err(QueryError::InvalidArguments(
                                "MAX".to_string(),
                                "expected 1 argument".to_string(),
                            ));
                        }
                        let mut max_val: Option<Value> = None;
                        for ctx in group {
                            let val = self.evaluate_expr(&func.args[0], ctx)?;
                            if matches!(val, Value::Null) {
                                continue;
                            }
                            max_val = Some(match max_val {
                                None => val,
                                Some(current) => {
                                    if self.value_less_than(&current, &val)? {
                                        val
                                    } else {
                                        current
                                    }
                                }
                            });
                        }
                        Ok(max_val.unwrap_or(Value::Null))
                    }
                    "AVG" => {
                        if func.args.len() != 1 {
                            return Err(QueryError::InvalidArguments(
                                "AVG".to_string(),
                                "expected 1 argument".to_string(),
                            ));
                        }
                        let mut sum = Decimal::ZERO;
                        let mut count = 0i64;
                        for ctx in group {
                            let val = self.evaluate_expr(&func.args[0], ctx)?;
                            match val {
                                Value::Number(n) => {
                                    sum += n;
                                    count += 1;
                                }
                                Value::Integer(i) => {
                                    sum += Decimal::from(i);
                                    count += 1;
                                }
                                Value::Null => {}
                                _ => {
                                    return Err(QueryError::Type(
                                        "AVG expects numeric values".to_string(),
                                    ));
                                }
                            }
                        }
                        if count == 0 {
                            Ok(Value::Null)
                        } else {
                            Ok(Value::Number(sum / Decimal::from(count)))
                        }
                    }
                    _ => {
                        // Non-aggregate function
                        if let Some(ctx) = group.first() {
                            self.evaluate_function(func, ctx)
                        } else {
                            Ok(Value::Null)
                        }
                    }
                }
            }
            Expr::Column(_) => {
                // For non-aggregate columns in aggregate query, take first value
                if let Some(ctx) = group.first() {
                    self.evaluate_expr(expr, ctx)
                } else {
                    Ok(Value::Null)
                }
            }
            Expr::BinaryOp(op) => {
                let left = self.evaluate_aggregate_expr(&op.left, group)?;
                let right = self.evaluate_aggregate_expr(&op.right, group)?;
                // Re-evaluate with computed values
                self.binary_op_on_values(op.op, &left, &right)
            }
            _ => {
                // For other expressions, evaluate on first row
                if let Some(ctx) = group.first() {
                    self.evaluate_expr(expr, ctx)
                } else {
                    Ok(Value::Null)
                }
            }
        }
    }

    /// Apply binary operator to already-evaluated values.
    fn binary_op_on_values(
        &self,
        op: BinaryOperator,
        left: &Value,
        right: &Value,
    ) -> Result<Value, QueryError> {
        match op {
            BinaryOperator::Eq => Ok(Value::Boolean(self.values_equal(left, right))),
            BinaryOperator::Ne => Ok(Value::Boolean(!self.values_equal(left, right))),
            BinaryOperator::Lt => self.compare_values(left, right, std::cmp::Ordering::is_lt),
            BinaryOperator::Le => self.compare_values(left, right, std::cmp::Ordering::is_le),
            BinaryOperator::Gt => self.compare_values(left, right, std::cmp::Ordering::is_gt),
            BinaryOperator::Ge => self.compare_values(left, right, std::cmp::Ordering::is_ge),
            BinaryOperator::And => {
                let l = self.to_bool(left)?;
                let r = self.to_bool(right)?;
                Ok(Value::Boolean(l && r))
            }
            BinaryOperator::Or => {
                let l = self.to_bool(left)?;
                let r = self.to_bool(right)?;
                Ok(Value::Boolean(l || r))
            }
            BinaryOperator::Regex => {
                // ~ operator: string matches regex pattern (simple contains check)
                let s = match left {
                    Value::String(s) => s,
                    _ => {
                        return Err(QueryError::Type(
                            "regex requires string left operand".to_string(),
                        ));
                    }
                };
                let pattern = match right {
                    Value::String(p) => p,
                    _ => {
                        return Err(QueryError::Type(
                            "regex requires string pattern".to_string(),
                        ));
                    }
                };
                // Use regex cache for pattern matching
                let regex_result = self.get_or_compile_regex(pattern);
                let matches = if let Some(regex) = regex_result {
                    regex.is_match(s)
                } else {
                    s.contains(pattern)
                };
                Ok(Value::Boolean(matches))
            }
            BinaryOperator::In => {
                // Check if left value is in right set
                match right {
                    Value::StringSet(set) => {
                        let needle = match left {
                            Value::String(s) => s,
                            _ => {
                                return Err(QueryError::Type(
                                    "IN requires string left operand".to_string(),
                                ));
                            }
                        };
                        Ok(Value::Boolean(set.contains(needle)))
                    }
                    _ => Err(QueryError::Type(
                        "IN requires set right operand".to_string(),
                    )),
                }
            }
            BinaryOperator::Add => self.arithmetic_op(left, right, |a, b| a + b),
            BinaryOperator::Sub => self.arithmetic_op(left, right, |a, b| a - b),
            BinaryOperator::Mul => self.arithmetic_op(left, right, |a, b| a * b),
            BinaryOperator::Div => self.arithmetic_op(left, right, |a, b| a / b),
        }
    }

    /// Sort results by ORDER BY clauses.
    fn sort_results(
        &self,
        result: &mut QueryResult,
        order_by: &[OrderSpec],
    ) -> Result<(), QueryError> {
        if order_by.is_empty() {
            return Ok(());
        }

        // Build a map from column names to indices
        let column_indices: std::collections::HashMap<&str, usize> = result
            .columns
            .iter()
            .enumerate()
            .map(|(i, name)| (name.as_str(), i))
            .collect();

        // Resolve ORDER BY expressions to column indices
        let mut sort_specs: Vec<(usize, bool)> = Vec::new();
        for spec in order_by {
            // Try to resolve the expression to a column index
            let idx = match &spec.expr {
                Expr::Column(name) => column_indices
                    .get(name.as_str())
                    .copied()
                    .ok_or_else(|| QueryError::UnknownColumn(name.clone()))?,
                Expr::Function(func) => {
                    // Try to find a column with the function name
                    column_indices
                        .get(func.name.as_str())
                        .copied()
                        .ok_or_else(|| {
                            QueryError::Evaluation(format!(
                                "ORDER BY expression not found in SELECT: {}",
                                func.name
                            ))
                        })?
                }
                _ => {
                    return Err(QueryError::Evaluation(
                        "ORDER BY expression must reference a selected column".to_string(),
                    ));
                }
            };
            let ascending = spec.direction != SortDirection::Desc;
            sort_specs.push((idx, ascending));
        }

        // Sort the rows
        result.rows.sort_by(|a, b| {
            for (idx, ascending) in &sort_specs {
                if *idx >= a.len() || *idx >= b.len() {
                    continue;
                }
                let ord = self.compare_values_for_sort(&a[*idx], &b[*idx]);
                if ord != std::cmp::Ordering::Equal {
                    return if *ascending { ord } else { ord.reverse() };
                }
            }
            std::cmp::Ordering::Equal
        });

        Ok(())
    }

    /// Compare two values for sorting purposes.
    fn compare_values_for_sort(&self, left: &Value, right: &Value) -> std::cmp::Ordering {
        match (left, right) {
            (Value::Null, Value::Null) => std::cmp::Ordering::Equal,
            (Value::Null, _) => std::cmp::Ordering::Greater, // Nulls sort last
            (_, Value::Null) => std::cmp::Ordering::Less,
            (Value::Number(a), Value::Number(b)) => a.cmp(b),
            (Value::Integer(a), Value::Integer(b)) => a.cmp(b),
            (Value::Number(a), Value::Integer(b)) => a.cmp(&Decimal::from(*b)),
            (Value::Integer(a), Value::Number(b)) => Decimal::from(*a).cmp(b),
            (Value::String(a), Value::String(b)) => a.cmp(b),
            (Value::Date(a), Value::Date(b)) => a.cmp(b),
            (Value::Boolean(a), Value::Boolean(b)) => a.cmp(b),
            // Compare amounts by their numeric value (same currency assumed)
            (Value::Amount(a), Value::Amount(b)) => a.number.cmp(&b.number),
            // Compare positions by their units' numeric value
            (Value::Position(a), Value::Position(b)) => a.units.number.cmp(&b.units.number),
            // Compare inventories by first position's value (for single-currency)
            (Value::Inventory(a), Value::Inventory(b)) => {
                let a_val = a.positions().first().map(|p| &p.units.number);
                let b_val = b.positions().first().map(|p| &p.units.number);
                match (a_val, b_val) {
                    (Some(av), Some(bv)) => av.cmp(bv),
                    (Some(_), None) => std::cmp::Ordering::Less,
                    (None, Some(_)) => std::cmp::Ordering::Greater,
                    (None, None) => std::cmp::Ordering::Equal,
                }
            }
            _ => std::cmp::Ordering::Equal, // Can't compare other types
        }
    }

    /// Evaluate a HAVING clause filter on an aggregated row.
    ///
    /// The HAVING clause can reference:
    /// - Column names/aliases from the SELECT clause
    /// - Aggregate functions (evaluated on the group)
    fn evaluate_having_filter(
        &self,
        having_expr: &Expr,
        row: &[Value],
        column_names: &[String],
        targets: &[Target],
        group: &[&PostingContext],
    ) -> Result<bool, QueryError> {
        // Build a map of column name -> index for quick lookup
        let col_map: HashMap<String, usize> = column_names
            .iter()
            .enumerate()
            .map(|(i, name)| (name.to_uppercase(), i))
            .collect();

        // Also map aliases
        let alias_map: HashMap<String, usize> = targets
            .iter()
            .enumerate()
            .filter_map(|(i, t)| t.alias.as_ref().map(|a| (a.to_uppercase(), i)))
            .collect();

        let val = self.evaluate_having_expr(having_expr, row, &col_map, &alias_map, group)?;

        match val {
            Value::Boolean(b) => Ok(b),
            Value::Null => Ok(false), // NULL is treated as false in HAVING
            _ => Err(QueryError::Type(
                "HAVING clause must evaluate to boolean".to_string(),
            )),
        }
    }

    /// Evaluate an expression in HAVING context (can reference aggregated values).
    fn evaluate_having_expr(
        &self,
        expr: &Expr,
        row: &[Value],
        col_map: &HashMap<String, usize>,
        alias_map: &HashMap<String, usize>,
        group: &[&PostingContext],
    ) -> Result<Value, QueryError> {
        match expr {
            Expr::Column(name) => {
                let upper_name = name.to_uppercase();
                // Try alias first, then column name
                if let Some(&idx) = alias_map.get(&upper_name) {
                    Ok(row.get(idx).cloned().unwrap_or(Value::Null))
                } else if let Some(&idx) = col_map.get(&upper_name) {
                    Ok(row.get(idx).cloned().unwrap_or(Value::Null))
                } else {
                    Err(QueryError::Evaluation(format!(
                        "Column '{name}' not found in SELECT clause for HAVING"
                    )))
                }
            }
            Expr::Literal(lit) => self.evaluate_literal(lit),
            Expr::Function(_) => {
                // Re-evaluate aggregate function on group
                self.evaluate_aggregate_expr(expr, group)
            }
            Expr::BinaryOp(op) => {
                let left = self.evaluate_having_expr(&op.left, row, col_map, alias_map, group)?;
                let right = self.evaluate_having_expr(&op.right, row, col_map, alias_map, group)?;
                self.binary_op_on_values(op.op, &left, &right)
            }
            Expr::UnaryOp(op) => {
                let val = self.evaluate_having_expr(&op.operand, row, col_map, alias_map, group)?;
                match op.op {
                    UnaryOperator::Not => {
                        let b = self.to_bool(&val)?;
                        Ok(Value::Boolean(!b))
                    }
                    UnaryOperator::Neg => match val {
                        Value::Number(n) => Ok(Value::Number(-n)),
                        Value::Integer(i) => Ok(Value::Integer(-i)),
                        _ => Err(QueryError::Type(
                            "Cannot negate non-numeric value".to_string(),
                        )),
                    },
                }
            }
            Expr::Paren(inner) => self.evaluate_having_expr(inner, row, col_map, alias_map, group),
            Expr::Wildcard => Err(QueryError::Evaluation(
                "Wildcard not allowed in HAVING clause".to_string(),
            )),
            Expr::Window(_) => Err(QueryError::Evaluation(
                "Window functions not allowed in HAVING clause".to_string(),
            )),
        }
    }

    /// Apply PIVOT BY transformation to results.
    ///
    /// PIVOT BY transforms rows into columns based on pivot column values.
    /// For example: `SELECT account, YEAR(date), SUM(amount) GROUP BY 1, 2 PIVOT BY YEAR(date)`
    /// would create columns for each year.
    fn apply_pivot(
        &self,
        result: &QueryResult,
        pivot_exprs: &[Expr],
        _targets: &[Target],
    ) -> Result<QueryResult, QueryError> {
        if pivot_exprs.is_empty() {
            return Ok(result.clone());
        }

        // For simplicity, we'll pivot on the first expression only
        // A full implementation would support multiple pivot columns
        let pivot_expr = &pivot_exprs[0];

        // Find which column in the result matches the pivot expression
        let pivot_col_idx = self.find_pivot_column(result, pivot_expr)?;

        // Collect unique pivot values
        let mut pivot_values: Vec<Value> = result
            .rows
            .iter()
            .map(|row| row.get(pivot_col_idx).cloned().unwrap_or(Value::Null))
            .collect();
        pivot_values.sort_by(|a, b| self.compare_values_for_sort(a, b));
        pivot_values.dedup();

        // Build new column names: original columns (except pivot) + pivot values
        let mut new_columns: Vec<String> = result
            .columns
            .iter()
            .enumerate()
            .filter(|(i, _)| *i != pivot_col_idx)
            .map(|(_, c)| c.clone())
            .collect();

        // Identify the "value" column (usually the last one, or the one with aggregate)
        let value_col_idx = result.columns.len() - 1;

        // Add pivot value columns
        for pv in &pivot_values {
            new_columns.push(self.value_to_string(pv));
        }

        let mut new_result = QueryResult::new(new_columns);

        // Group rows by non-pivot, non-value columns
        let group_cols: Vec<usize> = (0..result.columns.len())
            .filter(|i| *i != pivot_col_idx && *i != value_col_idx)
            .collect();

        let mut groups: HashMap<String, Vec<&Row>> = HashMap::new();
        for row in &result.rows {
            let key: String = group_cols
                .iter()
                .map(|&i| self.value_to_string(&row[i]))
                .collect::<Vec<_>>()
                .join("|");
            groups.entry(key).or_default().push(row);
        }

        // Build pivoted rows
        for (_key, group_rows) in groups {
            let mut new_row: Vec<Value> = group_cols
                .iter()
                .map(|&i| group_rows[0][i].clone())
                .collect();

            // Build O(1) pivot value -> row index for this group
            let pivot_index: HashMap<u64, usize> = group_rows
                .iter()
                .enumerate()
                .filter_map(|(idx, row)| {
                    row.get(pivot_col_idx).map(|v| (hash_single_value(v), idx))
                })
                .collect();

            // Add pivot values with O(1) lookup
            for pv in &pivot_values {
                let pv_hash = hash_single_value(pv);
                if let Some(&row_idx) = pivot_index.get(&pv_hash) {
                    new_row.push(
                        group_rows[row_idx]
                            .get(value_col_idx)
                            .cloned()
                            .unwrap_or(Value::Null),
                    );
                } else {
                    new_row.push(Value::Null);
                }
            }

            new_result.add_row(new_row);
        }

        Ok(new_result)
    }

    /// Find the column index matching the pivot expression.
    fn find_pivot_column(
        &self,
        result: &QueryResult,
        pivot_expr: &Expr,
    ) -> Result<usize, QueryError> {
        match pivot_expr {
            Expr::Column(name) => {
                let upper_name = name.to_uppercase();
                result
                    .columns
                    .iter()
                    .position(|c| c.to_uppercase() == upper_name)
                    .ok_or_else(|| {
                        QueryError::Evaluation(format!(
                            "PIVOT BY column '{name}' not found in SELECT"
                        ))
                    })
            }
            Expr::Literal(Literal::Integer(n)) => {
                let idx = (*n as usize).saturating_sub(1);
                if idx < result.columns.len() {
                    Ok(idx)
                } else {
                    Err(QueryError::Evaluation(format!(
                        "PIVOT BY column index {n} out of range"
                    )))
                }
            }
            Expr::Literal(Literal::Number(n)) => {
                // Numbers are parsed as Decimal - convert to integer index
                use rust_decimal::prelude::ToPrimitive;
                let idx = n.to_usize().unwrap_or(0).saturating_sub(1);
                if idx < result.columns.len() {
                    Ok(idx)
                } else {
                    Err(QueryError::Evaluation(format!(
                        "PIVOT BY column index {n} out of range"
                    )))
                }
            }
            _ => {
                // For complex expressions, try to find a matching column by string representation
                // This is a simplified approach
                Err(QueryError::Evaluation(
                    "PIVOT BY must reference a column name or index".to_string(),
                ))
            }
        }
    }

    /// Convert a value to string for display/grouping.
    fn value_to_string(&self, val: &Value) -> String {
        match val {
            Value::String(s) => s.clone(),
            Value::Number(n) => n.to_string(),
            Value::Integer(i) => i.to_string(),
            Value::Date(d) => d.to_string(),
            Value::Boolean(b) => b.to_string(),
            Value::Amount(a) => format!("{} {}", a.number, a.currency),
            Value::Position(p) => format!("{}", p.units),
            Value::Inventory(inv) => inv
                .positions()
                .iter()
                .map(|p| format!("{}", p.units))
                .collect::<Vec<_>>()
                .join(", "),
            Value::StringSet(ss) => ss.join(", "),
            Value::Null => "NULL".to_string(),
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::parse;
    use rust_decimal_macros::dec;
    use rustledger_core::Posting;

    fn date(year: i32, month: u32, day: u32) -> NaiveDate {
        NaiveDate::from_ymd_opt(year, month, day).unwrap()
    }

    fn sample_directives() -> Vec<Directive> {
        vec![
            Directive::Transaction(
                Transaction::new(date(2024, 1, 15), "Coffee")
                    .with_flag('*')
                    .with_payee("Coffee Shop")
                    .with_posting(Posting::new(
                        "Expenses:Food:Coffee",
                        Amount::new(dec!(5.00), "USD"),
                    ))
                    .with_posting(Posting::new(
                        "Assets:Bank:Checking",
                        Amount::new(dec!(-5.00), "USD"),
                    )),
            ),
            Directive::Transaction(
                Transaction::new(date(2024, 1, 16), "Groceries")
                    .with_flag('*')
                    .with_payee("Supermarket")
                    .with_posting(Posting::new(
                        "Expenses:Food:Groceries",
                        Amount::new(dec!(50.00), "USD"),
                    ))
                    .with_posting(Posting::new(
                        "Assets:Bank:Checking",
                        Amount::new(dec!(-50.00), "USD"),
                    )),
            ),
        ]
    }

    #[test]
    fn test_simple_select() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        let query = parse("SELECT date, account").unwrap();
        let result = executor.execute(&query).unwrap();

        assert_eq!(result.columns, vec!["date", "account"]);
        assert_eq!(result.len(), 4); // 2 transactions × 2 postings
    }

    #[test]
    fn test_where_clause() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        let query = parse("SELECT account WHERE account ~ \"Expenses:\"").unwrap();
        let result = executor.execute(&query).unwrap();

        assert_eq!(result.len(), 2); // Only expense postings
    }

    #[test]
    fn test_balances() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        let query = parse("BALANCES").unwrap();
        let result = executor.execute(&query).unwrap();

        assert_eq!(result.columns, vec!["account", "balance"]);
        assert!(result.len() >= 3); // At least 3 accounts
    }

    #[test]
    fn test_account_functions() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Test LEAF function
        let query = parse("SELECT DISTINCT LEAF(account) WHERE account ~ \"Expenses:\"").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2); // Coffee, Groceries

        // Test ROOT function
        let query = parse("SELECT DISTINCT ROOT(account)").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2); // Expenses, Assets

        // Test PARENT function
        let query = parse("SELECT DISTINCT PARENT(account) WHERE account ~ \"Expenses:\"").unwrap();
        let result = executor.execute(&query).unwrap();
        assert!(!result.is_empty()); // At least "Expenses:Food"
    }

    #[test]
    fn test_min_max_aggregate() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Test MIN(date)
        let query = parse("SELECT MIN(date)").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 1);
        assert_eq!(result.rows[0][0], Value::Date(date(2024, 1, 15)));

        // Test MAX(date)
        let query = parse("SELECT MAX(date)").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 1);
        assert_eq!(result.rows[0][0], Value::Date(date(2024, 1, 16)));
    }

    #[test]
    fn test_order_by() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        let query = parse("SELECT date, account ORDER BY date DESC").unwrap();
        let result = executor.execute(&query).unwrap();

        // Should have all postings, ordered by date descending
        assert_eq!(result.len(), 4);
        // First row should be from 2024-01-16 (later date)
        assert_eq!(result.rows[0][0], Value::Date(date(2024, 1, 16)));
    }

    #[test]
    fn test_hash_value_all_variants() {
        use rustledger_core::{Cost, Inventory, Position};

        // Test that all Value variants can be hashed without panic
        let values = vec![
            Value::String("test".to_string()),
            Value::Number(dec!(123.45)),
            Value::Integer(42),
            Value::Date(date(2024, 1, 15)),
            Value::Boolean(true),
            Value::Boolean(false),
            Value::Amount(Amount::new(dec!(100), "USD")),
            Value::Position(Position::simple(Amount::new(dec!(10), "AAPL"))),
            Value::Position(Position::with_cost(
                Amount::new(dec!(10), "AAPL"),
                Cost::new(dec!(150), "USD"),
            )),
            Value::Inventory(Inventory::new()),
            Value::StringSet(vec!["tag1".to_string(), "tag2".to_string()]),
            Value::Null,
        ];

        // Hash each value and verify no panic
        for value in &values {
            let hash = hash_single_value(value);
            assert!(hash != 0 || matches!(value, Value::Null));
        }

        // Test that different values produce different hashes (usually)
        let hash1 = hash_single_value(&Value::String("a".to_string()));
        let hash2 = hash_single_value(&Value::String("b".to_string()));
        assert_ne!(hash1, hash2);

        // Test that same values produce same hashes
        let hash3 = hash_single_value(&Value::Integer(42));
        let hash4 = hash_single_value(&Value::Integer(42));
        assert_eq!(hash3, hash4);
    }

    #[test]
    fn test_hash_row_distinct() {
        // Test hash_row for DISTINCT deduplication
        let row1 = vec![Value::String("a".to_string()), Value::Integer(1)];
        let row2 = vec![Value::String("a".to_string()), Value::Integer(1)];
        let row3 = vec![Value::String("b".to_string()), Value::Integer(1)];

        assert_eq!(hash_row(&row1), hash_row(&row2));
        assert_ne!(hash_row(&row1), hash_row(&row3));
    }

    #[test]
    fn test_string_set_hash_order_independent() {
        // StringSet hash should be order-independent
        let set1 = Value::StringSet(vec!["a".to_string(), "b".to_string(), "c".to_string()]);
        let set2 = Value::StringSet(vec!["c".to_string(), "a".to_string(), "b".to_string()]);
        let set3 = Value::StringSet(vec!["b".to_string(), "c".to_string(), "a".to_string()]);

        let hash1 = hash_single_value(&set1);
        let hash2 = hash_single_value(&set2);
        let hash3 = hash_single_value(&set3);

        assert_eq!(hash1, hash2);
        assert_eq!(hash2, hash3);
    }

    #[test]
    fn test_inventory_hash_includes_cost() {
        use rustledger_core::{Cost, Inventory, Position};

        // Two inventories with same units but different costs should hash differently
        let mut inv1 = Inventory::new();
        inv1.add(Position::with_cost(
            Amount::new(dec!(10), "AAPL"),
            Cost::new(dec!(100), "USD"),
        ));

        let mut inv2 = Inventory::new();
        inv2.add(Position::with_cost(
            Amount::new(dec!(10), "AAPL"),
            Cost::new(dec!(200), "USD"),
        ));

        let hash1 = hash_single_value(&Value::Inventory(inv1));
        let hash2 = hash_single_value(&Value::Inventory(inv2));

        assert_ne!(hash1, hash2);
    }

    #[test]
    fn test_distinct_deduplication() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Without DISTINCT - should have duplicates (same flag '*' for all)
        let query = parse("SELECT flag").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 4); // One per posting, all have flag '*'

        // With DISTINCT - should deduplicate
        let query = parse("SELECT DISTINCT flag").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 1); // Deduplicated to 1 (all '*')
    }

    #[test]
    fn test_limit_clause() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Test LIMIT restricts result count
        let query = parse("SELECT date, account LIMIT 2").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2);

        // Test LIMIT 0 returns empty
        let query = parse("SELECT date LIMIT 0").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 0);

        // Test LIMIT larger than result set returns all
        let query = parse("SELECT date LIMIT 100").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 4);
    }

    #[test]
    fn test_group_by_with_count() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Group by account root and count postings
        let query = parse("SELECT ROOT(account), COUNT(account) GROUP BY ROOT(account)").unwrap();
        let result = executor.execute(&query).unwrap();

        assert_eq!(result.columns.len(), 2);
        // Should have 2 groups: Assets and Expenses
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_count_aggregate() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Count all postings
        let query = parse("SELECT COUNT(account)").unwrap();
        let result = executor.execute(&query).unwrap();

        assert_eq!(result.len(), 1);
        assert_eq!(result.rows[0][0], Value::Integer(4));

        // Count with GROUP BY
        let query = parse("SELECT ROOT(account), COUNT(account) GROUP BY ROOT(account)").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2); // Assets, Expenses
    }

    #[test]
    fn test_journal_query() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // JOURNAL for Expenses account
        let query = parse("JOURNAL \"Expenses\"").unwrap();
        let result = executor.execute(&query).unwrap();

        // Should have columns for journal output
        assert!(result.columns.contains(&"account".to_string()));
        // Should only show expense account entries
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_print_query() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // PRINT outputs formatted directives
        let query = parse("PRINT").unwrap();
        let result = executor.execute(&query).unwrap();

        // PRINT returns single column "directive" with formatted output
        assert_eq!(result.columns.len(), 1);
        assert_eq!(result.columns[0], "directive");
        // Should have one row per directive (2 transactions)
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_empty_directives() {
        let directives: Vec<Directive> = vec![];
        let mut executor = Executor::new(&directives);

        // SELECT on empty directives
        let query = parse("SELECT date, account").unwrap();
        let result = executor.execute(&query).unwrap();
        assert!(result.is_empty());

        // BALANCES on empty directives
        let query = parse("BALANCES").unwrap();
        let result = executor.execute(&query).unwrap();
        assert!(result.is_empty());
    }

    #[test]
    fn test_comparison_operators() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Less than comparison on dates
        let query = parse("SELECT date WHERE date < 2024-01-16").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2); // First transaction postings

        // Greater than comparison on year
        let query = parse("SELECT date WHERE year > 2023").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 4); // All 2024 postings

        // Equality comparison on day
        let query = parse("SELECT account WHERE day = 15").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2); // First transaction postings (Jan 15)
    }

    #[test]
    fn test_logical_operators() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // AND operator
        let query = parse("SELECT account WHERE account ~ \"Expenses\" AND day > 14").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2); // Expense postings on Jan 15 and 16

        // OR operator
        let query = parse("SELECT account WHERE day = 15 OR day = 16").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 4); // All postings (both days)
    }

    #[test]
    fn test_arithmetic_expressions() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // Negation on integer
        let query = parse("SELECT -day WHERE day = 15").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 2);
        // Day 15 negated should be -15
        for row in &result.rows {
            if let Value::Integer(n) = &row[0] {
                assert_eq!(*n, -15);
            }
        }
    }

    #[test]
    fn test_first_last_aggregates() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // FIRST aggregate
        let query = parse("SELECT FIRST(date)").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 1);
        assert_eq!(result.rows[0][0], Value::Date(date(2024, 1, 15)));

        // LAST aggregate
        let query = parse("SELECT LAST(date)").unwrap();
        let result = executor.execute(&query).unwrap();
        assert_eq!(result.len(), 1);
        assert_eq!(result.rows[0][0], Value::Date(date(2024, 1, 16)));
    }

    #[test]
    fn test_wildcard_select() {
        let directives = sample_directives();
        let mut executor = Executor::new(&directives);

        // SELECT * returns all postings with wildcard column name
        let query = parse("SELECT *").unwrap();
        let result = executor.execute(&query).unwrap();

        // Wildcard produces column name "*"
        assert_eq!(result.columns, vec!["*"]);
        // But each row has expanded values (date, flag, payee, narration, account, position)
        assert_eq!(result.len(), 4);
        assert_eq!(result.rows[0].len(), 6); // 6 expanded values
    }

    #[test]
    fn test_query_result_methods() {
        let mut result = QueryResult::new(vec!["col1".to_string(), "col2".to_string()]);

        // Initially empty
        assert!(result.is_empty());
        assert_eq!(result.len(), 0);

        // Add rows
        result.add_row(vec![Value::Integer(1), Value::String("a".to_string())]);
        assert!(!result.is_empty());
        assert_eq!(result.len(), 1);

        result.add_row(vec![Value::Integer(2), Value::String("b".to_string())]);
        assert_eq!(result.len(), 2);
    }
}