ER/Schema Visualizer — Kotlin source

// schema-visualizer — pure CREATE TABLE DDL → layered ER diagram as SVG.
// Kotlin port (canonical TS: src/lib/schema-visualizer.ts; Go twin:
// cli/schema-visualizer). Tolerant common subset of Postgres/MySQL/SQLite:
// unparseable statements degrade to notes, never throw. Integer geometry only
// (half-up rounding — JS Math.round parity), so every port draws the
// byte-identical diagram.
import kotlin.math.floor

object SchemaVisualizer {

    private val LAYOUT = Layout(rowHeight = 24, charWidth = 7, padding = 8, layerGap = 60, columnGap = 40)
    private data class Layout(val rowHeight: Int, val charWidth: Int, val padding: Int, val layerGap: Int, val columnGap: Int)

    private val QUOTES = mapOf('\'' to '\'', '"' to '"', '`' to '`', '[' to ']')
    private val PUNCT = setOf('(', ')', ',', '.')
    private val WORD_STOP = PUNCT + QUOTES.keys // chars that always end a word

    private val MODIFIERS = setOf(
        "NOT", "NULL", "PRIMARY", "KEY", "UNIQUE", "DEFAULT", "REFERENCES",
        "AUTO_INCREMENT", "AUTOINCREMENT", "ON", "COMMENT", "CHECK", "CONSTRAINT",
    )

    private val SKIP_WORDS = setOf("UNIQUE", "KEY", "INDEX", "CHECK", "EXCLUDE", "CONSTRAINT")

    /** Sentinel used to abandon one statement without failing the whole parse. */
    private object SkipStatement : RuntimeException()

    // ---------------------------------------------------------------------
    // Model
    // ---------------------------------------------------------------------

    data class Column(val name: String, val type: String, var nullable: Boolean, var isPrimaryKey: Boolean)
    data class ForeignKey(val fromTable: String, val fromColumn: String, val toTable: String, val toColumn: String?)
    data class Table(val name: String, val columns: MutableList<Column>)
    data class Schema(val tables: List<Table>, val foreignKeys: List<ForeignKey>, val notes: List<String>)

    data class Box(var x: Int, var y: Int, val w: Int, val h: Int)
    data class LaidTable(val table: Table, val box: Box, val titleBar: Box, val columnRows: List<Box>)
    data class Edge(val fk: ForeignKey, val path: String, val label: String)
    data class Geometry(val width: Int, val height: Int, val tables: List<LaidTable>, val edges: List<Edge>)

    data class DdlResult(val svg: String, val schema: Schema)

    // ---------------------------------------------------------------------
    // Tokenizer
    // ---------------------------------------------------------------------

    private enum class Kind { WORD, QIDENT, STRING, PUNCT }
    private data class Tok(val text: String, val kind: Kind)

    /** Round half up (JS Math.round parity). All geometry values are >= 0. */
    private fun r(v: Double): Int = floor(v + 0.5).toInt()

    /** Split on `;` outside strings/quoted identifiers (depth-agnostic: an
     *  unterminated paren cannot swallow the statements after it). */
    private fun splitStatements(ddl: String): List<String> {
        val out = mutableListOf<String>()
        val cur = StringBuilder()
        var i = 0
        val n = ddl.length
        while (i < n) {
            val ch = ddl[i]
            val close = QUOTES[ch]
            if (close != null) {
                cur.append(ch); i++
                while (i < n) {
                    cur.append(ddl[i])
                    if (ddl[i] == close) {
                        if (close == '\'' && i + 1 < n && ddl[i + 1] == '\'') { cur.append(ddl[i + 1]); i += 2; continue }
                        break
                    }
                    i++
                }
                i++
                continue
            }
            if (ch == ';') { out.add(cur.toString()); cur.setLength(0); i++; continue }
            cur.append(ch); i++
        }
        if (cur.toString().trim().isNotEmpty()) out.add(cur.toString())
        return out
    }

