sheeter-engine 1.1.0

use std::error::Error;
use std::io::Cursor;

/// Format-agnostic intermediate value used by the Sheeter hub.
///
/// Every reader (CSV, TSV, JSON, YAML, XML) produces a `CanonicalValue`, and
/// every writer consumes one. Adding a format requires exactly one reader and
/// one writer, not `N-1` pairwise converters, because every spoke composes
/// through this hub.
///
/// Tabular data is represented as `Array<Object>`. XML documents are
/// represented as a single-key `Object` whose key is the root element name,
/// with attributes prefixed by `@` and text stored under `#text`. Repeated
/// sibling elements become `Array`.
///
/// # Examples
///
/// ```
/// use sheeter_engine::CanonicalValue;
///
/// let v = CanonicalValue::from_json(r#"[{"a":1}]"#).unwrap();
/// assert_eq!(v.to_csv().unwrap(), "a\n1\n");
/// ```
#[derive(Debug, Clone, PartialEq)]
#[non_exhaustive]
pub enum CanonicalValue {
    Null,
    Bool(bool),
    Number(serde_json::Number),
    String(String),
    Bytes(Vec<u8>),
    Array(Vec<CanonicalValue>),
    Object(Vec<(String, CanonicalValue)>),
}

type XmlStackFrame = (
    String,
    Vec<(String, String)>,
    Vec<(String, CanonicalValue)>,
    String,
);

impl CanonicalValue {
    pub(crate) fn from_json_value(v: serde_json::Value) -> Self {
        match v {
            serde_json::Value::Null => Self::Null,
            serde_json::Value::Bool(b) => Self::Bool(b),
            serde_json::Value::Number(n) => Self::Number(n),
            serde_json::Value::String(s) => Self::String(s),
            serde_json::Value::Array(a) => {
                Self::Array(a.into_iter().map(Self::from_json_value).collect())
            }
            serde_json::Value::Object(m) => {
                if m.len() == 1 {
                    if let Some(serde_json::Value::String(b64)) = m.get("$binary") {
                        use base64::Engine;
                        if let Ok(bytes) = base64::engine::general_purpose::STANDARD.decode(b64) {
                            return Self::Bytes(bytes);
                        }
                    }
                }
                Self::Object(
                    m.into_iter()
                        .map(|(k, v)| (k, Self::from_json_value(v)))
                        .collect(),
                )
            }
        }
    }

    pub(crate) fn to_json_value(&self) -> serde_json::Value {
        match self {
            Self::Null => serde_json::Value::Null,
            Self::Bool(b) => serde_json::Value::Bool(*b),
            Self::Number(n) => serde_json::Value::Number(n.clone()),
            Self::String(s) => serde_json::Value::String(s.clone()),
            Self::Bytes(b) => {
                use base64::Engine;
                let mut map = serde_json::Map::new();
                map.insert(
                    "$binary".to_string(),
                    serde_json::Value::String(base64::engine::general_purpose::STANDARD.encode(b)),
                );
                serde_json::Value::Object(map)
            }
            Self::Array(a) => {
                serde_json::Value::Array(a.iter().map(|v| v.to_json_value()).collect())
            }
            Self::Object(o) => {
                let mut map = serde_json::Map::new();
                for (k, v) in o {
                    map.insert(k.clone(), v.to_json_value());
                }
                serde_json::Value::Object(map)
            }
        }
    }

    /// Parses JSON into the canonical hub.
    ///
    /// # Errors
    /// Returns an error if the JSON is malformed.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// assert!(CanonicalValue::from_json(r#"{"a":1}"#).is_ok());
    /// ```
    pub fn from_json(input: &str) -> Result<Self, Box<dyn Error>> {
        Ok(Self::from_json_value(serde_json::from_str(input)?))
    }

    /// Serializes the canonical hub to JSON.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::String("x".into());
    /// assert_eq!(v.to_json().unwrap(), r#""x""#);
    /// ```
    pub fn to_json(&self) -> Result<String, Box<dyn Error>> {
        Ok(serde_json::to_string(&self.to_json_value())?)
    }

    fn from_delimited(input: &str, delim: u8) -> Result<Self, Box<dyn Error>> {
        let input = input.strip_prefix('\u{feff}').unwrap_or(input);
        let mut rdr = csv::ReaderBuilder::new()
            .delimiter(delim)
            .from_reader(Cursor::new(input));
        let headers = rdr.headers()?.clone();
        let mut seen = std::collections::HashSet::new();
        for h in headers.iter() {
            if !seen.insert(h) {
                return Err(format!("duplicate header: {h}").into());
            }
        }
        let mut rows = Vec::new();
        for result in rdr.records() {
            let record = result?;
            let mut obj = Vec::with_capacity(headers.len());
            for (h, v) in headers.iter().zip(record.iter()) {
                obj.push((h.to_string(), Self::String(v.to_string())));
            }
            rows.push(Self::Object(obj));
        }
        Ok(Self::Array(rows))
    }

