-- Verdict cache for the risk classifier (llm/classifier.org), keyed by
-- content hash + model — re-classification happens when content or model
-- changes, not on a wall clock.
CREATE TABLE IF NOT EXISTS llm_verdict_cache (
  content_hash TEXT NOT NULL,
  model TEXT NOT NULL,
  health REAL NOT NULL,
  private_score REAL NOT NULL,
  financial REAL NOT NULL,
  employer REAL NOT NULL,
  political REAL NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (content_hash, model)
);

insertVerdict:
INSERT OR REPLACE INTO llm_verdict_cache
  (content_hash, model, health, private_score, financial, employer, political, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?);

selectVerdict:
SELECT health, private_score, financial, employer, political, created_at
FROM llm_verdict_cache WHERE content_hash = ? AND model = ?;

selectToolNodeById:
SELECT id, level, todo, priority, scheduled, deadline, title, properties, olp
FROM nodes WHERE id = ?;

selectToolNodeFileById:
SELECT file AS file FROM nodes WHERE id = ?;

selectToolAncestorTags:
SELECT DISTINCT t.tag AS tag
FROM node_ancestors na
JOIN tags t ON t.node_id = na.ancestor_id
WHERE na.node_id = ?;

selectToolNodeBracket:
SELECT me.pos AS my_pos,
       (SELECT MIN(n2.pos) FROM nodes n2 WHERE n2.file = me.file AND n2.level <= me.level AND n2.pos > me.pos) AS bracket_end
FROM nodes me WHERE me.id = ?;

selectToolChildNodes:
SELECT n.id, n.title, n.todo, n.scheduled, n.deadline
FROM nodes n
JOIN nodes me ON me.id = ?1
WHERE n.file = me.file
  AND n.level = me.level + 1
  AND n.pos > me.pos
  AND n.pos < COALESCE(
      (SELECT MIN(n3.pos) FROM nodes n3
       WHERE n3.file = me.file AND n3.level <= me.level AND n3.pos > me.pos),
      2147483647)
ORDER BY n.pos;

selectToolDescendantIds:
SELECT n.id
FROM nodes n
JOIN nodes me ON me.id = ?
WHERE n.file = me.file
AND n.level > me.level
AND n.pos > me.pos
AND n.pos < COALESCE(
(SELECT MIN(n3.pos) FROM nodes n3
WHERE n3.file = me.file AND n3.level <= me.level AND n3.pos > me.pos),
2147483647)
ORDER BY n.pos;

selectToolLinksFrom:
SELECT l.to_node AS target_id, n.title AS target_title, l.type
FROM links l
LEFT JOIN nodes n ON n.id = l.to_node
WHERE l.from_node = ? AND l.to_node IS NOT NULL
ORDER BY l.pos;

selectToolLinksTo:
SELECT l.from_node AS target_id, n.title AS target_title, l.type
FROM links l
LEFT JOIN nodes n ON n.id = l.from_node
WHERE l.to_node = ?
ORDER BY l.pos;

selectToolRawLinks:
SELECT type, properties FROM links
WHERE from_node = ? AND to_node IS NULL;

selectToolRefsByNode:
SELECT ref, type FROM refs WHERE node_id = ? ORDER BY ref;

selectToolAliasesByNode:
SELECT alias AS alias FROM aliases WHERE node_id = ? ORDER BY alias;

selectToolTagsByNode:
SELECT tag AS tag FROM tags WHERE node_id = ? ORDER BY tag;

selectToolScheduledBetween:
SELECT id, title, todo, scheduled, deadline
FROM nodes
WHERE (scheduled BETWEEN ?1 AND ?2) OR (deadline BETWEEN ?1 AND ?2);

selectToolJournalNodesBetween:
SELECT id, title, todo, scheduled, deadline
FROM nodes
WHERE file >= ?1 AND file <= ?2
ORDER BY file, pos;

@Serializable
data class ToolError(val code: String, val message: String)

@Serializable
data class ToolEnvelope(
    val ok: Boolean,
    val data: JsonElement? = null,
    val error: ToolError? = null,
    val risk: RiskBlock? = null
)

/** Stable Json config for every tool's stdout. */
val ToolJson = Json {
    encodeDefaults = true
    prettyPrint = false
    ignoreUnknownKeys = true
}

data class ChildRecord(
    val id: String, val title: String?, val todo: String?,
    val scheduled: String?, val deadline: String?
)

data class LinkRecord(val targetId: String, val targetTitle: String?, val type: String)

data class RawLinkRecord(val type: String?, val properties: String?)

data class RefRecord(val ref: String, val type: String)

data class TimelineRecord(
    val id: String, val title: String?, val todo: String?,
    val scheduled: String?, val deadline: String?
)

data class VerdictRecord(
    val health: Double, val privateScore: Double, val financial: Double,
    val employer: Double, val political: Double, val createdAt: Long
)

interface ToolQueryRepository {
    suspend fun selectNodeById(id: String): NodeRecord?
    suspend fun selectAncestorTags(id: String): List<String>
    suspend fun selectChildNodes(id: String): List<ChildRecord>
    suspend fun selectDescendantIds(id: String): List<String>
    suspend fun selectLinksFrom(id: String): List<LinkRecord>
    suspend fun selectLinksTo(id: String): List<LinkRecord>
    suspend fun selectRawLinks(id: String): List<RawLinkRecord>
    suspend fun selectRefsByNode(id: String): List<RefRecord>
    suspend fun selectAliasesByNode(id: String): List<String>
    suspend fun selectTagsByNode(id: String): List<String>
    suspend fun selectScheduledBetween(from: String, to: String): List<TimelineRecord>
    suspend fun selectJournalNodesBetween(startFile: String, endFile: String): List<TimelineRecord>
    suspend fun insertVerdict(contentHash: String, model: String, scores: RiskScores, createdAt: Long)
    suspend fun selectVerdict(contentHash: String, model: String): VerdictRecord?
}

