"""The LFCS timesheet — week maths and the rendered .xlsx.

Sign-on / sign-off events are the presence record and this module never edits one. Hours are
DERIVED here, every time, from the events as they stand. Rebuild is idempotent: same events in,
same sheet out, so it is safe to run on every sign-off.

Two files hold the same events, on purpose:
  <job>/05 - Safety & Compliance/Sign-On/Attendance-Register.csv   per JOB    — the muster
  <Timesheets>/Crew/<Worker>/Attendance.csv                        per WORKER — the timesheet
The job register is the safety record. The worker index exists because a bloke can be on two jobs
in one week and his timesheet is one sheet — without it, the second job's rebuild would overwrite
the first job's hours. It is derivable from the job registers if it is ever lost.
"""
from __future__ import annotations

import csv
import re
from dataclasses import dataclass, field
from datetime import date, datetime, timedelta
from io import BytesIO
from pathlib import Path
from zoneinfo import ZoneInfo

from . import storage

TZ = ZoneInfo("Australia/Sydney")
HERE = Path(__file__).parent
TEMPLATE = HERE / "assets" / "LFCS-Timesheet.xlsx"

SHEET = "Timesheet"
# Mon..Sun. Each day is a 3-row merged block on the LFCS form; write the top row of the block.
DAY_ROWS = {0: 12, 1: 15, 2: 18, 3: 21, 4: 24, 5: 27, 6: 30}
TOTAL_CELL = "F33"
# The paper form already has a Day Works Y/N column — N is the box, O carries the printed Y/N a
# bloke circles by hand. A signed T&M docket fills the box in for him.
DAYWORKS_COL = "N"

ATTENDANCE = "Attendance.csv"                                     # per-worker index
# Same columns as the job register (storage.ATTENDANCE_HEADER) — the two files hold the same rows.
ATTENDANCE_HEADER = ["timestamp", "job", "name", "event", "lunch", "note", "source",
                     "day", "field", "old", "new", "reason", "by"]
LUNCH_CHOICES = ["0:00", "0:30", "1:00"]
DEFAULT_LUNCH = "0:30"
DAY_LABELS = ["Mon", "Tue", "Wed", "Thur", "Fri", "Sat", "Sun"]


# --------------------------------------------------------------------------- small helpers


def safe_name(name: str) -> str:
    return re.sub(r"[^\w .-]+", "", name).strip()


def parse_lunch(s: str) -> int:
    """'0:30' -> 30. Blank or junk -> 0. Never raises: a bad cell must not lose a day's hours."""
    s = (s or "").strip()
    if not s:
        return 0
    m = re.match(r"^(\d+):(\d{1,2})$", s)
    if m:
        return int(m.group(1)) * 60 + int(m.group(2))
    try:
        return int(round(float(s) * 60))
    except ValueError:
        return 0


def fmt_lunch(minutes: int) -> str:
    return f"{minutes // 60}:{minutes % 60:02d}"


def fmt_ampm(dt: datetime) -> str:
    """7:12 AM — how the LFCS sheet is written by hand."""
    return dt.strftime("%I:%M %p").lstrip("0")


def parse_hhmm(s: str):
    """'17:00' (what a phone's time picker gives) or '5:00 PM' (what a bloke types). None if junk."""
    s = re.sub(r"\s+", " ", (s or "").strip().upper().replace(".", ""))
    for fmt in ("%H:%M", "%I:%M %p", "%I:%M%p"):
        try:
            return datetime.strptime(s, fmt).time()
        except ValueError:
            pass
    return None


def parse_ts(s: str) -> datetime | None:
    try:
        dt = datetime.fromisoformat((s or "").strip())
    except ValueError:
        return None
    return dt.replace(tzinfo=TZ) if dt.tzinfo is None else dt.astimezone(TZ)


def num(hours: float):
    """8.0 -> 8, 7.25 -> 7.25. The sheet's own cells are plain numbers."""
    h = round(hours + 1e-9, 2)
    return int(h) if abs(h - int(h)) < 0.005 else h


def week_start(d: date) -> date:
    """Monday of the week holding d."""
    return d - timedelta(days=d.weekday())


def week_ending(monday: date) -> date:
    """The LFCS sheet is labelled by its Friday: 'Week Ending 7 Aug 2026'."""
    return monday + timedelta(days=4)


def we_tag(friday: date) -> str:
    return f"{friday.year}.{friday.month}.{friday.day}"


def week_filename(worker: str, monday: date) -> str:
    return f"{safe_name(worker)} Timesheet WE {we_tag(week_ending(monday))}.xlsx"


# --------------------------------------------------------------------------- the week


