File size: 52,706 Bytes
c1fc133
 
 
 
 
 
 
 
816fa8a
 
c1fc133
103a492
2e7e617
c1fc133
2eae9f0
c1fc133
 
816fa8a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
103a492
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
c1fc133
 
 
 
 
 
 
 
 
 
 
2e7e617
c1fc133
2e7e617
c1fc133
 
2e7e617
 
 
 
c1fc133
 
 
 
 
 
 
 
 
 
2eae9f0
 
 
 
 
 
 
c1fc133
 
 
 
 
2eae9f0
c1fc133
 
dec006b
 
 
 
 
 
 
 
c1fc133
dec006b
c1fc133
 
 
dec006b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8729c1d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8d3a743
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
 
 
 
 
 
 
 
 
 
2eae9f0
 
 
 
 
 
 
 
816fa8a
 
 
 
 
 
 
 
 
 
 
 
 
 
2eae9f0
 
 
c1fc133
 
2eae9f0
 
 
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
c1fc133
 
 
 
dec006b
 
c1fc133
 
07a68d0
 
 
f462120
95fb175
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
c1fc133
 
 
 
2eae9f0
 
 
 
 
 
 
 
 
 
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
816fa8a
 
 
 
 
 
 
 
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
c1fc133
 
 
 
 
 
 
 
 
 
 
2eae9f0
c1fc133
 
 
 
2e7e617
 
 
 
 
816fa8a
2e7e617
 
 
 
 
8873406
2e7e617
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
 
2e7e617
 
 
 
 
 
 
 
 
 
c1fc133
 
 
 
 
2eae9f0
 
 
816fa8a
c1fc133
816fa8a
 
 
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
c1fc133
 
 
 
 
 
 
 
 
 
 
816fa8a
8729c1d
 
 
816fa8a
c1fc133
2eae9f0
2e7e617
 
 
 
 
8729c1d
2e7e617
 
 
 
 
 
 
 
 
ce9586e
 
 
 
 
 
 
 
8729c1d
 
2e7e617
 
 
2eae9f0
c1fc133
 
 
 
 
2e7e617
 
c1fc133
2eae9f0
2e7e617
816fa8a
2e7e617
 
 
 
 
 
 
2eae9f0
816fa8a
c1fc133
 
2eae9f0
c1fc133
 
 
 
 
2eae9f0
c1fc133
 
 
2eae9f0
2e7e617
8873406
2e7e617
 
 
8873406
 
 
 
2e7e617
 
 
 
 
 
 
2eae9f0
2e7e617
816fa8a
2e7e617
 
 
 
 
 
 
 
 
 
 
 
 
 
2eae9f0
2e7e617
 
 
 
 
 
 
 
 
 
 
2eae9f0
2e7e617
816fa8a
 
2e7e617
 
816fa8a
c1fc133
 
 
2e7e617
 
 
 
 
 
 
 
 
 
103a492
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
dec006b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8729c1d
816fa8a
dec006b
 
 
816fa8a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8729c1d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
8729c1d
 
 
816fa8a
 
 
 
 
8729c1d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
 
 
 
 
dec006b
 
c1fc133
2eae9f0
 
 
 
 
 
c1fc133
8729c1d
 
c1fc133
 
 
 
 
 
 
 
2e7e617
 
c1fc133
 
 
2e7e617
 
 
c1fc133
 
 
 
 
2e7e617
 
c1fc133
 
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
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
"""External build tools over the app's Sheet, saved View and Application writers.

The API supplies its existing permission, read-projection and binding-resolution functions as
request-scoped callbacks. Core never imports API modules or chooses another tenant runtime.
Adding a tool means adding a registry entry; writes also require the router's literal allow-list.
"""
import copy
import json
import math
import re
from dataclasses import dataclass
from pathlib import Path
from typing import Callable, Optional

from core import perm_scope, sheets, sheet_structure, sheet_templates, write_execution


# Keep supported versions in release order, with the newest last.
MCP_SUPPORTED_PROTOCOL_VERSIONS = ("2025-06-18",)


def negotiate_mcp_protocol(requested=None) -> str:
    """Return the requested supported version or the newest supported version."""
    if requested is not None and not isinstance(requested, str):
        raise ValueError("The protocol version must be a string.")
    if requested in MCP_SUPPORTED_PROTOCOL_VERSIONS:
        return requested
    return MCP_SUPPORTED_PROTOCOL_VERSIONS[-1]


CELL_FIGURE_RULE = (
    "A figure a database can supply goes in a cell database or a calculation object, "
    "and a typed value is for a title, a label or an assumption."
)

BUILD_TAGS = tuple(json.loads(
    (Path(__file__).resolve().parents[1] / "build_tags.json").read_text(encoding="utf-8")))
_BUILD_TEXT = r"^(?=.*\S)[^\u0000-\u001f\u007f\u0085\u2013\u2014\u2028\u2029]+(?![\s\S])"
ABOUT_SCHEMA = {
    "type": "object", "additionalProperties": False,
    "properties": {
        "title": {"type": "string", "minLength": 1, "maxLength": 240, "pattern": _BUILD_TEXT},
        "description": {"type": "string", "minLength": 20, "maxLength": 240, "pattern": _BUILD_TEXT},
        "tags": {"type": "array", "minItems": 1, "uniqueItems": True,
                 "items": {"type": "string", "enum": list(BUILD_TAGS)}},
    }, "required": ["title", "description", "tags"],
}


@dataclass(frozen=True)
class ToolContext:
    session: object
    actor_name: str
    require_reader: Callable
    require_editor: Callable
    project_book: Callable
    resolve_binding: Callable
    cache_binding: Callable
    list_databases: Callable
    describe_database: Callable
    query_database: Callable
    view_event: Callable
    view_share_preview: Callable
    app_upsert: Callable
    app_delete: Callable
    database_object: Optional[Callable] = None
    sheet_object: Optional[Callable] = None
    validated_batch: Optional[Callable] = None
    confirm_change: Optional[Callable] = None


def _object(properties=None, required=(), **extra):
    return {"type": "object", "properties": properties or {}, "required": list(required), **extra}


_STRING = {"type": "string"}
_ADDRESS = {"type": "string", "description": "An A1 cell address on the named tab."}
_FORMAT = _object({"kind": _STRING, "code": _STRING, "decimals": {"type": "integer"},
                   "currency": _STRING, "thousands": {"type": "boolean"}}, ("kind",))
