File size: 22,207 Bytes
9ad4b87
868b75a
7f72224
868b75a
 
9ad4b87
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
d598032
 
 
 
9ad4b87
 
868b75a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1293f2e
 
 
 
 
 
 
868b75a
 
 
 
1293f2e
 
 
868b75a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1293f2e
 
 
868b75a
 
 
 
 
 
 
4ca2251
 
 
868b75a
 
 
 
 
 
d598032
868b75a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9ad4b87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
071ce2b
 
 
 
 
 
 
9ad4b87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
071ce2b
 
9ad4b87
 
868b75a
 
 
 
 
 
 
1293f2e
 
868b75a
 
 
 
1293f2e
868b75a
d598032
9ad4b87
 
 
 
 
 
 
 
071ce2b
868b75a
 
 
 
 
 
 
 
 
 
 
 
 
81a8217
7f72224
9ad4b87
 
 
cf09724
9ad4b87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
"""Sheet actions over the existing workbook permissions and writers."""
from difflib import get_close_matches
import json

from fastapi import HTTPException
from core import external_access, external_tools
from . import legacy_action


WORKBOOK_RESOURCE_URI = "ui://loopable/workbook.html"
WORKBOOK_RESOURCE_MIME = "text/html;profile=mcp-app"
WRITE_ABOUT_EXAMPLE = {
    "title": "Sheet write", "description": "Update the requested workbook cells.", "tags": ["reporting"]}
WRITE_INTENT_EXAMPLE = "Update the requested workbook cells"
WRITE_CALL_EXAMPLE = {
    "action": "write_cells", "arguments": {
        "workbook_id": "<workbook_id>", "tab": "<tab_id>",
        "cells": {"A1": {"kind": "literal", "value": "Weekly review"}}},
    "intent": WRITE_INTENT_EXAMPLE, "about": WRITE_ABOUT_EXAMPLE}
#: The next call after workbook_missing (S79-T11): list readable workbooks, then read one by its id.
WORKBOOK_MISSING_FIX = (
    'Call sheet with {"action": "workbook.list", "arguments": {"limit": 100}} and follow cursor until it is null. '
    'Then call sheet with {"action": "workbook.get", "arguments": {"workbook_id": "<id>"}} using a returned id.')


def _readable_workbooks(ctx):
    """Admit workbook documents before disclosing any indexed identity."""
    from core import sheets

    ctx.session.require("sheets")
    rows = {}
    for identity in sheets.list_workbooks(ctx.session.runtime):
        try:
            book = sheets.get_workbook(ctx.session.runtime, identity["id"], before_normalize=ctx.require_reader)
        except HTTPException as exc:
            if exc.status_code not in (403, 404):
                raise
            continue
        except sheets.SheetRefusal as exc:
            if exc.code not in {"workbook_missing", "workbook_id_invalid"}:
                raise
            continue
        rows[book["id"]] = {key: book[key] for key in ("id", "name", "rev")}
    return sorted(rows.values(), key=lambda row: (row["name"], row["id"]))


def _released_shape(ctx, verb, args):
    """Answer a call in the released verb-in-arguments shape (D-9630) through the legacy object door."""
    if "action" not in args and "arguments" not in args:
        return None
    return external_tools.dispatch(ctx, "workbook_object", {**args, "action": verb})


def workbook_list(ctx, args):
    """Page every currently readable workbook identity without a total result cap."""
    from routes_external import _decode_query_cursor, _encode_query_cursor, _json_digest

    released = _released_shape(ctx, "list", args)
    if released is not None:
        return released
    rows = _readable_workbooks(ctx)
    query = _json_digest({"action": "sheet.workbook.list"})
    snapshot = _json_digest(rows)
    offset = (_decode_query_cursor(ctx.session, args["cursor"], query, snapshot)
              if "cursor" in args else 0)
    limit = args.get("limit", 100)
    page = rows[offset:offset + limit]
    end = offset + len(page)
    cursor = _encode_query_cursor(ctx.session, query, snapshot, end) if end < len(rows) else None
    return {"workbooks": page, "count": len(page), "total": len(rows), "cursor": cursor}


