logisheets_controller 1.16.1

use std::collections::{HashMap, HashSet};

use logisheets_base::async_func::{AsyncCalcResult, AsyncFuncCommitTrait, Task};
use logisheets_base::block_ref::BlockRefTrait;
use logisheets_base::errors::BasicError;
use logisheets_base::get_curr_addr::GetCurrAddrTrait;
use logisheets_base::set_curr_cell::SetCurrCellTrait;
use logisheets_base::{
    Addr, CellId, CellValue, Error, FuncId, NameId, SheetId, TextId,
    cube_value::CubeValue,
    matrix_value::{MatrixValue, cross_product_usize},
};
use logisheets_base::{BlockCellId, BlockId, BlockRange, CubeCross, NormalRange, Range};
use logisheets_parser::ast;

use logisheets_parser::unparse;

use crate::block_manager::schema_manager::SchemaManager;
use crate::cube_manager::CubeManager;
use crate::ext_ref_manager::ExtRefManager;
use crate::formula_manager::FormulaManager;
use crate::id_manager::{NameIdManager, SheetIdManager};
use crate::navigator::BlockPlace;
use crate::range_manager::RangeManager;

use super::NameFetcher;
use crate::{
    async_func_manager::AsyncFuncManager,
    calc_engine::calculator::calc_vertex::Value,
    calc_engine::calculator::calc_vertex::{
        CalcReference, CalcValue, CalcVertex, ColRange, Reference, RowRange,
    },
    calc_engine::connector::Connector,
    cell::Cell,
    container::DataContainer,
    ext_book_manager::ExtBooksManager,
    id_manager::{FuncIdManager, TextIdManager},
    navigator::Navigator,
    workbook::sheet_info_manager::SheetInfoManager,
};

use crate::errors::Result;

#[allow(unused)]
pub struct CalcConnector<'a> {
    pub range_manager: &'a RangeManager,
    pub cube_manager: &'a CubeManager,
    pub navigator: &'a mut Navigator,
    pub container: &'a mut DataContainer,
    pub ext_links: &'a mut ExtBooksManager,
    pub text_id_manager: &'a mut TextIdManager,
    pub func_id_manager: &'a FuncIdManager,
    pub sheet_id_manager: &'a SheetIdManager,
    pub names_storage: HashMap<NameId, CalcValue>,
    pub cells_storage: HashMap<(SheetId, CellId), CalcValue>,
    pub sheet_pos_manager: &'a SheetInfoManager,
    pub async_func_manager: &'a mut AsyncFuncManager,
    pub async_funcs: &'a HashSet<String>,
    pub block_schema_manager: &'a SchemaManager,
    // Read-only managers needed to unparse a cell's formula back to text
    // (FORMULATEXT). The calc engine otherwise works on values, not source.
    pub formula_manager: &'a FormulaManager,
    pub name_id_manager: &'a NameIdManager,
    pub ext_ref_manager: &'a ExtRefManager,
    pub active_sheet: SheetId,
    pub curr_addr: Addr,

    pub dirty_cells_in_next_run: &'a mut imbl::HashSet<(SheetId, CellId)>,
    pub calc_cells: &'a mut HashSet<(SheetId, CellId)>,
}

impl<'a> GetCurrAddrTrait for CalcConnector<'a> {
    fn get_curr_addr(&self) -> Addr {
        self.curr_addr
    }
}

impl<'a> SetCurrCellTrait for CalcConnector<'a> {
    fn set_curr_cell(&mut self, active_sheet: SheetId, addr: Addr) {
        self.active_sheet = active_sheet;
        self.curr_addr = addr;
    }
}

impl<'a> AsyncFuncCommitTrait for CalcConnector<'a> {
    fn query_or_commit_task(
        &mut self,
        sheet_id: SheetId,
        cell_id: CellId,
        task: Task,
    ) -> Option<AsyncCalcResult> {
        self.async_func_manager
            .query_or_commit_task(task, sheet_id, cell_id)
    }
}