    fn to_delimited(&self, delim: u8) -> Result<String, Box<dyn Error>> {
        let rows = self.as_tabular()?;
        if rows.is_empty() {
            return Ok(String::new());
        }
        let mut headers: Vec<String> = Vec::new();
        for row in &rows {
            let obj = row.as_object()?;
            for (k, _) in obj {
                if !headers.contains(k) {
                    headers.push(k.clone());
                }
            }
        }
        let mut wtr = csv::WriterBuilder::new()
            .delimiter(delim)
            .from_writer(vec![]);
        wtr.write_record(&headers)?;
        for row in &rows {
            let obj = row.as_object()?;
            let mut record = Vec::with_capacity(headers.len());
            for h in &headers {
                let cell = match obj.iter().find(|(k, _)| k == h) {
                    Some((_, Self::Null)) | None => String::new(),
                    Some((_, Self::Bool(b))) => b.to_string(),
                    Some((_, Self::Number(n))) => n.to_string(),
                    Some((_, Self::String(s))) => s.clone(),
                    Some((_, Self::Bytes(b))) => {
                        use base64::Engine;
                        base64::engine::general_purpose::STANDARD.encode(b)
                    }
                    Some((_, Self::Array(_))) | Some((_, Self::Object(_))) => {
                        return Err(format!(
                            "nested value at key {h} cannot be represented in tabular output"
                        )
                        .into());
                    }
                };
                record.push(cell);
            }
            wtr.write_record(&record)?;
        }
        let data = wtr.into_inner()?;
        Ok(String::from_utf8(data)?)
    }

    pub fn from_toml(input: &str) -> Result<Self, Box<dyn Error>> {
        let value: toml::Value = toml::from_str(input)?;
        Ok(Self::from_toml_value(value))
    }

    fn from_toml_value(v: toml::Value) -> Self {
        match v {
            toml::Value::String(s) => Self::String(s),
            toml::Value::Integer(i) => Self::Number(serde_json::Number::from(i)),
            toml::Value::Float(f) => match serde_json::Number::from_f64(f) {
                Some(n) => Self::Number(n),
                None => Self::String(f.to_string()),
            },
            toml::Value::Boolean(b) => Self::Bool(b),
            toml::Value::Datetime(dt) => Self::String(dt.to_string()),
            toml::Value::Array(a) => {
                Self::Array(a.into_iter().map(Self::from_toml_value).collect())
            }
            toml::Value::Table(t) => Self::Object(
                t.into_iter()
                    .map(|(k, v)| (k, Self::from_toml_value(v)))
                    .collect(),
            ),
        }
    }

    pub fn to_toml(&self) -> Result<String, Box<dyn Error>> {
        let value = match self {
            Self::Array(_) => {
                let mut table = toml::map::Map::new();
                table.insert("item".to_string(), self.to_toml_value()?);
                toml::Value::Table(table)
            }
            Self::Object(_) => self.to_toml_value()?,
            _ => {
                let mut table = toml::map::Map::new();
                table.insert("value".to_string(), self.to_toml_value()?);
                toml::Value::Table(table)
            }
        };
        Ok(toml::to_string_pretty(&value)?)
    }

    fn to_toml_value(&self) -> Result<toml::Value, Box<dyn Error>> {
        Ok(match self {
            Self::Null => return Err("TOML has no null type".into()),
            Self::Bool(b) => toml::Value::Boolean(*b),
            Self::Number(n) => {
                if let Some(i) = n.as_i64() {
                    toml::Value::Integer(i)
                } else if let Some(u) = n.as_u64() {
                    if u <= i64::MAX as u64 {
                        toml::Value::Integer(u as i64)
                    } else {
                        return Err(format!("TOML integer out of range: {u}").into());
                    }
                } else if let Some(f) = n.as_f64() {
                    toml::Value::Float(f)
                } else {
                    return Err(format!("cannot represent number {n} in TOML").into());
                }
            }
            Self::String(s) => toml::Value::String(s.clone()),
            Self::Bytes(b) => {
                use base64::Engine;
                toml::Value::String(base64::engine::general_purpose::STANDARD.encode(b))
            }
            Self::Array(a) => {
                let mut items = Vec::with_capacity(a.len());
                for item in a {
                    if let Self::Null = item {
                        return Err("TOML arrays cannot contain null".into());
                    }
                    items.push(item.to_toml_value()?);
                }
                toml::Value::Array(items)
            }
            Self::Object(o) => {
                let mut table = toml::map::Map::new();
                for (k, v) in o {
                    if let Self::Null = v {
                        continue;
                    }
                    table.insert(k.clone(), v.to_toml_value()?);
                }
                toml::Value::Table(table)
            }
        })
    }

    pub fn from_xlsx_workbook(bytes: &[u8]) -> Result<Vec<(String, Self)>, Box<dyn Error>> {
        use calamine::{Reader, Xlsx};
        let cursor = Cursor::new(bytes.to_vec());
        let mut workbook: Xlsx<_> = Xlsx::new(cursor)?;
        let names = workbook.sheet_names().to_owned();
        let mut out = Vec::with_capacity(names.len());
        for name in names {
            let range = workbook
                .worksheet_range(&name)
                .map_err(|e| format!("failed to read sheet {name}: {e:?}"))?;
            out.push((name, Self::from_calamine_range(&range)?));
        }
        Ok(out)
    }