    /** Tokens: quoted identifiers/strings carry their text (quotes stripped);
     *  ( ) , . are punct; everything else is a word. */
    private fun tokenize(s: String): List<Tok> {
        val toks = mutableListOf<Tok>()
        var i = 0
        val n = s.length
        while (i < n) {
            val ch = s[i]
            if (ch.isWhitespace()) { i++; continue }
            val close = QUOTES[ch]
            if (close != null) {
                val text = StringBuilder()
                i++
                while (i < n) {
                    if (s[i] == close) {
                        if (close == '\'' && i + 1 < n && s[i + 1] == '\'') { text.append('\''); i += 2; continue }
                        break
                    }
                    text.append(s[i]); i++
                }
                i++ // consume the closer (or run off the end — tolerant)
                toks.add(Tok(text.toString(), if (ch == '\'') Kind.STRING else Kind.QIDENT))
                continue
            }
            if (ch in PUNCT) { toks.add(Tok(ch.toString(), Kind.PUNCT)); i++; continue }
            val word = StringBuilder()
            while (i < n && !s[i].isWhitespace() && s[i] !in WORD_STOP) { word.append(s[i]); i++ }
            toks.add(Tok(word.toString(), Kind.WORD))
        }
        return toks
    }

    private fun isP(t: Tok?, p: String): Boolean = t != null && t.kind == Kind.PUNCT && t.text == p
    private fun kw(t: Tok?, w: String): Boolean = t != null && t.kind == Kind.WORD && t.text.uppercase() == w

    /** name ('.' name)* — returns the joined name and the next token index. */
    private fun takeName(toks: List<Tok>, i: Int): Pair<String, Int>? {
        val first = toks.getOrNull(i) ?: return null
        if (first.kind != Kind.QIDENT && first.kind != Kind.WORD) return null
        var name = first.text
        var j = i + 1
        while (isP(toks.getOrNull(j), ".")) {
            val next = toks.getOrNull(j + 1) ?: break
            if (next.kind != Kind.QIDENT && next.kind != Kind.WORD) break
            name += "." + next.text
            j += 2
        }
        return name to j
    }

    /** ( name (',' name)* ')' — returns the names and the index after ')'. */
    private fun parenList(toks: List<Tok>, i: Int): Pair<List<String>, Int>? {
        if (!isP(toks.getOrNull(i), "(")) return null
        val names = mutableListOf<String>()
        var j = i + 1
        while (true) {
            val name = takeName(toks, j) ?: return null
            names.add(name.first)
            j = name.second
            if (isP(toks.getOrNull(j), ",")) { j++; continue }
            if (isP(toks.getOrNull(j), ")")) return names to j + 1
            return null
        }
    }

    // ---------------------------------------------------------------------
    // Parser
    // ---------------------------------------------------------------------

    /** Squeeze spaces around ( ) , inside a type, then normalize case. */
    private fun joinType(toks: List<Tok>): String = toks.joinToString(" ") { it.text }
        .replace(Regex("\\s*\\(\\s*"), "(")
        .replace(Regex("\\s*\\)\\s*"), ")")
        .replace(Regex("\\s*,\\s*"), ",")
        .trim()
        .uppercase()

