app.py

"""NetCom → WooCommerce transformer (Try 3 schema — meta‑days calc, sorted attributes, deduped AI sections, persistent cache, 100‑parallel,
duplicate‑safe, relative‑logo paths, cache‑preload)
==============================================================================

Accept a NetCom schedule (CSV/XLSX) and **optionally** a *previous* WooCommerce
CSV; output the fresh WooCommerce CSV.

New in this revision
--------------------
* **Meta days** automatically calculated as the inclusive span (in days) between
  the earliest and latest course dates for each Course ID.
* **Attribute 1 (Date)** lists are now guaranteed to be sorted chronologically.
* All AI‑generated sections (descriptions, objectives, agenda, prerequisites)
  are post‑processed to **deduplicate any repeated lines** inside each section.
* Everything else (persistent cache in `/data`, 100‑parallel semaphore,
  in‑flight de‑duplication, pandas compatibility fix) remains unchanged.
"""

from __future__ import annotations

import asyncio
import hashlib
import json
import os
import tempfile
from io import BytesIO
from pathlib import Path
from typing import List

import gradio as gr
import gradio_client.utils
import openai
import pandas as pd

# ── Gradio bool‑schema hot‑patch ────────────────────────────────────────────
_original = gradio_client.utils._json_schema_to_python_type

def _fixed_json_schema_to_python_type(schema, defs=None):  # type: ignore
    if isinstance(schema, bool):
        return "any"
    return _original(schema, defs)

gradio_client.utils._json_schema_to_python_type = _fixed_json_schema_to_python_type  # type: ignore

# ── Persistent disk cache (HF Spaces uses /data) ────────────────────────────
_PERSISTENT_ROOT = Path("/data")
CACHE_DIR = (_PERSISTENT_ROOT if _PERSISTENT_ROOT.exists() else Path(".")) / "ai_response_cache"
CACHE_DIR.mkdir(parents=True, exist_ok=True)


def _cache_path(p: str) -> Path:
    return CACHE_DIR / f"{hashlib.md5(p.encode()).hexdigest()}.json"


def _get_cached(p: str) -> str | None:
    try:
        return json.loads(_cache_path(p).read_text("utf-8"))["response"]
    except Exception:
        return None


def _set_cache(p: str, r: str) -> None:
    try:
        _cache_path(p).write_text(json.dumps({"prompt": p, "response": r}), "utf-8")
    except Exception:
        pass

# ── Helpers ────────────────────────────────────────────────────────────────

def _dedup_lines(txt: str) -> str:
    """Remove duplicated lines while preserving order inside a block of text."""
    seen = set()
    out: List[str] = []
    for raw in txt.splitlines():
        line = raw.rstrip()
        if line and line not in seen:
            out.append(line)
            seen.add(line)
    return "\n".join(out)

# ── OpenAI helpers: 100‑parallel + de‑dup ───────────────────────────────────
_SEM = asyncio.Semaphore(100)              # ≤100 concurrent OpenAI calls
_inflight: dict[str, asyncio.Future] = {}  # prompt → Future


async def _gpt_async(client: openai.AsyncOpenAI, prompt: str) -> str:
    cached = _get_cached(prompt)
    if cached is not None:
        return cached

    running = _inflight.get(prompt)
    if running is not None:
        return await running

    loop = asyncio.get_running_loop()

    async def _call_api() -> str:
        async with _SEM:
            try:
                msg = await client.chat.completions.create(
                    model="gpt-4o-mini",
                    messages=[{"role": "user", "content": prompt}],
                    temperature=0,
                )
                text = msg.choices[0].message.content
            except Exception as exc:
                text = f"Error: {exc}"
            _set_cache(prompt, text)
            return text

    task = loop.create_task(_call_api())
    _inflight[prompt] = task
    try:
        return await task
    finally:
        _inflight.pop(prompt, None)


async def _batch_async(lst, instruction: str, client):
    out = ["" for _ in lst]
    idx, prompts = [], []
    for i, txt in enumerate(lst):
        if isinstance(txt, str) and txt.strip():
            idx.append(i)
            prompts.append(f"{instruction}\n\nText: {txt}")
    if not prompts:
        return out

    responses = await asyncio.gather(*[_gpt_async(client, p) for p in prompts])
    for j, val in enumerate(responses):
        out[idx[j]] = _dedup_lines(val)
    return out

