larksuite__cli/shortcuts/sheets/lark_sheet_table_io.go

// Copyright (c) 2026 Lark Technologies Pte. Ltd.
// SPDX-License-Identifier: MIT

package sheets

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"math"
	"strconv"
	"strings"
	"time"

	"github.com/larksuite/cli/errs"
	"github.com/larksuite/cli/internal/util"
	"github.com/larksuite/cli/shortcuts/common"
)

// ─── +table-put (cli-only, typed DataFrame-style import) ───────────
//
// Imports a typed, column-described table into Lark Sheets, type-faithfully:
// numbers stay numbers, dates land as real dates (serial + date number_format),
// not look-alike text. The wire protocol is deliberately a "table with column
// types" — pandas DataFrames are its most common producer, but the CLI never
// has to know about pandas.
//
// Writes into an existing spreadsheet (addressed by --url / --spreadsheet-token);
// to create a fresh workbook first, use +workbook-create, then point +table-put
// at the returned token. Multiple DataFrames → multiple sheets in one call: the
// top-level `sheets` array carries one entry per sub-sheet, each matched to an
// existing sub-sheet by name (created when absent).
//
// Date faithfulness was verified empirically (see isoDateToSerial): the only
// way to get a *real* date (ISNUMBER=TRUE, sortable / pivotable) is to write
// the Excel serial number AND set a date number_format. A date *string*, even
// with a date format, stays text — so date columns always go through the
// serial conversion below.

// TablePut is the typed table-put shortcut. It writes into an existing
// spreadsheet, composing get_workbook_structure / modify_workbook_structure /
// set_cell_range — no new backend tool, and no workbook creation (use
// +workbook-create for that, consistent with every other write shortcut).
var TablePut = common.Shortcut{
	Service:     "sheets",
	Command:     "+table-put",
	Description: "Write a typed table (columns with types + rows) into an existing spreadsheet; numbers and dates stay type-faithful.",
	Risk:        "write",
	Scopes:      []string{"sheets:spreadsheet:read", "sheets:spreadsheet:write_only"},
	AuthTypes:   []string{"user", "bot"},
	HasFormat:   true,
	Flags:       flagsFor("+table-put"),
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := resolveSpreadsheetToken(runtime); err != nil {
			return err
		}
		payload, err := resolveTablePayload(runtime)
		if err != nil {
			return err
		}
		// --styles is parsed (and aligned against the payload's sheets) up front
		// so a malformed style item fails before any write lands — mirroring
		// +workbook-create's Validate.
		_, err = parseWorkbookCreateSheetStyles(runtime, payload)
		return err
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		return tablePutDryRun(runtime)
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, err := resolveSpreadsheetTokenExec(runtime)
		if err != nil {
			return err
		}
		payload, err := resolveTablePayload(runtime)
		if err != nil {
			return err
		}
		styles, err := parseWorkbookCreateSheetStyles(runtime, payload)
		if err != nil {
			return err
		}
		return tablePutWrite(ctx, runtime, token, payload, styles)
	},
	Tips: []string{
		"Writes into an existing spreadsheet — pass --url or --spreadsheet-token. To create a new workbook first, use +workbook-create, then point --spreadsheet-token here.",
		"Payload sheets are matched to existing sub-sheets by name (created when absent). Date columns take ISO yyyy-mm-dd strings — converted to real dates (serial + date format).",
		"--styles applies number formats, colors, merges, and row/col sizes in the same call (same shape as +workbook-create's --styles): one styles item per written sheet, name-matched. Skips the separate +cells-set-style round-trip.",
	},
}

// resolveTablePayload parses --sheets (typed JSON, multi-sheet) into the
// unified internal tablePayload. Both +table-put and +workbook-create funnel
// through here so the two entry points stay in lockstep; Validate / Execute /
// DryRun / workbookCreateData all share this one decision. Network-free.
func resolveTablePayload(rctx *common.RuntimeContext) (*tablePayload, error) {
	sheetsGiven := rctx.Changed("sheets") && strings.TrimSpace(rctx.Str("sheets")) != ""
	if !sheetsGiven {
		return nil, common.ValidationErrorf("--sheets is required")
	}
	return parseTablePutPayload(rctx)
}

// ─── protocol ─────────────────────────────────────────────────────────

type tablePayload struct {
	Sheets []tableSheetSpec `json:"sheets"`
}

// tableSheetSpec is the *internal* representation a sheet is normalized into
// after parsing the wire protocol. It carries everything buildSheetMatrix and
// friends need (typed columns + format + 2D row matrix) and is what the rest of
// this file works against. The wire shape — string columns + dtypes/formats
// maps + `data` — lives in tableSheetIn and is collapsed into this struct by
// (*tableSheetIn).normalize.
type tableSheetSpec struct {
	Name      string
	StartCell string
	// Mode controls write placement: "overwrite" (default) writes a header+data
	// block from start_cell; "append" writes data below the sheet's existing
	// data (start_cell's row is ignored, its column is honored).
	Mode string
	// Header is whether to write a header row of column names. nil defaults by
	// mode: true for overwrite, false for append (so appended rows don't repeat
	// the header). Set explicitly to override.
	Header *bool
	// AllowOverwrite, when explicitly false, makes the write fail if it would
	// land on a non-empty cell. nil defaults to true (overwrite).
	AllowOverwrite *bool
	Columns        []tableColumnSpec
	Rows           [][]interface{}
}

type tableColumnSpec struct {
	Name   string
	Type   string
	Format string
}

// tableSheetIn is the wire-level shape of one sheet in --sheets. It is
// pandas-DataFrame-shaped on purpose: `columns` is a plain string list, `data`
// is a 2D array, and the per-column type / display format are *separate*
// dtypes/formats maps keyed by column name. That gives agents a one-liner
// (`{**json.loads(df.to_json(orient="split")), "dtypes":
// df.dtypes.astype(str).to_dict()}`) and lets handwritten payloads stay flat
// rather than nest a {name, type, format} object per column.
type tableSheetIn struct {
	Name           string            `json:"name"`
	StartCell      string            `json:"start_cell"`
	Mode           string            `json:"mode"`
	Header         *bool             `json:"header"`
	AllowOverwrite *bool             `json:"allow_overwrite"`
	Columns        []string          `json:"columns"`
	Data           [][]interface{}   `json:"data"`
	Dtypes         map[string]string `json:"dtypes"`
	Formats        map[string]string `json:"formats"`
}

// dtypeToTypeFormat maps a pandas-style dtype string to the internal column
// (type, default format) pair. The mapping is deliberately permissive: a missing
// or unknown dtype falls through to string + text format (`@`) so a
// `to_json(orient="split")` payload that omits `dtypes` writes correctly as an
// all-string table. Recognized families:
//   - int*/uint* (lowercase numpy + capitalized nullable pandas) → number
//   - float* / Float* / complex*                                  → number
//   - bool / boolean (nullable)                                   → bool
//   - datetime*  (incl. tz-aware datetime64[ns, UTC])             → date, "yyyy-mm-dd"
//   - everything else (object, string, category, empty, unknown) → string, "@"
//
// Explicit `formats[col]` is layered on top of this default by normalize, so a
// user-supplied `#,##0.00` on a float64 column still wins.
func dtypeToTypeFormat(dtype string) (typ, format string) {
	d := strings.TrimSpace(dtype)
	if d == "" {
		return "string", "@"
	}
	lower := strings.ToLower(d)
	switch {
	case strings.HasPrefix(lower, "datetime"):
		return "date", "yyyy-mm-dd"
	case lower == "bool" || lower == "boolean":
		return "bool", ""
	case isNumericDtype(lower):
		return "number", ""
	default:
		return "string", "@"
	}
}

