from __future__ import annotations          
from math import sqrt




import tkinter as tk

import unicodedata
import hashlib
import random
from tkinter import filedialog, messagebox, ttk


import logging, re, string, traceback
from pathlib import Path
from typing import Dict, List, Optional
from zipfile import BadZipFile

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import statsmodels.api as sm
from openpyxl import load_workbook
from statsmodels.formula.api import ols
from scipy import stats

try:
    from scipy.stats import gaussian_kde
except ImportError:
    gaussian_kde = None


from sklearn.decomposition import PCA
from sklearn.manifold import MDS


TIMES   = {"ENTRATA": "pre", "USCITA": "post"}
GROUPS  = ["SI", "NO"]          

EXCEL_LEVELS = ["A1", "A2", "B3", "B4", "C5", "C6", "D7", "D8"]
CANON_LEVELS = ["A1", "A2", "B1", "B2", "C1", "C2", "D1", "D2"]
LEVEL_MAP    = dict(zip(EXCEL_LEVELS, CANON_LEVELS))
LEVEL_NUM    = {lvl: i + 1 for i, lvl in enumerate(CANON_LEVELS)}
TICKS        = list(range(1, 9))
TICKLAB      = CANON_LEVELS

COMP_NAMES = {
    "1.1": "Partecipazione Digitale",
    "1.2": "Gestione dell'identità online",
    "1.3": "Privacy e protezione dei dati",
    "1.4": "Impatto ambientale delle tecnologie digitali",
    "2.1": "Valori fondamentali",
    "2.2": "Atteggiamenti e responsabilità",
    "2.3": "Empatia e tolleranza",
    "2.4": "Etica e Sostenibilità",
    "2.5": "Collaborazione e cooperazione",
    "3.1": "Pensiero critico e comprensione",
    "3.2": "Visione e creatività verso il futuro",
    "3.3": "Iniziativa civica individuale"
}
def nice(code: str) -> str:
    return COMP_NAMES.get(code, code)



def _deterministic_jitter(school: str, lo: float = -0.015, hi: float = 0.015, min_abs: float = 0.006) -> float:
    
    h = hashlib.md5(school.encode("utf-8")).hexdigest()
    seed = int(h[:8], 16)
    rng = random.Random(seed)
    hi_abs = max(abs(lo), abs(hi))
    min_abs = max(0.0, min(min_abs, hi_abs))
    sign = -1.0 if rng.random() < 0.5 else 1.0
    mag = rng.uniform(min_abs if min_abs > 0 else 1e-9, hi_abs)
    v = sign * mag
    if v == 0.0:
        v = sign * max(1e-9, min_abs)
    return v


CELL_MAP: Dict[tuple[str, str], str] = {
    ("Cittadinanza Digitale", "1.1"): "B4",
    ("Cittadinanza Digitale", "1.2"): "B6",
    ("Cittadinanza Digitale", "1.3"): "B8",
    ("Cittadinanza Digitale", "1.4"): "B10",
    ("Valori e atteggiamenti sociali", "2.1"): "B14",
    ("Valori e atteggiamenti sociali", "2.2"): "B16",
    ("Valori e atteggiamenti sociali", "2.3"): "B18",
    ("Valori e atteggiamenti sociali", "2.4"): "B20",
    ("Valori e atteggiamenti sociali", "2.5"): "B22",
    ("Abilità e impegno civico", "3.1"): "B26",
    ("Abilità e impegno civico", "3.2"): "B28",
    ("Abilità e impegno civico", "3.3"): "B30",
}

_TMP  = re.compile(r"^~\$")          



def cell_to_rc(addr: str) -> tuple[int, int]:
    col = string.ascii_uppercase.index(addr[0])
    row = int(addr[1:]) - 1
    return row, col


def _strip_diacritics(s: str) -> str:
    
    return "".join(ch for ch in unicodedata.normalize("NFKD", s) if not unicodedata.combining(ch))

_CODE_END_ANY = re.compile(r"(\d{1,4})(?:\s*\(\d+\))?\s*$")

_CODE_LEAD_ANY = re.compile(r"^\s*(\d{1,4})(?=\D|$)")

_CODE_AFTER_GROUP_ANY = re.compile(r"(?:\bSi\b|\bNo\b|__Si|__No|_Si_|_No_|_Si|_No|\bSI\b|\bNO\b)\D*(\d{1,4})(?=\D|$)", re.IGNORECASE)

_CODE_ANY = re.compile(r"(?:^|\D)(\d{1,4})(?=\D|$)")


def extract_code(stem_or_filename: str):
    
    s = str(stem_or_filename)

    
    sl = s.lower()
    for _ in range(3):
        if sl.endswith('.xlsx') or sl.endswith('.xlsm'):
            s = s[:-5]
        elif sl.endswith('.xls'):
            s = s[:-4]
        elif sl.endswith('.ods'):
            s = s[:-4]
        else:
            break
        sl = s.lower()

    s = _strip_diacritics(s)

    m = _CODE_AFTER_GROUP_ANY.search(s)
    if m:
        return m.group(1)
    m = _CODE_END_ANY.search(s)
    if m:
        return m.group(1)
    m = _CODE_LEAD_ANY.search(s)
    if m:
        return m.group(1)
    all_codes = _CODE_ANY.findall(s)
    if all_codes:
        return all_codes[-1]
    return None