# ── Instructions (reuse across preload & gen) ───────────────────────────────
DESC_SHORT = "Create a concise 250-character summary of this course description:"
DESC_LONG  = "Condense this description to a maximum of 750 characters in paragraph format, with clean formatting:"
OBJECTIVES = "Format these objectives into a bullet list with clean formatting. Start each bullet with '• ':"
AGENDA     = "Format this agenda into a bullet list with clean formatting. Start each bullet with '• ':"
PREREQ     = "Format these prerequisites into a bullet list with clean formatting. Start each bullet with '• ':"

# ── Logo map (relative paths, with common aliases) ──────────────────────────
logos = {
    "Amazon Web Services": "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/aws.png",
    "AWS":                 "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/aws.png",
    "Cisco":               "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/cisco-e1738593292198-1.webp",
    "Microsoft":           "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/Microsoft-e1737494120985-1.png",
    "Google Cloud":        "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/Google_Cloud.png",
    "EC Council":          "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/Ec_Council.png",
    "ITIL":                "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/ITIL.webp",
    "PMI":                 "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/PMI.png",
    "Comptia":             "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/Comptia.png",
    "Autodesk":            "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/autodesk.png",
    "ISC2":                "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/ISC2.png",
    "AICerts":             "https://staging.greathorizonslearning.com/wp-content/uploads/2025/04/aicerts-logo-1.png",
}

DEFAULT_PREREQ = (
    "No specific prerequisites are required for this course. Basic computer literacy and "
    "familiarity with fundamental concepts in the subject area are recommended for the best "
    "learning experience."
)

# ── Cache‑preload from previous WooCommerce CSV ─────────────────────────────

def _preload_cache(prev_csv: str, df_new: pd.DataFrame, dcol, ocol, pcol, acol):
    """Seed the on‑disk cache with completions from an earlier WooCommerce CSV."""
    try:
        prev = pd.read_csv(prev_csv, encoding="utf-8-sig")
    except Exception:
        return

    prev_parent = prev[prev["Type"].str.startswith("variable", na=False)]
    prev_map = {row["SKU"]: row for _, row in prev_parent.iterrows()}  # SKU == Course ID

    for _, row in df_new.iterrows():
        cid = row["Course ID"]
        if cid not in prev_map:
            continue
        old = prev_map[cid]

        desc = str(row[dcol])
        obj  = str(row[ocol])
        ag   = str(row[acol])
        pre  = str(row[pcol])

        _set_cache(f"{DESC_SHORT}\n\nText: {desc}", _dedup_lines(old.get("Short description", "")))
        _set_cache(f"{DESC_LONG}\n\nText: {desc}",  _dedup_lines(old.get("Description", "")))
        _set_cache(f"{OBJECTIVES}\n\nText: {obj}",    _dedup_lines(old.get("Meta: objectives", "")))
        _set_cache(f"{AGENDA}\n\nText: {ag}",         _dedup_lines(old.get("Meta: agenda", "")))
        if pre.strip():
            _set_cache(f"{PREREQ}\n\nText: {pre}",    _dedup_lines(old.get("Meta: prerequisites", "")))

# ── Helper: read user file (CSV or Excel) ───────────────────────────────────

def _read(path: str) -> pd.DataFrame:
    if path.lower().endswith((".xlsx", ".xls")):
        return pd.read_excel(path)
    return pd.read_csv(path, encoding="latin1")

# ── Enrichment step (async batched LLM) ──────────────────────────────────────
async def _enrich_dataframe(df, dcol, ocol, pcol, acol):
    async with openai.AsyncOpenAI(api_key=os.getenv("OPENAI_API_KEY")) as client:
        sdesc, ldesc, fobj, fout = await asyncio.gather(
            _batch_async(df.get(dcol, "").fillna("").tolist(), DESC_SHORT, client),
            _batch_async(df.get(dcol, "").fillna("").tolist(), DESC_LONG,  client),
            _batch_async(df.get(ocol, "").fillna("").tolist(), OBJECTIVES, client),
            _batch_async(df.get(acol, "").fillna("").tolist(), AGENDA,     client),
        )

        prereq_raw = df.get(pcol, "").fillna("").tolist()
        fpre = []
        for req in prereq_raw:
            if not str(req).strip():
                fpre.append(DEFAULT_PREREQ)
            else:
                out = await _batch_async([req], PREREQ, client)
                fpre.append(out[0])

    # Ensure everything is deduped (safety).
    sdesc = [_dedup_lines(t) for t in sdesc]
    ldesc = [_dedup_lines(t) for t in ldesc]
    fobj  = [_dedup_lines(t) for t in fobj]
    fout  = [_dedup_lines(t) for t in fout]
    fpre  = [_dedup_lines(t) for t in fpre]

    return sdesc, ldesc, fobj, fout, fpre

