sheeter-engine 1.2.0

//! Excel-specific helpers: `xlsx:merge` and `xlsx:frag`.
//!
//! [`merge`] appends one sheet per input file to an existing or new `.xlsx`.
//! Inputs may be any format the engine reads; each is decoded through the
//! canonical hub before being written. Single-table inputs use their filename
//! stem as the sheet name; multi-sheet inputs (XLSX, ODS, multi-table SQL)
//! prefix the sheet name with the file stem to avoid collisions. Names are
//! truncated to Excel's 31-character limit, and collisions after truncation
//! get `_2`, `_3` suffixes. Existing sheets are preserved.
//!
//! [`frag`] splits an `.xlsx` into one JSON file per sheet, written to the
//! current directory (or `--out DIR`). Sheet names are normalized the same
//! way the merge mode formats its prefixes: NFD-decomposed, accents stripped,
//! lowercased, non-alphanumerics collapsed to single underscores, trimmed.
//! Empty results fall back to `sheet`; collisions get `_2`, `_3`.
//!
//! # Examples
//!
//! ```
//! use sheeter_engine::xlsx_tools;
//! use std::fs;
//!
//! let dir = tempfile::tempdir().unwrap();
//! fs::write(dir.path().join("a.csv"), "x\n1\n").unwrap();
//! fs::write(dir.path().join("b.json"), r#"[{"y":2}]"#).unwrap();
//! let out = dir.path().join("ss.xlsx");
//! xlsx_tools::merge(
//!     &[
//!         dir.path().join("a.csv").to_str().unwrap().to_string(),
//!         dir.path().join("b.json").to_str().unwrap().to_string(),
//!     ],
//!     out.to_str().unwrap(),
//! )
//! .unwrap();
//! let sheets = sheeter_engine::CanonicalValue::from_xlsx_workbook(&fs::read(out).unwrap()).unwrap();
//! assert_eq!(sheets.len(), 2);
//! assert_eq!(sheets[0].0, "a");
//! assert_eq!(sheets[1].0, "b");
//! ```

use crate::{detect_content, detect_extension, CanonicalValue, Format, SqlDialect};
use std::collections::HashSet;
use std::error::Error;
use std::path::{Path, PathBuf};
use unicode_normalization::UnicodeNormalization;

/// Appends one sheet per input file to `output`.
///
/// If `output` already exists, its sheets are loaded and preserved; new
/// sheets are appended. Sheet names come from the input filename stem for
/// single-table inputs, and from `<stem>_<sheet>` for multi-sheet inputs.
///
/// # Errors
///
/// Returns an error if any input cannot be read or decoded, if `output` does
/// not end in `.xlsx`, or if writing the workbook fails.
pub fn merge(inputs: &[String], output: &str) -> Result<(), Box<dyn Error>> {
    if !output.ends_with(".xlsx") {
        return Err(format!("output must end in .xlsx, got {output:?}").into());
    }

    let mut sheets: Vec<(String, CanonicalValue)> = if Path::new(output).exists() {
        CanonicalValue::from_xlsx_workbook(&std::fs::read(output)?)?
    } else {
        Vec::new()
    };
    let mut used: HashSet<String> = sheets.iter().map(|(n, _)| n.clone()).collect();

    for input_path in inputs {
        for (raw_name, value) in load_for_merge(input_path)? {
            let name = unique_excel_name(&raw_name, &mut used);
            sheets.push((name, value));
        }
    }

    let bytes = CanonicalValue::to_xlsx_workbook(&sheets)?;
    std::fs::write(output, bytes)?;
    Ok(())
}

