"""Merge many Excel workbooks into one sheet, keeping track of provenance.

Fixes over the naive version: it records which file each row came from, warns
when the workbooks do not share the same columns (silent concat produces a sheet
full of NaN otherwise), skips Excel's lock files, and refuses to write into the
folder it is reading from.

    python3 excel_merger.py ./excel_reports --out merged.xlsx

Requires: pandas, openpyxl
"""

from __future__ import annotations

import argparse
import sys
from pathlib import Path

import pandas as pd

SUFFIXES = {".xlsx", ".xls"}


def workbook_paths(folder: Path) -> list[Path]:
    return sorted(
        p for p in folder.iterdir()
        if p.suffix.lower() in SUFFIXES and not p.name.startswith(("~$", "."))
    )


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Merge Excel workbooks into one sheet.")
    parser.add_argument("folder", type=Path, help="Folder containing the workbooks")
    parser.add_argument("--out", type=Path, default=Path("merged.xlsx"), help="Output file")
    parser.add_argument("--sheet", default=0, help="Sheet name or index to read (default: first)")
    args = parser.parse_args(argv)

    folder: Path = args.folder.expanduser()
    if not folder.is_dir():
        print(f"{folder} is not a directory", file=sys.stderr)
        return 1

    paths = workbook_paths(folder)
    if not paths:
        print(f"No .xlsx/.xls files in {folder}", file=sys.stderr)
        return 1

    # Writing the output back into the source folder would make reruns compound.
    if args.out.expanduser().resolve().parent == folder.resolve():
        print("Refusing to write the merged file into the source folder.", file=sys.stderr)
        return 1

    frames: list[pd.DataFrame] = []
    column_sets: dict[str, tuple[str, ...]] = {}

    for path in paths:
        try:
            frame = pd.read_excel(path, sheet_name=args.sheet)
        except Exception as exc:
            print(f"Skipping {path.name}: {exc}", file=sys.stderr)
            continue
        frame.insert(0, "source_file", path.name)
        column_sets[path.name] = tuple(frame.columns)
        frames.append(frame)

    if not frames:
        print("Nothing could be read.", file=sys.stderr)
        return 1

    reference = next(iter(column_sets.values()))
    mismatched = [name for name, cols in column_sets.items() if cols != reference]
    if mismatched:
        print("WARNING: these workbooks have different columns, the merge will "
              "contain empty cells:", file=sys.stderr)
        for name in mismatched:
            print(f"  {name}", file=sys.stderr)

    merged = pd.concat(frames, ignore_index=True)
    merged.to_excel(args.out, index=False)
    print(f"Merged {len(frames)} workbook(s), {len(merged)} rows -> {args.out}")
    return 0


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