spreadsheet.kt

package afield.data.table

import afield.app.server.app
import afield.document.graph.introImports
import afield.runtime.lifecycle.pipe
import afield.ui.notation.bool
import afield.ui.notation.double
import afield.ui.notation.string
import afield.ui.notation.with
import afield.ui.output.column
import afield.ui.visualization.table

import afield.app.protocol.Handled
import afield.app.protocol.computeIfAbsent
import afield.app.protocol.handle
import afield.app.protocol.payload
import afield.app.protocol.rawHandlers
import afield.app.server.SessionInformation
import afield.app.server.documents
import afield.app.server.thisSession
import afield.app.server.thisSessionInfo
import afield.audio.synth.*
import afield.document.graph.Box
import afield.document.graph.box
import afield.document.graph.doResync
import afield.document.graph.isTrue
import afield.graphics.vulkan.*
import afield.media.stream.*
import afield.media.video.*
import afield.runtime.kotlin.KotlinInterface2
import afield.runtime.lifecycle.Runloop
import afield.runtime.lifecycle.RunsAt
import afield.runtime.lifecycle.end
import afield.runtime.lifecycle.inMain
import afield.runtime.lifecycle.installHandlers
import afield.runtime.lifecycle.map
import afield.tools.docs.format
import afield.ui.output.templates.filter
import afield.ui.output.update
import afield.ui.visualization.ValueVisualizer
import afield.ui.visualization.h

import afield.geometry.path.FLine
import afield.app.diagnostics.logging.lprintln
import afield.math.units.Quantity
import afield.math.units.autoScaleExpression
import afield.math.units.formatExpression
import afield.math.units.formatQuantityDisplay
import afield.math.units.formatUnitSuperscripts
import afield.math.linalg.Vec2
import afield.math.linalg.Vec3
import afield.math.linalg.Vec4
import afield.math.linalg.v3
import eu.sirotin.kotunil.core.*
import io.ktor.websocket.send
import kotlinx.coroutines.runBlocking
import kotlinx.serialization.Serializable
import kotlinx.serialization.encodeToString
import kotlinx.serialization.json.Json
import org.json.JSONObject
import kotlin.script.experimental.api.ResultValue
import kotlin.script.experimental.api.ScriptDiagnostic
import java.net.URLEncoder
import java.nio.charset.StandardCharsets
import java.util.UUID
import java.util.concurrent.ConcurrentHashMap
import java.util.concurrent.CopyOnWriteArraySet
import kotlin.math.round
import kotlin.math.sqrt

@Serializable
data class SpreadsheetCompletionItem(
    val label: String,
    val insertText: String,
    val kind: String = "item",
    val detail: String = "",
    val documentation: String = "",
    val replaceStart: Int,
    val replaceEnd: Int,
    val cursorOffset: Int? = null,
    val selectInsertedText: Boolean = false
)

private object SpreadsheetProtocol {
    val json = Json {
        ignoreUnknownKeys = true
        encodeDefaults = true
    }

    @Serializable
    data class Request(
        val channel: String? = null,
        val reqId: String = "",
        val op: String,
        val uid: String? = null,
        val property: String = "spreadsheet",
        val watch: Boolean = true,
        val state: SpreadsheetStoredState? = null,
        val text: String? = null,
        val cursor: Int? = null
    )

    @Serializable
    data class SnapshotEnvelope(
        val reqId: String? = null,
        val ok: Boolean = true,
        val type: String = "sheet",
        val snapshot: SpreadsheetSnapshot
    )

    @Serializable
    data class CompletionEnvelope(
        val reqId: String,
        val ok: Boolean = true,
        val type: String = "completion",
        val items: List<SpreadsheetCompletionItem>
    )

    @Serializable
    data class ErrorEnvelope(
        val reqId: String? = null,
        val ok: Boolean = false,
        val error: String
    )
}

private data class SpreadsheetKey(
    val documentFilename: String,
    val uid: String,
    val property: String
)

private data class SpreadsheetClient(
    val key: Any,
    val send: (String) -> Unit
) {
    override fun equals(other: Any?): Boolean = other is SpreadsheetClient && other.key === key
    override fun hashCode(): Int = System.identityHashCode(key)
}

private data class SpreadsheetRef(
    val uid: String,
    val name: String
)

internal data class CellCoord(
    val row: Int,
    val col: Int
)

private data class ParsedCellRef(
    val row: Int,
    val col: Int,
    val endExclusive: Int
)

private data class SheetWordFragment(
    val start: Int,
    val end: Int,
    val text: String
)

private data class EvalCell(
    val input: String,
    val type: String,
    val value: Any?,
    val display: String,
    val errorDetail: String?,
    val htmlDisplay: String? = null
)

private data class EvalResult(
    val value: Any?,
    val errorDetail: String? = null
)

private data class GeneralCompletion(
    val label: String,
    val insertText: String,
    val kind: String,
    val detail: String,
    val documentation: String = "",
    val cursorOffset: Int? = null,
    val selectInsertedText: Boolean = false
)

private sealed interface CompletionContext {
    data class BoxTarget(val replaceStart: Int, val replaceEnd: Int, val query: String) : CompletionContext
    data class BoxMember(val ref: String, val replaceStart: Int, val replaceEnd: Int, val query: String) : CompletionContext
    data class SelfMember(val replaceStart: Int, val replaceEnd: Int, val query: String) : CompletionContext
    data class General(val replaceStart: Int, val replaceEnd: Int, val query: String) : CompletionContext
}

internal enum class SpreadsheetBinaryOp(val symbol: String) {
    ADD("+"),
    SUBTRACT("-"),
    MULTIPLY("*"),
    DIVIDE("/"),
    REMAINDER("%")
}

private data class SpreadsheetFLineSummary(
    val x: Double,
    val y: Double,
    val w: Double,
    val h: Double,
    val centerX: Double,
    val centerY: Double,
    val diagonal: Double
) {
    fun center2(): Vec2 = Vec2(centerX.toFloat(), centerY.toFloat())
    fun center3(): Vec3 = Vec3(centerX.toFloat(), centerY.toFloat(), 0f)
    fun bounds4(): Vec4 = Vec4(x.toFloat(), y.toFloat(), w.toFloat(), h.toFloat())
}

private val SPREADSHEET_COMPONENT_REGEX = Regex("""[-+]?(?:\d*\.\d+|\d+)(?:[eE][-+]?\d+)?""")

private fun summarizeFLine(line: FLine): SpreadsheetFLineSummary {
    val bounds = runCatching { line.fast2DBounds() }.getOrNull()
    if (bounds == null) {
        return SpreadsheetFLineSummary(
            x = 0.0,
            y = 0.0,
            w = 0.0,
            h = 0.0,
            centerX = 0.0,
            centerY = 0.0,
            diagonal = 0.0
        )
    }
    val centerX = bounds.x + bounds.w * 0.5
    val centerY = bounds.y + bounds.h * 0.5
    return SpreadsheetFLineSummary(
        x = bounds.x,
        y = bounds.y,
        w = bounds.w,
        h = bounds.h,
        centerX = centerX,
        centerY = centerY,
        diagonal = sqrt(bounds.w * bounds.w + bounds.h * bounds.h)
    )
}

private fun spreadsheetLooseNumericComponents(value: Any?): List<Double>? {
    val normalized = unwrapSpreadsheetValue(value)
    return when (normalized) {
        null -> emptyList()
        is Double -> listOf(normalized)
        is Float -> listOf(normalized.toDouble())
        is Int -> listOf(normalized.toDouble())
        is Long -> listOf(normalized.toDouble())
        is Short -> listOf(normalized.toDouble())
        is Byte -> listOf(normalized.toDouble())
        is Number -> listOf(normalized.toDouble())
        is Boolean -> listOf(if (normalized) 1.0 else 0.0)
        is String -> SPREADSHEET_COMPONENT_REGEX.findAll(normalized).mapNotNull { it.value.toDoubleOrNull() }.toList().ifEmpty { null }
        is Vec2 -> listOf(normalized.x.toDouble(), normalized.y.toDouble())
        is Vec3 -> listOf(normalized.x.toDouble(), normalized.y.toDouble(), normalized.z.toDouble())
        is Vec4 -> listOf(normalized.x.toDouble(), normalized.y.toDouble(), normalized.z.toDouble(), normalized.w.toDouble())
        is Iterable<*> -> normalized.flatMap { spreadsheetLooseNumericComponents(it).orEmpty() }
        is Array<*> -> normalized.flatMap { spreadsheetLooseNumericComponents(it).orEmpty() }
        is IntArray -> normalized.map { it.toDouble() }
        is LongArray -> normalized.map { it.toDouble() }
        is FloatArray -> normalized.map { it.toDouble() }
        is DoubleArray -> normalized.toList()
        is ShortArray -> normalized.map { it.toDouble() }
        is ByteArray -> normalized.map { it.toDouble() }
        is BooleanArray -> normalized.map { if (it) 1.0 else 0.0 }
        else -> null
    }
}

private fun spreadsheetVectorFromLooseComponents(value: Any?, size: Int): FloatArray? {
    val components = spreadsheetLooseNumericComponents(value) ?: return null
    if (components.isEmpty()) return FloatArray(size)
    if (components.size == 1) return FloatArray(size) { components.first().toFloat() }
    return FloatArray(size) { index ->
        if (index < components.size) components[index].toFloat() else 0f
    }
}

private fun spreadsheetActualVectorDimension(value: Any?): Int? = when (unwrapSpreadsheetValue(value)) {
    is Vec2 -> 2
    is Vec3 -> 3
    is Vec4 -> 4
    else -> null
}

private fun spreadsheetBinaryComponent(op: SpreadsheetBinaryOp, left: Double, right: Double): Double = when (op) {
    SpreadsheetBinaryOp.ADD -> left + right
    SpreadsheetBinaryOp.SUBTRACT -> left - right
    SpreadsheetBinaryOp.MULTIPLY -> left * right
    SpreadsheetBinaryOp.DIVIDE -> left / right
    SpreadsheetBinaryOp.REMAINDER -> left % right
}

private fun spreadsheetListElements(value: Any?): List<Any?>? {
    val normalized = unwrapSpreadsheetValue(value)
    return when (normalized) {
        is Iterable<*> -> normalized.map { unwrapSpreadsheetValue(it) }
        is Array<*> -> normalized.map { unwrapSpreadsheetValue(it) }
        is IntArray -> normalized.map { it }
        is LongArray -> normalized.map { it }
        is FloatArray -> normalized.map { it }
        is DoubleArray -> normalized.toList()
        is ShortArray -> normalized.map { it }
        is ByteArray -> normalized.map { it }
        is BooleanArray -> normalized.map { it }
        else -> null
    }
}

private fun spreadsheetBroadcastLists(
    left: List<Any?>,
    right: List<Any?>,
    op: (Any?, Any?) -> Any?
): List<Any?> {
    if (left.isEmpty() || right.isEmpty()) return emptyList()
    if (left.size == right.size) return left.indices.map { index -> unwrapSpreadsheetValue(op(left[index], right[index])) }
    if (left.size == 1) return right.map { unwrapSpreadsheetValue(op(left.first(), it)) }
    if (right.size == 1) return left.map { unwrapSpreadsheetValue(op(it, right.first())) }
    error("Cannot broadcast lists of size ${left.size} and ${right.size}")
}

