CSV to SQL Importer — Swift source

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

/// RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
/// trailing field without a newline still completes its row.
func csvToRows(_ csv: String) -> [[String]] {
    var rows: [[String]] = []
    var field = ""
    var row: [String] = []
    var inQ = false
    let chars = Array(csv)
    var i = 0
    while i < chars.count {
        let ch = chars[i]
        if inQ {
            if ch == "\"" {
                if i + 1 < chars.count && chars[i + 1] == "\"" { field.append("\""); i += 1 }
                else { inQ = false }
            } else {
                field.append(ch)
            }
        } else if ch == "\"" {
            inQ = true
        } else if ch == "," {
            row.append(field); field = ""
        } else if ch == "\n" {
            row.append(field); rows.append(row); row = []; field = ""
        } else if ch != "\r" {
            field.append(ch)
        }
        i += 1
    }
    if !field.isEmpty || !row.isEmpty { row.append(field); rows.append(row) }
    return rows
}

func isIdentChar(_ c: Character) -> Bool {
    (c >= "A" && c <= "Z") || (c >= "a" && c <= "z") || (c >= "0" && c <= "9") || c == "_"
}

/// Verbatim numeric literal: -?(digits[.digits] | .digits)(e[+-]?digits)?
/// "5." is rejected (digits required after the dot) — matches the TS regex.
func isNumeric(_ s: String) -> Bool {
    let chars = Array(s)
    var i = 0
    let n = chars.count
    if i < n && chars[i] == "-" { i += 1 }
    let intStart = i
    while i < n && chars[i] >= "0" && chars[i] <= "9" { i += 1 }
    let hadInt = i > intStart
    var hadFrac = false
    var sawDot = false
    if i < n && chars[i] == "." {
        sawDot = true; i += 1
        let fs = i
        while i < n && chars[i] >= "0" && chars[i] <= "9" { i += 1 }
        hadFrac = i > fs
    }
    if sawDot && !hadFrac { return false }
    if !hadInt && !hadFrac { return false }
    if i < n && (chars[i] == "e" || chars[i] == "E") {
        i += 1
        if i < n && (chars[i] == "+" || chars[i] == "-") { i += 1 }
        let es = i
        while i < n && chars[i] >= "0" && chars[i] <= "9" { i += 1 }
        if i == es { return false }
    }
    return i == n
}

func escapeSqlString(_ s: String, dialect: String = "standard") -> String {
    var out = s.replacingOccurrences(of: "'", with: "''")
    if dialect == "mysql" {
        out = out.replacingOccurrences(of: "\\", with: "\\\\")
            .replacingOccurrences(of: "\0", with: "\\0")
            .replacingOccurrences(of: "\n", with: "\\n")
            .replacingOccurrences(of: "\r", with: "\\r")
            .replacingOccurrences(of: "\u{1a}", with: "\\Z")
    }
    return out
}

func fieldLiteral(_ value: String, dialect: String = "standard", inferTypes: Bool = true) -> String {
    if inferTypes {
        if value.isEmpty { return "NULL" }
        if isNumeric(value) { return value } // verbatim — no float round-trip
    }
    return "'\(escapeSqlString(value, dialect: dialect))'"
}

func sanitizeIdent(_ name: String) -> String {
    String(name.map { isIdentChar($0) ? $0 : "_" })
}

func sanitizeHeaders(_ headers: [String]) -> [String] {
    var seen: [String: Int] = [:]
    return headers.enumerated().map { i, h in
        var id = sanitizeIdent(h.trimmingCharacters(in: .whitespaces))
        if id.isEmpty { id = "col\(i + 1)" }
        seen[id, default: 0] += 1
        let n = seen[id]!
        return n > 1 ? "\(id)_\(n)" : id
    }
}

func csvEscape(_ field: String) -> String {
    let needs = field.contains(",") || field.contains("\"") ||
                field.contains("\n") || field.contains("\r")
    return needs ? "\"" + field.replacingOccurrences(of: "\"", with: "\"\"") + "\"" : field
}

public struct Options {
    public var table: String = ""
    public var format: String = "insert"        // insert | copy | load-data
    public var dialect: String = "standard"     // standard | mysql | postgres
    public var batchSize: Int = 100
    public var inferTypes: Bool = true
    public var quoteIdentifiers: Bool = true
    public var fileName: String = "import.csv"
    public init() {}
}

public struct Result {
    public var ok = false
    public var sql = ""
    public var rows = 0
    public var error: String?
}

func quoteIdent(_ name: String, mysql: Bool, quoteIds: Bool) -> String {
    if !quoteIds { return name }
    return mysql ? "`\(name)`" : "\"\(name)\""
}

public func csvToSql(_ csv: String, _ opts: Options) -> Result {
    let text = csv.trimmingCharacters(in: .whitespacesAndNewlines)
    let rows = csvToRows(text)
    if text.isEmpty || rows.count < 2 {
        return Result(ok: false, sql: "", rows: 0, error: "No rows to import.")
    }

    let cols = sanitizeHeaders(rows[0])
    let data = Array(rows.dropFirst())
    let mysql = opts.dialect == "mysql"
    let identMysql = opts.format == "load-data" ? true
                   : opts.format == "copy" ? false
                   : mysql

    let tblRaw = sanitizeIdent(opts.table)
    let tbl = quoteIdent(tblRaw.isEmpty ? "tbl" : tblRaw, mysql: identMysql, quoteIds: opts.quoteIdentifiers)
    let colList = cols.map { quoteIdent($0, mysql: identMysql, quoteIds: opts.quoteIdentifiers) }.joined(separator: ", ")

    func cellAt(_ r: [String], _ ci: Int) -> String { ci < r.count ? r[ci] : "" }

    if opts.format == "copy" || opts.format == "load-data" {
        var payload = [cols.map(csvEscape).joined(separator: ",")]
        payload += data.map { r in cols.indices.map { csvEscape(cellAt(r, $0)) }.joined(separator: ",") }
        let body = payload.joined(separator: "\n")
        if opts.format == "copy" {
            return Result(ok: true, sql: "COPY \(tbl) (\(colList)) FROM STDIN WITH (FORMAT csv, HEADER true);\n\(body)\n\\.", rows: data.count, error: nil)
        }
        let clean = String(opts.fileName.filter { isIdentChar($0) || $0 == "." || $0 == "-" || $0 == "/" })
        let file = clean.isEmpty ? "import.csv" : clean
        return Result(ok: true, sql: "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)", rows: data.count, error: nil)
    }

    let size = max(1, opts.batchSize)
    var stmts: [String] = []
    var start = 0
    while start < data.count {
        let end = min(data.count, start + size)
        let values = (start..<end).map { r in
            "  (" + cols.indices.map { ci in
                fieldLiteral(cellAt(data[r], ci), dialect: opts.dialect, inferTypes: opts.inferTypes)
            }.joined(separator: ", ") + ")"
        }
        stmts.append("INSERT INTO \(tbl) (\(colList)) VALUES\n" + values.joined(separator: ",\n") + ";")
        start += size
    }
    return Result(ok: true, sql: stmts.joined(separator: "\n"), rows: data.count, error: nil)
}

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