def normalize_student_code(x):
    
    if x is None:
        return ""
    try:
        import math
        if isinstance(x, float) and math.isnan(x):
            return ""
    except Exception:
        pass
    s = str(x).strip()
    if not s:
        return ""
    sl = s.lower()
    if sl in {"nan", "none", "null"}:
        return ""
    
    if re.fullmatch(r"\d+\.0+", s):
        s = s.split(".", 1)[0]
    
    if re.fullmatch(r"\d+", s):
        try:
            s = str(int(s))
        except Exception:
            pass
    return s
class Analyzer:
    
    def __init__(self, root: Path, out: Path, log: logging.Logger):
        self.root = root
        self.out  = out
        self.log  = log
        
        self.skipped: list[dict] = []
        self.bad_vals: list[str] = []
        self.rec: List[Dict] = []
        self.err_files = 0
        self.bad_vals  = []

    
    def _schools(self) -> List[str]:
        
        s = set()
        for folder in ("ENTRATA", "USCITA"):
            base = self.root / folder
            if base.exists():
                s.update(d.name for d in base.iterdir() if d.is_dir())
        return sorted(s)

    
    def parse(self) -> None:
        
        for folder, tlabel in TIMES.items():
            base = self.root / folder
            if not base.exists():
                continue
            for school in self._schools():
                gbase = base / school
                if not gbase.exists():
                    continue
                for grp in GROUPS:
                    gdir = gbase / grp
                    if not gdir.exists():
                        continue
                    for xl in gdir.glob("*.*"):
                        
                        if _TMP.match(xl.name) or xl.name.startswith(".") or xl.name.lower() == ".ds_store":
                            continue
                        self._parse_file(xl, school, grp, tlabel)

        if not self.rec:
            raise RuntimeError("Nessun dato valido trovato.")

        
        self.df = pd.DataFrame(self.rec)

    
    def _parse_file(self, xl: Path, school: str, grp: str, tlabel: str):
        
        try:
            reasons: list[str] = []
            added_before = len(self.rec)
            ext = xl.suffix.lower()
            if ext in {".xlsx", ".xlsm", ".xltx", ".xltm"}:
                ws = load_workbook(xl, data_only=True, read_only=True).worksheets[1]
                get = lambda addr: ws[addr].value
            elif ext == ".ods":
                df = pd.read_excel(xl, header=None, engine="odf", sheet_name=1)
                get = lambda addr: df.iat[cell_to_rc(addr)[0], cell_to_rc(addr)[1]]
            else:
                self.log.error("Formato non supportato: %s", xl)
                self.err_files += 1
                self.skipped.append(dict(school=school, group=grp, student="", file=xl.name, reason="Formato non supportato"))
                return

            code = extract_code(xl.name)  
            b3 = normalize_student_code(get("B3"))
            if b3 != "":
                code = b3
            if not code:
                
                self.skipped.append(dict(school=school, group=grp, student="", file=xl.name,
                                         reason="Codice studente mancante (né nel filename né in B3): file escluso"))
                return

            for (area, comp), cell in CELL_MAP.items():
                raw = str(get(cell)).strip()
                if not raw or raw == "None":
                    reasons.append(f"Vuoto in {cell} [{area} {comp}]")
                    continue
                if raw not in LEVEL_MAP:
                    self.bad_vals.append(f"{raw} in {xl.name} ({cell})")
                    reasons.append(f"Valore non riconosciuto '{raw}' in {cell} [{area} {comp}]")
                    continue
                canon = LEVEL_MAP[raw]
                self.rec.append(dict(
                    school=school,
                    group=grp,
                    student=code,
                    time=tlabel,
                    area=area,
                    competence=comp,
                    level=canon,
                    score=LEVEL_NUM[canon],
                ))
            
            added_after = len(self.rec)
            if added_after == added_before:
                self.skipped.append(dict(school=school, group=grp, student=code or "", file=xl.name, reason="; ".join(sorted(set(reasons))) or "Nessun valore valido"))
        except BadZipFile:
            self.log.error("File corrotto: %s", xl)
            self.err_files += 1
            self.skipped.append(dict(school=school, group=grp, student="", file=xl.name, reason="File corrotto (BadZipFile)"))
        except Exception as exc:
            self.log.error("Errore su %s → %s", xl, exc, exc_info=True)
            self.err_files += 1
            self.skipped.append(dict(school=school, group=grp, student="", file=xl.name, reason=f"Eccezione: {exc}"))

    
    def export_csv(self) -> Path:
        p = self.out / "riepilogo_valutazioni.csv"
        self.df.to_csv(p, index=False)
        return p

        
    def export_student_summary(self) -> Path:
        
        id_cols = ["school", "group", "student"]          

        pre  = self.df[self.df.time == "pre"]
        post = self.df[self.df.time == "post"]

        
        
        pre_w  = pre .pivot_table(index=id_cols, columns="competence",
                                  values="score", aggfunc="mean")
        post_w = post.pivot_table(index=id_cols, columns="competence",
                                  values="score", aggfunc="mean")

        
        delta_w = post_w - pre_w

        
        pre_comp   = pre_w  .add_prefix("pre_")
        post_comp  = post_w .add_prefix("post_")
        delta_comp = delta_w.add_prefix("delta_")
        
        
        pre_a  = pre .groupby(id_cols + ["area"]).score.mean().unstack()
        post_a = post.groupby(id_cols + ["area"]).score.mean().unstack()
        delta_a = post_a - pre_a

        pre_area   = pre_a  .add_prefix("pre_area_")
        post_area  = post_a .add_prefix("post_area_")
        delta_area = delta_a.add_prefix("delta_area_")

        
        pre_g   = pre .groupby(id_cols).score.mean()
        post_g  = post.groupby(id_cols).score.mean()
        delta_g = post_g - pre_g

        pre_glob   = pre_g  .rename("pre_global")
        post_glob  = post_g .rename("post_global")
        delta_glob = delta_g.rename("delta_global")

        
        summary = (
            pd.concat([pre_comp, post_comp, delta_comp,
                       pre_area, post_area, delta_area,
                       pre_glob, post_glob, delta_glob], axis=1)
              .reset_index()                        
              .sort_values(["school", "group", "student"])
        )

        out_file = self.out / "riepilogo_studenti.csv"
        summary.to_csv(out_file, index=False, encoding="utf-8")
        return out_file
    def export_group_means(self) -> Path:
        
        out_file = self.out / "means_pre_post_delta_by_group.csv"

        df = self.df.copy()
        if df is None or df.empty:
            pd.DataFrame(columns=["Measure","Group","N","Pre_M","Post_M","Delta_M"]).to_csv(out_file, index=False)
            return out_file

        def group_label(g) -> str:
            s = str(g).strip().upper()
            if s in ("SI", "TRUE", "ETW", "ETWINNING", "E-TWINNING", "1"):
                return "eTwinning"
            if s in ("NO", "FALSE", "CTRL", "CONTROL", "0"):
                return "Control"
            return str(g)

        def _digits(x: str) -> str:
            return "".join(ch for ch in str(x) if ch.isdigit())

        def comp_sort_key(code: str):
            try:
                a, b = str(code).split(".", 1)
                bdig = _digits(b)
                return (int(_digits(a) or "0"), int(bdig or "0"))
            except Exception:
                return (10**9, 10**9)

        
        area_to_comps = {}
        for area, sub in df.dropna(subset=["area","competence"]).groupby("area", dropna=False):
            comps = sorted([c for c in sub["competence"].dropna().unique().tolist()], key=comp_sort_key)
            area_to_comps[area] = comps

        def area_sort_key(area):
            comps = area_to_comps.get(area, [])
            if comps:
                return comp_sort_key(comps[0])[0]
            return 10**9

        areas = sorted([a for a in df["area"].dropna().unique().tolist()], key=area_sort_key)

        
        measures = [("overall", None, None)]
        for a in areas:
            measures.append(("area", a, None))
            for c in area_to_comps.get(a, []):
                measures.append(("competence", a, c))

        id_cols = ["school", "group", "student"]
        rec = []

        
        raw_groups = df["group"].astype(str).unique().tolist()
        if set(["SI","NO"]).issubset(set(raw_groups)):
            present_groups = ["SI","NO"]
        else:
            
            present_groups = [g for g in GROUPS if str(g) in raw_groups] or sorted(raw_groups)

        for level, area, comp in measures:
            if level == "overall":
                sub = df
                measure_label = "Key competence (overall)"
            elif level == "area":
                sub = df[df["area"] == area]
                measure_label = f"Area: {area}"
            else:
                sub = df[(df["area"] == area) & (df["competence"] == comp)]
                measure_label = f"Specific competence: {nice(str(comp))}"

            for g in present_groups:
                subg = sub[sub["group"].astype(str) == str(g)]
                pre  = subg[subg["time"] == "pre" ].groupby(id_cols)["score"].mean()
                post = subg[subg["time"] == "post"].groupby(id_cols)["score"].mean()

                common = pre.index.intersection(post.index)
                n = int(len(common))

                if n == 0:
                    pre_m = post_m = delta_m = np.nan
                else:
                    pre2  = pre.loc[common].astype(float)
                    post2 = post.loc[common].astype(float)
                    delta = (post2 - pre2)
                    pre_m   = float(pre2.mean())
                    post_m  = float(post2.mean())
                    delta_m = float(delta.mean())

                rec.append([measure_label, group_label(g), n, pre_m, post_m, delta_m])

        out_df = pd.DataFrame(rec, columns=["Measure","Group","N","Pre_M","Post_M","Delta_M"])

        
        group_order = {"eTwinning": 0, "Control": 1}
        measure_order = {m: i for i, m in enumerate(out_df["Measure"].drop_duplicates().tolist())}
        out_df["_mpos"] = out_df["Measure"].map(measure_order).fillna(10**9).astype(int)
        out_df["_gpos"] = out_df["Group"].map(group_order).fillna(9).astype(int)
        out_df = out_df.sort_values(["_mpos", "_gpos"]).drop(columns=["_mpos","_gpos"])

        out_df.to_csv(out_file, index=False)
        return out_file

    def export_skipped_csv(self) -> Path:
        
        df = pd.DataFrame(self.skipped or [])
        out = self.out / "skipped_or_missing_students.csv"
        if not df.empty:
            df = df[["school","group","student","file","reason"]]
            df.to_csv(out, index=False, encoding="utf-8")
        else:
            
            pd.DataFrame(columns=["school","group","student","file","reason"]).to_csv(out, index=False, encoding="utf-8")
        return out

    
    BLUE, RED = "tab:blue", "red"


    
    @staticmethod
    def _make_distinct_palette(n: int, cmap_name: str = "tab20"):
        cmap = plt.get_cmap(cmap_name)
        if n <= 0:
            return []
        return [cmap(i % getattr(cmap, "N", 20)) for i in range(n)]

    @staticmethod
    def _plot_compare_etw_minus_ctrl(out_png: Path,
                                    title: str,
                                    x_labels: list[str],
                                    diff_values: list[float],
                                    school_colors: list,
                                    missing_mask: list[bool]):
        import math as _math
        n = len(x_labels)
        figsize = (max(10, 0.6 * n), 5.0)

        vals = [v for v, m in zip(diff_values, missing_mask)
                if (not m) and v is not None and not (isinstance(v, float) and _math.isnan(v))]
        if vals:
            vmin, vmax = min(vals), max(vals)
        else:
            vmin, vmax = -1.0, 1.0

        vmin = min(vmin, 0.0)
        vmax = max(vmax, 0.0)
        span = vmax - vmin
        pad = 0.05 * span if span > 0 else 0.1
        ymin = vmin - pad
        ymax = vmax + pad

        eps = max(1e-6, 0.006 * (ymax - ymin))
        heights = [(eps if m else v) for v, m in zip(diff_values, missing_mask)]

        plt.figure(figsize=figsize)
        plt.bar(range(n), heights, color=school_colors)
        plt.xticks(range(n), x_labels, rotation=45, ha="right")
        plt.ylabel("Δ(ETW) - Δ(CTRL)")
        plt.title(title)
        plt.ylim(ymin, ymax)
        plt.axhline(0, linewidth=0.8)
        plt.tight_layout()
        plt.savefig(out_png, dpi=200)
        plt.close()

    def export_school_etw_minus_ctrl(self, label: str = "Competenza chiave globale"):
        
        if not hasattr(self, "delta_df") or self.delta_df is None or self.delta_df.empty:
            return None
        sub = self.delta_df[self.delta_df["label"].astype(str) == str(label)].copy()
        if sub.empty:
            return None

        schools_sorted = sorted(sub["school"].dropna().astype(str).unique().tolist(), key=lambda s: s.lower())
        if not schools_sorted:
            return None

        school_code = {s: f"Scuola {i+1}" for i, s in enumerate(schools_sorted)}
        x_labels = [school_code[s] for s in schools_sorted]

        palette = self._make_distinct_palette(len(schools_sorted), "tab20")
        school_colors = [palette[i] for i in range(len(schools_sorted))]

        m = (sub.groupby(["school", "group"])["delta"].mean().unstack())

        diff_vals = []
        missing = []
        for s in schools_sorted:
            v_etw = m.loc[s, "SI"] if ("SI" in m.columns and s in m.index) else float('nan')
            v_ctl = m.loc[s, "NO"] if ("NO" in m.columns and s in m.index) else float('nan')
            if pd.isna(v_etw) or pd.isna(v_ctl):
                diff_vals.append(0.0)
                missing.append(True)
            else:
                d = float(v_etw) - float(v_ctl)
                if abs(d) < 1e-12:
                    d = _deterministic_jitter(s, -0.015, 0.015, 0.006)
                diff_vals.append(d)
                missing.append(False)

        safe = re.sub(r"[^A-Za-z0-9_\\-]+", "_", str(label)).strip("_")
        out_png = self.out / f"{safe}_ETW_minus_CONTROLLO.png"

        
        map_path = self.out / f"{safe}_school_mapping.txt"
        with open(map_path, "w", encoding="utf-8") as f:
            for s in schools_sorted:
                f.write(f"{school_code[s]} = {s}\\n")

        self._plot_compare_etw_minus_ctrl(
            out_png=out_png,
            title=f"{label} - Differenza Δ medio per scuola (ETW - Controllo)",
            x_labels=x_labels,
            diff_values=diff_vals,
            school_colors=school_colors,
            missing_mask=missing,
        )
        return out_png


    @staticmethod
    def _safe_kde(vals: np.ndarray | pd.Series):
        if gaussian_kde is None or len(np.unique(vals)) < 2:
            return None
        try:
            return gaussian_kde(vals, bw_method=0.3)
        except Exception:
            return None

    
    @classmethod
    def _delta_kde(cls, df: pd.DataFrame, title: str, dest: Path):
        fig, axs = plt.subplots(2, 1, figsize=(5, 4), sharex=True)
        fig.suptitle(title)
        for ax, grp, colr, lbl in zip(
                axs, ["SI", "NO"], [cls.BLUE, cls.RED],
                ["eTwinning", "Controllo"]):
            vals = df.loc[df.group == grp, "delta"].dropna().astype(float)
            xs   = np.linspace(-1.75, 1.75, 400)
            kde  = cls._safe_kde(vals)
            if kde is not None:
                ax.plot(xs, kde(xs), color=colr)
            ax.axvline(0, ls=":", lw=.8, color="k")
            ax.set_xlim(-1.75, 1.75)
            ax.set_title(lbl)
            ax.grid(ls=":", axis="y", alpha=.6)
        fig.tight_layout()
        fig.savefig(dest, dpi=300)
        plt.close(fig)

    @classmethod
    def _delta_box(cls, df: pd.DataFrame, title: str, dest: Path):
        data = [df.loc[df.group == g, "delta"].dropna().astype(float)
                for g in GROUPS]
        plt.figure(figsize=(4, 4))
        plt.boxplot(data, patch_artist=True,
                    boxprops=dict(color="k", facecolor="w"),
                    medianprops=dict(color="k", linewidth=2),
                    whiskerprops=dict(color="k"),
                    capprops=dict(color="k"),
                    tick_labels=["eTwin", "Ctrl"])
        for i, vals in enumerate(data, 1):
            plt.scatter(np.random.normal(i, 0.04, len(vals)), vals,
                        s=12, alpha=.5,
                        color=(cls.BLUE if i == 1 else cls.RED))
        allv = np.hstack(data) if data else np.array([0])
        pad  = max((allv.max() - allv.min()) * .05, .1)
        plt.ylim(allv.min() - pad, allv.max() + pad)
        plt.ylabel("Δ livello (1-8)")
        plt.title(title)
        plt.tight_layout()
        plt.savefig(dest, dpi=300)
        plt.close()

    @classmethod
    def _delta_bar(cls, df: pd.DataFrame, title: str, dest: Path):
        means = df.groupby("group")["delta"].mean().reindex(GROUPS)
        plt.figure(figsize=(4, 4))
        plt.bar(["eTwinning", "Controllo"], means, color=[cls.BLUE, cls.RED])
        max_abs = float(np.nanmax(np.abs(means.values))) if len(means) else 0.0
        if max_abs <= 1e-8:
            max_abs = 0.2
        plt.ylim(-max_abs * 1.1, max_abs * 1.1)
        plt.ylabel("Δ medio (scala 1-8)")
        plt.title(title)
        plt.grid(ls=":", axis="y", alpha=.6)
        plt.tight_layout()
        plt.savefig(dest, dpi=300)
        plt.close()

    
    @classmethod
    def _levels_dist(cls, df: pd.DataFrame, time_lbl: str,
                     title: str, dest: Path):
        fig, axs = plt.subplots(2, 1, figsize=(4, 4),
                                 sharex=True, gridspec_kw={'hspace': 0.25})
        fig.suptitle(title)
        for ax, grp, colr, lbl in zip(
                axs, ["SI", "NO"], [cls.BLUE, cls.RED],
                ["eTwinning", "Controllo"]):
            vals = df.loc[(df.time == time_lbl) & (df.group == grp),
                          "score"].round().astype(int)
            pct  = (vals.value_counts(normalize=True)
                         .reindex(range(1, 9), fill_value=0) * 100)
            bars = ax.bar(range(1, 9), pct.values, color=colr, alpha=.6)
            ax.set_ylim(0, max(pct.values.max(), 5) * 1.2)
            ax.set_ylabel("%")
            ax.set_title(lbl, fontsize=9)
            for b, v in zip(bars, pct.values):
                if v > 0:
                    ax.text(b.get_x() + b.get_width() / 2,
                            b.get_height() + 1,
                            f"{v:.1f}%",
                            ha='center', va='bottom', fontsize=8)
        axs[-1].set_xticks(range(1, 9))
        axs[-1].set_xticklabels(TICKLAB)
        fig.tight_layout()
        fig.savefig(dest, dpi=300)
        plt.close(fig)

    @classmethod
    def _levels_kde(cls, df: pd.DataFrame, time_lbl: str,
                    title: str, dest: Path):
        fig, axs = plt.subplots(2, 1, figsize=(5, 4), sharex=True)
        fig.suptitle(title)
        for ax, grp, colr, lbl in zip(
                axs, ["SI", "NO"], [cls.BLUE, cls.RED],
                ["eTwinning", "Controllo"]):
            vals = df.loc[(df.time == time_lbl) & (df.group == grp),
                          "score"].dropna().astype(float)
            xs   = np.linspace(1, 8, 400)
            kde  = cls._safe_kde(vals)
            if kde is not None:
                ax.plot(xs, kde(xs), color=colr)
            ax.set_xlim(1, 8)
            ax.set_xticks(range(1, 9))
            ax.set_xticklabels(TICKLAB)
            ax.set_title(lbl)
            ax.grid(ls=":", axis="y", alpha=.6)
        fig.tight_layout()
        fig.savefig(dest, dpi=300)
        plt.close(fig)

    
    def plot_all(self):
        
        rec_delta = []

        def _one(obj_df: pd.DataFrame, titolo: str):
            pre  = obj_df[obj_df.time == "pre"]                     .groupby(["school", "group", "student"]).score.mean()
            post = obj_df[obj_df.time == "post"]                     .groupby(["school", "group", "student"]).score.mean()
            delta = (post - pre).dropna().rename("delta").reset_index()

            rec_delta.extend(delta.assign(label=titolo).to_dict("records"))

            self._delta_box(delta, f"{titolo} – Δ box-plot",
                            self.out / f"{titolo}_delta_box.png")
            self._delta_bar(delta, f"Δ medio – {titolo}",
                            self.out / f"{titolo}_delta_bar.png")
            self._delta_kde(delta, f"{titolo} – Δ",
                            self.out / f"{titolo}_delta_kde.png")

            self._levels_dist(obj_df, "pre",
                              f"{titolo} – distribuzione livelli PRE",
                              self.out / f"{titolo}_pre_dist.png")
            self._levels_dist(obj_df, "post",
                              f"{titolo} – distribuzione livelli POST",
                              self.out / f"{titolo}_post_dist.png")
            self._levels_kde(obj_df, "pre",
                             f"{titolo} – PRE",
                             self.out / f"{titolo}_pre_kde.png")
            self._levels_kde(obj_df, "post",
                             f"{titolo} – POST",
                             self.out / f"{titolo}_post_kde.png")

        for comp in self.df.competence.unique():
            _one(self.df[self.df.competence == comp], nice(comp))

        for area in self.df.area.unique():
            _one(self.df[self.df.area == area], f"Area {area}")

        _one(self.df, "Competenza chiave globale")

        self.delta_df = pd.DataFrame(rec_delta)

        
        try:
            self.export_school_etw_minus_ctrl("Competenza chiave globale")
        except Exception:
            pass

    
    def ancova(self):
        pre  = self.df[self.df.time == "pre"].rename(columns={"score": "pre"})
        post = self.df[self.df.time == "post"].rename(columns={"score": "post"})
        m = pd.merge(pre, post,
                     on=["student", "school", "group", "area", "competence"],
                     how="inner")
        if m.empty:
            return

        out = []

        def _save(d, label, disp):
            
            form_terms = ["C(group)", "pre"]
            
            if getattr(self, "cov_cls", None) and self.cov_cls.get():
                if "classe" in d.columns:
                    d = d.copy()
                    def _cls_to_num(v):
                        s = str(v).strip().lower()
                        if any(k in s for k in ["prima", "1"]):
                            return 1
                        if any(k in s for k in ["second", "2"]):
                            return 2
                        if any(k in s for k in ["terz", "3"]):
                            return 3
                        if any(k in s for k in ["quart", "4"]):
                            return 4
                        if any(k in s for k in ["quint", "5"]):
                            return 5
                        try:
                            return int(float(s))
                        except Exception:
                            return None
                    d["classe_num"] = d["classe"].map(_cls_to_num)
                    if d["classe_num"].notna().any():
                        form_terms.append("classe_num")
                    else:
                        try:
                            messagebox.showwarning("Attenzione", "Impossibile ricavare un valore numerico da 'classe': covariata classe ignorata.")
                        except Exception:
                            pass
                else:
                    try:
                        messagebox.showwarning("Attenzione", "Colonna 'classe' non trovata: Classe verrà ignorata nell'ANCOVA.")
                    except Exception:
                        pass
            
            if getattr(self, "cov_eta", None) and self.cov_eta.get():
                if "eta" in d.columns:
                    form_terms.append("eta")
                else:
                    try:
                        messagebox.showwarning("Attenzione", "Colonna 'eta' non trovata: Età verrà ignorata nell'ANCOVA.")
                    except Exception:
                        pass
            
            if getattr(self, "cov_scu", None) and self.cov_scu.get():
                form_terms.append("C(school)")
            form = "post ~ " + " + ".join(form_terms)
            mdl = ols(form, d).fit()
            aov = sm.stats.anova_lm(mdl, typ=2)
            aov.to_csv(self.out / f"ancova_{label}.csv")
            F, p = aov.loc["C(group)", ["F", "PR(>F)"]]
            out.append(dict(oggetto=disp,
                            F=F,
                            p=p,
                            significativo="✔" if p < .05 else "✘"))

        for comp in m.competence.unique():
            _save(m[m.competence == comp], comp, nice(comp))
        for area in m.area.unique():
            _save(m[m.area == area], f"area_{area}", f"Area {area}")
        _save(m, "globale", "Competenza chiave globale")

        pd.DataFrame(out).to_csv(self.out / "ancova_summary.csv", index=False)