private object SpreadsheetDynamicRegistry {
    private data class BinaryRule(
        val name: String,
        val matches: (Any?, Any?) -> Boolean,
        val apply: (Any?, Any?) -> Any?
    )

    private data class CoercionRule<T>(
        val name: String,
        val apply: (Any?) -> T?
    )

    private val binaryRules = SpreadsheetBinaryOp.entries.associateWith { mutableListOf<BinaryRule>() }
    private val scalarRules = mutableListOf<CoercionRule<Double>>()
    private val vec2Rules = mutableListOf<CoercionRule<Vec2>>()
    private val vec3Rules = mutableListOf<CoercionRule<Vec3>>()
    private val vec4Rules = mutableListOf<CoercionRule<Vec4>>()

    init {
        registerTracedRules()
        registerBuiltins()
    }

    // First, so a number computed from a variable cell keeps its trace through + - * /. A list or a
    // vector on either side is not a scalar operand, so it falls through to the broadcast rules, which
    // come back here per element.
    private fun registerTracedRules() {
        registerScalar("traced") { value -> (value as? SpreadsheetTraced)?.value }
        SpreadsheetBinaryOp.entries.forEach { op ->
            registerBinary(
                op,
                "traced",
                { left, right -> left is SpreadsheetTraced || right is SpreadsheetTraced },
                { left, right -> spreadsheetTracedBinary(op, left, right) ?: fallbackBinary(op, left, right) }
            )
        }
    }

    fun binary(op: SpreadsheetBinaryOp, left: Any?, right: Any?): Any? {
        val normalizedLeft = unwrapSpreadsheetValue(left)
        val normalizedRight = unwrapSpreadsheetValue(right)
        for (rule in binaryRules.getValue(op)) {
            if (!rule.matches(normalizedLeft, normalizedRight)) continue
            return unwrapSpreadsheetValue(rule.apply(normalizedLeft, normalizedRight))
        }
        return fallbackBinary(op, normalizedLeft, normalizedRight)
    }

    fun scalar(value: Any?): Double? = coerce(scalarRules, unwrapSpreadsheetValue(value))

    fun requireScalar(value: Any?): Double =
        scalar(value) ?: error("Expected a scalar-like value, found '${formatSpreadsheetValue(value)}'")

    fun vec2(value: Any?): Vec2? = coerce(vec2Rules, unwrapSpreadsheetValue(value))

    fun requireVec2(value: Any?): Vec2 =
        vec2(value) ?: error("Expected a Vec2-like value, found '${formatSpreadsheetValue(value)}'")

    fun vec3(value: Any?): Vec3? = coerce(vec3Rules, unwrapSpreadsheetValue(value))

    fun requireVec3(value: Any?): Vec3 =
        vec3(value) ?: error("Expected a Vec3-like value, found '${formatSpreadsheetValue(value)}'")

    fun vec4(value: Any?): Vec4? = coerce(vec4Rules, unwrapSpreadsheetValue(value))

    fun requireVec4(value: Any?): Vec4 =
        vec4(value) ?: error("Expected a Vec4-like value, found '${formatSpreadsheetValue(value)}'")

    fun component(value: Any?, index: Int): Any? {
        val normalized = unwrapSpreadsheetValue(value)
        return when (normalized) {
            null -> null
            is Vec2 -> listOf(normalized.x.toDouble(), normalized.y.toDouble()).getOrNull(index)
            is Vec3 -> listOf(normalized.x.toDouble(), normalized.y.toDouble(), normalized.z.toDouble()).getOrNull(index)
            is Vec4 -> listOf(normalized.x.toDouble(), normalized.y.toDouble(), normalized.z.toDouble(), normalized.w.toDouble()).getOrNull(index)
            is FLine -> {
                val summary = summarizeFLine(normalized)
                listOf(summary.x, summary.y, summary.w, summary.h).getOrNull(index)
            }

            else -> spreadsheetListElements(normalized)?.getOrNull(index)
                ?: spreadsheetLooseNumericComponents(normalized)?.getOrNull(index)
                ?: if (index == 0) scalar(normalized) else null
        }
    }

    private fun registerBuiltins() {
        // An empty cell is 0 in arithmetic, as in Excel and Sheets, so `A3 * 10` over a blank A3 is 0
        // rather than #ERR ("Expected a scalar-like value, found ''").
        registerScalar("empty") { value -> if (value == null || value == "") 0.0 else null }
        registerScalar("number") { value ->
            when (value) {
                is Double -> value
                is Float -> value.toDouble()
                is Int -> value.toDouble()
                is Long -> value.toDouble()
                is Short -> value.toDouble()
                is Byte -> value.toDouble()
                is Number -> value.toDouble()
                else -> null
            }
        }
        registerScalar("boolean") { value ->
            if (value is Boolean) {
                if (value) 1.0 else 0.0
            } else {
                null
            }
        }
        registerScalar("string-double") { value ->
            if (value is String) value.trim().toDoubleOrNull() else null
        }
        registerScalar("vector-length") { value ->
            when (value) {
                is Vec2 -> value.length().toDouble()
                is Vec3 -> value.length().toDouble()
                is Vec4 -> value.length().toDouble()
                else -> null
            }
        }
        registerScalar("fline-bounds") { value ->
            if (value is FLine) summarizeFLine(value).diagonal else null
        }
        registerScalar("numeric-components") { value ->
            val components = spreadsheetLooseNumericComponents(value) ?: return@registerScalar null
            when (components.size) {
                0 -> 0.0
                1 -> components.first()
                else -> sqrt(components.sumOf { it * it })
            }
        }

        registerVec2("vec2") { value ->
            when (value) {
                is Vec2 -> Vec2(value)
                is Vec3 -> Vec2(value.x, value.y)
                is Vec4 -> Vec2(value.x, value.y)
                is FLine -> summarizeFLine(value).center2()
                else -> spreadsheetVectorFromLooseComponents(value, 2)?.let { Vec2(it[0], it[1]) }
            }
        }
        registerVec3("vec3") { value ->
            when (value) {
                is Vec2 -> Vec3(value.x, value.y, 0f)
                is Vec3 -> Vec3(value)
                is Vec4 -> Vec3(value.x, value.y, value.z)
                is FLine -> summarizeFLine(value).center3()
                else -> spreadsheetVectorFromLooseComponents(value, 3)?.let { Vec3(it[0], it[1], it[2]) }
            }
        }
        registerVec4("vec4") { value ->
            when (value) {
                is Vec2 -> Vec4(value.x, value.y, 0f, 0f)
                is Vec3 -> Vec4(value.x, value.y, value.z, 0f)
                is Vec4 -> Vec4(value)
                is FLine -> summarizeFLine(value).bounds4()
                else -> spreadsheetVectorFromLooseComponents(value, 4)?.let { Vec4(it[0], it[1], it[2], it[3]) }
            }
        }

        SpreadsheetBinaryOp.entries.forEach { op ->
            registerBinary(
                op,
                "list-broadcast",
                { left, right -> spreadsheetListElements(left) != null || spreadsheetListElements(right) != null },
                { left, right ->
                val leftList = spreadsheetListElements(left)
                val rightList = spreadsheetListElements(right)
                when {
                    leftList != null && rightList != null -> spreadsheetBroadcastLists(leftList, rightList) { a, b -> binary(op, a, b) }
                    leftList != null -> leftList.map { unwrapSpreadsheetValue(binary(op, it, right)) }
                    rightList != null -> rightList.map { unwrapSpreadsheetValue(binary(op, left, it)) }
                    else -> emptyList<Any?>()
                }
                }
            )
        }

        registerBinary(SpreadsheetBinaryOp.ADD, "fline-left", { left, _ -> left is FLine }) { left, right ->
            applyFLineBinary(left as FLine, SpreadsheetBinaryOp.ADD, right)
        }
        registerBinary(SpreadsheetBinaryOp.SUBTRACT, "fline-left", { left, _ -> left is FLine }) { left, right ->
            applyFLineBinary(left as FLine, SpreadsheetBinaryOp.SUBTRACT, right)
        }
        registerBinary(SpreadsheetBinaryOp.MULTIPLY, "fline-left", { left, _ -> left is FLine }) { left, right ->
            applyFLineBinary(left as FLine, SpreadsheetBinaryOp.MULTIPLY, right)
        }
        registerBinary(SpreadsheetBinaryOp.DIVIDE, "fline-left", { left, _ -> left is FLine }) { left, right ->
            applyFLineBinary(left as FLine, SpreadsheetBinaryOp.DIVIDE, right)
        }
        registerBinary(SpreadsheetBinaryOp.REMAINDER, "fline-left", { left, _ -> left is FLine }) { left, right ->
            applyFLineBinary(left as FLine, SpreadsheetBinaryOp.REMAINDER, right)
        }
        registerBinary(SpreadsheetBinaryOp.ADD, "fline-right", { _, right -> right is FLine }) { left, right ->
            applyFLineBinary(right as FLine, SpreadsheetBinaryOp.ADD, left)
        }
        registerBinary(SpreadsheetBinaryOp.MULTIPLY, "fline-right", { _, right -> right is FLine }) { left, right ->
            applyFLineBinary(right as FLine, SpreadsheetBinaryOp.MULTIPLY, left)
        }

        SpreadsheetBinaryOp.entries.forEach { op ->
            registerBinary(
                op,
                "vector",
                { left, right -> spreadsheetActualVectorDimension(left) != null || spreadsheetActualVectorDimension(right) != null },
                { left, right -> applyVectorBinary(op, left, right) }
            )
        }

        registerScalar("quantity") { value ->
            when (value) {
                is Quantity -> value.displayValue
                is Expression -> value.value
                else -> null
            }
        }

        SpreadsheetBinaryOp.entries.forEach { op ->
            registerBinary(
                op,
                "quantity",
                { left, right -> left is Quantity || left is Expression || right is Quantity || right is Expression },
                { left, right -> applyQuantityBinary(op, left, right) }
            )
        }
    }

    private fun fallbackBinary(op: SpreadsheetBinaryOp, left: Any?, right: Any?): Any? = when (op) {
        SpreadsheetBinaryOp.ADD -> {
            val leftNumeric = scalar(left)
            val rightNumeric = scalar(right)
            when {
                leftNumeric != null && rightNumeric != null -> leftNumeric + rightNumeric
                left == null -> right
                right == null -> left
                else -> "${formatSpreadsheetValue(left)}${formatSpreadsheetValue(right)}"
            }
        }

        SpreadsheetBinaryOp.SUBTRACT -> requireScalar(left) - requireScalar(right)
        SpreadsheetBinaryOp.MULTIPLY -> requireScalar(left) * requireScalar(right)
        SpreadsheetBinaryOp.DIVIDE -> requireScalar(left) / requireScalar(right)
        SpreadsheetBinaryOp.REMAINDER -> requireScalar(left) % requireScalar(right)
    }