@dataclass
class Day:
    date: date
    label: str
    start: datetime | None = None
    finish: datetime | None = None
    lunch_min: int = 0
    hours: float = 0.0
    on_now: bool = False
    jobs: list[str] = field(default_factory=list)
    notes: list[str] = field(default_factory=list)
    is_today: bool = False
    # field name -> "reason — who". Present means a foreman corrected it; the original tap is still
    # in the register underneath. Never a silent overwrite.
    adjusted: dict = field(default_factory=dict)
    # He was on a T&M docket that day and the head contractor signed it. The Day Works column on
    # the paper form fills itself in — nobody has to remember on a Friday.
    dayworks: bool = False
    dockets: list[str] = field(default_factory=list)

    @property
    def job_text(self) -> str:
        return ", ".join(self.jobs)

    @property
    def note_text(self) -> str:
        # Two jobs or a split shift means two lots of his words on one line. Semicolon, not a
        # space — "reo to deck, bay 3 tied deck steel bay 3" reads like a typo.
        return "; ".join(self.notes)

    @property
    def start_text(self) -> str:
        return fmt_ampm(self.start) if self.start else ""

    @property
    def finish_text(self) -> str:
        return fmt_ampm(self.finish) if self.finish else ""

    @property
    def lunch_text(self) -> str:
        # Lunch is declared at sign-off. While he is still on, it is not a fact yet — leave it blank
        # rather than show a 0:00 he never said.
        return fmt_lunch(self.lunch_min) if (self.finish or "lunch" in self.adjusted) else ""

    @property
    def hours_text(self) -> str:
        return f"{num(self.hours)}" if self.hours >= 0.01 else ""


def build_week(events: list[dict], monday: date, now: datetime | None = None) -> list[Day]:
    """Pair on/off events into days. An unclosed 'on' counts up to now and is flagged on_now.

    Split shifts are handled: every on pairs with the next off, Start is the first on of the day,
    Finish the last off. Lunch is the sum of what was declared at each sign-off.
    """
    now = now or datetime.now(TZ)
    days = {monday + timedelta(days=i): Day(monday + timedelta(days=i), DAY_LABELS[i]) for i in range(7)}
    for d in days.values():
        d.is_today = d.date == now.date()

    per_day: dict[date, list[tuple[datetime, dict]]] = {}
    adjusts: list[dict] = []
    for row in events:
        ev = (row.get("event") or "").strip().lower()
        if ev == "adjust":
            # Keyed by the day it CORRECTS, not the day the foreman typed it. Held back until the
            # taps have been paired, then laid over the top.
            adjusts.append(row)
            continue
        if ev == "dayworks":
            # Written when a docket he is named on gets signed. Keyed by the day the work was done,
            # not the day it was signed, and it never touches his hours — only the Y/N column.
            try:
                dw = days.get(date.fromisoformat((row.get("day") or "").strip()))
            except ValueError:
                dw = None
            if dw:
                dw.dayworks = True
                ref = (row.get("note") or "").strip()
                if ref and ref not in dw.dockets:
                    dw.dockets.append(ref)
            continue
        ts = parse_ts(row.get("timestamp", ""))
        if not ts or ts.date() not in days:
            continue
        per_day.setdefault(ts.date(), []).append((ts, row))

    for dt, rows in per_day.items():
        day = days[dt]
        rows.sort(key=lambda x: x[0])
        worked = timedelta()
        open_at: datetime | None = None
        for ts, row in rows:
            ev = (row.get("event") or "").strip().lower()
            job = (row.get("job") or "").strip()
            note = (row.get("note") or "").strip()
            if job and job not in day.jobs:
                day.jobs.append(job)
            if note and note not in day.notes:
                day.notes.append(note)
            if ev == "on":
                if open_at is None:
                    open_at = ts
                    if day.start is None:
                        day.start = ts
            elif ev == "off":
                day.lunch_min += parse_lunch(row.get("lunch", ""))
                if open_at is not None:
                    worked += ts - open_at
                    open_at = None
                day.finish = ts
        if open_at is not None:
            day.on_now = True
            worked += max(now - open_at, timedelta())
        day.hours = max(worked.total_seconds() / 3600 - day.lunch_min / 60, 0.0)

    _apply_adjustments(days, adjusts)
    return list(days.values())


def _apply_adjustments(days: dict[date, Day], adjusts: list[dict]) -> None:
    """Latest correction per field per day wins, laid over the taps — which are left alone.

    A corrected day is recomputed straight off start/finish/lunch. If it held a split shift, that
    collapses to the one span the foreman has just said it was, which is the point of him correcting
    it. Presence is untouched: open_since() still reads only the on/off taps, because who was on
    site is the safety record and a foreman's discretion belongs in hours, not in the muster.
    """
    for row in sorted(adjusts, key=lambda r: (r.get("timestamp") or "")):
        try:
            when = date.fromisoformat((row.get("day") or "").strip())
        except ValueError:
            continue
        day = days.get(when)
        if not day:
            continue
        fld = (row.get("field") or "").strip().lower()
        val = (row.get("new") or "").strip()
        if fld in ("start", "finish"):
            t = parse_hhmm(val)
            if not t:
                continue
            stamped = datetime.combine(when, t).replace(tzinfo=TZ)
            if fld == "start":
                day.start = stamped
            else:
                day.finish = stamped
                day.on_now = False
        elif fld == "lunch":
            day.lunch_min = parse_lunch(val)
        else:
            continue
        who = (row.get("by") or "").strip()
        why = (row.get("reason") or "").strip()
        day.adjusted[fld] = " — ".join(x for x in (why, who) if x)

    for day in days.values():
        if day.adjusted and day.start and day.finish:
            worked = (day.finish - day.start).total_seconds() / 3600
            day.hours = max(worked - day.lunch_min / 60, 0.0)


