#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
build_register.py - spreadsheet -> threat register JSON

Reads the Common Risk Assessment workbook (ILT / RDI / RDW / I&W) and writes:
  data/threads_register.json

The output contains:
  * the 57 workshop threats (T-001..T-057)
  * optionally the 12 threats added by RDW (T-058..T-069)
  * category profiles for CVE, paper, repository and signal evidence
  * resolved matching rules per threat

Usage:
  python3 build_register.py [workbook.xlsx] [--include-rdw]

The matching rules below are judgement-based and should be reviewed. Signals are
external observations, incidents, reporting and strategic developments. A signal
can support or contextualise a threat, but does not by itself prove exploitation.
"""

import json
import re
import sys
from pathlib import Path

from openpyxl import load_workbook


DEFAULT_XLSX = "Common_Risk_Assessment_ILT_RDI_RDW_IenW_v0.65.xlsx"
OUT = "data/threats_register.json"

TECHNICAL = ["cve", "paper", "repo"]
STRATEGIC = ["paper", "signal"]
TECHNICAL_WITH_SIGNALS = ["cve", "paper", "repo", "signal"]
ALL = TECHNICAL_WITH_SIGNALS


# ------------------------------------------------------------------
# CATEGORY PROFILES
#
# surfaces:
#   Technical surfaces relevant to evidence matching.
# terms:
#   Stronger lexical or semantic indicators for the category.
# types:
#   Evidence types that can sensibly support or contextualise the threat.
#   [] means no directly correlated external evidence is expected.
# paper_queries:
#   Plain-language search queries used by papers_intel.py.
# signal_queries:
#   Plain-language themes that can be used by signal correlation or enrichment.
# ------------------------------------------------------------------
CATEGORIES = {
    "Mobility Services and Large-Scale Disruptions": dict(
        surfaces=["Telematics", "Backend / Cloud", "OTA / Firmware"],
        terms=["fleet", "fleet management", "mobility service", "vehicle cloud", "back end", "cascading"],
        types=ALL,
        paper_queries=[
            "fleet cyberattack connected vehicles large scale disruption",
            "vehicle backend cloud outage resilience",
        ],
        signal_queries=[
            "connected fleet disruption",
            "mobility service outage",
            "vehicle backend incident",
            "large scale automotive cyber disruption",
        ],
    ),
    "Energy, Charging, and V2G Infrastructure": dict(
        surfaces=["EV Charging"],
        terms=["v2g", "vehicle to grid", "iso 15118", "charging station", "ocpp", "evse", "smart charging", "power grid"],
        types=ALL,
        paper_queries=[
            "electric vehicle charging infrastructure cyber attack",
            "vehicle-to-grid security",
            "ISO 15118 security charging",
        ],
        signal_queries=[
            "EV charging cyber incident",
            "charging infrastructure disruption",
            "V2G security development",
            "OCPP vulnerability incident",
        ],
    ),
    "Vehicle Control and Function Manipulation": dict(
        surfaces=["CAN Bus", "ECU", "UDS Diagnostics", "Infotainment", "Gateway", "OBD"],
        terms=["remote control", "remote command", "vehicle takeover", "remote code execution"],
        types=ALL,
        paper_queries=[
            "vehicle remote control manipulation attack",
            "infotainment attack vehicle takeover",
            "remote lock unlock vehicle attack",
        ],
        signal_queries=[
            "vehicle remote control incident",
            "automotive function manipulation",
            "connected vehicle takeover report",
        ],
    ),
    "Critical Infrastructure and National Security": dict(
        surfaces=[],
        terms=["critical infrastructure", "national security", "satellite", "sovereignty", "resilience"],
        types=STRATEGIC,
        paper_queries=[
            "connected vehicles critical infrastructure national security",
            "satellite communication vehicles security",
        ],
        signal_queries=[
            "automotive critical infrastructure threat",
            "connected vehicles national security",
            "transportation infrastructure cyber campaign",
            "satellite vehicle communication disruption",
        ],
    ),
    "Development, Approval, and Lifecycle Management": dict(
        surfaces=[],
        terms=["secure development", "secure boot", "type approval", "penetration testing", "fuzzing", "security testing", "lifecycle"],
        types=["paper", "repo", "signal"],
        paper_queries=[
            "automotive secure development lifecycle",
            "vehicle type approval cybersecurity testing",
        ],
        signal_queries=[
            "automotive type approval cybersecurity development",
            "vehicle lifecycle security issue",
            "automotive testing capability development",
        ],
    ),
    "Road Safety and Autonomous Vehicles": dict(
        surfaces=[],
        terms=["autonomous vehicle", "autonomous vehicles", "autonomous driving", "adas", "self driving", "perception", "lidar", "adversarial", "sensor spoofing"],
        types=ALL,
        paper_queries=[
            "autonomous vehicle cyber attack safety",
            "adversarial attack autonomous driving perception",
            "sensor spoofing lidar camera vehicle",
        ],
        signal_queries=[
            "autonomous vehicle security incident",
            "ADAS manipulation report",
            "vehicle sensor spoofing incident",
            "autonomous driving cyber development",
        ],
    ),
    "Vehicle Ecosystem, Supply Chain, and Dependencies": dict(
        surfaces=[],
        terms=["supply chain", "supplier", "third party", "dependency", "tier 1", "end of life", "end of support", "geopolitical"],
        types=STRATEGIC,
        paper_queries=[
            "automotive supply chain cybersecurity",
            "OEM supplier dependency vehicle cybersecurity",
        ],
        signal_queries=[
            "automotive supplier cyber incident",
            "vehicle software supply chain compromise",
            "OEM third party dependency disruption",
            "automotive geopolitical dependency",
        ],
    ),
    "Privacy, Personal Data, and Surveillance": dict(
        surfaces=[],
        terms=["privacy", "personal data", "location data", "tracking", "gdpr", "driver monitoring", "occupant", "data leakage", "surveillance"],
        types=["paper", "signal"],
        paper_queries=[
            "connected car privacy personal data",
            "vehicle location tracking privacy",
            "driver monitoring camera privacy",
        ],
        signal_queries=[
            "connected vehicle privacy incident",
            "automotive personal data exposure",
            "vehicle location tracking report",
            "driver monitoring surveillance development",
        ],
    ),
    "Espionage and Intelligence Gathering": dict(
        surfaces=[],
        terms=["espionage", "surveillance", "microphone", "camera", "sensor data", "exfiltration"],
        types=STRATEGIC,
        paper_queries=[
            "vehicle espionage surveillance sensors",
            "connected car data foreign access",
        ],
        signal_queries=[
            "vehicle espionage campaign",
            "connected car intelligence collection",
            "automotive sensor data exfiltration",
            "vehicle surveillance concern",
        ],
    ),
    "Governance, Regulation, and Oversight": dict(
        surfaces=[],
        terms=["unece r155", "r156", "iso 21434", "cyber resilience act", "regulation", "type approval", "compliance"],
        types=STRATEGIC,
        paper_queries=[
            "UNECE R155 R156 cybersecurity management system",
            "cyber resilience act automotive",
        ],
        signal_queries=[
            "automotive cybersecurity regulation development",
            "UNECE R155 enforcement",
            "vehicle type approval oversight",
            "automotive compliance gap",
        ],
    ),
    "Software, Hardware, and Architecture Transparency": dict(
        surfaces=[],
        terms=["sbom", "software bill of materials", "firmware", "reverse engineering", "architecture", "variants", "asset inventory"],
        types=["paper", "repo", "signal"],
        paper_queries=[
            "automotive SBOM software bill of materials",
            "vehicle software transparency firmware analysis",
        ],
        signal_queries=[
            "automotive SBOM development",
            "vehicle firmware transparency issue",
            "automotive asset inventory gap",
        ],
    ),
    "Detection, Monitoring, and Incident Response": dict(
        surfaces=[],
        terms=["intrusion detection", "anomaly detection", "security operations center", "vsoc", "incident response", "logging", "telemetry", "siem"],
        types=["paper", "repo", "signal"],
        paper_queries=[
            "vehicle security operations center",
            "automotive intrusion detection fleet monitoring",
            "vehicle incident response logging forensics",
        ],
        signal_queries=[
            "automotive incident response development",
            "vehicle SOC monitoring capability",
            "fleet intrusion detection incident",
            "automotive forensics development",
        ],
    ),
    "Communication, Navigation, and Wireless Attacks": dict(
        surfaces=["GNSS", "Bluetooth", "Wi-Fi", "V2X"],
        terms=["gnss", "gps", "jamming", "spoofing", "sdr", "software defined radio", "wireless"],
        types=ALL,
        paper_queries=[
            "GNSS spoofing jamming vehicles",
            "software defined radio vehicle attack",
            "V2X wireless attack",
        ],
        signal_queries=[
            "GNSS jamming transportation incident",
            "vehicle wireless attack report",
            "V2X security incident",
            "automotive radio interference",
        ],
    ),
    "AI, Software Supply Chains, and External Services": dict(
        surfaces=[],
        terms=["llm", "large language model", "machine learning", "ai", "software supply chain", "open source", "dependency", "cloud"],
        types=["paper", "repo", "signal"],
        paper_queries=[
            "large language model automotive security",
            "AI software supply chain vehicle",
            "cloud AI services connected vehicle",
        ],
        signal_queries=[
            "automotive AI security development",
            "vehicle cloud service incident",
            "AI software supply chain compromise",
            "connected vehicle external service disruption",
        ],
    ),
    "Vehicle Theft and Digital Crime": dict(
        surfaces=["Keyless / Hardware", "CAN Bus", "OBD"],
        terms=["relay attack", "can injection", "key fob", "keyless", "immobilizer", "vehicle theft", "car theft"],
        types=ALL,
        paper_queries=[
            "relay attack keyless entry vehicle theft",
            "CAN injection vehicle theft",
        ],
        signal_queries=[
            "digital vehicle theft campaign",
            "keyless relay theft incident",
            "CAN injection theft report",
            "automotive crime tooling",
        ],
    ),
}


# ------------------------------------------------------------------
# PER-THREAT OVERRIDES
# ------------------------------------------------------------------
AV = dict(surfaces=[], terms=CATEGORIES["Road Safety and Autonomous Vehicles"]["terms"])
OVERRIDES = {
    "T-003": dict(surfaces=["EV Charging"], terms=["power grid", "grid", "charging station", "ocpp"]),
    "T-004": dict(surfaces=["EV Charging"], terms=["v2g", "vehicle to grid", "iso 15118"]),
    "T-005": dict(surfaces=["CAN Bus", "ECU", "Telematics", "Infotainment"], terms=["remote control", "sabotage"]),
    "T-007": dict(surfaces=["CAN Bus", "ECU", "Telematics", "Infotainment"], terms=["remote control", "sabotage"]),
    "T-008": dict(surfaces=["ECU", "Telematics", "OTA / Firmware"], terms=["remote disable", "immobilizer", "kill switch"]),
    "T-009": dict(surfaces=["Telematics", "Infotainment", "Backend / Cloud", "Wi-Fi", "Bluetooth"], terms=["botnet", "ddos", "remote code execution"], types=ALL),
    "T-014": dict(terms=["geopolitical", "sovereignty", "foreign", "trusted supplier", "export control"], types=STRATEGIC),
    "T-019": dict(types=[]),
    "T-023": dict(surfaces=["Infotainment", "Gateway"], terms=["head unit", "infotainment", "ivi"]),
    "T-028": dict(terms=["cyber resilience act"], types=["paper", "signal"]),
    "T-029": dict(types=[]),
    "T-030": dict(terms=["end of life", "end of support", "patching", "software updates", "long term support"], types=["paper", "signal"]),
    "T-035": dict(surfaces=["GNSS"], terms=["gnss", "gps", "gps spoofing", "jamming", "spoofing"]),
    "T-036": dict(types=["paper", "cve", "signal"], terms=["information disclosure", "sensitive information", "personal data", "privacy"]),
    "T-037": dict(surfaces=[], terms=["llm", "large language model", "machine learning", "vulnerability discovery", "fuzzing", "automated exploit"], types=["paper", "repo", "signal"]),
    "T-042": dict(surfaces=["ECU", "Infotainment", "OBD"], terms=["usb", "physical access"], types=ALL),
    "T-043": dict(surfaces=["Keyless / Hardware", "Backend / Cloud"], terms=["remote lock", "central locking", "remote unlock"]),
    "T-044": dict(),
    "T-045": dict(terms=["factory reset", "data deletion", "second hand", "used vehicle", "disposal"], types=["paper", "signal"]),
    "T-047": dict(surfaces=["OTA / Firmware", "Backend / Cloud", "Telematics", "ECU"], terms=["manipulation", "remote command"]),
    "T-052": dict(terms=["cloud", "voice assistant", "ai assistant", "edge", "external service", "backend"], types=["paper", "repo", "signal"]),
    "T-053": dict(surfaces=["OBD"], terms=["aftermarket", "dongle", "obd dongle", "tuning", "retrofit"], types=ALL),
    "T-054": dict(surfaces=["SDR / Radio", "Keyless / Hardware", "Bluetooth", "Wi-Fi", "V2X"], terms=["sdr", "software defined radio", "gnu radio", "relay attack", "replay attack"], types=ALL),
    "T-055": dict(terms=["satellite", "satcom", "starlink"], types=["paper", "signal"]),
    "T-056": dict(terms=["bankruptcy", "end of support", "financial"], types=["paper", "signal"]),
    # RDW additions
    "T-058": dict(surfaces=["Backend / Cloud", "Telematics"], terms=["backend", "cloud", "api"], types=ALL),
    "T-059": dict(surfaces=["Bluetooth", "Wi-Fi", "V2X", "Telematics"], terms=["communication channel", "wireless"], types=ALL),
    "T-060": dict(surfaces=["OTA / Firmware"], terms=["ota", "software update", "r156"], types=ALL),
    "T-061": dict(surfaces=[], terms=["social engineering", "phishing", "insider"], types=["paper", "signal"]),
    "T-062": dict(surfaces=["OBD", "Bluetooth", "Wi-Fi", "Infotainment"], terms=["usb", "obd", "dongle"], types=ALL),
    "T-063": dict(surfaces=["ECU", "OTA / Firmware"], terms=["credentials", "firmware extraction", "key extraction"], types=ALL),
    "T-064": dict(surfaces=["ECU", "Gateway", "Infotainment", "UDS Diagnostics"], terms=[], types=ALL),
    "T-065": dict(surfaces=["Backend / Cloud"], terms=["certificate", "pki", "cryptographic key", "key management"], types=ALL),
    "T-066": dict(**AV, types=ALL),
    "T-067": dict(surfaces=["Telematics", "Backend / Cloud"], terms=["teleoperation", "dispatch", "fleet"], types=ALL),
    "T-068": dict(surfaces=["V2X"], terms=["traffic management", "traffic light", "roadside unit", "rsu", "intelligent transportation"], types=["paper", "cve", "signal"]),
    "T-069": dict(surfaces=[], terms=["single point of failure", "shared dependency", "supply chain"], types=["paper", "signal"]),
}

for tid in ("T-010", "T-012", "T-013"):
    OVERRIDES[tid] = dict(AV, types=ALL)
for tid in ("T-017", "T-018"):
    OVERRIDES[tid] = dict(types=["paper", "signal"])
OVERRIDES["T-024"] = dict(terms=["safety", "iso 26262", "sotif", "iso 21434"], types=["paper", "signal"])
OVERRIDES["T-025"] = dict(terms=["penetration testing", "fuzzing", "security testing", "type approval"], types=["paper", "repo", "signal"])
OVERRIDES["T-027"] = dict(terms=["vehicle identification", "fleet inventory", "asset inventory"], types=["paper", "signal"])
OVERRIDES["T-031"] = dict(terms=["supply chain", "supplier", "tier 1"], types=["paper", "signal"])
OVERRIDES["T-033"] = dict(terms=["supply chain attack", "third party", "dependency"], types=["paper", "signal"])
OVERRIDES["T-038"] = dict(terms=["sbom", "software supply chain", "open source", "dependency", "llm", "ai"], types=["paper", "repo", "signal"])


# ------------------------------------------------------------------
def clean(v):
    if v is None:
        return ""
    return re.sub(r"[ \t]+", " ", str(v)).strip()


def split_list(v):
    return [x.strip() for x in re.split(r"[;,]", clean(v)) if x.strip()]


def as_number(v):
    if v is None or clean(v) == "":
        return ""
    if isinstance(v, bool):
        return v
    if isinstance(v, int):
        return v
    if isinstance(v, float):
        return int(v) if v.is_integer() else v
    text = clean(v)
    try:
        number = float(text)
        return int(number) if number.is_integer() else number
    except ValueError:
        return text


def read_collated(ws):
    rows = list(ws.iter_rows(values_only=True))
    if not rows:
        raise ValueError("Worksheet 'Collated Threat Taxonomy' is empty")
    head = [clean(h) for h in rows[0]]
    col = {h: i for i, h in enumerate(head) if h}
    required = {"Threat ID", "Threat Source", "Threat title", "Primary category", "Primary domain", "Secondary domain(s)", "Analysis/Description"}
    missing = sorted(required - set(col))
    if missing:
        raise ValueError("Missing required columns in Collated Threat Taxonomy: " + ", ".join(missing))

    def cell(row, name):
        idx = col.get(name)
        return row[idx] if idx is not None and idx < len(row) else None

    out = []
    seen = set()
    for row in rows[1:]:
        tid = clean(cell(row, "Threat ID"))
        if not re.fullmatch(r"T-\d{3}", tid):
            continue
        if tid in seen:
            raise ValueError(f"Duplicate threat ID: {tid}")
        seen.add(tid)
        primary = clean(cell(row, "Primary domain"))
        secondary = split_list(cell(row, "Secondary domain(s)"))
        affects = []
        for domain in [primary, *secondary]:
            if domain and domain not in affects:
                affects.append(domain)
        out.append({
            "id": tid,
            "title": clean(cell(row, "Threat title")),
            "title_nl": "",
            "title_note": "",
            "source": clean(cell(row, "Threat Source")),
            "category": clean(cell(row, "Primary category")),
            "affects": affects,
            "primary_domain": primary,
            "secondary_domains": secondary,
            "domain_rationale": "",
            "description": clean(cell(row, "Analysis/Description")),
            "impact": clean(cell(row, "Impact (based on older scale)")),
            "impact_rationale": clean(cell(row, "Impact Rationale (based on new scale)")),
            "impact_explanation": clean(cell(row, "Impact Explanations/Comments/Rationales")),
            "physical_safety_impact": clean(cell(row, "Physical Safety Impact")),
            "economic_financial_impact": clean(cell(row, "Economic/Financial Impact")),
            "social_political_privacy_impact": clean(cell(row, "Social, Political & Privacy Impact")),
            "territorial_security_impact": clean(cell(row, "Territorial Security Impact")),
            "impact_class": clean(cell(row, "Combined impact class")),
            "impact_score": as_number(cell(row, "Impact score")),
            "probability": clean(cell(row, "Likelihood (based on older scale)")),
            "likelihood_rationale": clean(cell(row, "Likelihood rationale")),
            "likelihood_class": clean(cell(row, "Combined likelihood class")),
            "likelihood_score": as_number(cell(row, "Likelihood score")),
            "risk_score": as_number(cell(row, "Combined risk score")),
            "risk": clean(cell(row, "Combined rating")) or clean(cell(row, "Risk rating (based on older scale)")),
            "priority": "",
            "tags": [],
            "risk_rationale": clean(cell(row, "Additional comments")),
            "additional_comments": clean(cell(row, "Additional comments")),
            "legacy_risk_score": as_number(cell(row, "Risk score (based on older scale)")),
            "legacy_risk_rating": clean(cell(row, "Risk rating (based on older scale)")),
        })
    out.sort(key=lambda item: item["id"])
    return out


def read_annex5_areas(ws):
    areas = {}
    for row in list(ws.iter_rows(values_only=True))[1:]:
        tid = clean(row[0] if len(row) > 0 else None)
        area = clean(row[2] if len(row) > 2 else None)
        if re.fullmatch(r"T-\d{3}", tid) and area:
            areas[tid] = area
    return areas


def main():
    args = [a for a in sys.argv[1:] if not a.startswith("--")]
    xlsx = args[0] if args else DEFAULT_XLSX

    if not Path(xlsx).exists():
        alt = Path("/mnt/user-data/uploads") / DEFAULT_XLSX
        xlsx = str(alt) if alt.exists() else xlsx

    wb = load_workbook(xlsx, read_only=True, data_only=True)

    collated_sheet = "Collated Threat Taxonomy"
    annex_sheet = "Additional Risks added by RDW "
    if collated_sheet not in wb.sheetnames:
        raise ValueError(f"Required worksheet not found: {collated_sheet}")
    if annex_sheet not in wb.sheetnames:
        raise ValueError(f"Required worksheet not found: {annex_sheet}")

    threats = read_collated(wb[collated_sheet])
    annex5_areas = read_annex5_areas(wb[annex_sheet])
    for threat in threats:
        area = annex5_areas.get(threat["id"])
        if area:
            threat["annex5_area"] = area

    if "--include-rdw" not in sys.argv:
        print("Note: --include-rdw is retained for compatibility; all 69 collated threats are included.")

    unknown = {t["category"] for t in threats} - set(CATEGORIES)
    if unknown:
        print("WARNING: categories without profile:", unknown)

    for threat in threats:
        profile = dict(
            CATEGORIES.get(
                threat["category"],
                dict(
                    surfaces=[],
                    terms=[],
                    types=ALL,
                    paper_queries=[],
                    signal_queries=[],
                ),
            )
        )
        profile.update(OVERRIDES.get(threat["id"], {}))

        threat["surfaces"] = list(profile.get("surfaces", []))
        threat["terms"] = list(profile.get("terms", []))
        threat["evidence_types"] = list(profile.get("types", []))
        threat["paper_queries"] = list(profile.get("paper_queries", []))
        threat["signal_queries"] = list(profile.get("signal_queries", []))

    data = {
        "_comment": (
            "Generated by build_register.py from the workbook. 'surfaces', 'terms' and "
            "'evidence_types' decide which evidence may be correlated to a threat. Supported "
            "types are cve, paper, repo and signal. evidence_types [] means that no directly "
            "correlated external evidence is expected. Signal evidence provides contextual or "
            "strategic support and does not by itself prove exploitation."
        ),
#       "source_workbook": Path(xlsx).name,
        "source_worksheet": "Collated Threat Taxonomy",
        "annex5_enrichment_worksheet": "Additional Risks added by RDW ",
        "source_worksheet": "Collated Threat Taxonomy",
        "annex5_enrichment_worksheet": "Additional Risks added by RDW ",
        "evidence_type_definitions": {
            "cve": "Known vulnerability evidence associated with a technical component or surface.",
            "paper": "Academic or technical research supporting the threat model.",
            "repo": "Public tooling, proof-of-concept, detection or mitigation research.",
            "signal": "Incident reporting, public reporting or strategic development that supports or contextualises the threat.",
        },
        "categories": CATEGORIES,
        "threats": threats,
    }

    output_path = Path(OUT)
    output_path.parent.mkdir(parents=True, exist_ok=True)
    output_path.write_text(
        json.dumps(data, indent=2, ensure_ascii=False),
        encoding="utf-8",
    )

    signal_enabled = sum(
        1 for threat in threats
        if "signal" in threat.get("evidence_types", [])
    )
    organisational = sum(
        1 for threat in threats
        if not threat.get("evidence_types")
    )

    print(
        f"Wrote {OUT}: {len(threats)} threats, {len(CATEGORIES)} categories, "
        f"{signal_enabled} signal-enabled threats, {organisational} organisational threats"
    )


if __name__ == "__main__":
    main()
