"""F1-dev-01: analytic firm dataset and revenue aggregates. Reads only data/firms.csv."""
import json, hashlib, os
import pandas as pd

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DATA = os.path.join(ROOT, "data", "firms.csv")
OUT = os.path.join(ROOT, "out")
UNIT_FACTOR = {"USD_millions": 1.0, "USD_thousands": 1e-3}

raw = pd.read_csv(DATA, dtype=str, keep_default_na=False)
n_rows = len(raw)
for c in raw.columns:
    raw[c] = raw[c].str.strip()
raw["revenue_num"] = pd.to_numeric(raw["revenue"].replace("", None), errors="coerce")
raw["employees"] = pd.to_numeric(raw["employees"], errors="raise").astype(int)

# Baseline: raw mean of revenue column as read (mixed units, duplicates included)
mean_revenue_raw = float(raw["revenue_num"].dropna().mean())

ledger, issues = [], []
def step(name, rule, before, after, why):
    ledger.append(dict(step=name, rule=rule, n_before=before, n_after=after,
                       n_removed=before - after, rationale=why))

step("0_read", "read all data rows of data/firms.csv", n_rows, n_rows, "starting row count")

# 1. exact duplicate rows
cols = ["firm_id", "sector", "revenue", "unit", "employees"]
n_exact_dup = int(raw.duplicated(subset=cols).sum())
d = raw.drop_duplicates(subset=cols, keep="first").copy()
step("1_dedup_exact", "drop rows identical on all columns to an earlier row (keep first)",
     n_rows, len(d), "assembly error: same firm appended more than once from overlapping extracts")
# any remaining conflicting duplicate firm_ids?
n_conflict = int(d["firm_id"].duplicated().sum())
if n_conflict:
    raise RuntimeError(f"{n_conflict} conflicting duplicate firm_id rows; handling not implemented")
issues.append({"issue": f"{n_exact_dup} exact duplicate rows (same firm_id and identical values); 0 conflicting duplicates",
               "handling": "kept first occurrence; one row per firm_id"})
firms = d  # one row per distinct firm
n_unique = int(firms["firm_id"].nunique())
mean_employees = float(firms["employees"].mean())

# 2. unit harmonisation
unknown_units = set(firms["unit"]) - set(UNIT_FACTOR)
if unknown_units:
    raise RuntimeError(f"unknown units {unknown_units}")
n_thou = int((firms["unit"] == "USD_thousands").sum())
firms["revenue_musd"] = firms["revenue_num"] * firms["unit"].map(UNIT_FACTOR)
step("2_unit_convert", "convert revenue to millions USD (USD_thousands / 1000; USD_millions unchanged)",
     len(firms), len(firms), f"mixed units across extracts; {n_thou} distinct firms recorded in USD_thousands (transformation, no rows removed)")
issues.append({"issue": f"mixed revenue units: {n_thou} distinct firms in USD_thousands, {len(firms)-n_thou} in USD_millions",
               "handling": "converted all revenue to millions USD via the row-level unit column"})

# 3. missing revenue
miss = firms["revenue_musd"].isna()
n_missing = int(miss.sum())
missing_ids = sorted(firms.loc[miss, "firm_id"].tolist())
analytic = firms.loc[~miss, ["firm_id", "sector", "revenue_musd", "employees"]].copy()
step("3_drop_missing_revenue", "drop firms with empty revenue: " + ";".join(missing_ids),
     len(firms), len(analytic), "revenue unknown; cannot contribute to revenue aggregates (employees still used for mean_employees)")
n_missing_rows_raw = int(raw["revenue_num"].isna().sum())
issues.append({"issue": f"revenue empty on {n_missing_rows_raw} raw rows = {n_missing} distinct firms ({', '.join(missing_ids)})",
               "handling": "excluded from revenue aggregates and analytic.csv; retained for n_unique_firms and mean_employees"})

analytic = analytic.sort_values("firm_id").reset_index(drop=True)
n_analysed = len(analytic)
assert n_rows - sum(r["n_removed"] for r in ledger) == n_analysed

res = {
    "task_id": "F1-dev-01",
    "n_unique_firms": n_unique,
    "n_missing_revenue": n_missing,
    "n_analysed": n_analysed,
    "mean_revenue_musd": round(float(analytic["revenue_musd"].mean()), 10),
    "median_revenue_musd": round(float(analytic["revenue_musd"].median()), 10),
    "mean_employees": round(mean_employees, 10),
    "mean_revenue_raw": round(mean_revenue_raw, 10),
    "feasibility": "complete",
    "feasibility_reason": None,
    "data_issues": issues,
    "n_input_rows": {"firms.csv": n_rows},
    "random_seed": None,
    "input_sha256": {"firms.csv": hashlib.sha256(open(DATA, "rb").read()).hexdigest()},
    "baseline_note": "mean_revenue_raw mixes USD_thousands and USD_millions and double-counts duplicates; not interpretable",
}
os.makedirs(OUT, exist_ok=True)
analytic.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)
with open(os.path.join(OUT, "results.json"), "w") as f:
    json.dump(res, f, indent=2)
print(json.dumps({k: res[k] for k in ["n_unique_firms","n_missing_revenue","n_analysed",
      "mean_revenue_musd","median_revenue_musd","mean_employees","mean_revenue_raw"]}, indent=2))