"""
report_v2.py — Recon V2 Excel report builder (phase R3).

Renders a report from PERSISTED results: Elixir queries recon_matches,
decodes the row payloads, and sends them here with the rule set's report
config and the tenant's UI labels. Nothing in this module knows about
hospitals or HIS — vocabulary comes from `labels`.

Payload shape (built by MwRecon.ReportBuilder):
  {
    "name":          "card_3way",
    "title":         "Card 3-Way Recon",
    "business_name": "Sahyadri Hospitals",
    "recon_date":    "2026-06-09",
    "labels":        {"internal_system": "HIS", "location": "Unit", ...},
    "report_config": {"location_summary": {"enabled": true}, "include_tier_breakdown": true},
    "stages": [
      {"stage": "...", "display_name": "...",
       "sheet_matched": "...", "sheet_unmatched": "...", "sheet_unmatched_b": null,
       "matched_rows": [...], "unmatched_a_rows": [...], "unmatched_b_rows": [...]}
    ]
  }

Counts, amounts, tier breakdowns, and location summaries are computed here
from the rows — one source of truth, no duplicated aggregation in Elixir.
"""

import io
import base64

import openpyxl
from openpyxl.styles import Font

from writer import (
    HEADER_FILL, HEADER_FONT, HEADER_ALIGN,
    _auto_width, _safe_sheet_name, _safe_value, _fmt_amount,
)

# Columns pulled to the front of every data sheet; the rest follow sorted.
PRIORITY_COLS = ["match_tier", "location", "txn_date", "amount"]


def build_report_v2(report: dict) -> str:
    """Build and return a base64-encoded XLSX report."""
    wb = openpyxl.Workbook()
    wb.remove(wb.active)

    stages = report.get("stages", [])

    for stage in stages:
        _write_rows_sheet(wb, stage.get("sheet_matched"), stage.get("matched_rows", []))
        _write_rows_sheet(wb, stage.get("sheet_unmatched"), stage.get("unmatched_a_rows", []))
        if stage.get("sheet_unmatched_b"):
            _write_rows_sheet(wb, stage["sheet_unmatched_b"], stage.get("unmatched_b_rows", []))

    _write_summary_sheet(wb, report)

    if not wb.sheetnames:
        wb.create_sheet("Summary")

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


# ── data sheets ───────────────────────────────────────────────────────────────

def _write_rows_sheet(wb, sheet_name, rows):
    if not sheet_name:
        return
    ws = wb.create_sheet(title=_unique_sheet_name(wb, sheet_name))

    if not rows:
        ws.append(["No data"])
        return

    columns = _ordered_columns(rows)

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

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

    _auto_width(ws, columns)


def _ordered_columns(rows):
    """Union of keys across rows (JSON maps lose order in transit):
    priority columns first, the rest alphabetical."""
    seen = set()
    for row in rows:
        seen.update(row.keys())
    front = [c for c in PRIORITY_COLS if c in seen]
    rest = sorted(c for c in seen if c not in PRIORITY_COLS)
    return front + rest


def _pretty_header(col: str) -> str:
    return str(col).replace("_", " ").title() if str(col).islower() else str(col)


# ── summary sheet ─────────────────────────────────────────────────────────────

def _write_summary_sheet(wb, report):
    ws = wb.create_sheet(title="Summary", index=0)
    labels = report.get("labels", {})
    config = report.get("report_config", {}) or {}
    stages = report.get("stages", [])

    ws["A1"] = report.get("business_name") or report.get("title", "Reconciliation")
    ws["A1"].font = Font(bold=True, size=14)
    ws["A2"] = report.get("title", "")
    ws["A3"] = f"Date: {report.get('recon_date') or ''}"

    row = 5
    row = _write_stage_table(ws, row, stages, config)

    if _location_summary_enabled(config):
        location_label = labels.get("location", "Location")
        for stage in stages:
            loc_rows = _location_summary(stage)
            if loc_rows:
                row = _write_location_table(ws, row + 1, stage, loc_rows, location_label)

    _auto_width(ws, [])


