report_builder.py

from __future__ import annotations

from pathlib import Path

from pyxll import xl_macro, xl_app
from PIL import Image as PILImage


# --- Configure paths ---
BASE_DIR = Path(r"C:\Users\usa\Documents\steward-view-main\data\output")

LABELED_CSV = BASE_DIR / "labeled_data.csv"
SUMMARY_CSV = BASE_DIR / "summary.csv"

PNG_FILES = [
    "spending_by_spender.png",
    "spending_by_category.png",
    "spending_by_vendor.png",
]

# PNG size control (0.5 = half size, 1.0 = original size)
IMG_SCALE = 0.8


# -----------------------------
# Helpers
# -----------------------------
def _sheet_by_name(wb, name: str):
    for ws in wb.Worksheets:
        if ws.Name == name:
            return ws
    return None


def _delete_sheet_if_exists(wb, name: str):
    try:
        wb.Worksheets(name).Delete()
    except Exception:
        pass


def _clear_sheet(ws):
    ws.Cells.Clear()
    try:
        while ws.Shapes.Count > 0:
            ws.Shapes(1).Delete()
    except Exception:
        pass
    try:
        while ws.ChartObjects().Count > 0:
            ws.ChartObjects(1).Delete()
    except Exception:
        pass


def _import_csv_as_sheet(app, target_wb, csv_path: Path, sheet_name: str):
    if not csv_path.exists():
        raise FileNotFoundError(f"CSV not found: {csv_path}")

    csv_wb = app.Workbooks.Open(str(csv_path))
    csv_ws = csv_wb.Worksheets(1)

    _delete_sheet_if_exists(target_wb, sheet_name)

    csv_ws.Copy(After=target_wb.Worksheets(target_wb.Worksheets.Count))
    new_ws = target_wb.Worksheets(target_wb.Worksheets.Count)
    new_ws.Name = sheet_name

    csv_wb.Close(SaveChanges=False)

    try:
        new_ws.Columns.AutoFit()
    except Exception:
        pass

    return new_ws


def _place_after_first_sheet(wb, sheet_names: list[str]):
    """Place these sheets immediately after Sheet1, in order, without moving Sheet1."""
    first = wb.Worksheets(1)
    prev = first
    for name in sheet_names:
        ws = _sheet_by_name(wb, name)
        if ws is None:
            continue
        ws.Move(After=prev)
        prev = ws


def _get_or_create_sheet_at_end(wb, name: str):
    ws = _sheet_by_name(wb, name)
    if ws is None:
        ws = wb.Worksheets.Add(After=wb.Worksheets(wb.Worksheets.Count))
        ws.Name = name
    return ws


def _add_picture(ws, img_path: Path, anchor_cell: str, scale: float):
    """Reliable PNG insert: pass explicit width/height then scale."""
    if not img_path.exists():
        ws.Range(anchor_cell).Value = f"Missing: {img_path.name}"
        ws.Range(anchor_cell).Font.Bold = True
        return None

    with PILImage.open(img_path) as im:
        w_px, h_px = im.size

    cell = ws.Range(anchor_cell)
    shape = ws.Shapes.AddPicture(
        str(img_path),
        False,  # LinkToFile
        True,   # SaveWithDocument
        cell.Left,
        cell.Top,
        float(w_px),
        float(h_px),
    )
    shape.LockAspectRatio = True
    shape.ScaleWidth(scale, True)
    shape.ScaleHeight(scale, True)
    return shape


def _last_used_row_col(ws):
    """Return (last_row, last_col) based on UsedRange (safe for CSV imports)."""
    used = ws.UsedRange
    last_row = used.Row + used.Rows.Count - 1
    last_col = used.Column + used.Columns.Count - 1
    return int(last_row), int(last_col)


def _find_header_col(ws, header_name: str):
    """Find a header in row 1 (case-insensitive). Returns 1-based column index."""
    _, last_col = _last_used_row_col(ws)
    for c in range(1, last_col + 1):
        v = ws.Cells(1, c).Value
        if v is None:
            continue
        if str(v).strip().lower() == header_name.lower():
            return c
    return None


def _validate_headers(ws, required: list[str]):
    missing = []
    for h in required:
        if _find_header_col(ws, h) is None:
            missing.append(h)
    if missing:
        headers = []
        _, last_col = _last_used_row_col(ws)
        for c in range(1, last_col + 1):
            v = ws.Cells(1, c).Value
            if v is not None:
                headers.append(str(v))
        raise KeyError(f"Missing columns in labeled_data: {missing}. Found headers: {headers}")


def _create_pivot(
    wb,
    source_range,
    dest_ws,
    dest_cell: str,
    table_name: str,
    row_field: str,
    data_field: str,
    caption: str,
    autoshow_top_n: int | None = None,
):
    """
    Create a PivotTable at dest_ws[dest_cell] with:
      - row_field as RowField
      - Sum of data_field as Values
    Optionally autoshow top N by sum.
    """
    # Excel constants (avoid needing win32com.constants)
    xlDatabase = 1
    xlRowField = 1
    xlSum = -4157
    xlAutomatic = -4105  # for AutoShow

    pivot_cache = wb.PivotCaches().Create(
        SourceType=xlDatabase,
        SourceData=source_range,
    )
    pt = pivot_cache.CreatePivotTable(
        TableDestination=dest_ws.Range(dest_cell),
        TableName=table_name,
    )

    # Set row field
    pf_row = pt.PivotFields(row_field)
    pf_row.Orientation = xlRowField
    pf_row.Position = 1

    # Add values field (sum)
    pf_val = pt.PivotFields(data_field)
    pt.AddDataField(pf_val, caption, xlSum)

    # Optional: top N filter (Excel may show >N if ties)
    if autoshow_top_n is not None:
        pf_row.AutoShow(xlAutomatic, int(autoshow_top_n), caption)

    # Layout tweaks (optional)
    try:
        pt.RowAxisLayout(1)  # xlTabularRow = 1
    except Exception:
        pass

    return pt


