1use std::collections::HashMap;
2
3use crate::cli::args::resolve_sheet_target;
4use crate::cli::error::{anyhow_to_app_error, AppError};
5use crate::excel::{Cell, Sheet, Workbook};
6use crate::utils::{index_to_col_name, parse_range};
7
8pub(crate) struct ResolvedSheet {
9 pub(crate) index: usize,
10 pub(crate) name: String,
11}
12
13#[derive(Clone, Copy)]
14pub(crate) struct SheetBounds {
15 pub(crate) start_row: usize,
16 pub(crate) end_row: usize,
17 pub(crate) start_col: usize,
18 pub(crate) end_col: usize,
19}
20
21pub(crate) fn load_target_sheet(
22 workbook: &Workbook,
23 sheet: &Option<String>,
24 sheet_index: &Option<usize>,
25) -> Result<ResolvedSheet, AppError> {
26 let index = resolve_sheet_target(workbook, sheet, sheet_index)?;
27 let name = workbook
28 .get_sheet_names()
29 .get(index)
30 .cloned()
31 .ok_or_else(|| AppError::TargetNotFound {
32 message: format!("Sheet index {} not found", index),
33 })?;
34
35 Ok(ResolvedSheet { index, name })
36}
37
38pub(crate) fn resolve_bounds(
39 workbook: &Workbook,
40 sheet: &Sheet,
41 sheet_index: usize,
42 range: Option<&str>,
43) -> Result<SheetBounds, AppError> {
44 let ((mut start_row, mut start_col), (mut end_row, mut end_col)) = if let Some(range) = range {
45 parse_range(range).ok_or_else(|| AppError::InvalidQuery {
46 message: format!("Invalid range format: {}", range),
47 })?
48 } else {
49 let used_range = workbook.get_used_range(sheet_index).unwrap_or_default();
50 if used_range.is_empty() {
51 ((1, 1), (1, 1))
52 } else {
53 parse_range(&used_range).unwrap_or(((1, 1), (1, 1)))
54 }
55 };
56
57 let max_row = sheet.max_rows.max(1);
58 let max_col = sheet.max_cols.max(1);
59 start_row = start_row.min(max_row);
60 start_col = start_col.min(max_col);
61 end_row = end_row.min(max_row);
62 end_col = end_col.min(max_col);
63
64 if start_row > end_row {
65 std::mem::swap(&mut start_row, &mut end_row);
66 }
67 if start_col > end_col {
68 std::mem::swap(&mut start_col, &mut end_col);
69 }
70
71 Ok(SheetBounds {
72 start_row,
73 end_row,
74 start_col,
75 end_col,
76 })
77}
78
79pub(crate) fn resolve_header_row(
80 workbook: &Workbook,
81 sheet: &Sheet,
82 sheet_index: usize,
83 header_row: &str,
84) -> Result<Option<usize>, AppError> {
85 if header_row == "auto" {
86 let (_, recommended) = workbook
87 .find_header_candidates(sheet_index)
88 .map_err(anyhow_to_app_error)?;
89 return Ok(recommended);
90 }
91
92 let row = header_row
93 .parse::<usize>()
94 .map_err(|_| AppError::InvalidQuery {
95 message: format!("Invalid header row: {}", header_row),
96 })?;
97
98 if row < 1 || row > sheet.max_rows {
99 return Err(AppError::InvalidQuery {
100 message: format!(
101 "Header row {} is outside the used row range 1..={}",
102 row, sheet.max_rows
103 ),
104 });
105 }
106
107 Ok(Some(row))
108}
109
110pub(crate) fn resolve_optional_header_row(
111 workbook: &Workbook,
112 sheet: &Sheet,
113 sheet_index: usize,
114 header_row: &str,
115) -> Result<Option<usize>, AppError> {
116 if header_row == "auto" {
117 let (_, recommended) = workbook
118 .find_header_candidates(sheet_index)
119 .map_err(anyhow_to_app_error)?;
120 return Ok(recommended);
121 }
122
123 Ok(header_row
124 .parse::<usize>()
125 .ok()
126 .filter(|row| *row >= 1 && *row <= sheet.max_rows))
127}
128
129pub(crate) fn cell_at(sheet: &Sheet, row: usize, col: usize) -> Option<&Cell> {
130 sheet.data.get(row).and_then(|row_data| row_data.get(col))
131}
132
133pub(crate) fn cell_has_formula(cell: &Cell) -> bool {
134 cell.is_formula || cell.formula.is_some()
135}
136
137pub(crate) fn cell_is_present(cell: Option<&Cell>) -> bool {
138 cell.map(|cell| !cell.value.trim().is_empty() || cell_has_formula(cell))
139 .unwrap_or(false)
140}
141
142pub(crate) fn header_value(sheet: &Sheet, row: usize, col: usize) -> String {
143 cell_at(sheet, row, col)
144 .filter(|cell| !cell_has_formula(cell))
145 .map(|cell| cell.value.trim().to_string())
146 .unwrap_or_default()
147}
148
149pub(crate) fn stable_record_keys(headers: &[String], start_col: usize) -> Vec<String> {
150 let mut counts = HashMap::new();
151
152 headers
153 .iter()
154 .enumerate()
155 .map(|(offset, header)| {
156 let base = if header.trim().is_empty() {
157 format!("col_{}", index_to_col_name(start_col + offset))
158 } else {
159 header.trim().to_string()
160 };
161
162 let count = counts.entry(base.clone()).or_insert(0usize);
163 *count += 1;
164 if *count == 1 {
165 base
166 } else {
167 format!("{base}_{count}")
168 }
169 })
170 .collect()
171}
172
173pub(crate) fn read_header_values(
174 sheet: &Sheet,
175 header_row: usize,
176 bounds: SheetBounds,
177) -> Vec<String> {
178 (bounds.start_col..=bounds.end_col)
179 .map(|col| {
180 if header_row < sheet.data.len() && col < sheet.data[header_row].len() {
181 sheet.data[header_row][col].value.clone()
182 } else {
183 String::new()
184 }
185 })
186 .collect()
187}
188
189#[cfg(test)]
190mod tests {
191 use super::*;
192 use crate::excel::{CellType, FreezePanes, Sheet};
193
194 fn sheet_with_values(name: &str, values: &[&[&str]]) -> Sheet {
195 let max_rows = values.len();
196 let max_cols = values.iter().map(|row| row.len()).max().unwrap_or(0);
197 let mut data = vec![vec![Cell::empty(); max_cols + 1]; max_rows + 1];
198
199 for (row_idx, row) in values.iter().enumerate() {
200 for (col_idx, value) in row.iter().enumerate() {
201 data[row_idx + 1][col_idx + 1] = Cell::new((*value).to_string(), false);
202 }
203 }
204
205 Sheet {
206 name: name.to_string(),
207 data,
208 max_rows,
209 max_cols,
210 is_loaded: true,
211 freeze_panes: FreezePanes::none(),
212 }
213 }
214
215 #[test]
216 fn resolve_bounds_clamps_and_normalizes_explicit_ranges() {
217 let workbook = Workbook::from_sheets_for_test(vec![sheet_with_values(
218 "Orders",
219 &[&["order_id", "customer"], &["1001", "Alice"]],
220 )]);
221 let sheet = workbook.get_sheet_by_index(0).unwrap();
222
223 let bounds = resolve_bounds(&workbook, sheet, 0, Some("D5:B2")).unwrap();
224
225 assert_eq!(bounds.start_row, 2);
226 assert_eq!(bounds.end_row, 2);
227 assert_eq!(bounds.start_col, 2);
228 assert_eq!(bounds.end_col, 2);
229 }
230
231 #[test]
232 fn resolve_bounds_falls_back_to_a1_for_empty_used_ranges() {
233 let mut sheet = Sheet::blank("Empty".to_string());
234 sheet.max_rows = 0;
235 sheet.max_cols = 0;
236 let workbook = Workbook::from_sheets_for_test(vec![sheet]);
237 let sheet = workbook.get_sheet_by_index(0).unwrap();
238
239 let bounds = resolve_bounds(&workbook, sheet, 0, None).unwrap();
240
241 assert_eq!(bounds.start_row, 1);
242 assert_eq!(bounds.end_row, 1);
243 assert_eq!(bounds.start_col, 1);
244 assert_eq!(bounds.end_col, 1);
245 }
246
247 #[test]
248 fn resolve_header_row_rejects_non_numeric_and_out_of_range_values() {
249 let workbook = Workbook::from_sheets_for_test(vec![sheet_with_values(
250 "Orders",
251 &[&["order_id", "customer"], &["1001", "Alice"]],
252 )]);
253 let sheet = workbook.get_sheet_by_index(0).unwrap();
254
255 let invalid = resolve_header_row(&workbook, sheet, 0, "header").unwrap_err();
256 assert_eq!(invalid.code(), "invalid_query");
257
258 let out_of_range = resolve_header_row(&workbook, sheet, 0, "9").unwrap_err();
259 assert_eq!(out_of_range.code(), "invalid_query");
260 }
261
262 #[test]
263 fn resolve_optional_header_row_preserves_lenient_record_resolution() {
264 let workbook = Workbook::from_sheets_for_test(vec![sheet_with_values(
265 "Orders",
266 &[&["order_id", "customer"], &["1001", "Alice"]],
267 )]);
268 let sheet = workbook.get_sheet_by_index(0).unwrap();
269
270 assert_eq!(
271 resolve_optional_header_row(&workbook, sheet, 0, "header").unwrap(),
272 None
273 );
274 assert_eq!(
275 resolve_optional_header_row(&workbook, sheet, 0, "9").unwrap(),
276 None
277 );
278 assert_eq!(
279 resolve_optional_header_row(&workbook, sheet, 0, "1").unwrap(),
280 Some(1)
281 );
282 }
283
284 #[test]
285 fn header_and_cell_helpers_preserve_existing_formula_semantics() {
286 let mut sheet = sheet_with_values("Orders", &[&["order_id", ""], &["1001", "Alice"]]);
287 sheet.data[1][2] = Cell {
288 value: "total".to_string(),
289 formula: Some("=UPPER(\"total\")".to_string()),
290 is_formula: false,
291 cell_type: CellType::Text,
292 original_type: None,
293 };
294 sheet.data[2][2] = Cell {
295 value: String::new(),
296 formula: Some("=A2".to_string()),
297 is_formula: false,
298 cell_type: CellType::Text,
299 original_type: None,
300 };
301
302 assert_eq!(header_value(&sheet, 1, 1), "order_id");
303 assert_eq!(header_value(&sheet, 1, 2), "");
304 assert!(cell_has_formula(cell_at(&sheet, 1, 2).unwrap()));
305 assert!(cell_is_present(cell_at(&sheet, 2, 2)));
306 }
307
308 #[test]
309 fn record_helpers_generate_stable_column_names_and_header_values() {
310 let sheet = sheet_with_values(
311 "Orders",
312 &[
313 &["order_id", "customer", "customer", ""],
314 &["1001", "Alice", "VIP", "true"],
315 ],
316 );
317 let bounds = SheetBounds {
318 start_row: 1,
319 end_row: 2,
320 start_col: 1,
321 end_col: 4,
322 };
323
324 let headers = read_header_values(&sheet, 1, bounds);
325 let columns = stable_record_keys(&headers, bounds.start_col);
326
327 assert_eq!(headers, vec!["order_id", "customer", "customer", ""]);
328 assert_eq!(columns, vec!["order_id", "customer", "customer_2", "col_D"]);
329 }
330}