    pub(crate) fn from_calamine_range(
        range: &calamine::Range<calamine::Data>,
    ) -> Result<Self, Box<dyn Error>> {
        use calamine::Data;
        let mut rows = range.rows();
        let header = match rows.next() {
            Some(h) => h.iter().map(cell_to_string).collect::<Vec<String>>(),
            None => return Ok(Self::Array(Vec::new())),
        };
        let mut records = Vec::new();
        for row in rows {
            if row.iter().all(|c| matches!(c, Data::Empty)) {
                continue;
            }
            let mut obj = Vec::with_capacity(header.len());
            for (i, h) in header.iter().enumerate() {
                let val = match row.get(i) {
                    Some(Data::Empty) | None => Self::Null,
                    Some(Data::String(s)) => Self::String(s.clone()),
                    Some(Data::Float(f)) => {
                        if f.is_finite() && f.fract() == 0.0 && f.abs() < i64::MAX as f64 {
                            Self::Number(serde_json::Number::from(*f as i64))
                        } else {
                            match serde_json::Number::from_f64(*f) {
                                Some(n) => Self::Number(n),
                                None => Self::String(f.to_string()),
                            }
                        }
                    }
                    Some(Data::Int(i)) => Self::Number(serde_json::Number::from(*i)),
                    Some(Data::Bool(b)) => Self::Bool(*b),
                    Some(Data::DateTime(dt)) => Self::String(dt.to_string()),
                    Some(Data::DateTimeIso(s)) => Self::String(s.clone()),
                    Some(Data::DurationIso(s)) => Self::String(s.clone()),
                    Some(Data::Error(e)) => Self::String(format!("{e:?}")),
                };
                obj.push((h.clone(), val));
            }
            records.push(Self::Object(obj));
        }
        Ok(Self::Array(records))
    }

    pub fn to_xlsx_workbook(sheets: &[(String, Self)]) -> Result<Vec<u8>, Box<dyn Error>> {
        use rust_xlsxwriter::Workbook;
        let mut wb = Workbook::new();
        for (name, value) in sheets {
            let ws = wb.add_worksheet();
            ws.set_name(name.as_str())?;
            let rows = value.as_tabular()?;
            if rows.is_empty() {
                continue;
            }
            let mut headers: Vec<String> = Vec::new();
            for row in &rows {
                let obj = row.as_object()?;
                for (k, _) in obj {
                    if !headers.contains(k) {
                        headers.push(k.clone());
                    }
                }
            }
            for (col, h) in headers.iter().enumerate() {
                ws.write_string(0, col as u16, h)?;
            }
            for (i, row) in rows.iter().enumerate() {
                let obj = row.as_object()?;
                for (col, h) in headers.iter().enumerate() {
                    let row_idx = (i + 1) as u32;
                    let col_idx = col as u16;
                    match obj.iter().find(|(k, _)| k == h) {
                        Some((_, Self::String(s))) => {
                            ws.write_string(row_idx, col_idx, s)?;
                        }
                        Some((_, Self::Number(n))) => {
                            if let Some(f) = n.as_f64() {
                                ws.write_number(row_idx, col_idx, f)?;
                            } else {
                                ws.write_string(row_idx, col_idx, n.to_string())?;
                            }
                        }
                        Some((_, Self::Bool(b))) => {
                            ws.write_boolean(row_idx, col_idx, *b)?;
                        }
                        Some((_, Self::Null)) | None => {}
                        Some((_, Self::Bytes(b))) => {
                            use base64::Engine;
                            let s = base64::engine::general_purpose::STANDARD.encode(b);
                            ws.write_string(row_idx, col_idx, &s)?;
                        }
                        Some((_, Self::Array(_))) | Some((_, Self::Object(_))) => {
                            return Err(format!(
                                "nested value at key {h} cannot be represented in XLSX"
                            )
                            .into());
                        }
                    }
                }
            }
        }
        Ok(wb.save_to_buffer()?)
    }