impl<'a> CalcConnector<'a> {
    /// For a block range that spans a full column from the block's TOP row,
    /// return the block's CURRENT bottom-row cell in that same column.
    ///
    /// A linked record column resolves to a block range with fixed cell ids; when
    /// the block grows (rows appended at the tail), those ids don't move, so the
    /// stored range would miss the new rows. Re-deriving the bottom from the
    /// block's current size at calc time makes the reference behave like a whole
    /// growable column. Returns `None` (leave the range as-is) for anything that
    /// isn't a single full-height column of one block.
    fn grow_block_column_end(
        &self,
        sheet_id: SheetId,
        start: BlockCellId,
        end: BlockCellId,
    ) -> Option<BlockCellId> {
        if start.block_id != end.block_id || start.col != end.col {
            return None;
        }
        let block_id = start.block_id;
        let master = self.navigator.get_master_cell(&sheet_id, &block_id).ok()?;
        let (m_row, m_col) = self
            .navigator
            .fetch_normal_cell_idx(&sheet_id, &master)
            .ok()?;
        let (s_row, s_col) = self
            .navigator
            .fetch_block_cell_idx(&sheet_id, &start)
            .ok()?;
        // Only a column anchored at the block's top row is a record column.
        if s_row != m_row {
            return None;
        }
        let (row_cnt, _) = self.navigator.get_block_size(&sheet_id, &block_id).ok()?;
        if row_cnt == 0 {
            return None;
        }
        let inner_col = s_col.checked_sub(m_col)?;
        self.navigator
            .fetch_block_cell_id(&sheet_id, &block_id, row_cnt - 1, inner_col)
            .ok()
    }

    /// Build a `CalcReference` for a block range on `sheet` (its OWN sheet — may
    /// differ from the referencing formula's sheet, for a cross-sheet link). An
    /// `AddrRange` re-grows its bottom to the block's current last row.
    fn block_reference(&self, sheet: SheetId, block_range: BlockRange) -> CalcVertex {
        match block_range {
            BlockRange::Single(cell) => {
                let Ok((row, col)) = self
                    .navigator
                    .fetch_cell_idx(&sheet, &CellId::BlockCell(cell))
                else {
                    return CalcVertex::from_error(ast::Error::Ref);
                };
                CalcVertex::Reference(CalcReference {
                    from_sheet: None,
                    sheet,
                    reference: Reference::Addr(Addr { row, col }),
                })
            }
            BlockRange::AddrRange(start, end) => {
                let end = self.grow_block_column_end(sheet, start, end).unwrap_or(end);
                let (Ok((start_row, start_col)), Ok((end_row, end_col))) = (
                    self.navigator
                        .fetch_cell_idx(&sheet, &CellId::BlockCell(start)),
                    self.navigator
                        .fetch_cell_idx(&sheet, &CellId::BlockCell(end)),
                ) else {
                    return CalcVertex::from_error(ast::Error::Ref);
                };
                CalcVertex::Reference(CalcReference {
                    from_sheet: None,
                    sheet,
                    reference: Reference::Range(
                        Addr {
                            row: start_row,
                            col: start_col,
                        },
                        Addr {
                            row: end_row,
                            col: end_col,
                        },
                    ),
                })
            }
        }
    }
}

