"""Phase 2 — deterministically generate ~100 core skill packages (no LLM).

Selection: knowledge-work occupations (management, business, IT, HR, finance,
marketing, support) = curated ISCO-08 prefixes, restricted to ESCO occupations
that have an O*NET crosswalk match (so every package gets tasks + tools).
The recruiter ("recruitment consultant", O*NET 13-1071) is always included.

Output per occupation: skills/<slug>/SKILL.md + references/ + manifest.json
Everything is regenerated from MSSQL on each run (idempotent).
"""
import json
import os
import re
import sys
import unicodedata
from datetime import date

sys.path.insert(0, os.path.dirname(__file__))
from db import connect

SKILLS_DIR = os.path.join(os.path.dirname(__file__), "..", "skills")
OVERLAYS_DIR = os.path.join(os.path.dirname(__file__), "..", "overlays")
REWRITE_CACHE = os.path.join(os.path.dirname(__file__), "..", "data", "rewrites")


def clean_intro(slug, raw_text):
    """Phase 2d: one grammatically clean English paragraph instead of raw
    ESCO text. gemma3-rewritten once, cached in data/rewrites/<slug>.intro.txt
    (re-runs cost nothing); falls back to the raw text when Ollama is
    unreachable. The raw ESCO original stays reachable via the ESCO URI in
    the sources footer."""
    if not raw_text:
        return raw_text
    os.makedirs(REWRITE_CACHE, exist_ok=True)
    cache = os.path.join(REWRITE_CACHE, f"{slug}.intro.txt")
    if os.path.exists(cache):
        cached = open(cache, encoding="utf-8").read().strip()
        if cached:
            return cached
    try:
        import requests
        from dotenv import load_dotenv
        load_dotenv(os.path.join(os.path.dirname(__file__), "..", ".env"))
        url = os.environ.get("OLLAMA_URL", "").rstrip("/")
        model = os.environ.get("OLLAMA_MODEL", "")
        if not url or not model:
            return raw_text
        prompt = (
            "Rewrite the following occupation description as ONE grammatically "
            "correct, clear English paragraph. Fix subject-verb agreement and "
            "awkward phrasing. Preserve every fact; add nothing; remove "
            "nothing substantive. Output only the paragraph.\n\n" + raw_text[:1500])
        r = requests.post(f"{url}/api/generate", json={
            "model": model, "prompt": prompt, "stream": False,
            "options": {"temperature": 0, "num_ctx": 2048, "num_predict": 400},
        }, timeout=120)
        r.raise_for_status()
        text = (r.json().get("response") or "").strip()
        # sanity: rewrite must stay a single plausible paragraph
        if 80 <= len(text) <= max(400, int(len(raw_text) * 1.6)) and "\n\n" not in text:
            open(cache, "w", encoding="utf-8", newline="\n").write(text + "\n")
            return text
    except Exception:
        pass
    return raw_text


def apply_overlays(slug, text):
    """Reviewed manual curation that must SURVIVE regeneration (phase 2d).
    overlays/<slug>/intro.md    -> replaces the intro paragraph
    overlays/<slug>/workflow.md -> replaces the '## Core workflow' body"""
    odir = os.path.join(OVERLAYS_DIR, slug)
    if not os.path.isdir(odir):
        return text
    intro_p = os.path.join(odir, "intro.md")
    if os.path.isfile(intro_p):
        intro = open(intro_p, encoding="utf-8").read().strip()
        text = re.sub(r"(?ms)(^---\n.*?\n---\n\n# [^\n]+\n\n).*?(?=\n\n## )",
                      lambda m: m.group(1) + intro, text, count=1)
    wf_p = os.path.join(odir, "workflow.md")
    if os.path.isfile(wf_p):
        wf = open(wf_p, encoding="utf-8").read().strip()
        text = re.sub(r"(?ms)(^## Core workflow\n\n).*?(?=\n\n## )",
                      lambda m: m.group(1) + wf, text, count=1)
    return text
TARGET_COUNT = 100

# ISCO-08 prefixes for knowledge work (management, business, IT, HR, finance,
# marketing, support). HR/recruiting lives in 242 (2423) and 333 (3333).
ISCO_PREFIXES = (
    "11", "12", "13",        # chief executives, managers
    "241", "242", "243",     # finance, admin/HR, sales & marketing professionals
    "251", "252",            # software/ICT professionals
    "261",                   # legal (contracts) — knowledge work
    "331", "332", "333", "334",  # business associate professionals
    "351", "352",            # ICT technicians/support
    "422",                   # client information workers (support)
)

MUST_INCLUDE_URIS = [
    # recruitment consultant (the deep-dive occupation of phase 3)
    "recruitment consultant",
]

