CSV to SQL Importer — Go source

// Package csvtosql is the Go twin of CosmoDev's src/lib/csv-to-sql.ts (dual
// source: the web lib is TypeScript, the CLI lib is Go — kept in lock-step).
// Pure + deterministic, never panics. The table-driven tests in
// csv-to-sql_test.go share vectors with src/lib/csv-to-sql.test.ts so the two
// implementations are held to the same contract.
//
// The converter mirrors the TS lib exactly: parse RFC 4180 CSV with the same
// hand-rolled loop as src/lib/csv.ts (csvToRows — ported verbatim, not via
// encoding/csv, whose lazy-quote and CR semantics differ), sanitize header
// names into SQL identifiers (de-duplicated deterministically), then emit one
// of three formats: batched multi-row INSERT statements (dialect-aware
// escaping + identifier quoting), a Postgres COPY FROM STDIN block, or a
// MySQL LOAD DATA statement with the CSV payload. Numeric-looking fields are
// emitted VERBATIM (no float parsing or reformatting — "007" stays "007",
// "1e3" stays "1e3"), so TypeScript and Go output is byte-identical. Empty
// fields become NULL under type inference.
package csvtosql

import (
	"regexp"
	"strings"
)

// Dialect selects identifier quoting and string escaping for INSERT output.
type Dialect string

const (
	DialectStandard Dialect = "standard"
	DialectMysql    Dialect = "mysql"
	DialectPostgres Dialect = "postgres"
)

// Format selects the emitted SQL shape.
type Format string

const (
	FormatInsert  Format = "insert"
	FormatCopy    Format = "copy"
	FormatLoadData Format = "load-data"
)

// Options configures CsvToSql. Table is required. Zero values resolve to the
// TS defaults: insert format, standard dialect, batch 100, inference on,
// identifiers quoted, file name "import.csv". The two booleans are pointers
// so the zero value can mean "unset" (nil → default true), mirroring the TS
// optional fields.
type Options struct {
	Table            string
	Format           Format
	Dialect          Dialect
	BatchSize        int
	InferTypes       *bool
	QuoteIdentifiers *bool
	FileName         string
}

// Result mirrors the TS Result shape.
type Result struct {
	Ok    bool
	Sql   string
	Rows  int
	Error string
}

// numericRe matches a numeric literal (integer, decimal, or scientific).
// The matched text is emitted verbatim — no float round-trip.
var numericRe = regexp.MustCompile(`^-?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$`)

func boolOr(p *bool, def bool) bool {
	if p == nil {
		return def
	}
	return *p
}

// csvToRows is a verbatim port of csvToRows in src/lib/csv.ts: RFC 4180
// parser with the same leniency (bare quotes toggle quoted mode, CR is
// dropped, a trailing field without a newline still completes the row).
func csvToRows(csv string) [][]string {
	var rows [][]string
	field := strings.Builder{}
	var row []string
	inQ := false
	runes := []rune(csv)
	for i := 0; i < len(runes); i++ {
		ch := runes[i]
		switch {
		case inQ:
			if ch == '"' {
				if i+1 < len(runes) && runes[i+1] == '"' {
					field.WriteRune('"')
					i++
				} else {
					inQ = false
				}
			} else {
				field.WriteRune(ch)
			}
		case ch == '"':
			inQ = true
		case ch == ',':
			row = append(row, field.String())
			field.Reset()
		case ch == '\n':
			row = append(row, field.String())
			rows = append(rows, row)
			row = nil
			field.Reset()
		case ch != '\r':
			field.WriteRune(ch)
		}
	}
	if field.Len() > 0 || row != nil {
		row = append(row, field.String())
		rows = append(rows, row)
	}
	return rows
}

