#!/usr/bin/env python3
"""
Sync Revenue / GP to a Google Sheet from **live Xero** (Business Groups P&L), not from a workbook.

Rows match the previous «Daily Figures» layout: **By Branch** and **By Department** tabs
(columns: name, Total revenue, GP Cash Total, GP %, Updated). Only the remote Google Sheet
is written; nothing is saved locally. **Total** and **GP Cash Total** are rounded to whole
dollars (nearest integer). **Updated** uses **Australia/Perth** (AWST, UTC+8).

Default period is **Australian FYTD**: 1 July (FY containing «today») through **today**.
Use ``--period mtd`` for calendar month-to-date, or ``--from`` / ``--to`` for an explicit range.

**Local (repo checkout)**

  export GOOGLE_DAILY_FIGURES_SHEETS_ID='…'
  python3 backend/scripts/sync_daily_figures_rev_gp_to_sheet.py
  # or:  python3 scripts/sync_daily_figures_rev_gp_to_sheet.py   (shim to this file)

**Docker (same env + Xero token volume as the FastAPI app)**

  docker exec nixon-backend python3 /app/scripts/sync_daily_figures_rev_gp_to_sheet.py --dry-run
  docker exec nixon-backend python3 /app/scripts/sync_daily_figures_rev_gp_to_sheet.py

Uses ``CLIENT_ID`` / ``CLIENT_SECRET`` / ``REFRESH_TOKEN`` (and optional ``/app/data/tokens``)
exactly like the dashboard — ensure ``docker-compose`` passes the same ``env_file`` / vars.

Env:
  GOOGLE_DAILY_FIGURES_SHEETS_ID — spreadsheet ID
  GOOGLE_CREDENTIALS_PATH — service account JSON (default: <backend>/data/google_credentials.json)
  Plus the same Xero variables as the backend (see docker-compose ``backend`` service).
"""
from __future__ import annotations

import argparse
import logging
import os
import re
import sys
import warnings
from datetime import date, datetime, timedelta, timezone
from pathlib import Path
from typing import Any


def _resolve_backend_dir() -> Path:
    """
    Directory that contains the FastAPI ``app`` package.

    - Repo: ``…/nixon-kpi-stats/backend/scripts/this.py`` → ``…/backend``
    - Docker: ``/app/scripts/this.py`` → ``/app``
    """
    here = Path(__file__).resolve().parent
    candidates = [here.parent / "backend", here.parent]
    for base in candidates:
        if (base / "app" / "main.py").is_file():
            return base
    raise RuntimeError(
        "Cannot find backend root (expected app/main.py). "
        f"Script dir={here!s}, tried: {candidates!r}"
    )


BACKEND_DIR = _resolve_backend_dir()

DEFAULT_SPREADSHEET_ID = "1kkqRkUkcSeCG0UJjM6l5b_2aNWzKhf_PvXX7xClJC-Y"

TAB_ALIASES: dict[str, tuple[str, ...]] = {
    "By Branch": ("bybranch", "by branch"),
    "By Department": ("bydepartment", "by department"),
}

BRANCH_ORDER: list[tuple[str, str]] = [
    ("branch2", "Bunbury"),
    ("branch1", "Busselton"),
    ("branch3", "Mandurah"),
]


def _bootstrap_imports() -> None:
    bd = str(BACKEND_DIR)
    if bd not in sys.path:
        sys.path.insert(0, bd)


def _norm_key(s: str) -> str:
    return re.sub(r"\s+", "", s.strip().lower())


def load_dotenv_simple(path: Path) -> dict[str, str]:
    out: dict[str, str] = {}
    if not path.exists():
        return out
    for raw in path.read_text(encoding="utf-8").splitlines():
        line = raw.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        k, _, v = line.partition("=")
        k, v = k.strip(), v.strip().strip('"').strip("'")
        out[k] = v
    return out


def _apply_dotenv_files() -> None:
    """Match dashboard: repo .env then backend .env (Docker usually has vars from compose already)."""
    for path in (BACKEND_DIR.parent / ".env", BACKEND_DIR / ".env"):
        for k, v in load_dotenv_simple(path).items():
            os.environ.setdefault(k, v)


