#!/usr/bin/env python3

import json
from pathlib import Path
import pandas as pd
import re
import unicodedata

EXCEL_FILE = "Common_Risk_Assessment_ILT_RDI_RDW_IenW_v0.65.xlsx"

DATA_DIR = Path("data")
DATA_DIR.mkdir(exist_ok=True)
def normalize_sheet_name(name):

    if name is None:
        return ""

    name = str(name)

    name = unicodedata.normalize(
        "NFKC",
        name
    )

    name = name.replace(
        "\xa0",
        " "
    )

    name = re.sub(
        r"\s+",
        " ",
        name
    )

    return name.strip().lower()


def find_sheet(
    xls,
    requested_name
):

    target = normalize_sheet_name(
        requested_name
    )

    for sheet in xls.sheet_names:

        if (
            normalize_sheet_name(sheet)
            == target
        ):
            return sheet

    print("\nAvailable worksheets:\n")

    for sheet in xls.sheet_names:

        print(repr(sheet))

    raise ValueError(
        f"Worksheet not found: {requested_name}"
    )


def load_sheet(
    xls,
    logical_name
):

    sheet = find_sheet(
        xls,
        logical_name
    )

    print(
        f"[OK] {logical_name} -> {repr(sheet)}"
    )

    return pd.read_excel(
        EXCEL_FILE,
        sheet_name=sheet
    )


def clean(value):

    if pd.isna(value):
        return ""

    return str(value).strip()


def save_json(filename, data):

    with open(
        DATA_DIR / filename,
        "w",
        encoding="utf-8"
    ) as f:

        json.dump(
            data,
            f,
            indent=2,
            ensure_ascii=False
        )



print("Loading workbook...")

xls = pd.ExcelFile(EXCEL_FILE)

print("\nDetected worksheets:\n")

for i, sheet in enumerate(
        xls.sheet_names):

    print(
        f"{i:02d}: {repr(sheet)}"
    )
#########################################################
# SHEET 1
#########################################################

print("Extracting threats...")

df = load_sheet(
    xls,
    "Workshop Threats Analysis"
)

threats = []

for _, row in df.iterrows():

    threat_id = clean(row.iloc[0])

    if not threat_id.startswith("T-"):
        continue

    threats.append({

        "id": threat_id,

        "title_nl": clean(row.iloc[1]),

        "title": clean(row.iloc[2]),

        "category": clean(row.iloc[3]),

        "user": clean(row.iloc[4]),

        "vehicle": clean(row.iloc[5]),

        "infrastructure": clean(row.iloc[6]),

        "society": clean(row.iloc[7]),

        "primary_domain": clean(row.iloc[8]),

        "secondary_domain": clean(row.iloc[9]),

        "domain_rationale": clean(row.iloc[10]),

        "impact": clean(row.iloc[11]),

        "impact_rationale": clean(row.iloc[12]),

        "likelihood": clean(row.iloc[13]),

        "likelihood_rationale": clean(row.iloc[14]),

        "risk_level": clean(row.iloc[15]),

        "priority": clean(row.iloc[16]),

        "tags": clean(row.iloc[17]),

        "impact_score": clean(row.iloc[18]),

        "likelihood_score": clean(row.iloc[19]),

        "risk_rationale": clean(row.iloc[20]),

        "github_count": 0,
        "cve_count": 0,
        "paper_count": 0,
        "incident_count": 0,

        "repositories": [],
        "cves": [],
        "papers": [],
        "incidents": [],

        "evidence_score": 0,
        "evidence_strength": "Unknown"
    })

save_json(
    "threats.json",
    threats
)

#########################################################
# SHEET 2
#########################################################

print("Extracting categories...")

df = load_sheet(
    xls,
    "Category Definitions"
)

categories = []

for _, row in df.iterrows():

    categories.append({

        "id": clean(row.iloc[0]),

        "name": clean(row.iloc[1]),

        "threat_ids": [
            x.strip()
            for x in clean(row.iloc[2]).split(",")
            if x.strip()
        ]
    })

save_json(
    "categories.json",
    categories
)

#########################################################
# SHEET 3
#########################################################

print("Extracting methodology...")

methodology = pd.read_excel(
    EXCEL_FILE,
    sheet_name= find_sheet(xls,"Risk Matrix & Methodology" ),
    header=None 
)

risk_methodology = {

    "rows": methodology.fillna("").values.tolist()
}

save_json(
    "risk_methodology.json",
    risk_methodology
)

#########################################################
# SHEET 4
#########################################################

print("Extracting additional risks...")

df = load_sheet(
    xls,
    "Additional Risks added by RDW"
)