def effect_size_summary(delta_df: pd.DataFrame, out: Path) -> pd.DataFrame:
    
    rec = []
    for lab, sub in delta_df.groupby("label"):
        etw  = sub[sub.group == "SI"].drop_duplicates(["school", "student"]).delta.astype(float)
        ctrl = sub[sub.group == "NO"].drop_duplicates(["school", "student"]).delta.astype(float)
        n1, n2 = len(etw), len(ctrl)
        if min(n1, n2) < 3:
            rec.append([lab, n1, n2, np.nan, np.nan, np.nan, "", ""])
            continue
        m1, m2  = etw.mean(), ctrl.mean()
        s1, s2  = etw.std(ddof=1), ctrl.std(ddof=1)
        sp      = sqrt(((n1 - 1) * s1**2 + (n2 - 1) * s2**2) / (n1 + n2 - 2))
        d       = (m1 - m2) / sp
        g       = d * (1 - 3 / (4 * (n1 + n2) - 9))            
        se_g    = sqrt((n1 + n2) / (n1 * n2) + g**2 / (2 * (n1 + n2 - 2)))
        ci_low, ci_high = g - 1.96 * se_g, g + 1.96 * se_g
        sig = "✔" if ci_low * ci_high > 0 else "✘"
        rec.append([lab, n1, n2, g, ci_low, ci_high, sig, m1 - m2])

    cols = ["oggetto", "N_etw", "N_ctrl", "Hedges_g",
            "CI95_low", "CI95_high", "sig", "Delta_mean_diff"]
    df_effect = pd.DataFrame(rec, columns=cols)
    df_effect.to_csv(out / "effect_size_summary.csv", index=False)
    return df_effect



