#!/usr/bin/env -S uv run --quiet --with openpyxl python
"""
analyze_spreadsheet.py — Radiografia estrutural de uma planilha (.xlsx).

Extrai TODA a inteligência embutida numa planilha para que a skill /planilha-ao-app
possa reconstruí-la como aplicativo: abas, cabeçalhos, amostra de dados, fórmulas
(PROCV/VLOOKUP, SE/IF, índices), validações de dados (listas suspensas), intervalos
nomeados e formatação condicional.

Uso:
    ./analyze_spreadsheet.py CAMINHO.xlsx                # imprime JSON no stdout
    ./analyze_spreadsheet.py CAMINHO.xlsx --out a.json   # salva em arquivo
    ./analyze_spreadsheet.py CAMINHO.xlsx --sample 10    # nº de linhas de amostra (default 5)

Roda via uv (o Python do Homebrew está quebrado nesta máquina — pyexpat).
O shebang já cuida de instalar openpyxl num ambiente efêmero.

Para .csv use --csv. Para .ods/.xls, converta antes para .xlsx (LibreOffice headless:
  soffice --headless --convert-to xlsx arquivo.ods).
"""
import argparse
import json
import re
import sys
from pathlib import Path

# Funções de planilha que viram REGRA DE APLICAÇÃO. Mapeadas para a categoria de lógica.
FORMULA_SIGNATURES = {
    "lookup":      [r"PROCV", r"VLOOKUP", r"PROCH", r"HLOOKUP", r"ÍNDICE", r"INDEX", r"CORRESP", r"MATCH", r"PROCX", r"XLOOKUP"],
    "condicional": [r"\bSE\b", r"\bIF\b", r"SES\b", r"IFS\b", r"SEERRO", r"IFERROR", r"E\(", r"OU\(", r"AND\(", r"OR\("],
    "agregacao":   [r"SOMA", r"SUM", r"MÉDIA", r"AVERAGE", r"CONT\.", r"COUNT", r"SOMASE", r"SUMIF", r"MÁXIMO", r"MAX", r"MÍNIMO", r"MIN"],
    "texto":       [r"CONCATENAR", r"CONCAT", r"TEXTO\(", r"TEXT\(", r"ESQUERDA", r"LEFT", r"DIREITA", r"RIGHT", r"EXT\.TEXTO", r"MID"],
    "data":        [r"HOJE", r"TODAY", r"AGORA", r"NOW", r"DATADIF", r"DATEDIF", r"DIA\b", r"DAY\b", r"MÊS", r"MONTH", r"ANO", r"YEAR"],
}


def classify_formula(formula: str) -> list[str]:
    tags = []
    up = formula.upper()
    for category, patterns in FORMULA_SIGNATURES.items():
        if any(re.search(p, up) for p in patterns):
            tags.append(category)
    return tags or ["outro"]


