#!/usr/bin/env python3
"""
xls-to-csv: Convert Excel workbooks (.xlsx / .xls / .xlsm / .xlsb) to CSV.

Each worksheet becomes a UTF-8 CSV. A single-sheet workbook produces
"<basename>.csv"; a multi-sheet workbook produces "<basename> - <SheetName>.csv"
per sheet. On successful conversion (all sheets written) the original source
file can be moved to a "processed" folder.

Uses Excel itself (COM, headless), so it handles modern OOXML workbooks and
legacy binary .xls (OLE2) alike, including files that carry a .xlsx extension
but are actually legacy .xls internally.

Usage:
  python convert.py --input "C:\\path\\book.xlsx" --out "C:\\out"
  python convert.py --input-dir "C:\\src" --out "C:\\out" \
      --processed-dir "C:\\src\\Processed" [--skip-existing]

Prints a JSON summary to stdout and per-file progress to stderr.

Requires: Windows + Microsoft Excel + pywin32.
"""

import argparse
import json
import os
import re
import shutil
import sys
import tempfile

XL_CSV_UTF8 = 62   # xlCSVUTF8 (Excel 2016+)
XL_CSV = 6         # xlCSV (legacy, local encoding) fallback
XL_SHEET_VISIBLE = -1

_excel_app = [None]  # lazily-created, reused within one process


def log(msg):
    sys.stderr.write(msg + "\n")
    sys.stderr.flush()


def get_excel():
    if _excel_app[0] is None:
        import win32com.client
        from win32com.client import dynamic
        # Force late-binding (dynamic) dispatch. A stale/partial win32com gen_py
        # early-binding cache can intermittently raise "'bool' object is not
        # callable" on Excel COM calls; dynamic dispatch sidesteps that entirely.
        app = dynamic.Dispatch("Excel.Application")
        try:
            app.Visible = False
        except Exception:
            pass
        try:
            app.DisplayAlerts = False
        except Exception:
            pass
        try:
            app.AskToUpdateLinks = False
        except Exception:
            pass
        _excel_app[0] = app
    return _excel_app[0]


def safe_component(name):
    """Sanitize a worksheet name so it is safe as a filename component."""
    name = re.sub(r'[\\/:*?"<>|]', "_", name)
    name = name.strip().strip(".")
    return name or "Sheet"


def sheet_has_data(ws):
    try:
        ur = ws.UsedRange
        if ur is None:
            return False
        if ur.Rows.Count > 1 or ur.Columns.Count > 1:
            return True
        val = ur.Cells(1, 1).Value
        return val is not None and str(val) != ""
    except Exception:
        return True  # if unsure, keep it


def save_sheet_csv(app, ws, csv_path):
    """Copy a single worksheet to a new workbook and save it as UTF-8 CSV."""
    ws.Copy()  # creates a new workbook containing only this sheet (now active)
    newwb = app.ActiveWorkbook
    try:
        try:
            newwb.SaveAs(csv_path, FileFormat=XL_CSV_UTF8)
        except Exception:
            newwb.SaveAs(csv_path, FileFormat=XL_CSV)  # fallback to local encoding
    finally:
        newwb.Close(SaveChanges=False)


def move_to_processed(src, processed_dir):
    os.makedirs(processed_dir, exist_ok=True)
    base = os.path.basename(src)
    stem, ext = os.path.splitext(base)
    target = os.path.join(processed_dir, base)
    n = 1
    while os.path.exists(target):
        target = os.path.join(processed_dir, "%s (%d)%s" % (stem, n, ext))
        n += 1
    shutil.move(src, target)
    return target