    pub fn from_sql_workbook(
        input: &str,
        dialect: crate::SqlDialect,
    ) -> Result<Vec<(String, Self)>, Box<dyn Error>> {
        use sqlparser::ast::{
            Expr, SetExpr, Statement, TableObject, Value as SqlValue, ValueWithSpan, Values,
        };
        use sqlparser::dialect::{GenericDialect, MySqlDialect, PostgreSqlDialect, SQLiteDialect};
        use sqlparser::parser::Parser;

        let statements = match dialect {
            crate::SqlDialect::Generic => Parser::parse_sql(&GenericDialect {}, input)?,
            crate::SqlDialect::PostgreSql => Parser::parse_sql(&PostgreSqlDialect {}, input)?,
            crate::SqlDialect::Sqlite => Parser::parse_sql(&SQLiteDialect {}, input)?,
            crate::SqlDialect::MySql => Parser::parse_sql(&MySqlDialect {}, input)?,
        };

        let mut schemas: std::collections::HashMap<String, Vec<String>> =
            std::collections::HashMap::new();
        let mut tables: Vec<SqlTable> = Vec::new();

        for statement in statements {
            match statement {
                Statement::CreateTable(create) => {
                    let name = clean_ident(&create.name.to_string());
                    let cols: Vec<String> = create
                        .columns
                        .iter()
                        .map(|c| clean_ident(&c.name.to_string()))
                        .collect();
                    schemas.insert(name, cols);
                }
                Statement::Insert(insert) => {
                    let table = match &insert.table {
                        TableObject::TableName(n) => clean_ident(&n.to_string()),
                        _ => {
                            return Err(
                                "INSERT into table functions or queries is not supported".into()
                            );
                        }
                    };
                    let source = insert
                        .source
                        .as_ref()
                        .ok_or("INSERT without a source is not supported")?;
                    let rows = match source.body.as_ref() {
                        SetExpr::Values(Values { rows, .. }) => rows,
                        _ => {
                            return Err(
                                "only INSERT ... VALUES is supported for data extraction".into()
                            );
                        }
                    };
                    let explicit_cols: Vec<String> = insert
                        .columns
                        .iter()
                        .map(|c| clean_ident(&c.to_string()))
                        .collect();
                    let cols = if explicit_cols.is_empty() {
                        schemas.get(&table).cloned().unwrap_or_else(|| {
                            (0..rows.first().map(|r| r.len()).unwrap_or(0))
                                .map(|i| format!("column_{}", i + 1))
                                .collect()
                        })
                    } else {
                        explicit_cols
                    };
                    let slot = match tables.iter_mut().find(|(n, _)| n == &table) {
                        Some((_, rows)) => rows,
                        None => {
                            tables.push((table.clone(), Vec::new()));
                            &mut tables.last_mut().expect("just pushed").1
                        }
                    };
                    for row in rows {
                        let mut obj = Vec::with_capacity(cols.len());
                        for (i, expr) in row.iter().enumerate() {
                            let key = cols
                                .get(i)
                                .cloned()
                                .unwrap_or_else(|| format!("column_{}", i + 1));
                            let value = match expr {
                                Expr::Value(ValueWithSpan { value, .. }) => match value {
                                    SqlValue::Number(n, _) => {
                                        if let Ok(i) = n.parse::<i64>() {
                                            Self::Number(serde_json::Number::from(i))
                                        } else if let Ok(f) = n.parse::<f64>() {
                                            match serde_json::Number::from_f64(f) {
                                                Some(n) => Self::Number(n),
                                                None => Self::String(n.clone()),
                                            }
                                        } else {
                                            Self::String(n.clone())
                                        }
                                    }
                                    SqlValue::SingleQuotedString(s)
                                    | SqlValue::DoubleQuotedString(s)
                                    | SqlValue::EscapedStringLiteral(s) => Self::String(s.clone()),
                                    SqlValue::Boolean(b) => Self::Bool(*b),
                                    SqlValue::Null => Self::Null,
                                    other => Self::String(other.to_string()),
                                },
                                Expr::Identifier(id) => Self::String(id.value.clone()),
                                other => Self::String(other.to_string()),
                            };
                            obj.push((key, value));
                        }
                        slot.push(obj);
                    }
                }
                _ => {}
            }
        }

        if tables.is_empty() {
            return Err("no INSERT statements with literal VALUES found".into());
        }

        Ok(tables
            .into_iter()
            .map(|(name, rows)| {
                let records = rows.into_iter().map(Self::Object).collect();
                (name, Self::Array(records))
            })
            .collect())
    }

    pub fn to_sql_workbook(sheets: &[(String, Self)]) -> Result<String, Box<dyn Error>> {
        let mut out = String::new();
        for (table, value) in sheets {
            out.push_str(&value.to_sql_with_table(table)?);
        }
        Ok(out)
    }

    pub fn to_sql_with_table(&self, table: &str) -> Result<String, Box<dyn Error>> {
        let rows = self.as_tabular()?;
        if rows.is_empty() {
            return Ok(String::new());
        }
        let mut headers: Vec<String> = Vec::new();
        for row in &rows {
            let obj = row.as_object()?;
            for (k, _) in obj {
                if !headers.contains(k) {
                    headers.push(k.clone());
                }
            }
        }
        let cols = headers
            .iter()
            .map(|h| quote_ident(h))
            .collect::<Vec<_>>()
            .join(", ");
        let mut out = String::new();
        for row in &rows {
            let obj = row.as_object()?;
            let mut values = Vec::with_capacity(headers.len());
            for h in &headers {
                let cell = match obj.iter().find(|(k, _)| k == h) {
                    Some((_, Self::Null)) | None => "NULL".to_string(),
                    Some((_, Self::Bool(b))) => b.to_string().to_uppercase(),
                    Some((_, Self::Number(n))) => n.to_string(),
                    Some((_, Self::String(s))) => format!("'{}'", s.replace('\'', "''")),
                    Some((_, Self::Bytes(b))) => {
                        format!(
                            "X'{}'",
                            b.iter()
                                .map(|byte| format!("{byte:02x}"))
                                .collect::<String>()
                        )
                    }
                    Some((_, Self::Array(_))) | Some((_, Self::Object(_))) => {
                        return Err(format!(
                            "nested value at key {h} cannot be represented in SQL"
                        )
                        .into());
                    }
                };
                values.push(cell);
            }
            out.push_str(&format!(
                "INSERT INTO {} ({}) VALUES ({});\n",
                quote_ident(table),
                cols,
                values.join(", ")
            ));
        }
        Ok(out)
    }