def analyze_xlsx(path: Path, sample_rows: int) -> dict:
    from openpyxl import load_workbook

    # data_only=False para capturar fórmulas (não os valores calculados)
    wb_formulas = load_workbook(path, data_only=False, read_only=False)
    # data_only=True para capturar valores reais (amostra de dados)
    wb_values = load_workbook(path, data_only=True, read_only=True)

    result = {
        "arquivo": str(path),
        "abas": [],
        "intervalos_nomeados": [],
        "resumo": {},
    }

    # Intervalos nomeados (geralmente são tabelas de domínio / catálogos)
    for name, defn in (wb_formulas.defined_names.items() if hasattr(wb_formulas.defined_names, "items") else []):
        try:
            result["intervalos_nomeados"].append({"nome": name, "referencia": str(defn.value)})
        except Exception:
            pass

    total_formulas = 0
    total_validacoes = 0

    for ws in wb_formulas.worksheets:
        aba = {
            "nome": ws.title,
            "dimensoes": ws.dimensions,
            "linhas": ws.max_row,
            "colunas": ws.max_column,
            "cabecalhos": [],
            "amostra": [],
            "formulas": [],
            "validacoes_dados": [],
            "formatacao_condicional": [],
        }

        # Cabeçalhos: primeira linha não-vazia
        header_row = None
        for r in range(1, min(ws.max_row, 10) + 1):
            vals = [ws.cell(row=r, column=c).value for c in range(1, ws.max_column + 1)]
            if any(v not in (None, "") for v in vals):
                header_row = r
                aba["cabecalhos"] = [(str(v).strip() if v is not None else "") for v in vals]
                break

        # Amostra de dados (valores reais via data_only)
        if ws.title in wb_values.sheetnames:
            wsv = wb_values[ws.title]
            start = (header_row or 1) + 1
            collected = 0
            for row in wsv.iter_rows(min_row=start, values_only=True):
                if collected >= sample_rows:
                    break
                if any(v not in (None, "") for v in row):
                    aba["amostra"].append([(str(v) if v is not None else "") for v in row])
                    collected += 1

        # Fórmulas (varre células procurando '='); limita a 200 únicas por aba
        seen_formula_shapes = set()
        for row in ws.iter_rows():
            for cell in row:
                v = cell.value
                if isinstance(v, str) and v.startswith("="):
                    total_formulas += 1
                    # "shape" = fórmula com refs genéricas, pra agrupar fórmulas repetidas por coluna
                    shape = re.sub(r"[A-Z]{1,3}\d+", "REF", v)
                    if shape in seen_formula_shapes:
                        continue
                    seen_formula_shapes.add(shape)
                    if len(aba["formulas"]) < 200:
                        aba["formulas"].append({
                            "celula": cell.coordinate,
                            "formula": v,
                            "categorias": classify_formula(v),
                        })

        # Validações de dados (listas suspensas = campos de seleção no app)
        try:
            for dv in ws.data_validations.dataValidation:
                total_validacoes += 1
                aba["validacoes_dados"].append({
                    "tipo": dv.type,
                    "operador": dv.operator,
                    "formula1": dv.formula1,
                    "celulas": str(dv.sqref),
                })
        except Exception:
            pass

        # Formatação condicional (regras visuais que viram badges/estados no app)
        try:
            for range_str, rules in ws.conditional_formatting._cf_rules.items():
                aba["formatacao_condicional"].append({
                    "intervalo": str(range_str.sqref) if hasattr(range_str, "sqref") else str(range_str),
                    "qtd_regras": len(rules),
                })
        except Exception:
            pass

        result["abas"].append(aba)

    result["resumo"] = {
        "total_abas": len(result["abas"]),
        "total_formulas_celulas": total_formulas,
        "total_validacoes": total_validacoes,
        "total_intervalos_nomeados": len(result["intervalos_nomeados"]),
    }
    return result


def analyze_csv(path: Path, sample_rows: int) -> dict:
    import csv
    with open(path, newline="", encoding="utf-8-sig") as f:
        # detecta delimitador
        sniff = f.read(4096)
        f.seek(0)
        try:
            dialect = csv.Sniffer().sniff(sniff, delimiters=",;\t|")
        except csv.Error:
            dialect = csv.excel
        reader = csv.reader(f, dialect)
        rows = list(reader)
    headers = rows[0] if rows else []
    sample = rows[1:1 + sample_rows]
    return {
        "arquivo": str(path),
        "abas": [{
            "nome": path.stem,
            "linhas": len(rows),
            "colunas": len(headers),
            "cabecalhos": headers,
            "amostra": sample,
            "formulas": [],
            "validacoes_dados": [],
            "formatacao_condicional": [],
        }],
        "intervalos_nomeados": [],
        "resumo": {"total_abas": 1, "total_formulas_celulas": 0, "total_validacoes": 0, "total_intervalos_nomeados": 0},
        "observacao": "CSV não carrega fórmulas nem validações — só dados. Para capturar regras, peça a planilha original (.xlsx).",
    }


def main():
    ap = argparse.ArgumentParser(description="Radiografia estrutural de planilha para virar app.")
    ap.add_argument("arquivo", help="Caminho do .xlsx (ou .csv com --csv)")
    ap.add_argument("--out", help="Salvar JSON neste caminho (default: stdout)")
    ap.add_argument("--sample", type=int, default=5, help="Nº de linhas de amostra por aba (default 5)")
    ap.add_argument("--csv", action="store_true", help="Tratar entrada como CSV")
    args = ap.parse_args()

    path = Path(args.arquivo).expanduser()
    if not path.exists():
        print(f"ERRO: arquivo não encontrado: {path}", file=sys.stderr)
        sys.exit(1)

    if args.csv or path.suffix.lower() == ".csv":
        data = analyze_csv(path, args.sample)
    elif path.suffix.lower() in (".xlsx", ".xlsm"):
        data = analyze_xlsx(path, args.sample)
    else:
        print(f"ERRO: formato não suportado: {path.suffix}. Converta .ods/.xls para .xlsx primeiro.", file=sys.stderr)
        sys.exit(1)

    out = json.dumps(data, ensure_ascii=False, indent=2)
    if args.out:
        Path(args.out).write_text(out, encoding="utf-8")
        print(f"✅ Análise salva em {args.out}")
        print(f"   {data['resumo']}")
    else:
        print(out)


if __name__ == "__main__":
    main()