additional_risks = []

for _, row in df.iterrows():

    additional_risks.append({

        "id": clean(row.iloc[0]),

        "title": clean(row.iloc[1]),

        "coverage": clean(row.iloc[2])
    })

save_json(
    "additional_risks.json",
    additional_risks
)

#########################################################
# SHEET 5
#########################################################

print("Extracting taxonomy...")

df =load_sheet(
    xls,
    "Collated Threat Taxonomy"
)

taxonomy = []

for _, row in df.iterrows():

    threat_id = clean(row.iloc[0])

    if not threat_id.startswith("T-"):
        continue

    taxonomy.append({

        "id": threat_id,

        "source": clean(row.iloc[1]),

        "title": clean(row.iloc[2]),

        "category": clean(row.iloc[3]),

        "primary_domain": clean(row.iloc[4]),

        "secondary_domains": clean(row.iloc[5]),

        "description": clean(row.iloc[6]),

        "impact": clean(row.iloc[7]),

        "likelihood": clean(row.iloc[8]),

        "risk_score": clean(row.iloc[9]),

        "risk_rating": clean(row.iloc[10]),

        "impact_rationale": clean(row.iloc[11]),

        "comments": clean(row.iloc[12]),

        "physical_safety": clean(row.iloc[13]),

        "economic_impact": clean(row.iloc[14]),

        "social_impact": clean(row.iloc[15]),

        "territorial_impact": clean(row.iloc[16]),

        "combined_impact": clean(row.iloc[17]),

        "combined_impact_score": clean(row.iloc[18]),

        "likelihood_rationale": clean(row.iloc[19]),

        "combined_likelihood": clean(row.iloc[20]),

        "combined_likelihood_score": clean(row.iloc[21]),

        "combined_risk_score": clean(row.iloc[22]),

        "combined_rating": clean(row.iloc[23]),

        "additional_comments": clean(row.iloc[24])
    })

save_json(
    "taxonomy.json",
    taxonomy
)

#########################################################
# SHEET 6
#########################################################

print("Extracting frameworks...")

df = load_sheet(
    xls,
    "Framework References"
)

frameworks = df.fillna("").to_dict(
    orient="records"
)

save_json(
    "frameworks.json",
    frameworks
)

#########################################################
# SHEET 7
#########################################################

print("Extracting executive summary...")

summary = pd.read_excel(
    EXCEL_FILE,
    sheet_name=find_sheet(xls,"Executive Summary" ),
    header=None
)

summary_json = {

    "rows": summary.fillna("").values.tolist()
}

save_json(
    "summary.json",
    summary_json
)

#########################################################
# SHEET 8
#########################################################

print("Extracting future threats...")

df = load_sheet(
    xls,
    "Additional Threat Taxonomy"
)

future_threats = []

for _, row in df.iterrows():

    threat_id = clean(row.iloc[0])

    if not threat_id.startswith("T-"):
        continue

    future_threats.append({

        "id": threat_id,

        "title": clean(row.iloc[1]),

        "description": clean(row.iloc[2]),

        "domain": clean(row.iloc[3]),

        "threat_type": clean(row.iloc[4]),

        "category": clean(row.iloc[5]),

        "priority": clean(row.iloc[6]),

        "impact_class": clean(row.iloc[7]),

        "likelihood_class": clean(row.iloc[8]),

        "impact_score": clean(row.iloc[9]),

        "likelihood_score": clean(row.iloc[10]),

        "risk_score": clean(row.iloc[11]),

        "risk_rating": clean(row.iloc[12]),

        "relationship": clean(row.iloc[13]),

        "disposition": clean(row.iloc[14]),

        "assessment_question": clean(row.iloc[15]),

        "source1": clean(row.iloc[16]),

        "source2": clean(row.iloc[17]),

        "review_status": clean(row.iloc[18])
    })

save_json(
    "additional_taxonomy.json",
    future_threats
)

#########################################################
# MASTER JSON
#########################################################

master = {

    "threats": threats,

    "categories": categories,

    "taxonomy": taxonomy,

    "additional_risks": additional_risks,

    "frameworks": frameworks,

    "summary": summary_json,

    "future_threats": future_threats
}

save_json(
    "master.json",
    master
)

#########################################################

print(
    "\\nExtraction completed."
)

print(
    f"Threats: {len(threats)}"
)

print(
    f"Categories: {len(categories)}"
)

print(
    f"Taxonomy entries: {len(taxonomy)}"
)

print(
    f"Future threats: {len(future_threats)}"
)

print(
    f"Files written to: {DATA_DIR}"
)
