"""One-off: manual O*NET mapping for the flagship artificial-intelligence-engineer.

The official ESCO<->O*NET crosswalk has no entry for this ESCO occupation, so
tasks.md/tools.md were empty and the provenance pie showed no O*NET segment.
Nearest O*NET 30.3 occupation: 15-2051.00 Data Scientists (model building,
deployment, evaluation — the closest day-to-day match for AI engineering).

Regenerates ONLY references/tasks.md + references/tools.md (p2 format),
re-inserts the market-evidence section (p3c), updates manifest ids and the
SKILL.md sources footer + Hot technologies. Does NOT touch the authored
SKILL.md workflow or the depth files.
"""
import json
import os
import re
import sys

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

BASE = os.path.join(os.path.dirname(__file__), "..")
SLUG = "artificial-intelligence-engineer"
SOC = "15-2051.00"
SOC_TITLE = "Data Scientists"
PKG = os.path.join(BASE, "skills", SLUG)


def main():
    cn = connect()
    cur = cn.cursor()

    cur.execute("SELECT task, task_type FROM onet_task WHERE soc_code=? ORDER BY task_id", SOC)
    tasks = cur.fetchall()
    cur.execute("""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)
    dwas = [r[0] for r in cur.fetchall()]
    cur.execute("""SELECT example, element_name, hot_technology FROM onet_software
                   WHERE soc_code=? ORDER BY hot_technology DESC, example""", SOC)
    software = cur.fetchall()

    label = "artificial intelligence engineer"
    note = (f"Source: O*NET 30.3, occupation {SOC} ({SOC_TITLE}) — manual nearest-"
            f"occupation mapping; the official ESCO crosswalk has no entry for "
            f"this ESCO occupation.")

    # --- tasks.md ---
    tl = [f"# Tasks & work activities — {label}", "", note, "", "## Task statements", ""]
    tl += [f"- **[{t.task_type or 'n/a'}]** {t.task}" for t in tasks]
    if dwas:
        tl += ["", "## Detailed work activities", ""] + [f"- {d}" for d in dwas]
    open(os.path.join(PKG, "references", "tasks.md"), "w", encoding="utf-8",
         newline="\n").write("\n".join(tl) + "\n")

    # --- tools.md (market section is re-added by p3c below) ---
    ol = [f"# Tools & technology — {label}", "", note, "",
          "| Software | Category | Hot technology |", "|---|---|---|"]
    ol += [f"| {s.example} | {s.element_name} | {'yes' if s.hot_technology == 'Y' else ''} |"
           for s in software]
    open(os.path.join(PKG, "references", "tools.md"), "w", encoding="utf-8",
         newline="\n").write("\n".join(ol) + "\n")

    # --- manifest ---
    mp = os.path.join(PKG, "manifest.json")
    m = json.load(open(mp, encoding="utf-8"))
    m["ids"]["onet_soc"] = SOC
    m["ids"]["crosswalk_match"] = f"manual nearest occupation ({SOC_TITLE})"
    json.dump(m, open(mp, "w", encoding="utf-8"), indent=2)

    # --- SKILL.md: sources footer + Hot technologies ---
    sp = os.path.join(PKG, "SKILL.md")
    text = open(sp, encoding="utf-8").read()
    text = text.replace("O*NET 30.3 (None)", f"O*NET 30.3 ({SOC}, manual nearest match)")
    if "## Hot technologies" not in text:
        hot = [s.example for s in software if s.hot_technology == "Y"][:10]
        if hot:
            block = "## Hot technologies\n\n" + "\n".join(f"- {h}" for h in hot) + "\n\n"
            text = re.sub(r"(?m)^---\n\*Sources:", block + "---\n*Sources:", text, count=1)
    open(sp, "w", encoding="utf-8", newline="\n").write(text)

    # --- market-evidence sections back into tools/skills ---
    p3c.aggregate_for_occupation(cn, SLUG, os.path.join(BASE, "skills"))
    cn.close()

    print(f"tasks: {len(tasks)}, dwas: {len(dwas)}, software: {len(software)} — "
          f"{SLUG} remapped to {SOC} ({SOC_TITLE})")


if __name__ == "__main__":
    main()