impl<'a> Connector for CalcConnector<'a> {
    fn convert(&mut self, cr: &ast::CellReference) -> CalcVertex {
        match cr {
            ast::CellReference::Mut(range_display) => {
                let sheet_id = range_display.sheet_id;
                let range_id = range_display.range_id;
                let range = self.range_manager.get_range(&sheet_id, &range_id);
                match range {
                    Some(range) => {
                        // Endpoints may refer to row/col ids that
                        // were removed since the range was registered
                        // (e.g. DeleteRowsInBlock). Fail closed with
                        // #REF! rather than panicking; the formula
                        // result becomes an error value, which beats
                        // a thread panic and matches the `None` arm
                        // below.
                        //
                        // First resolve a linked record column to its block
                        // column (so the reference reads the block), or reject an
                        // invalid record reference with #VALUE!. The formula's own
                        // range id stays literal (`A1:A2`); this redirect is purely
                        // resolution-time, so save/load keeps the facade.
                        let range = if let Range::Normal(nr) = &range {
                            use crate::range_manager::link::{LinkRef, NavLink, resolve_normal};
                            let res =
                                self.range_manager
                                    .get_sheet_manager_assert(&sheet_id)
                                    .map(|m| {
                                        resolve_normal(
                                            &NavLink(self.navigator),
                                            sheet_id,
                                            &m.links,
                                            nr,
                                        )
                                    });
                            match res {
                                Some(LinkRef::Invalid) => {
                                    return CalcVertex::from_error(ast::Error::Value);
                                }
                                // The linked block may be on ANOTHER sheet — build
                                // the reference on the block's own sheet directly.
                                Some(LinkRef::Column { sheet: tgt, block }) => {
                                    return self.block_reference(tgt, block);
                                }
                                _ => range,
                            }
                        } else {
                            range
                        };
                        match range {
                            Range::Normal(nomral_range) => match nomral_range {
                                NormalRange::Single(normal_cell_id) => {
                                    let Ok((row, col)) = self
                                        .navigator
                                        .fetch_normal_cell_idx(&sheet_id, &normal_cell_id)
                                    else {
                                        return CalcVertex::from_error(ast::Error::Ref);
                                    };
                                    CalcVertex::Reference(CalcReference {
                                        from_sheet: None,
                                        sheet: sheet_id,
                                        reference: Reference::Addr(Addr { row, col }),
                                    })
                                }
                                NormalRange::RowRange(start_id, end_id) => {
                                    let (Ok(start), Ok(end)) = (
                                        self.navigator.fetch_row_idx(&sheet_id, &start_id),
                                        self.navigator.fetch_row_idx(&sheet_id, &end_id),
                                    ) else {
                                        return CalcVertex::from_error(ast::Error::Ref);
                                    };
                                    CalcVertex::Reference(CalcReference {
                                        from_sheet: None,
                                        sheet: sheet_id,
                                        reference: Reference::RowRange(RowRange { start, end }),
                                    })
                                }
                                NormalRange::ColRange(start_id, end_id) => {
                                    let (Ok(start), Ok(end)) = (
                                        self.navigator.fetch_col_idx(&sheet_id, &start_id),
                                        self.navigator.fetch_col_idx(&sheet_id, &end_id),
                                    ) else {
                                        return CalcVertex::from_error(ast::Error::Ref);
                                    };
                                    CalcVertex::Reference(CalcReference {
                                        from_sheet: None,
                                        sheet: sheet_id,
                                        reference: Reference::ColumnRange(ColRange { start, end }),
                                    })
                                }
                                NormalRange::AddrRange(start, end) => {
                                    let (Ok((start_row, start_col)), Ok((end_row, end_col))) = (
                                        self.navigator.fetch_normal_cell_idx(&sheet_id, &start),
                                        self.navigator.fetch_normal_cell_idx(&sheet_id, &end),
                                    ) else {
                                        return CalcVertex::from_error(ast::Error::Ref);
                                    };
                                    CalcVertex::Reference(CalcReference {
                                        from_sheet: None,
                                        sheet: sheet_id,
                                        reference: Reference::Range(
                                            Addr {
                                                row: start_row,
                                                col: start_col,
                                            },
                                            Addr {
                                                row: end_row,
                                                col: end_col,
                                            },
                                        ),
                                    })
                                }
                            },
                            Range::Block(block_range) => {
                                self.block_reference(sheet_id, block_range)
                            }
                            Range::Ephemeral(id) => CalcVertex::Ephemeral((sheet_id, id)),
                        }
                    }
                    None => CalcVertex::from_error(ast::Error::Ref),
                }
            }
            ast::CellReference::UnMut(cube) => {
                let cube_id = cube.cube_id;
                let Some(cube) = self.cube_manager.get_cube(&cube_id) else {
                    return CalcVertex::from_error(ast::Error::Ref);
                };
                match cube.cross {
                    CubeCross::Single(row, col) => CalcVertex::Reference(CalcReference {
                        from_sheet: Some(cube.from_sheet),
                        sheet: cube.to_sheet,
                        reference: Reference::Addr(Addr { row, col }),
                    }),
                    CubeCross::RowRange(start, end) => CalcVertex::Reference(CalcReference {
                        from_sheet: Some(cube.from_sheet),
                        sheet: cube.to_sheet,
                        reference: Reference::RowRange(RowRange { start, end }),
                    }),
                    CubeCross::ColRange(start, end) => CalcVertex::Reference(CalcReference {
                        from_sheet: Some(cube.from_sheet),
                        sheet: cube.to_sheet,
                        reference: Reference::ColumnRange(ColRange { start, end }),
                    }),
                    CubeCross::AddrRange(start, end) => CalcVertex::Reference(CalcReference {
                        from_sheet: Some(cube.from_sheet),
                        sheet: cube.to_sheet,
                        reference: Reference::Range(
                            Addr {
                                row: start.row,
                                col: start.col,
                            },
                            Addr {
                                row: end.row,
                                col: end.col,
                            },
                        ),
                    }),
                }
            }
            // Other workbooks are never loaded, so there is nothing to read:
            // `ExtBooksManager` answers every lookup with #REF! as well.
            ast::CellReference::Ext(_) => CalcVertex::from_error(ast::Error::Ref),
            // A defined name evaluates its definition in place — a reference
            // stays a reference, so `SUM(Sales)` and `ROWS(Sales)` see a range
            // rather than a value. An undefined name is `#NAME?`. A definition
            // cannot reach itself: `DefineName` and the loader refuse that.
            ast::CellReference::Name(id) => match self.formula_manager.names.get(id) {
                Some(def) => {
                    let def = def.clone();
                    crate::calc_engine::calculator::calculator::calc_node(&def, self)
                }
                None => CalcVertex::from_error(ast::Error::Name),
            },
            ast::CellReference::RefErr => CalcVertex::from_error(ast::Error::Ref),
        }
    }