// EscapeSqlString escapes a single-quoted SQL literal: '' doubling
// everywhere; MySQL mode additionally escapes the backslash and the control
// bytes \0 \n \r \x1a. Same contract as jsonToSql's escapeSqlString.
func EscapeSqlString(s string, dialect Dialect) string {
	out := strings.ReplaceAll(s, "'", "''")
	if dialect == DialectMysql {
		out = strings.ReplaceAll(out, `\`, `\\`)
		out = strings.ReplaceAll(out, "\x00", `\0`)
		out = strings.ReplaceAll(out, "\n", `\n`)
		out = strings.ReplaceAll(out, "\r", `\r`)
		out = strings.ReplaceAll(out, "\x1a", `\Z`)
	}
	return out
}

// FieldLiteral renders one CSV field as an INSERT value literal.
func FieldLiteral(value string, dialect Dialect, inferTypes bool) string {
	if inferTypes {
		if value == "" {
			return "NULL"
		}
		if numericRe.MatchString(value) {
			return value
		}
	}
	return "'" + EscapeSqlString(value, dialect) + "'"
}

// SanitizeIdent replaces characters SQL identifiers cannot carry with '_'.
func SanitizeIdent(name string) string {
	var b strings.Builder
	for _, r := range name {
		if (r >= 'A' && r <= 'Z') || (r >= 'a' && r <= 'z') || (r >= '0' && r <= '9') || r == '_' {
			b.WriteRune(r)
		} else {
			b.WriteRune('_')
		}
	}
	return b.String()
}

func quoteIdent(name string, dialect Dialect, quoteIdentifiers bool) string {
	if !quoteIdentifiers {
		return name
	}
	if dialect == DialectMysql {
		return "`" + name + "`"
	}
	return `"` + name + `"`
}

// sanitizeHeaders maps a header row to SQL column names: trim, sanitize,
// empty → col<N> (1-based), duplicates get _2, _3… suffixes in first-seen
// order.
func sanitizeHeaders(headers []string) []string {
	out := make([]string, 0, len(headers))
	seen := map[string]int{}
	for i, h := range headers {
		id := SanitizeIdent(strings.TrimSpace(h))
		if id == "" {
			id = "col" + itoa(i+1)
		}
		seen[id]++
		if n := seen[id]; n > 1 {
			id = id + "_" + itoa(n)
		}
		out = append(out, id)
	}
	return out
}

func itoa(n int) string {
	if n == 0 {
		return "0"
	}
	var b [20]byte
	i := len(b)
	for n > 0 {
		i--
		b[i] = byte('0' + n%10)
		n /= 10
	}
	return string(b[i:])
}

// csvEscape quotes a CSV field for re-emission (RFC 4180).
func csvEscape(field string) string {
	if strings.ContainsAny(field, ",\n\r\"") {
		return `"` + strings.ReplaceAll(field, `"`, `""`) + `"`
	}
	return field
}

// sanitizeFileName keeps path-safe characters for the LOAD DATA reference.
func sanitizeFileName(name string) string {
	var b strings.Builder
	for _, r := range name {
		if (r >= 'A' && r <= 'Z') || (r >= 'a' && r <= 'z') || (r >= '0' && r <= '9') ||
			r == '.' || r == '_' || r == '-' || r == '/' {
			b.WriteRune(r)
		}
	}
	if b.Len() == 0 {
		return "import.csv"
	}
	return b.String()
}

// CsvToSql converts CSV text into SQL import statements. Never panics;
// errors come back through Result.
func CsvToSql(csv string, opts Options) Result {
	fail := func(msg string) Result { return Result{Ok: false, Error: msg} }

	text := strings.TrimSpace(csv)
	if text == "" {
		return fail("No rows to import.")
	}

	// csvToRows on trimmed non-empty text always yields >= 1 row.
	rows := csvToRows(text)
	headers := rows[0]
	if len(rows) < 2 {
		return fail("No data rows below the header.")
	}
	data := rows[1:]

	format := opts.Format
	if format == "" {
		format = FormatInsert
	}
	dialect := opts.Dialect
	if dialect == "" {
		dialect = DialectStandard
	}
	inferTypes := boolOr(opts.InferTypes, true)
	quoteIdentifiers := boolOr(opts.QuoteIdentifiers, true)

	cols := sanitizeHeaders(headers)
	// Quoting semantics follow the format: INSERT honors the dialect option,
	// COPY is Postgres, LOAD DATA is MySQL.
	identDialect := dialect
	switch format {
	case FormatCopy:
		identDialect = DialectPostgres
	case FormatLoadData:
		identDialect = DialectMysql
	}
	tableIdent := SanitizeIdent(opts.Table)
	if tableIdent == "" {
		tableIdent = "tbl"
	}
	table := quoteIdent(tableIdent, identDialect, quoteIdentifiers)
	quotedCols := make([]string, len(cols))
	for i, c := range cols {
		quotedCols[i] = quoteIdent(c, identDialect, quoteIdentifiers)
	}
	colList := strings.Join(quotedCols, ", ")

	if format == FormatCopy || format == FormatLoadData {
		payload := make([]string, 0, len(data)+1)
		escapedCols := make([]string, len(cols))
		for i, c := range cols {
			escapedCols[i] = csvEscape(c)
		}
		payload = append(payload, strings.Join(escapedCols, ","))
		for _, row := range data {
			fields := make([]string, len(cols))
			for ci := range cols {
				v := ""
				if ci < len(row) {
					v = row[ci]
				}
				fields[ci] = csvEscape(v)
			}
			payload = append(payload, strings.Join(fields, ","))
		}
		body := strings.Join(payload, "\n")
		if format == FormatCopy {
			sql := "COPY " + table + " (" + colList + ") FROM STDIN WITH (FORMAT csv, HEADER true);\n" +
				body + "\n\\."
			return Result{Ok: true, Sql: sql, Rows: len(data)}
		}
		sql := "LOAD DATA LOCAL INFILE '" + sanitizeFileName(opts.FileName) + "'\n" +
			"INTO TABLE " + table + "\n" +
			"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\n" +
			"LINES TERMINATED BY '\\n'\n" +
			"IGNORE 1 LINES;\n\n" + body
		return Result{Ok: true, Sql: sql, Rows: len(data)}
	}

	// INSERT: batched multi-row statements.
	batchSize := opts.BatchSize
	if batchSize <= 0 {
		batchSize = 100
	}
	var stmts []string
	for start := 0; start < len(data); start += batchSize {
		end := min(start+batchSize, len(data))
		var values []string
		for _, row := range data[start:end] {
			vals := make([]string, len(cols))
			for ci := range cols {
				v := ""
				if ci < len(row) {
					v = row[ci]
				}
				vals[ci] = FieldLiteral(v, dialect, inferTypes)
			}
			values = append(values, "  ("+strings.Join(vals, ", ")+")")
		}
		stmts = append(stmts, "INSERT INTO "+table+" ("+colList+") VALUES\n"+strings.Join(values, ",\n")+";")
	}
	return Result{Ok: true, Sql: strings.Join(stmts, "\n"), Rows: len(data)}
}