class ToolQueryRepositoryImpl(
    private val database: ArcologyDatabase
) : ToolQueryRepository {
    @OptIn(ExperimentalCoroutinesApi::class)
    private val dbDispatcher = Dispatchers.IO.limitedParallelism(1)

    override suspend fun selectNodeById(id: String): NodeRecord? = withContext(dbDispatcher) {
        database.toolsQueries.selectToolNodeById(id).executeAsOneOrNull()?.let {
            NodeRecord(it.id, it.level, it.todo, it.priority, it.scheduled, it.deadline, it.title, it.properties, it.olp)
        }
    }

    override suspend fun selectAncestorTags(id: String): List<String> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolAncestorTags(id).executeAsList()
    }

    override suspend fun selectChildNodes(id: String): List<ChildRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolChildNodes(id).executeAsList().map {
            ChildRecord(it.id, it.title, it.todo, it.scheduled, it.deadline)
        }
    }

    override suspend fun selectDescendantIds(id: String): List<String> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolDescendantIds(id).executeAsList()
    }

    override suspend fun selectLinksFrom(id: String): List<LinkRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolLinksFrom(id).executeAsList().map {
            LinkRecord(it.target_id, it.target_title, it.type)
        }
    }

    override suspend fun selectLinksTo(id: String): List<LinkRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolLinksTo(id).executeAsList().map {
            LinkRecord(it.target_id, it.target_title, it.type)
        }
    }

    override suspend fun selectRawLinks(id: String): List<RawLinkRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolRawLinks(id).executeAsList().map { RawLinkRecord(it.type, it.properties) }
    }

    override suspend fun selectRefsByNode(id: String): List<RefRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolRefsByNode(id).executeAsList().map { RefRecord(it.ref, it.type) }
    }

    override suspend fun selectAliasesByNode(id: String): List<String> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolAliasesByNode(id).executeAsList()
    }

    override suspend fun selectTagsByNode(id: String): List<String> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolTagsByNode(id).executeAsList()
    }

    override suspend fun selectScheduledBetween(from: String, to: String): List<TimelineRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolScheduledBetween(from, to).executeAsList().map {
            TimelineRecord(it.id, it.title, it.todo, it.scheduled, it.deadline)
        }
    }

    override suspend fun selectJournalNodesBetween(startFile: String, endFile: String): List<TimelineRecord> = withContext(dbDispatcher) {
        database.toolsQueries.selectToolJournalNodesBetween(startFile, endFile).executeAsList().map {
            TimelineRecord(it.id, it.title, it.todo, it.scheduled, it.deadline)
        }
    }

    override suspend fun insertVerdict(contentHash: String, model: String, scores: RiskScores, createdAt: Long) = withContext(dbDispatcher) {
        database.toolsQueries.insertVerdict(
            contentHash, model, scores.health, scores.privateScore, scores.financial,
            scores.employer, scores.political, createdAt
        )
    }

    override suspend fun selectVerdict(contentHash: String, model: String): VerdictRecord? = withContext(dbDispatcher) {
        database.toolsQueries.selectVerdict(contentHash, model).executeAsOneOrNull()?.let {
            VerdictRecord(it.health, it.private_score, it.financial, it.employer, it.political, it.created_at)
        }
    }
}

class NotesCommand : CliktCommand(
    name = "notes",
    help = "LLM-harness tools: get, search, timeline, capture, sql"
) {
    override fun run() = Unit
}

/**
 * Tags that suppress a note from tool output, mirroring the publishing
 * layer's exclusions. Case-sensitive on purpose: :ARCHIVE: hides,
 * :Archive: (a knowledge-organization tag) does not.
 */
val EXCLUDE_TAG_SET = setOf("noexport", "NOEXPORT", "ARCHIVE")

const val DEFAULT_MAX_BODY_BYTES: Long = 20_480L

abstract class NoteToolBase(name: String, help: String) : CliktCommand(name = name, help = help) {
    protected val dbPath: String by option("--db", help = "Path to SQLite database")
        .default("~/org/arcology.db")
    protected val orgDir: String by option("--org-dir", help = "Root org-mode directory")
        .default("~/org")
    // Nullable options with no explicit default: Clikt yields null when
    // absent. Env fallback happens in makeGate — same pattern as SyncCommand.
    protected val riskEndpoint: String? by option(
        "--risk-endpoint",
        help = "Ollama http://host:port for the risk classifier (default: \$ARCOLOGY_RISK_ENDPOINT)"
    )
    protected val riskModel: String? by option(
        "--risk-model", help = "Classifier model name (default: \$ARCOLOGY_RISK_MODEL)"
    )
    protected val riskThreshold: Double by option(
        "--risk-threshold", help = "Overall score above which content is redacted"
    ).double().default(0.7)

    /** =--no-risk-gate=: explicit escape hatch for trusted local runs. */
    protected val noRiskGate: Boolean by option(
        "--no-risk-gate", help = "Disable risk gating for this invocation"
    ).flag(default = false)

    /** =--verbose=: attach the classifier input/output transcript to the envelope. */
    protected val verbose: Boolean by option(
        "--verbose", help = "Include the classifier request/response transcript in the risk block"
    ).flag(default = false)

    protected fun expandTilde(path: String) = path.replace("~", System.getProperty("user.home"))

    protected fun openRepo(): ToolQueryRepository =
        ToolQueryRepositoryImpl(DatabaseFactory.createDatabase(expandTilde(dbPath)))

    /** Classifier when endpoint configured; else null — gate fails closed. */
    protected fun makeGate(repo: ToolQueryRepository): RiskGate {
        val endpoint = riskEndpoint ?: System.getenv("ARCOLOGY_RISK_ENDPOINT")
        val model = riskModel ?: System.getenv("ARCOLOGY_RISK_MODEL") ?: "unknown"
        val classifier = endpoint?.let { ep ->
            RiskClassifier(
                endpoint = ep,
                model = model,
                httpClient = HttpClient {
                    install(HttpTimeout) { requestTimeoutMillis = 45_000 }
                }
            )
        }
        return RiskGate(repo, classifier, model = model, trustLocal = noRiskGate)
    }

    suspend fun printEnvelope(envelope: ToolEnvelope) {
        println(ToolJson.encodeToString(ToolEnvelope.serializer(), envelope))
    }