    fn get_calc_value(&mut self, vertex: CalcVertex) -> CalcValue {
        match vertex {
            CalcVertex::Value(v) => v,
            CalcVertex::Union(union) => {
                let values = union
                    .into_iter()
                    .map(|arg| Box::new(self.get_calc_value(*arg)))
                    .collect::<Vec<_>>();
                CalcValue::Union(values)
            }
            CalcVertex::Reference(r) => {
                let sheet_id = r.sheet;
                match r.reference {
                    Reference::Addr(addr) => match r.from_sheet {
                        Some(from_sheet) => self.get_cube_value(from_sheet, sheet_id, addr, addr),
                        None => self.get_sheet_calc_range_value(
                            sheet_id, addr.row, addr.col, addr.row, addr.col,
                        ),
                    },
                    Reference::ColumnRange(cr) => {
                        let start = cr.start;
                        let end = cr.end;
                        let v = self.get_matrix_between_cols(sheet_id, start, end);
                        CalcValue::Range(v)
                    }
                    Reference::RowRange(rr) => {
                        let start = rr.start;
                        let end = rr.end;
                        let v = self.get_matrix_between_rows(sheet_id, start, end);
                        CalcValue::Range(v)
                    }
                    Reference::Range(start, end) => match r.from_sheet {
                        Some(from_sheet) => self.get_cube_value(from_sheet, sheet_id, start, end),
                        None => self.get_sheet_calc_range_value(
                            sheet_id, start.row, start.col, end.row, end.col,
                        ),
                    },
                }
            }
            CalcVertex::Ephemeral((sheet_id, id)) => {
                let cell_id = CellId::EphemeralCell(id);
                let container = self.container.get_sheet_container_mut(sheet_id);
                let v = container.cells.get(&cell_id);
                match v {
                    Some(v) => {
                        let cell_value = v.value.clone();
                        let value = Value::from_cell_value(cell_value, &|_| None);
                        CalcValue::Scalar(value)
                    }
                    None => CalcValue::Scalar(Value::Error(ast::Error::Ref)),
                }
            }
        }
    }

    #[inline]
    fn get_func_name(&self, fid: &FuncId) -> Result<String> {
        self.func_id_manager
            .get_string(fid)
            .ok_or(BasicError::FuncIdNotFound(*fid).into())
    }

    fn get_cell_idx(
        &self,
        sheet_id: SheetId,
        cell_id: &logisheets_base::CellId,
    ) -> Result<(usize, usize)> {
        self.navigator
            .fetch_cell_idx(&sheet_id, cell_id)
            .map_err(|e| e.into())
    }

    #[inline]
    fn get_cell_id(&self, sheet_id: SheetId, row: usize, col: usize) -> Result<CellId> {
        self.navigator
            .fetch_cell_id(&sheet_id, row, col)
            .map_err(|e| e.into())
    }