    private fun parseColumn(line: List<Tok>, tableName: String, fks: MutableList<ForeignKey>): Column? {
        val name = takeName(line, 0) ?: return null
        var i = name.second
        val typeToks = mutableListOf<Tok>()
        while (i < line.size && !(line[i].kind == Kind.WORD && line[i].text.uppercase() in MODIFIERS)) {
            typeToks.add(line[i]); i++
        }
        val type = joinType(typeToks)
        var nullable = true
        var pk = false
        while (i < line.size) {
            val t = line[i]
            if (kw(t, "NOT") && kw(line.getOrNull(i + 1), "NULL")) { nullable = false; i += 2; continue }
            if (kw(t, "NULL")) { i++; continue }
            if (kw(t, "PRIMARY") && kw(line.getOrNull(i + 1), "KEY")) { pk = true; nullable = false; i += 2; continue }
            if (kw(t, "UNIQUE") || kw(t, "AUTO_INCREMENT") || kw(t, "AUTOINCREMENT")) { i++; continue }
            if (kw(t, "DEFAULT")) {
                i++
                if (isP(line.getOrNull(i), "(")) {
                    var d = 0
                    do {
                        if (isP(line.getOrNull(i), "(")) d++
                        if (isP(line.getOrNull(i), ")")) d--
                        i++
                    } while (i < line.size && d > 0)
                } else if (i < line.size) i++
                continue
            }
            if (kw(t, "COMMENT")) { i++; if (i < line.size && line[i].kind == Kind.STRING) i++; continue }
            if (kw(t, "ON")) {
                i += 2
                if (kw(line.getOrNull(i), "SET") || kw(line.getOrNull(i), "NO")) i += 2
                else if (i < line.size) i++
                continue
            }
            if (kw(t, "REFERENCES")) {
                i++
                val target = takeName(line, i)
                if (target != null) {
                    i = target.second
                    var toCol: String? = null
                    if (isP(line.getOrNull(i), "(")) {
                        val list = parenList(line, i)
                        if (list != null) { toCol = list.first.first(); i = list.second }
                    }
                    fks.add(ForeignKey(tableName, name.first, target.first, toCol))
                }
                continue
            }
            i++ // unknown modifier tolerated
        }
        return Column(name.first, type, nullable, pk)
    }

    fun parseDdl(ddl: String): Schema {
        if (ddl.trim().isEmpty()) return Schema(emptyList(), emptyList(), listOf("No DDL input."))
        val tables = mutableListOf<Table>()
        val fks = mutableListOf<ForeignKey>()
        val notes = mutableListOf<String>()
        for (stmt in splitStatements(ddl)) {
            if (stmt.trim().isEmpty()) continue
            val toks = tokenize(stmt)
            try {
                var i = 0
                if (!kw(toks.getOrNull(i), "CREATE")) throw SkipStatement
                i++
                while (kw(toks.getOrNull(i), "TEMP") || kw(toks.getOrNull(i), "TEMPORARY") || kw(toks.getOrNull(i), "UNLOGGED")) i++
                if (!kw(toks.getOrNull(i), "TABLE")) { notes.add("Skipped non-table statement."); continue }
                i++
                if (kw(toks.getOrNull(i), "IF") && kw(toks.getOrNull(i + 1), "NOT") && kw(toks.getOrNull(i + 2), "EXISTS")) i += 3
                val name = takeName(toks, i)
                if (name == null || !isP(toks.getOrNull(name.second), "(")) throw SkipStatement
                i = name.second + 1
                // body = tokens until the matching close paren (depth-aware)
                val body = mutableListOf<Tok>()
                var depth = 0
                while (i < toks.size) {
                    if (isP(toks[i], "(")) depth++
                    if (isP(toks[i], ")")) { if (depth == 0) break; depth-- }
                    body.add(toks[i]); i++
                }
                if (i >= toks.size) throw SkipStatement
                // split body into top-level comma lines
                val lines = mutableListOf<List<Tok>>()
                var ln = mutableListOf<Tok>()
                for (t in body) {
                    if (isP(t, "(")) depth++
                    if (isP(t, ")")) depth--
                    if (isP(t, ",") && depth == 0) { lines.add(ln); ln = mutableListOf(); continue }
                    ln.add(t)
                }
                if (ln.isNotEmpty()) lines.add(ln)
                val table = Table(name.first, mutableListOf())
                tables.add(table)
                for (line in lines) {
                    if (line.isEmpty()) continue
                    val first = line[0]
                    val u = if (first.kind == Kind.WORD) first.text.uppercase() else ""
                    if (u == "PRIMARY" && kw(line.getOrNull(1), "KEY")) {
                        val list = parenList(line, 2)
                        if (list != null) for (cn in list.first) {
                            val col = table.columns.find { it.name == cn }
                            if (col != null) { col.isPrimaryKey = true; col.nullable = false }
                        }
                        continue
                    }
                    if (u == "FOREIGN" && kw(line.getOrNull(1), "KEY")) {
                        val from = parenList(line, 2)
                        if (from != null && kw(line.getOrNull(from.second), "REFERENCES")) {
                            val target = takeName(line, from.second + 1)
                            if (target != null) {
                                var toCols: List<String>? = null
                                if (isP(line.getOrNull(target.second), "(")) {
                                    val to = parenList(line, target.second)
                                    if (to != null) toCols = to.first
                                }
                                from.first.forEachIndexed { idx, fc ->
                                    val toCol = if (toCols != null)
                                        (if (idx < toCols.size) toCols[idx] else toCols.last()) else null
                                    fks.add(ForeignKey(table.name, fc, target.first, toCol))
                                }
                            }
                        }
                        continue
                    }
                    if (u in SKIP_WORDS) continue
                    val col = parseColumn(line, table.name, fks)
                    if (col != null) table.columns.add(col)
                }
            } catch (_: SkipStatement) {
                notes.add("Skipped unparseable statement.")
            }
        }
        // resolve omitted FK target columns to the referenced table's first PK
        val foreignKeys = fks.map { fk ->
            if (fk.toColumn.isNullOrEmpty()) {
                val target = tables.find { it.name == fk.toTable }
                fk.copy(toColumn = target?.columns?.find { it.isPrimaryKey }?.name ?: "id")
            } else fk
        }
        return Schema(tables, foreignKeys, notes)
    }