def _write_stage_table(ws, row, stages, config):
    headers = ["Stage", "Total", "Matched", "Unmatched",
               "Side-B Unmatched", "Matched Amount", "Unmatched Amount"]
    for col_idx, h in enumerate(headers, start=1):
        cell = ws.cell(row=row, column=col_idx, value=h)
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = HEADER_ALIGN
    row += 1

    include_tiers = config.get("include_tier_breakdown", True)

    for stage in stages:
        matched = stage.get("matched_rows", [])
        unmatched_a = stage.get("unmatched_a_rows", [])
        unmatched_b = stage.get("unmatched_b_rows", [])

        values = [
            stage.get("display_name") or stage.get("stage"),
            len(matched) + len(unmatched_a),
            len(matched),
            len(unmatched_a),
            len(unmatched_b),
            _fmt_amount(_sum_amount(matched)),
            _fmt_amount(_sum_amount(unmatched_a)),
        ]
        for col_idx, val in enumerate(values, start=1):
            ws.cell(row=row, column=col_idx, value=val)
        ws.cell(row=row, column=1).font = Font(bold=True)
        row += 1

        if include_tiers:
            for tier, count in _tier_counts(matched).items():
                ws.cell(row=row, column=1, value=f"    ↳ tier: {tier}")
                ws.cell(row=row, column=3, value=count)
                row += 1

    return row


def _write_location_table(ws, row, stage, loc_rows, location_label):
    title = stage.get("display_name") or stage.get("stage")
    ws.cell(row=row, column=1, value=f"{location_label}-wise — {title}").font = Font(bold=True)
    row += 1

    headers = [location_label, "Total", "Total Amount",
               "Matched", "Matched Amount", "Unmatched", "Unmatched Amount"]
    for col_idx, h in enumerate(headers, start=1):
        cell = ws.cell(row=row, column=col_idx, value=h)
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = HEADER_ALIGN
    row += 1

    for loc in loc_rows:
        values = [
            loc["location"],
            loc["matched_count"] + loc["unmatched_count"],
            _fmt_amount(loc["matched_amount"] + loc["unmatched_amount"]),
            loc["matched_count"],
            _fmt_amount(loc["matched_amount"]),
            loc["unmatched_count"],
            _fmt_amount(loc["unmatched_amount"]),
        ]
        for col_idx, val in enumerate(values, start=1):
            ws.cell(row=row, column=col_idx, value=val)
        row += 1

    return row


# ── aggregation (single source: the rows themselves) ─────────────────────────

def _location_summary_enabled(config):
    loc_cfg = config.get("location_summary", {}) or {}
    return loc_cfg.get("enabled", True)


def _location_summary(stage):
    buckets = {}
    for status, rows in (("matched", stage.get("matched_rows", [])),
                         ("unmatched", stage.get("unmatched_a_rows", []))):
        for row in rows:
            location = row.get("location")
            if location in (None, ""):
                continue
            b = buckets.setdefault(location, {
                "location": location,
                "matched_count": 0, "matched_amount": 0.0,
                "unmatched_count": 0, "unmatched_amount": 0.0,
            })
            b[f"{status}_count"] += 1
            b[f"{status}_amount"] += _num(row.get("amount"))
    return [buckets[k] for k in sorted(buckets)]


def _tier_counts(matched_rows):
    counts = {}
    for row in matched_rows:
        tier = row.get("match_tier") or "unknown"
        counts[tier] = counts.get(tier, 0) + 1
    return counts


def _sum_amount(rows):
    return sum(_num(row.get("amount")) for row in rows)


def _num(value):
    try:
        return float(value)
    except (TypeError, ValueError):
        return 0.0


def _unique_sheet_name(wb, name):
    base = _safe_sheet_name(str(name))
    if base not in wb.sheetnames:
        return base
    for i in range(2, 100):
        candidate = _safe_sheet_name(f"{base[:28]}-{i}")
        if candidate not in wb.sheetnames:
            return candidate
    return base[:28] + "-x"