    fn as_object(&self) -> Result<&Vec<(String, CanonicalValue)>, Box<dyn Error>> {
        match self {
            Self::Object(o) => Ok(o),
            _ => Err("expected object".into()),
        }
    }

    pub(crate) fn as_tabular(&self) -> Result<Vec<&CanonicalValue>, Box<dyn Error>> {
        if let Some(arr) = find_first_object_array(self) {
            return Ok(arr.iter().collect());
        }
        let mut current = self;
        while let Self::Object(o) = current {
            if o.len() != 1 {
                break;
            }
            match &o[0].1 {
                Self::Object(_) => current = &o[0].1,
                _ => break,
            }
        }
        if let Self::Object(o) = current {
            if o.iter().all(|(_, v)| is_scalar(v)) {
                return Ok(vec![current]);
            }
        }
        Err("cannot determine tabular structure".into())
    }

    /// Parses CSV into the canonical hub.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_csv("a\n1").unwrap();
    /// assert_eq!(v.to_json().unwrap(), r#"[{"a":"1"}]"#);
    /// ```
    pub fn from_csv(input: &str) -> Result<Self, Box<dyn Error>> {
        Self::from_delimited(input, b',')
    }

    /// Serializes the canonical hub to CSV.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_json(r#"[{"a":1}]"#).unwrap();
    /// assert_eq!(v.to_csv().unwrap(), "a\n1\n");
    /// ```
    pub fn to_csv(&self) -> Result<String, Box<dyn Error>> {
        self.to_delimited(b',')
    }

    /// Parses TSV into the canonical hub.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_tsv("a\tb\n1\t2").unwrap();
    /// assert_eq!(v.to_json().unwrap(), r#"[{"a":"1","b":"2"}]"#);
    /// ```
    pub fn from_tsv(input: &str) -> Result<Self, Box<dyn Error>> {
        Self::from_delimited(input, b'\t')
    }

    /// Serializes the canonical hub to TSV.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_json(r#"[{"a":1}]"#).unwrap();
    /// assert_eq!(v.to_tsv().unwrap(), "a\n1\n");
    /// ```
    pub fn to_tsv(&self) -> Result<String, Box<dyn Error>> {
        self.to_delimited(b'\t')
    }

    /// Parses YAML into the canonical hub.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_yaml("- a: 1\n").unwrap();
    /// assert_eq!(v.to_json().unwrap(), r#"[{"a":1}]"#);
    /// ```
    pub fn from_yaml(input: &str) -> Result<Self, Box<dyn Error>> {
        let opts = serde_saphyr::options! {
            strict_booleans: true,
            ignore_binary_tag_for_string: true,
        };
        let v: serde_json::Value = serde_saphyr::from_str_with_options(input, opts)?;
        Ok(Self::from_json_value(v))
    }

    /// Serializes the canonical hub to YAML.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_json(r#"[{"a":1}]"#).unwrap();
    /// assert!(v.to_yaml().unwrap().contains("a: 1"));
    /// ```
    pub fn to_yaml(&self) -> Result<String, Box<dyn Error>> {
        Ok(serde_saphyr::to_string(&self.to_json_value())?)
    }

