Workflow-App/Workflow/scripts/SARS retun VAT201 push to PB.py

"""
SARS retun VAT201 push to PB.py

Reads the JSON output of `SARS Retun VAT201 single or all pty.py`
(the SARS capture script) and upserts one row per (company, period)
into the PocketBase collection `ysterbaard_SARS_VAT_VAT201`.

The script does NOT touch the SARS website — that's the capture
script's job. This script is the "save what we already captured"
step in the workflow.

CLI:
    --input <path>            path to a specific JSON file
    --input latest            (default) newest *.json in sars_captures/
    --list                    list available JSON files, don't write
    --dry-run                 print what would be written, no writes

Behaviour:
    * For each company in the JSON:
        * For each period in results[]  → POST or PATCH (Return_Filed=true)
        * For each period in not_filed[] → POST or PATCH (Return_Filed=false)
        * Periods in errors[] are SKIPPED (capture didn't succeed)
    * Upsert key: company_period_key = f"{pb_id}:{period}"
        → UNIQUE index in PB → at most one row per (company, period)
    * When a row already exists, PATCH in place (option A).
    * We do NOT write to: status (workflow app's job), documents,
      notes, is_active, last_updated_by, declared_amount, version
      (work-page-only audit info; no PB slot).
    * We DO write date_submitted (from the work page's Status Date
      cell, captured by the SARS capture script as `date_filed` and
      renamed here). Re-runs overwrite with the SARS-side value (it
      should be stable across runs — it's the original submission
      date, not the run date).

PB env:
    PB_URL, PB_TOKEN are read from `Workflow/.env.local`.

Schema (verified live 2026-09-14 against PB):
    ysterbaard_SARS_VAT_VAT201
        id                                text [sys]
        period                            text  required  pattern ^\\d{6}$
        standard_rate_1                   number
        zero_rate_2                       number
        exempt_and_non_supplies_3         number
        capital_goods_and_services_supplied_to_you_14   number
        other_goods_and_services_supplied_to_you_15     number
        total_payable                     number          (Field 38 from Section C)
        interest_d                        number          (always 0 unless overdue)
        penalties_d                       number          (always 0 unless overdue)
        status                            select {draft, submitted, paid}  required
        date_submitted                    date
        date_paid                         date
        due_date                          date
        payment_reference                 text  max 100
        company_period_key                text  UNIQUE INDEX (hidden)
        company                           relation → ysterbaard_companies
        Return_Filed                      bool
        last_updated                      date
        notes                             text
        first_name, surname, capacity     text  (declaration section)
        bus_tel_no, cell_no               number
        contact_email                     email
        is_active                         bool
        documents                         file
"""

import argparse
import json
import os
import sys
import time
from datetime import datetime
from pathlib import Path
from typing import List, Optional, Tuple

import requests


# ── Paths & env ──────────────────────────────────────────────────

SCRIPT_DIR = Path(__file__).resolve().parent
PROJECT_ROOT = SCRIPT_DIR.parent
ENV_PATH = PROJECT_ROOT / ".env.local"
CAPTURES_DIR = Path(r"C:\Users\SJ\Downloads\sars_captures")
PB_COLLECTION = "ysterbaard_SARS_VAT_VAT201"


def load_env() -> None:
    """Load .env.local into os.environ (best-effort, no hard dep on dotenv)."""
    if not ENV_PATH.exists():
        return
    try:
        from dotenv import load_dotenv
        load_dotenv(ENV_PATH)
        return
    except ImportError:
        pass
    for line in ENV_PATH.read_text(encoding="utf-8").splitlines():
        line = line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        k, v = line.split("=", 1)
        os.environ.setdefault(k.strip(), v.strip().strip('"').strip("'"))


# ── PB HTTP helpers ──────────────────────────────────────────────

def pb_headers() -> dict:
    token = os.environ.get("PB_TOKEN", "")
    if not token:
        raise RuntimeError(
            f"PB_TOKEN missing. Add it to {ENV_PATH}."
        )
    return {"Authorization": f"Bearer {token}"}


def pb_base() -> str:
    return os.environ.get("PB_URL", "https://pb.agent.apphub.co.za").rstrip("/")


def pb_find_by_key(key: str) -> Optional[dict]:
    """Return the existing record for `company_period_key`, or None."""
    r = requests.get(
        f"{pb_base()}/api/collections/{PB_COLLECTION}/records",
        headers=pb_headers(),
        params={"filter": f'company_period_key="{key}"', "perPage": 1},
        timeout=30,
    )
    r.raise_for_status()
    items = r.json().get("items", [])
    return items[0] if items else None


