sheeter-engine 1.3.0

use clap::{Parser, Subcommand, ValueEnum};
use sheeter_engine::{
    convert_workbook_with_options, detect_content, detect_extension, external_sort_delimited,
    inspect, stream_delimited, stream_delimited_to_ndjson, stream_ndjson_to_delimited,
    stream_ndjson_to_ndjson, CanonicalValue, Filter, Format, SortSpec, SqlDialect, Transforms,
};
use std::fs;
use std::io::{BufWriter, Read, Write};
use std::path::Path;

#[derive(Parser)]
#[command(
    name = "sheeter",
    version,
    about = "Convert, inspect, and transform data files"
)]
struct Cli {
    #[command(subcommand)]
    command: Commands,
}

#[derive(Subcommand)]
#[allow(clippy::large_enum_variant)]
enum Commands {
    /// Convert a data file from one format to another
    Convert {
        /// Source format; auto-detected when omitted
        #[arg(long, ignore_case = true)]
        from: Option<FormatArg>,
        #[arg(long, ignore_case = true)]
        to: FormatArg,
        #[arg(long)]
        sheet: Option<String>,
        #[arg(long, default_value = "generic")]
        dialect: String,
        /// Filter expression (e.g. "age > 30 and name == 'Bob'")
        #[arg(long)]
        filter: Option<String>,
        /// Columns to keep, comma-separated
        #[arg(long)]
        select: Option<String>,
        /// Rename columns (e.g. "a=x,b=y")
        #[arg(long)]
        rename: Option<String>,
        /// Maximum number of rows
        #[arg(long)]
        limit: Option<usize>,
        /// Sort by column(s), e.g. "age desc,name"
        #[arg(long)]
        sort: Option<String>,
        /// Drop duplicate rows (whole row)
        #[arg(long)]
        dedup: bool,
        /// Drop duplicates by column(s), e.g. "email"
        #[arg(long, value_name = "COLS")]
        dedup_by: Option<String>,
        /// Input file, or "-" for stdin
        input: String,
        /// Output file, or "-" for stdout
        output: String,
    },
    /// Run a SQL SELECT statement against files
    Query {
        /// SQL statement. File paths in FROM resolve against the current directory.
        sql: String,
        /// Output file, or "-" for stdout (default)
        #[arg(short = 'o', long, default_value = "-")]
        output: String,
        /// Output format (auto-detected from extension; json for stdout)
        #[arg(long, ignore_case = true)]
        to: Option<FormatArg>,
        /// Sheet name for binary output formats (xlsx, ods)
        #[arg(long, default_value = "result")]
        sheet_name: String,
    },
    /// Display one or more files as formatted tables
    View {
        /// Input files (shell expands globs)
        #[arg(required = true)]
        inputs: Vec<String>,
        /// Emit JSON instead of formatted tables
        #[arg(long)]
        to_json: bool,
    },
    /// Merge multiple files into sheets of an XLSX workbook
    #[command(name = "xlsx:merge")]
    XlsxMerge {
        /// Input files (one sheet per file; multi-sheet XLSX inputs get prefixed names)
        #[arg(required = true)]
        inputs: Vec<String>,
        /// Output .xlsx path
        #[arg(short, long)]
        output: String,
    },
    /// Concatenate many files into a single XLSX sheet with a source column
    #[command(name = "xlsx:concat")]
    XlsxConcat {
        /// Input files (any format)
        #[arg(required = true)]
        inputs: Vec<String>,
        /// Output .xlsx path
        #[arg(short, long)]
        output: String,
        /// Omit the source column
        #[arg(long)]
        no_source: bool,
        /// Sheet name (default: data)
        #[arg(long, default_value = "data")]
        sheet_name: String,
    },
    /// Split every sheet of an XLSX file into its own JSON file
    #[command(name = "xlsx:frag")]
    XlsxFrag {
        /// Input .xlsx file
        input: String,
        /// Output directory (default: current directory)
        #[arg(long)]
        out: Option<String>,
    },
    /// Print a structural summary of a data file
    Inspect {
        /// Format; auto-detected when omitted
        #[arg(long, ignore_case = true)]
        format: Option<FormatArg>,
        /// Input file, or "-" for stdin
        input: String,
    },
    /// Print the detected format of a file or stdin
    Detect {
        /// Input file, or "-" for stdin
        input: String,
    },
}

#[derive(Debug, Clone, Copy, ValueEnum)]
enum FormatArg {
    Csv,
    Tsv,
    Json,
    Yaml,
    Xml,
    Toml,
    Xlsx,
    Sql,
    Ndjson,
    Ods,
}