    /** Emit an error envelope and abort with the message. */
    protected suspend fun fail(code: String, message: String): Nothing {
        println(
            ToolJson.encodeToString(
                ToolEnvelope.serializer(),
                ToolEnvelope(ok = false, error = ToolError(code, message))
            )
        )
        throw CliktError(message)
    }
}

/** Tool-facing node projection — no file path field, by design. */
data class NodeRecord(
    val id: String,
    val level: Long,
    val todo: String?,
    val priority: String?,
    val scheduled: String?,
    val deadline: String?,
    val title: String?,
    val properties: String?,
    val olp: String?
)

/**
 * True when the heading itself carries an exclusion tag (noexport/NOEXPORT/
 * ARCHIVE) — mirrors the publishing layer's EXCLUDE_TAGS semantics. Only the
 * heading's *own* tags count here: inherited exclusion (an excluded parent
 * already removed this subtree during the walk) and excluded ancestors are
 * handled by the recursive render, and filetags deliberately do NOT exclude
 * (a file tagged :noshow: still wants its tool output).
 */
internal fun isExcludedHeading(heading: xyz.lepisma.orgmode.OrgHeading): Boolean {
    val tags = heading.tags?.tags ?: return false
    return tags.any { it in EXCLUDE_TAG_SET }
}

/** Recursive plaintext walk over org chunks with heading rendering. */
internal fun renderChunksPlaintext(
    chunks: List<xyz.lepisma.orgmode.OrgChunk>,
    sb: StringBuilder
) {
    for (chunk in chunks) {
        when (chunk) {
            is xyz.lepisma.orgmode.OrgChunk.OrgParagraph -> {
                val line = chunk.tokens.joinToString("") { it.text }.trim()
                if (line.isNotEmpty()) {
                    sb.appendLine(line)
                    sb.appendLine()
                }
            }
            is xyz.lepisma.orgmode.OrgChunk.OrgTable ->
                (listOfNotNull(chunk.header) + chunk.subtables.flatten())
                    .joinTo(sb, "\n", postfix = "\n\n") { row ->
                        row.cells.joinToString(" | ") { it.plainText() }
                    }
            is xyz.lepisma.orgmode.OrgList.OrgUnorderedList ->
                chunk.items.forEach { item ->
                    item.content.forEach { renderChunksInline(it, sb) }
                }
            is xyz.lepisma.orgmode.OrgList.OrgOrderedList ->
                chunk.items.forEach { item ->
                    item.content.forEach { renderChunksInline(it, sb) }
                }
            is xyz.lepisma.orgmode.OrgSection ->
                renderSectionPlaintext(chunk, sb)
            else -> { } // keyword lines, logbooks, review data: noise for the harness
        }
    }
}

/** Non-section chunk rendered by [[renderChunksPlaintext]] — shared with lists. */
internal fun renderChunksInline(
    chunk: xyz.lepisma.orgmode.OrgChunk,
    sb: StringBuilder
) {
    when (chunk) {
        is xyz.lepisma.orgmode.OrgChunk.OrgParagraph -> {
            val line = chunk.tokens.joinToString("") { it.text }.trim()
            if (line.isNotEmpty()) {
                sb.appendLine(line)
                sb.appendLine()
            }
        }
        is xyz.lepisma.orgmode.OrgBlock.OrgSourceBlock ->
            if (chunk.body.isNotBlank()) {
                sb.appendLine(chunk.body.trim())
                sb.appendLine()
            }
        is xyz.lepisma.orgmode.OrgBlock.OrgExampleBlock ->
            if (chunk.text.isNotBlank()) {
                sb.appendLine(chunk.text.trim())
                sb.appendLine()
            }
        is xyz.lepisma.orgmode.OrgChunk.OrgTable ->
            (listOfNotNull(chunk.header) + chunk.subtables.flatten())
                .joinTo(sb, "\n", postfix = "\n\n") { row ->
                    row.cells.joinToString(" | ") { it.plainText() }
                }
        is xyz.lepisma.orgmode.OrgBlock.OrgQuoteBlock ->
            chunk.body.forEach { renderChunksInline(it, sb) }
        is xyz.lepisma.orgmode.OrgSection ->
            renderSectionPlaintext(chunk, sb)
        else -> { }
    }
}

/**
 * Render one heading's section: excluded headings vanish with their subtree;
 * ID-bearing headings collapse to stubs; ID-less headings render inline with
 * recursive exclusion checks.
 */
internal fun renderSectionPlaintext(
    section: xyz.lepisma.orgmode.OrgSection,
    sb: StringBuilder
) {
    val tags = section.heading.tags?.tags ?: emptyList()
    if (tags.any { it in EXCLUDE_TAG_SET }) {
        // Excluded subtree: emit nothing, not even a stub. The publishing
        // layer treats the heading as unpublished; the tool treats it as
        // unknowable.
        return
    }

    val id = section.heading.properties?.map
        ?.get("ID")
        ?.let { line -> line.items.filterIsInstance<xyz.lepisma.orgmode.OrgInlineElem.Text>()
            .joinToString("") { it.text }.trim() }
        ?.takeIf { it.isNotEmpty() }

    if (id != null) {
        // ID-bearing child: stub for discovery. NOT the content — the
        // harness fetches it by ID.
        sb.appendLine("${"*".repeat(section.heading.level.level)} ${section.heading.title.plainText()} [id:$id]")
        sb.appendLine()
        return
    }

    // ID-less heading: its text is part of this body. Render inline.
    renderChunksPlaintext(section.body, sb)
}

/**
 * Render the plaintext body of a note by ID. For a regular heading node:
 * its own section (ID-less children inline, ID-bearing children as stubs,
 * excluded subtrees elided). For the file-level node: preface + all
 * top-level sections with the same rules.
 *
 * Returns null when the node/file is missing or unparseable — callers
 * treat null as "no body". The node's file path never crosses the tool
 * boundary.
 */