    fn commit_calc_values(&mut self, vertex: (SheetId, CellId), result: CalcValue) {
        self.calc_cells.insert(vertex);

        let sheet_id = vertex.0;
        let cell_id = vertex.1;
        match result {
            CalcValue::Scalar(v) => {
                let cell_value =
                    value_to_cell_value(v, &mut |t| self.text_id_manager.get_or_register_id(&t));
                self.set_cell_value(sheet_id, cell_id, cell_value);
                self.calc_cells.insert(vertex);
            }
            CalcValue::Range(_) => {
                self.set_cell_value(sheet_id, cell_id, CellValue::Error(Error::Value));
            }
            CalcValue::Union(_) => {
                self.set_cell_value(sheet_id, cell_id, CellValue::Error(Error::Value));
            }
            CalcValue::Cube(_) => {
                self.set_cell_value(sheet_id, cell_id, CellValue::Error(Error::Value));
            }
        }
    }

    fn is_async_func(&self, func_name: &str) -> bool {
        self.async_funcs.get(func_name).is_some()
    }

    fn get_range(&self, sheet_id: &SheetId, range: &u32) -> Option<Range> {
        self.range_manager.get_range(sheet_id, range)
    }

    fn get_sheet_id_by_name(&self, name: &str) -> Result<SheetId> {
        let id = self.sheet_id_manager.get_id(name);
        match id {
            Some(id) => Ok(*id),
            None => Err(BasicError::SheetNameNotFound(name.to_string()).into()),
        }
    }

    fn get_formula_string(&self, sheet_id: SheetId, cell_id: &CellId) -> Option<String> {
        let node = self.formula_manager.formulas.get(&(sheet_id, *cell_id))?;
        // Reborrow the mutably-held managers as shared for the name fetcher.
        let mut name_fetcher = NameFetcher {
            func_manager: self.func_id_manager,
            range_manager: self.range_manager,
            cube_manager: self.cube_manager,
            ext_ref_manager: self.ext_ref_manager,
            sheet_id_manager: self.sheet_id_manager,
            external_links_manager: &*self.ext_links,
            text_id_manager: &*self.text_id_manager,
            name_id_manager: self.name_id_manager,
            navigator: &*self.navigator,
            block_schema_manager: self.block_schema_manager,
        };
        unparse::unparse(node, &mut name_fetcher, sheet_id).ok()
    }

    fn has_formula(&self, sheet_id: SheetId, cell_id: &CellId) -> bool {
        self.formula_manager
            .formulas
            .contains_key(&(sheet_id, *cell_id))
    }

    fn set_curr_as_dirty(&mut self) -> Result<()> {
        let Addr { row, col } = self.get_curr_addr();
        let sheet_id = self.get_active_sheet();
        let cell_id = self.get_cell_id(sheet_id, row, col)?;
        self.dirty_cells_in_next_run.insert((sheet_id, cell_id));
        Ok(())
    }

    fn get_active_sheet(&self) -> SheetId {
        self.active_sheet
    }

    fn get_block_cell_value(
        &mut self,
        sheet_id: SheetId,
        cell_id: BlockCellId,
    ) -> Option<CalcValue> {
        let sheet = self.container.get_sheet_container_mut(sheet_id);
        if let Some(cell) = sheet.cells.get(&CellId::BlockCell(cell_id)) {
            let cell_value = cell.value.clone();
            let value =
                Value::from_cell_value(cell_value, &|id| self.text_id_manager.get_string(id));
            Some(CalcValue::Scalar(value))
        } else {
            None
        }
    }
}

impl<'a> CalcConnector<'a> {
    /// A 3D reference (`Sheet1:Sheet3!A1:B2`): the same rectangle on every
    /// sheet from `from_sheet` to `to_sheet` in tab order, one matrix per
    /// sheet. #REF! when either end sheet no longer exists.
    fn get_cube_value(
        &mut self,
        from_sheet: SheetId,
        to_sheet: SheetId,
        start: Addr,
        end: Addr,
    ) -> CalcValue {
        let sheets = self.get_sheet_ids(from_sheet, to_sheet);
        if sheets.is_empty() {
            return CalcValue::Scalar(Value::Error(ast::Error::Ref));
        }
        let data = sheets
            .into_iter()
            .map(|s| {
                match self.get_sheet_calc_range_value(s, start.row, start.col, end.row, end.col) {
                    CalcValue::Range(m) => m,
                    CalcValue::Scalar(v) => MatrixValue::from(vec![vec![v]]),
                    // `get_sheet_calc_range_value` only builds the two above.
                    _ => MatrixValue::from(vec![vec![Value::Error(ast::Error::Ref)]]),
                }
            })
            .collect();
        CalcValue::Cube(CubeValue::new(data))
    }