# S67-T14 / contract C2: a border side is the legacy `true` (read as a thin black line) or
# `{style, color}` with one of Excel's seven line styles and a six digit hex colour; `false` or an
# absent side is no side. `sheets.py::_clean_border_side` refuses anything else by code, and this
# publishes the same shape so an AI client can draw a styled border at all.
_BORDER_SIDE = {"oneOf": [{"type": "boolean"}, _object({
    "style": {"enum": list(sheets.CELL_BORDER_STYLES)},
    "color": {"type": "string", "pattern": "^#[0-9A-Fa-f]{6}$"}}, ("style", "color"))]}
_STYLE = _object({
    **{key: {"type": "boolean"} for key in ("bold", "italic", "underline", "strike", "wrap")},
    **{key: _STRING for key in ("fill", "color", "font", "align", "valign")},
    "size": {"type": ["number", "string"]}, "indent": {"type": "integer"},
    "textRotation": {"type": "number"}, "format": _FORMAT,
    "border": _object({side: _BORDER_SIDE for side in ("top", "right", "bottom", "left")}),
})
_VALIDATION = _object({"list": {"type": "array", "items": _STRING}}, ("list",))
# Mirror the grid vocabulary without importing the API. verify_api checks parity.
FILTER_OPS = frozenset({
    "contains", "doesNotContain", "eq", "neq", "isEmpty", "isNotEmpty",
    "gt", "gte", "lt", "lte", "between", "within",
    "topN", "bottomN", "inTopPct", "inBottomPct", "aboveAvg", "belowAvg",
    "inQuartile", "inDecile", "isAnyOf", "isNoneOf", "hasAnyOf", "hasAllOf", "hasNoneOf",
})
_FILTER_OP = {"type": "string", "enum": sorted(FILTER_OPS)}
_FILTER = _object({"conj": {"enum": ["and", "or"]}, "nodes": {"type": "array", "items": _object({
    "colId": _STRING, "op": _FILTER_OP, "value": {},
    "valueRef": _object({"ref": _ADDRESS}, ("ref",)),
    "conj": _STRING, "children": {"type": "array", "items": {"type": "object"}},
})}})


def _query_filter_node(depth=1, *, allow_link=True):
    """Publish bounded query nodes without adding link conditions to saved View bindings."""
    leaf = _object({
        "id": _STRING, "colId": _STRING, "op": _FILTER_OP, "value": {}, "value2": {},
        "values": {"type": "array", "items": _STRING, "minItems": 1, "maxItems": 100},
        "rhs": _object({"kind": {"enum": ["field"]}, "colId": _STRING}, ("kind", "colId"),
                       additionalProperties=False),
    }, ("colId", "op"), additionalProperties=False)
    nodes = [leaf]
    if depth < 3:
        nodes.append(_object({"conj": {"enum": ["and", "or"]}, "children": {
            "type": "array", "minItems": 1, "maxItems": 50,
            "items": _query_filter_node(depth + 1, allow_link=allow_link),
        }}, ("conj", "children"), additionalProperties=False))
    if allow_link:
        nodes.append(_object({
            "link": _STRING,
            "where": _query_filter_node(allow_link=False),
        }, ("link", "where"), additionalProperties=False,
            description="One link condition per call. The target database and join fields must be readable. "
                        "Use AND to intersect this pin with source filters."))
    return {"oneOf": nodes}


_QUERY_FILTER_NODE = _query_filter_node()
_QUERY_FILTER = {"oneOf": [
    _object({"conj": {"enum": ["and", "or"]}, "nodes": {
        "type": "array", "minItems": 1, "maxItems": 50, "items": _QUERY_FILTER_NODE,
    }}, ("conj", "nodes"), additionalProperties=False),
    {"type": "array", "minItems": 1, "maxItems": 50, "items": _QUERY_FILTER_NODE},
    _object({"link": _STRING, "where": _query_filter_node(allow_link=False)},
            ("link", "where"), additionalProperties=False),
]}
_CLIENT_QUERY_LEAF = {"anyOf": [
    _QUERY_FILTER_NODE,
    _object({"field": _STRING, "op": _FILTER_OP, "value": {}},
            ("field", "op"), additionalProperties=False),
    _object({"field": _STRING, "operator": {"enum": ["="]}, "value": {}},
            ("field", "operator"), additionalProperties=False),
]}
_CLIENT_QUERY_NODES = {"type": "array", "minItems": 1, "maxItems": 50,
                       "items": _CLIENT_QUERY_LEAF}
_CLIENT_QUERY_FILTER = {"anyOf": [
    _QUERY_FILTER, _CLIENT_QUERY_LEAF, _CLIENT_QUERY_NODES,
    *[_object({conj: _CLIENT_QUERY_NODES}, (conj,), additionalProperties=False)
      for conj in ("and", "or")],
    _object(additionalProperties={"type": ["string", "number", "boolean", "null"]}),
]}
FILTER_NODE_REF = "#/$defs/filter_node"
FILTER_NODE_EXAMPLE = {"conj": "and", "nodes": [
    {"colId": "status", "op": "eq", "value": "open"},
    {"conj": "or", "children": [{"colId": "amount", "op": "gt", "value": 100},
                                {"colId": "region", "op": "eq", "value": "West"}]},
]}


def _is_filter_node(value):
    """Tell a published query filter node from any other schema."""
    branches = value.get("oneOf") if isinstance(value, dict) and set(value) == {"oneOf"} else None
    first = branches[0] if isinstance(branches, list) and branches and isinstance(branches[0], dict) else {}
    properties = first.get("properties")
    return isinstance(properties, dict) and "colId" in properties and "op" in properties


def guide_schema(schema):
    """Return a guide copy with each query filter node replaced by one reference, and whether any was."""
    if _is_filter_node(schema):
        return {"$ref": FILTER_NODE_REF}, True
    if isinstance(schema, list):
        pairs = [guide_schema(item) for item in schema]
        return [item for item, _ in pairs], any(used for _, used in pairs)
    if not isinstance(schema, dict):
        return copy.deepcopy(schema), False
    pairs = {key: guide_schema(child) for key, child in schema.items()}
    return {key: item for key, (item, _) in pairs.items()}, any(used for _, used in pairs.values())


def filter_node_guide():
    """Return the one markdown section that defines the filter_node reference in a guide."""
    node = {"oneOf": [guide_schema(branch)[0] for branch in _QUERY_FILTER_NODE["oneOf"]]}
    return ("## filter_node\n\n"
            "A filter_node is a condition {colId, op, ...}, a group {conj, children: 1 to 50 filter_node}, "
            "or a link {link, where}.\n"
            "Groups nest at most two deep inside one node. A link's where holds no link.\n"
            "Example filter: `" + json.dumps(FILTER_NODE_EXAMPLE, ensure_ascii=True) + "`\n"
            "```json\n" + json.dumps({"$defs": {"filter_node": node}}, ensure_ascii=True, indent=2) + "\n```")


