#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Kobo-to-Report Kit — Demo (FICTITIOUS DATA)
==========================================
End-to-end pipeline: a fictional KoboToolbox/ODK survey export
  -> cleaned dataset  -> pivot tables  -> 2-page summary report.

Run:  python kobo_to_report.py
Requires: pandas (no other dependency). Produces:
  rapport_exemple.txt, pivot_village_fcs.csv, pivot_water_source.csv

Author: Ahadi Jean Cyrille Dahani — humanitarian analyst (portfolio demo).
"""
import pandas as pd

SRC = "survey_data.csv"
REPORT = "rapport_exemple.txt"

# ------------------------------------------------------------------ 1. Load
def load(path):
    """Read the KoboToolbox CSV export exactly as it comes from the field."""
    df = pd.read_csv(path)
    print(f"[1/5] Loaded {len(df)} raw rows, {df.shape[1]} columns (fictional demo).")
    return df

# ------------------------------------------------------- 2. Clean & standardise
def clean(df):
    """Flatten Kobo group names, fix village casing, coerce types, handle N/A."""
    df = df.copy()
    # a) Kobo column names contain '/' (group hierarchy) -> flatten for analysis
    df.columns = [c.replace("/", "_") for c in df.columns]

    # b) Village labels come with stray whitespace and mixed casing
    df["group_hh_village"] = (
        df["group_hh_village"].astype(str).str.strip().str.title()
    )

    # c) Water source / treatment / latrine: same standardisation
    for col in ["group_wash_water_source", "group_wash_water_treatment",
                "group_wash_latrine_type", "assist_received"]:
        df[col] = df[col].astype(str).str.strip().str.title().replace({"N/A": pd.NA})

    # d) Duplicates: same household submitted twice (fictional artefact)
    before = len(df)
    df = df.drop_duplicates(subset=df.columns.difference(["_index", "start"]))
    print(f"[2/5] Cleaning: {before - len(df)} duplicate row(s) removed; "
          f"labels standardised; 'N/A' -> missing.")

    # e) Numeric coercion + missing handling
    num_cols = [c for c in df.columns if c.startswith("group_food") or
                c in ("group_hh_household_size",)]
    for c in num_cols:
        df[c] = pd.to_numeric(df[c], errors="coerce")
    return df

# --------------------------------------------------------- 3. Derive indicators
def derive(df):
    """Food Consumption Score (WFP standard weights) + FCS categories."""
    w = {"fcs_staple": 2, "fcs_pulse": 3, "fcs_veg": 1, "fcs_fruit": 1,
         "fcs_meat": 4, "fcs_dairy": 4, "fcs_oil": 0.5, "fcs_sugar": 0.5}
    df["FCS"] = sum(df[f"group_food_{k}"].fillna(0) * x for k, x in w.items())
    df["FCS_category"] = pd.cut(df["FCS"], bins=[-1, 21, 35, 10**6],
                                labels=["Poor", "Borderline", "Acceptable"])
    print(f"[3/5] Derived FCS (WFP weights) and categories: "
          f"Poor {int((df.FCS_category=='Poor').sum())} · "
          f"Borderline {int((df.FCS_category=='Borderline').sum())} · "
          f"Acceptable {int((df.FCS_category=='Acceptable').sum())} households.")
    return df

# ------------------------------------------------------------- 4. Pivot tables
def pivots(df):
    p1 = (df.pivot_table(index="group_hh_village", columns="FCS_category",
                         values="_index", aggfunc="count", fill_value=0, observed=False)
            .assign(Total=lambda x: x.sum(axis=1)).sort_values("Total", ascending=False))
    p2 = (df["group_wash_water_source"].value_counts(dropna=False)
            .rename("households").to_frame())
    p1.to_csv("pivot_village_fcs.csv")
    p2.to_csv("pivot_water_source.csv")
    print(f"[4/5] Pivots written: pivot_village_fcs.csv, pivot_water_source.csv")
    return p1, p2

# ------------------------------------------------------------- 5. Report writer
def write_report(df, p1, p2):
    n = len(df)
    poor = int((df.FCS_category == "Poor").sum())
    borderline = int((df.FCS_category == "Borderline").sum())
    open_well = int(df.group_wash_water_source.eq("Open Well").sum())
    top_v = p1.index[0]
    worst_share = p1.loc[top_v, ["Poor", "Borderline"]].sum() / p1.loc[top_v, "Total"]
    lines = []
    A = lines.append
    A("=" * 74)
    A("HOUSEHOLD FOOD SECURITY & WASH — SUMMARY REPORT")
    A("Fictional survey · Kobo-to-Report pipeline demo · ALL DATA FICTITIOUS")
    A("=" * 74)
    A("")
    A("BLUF — Households surveyed in five villages show a moderate food")
    A("security situation overall, with a concentrated pocket of poor")
    A("consumption in the largest village. Open wells remain the main water")
    A("source; treatment practices are uneven. Findings below are illustrative")
    A("(fictional data) and demonstrate the full chain: collect -> clean ->")
    A("analyse -> deliver.")
    A("")
    A("1. KEY FIGURES")
    A(f"   - Households surveyed: {n} (after duplicate removal and cleaning)")
    A(f"   - Food Consumption Score: {poor} Poor ({poor/n:.0%}) · "
      f"{borderline} Borderline ({borderline/n:.0%}) · "
      f"{n-poor-borderline} Acceptable ({(n-poor-borderline)/n:.0%})")
    A(f"   - Main water source: Open Well — {open_well} households ({open_well/n:.0%})")
    A(f"   - Mean Coping Strategy Index: {df.group_food_csi_count.mean():.1f} (range 0–12)")
    A("")
    A("2. FOOD SECURITY BY VILLAGE (top of table)")
    for v, row in p1.iterrows():
        A(f"   {v:<12} n={int(row.Total):<3} Poor={int(row.get('Poor',0)):<3} "
          f"Borderline={int(row.get('Borderline',0)):<3} Acceptable={int(row.get('Acceptable',0))}")
    A(f"   => Attention: {top_v} shows the highest share of Poor+Borderline "
      f"({worst_share:.0%} of its households).")
    A("")
    A("3. WASH SNAPSHOT")
    for src, row in p2.iterrows():
        A(f"   - {str(src):<12} {int(row.households)} households ({row.households/n:.0%})")
    A(f"   - No water treatment reported: "
      f"{int(df.group_wash_water_treatment.isna().sum() + df.group_wash_water_treatment.eq('None').sum())} households")
    A("")
    A("4. METHOD")
    A("   - Source: fictional KoboToolbox export (survey_data.csv, 55 raw rows).")
    A("   - Cleaning: label standardisation (village casing, water/treatment")
    A("     categories), duplicate removal, 'N/A' -> missing, numeric coercion.")
    A("   - FCS computed with WFP standard weights (staple x2, pulse x3,")
    A("     meat/dairy x4, veg/fruit x1, oil/sugar x0.5); thresholds:")
    A("     Poor <=21, Borderline 21.5-35, Acceptable >35.")
    A("")
    A("5. DATA QUALITY NOTES")
    A("   - 1 duplicate submission removed (identical household, distinct index).")
    A("   - 3 missing values coerced (FCS staple, water treatment, CSI).")
    A("   - Village labels required case/whitespace standardisation.")
    A("")
    A("6. LIMITS")
    A("   - FICTITIOUS data: no inference for any real location or population.")
    A("   - Convenience sample, not representative; no weighting applied.")
    A("   - FCS self-reported; no triangulation with consumption recall.")
    A("")
    A("-" * 74)
    A("Pipeline: kobo_to_report.py -> pivot CSVs -> this report. ")
    A("Author: Ahadi Jean Cyrille Dahani — humanitarian analyst, Ouagadougou.")
    A("=" * 74)
    text = "\n".join(lines)
    with open(REPORT, "w") as f:
        f.write(text)
    print(f"[5/5] Report written: {REPORT} ({len(text)} chars)")

if __name__ == "__main__":
    df = load(SRC)
    df = clean(df)
    df = derive(df)
    p1, p2 = pivots(df)
    write_report(df, p1, p2)
    print("Done — collect -> analyse -> deliver, fully reproducible.")