#  File:    transform.py
#  Path:    apps/etl/src/airiskindex_etl/transform.py
#  Project: AI Risk Index — airiskindex.io
#  Author:  Simon-Pierre Boucher
#  Contact: contact@spboucher.ai
#  Copyright © 2026 Simon-Pierre Boucher. All rights reserved.
#
#  Description: Transform raw O*NET + OEWS dumps into derived CSVs with a manifest.

"""Transform raw O*NET + OEWS dumps into derived CSVs with a manifest.

Raw is never edited in place; derived payloads are gitignored, only the
manifest (hashes + row counts) is committed (CLAUDE.md §5).
"""

from __future__ import annotations

import csv
import hashlib
import json
from pathlib import Path

from openpyxl import load_workbook

REPO_ROOT = Path(__file__).resolve().parents[4]
RAW_ONET = REPO_ROOT / "data" / "raw" / "onet"
RAW_OEWS = REPO_ROOT / "data" / "raw" / "oews"
DERIVED = REPO_ROOT / "data" / "derived" / "onet"


def read_onet_table(name: str) -> list[dict[str, str]]:
    # The text dump extracts into a versioned subdirectory (db_30_3_text/).
    path = next(RAW_ONET.glob(f"**/{name}"))
    with path.open(encoding="utf-8", newline="") as fh:
        return list(csv.DictReader(fh, delimiter="\t"))


def transform_occupations() -> list[dict[str, str]]:
    rows = read_onet_table("Occupation Data.txt")
    return [
        {
            "code": row["O*NET-SOC Code"],
            "title": row["Title"],
            "description": row["Description"],
        }
        for row in rows
    ]


def transform_tasks() -> list[dict[str, str]]:
    statements = read_onet_table("Task Statements.txt")
    ratings = read_onet_table("Task Ratings.txt")

    # Importance = Task Ratings rows with Scale ID "IM" (1–5), one per task.
    importance: dict[str, str] = {}
    for row in ratings:
        if row["Scale ID"] == "IM" and row.get("Recommend Suppress", "N") != "Y":
            importance[row["Task ID"]] = row["Data Value"]

    return [
        {
            "task_id": row["Task ID"],
            "code": row["O*NET-SOC Code"],
            "statement": row["Task"],
            "task_type": row.get("Task Type", ""),
            "importance": importance.get(row["Task ID"], ""),
        }
        for row in statements
    ]


def transform_wages() -> list[dict[str, str]]:
    """OEWS national medians per detailed SOC: annual median (cents) + employment."""
    xlsx = next(RAW_OEWS.glob("**/national_M2025_dl.xlsx"), None) or next(
        RAW_OEWS.glob("**/*_dl.xlsx")
    )
    sheet = load_workbook(xlsx, read_only=True).active
    header = [str(cell.value).strip().upper() for cell in next(sheet.iter_rows(max_row=1))]
    idx = {name: header.index(name) for name in ("OCC_CODE", "O_GROUP", "TOT_EMP", "A_MEDIAN")}

    out: list[dict[str, str]] = []
    for row in sheet.iter_rows(min_row=2, values_only=True):
        if str(row[idx["O_GROUP"]]).strip() != "detailed":
            continue
        soc = str(row[idx["OCC_CODE"]]).strip()
        median = row[idx["A_MEDIAN"]]
        employment = row[idx["TOT_EMP"]]
        # "#" = wage above the top-code (~$239,200); "*" / "**" = unavailable.
        if median == "#":
            median_cents = 23_920_000
        elif isinstance(median, (int, float)):
            median_cents = int(round(float(median) * 100))
        else:
            median_cents = ""
        out.append(
            {
                "soc": soc,
                "median_wage_cents": str(median_cents),
                "employment": str(int(employment)) if isinstance(employment, (int, float)) else "",
            }
        )
    return out


def write_csv(path: Path, rows: list[dict[str, str]]) -> dict[str, object]:
    path.parent.mkdir(parents=True, exist_ok=True)
    with path.open("w", encoding="utf-8", newline="") as fh:
        writer = csv.DictWriter(fh, fieldnames=list(rows[0].keys()))
        writer.writeheader()
        writer.writerows(rows)
    return {
        "file": path.name,
        "rows": len(rows),
        "sha256": hashlib.sha256(path.read_bytes()).hexdigest(),
    }


def main() -> None:
    onet_manifest = json.loads((RAW_ONET / "manifest.json").read_text())
    entries = [
        write_csv(DERIVED / "occupations.csv", transform_occupations()),
        write_csv(DERIVED / "tasks.csv", transform_tasks()),
        write_csv(DERIVED / "wages.csv", transform_wages()),
    ]
    (DERIVED / "manifest.json").write_text(
        json.dumps(
            {"source_onet_version": onet_manifest["version"], "outputs": entries}, indent=2
        )
    )
    for entry in entries:
        print(f"{entry['file']}: {entry['rows']} rows")


if __name__ == "__main__":
    main()