    // ---------------------------------------------------------------------
    // Layout
    // ---------------------------------------------------------------------

    fun layoutSchema(schema: Schema): Geometry {
        val o = LAYOUT
        if (schema.tables.isEmpty()) return Geometry(0, 0, emptyList(), emptyList())
        val index = HashMap<String, Int>()
        schema.tables.forEachIndexed { i, t -> index.putIfAbsent(t.name, i) }
        val boxes = schema.tables.map { t ->
            val textLen = maxOf(t.name.length, t.columns.maxOfOrNull { "${it.name} ${it.type}".length } ?: 0, 1)
            Box(0, 0, textLen * o.charWidth + 2 * o.padding, o.rowHeight * (1 + t.columns.size) + o.padding)
        }.toMutableList()
        // referenced tables end up above referencing ones (longest path wins)
        val layerOf = IntArray(schema.tables.size)
        for (pass in schema.tables.indices) {
            var changed = false
            for (fk in schema.foreignKeys) {
                val ti = index[fk.fromTable]
                val tj = index[fk.toTable]
                if (ti == null || tj == null || ti == tj) continue
                if (layerOf[ti] < layerOf[tj] + 1) { layerOf[ti] = layerOf[tj] + 1; changed = true }
            }
            if (!changed) break
        }
        val layers = List(layerOf.max() + 1) { mutableListOf<Int>() }
        layerOf.forEachIndexed { i, l -> layers[l].add(i) }
        var y = 0
        var width = 0
        var height = 0
        for (layer in layers) {
            var x = 0
            var layerH = 0
            for (i in layer) {
                boxes[i].x = x
                boxes[i].y = y
                x += boxes[i].w + o.columnGap
                layerH = maxOf(layerH, boxes[i].h)
            }
            width = maxOf(width, x - o.columnGap)
            height = maxOf(height, y + layerH)
            y += layerH + o.layerGap
        }
        val edges = schema.foreignKeys.mapNotNull { fk ->
            val from = index[fk.fromTable]?.let { boxes[it] } ?: return@mapNotNull null
            val to = index[fk.toTable]?.let { boxes[it] } ?: return@mapNotNull null
            val x1 = to.x + r(to.w / 2.0)
            val y1 = to.y + to.h
            val x2 = from.x + r(from.w / 2.0)
            val y2 = from.y
            val midY = r((y1 + y2) / 2.0)
            Edge(fk, "M $x1 $y1 V $midY H $x2 V $y2", "${fk.fromColumn} → ${fk.toColumn}")
        }
        val laid = schema.tables.mapIndexed { i, table ->
            LaidTable(
                table, boxes[i],
                Box(boxes[i].x, boxes[i].y, boxes[i].w, o.rowHeight),
                table.columns.mapIndexed { ci, _ ->
                    Box(boxes[i].x, boxes[i].y + o.rowHeight * (1 + ci), boxes[i].w, o.rowHeight)
                },
            )
        }
        return Geometry(width, height, laid, edges)
    }