impl From<FormatArg> for Format {
    fn from(f: FormatArg) -> Self {
        match f {
            FormatArg::Csv => Self::Csv,
            FormatArg::Tsv => Self::Tsv,
            FormatArg::Json => Self::Json,
            FormatArg::Yaml => Self::Yaml,
            FormatArg::Xml => Self::Xml,
            FormatArg::Toml => Self::Toml,
            FormatArg::Xlsx => Self::Xlsx,
            FormatArg::Sql => Self::Sql,
            FormatArg::Ndjson => Self::Ndjson,
            FormatArg::Ods => Self::Ods,
        }
    }
}

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

fn read_input(path: &str) -> Result<Vec<u8>, Box<dyn std::error::Error>> {
    if path == "-" {
        let mut buf = Vec::new();
        std::io::stdin().read_to_end(&mut buf)?;
        Ok(buf)
    } else {
        Ok(fs::read(path)?)
    }
}

fn write_output(
    path: &str,
    content: &[u8],
    is_binary: bool,
) -> Result<(), Box<dyn std::error::Error>> {
    if path == "-" {
        if is_binary {
            return Err("cannot write binary output to stdout; specify a file path".into());
        }
        std::io::stdout().write_all(content)?;
        std::io::stdout().flush()?;
    } else {
        fs::write(path, content)?;
    }
    Ok(())
}

fn resolve_from(
    explicit: Option<FormatArg>,
    input_path: &str,
    input_bytes: &[u8],
) -> Result<Format, Box<dyn std::error::Error>> {
    if let Some(f) = explicit {
        return Ok(f.into());
    }
    if input_path != "-" {
        if let Some(f) = detect_extension(Path::new(input_path)) {
            return Ok(f);
        }
    }
    detect_content(input_bytes)
}

fn build_transforms(
    filter: Option<String>,
    select: Option<String>,
    rename: Option<String>,
    limit: Option<usize>,
    dedup: bool,
    dedup_by: Option<String>,
) -> Result<Transforms, Box<dyn std::error::Error>> {
    let filter = match filter {
        Some(s) => Some(Filter::parse(&s)?),
        None => None,
    };
    let select = select.map(|s| {
        s.split(',')
            .map(|x| x.trim().to_string())
            .filter(|x| !x.is_empty())
            .collect::<Vec<_>>()
    });
    let rename = rename
        .map(|s| {
            s.split(',')
                .filter_map(|pair| {
                    let mut parts = pair.split('=');
                    let from = parts.next()?.trim().to_string();
                    let to = parts.next()?.trim().to_string();
                    if from.is_empty() || to.is_empty() {
                        None
                    } else {
                        Some((from, to))
                    }
                })
                .collect::<Vec<_>>()
        })
        .unwrap_or_default();
    let dedup_spec = if dedup {
        Some(Vec::new())
    } else {
        dedup_by.map(|s| {
            s.split(',')
                .map(|x| x.trim().to_string())
                .filter(|x| !x.is_empty())
                .collect::<Vec<_>>()
        })
    };
    Ok(Transforms {
        filter,
        select,
        rename,
        limit,
        dedup: dedup_spec,
    })
}

fn is_binary_format(f: Format) -> bool {
    matches!(f, Format::Xlsx | Format::Ods)
}

fn is_text_streamable(from: Format, to: Format) -> bool {
    let delimited = |f: Format| matches!(f, Format::Csv | Format::Tsv);
    let ndjson = |f: Format| matches!(f, Format::Ndjson);
    (delimited(from) && delimited(to))
        || (ndjson(from) && delimited(to))
        || (delimited(from) && ndjson(to))
        || (ndjson(from) && ndjson(to))
}