    private fun applyFLineBinary(line: FLine, op: SpreadsheetBinaryOp, other: Any?): Any {
        val vectorDimension = spreadsheetActualVectorDimension(other)
        val scalarValue = scalar(other)?.toFloat() ?: 0f
        return when (op) {
            SpreadsheetBinaryOp.ADD -> when (vectorDimension) {
                2 -> line + requireVec2(other)
                3, 4 -> line + requireVec3(other)
                else -> line + Vec3(scalarValue, scalarValue, scalarValue)
            }

            SpreadsheetBinaryOp.SUBTRACT -> when (vectorDimension) {
                2 -> line - requireVec2(other)
                3, 4 -> line - requireVec3(other)
                else -> line - Vec3(scalarValue, scalarValue, scalarValue)
            }

            SpreadsheetBinaryOp.MULTIPLY -> when (vectorDimension) {
                2 -> line * requireVec2(other)
                3, 4 -> line * requireVec3(other)
                else -> line * requireScalar(other)
            }

            SpreadsheetBinaryOp.DIVIDE -> when (vectorDimension) {
                2 -> {
                    val divisor = requireVec2(other)
                    line.transform { point -> Vec3(point.x / divisor.x, point.y / divisor.y, point.z) }
                }

                3, 4 -> {
                    val divisor = requireVec3(other)
                    line.transform { point -> Vec3(point.x / divisor.x, point.y / divisor.y, point.z / divisor.z) }
                }

                else -> {
                    val divisor = requireScalar(other).toFloat()
                    line.transform { point -> Vec3(point.x / divisor, point.y / divisor, point.z / divisor) }
                }
            }

            SpreadsheetBinaryOp.REMAINDER -> when (vectorDimension) {
                2 -> {
                    val divisor = requireVec2(other)
                    line.transform { point -> Vec3(point.x % divisor.x, point.y % divisor.y, point.z) }
                }

                3, 4 -> {
                    val divisor = requireVec3(other)
                    line.transform { point -> Vec3(point.x % divisor.x, point.y % divisor.y, point.z % divisor.z) }
                }

                else -> {
                    val divisor = requireScalar(other).toFloat()
                    line.transform { point -> Vec3(point.x % divisor, point.y % divisor, point.z % divisor) }
                }
            }
        }
    }

    private fun toExpression(value: Any?): Expression? = when (value) {
        is Quantity -> value.expression
        is Expression -> value
        is Number -> Expression(value.toDouble(), eu.sirotin.kotunil.core.Dimensions(emptySet()))
        else -> {
            val d = scalar(value)
            if (d != null) Expression(d, eu.sirotin.kotunil.core.Dimensions(emptySet())) else null
        }
    }

    private fun quantityDisplayUnit(value: Any?): Expression? = when (value) {
        is Quantity -> value.displayUnit
        else -> null
    }

    private fun isPlainNumber(value: Any?): Boolean = value !is Quantity && value !is Expression

    private fun scaledDisplayUnit(left: Any?, right: Any?): Expression? = when {
        isPlainNumber(right) -> quantityDisplayUnit(left)
        isPlainNumber(left) -> quantityDisplayUnit(right)
        else -> null
    }

    private fun applyQuantityBinary(op: SpreadsheetBinaryOp, left: Any?, right: Any?): Any? {
        val leftExpr = toExpression(left) ?: error("Cannot convert '${formatSpreadsheetValue(left)}' to a unit expression")
        val rightExpr = toExpression(right) ?: error("Cannot convert '${formatSpreadsheetValue(right)}' to a unit expression")
        return when (op) {
            SpreadsheetBinaryOp.ADD -> Quantity(leftExpr + rightExpr, quantityDisplayUnit(left) ?: quantityDisplayUnit(right))
            SpreadsheetBinaryOp.SUBTRACT -> Quantity(leftExpr - rightExpr, quantityDisplayUnit(left) ?: quantityDisplayUnit(right))
            // Scaling by a plain number keeps the unit it was written in: `(0.21 | m) * 3` is 0.63 m,
            // not the auto-scaled 6.3 dm.
            SpreadsheetBinaryOp.MULTIPLY -> Quantity(leftExpr * rightExpr, scaledDisplayUnit(left, right))
            SpreadsheetBinaryOp.DIVIDE -> Quantity(leftExpr / rightExpr, if (isPlainNumber(right)) quantityDisplayUnit(left) else null)
            SpreadsheetBinaryOp.REMAINDER -> Quantity(leftExpr % rightExpr)
        }
    }

    private fun applyVectorBinary(op: SpreadsheetBinaryOp, left: Any?, right: Any?): Any {
        val dimension = maxOf(spreadsheetActualVectorDimension(left) ?: 0, spreadsheetActualVectorDimension(right) ?: 0)
        return when (dimension) {
            2 -> {
                val a = requireVec2(left)
                val b = requireVec2(right)
                Vec2(
                    spreadsheetBinaryComponent(op, a.x.toDouble(), b.x.toDouble()).toFloat(),
                    spreadsheetBinaryComponent(op, a.y.toDouble(), b.y.toDouble()).toFloat()
                )
            }

            3 -> {
                val a = requireVec3(left)
                val b = requireVec3(right)
                Vec3(
                    spreadsheetBinaryComponent(op, a.x.toDouble(), b.x.toDouble()).toFloat(),
                    spreadsheetBinaryComponent(op, a.y.toDouble(), b.y.toDouble()).toFloat(),
                    spreadsheetBinaryComponent(op, a.z.toDouble(), b.z.toDouble()).toFloat()
                )
            }

            else -> {
                val a = requireVec4(left)
                val b = requireVec4(right)
                Vec4(
                    spreadsheetBinaryComponent(op, a.x.toDouble(), b.x.toDouble()).toFloat(),
                    spreadsheetBinaryComponent(op, a.y.toDouble(), b.y.toDouble()).toFloat(),
                    spreadsheetBinaryComponent(op, a.z.toDouble(), b.z.toDouble()).toFloat(),
                    spreadsheetBinaryComponent(op, a.w.toDouble(), b.w.toDouble()).toFloat()
                )
            }
        }
    }

    private fun registerBinary(
        op: SpreadsheetBinaryOp,
        name: String,
        matches: (Any?, Any?) -> Boolean,
        apply: (Any?, Any?) -> Any?
    ) {
        binaryRules.getValue(op) += BinaryRule(name, matches, apply)
    }

    private fun registerScalar(name: String, apply: (Any?) -> Double?) {
        scalarRules += CoercionRule(name, apply)
    }

    private fun registerVec2(name: String, apply: (Any?) -> Vec2?) {
        vec2Rules += CoercionRule(name, apply)
    }

    private fun registerVec3(name: String, apply: (Any?) -> Vec3?) {
        vec3Rules += CoercionRule(name, apply)
    }

    private fun registerVec4(name: String, apply: (Any?) -> Vec4?) {
        vec4Rules += CoercionRule(name, apply)
    }

    private fun <T> coerce(rules: List<CoercionRule<T>>, value: Any?): T? {
        for (rule in rules) {
            val result = runCatching { rule.apply(value) }.getOrNull()
            if (result != null) return result
        }
        return null
    }
}

    private fun resolveBoxReference(ref: String): Box? {
        val trimmed = ref.trim()
        if (trimmed.isEmpty()) return null
        if (trimmed == "self") return currentBox
        info.boxes[trimmed]?.let { return it }
        if (trimmed == "root") return info.root
        val matches = info.boxes.values.filter { it.name.trim() == trimmed }
        return if (matches.size == 1) matches.first() else null
    }
}

private fun bindSpreadsheetBox(runtime: SpreadsheetRuntime, target: Box): SpreadsheetBoxBinding =
    SpreadsheetBoxBinding(
        touchMember = { member, access -> runtime.touchBoxDependency(target, member, access) },
        memberNamesProvider = { runtime.memberNamesFor(target) },
        target = target
    )

private class SpreadsheetEvaluator(private val cachePrefix: String) {
    private val lock = Any()
    // One compiled formula *function* per template. A snippet is evaluated once, to make the function;
    // each recompute then calls it. Until 2026-10-26 each recompute re-evaluated the snippet, and the
    // K2 REPL handed back the previous result when the same snippet was evaluated twice in a row — so a
    // sheet with one formula never followed the box it read, and a template shared by a filled-down
    // column would have shown its first row everywhere (SpreadsheetTest pins both).
    private val compiled = ConcurrentHashMap<String, () -> Any?>()
    // K2, like box code. Formulas moved off the legacy K1 engine on 2026-09-28, with no change a sheet
    // can see (examples.spreadsheet.formulas pins that); K1's REPL compiler is an error-level
    // deprecation from Kotlin 2.5.
    private val compiler = KotlinInterface2(filePrefix = cachePrefix)
    private var bootstrapped = false

    // The K2 REPL defines its `ReplState` class with the first snippet it evaluates. Until 2026-10-26
    // that was the sheet's first formula, so a sheet whose first formula did not compile never got
    // one, and every later formula on it failed with `NoClassDefFoundError: ReplState` until Field
    // restarted. Box code bootstraps its engine with the intro imports; the sheet now does the same.
    private fun bootstrapLocked() {
        if (bootstrapped) return
        bootstrapped = true
        runBlocking {
            compiler.bootstrap(
                "${introImports.joinToString("\n")}\nUnit",
                implicits = emptyList(),
                onCompileError = { lprintln("warning,spreadsheet", "formula engine bootstrap did not compile: ${it.firstOrNull()?.message}") },
                onEvalError = {},
                onEvalException = { lprintln("warning,spreadsheet", "formula engine bootstrap failed: ${it.message}") },
            )
        }
    }

    fun evaluate(expression: String, runtime: SpreadsheetRuntime): EvalResult {
        val source = buildSource(expression)
        var failure: String? = null
        val formula = synchronized(lock) {
            bootstrapLocked()
            compiled[source] ?: compileLocked(source) { failure = it }?.also { compiled[source] = it }
        } ?: return EvalResult(value = null, errorDetail = failure ?: "Formula compilation failed")

        return withSpreadsheetRuntime(runtime) {
            try {
                when (val value = formula()) {
                    is SpreadsheetEvalHelpers.Failure -> EvalResult(null, value.error.message ?: value.error.toString())
                    // Stored unwrapped, so `getSpreadsheetCellValue("C3") as? FLine` finds the line
                    // a formula like `C1 + B1` made, not the `SpreadsheetScalar` wrapped around it.
                    else -> EvalResult(unwrapSpreadsheetValue(value))
                }
            } catch (t: Throwable) {
                EvalResult(null, t.message ?: t.toString())
            }
        }
    }

    @Suppress("UNCHECKED_CAST")
    private fun compileLocked(source: String, onFailure: (String) -> Unit): (() -> Any?)? {
        var compileError: String? = null
        val snippet = runBlocking {
            compiler.compileSnippet(source, implicits = emptyList(), onCompileError = { diagnostics ->
                compileError = diagnostics.firstOrNull {
                    it.severity.ordinal >= ScriptDiagnostic.Severity.ERROR.ordinal
                }?.message
            })
        }
        if (snippet == null) {
            onFailure(compileError?.let { "Formula compilation failed: $it" } ?: "Formula compilation failed")
            return null
        }
        return when (val result = snippet.evalAndWait(updateContext = false, implicits = emptyList(), onEvalError = {}, onEvalException = {})) {
            is ResultValue.Value -> (result.value as? () -> Any?) ?: run { onFailure("Formula did not compile to a function"); null }
            is ResultValue.Error -> { onFailure(result.error.message ?: result.error.toString()); null }
            else -> { onFailure("Formula was not evaluated"); null }
        }
    }