suspend fun loadLeafBody(
    database: ArcologyDatabase,
    nodeId: String,
    orgDir: String,
    maxBytes: Long
): String? = withContext(Dispatchers.IO) {
    val relative = database.toolsQueries.selectToolNodeFileById(nodeId)
        .executeAsOneOrNull() ?: return@withContext null
    val file = File(orgDir, relative)
    if (!file.exists()) return@withContext null
    val parseResult = OrgFileParser().parseFileContent(
        relative, file.readText(), Instant.fromEpochSeconds(0)
    )
    val success = parseResult as? ParseResult.Success ?: return@withContext null
    val document = success.document ?: return@withContext null

    val sb = StringBuilder()
    val isFileLevel = nodeId == document.preamble.properties?.map?.get("ID")
        ?.let { line -> line.items.filterIsInstance<xyz.lepisma.orgmode.OrgInlineElem.Text>()
            .joinToString("") { it.text }.trim() }

    if (isFileLevel == true) {
        // File-level node: preface paragraphs + every top-level section,
        // each under the recursion/exclusion/stub rules.
        renderChunksPlaintext(document.preface.body, sb)
        document.content.forEach { section ->
            renderSectionPlaintext(section, sb)
        }
    } else {
        val section = findToolSection(document.content, nodeId) ?: return@withContext null
        renderChunksPlaintext(section.body, sb)
    }

    val body = sb.toString().trim()
    if (body.isEmpty()) return@withContext null
    if (body.length > maxBytes) {
        body.take(maxBytes.toInt()) + "\n\n[truncated at $maxBytes bytes]"
    } else {
        body
    }
}

/** Section lookup owned by the llm package (same logic as publishing's helper). */
internal tailrec fun findToolSection(
    sections: List<xyz.lepisma.orgmode.OrgSection>,
    nodeId: String
): xyz.lepisma.orgmode.OrgSection? {
    if (sections.isEmpty()) return null
    for (section in sections) {
        val sectionId = section.heading.properties?.map?.get("ID")
            ?.let { line -> line.items.filterIsInstance<xyz.lepisma.orgmode.OrgInlineElem.Text>()
                .joinToString("") { it.text }.trim() }
        if (sectionId == nodeId) return section
    }
    // recurse: sections' nested bodies are the children lists
    val nested = sections.flatMap { it.body.filterIsInstance<xyz.lepisma.orgmode.OrgSection>() }
    return findToolSection(nested, nodeId)
}

class NoteGetCommand : NoteToolBase(
    name = "get",
    help = "Fetch a note by org ID: metadata, child ids+titles, links, refs, and leaf body as plaintext"
) {
    private val id: String by argument(help = "Org-mode node ID")
    private val maxBodyBytes: Long by option("--max-body-bytes", help = "Body size cap in bytes")
        .long().default(DEFAULT_MAX_BODY_BYTES)

    override fun run() = runBlocking {
        val database = computer.whatthefuck.arcology.database.DatabaseFactory.createDatabase(expandTilde(dbPath))
        val repo = ToolQueryRepositoryImpl(database)

        val record = repo.selectNodeById(id)
            ?: return@runBlocking fail("not_found", "no such note")
        val ancestorTags = repo.selectAncestorTags(id)
        if (ancestorTags.any { it in EXCLUDE_TAG_SET }) {
            fail("not_found", "no such note")
        }

        val body = loadLeafBody(database, id, expandTilde(orgDir), maxBodyBytes)

        val gate = makeGate(repo)
        val gateResult = if (body != null) gate.gate(body, riskThreshold, verbose) else null
        val gated = gateResult?.gated == true

        // Gated metadata policy: a redacted note returns only what the
        // harness itself supplied — the ID (echo of the request) and the
        // risk block. Links/tags/aliases/properties/children all leak
        // signal about otherwise-withheld content, so none of them flow.
        val data = if (gated) {
            buildJsonObject {
                put("id", JsonPrimitive(record.id))
                put("body", buildJsonObject { put("redacted", JsonPrimitive(true)) })
            }
        } else {
            val linksFrom = repo.selectLinksFrom(id)
            val linksTo = repo.selectLinksTo(id)
            val rawLinks = repo.selectRawLinks(id)
            val refs = repo.selectRefsByNode(id)
            val aliases = repo.selectAliasesByNode(id)
            val tags = repo.selectTagsByNode(id)
            val children = repo.selectChildNodes(id)
            buildJsonObject {
                put("id", JsonPrimitive(record.id))
                record.title?.let { put("title", JsonPrimitive(it)) }
                record.todo?.let { put("todo", JsonPrimitive(it)) }
                record.priority?.let { put("priority", JsonPrimitive(it)) }
                record.scheduled?.let { put("scheduled", JsonPrimitive(it)) }
                record.deadline?.let { put("deadline", JsonPrimitive(it)) }
                put("tags", JsonArray(tags.map { JsonPrimitive(it) }))
                put("aliases", JsonArray(aliases.map { JsonPrimitive(it) }))
                put("properties", parsePropertiesString(record.properties))
                put("children", JsonArray(children.map { c ->
                    buildJsonObject {
                        put("id", JsonPrimitive(c.id))
                        c.title?.let { put("title", JsonPrimitive(it)) }
                        c.todo?.let { put("todo", JsonPrimitive(it)) }
                    }
                }))
                put("links", buildJsonObject {
                    put("outgoing", JsonArray(linksFrom.map { l ->
                        buildJsonObject {
                            put("id", JsonPrimitive(l.targetId))
                            l.targetTitle?.let { put("title", JsonPrimitive(it)) }
                        }
                    }))
                    put("incoming", JsonArray(linksTo.map { l ->
                        buildJsonObject {
                            put("id", JsonPrimitive(l.targetId))
                            l.targetTitle?.let { put("title", JsonPrimitive(it)) }
                        }
                    }))
                    put("external", JsonArray(rawLinks.map { rl ->
                        buildJsonObject {
                            rl.type?.let { put("type", JsonPrimitive(it)) }
                            rl.properties?.takeIf { it.isNotBlank() }?.let { put("target", JsonPrimitive(it)) }
                        }
                    }))
                })
                put("refs", JsonArray(refs.map { r ->
                    buildJsonObject {
                        put("ref", JsonPrimitive(r.ref))
                        put("type", JsonPrimitive(r.type))
                    }
                }))
                put(
                    "body",
                    when {
                        body == null -> buildJsonObject {
                            put("redacted", JsonPrimitive(true))
                            put("missing", JsonPrimitive(true))
                        }
                        else -> JsonPrimitive(body)
                    }
                )
            }
        }

        printEnvelope(ToolEnvelope(ok = true, data = data, risk = gateResult?.block))
    }
}