    /// The sheets between `start` and `end` in tab order, inclusive, in
    /// either order (`Sheet3:Sheet1` spans the same sheets as `Sheet1:Sheet3`).
    /// Empty when either sheet is gone.
    fn get_sheet_ids(&self, start: SheetId, end: SheetId) -> Vec<SheetId> {
        let start_idx = self.sheet_pos_manager.get_sheet_idx(&start);
        let end_idx = self.sheet_pos_manager.get_sheet_idx(&end);
        let mut result: Vec<SheetId> = Vec::new();
        match (start_idx, end_idx) {
            (Some(a), Some(b)) => {
                let (s_idx, e_idx) = (a.min(b), a.max(b));
                (s_idx..e_idx + 1).into_iter().for_each(|i| {
                    if let Some(id) = self.sheet_pos_manager.get_sheet_id(i) {
                        result.push(id)
                    }
                });
                result
            }
            _ => vec![],
        }
    }

    fn get_matrix_between_rows(
        &mut self,
        sheet_id: SheetId,
        row_start: usize,
        row_end: usize,
    ) -> MatrixValue<Value> {
        let sheet_container = self.container.data.get(&sheet_id);
        if sheet_container.is_none() {
            return MatrixValue::new(0, 0);
        }
        let sheet_container = sheet_container.unwrap().clone();
        // todo!() usize::MAX
        let mut matrix = MatrixValue::<Value>::new(row_end - row_start + 1, 65535);
        sheet_container.cells.iter().for_each(|(id, cell)| {
            let idx = self.navigator.fetch_cell_idx(&sheet_id, id);
            if let Ok((r, c)) = idx {
                if r >= row_start && r <= row_end {
                    let v = Value::from_cell_value(cell.value.clone(), &|t| {
                        self.text_id_manager.get_string(t)
                    });
                    matrix.insert(r - row_start, c, v);
                }
            }
        });
        matrix
    }

    fn get_matrix_between_cols(
        &mut self,
        sheet_id: SheetId,
        col_start: usize,
        col_end: usize,
    ) -> MatrixValue<Value> {
        let sheet_container = self.container.data.get(&sheet_id);
        if sheet_container.is_none() {
            return MatrixValue::new(0, 0);
        }
        let sheet_container = sheet_container.unwrap().clone();
        // todo!() usize::MAX
        let mut matrix = MatrixValue::<Value>::new(65535, col_end - col_start + 1);
        sheet_container.cells.iter().for_each(|(id, cell)| {
            let idx = self.navigator.fetch_cell_idx(&sheet_id, id);
            if let Ok((r, c)) = idx {
                if c >= col_start && c <= col_end {
                    let v = Value::from_cell_value(cell.value.clone(), &|t| {
                        self.text_id_manager.get_string(t)
                    });
                    matrix.insert(r, c - col_start, v);
                }
            }
        });
        matrix
    }

    fn get_sheet_calc_range_value(
        &mut self,
        sheet_id: SheetId,
        start_row: usize,
        start_col: usize,
        end_row: usize,
        end_col: usize,
    ) -> CalcValue {
        let pos = cross_product_usize(start_row, end_row, start_col, end_col);
        let values = pos
            .into_iter()
            .map(|(r_idx, c_idx)| {
                self.get_cell_value(sheet_id, r_idx, c_idx)
                    .unwrap_or(CellValue::Blank)
            })
            .collect::<Vec<_>>();
        if start_row == end_row && start_col == end_col {
            let cv = values.into_iter().next().unwrap();
            let value = Value::from_cell_value(cv, &|t| self.text_id_manager.get_string(t));
            CalcValue::Scalar(value)
        } else {
            let mut res: Vec<Vec<Value>> = Vec::with_capacity(end_row - start_row + 1);
            let mut values = values.into_iter();
            (start_row..end_row + 1).into_iter().for_each(|_| {
                let mut row = Vec::<Value>::new();
                (start_col..end_col + 1).into_iter().for_each(|_| {
                    let value = Value::from_cell_value(values.next().unwrap(), &|t| {
                        self.text_id_manager.get_string(t)
                    });
                    row.push(value);
                });
                res.push(row)
            });
            CalcValue::Range(MatrixValue::from(res))
        }
    }

