JSON to SQL INSERT — Go source

// Package jsontosql is the Go twin of CosmoDev's src/lib/jsonToSql.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
// json-to-sql_test.go share vectors with src/lib/jsonToSql.test.ts so the two
// implementations are held to the same contract.
//
// The converter mirrors the TS lib exactly: double single quotes (and, for
// MySQL, escape backslashes + the control bytes \0 \n \r \x1a), render
// null/boolean/number/object/array values as SQL literals, gather the union of
// object keys in first-seen order, and emit one multi-row INSERT statement. JSON
// object keys are decoded in document order (via an ordered decoder) so column
// order matches JS Object.keys insertion order.
package jsontosql

import (
	"bytes"
	"encoding/json"
	"fmt"
	"io"
	"math"
	"regexp"
	"strconv"
	"strings"
)

// Dialect selects the SQL dialect for identifier quoting and string escaping.
type Dialect string

const (
	// DialectStandard is the zero value, matching the TS default
	// (dialect: 'standard'). Standard and Postgres use double-quoted
	// identifiers and TRUE/FALSE booleans.
	DialectStandard Dialect = "standard"
	DialectMysql    Dialect = "mysql"
	DialectPostgres Dialect = "postgres"
)

// Options configures JsonToInsert. Table is required. Dialect and
// QuoteIdentifiers mirror the TS option object; their zero values resolve to the
// TS defaults (standard dialect, identifiers quoted).
//
// QuoteIdentifiers is a *bool so the zero value means the TS default (true)
// while a non-nil false still means "do not quote" — exactly like the TS lib's
// distinction between omitted (→ true) and false (→ unquoted).
type Options struct {
	Table            string
	Dialect          Dialect // "" → "standard" (default)
	QuoteIdentifiers *bool   // nil → true (default); non-nil used verbatim
}

// Result is the outcome of a conversion. Error is empty when Ok is true (the TS
// lib uses null there).
type Result struct {
	Ok    bool
	Sql   string
	Rows  int
	Error string
}

// OrderedMap is a JSON object decoded with its keys in document order (unlike
// map[string]interface{}, whose iteration order is randomized). It is internal
// to the decoder so the emitted column order matches JS Object.keys insertion
// order.
type OrderedMap struct {
	Keys   []string
	Values map[string]interface{}
}

// MarshalJSON emits the object compact, keys in document order, no HTML escaping
// — mirroring JSON.stringify on the TS side.
func (om *OrderedMap) MarshalJSON() ([]byte, error) {
	var buf bytes.Buffer
	buf.WriteByte('{')
	for i, k := range om.Keys {
		if i > 0 {
			buf.WriteByte(',')
		}
		kb, err := json.Marshal(k)
		if err != nil {
			return nil, err
		}
		buf.Write(kb)
		buf.WriteByte(':')
		vb, err := json.Marshal(om.Values[k])
		if err != nil {
			return nil, err
		}
		buf.Write(vb)
	}
	buf.WriteByte('}')
	return buf.Bytes(), nil
}

// EscapeSqlString doubles single quotes (every dialect) and, for MySQL, escapes
// backslashes and the control bytes \0 \n \r \x1a. It is the Go twin of
// escapeSqlString() in src/lib/jsonToSql.ts and must agree on every shared
// vector.
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
}

// SqlLiteral renders a single decoded JSON value as a SQL literal. It is the Go
// twin of sqlLiteral() in src/lib/jsonToSql.ts: null → NULL, booleans →
// TRUE/FALSE (1/0 for MySQL), numbers → their text form (non-finite → NULL),
// strings → quoted+escaped, objects/arrays → quoted+escaped JSON text.
func SqlLiteral(value interface{}, dialect Dialect) string {
	if value == nil {
		return "NULL"
	}
	switch v := value.(type) {
	case bool:
		if dialect == DialectMysql {
			if v {
				return "1"
			}
			return "0"
		}
		if v {
			return "TRUE"
		}
		return "FALSE"
	case float64:
		if math.IsNaN(v) || math.IsInf(v, 0) {
			return "NULL"
		}
		return jsNumberString(v)
	case string:
		return "'" + EscapeSqlString(v, dialect) + "'"
	default:
		// objects (*OrderedMap) / arrays ([]interface{}) → JSON text literal
		s, err := jsonString(v)
		if err != nil {
			return "NULL" // mirror the TS lib: never throw
		}
		return "'" + EscapeSqlString(s, dialect) + "'"
	}
}

var nonIdentChar = regexp.MustCompile(`[^A-Za-z0-9_]`)

// sanitizeIdent replaces every non [A-Za-z0-9_] rune with '_' and falls back to
// "tbl" for an empty result — mirroring sanitizeIdent() in the TS lib.
func sanitizeIdent(name string) string {
	cleaned := nonIdentChar.ReplaceAllString(name, "_")
	if cleaned == "" {
		return "tbl"
	}
	return cleaned
}

// quoteIdent wraps an identifier in the dialect's quoting characters (or leaves
// it bare when quoteIdentifiers is false) — mirroring quoteIdent() in the TS lib.
func quoteIdent(name string, dialect Dialect, quoteIdentifiers bool) string {
	if !quoteIdentifiers {
		return name
	}
	if dialect == DialectMysql {
		return "`" + name + "`"
	}
	return "\"" + name + "\""
}

