CSV to SQL Importer — Rust source

//! csv-to-sql — pure CSV → SQL import generator. Rust port (canonical TS:
//! `src/lib/csv-to-sql.ts`; Go twin: `cli/csv-to-sql`). RFC 4180 parse, header
//! sanitizing into SQL identifiers, then batched INSERTs, a Postgres COPY
//! block, or a MySQL LOAD DATA statement. Numeric-looking text emits bare and
//! verbatim; empty fields become NULL. No external crates — mirrors the TS
//! loop directly so all ports stay byte-identical.

/// RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
/// trailing field without a newline still completes its row.
pub fn csv_to_rows(csv: &str) -> Vec<Vec<String>> {
    let mut rows: Vec<Vec<String>> = Vec::new();
    let mut field = String::new();
    let mut row: Vec<String> = Vec::new();
    let mut in_q = false;
    let chars: Vec<char> = csv.chars().collect();
    let n = chars.len();
    let mut i = 0;
    while i < n {
        let ch = chars[i];
        if in_q {
            if ch == '"' {
                if i + 1 < n && chars[i + 1] == '"' {
                    field.push('"');
                    i += 1;
                } else {
                    in_q = false;
                }
            } else {
                field.push(ch);
            }
        } else if ch == '"' {
            in_q = true;
        } else if ch == ',' {
            row.push(std::mem::take(&mut field));
        } else if ch == '\n' {
            row.push(std::mem::take(&mut field));
            rows.push(std::mem::take(&mut row));
        } else if ch != '\r' {
            field.push(ch);
        }
        i += 1;
    }
    if !field.is_empty() || !row.is_empty() {
        row.push(field);
        rows.push(row);
    }
    rows
}

pub fn escape_sql_string(s: &str, dialect: &str) -> String {
    let mut out = s.replace('\'', "''");
    if dialect == "mysql" {
        out = out.replace('\\', "\\\\")
            .replace('\0', "\\0")
            .replace('\n', "\\n")
            .replace('\r', "\\r")
            .replace('\u{1a}', "\\Z");
    }
    out
}

fn is_numeric(value: &str) -> bool {
    // Integer, decimal, or scientific literal — the matched text is emitted
    // VERBATIM (no float round-trip, so "007" and "1e3" pass through as-is).
    let b = value.as_bytes();
    let mut i = 0;
    if i < b.len() && b[i] == b'-' { i += 1; }
    let int_start = i;
    while i < b.len() && b[i].is_ascii_digit() { i += 1; }
    let had_int = i > int_start;
    let mut had_frac = false;
    let mut saw_dot = false;
    if i < b.len() && b[i] == b'.' {
        saw_dot = true;
        i += 1;
        let fs = i;
        while i < b.len() && b[i].is_ascii_digit() { i += 1; }
        had_frac = i > fs;
    }
    // ".5" ok, "5." rejected (digits required after the dot) — matches TS.
    if saw_dot && !had_frac { return false; }
    if !had_int && !had_frac { return false; }
    if i < b.len() && (b[i] == b'e' || b[i] == b'E') {
        i += 1;
        if i < b.len() && (b[i] == b'+' || b[i] == b'-') { i += 1; }
        let es = i;
        while i < b.len() && b[i].is_ascii_digit() { i += 1; }
        if i == es { return false; }
    }
    i == b.len()
}

pub fn field_literal(value: &str, dialect: &str, infer_types: bool) -> String {
    if infer_types {
        if value.is_empty() { return "NULL".into(); }
        if is_numeric(value) { return value.into(); }
    }
    format!("'{}'", escape_sql_string(value, dialect))
}

pub fn sanitize_ident(name: &str) -> String {
    name.chars()
        .map(|c| if c.is_ascii_alphanumeric() || c == '_' { c } else { '_' })
        .collect()
}

fn sanitize_headers(headers: &[String]) -> Vec<String> {
    let mut seen: std::collections::HashMap<String, usize> = std::collections::HashMap::new();
    headers
        .iter()
        .enumerate()
        .map(|(i, h)| {
            let mut id = sanitize_ident(h.trim());
            if id.is_empty() {
                id = format!("col{}", i + 1);
            }
            let n = seen.entry(id.clone()).or_insert(0);
            *n += 1;
            if *n > 1 {
                id = format!("{}_{}", id, n);
            }
            id
        })
        .collect()
}