// isNumericDtype recognizes pandas/numpy numeric dtype strings (lowercased).
// Covers numpy ints (`int8`/`int64`/...), unsigned ints (`uint*`), floats
// (`float32`/`float64`), complex, and pandas' nullable variants
// (`int64`/`uint64`/`float64` lowercased from `Int64`/`UInt64`/`Float64`).
func isNumericDtype(lower string) bool {
	for _, p := range []string{"int", "uint", "float", "complex"} {
		if strings.HasPrefix(lower, p) {
			return true
		}
	}
	return false
}

// typeToDtype is the inverse used by +table-get to label each output column.
// Choices are picked to be safe under a single `df.astype(dtypes)` round-trip:
//   - string → object (pandas default, no-op astype)
//   - number → float64 (works for all numeric cells, including ones with NaN)
//   - date   → datetime64[ns] (matches the ISO strings we emit)
//   - bool   → bool (inferColumnType only picks bool when every cell is bool)
//
// Anything else (defensive default) maps to object.
func typeToDtype(typ string) string {
	switch typ {
	case "number":
		return "float64"
	case "date":
		return "datetime64[ns]"
	case "bool":
		return "bool"
	default:
		return "object"
	}
}

// decoderExpectEOF ensures the decoder has nothing left to read after a
// successful Decode. json.Decoder accepts trailing non-whitespace after the
// first JSON value (unlike json.Unmarshal), so a payload like `{...} trailing`
// would silently be treated as the leading object only. Use this after the
// first Decode to surface the trailing data as a validation error.
func decoderExpectEOF(dec *json.Decoder) error {
	var trailing json.RawMessage
	if err := dec.Decode(&trailing); err == nil {
		return fmt.Errorf("trailing data after JSON value") //nolint:forbidigo // intermediate error; the caller wraps it into a typed --sheets/--values validation error
	} else if !errors.Is(err, io.EOF) {
		return fmt.Errorf("trailing data after JSON value: %w", err) //nolint:forbidigo // intermediate error; the caller wraps it into a typed --sheets/--values validation error
	}
	return nil
}

// parseTablePutPayload reads --sheets (JSON, supports @file / stdin) into a
// validated payload. UseNumber keeps numeric cells as json.Number so large
// integers (order IDs, etc.) survive without precision loss or scientific
// notation. The wire shape (tableSheetIn: string columns + dtypes/formats maps
// + `data`) is normalized into the internal tableSheetSpec so the rest of the
// file (buildSheetMatrix, sheetCreateDims, …) is unaware of it. Network-free:
// safe from Validate and DryRun.
func parseTablePutPayload(runtime flagView) (*tablePayload, error) {
	raw := strings.TrimSpace(runtime.Str("sheets"))
	if raw == "" {
		return nil, common.ValidationErrorf("--sheets is required")
	}
	dec := json.NewDecoder(strings.NewReader(raw))
	dec.UseNumber()
	var wire struct {
		Sheets []tableSheetIn `json:"sheets"`
	}
	if err := dec.Decode(&wire); err != nil {
		return nil, common.ValidationErrorf("--sheets: invalid JSON: %v", err).WithCause(err)
	}
	// Reject trailing non-whitespace after the first JSON value: json.Decoder
	// accepts it silently (unlike json.Unmarshal), so e.g. `--sheets '{...} oops'`
	// would otherwise pass Validate and surface as confusing downstream errors.
	if err := decoderExpectEOF(dec); err != nil {
		return nil, common.ValidationErrorf("--sheets: %v", err).WithCause(err)
	}
	p := &tablePayload{Sheets: make([]tableSheetSpec, 0, len(wire.Sheets))}
	for i := range wire.Sheets {
		spec, err := wire.Sheets[i].normalize(i)
		if err != nil {
			return nil, err
		}
		p.Sheets = append(p.Sheets, spec)
	}
	if err := p.validate(); err != nil {
		return nil, err
	}
	return p, nil
}

// normalize collapses the wire-level pandas-shaped tableSheetIn into the
// internal tableSheetSpec used by the writer. It pairs each column name with
// its dtype-derived (type, format) — with `formats[name]` overriding the
// default — and renames `data` back to the writer's `Rows`. Per-column
// validation that needs the resolved type lives in tablePayload.validate (so
// errors carry the sheet-index/name context the writer already prints).
func (in *tableSheetIn) normalize(idx int) (tableSheetSpec, error) {
	spec := tableSheetSpec{
		Name:           in.Name,
		StartCell:      in.StartCell,
		Mode:           in.Mode,
		Header:         in.Header,
		AllowOverwrite: in.AllowOverwrite,
		Rows:           in.Data,
	}
	seenCol := make(map[string]bool, len(in.Columns))
	spec.Columns = make([]tableColumnSpec, len(in.Columns))
	for j, name := range in.Columns {
		trimmed := strings.TrimSpace(name)
		if trimmed == "" {
			return tableSheetSpec{}, common.ValidationErrorf("--sheets[%d] %q: columns[%d] name is required", idx, in.Name, j)
		}
		if seenCol[name] {
			return tableSheetSpec{}, common.ValidationErrorf("--sheets[%d] %q: duplicate column name %q", idx, in.Name, name)
		}
		seenCol[name] = true
		typ, format := dtypeToTypeFormat(in.Dtypes[name])
		if f, ok := in.Formats[name]; ok {
			format = strings.TrimSpace(f)
		}
		spec.Columns[j] = tableColumnSpec{Name: name, Type: typ, Format: format}
	}
	// Surface dtypes/formats entries that reference a column the sheet doesn't
	// have — almost always a typo (`"foramt"`, `"营 收"` with stray spaces) and
	// silently ignoring them would let the writer succeed with the wrong
	// formatting. The check runs after the column list is built so we can
	// compare against the canonical set.
	for k := range in.Dtypes {
		if !seenCol[k] {
			return tableSheetSpec{}, common.ValidationErrorf("--sheets[%d] %q: dtypes references unknown column %q", idx, in.Name, k)
		}
	}
	for k := range in.Formats {
		if !seenCol[k] {
			return tableSheetSpec{}, common.ValidationErrorf("--sheets[%d] %q: formats references unknown column %q", idx, in.Name, k)
		}
	}
	return spec, nil
}

func (p *tablePayload) validate() error {
	if len(p.Sheets) == 0 {
		return common.ValidationErrorf("--sheets: must contain at least one sheet")
	}
	seen := make(map[string]bool, len(p.Sheets))
	for i := range p.Sheets {
		s := &p.Sheets[i]
		if strings.TrimSpace(s.Name) == "" {
			return common.ValidationErrorf("--sheets[%d]: name is required", i)
		}
		if seen[s.Name] {
			return common.ValidationErrorf("--sheets[%d]: duplicate sheet name %q", i, s.Name)
		}
		seen[s.Name] = true
		if len(s.Columns) == 0 {
			return common.ValidationErrorf("--sheets[%d] %q: columns must be non-empty", i, s.Name)
		}
		for j := range s.Columns {
			c := &s.Columns[j]
			// validColumnType still guards the internal Type so a future
			// dtype-mapping change (or a direct test-time construction of a
			// tableSheetSpec) can't silently route an unknown type into
			// buildTypedCell's default branch.
			if !validColumnType(c.Type) {
				return common.ValidationErrorf("--sheets[%d] %q: columns[%d] %q has invalid type %q (want string/number/date/bool)",
					i, s.Name, j, c.Name, c.Type)
			}
		}
		for r := range s.Rows {
			if len(s.Rows[r]) != len(s.Columns) {
				return common.ValidationErrorf("--sheets[%d] %q: row %d has %d cells, want %d (column count)",
					i, s.Name, r, len(s.Rows[r]), len(s.Columns))
			}
			// Validate each cell's value against its column type up front (pure,
			// network-free): a bad date/number/bool is caught here — before any
			// workbook is created — instead of failing mid-write and leaving a
			// stray empty spreadsheet behind.
			for c := range s.Columns {
				if _, err := buildTypedCell(&s.Columns[c], s.Rows[r][c]); err != nil {
					return common.ValidationErrorf("--sheets[%d] %q: row %d column %q: %v", i, s.Name, r, s.Columns[c].Name, err).WithCause(err)
				}
			}
		}
		if sc := strings.TrimSpace(s.StartCell); sc != "" {
			if _, _, ok := splitCellRef(sc); !ok {
				return common.ValidationErrorf("--sheets[%d] %q: start_cell %q must be a single cell ref (e.g. A1)", i, s.Name, sc)
			}
		}
		switch s.Mode {
		case "", "overwrite", "append":
		default:
			return common.ValidationErrorf("--sheets[%d] %q: mode %q is invalid (want \"overwrite\" or \"append\")", i, s.Name, s.Mode)
		}
	}
	return nil
}

