#!/usr/bin/env python3
"""excel_batch_toolkit.py - Excel (.xlsx/.csv) + Windows batch (.bat) automation."""
from __future__ import annotations
import csv, logging, subprocess, sys
from pathlib import Path
from typing import Any, Iterable, Optional
log = logging.getLogger("excel_batch_toolkit")

def _oxl():
    try:
        import openpyxl
        return openpyxl
    except ImportError:
        raise RuntimeError("openpyxl required for .xlsx: pip install openpyxl")

def read_excel(path: str, sheet: Optional[str] = None) -> list[dict[str, Any]]:
    p = Path(path)
    if p.suffix.lower() == ".csv":
        with p.open("r", encoding="utf-8-sig", newline="") as f:
            return list(csv.DictReader(f))
    ox = _oxl(); wb = ox.load_workbook(path, data_only=True)
    ws = wb[sheet] if sheet else wb.active
    rows = list(ws.iter_rows(values_only=True))
    if not rows: return []
    headers = [str(h) if h is not None else f"col_{i}" for i, h in enumerate(rows[0])]
    return [{headers[i]: (r[i] if i < len(r) else None) for i in range(len(headers))} for r in rows[1:]]

def write_excel(path: str, rows: Iterable[dict[str, Any]], sheet: str = "Sheet1") -> str:
    rows = list(rows)
    if not rows: raise ValueError("No rows")
    ox = _oxl()
    from openpyxl.styles import Font
    wb = ox.Workbook(); ws = wb.active; ws.title = sheet
    headers = list(rows[0].keys()); ws.append(headers)
    for c in ws[1]: c.font = Font(bold=True)
    for r in rows: ws.append([r.get(h) for h in headers])
    for i, h in enumerate(headers, 1):
        w = max(len(str(h)), max((len(str(r.get(h))) if r.get(h) is not None else 0) for r in rows))
        ws.column_dimensions[ox.utils.get_column_letter(i)].width = min(w + 2, 60)
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    wb.save(path); log.info("wrote %d rows -> %s", len(rows), path)
    return str(path)

def append_excel(path: str, rows: Iterable[dict[str, Any]], sheet: Optional[str] = None) -> str:
    rows = list(rows)
    if not Path(path).exists(): return write_excel(path, rows, sheet or "Sheet1")
    ox = _oxl(); wb = ox.load_workbook(path); ws = wb[sheet] if sheet else wb.active
    headers = [c.value for c in ws[1]]
    for r in rows: ws.append([r.get(h) for h in headers])
    wb.save(path); log.info("appended %d rows -> %s", len(rows), path)
    return str(path)

def create_batch(commands: Iterable[str], path: str, echo: bool = True) -> str:
    lines = ([] if echo else ["@echo off"]) + list(commands)
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    Path(path).write_text("\r\n".join(lines) + "\r\n", encoding="utf-8")
    log.info("wrote batch %s", path)
    return str(path)

def run_batch(path_or_command: str, args: Optional[list[str]] = None, timeout: int = 300,
              cwd: Optional[str] = None, is_batch_file: bool = True) -> dict[str, Any]:
    args = args or []
    if sys.platform == "win32":
        cmd = [path_or_command, *args] if is_batch_file else ["cmd.exe", "/c", path_or_command, *args]
    else:
        joined = path_or_command + " " + " ".join(args)
        cmd = ["bash", "-c", joined]
    proc = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, cwd=cwd,
                            text=True, errors="replace")
    try:
        out, err = proc.communicate(timeout=timeout)
        return {"returncode": proc.returncode, "stdout": out, "stderr": err, "timed_out": False}
    except subprocess.TimeoutExpired:
        proc.kill(); out, err = proc.communicate()
        return {"returncode": -1, "stdout": out, "stderr": err, "timed_out": True}

def run_pipeline(config: dict) -> dict[str, Any]:
    res = {}
    for i, s in enumerate(config.get("excel", [])):
        a = s["action"]
        res[f"excel_{i}"] = (read_excel(s["path"], s.get("sheet")) if a == "read"
            else write_excel(s["path"], s["rows"], s.get("sheet", "Sheet1")) if a == "write"
            else append_excel(s["path"], s["rows"], s.get("sheet")) if a == "append"
            else (_ for _ in ()).throw(ValueError(f"bad excel action {a}")))
    for i, s in enumerate(config.get("batch", [])):
        a = s["action"]
        res[f"batch_{i}"] = (create_batch(s["commands"], s["path"], s.get("echo", True)) if a == "create"
            else run_batch(s["path"], s.get("args"), s.get("timeout", 300), s.get("cwd"), s.get("is_batch_file", True)))
    return res

if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    demo = [{"name": "alpha", "value": 1.5, "ok": True}, {"name": "beta", "value": 2.5, "ok": False}]
    write_excel("demo.xlsx", demo)
    print("read back:", read_excel("demo.xlsx"))
    create_batch(["echo hello-from-batch", "dir"], "demo.bat")
    print("batch:", run_batch("demo.bat", timeout=10))