"""
Render the metrics dict into a PressONE-branded multi-tab Excel workbook.

Tabs (per spec):
    Summary, By Division, Monthly, Medical Case Managers, Monthly by Division,
    Hourly, Regional, Area Codes, Cleveland Summary, CLE Area Codes,
    CLE by Division, CLE Monthly
Plus: Missed by Extension, Validation (extra detail tabs).
"""

import json

import xlsxwriter

import config

C = config.BRAND


def _formats(wb):
    return {
        "title": wb.add_format({"bold": True, "font_size": 15, "font_color": C["purple_deep"],
                                "font_name": "Calibri"}),
        "sub": wb.add_format({"font_size": 10, "font_color": C["purple_med"], "italic": True}),
        "hdr": wb.add_format({"bold": True, "font_size": 10, "font_color": "#FFFFFF",
                              "bg_color": C["purple_deep"], "border": 1, "border_color": "#FFFFFF",
                              "align": "left", "valign": "vcenter", "text_wrap": True}),
        "hdr_num": wb.add_format({"bold": True, "font_size": 10, "font_color": "#FFFFFF",
                                  "bg_color": C["purple_deep"], "border": 1, "border_color": "#FFFFFF",
                                  "align": "right", "valign": "vcenter", "text_wrap": True}),
        "cle_hdr": wb.add_format({"bold": True, "font_size": 10, "font_color": "#FFFFFF",
                                  "bg_color": C["orange"], "border": 1, "border_color": "#FFFFFF",
                                  "align": "right", "valign": "vcenter", "text_wrap": True}),
        "cle_hdr_l": wb.add_format({"bold": True, "font_size": 10, "font_color": "#FFFFFF",
                                    "bg_color": C["orange"], "border": 1, "border_color": "#FFFFFF",
                                    "align": "left", "valign": "vcenter", "text_wrap": True}),
        "txt": wb.add_format({"font_size": 10, "border": 1, "border_color": C["border"]}),
        "txt_b": wb.add_format({"font_size": 10, "bold": True, "font_color": C["purple_deep"],
                                "border": 1, "border_color": C["border"]}),
        "int": wb.add_format({"font_size": 10, "num_format": "#,##0", "border": 1,
                              "border_color": C["border"]}),
        "int_b": wb.add_format({"font_size": 10, "bold": True, "num_format": "#,##0",
                                "border": 1, "border_color": C["border"], "font_color": C["purple_deep"]}),
        "flt": wb.add_format({"font_size": 10, "num_format": "#,##0.0", "border": 1,
                              "border_color": C["border"]}),
        "pct": wb.add_format({"font_size": 10, "num_format": '0.0"%"', "border": 1,
                              "border_color": C["border"]}),
        "kv_k": wb.add_format({"font_size": 10, "bold": True, "font_color": C["purple_deep"]}),
        "kv_v": wb.add_format({"font_size": 10, "num_format": "#,##0"}),
        "kv_vf": wb.add_format({"font_size": 10, "num_format": "#,##0.0"}),
    }


# column spec: (header, dict_key, kind)  kind in {text, text_b, int, int_b, flt, pct}
def _table(ws, fmt, start_row, columns, rows, cle=False):
    hdr_l = fmt["cle_hdr_l"] if cle else fmt["hdr"]
    hdr_n = fmt["cle_hdr"] if cle else fmt["hdr_num"]
    for ci, (title, _key, kind) in enumerate(columns):
        ws.write(start_row, ci, title, hdr_n if kind in ("int", "int_b", "flt", "pct") else hdr_l)
    r = start_row + 1
    for row in rows:
        for ci, (_t, key, kind) in enumerate(columns):
            v = row.get(key, "")
            if kind == "text":
                ws.write(r, ci, v, fmt["txt"])
            elif kind == "text_b":
                ws.write(r, ci, v, fmt["txt_b"])
            elif kind == "int":
                ws.write_number(r, ci, float(v or 0), fmt["int"])
            elif kind == "int_b":
                ws.write_number(r, ci, float(v or 0), fmt["int_b"])
            elif kind == "flt":
                ws.write_number(r, ci, float(v or 0), fmt["flt"])
            elif kind == "pct":
                ws.write_number(r, ci, float(v or 0), fmt["pct"])
        r += 1
    # autosize-ish
    for ci, (title, key, kind) in enumerate(columns):
        width = max(len(str(title)) + 2, 11)
        if kind in ("text", "text_b"):
            longest = max([len(str(row.get(key, ""))) for row in rows] + [0])
            width = max(width, min(longest + 2, 34))
        ws.set_column(ci, ci, width)
    return r