def _start_of_fy_au(d: date) -> date:
    if d.month >= 7:
        return date(d.year, 7, 1)
    return date(d.year - 1, 7, 1)


def _money_whole(v: Any) -> int:
    """Nearest whole dollar for sheet display (e.g. 522925.88 → 522926)."""
    return int(round(float(v or 0.0)))


def _now_perth() -> datetime:
    """Wall clock in Australia/Perth (no DST). Falls back to UTC+8 if tz data missing."""
    try:
        from zoneinfo import ZoneInfo

        return datetime.now(ZoneInfo("Australia/Perth"))
    except Exception:
        return datetime.now(timezone(timedelta(hours=8), name="AWST"))


def _col_letter(n: int) -> str:
    s = ""
    while n > 0:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return s


def _updated_column_index(header: list[Any]) -> int:
    for i, cell in enumerate(header):
        if str(cell).strip().lower() == "updated":
            return i
    return len(header) - 1


def _stamp_updated_column(padded: list[list[Any]], when: datetime) -> None:
    if len(padded) < 2:
        return
    stamp = when.strftime("%d/%m/%Y %H:%M:%S")
    col = _updated_column_index(padded[0])
    for row in padded[1:]:
        if col < len(row):
            row[col] = stamp


def _quote_sheet_tab(title: str) -> str:
    return "'" + title.replace("'", "''") + "'"


def _resolve_remote_tab(remote_titles: list[str], logical_tab: str) -> str:
    aliases = TAB_ALIASES.get(logical_tab, (_norm_key(logical_tab),))
    normalized_remote = {t: _norm_key(t) for t in remote_titles}
    for alias in aliases:
        for title, nk in normalized_remote.items():
            if nk == alias:
                return title
    for title, nk in normalized_remote.items():
        if _norm_key(logical_tab) == nk:
            return title
    raise ValueError(
        f"No Google Sheet tab matched {logical_tab!r}. "
        f"Remote tabs: {remote_titles}. Extend TAB_ALIASES if needed."
    )


def _default_creds_path() -> Path:
    env = os.getenv("GOOGLE_CREDENTIALS_PATH")
    if env:
        return Path(env).expanduser()
    return BACKEND_DIR / "data" / "google_credentials.json"


def _suppress_google_client_python_eol_warnings() -> None:
    warnings.filterwarnings(
        "ignore",
        category=FutureWarning,
        module=r"google\.(oauth2|auth|api_core)",
    )


def department_sheet_label(branch_name: str, company_id: int, dept_name: str) -> str:
    if branch_name == "Bunbury" and company_id == 2 and dept_name == "Residential":
        return "Bunbury Electrical"
    return f"{branch_name} {dept_name}"


def _round_gp_pct(revenue: float, gross_profit: float, margin: float | None) -> float:
    if margin is not None:
        return round(float(margin), 1)
    if revenue:
        return round((gross_profit / revenue) * 100, 1)
    return 0.0


