#!/usr/bin/env python3
"""tracker_io.py — shared, safe I/O for "Application Job Tracker.xlsx".

Single source of truth for how ANYTHING writes the tracker. Used by
serve_dashboard.py; daily/interactive sweep sessions should use it too:

    import tracker_io as tio

    def my_edit(wb):                 # wb is an openpyxl workbook
        wb["Leads"].append([...])
        return "whatever you like"

    result = tio.mutate(my_edit)     # lock -> backup -> load -> edit ->
                                     # validate -> verified in-place save

What mutate() guarantees, in order:
  1. LOCK      — cooperative ".tracker.lock" file (O_CREAT|O_EXCL). Stale
                 locks (>2 min) are stolen. Raises TrackerBusy if it stays
                 locked. Anything that writes the tracker must go through
                 this lock; readers don't need it.
  2. BACKUP    — ONE rolling daily backup: "_Backups/tracker-YYYY-MM-DD.xlsx"
                 (pre-edit state; overwritten on later writes the same day),
                 pruned to the newest 5. Do NOT create per-session dated
                 backups in the main folder any more.
  3. VALIDATE  — the mutated workbook is serialized to memory and re-opened,
                 and its schema checked (required sheets + required headers +
                 Job Tracker non-empty). A workbook that looks corrupted is
                 NEVER written to disk; SchemaError is raised instead.
  4. SAVE      — verified IN-PLACE write (truncate + write + fsync + size
                 check, retried). Deliberately NOT an atomic rename: this
                 folder is synced by Nextcloud via macOS File Provider, and
                 rename-replace looks like delete+create to the sync layer,
                 which once made the file vanish. In-place produces a single
                 'modified' event. Steps 2-3 are what make this safe.

Also here: housekeeping() — prunes old backups and processed
.verify_queue.json entries (>7 days). Call it from long-running processes
once in a while; it never raises.

Requires openpyxl (only when actually loading/saving a workbook).
"""

import io
import json
import os
import shutil
import time
from contextlib import contextmanager
from datetime import date, datetime, timedelta
from pathlib import Path

def _jobdash_data_dir(_default):
    """Pi deployment: use the Nextcloud data-dir copy of the Job Search folder
    when jobdash_pi.json (written by deploy_to_pi.py) sits next to this script."""
    try:
        import json as _json
        return Path(_json.loads(
            (_default / "jobdash_pi.json").read_text())["data_dir"])
    except Exception:
        return _default
HERE = _jobdash_data_dir(Path(__file__).resolve().parent)
XLSX = HERE / "Application Job Tracker.xlsx"
LOCK = HERE / ".tracker.lock"
BACKUP_DIR = HERE / "_Backups"
BACKUP_KEEP = 5
VERIFY_QUEUE = HERE / ".verify_queue.json"
PRUNE_DAYS = 7
LOCK_STALE_SECS = 120

# Required sheets and the headers each must contain (extra headers are fine,
# order doesn't matter). Metrics is formula-driven, so only presence is checked.
LEAD_HEADERS = ("Company", "Role", "Location", "Salary", "Link",
                "Verified Live", "Odds (honest)", "Notes")
REQUIRED_SHEETS = {
    "Job Tracker": ("Company", "Link to JD", "Date Applied", "Stage", "Role",
                    "Location", "Notes"),
    "Metrics": (),
    "Leads": LEAD_HEADERS,
    "Eng Leads": LEAD_HEADERS,
    "Manual Review": LEAD_HEADERS,
    "Dead Leads": LEAD_HEADERS + ("Marked Dead",),
    "Archived": ("Company", "Link to JD", "Date Applied", "Stage", "Role",
                 "Location", "Notes", "Archived"),
}


class TrackerBusy(Exception):
    """The .tracker.lock is held by someone else and didn't clear in time."""


class SchemaError(Exception):
    """The workbook is missing sheets/headers — refused to save it."""


# ---------- lock ----------

def acquire_lock(retries=12, wait=0.25):
    """Cooperative lock file, shared by the dashboard server and sweeps."""
    for _ in range(retries):
        try:
            fd = os.open(LOCK, os.O_CREAT | os.O_EXCL | os.O_WRONLY)
            os.write(fd, str(os.getpid()).encode())
            os.close(fd)
            return
        except FileExistsError:
            try:
                if time.time() - LOCK.stat().st_mtime > LOCK_STALE_SECS:
                    LOCK.unlink()  # stale — steal it
                    continue
            except FileNotFoundError:
                continue
            time.sleep(wait)
    raise TrackerBusy


def release_lock():
    try:
        LOCK.unlink()
    except FileNotFoundError:
        pass


@contextmanager
def locked(retries=12, wait=0.25):
    acquire_lock(retries=retries, wait=wait)
    try:
        yield
    finally:
        release_lock()


# ---------- schema validation ----------

def _check_workbook(wb):
    """Raise SchemaError unless every required sheet + header is present."""
    problems = []
    for sheet, required in REQUIRED_SHEETS.items():
        if sheet not in wb.sheetnames:
            problems.append(f"missing sheet {sheet!r}")
            continue
        if not required:
            continue
        ws = wb[sheet]
        headers = {str(c.value).strip() for c in ws[1] if c.value is not None}
        missing = [h for h in required if h not in headers]
        if missing:
            problems.append(f"{sheet!r} missing header(s): {', '.join(missing)}")
    if "Job Tracker" in wb.sheetnames:
        ws = wb["Job Tracker"]
        if ws.max_row < 2 or ws.cell(2, 1).value in (None, ""):
            problems.append("'Job Tracker' has no data rows")
    if problems:
        raise SchemaError("; ".join(problems))