def week_hours(days: list[Day]) -> float:
    return sum(d.hours for d in days)


def open_since(events: list[dict], now: datetime | None = None) -> datetime | None:
    """Is he on right now? Returns the time he signed on, or None. Today's events only —
    a bloke who forgot to sign off yesterday is not still on site this morning."""
    now = now or datetime.now(TZ)
    todays = []
    for row in events:
        ts = parse_ts(row.get("timestamp", ""))
        if ts and ts.date() == now.date():
            todays.append((ts, (row.get("event") or "").strip().lower()))
    todays.sort(key=lambda x: x[0])
    open_at = None
    for ts, ev in todays:
        if ev == "on" and open_at is None:
            open_at = ts
        elif ev == "off":
            open_at = None
    return open_at


# --------------------------------------------------------------------------- the .xlsx


def render_xlsx(worker: str, monday: date, days: list[Day]) -> bytes:
    """Fill the real LFCS form. Nothing is invented — a day with no events stays blank.

    An adjusted cell is shaded and carries a note saying who changed it and why, so the office can
    see at a glance that a number was corrected rather than tapped.
    """
    from openpyxl import load_workbook
    from openpyxl.comments import Comment
    from openpyxl.styles import PatternFill

    wb = load_workbook(TEMPLATE)
    ws = wb[SHEET]
    adj_fill = PatternFill("solid", fgColor="FFE9A8")

    def mark(cell_ref: str, why: str) -> None:
        c = ws[cell_ref]
        c.fill = adj_fill
        c.comment = Comment(f"Adjusted: {why}", "LFCS Sign-On")

    friday = week_ending(monday)
    ws["A3"] = f"Name: {worker}"
    ws["A7"] = f"Dates: Week Ending {friday.day} {friday:%b %Y}"
    ws["C6"] = f"{monday:%d/%m/%Y}"
    ws["E6"] = f"{friday:%d/%m/%Y}"

    total = 0.0
    for d in days:
        r = DAY_ROWS[d.date.weekday()]
        ws[f"B{r}"] = f"{d.date:%d/%m/%Y}"
        if d.start:
            ws[f"C{r}"] = d.start_text
        if d.finish:
            ws[f"D{r}"] = d.finish_text
        if d.lunch_text:
            ws[f"E{r}"] = fmt_lunch(d.lunch_min)
        if d.hours >= 0.01:
            ws[f"F{r}"] = num(d.hours)
            total += d.hours
        for fld, cell in (("start", "C"), ("finish", "D"), ("lunch", "E")):
            if fld in d.adjusted:
                mark(f"{cell}{r}", d.adjusted[fld])
        if d.adjusted:
            mark(f"F{r}", "; ".join(f"{k} {v}" for k, v in d.adjusted.items()))
        if d.jobs:
            ws[f"G{r}"] = d.job_text
        desc = d.note_text or d.job_text
        if desc:
            ws[f"J{r}"] = desc
        if d.dayworks:
            ws[f"{DAYWORKS_COL}{r}"] = "Y"
    ws[TOTAL_CELL] = num(total) if total else None

    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


# --------------------------------------------------------------------------- per-worker files


class CrewFiles:
    """<Timesheets root>/Crew/<Worker Name>/ — the worker's own events and his rendered weeks.

    Plain files on the Drive mount, same as everything else. Nothing here deletes or edits: the
    events file is append-only and the .xlsx is a rebuild of what the events already say.
    """

    def __init__(self, root: str):
        self.root = Path(root)

    def worker_dir(self, worker: str, create: bool = True) -> Path:
        p = self.root / "Crew" / safe_name(worker)
        if create:
            p.mkdir(parents=True, exist_ok=True)
        return p

    def append_event(self, worker: str, row: dict) -> str:
        p = self.worker_dir(worker) / ATTENDANCE
        storage.append_csv(p, ATTENDANCE_HEADER, row)
        return str(p)

    def list_events(self, worker: str) -> list[dict]:
        p = self.worker_dir(worker, create=False) / ATTENDANCE
        if not p.exists():
            return []
        with p.open(newline="", encoding="utf-8") as f:
            return [dict(r) for r in csv.DictReader(f)]

    def write_week(self, worker: str, monday: date, data: bytes) -> str:
        p = self.worker_dir(worker) / week_filename(worker, monday)
        p.write_bytes(data)
        return str(p)