"""
writer.py — Excel report writer for recon_report_py.

Writes matched/unmatched sheets into an openpyxl workbook with:
  - Blue header row (#1274bd, white bold text)
  - Auto-sized columns (capped at 50)
  - Summary sheet with per-location stats (for HIS recon types)
  - Returns base64-encoded XLSX bytes for storage in DB

Header colour matches the existing Sahyadri report format (#1274bd).
"""

import io
import base64
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment
from openpyxl.utils import get_column_letter

HEADER_FILL  = PatternFill(fgColor="1274BD", fill_type="solid")
HEADER_FONT  = Font(bold=True, color="FFFFFF")
HEADER_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)

MAX_COL_WIDTH = 50
MIN_COL_WIDTH = 10


def build_report(result: dict, config: dict) -> str:
    """
    Build and return a base64-encoded XLSX report.

    `result` is the dict returned by the matching engine (engine_client.ex → recon_engine_py).
    `config` contains display metadata (hospital_name, recon_type, recon_date).
    """
    wb = openpyxl.Workbook()
    wb.remove(wb.active)  # remove default Sheet

    hospital    = config.get("hospital_name", "Hospital")
    recon_type  = config.get("recon_type", "")
    recon_date  = config.get("recon_date", "")

    sheet_matched   = result.get("sheet_matched",   _default_sheet("Matched",   recon_type))
    sheet_unmatched = result.get("sheet_unmatched", _default_sheet("Unmatched", recon_type))

    # ── Matched sheet ─────────────────────────────────────────────────────────
    matched_rows   = result.get("matched_rows", [])
    unmatched_rows = result.get("unmatched_rows", [])

    _write_sheet(wb, sheet_matched,   matched_rows)
    _write_sheet(wb, sheet_unmatched, unmatched_rows)

    # ── Summary sheet ─────────────────────────────────────────────────────────
    location_summary = result.get("location_summary", [])
    _write_summary_sheet(wb, result, location_summary, hospital, recon_type, recon_date)

    # ── Serialise ─────────────────────────────────────────────────────────────
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return base64.b64encode(buf.read()).decode("utf-8")


def _write_sheet(wb: openpyxl.Workbook, sheet_name: str, rows: list):
    if not rows:
        ws = wb.create_sheet(title=_safe_sheet_name(sheet_name))
        ws.append(["No data"])
        return

    ws = wb.create_sheet(title=_safe_sheet_name(sheet_name))
    columns = list(rows[0].keys())

    # Header row
    for col_idx, col_name in enumerate(columns, start=1):
        cell = ws.cell(row=1, column=col_idx, value=str(col_name))
        cell.fill  = HEADER_FILL
        cell.font  = HEADER_FONT
        cell.alignment = HEADER_ALIGN

    # Data rows
    for row_idx, row in enumerate(rows, start=2):
        for col_idx, col_name in enumerate(columns, start=1):
            val = row.get(col_name)
            ws.cell(row=row_idx, column=col_idx, value=_safe_value(val))

    # Auto-width
    _auto_width(ws, columns)


def _write_summary_sheet(wb, result: dict, location_summary: list,
                          hospital: str, recon_type: str, recon_date: str):
    ws = wb.create_sheet(title="Summary")

    # Top metadata
    ws["A1"] = hospital
    ws["A1"].font = Font(bold=True, size=14)
    ws["A2"] = f"Recon Type: {recon_type}"
    ws["A3"] = f"Date: {recon_date}"

    # Totals block
    ws["A5"] = "OVERALL TOTALS"
    ws["A5"].font = Font(bold=True)

    labels_vals = [
        ("Total Records",     result.get("total_count",      0)),
        ("Matched Records",   result.get("matched_count",    0)),
        ("Unmatched Records", result.get("unmatched_count",  0)),
        ("Total Amount",      _fmt_amount(result.get("total_amount",     0))),
        ("Matched Amount",    _fmt_amount(result.get("matched_amount",   0))),
        ("Unmatched Amount",  _fmt_amount(result.get("unmatched_amount", 0))),
    ]
    for offset, (label, val) in enumerate(labels_vals, start=6):
        ws.cell(row=offset, column=1, value=label).font = Font(bold=True)
        ws.cell(row=offset, column=2, value=val)

    if not location_summary:
        _auto_width(ws, ["A", "B"])
        return

    # Per-location table
    start_row = 14
    loc_headers = ["Location", "Total Count", "Total Amount",
                   "Matched Count", "Matched Amount",
                   "Unmatched Count", "Unmatched Amount"]
    for col_idx, h in enumerate(loc_headers, start=1):
        cell = ws.cell(row=start_row, column=col_idx, value=h)
        cell.fill  = HEADER_FILL
        cell.font  = HEADER_FONT
        cell.alignment = HEADER_ALIGN

    loc_cols = [
        "Unit/Location", "total_count", "total_amount",
        "matched_count", "matched_amount",
        "unmatched_count", "unmatched_amount",
    ]
    for row_offset, loc_row in enumerate(location_summary, start=1):
        for col_idx, key in enumerate(loc_cols, start=1):
            val = loc_row.get(key, 0)
            ws.cell(row=start_row + row_offset, column=col_idx, value=_safe_value(val))

    _auto_width(ws, loc_headers)


# ── Helpers ───────────────────────────────────────────────────────────────────

def _safe_sheet_name(name: str) -> str:
    r"""Excel sheet names must be ≤31 chars and cannot contain []:*?\/."""
    forbidden = r"[]:*?\/'"
    for ch in forbidden:
        name = name.replace(ch, "-")
    return name[:31]


def _safe_value(val):
    if val is None or (isinstance(val, float) and val != val):  # NaN check
        return ""
    if isinstance(val, bool):
        return val
    return val


def _auto_width(ws, columns):
    for col_idx in range(1, ws.max_column + 1):
        max_len = MIN_COL_WIDTH
        col_letter = get_column_letter(col_idx)
        for row in ws.iter_rows(min_col=col_idx, max_col=col_idx):
            for cell in row:
                try:
                    cell_len = len(str(cell.value)) if cell.value else 0
                    max_len = max(max_len, cell_len)
                except Exception:
                    pass
        ws.column_dimensions[col_letter].width = min(max_len + 2, MAX_COL_WIDTH)


def _default_sheet(suffix: str, recon_type: str) -> str:
    return f"{recon_type[:15]}-{suffix}" if recon_type else suffix


def _fmt_amount(val) -> str:
    try:
        return f"₹{float(val):,.2f}"
    except Exception:
        return str(val)