def validate_bytes(data):
    """Validate serialized xlsx bytes (also proves they re-open cleanly)."""
    import openpyxl
    wb = openpyxl.load_workbook(io.BytesIO(data), read_only=True)
    try:
        _check_workbook(wb)
    finally:
        wb.close()


def validate_file(path=None):
    """Validate an xlsx on disk (default: the live tracker)."""
    validate_bytes(Path(path or XLSX).read_bytes())


# ---------- backup ----------

def daily_backup():
    """Rolling pre-edit backup: _Backups/tracker-YYYY-MM-DD.xlsx.
    Overwrites the same-day file (last pre-edit state of the day wins),
    then prunes to the newest BACKUP_KEEP."""
    BACKUP_DIR.mkdir(exist_ok=True)
    shutil.copy2(XLSX, BACKUP_DIR / f"tracker-{date.today().isoformat()}.xlsx")
    prune_backups()


def prune_backups(keep=BACKUP_KEEP):
    """Keep the newest `keep` tracker-*.xlsx backups; best-effort delete rest.
    Sorted by filename (ISO date), not mtime — the sync layer rewrites mtimes."""
    removed = 0
    try:
        files = sorted(BACKUP_DIR.glob("tracker-*.xlsx"),
                       key=lambda p: p.name, reverse=True)
        for p in files[keep:]:
            try:
                p.unlink()
                removed += 1
            except OSError:
                pass
    except OSError:
        pass
    return removed


# ---------- save ----------

def save_workbook(wb, path=None):
    """Serialize -> validate in memory -> verified in-place write.
    See module docstring for why this is NOT a rename-replace."""
    path = Path(path or XLSX)
    buf = io.BytesIO()
    wb.save(buf)
    data = buf.getvalue()
    validate_bytes(data)  # SchemaError here means nothing touched the disk
    for attempt in range(3):
        with open(path, "wb") as f:
            f.write(data)
            f.flush()
            os.fsync(f.fileno())
        if path.exists() and path.stat().st_size == len(data):
            return
        time.sleep(0.5)
    raise RuntimeError("Write did not stick (sync layer interfered) — "
                       "check the folder; a backup copy exists in _Backups/.")


def mutate(mutator, path=None):
    """Lock -> daily backup -> load (formulas kept) -> mutator(wb) ->
    validate -> save. Returns whatever mutator returns."""
    import openpyxl
    path = Path(path or XLSX)
    with locked():
        if path == XLSX:
            daily_backup()
        wb = openpyxl.load_workbook(path)  # data_only=False: formulas preserved
        try:
            result = mutator(wb)
            save_workbook(wb, path)
        finally:
            wb.close()
    nc_scan()  # Pi: tell Nextcloud to re-index after the direct write
    return result


# ---------- housekeeping ----------

def prune_verify_queue(days=PRUNE_DAYS):
    """Drop .verify_queue.json entries processed more than `days` ago.
    Unprocessed entries are always kept. Returns count removed."""
    try:
        queue = json.loads(VERIFY_QUEUE.read_text())
    except Exception:
        return 0
    if not isinstance(queue, list):
        return 0
    cutoff = (datetime.now() - timedelta(days=days)).isoformat(timespec="seconds")

    def fresh(e):
        if not isinstance(e, dict):
            return False  # junk entry — drop
        res = e.get("result")
        if not res:
            return True   # still pending
        return str(res.get("processed_at", "")) >= cutoff

    kept = [e for e in queue if fresh(e)]
    removed = len(queue) - len(kept)
    if removed:
        try:
            VERIFY_QUEUE.write_text(json.dumps(kept, indent=2))
        except OSError:
            return 0
    return removed


def prune_apify_results(days=PRUNE_DAYS):
    """Delete .apify_results_YYYY-MM-DD.json older than `days` days.
    (apify_jobs.py also prunes after each run; this is a belt-and-braces.)"""
    cutoff = (date.today() - timedelta(days=days)).isoformat()
    removed = 0
    for p in HERE.glob(".apify_results_*.json"):
        stamp = p.name[len(".apify_results_"):-len(".json")]
        if stamp < cutoff:
            try:
                p.unlink()
                removed += 1
            except OSError:
                pass
    return removed


def housekeeping():
    """Run all prunes; never raises. Returns a one-line summary."""
    try:
        b = prune_backups()
        q = prune_verify_queue()
        r = prune_apify_results()
        return f"housekeeping: pruned {b} backup(s), {q} queue entr(ies), {r} result file(s)"
    except Exception as e:  # pragma: no cover — belt and braces
        return f"housekeeping error (ignored): {e!r}"


# ---------- Nextcloud re-index (added by deploy_to_pi.py, Pi only) ----------

_PI_CFG_PATH = Path(__file__).resolve().parent / "jobdash_pi.json"


def nc_scan():
    """After a direct write into the Nextcloud data dir, ask Nextcloud to
    re-index the Job Search folder so sync clients (Mac, iPhone) see it.
    Best-effort: never raises, returns True only on a clean scan.
    No-op when jobdash_pi.json is absent (i.e. on the Mac)."""
    try:
        cfg = json.loads(_PI_CFG_PATH.read_text())
        cmd = cfg.get("scan_cmd")
        if not cmd:
            return False
        import subprocess as _sp
        r = _sp.run(cmd, capture_output=True, text=True, timeout=600)
        if r.returncode != 0:
            _nc_scan_log(f"scan failed rc={r.returncode}: "
                         f"{(r.stderr or r.stdout)[-300:]}")
        return r.returncode == 0
    except Exception as e:
        _nc_scan_log(f"scan error: {e!r}")
        return False


def _nc_scan_log(msg):
    try:
        with open(HERE / ".apify_log.txt", "a") as f:
            f.write(f"{datetime.now():%Y-%m-%d %H:%M:%S} [ncscan] {msg}\n")
    except Exception:
        pass