def workbook_get(ctx, args):
    """Find one readable workbook by id or exact name, without fuzzy selection."""
    from core import sheets
    from deps import err

    ctx.session.require("sheets")
    released = _released_shape(ctx, "get", args)
    if released is not None:
        return released
    if ("workbook_id" in args) == ("name" in args):
        raise err(400, "tool_arguments_invalid", "Send either workbook_id or an exact name.")
    if "workbook_id" in args:
        book = external_tools._book(ctx, args)
    else:
        rows = _readable_workbooks(ctx)
        matches = [row for row in rows if row["name"] == args["name"]]
        if not matches:
            # D-9630 kept: an exact name read without its letter case still resolves, never a near name.
            matches = [row for row in rows if row["name"].casefold() == args["name"].casefold()]
        if len(matches) > 1:
            raise err(409, "workbook_name_ambiguous",
                      "More than one readable workbook has this exact name. Use a workbook_id.",
                      {"candidates": matches})
        if not matches:
            names = get_close_matches(args["name"], sorted({row["name"] for row in rows}), n=5)
            raise err(404, "workbook_missing", "No readable workbook has that exact name. " + WORKBOOK_MISSING_FIX,
                      {"suggestions": names})
        book = external_tools._book(ctx, {"workbook_id": matches[0]["id"]})
    projected = ctx.project_book(book)
    return {"workbook": {key: projected[key] for key in ("id", "name", "rev", "sheets")}}


def read_guide():
    """Describe the grouped workbook read shape and available replacements."""
    return ('Workbook discovery example: {"action":"workbook.list","arguments":{"limit":100}}. '
            'Follow cursor until null. Exact lookup: {"action":"workbook.get","arguments":{"name":"Exact workbook name"}}. '
            'Use either name or workbook_id, never both. '
            'Read cells: {"action":"read_cells","arguments":{"workbook_id":"returned-id","tab":"returned-tab-id"}}. '
            'Refresh writes need intent and about beside action and arguments. '
            'A missing or hidden workbook returns workbook_missing. Readable workbooks return their current tabs.')


def write_guide():
    """Publish the metadata, argument shape and signed confirmation required for Sheet writes."""
    return (
        "Every Sheet write needs intent and about beside action and arguments. "
        "If your client sends only action and arguments, put intent and about inside arguments. "
        "Use intent as 1 to 500 characters explaining the write. "
        "Use about with only title (1 to 240 characters), description (20 to 240 characters), "
        "and tags (one or more distinct values). Use no control characters or long dashes in title or description. "
        "Allowed tags: " + ", ".join(external_tools.BUILD_TAGS) + ". "
        "Use workbook_id and tab from workbook.get. Call sheet with "
        + json.dumps(WRITE_CALL_EXAMPLE, separators=(", ", ": ")) + ". "
        "The write returns preview_ready without changing cells. After the user approves, call confirm_change "
        'with {"confirmation_token": "<returned signed token>", "intent": "Apply the approved Sheet write"}. '
        "Confirmation uses the saved about."
    )


def _workbook_view(ctx, args):
    """Project native workbook cells without exposing bindings or private workbook metadata."""
    import copy
    from fastapi import HTTPException
    from core import sheets

    ctx.session.require("sheets")
    book = external_tools._book(ctx, args)
    reason = ""
    try:
        ctx.require_editor(book)
    except (HTTPException, sheets.SheetRefusal):
        reason = "You can read this workbook, but you cannot edit it."
    access, _ = external_access.scope_of({
        "access": ctx.session.claims.get("external_access"),
        "databases": ctx.session.claims.get("external_databases")})
    if access != "readwrite":
        reason = "This connection can read cells, but it cannot edit them."
    projected = ctx.project_book(book)
    tabs = []
    for tab in projected.get("sheets", []):
        cells = {}
        for addr, cell in tab.get("cells", {}).items():
            kind = cell.get("kind")
            blocked = reason
            if kind not in ("literal", "empty") or cell.get("spillParent"):
                blocked = blocked or "This cell is calculated. Edit its source in Loopable."
            # Placed database views are outside this resource's contract.
            binding = cell.get("binding") or {}
            hidden = cell.get("state") == "refused" or kind == "bound" and binding.get("mode") == "view"
            row = {"value": None if hidden else cell.get("value"),
                   "display": "" if hidden else cell.get("display", ""),
                   "editable": not bool(blocked), "reason": blocked}
            if not blocked:
                row["edit"] = {key: copy.deepcopy(cell[key]) for key in
                               ("kind", "value", "text", "style", "validation", "link", "note") if key in cell}
            cells[addr] = row
        tabs.append({"id": tab["id"], "name": tab["name"], "cells": cells})
    return {"workbook": {"id": book["id"], "name": book["name"], "rev": book["rev"],
                         "editable": not bool(reason), "reason": reason, "sheets": tabs}}