fn stream_convert(
    from: Format,
    to: Format,
    input: &mut dyn Read,
    output: &mut dyn Write,
    transforms: &Transforms,
    sort: Option<&str>,
) -> Result<(), Box<dyn std::error::Error>> {
    let from_delim = if matches!(from, Format::Tsv) {
        b'\t'
    } else {
        b','
    };
    let to_delim = if matches!(to, Format::Tsv) {
        b'\t'
    } else {
        b','
    };

    match (from, to) {
        (Format::Csv | Format::Tsv, Format::Csv | Format::Tsv) => {
            if let Some(spec) = sort {
                let sort_spec = SortSpec::parse(spec)?;
                external_sort_delimited(
                    input, from_delim, output, to_delim, transforms, &sort_spec, 50_000,
                )?;
            } else {
                stream_delimited(input, from_delim, output, to_delim, transforms)?;
            }
        }
        (Format::Ndjson, Format::Csv | Format::Tsv) => {
            if sort.is_some() {
                return Err("--sort is not yet supported with NDJSON input".into());
            }
            stream_ndjson_to_delimited(input, output, to_delim, transforms)?;
        }
        (Format::Csv | Format::Tsv, Format::Ndjson) => {
            if sort.is_some() {
                return Err("--sort is not yet supported with NDJSON output".into());
            }
            stream_delimited_to_ndjson(input, from_delim, output, transforms)?;
        }
        (Format::Ndjson, Format::Ndjson) => {
            if sort.is_some() {
                return Err("--sort is not yet supported with NDJSON".into());
            }
            stream_ndjson_to_ndjson(input, output, transforms)?;
        }
        _ => return Err("stream_convert called with non-streamable pair".into()),
    }
    Ok(())
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let cli = Cli::parse();
    match cli.command {
        Commands::Convert {
            from,
            to,
            sheet,
            dialect,
            filter,
            select,
            rename,
            limit,
            sort,
            dedup,
            dedup_by,
            input,
            output,
        } => {
            let to_fmt: Format = to.into();
            let transforms = build_transforms(filter, select, rename, limit, dedup, dedup_by)?;

            if is_binary_format(to_fmt) && !transforms.is_empty() {
                return Err(format!(
                    "transforms cannot be applied when the output format is {to_fmt:?} because it cannot be parsed back. Convert to a text format first."
                )
                .into());
            }

            let input_bytes = read_input(&input)?;
            let from_fmt = resolve_from(from, &input, &input_bytes)?;

            if is_text_streamable(from_fmt, to_fmt) {
                let mut reader = std::io::Cursor::new(&input_bytes);
                if output == "-" {
                    let mut out = std::io::stdout();
                    stream_convert(
                        from_fmt,
                        to_fmt,
                        &mut reader,
                        &mut out,
                        &transforms,
                        sort.as_deref(),
                    )?;
                } else {
                    let file = fs::File::create(&output)?;
                    let mut out = BufWriter::new(file);
                    stream_convert(
                        from_fmt,
                        to_fmt,
                        &mut reader,
                        &mut out,
                        &transforms,
                        sort.as_deref(),
                    )?;
                }
                return Ok(());
            }

            let sql_dialect: SqlDialect = dialect
                .parse()
                .map_err(|e: String| std::io::Error::new(std::io::ErrorKind::InvalidInput, e))?;
            let table_name = if output == "-" {
                None
            } else {
                Path::new(&output).file_stem().and_then(|s| s.to_str())
            };
            let mut results = convert_workbook_with_options(
                from_fmt,
                to_fmt,
                &input_bytes,
                sql_dialect,
                table_name,
            )?;

            if !transforms.is_empty() {
                for (_, content) in results.iter_mut() {
                    let text = std::str::from_utf8(content)?;
                    let hub = match to_fmt {
                        Format::Json => CanonicalValue::from_json(text)?,
                        Format::Yaml => CanonicalValue::from_yaml(text)?,
                        Format::Xml => CanonicalValue::from_xml(text)?,
                        Format::Toml => CanonicalValue::from_toml(text)?,
                        Format::Csv => CanonicalValue::from_csv(text)?,
                        Format::Tsv => CanonicalValue::from_tsv(text)?,
                        Format::Ndjson => CanonicalValue::from_ndjson(text)?,
                        Format::Xlsx | Format::Sql | Format::Ods => continue,
                        _ => continue,
                    };
                    let filtered = transforms.apply_to_array(&hub);
                    let reserialized = match to_fmt {
                        Format::Json => filtered.to_json()?,
                        Format::Yaml => filtered.to_yaml()?,
                        Format::Xml => filtered.to_xml()?,
                        Format::Toml => filtered.to_toml()?,
                        Format::Csv => filtered.to_csv()?,
                        Format::Tsv => filtered.to_tsv()?,
                        Format::Ndjson => filtered.to_ndjson()?,
                        Format::Xlsx | Format::Sql | Format::Ods => continue,
                        _ => continue,
                    };
                    *content = reserialized.into_bytes();
                }
            }

            if let Some(want) = sheet {
                results.retain(|(name, _)| name == &want);
                if results.is_empty() {
                    return Err(format!("sheet {want:?} not found").into());
                }
            }

            if results.len() == 1 {
                write_output(&output, &results[0].1, is_binary_format(to_fmt))?;
                return Ok(());
            }

            let out_path = Path::new(&output);
            let stem = out_path
                .file_stem()
                .and_then(|s| s.to_str())
                .unwrap_or("output");
            let ext = out_path.extension().and_then(|s| s.to_str()).unwrap_or("");
            let parent = out_path.parent().unwrap_or_else(|| Path::new("."));
            let mut used: std::collections::HashSet<String> = std::collections::HashSet::new();
            for (raw_name, content) in &results {
                let mut base = sanitize(raw_name);
                let mut n = 2;
                while !used.insert(base.clone()) {
                    base = format!("{}_{}", sanitize(raw_name), n);
                    n += 1;
                }
                let filename = if ext.is_empty() {
                    format!("{}.{}", stem, base)
                } else {
                    format!("{}.{}.{}", stem, base, ext)
                };
                fs::write(parent.join(filename), content)?;
            }
            Ok(())
        }
        Commands::Query {
            sql,
            output,
            to,
            sheet_name,
        } => {
            let base_dir = std::env::current_dir()?;
            let value = sheeter_engine::query::run(&sql, &base_dir)?;
            let to_fmt: Format = match to {
                Some(f) => f.into(),
                None if output != "-" => {
                    sheeter_engine::detect_extension(Path::new(&output)).unwrap_or(Format::Json)
                }
                None => Format::Json,
            };

            if matches!(to_fmt, Format::Xlsx | Format::Ods) {
                if output == "-" {
                    return Err(format!("{to_fmt:?} is binary; specify a file path").into());
                }
                let bytes = match to_fmt {
                    Format::Xlsx => {
                        CanonicalValue::to_xlsx_workbook(&[(sheet_name.clone(), value)])?
                    }
                    Format::Ods => sheeter_engine::to_ods_workbook(&[(sheet_name.clone(), value)])?,
                    _ => unreachable!(),
                };
                std::fs::write(&output, bytes)?;
                return Ok(());
            }
            let text = match to_fmt {
                Format::Json => value.to_json()?,
                Format::Yaml => value.to_yaml()?,
                Format::Csv => value.to_csv()?,
                Format::Tsv => value.to_tsv()?,
                Format::Ndjson => value.to_ndjson()?,
                Format::Xml => value.to_xml()?,
                Format::Toml => value.to_toml()?,
                Format::Sql => value.to_sql_with_table("data")?,
                _ => return Err(format!("query output cannot be {to_fmt:?}; use convert").into()),
            };
            if output == "-" {
                std::io::stdout().write_all(text.as_bytes())?;
                if !text.ends_with('\n') {
                    std::io::stdout().write_all(b"\n")?;
                }
                std::io::stdout().flush()?;
            } else {
                std::fs::write(&output, text)?;
            }
            Ok(())
        }
        Commands::View { inputs, to_json } => {
            let mut sources: Vec<(String, CanonicalValue)> = Vec::new();
            for input in &inputs {
                sources.extend(sheeter_engine::view::expand(input)?);
            }
            let rendered = sheeter_engine::view::render(sources, to_json)?;
            std::io::stdout().write_all(rendered.as_bytes())?;
            std::io::stdout().flush()?;
            Ok(())
        }
        Commands::XlsxMerge { inputs, output } => {
            sheeter_engine::xlsx_tools::merge(&inputs, &output)?;
            Ok(())
        }
        Commands::XlsxConcat {
            inputs,
            output,
            no_source,
            sheet_name,
        } => {
            sheeter_engine::xlsx_tools::concat(&inputs, &output, no_source, &sheet_name)?;
            Ok(())
        }
        Commands::XlsxFrag { input, out } => {
            let written = sheeter_engine::xlsx_tools::frag(&input, out.as_deref())?;
            for path in written {
                println!("{}", path.display());
            }
            Ok(())
        }
        Commands::Inspect { format, input } => {
            let bytes = read_input(&input)?;
            let fmt = resolve_from(format, &input, &bytes)?;
            let report = inspect(&bytes, fmt)?;
            print!("{report}");
            Ok(())
        }
        Commands::Detect { input } => {
            let bytes = read_input(&input)?;
            let fmt = resolve_from(None, &input, &bytes)?;
            println!("{:?}", fmt);
            Ok(())
        }
    }
}

#[cfg(test)]
mod tests {
    use super::sanitize;

    #[test]
    fn sanitize_replaces_forbidden_chars() {
        assert_eq!(sanitize("A/B"), "A_B");
        assert_eq!(sanitize("A:B*C?"), "A_B_C_");
        assert_eq!(sanitize("a\\b"), "a_b");
    }

    #[test]
    fn sanitize_preserves_valid_names() {
        assert_eq!(sanitize("Sheet1"), "Sheet1");
        assert_eq!(sanitize("Vendas 2024"), "Vendas 2024");
        assert_eq!(sanitize("A.B"), "A.B");
    }

    #[test]
    fn sanitize_falls_back_on_empty() {
        assert_eq!(sanitize(""), "Sheet");
    }
}