main.py

"""
main.py  —  Pending Order Report (standalone, no Flask)
────────────────────────────────────────────────────────
Place your files in the  data/  folder next to this script:

  data/
    ov.xlsx              ← OV Pending Order Report        (required)
    fa.xlsx              ← FA Invoice Report               (required)
    ageing.xlsx          ← Ageing & Open Invoices Report   (required)
    item_prices.xlsx     ← Item Prices                     (required)
    inventory.xlsx       ← Inventory Query                 (optional)

Then run:
    python main.py

The output will be saved to:
    outputs/DD.MM.YYYY - Pending Order Report.xlsx
"""

import os
import sys
from datetime import datetime
from pathlib import Path

# ── Resolve paths ─────────────────────────────────────────────────────────────
ROOT        = Path(__file__).parent.resolve()
DATA_DIR    = ROOT / "data"
OUTPUT_DIR  = ROOT / "outputs"

REQUIRED = {
    "ov_file":      DATA_DIR / "ov.xlsx",
    "fa_file":      DATA_DIR / "fa.xlsx",
    "ageing_file":  DATA_DIR / "ageing.xlsx",
    "prices_file":  DATA_DIR / "item_prices.xlsx",
}
OPTIONAL = {
    "inventory_file": DATA_DIR / "inventory.xlsx",
}

LABEL = {
    "ov_file":        "OV Pending Order Report  →  data/ov.xlsx",
    "fa_file":        "FA Invoice Report         →  data/fa.xlsx",
    "ageing_file":    "Ageing Report             →  data/ageing.xlsx",
    "prices_file":    "Item Prices               →  data/item_prices.xlsx",
    "inventory_file": "Inventory Query           →  data/inventory.xlsx",
}


def _sep(char="─", n=60):
    print(char * n)


def _check_files():
    """Verify required files exist; warn about missing optional ones."""
    _sep()
    print("  Checking input files …")
    _sep()

    missing_required = []
    for key, path in REQUIRED.items():
        exists = path.exists()
        mark   = "✓" if exists else "✗"
        print(f"  [{mark}]  {LABEL[key]}")
        if not exists:
            missing_required.append(key)

    for key, path in OPTIONAL.items():
        exists = path.exists()
        mark   = "✓" if exists else "–"
        note   = "" if exists else "  (optional — stock check skipped)"
        print(f"  [{mark}]  {LABEL[key]}{note}")

    _sep()

    if missing_required:
        print("\n  ✗  Missing required files:")
        for key in missing_required:
            print(f"       {LABEL[key]}")
        print(
            "\n  Place the files in the  data/  folder and re-run.\n"
            "  Rename them exactly as shown above.\n"
        )
        sys.exit(1)

    print("  All required files found.\n")


def main():
    print()
    print("  Pending Order Report Generator")
    print()

    # ── 1. Check files ────────────────────────────────────────────────────────
    DATA_DIR.mkdir(exist_ok=True)
    OUTPUT_DIR.mkdir(exist_ok=True)
    _check_files()

    # ── 2. Import compare (after confirming files, so import errors are clean) ─
    try:
        from compare import run_comparison, build_excel, _load_item_prices
    except ImportError as e:
        print(f"  ✗  Could not import compare.py: {e}")
        print("     Make sure compare.py is in the same folder as main.py.")
        sys.exit(1)

    inventory_path = OPTIONAL["inventory_file"] if OPTIONAL["inventory_file"].exists() else None

    # ── 3. Run comparison ─────────────────────────────────────────────────────
    _sep()
    print("  Running comparison …")
    _sep()

    try:
        findings_df, ov_df, fa_df, \
            ref_customers_df, ref_overdue_df, ref_inventory_df = run_comparison(
                str(REQUIRED["ov_file"]),
                str(REQUIRED["fa_file"]),
                customers_path=None,
                ageing_path=str(REQUIRED["ageing_file"]),
                inventory_path=str(inventory_path) if inventory_path else None,
            )
    except Exception as e:
        import traceback
        print(f"\n  ✗  Error during comparison:\n")
        traceback.print_exc()
        print()
        sys.exit(1)

    total_rows     = len(findings_df)
    invoiced       = (findings_df["Status"] == "INVOICED").sum()          if total_rows else 0
    not_invoiced   = (findings_df["Status"] == "NOT INVOICED").sum()      if total_rows else 0
    partial        = (findings_df["Status"] == "PARTIALLY INVOICED").sum() if total_rows else 0
    over           = (findings_df["Status"] == "OVER-INVOICED").sum()     if total_rows else 0

    print(f"  Orders processed : {total_rows}")
    print(f"  Invoiced         : {invoiced}")
    print(f"  Partially        : {partial}")
    print(f"  Not invoiced     : {not_invoiced}")
    print(f"  Over-invoiced    : {over}")
    print()

    # ── 4. Load prices & build Excel ──────────────────────────────────────────
    _sep()
    print("  Building Excel workbook …")
    _sep()

    try:
        price_map = _load_item_prices(str(REQUIRED["prices_file"]))
        buf = build_excel(
            findings_df, ov_df, fa_df, price_map,
            ref_customers_df=ref_customers_df,
            ref_overdue_df=ref_overdue_df,
            ref_inventory_df=ref_inventory_df,
        )
    except Exception as e:
        import traceback
        print(f"\n  ✗  Error building Excel:\n")
        traceback.print_exc()
        print()
        sys.exit(1)

    # ── 5. Save output ────────────────────────────────────────────────────────
    now      = datetime.now()
    filename = f"{now.strftime('%d.%m.%Y')} - Pending Order Report.xlsx"
    out_path = OUTPUT_DIR / filename

    with open(out_path, "wb") as f:
        f.write(buf.read())

    _sep()
    print(f"  ✓  Report saved to:\n")
    print(f"     {out_path}")
    print()
    _sep()
    print()


if __name__ == "__main__":
    main()