"""
Work in Progress (WIP) jobs from the Simpro REST API only (no CSV fallback for live dashboards).

WIP value = sum of |Difference| for negative-Difference jobs, excluding stock orders.
Results are cached in-memory (see simpro_api_limits.SIMPRO_API_CACHE_TTL_SECONDS) to avoid hammering Simpro.

CSV helpers below remain for offline analysis or one-off imports; application routes do not use them.
"""
import asyncio
import csv
import logging
import time
from pathlib import Path
from typing import Any, Dict, Optional, Tuple

from app.config import settings
from app.services.simpro_api_limits import SIMPRO_API_CACHE_TTL_SECONDS

logger = logging.getLogger(__name__)

BRANCH_ID_TO_NAME = {"branch1": "Busselton", "branch2": "Bunbury", "branch3": "Mandurah"}

_wip_cache: Dict[str, Tuple[float, Any]] = {}  # branch_id -> (timestamp, data)

_EMPTY_WIP: Dict[str, Any] = {"job_count": 0, "wip_value": 0.0, "departments": {}}

DIFFERENCE_COLUMN_NAMES = ["Difference"]


def _parse_currency(value: str) -> float:
    """Parse currency string like '$1,234.56' or '($123.45)' to float."""
    if not value or not isinstance(value, str):
        return 0.0
    cleaned = value.strip().replace("$", "").replace(",", "").strip()
    if not cleaned:
        return 0.0
    try:
        if cleaned.startswith("(") and cleaned.endswith(")"):
            cleaned = "-" + cleaned[1:-1]
        return float(cleaned)
    except (ValueError, TypeError):
        return 0.0


def _is_stock_order(row: dict) -> bool:
    site = (row.get("Site") or "").lower()
    name = (row.get("Job Name") or "").lower()
    return "stock order" in site or "stock order" in name


def load_wip_jobs_by_branch() -> Dict[str, dict]:
    """Legacy: load WIP from branch CSV exports under data/ (not used by live API routes)."""
    data_dir = Path(__file__).resolve().parent.parent.parent.parent / "data"
    branch_files = {
        "Bunbury": "wip-jobs-bunbury.csv",
        "Busselton": "wip-jobs-busselton.csv",
        "Mandurah": "wip-jobs-mandurah.csv",
    }
    result = {}
    for branch_name, filename in branch_files.items():
        csv_path = data_dir / filename
        job_count = 0
        wip_value = 0.0
        departments: Dict[str, dict] = {}
        try:
            if csv_path.exists():
                with open(csv_path, encoding="utf-8") as f:
                    for _ in range(3):
                        next(f)
                    reader = csv.DictReader(f)
                    diff_col = None
                    for row in reader:
                        company = (row.get("Company Name") or "").strip()
                        if company == "Total" or not company:
                            continue
                        if _is_stock_order(row):
                            continue
                        if diff_col is None:
                            for col in DIFFERENCE_COLUMN_NAMES:
                                if col in row:
                                    diff_col = col
                                    break
                        if not diff_col or diff_col not in row:
                            continue
                        difference = _parse_currency(row[diff_col])
                        if difference >= 0:
                            continue
                        abs_diff = abs(difference)
                        job_count += 1
                        wip_value += abs_diff
                        dept = departments.setdefault(company, {"job_count": 0, "wip_value": 0.0})
                        dept["job_count"] += 1
                        dept["wip_value"] += abs_diff
        except Exception:
            pass
        for dept in departments.values():
            dept["wip_value"] = round(dept["wip_value"], 2)
        result[branch_name] = {
            "job_count": job_count,
            "wip_value": round(wip_value, 2),
            "departments": departments,
        }
    return result


def load_wip_jobs_for_branch(branch_name: str) -> Optional[dict]:
    """Legacy: single branch from CSV map (not used by live API routes)."""
    all_data = load_wip_jobs_by_branch()
    return all_data.get(branch_name)


async def get_wip_jobs_for_branch(branch_id: str, force_refresh: bool = False) -> dict:
    """
    Get WIP jobs for a branch from Simpro only. Returns cached data while within SIMPRO_API_CACHE_TTL_SECONDS.
    On API failure or missing config, returns zeros (no CSV fallback).
    Returns { job_count: int, wip_value: float, departments: {...} }.
    """
    now = time.time()

    if not force_refresh and branch_id in _wip_cache:
        cached_at, cached_data = _wip_cache[branch_id]
        age = now - cached_at
        if age < SIMPRO_API_CACHE_TTL_SECONDS:
            logger.debug("WIP cache HIT for %s (age %.0fs)", branch_id, age)
            return cached_data

    from asgiref.sync import sync_to_async
    from app.services.simpro_client import SimProClient

    result = {}
    if branch_id in BRANCH_ID_TO_NAME:
        try:
            # SimProClient.__init__ reads settings.BRANCHES (Django ORM); must not run on the asyncio loop.
            client = await sync_to_async(SimProClient)(branch_id)
            result = await client.fetch_wip_jobs_for_branch()
        except Exception as e:
            logger.warning("Simpro WIP fetch failed for %s: %s", branch_id, e)

    if not result:
        result = dict(_EMPTY_WIP)

    if branch_id in BRANCH_ID_TO_NAME:
        _wip_cache[branch_id] = (now, result)
        logger.info(
            "WIP cache SET for %s (%d jobs, $%s)",
            branch_id,
            result.get("job_count", 0),
            f"{result.get('wip_value', 0):,.2f}",
        )

    return result


async def get_wip_jobs_all_branches() -> Dict[str, dict]:
    """Live Simpro WIP for Bunbury, Busselton, Mandurah (same keys as legacy directors CSV map)."""
    order = [
        ("branch2", "Bunbury"),
        ("branch1", "Busselton"),
        ("branch3", "Mandurah"),
    ]
    tasks = []
    names: list[str] = []
    for bid, bname in order:
        if bid not in settings.BRANCHES:
            continue
        names.append(bname)
        tasks.append(get_wip_jobs_for_branch(bid))

    if not tasks:
        return {}

    results = await asyncio.gather(*tasks, return_exceptions=True)
    out: Dict[str, dict] = {}
    for bname, res in zip(names, results):
        if isinstance(res, Exception):
            logger.warning("WIP fetch failed for %s: %s", bname, res)
            out[bname] = dict(_EMPTY_WIP)
        else:
            out[bname] = res
    return out


def invalidate_wip_cache(branch_id: Optional[str] = None) -> None:
    """Clear cached WIP data. If branch_id is None, clears all branches."""
    if branch_id:
        _wip_cache.pop(branch_id, None)
        logger.info("WIP cache invalidated for %s", branch_id)
    else:
        _wip_cache.clear()
        logger.info("WIP cache cleared (all branches)")