def pb_post(payload: dict) -> dict:
    r = requests.post(
        f"{pb_base()}/api/collections/{PB_COLLECTION}/records",
        headers={**pb_headers(), "Content-Type": "application/json"},
        json=payload,
        timeout=30,
    )
    if r.status_code >= 400:
        raise RuntimeError(
            f"PB POST {r.status_code}: {r.text[:500]}"
        )
    r.raise_for_status()
    return r.json()


def pb_patch(record_id: str, payload: dict) -> dict:
    r = requests.patch(
        f"{pb_base()}/api/collections/{PB_COLLECTION}/records/{record_id}",
        headers={**pb_headers(), "Content-Type": "application/json"},
        json=payload,
        timeout=30,
    )
    if r.status_code >= 400:
        raise RuntimeError(
            f"PB PATCH {r.status_code}: {r.text[:500]}"
        )
    r.raise_for_status()
    return r.json()


# ── Field mapping ───────────────────────────────────────────────

def _to_float(s) -> Optional[float]:
    """Parse a number string from the JSON. Returns None for empty/None/non-numeric."""
    if s is None:
        return None
    s = str(s).strip()
    if not s:
        return None
    # SARS sometimes uses comma as thousands sep; defensively strip here.
    s = s.replace(",", "")
    try:
        return float(s)
    except ValueError:
        return None


def _pb_date(dt: datetime) -> str:
    """Format a datetime in PocketBase's expected `date` field format:
    "YYYY-MM-DD HH:MM:SS.sssZ" (space separator, millis, trailing Z).
    PB silently drops ISO "T"-separated strings on date fields, so this
    helper is required."""
    return dt.strftime("%Y-%m-%d %H:%M:%S.") + f"{dt.microsecond // 1000:03d}Z"


# Field list parallel to `ysterbaard_SARS_EMP201_PAYE`'s push payload,
# extended with the VAT201-specific numbers.
NUMERIC_FIELDS = [
    "standard_rate_1",
    "zero_rate_2",
    "exempt_and_non_supplies_3",
    "capital_goods_and_services_supplied_to_you_14",
    "other_goods_and_services_supplied_to_you_15",
    "total_payable",
]


def build_payload(period: str, pb_id_company: str, row: dict,
                  return_filed: bool) -> dict:
    """Build the PB row payload for a CAPTURED period (return_filed=True).
    For not_filed rows, the caller uses an empty row dict."""
    payload = {
        "company_period_key": f"{pb_id_company}:{period}",
        "company":           pb_id_company,
        "period":            period,
        "Return_Filed":      return_filed,
        "last_updated":      _pb_date(datetime.now()),
        # Status is a required PB select (draft / submitted / paid).
        # Default to 'submitted' on first write. The workflow app takes
        # it from there. PATCH deliberately does NOT touch this.
        "status":            "submitted" if return_filed else "draft",
    }
    if return_filed:
        # The capture script stores the SARS-side filing date as JSON
        # key `date_filed` (work page Status column); the PB schema uses
        # `date_submitted` (matches EMP201 naming). Rename on write.
        payload["date_submitted"] = row.get("date_filed") or None
        # Payment reference is captured from the form's Payment Details
        # section; only present when the v1 form loaded successfully.
        pr = (row.get("payment_reference") or "").strip() or None
        payload["payment_reference"] = pr
        # The 5 form amounts + Section C total.
        for key in NUMERIC_FIELDS:
            payload[key] = _to_float(row.get(key))
    return payload


def build_patch_payload(row: dict, return_filed: bool) -> dict:
    """Fields we update on PATCH. Deliberately does NOT include
    `status` — we don't want to overwrite a status the user manually
    set elsewhere."""
    patch = {
        "Return_Filed": return_filed,
        "last_updated": _pb_date(datetime.now()),
    }
    if return_filed:
        patch["date_submitted"]    = row.get("date_filed") or None
        patch["payment_reference"] = (row.get("payment_reference") or "").strip() or None
        for key in NUMERIC_FIELDS:
            patch[key] = _to_float(row.get(key))
    return patch


# ── Capture file resolution ─────────────────────────────────────

def find_latest_capture() -> Path:
    if not CAPTURES_DIR.exists():
        raise SystemExit(
            f"Captures dir not found: {CAPTURES_DIR}. "
            "Run the SARS capture script first."
        )
    candidates = sorted(CAPTURES_DIR.glob("*.json"),
                         key=lambda p: p.stat().st_mtime,
                         reverse=True)
    if not candidates:
        raise SystemExit(
            f"No *.json in {CAPTURES_DIR}. Run the capture script first."
        )
    return candidates[0]


def list_captures() -> List[Tuple[Path, float]]:
    if not CAPTURES_DIR.exists():
        return []
    out = []
    for p in sorted(CAPTURES_DIR.glob("*.json"),
                    key=lambda p: p.stat().st_mtime,
                    reverse=True):
        out.append((p, p.stat().st_mtime))
    return out


