# ============================================================
# ADDITIVE: eSSL "Monthly Status Report (Detailed Work Duration (Four Punch))" .xls reader
# ------------------------------------------------------------
# Same return shape as essl_pdf.parse_report(), so the EXISTING essl_sync.import_report() loads it unchanged.
# Per employee block: Status / InTime1 / OutTime1 / InTime2 / OutTime2 / Duration ... one column per day.
# IN = InTime1, OUT = the last out punch of the day (OutTime2, else OutTime1).
# Needs `pip install xlrd` (the .xls binary format).
# ============================================================
import re
from datetime import date

from .essl_pdf import PdfFormatError, STATUS_MAP, TIME_RE, _norm_status, parse_period

DAY_RE = re.compile(r"^\s*(\d{1,2})\s+[A-Za-z]{1,2}\s*$")


def _s(v):
    return "" if v is None else str(v).strip()


def _t(v):
    v = _s(v)
    return v if TIME_RE.match(v) else None


def parse_report(file_or_bytes):
    try:
        import xlrd
    except ImportError:
        raise PdfFormatError("The server needs the 'xlrd' package to read .xls reports (pip install xlrd).")
    data = file_or_bytes.read() if hasattr(file_or_bytes, "read") else file_or_bytes
    try:
        book = xlrd.open_workbook(file_contents=data)
        sh = book.sheet_by_index(0)
    except Exception as e:
        raise PdfFormatError(f"Cannot read the .xls file: {e}")
    rows = [[sh.cell_value(r, c) for c in range(sh.ncols)] for r in range(sh.nrows)]
    head = " ".join(_s(v) for r in rows[:8] for v in r if _s(v))
    if "Monthly Status Report" not in head:
        raise PdfFormatError("This does not look like an eSSL 'Monthly Status Report' .xls file.")
    period = parse_period(head)
    m = re.search(r"Company:\s*(.+?)\s+Printed On", head)
    company = m.group(1).strip() if m else None
    m = re.search(r"Printed On\s*:\s*(.+?\d{2}:\d{2})", head)
    printed = m.group(1).strip() if m else None
    daycol = {}
    for r in rows[:12]:
        if _s(r[0]) == "Days":
            for c, v in enumerate(r):
                mm = DAY_RE.match(_s(v))
                if mm:
                    daycol[int(mm.group(1))] = c
            break
    if not daycol:
        raise PdfFormatError("The day header row ('1 Th', '2 F' ...) was not found.")
    employees, dept = [], None
    for i, r in enumerate(rows):
        if _s(r[0]) == "Department:":
            dept = next((_s(v) for v in r[1:] if _s(v)), None)
        if _s(r[0]) != "Employee:":
            continue
        label = next((_s(v) for v in r[1:7] if " : " in _s(v)), "")
        if " : " not in label:
            continue
        code, name = [x.strip() for x in label.split(" : ", 1)]
        block = {}
        for k in range(1, 12):
            if i + k >= len(rows) or _s(rows[i + k][0]) == "Employee:":
                break
            block[_s(rows[i + k][0])] = rows[i + k]
        if "Status" not in block:
            continue
        days = {}
        for d, c in daycol.items():
            st = _norm_status(_s(block["Status"][c]))
            if not st:
                continue
            tin = _t(block.get("InTime1", [""] * (c + 1))[c])
            outs = [_t(block.get(n, [""] * (c + 1))[c]) for n in ("OutTime2", "OutTime1")]
            tout = next((o for o in outs if o), None)
            tot = _s(block.get("Duration", [""] * (c + 1))[c]) or None
            days[d] = {"status": st, "status_label": STATUS_MAP.get(st, st), "in": tin, "out": tout, "total": tot}
        employees.append({"code": code, "name": name, "department": dept, "days": days})
    if not employees:
        raise PdfFormatError("No employee blocks found in this .xls file.")
    merged = {}
    for e in employees:
        t = merged.setdefault(e["code"], {**e, "days": {}})
        t["days"].update(e["days"])
    return {"period": period, "company": company, "printed_on": printed, "employees": list(merged.values())}