# ── Main converter ──────────────────────────────────────────────────────────

def convert(schedule_path: str, prev_csv_path: str | None = None) -> BytesIO:
    df = _read(schedule_path)
    df.columns = df.columns.str.strip()

    first_col = lambda *cand: next((c for c in cand if c in df.columns), None)
    dcol = first_col("Description", "Decription")
    ocol = first_col("Objectives", "objectives")
    pcol = first_col("RequiredPrerequisite", "Required Pre-requisite")
    acol = first_col("Outline")
    dur  = first_col("Duration") or "Duration"  # kept for backward‑compat (unused)
    sid  = first_col("Course SID", "Course SID")

    # optional cache preload
    if prev_csv_path:
        _preload_cache(prev_csv_path, df, dcol, ocol, pcol, acol)

    # async‑enrich via LLM
    sdesc, ldesc, fobj, fout, fpre = asyncio.run(
        _enrich_dataframe(df, dcol, ocol, pcol, acol)
    )

    df["Short_Description"]       = sdesc
    df["Condensed_Description"]   = ldesc
    df["Formatted_Objectives"]    = fobj
    df["Formatted_Agenda"]        = fout
    df["Formatted_Prerequisites"] = fpre

    # schedule aggregation & meta‑days calculation
    df["Course Start Date"] = pd.to_datetime(df["Course Start Date"], errors="coerce")
    df["Date_fmt"] = df["Course Start Date"].dt.strftime("%-m/%-d/%Y")

    dsorted = df.sort_values(["Course ID", "Course Start Date"])

    # "MetaDays" = inclusive span between earliest & latest dates per Course ID
    meta_days = (
        dsorted.groupby("Course ID")["Course Start Date"].agg(lambda s: (s.max() - s.min()).days + 1)
        .reset_index(name="MetaDays")
    )

    # Attribute 1 list — ensure chronological order
    d_agg = (
        dsorted.groupby("Course ID")["Date_fmt"]
        .apply(lambda s: ",".join(sorted(s.dropna().unique(), key=lambda x: pd.to_datetime(x))))
        .reset_index(name="Dates")
    )

    t_agg = (
        dsorted.groupby("Course ID", group_keys=False)
        .apply(
            lambda g: ",".join(
                f"{st}-{et} {tz}"
                for st, et, tz in zip(
                    g["Course Start Time"], g["Course End Time"], g["Time Zone"]
                )
            ),
            include_groups=False,
        )
        .reset_index(name="Times")
    )

    parents = (
        dsorted.drop_duplicates("Course ID")
        .merge(d_agg)
        .merge(t_agg)
        .merge(meta_days)
    )

    # propagate MetaDays to each schedule row
    dsorted = dsorted.merge(meta_days, on="Course ID", how="left")

    parent = pd.DataFrame(
        {
            "Type": "variable",
            "SKU": parents["Course ID"],
            "Name": parents["Course Name"],
            "Published": 1,
            "Visibility in catalog": "visible",
            "Short description": parents["Short_Description"],
            "Description": parents["Condensed_Description"],
            "Tax status": "taxable",
            "In stock?": 1,
            "Stock": 1,
            "Sold individually?": 1,
            "Regular price": parents["SRP Pricing"].replace("[\\$,]", "", regex=True),
            "Categories": "courses",
            "Images": parents["Vendor"].map(logos).fillna(""),
            "Parent": "",
            "Brands": parents["Vendor"],
            "Attribute 1 name": "Date",
            "Attribute 1 value(s)": parents["Dates"],
            "Attribute 1 visible": "visible",
            "Attribute 1 global": 1,
            "Attribute 2 name": "Location",
            "Attribute 2 value(s)": "Virtual",
            "Attribute 2 visible": "visible",
            "Attribute 2 global": 1,
            "Attribute 3 name": "Time",
            "Attribute 3 value(s)": parents["Times"],
            "Attribute 3 visible": "visible",
            "Attribute 3 global": 1,
            "Meta: outline": parents["Formatted_Agenda"],
            "Meta: days": parents["MetaDays"],
            "Meta: location": "Virtual",
            "Meta: overview": parents["Target Audience"],
            "Meta: objectives": parents["Formatted_Objectives"],
            "Meta: prerequisites": parents["Formatted_Prerequisites"],
            "Meta: agenda": parents["Formatted_Agenda"],
        }
    )

    child = pd.DataFrame(
        {
            "Type": "variation, virtual",
            "SKU": dsorted[sid].astype(str).str.strip(),
            "Name": dsorted["Course Name"],
            "Published": 1,
            "Visibility in catalog": "visible",
            "Short description": dsorted["Short_Description"],
            "Description": dsorted["Condensed_Description"],
            "Tax status": "taxable",
            "In stock?": 1,
            "Stock": 1,
            "Sold individually?": 1,
            "Regular price": dsorted["SRP Pricing"].replace("[\\$,]", "", regex=True),
            "Categories": "courses",
            "Images": dsorted["Vendor"].map(logos).fillna(""),
            "Parent": dsorted["Course ID"],
            "Brands": dsorted["Vendor"],
            "Attribute 1 name": "Date",
            "Attribute 1 value(s)": dsorted["Date_fmt"],
            "Attribute 1 visible": "visible",
            "Attribute 1 global": 1,
            "Attribute 2 name": "Location",
            "Attribute 2 value(s)": "Virtual",
            "Attribute 2 visible": "visible",
            "Attribute 2 global": 1,
            "Attribute 3 name": "Time",
            "Attribute 3 value(s)": dsorted.apply(
                lambda r: f"{r['Course Start Time']}-{r['Course End Time']} {r['Time Zone']}",
                axis=1,
            ),
            "Attribute 3 visible": "visible",
            "Attribute 3 global": 1,
            "Meta: outline": dsorted["Formatted_Agenda"],
            "Meta: days": dsorted["MetaDays"],
            "Meta: location": "Virtual",
            "Meta: overview": dsorted["Target Audience"],
            "Meta: objectives": dsorted["Formatted_Objectives"],
            "Meta: prerequisites": dsorted["Formatted_Prerequisites"],
            "Meta: agenda": dsorted["Formatted_Agenda"],
        }
    )

    all_rows = pd.concat([parent, child], ignore_index=True)
    order = [
        "Type","SKU","Name","Published","Visibility in catalog","Short description","Description",
        "Tax status","In stock?","Stock","Sold individually?","Regular price","Categories","Images",
        "Parent","Brands","Attribute 1 name","Attribute 1 value(s)","Attribute 1 visible","Attribute 1 global",
        "Attribute 2 name","Attribute 2 value(s)","Attribute 2 visible","Attribute 2 global",
        "Attribute 3 name","Attribute 3 value(s)","Attribute 3 visible","Attribute 3 global",
        "Meta: outline","Meta: days","Meta: location","Meta: overview","Meta: objectives",
        "Meta: prerequisites","Meta: agenda",
    ]
    out = BytesIO()
    all_rows[order].to_csv(out, index=False, encoding="utf-8-sig")
    out.seek(0)
    return out

# ── Gradio interface ────────────────────────────────────────────────────────

def process_files(schedule: gr.File, previous: gr.File | None) -> str:
    csv_bytes = convert(schedule.name, previous.name if previous else None)
    with tempfile.NamedTemporaryFile(delete=False, suffix=".csv") as tmp:
        tmp.write(csv_bytes.getvalue())
        return tmp.name

ui = gr.Interface(
    fn=process_files,
    inputs=[
        gr.File(label="Upload NetCom schedule (.csv/.xlsx/.xls)", file_types=[".csv", ".xlsx", ".xls"]),
        gr.File(label="Previous WooCommerce CSV (optional)", file_types=[".csv"]),
    ],
    outputs=gr.File(label="Download WooCommerce CSV"),
    title="NetCom → WooCommerce CSV Processor (Try 3)",
    description=(
        "1. Upload the **latest NetCom schedule** file.\n"
        "2. *(Optional)* Upload the **WooCommerce CSV** generated by a previous run to "
        "pre-load the cache and skip already-processed courses."
    ),
    analytics_enabled=False,
)

if __name__ == "__main__":
    if not os.getenv("OPENAI_API_KEY"):
        print("⚠️  OPENAI_API_KEY not set – AI features will error")
    ui.launch()