excel_generator.py

# -*- coding: utf-8 -*-
"""
excel_generator.py - Excel Report Generator
============================================
Generates professionally formatted Excel files from receipt processing results.

Sheets:
1. All Receipts  - Full data with SUM formulas, auto-filter, freeze panes
2. Needs Review  - Low-confidence items flagged for manual review
3. Summary       - Statistics and category breakdown
"""

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from datetime import datetime
import os
import logging

logger = logging.getLogger("ExcelGenerator")


class ExcelGenerator:
    """
    Generates formatted Excel reports from receipt data.

    Features:
    - Auto column widths
    - Currency format ($#,##0.00)
    - SUM formulas (not hardcoded values)
    - Zebra striping
    - Freeze panes
    - Auto-filter
    - Category breakdown in summary
    """

    def __init__(self, config: dict):
        self.config = config
        self.excel_config = config["excel"]
        self.columns = self.excel_config["columns"]
        self.sheet_names = self.excel_config["sheet_names"]
        self.categories = config.get("categories", [])

    def generate_excel(self, results: list, output_dir: str = "output") -> str:
        """
        Generate Excel file from processing results.

        Args:
            results: List of process_receipt() return values.
            output_dir: Output directory path.

        Returns:
            Absolute path to the generated Excel file.
        """
        os.makedirs(output_dir, exist_ok=True)

        now = datetime.now()
        filename = self.excel_config["filename_template"].format(
            year=now.year, month=str(now.month).zfill(2)
        )
        filepath = os.path.join(output_dir, filename)

        main_rows, review_rows = self._prepare_data(results)

        df_main = pd.DataFrame(main_rows) if main_rows else pd.DataFrame()
        df_review = pd.DataFrame(review_rows) if review_rows else pd.DataFrame()

        sn = self.sheet_names

        with pd.ExcelWriter(filepath, engine="openpyxl") as writer:
            # Sheet 1: All Receipts
            if not df_main.empty:
                col_keys = [c["key"] for c in self.columns]
                existing = [c for c in col_keys if c in df_main.columns]
                df_main[existing].to_excel(writer, sheet_name=sn["main"], index=False)
            else:
                pd.DataFrame(columns=[c["key"] for c in self.columns]).to_excel(
                    writer, sheet_name=sn["main"], index=False
                )

            # Sheet 2: Needs Review
            if not df_review.empty:
                df_review.to_excel(writer, sheet_name=sn["review"], index=False)

            # Sheet 3: Summary
            self._write_summary_sheet(writer, results, main_rows)

        # Apply formatting
        self._apply_all_formatting(filepath, len(main_rows), len(review_rows))

        logger.info("Excel generated: %s (%d rows)", filepath, len(main_rows))
        return filepath

    # ------------------------------------------------------------------
    # Data preparation
    # ------------------------------------------------------------------
    def _prepare_data(self, results: list) -> tuple:
        """Split results into main data rows and review rows."""
        main_rows = []
        review_rows = []

        for r in results:
            if not r.get("success") or not r.get("data"):
                continue

            d = r["data"]
            row = {
                "date": d.get("date", ""),
                "vendor": d.get("vendor", ""),
                "location": d.get("location", ""),
                "subtotal": d.get("subtotal", 0.0),
                "tax": d.get("tax", 0.0),
                "tip": d.get("tip", 0.0),
                "total": d.get("total", 0.0),
                "payment_method": d.get("payment_method", ""),
                "card_last_4": d.get("card_last_4", ""),
                "category": d.get("category", ""),
                "state": d.get("state", ""),
                "notes": d.get("notes", ""),
            }
            main_rows.append(row)

            if r.get("needs_review"):
                review_rows.append({
                    "vendor": d.get("vendor", ""),
                    "date": d.get("date", ""),
                    "total": d.get("total", 0.0),
                    "category": d.get("category", ""),
                    "confidence": f"{r.get('confidence', 0) * 100:.0f}%",
                    "issue": r.get("issue", ""),
                    "notes": d.get("notes", ""),
                })

        return main_rows, review_rows

    # ------------------------------------------------------------------
    # Summary sheet
    # ------------------------------------------------------------------
    def _write_summary_sheet(self, writer, results: list, main_rows: list):
        """Write summary statistics and category breakdown."""
        sn = self.sheet_names
        total_count = len(results)
        success = [r for r in results if r.get("success")]
        auto_count = len([r for r in success if not r.get("needs_review")])
        review_count = len([r for r in success if r.get("needs_review")])
        error_count = len([r for r in results if not r.get("success")])
        total_amount = sum(
            r["data"].get("total", 0) for r in success if r.get("data")
        )
        avg_confidence = 0
        if success:
            avg_confidence = sum(r.get("confidence", 0) for r in success) / len(success)

        stats = [
            ["Processed At", datetime.now().strftime("%Y-%m-%d %H:%M:%S")],
            ["Total Count", total_count],
            ["Auto-Processed", auto_count],
            ["Needs Review", review_count],
            ["Errors", error_count],
            ["Total Amount", f"${total_amount:,.2f}"],
            ["Avg Confidence", f"{avg_confidence * 100:.1f}%"],
            [
                "Auto-Process Rate",
                f"{auto_count / total_count * 100:.1f}%" if total_count > 0 else "0%",
            ],
        ]

        df_stats = pd.DataFrame(stats, columns=["Item", "Value"])
        df_stats.to_excel(writer, sheet_name=sn["summary"], startrow=0, index=False)

        # Category breakdown
        if main_rows:
            df = pd.DataFrame(main_rows)
            cat_summary = (
                df.groupby("category")
                .agg(Count=("total", "count"), Sum=("total", "sum"), Average=("total", "mean"))
                .reset_index()
            )
            cat_summary.columns = ["Category", "Count", "Sum ($)", "Average ($)"]
            cat_summary["Sum ($)"] = cat_summary["Sum ($)"].apply(lambda x: f"${x:,.2f}")
            cat_summary["Average ($)"] = cat_summary["Average ($)"].apply(lambda x: f"${x:,.2f}")

            start = len(df_stats) + 3
            ws = writer.sheets[sn["summary"]]
            ws.cell(start, 1, "Category Breakdown")
            cat_summary.to_excel(
                writer, sheet_name=sn["summary"], startrow=start, index=False
            )

    # ------------------------------------------------------------------
    # Formatting
    # ------------------------------------------------------------------
    def _apply_all_formatting(self, filepath: str, main_count: int, review_count: int):
        """Apply formatting to all sheets."""
        wb = load_workbook(filepath)
        sn = self.sheet_names

        if sn["main"] in wb.sheetnames:
            self._format_main_sheet(wb[sn["main"]], main_count)

        if sn["review"] in wb.sheetnames:
            self._format_review_sheet(wb[sn["review"]], review_count)

        if sn["summary"] in wb.sheetnames:
            self._format_summary_sheet(wb[sn["summary"]])

        wb.save(filepath)
        logger.info("Formatting applied")

    def _format_main_sheet(self, ws, data_count: int):
        """Format the main receipts sheet."""
        # Cross-platform font (Arial is available on Windows, Mac, Linux)
        header_font = Font(name="Arial", bold=True, color="FFFFFF", size=10)
        header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
        header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)

        data_font = Font(name="Arial", size=10)
        currency_font = Font(name="Consolas", size=10)

        even_fill = PatternFill(start_color="D6E4F0", end_color="D6E4F0", fill_type="solid")
        odd_fill = PatternFill(start_color="FFFFFF", end_color="FFFFFF", fill_type="solid")

        thin_border = Border(
            left=Side(style="thin", color="B4C6E7"),
            right=Side(style="thin", color="B4C6E7"),
            top=Side(style="thin", color="B4C6E7"),
            bottom=Side(style="thin", color="B4C6E7"),
        )
        header_border = Border(
            left=Side(style="thin", color="1F3864"),
            right=Side(style="thin", color="1F3864"),
            top=Side(style="medium", color="1F3864"),
            bottom=Side(style="medium", color="1F3864"),
        )

        # -- Headers --
        for col_idx, col_def in enumerate(self.columns, 1):
            cell = ws.cell(1, col_idx)
            cell.value = col_def["header"]
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_align
            cell.border = header_border
            ws.column_dimensions[get_column_letter(col_idx)].width = col_def.get("width", 15)

        ws.row_dimensions[1].height = 28

        # -- Data rows --
        currency_keys = {"subtotal", "tax", "tip", "total"}

        for row_num in range(2, data_count + 2):
            fill = even_fill if row_num % 2 == 0 else odd_fill
            ws.row_dimensions[row_num].height = 22

            for col_idx, col_def in enumerate(self.columns, 1):
                cell = ws.cell(row_num, col_idx)
                cell.border = thin_border
                cell.fill = fill

                if col_def["key"] in currency_keys:
                    cell.font = currency_font
                    cell.number_format = '"$"#,##0.00'
                    cell.alignment = Alignment(horizontal="right", vertical="center")
                else:
                    cell.font = data_font
                    cell.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)

        # -- SUM row --
        if data_count > 0:
            sum_row = data_count + 2
            ws.row_dimensions[sum_row].height = 28

            sum_font = Font(name="Arial", bold=True, size=11, color="1F3864")
            sum_fill = PatternFill(start_color="BDD7EE", end_color="BDD7EE", fill_type="solid")
            sum_border = Border(
                left=Side(style="thin", color="1F3864"),
                right=Side(style="thin", color="1F3864"),
                top=Side(style="double", color="1F3864"),
                bottom=Side(style="medium", color="1F3864"),
            )

            ws.cell(sum_row, 1).value = "TOTAL"
            ws.cell(sum_row, 1).font = sum_font
            ws.cell(sum_row, 1).fill = sum_fill
            ws.cell(sum_row, 1).alignment = Alignment(horizontal="center", vertical="center")
            ws.cell(sum_row, 1).border = sum_border

            for col_idx in range(2, len(self.columns) + 1):
                ws.cell(sum_row, col_idx).fill = sum_fill
                ws.cell(sum_row, col_idx).border = sum_border

            for col_idx, col_def in enumerate(self.columns, 1):
                if col_def["key"] in currency_keys:
                    col_letter = get_column_letter(col_idx)
                    formula = f"=SUM({col_letter}2:{col_letter}{data_count + 1})"
                    cell = ws.cell(sum_row, col_idx)
                    cell.value = formula
                    cell.font = Font(name="Consolas", bold=True, size=11, color="1F3864")
                    cell.number_format = '"$"#,##0.00'
                    cell.alignment = Alignment(horizontal="right", vertical="center")

        # -- Freeze panes & auto-filter --
        ws.freeze_panes = "A2"
        ws.auto_filter.ref = f"A1:{get_column_letter(len(self.columns))}{data_count + 1}"

    def _format_review_sheet(self, ws, data_count: int):
        """Format the review sheet."""
        header_font = Font(name="Arial", bold=True, color="FFFFFF", size=10)
        header_fill = PatternFill(start_color="C55A11", end_color="C55A11", fill_type="solid")
        warn_fill = PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid")
        thin_border = Border(
            left=Side(style="thin", color="E0A040"),
            right=Side(style="thin", color="E0A040"),
            top=Side(style="thin", color="E0A040"),
            bottom=Side(style="thin", color="E0A040"),
        )

        for col_num in range(1, ws.max_column + 1):
            cell = ws.cell(1, col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.border = thin_border

        ws.row_dimensions[1].height = 28

        for row_num in range(2, data_count + 2):
            for col_num in range(1, ws.max_column + 1):
                cell = ws.cell(row_num, col_num)
                cell.fill = warn_fill
                cell.border = thin_border
                cell.font = Font(name="Arial", size=10)
                cell.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)

        widths = [20, 14, 13, 20, 12, 30, 30]
        for i, w in enumerate(widths):
            if i < ws.max_column:
                ws.column_dimensions[get_column_letter(i + 1)].width = w

    def _format_summary_sheet(self, ws):
        """Format the summary sheet."""
        title_font = Font(name="Arial", bold=True, size=12, color="1F3864")

        # Stats header row
        for col in (1, 2):
            ws.cell(1, col).font = Font(name="Arial", bold=True, color="FFFFFF", size=10)
            ws.cell(1, col).fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")

        for row_num in range(2, 10):
            ws.cell(row_num, 1).font = Font(name="Arial", bold=True, size=10)
            ws.cell(row_num, 2).font = Font(name="Arial", size=10)
            ws.cell(row_num, 2).alignment = Alignment(horizontal="left")

        # Category breakdown header
        for row_num in range(1, ws.max_row + 1):
            val = ws.cell(row_num, 1).value
            if val and "Category" in str(val):
                ws.cell(row_num, 1).font = title_font
                header_row = row_num + 1
                cat_fill = PatternFill(start_color="548235", end_color="548235", fill_type="solid")
                cat_font = Font(name="Arial", bold=True, color="FFFFFF", size=10)
                for col in range(1, 5):
                    cell = ws.cell(header_row, col)
                    cell.fill = cat_fill
                    cell.font = cat_font
                    cell.alignment = Alignment(horizontal="center")
                break

        ws.column_dimensions["A"].width = 25
        ws.column_dimensions["B"].width = 20
        ws.column_dimensions["C"].width = 18
        ws.column_dimensions["D"].width = 18