_BINDING = _object({
    "database": _STRING,
    "aggregate": _object({"fn": _STRING, "field": _STRING, "metric_key": _STRING}),
    "scope": _object({"filter": _FILTER, "view_id": _STRING, "view_name": _STRING}),
    "period": _object({"unit": _STRING, "start": _STRING}),
    "ending": {"type": "object"},
}, ("database", "aggregate", "scope"))
_RANGE = {"oneOf": [_STRING, _object({key: {"type": "integer", "minimum": 0}
                                     for key in ("col0", "row0", "col1", "row1")},
                                    ("col0", "row0", "col1", "row1"))]}
# S67-T18: an in-cell sparkline, `{kind, range}` exactly as `sheets.py::_clean_sparkline` stores
# it; the range is a rectangle on the SAME tab (an A1 range such as "B2:M2", or the dict).
_SPARKLINE = _object({"kind": {"enum": list(sheets.SPARKLINE_KINDS)}, "range": _RANGE},
                     ("kind", "range"))
_CELL = _object({"kind": {"enum": ["literal", "formula", "bound", "empty"]},
                 "value": {}, "text": _STRING, "formula": _STRING, "state": _STRING,
                 "style": _STYLE, "validation": _VALIDATION, "binding": _BINDING,
                 "sparkline": _SPARKLINE}, ("kind",))
_FIGURE_SOURCES = {
    "type": "object",
    "description": (
        "Optional fields used together for each typed number, keyed by cell address. "
        "List fields from both databases when the figure needs a join. "
        "The preview names the join limit only when at least two visible databases are listed "
        "and the workbook already reads one of them. This does not create a binding or a join."
    ),
    "additionalProperties": {
        "type": "array", "minItems": 1, "maxItems": 8,
        "items": _object({"database": _STRING, "field": _STRING}, ("database", "field"),
                         additionalProperties=False),
    },
}
# S67-T13: a colour scale's two ends and what each end means. `sheets.py::_clean_rules` refuses
# a type outside `SHEET_RULE_SCALE_TYPES`; the enum publishes the same list to the caller.
_SCALE_TYPE = {"enum": list(sheets.SHEET_RULE_SCALE_TYPES)}
_RULE = _object({"id": _STRING, "range": _RANGE, "kind": _STRING, "op": _STRING,
                 "value": {}, "value2": {}, "formula": _STRING, "style": _STYLE,
                 "scale": _object({"low": _STRING, "high": _STRING,
                                   "minType": _SCALE_TYPE, "maxType": _SCALE_TYPE})},
                ("range", "op"))
_CHART = _object({"id": _STRING, "kind": _STRING, "range": _RANGE, "title": _STRING,
                  "stacking": _STRING, "source": {"type": "object"},
                  "series": {"type": "array", "items": {"type": "object"}},
                  "geometry": {"type": "object"}, "anchor": {"type": "object"},
                  "settings": {"type": "object"}}, ("kind", "range"))
_TARGET = {"workbook_id": _STRING,
           "tab": {"type": "string", "description": "Tab id or tab name."},
           "rev": {"type": "integer", "description": "Optional expected workbook revision."}}
_VIEW_CONFIG = _object({
    "filters": {"oneOf": [_FILTER, {"type": "array", "items": {"type": "object"}}]},
    "filterConj": {"enum": ["and", "or"]},
    "sorts": {"type": "array", "items": _object({
        "colId": _STRING, "dir": {"enum": ["asc", "desc"]},
        "locked": {"type": "boolean"}}, ("colId", "dir"))},
    "groupBy": {"type": ["string", "null"]},
    "visible": {"type": "array", "items": _STRING,
                "description": "Fields to show. Fields omitted from this list are hidden."},
    "order": {"type": "array", "items": _STRING},
    "display": _object({"mode": _STRING}, ("mode",)),
}, description="The View's filters, sorting, visible fields and display settings.")
_APPLICATION = {
    "app_id": {"type": "string", "minLength": 1,
               "description": "Omit to create. Supply an id to update mutable properties."},
    "name": _STRING,
    # Mirrors application_registry.APPLICATION_TYPES (core may not import the API layer).
    "type": {"enum": ["chart", "route", "map", "code"]},
    "compatibleDatabases": {"type": "array", "items": _STRING},
    "sourceDatabase": {"type": "string", "description": "Source for a template Application only."},
    # Mirrors aios-web/api/app_templates.TEMPLATES kinds; api_app_templates holds the parity.
    "templateKind": {"enum": ["route", "catalog", "competitor_watch", "ranking_diagnostic",
                              "amazon_operator", "restock_planner", "reimbursements", "aged_stock",
                              "opportunity_finder", "returns_refunds", "repeat_buyers", "payouts", "account_health",
                              "amazon_dashboard", "amazon_profit", "alerts", "reports"]},
    "fieldMap": {"type": "object", "additionalProperties": _STRING},
    "navPlacement": _object({"section": {"const": "app"},
                             "order": {"type": "integer", "minimum": 0, "maximum": 999}},
                            ("section", "order"), additionalProperties=False),
    "semanticRoleBindings": {"type": "object"},
    "config": _object({"schemaVersion": {"const": 1},
                       "display": _object({"mode": _STRING}, ("mode",))},
                      ("schemaVersion", "display"), additionalProperties=False),
    "share": _object({"mode": {"enum": ["private", "users", "tenant"]},
                      "users": {"type": "array", "items": _STRING}}, ("mode",)),
    "starred": {"type": "boolean"},
}


def _schema(properties, required=(), *, tab=True):
    return _object({**_TARGET, **properties},
                   ("workbook_id", *(("tab",) if tab else ()), *required),
                   additionalProperties=False)


def _book(ctx, args, *, write=False):
    book = sheets.get_workbook(ctx.session.runtime, args["workbook_id"])
    (ctx.require_editor if write else ctx.require_reader)(book)
    return book


def _saved(book, tab=None):
    out = {"id": book["id"], "rev": book["rev"],
           "tabs": [{"id": row["id"], "name": row["name"]} for row in book["sheets"]]}
    if tab is not None:
        out["tab"] = tab
    return out


def _put(ctx, args, book, tab=None):
    # Carry the revision read for this document patch even when the client omitted one.
    # Never overwrite an intervening UI save with the older snapshot we just patched.
    if "rev" in args:
        book["rev"] = args["rev"]
    return _saved(sheets.put_workbook(ctx.session.runtime, book["id"], book,
                                    actor=ctx.session.uname, actor_name=ctx.actor_name), tab)