    private fun buildSource(expression: String): String = """
        ${introImports.joinToString("\n")}
        import kotlin.math.*
        import eu.sirotin.kotunil.core.*
        import eu.sirotin.kotunil.base.*
        import eu.sirotin.kotunil.derived.*
        import eu.sirotin.kotunil.specialunits.*
        run {
            fun cell(ref: String) = afield.data.table.SpreadsheetEvalHelpers.cell(ref)
            fun range(ref: String) = afield.data.table.SpreadsheetEvalHelpers.range(ref)
            fun box(ref: String) = afield.data.table.SpreadsheetEvalHelpers.box(ref)
            fun __ref(index: Int) = afield.data.table.SpreadsheetEvalHelpers.ref(index)
            fun sum(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.total(values.toList())
            fun product(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.product(values.toList())
            fun avg(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.avg(values.toList())
            fun mean(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.mean(values.toList())
            fun min(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.min(values.toList())
            fun max(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.max(values.toList())
            fun count(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.count(values.toList())
            fun nonEmpty(vararg values: Any?) = afield.data.table.SpreadsheetEvalHelpers.nonEmpty(values.toList())
            fun num(value: Any?) = afield.data.table.SpreadsheetEvalHelpers.num(value)
            fun free(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.free(x)
            fun sqrt(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.sqrt(x)
            fun abs(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.abs(x)
            fun round(x: Any?) = kotlin.math.round(num(x))
            fun floor(x: Any?) = kotlin.math.floor(num(x))
            fun ceil(x: Any?) = kotlin.math.ceil(num(x))
            fun exp(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.exp(x)
            fun ln(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.ln(x)
            fun log10(x: Any?) = kotlin.math.log10(num(x))
            fun sin(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.sin(x)
            fun cos(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.cos(x)
            fun tan(x: Any?) = afield.data.table.SpreadsheetEvalHelpers.tan(x)
            fun atan2(y: Any?, x: Any?) = afield.data.table.SpreadsheetEvalHelpers.atan2(y, x)
            fun pow(x: Any?, p: Any?) = afield.data.table.SpreadsheetEvalHelpers.pow(x, p)
            infix fun Number.eq(other: Any?) = afield.data.table.spreadsheetValue(this).eq(other)
            infix fun Number.le(other: Any?) = afield.data.table.spreadsheetValue(this).le(other)
            infix fun Number.ge(other: Any?) = afield.data.table.spreadsheetValue(this).ge(other)
            val formula: () -> Any? = {
                val self = afield.data.table.SpreadsheetEvalHelpers.self
                try {
                    $expression
                } catch (t: Throwable) {
                    afield.data.table.SpreadsheetEvalHelpers.failed(t)
                }
            }
            formula
        }
    """.trimIndent()
}

private class SpreadsheetDocument(
    private val key: SpreadsheetKey
) {
    private val evaluator = SpreadsheetEvaluator(
        cachePrefix = "sheet_${safeForId(key.uid)}_${safeForId(key.property)}"
    )
    private val lock = Any()
    private var storedState: SpreadsheetStoredState = normalizeStoredState(null)
    private var persistedJson: String? = null
    private var version: Long = 0
    private var cachedSnapshot: SpreadsheetSnapshot? = null
    private var cachedEvalCells: Map<String, EvalCell> = emptyMap()
    private var lastExternalBoxDeps: Set<SpreadsheetBoxDependency> = emptySet()
    private var lastObservedExternalValues: Map<SpreadsheetBoxDependency, String> = emptyMap()
    val subscribers: MutableSet<SpreadsheetClient> = CopyOnWriteArraySet()

    fun hasSubscribers(): Boolean = subscribers.isNotEmpty()

    fun removeClient(clientKey: Any) {
        val matches = subscribers.filter { it.key === clientKey }.toSet()
        if (matches.isNotEmpty()) {
            subscribers.removeAll(matches)
        }
    }

    fun snapshot(box: Box, info: SessionInformation, forceReload: Boolean = false, forceRecompute: Boolean = false): SpreadsheetSnapshot {
        synchronized(lock) {
            reloadFromBox(box, forceReload)
            if (cachedSnapshot == null || forceRecompute) {
                if (forceRecompute) version += 1
                cachedSnapshot = computeSnapshot(box, info)
            }
            return cachedSnapshot!!
        }
    }

    fun cellValue(
        box: Box,
        info: SessionInformation,
        ref: String,
        forceReload: Boolean = false,
        forceRecompute: Boolean = true
    ): Any? {
        synchronized(lock) {
            reloadFromBox(box, forceReload)
            val coord = cellRefToCoord(ref.trim()) ?: error("Unknown spreadsheet cell reference '$ref'")
            if (coord.row !in 0 until storedState.rows || coord.col !in 0 until storedState.cols) {
                error("Cell ${cellLabel(coord.row, coord.col)} is outside the sheet")
            }
            if (cachedSnapshot == null || forceRecompute) {
                if (forceRecompute) version += 1
                cachedSnapshot = computeSnapshot(box, info)
            }
            // A variable cell, or a formula over one, reads as its number from outside the sheet.
            return when (val value = cachedEvalCells["${coord.row}:${coord.col}"]?.value) {
                null -> ""
                is SpreadsheetTraced -> value.value
                else -> value
            }
        }
    }

    fun update(box: Box, info: SessionInformation, nextState: SpreadsheetStoredState): SpreadsheetSnapshot {
        synchronized(lock) {
            // The editor sends cells and sizes only; keep what the last solve recorded.
            val carried = if (nextState.solved.isEmpty()) nextState.copy(solved = storedState.solved) else nextState
            storedState = writeStoredSpreadsheetState(box, key.property, carried)
            persistedJson = encodeStoredSpreadsheetState(storedState)
            version += 1
            cachedSnapshot = computeSnapshot(box, info)
            return cachedSnapshot!!
        }
    }

    fun pollExternalDependencies(box: Box, info: SessionInformation): SpreadsheetSnapshot? {
        synchronized(lock) {
            if (lastExternalBoxDeps.isEmpty()) {
                lastObservedExternalValues = emptyMap()
                return null
            }
            val observedNow = captureExternalDependencyFingerprints(info)
            if (observedNow == lastObservedExternalValues) return null
            version += 1
            cachedSnapshot = computeSnapshot(box, info)
            return cachedSnapshot!!
        }
    }

    private fun reloadFromBox(box: Box, forceReload: Boolean) {
        val raw = box.getPropertyRaw(key.property, null) as? String
        if (!forceReload && raw == persistedJson) return
        persistedJson = raw
        storedState = decodeStoredState(raw)
        cachedSnapshot = null
        cachedEvalCells = emptyMap()
        version += 1
    }

    private fun computeSnapshot(box: Box, info: SessionInformation, allowSolve: Boolean = true, solved: SheetSolveOutcome? = null): SpreadsheetSnapshot {
        val cellInputs = mutableMapOf<String, String>()
        storedState.cells.forEach { cell ->
            if (cell.row in 0 until storedState.rows && cell.col in 0 until storedState.cols) {
                cellInputs["${cell.row}:${cell.col}"] = cell.input
            }
        }
        // Typing a plain number over a variable cell edits the variable rather than replacing it.
        if (allowSolve) {
            val retyped = cellInputs.filter { (k, input) ->
                storedState.solved[k] != null && input.trim().toDoubleOrNull() != null
            }
            if (retyped.isNotEmpty()) {
                retyped.forEach { (k, input) -> cellInputs[k] = freeCellInput(input.trim().toDouble()) }
                storedState = writeStoredSpreadsheetState(box, key.property, storedState.copy(cells = storedState.cells.map { c ->
                    cellInputs["${c.row}:${c.col}"]?.let { c.copy(input = it) } ?: c
                }))
                persistedJson = encodeStoredSpreadsheetState(storedState)
            }
        }
        val constraints = SheetConstraintCollector()

        val memo = mutableMapOf<String, EvalCell>()
        // The chain of formulas being evaluated, innermost last, so a cycle can name every cell in it.
        val visiting = mutableListOf<String>()
        val cyclic = mutableSetOf<String>()
        val externalDeps = linkedSetOf<SpreadsheetBoxDependency>()

        fun labelOf(keyString: String): String =
            keyString.split(':').let { cellLabel(it[0].toInt(), it[1].toInt()) }

        fun evaluateCell(row: Int, col: Int): EvalCell {
            val keyString = "$row:$col"
            memo[keyString]?.let { return it }
            if (keyString in visiting) {
                // Until 2026-10-26 this returned an empty value and the cell that closed the loop then
                // overwrote its own #CYCLE, so a cycle showed as blank cells. Now every cell on the
                // loop is marked and the reference throws, so readers outside it see an error.
                val loop = visiting.subList(visiting.indexOf(keyString), visiting.size)
                cyclic += loop
                throw IllegalStateException("Circular reference: ${(loop + keyString).joinToString(" -> ") { labelOf(it) }}")
            }

            val input = cellInputs[keyString].orEmpty()
            val type = classifyCellInput(input)
            if (type == "empty") {
                return EvalCell(input, type, "", "", null).also { memo[keyString] = it }
            }
            if (type == "text") {
                return EvalCell(input, type, input, input, null).also { memo[keyString] = it }
            }
            if (type == "number") {
                val number = input.trim().toDoubleOrNull()
                val display = formatSpreadsheetValue(number)
                return EvalCell(input, type, number, display, null).also { memo[keyString] = it }
            }

            visiting += keyString
            return try {
                val expr = input.removePrefix("=").trim()
                if (expr.isEmpty()) {
                    EvalCell(input, "formula", "", "", null)
                } else {
                    val refs = mutableListOf<String>()
                    val rewritten = rewriteSpreadsheetFormula(expr, refs)
                    val runtime = SpreadsheetRuntime(info, box, refs, keyString, input, constraints) { coord ->
                        if (coord.row !in 0 until storedState.rows || coord.col !in 0 until storedState.cols) {
                            error("Cell ${cellLabel(coord.row, coord.col)} is outside the sheet")
                        }
                        val referenced = evaluateCell(coord.row, coord.col)
                        // A formula that reads a failed cell fails too, as in Excel, instead of reading
                        // the failure as empty (which made `A1 + 1` show 1 when A1 was #ERR).
                        if (referenced.errorDetail != null) {
                            error("${cellLabel(coord.row, coord.col)} is ${referenced.display}: ${referenced.errorDetail}")
                        }
                        referenced.value
                    }
                    val evalResult = evaluator.evaluate(rewritten, runtime)
                    externalDeps += runtime.boxDependencies()
                    if (keyString in cyclic) {
                        EvalCell(input, "formula", null, "#CYCLE", evalResult.errorDetail ?: "Circular reference at ${cellLabel(row, col)}")
                    } else if (evalResult.errorDetail != null) {
                        EvalCell(input, "formula", null, "#ERR", evalResult.errorDetail)
                    } else {
                        EvalCell(
                            input = input,
                            type = when {
                                constraints.isVariable(keyString) -> "variable"
                                evalResult.value is SpreadsheetAssertion -> "assertion"
                                else -> "formula"
                            },
                            value = evalResult.value,
                            display = formatSpreadsheetValue(evalResult.value),
                            errorDetail = null,
                            htmlDisplay = spreadsheetHtmlDisplay(evalResult.value)
                        )
                    }
                }
            } catch (t: Throwable) {
                EvalCell(input, "formula", null, if (keyString in cyclic) "#CYCLE" else "#ERR", t.message ?: t.toString())
            }.also {
                memo[keyString] = it
                visiting.removeAt(visiting.lastIndex)
            }
        }

        for (row in 0 until storedState.rows) {
            for (col in 0 until storedState.cols) {
                val maybeInput = cellInputs["$row:$col"]
                if (!maybeInput.isNullOrBlank()) {
                    evaluateCell(row, col)
                }
            }
        }

        if (allowSolve) {
            // Solved values are written into the variable cells; evaluate again so every cell downstream,
            // and every assertion's verdict, reads them.
            solveConstraints(box, constraints)?.let { return computeSnapshot(box, info, allowSolve = false, solved = it) }
        }

        lastExternalBoxDeps = externalDeps.toSet()
        lastObservedExternalValues = captureExternalDependencyFingerprints(info)
        cachedEvalCells = memo.toMap()

        val cells = memo.entries
            .mapNotNull { (keyString, cell) ->
                val parts = keyString.split(':', limit = 2)
                val row = parts.getOrNull(0)?.toIntOrNull() ?: return@mapNotNull null
                val col = parts.getOrNull(1)?.toIntOrNull() ?: return@mapNotNull null
                if (cell.type == "empty" && cell.display.isEmpty()) return@mapNotNull null
                SpreadsheetCellSnapshot(
                    row = row,
                    col = col,
                    input = cell.input,
                    type = cell.type,
                    display = cell.display,
                    errorDetail = cell.errorDetail,
                    htmlDisplay = cell.htmlDisplay
                )
            }
            .sortedWith(compareBy<SpreadsheetCellSnapshot> { it.row }.thenBy { it.col })

        return SpreadsheetSnapshot(
            uid = key.uid,
            property = key.property,
            rows = storedState.rows,
            cols = storedState.cols,
            colWidths = storedState.colWidths.map { it.toInt() },
            rowHeights = storedState.rowHeights.map { it.toInt() },
            cells = cells,
            version = version,
            solve = solved?.let { outcome ->
                if (!outcome.ok) "solve failed: ${outcome.note ?: "unknown"}"
                else outcome.summary(
                    constraints.variables.size,
                    constraints.assertions.size,
                    constraints.assertions.count { !it.second.satisfied },
                )
            }
        )
    }

    /**
     * Solve the sheet's assertions for its variable cells and write the answers back into their
     * `=free(...)` formulas. Null when there is nothing to solve (no variable is read by an assertion);
     * otherwise the outcome, which the re-evaluation turns into the sheet header's one-line summary.
     */
    private fun solveConstraints(box: Box, constraints: SheetConstraintCollector): SheetSolveOutcome? {
        val variables = constraints.variables
        val assertions = constraints.assertions.map { it.second }
        val inputs = storedState.cells.associate { "${it.row}:${it.col}" to it.input }
        val currentVariableInputs = variables.associate { it.cellKey to inputs[it.cellKey].orEmpty() }
        if (variables.isEmpty() || assertions.none { it.traced }) {
            recordSolved(box, currentVariableInputs, emptyMap())
            return null
        }
        val previouslySolved = storedState.solved
        val edited = variables.indices.filter { i ->
            val k = variables[i].cellKey
            previouslySolved.isNotEmpty() && previouslySolved[k] != currentVariableInputs[k]
        }.toSet()
        val outcome = SheetConstraintSolver.solve(variables.map { it.previous }.toDoubleArray(), edited, assertions)
        if (!outcome.ok) {
            recordSolved(box, currentVariableInputs, emptyMap())
            return outcome
        }
        val rewritten = mutableMapOf<String, String>()
        variables.forEachIndexed { i, v ->
            val next = freeCellInput(outcome.values[i])
            val current = currentVariableInputs[v.cellKey].orEmpty()
            val currentValue = FREE_CELL_PATTERN.find(current)?.groupValues?.get(1)?.toDoubleOrNull()
            if (currentValue == null || freeLiteral(currentValue) != freeLiteral(outcome.values[i])) rewritten[v.cellKey] = next
        }
        recordSolved(box, currentVariableInputs, rewritten)
        return outcome
    }

    /** Write solved values into their cells and record the variables' inputs as solved; no write if nothing changed. */
    private fun recordSolved(box: Box, variableInputs: Map<String, String>, rewritten: Map<String, String>) {
        val solved = variableInputs + rewritten
        if (rewritten.isEmpty() && solved == storedState.solved) return
        storedState = writeStoredSpreadsheetState(box, key.property, storedState.copy(
            cells = storedState.cells.map { c -> rewritten["${c.row}:${c.col}"]?.let { c.copy(input = it) } ?: c },
            solved = solved,
        ))
        persistedJson = encodeStoredSpreadsheetState(storedState)
    }

    private fun captureExternalDependencyFingerprints(info: SessionInformation): Map<SpreadsheetBoxDependency, String> =
        lastExternalBoxDeps.associateWith { dependency ->
            val target = when (dependency.boxId) {
                "root" -> info.root
                else -> info.boxes[dependency.boxId]
            }
            externalDependencyFingerprint(target, dependency)
        }

    private fun externalDependencyFingerprint(target: Box?, dependency: SpreadsheetBoxDependency): String {
        if (target == null) {
            return "missing:${dependency.boxId}:${dependency.member}:${dependency.access}"
        }
        if (target.getPropertyRaw("_tombstone").isTrue()) {
            return "tombstone:${dependency.boxId}:${dependency.member}:${dependency.access}"
        }
        val value = when (dependency.access) {
            SpreadsheetBoxMemberAccess.GET -> target.ki.context.vars[dependency.member]?.value
                ?: target.getPropertyRaw(dependency.member, null)

            SpreadsheetBoxMemberAccess.PROP -> target.getPropertyRaw(dependency.member, null)
            SpreadsheetBoxMemberAccess.VARIABLE -> target.ki.context.vars[dependency.member]?.value
        }
        return fingerprintSpreadsheetDependencyValue(value)
    }
}