ONET_ATTRIBUTION = (
    "This package includes information from the O*NET Database (v30.3) by the "
    "U.S. Department of Labor, Employment and Training Administration "
    "(USDOL/ETA), CC BY 4.0. skillfactor is not endorsed by USDOL/ETA. "
    "ESCO data (v1.2.1) (c) European Union, used per the ESCO download "
    "conditions: https://esco.ec.europa.eu/en/use-esco/download"
)


def slugify(label: str) -> str:
    s = unicodedata.normalize("NFKD", label).encode("ascii", "ignore").decode()
    s = re.sub(r"[^a-z0-9]+", "-", s.lower()).strip("-")
    return s[:80]


def q(cn, sql, *params):
    cur = cn.cursor()
    cur.execute(sql, *params)
    cols = [c[0] for c in cur.description]
    return [dict(zip(cols, row)) for row in cur.fetchall()]


def all_occupations(cn):
    """All 3039 ESCO occupations with best O*NET match (LEFT JOIN; onet_id is None if
    no crosswalk entry). Used by batch_run.py stage_packages for the full catalog."""
    rows = q(cn, """
        SELECT o.concept_uri, o.preferred_label, o.alt_labels, o.description,
               o.definition, o.isco_group, o.code,
               cw.onet_id, cw.onet_title, cw.match_type,
               ig.preferred_label AS isco_label
        FROM esco_occupation o
        LEFT JOIN crosswalk_esco_onet cw
            ON cw.esco_uri = o.concept_uri AND cw.match_type IN ('exactMatch','closeMatch')
        LEFT JOIN esco_isco_group ig ON ig.code = o.isco_group
    """)
    best = {}
    for r in rows:
        k = r["concept_uri"]
        if k not in best:
            best[k] = (None, r)
        elif r["onet_id"] is not None:
            existing_rank, _ = best[k]
            new_rank = (0 if r["match_type"] == "exactMatch" else 1, r["onet_id"])
            if existing_rank is None or new_rank < existing_rank:
                best[k] = (new_rank, r)
    return [v[1] for v in best.values()]


def pick_occupations(cn):
    """Deterministic candidate list: ESCO occupation + best O*NET match."""
    rows = q(cn, """
        SELECT o.concept_uri, o.preferred_label, o.alt_labels, o.description,
               o.definition, o.isco_group, o.code,
               cw.onet_id, cw.onet_title, cw.match_type,
               ig.preferred_label AS isco_label
        FROM esco_occupation o
        JOIN crosswalk_esco_onet cw ON cw.esco_uri = o.concept_uri
        LEFT JOIN esco_isco_group ig ON ig.code = o.isco_group
        WHERE cw.match_type IN ('exactMatch','closeMatch')
    """)
    # best match per ESCO occupation: exactMatch wins, then lowest onet_id (stable)
    best = {}
    for r in rows:
        k = r["concept_uri"]
        rank = (0 if r["match_type"] == "exactMatch" else 1, r["onet_id"])
        if k not in best or rank < best[k][0]:
            best[k] = (rank, r)
    cands = [v[1] for v in best.values()]

    # bucket per ISCO prefix, then round-robin across buckets so the selection
    # spreads over management, business, IT, HR, finance, marketing and support
    # instead of filling up with ISCO 11-13 managers alphabetically
    buckets = {p: [] for p in ISCO_PREFIXES}
    for r in cands:
        grp = r["isco_group"] or ""
        for p in ISCO_PREFIXES:
            if grp.startswith(p):
                buckets[p].append(r)
                break
    for p in buckets:
        buckets[p].sort(key=lambda r: (r["isco_group"] or "", r["preferred_label"] or ""))

    # force-include the recruiter even if selection window would cut it
    selected, seen = [], set()
    for r in cands:
        if (r["preferred_label"] or "").lower() in MUST_INCLUDE_URIS:
            selected.append(r)
            seen.add(r["concept_uri"])
    while len(selected) < TARGET_COUNT and any(buckets.values()):
        for p in ISCO_PREFIXES:
            if len(selected) >= TARGET_COUNT:
                break
            while buckets[p]:
                r = buckets[p].pop(0)
                if r["concept_uri"] not in seen:
                    selected.append(r)
                    seen.add(r["concept_uri"])
                    break
    return selected


def esco_skills_for(cn, uri):
    return q(cn, """
        SELECT r.relation_type, r.skill_type, s.preferred_label, s.description
        FROM esco_occ_skill r
        JOIN esco_skill s ON s.concept_uri = r.skill_uri
        WHERE r.occupation_uri = ?
        ORDER BY CASE r.relation_type WHEN 'essential' THEN 0 ELSE 1 END,
                 s.preferred_label
    """, uri)


def onet_tasks_for(cn, soc):
    return q(cn, """
        SELECT task, task_type FROM onet_task WHERE soc_code = ?
        ORDER BY CASE task_type WHEN 'Core' THEN 0 ELSE 1 END, task_id
    """, soc)