/** Parse the serialized `properties` column ("k: v\nk2: v2…") into a JSON object. */
internal fun parsePropertiesString(raw: String?): kotlinx.serialization.json.JsonObject {
    if (raw.isNullOrBlank()) return kotlinx.serialization.json.buildJsonObject { }
    val obj = kotlinx.serialization.json.buildJsonObject {
        raw.lineSequence()
            .map { it.trim() }
            .filter { it.contains(": ") }
            .forEach { line ->
                val idx = line.indexOf(": ")
                put(line.substring(0, idx), JsonPrimitive(line.substring(idx + 2)))
            }
    }
    return obj
}

class NoteSearchCommand : NoteToolBase(
    name = "search",
    help = "Search notes by title/content; returns ids, titles and outline paths only"
) {
    private val query: String by option("--query", "-q", help = "Search query").required()
    private val mode: String by option("--mode", help = "title | content | combined")
        .default("combined")
    private val limit: Int by option("--limit", help = "Max results").int().default(20)

    override fun run() = runBlocking {
        val database = DatabaseFactory.createDatabase(expandTilde(dbPath))
        val service = SearchService(RoamRepositoryImpl(database))
        val results = when (mode) {
            "title" -> service.searchPrimary(query, limit)
            "content" -> service.searchContent(query, limit = limit)
            else -> service.searchCombined(query, primaryLimit = limit, totalLimit = limit)
        }

        val repo = ToolQueryRepositoryImpl(database)
        val filtered = results.filter { r ->
            repo.selectAncestorTags(r.node.id).none { it in EXCLUDE_TAG_SET }
        }

        val data = buildJsonObject {
            put("results", JsonArray(filtered.map { r ->
                buildJsonObject {
                    put("id", JsonPrimitive(r.node.id))
                    r.node.title?.let { put("title", JsonPrimitive(it)) }
                    put("outline", JsonArray(r.node.outlinePath.map { JsonPrimitive(it) }))
                    put("score", JsonPrimitive(r.rank))
                }
            }))
        }
        printEnvelope(ToolEnvelope(ok = true, data = data))
    }
}

internal val DATE_RE = Regex("""\d{4}-\d{2}-\d{2}""")

class NoteTimelineCommand : NoteToolBase(
    name = "timeline",
    help = "Notes with SCHEDULED/DEADLINE in a date range, plus daily-file journal entries"
) {
    private val from: String by option("--from", help = "Start date YYYY-MM-DD (inclusive)").required()
    private val to: String by option("--to", help = "End date YYYY-MM-DD (inclusive)").required()
    private val kind: String by option("--kind", help = "journal | scheduled | both")
        .default("both")

    override fun run() = runBlocking {
        if (!DATE_RE.matches(from) || !DATE_RE.matches(to)) {
            fail("bad_date", "--from/--to must be YYYY-MM-DD")
        }

        val repo = openRepo()
        val scheduled = if (kind != "journal") repo.selectScheduledBetween(from, to) else emptyList()
        val journal = if (kind != "scheduled") repo.selectJournalNodesBetween(
            "journals/$from.org", "journals/$to.org"
        ) else emptyList()

        val data = buildJsonObject {
            put("scheduled", JsonArray(scheduled.map { r ->
                buildJsonObject {
                    put("id", JsonPrimitive(r.id))
                    r.title?.let { put("title", JsonPrimitive(it)) }
                    r.todo?.let { put("todo", JsonPrimitive(it)) }
                    r.scheduled?.let { put("scheduled", JsonPrimitive(it)) }
                    r.deadline?.let { put("deadline", JsonPrimitive(it)) }
                }
            }))
            put("journal", JsonArray(journal.map { r ->
                buildJsonObject {
                    put("id", JsonPrimitive(r.id))
                    r.title?.let { put("title", JsonPrimitive(it)) }
                    r.todo?.let { put("todo", JsonPrimitive(it)) }
                    r.scheduled?.let { put("scheduled", JsonPrimitive(it)) }
                    r.deadline?.let { put("deadline", JsonPrimitive(it)) }
                }
            }))
        }
        printEnvelope(ToolEnvelope(ok = true, data = data))
    }
}

class NoteCaptureCommand : NoteToolBase(
    name = "capture",
    help = "Append a capture entry to a daily journal file and re-index it"
) {
    private val title: String by option("--title", help = "Entry title").required()
    private val bodyOpt: String by option("--body", help = "Entry body text").default("")
    private val tags: String by option("--tags", help = "Comma-separated tags").default("")
    private val todo: String? by option("--todo", help = "TODO keyword, e.g. TODO")
    private val date: String by option("--date", help = "'today' or YYYY-MM-DD").default("today")

    override fun run() = runBlocking {
        if (date != "today" && !DATE_RE.matches(date)) {
            fail("bad_date", "--date must be 'today' or YYYY-MM-DD")
        }
        val targetDate = if (date == "today") {
            val now = java.time.LocalDate.now()
            "%04d-%02d-%02d".format(now.year, now.monthValue, now.dayOfMonth)
        } else {
            date
        }

        val repo = openRepo()
        val fileSystem = JvmFileSystem(java.nio.file.Paths.get(expandTilde(orgDir)))

        val now = java.time.LocalDateTime.now()
        val captureId = CaptureService.generateId(
            LocalDateTime(
                targetDate.substring(0, 4).toInt(), targetDate.substring(5, 7).toInt(),
                targetDate.substring(8, 10).toInt(), now.hour, now.minute, now.second, 0
            )
        )
        val entry = CaptureService.buildCaptureEntry(
            body = bodyOpt,
            title = title,
            tags = tags.split(",").map { it.trim() }.filter { it.isNotEmpty() },
            todoState = todo,
            id = captureId,
            time = LocalTime(now.hour, now.minute)
        )

        // Classify before writing so the score lands in the envelope.
        val gate = makeGate(repo)
        val gateResult = gate.gate("$title\n$bodyOpt", riskThreshold, verbose)

        val relativePath = "journals/$targetDate.org"
        fileSystem.appendToFile(relativePath, "\n" + entry)

        val config = IndexingConfig(
            batchSize = 1, memoryMonitoring = false, enableFtsDefer = false,
            exportDatabaseAfterIndexing = false
        )
        val indexer = createIndexingService(
            databasePath = expandTilde(dbPath), config = config, basePath = expandTilde(orgDir)
        )
        when (val result = indexer.indexFile(relativePath)) {
            is FileIndexResult.Success -> printEnvelope(
                ToolEnvelope(
                    ok = true,
                    data = buildJsonObject {
                        put("node_id", JsonPrimitive(captureId))
                        put("file", JsonPrimitive(relativePath))
                        put("nodes_indexed", JsonPrimitive(result.nodesCount))
                    },
                    risk = gateResult.block
                )
            )
            is FileIndexResult.Error -> fail("index_failed", result.message)
            is FileIndexResult.Skipped -> fail("index_skipped", result.reason)
        }
    }
}