def forest_plot(effect_df: pd.DataFrame, out: Path):
    
    effect_df = effect_df.dropna(subset=["Hedges_g"])
    if effect_df.empty:
        return
    y_pos = np.arange(len(effect_df))[::-1]
    fig, ax = plt.subplots(figsize=(6, 0.4 * len(effect_df) + 1))
    ax.errorbar(effect_df["Hedges_g"], y_pos,
                xerr=[effect_df["Hedges_g"] - effect_df["CI95_low"],
                      effect_df["CI95_high"] - effect_df["Hedges_g"]],
                fmt="o", color="black", ecolor="gray", capsize=4)
    ax.axvline(0, color="red", ls="--")
    ax.set_yticks(y_pos)
    ax.set_yticklabels(effect_df["oggetto"])
    ax.set_xlabel("Hedges g  (Δ etw − Δ ctrl)")
    ax.set_title("Effect size con IC 95 %")
    plt.tight_layout()
    fig.savefig(out / "forest_plot_effects.png", dpi=300)
    plt.close(fig)



def descriptive_table(df: pd.DataFrame, out: Path):
    
    rec = []
    for (area, comp), sub in df.groupby(["area", "competence"], dropna=False):
        lab_name = nice(comp) if comp else f"Area {area}"
        for g in GROUPS:
            et = sub[sub.group == g]
            pre   = et[et.time == "pre" ].groupby(["school", "student"]).score.mean()
            post  = et[et.time == "post"].groupby(["school", "student"]).score.mean()
            delta = (post - pre).dropna()
            if delta.empty:
                continue
            rec.append([lab_name, g, len(delta),
                        pre.mean(),  pre.std(ddof=1),
                        post.mean(), post.std(ddof=1),
                        delta.mean(), delta.std(ddof=1)])
    cols = ["oggetto", "gruppo", "N",
            "pre_mean",  "pre_sd",
            "post_mean", "post_sd",
            "delta_mean", "delta_sd"]
    pd.DataFrame(rec, columns=cols)      .to_csv(out / "descriptive_table.csv", index=False)



