#!/usr/bin/env python3
"""Small OpenClaw helper for local spreadsheet inspection and conversion."""

from __future__ import annotations

import argparse
import csv
import json
import sys
from pathlib import Path
from typing import Any


def require_openpyxl():
    try:
        import openpyxl  # type: ignore
    except ImportError:
        print("ERROR: openpyxl is required. Install with: python -m pip install openpyxl", file=sys.stderr)
        sys.exit(2)
    return openpyxl


def require_pandas():
    try:
        import pandas as pd  # type: ignore
    except ImportError:
        print("ERROR: pandas is required. Install with: python -m pip install pandas openpyxl", file=sys.stderr)
        sys.exit(2)
    return pd


def detect_delimiter(path: Path) -> str:
    sample = path.read_text(encoding="utf-8-sig", errors="replace")[:8192]
    try:
        dialect = csv.Sniffer().sniff(sample, delimiters=",\t;|")
        return dialect.delimiter
    except csv.Error:
        return "\t" if path.suffix.lower() == ".tsv" else ","


def cell_value_preview(value: Any) -> Any:
    if value is None:
        return None
    if isinstance(value, (str, int, float, bool)):
        text = str(value)
        return text[:120] + "..." if len(text) > 120 else value
    return str(value)


def inspect_workbook(path: Path) -> None:
    openpyxl = require_openpyxl()
    wb = openpyxl.load_workbook(path, data_only=False, read_only=False)
    payload: dict[str, Any] = {
        "file": str(path),
        "sheets": [],
    }
    for ws in wb.worksheets:
        formulas = []
        non_empty_rows = 0
        non_empty_cols: set[int] = set()
        preview = []
        for row in ws.iter_rows():
            row_values = [cell.value for cell in row]
            if any(value is not None for value in row_values):
                non_empty_rows += 1
                for cell in row:
                    if cell.value is not None:
                        non_empty_cols.add(cell.column)
                    if isinstance(cell.value, str) and cell.value.startswith("="):
                        formulas.append(cell.coordinate)
            if len(preview) < 5 and any(value is not None for value in row_values):
                preview.append([cell_value_preview(value) for value in row_values[:12]])
        payload["sheets"].append(
            {
                "name": ws.title,
                "max_row": ws.max_row,
                "max_column": ws.max_column,
                "non_empty_rows": non_empty_rows,
                "non_empty_columns": len(non_empty_cols),
                "merged_ranges": [str(rng) for rng in ws.merged_cells.ranges],
                "freeze_panes": str(ws.freeze_panes) if ws.freeze_panes else None,
                "auto_filter": ws.auto_filter.ref,
                "formula_count": len(formulas),
                "formula_cells_sample": formulas[:20],
                "preview": preview,
            }
        )
    print(json.dumps(payload, ensure_ascii=False, indent=2))


def csv_to_xlsx(input_path: Path, output_path: Path) -> None:
    pd = require_pandas()
    delimiter = detect_delimiter(input_path)
    df = pd.read_csv(input_path, sep=delimiter, dtype=str, keep_default_na=False)
    output_path.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="Data", index=False)
    print(json.dumps({"output": str(output_path), "rows": int(len(df)), "columns": int(len(df.columns))}, ensure_ascii=False))


def xlsx_to_csv(input_path: Path, output_dir: Path) -> None:
    pd = require_pandas()
    output_dir.mkdir(parents=True, exist_ok=True)
    sheets = pd.read_excel(input_path, sheet_name=None, dtype=str, keep_default_na=False)
    outputs = []
    for sheet_name, df in sheets.items():
        safe_name = "".join(ch if ch.isalnum() or ch in "-_." else "_" for ch in sheet_name).strip("_") or "sheet"
        out = output_dir / f"{safe_name}.csv"
        df.to_csv(out, index=False, encoding="utf-8-sig")
        outputs.append({"sheet": sheet_name, "output": str(out), "rows": int(len(df)), "columns": int(len(df.columns))})
    print(json.dumps({"outputs": outputs}, ensure_ascii=False, indent=2))


def validate_xlsx(path: Path) -> None:
    openpyxl = require_openpyxl()
    wb = openpyxl.load_workbook(path, data_only=False, read_only=True)
    print(json.dumps({"file": str(path), "ok": True, "sheets": wb.sheetnames}, ensure_ascii=False, indent=2))


def main() -> int:
    parser = argparse.ArgumentParser(description="OpenClaw XLSX helper")
    sub = parser.add_subparsers(dest="command", required=True)

    p = sub.add_parser("inspect", help="Inspect workbook structure")
    p.add_argument("input")

    p = sub.add_parser("validate", help="Check that an XLSX workbook can be opened")
    p.add_argument("input")

    p = sub.add_parser("csv-to-xlsx", help="Convert CSV/TSV to XLSX")
    p.add_argument("input")
    p.add_argument("output")

    p = sub.add_parser("xlsx-to-csv", help="Export each XLSX sheet to CSV")
    p.add_argument("input")
    p.add_argument("output_dir")

    args = parser.parse_args()
    if args.command == "inspect":
        inspect_workbook(Path(args.input))
    elif args.command == "validate":
        validate_xlsx(Path(args.input))
    elif args.command == "csv-to-xlsx":
        csv_to_xlsx(Path(args.input), Path(args.output))
    elif args.command == "xlsx-to-csv":
        xlsx_to_csv(Path(args.input), Path(args.output_dir))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())
