#!/usr/bin/env python3
"""
Excel + Windows batch automation sample (deliverable-ready).

Reads an Excel workbook, summarizes a chosen column, exports a CSV report,
and safely invokes a Windows batch file to archive the output. Built for a
small-business workflow; no PII is assumed in the sample data.

Requires: pip install pandas openpyxl
"""
from __future__ import annotations
import argparse
import csv
import subprocess
import sys
from pathlib import Path

try:
    import pandas as pd
except ImportError:
    sys.stderr.write("Missing dependency: pip install pandas openpyxl\n")
    sys.exit(2)


def read_excel(path: str, sheet: str = 0) -> pd.DataFrame:
    """Read one sheet from an .xlsx workbook."""
    return pd.read_excel(path, sheet_name=sheet)


def summarize(df: pd.DataFrame, column: str) -> dict:
    """Return a compact summary for a named column."""
    col = df[column]
    return {
        "rows": int(len(df)),
        "non_null": int(col.notna().sum()),
        "unique": int(col.nunique()),
        "min": str(col.min()) if col.notna().any() else None,
        "max": str(col.max()) if col.notna().any() else None,
    }


def write_csv(df: pd.DataFrame, out_path: str) -> None:
    df.to_csv(out_path, index=False, quoting=csv.QUOTE_MINIMAL)


def run_batch(batch_file: str, *args: str) -> int:
    """Invoke a Windows batch file with arguments. Batch files are expected to
    be trusted local scripts; never pass untrusted user input to shell=True."""
    cmd = [batch_file, *args]
    result = subprocess.run(cmd, shell=False, capture_output=True, text=True, timeout=120)
    sys.stdout.write(result.stdout)
    sys.stderr.write(result.stderr)
    return result.returncode


def main() -> int:
    ap = argparse.ArgumentParser(description="Excel -> CSV report with optional batch archiver")
    ap.add_argument("input", help="path to input .xlsx")
    ap.add_argument("column", help="column to summarize")
    ap.add_argument("--sheet", default=0, help="sheet name or index (default 0)")
    ap.add_argument("--out", default="report.csv", help="output CSV path")
    ap.add_argument("--archive-bat", help="optional Windows batch file to run after export")
    args = ap.parse_args()

    df = read_excel(args.input, args.sheet)
    summary = summarize(df, args.column)
    write_csv(df, args.out)
    print(f"Wrote {args.out} ({len(df)} rows)")
    print("Summary:", summary)

    if args.archive_bat:
        rc = run_batch(args.archive_bat, str(Path(args.out).resolve()))
        if rc != 0:
            print(f"Batch file exited with code {rc}", file=sys.stderr)
            return rc
    return 0


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