    fn get_cell_value(
        &mut self,
        sheet_id: SheetId,
        row_idx: usize,
        col_idx: usize,
    ) -> Result<CellValue> {
        let cid = self.navigator.fetch_cell_id(&sheet_id, row_idx, col_idx)?;
        let sheet = self.container.get_sheet_container_mut(sheet_id);
        if let Some(cell) = sheet.cells.get(&cid) {
            let value = cell.value.clone();
            Ok(value)
        } else {
            Ok(CellValue::Blank)
        }
    }

    fn set_cell_value(&mut self, sheet_id: SheetId, cell_id: CellId, value: CellValue) {
        let sheet = self.container.get_sheet_container_mut(sheet_id);
        if let Some(c) = sheet.cells.get_mut(&cell_id) {
            c.value = value
        } else {
            let mut cell = Cell::default();
            cell.value = value;
            self.container.add_cell(sheet_id, cell_id, cell)
        }
    }
}

fn value_to_cell_value<F>(value: Value, text_converter: &mut F) -> CellValue
where
    F: FnMut(String) -> TextId,
{
    match value {
        Value::Blank => CellValue::Blank,
        Value::Number(f) => CellValue::Number(f),
        Value::Text(text) => CellValue::String(text_converter(text)),
        Value::Boolean(b) => CellValue::Boolean(b),
        Value::Error(e) => match e {
            ast::Error::Unspecified => CellValue::Error(Error::Unspecified),
            ast::Error::Div0 => CellValue::Error(Error::Div0),
            ast::Error::Na => CellValue::Error(Error::NA),
            ast::Error::Name => CellValue::Error(Error::Name),
            ast::Error::Null => CellValue::Error(Error::Null),
            ast::Error::Num => CellValue::Error(Error::Num),
            ast::Error::Ref => CellValue::Error(Error::Ref),
            ast::Error::Value => CellValue::Error(Error::Value),
            ast::Error::GettingData => CellValue::Error(Error::GettingData),
            ast::Error::Placeholder => CellValue::Error(Error::Placeholder),
            // Unsubstituted template placeholders only reach evaluation
            // if a formula was inputted without the per-row template
            // context (i.e. somebody wrote `=#KEY` directly into a cell
            // that isn't a templated block-field). Surface as #NAME?.
            ast::Error::Key => CellValue::Error(Error::Name),
            ast::Error::FieldRef(..) => CellValue::Error(Error::Name),
        },
    }
}

impl<'a> BlockRefTrait for CalcConnector<'a> {
    fn get_all_keys(&self, ref_name: &str) -> Vec<(String, SheetId, BlockCellId)> {
        let f = |sheet_id: &SheetId, block_id: &BlockId| -> Option<&BlockPlace> {
            self.navigator.get_block_place(sheet_id, block_id).ok()
        };
        let (sheet_id, block_ids) = self
            .block_schema_manager
            .get_all_key_cell_ids(ref_name, &f)
            .unwrap_or_default();
        let text_fetcher = |id: TextId| self.text_id_manager.get_string(&id).unwrap().clone();
        block_ids
            .iter()
            .flat_map(|id| {
                let cell = self.container.get_cell(sheet_id, &CellId::BlockCell(*id))?;
                let v = cell.value.to_string(&text_fetcher);
                Some((v, sheet_id, *id))
            })
            .collect()
    }

    fn get_all_fields(&self, ref_name: &str) -> Vec<String> {
        self.block_schema_manager
            .get_all_fields(ref_name)
            .unwrap_or_default()
    }

    fn resolve(
        &self,
        ref_name: &str,
        key: &String,
        field: &String,
    ) -> Option<(SheetId, BlockCellId)> {
        let keys = self.get_all_keys(ref_name);
        keys.into_iter()
            .find(|(k, _, _)| k == key)
            .and_then(|(_, sheet_id, id)| {
                let cell_id = self
                    .block_schema_manager
                    .partially_resolve(ref_name, id, field)?;
                Some((sheet_id, cell_id))
            })
    }

