CSV to SQL Importer — Ruby source

# csv-to-sql — pure CSV → SQL import generator. Ruby port (canonical TS:
# src/lib/csv-to-sql.ts; Go twin: cli/csv-to-sql). Mirrors the JS/Python
# ports: RFC 4180 parse, header sanitizing, then batched INSERTs, a Postgres
# COPY block, or a MySQL LOAD DATA statement. Numeric text stays verbatim;
# empty fields become NULL. Errors return through the result hash.
module CsvToSql
  module_function

  NUMERIC = /^-?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$/.freeze
  IDENT = /[^A-Za-z0-9_]/

  # RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
  # trailing field without a newline still completes its row.
  def csv_to_rows(csv)
    rows = []
    field = +''
    row = []
    in_q = false
    i = 0
    n = csv.length
    while i < n
      ch = csv[i]
      if in_q
        if ch == '"'
          if i + 1 < n && csv[i + 1] == '"'
            field << '"'
            i += 1
          else
            in_q = false
          end
        else
          field << ch
        end
      elsif ch == '"'
        in_q = true
      elsif ch == ','
        row << field
        field = +''
      elsif ch == "\n"
        row << field
        rows << row
        row = []
        field = +''
      elsif ch != "\r"
        field << ch
      end
      i += 1
    end
    row << field if !field.empty? || !row.empty?
    rows << row if !row.empty?
    rows
  end

  def escape_sql_string(s, dialect = 'standard')
    out = s.gsub("'", "''")
    return out unless dialect == 'mysql'
    # Block-form gsub keeps replacements literal (no '\0' whole-match pitfalls).
    out.gsub('\\') { '\\\\' }.gsub("\0") { '\0' }.gsub("\n") { '\n' }
       .gsub("\r") { '\r' }.gsub("\x1a") { '\Z' }
  end

  def field_literal(value, dialect = 'standard', infer_types = true)
    if infer_types
      return 'NULL' if value == ''
      return value if NUMERIC.match?(value) # verbatim — no float round-trip
    end
    "'#{escape_sql_string(value, dialect)}'"
  end

  def sanitize_ident(name)
    name.gsub(IDENT, '_')
  end

  def sanitize_headers(headers)
    seen = Hash.new(0)
    headers.each_with_index.map do |h, i|
      id = sanitize_ident(h.strip)
      id = "col#{i + 1}" if id.empty?
      seen[id] += 1
      seen[id] > 1 ? "#{id}_#{seen[id]}" : id
    end
  end

  def csv_escape(field)
    return field unless field.match?(/[",\n\r]/)
    '"' + field.gsub('"', '""') + '"'
  end

  def csv_to_sql(csv, table:, format: 'insert', dialect: 'standard', batch_size: 100,
                 infer_types: true, quote_identifiers: true, file_name: 'import.csv')
    text = csv.strip
    rows = csv_to_rows(text)
    return { ok: false, sql: '', rows: 0, error: 'No rows to import.' } if text.empty? || rows.length < 2

    cols = sanitize_headers(rows[0])
    data = rows[1..]
    ident_dialect = { 'copy' => 'postgres', 'load-data' => 'mysql' }.fetch(format, dialect)
    q = lambda do |name|
      next name unless quote_identifiers
      ident_dialect == 'mysql' ? "`#{name}`" : "\"#{name}\""
    end
    tbl = q.call(sanitize_ident(table).empty? ? 'tbl' : sanitize_ident(table))
    col_list = cols.map { |c| q.call(c) }.join(', ')

    if %w[copy load-data].include?(format)
      payload = [cols.map { |c| csv_escape(c) }.join(',')]
      payload += data.map do |r|
        cols.each_index.map { |ci| csv_escape(r[ci] || '') }.join(',')
      end
      if format == 'copy'
        sql = "COPY #{tbl} (#{col_list}) FROM STDIN WITH (FORMAT csv, HEADER true);\n" +
              payload.join("\n") + "\n\\."
      else
        clean = file_name.gsub(/[^A-Za-z0-9._\-\/]/, '')
        clean = 'import.csv' if clean.empty?
        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" + payload.join("\n")
      end
      return { ok: true, sql: sql, rows: data.length, error: nil }
    end

    size = [1, batch_size].max
    stmts = []
    (0...data.length).step(size) do |start|
      values = data[start, size].map do |r|
        '  (' + cols.each_index.map { |ci| field_literal(r[ci] || '', dialect, infer_types) }.join(', ') + ')'
      end
      stmts << "INSERT INTO #{tbl} (#{col_list}) VALUES\n#{values.join(",\n")};"
    end
    { ok: true, sql: stmts.join("\n"), rows: data.length, error: nil }
  end
end

# Example:
# CsvToSql.csv_to_sql("id,name\n1,Ada\n2,", table: 'users')[:sql]
# → "INSERT INTO \"users\" (\"id\", \"name\") VALUES\n  (1, 'Ada'),\n  (2, NULL);"