    /// Parses XML into the canonical hub.
    ///
    /// Attributes become `@name` keys, text content becomes `#text`, and
    /// repeated sibling elements become arrays. The returned value is an
    /// object with exactly one key: the root element name.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_xml("<r><a>1</a><a>2</a></r>").unwrap();
    /// assert_eq!(v.to_json().unwrap(), r#"{"r":{"a":["1","2"]}}"#);
    /// ```
    pub fn from_xml(input: &str) -> Result<Self, Box<dyn Error>> {
        use quick_xml::events::Event;
        use quick_xml::Reader;

        let mut reader = Reader::from_str(input);
        reader.config_mut().trim_text(true);
        let mut stack: Vec<XmlStackFrame> = Vec::new();
        let mut root: Option<(String, CanonicalValue)> = None;

        loop {
            match reader.read_event() {
                Ok(Event::Start(e)) => {
                    let name = String::from_utf8_lossy(e.name().as_ref()).to_string();
                    let mut attrs = Vec::new();
                    for a in e.attributes() {
                        let a = a?;
                        attrs.push((
                            String::from_utf8_lossy(a.key.as_ref()).to_string(),
                            a.unescape_value()?.to_string(),
                        ));
                    }
                    stack.push((name, attrs, Vec::new(), String::new()));
                }
                Ok(Event::Text(e)) => {
                    if let Some(top) = stack.last_mut() {
                        top.3.push_str(&e.unescape()?);
                    }
                }
                Ok(Event::End(_)) => {
                    let (name, attributes, children, text) = stack.pop().ok_or("unbalanced XML")?;
                    let trimmed = text.trim().to_string();
                    let value = if attributes.is_empty() && children.is_empty() {
                        Self::String(trimmed)
                    } else {
                        let mut obj: Vec<(String, CanonicalValue)> = Vec::new();
                        for (k, v) in attributes {
                            obj.push((format!("@{k}"), Self::String(v)));
                        }
                        if !trimmed.is_empty() {
                            obj.push(("#text".to_string(), Self::String(trimmed)));
                        }
                        for (cname, cval) in children {
                            if let Some(slot) = obj.iter_mut().find(|(k, _)| k == &cname) {
                                match &mut slot.1 {
                                    Self::Array(a) => a.push(cval),
                                    _ => {
                                        let old = slot.1.clone();
                                        slot.1 = Self::Array(vec![old, cval]);
                                    }
                                }
                            } else {
                                obj.push((cname, cval));
                            }
                        }
                        Self::Object(obj)
                    };
                    if let Some(parent) = stack.last_mut() {
                        parent.2.push((name, value));
                    } else {
                        root = Some((name, value));
                    }
                }
                Ok(Event::Eof) => break,
                Ok(_) => {}
                Err(e) => return Err(e.into()),
            }
        }

        let (root_name, root_value) = root.ok_or("empty XML")?;
        Ok(Self::Object(vec![(root_name, root_value)]))
    }

    /// Serializes the canonical hub to XML.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_json(r#"[{"a":"1"}]"#).unwrap();
    /// let xml = v.to_xml().unwrap();
    /// assert!(xml.contains("<root>"));
    /// assert!(xml.contains("<a>1</a>"));
    /// ```
    pub fn to_xml(&self) -> Result<String, Box<dyn Error>> {
        let mut writer = quick_xml::Writer::new_with_indent(Vec::new(), b' ', 2);
        match self {
            Self::Object(o) if o.len() == 1 && !o[0].0.starts_with('@') && o[0].0 != "#text" => {
                write_element(&mut writer, &o[0].0, &o[0].1)?;
            }
            Self::Array(a) => {
                write_open(&mut writer, "root", &[])?;
                for item in a {
                    write_element(&mut writer, "item", item)?;
                }
                write_close(&mut writer, "root")?;
            }
            _ => write_element(&mut writer, "root", self)?,
        }
        Ok(String::from_utf8(writer.into_inner())?)
    }
}

fn write_open<W: std::io::Write>(
    writer: &mut quick_xml::Writer<W>,
    name: &str,
    attrs: &[(String, String)],
) -> Result<(), Box<dyn Error>> {
    use quick_xml::events::{BytesStart, Event};
    let mut start = BytesStart::new(name);
    for (k, v) in attrs {
        start.push_attribute((k.as_str(), v.as_str()));
    }
    writer.write_event(Event::Start(start))?;
    Ok(())
}

fn write_close<W: std::io::Write>(
    writer: &mut quick_xml::Writer<W>,
    name: &str,
) -> Result<(), Box<dyn Error>> {
    use quick_xml::events::{BytesEnd, Event};
    writer.write_event(Event::End(BytesEnd::new(name)))?;
    Ok(())
}

fn write_element<W: std::io::Write>(
    writer: &mut quick_xml::Writer<W>,
    name: &str,
    value: &CanonicalValue,
) -> Result<(), Box<dyn Error>> {
    use quick_xml::events::{BytesText, Event};

    match value {
        CanonicalValue::Array(items) => {
            for item in items {
                write_element(writer, name, item)?;
            }
        }
        CanonicalValue::Object(o) => {
            let attrs: Vec<(String, String)> = o
                .iter()
                .filter_map(|(k, v)| {
                    k.strip_prefix('@').and_then(|attr| match v {
                        CanonicalValue::String(s) => Some((attr.to_string(), s.clone())),
                        _ => None,
                    })
                })
                .collect();
            write_open(writer, name, &attrs)?;
            for (k, v) in o {
                if k.starts_with('@') {
                    continue;
                }
                if k == "#text" {
                    if let CanonicalValue::String(s) = v {
                        writer.write_event(Event::Text(BytesText::new(s)))?;
                    }
                    continue;
                }
                write_element(writer, k, v)?;
            }
            write_close(writer, name)?;
        }
        CanonicalValue::String(s) => {
            write_open(writer, name, &[])?;
            writer.write_event(Event::Text(BytesText::new(s)))?;
            write_close(writer, name)?;
        }
        CanonicalValue::Bytes(b) => {
            use base64::Engine;
            let s = base64::engine::general_purpose::STANDARD.encode(b);
            write_open(writer, name, &[])?;
            writer.write_event(Event::Text(BytesText::new(&s)))?;
            write_close(writer, name)?;
        }
        CanonicalValue::Number(n) => {
            write_open(writer, name, &[])?;
            writer.write_event(Event::Text(BytesText::new(&n.to_string())))?;
            write_close(writer, name)?;
        }
        CanonicalValue::Bool(b) => {
            write_open(writer, name, &[])?;
            writer.write_event(Event::Text(BytesText::new(&b.to_string())))?;
            write_close(writer, name)?;
        }
        CanonicalValue::Null => {
            write_open(writer, name, &[])?;
            write_close(writer, name)?;
        }
    }
    Ok(())
}

