foodlinkk-command-center/cockpit/app/services/excel_import.py

"""Parse Succes Sheet revenue tab — runs in cockpit with NAS mount."""
from __future__ import annotations

import os
import re
from datetime import datetime, timezone
from pathlib import Path
from typing import Any


NAS_SHARE_ROOT = Path(os.getenv("NAS_SHARE_ROOT", "/data/nas-share"))
DEFAULT_FILE = os.getenv("CEO_EXCEL_PATH", "Succes Sheet .xlsx")
DEFAULT_SHEET = os.getenv("CEO_EXCEL_SHEET", "Projects next steps revenue")

_FILL_STYLE = {
    "FF92D050": "green",
    "FFFF0000": "red",
    "FFFFC000": "orange",
    "FFFFFF00": "yellow",
    "FFBDD7EE": "blue",
    "FFDDEBF7": "blue",
    "FF0070C0": "blue",
}


def _num(val: Any) -> float | None:
    if val is None or val == "":
        return None
    try:
        return float(val)
    except (TypeError, ValueError):
        return None


def _txt(val: Any) -> str:
    if val is None:
        return ""
    return str(val).strip()


def _cell_fill_style(cell) -> str:
    try:
        if not cell or not cell.fill or cell.fill.fill_type != "solid":
            return "white"
        rgb = cell.fill.fgColor.rgb if cell.fill.fgColor else None
        if not rgb or rgb in ("00000000", "FFFFFFFF", "00FFFFFF"):
            return "white"
        key = rgb[-8:].upper() if len(rgb) >= 8 else rgb.upper()
        if key in _FILL_STYLE:
            return _FILL_STYLE[key]
        short = key[-6:]
        for k, v in _FILL_STYLE.items():
            if k.endswith(short):
                return v
        return "white"
    except Exception:
        return "white"


def parse_revenue_sheet(
    rel_path: str = DEFAULT_FILE,
    sheet_name: str | None = DEFAULT_SHEET,
    nas_root: Path | None = None,
) -> dict[str, Any]:
    try:
        from openpyxl import load_workbook
    except ImportError as exc:
        return {"ok": False, "error": f"openpyxl not installed: {exc}"}

    root = nas_root or NAS_SHARE_ROOT
    full = root / rel_path.lstrip("/")
    if not full.is_file():
        return {"ok": False, "error": f"File not found: {full}"}

    st = full.stat()
    wb = load_workbook(full, read_only=False, data_only=True)
    names = wb.sheetnames
    target_name = sheet_name
    if not target_name:
        for n in names:
            if "project" in n.lower() and "revenue" in n.lower():
                target_name = n
                break
    if not target_name and len(names) > 2:
        target_name = names[2]
    if not target_name:
        return {"ok": False, "error": "Sheet not found", "sheetnames": names}

    ws = wb[target_name]
    rows = list(ws.iter_rows(values_only=False))

    goals: dict[str, str] = {"vision_text": "", "horizon_text": "", "mid_text": "", "tagline": ""}
    if rows:
        r0 = rows[0]
        goals["vision_text"] = _txt(r0[0].value if len(r0) > 0 else "")
        goals["horizon_text"] = _txt(r0[1].value if len(r0) > 1 else "")
        goals["mid_text"] = _txt(r0[2].value if len(r0) > 2 else "")
        goals["tagline"] = _txt(r0[4].value if len(r0) > 4 else (_txt(r0[3].value if len(r0) > 3 else "")))

    projects: list[dict[str, Any]] = []
    sort_order = 0
    for idx, row in enumerate(rows):
        if idx <= 1:
            continue
        cells = list(row) if row else []
        name = _txt(cells[1].value if len(cells) > 1 else "")
        if not name:
            continue
        margin_month = _num(cells[2].value if len(cells) > 2 else None)
        margin_year = _num(cells[3].value if len(cells) > 3 else None)
        next_steps = _txt(cells[4].value if len(cells) > 4 else "")
        target_extra = _num(cells[5].value if len(cells) > 5 else None)
        row_style = _cell_fill_style(cells[1] if len(cells) > 1 else None)
        category = "deal" if margin_month is not None or margin_year is not None else "initiative"
        if row_style == "yellow" or re.search(r"foodlinkk|linknbit|subsid|total earnings|loonkosten", name, re.I):
            category = "strategic"
        projects.append(
            {
                "name": name,
                "category": category,
                "margin_month": margin_month,
                "margin_year": margin_year,
                "target_revenue": target_extra or margin_year,
                "next_steps": next_steps,
                "status": "active",
                "sort_order": sort_order,
                "source_row": idx,
                "row_style": row_style,
            }
        )
        sort_order += 1

    wb.close()
    return {
        "ok": True,
        "source_file": rel_path,
        "sheet_name": target_name,
        "sheetnames": names,
        "file_mtime": datetime.fromtimestamp(st.st_mtime, tz=timezone.utc).isoformat(),
        "file_size": st.st_size,
        "goals": goals,
        "projects": projects,
        "parsed_at": datetime.now(timezone.utc).isoformat(),
        "project_count": len(projects),
    }