"""
04-01  Survey Data Organizer & Codebook
Chapters 01-01, 01-02

Run from the project folder:   python python/analysis.py
"""
import numpy as np
import pandas as pd

pd.set_option("display.width", 110)
pd.set_option("display.max_columns", 30)

raw = pd.read_csv("data/survey_raw.csv", keep_default_na=True)

# ============================================================================
print("\n===== 1. QUALITY REPORT (before any changes) =====\n")

print(f"Rows:          {len(raw)}")
print(f"Columns:       {raw.shape[1]}")

dup_mask = raw["student_id"].duplicated(keep=False)
print(f"Duplicate IDs: {raw['student_id'].duplicated().sum()}")
if dup_mask.any():
    print(raw.loc[dup_mask, ["student_id", "gender", "major"]].to_string(index=False))

print("\nMissing values per column:")
print(raw.isna().sum())

as_num = pd.to_numeric(raw["study_hours"], errors="coerce")
print(f"\nstudy_hours cells that are NOT numeric: {(as_num.isna() & raw['study_hours'].notna()).sum()}")

print("\nDistinct raw categories:")
print(raw["gender"].value_counts(dropna=False))
print(raw["major"].value_counts(dropna=False))

mid = pd.to_numeric(raw["midterm"], errors="coerce")
print(f"\nRange checks:")
print(f"  age     {raw['age'].min()} to {raw['age'].max()}")
print(f"  midterm {mid.min()} to {mid.max()}"
      + ("   <-- IMPOSSIBLE" if mid.max() > 100 else ""))

# ============================================================================
print("\n===== 2. CLEAN =====\n")

clean = raw.copy()

# ── the duplicated key: keep the FIRST occurrence, and say so ───────────────
dups = clean["student_id"].duplicated()
if dups.any():
    print(f"Removing {dups.sum()} duplicate row(s): "
          f"{', '.join(clean.loc[dups, 'student_id'])}")
    clean = clean[~dups].copy()

# ── gender: 10 raw spellings -> exactly F / M / X ───────────────────────────
g = clean["gender"].fillna("").astype(str).str.strip().str.upper().str[:1]
clean["gender"] = pd.Categorical(g.where(g.isin(["F", "M"]), "X"),
                                 categories=["F", "M", "X"])

# ── major: trim, then title-case ────────────────────────────────────────────
clean["major"] = clean["major"].str.strip().str.title().astype("category")

# ── study_hours: strip any non-numeric characters, then coerce ──────────────
clean["study_hours"] = pd.to_numeric(
    clean["study_hours"].astype(str).str.replace(r"[^0-9.]", "", regex=True),
    errors="coerce")

# ── midterm: void the impossible value rather than guessing at it ───────────
clean["midterm"] = pd.to_numeric(clean["midterm"], errors="coerce")
bad = clean["midterm"].notna() & ((clean["midterm"] > 100) | (clean["midterm"] < 0))
if bad.any():
    print(f"Voiding {bad.sum()} impossible midterm value(s): "
          f"{list(clean.loc[bad, 'midterm'])}")
    clean.loc[bad, "midterm"] = np.nan

# ── satisfaction: ORDINAL, never numeric ────────────────────────────────────
clean["satisfaction"] = pd.Categorical(clean["satisfaction"],
                                       categories=[1, 2, 3, 4, 5], ordered=True)

# ============================================================================
print("\n===== 3. DERIVE =====\n")

clean["female"] = (clean["gender"] == "F").astype(int)
clean["age_group"] = pd.cut(clean["age"],
                            bins=[-np.inf, 25, 40, np.inf],
                            labels=["Under 25", "25-39", "40+"],
                            right=False)

major_dummies = pd.get_dummies(clean["major"], prefix="major", drop_first=True)
print(f"Major dummies (reference = {clean['major'].cat.categories[0]}): "
      f"{', '.join(major_dummies.columns)}")

print()
print(clean.dtypes)

# ============================================================================
print("\n===== 4. SUMMARY (respecting the measurement levels) =====\n")

print("Quantitative columns — mean is legal:")
print(clean[["age", "study_hours", "midterm"]].describe().round(2))

print("\nNominal columns — frequencies only:")
print(clean["gender"].value_counts())
print(clean["major"].value_counts())

print("\nOrdinal column — median and frequencies, NOT a mean:")
print(clean["satisfaction"].value_counts().sort_index())
print(f"median satisfaction: {clean['satisfaction'].dropna().median()}")

print("\nStudy hours by major:")
print(clean.groupby("major", observed=True)["study_hours"]
      .agg(["count", "mean", "std"]).round(2))

# ============================================================================
print("\n===== 5. CODEBOOK =====\n")

codebook = pd.DataFrame({
    "variable": ["student_id", "gender", "major", "age",
                 "study_hours", "midterm", "satisfaction"],
    "type": ["text", "category", "category", "int",
             "float", "float", "ordered category"],
    "level": ["nominal", "nominal", "nominal", "ratio",
              "ratio", "ratio", "ORDINAL"],
    "allowed": ["S001-S060", "F, M, X",
                ", ".join(clean["major"].cat.categories),
                "15-100", "0-40", "0-100", "1-5"],
    "notes": ["unique key; 1 duplicate removed",
              "normalized from 10 raw spellings",
              "trimmed and title-cased",
              "-", "text suffixes stripped",
              "blank = absent; >100 voided",
              "DO NOT AVERAGE - report the median"],
})
print(codebook.to_string(index=False))

# clean.to_csv("data/survey_clean.csv", index=False)
# codebook.to_csv("data/codebook.csv", index=False)
print("\nDone. Uncomment the to_csv lines to save the cleaned outputs.")