private object SpreadsheetService {
    private val documents = ConcurrentHashMap<SpreadsheetKey, SpreadsheetDocument>()

    fun detachClient(clientKey: Any) {
        documents.values.forEach { it.removeClient(clientKey) }
    }

    fun currentState(box: Box, property: String): SpreadsheetStoredState =
        decodeStoredState(box.getPropertyRaw(normalizedSpreadsheetProperty(property), null) as? String)

    fun snapshot(
        box: Box,
        info: SessionInformation,
        property: String,
        forceReload: Boolean = false,
        forceRecompute: Boolean = true
    ): SpreadsheetSnapshot {
        val normalizedProperty = normalizedSpreadsheetProperty(property)
        registerSpreadsheetProperty(normalizedProperty)
        val key = SpreadsheetKey(info.filename, box.id, normalizedProperty)
        val document = documents.computeIfAbsent(key) { SpreadsheetDocument(key) }
        return document.snapshot(box, info, forceReload = forceReload, forceRecompute = forceRecompute)
    }

    fun cellValue(
        box: Box,
        info: SessionInformation,
        property: String,
        ref: String,
        forceReload: Boolean = false,
        forceRecompute: Boolean = true
    ): Any? {
        val normalizedProperty = normalizedSpreadsheetProperty(property)
        registerSpreadsheetProperty(normalizedProperty)
        val key = SpreadsheetKey(info.filename, box.id, normalizedProperty)
        val document = documents.computeIfAbsent(key) { SpreadsheetDocument(key) }
        return document.cellValue(box, info, ref, forceReload = forceReload, forceRecompute = forceRecompute)
    }

    fun applyProgrammaticState(
        box: Box,
        info: SessionInformation,
        property: String,
        nextState: SpreadsheetStoredState,
        broadcast: Boolean = true
    ): SpreadsheetStoredState {
        val normalizedProperty = normalizedSpreadsheetProperty(property)
        registerSpreadsheetProperty(normalizedProperty)
        val normalizedState = normalizeStoredState(nextState)
        val key = SpreadsheetKey(info.filename, box.id, normalizedProperty)
        val document = documents[key]
        if (document == null) {
            return writeStoredSpreadsheetState(box, normalizedProperty, normalizedState)
        }
        val snapshot = document.update(box, info, normalizedState)
        if (broadcast) {
            broadcast(document, snapshot, key)
        }
        return normalizedState
    }

    fun handle(text: String, info: SessionInformation?, clientKey: Any, push: (String) -> Unit): String {
        val req = try {
            SpreadsheetProtocol.json.decodeFromString(SpreadsheetProtocol.Request.serializer(), text)
        } catch (t: Throwable) {
            lprintln("warning,spreadsheet,ws", "[sheet-handle] failed to decode request: ${t.message}")
            return ""
        }
        if (req.channel != null && req.channel != SPREADSHEET_CHANNEL) return ""

        lprintln("debug,spreadsheet,ws", "[sheet-handle] op=${req.op} reqId=${req.reqId} uid=${req.uid} property=${req.property}")

        val sessionInfo = info
            ?: return error(req.reqId, "Spreadsheet session is not attached yet. Wait for hello to complete and retry.")
        val uid = req.uid?.trim().orEmpty()
        if (uid.isEmpty()) {
            return error(req.reqId, "Missing spreadsheet box uid")
        }

        val normalizedProperty = normalizedSpreadsheetProperty(req.property)
        registerSpreadsheetProperty(normalizedProperty)

        val box = runCatching { box(uid, createIfMissing = false, root = sessionInfo) }
            .getOrElse { return error(req.reqId, "Unknown spreadsheet box '$uid'") }
        val key = SpreadsheetKey(sessionInfo.filename, uid, normalizedProperty)
        val document = documents.computeIfAbsent(key) { SpreadsheetDocument(key) }
        val client = SpreadsheetClient(clientKey, send = push)

        return try {
            val startMs = System.currentTimeMillis()
            val result = when (req.op) {
                "open" -> {
                    if (req.watch) document.subscribers += client
                    val snapshot = document.snapshot(box, sessionInfo, forceRecompute = true)
                    SpreadsheetProtocol.json.encodeToString(
                        SpreadsheetProtocol.SnapshotEnvelope.serializer(),
                        SpreadsheetProtocol.SnapshotEnvelope(reqId = req.reqId, snapshot = snapshot)
                    )
                }

                "update" -> {
                    if (req.watch) document.subscribers += client
                    val nextState = req.state ?: normalizeStoredState(null)
                    val snapshot = document.update(box, sessionInfo, nextState)
                    broadcast(document, snapshot, key)
                    SpreadsheetProtocol.json.encodeToString(
                        SpreadsheetProtocol.SnapshotEnvelope.serializer(),
                        SpreadsheetProtocol.SnapshotEnvelope(reqId = req.reqId, snapshot = snapshot)
                    )
                }

                "refresh" -> {
                    if (req.watch) document.subscribers += client
                    val snapshot = document.snapshot(box, sessionInfo, forceReload = true, forceRecompute = true)
                    broadcast(document, snapshot, key)
                    SpreadsheetProtocol.json.encodeToString(
                        SpreadsheetProtocol.SnapshotEnvelope.serializer(),
                        SpreadsheetProtocol.SnapshotEnvelope(reqId = req.reqId, snapshot = snapshot)
                    )
                }

                "complete" -> {
                    val formula = req.text ?: ""
                    val cursor = (req.cursor ?: formula.length).coerceIn(0, formula.length)
                    val items = completionsFor(box, sessionInfo, formula, cursor)
                    SpreadsheetProtocol.json.encodeToString(
                        SpreadsheetProtocol.CompletionEnvelope.serializer(),
                        SpreadsheetProtocol.CompletionEnvelope(reqId = req.reqId, items = items)
                    )
                }

                "unsubscribe" -> {
                    document.subscribers -= client
                    SpreadsheetProtocol.json.encodeToString(
                        SpreadsheetProtocol.CompletionEnvelope.serializer(),
                        SpreadsheetProtocol.CompletionEnvelope(reqId = req.reqId, items = emptyList())
                    )
                }

                else -> error(req.reqId, "Unknown spreadsheet op '${req.op}'")
            }
            val elapsed = System.currentTimeMillis() - startMs
            lprintln("debug,spreadsheet,ws", "[sheet-handle] op=${req.op} reqId=${req.reqId} completed in ${elapsed}ms, response=${result.length} chars")
            result
        } catch (t: Throwable) {
            lprintln("error,spreadsheet,ws", "[sheet-handle] op=${req.op} reqId=${req.reqId} threw ${t::class.simpleName}: ${t.message}")
            error(req.reqId, t.message ?: t.toString())
        }
    }

