"""F1-dev-01: build analytic firm dataset and revenue aggregates.
Run from workspace root: ~/wp29/venv/bin/python out/analysis.py
Reads only data/firms.csv; writes out/results.json, out/analytic.csv, out/exclusions.csv.
No randomness is used (random_seed = null)."""
import json, os
import numpy as np
import pandas as pd

RAW = "data/firms.csv"
OUT = "out"
UNIT_TO_MUSD = {"USD_millions": 1.0, "USD_thousands": 1e-3}

raw = pd.read_csv(RAW, dtype=str, keep_default_na=False)
n_rows = len(raw)

# Baseline: raw revenue column mean over non-empty cells, no cleaning, units as in file
raw_rev = pd.to_numeric(raw["revenue"].replace("", np.nan), errors="raise")
mean_revenue_raw = float(raw_rev.mean())

issues, ledger = [], []

def log_step(step, rule, before, after, rationale):
    ledger.append(dict(step=step, rule=rule, n_before=before, n_after=after,
                       n_removed=before - after, rationale=rationale))

# Sanity checks on the raw file
for c in raw.columns:
    assert (raw[c].str.strip() == raw[c]).all(), c
unknown_units = set(raw["unit"]) - set(UNIT_TO_MUSD)
assert not unknown_units, unknown_units
assert (raw["employees"] != "").all()

# Step 1: exact duplicate rows
df = raw.copy()
n_exact_dup = int(df.duplicated().sum())
df = df.drop_duplicates(keep="first").reset_index(drop=True)
log_step(1, "drop exact duplicate rows (all 5 columns identical); keep first occurrence",
         n_rows, len(df), "file assembled from several extracts; repeated identical records of the same firm")
if n_exact_dup:
    issues.append({"issue": f"{n_exact_dup} exact duplicate rows ({n_exact_dup} firms appear twice with identical values in all columns)",
                   "handling": "dropped the repeated rows, kept one row per firm (exclusion step 1)"})

# No conflicting duplicates may remain
assert int(df["firm_id"].duplicated().sum()) == 0, "conflicting duplicate firm_id records present"
n_unique_firms = int(raw["firm_id"].nunique())
assert len(df) == n_unique_firms

# Step 2: unit harmonisation to millions USD (no rows removed)
df["revenue_num"] = pd.to_numeric(df["revenue"].replace("", np.nan), errors="raise")
n_thousands = int((df["unit"] == "USD_thousands").sum())
df["revenue_musd"] = df["revenue_num"] * df["unit"].map(UNIT_TO_MUSD)
log_step(2, "recode revenue to millions USD: USD_thousands values divided by 1000; USD_millions unchanged",
         len(df), len(df), "revenue reported in mixed units; all outputs must be in millions USD (recode only)")
issues.append({"issue": f"mixed revenue units: {n_thousands} distinct firms report revenue in USD_thousands, the others in USD_millions",
               "handling": "converted to millions USD by dividing USD_thousands values by 1000 (exclusion step 2, recode, no rows removed)"})

# Employees as integer, all firms
df["employees"] = pd.to_numeric(df["employees"], errors="raise").astype(int)
assert (df["employees"] > 0).all()
mean_employees = float(df["employees"].mean())

# Step 3: missing revenue
miss = df["revenue_musd"].isna()
n_missing_revenue = int(miss.sum())
missing_ids = sorted(df.loc[miss, "firm_id"])
before = len(df)
ana = df.loc[~miss].copy()
log_step(3, "drop firms with empty (unknown) revenue: " + ";".join(missing_ids),
         before, len(ana), "revenue unknown; cannot contribute to revenue aggregates (task rule)")
if n_missing_revenue:
    issues.append({"issue": f"{n_missing_revenue} distinct firms with unknown (empty) revenue ({int(raw['revenue'].eq('').sum())} raw rows including duplicates): " + ", ".join(missing_ids),
                   "handling": "excluded from revenue aggregates and analytic.csv (exclusion step 3); kept for n_unique_firms and mean_employees"})

ana = ana.sort_values("firm_id")[["firm_id", "sector", "revenue_musd", "employees"]].reset_index(drop=True)
n_analysed = len(ana)

os.makedirs(OUT, exist_ok=True)
ana.to_csv(os.path.join(OUT, "analytic.csv"), index=False, float_format="%.6f")
pd.DataFrame(ledger).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)

sd = float(ana["revenue_musd"].std(ddof=1))
results = {
    "task_id": "F1-dev-01",
    "n_unique_firms": n_unique_firms,
    "n_missing_revenue": n_missing_revenue,
    "n_analysed": int(n_analysed),
    "mean_revenue_musd": float(ana["revenue_musd"].mean()),
    "median_revenue_musd": float(ana["revenue_musd"].median()),
    "mean_employees": mean_employees,
    "mean_revenue_raw": mean_revenue_raw,
    "feasibility": "complete",
    "feasibility_reason": None,
    "data_issues": issues,
    "n_input_rows": {"firms.csv": int(n_rows)},
    "random_seed": None,
    # supplementary descriptive keys
    "sd_revenue_musd": sd,
    "se_mean_revenue_musd": sd / float(np.sqrt(n_analysed)),
    "n_exact_duplicate_rows": n_exact_dup,
    "n_firms_usd_thousands": n_thousands,
    "mean_employees_analysed": float(ana["employees"].mean()),
}
with open(os.path.join(OUT, "results.json"), "w") as f:
    json.dump(results, f, indent=2)
print(json.dumps({k: v for k, v in results.items() if k != "data_issues"}, indent=2))