class NoteSqlCommand : NoteToolBase(
    name = "sql",
    help = "Run a read-only SELECT against the arcology database"
) {
    private val query: String by option("--query", "-q", help = "A single SELECT statement").required()
    private val maxRows: Int by option("--max-rows", help = "Row cap").int().default(200)

    override fun run() = runBlocking {
        val trimmed = query.trim()
        val statement = trimmed.removeSuffix(";").trim()
        val firstWord = statement.split(Regex("\\s+"), limit = 2).firstOrNull()?.uppercase()
        if (statement.isEmpty() || firstWord != "SELECT" || statement.contains(';')) {
            fail("rejected", "only a single-statement SELECT is allowed")
        }

        val repo = openRepo()
        val rows = try {
            executeReadOnlySelect(statement, maxRows, expandTilde(dbPath))
        } catch (e: Exception) {
            fail("sql_error", e.message ?: "query failed")
        }

        val gate = makeGate(repo)
        val gateResult = gate.gate(
            rows.joinToString("\n") { row -> row.entries.joinToString(" | ") { "${it.key}: ${it.value ?: ""}" } },
            riskThreshold,
            verbose
        )

        val data = buildJsonObject {
            if (gateResult.gated) {
                // Gated: only the risk block carries information. Row count
                // itself leaks (a count of sensitive rows is sensitive).
                put(
                    "rows",
                    buildJsonObject { put("redacted", JsonPrimitive(true)) }
                )
            } else {
                put(
                    "rows",
                    JsonArray(rows.map { row ->
                                  buildJsonObject { row.forEach { (k, v) -> put(k, JsonPrimitive(v)) } } })
                )
                put("row_count", JsonPrimitive(rows.size))
            }
        }
        printEnvelope(ToolEnvelope(ok = true, data = data, risk = gateResult.block))
    }
}

/**
 * Open the database read-only and run [sql], returning up to [maxRows] rows
 * with cell values truncated to [maxCellChars].
 *
 * Read-only enforcement is two-layered: PRAGMA query_only=ON makes the
 * connection itself reject any write (INSERT/UPDATE/DDL/PRAGMA writes), and
 * the CLI's statement-shape guard runs before this. The xerial driver is
 * already on the runtime classpath via sqldelight's sqlite-driver, so the
 * org.sqlite.SQLiteConfig convenience is unnecessary — connection properties
 * suffice.
 */
fun executeReadOnlySelect(
    sql: String,
    maxRows: Int,
    dbPath: String,
    maxCellChars: Int = 2000
): List<Map<String, String?>> {
    val path = dbPath.replace("~", System.getProperty("user.home"))
    val props = Properties().apply {
        put("open_mode", "1")  // SQLITE_OPEN_READONLY
        put("busy_timeout", "5000")
        put("query_only", "true")
    }
    DriverManager.getConnection("jdbc:sqlite:$path", props).use { connection ->
        connection.createStatement().use { statement ->
            statement.queryTimeout = 10
            statement.executeQuery(sql).use { resultSet ->
                val meta = resultSet.metaData
                val columns = (1..meta.columnCount).map { i -> i to meta.getColumnName(i) }
                val rows = mutableListOf<Map<String, String?>>()
                while (resultSet.next() && rows.size < maxRows) {
                    rows.add(columns.associate { (i, name) ->
                        name to resultSet.getString(i)?.take(maxCellChars)
                    })
                }
                return rows
            }
        }
    }
}

package computer.whatthefuck.arcology.llm

import com.github.ajalt.clikt.core.CliktCommand
import com.github.ajalt.clikt.core.CliktError
import com.github.ajalt.clikt.parameters.arguments.argument
import com.github.ajalt.clikt.parameters.options.default
import com.github.ajalt.clikt.parameters.options.flag
import com.github.ajalt.clikt.parameters.options.option
import com.github.ajalt.clikt.parameters.options.required
import com.github.ajalt.clikt.parameters.types.double
import com.github.ajalt.clikt.parameters.types.int
import com.github.ajalt.clikt.parameters.types.long
import computer.whatthefuck.arcology.capture.CaptureService
import computer.whatthefuck.arcology.db.ArcologyDatabase
import computer.whatthefuck.arcology.database.DatabaseFactory
import computer.whatthefuck.arcology.database.RoamRepositoryImpl
import computer.whatthefuck.arcology.indexer.FileIndexResult
import computer.whatthefuck.arcology.indexer.IndexingConfig
import computer.whatthefuck.arcology.indexer.JvmFileSystem
import computer.whatthefuck.arcology.indexer.createIndexingService
import computer.whatthefuck.arcology.parser.OrgFileParser
import computer.whatthefuck.arcology.parser.ParseResult
import computer.whatthefuck.arcology.search.SearchService
import io.ktor.client.*
import io.ktor.client.plugins.*
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.ExperimentalCoroutinesApi
import kotlinx.coroutines.runBlocking
import kotlinx.coroutines.withContext
import kotlinx.datetime.LocalDateTime
import kotlinx.datetime.LocalTime
import kotlinx.serialization.Serializable
import kotlinx.serialization.encodeToString
import kotlinx.serialization.json.Json
import kotlinx.serialization.json.JsonArray
import kotlinx.serialization.json.JsonElement
import kotlinx.serialization.json.JsonPrimitive
import kotlinx.serialization.json.buildJsonObject
import kotlinx.serialization.json.put
import xyz.lepisma.orgmode.plainText
import java.io.File
import java.sql.DriverManager
import java.util.Properties
import kotlin.time.Instant