def _add_chart_from_pivot(dest_ws, pivot_table, anchor_cell: str, chart_type: int, title: str, width=420, height=260):
    """Create a chart using the pivot table range as source."""
    cell = dest_ws.Range(anchor_cell)
    chart_obj = dest_ws.ChartObjects().Add(cell.Left, cell.Top, width, height)
    chart = chart_obj.Chart
    chart.ChartType = chart_type

    # Prefer TableRange2 when available (often excludes some headers/totals)
    try:
        chart.SetSourceData(pivot_table.TableRange2)
    except Exception:
        chart.SetSourceData(pivot_table.TableRange1)

    chart.HasTitle = True
    chart.ChartTitle.Text = title
    return chart_obj


# -----------------------------
# Main macro
# -----------------------------
@xl_macro(disable_screen_updating=True, disable_alerts=True)
def build_pyfi_excel_report():
    """
    Sheet1 stays first.
    Then: labeled_data (csv) -> Summary (csv) -> Charts (pngs) -> Native Charts (PivotTables + Charts)

    Vendor pivot is placed at A61 and its chart is anchored at D61,
    created the same way as the first two charts.
    """
    app = xl_app()
    wb = app.ActiveWorkbook

    # 1) Import labeled_data + Summary (Excel-native)
    ws_data = _import_csv_as_sheet(app, wb, LABELED_CSV, "labeled_data")
    _import_csv_as_sheet(app, wb, SUMMARY_CSV, "Summary")

    # Validate required headers exist in labeled_data sheet
    _validate_headers(ws_data, ["amount", "spender", "category", "vendor"])

    # 2) Charts sheet (PNGs)
    ws_charts = _get_or_create_sheet_at_end(wb, "Charts")
    _clear_sheet(ws_charts)

    ws_charts.Range("A1").Value = "Charts"
    ws_charts.Range("A1").Font.Bold = True
    ws_charts.Range("A1").Font.Size = 14

    start_row = 5
    cols = ["A", "H", "O"]
    for i, fname in enumerate(PNG_FILES):
        col = cols[i] if i < len(cols) else "A"
        title_cell = f"{col}{start_row - 1}"
        anchor_cell = f"{col}{start_row}"

        ws_charts.Range(title_cell).Value = fname.replace(".png", "").replace("_", " ").title()
        ws_charts.Range(title_cell).Font.Bold = True
        _add_picture(ws_charts, BASE_DIR / fname, anchor_cell, scale=IMG_SCALE)

    # 3) Native Charts sheet (PivotTables + charts)
    ws_native = _get_or_create_sheet_at_end(wb, "Native Charts")
    _clear_sheet(ws_native)

    ws_native.Range("A1").Value = "Native Charts (PivotTables)"
    ws_native.Range("A1").Font.Bold = True
    ws_native.Range("A1").Font.Size = 14

    # Source range for pivots: use the entire used range from labeled_data
    last_row, last_col = _last_used_row_col(ws_data)
    source_range = ws_data.Range(ws_data.Cells(1, 1), ws_data.Cells(last_row, last_col))

    # Excel chart types
    xlPie = 5
    xlBarClustered = 57  # <-- "Clustered Bar" in Excel UI (horizontal)

    # Pivot 1: Spender
    ws_native.Range("A3").Value = "Spending by Spender"
    ws_native.Range("A3").Font.Bold = True
    pt_spender = _create_pivot(
        wb, source_range, ws_native, "A4",
        "pvt_spender", "spender", "amount", "Sum of Amount"
    )
    try:
        pt_spender.RefreshTable()
    except Exception:
        pass
    _add_chart_from_pivot(ws_native, pt_spender, "D4", xlPie, "Spending by Spender", 420, 260)

    # Pivot 2: Category
    ws_native.Range("A25").Value = "Spending by Category"
    ws_native.Range("A25").Font.Bold = True
    pt_category = _create_pivot(
        wb, source_range, ws_native, "A26",
        "pvt_category", "category", "amount", "Sum of Amount"
    )
    try:
        pt_category.RefreshTable()
    except Exception:
        pass
    _add_chart_from_pivot(ws_native, pt_category, "D26", xlPie, "Spending by Category", 420, 260)

    # Pivot 3: Vendor (pivot at A61, chart at D61, clustered bar)
    ws_native.Range("A60").Value = "Spending by Vendor"
    ws_native.Range("A60").Font.Bold = True

    pt_vendor = _create_pivot(
        wb, source_range, ws_native, "A61",
        "pvt_vendor", "vendor", "amount", "Sum of Amount",
    )
    try:
        pt_vendor.RefreshTable()
    except Exception:
        pass

    _add_chart_from_pivot(ws_native, pt_vendor, "D61", xlBarClustered, "Spending by Vendor", 600, 420)

    # Light formatting
    try:
        ws_native.Columns("A").ColumnWidth = 28
        ws_native.Columns("B").ColumnWidth = 18
    except Exception:
        pass

    # 4) Order after first sheet
    _place_after_first_sheet(wb, ["labeled_data", "Summary", "Charts", "Native Charts"])