ER/Schema Visualizer — Ruby source

# schema-visualizer — pure CREATE TABLE DDL → layered ER diagram as SVG.
# Ruby 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 raise. Integer geometry only
# (half-up rounding — JS Math.round parity), so every port draws the
# byte-identical diagram.
module SchemaVisualizer
  module_function

  LAYOUT = { row_height: 24, char_width: 7, padding: 8, layer_gap: 60, column_gap: 40 }.freeze
  MODIFIERS = %w[NOT NULL PRIMARY KEY UNIQUE DEFAULT REFERENCES
                 AUTO_INCREMENT AUTOINCREMENT ON COMMENT CHECK CONSTRAINT].freeze
  QUOTES = { "'" => "'", '"' => '"', '`' => '`', '[' => ']' }.freeze

  def r_half_up(v)
    (v + 0.5).floor
  end

  # Split on `;` outside strings/quoted identifiers. Depth-agnostic: an
  # unterminated paren cannot swallow the statements after it.
  def split_statements(ddl)
    out = []
    cur = +''
    i = 0
    n = ddl.length
    while i < n
      ch = ddl[i]
      if QUOTES.key?(ch)
        close = QUOTES[ch]
        cur << ch
        i += 1
        while i < n
          cur << ddl[i]
          if ddl[i] == close
            if close == "'" && i + 1 < n && ddl[i + 1] == "'"
              cur << ddl[i + 1]
              i += 2
              next
            end
            break
          end
          i += 1
        end
        i += 1
        next
      end
      if ch == ';'
        out << cur
        cur = +''
        i += 1
        next
      end
      cur << ch
      i += 1
    end
    out << cur if cur.strip != ''
    out
  end

  # Tokens: [kind, text] — qident/string carry text with quotes stripped.
  def tokenize(s)
    toks = []
    i = 0
    n = s.length
    while i < n
      ch = s[i]
      if ch.match?(/\s/)
        i += 1
        next
      end
      if QUOTES.key?(ch)
        close = QUOTES[ch]
        text = +''
        i += 1
        while i < n
          if s[i] == close
            if close == "'" && i + 1 < n && s[i + 1] == "'"
              text << "'"
              i += 2
              next
            end
            break
          end
          text << s[i]
          i += 1
        end
        i += 1
        toks << [(ch == "'" ? :string : :qident), text]
        next
      end
      if '(),.'.include?(ch)
        toks << [:punct, ch]
        i += 1
        next
      end
      j = i
      j += 1 while j < n && !s[j].match?(/\s/) && !"',().`[]".include?(s[j])
      toks << [:word, s[i...j]]
      i = j
    end
    toks
  end

  def punct?(tok, p)
    !tok.nil? && tok[0] == :punct && tok[1] == p
  end

  def kw?(tok, w)
    !tok.nil? && tok[0] == :word && tok[1].upcase == w
  end

  def take_name(toks, i)
    first = toks[i]
    return nil if first.nil? || (first[0] != :qident && first[0] != :word)

    name = +''
    name << first[1]
    j = i + 1
    while punct?(toks[j], '.') && !toks[j + 1].nil? && %i[qident word].include?(toks[j + 1][0])
      name << '.' << toks[j + 1][1]
      j += 2
    end
    [name, j]
  end

  def paren_list(toks, i)
    return nil unless punct?(toks[i], '(')

    names = []
    j = i + 1
    loop do
      name = take_name(toks, j)
      return nil if name.nil?

      names << name[0]
      j = name[1]
      if punct?(toks[j], ',')
        j += 1
        next
      end
      return [names, j + 1] if punct?(toks[j], ')')

      return nil
    end
  end

  def join_type(toks)
    toks.map { |t| t[1] }.join(' ')
        .gsub(/\s*\(\s*/, '(').gsub(/\s*\)\s*/, ')').gsub(/\s*,\s*/, ',')
        .strip.upcase
  end

  def parse_column(line, table_name, fks)
    name = take_name(line, 0)
    return nil if name.nil?

    i = name[1]
    type_toks = []
    while i < line.length && !(line[i][0] == :word && MODIFIERS.include?(line[i][1].upcase))
      type_toks << line[i]
      i += 1
    end
    nullable = true
    pk = false
    while i < line.length
      t = line[i]
      if kw?(t, 'NOT') && kw?(line[i + 1], 'NULL')
        nullable = false
        i += 2
        next
      end
      if kw?(t, 'NULL')
        i += 1
        next
      end
      if kw?(t, 'PRIMARY') && kw?(line[i + 1], 'KEY')
        pk = true
        nullable = false
        i += 2
        next
      end
      if kw?(t, 'UNIQUE') || kw?(t, 'AUTO_INCREMENT') || kw?(t, 'AUTOINCREMENT')
        i += 1
        next
      end
      if kw?(t, 'DEFAULT')
        i += 1
        if punct?(line[i], '(')
          depth = 0
          while i < line.length
            depth += 1 if punct?(line[i], '(')
            depth -= 1 if punct?(line[i], ')')
            i += 1
            break if depth.zero?
          end
        elsif i < line.length
          i += 1
        end
        next
      end
      if kw?(t, 'COMMENT')
        i += 1
        i += 1 if line[i] && line[i][0] == :string
        next
      end
      if kw?(t, 'ON')
        i += 2
        if kw?(line[i], 'SET') || kw?(line[i], 'NO')
          i += 2
        elsif i < line.length
          i += 1
        end
        next
      end
      if kw?(t, 'REFERENCES')
        i += 1
        target = take_name(line, i)
        if target
          i = target[1]
          to_col = nil
          if punct?(line[i], '(')
            list = paren_list(line, i)
            if list
              to_col = list[0][0]
              i = list[1]
            end
          end
          fks << { 'fromTable' => table_name, 'fromColumn' => name[0], 'toTable' => target[0], 'toColumn' => to_col }
        end
        next
      end
      i += 1 # unknown modifier tolerated
    end
    { 'name' => name[0], 'type' => join_type(type_toks), 'nullable' => nullable, 'isPrimaryKey' => pk }
  end

  def parse_ddl(ddl)
    return { 'tables' => [], 'foreignKeys' => [], 'notes' => ['No DDL input.'] } if ddl.strip.empty?

    tables = []
    fks = []
    notes = []
    split_statements(ddl).each do |stmt|
      next if stmt.strip.empty?

      toks = tokenize(stmt)
      begin
        i = 0
        raise StandardError unless kw?(toks[i], 'CREATE')

        i += 1
        i += 1 while kw?(toks[i], 'TEMP') || kw?(toks[i], 'TEMPORARY') || kw?(toks[i], 'UNLOGGED')
        unless kw?(toks[i], 'TABLE')
          notes << 'Skipped non-table statement.'
          next
        end
        i += 1
        i += 3 if kw?(toks[i], 'IF') && kw?(toks[i + 1], 'NOT') && kw?(toks[i + 2], 'EXISTS')
        name = take_name(toks, i)
        raise StandardError if name.nil? || !punct?(toks[name[1]], '(')

        i = name[1] + 1
        body = []
        depth = 0
        while i < toks.length
          depth += 1 if punct?(toks[i], '(')
          if punct?(toks[i], ')')
            break if depth.zero?

            depth -= 1
          end
          body << toks[i]
          i += 1
        end
        raise StandardError if i >= toks.length

        lines = []
        line = []
        depth = 0
        body.each do |t|
          depth += 1 if punct?(t, '(')
          depth -= 1 if punct?(t, ')')
          if punct?(t, ',') && depth.zero?
            lines << line
            line = []
            next
          end
          line << t
        end
        lines << line unless line.empty?
        table = { 'name' => name[0], 'columns' => [] }
        tables << table
        lines.each do |t2|
          next if t2.empty?

          first = t2[0]
          u = first[0] == :word ? first[1].upcase : ''
          if u == 'PRIMARY' && kw?(t2[1], 'KEY')
            list = paren_list(t2, 2)
            if list
              list[0].each do |cn|
                col = table['columns'].find { |c| c['name'] == cn }
                next unless col

                col['isPrimaryKey'] = true
                col['nullable'] = false
              end
            end
            next
          end
          if u == 'FOREIGN' && kw?(t2[1], 'KEY')
            from = paren_list(t2, 2)
            if from && kw?(t2[from[1]], 'REFERENCES')
              target = take_name(t2, from[1] + 1)
              if target
                to_cols = nil
                if punct?(t2[target[1]], '(')
                  to = paren_list(t2, target[1])
                  to_cols = to[0] if to
                end
                from[0].each_with_index do |fc, idx|
                  to_col = nil
                  if to_cols
                    to_col = idx < to_cols.length ? to_cols[idx] : to_cols[-1]
                  end
                  fks << { 'fromTable' => table['name'], 'fromColumn' => fc,
                           'toTable' => target[0], 'toColumn' => to_col }
                end
              end
            end
            next
          end
          next if %w[UNIQUE KEY INDEX CHECK EXCLUDE CONSTRAINT].include?(u)

          col = parse_column(t2, table['name'], fks)
          table['columns'] << col if col
        end
      rescue StandardError
        notes << 'Skipped unparseable statement.'
      end
    end

    foreign_keys = fks.map do |fk|
      next fk if fk['toColumn']

      target = tables.find { |t| t['name'] == fk['toTable'] }
      pk = target && target['columns'].find { |c| c['isPrimaryKey'] }
      fk.merge('toColumn' => pk ? pk['name'] : 'id')
    end
    { 'tables' => tables, 'foreignKeys' => foreign_keys, 'notes' => notes }
  end

  def layout_schema(schema)
    o = LAYOUT
    return { 'width' => 0, 'height' => 0, 'tables' => [], 'edges' => [] } if schema['tables'].empty?

    index = {}
    schema['tables'].each_with_index { |t, i| index[t['name']] ||= i }
    boxes = schema['tables'].map do |t|
      lens = [t['name'].length] + t['columns'].map { |c| "#{c['name']} #{c['type']}".length } + [1]
      { 'x' => 0, 'y' => 0,
        'w' => r_half_up(lens.max * o[:char_width] + 2 * o[:padding]),
        'h' => r_half_up(o[:row_height] * (1 + t['columns'].length) + o[:padding]) }
    end
    layer_of = Array.new(schema['tables'].length, 0)
    schema['tables'].length.times do
      changed = false
      schema['foreignKeys'].each do |fk|
        ti = index[fk['fromTable']]
        tj = index[fk['toTable']]
        next if ti.nil? || tj.nil? || ti == tj

        if layer_of[ti] < layer_of[tj] + 1
          layer_of[ti] = layer_of[tj] + 1
          changed = true
        end
      end
      break unless changed
    end
    layers = {}
    layer_of.each_with_index { |l, i| (layers[l] ||= []) << i }
    y = 0
    width = 0
    height = 0
    layers.keys.sort.each do |li|
      x = 0
      layer_h = 0
      layers[li].each do |i|
        boxes[i]['x'] = x
        boxes[i]['y'] = y
        x += boxes[i]['w'] + o[:column_gap]
        layer_h = [layer_h, boxes[i]['h']].max
      end
      width = [width, x - o[:column_gap]].max
      height = [height, y + layer_h].max
      y += layer_h + o[:layer_gap]
    end
    edges = schema['foreignKeys'].filter_map do |fk|
      frm = index[fk['fromTable']]
      to = index[fk['toTable']]
      next if frm.nil? || to.nil?

      x1 = boxes[to]['x'] + r_half_up(boxes[to]['w'] / 2.0)
      y1 = boxes[to]['y'] + boxes[to]['h']
      x2 = boxes[frm]['x'] + r_half_up(boxes[frm]['w'] / 2.0)
      y2 = boxes[frm]['y']
      mid_y = r_half_up((y1 + y2) / 2.0)
      { 'fk' => fk, 'path' => "M #{x1} #{y1} V #{mid_y} H #{x2} V #{y2}",
        'label' => "#{fk['fromColumn']} → #{fk['toColumn']}" }
    end
    laid = schema['tables'].each_with_index.map do |t, i|
      rows = t['columns'].each_index.map do |ci|
        { 'x' => boxes[i]['x'], 'y' => boxes[i]['y'] + o[:row_height] * (1 + ci),
          'w' => boxes[i]['w'], 'h' => o[:row_height] }
      end
      { 'table' => t, 'box' => boxes[i],
        'titleBar' => { 'x' => boxes[i]['x'], 'y' => boxes[i]['y'], 'w' => boxes[i]['w'], 'h' => o[:row_height] },
        'columnRows' => rows }
    end
    { 'width' => width, 'height' => height, 'tables' => laid, 'edges' => edges }
  end

  def esc(s)
    s.gsub(/[&<>"]/) do |m|
      { '&' => '&amp;', '<' => '&lt;', '>' => '&gt;', '"' => '&quot;' }[m]
    end
  end

  def render_svg(geo)
    out = ["<svg xmlns=\"http://www.w3.org/2000/svg\" viewBox=\"0 0 #{geo['width']} #{geo['height']}\" " \
           "class=\"sv-root\" role=\"img\"><title>Schema diagram</title>"]
    box_of = geo['tables'].to_h { |t| [t['table']['name'], t['box']] }
    geo['edges'].each do |e|
      frm = box_of[e['fk']['fromTable']]
      next if frm.nil?

      ax = frm['x'] + r_half_up(frm['w'] / 2.0)
      out << "<path class=\"sv-edge\" d=\"#{e['path']}\"/>" \
             "<polygon class=\"sv-arrow\" points=\"#{ax - 5},#{frm['y'] - 8} #{ax + 5},#{frm['y'] - 8} #{ax},#{frm['y']}\"/>"
    end
    geo['tables'].each do |t|
      b = t['box']
      tb = t['titleBar']
      out << "<g class=\"sv-table\"><rect class=\"sv-box\" x=\"#{b['x']}\" y=\"#{b['y']}\" width=\"#{b['w']}\" height=\"#{b['h']}\" rx=\"6\"/>" \
             "<rect class=\"sv-titlebar\" x=\"#{tb['x']}\" y=\"#{tb['y']}\" width=\"#{tb['w']}\" height=\"#{tb['h']}\" rx=\"6\"/>" \
             "<text class=\"sv-title\" x=\"#{b['x'] + 8}\" y=\"#{tb['y'] + 17}\">#{esc(t['table']['name'])}</text>"
      t['table']['columns'].zip(t['columnRows']) do |c, row|
        cls = c['isPrimaryKey'] ? 'sv-pk' : 'sv-col'
        out << "<text class=\"#{cls}\" x=\"#{row['x'] + 8}\" y=\"#{row['y'] + 17}\">#{esc(c['name'])} #{esc(c['type'])}</text>"
      end
      out << '</g>'
    end
    out << '</svg>'
    out.join
  end

  def ddl_to_svg(ddl)
    schema = parse_ddl(ddl)
    { svg: render_svg(layout_schema(schema)), schema: schema }
  end
end

# Example:
#   SchemaVisualizer.ddl_to_svg(
#     '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).