def build_tables_from_xero(
    from_date: date,
    to_date: date,
    *,
    settings: Any,
    xero_service: Any,
    is_xero_enabled: Any,
) -> tuple[list[list[Any]], list[list[Any]]]:
    branch_header = ["Branch", "Total", "GP Cash Total", "GP Percentage", "Updated"]
    dept_header = ["Department", "Total", "GP Cash Total", "GP Percentage", "Updated"]
    branch_rows: list[list[Any]] = [branch_header]
    dept_rows: list[list[Any]] = [dept_header]

    for branch_id, branch_name in BRANCH_ORDER:
        if not is_xero_enabled(branch_id):
            print(f"Warning: Xero disabled or unconfigured for {branch_id} ({branch_name}); skipping.", file=sys.stderr)
            continue
        metrics = xero_service.get_business_group_gp_metrics_for_date_range(
            from_date, to_date, branch_id
        )
        if not metrics:
            print(
                f"Warning: no GP metrics returned for {branch_name} ({branch_id}) "
                f"for {from_date}–{to_date}.",
                file=sys.stderr,
            )

        total_rev = 0
        total_gp = 0
        branch_cfg = settings.BRANCHES.get(branch_id, {})
        companies: dict[int, Any] = branch_cfg.get("companies", {})

        for company_id in sorted(companies.keys()):
            m = metrics.get(company_id) or {}
            rev = _money_whole(m.get("total_revenue"))
            gp = _money_whole(m.get("gross_profit"))
            pct = _round_gp_pct(float(rev), float(gp), None)
            dept_name = companies[company_id].get("name", str(company_id))
            label = department_sheet_label(branch_name, company_id, dept_name)
            dept_rows.append([label, rev, gp, pct, ""])
            total_rev += rev
            total_gp += gp

        branch_pct = round((total_gp / total_rev) * 100, 1) if total_rev else 0.0
        branch_rows.append([branch_name, total_rev, total_gp, branch_pct, ""])

    if len(branch_rows) < 2:
        raise RuntimeError("No branch data to sync. Enable Xero for at least one branch and check .env.")
    if len(dept_rows) < 2:
        raise RuntimeError("No department rows built. Check Xero tracking / company config.")

    return branch_rows, dept_rows


def _to_padded(rows: list[list[Any]]) -> tuple[int, int, list[list[Any]]]:
    nrows = len(rows)
    ncols = max(len(r) for r in rows)
    padded = []
    for r in rows:
        padded.append([(r[i] if i < len(r) else "") for i in range(ncols)])
    return nrows, ncols, padded