def onet_dwas_for(cn, soc):
    return q(cn, """
        SELECT DISTINCT d.dwa_name
        FROM onet_task_dwa td JOIN onet_dwa d ON d.dwa_id = td.dwa_id
        WHERE td.soc_code = ? ORDER BY d.dwa_name
    """, soc)


def onet_software_for(cn, soc):
    return q(cn, """
        SELECT example, element_name, hot_technology
        FROM onet_software WHERE soc_code = ?
        ORDER BY CASE hot_technology WHEN 'Y' THEN 0 ELSE 1 END, element_name, example
    """, soc)


def alt_label_list(alt_labels):
    if not alt_labels:
        return []
    return [a.strip() for a in str(alt_labels).splitlines() if a.strip()]


def write(path, content):
    os.makedirs(os.path.dirname(path), exist_ok=True)
    with open(path, "w", encoding="utf-8", newline="\n") as f:
        f.write(content)


def gen_package(cn, r):
    label = r["preferred_label"]
    slug = slugify(label)
    base = os.path.join(SKILLS_DIR, slug)
    alts = alt_label_list(r["alt_labels"])
    skills = esco_skills_for(cn, r["concept_uri"])
    tasks = onet_tasks_for(cn, r["onet_id"]) if r.get("onet_id") else []
    dwas = onet_dwas_for(cn, r["onet_id"]) if r.get("onet_id") else []
    software = onet_software_for(cn, r["onet_id"]) if r.get("onet_id") else []

    core_tasks = [t["task"] for t in tasks if t["task_type"] == "Core"][:8] or \
                 [t["task"] for t in tasks][:8]
    essential = [s for s in skills if s["relation_type"] == "essential"]
    optional = [s for s in skills if s["relation_type"] != "essential"]

    trigger_examples = "; ".join(core_tasks[:3]) if core_tasks else \
        f"typical {label} responsibilities"
    description = (
        f"Occupational skill for the role '{label}'"
        + (f" (also: {', '.join(alts[:6])})" if alts else "")
        + f". Use when the user asks for typical {label} work such as: {trigger_examples}"
    )

    # --- SKILL.md (< 300 lines, progressive disclosure into references/) ---
    # Phase 2d: intro = grammatically clean rewrite (cached, gemma3);
    # the raw ESCO text stays reachable via the provenance link in the footer.
    intro = clean_intro(slug, (r["description"] or r["definition"] or "").strip())
    lines = [
        "---",
        f"name: {slug}",
        f"description: {json.dumps(description)}",
        "---",
        "",
        f"# {label.title()}",
        "",
        intro,
        "",
        "## Core workflow",
        "",
    ]
    for i, t in enumerate(core_tasks, 1):
        lines.append(f"{i}. {t}")
    lines += [
        "",
        "## How to use this skill",
        "",
        "- Read [references/profile.md](references/profile.md) for the occupation profile and scope.",
        "- Consult [references/tasks.md](references/tasks.md) for the full task and activity inventory.",
        "- Check [references/skills.md](references/skills.md) for essential vs. optional competences.",
        "- Check [references/tools.md](references/tools.md) for the software commonly used in this role.",
    ]
    # enrichment files (phase 3) survive regeneration — link them when present
    enrichment = [
        ("references/ai-skills.md", "matched external AI agent skills (tiered, per-source attribution)"),
        ("references/practitioner-qa.md", "curated Stack Exchange practitioner Q&A (per-entry attribution, CC-BY-SA 4.0)"),
        ("references/market.md", "FULL market-evidence report from real job ads (rankings, seniority, titles)"),
        ("references/usecases.md", "ranked work packages the agent can take over"),
        ("references/intake.md", "mandatory questions before starting any task"),
        ("references/quality.md", "acceptance criteria for deliverables"),
        ("references/glossary.md", "definitions of the key tools and methods"),
        ("references/literature.md", "standard works per top skill"),
        ("evals/", "test tasks with pass criteria"),
    ]
    for rel, why in enrichment:
        if os.path.exists(os.path.join(base, rel.rstrip("/"))):
            lines.append(f"- See [{rel}]({rel}) — {why}.")
    lines += [
        "",
        "## Key competences (essential)",
        "",
    ]
    # Phase 2d: FULL essential list — never a silently cut alphabetical prefix.
    for s in essential:
        lines.append(f"- {s['preferred_label']}")
    # "Hot technologies" intentionally NOT emitted here: it is derived from
    # post-gate market evidence by p3c (omitted when the corpus is too thin),
    # never from the alphabetical O*NET dump.
    lines += [
        "",
        "---",
        f"*Sources: ESCO v1.2.1 ({r['concept_uri']}), O*NET 30.3 ({r['onet_id']}). "
        "See manifest.json for licensing/attribution.*",
    ]
    # Phase 2d: standardized proxy caveat wherever O*NET data is not an exact match
    if r.get("onet_id") and (r.get("match_type") or "").lower() not in ("exact", ""):
        lines.append(f"*O*NET nearest match: {r['onet_id']} {r.get('onet_title', '')} "
                     "(proxy — no exact O*NET occupation exists).*")
    lines.append("")
    write(os.path.join(base, "SKILL.md"),
          apply_overlays(slug, "\n".join(lines)))

    # --- references/profile.md ---
    prof = [
        f"# Occupation profile — {label}",
        "",
        f"- **ESCO URI:** {r['concept_uri']}",
        f"- **ESCO code:** {r['code']}",
        f"- **ISCO-08 group:** {r['isco_group']} — {r.get('isco_label') or 'n/a'}",
    ]
    if r.get("onet_id"):
        prof.append(
            f"- **O*NET-SOC:** {r['onet_id']} — {r['onet_title']} (match: {r['match_type']})"
        )
    prof += [
        "",
        "## Description (ESCO)",
        "",
        (r["description"] or "").strip(),
    ]
    if r["definition"]:
        prof += ["", "## Definition", "", str(r["definition"]).strip()]
    if alts:
        prof += ["", "## Alternative labels", ""] + [f"- {a}" for a in alts]
    write(os.path.join(base, "references", "profile.md"), "\n".join(prof) + "\n")

    # --- references/tasks.md ---
    tl = [f"# Tasks & work activities — {label}", "",
          f"Source: O*NET 30.3, occupation {r['onet_id']} ({r['onet_title']}).", "",
          "## Task statements", ""]
    for t in tasks:
        tl.append(f"- **[{t['task_type'] or 'n/a'}]** {t['task']}")
    if dwas:
        tl += ["", "## Detailed work activities", ""]
        tl += [f"- {d['dwa_name']}" for d in dwas]
    write(os.path.join(base, "references", "tasks.md"), "\n".join(tl) + "\n")

    # --- references/skills.md ---
    sl = [f"# Competences — {label}", "",
          f"Source: ESCO v1.2.1 occupation-skill relations ({r['concept_uri']}).", "",
          "## Essential", ""]
    for s in essential:
        sl.append(f"- **{s['preferred_label']}** ({s['skill_type']})")
    sl += ["", "## Optional", ""]
    for s in optional:
        sl.append(f"- {s['preferred_label']} ({s['skill_type']})")
    write(os.path.join(base, "references", "skills.md"), "\n".join(sl) + "\n")

    # --- references/tools.md ---
    ol = [f"# Tools & technology — {label}", "",
          f"Source: O*NET 30.3 'Software Skills' for {r['onet_id']}.",
          "", "| Software | Category | Hot technology |", "|---|---|---|"]
    for s in software:
        ol.append(f"| {s['example']} | {s['element_name']} | {'yes' if s['hot_technology'] == 'Y' else ''} |")
    write(os.path.join(base, "references", "tools.md"), "\n".join(ol) + "\n")

    # --- manifest.json ---
    manifest = {
        "name": slug,
        "title": label,
        "version": "0.1.0",
        "layer": "core",
        "language": "en",
        "generated": date.today().isoformat(),
        "ids": {
            "esco_uri": r["concept_uri"],
            "esco_code": r["code"],
            "isco_group": r["isco_group"],
            "onet_soc": r.get("onet_id"),
            "crosswalk_match": r.get("match_type"),
        },
        "sources": [
            {"name": "ESCO", "version": "1.2.1", "url": "https://esco.ec.europa.eu/"},
            {"name": "O*NET", "version": "30.3", "url": "https://www.onetcenter.org/", "license": "CC BY 4.0"},
        ],
        "attribution": ONET_ATTRIBUTION,
        "counts": {
            "tasks": len(tasks),
            "dwas": len(dwas),
            "skills_essential": len(essential),
            "skills_optional": len(optional),
            "software": len(software),
        },
    }
    write(os.path.join(base, "manifest.json"), json.dumps(manifest, indent=2) + "\n")
    return slug


def main():
    cn = connect()
    selected = pick_occupations(cn)
    print(f"Selected {len(selected)} occupations")
    slugs = []
    for r in selected:
        slugs.append(gen_package(cn, r))
    cn.close()
    # remove packages from a previous selection that are no longer chosen
    import shutil
    for d in os.listdir(SKILLS_DIR):
        full = os.path.join(SKILLS_DIR, d)
        if os.path.isdir(full) and d not in slugs:
            shutil.rmtree(full)
            print(f"removed stale package: {d}")
    print(f"Generated {len(slugs)} packages under skills/")
    print("Recruiter included:", any("recruit" in s for s in slugs))


if __name__ == "__main__":
    main()