# ── Upsert loop ─────────────────────────────────────────────────

def upsert_period(period: str, pb_id: str, row: dict,
                  return_filed: bool, dry_run: bool,
                  stats: dict) -> None:
    """One (company, period) row. Updates stats. Never raises on
    a single-row failure (logged and counted in `errored`)."""
    if not pb_id:
        msg = f"{period}: missing company pb_id, skipping"
        print(f"  {msg}", flush=True)
        stats["skipped"] += 1
        stats["messages"].append(msg)
        return

    payload = build_payload(period, pb_id, row, return_filed)
    key = payload["company_period_key"]

    if dry_run:
        existing = "(would PATCH)" if pb_find_by_key(key) else "(would POST)"
        print(f"  {period}: {return_filed=!s:5}  {existing}  {key}",
              flush=True)
        stats["dry_run"] += 1
        return

    try:
        existing = pb_find_by_key(key)
        if existing:
            patch_payload = build_patch_payload(row, return_filed)
            pb_patch(existing["id"], patch_payload)
            print(f"  {period}: PATCHED  {key}", flush=True)
            stats["patched"] += 1
        else:
            pb_post(payload)
            print(f"  {period}: POSTED   {key}", flush=True)
            stats["posted"] += 1
    except Exception as e:
        msg = f"{period}: {type(e).__name__}: {e}"
        print(f"  {msg}", flush=True)
        stats["errored"] += 1
        stats["messages"].append(msg)


# ── Main ─────────────────────────────────────────────────────────

def main():
    ap = argparse.ArgumentParser(
        description="Push SARS capture JSON → PocketBase ysterbaard_SARS_VAT_VAT201."
    )
    ap.add_argument("--input", default="latest",
                    help='Path to capture JSON, or "latest" (default: latest)')
    ap.add_argument("--list", action="store_true",
                    help="List available capture JSON files and exit")
    ap.add_argument("--dry-run", action="store_true",
                    help="Print what would be written, no PB writes")
    args = ap.parse_args()

    load_env()

    if args.list:
        print("Available capture files (newest first):", flush=True)
        for p, mtime in list_captures():
            print(f"  {datetime.fromtimestamp(mtime).isoformat(timespec='seconds')}  "
                  f"{p.name}  ({p.stat().st_size} bytes)", flush=True)
        return

    if args.input == "latest":
        in_path = find_latest_capture()
    elif os.path.isfile(args.input):
        in_path = Path(args.input)
    else:
        candidate = CAPTURES_DIR / args.input
        if candidate.is_file():
            in_path = candidate
        else:
            raise SystemExit(
                f"Input not found: {args.input!r} (also tried {candidate})"
            )

    print(f"Reading:        {in_path}", flush=True)
    print(f"Dry run:        {args.dry_run}", flush=True)

    with open(in_path, "r", encoding="utf-8") as f:
        capture = json.load(f)

    companies = capture.get("companies", [])
    if not companies:
        raise SystemExit("No 'companies' array in the capture JSON.")

    stats = {
        "posted": 0, "patched": 0,
        "errored": 0, "skipped": 0, "dry_run": 0,
        "messages": [],
    }

    started = time.time()
    for ci, co in enumerate(companies, 1):
        pb_id = co.get("pb_id") or ""
        name = co.get("name") or "?"
        print(f"\n[{ci}/{len(companies)}] {name}  pb_id={pb_id}", flush=True)

        for r in co.get("results", []):
            period = r.get("period")
            if not period:
                continue
            upsert_period(period, pb_id, r, return_filed=True,
                          dry_run=args.dry_run, stats=stats)

        for period in co.get("not_filed", []):
            if not period:
                continue
            upsert_period(period, pb_id, {}, return_filed=False,
                          dry_run=args.dry_run, stats=stats)

    elapsed = time.time() - started

    print("\n" + "=" * 60, flush=True)
    if args.dry_run:
        print(f"DRY RUN — no writes performed.", flush=True)
        print(f"  would-write rows:  {stats['dry_run']}", flush=True)
    else:
        print(f"PB writes:", flush=True)
        print(f"  POSTED (new):     {stats['posted']}", flush=True)
        print(f"  PATCHED (updated): {stats['patched']}", flush=True)
    if stats["errored"]:
        print(f"  ERRORED:          {stats['errored']}", flush=True)
        for m in stats["messages"]:
            print(f"    {m}", flush=True)
    if stats["skipped"]:
        print(f"  SKIPPED:          {stats['skipped']}", flush=True)
    print(f"\nElapsed: {elapsed:.1f}s", flush=True)
    print(f"Source:  {in_path.name}", flush=True)
    print("=" * 60, flush=True)


if __name__ == "__main__":
    main()