    fun pollLiveSheets() {
        val broadcasts = mutableListOf<Triple<SpreadsheetKey, SpreadsheetDocument, SpreadsheetSnapshot>>()
        val removals = mutableListOf<SpreadsheetKey>()

        documents.forEach { (key, document) ->
            if (!document.hasSubscribers()) return@forEach

            val info = afield.app.server.documents[key.documentFilename]
            if (info == null) {
                removals += key
                return@forEach
            }

            val box = when (key.uid) {
                "root" -> info.root
                else -> info.boxes[key.uid]
            }
            if (box == null || box.getPropertyRaw("_tombstone").isTrue()) {
                removals += key
                return@forEach
            }

            val snapshot = runCatching { document.pollExternalDependencies(box, info) }
                .onFailure { lprintln("warning,spreadsheet", "live spreadsheet polling failed for ${key.uid}/${key.property}: ${it.message}") }
                .getOrNull()
                ?: return@forEach

            broadcasts += Triple(key, document, snapshot)
        }

        broadcasts.forEach { (key, document, snapshot) ->
            broadcast(document, snapshot, key)
        }
        removals.forEach { key ->
            documents.remove(key)
            lprintln("info,spreadsheet", "stopped live polling for ${key.uid}/${key.property}")
        }
    }

    private fun broadcast(document: SpreadsheetDocument, snapshot: SpreadsheetSnapshot, key: SpreadsheetKey) {
        val payload = SpreadsheetProtocol.json.encodeToString(
            SpreadsheetProtocol.SnapshotEnvelope.serializer(),
            SpreadsheetProtocol.SnapshotEnvelope(snapshot = snapshot)
        )
        val deadClients = mutableListOf<SpreadsheetClient>()
        document.subscribers.forEach { client ->
            runCatching { client.send(payload) }
                .onFailure { deadClients += client }
        }
        if (deadClients.isNotEmpty()) {
            document.subscribers.removeAll(deadClients.toSet())
        }
        lprintln(
            "info,spreadsheet",
            "broadcast sheet ${key.uid}/${key.property} v${snapshot.version} to ${document.subscribers.size} subscribers"
        )
    }

    private fun error(reqId: String?, message: String): String =
        SpreadsheetProtocol.json.encodeToString(
            SpreadsheetProtocol.ErrorEnvelope.serializer(),
            SpreadsheetProtocol.ErrorEnvelope(reqId = reqId, error = message)
        )

    private fun completionsFor(box: Box, info: SessionInformation, formula: String, cursor: Int): List<SpreadsheetCompletionItem> {
        val context = completionContextFor(formula, cursor)
        return when (context) {
            is CompletionContext.BoxTarget -> {
                val suggestions = availableBoxRefs(info)
                    .filter { matchesCompletionQuery(it.name, context.query) || matchesCompletionQuery(it.uid, context.query) }
                    .distinctBy { it.uid }
                    .sortedWith(compareBy<SpreadsheetRef> { if (it.name == it.uid) 1 else 0 }.thenBy { it.name.lowercase() })
                    .map { ref ->
                        SpreadsheetCompletionItem(
                            label = ref.name,
                            insertText = ref.name,
                            kind = "box",
                            detail = ref.uid,
                            documentation = "Box reference",
                            replaceStart = context.replaceStart,
                            replaceEnd = context.replaceEnd
                        )
                    }
                suggestions
            }

            is CompletionContext.BoxMember -> {
                val target = resolveBox(info, box, context.ref)
                val names = memberNamesFor(target)
                names.filter { matchesCompletionQuery(it, context.query) }
                    .sorted()
                    .map { name ->
                        SpreadsheetCompletionItem(
                            label = name,
                            insertText = name,
                            kind = "member",
                            detail = "Box value",
                            documentation = "Value from ${target?.name?.ifBlank { target.id } ?: context.ref}",
                            replaceStart = context.replaceStart,
                            replaceEnd = context.replaceEnd
                        )
                    }
            }

            is CompletionContext.SelfMember -> {
                memberNamesFor(box)
                    .filter { matchesCompletionQuery(it, context.query) }
                    .sorted()
                    .map { name ->
                        SpreadsheetCompletionItem(
                            label = name,
                            insertText = name,
                            kind = "member",
                            detail = "Current box value",
                            documentation = "Current box variable or property",
                            replaceStart = context.replaceStart,
                            replaceEnd = context.replaceEnd
                        )
                    }
            }

            is CompletionContext.General -> generalCompletions()
                .filter { matchesCompletionQuery("${it.label} ${it.detail}", context.query) }
                .map { item ->
                    SpreadsheetCompletionItem(
                        label = item.label,
                        insertText = item.insertText,
                        kind = item.kind,
                        detail = item.detail,
                        documentation = item.documentation,
                        replaceStart = context.replaceStart,
                        replaceEnd = context.replaceEnd,
                        cursorOffset = item.cursorOffset,
                        selectInsertedText = item.selectInsertedText
                    )
                }
        }.take(40)
    }

    private fun availableBoxRefs(info: SessionInformation): List<SpreadsheetRef> =
        info.boxes.values.map { box ->
            val displayName = box.name.trim().ifBlank { box.id }
            SpreadsheetRef(uid = box.id, name = displayName)
        }

    private fun memberNamesFor(box: Box?): List<String> {
        if (box == null) return emptyList()
        return buildSet {
            addAll(box.properties.keys)
            addAll(box.ki.context.vars.keys)
        }.sorted()
    }

    private fun resolveBox(info: SessionInformation, currentBox: Box, ref: String): Box? {
        val trimmed = ref.trim()
        if (trimmed.isEmpty()) return null
        if (trimmed == "self") return currentBox
        info.boxes[trimmed]?.let { return it }
        if (trimmed == "root") return info.root
        val matches = info.boxes.values.filter { it.name.trim() == trimmed }
        return if (matches.size == 1) matches.first() else null
    }

    private fun completionContextFor(formula: String, cursor: Int): CompletionContext {
        val beforeCursor = formula.take(cursor)

        val boxMemberPattern = Regex("""box\s*\(\s*(['"])([^'"]*)\1\s*\)\s*\[\s*(['"])([^'"]*)$""")
        boxMemberPattern.find(beforeCursor)?.let { match ->
            val query = match.groupValues[4]
            return CompletionContext.BoxMember(
                ref = match.groupValues[2],
                replaceStart = cursor - query.length,
                replaceEnd = cursor,
                query = query
            )
        }

        val selfMemberPattern = Regex("""self\s*\[\s*(['"])([^'"]*)$""")
        selfMemberPattern.find(beforeCursor)?.let { match ->
            val query = match.groupValues[2]
            return CompletionContext.SelfMember(
                replaceStart = cursor - query.length,
                replaceEnd = cursor,
                query = query
            )
        }

        val boxTargetPattern = Regex("""box\s*\(\s*(['"])([^'"]*)$""")
        boxTargetPattern.find(beforeCursor)?.let { match ->
            val query = match.groupValues[2]
            return CompletionContext.BoxTarget(
                replaceStart = cursor - query.length,
                replaceEnd = cursor,
                query = query
            )
        }

        val fragment = wordFragmentAround(formula, cursor)
        return CompletionContext.General(
            replaceStart = fragment.start,
            replaceEnd = fragment.end,
            query = fragment.text
        )
    }