def assumption_plots(delta_df: pd.DataFrame, out: Path):
    
    for grp, colr in zip(GROUPS, ["tab:blue", "tab:red"]):
        vals = delta_df[delta_df.group == grp]                 .drop_duplicates(["school", "student"]).delta.astype(float)
        if len(vals) < 5:
            continue
        fig, axs = plt.subplots(1, 2, figsize=(6, 3))
        sm.qqplot(vals, line="s", ax=axs[0], color=colr)
        axs[0].set_title(f"QQ-plot {grp}")
        axs[1].hist(vals, bins=15, color=colr, alpha=.7)
        axs[1].set_title(f"Istogramma Δ {grp}")
        plt.tight_layout()
        fig.savefig(out / f"assumption_{grp}.png", dpi=300)
        plt.close(fig)


def shapiro_francia(x):
    x = x[np.isfinite(x)]
    return stats.normaltest(x, nan_policy="omit")           if len(x) < 5 or len(x) > 5000 else stats.shapiro(x)


def normality_summary(df: pd.DataFrame, out: Path):
    
    rec = []
    for lab in df.label.unique():
        sub = df[df.label == lab]
        for g in GROUPS:
            vals = sub[sub.group == g]                     .groupby(["school","student"]).delta.mean().values
            if len(vals) < 8:
                rec.append([lab, g, np.nan, np.nan]); continue
            W, p = shapiro_francia(vals)
            rec.append([lab, g, W, p])
    pd.DataFrame(rec, columns=["oggetto", "gruppo", "W", "p"])      .to_csv(out / "normality_summary.csv", index=False)