def _object_available(operation):
    """Promise only object actions implemented by the compatibility dispatcher."""
    from core import sheet_compat
    return (callable(getattr(sheet_compat, "dispatch_object_operation", None))
            and operation in sheet_compat.OBJECT_OPERATIONS)


GROUP = {
    "name": "sheet", "title": "Sheets",
    "guide": ("Read and change workbooks, tabs, cells, styles and charts. "
              + external_tools.CELL_FIGURE_RULE + " Read loopable_guide(group='sheet') for actions."),
    "actions": {
        name.removeprefix("sheet_"): legacy_action(
            name, destructive=name in {"sheet_delete_tab", "sheet_write_cells", "sheet_structure"})
        for name in external_tools.TOOLS if name.startswith("sheet_")
    },
}
for name, actions in external_tools._SHEET_OBJECT_ACTIONS.items():
    for action in actions:
        GROUP["actions"][name.removesuffix("_object") + "." + action] = legacy_action(
            name, action=action, read=action in external_tools._OBJECT_READ_ACTIONS[name],
            destructive=action in {"delete", "clear"})
        operation = name.removesuffix("_object") + "." + action
        GROUP["actions"][operation]["available"] = lambda operation=operation: _object_available(operation)
GROUP["actions"]["read_batch"] = legacy_action("validated_batch", read=True)
GROUP["actions"]["write_batch"] = legacy_action("validated_batch", read=False, destructive=True)
for name, handler, properties in (
        ("workbook.list", workbook_list, {
            "limit": {"type": "integer", "minimum": 1, "maximum": 500},
            "cursor": {"type": "string", "minLength": 1}}),
        ("workbook.get", workbook_get, {
            "workbook_id": {"type": "string", "minLength": 1},
            "name": {"type": "string", "minLength": 1}})):
    # The released D-9630 shape stays accepted: the verb inside arguments, its options nested.
    properties = {**properties, "action": {"enum": [name.rsplit(".", 1)[-1]]}, "arguments": {"type": "object"}}
    GROUP["actions"][name] = {
        "read": True, "destructive": False, "confirm": False, "grants": ("sheets",), "handler": handler,
        "schema": {"type": "object", "description": handler.__doc__, "properties": properties,
                   "additionalProperties": False},
        "available": lambda operation=name: _object_available(operation),
    }
GROUP["actions"]["workbook.get"]["example"] = {"workbook_id": "<workbook_id>"}
GROUP["actions"]["workbook.view"] = {
    "read": True, "destructive": False, "confirm": True, "grants": ("sheets",),
    "handler": _workbook_view, "_meta": {"ui": {"resourceUri": WORKBOOK_RESOURCE_URI}},
    "schema": {"type": "object", "description": "Open one workbook in the workbook view.",
               "properties": {"workbook_id": {"type": "string", "minLength": 1}},
               "required": ["workbook_id"], "additionalProperties": False},
}
GROUP["guide"] += " Use workbook.view with workbook_id to open the workbook view."
GROUP["guide"] += (
    " Use workbook.list with optional limit and cursor until cursor is null to find all readable workbooks. "
    "Use workbook.get with either workbook_id or an exact name to read projected tabs and cells. "
    "Duplicate exact names require an id. Near names return at most five readable suggestions. "
    "Use returned workbook and tab ids for read_cells. "
    "Actions marked not supported here refuse before writing. Their schemas do not promise support. "
    "Use create_workbook for workbook.create, add_tab for worksheet.add, and delete_tab for worksheet.delete. "
    "Use workbook.get for worksheet.list or worksheet.get, and read_cells for range.read. "
    "Use write_cells for range.write_values, range.write_formulas or range.clear. "
    "Use set_styles for range.write_styles or range.number_format, and set_dimensions for worksheet.geometry. "
    "Use worksheet.rename, worksheet.reorder or worksheet.duplicate where available. "
    "Use refresh_scope for calculation.refresh. Refresh can contact sources and requires confirmation. "
    "Use write_batch for a checked sequence of supported writes. "
    "Workbook export and named ranges have no grouped replacement in this build.")
