CSV to SQL Importer — PHP source

<?php
// csv-to-sql — pure CSV → SQL import generator. PHP 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 text stays verbatim; empty
// fields become NULL. Never throws — errors travel in the result array.

function csv_to_rows(string $csv): array
{
    $rows = [];
    $field = '';
    $row = [];
    $inQ = false;
    $n = strlen($csv);
    for ($i = 0; $i < $n; $i++) {
        $ch = $csv[$i];
        if ($inQ) {
            if ($ch === '"') {
                if ($i + 1 < $n && $csv[$i + 1] === '"') { $field .= '"'; $i++; }
                else { $inQ = false; }
            } else {
                $field .= $ch;
            }
        } elseif ($ch === '"') {
            $inQ = true;
        } elseif ($ch === ',') {
            $row[] = $field; $field = '';
        } elseif ($ch === "\n") {
            $row[] = $field; $rows[] = $row; $row = []; $field = '';
        } elseif ($ch !== "\r") {
            $field .= $ch;
        }
    }
    if ($field !== '' || $row !== []) { $row[] = $field; $rows[] = $row; }
    return $rows;
}

function csv_to_sql_escape(string $s, string $dialect = 'standard'): string
{
    $out = str_replace("'", "''", $s);
    if ($dialect === 'mysql') {
        $out = str_replace(["\\", "\0", "\n", "\r", "\x1a"], ["\\\\", "\\0", "\\n", "\\r", "\\Z"], $out);
    }
    return $out;
}

function csv_to_sql_field(string $value, string $dialect = 'standard', bool $inferTypes = true): string
{
    if ($inferTypes) {
        if ($value === '') return 'NULL';
        // Verbatim numeric match — no float round-trip, keeps parity with TS/Go.
        if (preg_match('/^-?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$/', $value)) return $value;
    }
    return "'" . csv_to_sql_escape($value, $dialect) . "'";
}

function csv_to_sql_sanitize(string $name): string
{
    return preg_replace('/[^A-Za-z0-9_]/', '_', $name);
}

function csv_to_sql_headers(array $headers): array
{
    $seen = [];
    $out = [];
    foreach ($headers as $i => $h) {
        $id = csv_to_sql_sanitize(trim($h));
        if ($id === '') $id = 'col' . ($i + 1);
        $seen[$id] = ($seen[$id] ?? 0) + 1;
        if ($seen[$id] > 1) $id = $id . '_' . $seen[$id];
        $out[] = $id;
    }
    return $out;
}

function csv_to_sql_escape_csv(string $field): string
{
    if (strpbrk($field, ",\n\r\"") !== false) {
        return '"' . str_replace('"', '""', $field) . '"';
    }
    return $field;
}

/**
 * @return array{ok: bool, sql: string, rows: int, error: ?string}
 */
function csv_to_sql(string $csv, string $table, string $format = 'insert', string $dialect = 'standard',
                    int $batchSize = 100, bool $inferTypes = true, bool $quoteIdentifiers = true,
                    string $fileName = 'import.csv'): array
{
    $fail = function (string $msg): array {
        return ['ok' => false, 'sql' => '', 'rows' => 0, 'error' => $msg];
    };

    $text = trim($csv);
    $rows = csv_to_rows($text);
    if ($text === '' || count($rows) < 2) return $fail('No rows to import.');

    $cols = csv_to_sql_headers($rows[0]);
    $data = array_slice($rows, 1);
    $identDialect = $format === 'copy' ? 'postgres' : ($format === 'load-data' ? 'mysql' : $dialect);
    $q = function (string $name) use ($quoteIdentifiers, $identDialect): string {
        if (!$quoteIdentifiers) return $name;
        return $identDialect === 'mysql' ? "`{$name}`" : "\"{$name}\"";
    };
    $tblIdent = csv_to_sql_sanitize($table);
    $tbl = $q($tblIdent === '' ? 'tbl' : $tblIdent);
    $colList = implode(', ', array_map($q, $cols));

    if ($format === 'copy' || $format === 'load-data') {
        $payload = [implode(',', array_map('csv_to_sql_escape_csv', $cols))];
        foreach ($data as $r) {
            $fields = [];
            for ($ci = 0; $ci < count($cols); $ci++) {
                $fields[] = csv_to_sql_escape_csv($r[$ci] ?? '');
            }
            $payload[] = implode(',', $fields);
        }
        if ($format === 'copy') {
            $sql = "COPY {$tbl} ({$colList}) FROM STDIN WITH (FORMAT csv, HEADER true);\n"
                . implode("\n", $payload) . "\n\\.";
        } else {
            $clean = preg_replace('/[^A-Za-z0-9._\-\/]/', '', $fileName);
            if ($clean === '' || $clean === null) $clean = 'import.csv';
            $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" . implode("\n", $payload);
        }
        return ['ok' => true, 'sql' => $sql, 'rows' => count($data), 'error' => null];
    }

    $size = max(1, $batchSize);
    $stmts = [];
    for ($start = 0; $start < count($data); $start += $size) {
        $values = [];
        foreach (array_slice($data, $start, $size) as $r) {
            $vals = [];
            for ($ci = 0; $ci < count($cols); $ci++) {
                $vals[] = csv_to_sql_field($r[$ci] ?? '', $dialect, $inferTypes);
            }
            $values[] = '  (' . implode(', ', $vals) . ')';
        }
        $stmts[] = "INSERT INTO {$tbl} ({$colList}) VALUES\n" . implode(",\n", $values) . ';';
    }
    return ['ok' => true, 'sql' => implode("\n", $stmts), 'rows' => count($data), 'error' => null];
}

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