fn load_for_merge(path: &str) -> Result<Vec<(String, CanonicalValue)>, Box<dyn Error>> {
    let bytes = std::fs::read(path)?;
    let fmt = match detect_extension(Path::new(path)) {
        Some(f) => f,
        None => detect_content(&bytes)?,
    };
    let stem = Path::new(path)
        .file_stem()
        .and_then(|s| s.to_str())
        .unwrap_or(path)
        .to_string();

    match fmt {
        Format::Xlsx => {
            let sheets = CanonicalValue::from_xlsx_workbook(&bytes)?;
            if sheets.len() == 1 {
                let mut it = sheets.into_iter();
                Ok(vec![(stem, it.next().unwrap().1)])
            } else {
                Ok(sheets
                    .into_iter()
                    .map(|(name, v)| (format!("{stem}_{name}"), v))
                    .collect())
            }
        }
        Format::Ods => {
            let v = CanonicalValue::from_ods(&bytes)?;
            match v {
                CanonicalValue::Object(o) if o.len() > 1 => Ok(o
                    .into_iter()
                    .map(|(name, v)| (format!("{stem}_{name}"), v))
                    .collect()),
                other => Ok(vec![(stem, other)]),
            }
        }
        other => {
            let text = std::str::from_utf8(&bytes)?;
            let hub = match other {
                Format::Csv => CanonicalValue::from_csv(text)?,
                Format::Tsv => CanonicalValue::from_tsv(text)?,
                Format::Json => CanonicalValue::from_json(text)?,
                Format::Ndjson => CanonicalValue::from_ndjson(text)?,
                Format::Yaml => CanonicalValue::from_yaml(text)?,
                Format::Xml => CanonicalValue::from_xml(text)?,
                Format::Toml => CanonicalValue::from_toml(text)?,
                Format::Sql => {
                    let sheets = CanonicalValue::from_sql_workbook(text, SqlDialect::Generic)?;
                    if sheets.len() == 1 {
                        return Ok(vec![(stem, sheets.into_iter().next().unwrap().1)]);
                    }
                    return Ok(sheets
                        .into_iter()
                        .map(|(name, v)| (format!("{stem}_{name}"), v))
                        .collect());
                }
                Format::Xlsx | Format::Ods => unreachable!(),
            };
            Ok(vec![(stem, hub)])
        }
    }
}

/// Splits every sheet of an `.xlsx` into its own JSON file.
///
/// # Errors
///
/// Returns an error if the input is not `.xlsx`, cannot be read, or if any
/// write fails.
pub fn frag(input: &str, out_dir: Option<&str>) -> Result<Vec<PathBuf>, Box<dyn Error>> {
    let bytes = std::fs::read(input)?;
    let fmt = match detect_extension(Path::new(input)) {
        Some(f) => f,
        None => detect_content(&bytes)?,
    };
    if !matches!(fmt, Format::Xlsx) {
        return Err(format!(
            "xlsx:frag requires an .xlsx file, got {fmt:?}; convert to .xlsx first"
        )
        .into());
    }

    let sheets = CanonicalValue::from_xlsx_workbook(&bytes)?;
    let out = match out_dir {
        Some(d) => Path::new(d),
        None => Path::new("."),
    };
    std::fs::create_dir_all(out)?;

    let mut written: Vec<PathBuf> = Vec::new();
    let mut used: HashSet<String> = HashSet::new();
    for (name, value) in sheets {
        let base = unique_file_name(&name, &mut used);
        let path = out.join(format!("{base}.json"));
        std::fs::write(&path, value.to_json()?)?;
        written.push(path);
    }
    Ok(written)
}

fn unique_excel_name(raw: &str, used: &mut HashSet<String>) -> String {
    let base = truncate(&excel_safe(raw), 31);
    if used.insert(base.clone()) {
        return base;
    }
    let mut counter = 2;
    loop {
        let suffix = format!("_{counter}");
        let candidate = format!("{}{}", truncate(&base, 31 - suffix.len()), suffix);
        if used.insert(candidate.clone()) {
            return candidate;
        }
        counter += 1;
    }
}

fn unique_file_name(raw: &str, used: &mut HashSet<String>) -> String {
    let base = sanitize_sheet_name(raw);
    if used.insert(base.clone()) {
        return base;
    }
    let mut counter = 2;
    loop {
        let candidate = format!("{}_{}", sanitize_sheet_name(raw), counter);
        if used.insert(candidate.clone()) {
            return candidate;
        }
        counter += 1;
    }
}

fn excel_safe(name: &str) -> String {
    let cleaned: String = name
        .chars()
        .map(|c| match c {
            '[' | ']' | ':' | '*' | '?' | '/' | '\\' => '_',
            c if c.is_control() => '_',
            c => c,
        })
        .collect();
    let trimmed = cleaned.trim();
    if trimmed.is_empty() {
        "Sheet".to_string()
    } else {
        trimmed.to_string()
    }
}

fn truncate(s: &str, max: usize) -> String {
    if s.chars().count() <= max {
        s.to_string()
    } else {
        s.chars().take(max).collect()
    }
}

fn sanitize_sheet_name(name: &str) -> String {
    let normalized: String = name
        .nfd()
        .filter(|c| !unicode_normalization::char::is_combining_mark(*c))
        .collect::<String>()
        .to_lowercase();
    let mut out = String::new();
    let mut last_sep = false;
    for c in normalized.chars() {
        if c.is_ascii_alphanumeric() {
            out.push(c);
            last_sep = false;
        } else if !last_sep && !out.is_empty() {
            out.push('_');
            last_sep = true;
        }
    }
    while out.ends_with('_') {
        out.pop();
    }
    if out.is_empty() {
        "sheet".to_string()
    } else {
        out
    }
}