GROUP["guide"] += ' Under MCP rule 9, ask "Do you want me to build a Sheet workbook in Loopable?".'
GROUP["guide"] += " " + write_guide()
_BATCH_GUIDE = (
    " A batch has 1 to 50 operations for one workbook. Use name and arguments for each operation. "
    "Use sheet_add_tab, sheet_write_cells, sheet_set_binding, sheet_set_styles or sheet_set_dimensions "
    "with their published arguments. A sheet_write_cells preview of 250 or more changes shows counts, ranges and a sample. confirm_change writes every cell. "
    "Range actions use name='range_object', with action, workbook_id, worksheet and an A1 range. "
    "Put values inside arguments for write_values, write_formulas, write_styles or number_format. "
    "For worksheet_object.add, put name inside arguments. For worksheet_object.geometry, "
    "put column_width or row_height inside arguments and supply a range. "
    "Later operations can name a newly added tab. After sheet_create_workbook, omit workbook_id "
    "to use that workbook. Each supplied rev or revision refers to the workbook before the batch. "
    "All operations are checked before one preview. confirm_change saves one workbook revision. "
    "If any operation fails, nothing changes and the reply names its position. "
    "Direct number writes keep the same advice and optional figure_sources as write_cells."
)
for action in ("read_batch", "write_batch"):
    GROUP["actions"][action]["schema"]["description"] = _BATCH_GUIDE.strip()


def _capability(name):
    """Resolve C8 at call time so an absent lane never prevents group discovery."""
    from core import sheets
    return getattr(sheets, name, None)


def _write_book(ctx, args):
    """Admit the current editor before comparing the workbook revision."""
    from core import sheets
    ctx.session.require("sheets")
    book = external_tools._book(ctx, args, write=True)
    if args["rev"] != book["rev"]:
        raise sheets.SheetStaleRefusal(
            "workbook_revision_stale", "The workbook changed. Read it and preview the change again.",
            rev_current=book["rev"])
    return book


def _binding_scope(ctx, book):
    """Refuse excluded source bindings before a provider can follow workbook dependencies."""
    from core import sheets
    claims = ctx.session.claims
    _, databases = external_access.scope_of({
        "access": claims.get("external_access"), "databases": claims.get("external_databases")})
    for tab in book.get("sheets", []):
        for cell in tab.get("cells", {}).values():
            binding = cell.get("binding") if isinstance(cell, dict) else None
            if isinstance(binding, dict) and not external_access.database_allowed(databases, binding.get("database")):
                raise sheets.SheetRefusal(
                    "key_database_out_of_scope", "This connection cannot access a database used by this workbook.")


def refresh_sources(session, book):
    """Check stored source dependencies without resolving or changing any cells."""
    import routes_sheet
    from core import sheets
    from modules import sheet as sheet_mod

    _, databases = external_access.scope_of({
        "access": session.claims.get("external_access"),
        "databases": session.claims.get("external_databases")})
    bindings = []
    for tab in book.get("sheets", []):
        for cell in tab.get("cells", {}).values():
            if cell.get("kind") != "bound":
                continue
            try:
                binding = sheet_mod.clean_binding(cell.get("binding"))
            except sheet_mod.SheetRefusal as exc:
                raise sheets.SheetRefusal(exc.code, exc.message) from exc
            database = binding["database"]
            if not external_access.database_allowed(databases, database):
                raise sheets.SheetRefusal("key_database_out_of_scope",
                    "This connection cannot access a database used by this workbook.")
            routes_sheet._require_database(session, database)
            nodes, _ = routes_sheet._binding_filter_nodes(session, database, binding)
            for via in sheet_mod.related_vias(nodes):
                target = routes_sheet._binding_related_entry(session, database, via)["target"]
                if not external_access.database_allowed(databases, target):
                    raise sheets.SheetRefusal("key_database_out_of_scope",
                        "This connection cannot access a database used by this workbook.")
            bindings.append((binding, tab["id"]))
    return bindings


def refresh_access(ctx, args):
    """Admit the workbook and every source before a refresh can stage any changes."""
    book = _write_book(ctx, args)
    for binding, tab_id in refresh_sources(ctx.session, book):
        # Resolve through the native row and field walls without saving a snapshot.
        ctx.resolve_binding(binding, book, tab_id)
    return book