// validColumnType reports whether a column type is one the writer understands.
// An empty type is valid and means "type-less" (raw passthrough): the value is
// written as-is and Lark Sheets auto-detects its type — see buildTypedCell.
// +workbook-create's --values synthesizes an all-type-less payload through this.
func validColumnType(t string) bool {
	switch t {
	case "", "string", "number", "date", "bool":
		return true
	}
	return false
}

// ─── type mapping ─────────────────────────────────────────────────────

// headerOn reports whether a header row of column names should be written. A
// nil Header defaults by mode: overwrite writes it; append omits it so the
// appended rows don't repeat the header below an existing one.
func headerOn(s *tableSheetSpec) bool {
	if s.Header != nil {
		return *s.Header
	}
	return s.Mode != "append"
}

// buildSheetMatrix turns a sheet spec into the set_cell_range cells matrix:
// optionally a header row of column names, then one row per data record with
// each cell mapped by its column type. Per-column number_format is attached so
// numbers/dates render correctly (and dates become real dates). Header cells
// carry no style of their own — style them via --styles like any other cell.
func buildSheetMatrix(s *tableSheetSpec, writeHeader bool) ([][]interface{}, error) {
	ncols := len(s.Columns)
	matrix := make([][]interface{}, 0, len(s.Rows)+1)

	if writeHeader {
		header := make([]interface{}, ncols)
		for c := range s.Columns {
			header[c] = map[string]interface{}{"value": s.Columns[c].Name}
		}
		matrix = append(matrix, header)
	}

	for r := range s.Rows {
		row := make([]interface{}, ncols)
		for c := range s.Columns {
			cell, err := buildTypedCell(&s.Columns[c], s.Rows[r][c])
			if err != nil {
				return nil, common.ValidationErrorf("sheet %q row %d column %q: %v", s.Name, r, s.Columns[c].Name, err).WithCause(err)
			}
			row[c] = cell
		}
		matrix = append(matrix, row)
	}
	return matrix, nil
}

// buildTypedCell maps one raw JSON value to a set_cell_range cell per its
// declared column type. A nil (JSON null) becomes an empty cell that still
// carries the column's number_format. number values are kept as json.Number to
// preserve precision; dates are converted to Excel serials. A type-less column
// (Type == "") writes the raw scalar unchanged, letting the backend auto-detect
// the type — the untyped --values path relies on this.
func buildTypedCell(col *tableColumnSpec, raw interface{}) (map[string]interface{}, error) {
	cell := map[string]interface{}{}
	nf := strings.TrimSpace(col.Format)
	if nf == "" {
		switch col.Type {
		case "date":
			nf = "yyyy-mm-dd"
		case "string":
			// Text format keeps digit-like strings (IDs / postcodes / phone numbers)
			// as text, and lets +table-get infer the column back as string instead
			// of guessing number from a numeric-looking value.
			nf = "@"
		}
	}
	if nf != "" {
		cell["cell_styles"] = map[string]interface{}{"number_format": nf}
	}
	if raw == nil {
		return cell, nil
	}
	switch col.Type {
	case "":
		// Type-less column: write the raw JSON scalar as-is so Lark Sheets
		// auto-detects the type (numeric → number, else text). json.Number is
		// kept verbatim for precision; an optional --styles number_format
		// controls display. This is the untyped --values behavior.
		cell["value"] = raw
	case "string":
		cell["value"] = stringifyCellValue(raw)
	case "number":
		n, ok := raw.(json.Number)
		if !ok {
			return nil, fmt.Errorf("number expects a numeric value, got %s", describeJSONType(raw)) //nolint:forbidigo // intermediate error; callers wrap it into a typed --sheets/--values validation error with row/column context
		}
		cell["value"] = n
	case "bool":
		b, ok := raw.(bool)
		if !ok {
			return nil, fmt.Errorf("bool expects true/false, got %s", describeJSONType(raw)) //nolint:forbidigo // intermediate error; callers wrap it into a typed --sheets/--values validation error with row/column context
		}
		cell["value"] = b
	case "date":
		str, ok := raw.(string)
		if !ok {
			return nil, fmt.Errorf("date expects an ISO yyyy-mm-dd string, got %s", describeJSONType(raw)) //nolint:forbidigo // intermediate error; callers wrap it into a typed --sheets/--values validation error with row/column context
		}
		serial, err := isoDateToSerial(str)
		if err != nil {
			return nil, err
		}
		cell["value"] = serial
	default:
		return nil, fmt.Errorf("unsupported type %q", col.Type) //nolint:forbidigo // intermediate error; callers wrap it into a typed --sheets/--values validation error with row/column context
	}
	return cell, nil
}

// stringifyCellValue renders any JSON scalar as the literal text a string
// column should hold. json.Number keeps its exact digits (no scientific
// notation), so IDs / postcodes survive as written.
func stringifyCellValue(raw interface{}) string {
	switch v := raw.(type) {
	case string:
		return v
	case json.Number:
		return v.String()
	case bool:
		if v {
			return "TRUE"
		}
		return "FALSE"
	default:
		return fmt.Sprintf("%v", v)
	}
}

func describeJSONType(raw interface{}) string {
	switch raw.(type) {
	case string:
		return "a string"
	case json.Number:
		return "a number"
	case bool:
		return "a boolean"
	case []interface{}:
		return "an array"
	case map[string]interface{}:
		return "an object"
	default:
		return fmt.Sprintf("%T", raw)
	}
}

// excelEpoch is the Excel / Lark Sheets serial-date origin (1899-12-30 = 0).
// Verified empirically: writing serial 45306 renders as 2024-01-15 in Lark
// Sheets, matching Excel's 1900 date system exactly.
var excelEpoch = time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)

// isoDateToSerial converts an ISO yyyy-mm-dd string to its Excel serial day
// number. The result is written as a numeric cell value with a date
// number_format, which is the only combination that yields a real (sortable,
// pivotable, ISNUMBER=TRUE) date in Lark Sheets.
//
// Accepts both bare dates (`2024-01-15`) and full ISO datetime strings with a
// `T` separator (`2024-01-15T00:00:00.000`, `2024-01-15T08:30:00+08:00`). The
// `T...` suffix is dropped before parsing so the pandas `df_to_sheet` helper
// — which uses `df.to_json(orient="split", date_format="iso")` and therefore
// always emits the full ISO form — round-trips without an extra string clean
// step on the agent side. A leading `T` (no date prefix) is left alone so the
// parser still rejects it cleanly.
func isoDateToSerial(s string) (int, error) {
	s = strings.TrimSpace(s)
	if i := strings.Index(s, "T"); i > 0 {
		s = s[:i]
	}
	t, err := time.Parse("2006-01-02", s)
	if err != nil {
		return 0, fmt.Errorf("date %q must be ISO yyyy-mm-dd: %w", s, err) //nolint:forbidigo // intermediate error; callers wrap it into a typed --sheets/--values validation error with row/column context
	}
	return int(math.Round(t.Sub(excelEpoch).Hours() / 24)), nil
}

// ─── range helpers ────────────────────────────────────────────────────

// tablePutMaxCellsPerWrite caps a single set_cell_range write. Larger
// sheets are split into row batches so one oversized request can't exceed the
// tool's cell ceiling. Matches +cells-set's default --max-cells.
const tablePutMaxCellsPerWrite = 50000

