"""F1-dev-01: build the analytic firm dataset and revenue aggregates.
Run from workspace root: ~/wp29/venv/bin/python out/analysis.py
Reads only data/firms.csv; deterministic (no randomness)."""
import json, hashlib
from pathlib import Path
import pandas as pd

ROOT = Path(__file__).resolve().parent.parent
RAW = ROOT / "data" / "firms.csv"
OUT = ROOT / "out"
UNIT_TO_MUSD = {"USD_millions": 1.0, "USD_thousands": 1e-3}

raw = pd.read_csv(RAW, dtype={"firm_id": str, "sector": str, "unit": str})
n_rows = len(raw)
mean_revenue_raw = float(raw["revenue"].mean())  # baseline: non-empty values as read, no cleaning

issues, ledger = [], []
def log(step, rule, nb, na, why):
    ledger.append(dict(step=step, rule=rule, n_before=nb, n_after=na, n_removed=nb - na, rationale=why))

# Step 1: exact duplicate rows
n_dup_id = int(raw["firm_id"].duplicated().sum())
d = raw.drop_duplicates().reset_index(drop=True)
n_exact = n_rows - len(d)
log(1, "drop exact duplicate rows (all columns identical)", n_rows, len(d),
    "file assembled from several extracts; identical repeated rows are assembly errors")
issues.append({"issue": f"{n_exact} exact duplicate rows ({n_dup_id} repeated firm_id rows in total)",
               "handling": "kept first occurrence of each identical row"})
# Any remaining conflicting duplicate firm_ids would make the firm ambiguous
conflict = d["firm_id"].duplicated(keep=False)
if conflict.any():
    raise SystemExit(f"conflicting duplicates for {d.loc[conflict,'firm_id'].nunique()} firms; resolve manually")
n_unique = int(d["firm_id"].nunique())
assert n_unique == raw["firm_id"].nunique() == len(d)

# Step 2: unit harmonisation (no rows removed)
unknown_units = set(d["unit"].unique()) - set(UNIT_TO_MUSD)
if unknown_units:
    raise SystemExit(f"unknown units {unknown_units}")
n_thousands = int((d["unit"] == "USD_thousands").sum())
d["revenue_musd"] = d["revenue"] * d["unit"].map(UNIT_TO_MUSD)
log(2, "convert revenue to millions USD (USD_thousands /1000; USD_millions unchanged)", len(d), len(d),
    "mixed currency units across extracts; outputs must be in millions USD")
issues.append({"issue": f"mixed revenue units: {n_thousands} distinct firms report revenue in USD_thousands, rest in USD_millions",
               "handling": "converted all revenue to millions USD using the per-row unit column"})

# Employees over all distinct firms (before dropping missing revenue)
mean_employees = float(d["employees"].mean())

# Step 3: drop firms with unknown revenue
miss = d["revenue_musd"].isna()
n_missing = int(miss.sum())
missing_ids = sorted(d.loc[miss, "firm_id"])
a = d.loc[~miss].copy()
log(3, "drop firms with empty revenue: " + ";".join(missing_ids), len(d), len(a),
    "revenue unknown; cannot contribute to revenue aggregates (still counted in mean_employees)")
issues.append({"issue": f"{n_missing} distinct firms with empty revenue ({int(raw['revenue'].isna().sum())} raw rows incl. duplicates)",
               "handling": "excluded from revenue aggregates and analytic.csv; retained for n_unique_firms and mean_employees"})

analytic = a[["firm_id", "sector", "revenue_musd", "employees"]].sort_values("firm_id").reset_index(drop=True)
analytic.to_csv(OUT / "analytic.csv", index=False)
pd.DataFrame(ledger).to_csv(OUT / "exclusions.csv", index=False)

res = {
    "task_id": "F1-dev-01",
    "n_unique_firms": n_unique,
    "n_missing_revenue": n_missing,
    "n_analysed": int(len(analytic)),
    "mean_revenue_musd": round(float(analytic["revenue_musd"].mean()), 6),
    "median_revenue_musd": round(float(analytic["revenue_musd"].median()), 6),
    "mean_employees": round(mean_employees, 6),
    "mean_revenue_raw": round(mean_revenue_raw, 6),
    "feasibility": "complete",
    "feasibility_reason": None,
    "data_issues": issues,
    "n_input_rows": {"firms.csv": int(n_rows)},
    "random_seed": None,
    "input_sha256": {"firms.csv": hashlib.sha256(RAW.read_bytes()).hexdigest()},
    "sd_revenue_musd": round(float(analytic["revenue_musd"].std(ddof=1)), 6),
    "se_mean_revenue_musd": round(float(analytic["revenue_musd"].std(ddof=1) / len(analytic) ** 0.5), 6),
    "n_thousands_unit_firms": n_thousands,
    "n_exact_duplicate_rows": int(n_exact),
}
(OUT / "results.json").write_text(json.dumps(res, indent=2) + "\n")
print(json.dumps({k: v for k, v in res.items() if k != "data_issues"}, indent=2))