<<tools-imports>>

<<tools-notes-parent>>

<<tools-json>>

<<tools-repository>>

<<tools-base-command>>

<<tools-note-get>>

<<tools-body-renderer>>

<<tools-body-loader>>

<<tools-note-get-command>>

<<tools-note-search>>

<<tools-note-timeline>>

<<tools-note-capture>>

<<tools-note-sql>>

<<tools-sql-runtime>>

package computer.whatthefuck.arcology.llm

import computer.whatthefuck.arcology.db.ArcologyDatabase
import kotlinx.coroutines.test.runTest
import kotlinx.serialization.json.*
import kotlin.io.path.writeText
import kotlin.io.path.absolutePathString
import kotlin.test.*

class NoteToolsTest {

    private fun makeDatabase(): ArcologyDatabase =
        computer.whatthefuck.arcology.database.DatabaseFactory.createInMemoryDatabase()

    /** Seed: root heading, child under it, excluded-tagged sibling, a link. */
    private suspend fun seed(db: ArcologyDatabase) {
        val q = db.arcologyDatabaseQueries
        q.insertFile("/d/notes.org", "notes file", "hash1", 0L, 0L)
        q.insertNode("root", "/d/notes.org", 1, 10, null, null, null, null, "Project Root", "", "Project Root")
        q.insertNode("child-a", "/d/notes.org", 2, 20, null, null, null, null, "Child A", "", "Project Root/Child A")
        q.insertNode("child-b", "/d/notes.org", 2, 30, null, null, null, null, "Child B", "", "Project Root/Child B")
        q.insertNode("unrelated", "/d/notes.org", 1, 40, null, null, null, null, "Unrelated Top", "", "Unrelated Top")
        q.insertNodeAncestor("root", "root")
        q.insertNodeAncestor("child-a", "child-a")
        q.insertNodeAncestor("child-a", "root")
        q.insertNodeAncestor("child-b", "child-b")
        q.insertNodeAncestor("child-b", "root")
        q.insertNodeAncestor("unrelated", "unrelated")
        q.insertTag("child-b", "NOEXPORT")
        q.insertLink(1L, "child-a", "root", "internal", "")
        q.insertTag("root", "project")
    }

    @Test
    fun `node projection omits file column`() = runTest {
        val db = makeDatabase(); seed(db)
        val row = db.toolsQueries.selectToolNodeById("root").executeAsOneOrNull()
        assertEquals("Project Root", row?.title)
        // selectToolNodeById has no `file` column at all — compile-level guarantee.
    }

    @Test
    fun `bracket bounds children and descendants`() = runTest {
        val db = makeDatabase(); seed(db)
        val root = db.toolsQueries.selectToolNodeBracket("root").executeAsOne()
        assertEquals(10L, root.my_pos)
        assertEquals(40L, root.bracket_end) // next level<=1 node after root

        val children = db.toolsQueries.selectToolChildNodes("root")
            .executeAsList().map { it.id }
        // child-b is NOEXPORT-tagged but still enumerable as structure;
        // exclusion filtering happens at the repository/command layer.
        assertEquals(listOf("child-a", "child-b"), children)

        val unrelated = db.toolsQueries.selectToolNodeBracket("unrelated").executeAsOne()
        assertEquals(40L, unrelated.my_pos)
        assertNull(unrelated.bracket_end)
        assertEquals(
            emptyList(),
            db.toolsQueries.selectToolChildNodes("unrelated").executeAsList()
        )
    }

    @Test
    fun `ancestor tags include inherited chain`() = runTest {
        val db = makeDatabase(); seed(db)
        val childTags = db.toolsQueries.selectToolAncestorTags("child-a").executeAsList()
        assertTrue("NOEXPORT" !in childTags)
        assertTrue("project" in childTags) // inherited from root

        val excludedTags = db.toolsQueries.selectToolAncestorTags("child-b").executeAsList()
        assertTrue("NOEXPORT" in excludedTags)
    }

    @Test
    fun `scheduled between matches lexicographic dates`() = runTest {
        val db = makeDatabase(); seed(db)
        db.arcologyDatabaseQueries.insertNode(
            "task-1", "/d/notes.org", 1, 50, null, null, "2026-09-28", null, "Task One", "", "Task One"
        )
        db.arcologyDatabaseQueries.insertNode(
            "task-2", "/d/notes.org", 1, 60, null, null, null, "2026-10-02", "Task Two", "", "Task Two"
        )
        val repo = ToolQueryRepositoryImpl(db)
        val hits = repo.selectScheduledBetween("2026-09-01", "2026-09-30")
        assertEquals(listOf("task-1"), hits.map { it.id })
    }

    @Test
    fun `journal between scopes to daily files`() = runTest {
        val db = makeDatabase(); seed(db)
        db.arcologyDatabaseQueries.insertFile("journals/2026-09-28.org", "journal 28", "h2", 0L, 0L)
        db.arcologyDatabaseQueries.insertNode(
            "entry-1", "journals/2026-09-28.org", 1, 10, null, null, null, null, "Morning entry", "", "Morning entry"
        )
        val repo = ToolQueryRepositoryImpl(db)
        val between = repo.selectJournalNodesBetween("journals/2026-09-01.org", "journals/2026-09-30.org")
        assertEquals(listOf("entry-1"), between.map { it.id })
    }

    @Test
    fun `verdict cache roundtrip and model scoping`() = runTest {
        val repo = ToolQueryRepositoryImpl(makeDatabase())
        repo.insertVerdict("hash1", "llama", RiskScores(health = 0.9), 42L)
        assertEquals(0.9, repo.selectVerdict("hash1", "llama")?.health)
        assertNull(repo.selectVerdict("hash1", "other-model"))
    }