def _sheet(wb, fmt, name, title, sub=""):
    ws = wb.add_worksheet(name[:31])
    ws.hide_gridlines(2)
    ws.write(0, 0, title, fmt["title"])
    if sub:
        ws.write(1, 0, sub, fmt["sub"])
    return ws


def _region_tabs(wb, fmt, period, reg):
    """Write the 4 region-specific tabs (Summary / Area Codes / by Division / Monthly)."""
    rmeta = reg["meta"]
    rname = rmeta["name"]
    cs = reg["summary"]
    ws = _sheet(wb, fmt, f"{rname} Summary", f"{rmeta['display']} — regional deep-dive", period)
    ws.write(2, 0, rmeta["msa_name"], fmt["sub"])
    ckv = [
        ("Inbound attempts", cs["inbound_attempts"], "int"),
        ("Inbound answered", cs["inbound_answered"], "int"),
        ("Inbound missed", cs["inbound_missed"], "int"),
        ("Answer rate", cs["answer_rate"], "pct"),
        (f"Unique {rname} callers", cs["unique_callers"], "int"),
        ("Callers reached live", cs["callers_reached"], "int"),
        ("Reach rate", cs["reach_rate"], "pct"),
        ("Talk time (hours)", cs["talk_hours"], "flt"),
        ("Talk time (minutes)", cs["talk_minutes"], "int"),
        ("Voicemails left", cs["voicemail_count"], "int"),
        ("Voicemail % of inbound", cs["voicemail_pct_inbound"], "pct"),
        ("% of all inbound calls", cs["pct_of_all_inbound"], "pct"),
    ]
    r = 4
    for k, v, kind in ckv:
        ws.write(r, 0, k, fmt["kv_k"])
        if kind == "pct":
            ws.write_number(r, 1, float(v), fmt["pct"])
        elif kind == "flt":
            ws.write_number(r, 1, float(v), fmt["kv_vf"])
        else:
            ws.write_number(r, 1, float(v), fmt["kv_v"])
        r += 1
    ws.set_column(0, 0, 30); ws.set_column(1, 1, 16)

    ws = _sheet(wb, fmt, f"{rname} Area Codes", f"{rmeta['display']} by area code", period)
    cols = [("Area code", "area_code", "text_b"), ("Coverage", "label", "text"),
            ("Inbound", "calls", "int_b"), ("Answered", "answered", "int"),
            ("Missed", "missed", "int"), ("Unique callers", "unique_callers", "int")]
    _table(ws, fmt, 3, cols, reg["area_codes"], cle=True)

    ws = _sheet(wb, fmt, f"{rname} by Division", f"{rmeta['display']} by reporting division", period)
    cols = [("Division", "division", "text_b"), ("Answered", "inbound_answered", "int_b"),
            ("Missed", "inbound_missed", "int"), ("Inbound attempts", "inbound_attempts", "int"),
            ("Unique callers", "unique_callers", "int"), ("Talk hrs", "talk_hours", "flt")]
    _table(ws, fmt, 3, cols, reg["by_division"], cle=True)

    if len(reg["monthly"]) > 1:   # nothing to trend on a single-month report
        ws = _sheet(wb, fmt, f"{rname} Monthly", f"{rmeta['display']} monthly trend", period)
        cols = [("Month", "month", "text_b"), ("Answered", "inbound_answered", "int_b"),
                ("Missed", "inbound_missed", "int"), ("Inbound attempts", "inbound_attempts", "int"),
                ("Talk hrs", "talk_hours", "flt")]
        _table(ws, fmt, 3, cols, reg["monthly"], cle=True)