def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument(
        "--spreadsheet-id",
        default=os.getenv("GOOGLE_DAILY_FIGURES_SHEETS_ID", DEFAULT_SPREADSHEET_ID),
        help="Target spreadsheet ID",
    )
    parser.add_argument(
        "--period",
        choices=("fytd", "mtd"),
        default="fytd",
        help="Date window from Xero: fytd = Australian FY start through today; mtd = month start through today",
    )
    parser.add_argument("--from", dest="from_date", default=None, metavar="YYYY-MM-DD", help="Override range start")
    parser.add_argument("--to", dest="to_date", default=None, metavar="YYYY-MM-DD", help="Override range end (inclusive)")
    parser.add_argument(
        "--dry-run",
        action="store_true",
        help="Fetch Xero and print payload; do not update Google Sheets",
    )
    args = parser.parse_args()

    _apply_dotenv_files()

    today = date.today()
    if args.from_date or args.to_date:
        if not args.from_date or not args.to_date:
            print("Both --from and --to are required when overriding the range.", file=sys.stderr)
            return 1
        try:
            from_date = date.fromisoformat(args.from_date)
            to_date = date.fromisoformat(args.to_date)
        except ValueError as e:
            print(f"Invalid date: {e}", file=sys.stderr)
            return 1
        if from_date > to_date:
            print("--from must be on or before --to.", file=sys.stderr)
            return 1
    elif args.period == "mtd":
        from_date = date(today.year, today.month, 1)
        to_date = today
    else:
        from_date = _start_of_fy_au(today)
        to_date = today

    period_label = "custom" if (args.from_date or args.to_date) else args.period

    _bootstrap_imports()
    logging.basicConfig(level=logging.WARNING)

    try:
        from app.config import settings
        from app.config.xero_config import is_xero_enabled
        from app.services.xero_service import xero_service
    except ImportError as e:
        print(
            "Could not import backend (app.*). Install backend deps and run this file from a checkout "
            f"where backend/app exists (BACKEND_DIR={BACKEND_DIR}):\n"
            f"  pip install -r backend/requirements.txt\n{e}",
            file=sys.stderr,
        )
        return 1

    synced_at = _now_perth()
    try:
        branch_rows, dept_rows = build_tables_from_xero(
            from_date, to_date, settings=settings, xero_service=xero_service, is_xero_enabled=is_xero_enabled
        )
    except ValueError as e:
        print(f"Xero authentication/configuration error: {e}", file=sys.stderr)
        return 1
    except RuntimeError as e:
        print(str(e), file=sys.stderr)
        return 1

    prepared: list[tuple[str, int, int, list[list[Any]]]] = []
    for logical_tab, rows in (("By Branch", branch_rows), ("By Department", dept_rows)):
        nrows, ncols, padded = _to_padded(rows)
        _stamp_updated_column(padded, synced_at)
        prepared.append((logical_tab, nrows, ncols, padded))

    spreadsheet_id = (args.spreadsheet_id or "").strip()
    if not spreadsheet_id:
        print("Spreadsheet ID empty. Set GOOGLE_DAILY_FIGURES_SHEETS_ID or pass --spreadsheet-id.", file=sys.stderr)
        return 1

    if args.dry_run:
        print("Dry run — Xero fetched; no Google Sheets API calls.")
        print(f"Backend dir: {BACKEND_DIR}")
        print(f"Xero range: {from_date.isoformat()} → {to_date.isoformat()} ({period_label})")
        print(f"Spreadsheet ID would be: {spreadsheet_id}")
        print(f"Updated column stamped at (Perth AWST): {synced_at.strftime('%d/%m/%Y %H:%M:%S')}")
        for logical_tab, nrows, ncols, padded in prepared:
            col_letter_end = _col_letter(ncols)
            print(
                f"  {logical_tab}: {nrows} rows × {ncols} cols → "
                f"{_quote_sheet_tab(logical_tab)}!A1:{col_letter_end}{nrows}"
            )
            for i, row in enumerate(padded, start=1):
                print(f"    {i}: {row}")
        print("Google tab titles are matched case-insensitively (e.g. «by branch» ↔ «By Branch»).")
        return 0

    _suppress_google_client_python_eol_warnings()
    try:
        from google.oauth2 import service_account
        from googleapiclient.discovery import build
    except ImportError:
        print("pip install google-api-python-client google-auth", file=sys.stderr)
        return 1

    creds_path = _default_creds_path()
    if not creds_path.is_file():
        print(f"Credentials not found: {creds_path}", file=sys.stderr)
        return 1

    scopes = ["https://www.googleapis.com/auth/spreadsheets"]
    credentials = service_account.Credentials.from_service_account_file(
        str(creds_path.resolve()),
        scopes=scopes,
    )
    service = build("sheets", "v4", credentials=credentials, cache_discovery=False)

    meta = (
        service.spreadsheets()
        .get(spreadsheetId=spreadsheet_id, fields="sheets(properties(title))")
        .execute()
    )
    remote_titles = [s["properties"]["title"] for s in meta.get("sheets", [])]

    clear_ranges: list[str] = []
    value_updates: list[dict[str, Any]] = []

    for logical_tab, nrows, ncols, padded in prepared:
        remote_tab = _resolve_remote_tab(remote_titles, logical_tab)
        col_letter_end = _col_letter(ncols)
        a1 = f"{_quote_sheet_tab(remote_tab)}!A1:{col_letter_end}{nrows}"
        value_updates.append({"range": a1, "values": padded})
        clear_ranges.append(f"{_quote_sheet_tab(remote_tab)}!A{nrows + 1}:{col_letter_end}1000")

    service.spreadsheets().values().batchClear(
        spreadsheetId=spreadsheet_id,
        body={"ranges": clear_ranges},
    ).execute()

    service.spreadsheets().values().batchUpdate(
        spreadsheetId=spreadsheet_id,
        body={"valueInputOption": "USER_ENTERED", "data": value_updates},
    ).execute()

    print(f"Updated {len(value_updates)} tab(s) in spreadsheet {spreadsheet_id}.")
    print(f"Xero range: {from_date.isoformat()} → {to_date.isoformat()} ({period_label})")
    print(f"Updated column set to (Perth AWST): {synced_at.strftime('%d/%m/%Y %H:%M:%S')}")
    for u in value_updates:
        print(" ", u["range"])
    return 0


if __name__ == "__main__":
    raise SystemExit(main())