CSV to SQL Importer — Kotlin source

// csv-to-sql — pure CSV → SQL import generator. Kotlin 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.

/** RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
 *  trailing field without a newline still completes its row. */
fun csvToRows(csv: String): List<List<String>> {
    val rows = mutableListOf<List<String>>()
    val field = StringBuilder()
    var row = mutableListOf<String>()
    var inQ = false
    var i = 0
    while (i < csv.length) {
        val ch = csv[i]
        when {
            inQ -> when (ch) {
                '"' -> if (i + 1 < csv.length && csv[i + 1] == '"') { field.append('"'); i++ } else inQ = false
                else -> field.append(ch)
            }
            ch == '"' -> inQ = true
            ch == ',' -> { row.add(field.toString()); field.setLength(0) }
            ch == '\n' -> {
                row.add(field.toString()); rows.add(row); row = mutableListOf(); field.setLength(0)
            }
            ch != '\r' -> field.append(ch)
        }
        i++
    }
    if (field.isNotEmpty() || row.isNotEmpty()) { row.add(field.toString()); rows.add(row) }
    return rows
}

private val NUMERIC = Regex("""^-?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$""")
private val IDENT = Regex("[^A-Za-z0-9_]")

fun escapeSqlString(s: String, dialect: String = "standard"): String {
    var out = s.replace("'", "''")
    if (dialect == "mysql") {
        out = out.replace("\\", "\\\\").replace("\u0000", "\\0")
            .replace("\n", "\\n").replace("\r", "\\r").replace("\u001A", "\\Z")
    }
    return out
}

fun fieldLiteral(value: String, dialect: String = "standard", inferTypes: Boolean = true): String {
    if (inferTypes) {
        if (value.isEmpty()) return "NULL"
        if (NUMERIC.matches(value)) return value // verbatim — no float round-trip
    }
    return "'${escapeSqlString(value, dialect)}'"
}

fun sanitizeIdent(name: String): String = IDENT.replace(name, "_")

private fun sanitizeHeaders(headers: List<String>): List<String> {
    val seen = mutableMapOf<String, Int>()
    return headers.mapIndexed { i, h ->
        var id = sanitizeIdent(h.trim())
        if (id.isEmpty()) id = "col${i + 1}"
        val n = (seen[id] ?: 0) + 1
        seen[id] = n
        if (n > 1) "${id}_$n" else id
    }
}

private fun csvEscape(field: String): String =
    if (field.any { it == ',' || it == '"' || it == '\n' || it == '\r' })
        "\"" + field.replace("\"", "\"\"") + "\""
    else field

data class Options(
    val table: String = "",
    val format: String = "insert",        // insert | copy | load-data
    val dialect: String = "standard",     // standard | mysql | postgres
    val batchSize: Int = 100,
    val inferTypes: Boolean = true,
    val quoteIdentifiers: Boolean = true,
    val fileName: String = "import.csv",
)

data class Result(val ok: Boolean, val sql: String, val rows: Int, val error: String?)

private fun quoteIdent(name: String, mysql: Boolean, quoteIds: Boolean): String =
    when {
        !quoteIds -> name
        mysql -> "`$name`"
        else -> "\"$name\""
    }

fun csvToSql(csv: String, opts: Options): Result {
    val text = csv.trim()
    val rows = csvToRows(text)
    if (text.isEmpty() || rows.size < 2) return Result(false, "", 0, "No rows to import.")

    val cols = sanitizeHeaders(rows[0])
    val data = rows.drop(1)
    val mysql = opts.dialect == "mysql"
    val identMysql = when (opts.format) {
        "load-data" -> true
        "copy" -> false
        else -> mysql
    }

    val tblRaw = sanitizeIdent(opts.table)
    val tbl = quoteIdent(tblRaw.ifEmpty { "tbl" }, identMysql, opts.quoteIdentifiers)
    val colList = cols.joinToString(", ") { quoteIdent(it, identMysql, opts.quoteIdentifiers) }

    fun cellAt(r: List<String>, ci: Int) = if (ci < r.size) r[ci] else ""

    if (opts.format == "copy" || opts.format == "load-data") {
        val payload = mutableListOf(cols.joinToString(",") { csvEscape(it) })
        data.forEach { r -> payload.add(cols.indices.joinToString(",") { ci -> csvEscape(cellAt(r, ci)) }) }
        val body = payload.joinToString("\n")
        return if (opts.format == "copy") {
            Result(true, "COPY $tbl ($colList) FROM STDIN WITH (FORMAT csv, HEADER true);\n$body\n\\.", data.size, null)
        } else {
            val clean = opts.fileName.filter { it in 'A'..'Z' || it in 'a'..'z' || it in '0'..'9' || it == '.' || it == '_' || it == '-' || it == '/' }
            val file = clean.ifEmpty { "import.csv" }
            Result(true, "LOAD DATA LOCAL INFILE '$file'\nINTO TABLE $tbl\n" +
                "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\n" +
                "LINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n$body", data.size, null)
        }
    }

    val size = maxOf(1, opts.batchSize)
    val stmts = data.chunked(size).map { chunk ->
        val values = chunk.map { r ->
            "  (" + cols.indices.joinToString(", ") { ci -> fieldLiteral(cellAt(r, ci), opts.dialect, opts.inferTypes) } + ")"
        }
        "INSERT INTO $tbl ($colList) VALUES\n" + values.joinToString(",\n") + ";"
    }
    return Result(true, stmts.joinToString("\n"), data.size, null)
}

// Example:
//   val r = csvToSql("id,name\n1,Ada\n2,", Options(table = "users"))
//   println(r.sql)
//   // INSERT INTO "users" ("id", "name") VALUES
//   //   (1, 'Ada'),
//   //   (2, NULL);