CSV to SQL Importer — C# source

// csv-to-sql — pure CSV → SQL import generator. C# 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.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

public static class CsvToSql
{
    /// RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
    /// trailing field without a newline still completes its row.
    public static List<string[]> CsvToRows(string csv)
    {
        var rows = new List<string[]>();
        var field = new StringBuilder();
        var row = new List<string>();
        bool inQ = false;
        for (int i = 0; i < csv.Length; i++)
        {
            char ch = csv[i];
            if (inQ)
            {
                if (ch == '"')
                {
                    if (i + 1 < csv.Length && csv[i + 1] == '"') { field.Append('"'); i++; }
                    else inQ = false;
                }
                else field.Append(ch);
            }
            else if (ch == '"') inQ = true;
            else if (ch == ',') { row.Add(field.ToString()); field.Clear(); }
            else if (ch == '\n') { row.Add(field.ToString()); rows.Add(row.ToArray()); row.Clear(); field.Clear(); }
            else if (ch != '\r') field.Append(ch);
        }
        if (field.Length > 0 || row.Count > 0) { row.Add(field.ToString()); rows.Add(row.ToArray()); }
        return rows;
    }

    // Verbatim numeric literal — the matched text is emitted as-is (no float
    // round-trip, so "007" and "1e3" pass through unchanged).
    private static readonly Regex Numeric = new Regex(@"^-?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$", RegexOptions.Compiled);

    public static string EscapeSqlString(string s, string dialect = "standard")
    {
        var outText = s.Replace("'", "''");
        if (dialect == "mysql")
        {
            outText = outText.Replace("\\", "\\\\")
                .Replace("\0", "\\0")
                .Replace("\n", "\\n")
                .Replace("\r", "\\r")
                .Replace("\x1a", "\\Z");
        }
        return outText;
    }

    public static string FieldLiteral(string value, string dialect = "standard", bool inferTypes = true)
    {
        if (inferTypes)
        {
            if (value == "") return "NULL";
            if (Numeric.IsMatch(value)) return value;
        }
        return "'" + EscapeSqlString(value, dialect) + "'";
    }

    public static string SanitizeIdent(string name) =>
        new string(name.Select(c => char.IsLetterOrDigit(c) && c < 128 || c == '_' ? c : '_').ToArray());

    private static string[] SanitizeHeaders(string[] headers)
    {
        var seen = new Dictionary<string, int>();
        var cols = new string[headers.Length];
        for (int i = 0; i < headers.Length; i++)
        {
            string id = SanitizeIdent(headers[i].Trim());
            if (id.Length == 0) id = "col" + (i + 1);
            seen[id] = seen.TryGetValue(id, out int n) ? n + 1 : 1;
            if (seen[id] > 1) id = id + "_" + seen[id];
            cols[i] = id;
        }
        return cols;
    }

    private static string CsvEscape(string field) =>
        field.IndexOfAny(new[] { ',', '"', '\n', '\r' }) < 0
            ? field
            : "\"" + field.Replace("\"", "\"\"") + "\"";

    public sealed class Options
    {
        public string Table = "";
        public string Format = "insert";        // insert | copy | load-data
        public string Dialect = "standard";     // standard | mysql | postgres
        public int BatchSize = 100;
        public bool InferTypes = true;
        public bool QuoteIdentifiers = true;
        public string FileName = "import.csv";
    }

    public sealed class Result
    {
        public bool Ok;
        public string Sql = "";
        public int Rows;
        public string? Error;
    }

    private static string QuoteIdent(string name, bool mysql, bool quoteIds) =>
        !quoteIds ? name : mysql ? $"`{name}`" : $"\"{name}\"";

    public static Result Convert(string csv, Options opts)
    {
        string text = csv.Trim();
        var rows = CsvToRows(text);
        if (text.Length == 0 || rows.Count < 2)
            return new Result { Error = "No rows to import." };

        var cols = SanitizeHeaders(rows[0]);
        var data = rows.Skip(1).ToList();
        bool mysql = opts.Dialect == "mysql";
        bool identMysql = opts.Format == "load-data" ? true
                        : opts.Format == "copy" ? false
                        : mysql;

        string tblRaw = SanitizeIdent(opts.Table);
        string tbl = QuoteIdent(tblRaw.Length == 0 ? "tbl" : tblRaw, identMysql, opts.QuoteIdentifiers);
        string colList = string.Join(", ", cols.Select(c => QuoteIdent(c, identMysql, opts.QuoteIdentifiers)));

        string CellAt(string[] r, int ci) => ci < r.Length ? r[ci] : "";

        if (opts.Format == "copy" || opts.Format == "load-data")
        {
            var payload = new List<string> { string.Join(",", cols.Select(CsvEscape)) };
            payload.AddRange(data.Select(r => string.Join(",", cols.Select((_, ci) => CsvEscape(CellAt(r, ci))))));
            string body = string.Join("\n", payload);
            if (opts.Format == "copy")
            {
                return new Result { Ok = true, Rows = data.Count,
                    Sql = $"COPY {tbl} ({colList}) FROM STDIN WITH (FORMAT csv, HEADER true);\n{body}\n\\." };
            }
            string clean = new string(opts.FileName.Where(c => char.IsLetterOrDigit(c) && c < 128 || c == '.' || c == '_' || c == '-' || c == '/').ToArray());
            if (clean.Length == 0) clean = "import.csv";
            return new Result { Ok = true, Rows = data.Count,
                Sql = $"LOAD DATA LOCAL INFILE '{clean}'\nINTO TABLE {tbl}\n" +
                      "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\n" +
                      "LINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n" + body };
        }

        int size = Math.Max(1, opts.BatchSize);
        var stmts = new List<string>();
        for (int start = 0; start < data.Count; start += size)
        {
            var values = new List<string>();
            for (int r = start; r < Math.Min(data.Count, start + size); r++)
            {
                var vals = cols.Select((_, ci) => FieldLiteral(CellAt(data[r], ci), opts.Dialect, opts.InferTypes));
                values.Add("  (" + string.Join(", ", vals) + ")");
            }
            stmts.Add($"INSERT INTO {tbl} ({colList}) VALUES\n" + string.Join(",\n", values) + ";");
        }
        return new Result { Ok = true, Sql = string.Join("\n", stmts), Rows = data.Count };
    }
}

// Example:
//   var r = CsvToSql.Convert("id,name\n1,Ada\n2,", new CsvToSql.Options { Table = "users" });
//   Console.WriteLine(r.Sql);
//   // INSERT INTO "users" ("id", "name") VALUES
//   //   (1, 'Ada'),
//   //   (2, NULL);