def _create(ctx, args):
    return _saved(sheets.create_workbook(ctx.session.runtime, name=args["name"],
                                        created_by=ctx.session.uname,
                                        on_duplicate="refuse"))


def _add_tab(ctx, args):
    book = _book(ctx, args, write=True)
    #: ⛔ A DUPLICATE TAB NAME IS REFUSED, because every other tool addresses a tab by `tab`,
    #: which `sheets.find_tab` resolves BY NAME to the FIRST match. Two tabs sharing a name make
    #: every later write land on one of them and the other unreachable, with nothing reported.
    #: Measured at SDLC 61's C7 build: a retried `sheet_add_tab` produced a second
    #: `Summary (All)` and the workbook's cells split silently across the pair.
    wanted = str(args["name"]).strip()
    if any(str(sheet.get("name") or "").strip().casefold() == wanted.casefold()
           for sheet in (book.get("sheets") or ())):
        raise sheets.SheetRefusal(
            "tab_name_taken",
            "Tab " + wanted + " is already in this workbook. Choose another name.")
    tab = {"id": write_execution.new_id()[:12], "name": args["name"], "cells": {}}
    book["sheets"].append(tab)
    return _put(ctx, args, book, tab["id"])


def _delete_tab(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    if len(book["sheets"]) <= 1:
        raise sheets.SheetRefusal(
            "tab_last", "A workbook keeps at least one sheet, so this one cannot be deleted.")
    book["sheets"] = [row for row in book["sheets"] if row["id"] != tab["id"]]
    return _put(ctx, args, book)


def _save_template(ctx, args):
    book = ctx.project_book(_book(ctx, args))
    record = sheet_templates.save_sheet_template(
        ctx.session.runtime, book, args["key"], name=args["name"],
        description=args["description"], created_by=ctx.session.uname, actor_name=ctx.actor_name)
    return {"template": record}


def _read_cells(ctx, args):
    book = ctx.project_book(_book(ctx, args))
    tab = sheets.find_tab(book, args["tab"])
    if "addresses" in args:
        wanted = {addr.strip().upper() for addr in args["addresses"]}
        tab = {**tab, "cells": {addr: cell for addr, cell in tab["cells"].items() if addr in wanted}}
    return {"id": book["id"], "rev": book["rev"], "tab": tab}


def _patch(ctx, args, cells, *, resolved=None):
    out = sheets.patch_cells(ctx.session.runtime, args["workbook_id"], args["tab"], cells,
                             expect_rev=args.get("rev"), actor=ctx.session.uname,
                             actor_name=ctx.actor_name)
    if resolved is not None:
        for addr in out["accepted"]:
            cell = cells[addr]
            ctx.cache_binding(args["workbook_id"], out["tab"], addr, cell["binding"], resolved)
    # The UI computes after its patch too. Project before computing so a shared workbook
    # never returns its owner's cached database numbers through an editor's write response.
    book = ctx.project_book(_book(ctx, args))
    tab = sheets.find_tab(book, args["tab"])
    out["computed"] = {addr: cell for addr, cell in tab["cells"].items()
                       if cell.get("kind") in ("formula", "bound") or "spillParent" in cell}
    return out


def _cell_figure_hints(ctx, args, book):
    """Suggest visible source fields without inferring where a typed number came from."""
    numbers = [addr.strip().upper() for addr, cell in args["cells"].items()
               if isinstance(cell, dict) and cell.get("kind") == "literal"
               and type(cell.get("value")) in (int, float)]
    if not numbers:
        return []
    target = sheets.find_tab(book, args["tab"])
    databases = []
    for tab in [target, *(tab for tab in book["sheets"] if tab["id"] != target["id"])]:
        bindings = [cell.get("binding") for cell in tab["cells"].values()
                    if isinstance(cell, dict) and cell.get("kind") == "bound"]
        for binding in [*bindings, tab.get("pivot")]:
            database = binding.get("database") if isinstance(binding, dict) else None
            if database and database not in databases:
                databases.append(database)
    if not databases:
        return []

    schemas = {}

    def describe(database):
        if database not in schemas:
            try:
                schemas[database] = ctx.describe_database(database)
            except Exception:
                # Optional advice omits unavailable metadata and never grants access or blocks a write.
                schemas[database] = {}
        return schemas[database]

    numeric_types = {"int", "integer", "float", "number", "decimal", "currency", "percent", "rating"}
    candidate = None
    for database in databases:
        schema = describe(database)
        field = next((field for field in schema.get("fields", [])
                      if field.get("type") in numeric_types and field.get("label")), None)
        if schema.get("label") and field:
            candidate = (schema["label"], field["label"])
            break
    if candidate is None:
        return []

    sources = {addr.strip().upper(): refs for addr, refs in (args.get("figure_sources") or {}).items()}
    hints = []
    for addr in numbers:
        declared = {}
        for ref in sources.get(addr, []):
            schema = describe(ref["database"])
            field = next((field for field in schema.get("fields", [])
                          if field.get("key") == ref["field"] and field.get("label")), None)
            if schema.get("label") and field:
                declared[ref["database"]] = f'"{field["label"]}" in "{schema["label"]}"'
        if len(declared) >= 2 and set(declared).intersection(databases):
            hint = ("The declared figure uses " + " and ".join(declared.values()) + ". "
                    "A cell database cannot join two databases yet. "
                    "Use separate cell databases and a calculation object, or confirm a typed assumption.")
        else:
            database_label, field_label = candidate
            hint = (f'This workbook reads "{database_label}". Use a cell database with "{field_label}" '
                    f'in "{database_label}" when it supplies this figure. You can still confirm a typed assumption.')
        if hint not in hints:
            hints.append(hint)
    return hints


def _write_cells(ctx, args):
    book = _book(ctx, args, write=True)
    if "figure_sources" in args:
        validate_arguments(args["figure_sources"], _FIGURE_SOURCES)
    hints = _cell_figure_hints(ctx, args, book)
    out = _patch(ctx, args, args["cells"])
    if hints:
        out["warnings"] = hints
    return out


def _set_binding(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    resolved = ctx.resolve_binding(args["binding"], book, tab["id"])
    addr = args["addr"].strip().upper()
    before = tab["cells"].get(addr) or {}
    cell = {key: copy.deepcopy(before[key]) for key in ("style", "validation", "link", "note")
            if key in before}
    cell.update(kind="bound", binding=copy.deepcopy(args["binding"]), state="fresh",
                value=resolved["value"],
                display="" if resolved["value"] is None else str(resolved["value"]))
    out = _patch(ctx, args, {addr: cell}, resolved=resolved)
    out["limits"] = resolved.get("limits") or []
    return out


def _cell_property(ctx, args, property_name, values):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    cells = {}
    for addr, value in values.items():
        cell = copy.deepcopy(tab["cells"].get(addr.strip().upper()) or {"kind": "empty"})
        cell[property_name] = value
        cells[addr] = cell
    return _patch(ctx, args, cells)


def _dimensions(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    tab.update(copy.deepcopy(args["dimensions"]))
    return _put(ctx, args, book, tab["id"])


def _freeze(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    tab["freeze"] = args["freeze"]
    return _put(ctx, args, book, tab["id"])


def _rule(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    tab["rules"] = [*(tab.get("rules") or []), args["rule"]]
    return _put(ctx, args, book, tab["id"])


def _chart(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    chart = copy.deepcopy(args["chart"])
    chart.setdefault("id", write_execution.new_id()[:12])
    tab["charts"] = [row for row in tab.get("charts", []) if row["id"] != chart["id"]] + [chart]
    return {**_put(ctx, args, book, tab["id"]), "chart_id": chart["id"]}


def _structure(ctx, args):
    book = _book(ctx, args, write=True)
    tab = sheets.find_tab(book, args["tab"])
    shifted = sheet_structure.apply_structure(book, tab["id"], args["op"])
    return _put(ctx, args, shifted, tab["id"])


def _tool(description, schema, handler, *, write=False, grants=("ai_apps", "sheets")):
    return {"description": description, "inputSchema": schema, "write": write,
            "grants": grants, "handler": handler}


def _callback(ctx, name, args):
    callback = getattr(ctx, name, None)
    if not callable(callback):
        raise sheets.SheetRefusal(
            "tool_capability_unsupported",
            "This action is not available yet.")
    return callback(args)


_DATABASE_ACTIONS = (
    "list", "describe", "query", "record_create", "record_update", "record_delete",
    "field_create", "field_update", "field_delete", "rename",
)
_VIEW_ACTIONS = ("upsert", "delete", "share_preview")
_SHEET_OBJECT_ACTIONS = {
    "workbook_object": ("list", "get", "create", "named_ranges", "export"),
    "worksheet_object": (
        "list", "get", "add", "update", "delete", "geometry", "hidden_bands",
        "freeze", "gridlines", "named_ranges",
    ),
    "range_object": (
        "read", "write_values", "write_formulas", "write_styles", "number_format",
        "fill_down", "clear",
    ),
    "calculation_object": ("calculate", "refresh"),
}


def _object_tool(name, actions):
    target = {
        "workbook_id": _STRING,
        "worksheet": _STRING,
        "range": _RANGE,
        "revision": {"type": "integer"},
        "arguments": {"type": "object"},
    }
    return _tool(
        "Read or change a Sheet "
        + name.removesuffix("_object") + ".",
        _object({"action": {"enum": list(actions)}, **target}, ("action",),
                additionalProperties=False),
        lambda ctx, args, object_name=name: _callback(
            ctx, "sheet_object", {"object": object_name.removesuffix("_object"), **args}),
        # The action classifier below distinguishes reads. Declaring the surface write-capable
        # keeps the API's literal allow-list fail closed when a new action is added.
        write=True,
    )


TOOLS = {
    "sheet_create_workbook": _tool("Create a private workbook with one initial tab.",
        _object({"name": _STRING}, ("name",), additionalProperties=False), _create, write=True),
    "sheet_add_tab": _tool("Add a blank tab to a workbook.",
        _schema({"name": _STRING}, ("name",), tab=False), _add_tab, write=True),
    "sheet_delete_tab": _tool(
        "Preview deleting a tab by id or name; requires typed confirmation. The last tab cannot be deleted.",
        _schema({}), _delete_tab, write=True),
    "sheet_read_cells": _tool("Read a tab's styles, sizes and figures that you have permission to see.",
        _schema({"addresses": {"type": "array", "items": _ADDRESS}}), _read_cells),
    "sheet_write_cells": _tool("Write cell documents with values or formulas; null deletes a cell. " + CELL_FIGURE_RULE,
        _schema({"cells": {"type": "object", "additionalProperties": {"anyOf": [_CELL, {"type": "null"}]}},
                 "figure_sources": _FIGURE_SOURCES},
                ("cells",)), _write_cells, write=True),
    "sheet_set_binding": _tool("Set and resolve a Cell database binding, including filter valueRef cells.",
        _schema({"addr": _ADDRESS, "binding": _BINDING}, ("addr", "binding")), _set_binding, write=True),
    "sheet_set_styles": _tool("Replace styles by address, preserving cell content; null clears a style.",
        _schema({"styles": {"type": "object", "additionalProperties": {"anyOf": [_STYLE, {"type": "null"}]}}},
                ("styles",)), lambda ctx, args: _cell_property(ctx, args, "style", args["styles"]), write=True),
    "sheet_set_dimensions": _tool("Replace supplied geometry fields; widths use letters and row indices start at one.",
        _schema({"dimensions": _object({
            "cols": {"type": "integer"}, "rows": {"type": "integer"},
            "colWidths": {"type": "object", "additionalProperties": {"type": "number"}},
            "rowHeight": {"type": "number"},
            "rowHeights": {"type": "object", "additionalProperties": {"type": "number"}},
            "hiddenRows": {"type": "array", "items": {"type": "integer"}},
            "hiddenCols": {"type": "array", "items": {"type": "integer"}},
        }, additionalProperties=False)}, ("dimensions",)), _dimensions, write=True),
    "sheet_set_validation": _tool("Replace dropdown lists by address, preserving content; null clears validation.",
        _schema({"validations": {"type": "object", "additionalProperties": {
            "anyOf": [_VALIDATION, {"type": "null"}]}}}, ("validations",)),
        lambda ctx, args: _cell_property(ctx, args, "validation", args["validations"]), write=True),
    "sheet_set_freeze": _tool("Set frozen row and column counts, using zero to unfreeze.",
        _schema({"freeze": _object({"rows": {"type": "integer"}, "cols": {"type": "integer"}})},
                ("freeze",)), _freeze, write=True),
    "sheet_add_rule": _tool("Add a conditional formatting rule to a Sheet.",
        _schema({"rule": _RULE}, ("rule",)), _rule, write=True),
    "sheet_set_chart": _tool("Add a chart or replace a chart with the same id using the app's chart document.",
        _schema({"chart": _CHART}, ("chart",)), _chart, write=True),
    "sheet_structure": _tool("Insert or delete rows or columns and reanchor references; positive delta inserts.",
        _schema({"op": _object({"axis": {"enum": ["row", "col"]},
            "at": {"type": "integer", "minimum": 1}, "delta": {"type": "integer"}},
            ("axis", "at", "delta"))}, ("op",)), _structure, write=True),
    "sheet_save_template": _tool("Save a readable workbook as a tenant Sheet template with its Cell databases.",
        _object({key: _STRING for key in ("workbook_id", "key", "name", "description")},
                ("workbook_id", "key", "name", "description"), additionalProperties=False),
        _save_template, write=True),
    "list_databases": _tool("List the databases you can access or see in Loopable.",
        _object({"limit": {"type": "integer", "minimum": 1,
                 "description": "Optional client hint. The complete permitted database list is returned."}},
                additionalProperties=False), lambda ctx, args: ctx.list_databases(), grants=("ai_apps",)),
    "describe_database": _tool("Read the fields you have permission to use in a database.",
        _object({"database": _STRING}, ("database",), additionalProperties=False),
        lambda ctx, args: ctx.describe_database(args["database"]), grants=("ai_apps",)),
    "query_database": _tool(
        "Query currently permitted records with bounded filters, stable sorts, cursor pagination and aggregates.",
        _object({
            "database": _STRING,
            "fields": {"type": "array", "items": _STRING, "maxItems": 200},
            "filters": _CLIENT_QUERY_FILTER,
            "sorts": {"type": "array", "maxItems": 8, "items": _object({
                "field": _STRING, "direction": {"enum": ["asc", "desc"]},
            }, ("field", "direction"), additionalProperties=False)},
            "limit": {"type": "integer", "minimum": 1, "maximum": 500},
            "cursor": _STRING,
            "aggregates": {"type": "array", "maxItems": 16, "items": _object({
                "fn": {"enum": ["count", "count_distinct", "sum", "avg", "min", "max"]},
                "field": _STRING,
            }, ("fn",), additionalProperties=False)},
            # S68-T55 (COMBINE-AMEND): the MCP door asks for the same SKU x month groups the REST
            # door answers (`routes_external._normalise_query_groups`): 1 to 4 field keys, or a
            # field with unit month.
            "groupBy": {"type": "array", "minItems": 1, "maxItems": 4, "items": {"oneOf": [
                _STRING,
                _object({"field": _STRING, "unit": {"enum": ["month"]}}, ("field",),
                        additionalProperties=False),
            ]}},
            "group_by": {"type": "array", "minItems": 1, "maxItems": 4,
                         "items": _STRING, "description": "Client alias for groupBy. Send only one spelling."},
        }, ("database",), additionalProperties=False),
        lambda ctx, args: ctx.query_database(args["database"], {
            key: value for key, value in args.items() if key != "database"
        }), grants=("ai_apps",)),
    "view_upsert": _tool(
        "Create or update a saved View using the grid's filters, sorts, groupBy, visible fields and display config.",
        _object({"database": _STRING, "view": _object({
            "id": {"type": "string", "minLength": 1, "maxLength": 120},
            "name": {"type": "string", "minLength": 1, "maxLength": 120},
            "config": _VIEW_CONFIG, "note": _STRING}, ("id", "name", "config"),
            additionalProperties=False)},
            ("database", "view"), additionalProperties=False),
        lambda ctx, args: ctx.view_event("view_upsert", args), write=True, grants=("ai_apps",)),
    "view_share_preview": _tool(
        "Check who could see a personal draft View and its fields before sharing it.",
        _object({
            "database": _STRING,
            "view_id": _STRING,
            "entries": {"type": "array", "maxItems": 200, "items": _object({
                "user": _STRING, "role": {"enum": ["view", "edit"]},
            }, ("user", "role"), additionalProperties=False)},
        }, ("database", "view_id", "entries"), additionalProperties=False),
        lambda ctx, args: ctx.view_share_preview(args), grants=("ai_apps",)),
    "view_delete": _tool("Delete a saved View when you have permission to do so.",
        _object({"database": _STRING, "view_id": _STRING}, ("database", "view_id"),
                additionalProperties=False),
        lambda ctx, args: ctx.view_event("view_delete", args), write=True, grants=("ai_apps",)),
    "app_upsert": _tool(
        "Create an App with name, type, compatibleDatabases and config, or update it by app_id. Identity fields are immutable.",
        _object(_APPLICATION, additionalProperties=False,
                anyOf=[{"required": ["app_id"]}, {"required": ["name", "type", "config"]},
                       {"required": ["name", "templateKind", "sourceDatabase", "fieldMap", "navPlacement"]}]),
        lambda ctx, args: ctx.app_upsert(args), write=True, grants=("ai_apps",)),
    "app_delete": _tool("Delete an App. confirmName must match its current name exactly.",
        _object({"app_id": _STRING, "confirmName": _STRING}, ("app_id", "confirmName"),
                additionalProperties=False),
        lambda ctx, args: ctx.app_delete(args), write=True, grants=("ai_apps",)),
    "database_object": _tool(
        "Read a database or preview changes to its records, fields, name or icon, then confirm them. Name and icon changes require an Administrator.",
        _object({
            "action": {"enum": list(_DATABASE_ACTIONS)},
            "database": _STRING,
            "name": _STRING,
            "icon": {"oneOf": [{"type": "null"}, _object({
                "shape": _STRING, "tone": _STRING,
            }, ("shape", "tone"), additionalProperties=False)]},
            "record_id": {"oneOf": [_STRING, {"type": "integer"}]},
            "values": {"type": "object"},
            "field": {"type": "object"},
            "field_key": _STRING,
            "query": {"type": "object"},
        }, ("action",), additionalProperties=False),
        lambda ctx, args: _callback(ctx, "database_object", args), write=True,
        grants=("ai_apps",)),
    "view_object": _tool(
        "Read a personal View or preview changes to it, then confirm them.",
        _object({
            "action": {"enum": list(_VIEW_ACTIONS)}, "database": _STRING,
            "view": {"type": "object"}, "view_id": _STRING,
            "entries": {"type": "array", "items": {"type": "object"}, "maxItems": 200},
        }, ("action", "database"), additionalProperties=False),
        lambda ctx, args: (
            ctx.view_event("view_upsert", {"database": args["database"], "view": args["view"]})
            if args["action"] == "upsert" else
            ctx.view_event("view_delete", {"database": args["database"],
                                            "view_id": args["view_id"]})
            if args["action"] == "delete" else
            ctx.view_share_preview({"database": args["database"],
                                    "view_id": args["view_id"],
                                    "entries": args.get("entries") or []})
        ), write=True, grants=("ai_apps",)),
    **{name: _object_tool(name, actions)
       for name, actions in _SHEET_OBJECT_ACTIONS.items()},
    "validated_batch": _tool(
        "Validate a bounded object-operation batch; writes preview and confirm as one unit.",
        _object({"operations": {"type": "array", "minItems": 1, "maxItems": 50,
                                "items": _object({"name": _STRING,
                                                  "arguments": {"type": "object"}},
                                                 ("name", "arguments"),
                                                 additionalProperties=False)}},
                ("operations",), additionalProperties=False),
        lambda ctx, args: _callback(ctx, "validated_batch", args), write=True,
        grants=("ai_apps",)),
    "confirm_change": _tool(
        "Confirm a current preview and keep its operation_id. Repeating it returns the first result. "
        "Read data.operation_status with that id if a response is lost. Deletions and field changes need the preview's exact phrase.",
        _object({"confirmation_token": _STRING, "confirmation_phrase": _STRING},
                ("confirmation_token",), additionalProperties=False),
        lambda ctx, args: _callback(ctx, "confirm_change", args), write=True, grants=("ai_apps",)),
}


_OBJECT_READ_ACTIONS = {
    "database_object": frozenset({"list", "describe", "query"}),
    "view_object": frozenset({"share_preview"}),
    "workbook_object": frozenset({"list", "get", "export"}),
    "worksheet_object": frozenset({"list", "get"}),
    "range_object": frozenset({"read"}),
    "calculation_object": frozenset(),
}


def build_kind(name):
    """Derive a flat tool's build kind without accepting a caller's kind."""
    if name.startswith("view_"):
        return "view"
    if name.startswith("app_"):
        return "app"
    if name.startswith("sheet_") or name in _SHEET_OBJECT_ACTIONS:
        return "workbook"
    return {"database_object": "database", "validated_batch": "batch"}.get(name, name)


for _name, _entry in TOOLS.items():
    if _entry["write"] and _name != "confirm_change":
        _entry["buildKind"] = build_kind(_name)
        _entry["inputSchema"]["properties"]["about"] = copy.deepcopy(ABOUT_SCHEMA)


def is_write(name, arguments) -> bool:
    """Classify dynamic object tools without trusting an unvalidated action as a read."""
    tool = TOOLS.get(name) if isinstance(name, str) else None
    if tool is None or name == "confirm_change":
        return False
    if name == "validated_batch":
        operations = arguments.get("operations") if isinstance(arguments, dict) else None
        if not isinstance(operations, list):
            return True
        return any(is_write(item.get("name"), item.get("arguments"))
                   for item in operations if isinstance(item, dict))
    if name in _OBJECT_READ_ACTIONS:
        action = arguments.get("action") if isinstance(arguments, dict) else None
        return action not in _OBJECT_READ_ACTIONS[name]
    return bool(tool.get("write"))


def validate_query_filter_operators(filters):
    """Refuse unknown source or target operators before permission or data access."""
    stack, seen = [filters], set()
    while stack:
        node = stack.pop()
        if not isinstance(node, (dict, list)) or id(node) in seen:
            continue
        seen.add(id(node))
        if isinstance(node, list):
            stack.extend(node)
            continue
        if "op" in node and (not isinstance(node["op"], str) or node["op"] not in FILTER_OPS):
            raise sheets.SheetRefusal(
                "tool_arguments_invalid", "Filter op must be one of: " + ", ".join(sorted(FILTER_OPS)) + ".")
        stack.extend(node[key] for key in ("nodes", "children", "where") if key in node)


def validate_arguments(arguments, schema, *, named=False):
    """Validate grouped arguments against the selected action's JSON schema."""
    if isinstance(arguments, dict) and "filters" in schema.get("properties", {}):
        validate_query_filter_operators(arguments.get("filters"))

    def equal(value, expected):
        if isinstance(value, bool) != isinstance(expected, bool):
            return False
        if isinstance(value, dict) and isinstance(expected, dict):
            return value.keys() == expected.keys() and all(equal(value[key], expected[key]) for key in value)
        if isinstance(value, list) and isinstance(expected, list):
            return len(value) == len(expected) and all(equal(a, b) for a, b in zip(value, expected))
        return value == expected

    def matches(value, rule, depth=0):
        if depth > 40:
            return False
        if isinstance(rule, bool):
            return rule
        kinds = {
            "object": isinstance(value, dict), "array": isinstance(value, list),
            "string": isinstance(value, str), "boolean": type(value) is bool,
            "integer": type(value) is int,
            "number": type(value) is int or type(value) is float and math.isfinite(value),
            "null": value is None,
        }
        kind = rule.get("type")
        if kind is not None and not any(kinds.get(item, False) for item in (
                kind if isinstance(kind, list) else [kind])):
            return False
        if "enum" in rule and not any(equal(value, choice) for choice in rule["enum"]):
            return False
        if "const" in rule and not equal(value, rule["const"]):
            return False
        for key in ("anyOf", "oneOf", "allOf"):
            if key in rule:
                count = sum(matches(value, child, depth + 1) for child in rule[key])
                if (key == "anyOf" and not count or key == "oneOf" and count != 1
                        or key == "allOf" and count != len(rule[key])):
                    return False
        if isinstance(value, dict):
            if set(rule.get("required", ())) - value.keys():
                return False
            properties = rule.get("properties", {})
            for key, child in value.items():
                if not matches(child, properties.get(key, rule.get("additionalProperties", True)), depth + 1):
                    return False
        if isinstance(value, list):
            if not rule.get("minItems", 0) <= len(value) <= rule.get("maxItems", float("inf")):
                return False
            if any(not matches(child, rule.get("items", True), depth + 1) for child in value):
                return False
        if isinstance(value, str):
            if not rule.get("minLength", 0) <= len(value) <= rule.get("maxLength", float("inf")):
                return False
            if "pattern" in rule and not re.search(rule["pattern"], value):
                return False
        if type(value) in (int, float):
            if type(value) is float and not math.isfinite(value):
                return False
            if not rule.get("minimum", -float("inf")) <= value <= rule.get("maximum", float("inf")):
                return False
        return True

    def problems(value, rule, path="arguments"):
        if matches(value, rule):
            return []
        if not isinstance(rule, dict):
            return [path]
        for branch in ("anyOf", "oneOf"):
            if branch in rule:
                candidates = [problems(value, child, path) for child in rule[branch]]
                return min(candidates, key=lambda found: (
                    -max((item.count(".") + item.count("[") for item in found), default=0), len(found)))
        if isinstance(value, dict) and rule.get("type") == "object":
            properties = rule.get("properties", {})
            found = [path + "." + key for key in rule.get("required", ()) if key not in value]
            for key, child in value.items():
                found.extend(problems(child, properties.get(key, rule.get("additionalProperties", True)),
                                      path + "." + str(key)))
            return found or [path]
        if isinstance(value, list) and rule.get("type") == "array":
            found = [item for index, child in enumerate(value)
                     for item in problems(child, rule.get("items", True), f"{path}[{index}]")]
            return found or [path]
        return [path]

    if not isinstance(arguments, dict) or not matches(arguments, schema):
        detail = (" Invalid arguments: " + ", ".join(dict.fromkeys(problems(arguments, schema))) + "."
                  if named else "")
        raise sheets.SheetRefusal("tool_arguments_invalid", "Tool arguments must match the published input schema." + detail)
    encoded = json.dumps(arguments, ensure_ascii=False, allow_nan=False)
    if any(prefix in encoded for prefix in ("lpb1.", "lpo1.", "lpr1.", "lpc1.")):
        raise sheets.SheetRefusal("credential_in_arguments", "Tool arguments must not contain credential values.")


def table_read_arguments(name, arguments, schema=None):
    """Admit supported client spellings before the existing scoped Table read."""
    schema = schema if schema is not None else TOOLS[name]["inputSchema"]
    try:
        try:
            validate_arguments(arguments, schema, named=True)
        except sheets.SheetRefusal as exc:
            if name != "query_database" or exc.code != "tool_arguments_invalid":
                raise
            aggregate = schema["properties"]["aggregates"]
            item = aggregate["items"]
            # Preserve the semantic code only when aggregate member values cause the schema failure.
            transport = {**schema, "properties": {**schema["properties"], "aggregates": {
                **aggregate, "items": {**item, "properties": {key: {} for key in item["properties"]}}}}}
            validate_arguments(arguments, transport, named=True)
            accepted = ", ".join(item["properties"]["fn"]["enum"])
            raise sheets.SheetRefusal("query_aggregates_invalid", exc.message + " Use aggregates like "
                '[{"fn": "count"}, {"fn": "sum", "field": "<readable field key>"}]. '
                "fn must be one of: " + accepted + ". When supplied, field must be a readable field key string.") from exc
        args = copy.deepcopy(arguments)
        if name != "query_database":
            return args
        if "group_by" in args:
            if "groupBy" in args:
                raise sheets.SheetRefusal("tool_arguments_invalid", "Send group_by or groupBy, not both.")
            args["groupBy"] = args.pop("group_by")
        filters = args.get("filters")

        def leaf(node):
            validate_arguments({"filters": node}, _object({"filters": _CLIENT_QUERY_LEAF}), named=True)
            if "field" not in node:
                return node
            return {"colId": node["field"], "op": "eq" if "operator" in node else node["op"],
                    **({"value": node["value"]} if "value" in node else {})}

        if isinstance(filters, list):
            args["filters"] = [leaf(node) for node in filters]
        elif isinstance(filters, dict):
            if set(filters) in ({"and"}, {"or"}):
                conj = next(iter(filters))
                args["filters"] = {"conj": conj, "nodes": [leaf(node) for node in filters[conj]]}
            elif set(filters) & {"colId", "field", "op", "operator"}:
                args["filters"] = {"conj": "and", "nodes": [leaf(filters)]}
            elif not set(filters) & {"conj", "nodes", "link", "where", "children", "and", "or"}:
                if not filters or len(filters) > 50:
                    raise sheets.SheetRefusal("tool_arguments_invalid", "filters must contain 1 to 50 field equality values.")
                args["filters"] = {"conj": "and", "nodes": [
                    {"colId": field, "op": "eq", "value": value} for field, value in filters.items()]}
            validate_arguments({"filters": args["filters"]}, _object({"filters": _QUERY_FILTER}), named=True)
        return args
    except sheets.SheetRefusal as exc:
        if exc.code != "tool_arguments_invalid":
            raise
        example = {"database": "<database>", "filters": {"conj": "and", "nodes": [
            {"colId": "<field>", "op": "eq", "value": "<value>"}]}} if name == "query_database" else {}
        shape = {"action": name, "arguments": example}
        raise sheets.SheetRefusal(exc.code, exc.message + " Call table with " + json.dumps(shape) + ".") from exc


def dispatch(ctx, name, arguments):
    """Dispatch one declared tool. The API translates core and grant refusals to MCP content."""
    tool = TOOLS.get(name) if isinstance(name, str) else None
    if tool is None:
        raise sheets.SheetRefusal("tool_not_found", "The requested tool is not available.")
    if name == "query_database":
        validate_query_filter_operators(arguments.get("filters") if isinstance(arguments, dict) else None)
    for grant in tool["grants"]:
        if grant == perm_scope.AI_APPS:
            reason = perm_scope.ai_apps_refusal(ctx.session.user, ctx.session.runtime)
            if reason:
                raise sheets.SheetRefusal("ai_apps_off", reason)
        else:
            ctx.session.require(grant)
    schema = tool["inputSchema"]
    if name in {"query_database", "list_databases"}:
        arguments = table_read_arguments(name, arguments, schema)
    if not isinstance(arguments, dict):
        raise sheets.SheetRefusal("tool_arguments_invalid", "Tool arguments must be an object.")
    missing = set(schema["required"]) - arguments.keys()
    unknown = arguments.keys() - schema["properties"].keys()
    if missing or (unknown and schema.get("additionalProperties") is False):
        raise sheets.SheetRefusal("tool_arguments_invalid", "Tool arguments must match the published input schema.")
    # Validate only the transport shape here. Document semantics belong to the app writers.
    for key, value in arguments.items():
        property_schema = schema["properties"].get(key, {})
        kind = property_schema.get("type")
        types = {"object": dict, "array": list, "string": str, "integer": int}
        if kind in types and (not isinstance(value, types[kind]) or (kind == "integer" and isinstance(value, bool))):
            raise sheets.SheetRefusal("tool_arguments_invalid", f"Tool argument {key} must be an {kind}.")
        if "enum" in property_schema and value not in property_schema["enum"]:
            raise sheets.SheetRefusal(
                "tool_arguments_invalid", f"Tool argument {key} is not a published operation.")
    # A dimension patch must never become an arbitrary tab-document replacement.
    if name == "sheet_set_dimensions":
        allowed = schema["properties"]["dimensions"]["properties"]
        if arguments["dimensions"].keys() - allowed.keys():
            raise sheets.SheetRefusal("tool_arguments_invalid", "Dimensions may contain only the published geometry fields.")
    encoded_arguments = json.dumps(arguments, ensure_ascii=False, allow_nan=False)
    if any(prefix in encoded_arguments for prefix in ("lpb1.", "lpo1.", "lpr1.", "lpc1.")):
        raise sheets.SheetRefusal("credential_in_arguments", "Tool arguments must not contain credential values.")
    return tool["handler"](ctx, arguments)