    // ---------------------------------------------------------------------
    // Renderer
    // ---------------------------------------------------------------------

    private fun esc(s: String): String = buildString(s.length) {
        for (c in s) when (c) {
            '&' -> append("&amp;")
            '<' -> append("&lt;")
            '>' -> append("&gt;")
            '"' -> append("&quot;")
            else -> append(c)
        }
    }

    fun renderSvg(geo: Geometry): String {
        val out = mutableListOf(
            "<svg xmlns=\"http://www.w3.org/2000/svg\" viewBox=\"0 0 ${geo.width} ${geo.height}\" " +
                "class=\"sv-root\" role=\"img\"><title>Schema diagram</title>",
        )
        val boxOf = HashMap<String, Box>()
        for (t in geo.tables) boxOf[t.table.name] = t.box
        for (e in geo.edges) {
            val from = boxOf[e.fk.fromTable] ?: continue
            val ax = from.x + r(from.w / 2.0)
            out.add(
                "<path class=\"sv-edge\" d=\"${e.path}\"/>" +
                    "<polygon class=\"sv-arrow\" points=\"${ax - 5},${from.y - 8} ${ax + 5},${from.y - 8} ${ax},${from.y}\"/>",
            )
        }
        for (t in geo.tables) {
            out.add(
                "<g class=\"sv-table\"><rect class=\"sv-box\" x=\"${t.box.x}\" y=\"${t.box.y}\" width=\"${t.box.w}\" height=\"${t.box.h}\" rx=\"6\"/>" +
                    "<rect class=\"sv-titlebar\" x=\"${t.titleBar.x}\" y=\"${t.titleBar.y}\" width=\"${t.titleBar.w}\" height=\"${t.titleBar.h}\" rx=\"6\"/>" +
                    "<text class=\"sv-title\" x=\"${t.box.x + 8}\" y=\"${t.titleBar.y + 17}\">${esc(t.table.name)}</text>",
            )
            t.table.columns.forEachIndexed { ci, c ->
                out.add(
                    "<text class=\"${if (c.isPrimaryKey) "sv-pk" else "sv-col"}\" x=\"${t.box.x + 8}\" " +
                        "y=\"${t.columnRows[ci].y + 17}\">${esc(c.name)} ${esc(c.type)}</text>",
                )
            }
            out.add("</g>")
        }
        out.add("</svg>")
        return out.joinToString("")
    }

    fun ddlToSvg(ddl: String): DdlResult {
        val schema = parseDdl(ddl)
        return DdlResult(renderSvg(layoutSchema(schema)), schema)
    }
}

// Example:
//   SchemaVisualizer.ddlToSvg(
//     "CREATE TABLE users (id INT PRIMARY KEY);" +
//     "CREATE TABLE posts (id INT PRIMARY KEY, user_id INT REFERENCES users(id), title TEXT);"
//   ).svg
// → users box on layer 0, posts below, one FK edge — byte-identical to the
//   TS/Go/… ports (integer geometry, same defaults).