def convert_one(src, out_dir, processed_dir, tmp_root):
    """Convert one workbook. Returns a result dict."""
    basename = os.path.splitext(os.path.basename(src))[0]
    work = tempfile.mkdtemp(prefix="xls2csv_", dir=tmp_root)
    app = get_excel()
    try:
        # Copy locally first: hydrates OneDrive cloud-only files, avoids locks.
        local = os.path.join(work, os.path.basename(src))
        shutil.copy2(src, local)

        wb = app.Workbooks.Open(local, ReadOnly=True, UpdateLinks=0)
        outputs = []
        try:
            worksheets = [ws for ws in wb.Worksheets]
            data_sheets = [ws for ws in worksheets if sheet_has_data(ws)]
            if not data_sheets:
                data_sheets = worksheets  # nothing looked non-empty; emit anyway
            multi = len(data_sheets) > 1

            os.makedirs(out_dir, exist_ok=True)
            used_names = set()
            for ws in data_sheets:
                if multi:
                    comp = safe_component(ws.Name)
                    stem = "%s - %s" % (basename, comp)
                else:
                    stem = basename
                # Avoid filename collisions between sheets.
                candidate = stem
                k = 1
                while candidate.lower() in used_names:
                    candidate = "%s (%d)" % (stem, k)
                    k += 1
                used_names.add(candidate.lower())

                csv_path = os.path.join(out_dir, candidate + ".csv")
                save_sheet_csv(app, ws, csv_path)
                outputs.append(csv_path)
        finally:
            wb.Close(False)

        if not outputs or not all(os.path.exists(p) and os.path.getsize(p) >= 0 for p in outputs):
            raise RuntimeError("no CSV output produced")

        moved_to = None
        if processed_dir:
            moved_to = move_to_processed(os.path.abspath(src), processed_dir)

        return {"file": os.path.basename(src), "outputs": outputs,
                "sheets": len(outputs), "moved_to": moved_to}
    finally:
        shutil.rmtree(work, ignore_errors=True)


def gather_inputs(args):
    exts = (".xlsx", ".xls", ".xlsm", ".xlsb")
    inputs = list(args.input or [])
    if args.input_dir:
        for name in sorted(os.listdir(args.input_dir)):
            if name.startswith("~$"):
                continue  # Office lock/temp file
            full = os.path.join(args.input_dir, name)
            if os.path.isfile(full) and name.lower().endswith(exts):
                inputs.append(full)
    seen, ordered = set(), []
    for p in inputs:
        ap = os.path.abspath(p)
        if ap not in seen:
            seen.add(ap)
            ordered.append(ap)
    return ordered


def main():
    ap = argparse.ArgumentParser(description="Convert Excel workbooks to CSV (one CSV per sheet).")
    ap.add_argument("--input", action="append", default=[],
                    help="Path to a workbook. Repeat for multiple files.")
    ap.add_argument("--input-dir", default="",
                    help="Convert every workbook in this folder (non-recursive).")
    ap.add_argument("--out", required=True, help="Output directory for .csv files.")
    ap.add_argument("--processed-dir", default="",
                    help="If set, move each source file here after a successful conversion.")
    ap.add_argument("--skip-existing", action="store_true",
                    help="Skip a workbook if its primary output .csv already exists.")
    args = ap.parse_args()

    import pythoncom
    pythoncom.CoInitialize()

    inputs = gather_inputs(args)
    if not inputs:
        print(json.dumps({"processed": [], "errors": [
            {"file": "", "error": "no input files (use --input or --input-dir)"}]}))
        return 1

    total = len(inputs)
    processed, errors, skipped = [], [], []
    tmp_root = tempfile.gettempdir()

    try:
        for i, src in enumerate(inputs, start=1):
            name = os.path.basename(src)
            if not os.path.exists(src):
                errors.append({"file": src, "error": "not found"})
                log("[%d/%d] SKIP (not found): %s" % (i, total, name))
                continue
            if args.skip_existing:
                primary = os.path.join(args.out, os.path.splitext(name)[0] + ".csv")
                if os.path.exists(primary) and os.path.getsize(primary) > 0:
                    skipped.append(name)
                    log("[%d/%d] skip (exists): %s" % (i, total, name))
                    continue
            log("[%d/%d] converting: %s ..." % (i, total, name))
            try:
                res = convert_one(src, args.out, args.processed_dir, tmp_root)
                processed.append(res)
                extra = " -> moved" if res.get("moved_to") else ""
                log("[%d/%d] done: %s (%d sheet CSV)%s" % (i, total, name, res["sheets"], extra))
            except Exception as e:
                errors.append({"file": name, "error": repr(e)})
                log("[%d/%d] ERROR: %s -> %r" % (i, total, name, e))
    finally:
        if _excel_app[0] is not None:
            try:
                _excel_app[0].Quit()
            except Exception:
                pass
        pythoncom.CoUninitialize()

    log("COMPLETE: %d converted, %d errors, %d skipped (of %d)"
        % (len(processed), len(errors), len(skipped), total))
    print(json.dumps({"processed": processed, "errors": errors, "skipped": skipped}, indent=2))
    return 1 if errors and not processed else 0


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