    fn get_all_keys_by_block(
        &self,
        sheet_id: SheetId,
        block_id: BlockId,
    ) -> Vec<(String, SheetId, BlockCellId)> {
        let bp = match self.navigator.get_block_place(&sheet_id, &block_id).ok() {
            Some(bp) => bp,
            None => return vec![],
        };
        let block_ids = match self
            .block_schema_manager
            .get_all_key_cell_ids_by_block(sheet_id, block_id, bp)
        {
            Some(ids) => ids,
            None => return vec![],
        };
        let text_fetcher = |id: TextId| self.text_id_manager.get_string(&id).unwrap().clone();
        block_ids
            .iter()
            .flat_map(|id| {
                let cell = self.container.get_cell(sheet_id, &CellId::BlockCell(*id))?;
                let v = cell.value.to_string(&text_fetcher);
                Some((v, sheet_id, *id))
            })
            .collect()
    }

    fn get_all_fields_by_block(&self, sheet_id: SheetId, block_id: BlockId) -> Vec<String> {
        self.block_schema_manager
            .get_all_fields_by_block(sheet_id, block_id)
            .unwrap_or_default()
    }

    fn resolve_by_block(
        &self,
        sheet_id: SheetId,
        block_id: BlockId,
        key: &String,
        field: &String,
    ) -> Option<(SheetId, BlockCellId)> {
        let keys = self.get_all_keys_by_block(sheet_id, block_id);
        keys.into_iter()
            .find(|(k, _, _)| k == key)
            .and_then(|(_, sid, id)| {
                let cell_id = self
                    .block_schema_manager
                    .partially_resolve_by_block(sid, block_id, id, field)?;
                Some((sid, cell_id))
            })
    }

    fn resolve_by_block_field_id(
        &self,
        sheet_id: SheetId,
        block_id: BlockId,
        key: &String,
        field_id: logisheets_base::BlockFieldId,
    ) -> Option<(SheetId, BlockCellId)> {
        // Empty key => "the sole row" of a degenerate (single-row / single-col)
        // block. Such a block has no meaningful key column, so its key cell is
        // left empty; the value-matching path below can't find it because empty
        // cells carry no stored value. Resolve it by geometry instead: the
        // schema enumerates exactly one key cell id, and we return that row's
        // field cell. Only unambiguous when there is a single row — a block with
        // multiple rows queried by empty key is treated as "not found".
        if key.is_empty() {
            let bp = self.navigator.get_block_place(&sheet_id, &block_id).ok()?;
            let key_cell_ids = self
                .block_schema_manager
                .get_all_key_cell_ids_by_block(sheet_id, block_id, bp)?;
            let [only_key] = key_cell_ids.as_slice() else {
                return None;
            };
            let cell_id = self
                .block_schema_manager
                .partially_resolve_by_field_id(sheet_id, block_id, *only_key, field_id)?;
            return Some((sheet_id, cell_id));
        }

        let keys = self.get_all_keys_by_block(sheet_id, block_id);
        keys.into_iter()
            .find(|(k, _, _)| k == key)
            .and_then(|(_, sid, id)| {
                let cell_id = self
                    .block_schema_manager
                    .partially_resolve_by_field_id(sid, block_id, id, field_id)?;
                Some((sid, cell_id))
            })
    }

    fn get_all_field_ids_by_block(
        &self,
        sheet_id: SheetId,
        block_id: BlockId,
    ) -> Vec<(String, logisheets_base::BlockFieldId)> {
        self.block_schema_manager
            .get_all_field_ids_by_block(sheet_id, block_id)
    }

    fn resolve_field_id(
        &self,
        sheet_id: SheetId,
        block_id: BlockId,
        field: &str,
    ) -> Option<logisheets_base::BlockFieldId> {
        self.block_schema_manager
            .resolve_field_id(sheet_id, block_id, field)
    }

    fn fetch_field_name(
        &self,
        sheet_id: SheetId,
        block_id: BlockId,
        field_id: logisheets_base::BlockFieldId,
    ) -> Option<String> {
        self.block_schema_manager
            .fetch_field_name(sheet_id, block_id, field_id)
    }
}