def render(metrics, path, region_key=None):
    wb = xlsxwriter.Workbook(path, {"constant_memory": False})
    fmt = _formats(wb)
    meta = metrics["meta"]
    period = f'{meta["period_start"]} to {meta["period_end"]}'

    # ---- Summary -----------------------------------------------------------
    ws = _sheet(wb, fmt, "Summary", "A TIME — Call Activity Summary", period)
    s = metrics["summary"]
    kv = [
        ("INBOUND (families calling in)", None, "hdr"),
        ("Inbound answered (live)", s["inbound_answered"], "int"),
        ("Inbound missed", s["inbound_missed"], "int"),
        ("Total inbound attempts", s["inbound_attempts"], "int"),
        ("Answer rate", s["answer_rate"], "pct"),
        ("Inbound talk time (hours)", s["talk_hours_inbound"], "flt"),
        ("Inbound talk time (minutes)", s["talk_minutes_inbound"], "int"),
        ("Unique callers", s["unique_callers"], "int"),
        ("Callers reached live", s["callers_reached"], "int"),
        ("Reach rate", s["reach_rate"], "pct"),
        ("Voicemails left", s["voicemail_count"], "int"),
        ("Voicemail % of inbound", s["voicemail_pct_inbound"], "pct"),
        ("Queue-routed attempts", s["queue_count"], "int"),
        ("Direct-to-extension attempts", s["direct_count"], "int"),
        ("Department-classified attempts", s["department_count"], "int"),
        ("After-hours % (outside 8a–7p)", s["after_hours_pct"], "pct"),
        ("Overnight % (10p–6a)", s["overnight_pct"], "pct"),
        ("OUTBOUND (staff follow-up)", None, "hdr"),
        ("Outbound calls", s["outbound"], "int"),
        ("Outbound talk time (hours)", s["talk_hours_outbound"], "flt"),
        ("Outbound talk time (minutes)", s["talk_minutes_outbound"], "int"),
        ("TOTALS", None, "hdr"),
        ("Total calls (all divisions)", s["total_calls"], "int"),
        ("Total talk time (hours)", s["talk_hours"], "flt"),
    ]
    r = 3
    for k, v, kind in kv:
        if kind == "hdr":
            r += 1
            ws.write(r, 0, k, fmt["title"])
            r += 1
            continue
        ws.write(r, 0, k, fmt["kv_k"])
        if kind == "pct":
            ws.write_number(r, 1, float(v), fmt["pct"])
        elif kind == "flt":
            ws.write_number(r, 1, float(v), fmt["kv_vf"])
        else:
            ws.write_number(r, 1, float(v), fmt["kv_v"])
        r += 1
    ws.set_column(0, 0, 32); ws.set_column(1, 1, 16)

    # ---- By Division -------------------------------------------------------
    ws = _sheet(wb, fmt, "By Division", "Calls by Reporting Division",
                f"{period} — inbound and outbound reported separately; "
                f"Queue + Direct + Dept = inbound attempts")
    cols = [("Division", "division", "text_b"),
            ("Inbound answered", "inbound_answered", "int_b"),
            ("Inbound missed", "inbound_missed", "int"),
            ("Total In (Attempts)", "inbound_attempts", "int_b"),
            ("Answer %", "answer_rate", "pct"),
            ("Inbound talk hrs", "talk_hours_in", "flt"),
            ("Unique callers", "unique_callers", "int"),
            ("Voicemail", "voicemail", "int"),
            ("Queue", "queue", "int"), ("Direct", "direct", "int"), ("Dept", "dept", "int"),
            ("Total Out", "outbound", "int_b"),
            ("Outbound talk hrs", "talk_hours_out", "flt"),
            ("Grand Total", "total", "int_b")]
    _table(ws, fmt, 3, cols, metrics["by_division"])

    # ---- Monthly (skipped for single-month reports — nothing to trend) ------
    multi_month = len(metrics["monthly"]) > 1
    if multi_month:
        ws = _sheet(wb, fmt, "Monthly", "Monthly Trend (all divisions)", period)
        cols = [("Month", "month", "text_b"), ("Answered", "inbound_answered", "int_b"),
                ("Missed", "inbound_missed", "int"), ("Answer %", "answer_rate", "pct"),
                ("Inbound talk hrs", "talk_hours_in", "flt"),
                ("Outbound", "outbound", "int"), ("Outbound talk hrs", "talk_hours_out", "flt"),
                ("Total calls", "total", "int")]
        _table(ws, fmt, 3, cols, metrics["monthly"])

    # ---- Medical Case Managers --------------------------------------------
    ws = _sheet(wb, fmt, "Medical Case Managers",
                "Medical Helpline — Case Manager Volume", period)
    cols = [("Case manager", "name", "text_b"), ("Extensions", "extensions", "text"),
            ("Answered", "inbound_answered", "int_b"), ("Missed", "inbound_missed", "int"),
            ("Answer %", "answer_rate", "pct"), ("Inbound talk hrs", "talk_hours_in", "flt"),
            ("Outbound", "outbound", "int"), ("Outbound talk hrs", "talk_hours_out", "flt"),
            ("Total calls", "total", "int")]
    _table(ws, fmt, 3, cols, metrics.get("mhl_by_person", []))

    # ---- Monthly by Division (matrix; skipped for single-month reports) -----
    if multi_month:
        ws = _sheet(wb, fmt, "Monthly by Division",
                    "Monthly inbound attempts by division", period)
        mbd = metrics["monthly_by_division"]
        months = sorted({row["month"] for rows in mbd.values() for row in rows})
        divs = [d for d in config.REPORTING_DIVISIONS if d in mbd]
        ws.write(3, 0, "Month", fmt["hdr"])
        for ci, d in enumerate(divs):
            ws.write(3, ci + 1, d, fmt["hdr_num"])
        lookup = {d: {row["month"]: row["inbound_attempts"] if "inbound_attempts" in row
                      else row["inbound_answered"] + row["inbound_missed"] for row in mbd[d]}
                  for d in divs}
        for ri, m in enumerate(months):
            ws.write(4 + ri, 0, m, fmt["txt_b"])
            for ci, d in enumerate(divs):
                ws.write_number(4 + ri, ci + 1, float(lookup[d].get(m, 0)), fmt["int"])
        ws.set_column(0, 0, 10); ws.set_column(1, len(divs), 12)

    # ---- Hourly (+ day of week) -------------------------------------------
    ws = _sheet(wb, fmt, "Hourly", "Inbound calls by hour & day of week", period)
    ws.write(3, 0, "Hour (ET)", fmt["hdr"]); ws.write(3, 1, "Inbound calls", fmt["hdr_num"])
    for h, v in enumerate(metrics["hourly"]):
        ws.write(4 + h, 0, f"{h:02d}:00", fmt["txt_b"])
        ws.write_number(4 + h, 1, float(v), fmt["int"])
    dow_names = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
    ws.write(3, 3, "Day of week", fmt["hdr"]); ws.write(3, 4, "Inbound calls", fmt["hdr_num"])
    for d, v in enumerate(metrics["dow"]):
        ws.write(4 + d, 3, dow_names[d], fmt["txt_b"])
        ws.write_number(4 + d, 4, float(v), fmt["int"])
    ws.set_column(0, 0, 10); ws.set_column(1, 1, 14); ws.set_column(3, 3, 12); ws.set_column(4, 4, 14)

    # ---- Regional ----------------------------------------------------------
    ws = _sheet(wb, fmt, "Regional", "Inbound calls by region (caller area code)", period)
    cols = [("Region", "region", "text_b"), ("Inbound calls", "calls", "int_b"),
            ("Unique callers", "unique_callers", "int")]
    _table(ws, fmt, 3, cols, metrics["geo"]["regional"])

    # ---- Area Codes --------------------------------------------------------
    ws = _sheet(wb, fmt, "Area Codes", "Inbound calls by area code", period)
    cols = [("Area code", "area_code", "text_b"), ("Region", "region", "text"),
            ("Inbound calls", "calls", "int_b"), ("Answered", "answered", "int"),
            ("Unique callers", "unique_callers", "int")]
    _table(ws, fmt, 3, cols, metrics["geo"]["area_codes"])

    # ---- Region tabs (skipped for the generic org-wide report) ------------
    if region_key:
        _region_tabs(wb, fmt, period, metrics["regions"][region_key])

    # ---- Missed by Extension (extra) --------------------------------------
    ws = _sheet(wb, fmt, "Missed by Ext", "Missed calls by extension, per division", period)
    r = 3
    for div in config.REPORTING_DIVISIONS:
        rows = metrics.get("missed_by_extension", {}).get(div, [])
        if not rows:
            continue
        ws.write(r, 0, div, fmt["title"]); r += 1
        cols = [("Extension", "extension", "text_b"), ("Name", "name", "text"),
                ("Missed calls", "missed", "int_b")]
        r = _table(ws, fmt, r, cols, rows) + 1

    # ---- Validation (extra) -----------------------------------------------
    ws = _sheet(wb, fmt, "Validation", "Data quality & classification validation", period)
    v = metrics["validation"]
    vkv = [
        ("Total CDR records", v["total_records"], "int"),
        ("Internal (dir 3) excluded", v["internal_excluded"], "int"),
        ("Admin/back-office excluded", v["admin_excluded"], "int"),
        ("Unmapped excluded", v["unmapped_excluded"], "int"),
        ("Unmapped %", v["unmapped_pct"], "pct"),
        ("Main AA ext-998 legs", v["main_aa_998_legs"], "int"),
        ("In-scope records (all divisions)", v["helpline_records"], "int"),
        ("Classification agreement %", v["classification_agreement_pct"], "pct"),
        ("Calls compared", v["classification_compared"], "int"),
        ("Disagreements", v["classification_disagreements"], "int"),
        ("Phone extracted % (all)", v["phone_extracted_pct_all"], "pct"),
        ("Phone extracted % (inbound)", v["phone_extracted_pct_inbound"], "pct"),
    ]
    r = 3
    for k, val, kind in vkv:
        ws.write(r, 0, k, fmt["kv_k"])
        ws.write_number(r, 1, float(val), fmt["pct"] if kind == "pct" else fmt["kv_v"])
        r += 1
    r += 1
    ws.write(r, 0, "Top disagreement extensions", fmt["kv_k"]); r += 1
    for ext, cnt in v.get("disagreement_top_extensions", {}).items():
        ws.write(r, 0, f"ext {ext}", fmt["txt"]); ws.write_number(r, 1, float(cnt), fmt["int"]); r += 1
    ws.set_column(0, 0, 30); ws.set_column(1, 1, 16)

    wb.close()
    return path


if __name__ == "__main__":
    import sys
    src = sys.argv[1] if len(sys.argv) > 1 else "output/metrics.json"
    out = sys.argv[2] if len(sys.argv) > 2 else "output/ATIME_Call_Data.xlsx"
    region_key = sys.argv[3] if len(sys.argv) > 3 else "cleveland"
    with open(src) as f:
        m = json.load(f)
    render(m, out, region_key)
    print(f"wrote {out}")