"""
Convert shapes/arrows in an Excel workbook into Mermaid flowchart diagrams.

Thin CLI wrapper around ``src.extraction_v2.mermaid_flowchart_extractor``.
All detection / mermaid emission / cell-mining logic lives in that module;
this script handles only:
  1. LibreOffice conversion of .xls/.xlsm sources.
  2. CLI arg parsing.
  3. Output formatting (markdown file, JSON sidecar, or stdout).

Usage:
    uv run python scripts/excel_to_mermaid.py <input> [--output OUT.md]
"""

from __future__ import annotations

import argparse
import json
import logging
import os
import shutil
import subprocess
import sys
import tempfile
from pathlib import Path
from typing import List, Optional

sys.path.insert(0, str(Path(__file__).parent.parent))

from src.extraction_v2.mermaid_flowchart_extractor import (
    GraphResult,
    extract_mermaid_graphs,
)

logger = logging.getLogger("excel_to_mermaid")


def _convert_to_xlsx(src: str, out_dir: str) -> str:
    candidates = [
        "libreoffice", "soffice",
        "/usr/bin/libreoffice", "/usr/bin/soffice",
        "/Applications/LibreOffice.app/Contents/MacOS/soffice",
    ]
    cmd = next((c for c in candidates if os.path.exists(c) or shutil.which(c)), None)
    if cmd is None:
        raise RuntimeError("LibreOffice not found; install with apt-get install libreoffice")
    result = subprocess.run(
        [cmd, "--headless", "--convert-to", "xlsx", "--outdir", out_dir, src],
        capture_output=True, text=True, timeout=180,
    )
    if result.returncode != 0:
        raise RuntimeError(f"LibreOffice convert failed: {result.stderr.strip()}")
    base = os.path.splitext(os.path.basename(src))[0]
    out = os.path.join(out_dir, base + ".xlsx")
    if not os.path.exists(out):
        raise RuntimeError(f"Converted file missing: {out}")
    return out


def run(input_path: str, output_path: Optional[str], direction: str,
        json_output: Optional[str]) -> int:
    if not os.path.exists(input_path):
        logger.error("Input not found: %s", input_path)
        return 2

    ext = Path(input_path).suffix.lower()
    workdir = tempfile.mkdtemp(prefix="excel_to_mermaid_")
    try:
        if ext in (".xls", ".xlsm"):
            xlsx_path = _convert_to_xlsx(input_path, workdir)
        elif ext == ".xlsx":
            xlsx_path = input_path
        else:
            logger.error("Unsupported extension: %s (need .xls/.xlsm/.xlsx)", ext)
            return 2

        per_sheet = extract_mermaid_graphs(xlsx_path, direction=direction)

        all_graphs: List[GraphResult] = []
        for sheet_name, (graphs, _consumed) in per_sheet.items():
            if not graphs:
                logger.info("Sheet %r: no graphs", sheet_name)
                continue
            all_graphs.extend(graphs)

        if not all_graphs:
            logger.warning("No mermaid output (no graphs found)")
            return 1

        if output_path:
            os.makedirs(os.path.dirname(os.path.abspath(output_path)), exist_ok=True)
            with open(output_path, "w", encoding="utf-8") as f:
                for g in all_graphs:
                    loc = f" — {g.location}" if g.location else ""
                    f.write(f"## {g.sheet}{loc}\n\n")
                    f.write("```mermaid\n")
                    f.write(g.mermaid_diagram)
                    f.write("\n```\n\n")
            logger.info("Wrote %s (%d graph(s))", output_path, len(all_graphs))
        else:
            for g in all_graphs:
                loc = f" — {g.location}" if g.location else ""
                print(f"\n===== {g.sheet}{loc} =====")
                print("```mermaid")
                print(g.mermaid_diagram)
                print("```")

        if json_output:
            payload = [
                {"sheet": g.sheet, "location": g.location, "mermaid_diagram": g.mermaid_diagram}
                for g in all_graphs
            ]
            os.makedirs(os.path.dirname(os.path.abspath(json_output)), exist_ok=True)
            with open(json_output, "w", encoding="utf-8") as f:
                json.dump(payload, f, ensure_ascii=False, indent=2)
            logger.info("Wrote %s", json_output)

        return 0
    finally:
        shutil.rmtree(workdir, ignore_errors=True)


def main() -> int:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("input", help="Input .xls/.xlsm/.xlsx")
    ap.add_argument("--output", help="Output .md (default: stdout)")
    ap.add_argument("--json", dest="json_output",
                    help="Also write a JSON list of {sheet, location, mermaid_diagram}")
    ap.add_argument("--direction", default="TD", choices=["LR", "TD", "TB", "RL", "BT"],
                    help="Mermaid flowchart direction (default: TD)")
    ap.add_argument("-v", "--verbose", action="store_true")
    args = ap.parse_args()

    logging.basicConfig(
        level=logging.DEBUG if args.verbose else logging.INFO,
        format="%(asctime)s %(levelname)s %(name)s: %(message)s",
    )
    return run(args.input, args.output, args.direction, args.json_output)


if __name__ == "__main__":
    sys.exit(main())