type SqlRow = Vec<(String, CanonicalValue)>;
type SqlTable = (String, Vec<SqlRow>);

impl CanonicalValue {
    /// Parses an ODS (OpenDocument Spreadsheet) file into a value.
    ///
    /// A single-sheet ODS becomes an array of row objects; a multi-sheet
    /// ODS becomes an object keyed by sheet name.
    ///
    /// # Examples
    ///
    /// ```ignore
    /// // Fixture covered in tests/ods.rs.
    /// ```
    pub fn from_ods(bytes: &[u8]) -> Result<Self, Box<dyn Error>> {
        use calamine::{Ods, Reader};
        let cursor = Cursor::new(bytes.to_vec());
        let mut workbook: Ods<_> = Ods::new(cursor)?;
        let names = workbook.sheet_names().to_owned();
        let mut out: Vec<(String, CanonicalValue)> = Vec::with_capacity(names.len());
        for name in names {
            let range = workbook
                .worksheet_range(&name)
                .map_err(|e| format!("failed to read sheet {name}: {e:?}"))?;
            out.push((name, Self::from_calamine_range(&range)?));
        }
        if out.len() == 1 {
            Ok(out.remove(0).1)
        } else {
            Ok(Self::Object(out))
        }
    }
}

/// Writes a workbook as an ODS file.
///
/// Each entry of `sheets` becomes a `table:table` inside the resulting
/// `content.xml`. The `mimetype` entry is written first and stored
/// uncompressed, as required by the ODF spec.
///
/// # Errors
///
/// Returns an error if any row contains nested values or if ZIP/XML I/O fails.
pub fn to_ods_workbook(sheets: &[(String, CanonicalValue)]) -> Result<Vec<u8>, Box<dyn Error>> {
    use std::io::Write as IoWrite;
    use zip::write::SimpleFileOptions;
    use zip::{CompressionMethod, ZipWriter};

    let content = ods_content_xml(sheets)?;
    let manifest = ods_manifest_xml();
    let mimetype = "application/vnd.oasis.opendocument.spreadsheet";

    let cursor = Cursor::new(Vec::new());
    let mut zip = ZipWriter::new(cursor);
    let stored = SimpleFileOptions::default().compression_method(CompressionMethod::Stored);
    let deflated = SimpleFileOptions::default().compression_method(CompressionMethod::Deflated);

    zip.start_file("mimetype", stored)?;
    zip.write_all(mimetype.as_bytes())?;

    zip.start_file("META-INF/manifest.xml", deflated)?;
    zip.write_all(manifest.as_bytes())?;

    zip.start_file("content.xml", deflated)?;
    zip.write_all(content.as_bytes())?;

    let cursor = zip.finish()?;
    Ok(cursor.into_inner())
}

fn ods_manifest_xml() -> String {
    r#"<?xml version="1.0" encoding="UTF-8"?>
<manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" manifest:version="1.2">
  <manifest:file-entry manifest:full-path="/" manifest:version="1.2" manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/>
  <manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/>
</manifest:manifest>
"#
    .to_string()
}