fn csv_escape(field: &str) -> String {
    if field.contains(',') || field.contains('\n') || field.contains('\r') || field.contains('"') {
        format!("\"{}\"", field.replace('"', "\"\""))
    } else {
        field.to_string()
    }
}

pub struct Options<'a> {
    pub table: &'a str,
    pub format: &'a str,           // "insert" | "copy" | "load-data"
    pub dialect: &'a str,          // "standard" | "mysql" | "postgres"
    pub batch_size: usize,
    pub infer_types: bool,
    pub quote_identifiers: bool,
    pub file_name: &'a str,
}

impl Default for Options<'_> {
    fn default() -> Self {
        Options {
            table: "",
            format: "insert",
            dialect: "standard",
            batch_size: 100,
            infer_types: true,
            quote_identifiers: true,
            file_name: "import.csv",
        }
    }
}

pub struct Result {
    pub ok: bool,
    pub sql: String,
    pub rows: usize,
    pub error: Option<String>,
}

pub fn csv_to_sql(csv: &str, opts: &Options) -> Result {
    let fail = |msg: &str| Result { ok: false, sql: String::new(), rows: 0, error: Some(msg.into()) };

    let text = csv.trim();
    let rows = csv_to_rows(text);
    if text.is_empty() || rows.len() < 2 {
        return fail("No rows to import.");
    }

    let cols = sanitize_headers(&rows[0]);
    let data = &rows[1..];
    let ident_dialect = match opts.format {
        "copy" => "postgres",
        "load-data" => "mysql",
        _ => opts.dialect,
    };
    let q = |name: &str| {
        if !opts.quote_identifiers {
            name.to_string()
        } else if ident_dialect == "mysql" {
            format!("`{}`", name)
        } else {
            format!("\"{}\"", name)
        }
    };
    let tbl_ident = sanitize_ident(opts.table);
    let table = q(if tbl_ident.is_empty() { "tbl" } else { &tbl_ident });
    let col_list = cols.iter().map(|c| q(c)).collect::<Vec<_>>().join(", ");

    if opts.format == "copy" || opts.format == "load-data" {
        let mut payload = vec![cols.iter().map(|c| csv_escape(c)).collect::<Vec<_>>().join(",")];
        for r in data {
            let fields: Vec<String> = (0..cols.len())
                .map(|ci| csv_escape(r.get(ci).map(String::as_str).unwrap_or("")))
                .collect();
            payload.push(fields.join(","));
        }
        if opts.format == "copy" {
            let sql = format!(
                "COPY {} ({}) FROM STDIN WITH (FORMAT csv, HEADER true);\n{}\n\\.",
                table, col_list, payload.join("\n")
            );
            return Result { ok: true, sql, rows: data.len(), error: None };
        }
        let clean: String = opts
            .file_name
            .chars()
            .filter(|c| c.is_ascii_alphanumeric() || *c == '.' || *c == '_' || *c == '-' || *c == '/')
            .collect();
        let clean = if clean.is_empty() { "import.csv" } else { &clean };
        let sql = format!(
            "LOAD DATA LOCAL INFILE '{}'\nINTO TABLE {}\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\nLINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n{}",
            clean, table, payload.join("\n")
        );
        return Result { ok: true, sql, rows: data.len(), error: None };
    }

    let size = opts.batch_size.max(1);
    let mut stmts: Vec<String> = Vec::new();
    for chunk in data.chunks(size) {
        let values: Vec<String> = chunk
            .iter()
            .map(|r| {
                let vals: Vec<String> = (0..cols.len())
                    .map(|ci| field_literal(r.get(ci).map(String::as_str).unwrap_or(""), opts.dialect, opts.infer_types))
                    .collect();
                format!("  ({})", vals.join(", "))
            })
            .collect();
        stmts.push(format!("INSERT INTO {} ({}) VALUES\n{};", table, col_list, values.join(",\n")));
    }
    Result { ok: true, sql: stmts.join("\n"), rows: data.len(), error: None }
}

// Example:
// let r = csv_to_sql("id,name\n1,Ada\n2,", &Options { table: "users", ..Default::default() });
// assert!(r.sql.contains("(2, NULL)"));