    private fun generalCompletions(): List<GeneralCompletion> = listOf(
        GeneralCompletion(
            label = "sum",
            insertText = "sum(range(\"A1:A4\"))",
            kind = "function",
            detail = "Sum numeric values",
            documentation = "Adds every numeric value in a range or list.",
            cursorOffset = 11
        ),
        GeneralCompletion(
            label = "avg",
            insertText = "avg(range(\"A1:A4\"))",
            kind = "function",
            detail = "Average numeric values",
            documentation = "Returns the arithmetic mean of numeric values; an error if there are none.",
            cursorOffset = 11
        ),
        GeneralCompletion(
            label = "min",
            insertText = "min(range(\"A1:A4\"))",
            kind = "function",
            detail = "Minimum numeric value",
            documentation = "Returns the smallest numeric value in a range or list, or 0 if it has none.",
            cursorOffset = 11
        ),
        GeneralCompletion(
            label = "max",
            insertText = "max(range(\"A1:A4\"))",
            kind = "function",
            detail = "Maximum numeric value",
            documentation = "Returns the largest numeric value in a range or list, or 0 if it has none.",
            cursorOffset = 11
        ),
        GeneralCompletion(
            label = "count",
            insertText = "count(range(\"A1:A4\"))",
            kind = "function",
            detail = "Count non-empty values",
            documentation = "Counts non-empty values in a range or list.",
            cursorOffset = 13
        ),
        GeneralCompletion(
            label = "box",
            insertText = "box(\"Box Name\")[\"value\"]",
            kind = "box",
            detail = "Reference another Field box",
            documentation = "References a variable or property on another box by unique name or uid.",
            cursorOffset = 5
        ),
        GeneralCompletion(
            label = "self",
            insertText = "self[\"value\"]",
            kind = "box",
            detail = "Reference the current box",
            documentation = "References a variable or property on the box that owns this spreadsheet.",
            cursorOffset = 6
        ),
        GeneralCompletion(
            label = "cell",
            insertText = "cell(\"A1\")",
            kind = "function",
            detail = "Explicit single-cell lookup",
            documentation = "Equivalent to writing A1 directly inside a formula.",
            cursorOffset = 6
        ),
        GeneralCompletion(
            label = "range",
            insertText = "range(\"A1:B8\")",
            kind = "function",
            detail = "Explicit range lookup",
            documentation = "Returns a row-major list of non-empty values.",
            cursorOffset = 7
        ),
        GeneralCompletion(
            label = "if",
            insertText = "if (true) 1 else 0",
            kind = "snippet",
            detail = "Kotlin conditional expression",
            documentation = "Use Kotlin's normal conditional expression syntax.",
            cursorOffset = 4
        ),
        GeneralCompletion(
            label = "listOf",
            insertText = "listOf(A1, A2, A3)",
            kind = "function",
            detail = "Build a Kotlin list",
            documentation = "Constructs a Kotlin list from cell values or literals.",
            cursorOffset = 7
        ),
        // ── Units (via KotUniL + pipe syntax) ────────────────────────
        GeneralCompletion(label = "| m", insertText = "| m", kind = "unit", detail = "metres", documentation = "Tag value with metres (SI length)."),
        GeneralCompletion(label = "| mm", insertText = "| mm", kind = "unit", detail = "millimetres", documentation = "Tag value with millimetres."),
        GeneralCompletion(label = "| cm", insertText = "| cm", kind = "unit", detail = "centimetres", documentation = "Tag value with centimetres."),
        GeneralCompletion(label = "| km", insertText = "| km", kind = "unit", detail = "kilometres", documentation = "Tag value with kilometres."),
        GeneralCompletion(label = "| kg", insertText = "| kg", kind = "unit", detail = "kilograms", documentation = "Tag value with kilograms (SI mass)."),
        GeneralCompletion(label = "| g", insertText = "| g", kind = "unit", detail = "grams", documentation = "Tag value with grams."),
        GeneralCompletion(label = "| mg", insertText = "| mg", kind = "unit", detail = "milligrams", documentation = "Tag value with milligrams."),
        GeneralCompletion(label = "| t", insertText = "| t", kind = "unit", detail = "metric tons", documentation = "Tag value with metric tons (1000 kg)."),
        GeneralCompletion(label = "| s", insertText = "| s", kind = "unit", detail = "seconds", documentation = "Tag value with seconds (SI time)."),
        GeneralCompletion(label = "| ms", insertText = "| ms", kind = "unit", detail = "milliseconds", documentation = "Tag value with milliseconds."),
        GeneralCompletion(label = "| min", insertText = "| min", kind = "unit", detail = "minutes", documentation = "Tag value with minutes (60 s)."),
        GeneralCompletion(label = "| h", insertText = "| h", kind = "unit", detail = "hours", documentation = "Tag value with hours (3600 s)."),
        GeneralCompletion(label = "| N", insertText = "| N", kind = "unit", detail = "newtons", documentation = "Tag value with newtons (SI force)."),
        GeneralCompletion(label = "| Pa", insertText = "| Pa", kind = "unit", detail = "pascals", documentation = "Tag value with pascals (SI pressure)."),
        GeneralCompletion(label = "| J", insertText = "| J", kind = "unit", detail = "joules", documentation = "Tag value with joules (SI energy)."),
        GeneralCompletion(label = "| W", insertText = "| W", kind = "unit", detail = "watts", documentation = "Tag value with watts (SI power)."),
        GeneralCompletion(label = "| V", insertText = "| V", kind = "unit", detail = "volts", documentation = "Tag value with volts (SI voltage)."),
        GeneralCompletion(label = "| A", insertText = "| A", kind = "unit", detail = "amperes", documentation = "Tag value with amperes (SI current)."),
        GeneralCompletion(label = "| Hz", insertText = "| Hz", kind = "unit", detail = "hertz", documentation = "Tag value with hertz (SI frequency)."),
        GeneralCompletion(label = "| l", insertText = "| l", kind = "unit", detail = "litres", documentation = "Tag value with litres."),
        GeneralCompletion(label = "| ha", insertText = "| ha", kind = "unit", detail = "hectares", documentation = "Tag value with hectares."),
        GeneralCompletion(label = "| K", insertText = "| K", kind = "unit", detail = "kelvin", documentation = "Tag value with kelvin (SI temperature).")
    )
}

private fun normalizeStoredState(state: SpreadsheetStoredState?): SpreadsheetStoredState {
    val rows = (state?.rows ?: DEFAULT_SHEET_ROWS).coerceIn(1, MAX_SHEET_ROWS)
    val cols = (state?.cols ?: DEFAULT_SHEET_COLS).coerceIn(1, MAX_SHEET_COLS)
    val colWidths = MutableList<Double>(cols) { index ->
        (state?.colWidths?.getOrNull(index)?.toInt()?.coerceIn(48, 480) ?: DEFAULT_SHEET_COL_WIDTH).toDouble()
    }
    val rowHeights = MutableList<Double>(rows) { index ->
        (state?.rowHeights?.getOrNull(index)?.toInt()?.coerceIn(22, 240) ?: DEFAULT_SHEET_ROW_HEIGHT).toDouble()
    }
    val cells = linkedMapOf<String, SpreadsheetStoredCell>()
    state?.cells.orEmpty().forEach { cell ->
        if (cell.row !in 0 until rows || cell.col !in 0 until cols) return@forEach
        if (cell.input.isBlank()) return@forEach
        cells["${cell.row}:${cell.col}"] = SpreadsheetStoredCell(cell.row, cell.col, cell.input)
    }
    return SpreadsheetStoredState(
        rows = rows,
        cols = cols,
        colWidths = colWidths,
        rowHeights = rowHeights,
        cells = cells.values.toList(),
        solved = state?.solved.orEmpty().filterKeys { k ->
            k.split(':').let { it.size == 2 && (it[0].toIntOrNull() ?: -1) in 0 until rows && (it[1].toIntOrNull() ?: -1) in 0 until cols }
        }
    )
}

private fun normalizedSpreadsheetProperty(property: String): String =
    property.trim().ifBlank { "spreadsheet" }

private fun encodeStoredSpreadsheetState(state: SpreadsheetStoredState): String =
    SpreadsheetProtocol.json.encodeToString(normalizeStoredState(state))

private fun writeStoredSpreadsheetState(box: Box, property: String, state: SpreadsheetStoredState): SpreadsheetStoredState {
    val normalizedProperty = normalizedSpreadsheetProperty(property)
    val normalizedState = normalizeStoredState(state)
    box.setPropertyRaw(normalizedProperty, encodeStoredSpreadsheetState(normalizedState), doResync = false)
    if (Box.ioSeparateProperties.containsKey(normalizedProperty) && box.isInsideInitialized() && !box.inside.ephemeral) {
        box.inside.markSidecarPending(box.id, normalizedProperty)
    }
    return normalizedState
}

private fun decodeStoredState(raw: String?): SpreadsheetStoredState {
    if (raw.isNullOrBlank()) return normalizeStoredState(null)
    return runCatching {
        SpreadsheetProtocol.json.decodeFromString(SpreadsheetStoredState.serializer(), raw)
    }.getOrElse {
        lprintln("warning,spreadsheet", "failed to decode stored spreadsheet state: ${it.message}")
        normalizeStoredState(null)
    }
}

private fun classifyCellInput(input: String): String {
    val trimmed = input.trim()
    if (trimmed.isEmpty()) return "empty"
    if (trimmed.startsWith("=")) return "formula"
    if (trimmed.toDoubleOrNull() != null) return "number"
    return "text"
}

private fun spreadsheetCellInputText(input: Any?): String = when (input) {
    null -> ""
    is SpreadsheetScalar -> formatSpreadsheetValue(input.raw)
    is String -> input
    is CharSequence -> input.toString()
    is Number, is Boolean -> formatSpreadsheetValue(input)
    else -> input.toString()
}

private fun templateEnd(input: String, open: Int): Int {
    var depth = 1
    var index = open + 1
    while (index < input.length) {
        when (input[index]) {
            '{' -> depth += 1
            '}' -> { depth -= 1; if (depth == 0) return index }
        }
        index += 1
    }
    return -1
}

private fun rewriteCellRefs(input: String, refs: MutableList<String>): String {
    val out = StringBuilder(input.length + 32)
    var index = 0
    var quote: Char? = null
    while (index < input.length) {
        val ch = input[index]
        if (quote != null) {
            // The code inside a string template is code: `"${'$'}{min(A2:A4)}"` reads the range.
            if (quote == '"' && ch == '$' && index + 1 < input.length && input[index + 1] == '{') {
                val close = templateEnd(input, index + 1)
                if (close > 0) {
                    out.append("${'$'}{").append(rewriteCellRefs(input.substring(index + 2, close), refs)).append('}')
                    index = close + 1
                    continue
                }
            }
            out.append(ch)
            if (ch == '\\' && quote != '`' && index + 1 < input.length) {
                out.append(input[index + 1])
                index += 2
                continue
            }
            if (ch == quote) quote = null
            index += 1
            continue
        }
        if (ch == '\'' || ch == '"' || ch == '`') {
            quote = ch
            out.append(ch)
            index += 1
            continue
        }
        val parsedFirst = parseCellRefAt(input, index)
        if (parsedFirst != null && isRefBoundaryBefore(input, index)) {
            val colonIndex = parsedFirst.endExclusive
            if (colonIndex < input.length && input[colonIndex] == ':') {
                val parsedSecond = parseCellRefAt(input, colonIndex + 1)
                if (parsedSecond != null && isRefBoundaryAfter(input, parsedSecond.endExclusive)) {
                    refs += "${cellLabel(parsedFirst.row, parsedFirst.col)}:${cellLabel(parsedSecond.row, parsedSecond.col)}"
                    out.append("range(__ref(${refs.lastIndex}))")
                    index = parsedSecond.endExclusive
                    continue
                }
            }
            if (isRefBoundaryAfter(input, parsedFirst.endExclusive)) {
                refs += cellLabel(parsedFirst.row, parsedFirst.col)
                out.append("cell(__ref(${refs.lastIndex}))")
                index = parsedFirst.endExclusive
                continue
            }
        }
        out.append(ch)
        index += 1
    }
    return out.toString()
}

private val PIPE_UNIT_PATTERN = Regex("""\|\s*([A-Za-z_`][A-Za-z0-9_`]*)""")

private fun rewritePipeUnits(input: String): String {
    val out = StringBuilder(input.length + 32)
    var index = 0
    var quote: Char? = null
    while (index < input.length) {
        val ch = input[index]
        if (quote != null) {
            out.append(ch)
            if (ch == '\\' && quote != '`' && index + 1 < input.length) {
                out.append(input[index + 1])
                index += 2
                continue
            }
            if (ch == quote) quote = null
            index += 1
            continue
        }
        if (ch == '\'' || ch == '"' || ch == '`') {
            quote = ch
            out.append(ch)
            index += 1
            continue
        }
        if (ch == '|') {
            val remaining = input.substring(index)
            val match = PIPE_UNIT_PATTERN.matchAt(remaining, 0)
            if (match != null) {
                val unitIdent = match.groupValues[1]
                out.append(".withUnit($unitIdent)")
                index += match.value.length
                continue
            }
        }
        out.append(ch)
        index += 1
    }
    return out.toString()
}

private fun parseCellRefAt(source: String, start: Int): ParsedCellRef? {
    var index = start
    if (index >= source.length) return null
    if (source[index] == '$') index += 1
    val colStart = index
    while (index < source.length && source[index].isLetter()) index += 1
    if (index == colStart) return null
    if (index < source.length && source[index] == '$') index += 1
    val rowStart = index
    while (index < source.length && source[index].isDigit()) index += 1
    if (index == rowStart) return null

    val colEnd = if (rowStart > colStart && source[rowStart - 1] == '$') rowStart - 1 else rowStart
    val colText = source.substring(colStart, colEnd)
    val rowDigits = source.substring(rowStart, index)
    val row = rowDigits.toIntOrNull()?.minus(1) ?: return null
    if (row < 0) return null
    var col = 0
    colText.uppercase().forEach { letter ->
        col = col * 26 + (letter.code - 'A'.code + 1)
    }
    return ParsedCellRef(row = row, col = col - 1, endExclusive = index)
}