def _rename(ctx, args):
    """Rename through the compatibility contract and native revision guard."""
    from core import sheet_compat, sheets
    book = _write_book(ctx, args)
    tab = sheets.find_tab(book, args["tab"])
    return sheet_compat.execute(ctx.session.runtime, book["id"], "worksheet.rename", {
        "sheet": tab["id"], "name": args["name"], "expected_revision": args["rev"]},
        actor=ctx.session.uname, actor_name=ctx.actor_name)


def _foreign(ctx, args, name):
    """Call the exact C8 signature with the admitted runtime and current actor."""
    from core import sheets
    book = _write_book(ctx, args)
    function = _capability(name)
    if not callable(function):
        reason = {"reorder_tab": "Moving tabs is not available in this build.",
                  "duplicate_tab": "Copying tabs is not available in this build.",
                  "refresh_scope": "Refreshing cells is not available in this build."}[name]
        raise sheets.SheetRefusal("capability_unavailable", reason)
    if name == "refresh_scope":
        if ctx.session.claims.get("external_access") == external_access.ACCESS_REFRESH:
            refresh_access(ctx, args)
        else:
            _binding_scope(ctx, book)
        return function(ctx.session.runtime, book["id"], args["scope"], ctx.session)
    tab = sheets.find_tab(book, args["tab"])
    if name == "duplicate_tab":
        _binding_scope(ctx, book)
        return function(ctx.session.runtime, book, tab["id"], args["name"], ctx.session)
    return function(ctx.session.runtime, book, tab["id"], args["index"], ctx.session)


def _receipt_access(ctx, args):
    """Read current workbook access without invoking a writer on receipt replay."""
    ctx.session.require("sheets")
    external_tools._book(ctx, args)
    return True


def _write_action(description, properties, required, handler, capability=None):
    entry = {"read": False, "destructive": False, "confirm": True, "grants": ("sheets",),
             "handler": handler, "receipt_access": _receipt_access,
             "schema": {"type": "object", "description": description + " Preview first, then call confirm_change.",
                        "properties": {
                            "workbook_id": {"type": "string", "minLength": 1},
                            "rev": {"type": "integer", "minimum": 0,
                                    "description": "The workbook revision from your latest read."},
                            **properties}, "required": ["workbook_id", "rev", *required],
                        "additionalProperties": False}}
    if capability:
        entry["available"] = lambda: callable(_capability(capability))
    return entry


_TAB = {"type": "string", "minLength": 1, "description": "The tab id or current name."}
_NAME = {"type": "string", "minLength": 1, "maxLength": 120, "pattern": "^[^']+$"}
_SCOPE = {"oneOf": [
    {"type": "object", "properties": {"cell": {
        "type": "string", "pattern": "^.{1,80}![A-Z]{1,3}[1-9][0-9]{0,6}$",
        "description": "A stable tab id and cell address, such as tab-id!A1."}},
     "required": ["cell"], "additionalProperties": False},
    {"type": "object", "properties": {"range": {
        "type": "string", "pattern": "^.{1,80}![A-Z]{1,3}[1-9][0-9]{0,6}:[A-Z]{1,3}[1-9][0-9]{0,6}$",
        "description": "A stable tab id and inclusive range, such as tab-id!A1:B3."}},
     "required": ["range"], "additionalProperties": False},
    {"type": "object", "properties": {"workbook": {"const": True}},
     "required": ["workbook"], "additionalProperties": False},
]}
GROUP["actions"].update({
    "worksheet.rename": _write_action("Rename a tab and update formulas that name it.",
        {"tab": _TAB, "name": _NAME}, ("tab", "name"), _rename),
    "worksheet.reorder": _write_action("Move a tab to a zero-based final position.",
        {"tab": _TAB, "index": {"type": "integer", "minimum": 0}}, ("tab", "index"),
        lambda ctx, args: _foreign(ctx, args, "reorder_tab"), "reorder_tab"),
    "worksheet.duplicate": _write_action("Copy a tab with its formulas, styles and cell databases.",
        {"tab": _TAB, "name": _NAME}, ("tab", "name"),
        lambda ctx, args: _foreign(ctx, args, "duplicate_tab"), "duplicate_tab"),
    "refresh_scope": _write_action("Refresh one cell, one range or the workbook and its dependent cells.",
        {"scope": _SCOPE}, ("scope",),
        lambda ctx, args: _foreign(ctx, args, "refresh_scope"), "refresh_scope"),
})