// sheetAnchor returns the resolved start cell (default A1) and its 0-based
// column/row. Caller has already validated start_cell, so the parse can't fail
// in practice; the ok guard is defensive.
func sheetAnchor(s *tableSheetSpec) (anchor string, col0, row0 int, err error) {
	anchor = strings.TrimSpace(s.StartCell)
	if anchor == "" {
		anchor = "A1"
	}
	c, r, ok := splitCellRef(anchor)
	if !ok {
		return "", 0, 0, common.ValidationErrorf("start_cell %q must be a single cell ref (e.g. A1)", anchor)
	}
	return anchor, c, r, nil
}

// tablePutFullRange is the A1 rectangle the whole matrix (header + data)
// occupies, for reporting in the result / dry-run. Returns "" when there is
// nothing to write (e.g. header=false with no data rows) — the previous
// formula would have produced an invalid trailing row like "A1:C0".
func tablePutFullRange(s *tableSheetSpec, totalRows int) string {
	if totalRows <= 0 || len(s.Columns) == 0 {
		return ""
	}
	_, col0, row0, err := sheetAnchor(s)
	if err != nil {
		return strings.TrimSpace(s.StartCell)
	}
	ncols := len(s.Columns)
	return fmt.Sprintf("%s%d:%s%d",
		columnIndexToLetter(col0), row0+1,
		columnIndexToLetter(col0+ncols-1), row0+totalRows)
}

// ─── write path ───────────────────────────────────────────────────────

