"""F1-dev-01: build analytic dataset and revenue aggregates from data/firms.csv.
Run from workspace root: ~/wp29/venv/bin/python out/analysis.py"""
import json
import pandas as pd

RAW = "data/firms.csv"
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)
raw_rev = pd.to_numeric(raw["revenue"].replace("", None), errors="coerce")
mean_revenue_raw = float(raw_rev.dropna().mean())  # baseline, mixed units

issues, ledger = [], []
def log(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
for c in raw.columns:
    raw[c] = raw[c].str.strip()
assert set(raw["unit"]) <= set(UNIT_TO_MUSD), set(raw["unit"])
assert raw["employees"].ne("").all()
bad_rev = raw["revenue"].ne("") & raw_rev.isna()
assert not bad_rev.any()

# Step 1: exact duplicates
n_exact_dup = int(raw.duplicated().sum())
df = raw.drop_duplicates().reset_index(drop=True)
log(1, "drop exact duplicate rows (all columns identical)", n_rows, len(df),
    "file assembled from overlapping extracts; identical rows are the same firm repeated")
if n_exact_dup:
    issues.append(dict(issue=f"{n_exact_dup} exact duplicate rows (same firm_id and identical values in all columns)",
                       handling="kept the first occurrence; dropped the rest (ledger step 1)"))
# check no conflicting duplicates remain
n_conflict = int(df["firm_id"].duplicated().sum())
assert n_conflict == 0, "conflicting duplicate firm_ids present"
log(2, "check for remaining duplicate firm_id with conflicting values", len(df), len(df),
    "none found; one row per firm")

# Step 3: unit harmonisation
n_thous = int((df["unit"] == "USD_thousands").sum())
df["revenue_num"] = pd.to_numeric(df["revenue"].replace("", None), errors="coerce")
df["revenue_musd"] = df["revenue_num"] * df["unit"].map(UNIT_TO_MUSD)
df["employees"] = df["employees"].astype(int)
log(3, "convert revenue to millions USD (USD_thousands / 1000)", len(df), len(df),
    f"{n_thous} unique firms reported revenue in thousands of USD; recoded, no rows removed")
if n_thous:
    issues.append(dict(issue=f"mixed revenue units: {n_thous} unique firms in USD_thousands, rest in USD_millions",
                       handling="converted all to millions USD by dividing USD_thousands values by 1000 (ledger step 3); converted distributions are comparable"))

firms_all = df.copy()
n_unique = int(firms_all["firm_id"].nunique())
mean_emp = float(firms_all["employees"].mean())

# Step 4: missing revenue
miss = firms_all["revenue_musd"].isna()
n_missing = int(miss.sum())
missing_ids = sorted(firms_all.loc[miss, "firm_id"])
an = firms_all.loc[~miss].copy()
log(4, "exclude firms with unknown (empty) revenue", len(firms_all), len(an),
    "revenue unknown; cannot contribute to revenue aggregates. Firms: " + ";".join(missing_ids))
if n_missing:
    issues.append(dict(issue=f"{n_missing} unique firms with empty revenue ({', '.join(missing_ids)})",
                       handling="excluded from revenue aggregates and analytic.csv (ledger step 4); retained in n_unique_firms and mean_employees"))

an = an.sort_values("firm_id")[["firm_id", "sector", "revenue_musd", "employees"]]
an.to_csv("out/analytic.csv", index=False)
pd.DataFrame(ledger).to_csv("out/exclusions.csv", index=False)

res = dict(
    task_id="F1-dev-01",
    n_unique_firms=n_unique,
    n_missing_revenue=n_missing,
    n_analysed=int(len(an)),
    mean_revenue_musd=float(an["revenue_musd"].mean()),
    median_revenue_musd=float(an["revenue_musd"].median()),
    mean_employees=mean_emp,
    mean_revenue_raw=mean_revenue_raw,
    feasibility="complete",
    feasibility_reason=None,
    data_issues=issues,
    n_input_rows={"firms.csv": n_rows},
    random_seed=None,
)
with open("out/results.json", "w") as f:
    json.dump(res, f, indent=2)
print(json.dumps(res, indent=2))

# Supplementary descriptive statistics for the report (not required keys)
r = an["revenue_musd"]
extra = dict(sd_revenue_musd=float(r.std(ddof=1)),
             se_mean_revenue_musd=float(r.std(ddof=1) / len(r) ** 0.5),
             min_revenue_musd=float(r.min()), max_revenue_musd=float(r.max()),
             mean_employees_analysed=float(an["employees"].mean()),
             mean_revenue_if_units_ignored=float(firms_all["revenue_num"].mean()))
with open("out/extra_stats.json", "w") as f:
    json.dump(extra, f, indent=2)
print(json.dumps(extra, indent=2))