fn ods_content_xml(sheets: &[(String, CanonicalValue)]) -> Result<String, Box<dyn Error>> {
    use quick_xml::escape::escape;
    let mut out = String::new();
    out.push_str(r#"<?xml version="1.0" encoding="UTF-8"?>"#);
    out.push_str(
        r#"<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" office:version="1.2">"#,
    );
    out.push_str("<office:body><office:spreadsheet>");

    for (name, value) in sheets {
        out.push_str(&format!("<table:table table:name=\"{}\">", escape(name)));
        let rows = value.as_tabular()?;
        let mut headers: Vec<String> = Vec::new();
        for row in &rows {
            if let CanonicalValue::Object(o) = row {
                for (k, _) in o {
                    if !headers.contains(k) {
                        headers.push(k.clone());
                    }
                }
            }
        }
        out.push_str("<table:table-row>");
        for h in &headers {
            out.push_str("<table:table-cell office:value-type=\"string\">");
            out.push_str(&format!("<text:p>{}</text:p>", escape(h)));
            out.push_str("</table:table-cell>");
        }
        out.push_str("</table:table-row>");

        for row in &rows {
            if let CanonicalValue::Object(o) = row {
                out.push_str("<table:table-row>");
                for h in &headers {
                    match o.iter().find(|(k, _)| k == h).map(|(_, v)| v) {
                        Some(CanonicalValue::String(s)) => {
                            out.push_str("<table:table-cell office:value-type=\"string\">");
                            out.push_str(&format!("<text:p>{}</text:p>", escape(s)));
                            out.push_str("</table:table-cell>");
                        }
                        Some(CanonicalValue::Number(n)) => {
                            out.push_str(&format!(
                                "<table:table-cell office:value-type=\"float\" office:value=\"{}\">",
                                n
                            ));
                            out.push_str(&format!("<text:p>{}</text:p>", n));
                            out.push_str("</table:table-cell>");
                        }
                        Some(CanonicalValue::Bool(b)) => {
                            out.push_str(&format!(
                                "<table:table-cell office:value-type=\"boolean\" office:boolean-value=\"{}\">",
                                b
                            ));
                            out.push_str(&format!(
                                "<text:p>{}</text:p>",
                                if *b { "TRUE" } else { "FALSE" }
                            ));
                            out.push_str("</table:table-cell>");
                        }
                        Some(CanonicalValue::Bytes(b)) => {
                            use base64::Engine;
                            let s = base64::engine::general_purpose::STANDARD.encode(b);
                            out.push_str("<table:table-cell office:value-type=\"string\">");
                            out.push_str(&format!("<text:p>{}</text:p>", s));
                            out.push_str("</table:table-cell>");
                        }
                        Some(CanonicalValue::Null) | None => {
                            out.push_str("<table:table-cell/>");
                        }
                        Some(CanonicalValue::Array(_)) | Some(CanonicalValue::Object(_)) => {
                            return Err(format!(
                                "nested value at key {h} cannot be represented in ODS"
                            )
                            .into());
                        }
                    }
                }
                out.push_str("</table:table-row>");
            }
        }
        out.push_str("</table:table>");
    }

    out.push_str("</office:spreadsheet></office:body></office:document-content>");
    Ok(out)
}

fn is_scalar(v: &CanonicalValue) -> bool {
    matches!(
        v,
        CanonicalValue::Null
            | CanonicalValue::Bool(_)
            | CanonicalValue::Number(_)
            | CanonicalValue::String(_)
    )
}

fn find_first_object_array(v: &CanonicalValue) -> Option<&Vec<CanonicalValue>> {
    match v {
        CanonicalValue::Array(a)
            if !a.is_empty() && a.iter().all(|x| matches!(x, CanonicalValue::Object(_))) =>
        {
            Some(a)
        }
        CanonicalValue::Object(o) => {
            for (_, v) in o {
                if let Some(arr) = find_first_object_array(v) {
                    return Some(arr);
                }
            }
            None
        }
        _ => None,
    }
}

fn cell_to_string(c: &calamine::Data) -> String {
    use calamine::Data;
    match c {
        Data::Empty => String::new(),
        Data::String(s) => s.clone(),
        Data::Float(f) => f.to_string(),
        Data::Int(i) => i.to_string(),
        Data::Bool(b) => b.to_string(),
        Data::DateTime(dt) => dt.to_string(),
        Data::DateTimeIso(s) => s.clone(),
        Data::DurationIso(s) => s.clone(),
        Data::Error(e) => format!("{e:?}"),
    }
}

fn clean_ident(s: &str) -> String {
    s.trim_matches('"').trim_matches('`').to_string()
}

fn quote_ident(s: &str) -> String {
    format!("\"{}\"", s.replace('"', "\"\""))
}

impl CanonicalValue {
    /// Parses NDJSON (newline-delimited JSON) into an array.
    ///
    /// Each non-empty line must be a complete JSON value. The result is a
    /// `CanonicalValue::Array` with one element per line, preserving order.
    ///
    /// # Errors
    /// Returns an error if any non-empty line is malformed JSON.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_ndjson("{\"a\":1}\n{\"a\":2}\n").unwrap();
    /// assert_eq!(v.to_json().unwrap(), r#"[{"a":1},{"a":2}]"#);
    /// ```
    pub fn from_ndjson(input: &str) -> Result<Self, Box<dyn std::error::Error>> {
        let mut values = Vec::new();
        for line in input.lines() {
            let trimmed = line.trim();
            if trimmed.is_empty() {
                continue;
            }
            let v: serde_json::Value = serde_json::from_str(trimmed)?;
            values.push(Self::from_json_value(v));
        }
        Ok(Self::Array(values))
    }

    /// Serializes the value as NDJSON (one JSON value per line).
    ///
    /// Arrays become one line per element. Non-array values become a
    /// single line.
    ///
    /// # Errors
    /// Returns an error if serialization fails.
    ///
    /// # Examples
    /// ```
    /// use sheeter_engine::CanonicalValue;
    /// let v = CanonicalValue::from_json(r#"[{"a":1},{"a":2}]"#).unwrap();
    /// assert_eq!(v.to_ndjson().unwrap(), "{\"a\":1}\n{\"a\":2}\n");
    /// ```
    pub fn to_ndjson(&self) -> Result<String, Box<dyn std::error::Error>> {
        let mut out = String::new();
        match self {
            Self::Array(items) => {
                for item in items {
                    out.push_str(&serde_json::to_string(&item.to_json_value())?);
                    out.push('\n');
                }
            }
            other => {
                out.push_str(&serde_json::to_string(&other.to_json_value())?);
                out.push('\n');
            }
        }
        Ok(out)
    }
}