    @Test
    fun `envelope serializes ok and defaults`() {
        val json = ToolJson.encodeToString(
            ToolEnvelope.serializer(),
            ToolEnvelope(ok = true, data = buildJsonObject { put("x", 1) })
        )
        assertTrue(json.contains("\"ok\":true"))
        // encodeDefaults forces explicit risk fields even when null-safe
        assertTrue(json.contains("\"data\""))
    }

    @Test
    fun `parse properties string`() {
        val obj = parsePropertiesString("CATEGORY: project\nID: root")
        assertEquals("project", obj["CATEGORY"]?.jsonPrimitive?.content)
        assertEquals("root", obj["ID"]?.jsonPrimitive?.content)
    }

    // ─── loadLeafBody: document-AST rendering ───────────────────────────
    //
    // Fixture mirrors the file-level-node shape from the planning session:
    // preamble ID, preface text, ID-less headings (inline), a noexport
    // subtree, an ID+noexport heading, and an ID-bearing non-excluded
    // sibling (stub).

    private val fixtureOrg = """
        :PROPERTIES:
        :ID: The_Id
        :END:
        #+TITLE: THe Title

        Some preamble text

        * ID-less heading 1

        bla

        * ID-less heading 2

        ble bla

        * a final heading :noexport:

        some text.,,, under the noexport!

        * heading with ID :ATTACH:noexport:

        this is here to capture file attachments

        * fetchable child
        :PROPERTIES:
        :ID: child-fetchable
        :END:

        child body content
    """.trimIndent()

    private suspend fun seedBodyFixture(): Triple<ArcologyDatabase, java.nio.file.Path, java.nio.file.Path> {
        val tmp = kotlin.io.path.createTempDirectory(prefix = "arcology-bodytest-")
        val orgDir = tmp.resolve("org")
        java.nio.file.Files.createDirectories(orgDir)
        orgDir.resolve("fixture.org").writeText(fixtureOrg)

        val db = makeDatabase()
        db.arcologyDatabaseQueries.insertFile("fixture.org", "The Title", "h1", 0L, 0L)
        db.arcologyDatabaseQueries.insertNode(
            "The_Id", "fixture.org", 0, 0, null, null, null, null, "The Title", "ID: The_Id", ""
        )
        db.arcologyDatabaseQueries.insertNode(
            "child-fetchable", "fixture.org", 1, 10, null, null, null, null,
            "fetchable child", "ID: child-fetchable", "The Title/fetchable child"
        )
        return Triple(db, tmp, orgDir)
    }

    @Test
    fun `file-level body includes preamble and ID-less heading text inline`() = runTest {
        val (db, _, orgDir) = seedBodyFixture()
        val body = loadLeafBody(db, "The_Id", orgDir.toString(), maxBytes = 65536)!!
        assertTrue(body.contains("Some preamble text"), "preamble must render: $body")
        assertTrue(body.contains("bla"), "ID-less heading 1 text inline: $body")
        assertTrue(body.contains("ble bla"), "ID-less heading 2 text inline: $body")
    }

    @Test
    fun `file-level body elides noexport subtree text`() = runTest {
        val (db, _, orgDir) = seedBodyFixture()
        val body = loadLeafBody(db, "The_Id", orgDir.toString(), maxBytes = 65536)!!
        assertFalse(body.contains("under the noexport"), "noexport text must vanish: $body")
        assertFalse(body.contains("this is here to capture file attachments"), "ATTACH:noexport text must vanish: $body")
        assertFalse(body.contains("heading with ID"), "excluded ID-bearing heading leaves no stub: $body")
        assertFalse(body.contains("a final heading"), "excluded heading leaves no stub: $body")
        assertFalse(body.contains("child body content"), "non-excluded ID-bearing sibling is body of its own node, not parent's: $body")
    }

    @Test
    fun `file-level body stubs ID-bearing child headings`() = runTest {
        val (db, _, orgDir) = seedBodyFixture()
        val body = loadLeafBody(db, "The_Id", orgDir.toString(), maxBytes = 65536)!!
        assertTrue(body.contains("* fetchable child [id:child-fetchable]"), "stub line for discovery: $body")
    }

    @Test
    fun `ID-bearing leaf body is own text only, siblings excluded`() = runTest {
        val (db, _, orgDir) = seedBodyFixture()
        val body = loadLeafBody(db, "child-fetchable", orgDir.toString(), maxBytes = 65536)!!
        assertTrue(body.contains("child body content"), "own text: $body")
        assertFalse(body.contains("Some preamble text"), "no preamble leakage: $body")
        assertFalse(body.contains("ble bla"), "no sibling text leakage: $body")
    }

    @Test
    fun `body loader truncates oversized output`() = runTest {
        val tmp = kotlin.io.path.createTempDirectory(prefix = "arcology-bodytest-")
        val huge = (1..500).joinToString("\n") { "line $it filler text" }
        tmp.resolve("big.org").writeText(":PROPERTIES:\n:ID: big_id\n:END:\n\n$huge\n")
        val db = makeDatabase()
        db.arcologyDatabaseQueries.insertFile("big.org", "big", "h", 0L, 0L)
        db.arcologyDatabaseQueries.insertNode(
            "big_id", "big.org", 0, 0, null, null, null, null, "big", "ID: big_id", ""
        )
        val body = loadLeafBody(db, "big_id", tmp.toString(), maxBytes = 200)!!
        assertTrue(body.contains("[truncated at 200 bytes]"))
        assertTrue(body.length < 400)
    }

    @Test
    fun `missing file body returns null`() = runTest {
        val db = makeDatabase()
        db.arcologyDatabaseQueries.insertFile("nowhere.org", "nowhere", "h", 0L, 0L)
        db.arcologyDatabaseQueries.insertNode(
            "ghost", "nowhere.org", 0, 0, null, null, null, null, "ghost", "", ""
        )
        assertNull(loadLeafBody(db, "ghost", kotlin.io.path.createTempDirectory(prefix = "arcology-bodytest-").toString(), maxBytes = 65536))
    }
}

export ARCOLOGY_RISK_ENDPOINT=http://windows:11434
export ARCOLOGY_RISK_MODEL=hermes3:8b

{
  "permission": {
    "bash": {
      "arcology2 notes *": "allow"
    }
  }
}