"""Read a DreamSheets .dsheet file from Python — no DreamSheets install needed.

    import dsheet
    doc = dsheet.load("Budget.dsheet")
    doc.tables["Orders"]          # a pandas DataFrame per table, by name
    doc.calc_columns["Orders"]    # {"Revenue": "@Units * @UnitPrice", ...}
    doc.tiles["TaxRate"]          # a tile's value, or its formula text

A .dsheet is a zip: document.json (structure), formulas.json (formula text)
and one parquet file per table. Calculated columns, formula tiles and totals
rows are COMPUTED by the app and are not stored, so you get their formulas
here, not their values. Requires pandas + pyarrow (pip install pandas pyarrow).
Read-only: to write a document, emit dsdoc JSON and import it in the app
(https://www.dreamsheets.app/docs/llm-authoring).
"""
from __future__ import annotations

import io
import json
import zipfile
from dataclasses import dataclass, field

import pandas as pd

SYSTEM_COLUMNS = ("_rowid", "_insert_at")


@dataclass
class Document:
    title: str
    tables: dict[str, pd.DataFrame] = field(default_factory=dict)
    calc_columns: dict[str, dict[str, str]] = field(default_factory=dict)
    tiles: dict[str, object] = field(default_factory=dict)
    descriptions: dict[str, str] = field(default_factory=dict)


def load(path: str, keep_system_columns: bool = False) -> Document:
    with zipfile.ZipFile(path) as z:
        doc = json.loads(z.read("document.json"))
        formulas = json.loads(z.read("formulas.json")).get("formulas", {})
        names = set(z.namelist())
        # A THIN archive (a cloud version download) lists a parquet in the
        # manifest's integrity map that the zip doesn't carry — the data lives
        # in shared blobs. Refuse rather than hand back an empty table.
        manifest = json.loads(z.read("manifest.json")) if "manifest.json" in names else {}
        listed = (manifest.get("integrity") or {}).get("files") or {}
        out = Document(title=doc.get("title", ""))
        for t in doc.get("tables", []):
            p = f"data/{t['tab_id']}/{t['id']}.parquet"
            data_cols = [c["name"] for c in t["columns"] if not c.get("is_calc")]
            if p in names:
                df = pd.read_parquet(io.BytesIO(z.read(p)))
                if not keep_system_columns:
                    df = df.drop(columns=[c for c in SYSTEM_COLUMNS if c in df.columns])
            elif t.get("row_count", 0) > 0 or p in listed:
                raise ValueError(
                    f"table {t['name']!r}: its data is stored by reference, not in this file; "
                    "download the document from DreamSheets Drive to get a complete copy"
                )
            else:  # an empty table has no parquet
                df = pd.DataFrame(columns=data_cols)
            out.tables[t["name"]] = df
            calc = {
                c["name"]: formulas.get(c.get("formula_id") or "", {}).get("source", "")
                for c in t["columns"]
                if c.get("is_calc")
            }
            if calc:
                out.calc_columns[t["name"]] = calc
            if t.get("description"):
                out.descriptions[t["name"]] = t["description"]
        for tile in doc.get("tiles", []):
            if tile.get("formula_id"):
                out.tiles[tile["name"]] = "=" + formulas.get(tile["formula_id"], {}).get("source", "")
            else:
                v = tile.get("value") or {}
                out.tiles[tile["name"]] = next(iter(v.values()), None) if isinstance(v, dict) else v
        return out


if __name__ == "__main__":
    import sys

    sys.stdout.reconfigure(errors="replace")  # a cp1252 console must not crash on an arrow glyph
    doc = load(sys.argv[1])
    print(doc.title or sys.argv[1])
    for name, df in doc.tables.items():
        calc = doc.calc_columns.get(name, {})
        print(f"  {name}: {len(df)} rows, columns {list(df.columns)}" + (f", calc {calc}" if calc else ""))
    for name, v in doc.tiles.items():
        print(f"  tile {name} = {v!r}")