// writeSheetData writes one sheet's matrix via set_cell_range, splitting into
// row batches when the cell count would exceed tablePutMaxCellsPerWrite.
// Returns a per-sheet summary for the result envelope.
func writeSheetData(ctx context.Context, runtime *common.RuntimeContext, token, sheetID string, s *tableSheetSpec, styles *workbookCreateStylePayload, dims gridDims) (map[string]interface{}, error) {
	_, col0, row0, err := sheetAnchor(s)
	if err != nil {
		return nil, err
	}
	ncols := len(s.Columns)

	// append mode starts below the sheet's existing data; start_cell's row is
	// ignored (its column is still honored). overwrite mode anchors at row0.
	baseRow := row0
	writeHeader := headerOn(s)
	if s.Mode == "append" {
		lastRow, err := lastDataRow(ctx, runtime, token, sheetID, dims)
		if err != nil {
			return nil, fmt.Errorf("resolving last data row for append: %w", err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
		}
		if lastRow > 0 {
			baseRow = lastRow // 0-based index of the row just below the 1-based last data row
		} else if s.Header == nil {
			// appending to an empty sheet with no explicit header choice: write the
			// header so column names aren't lost (and a later +table-get doesn't
			// consume the first data row as the header).
			writeHeader = true
		}
	}

	matrix, err := buildSheetMatrix(s, writeHeader)
	if err != nil {
		return nil, err
	}
	if err := applyWorkbookCreateStylesToMatrix(matrix, styles, col0, baseRow, fmt.Sprintf("--styles for sheet %q", s.Name)); err != nil {
		return nil, err
	}

	if len(matrix) == 0 {
		// header:false with no data rows — nothing to write.
		return map[string]interface{}{
			"name": s.Name, "sheet_id": sheetID, "range": "",
			"data_rows": 0, "columns": ncols, "writes": 0, "mode": writeModeName(s),
		}, nil
	}

	startCol := columnIndexToLetter(col0)
	endCol := columnIndexToLetter(col0 + ncols - 1)
	allowOverwrite := s.AllowOverwrite == nil || *s.AllowOverwrite

	rowsPerBatch := tablePutMaxCellsPerWrite / ncols
	if rowsPerBatch < 1 {
		rowsPerBatch = 1
	}

	writes := 0
	for start := 0; start < len(matrix); start += rowsPerBatch {
		end := start + rowsPerBatch
		if end > len(matrix) {
			end = len(matrix)
		}
		batchRange := fmt.Sprintf("%s%d:%s%d", startCol, baseRow+start+1, endCol, baseRow+end)
		input := map[string]interface{}{
			"excel_id": token,
			"sheet_id": sheetID,
			"range":    batchRange,
			"cells":    matrix[start:end],
		}
		if !allowOverwrite {
			input["allow_overwrite"] = false
		}
		if _, err := callTool(ctx, runtime, token, ToolKindWrite, "set_cell_range", input); err != nil {
			return nil, fmt.Errorf("writing rows %d-%d: %w", start+1, end, err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
		}
		writes++
	}
	if err := applyWorkbookCreateVisualOps(ctx, runtime, token, sheetID, styles); err != nil {
		return nil, fmt.Errorf("applying visual styles: %w", err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
	}
	return map[string]interface{}{
		"name":      s.Name,
		"sheet_id":  sheetID,
		"range":     fmt.Sprintf("%s%d:%s%d", startCol, baseRow+1, endCol, baseRow+len(matrix)),
		"data_rows": len(s.Rows),
		"columns":   ncols,
		"writes":    writes,
		"mode":      writeModeName(s),
	}, nil
}

// writeModeName normalizes the sheet's write mode to a non-empty label for
// result / dry-run reporting ("" defaults to "overwrite").
func writeModeName(s *tableSheetSpec) string {
	if s.Mode == "append" {
		return "append"
	}
	return "overwrite"
}

// lastDataRow returns the 1-based row number of the last row containing data in
// the sheet (0 when empty), so append mode can place new rows just below it. It
// reads current_region via get_range_as_csv — the backend's reported true data
// extent.
//
// The anchor range matters for the same reason it does in sheetCurrentRegion:
// current_region is the bounding box of non-empty cells WITHIN the requested
// range, so an A1 anchor stops at the first fully-empty row and reports a
// too-small last row — append would then write on top of and overwrite the data
// past that gap. Anchoring over the whole grid (A1:<lastCol><lastRow>, from the
// sheet's row_count / column_count) makes the probe span internal blank rows.
// When dimensions are unknown it falls back to the legacy A1 anchor.
func lastDataRow(ctx context.Context, runtime *common.RuntimeContext, token, sheetID string, dims gridDims) (int, error) {
	anchor := "A1"
	if dims.rows > 0 && dims.cols > 0 {
		anchor = "A1:" + columnIndexToLetter(dims.cols-1) + strconv.Itoa(dims.rows)
	}
	out, err := callTool(ctx, runtime, token, ToolKindRead, "get_range_as_csv", map[string]interface{}{
		"excel_id": token,
		"sheet_id": sheetID,
		"range":    anchor,
		"max_rows": unboundedReadLimit,
	})
	if err != nil {
		return 0, err
	}
	m, ok := out.(map[string]interface{})
	if !ok {
		return 0, nil // empty sheet — no output
	}
	region, _ := m["current_region"].(string)
	if region == "" {
		region, _ = m["actual_range"].(string)
	}
	return a1EndRow(region), nil
}

// writeTypedSheets writes a typed payload's sheets into a workbook and returns
// the per-sheet summaries. It deliberately does not emit output — the caller
// composes the envelope, because +table-put and +workbook-create report
// different top-level shapes (a bare token vs. the full spreadsheet metadata).
// Existing sub-sheets are matched by name in a single structure read; missing
// ones are created on demand.
//
// adoptSheetID, when non-empty, is the id of a freshly created workbook's
// default sub-sheet: the first payload sheet adopts it (the default sheet is
// renamed to that sheet's name and reused) so the new workbook isn't left with
// a stray empty "Sheet1" beside the typed sheets. +table-put passes "" (it
// writes into an existing workbook, with no default sheet to adopt);
// +workbook-create passes the default sheet's id.
//
// On failure it returns the summaries written so far alongside the error, so
// the caller can surface a partial_success.
func writeTypedSheets(ctx context.Context, runtime *common.RuntimeContext, token string, payload *tablePayload, adoptSheetID string, styles *workbookCreateSheetStyles) ([]interface{}, error) {
	byName, dimsByName, err := listSheetIDsByName(ctx, runtime, token)
	if err != nil {
		return nil, err
	}

	// Adopt the default sheet as the first payload sheet (rename + reuse), so a
	// just-created workbook doesn't keep its empty default sheet around. Skip if
	// a sheet by that name already exists (it'll be matched normally below).
	if adoptSheetID != "" && len(payload.Sheets) > 0 {
		first := payload.Sheets[0].Name
		if _, exists := byName[first]; !exists {
			if err := renameSheet(ctx, runtime, token, adoptSheetID, first); err != nil {
				return nil, fmt.Errorf("adopting the default sheet as %q failed: %w", first, err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
			}
			byName[first] = adoptSheetID
		}
	}

	written := make([]interface{}, 0, len(payload.Sheets))
	for i := range payload.Sheets {
		s := &payload.Sheets[i]
		sheetID, ok := byName[s.Name]
		if !ok {
			rows, cols := sheetCreateDims(s)
			sheetID, err = createSheet(ctx, runtime, token, s.Name, rows, cols)
			if err != nil {
				return written, fmt.Errorf("creating sheet %q failed: %w", s.Name, err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
			}
			byName[s.Name] = sheetID
			// A freshly created sheet's grid is exactly what we just asked for.
			dimsByName[s.Name] = gridDims{rows: rows, cols: cols}
		}
		summary, err := writeSheetData(ctx, runtime, token, sheetID, s, styles.styleFor(i), dimsByName[s.Name])
		if err != nil {
			return written, fmt.Errorf("writing sheet %q failed: %w", s.Name, err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
		}
		written = append(written, summary)
	}
	return written, nil
}

// renameSheet renames a sub-sheet in place via modify_workbook_structure. Used
// to adopt a freshly created workbook's default sheet as the first typed sheet
// (see writeTypedSheets); mirrors +sheet-rename's tool input.
func renameSheet(ctx context.Context, runtime *common.RuntimeContext, token, sheetID, newName string) error {
	_, err := callTool(ctx, runtime, token, ToolKindWrite, "modify_workbook_structure", map[string]interface{}{
		"excel_id":  token,
		"operation": "rename",
		"sheet_id":  sheetID,
		"new_name":  newName,
	})
	return err
}

// tablePutWrite writes the payload into an existing workbook and emits the
// +table-put envelope. The shared write loop lives in writeTypedSheets; this
// wrapper adds +table-put's output shape and partial-success reporting. styles
// (optional, parsed from --styles) is forwarded so per-sheet visual ops apply
// in the same call.
func tablePutWrite(ctx context.Context, runtime *common.RuntimeContext, token string, payload *tablePayload, styles *workbookCreateSheetStyles) error {
	written, err := writeTypedSheets(ctx, runtime, token, payload, "", styles)
	if err != nil {
		return tablePutPartial(runtime, token, nil, written, err.Error())
	}
	runtime.Out(map[string]interface{}{
		"spreadsheet_token": token,
		"sheets":            written,
	}, nil)
	return nil
}

// createSheet appends a new sub-sheet sized to hold the spec, then resolves its
// id. The backend's default sheet (20 cols × 200 rows) is too small for wide or
// long tables (e.g. a 37-column quarter matrix), so the create request sizes the
// sheet to the write range up front — otherwise the follow-up set_cell_range
// fails with "range … exceeds sheet bounds". modify_workbook_structure's create
// output shape isn't relied upon — the id is read back by name, which is robust
// across tool-response variations.
func createSheet(ctx context.Context, runtime *common.RuntimeContext, token, name string, rows, cols int) (string, error) {
	input := map[string]interface{}{
		"excel_id":   token,
		"operation":  "create",
		"sheet_name": name,
	}
	if rows > 0 {
		input["rows"] = rows
	}
	if cols > 0 {
		input["columns"] = cols
	}
	if _, err := callTool(ctx, runtime, token, ToolKindWrite, "modify_workbook_structure", input); err != nil {
		return "", err
	}
	id, _, err := lookupSheetIndex(ctx, runtime, token, "", name)
	if err != nil {
		return "", fmt.Errorf("sheet %q created but resolving its id failed: %w", name, err) //nolint:forbidigo // intermediate error; surfaced as a partial_success message string via tablePutPartial, not a typed final error
	}
	return id, nil
}

// sheetCreateDims sizes a to-be-created sheet to the spec's write range so the
// follow-up set_cell_range can't exceed sheet bounds. It accounts for the
// start_cell offset and the optional header row. The backend's 20×200 defaults
// are kept as floors (ordinary small tables are created exactly as before) and
// its hard limits (200 cols, 50000 rows) as ceilings.
func sheetCreateDims(s *tableSheetSpec) (rows, cols int) {
	_, col0, row0, _ := sheetAnchor(s)
	cols = col0 + len(s.Columns)
	rows = row0 + len(s.Rows)
	// Match writeSheetData's header decision exactly. headerOn() is false for
	// append mode by default, but writeSheetData *forces* a header when append
	// hits an empty sheet with no explicit Header choice (so column names
	// aren't lost on the first append). sheetCreateDims runs only when the
	// sheet is being created — therefore the sheet IS empty and that forced
	// header WILL be written, so size for it here. Without this the sheet is
	// created one row short and append-near-50000 / append-at-N-cols-200
	// would bounce off the backend's hard cap.
	if headerOn(s) || (s.Mode == "append" && s.Header == nil) {
		rows++
	}
	if cols < 20 {
		cols = 20
	}
	if cols > 200 {
		cols = 200
	}
	if rows < 200 {
		rows = 200
	}
	if rows > 50000 {
		rows = 50000
	}
	return rows, cols
}

// gridDims is a sub-sheet's physical grid size (row_count × column_count from
// get_workbook_structure). A zero in either field means "unknown", which makes
// the used-range probes (sheetCurrentRegion / lastDataRow) fall back to their
// legacy A1 anchor instead of a full-grid one.
type gridDims struct {
	rows int
	cols int
}

// listSheetIDsByName maps every existing sub-sheet's display name to its id and
// physical grid dimensions via a single get_workbook_structure read. Used by
// write mode to decide which payload sheets already exist (and, for append, to
// anchor the last-data-row probe over the whole grid — see lastDataRow).
func listSheetIDsByName(ctx context.Context, runtime *common.RuntimeContext, token string) (map[string]string, map[string]gridDims, error) {
	out, err := callTool(ctx, runtime, token, ToolKindRead, "get_workbook_structure", map[string]interface{}{
		"excel_id": token,
	})
	if err != nil {
		return nil, nil, err
	}
	m, ok := out.(map[string]interface{})
	if !ok {
		return nil, nil, errs.NewInternalError(errs.SubtypeInvalidResponse, "get_workbook_structure returned non-object output")
	}
	sheets, _ := m["sheets"].([]interface{})
	byName := make(map[string]string, len(sheets))
	dimsByName := make(map[string]gridDims, len(sheets))
	for _, raw := range sheets {
		id, name, rc, cc := tableGetSheetMeta(raw)
		if id != "" && name != "" {
			byName[name] = id
			dimsByName[name] = gridDims{rows: rc, cols: cc}
		}
	}
	return byName, dimsByName, nil
}

// tablePutPartial reports a multi-sheet write that failed partway. When some
// sheets already landed it is a partial_success: their summaries are the primary
// machine-readable output, so we emit an ok:false result envelope on stdout (via
// OutPartialFailure) carrying written_sheets, and return the partial-failure exit
// signal — callers can retry the rest or delete the workbook. When nothing landed
// — the first or only sheet failed — it is a plain failure, so we return a typed
// errs.APIError rather than misleadingly claiming "some sheets were written".
func tablePutPartial(runtime *common.RuntimeContext, token string, spreadsheet interface{}, written []interface{}, reason string) error {
	if len(written) == 0 {
		return errs.NewAPIError(errs.SubtypeServerError, "table-put failed on %s: %s", token, reason).
			WithHint("no sheets were written; fix the cause and retry")
	}
	data := map[string]interface{}{
		"spreadsheet_token": token,
		"written_sheets":    written,
		"reason":            reason,
		"hint":              "some sheets were written; inspect written_sheets, then retry the remaining sheets or delete the spreadsheet",
	}
	if spreadsheet != nil {
		data["spreadsheet"] = spreadsheet
	}
	return runtime.OutPartialFailure(data, nil)
}

// ─── dry-run ──────────────────────────────────────────────────────────

// tablePutDryRun renders the set_cell_range write the shortcut would send for
// each sheet, plus any --styles visual ops (cell_styles merged into the matrix;
// merges / row+col sizes as their own tool calls). Network-free; the payload,
// locator, and styles have already been validated by Validate, so errors here
// degrade to an empty preview rather than twice.
func tablePutDryRun(runtime *common.RuntimeContext) *common.DryRunAPI {
	dry := common.NewDryRunAPI()
	payload, err := resolveTablePayload(runtime)
	if err != nil {
		return dry
	}
	token, err := resolveSpreadsheetToken(runtime)
	if err != nil {
		return dry
	}
	sheetStyles, _ := parseWorkbookCreateSheetStyles(runtime, payload)
	for i := range payload.Sheets {
		s := &payload.Sheets[i]
		matrix, _ := buildSheetMatrix(s, headerOn(s))
		desc := fmt.Sprintf("write sheet %q (%d data rows × %d cols, mode=%s) via set_cell_range",
			s.Name, len(s.Rows), len(s.Columns), writeModeName(s))
		rng := tablePutFullRange(s, len(matrix))
		if s.Mode == "append" {
			rng = "<append below existing data>"
		} else {
			// cell_styles are merged into the matrix only for overwrite mode,
			// where the anchor row is known statically; append's base row is
			// resolved at execute time, so the preview leaves the matrix bare
			// (the merges / sizes ops below still render).
			_, col0, row0, _ := sheetAnchor(s)
			_ = applyWorkbookCreateStylesToMatrix(matrix, sheetStyles.styleFor(i), col0, row0, fmt.Sprintf("--styles for sheet %q", s.Name))
		}
		input := map[string]interface{}{
			"excel_id":   token,
			"sheet_name": s.Name,
			"range":      rng,
			"cells":      matrix,
		}
		if s.AllowOverwrite != nil && !*s.AllowOverwrite {
			input["allow_overwrite"] = false
		}
		wireBody, _ := buildToolBody("set_cell_range", input)
		dry.POST(toolInvokePath(token, ToolKindWrite)).Desc(desc).Body(wireBody)
		appendWorkbookCreateVisualOpsDryRun(dry, token, "", s.Name, sheetStyles.styleFor(i))
	}
	return dry
}

// ─── +table-get (typed read-back, mirror of +table-put) ───────────────
//
// Reads a spreadsheet's sheets back into the same typed protocol +table-put
// consumes, so the output round-trips: pipe it straight back to +table-put, or
// load it into a DataFrame. Column types are inferred from each column's
// number_format (a date format → date, numeric/percent → number) and date
// serials are converted back to ISO strings — the exact inverse of the put path.

// TableGet reads sheets back into the typed table protocol.
var TableGet = common.Shortcut{
	Service:     "sheets",
	Command:     "+table-get",
	Description: "Read sheets back into the typed table protocol (mirror of +table-put); column types are inferred from number_format so the output feeds straight to +table-put or a DataFrame.",
	Risk:        "read",
	Scopes:      []string{"sheets:spreadsheet:read"},
	AuthTypes:   []string{"user", "bot"},
	HasFormat:   true,
	Flags:       flagsFor("+table-get"),
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := resolveSpreadsheetToken(runtime); err != nil {
			return err
		}
		if strings.TrimSpace(runtime.Str("sheet-id")) != "" && strings.TrimSpace(runtime.Str("sheet-name")) != "" {
			return common.ValidationErrorf("--sheet-id and --sheet-name are mutually exclusive")
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token, _ := resolveSpreadsheetToken(runtime)
		dry := common.NewDryRunAPI()
		// get_workbook_structure runs on every path now (not just whole-workbook):
		// the single-sheet selector path also reads it to learn the grid's physical
		// dimensions, which anchor the default used-range probe over the full grid.
		body, _ := buildToolBody("get_workbook_structure", map[string]interface{}{"excel_id": token})
		dry.POST(toolInvokePath(token, ToolKindRead)).Desc("read sub-sheets + grid dimensions via get_workbook_structure").Body(body)
		rng := strings.TrimSpace(runtime.Str("range"))
		if rng == "" {
			rng = "<each sheet's used range (full-grid current_region)>"
		}
		// Mirror the selector the Execute path will pass to get_cell_ranges so the
		// dry-run body matches the real request shape; agents that validate one and
		// run the other would otherwise see a sheet_id/sheet_name field appear out
		// of nowhere. Network-free: only echoes the flags the caller already gave.
		input := map[string]interface{}{
			"excel_id": token, "ranges": []string{rng},
			"include_styles": true, "value_render_option": "raw_value",
		}
		sheetSelectorForToolInput(input,
			strings.TrimSpace(runtime.Str("sheet-id")),
			strings.TrimSpace(runtime.Str("sheet-name")),
		)
		body, _ = buildToolBody("get_cell_ranges", input)
		dry.POST(toolInvokePath(token, ToolKindRead)).
			Desc(fmt.Sprintf("read cells (%s) + styles via get_cell_ranges, then infer column types", rng)).
			Body(body)
		return dry
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, err := resolveSpreadsheetTokenExec(runtime)
		if err != nil {
			return err
		}
		targets, err := tableGetTargets(ctx, runtime, token)
		if err != nil {
			return err
		}
		noHeader := runtime.Bool("no-header")
		userRange := strings.TrimSpace(runtime.Str("range"))
		sheets := make([]interface{}, 0, len(targets))
		for _, t := range targets {
			spec, err := readSheetAsSpec(ctx, runtime, token, t, userRange, noHeader)
			if err != nil {
				return err
			}
			sheets = append(sheets, spec)
		}
		runtime.Out(map[string]interface{}{"sheets": sheets}, nil)
		return nil
	},
	Tips: []string{
		"Output is the same shape +table-put consumes — pipe it back in, or load sheets[].rows into a DataFrame keyed by columns[].name.",
		"Column types are inferred per column, but only when every non-empty cell agrees; a column mixing types (e.g. numbers + \"暂无\") degrades to string — lossless and round-trips cleanly. Numeric coercion of dirty cells is the caller's job (pandas to_numeric(errors=\"coerce\") on the string column).",
	},
}

// tableGetSheet identifies a sheet to read back.
//
// rowCount / colCount carry the sheet's physical grid dimensions
// (get_workbook_structure's row_count / column_count). They're used to anchor
// the default used-range probe over the whole grid instead of A1 — see
// sheetCurrentRegion. 0 means "unknown" (structure read skipped or failed), in
// which case the probe falls back to the legacy A1 anchor.
type tableGetSheet struct {
	id       string
	name     string
	rowCount int
	colCount int
}

// tableGetTargets resolves which sheets +table-get reads: the one named by
// --sheet-id / --sheet-name, or every sheet (in workbook order) when neither is
// given. Either way it reads get_workbook_structure once so each target carries
// its physical grid dimensions (rowCount / colCount) — the default used-range
// probe needs them to anchor over the full grid. For the single-sheet selector
// path this also backfills the missing name (handy when only --sheet-id was
// given, so the output spec isn't left nameless).
func tableGetTargets(ctx context.Context, runtime *common.RuntimeContext, token string) ([]tableGetSheet, error) {
	id := strings.TrimSpace(runtime.Str("sheet-id"))
	name := strings.TrimSpace(runtime.Str("sheet-name"))

	out, err := callTool(ctx, runtime, token, ToolKindRead, "get_workbook_structure", map[string]interface{}{"excel_id": token})
	if err != nil {
		// Single-sheet selector path can degrade gracefully without dimensions
		// (the probe falls back to the A1 anchor); the whole-workbook path can't
		// enumerate sheets without the structure, so it must surface the error.
		// Name doubles as id for --sheet-id so the output spec is never nameless
		// (an empty name would break +table-get → +table-put round-trip — the
		// writer requires a non-empty sheet name).
		if id != "" {
			return []tableGetSheet{{id: id, name: id}}, nil
		}
		if name != "" {
			return []tableGetSheet{{name: name}}, nil
		}
		return nil, err
	}
	m, _ := out.(map[string]interface{})
	raw, _ := m["sheets"].([]interface{})

	// Selector path: find the matching sheet to pick up its dimensions (and
	// backfill name when only --sheet-id was given). If no row matches, fall
	// back to a dimensionless target so the read still proceeds via the A1
	// anchor rather than erroring on a structure/selector mismatch — same
	// id-as-name backfill applies so the output spec round-trips.
	if id != "" || name != "" {
		for _, r := range raw {
			sid, sname, rc, cc := tableGetSheetMeta(r)
			if (id != "" && sid == id) || (name != "" && sname == name) {
				return []tableGetSheet{{id: sid, name: sname, rowCount: rc, colCount: cc}}, nil
			}
		}
		if id != "" {
			return []tableGetSheet{{id: id, name: id}}, nil
		}
		return []tableGetSheet{{name: name}}, nil
	}

	targets := make([]tableGetSheet, 0, len(raw))
	for _, r := range raw {
		sid, sname, rc, cc := tableGetSheetMeta(r)
		if sid != "" {
			targets = append(targets, tableGetSheet{id: sid, name: sname, rowCount: rc, colCount: cc})
		}
	}
	if len(targets) == 0 {
		return nil, errs.NewInternalError(errs.SubtypeInvalidResponse, "get_workbook_structure returned no sheets")
	}
	return targets, nil
}

// tableGetSheetMeta pulls one sheet's id, name (title fallback), and physical
// grid dimensions out of a get_workbook_structure sheets[] entry. Dimensions
// default to 0 ("unknown") when absent or non-numeric.
func tableGetSheetMeta(r interface{}) (id, name string, rowCount, colCount int) {
	sm, ok := r.(map[string]interface{})
	if !ok {
		return "", "", 0, 0
	}
	id, _ = sm["sheet_id"].(string)
	name, _ = sm["sheet_name"].(string)
	if name == "" {
		name, _ = sm["title"].(string)
	}
	if f, ok := util.ToFloat64(sm["row_count"]); ok {
		rowCount = int(f)
	}
	if f, ok := util.ToFloat64(sm["column_count"]); ok {
		colCount = int(f)
	}
	return id, name, rowCount, colCount
}

// readSheetAsSpec reads one sheet's region and rebuilds it as a typed-protocol
// sheet — the inverse of the put path and the same wire shape +table-put
// accepts: a string `columns` list, a 2D `data` matrix, and `dtypes` / `formats`
// maps keyed by column name. That symmetry lets callers round-trip via the
// pandas-native idiom
//
//	pd.DataFrame(sheet["data"], columns=sheet["columns"]).astype(sheet["dtypes"])
//
// without a custom helper. `dtypes` is always emitted (one entry per column, so
// a single `astype()` call covers every column); `formats` is emitted only for
// columns whose source cells carry a non-empty number_format, since `astype`
// ignores it and we'd rather not pollute the output.
func readSheetAsSpec(ctx context.Context, runtime *common.RuntimeContext, token string, t tableGetSheet, userRange string, noHeader bool) (map[string]interface{}, error) {
	emptySpec := func() map[string]interface{} {
		return map[string]interface{}{
			"name":    t.name,
			"columns": []interface{}{},
			"data":    []interface{}{},
			"dtypes":  map[string]interface{}{},
			"range":   "",
		}
	}
	region := userRange
	if region == "" {
		r, err := sheetCurrentRegion(ctx, runtime, token, t)
		if err != nil {
			return nil, err
		}
		region = r
	}
	if region == "" {
		return emptySpec(), nil // empty sheet
	}
	input := map[string]interface{}{
		"excel_id":            token,
		"ranges":              []string{region},
		"include_styles":      true,
		"value_render_option": "raw_value",
		"cell_limit":          unboundedReadLimit,
	}
	sheetSelectorForToolInput(input, t.id, t.name)
	out, err := callTool(ctx, runtime, token, ToolKindRead, "get_cell_ranges", input)
	if err != nil {
		return nil, err
	}
	grid := extractCellGrid(out)
	if len(grid) == 0 {
		return emptySpec(), nil
	}

	var headerRow []map[string]interface{}
	dataRows := grid
	if !noHeader {
		headerRow = grid[0]
		dataRows = grid[1:]
	}
	ncols := 0
	for _, r := range grid {
		if len(r) > ncols {
			ncols = len(r)
		}
	}

	columnNames := make([]interface{}, ncols)
	colTypes := make([]string, ncols)
	dtypes := make(map[string]interface{}, ncols)
	formats := map[string]interface{}{}
	// Duplicate header names break +table-get → +table-put round-trip: the dtypes
	// map (keyed by name) silently collapses to a single entry and the writer
	// later rejects the duplicate columns in --sheets validation. Fail fast with
	// an actionable hint when the source sheet actually has duplicate headers.
	// noHeader mode is exempt because tableGetColumnName falls back to positional
	// col<N> names which are always unique.
	seenNames := map[string]int{}
	for c := 0; c < ncols; c++ {
		typ, format := inferColumnType(dataRows, c)
		colTypes[c] = typ
		name := tableGetColumnName(headerRow, c, noHeader)
		if !noHeader {
			if prev, dup := seenNames[name]; dup {
				return nil, common.ValidationErrorf(
					"sheet %q: duplicate header column name %q at columns %d and %d; this would break the +table-get → +table-put round-trip. Rename the headers or pass --no-header to read by position (col1/col2/…).",
					t.name, name, prev+1, c+1)
			}
			seenNames[name] = c
		}
		columnNames[c] = name
		dtypes[name] = typeToDtype(typ)
		// Only emit a format when the column actually has one and it's not the
		// implicit text-format we paint on string columns (the `@` is a writer
		// convention, not user intent — surfacing it would round-trip back as
		// an explicit format the user never set).
		if format != "" && !isTextNumberFormat(format) {
			formats[name] = format
		}
	}

	data := make([][]interface{}, 0, len(dataRows))
	for _, r := range dataRows {
		row := make([]interface{}, ncols)
		for c := 0; c < ncols; c++ {
			row[c] = cellToTyped(cellAt(r, c), colTypes[c])
		}
		data = append(data, row)
	}
	spec := map[string]interface{}{
		"name":    t.name,
		"columns": columnNames,
		"data":    data,
		"dtypes":  dtypes,
		// The range actually read — whether from --range or the computed used
		// range. get_cell_ranges has no has_more flag, so this is the only signal
		// a caller has to detect truncation (compare its extent against the source
		// xlsx / +workbook-info). Harmless on round-trip: +table-put ignores it.
		"range": region,
	}
	if len(formats) > 0 {
		spec["formats"] = formats
	}
	return spec, nil
}

// sheetCurrentRegion returns the A1 range covering the sheet's existing data,
// or "" for an empty sheet.
//
// It reads get_range_as_csv's current_region, but the anchor range it requests
// is critical: current_region is the bounding box of the non-empty cells WITHIN
// the requested range, so an A1 anchor stops at the first fully-empty row or
// column and silently truncates everything past it (the pro016 / pro025
// incident). Anchoring over the whole physical grid (A1:<lastCol><lastRow>,
// from the sheet's row_count / column_count) instead makes the backend span
// internal gaps and report the true used range. When dimensions are unknown
// (structure read skipped / failed) it falls back to the legacy A1 anchor so
// the path degrades to its prior behavior rather than erroring.
func sheetCurrentRegion(ctx context.Context, runtime *common.RuntimeContext, token string, t tableGetSheet) (string, error) {
	anchor := "A1"
	if t.rowCount > 0 && t.colCount > 0 {
		anchor = "A1:" + columnIndexToLetter(t.colCount-1) + strconv.Itoa(t.rowCount)
	}
	input := map[string]interface{}{"excel_id": token, "range": anchor, "max_rows": unboundedReadLimit}
	sheetSelectorForToolInput(input, t.id, t.name)
	out, err := callTool(ctx, runtime, token, ToolKindRead, "get_range_as_csv", input)
	if err != nil {
		return "", err
	}
	m, ok := out.(map[string]interface{})
	if !ok {
		return "", nil
	}
	region, _ := m["current_region"].(string)
	if region == "" {
		region, _ = m["actual_range"].(string)
	}
	return region, nil
}

// extractCellGrid pulls ranges[0].cells out of a get_cell_ranges response into a
// 2D grid of cell objects (each carrying value + cell_styles).
func extractCellGrid(out interface{}) [][]map[string]interface{} {
	m, ok := out.(map[string]interface{})
	if !ok {
		return nil
	}
	ranges, _ := m["ranges"].([]interface{})
	if len(ranges) == 0 {
		return nil
	}
	r0, _ := ranges[0].(map[string]interface{})
	rawCells, _ := r0["cells"].([]interface{})
	grid := make([][]map[string]interface{}, 0, len(rawCells))
	for _, rr := range rawCells {
		rowArr, _ := rr.([]interface{})
		row := make([]map[string]interface{}, 0, len(rowArr))
		for _, cc := range rowArr {
			cm, _ := cc.(map[string]interface{})
			row = append(row, cm)
		}
		grid = append(grid, row)
	}
	return grid
}

func cellAt(row []map[string]interface{}, c int) map[string]interface{} {
	if c >= 0 && c < len(row) {
		return row[c]
	}
	return nil
}

func readCellValue(cell map[string]interface{}) interface{} {
	if cell == nil {
		return nil
	}
	return cell["value"]
}

func readCellFormat(cell map[string]interface{}) string {
	if cell == nil {
		return ""
	}
	st, _ := cell["cell_styles"].(map[string]interface{})
	nf, _ := st["number_format"].(string)
	return nf
}

// inferColumnType decides a column's type from its data cells: a date
// number_format guides each cell's type, but a column is given a non-string type
// only when EVERY non-empty cell agrees. Real sheet columns often mix types (a
// number column with a stray "暂无", a date column with a bare count); declaring
// number/date while a string value rides along makes the output inconsistent —
// it breaks round-trip back into +table-put (which rejects a string in a number
// column) and crashes pandas astype. So a mixed column degrades to string
// (lossless, self-consistent), keeping columns[].type faithful to every value in
// rows. Coercing dirty cells onto a numeric column is deliberately left to the
// caller (pandas to_numeric(errors="coerce") on the string column): lossless
// there — the original values stay in the frame — whereas doing it here would
// drop them silently and irrecoverably.
func inferColumnType(dataRows [][]map[string]interface{}, c int) (string, string) {
	seen := map[string]bool{}
	numberFormat, dateFormat := "", ""
	for _, r := range dataRows {
		cell := cellAt(r, c)
		v := readCellValue(cell)
		if v == nil {
			continue
		}
		if s, ok := v.(string); ok && s == "" {
			continue // empty string is empty, not a string value
		}
		nf := readCellFormat(cell)
		switch {
		case isDateNumberFormat(nf):
			// A date format yields date only when the cell is actually a serial
			// number; a date format painted on text is just text.
			if _, ok := tableGetToFloat(v); ok {
				seen["date"] = true
				if dateFormat == "" {
					dateFormat = nf
				}
			} else {
				seen["string"] = true
			}
		case isTextNumberFormat(nf):
			seen["string"] = true
		default:
			switch v.(type) {
			case float64, json.Number:
				seen["number"] = true
				if numberFormat == "" {
					numberFormat = nf
				}
			case bool:
				seen["bool"] = true
			default:
				seen["string"] = true
			}
		}
	}
	switch {
	case len(seen) == 0:
		return "string", "" // all empty
	case len(seen) == 1:
		switch {
		case seen["date"]:
			return "date", dateFormat
		case seen["number"]:
			return "number", numberFormat
		case seen["bool"]:
			return "bool", ""
		default:
			return "string", ""
		}
	default:
		return "string", "" // mixed types → string (self-consistent, lossless)
	}
}

// isDateNumberFormat reports whether a number_format denotes a date/time. Date
// formats carry a year token (Excel's 'yy' or 'yyyy'); pure numeric formats
// (#,##0, 0.00, 0.00%, @) do not.
//
// Token-aware so currency / unit prefixes that happen to contain a lone 'y' or
// 'Y' — most notably "JPY #,##0" — are not misread as dates. The scanner skips:
//   - characters inside double-quoted literals  ("Yen ")
//   - the character following a backslash escape (\y)
//   - characters inside [...] sections          ([Red], [$EUR-2])
//
// and only fires on an unquoted/unescaped/unbracketed 'yy' (a single 'y' is
// not a year token in Excel; "JPY 0" has 'Y' but never 'yy').
func isDateNumberFormat(nf string) bool {
	s := strings.ToLower(nf)
	inQuote, inBracket, escape := false, false, false
	for i := 0; i < len(s); i++ {
		c := s[i]
		if escape {
			escape = false
			continue
		}
		switch {
		case c == '\\':
			escape = true
		case !inBracket && c == '"':
			inQuote = !inQuote
		case !inQuote && c == '[':
			inBracket = true
		case !inQuote && c == ']':
			inBracket = false
		case !inQuote && !inBracket:
			if c == 'y' && i+1 < len(s) && s[i+1] == 'y' {
				return true
			}
		}
	}
	return false
}

// isTextNumberFormat reports whether a number_format is Excel/Lark text format
// ("@"), which +table-put writes on string columns so digit-like strings survive
// and read back as string instead of being inferred as number.
func isTextNumberFormat(nf string) bool {
	return strings.TrimSpace(nf) == "@"
}

// cellToTyped converts a read-back cell to the JSON-safe value its column type
// implies: date serials become ISO strings, numbers/bools pass through, empty
// cells become null, everything else is stringified. inferColumnType guarantees
// a non-string column is homogeneous, so the date/number branches never meet a
// stray off-type value.
func cellToTyped(cell map[string]interface{}, typ string) interface{} {
	v := readCellValue(cell)
	if v == nil {
		return nil
	}
	if s, ok := v.(string); ok && s == "" {
		return nil
	}
	switch typ {
	case "date":
		if f, ok := tableGetToFloat(v); ok {
			return serialToISO(f)
		}
		return v
	case "number", "bool":
		return v
	default:
		return stringifyCellValue(v)
	}
}

// tableGetColumnName returns the column's name: the header cell's text, or a
// positional col<N> when there is no header row.
func tableGetColumnName(headerRow []map[string]interface{}, c int, noHeader bool) string {
	if !noHeader {
		if v := readCellValue(cellAt(headerRow, c)); v != nil {
			if s := stringifyCellValue(v); s != "" {
				return s
			}
		}
	}
	return fmt.Sprintf("col%d", c+1)
}

func tableGetToFloat(v interface{}) (float64, bool) {
	switch n := v.(type) {
	case float64:
		return n, true
	case json.Number:
		f, err := n.Float64()
		return f, err == nil
	}
	return 0, false
}

// serialToISO converts an Excel serial day number back to an ISO yyyy-mm-dd
// string — the inverse of isoDateToSerial.
func serialToISO(serial float64) string {
	return excelEpoch.AddDate(0, 0, int(serial)).Format("2006-01-02")
}