def welch_mw_summary(df: pd.DataFrame, out: Path):
    
    rows = []
    for lab in df.label.unique():
        s = df[df.label == lab]
        etw  = s[s.group == "SI"]                 .groupby(["school","student"]).delta.mean().values
        ctrl = s[s.group == "NO"]                 .groupby(["school","student"]).delta.mean().values
        if min(len(etw), len(ctrl)) < 3:
            rows.append([lab, len(etw), len(ctrl), "", "", "", ""]); continue
        if shapiro_francia(etw)[1] > .05 and shapiro_francia(ctrl)[1] > .05:
            stat, p = stats.ttest_ind(etw, ctrl, equal_var=False); test = "Welch"
        else:
            stat, p = stats.mannwhitneyu(etw, ctrl, alternative="two-sided"); test = "Mann-Whitney"
        rows.append([lab, len(etw), len(ctrl), test, stat, p,
                     "sig" if p < .05 else "n.s."])
    pd.DataFrame(rows,
                 columns=["oggetto", "N_etw", "N_ctrl", "test",
                          "statistic", "p", "sig/NS"])      .to_csv(out / "Welch_MW_summary.csv", index=False)


def ttest_stepwise(df: pd.DataFrame, out: Path, cutoffs=(.20, .40, .60, .80)):
    
    rec = []
    for lab in df.label.unique():
        sub = df[df.label == lab]
        for th in cutoffs:
            s = sub[abs(sub.delta) >= th]
            etw  = s[s.group == "SI"]                     .groupby(["school","student"]).delta.mean().values
            ctrl = s[s.group == "NO"]                     .groupby(["school","student"]).delta.mean().values
            if min(len(etw), len(ctrl)) < 3:
                rec.append([lab, th, np.nan, np.nan, np.nan, ""]); continue
            t, p = stats.ttest_ind(etw, ctrl, equal_var=False)
            rec.append([lab, th, len(etw)+len(ctrl), t, p,
                        "sig" if p < .05 else "n.s."])
    pd.DataFrame(rec,
                 columns=["oggetto", "cutoff", "N", "t", "p", "sig/NS"])      .to_csv(out / "ttest_stepwise.csv", index=False)