// jsNumberString formats a float64 the way JS String(number) does for the common
// cases: integers within ±1e21 print without a decimal point; everything else
// uses the shortest round-tripping form.
func jsNumberString(v float64) string {
	if v == math.Trunc(v) && math.Abs(v) < 1e21 {
		return strconv.FormatFloat(v, 'f', -1, 64)
	}
	return strconv.FormatFloat(v, 'g', -1, 64)
}

// jsonString serializes a nested value the way TS JSON.stringify does — compact
// and without HTML escaping — for embedding as a JSON text literal. Object keys
// stay in document order via OrderedMap.MarshalJSON.
func jsonString(v interface{}) (string, error) {
	var buf bytes.Buffer
	enc := json.NewEncoder(&buf)
	enc.SetEscapeHTML(false)
	if err := enc.Encode(v); err != nil {
		return "", err
	}
	// json.Encoder.Encode appends a trailing newline; strip it.
	return strings.TrimRight(buf.String(), "\n"), nil
}

// JsonToInsert parses a JSON string (array of row objects, or a single object)
// and builds a single multi-row INSERT statement. It is the Go twin of
// jsonToInsert() in src/lib/jsonToSql.ts and must agree on every shared vector.
func JsonToInsert(jsonString string, opts Options) Result {
	data, err := decodeOrdered(strings.NewReader(jsonString))
	if err != nil {
		return Result{Ok: false, Error: err.Error()}
	}

	var arr []interface{}
	if d, ok := data.([]interface{}); ok {
		arr = d
	} else {
		arr = []interface{}{data}
	}

	if len(arr) == 0 {
		return Result{Ok: false, Error: "No rows to insert."}
	}
	for _, r := range arr {
		if _, ok := r.(*OrderedMap); !ok {
			return Result{Ok: false, Error: "Rows must be objects."}
		}
	}

	dialect := opts.Dialect
	if dialect == "" {
		dialect = DialectStandard
	}
	quoteIdents := true
	if opts.QuoteIdentifiers != nil {
		quoteIdents = *opts.QuoteIdentifiers
	}
	table := quoteIdent(sanitizeIdent(opts.Table), dialect, quoteIdents)

	// Union of keys, first-seen order (object keys decoded in document order).
	var cols []string
	seen := map[string]bool{}
	for _, r := range arr {
		om := r.(*OrderedMap)
		for _, k := range om.Keys {
			if !seen[k] {
				seen[k] = true
				cols = append(cols, k)
			}
		}
	}
	quotedCols := make([]string, len(cols))
	for i, c := range cols {
		quotedCols[i] = quoteIdent(c, dialect, quoteIdents)
	}
	colList := strings.Join(quotedCols, ", ")

	valueLists := make([]string, len(arr))
	for i, r := range arr {
		om := r.(*OrderedMap)
		vals := make([]string, len(cols))
		for j, c := range cols {
			v, ok := om.Values[c]
			if !ok {
				v = nil
			}
			vals[j] = SqlLiteral(v, dialect)
		}
		valueLists[i] = "  (" + strings.Join(vals, ", ") + ")"
	}

	sql := "INSERT INTO " + table + " (" + colList + ") VALUES\n" + strings.Join(valueLists, ",\n") + ";"
	return Result{Ok: true, Sql: sql, Rows: len(arr)}
}

// decodeOrdered reads one JSON value, preserving object key order via
// *OrderedMap. It is the order-preserving analogue of json.Unmarshal into
// interface{}: objects → *OrderedMap, arrays → []interface{}, numbers →
// float64, booleans → bool, strings → string, null → nil.
func decodeOrdered(r io.Reader) (interface{}, error) {
	dec := json.NewDecoder(r)
	return parseValue(dec)
}

// parseValue reads a single JSON value from the decoder stream.
func parseValue(dec *json.Decoder) (interface{}, error) {
	t, err := dec.Token()
	if err != nil {
		return nil, err
	}
	delim, isDelim := t.(json.Delim)
	if !isDelim {
		// primitive token: bool, float64, string, or nil (JSON null)
		return t, nil
	}
	switch delim {
	case '{':
		om := &OrderedMap{Values: map[string]interface{}{}}
		for dec.More() {
			kTok, err := dec.Token()
			if err != nil {
				return nil, err
			}
			key, ok := kTok.(string)
			if !ok {
				return nil, fmt.Errorf("json: expected string object key")
			}
			val, err := parseValue(dec)
			if err != nil {
				return nil, err
			}
			if _, exists := om.Values[key]; !exists {
				om.Keys = append(om.Keys, key)
			}
			om.Values[key] = val
		}
		// consume the closing '}'
		if _, err := dec.Token(); err != nil {
			return nil, err
		}
		return om, nil
	case '[':
		var arr []interface{}
		for dec.More() {
			val, err := parseValue(dec)
			if err != nil {
				return nil, err
			}
			arr = append(arr, val)
		}
		// consume the closing ']'
		if _, err := dec.Token(); err != nil {
			return nil, err
		}
		return arr, nil
	}
	return nil, fmt.Errorf("json: unexpected delimiter %q", delim)
}