private fun isRefBoundaryBefore(source: String, index: Int): Boolean {
    if (index <= 0) return true
    val prev = source[index - 1]
    return !(prev.isLetterOrDigit() || prev == '_' || prev == ']')
}

private fun isRefBoundaryAfter(source: String, index: Int): Boolean {
    if (index >= source.length) return true
    val next = source[index]
    return !(next.isLetterOrDigit() || next == '_' || next == '[')
}

private fun cellRefToCoord(ref: String): CellCoord? {
    val parsed = parseCellRefAt(ref.trim(), 0) ?: return null
    if (parsed.endExclusive != ref.trim().length) return null
    return CellCoord(parsed.row, parsed.col)
}

private fun columnLabelToIndex(label: String): Int? {
    val text = label.trim().uppercase()
    if (text.isEmpty() || text.any { !it.isLetter() }) return null
    var col = 0
    text.forEach { letter ->
        col = col * 26 + (letter.code - 'A'.code + 1)
    }
    return col - 1
}

private fun cellLabel(row: Int, col: Int): String {
    var label = ""
    var current = col
    while (current >= 0) {
        label = ('A'.code + (current % 26)).toChar() + label
        current = current / 26 - 1
    }
    return "$label${row + 1}"
}

private fun fingerprintSpreadsheetDependencyValue(value: Any?): String = when (value) {
    is SpreadsheetScalar -> fingerprintSpreadsheetDependencyValue(value.raw)
    null -> "null"
    is Unit -> "unit"
    is String -> "string:${value.length}:$value"
    is Boolean -> "bool:$value"
    is Vec2 -> "vec2:${value.x},${value.y}"
    is Vec3 -> "vec3:${value.x},${value.y},${value.z}"
    is Vec4 -> "vec4:${value.x},${value.y},${value.z},${value.w}"
    is FLine -> summarizeFLine(value).let { "fline:${it.x},${it.y},${it.w},${it.h}" }
    is ByteArray -> value.joinToString(prefix = "bytes[", postfix = "]")
    is ShortArray -> value.joinToString(prefix = "shorts[", postfix = "]")
    is IntArray -> value.joinToString(prefix = "ints[", postfix = "]")
    is LongArray -> value.joinToString(prefix = "longs[", postfix = "]")
    is FloatArray -> value.joinToString(prefix = "floats[", postfix = "]")
    is DoubleArray -> value.joinToString(prefix = "doubles[", postfix = "]")
    is BooleanArray -> value.joinToString(prefix = "bools[", postfix = "]")
    is CharArray -> "chars[${value.concatToString()}]"
    is Array<*> -> value.joinToString(prefix = "array[", postfix = "]") { fingerprintSpreadsheetDependencyValue(it) }
    is Iterable<*> -> value.joinToString(prefix = "iter[", postfix = "]") { fingerprintSpreadsheetDependencyValue(it) }
    is Map<*, *> -> value.entries
        .sortedBy { fingerprintSpreadsheetDependencyValue(it.key) }
        .joinToString(prefix = "map{", postfix = "}") {
            "${fingerprintSpreadsheetDependencyValue(it.key)}=${fingerprintSpreadsheetDependencyValue(it.value)}"
        }

    is Quantity -> "quantity:${value.value}:${value.unitSymbols}:${value.displaySymbol}"
    is Expression -> "expression:${value.value}:${value.unitSymbols()}"
    is Number -> "number:${value.javaClass.name}:${value}"
    else -> "${value.javaClass.name}:${value}"
}

private fun formatSpreadsheetValue(value: Any?): String = when (value) {
    is SpreadsheetScalar -> formatSpreadsheetValue(value.raw)
    is SpreadsheetTraced -> formatNumber(value.value)
    is SpreadsheetAssertion -> formatAssertion(value)
    null -> ""
    is Unit -> ""
    is Quantity -> {
        val (v, sym) = formatQuantityDisplay(value)
        if (sym.isNotEmpty()) "${formatNumber(v)} $sym" else formatNumber(v)
    }
    is Expression -> {
        val (v, sym) = formatExpression(value)
        if (sym.isNotEmpty()) "${formatNumber(v)} $sym" else formatNumber(value.value)
    }
    is Double -> formatNumber(value)
    is Float -> formatNumber(value.toDouble())
    is Number -> value.toString()
    is Boolean -> value.toString()
    is String -> value
    is Vec2 -> "vec(${formatNumber(value.x.toDouble())}, ${formatNumber(value.y.toDouble())})"
    is Vec3 -> "vec(${formatNumber(value.x.toDouble())}, ${formatNumber(value.y.toDouble())}, ${formatNumber(value.z.toDouble())})"
    is Vec4 -> "vec(${formatNumber(value.x.toDouble())}, ${formatNumber(value.y.toDouble())}, ${formatNumber(value.z.toDouble())}, ${formatNumber(value.w.toDouble())})"
    is FLine -> summarizeFLine(value).let {
        "FLine(${formatNumber(it.x)}, ${formatNumber(it.y)}, ${formatNumber(it.w)}, ${formatNumber(it.h)})"
    }
    is SpreadsheetBoxBinding -> value.name
    is ByteArray -> value.joinToString(prefix = "[", postfix = "]")
    is ShortArray -> value.joinToString(prefix = "[", postfix = "]")
    is IntArray -> value.joinToString(prefix = "[", postfix = "]")
    is LongArray -> value.joinToString(prefix = "[", postfix = "]")
    is FloatArray -> value.joinToString(prefix = "[", postfix = "]") { formatNumber(it.toDouble()) }
    is DoubleArray -> value.joinToString(prefix = "[", postfix = "]") { formatNumber(it) }
    is BooleanArray -> value.joinToString(prefix = "[", postfix = "]")
    is Iterable<*> -> value.joinToString(prefix = "[", postfix = "]") { formatSpreadsheetValue(it) }
    is Array<*> -> value.joinToString(prefix = "[", postfix = "]") { formatSpreadsheetValue(it) }
    else -> value.toString()
}

private fun isSpreadsheetScalarDisplay(value: Any?): Boolean {
    val raw = unwrapSpreadsheetValue(value)
    return when (raw) {
        null, is Unit -> true
        is Number, is Boolean, is SpreadsheetTraced -> true
        is Quantity, is Expression -> true
        is String -> raw.length <= 120
        else -> false
    }
}

private fun formatAssertion(a: SpreadsheetAssertion): String =
    if (a.satisfied) "✓ ${formatNumber(a.left)} ${a.relation.symbol} ${formatNumber(a.right)}"
    else "✗ ${formatNumber(a.left)} ${a.relation.symbol} ${formatNumber(a.right)} (off by ${formatNumber(a.violation).let { if (it == "0") "%.2g".format(a.violation) else it }})"

private fun spreadsheetHtmlDisplay(value: Any?): String? {
    val raw = unwrapSpreadsheetValue(value)
    if (raw is SpreadsheetAssertion) {
        val state = if (raw.satisfied) "ok" else "violated"
        return "<span class=\"sheet-assertion\" data-assertion=\"$state\">${formatAssertion(raw)}</span>"
    }
    if (isSpreadsheetScalarDisplay(value)) return null
    return try {
        ValueVisualizer.visualize(unwrapSpreadsheetValue(value))
    } catch (_: Throwable) {
        null
    }
}

private fun formatNumber(value: Double): String {
    val rounded = round(value * 10_000.0) / 10_000.0
    val safe = if (rounded == -0.0) 0.0 else rounded
    val fixed = "%,.4f".format(java.util.Locale.US, safe)
    return fixed
        .replace(",", "")
        .replace(Regex("""\.?0+$"""), "")
}

private fun numericValue(value: Any?): Double? = SpreadsheetDynamicRegistry.scalar(value)

private fun safeForId(value: String): String =
    value.replace(Regex("""[^A-Za-z0-9_]+"""), "_").trim('_').ifBlank { UUID.randomUUID().toString().replace("-", "_") }

private fun wordFragmentAround(text: String, cursor: Int): SheetWordFragment {
    var start = cursor
    var end = cursor
    while (start > 0 && isCompletionWordChar(text[start - 1])) start -= 1
    while (end < text.length && isCompletionWordChar(text[end])) end += 1
    return SheetWordFragment(start, end, text.substring(start, cursor))
}

private fun isCompletionWordChar(ch: Char): Boolean =
    ch.isLetterOrDigit() || ch == '_' || ch == '.'

private fun matchesCompletionQuery(label: String, query: String): Boolean {
    if (query.isBlank()) return true
    val haystack = label.lowercase()
    val needle = query.lowercase()
    return haystack.contains(needle)
}

@RunsAt(installHandlers)
fun installSpreadsheetSupport() {
    registerSpreadsheetProperty("spreadsheet")
    Runloop.runLoop.preRun[SPREADSHEET_POLL_HOOK] = sequence {
        while (true) {
            runCatching { SpreadsheetService.pollLiveSheets() }
                .onFailure { lprintln("warning,spreadsheet", "spreadsheet live polling failed: ${it.message}") }
            yield(Unit)
        }
    }

    rawHandlers.getOrPut(SPREADSHEET_CHANNEL) { mutableSetOf() }.add spreadsheet@{ text ->
        val envelope = runCatching { JSONObject(text) }.getOrNull()
        if (envelope == null || (!envelope.has("op") && envelope.optString("channel") != SPREADSHEET_CHANNEL)) {
            return@spreadsheet Handled.no
        }
        val ws = thisSession.get()
        val info = thisSessionInfo.get()
        val reqSnippet = text.take(120)
        lprintln("debug,spreadsheet,ws", "[sheet-ws] raw handler received (${text.length} chars): $reqSnippet")
        inMain {
            lprintln("debug,spreadsheet,ws", "[sheet-ws] inMain entered for: $reqSnippet")
            try {
                val response = SpreadsheetService.handle(text, info = info, clientKey = ws) { payload ->
                    lprintln("debug,spreadsheet,ws", "[sheet-ws] broadcast push (${payload.length} chars)")
                    inMain {
                        try {
                            ws.send(payload)
                        } catch (t: Throwable) {
                            lprintln("warning,spreadsheet,ws", "[sheet-ws] broadcast send failed: ${t.message}")
                        }
                    }
                }
                if (response.isNotEmpty()) {
                    lprintln("debug,spreadsheet,ws", "[sheet-ws] sending response (${response.length} chars)")
                    ws.send(response)
                    lprintln("debug,spreadsheet,ws", "[sheet-ws] response sent OK")
                } else {
                    lprintln("debug,spreadsheet,ws", "[sheet-ws] handle returned empty response")
                }
            } catch (t: Throwable) {
                lprintln("error,spreadsheet,ws", "[sheet-ws] inMain block failed: ${t::class.simpleName}: ${t.message}")
                t.printStackTrace()
            }
        }
        Handled.yes
    }
}