def sanity_counts(root: Path, df: pd.DataFrame, out: Path):
    
    df_cnt = df[["school", "group", "student"]].drop_duplicates()               .groupby("group").size()
    fs_codes = {g: set() for g in GROUPS}
    for fold in ("ENTRATA", "USCITA"):
        base = root / fold
        if not base.exists(): continue
        for sdir in base.iterdir():
            if not sdir.is_dir(): continue
            school = sdir.name
            for g in GROUPS:
                gdir = sdir / g
                if not gdir.exists(): continue
                for xl in gdir.glob("*.*"):
                    if _TMP.match(xl.name): continue
                    code = extract_code(xl.stem)
                    if code:
                        fs_codes[g].add((school, g, code))
    fs_cnt = {g: len(c) for g, c in fs_codes.items()}

    lines = ["Conteggio studenti univoci:", ""]
    for g in GROUPS:
        ok = "✔" if df_cnt.get(g, 0) == fs_cnt.get(g, 0) else "✘"
        lines.append(f"{g}:  file={fs_cnt.get(g,0)}  dataframe={df_cnt.get(g,0)}  {ok}")
    (out / "sanity_check.txt").write_text("\n".join(lines), encoding="utf-8")









class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("Analisi competenze – eTwinning vs Controllo")
        self.geometry("780x380")
        self.minsize(580, 260)
        self.columnconfigure(0, weight=1)

        
        self.dir_in  = tk.StringVar()
        self.dir_out = tk.StringVar()

        
        self.opt_effect  = tk.BooleanVar(value=True)
        self.opt_forest  = tk.BooleanVar(value=True)
        self.opt_descr   = tk.BooleanVar(value=True)
        self.opt_assump  = tk.BooleanVar(value=False)

        
        ttk.Label(self, text="Cartella dati (ENTRATA / USCITA):")           .grid(row=0, column=0, sticky="w", padx=12, pady=(10, 0))
        ttk.Entry(self, textvariable=self.dir_in)           .grid(row=1, column=0, sticky="ew", padx=12)
        ttk.Button(self, text="Sfoglia", command=self.browse_in)           .grid(row=2, column=0, sticky="w", padx=12, pady=4)

        ttk.Label(self, text="Cartella output:")           .grid(row=3, column=0, sticky="w", padx=12, pady=(12, 0))
        ttk.Entry(self, textvariable=self.dir_out)           .grid(row=4, column=0, sticky="ew", padx=12)
        ttk.Button(self, text="Sfoglia", command=self.browse_out)           .grid(row=5, column=0, sticky="w", padx=12, pady=4)

        ttk.Button(self, text="Avvia", command=self.run)           .grid(row=6, column=0, pady=12)

        
        opts = ttk.Frame(self); opts.grid(row=7, column=0, padx=12, pady=4, sticky="w")
        ttk.Checkbutton(opts, text="Effect size + CI",
                        variable=self.opt_effect).grid(row=0, column=0, sticky="w")
        ttk.Checkbutton(opts, text="Forest-plot",
                        variable=self.opt_forest).grid(row=0, column=1, sticky="w")
        ttk.Checkbutton(opts, text="Tabella descrittiva",
                        variable=self.opt_descr).grid(row=0, column=2, sticky="w")
        ttk.Checkbutton(opts, text="Grafici assunzioni",
                        variable=self.opt_assump).grid(row=0, column=3, sticky="w")
        
        self.cov_cls = tk.BooleanVar(value=False)
        self.cov_eta = tk.BooleanVar(value=False)
        self.cov_scu = tk.BooleanVar(value=False)
        cov = ttk.Frame(self); cov.grid(row=8, column=0, padx=12, pady=(0,4), sticky="w")
        ttk.Label(cov, text="Covariate ANCOVA:").grid(row=0, column=0, sticky="w", padx=(0,12))
        ttk.Checkbutton(cov, text="Classe (covariata)", variable=self.cov_cls).grid(row=0, column=1, sticky="w")
        ttk.Checkbutton(cov, text="Età (continua)", variable=self.cov_eta).grid(row=0, column=2, sticky="w")
        ttk.Checkbutton(cov, text="Scuola (fattore)", variable=self.cov_scu).grid(row=0, column=3, sticky="w")


        
        self.status = ttk.Label(self, text="Pronto", foreground="blue")
        self.status.grid(row=9, column=0, pady=(0, 12))

    
    def browse_in(self):
        d = filedialog.askdirectory()
        self.dir_in.set(d or self.dir_in.get())

    def browse_out(self):
        d = filedialog.askdirectory()
        self.dir_out.set(d or self.dir_out.get())

    
    def run(self):
        try:
            root = Path(self.dir_in.get())
            out  = Path(self.dir_out.get())
            if not root.exists():
                messagebox.showerror("Errore", "Cartella dati non valida.")
                return
            out.mkdir(parents=True, exist_ok=True)

            logging.basicConfig(filename=out / "error_log.txt",
                                filemode="w", level=logging.INFO,
                                format="%(asctime)s - %(levelname)s - %(message)s")
            log = logging.getLogger("competence")

            self.status.configure(text="Parsing …"); self.update_idletasks()
            a = Analyzer(root, out, log); a.parse()

            self.status.configure(text="CSV …"); self.update_idletasks()
            csv_path = a.export_csv()

            stud_path = a.export_student_summary()

            means_path = a.export_group_means()

            self.status.configure(text="Grafici …"); self.update_idletasks()
            a.plot_all()

            normality_summary(a.delta_df, out)
            welch_mw_summary(a.delta_df, out)
            ttest_stepwise(a.delta_df, out)
            sanity_counts(root, a.df, out)

            self.status.configure(text="ANCOVA …"); self.update_idletasks()
            a.ancova()

            
            if self.opt_effect.get():
                eff_df = effect_size_summary(a.delta_df, out)
                if self.opt_forest.get():
                    forest_plot(eff_df, out)

            if self.opt_descr.get():
                descriptive_table(a.df, out)

            if self.opt_assump.get():
                assumption_plots(a.delta_df, out)

            
            msg = (f"Analisi completata!\nCSV: {csv_path}\n"
                   "(vedi sanity_check.txt)")
            if a.err_files:
                msg += f"\nFile problematici: {a.err_files} (log)"
            if a.bad_vals:
                msg += f"\nValori non riconosciuti: {len(a.bad_vals)} (log)"
            messagebox.showinfo("Fine", msg)
            self.status.configure(text="Completato")

        except Exception as exc:
            traceback.print_exc()
            messagebox.showerror("Errore", str(exc))
            self.status.configure(text="Errore")



if __name__ == "__main__":
    App().mainloop()
