#!/usr/bin/env python3
from __future__ import annotations
from pathlib import Path
import pandas as pd

ROOT = Path(__file__).resolve().parents[1]
RAW = ROOT / "data_raw"
CURATED = ROOT / "data_curated"
CURATED.mkdir(exist_ok=True)

def clean_model_val(path: Path, dataset_name: str) -> pd.DataFrame:
    df = pd.read_excel(path)
    df = df[df["ID"].notna()].copy()
    out = pd.DataFrame({
        "row_id": df["ID"].astype(int),
        "author": df["Autor"].astype(str),
        "fc_mpa": df["fc"].astype(float),
        "vf_percent": df["Vf"].astype(float),
        "aspect_ratio_ld": df["LD"].astype(float),
        "ec_exp_gpa": df["Ec_exp"].astype(float),
        "ec_pred_optimized_gpa": df["Eq. Otimizada"].astype(float),
        "residual_gpa": df["Ec+exp - Ec_otim"].astype(float),
        "residual_sq_gpa2": df["(Ec+exp - Ec_otim)^2"].astype(float),
        "dataset": dataset_name,
    })
    return out

def clean_unified(path: Path) -> pd.DataFrame:
    df = pd.read_excel(path)
    df = df[df["ID"].notna()].copy()
    out = pd.DataFrame({
        "study_id": df["ID"].astype(str),
        "author": df["Fonte/ Autor"].astype(str),
        "fc_mpa": df["fc (Mpa)"].astype(float),
        "vf_percent": df["Vf (%)"].astype(float),
        "aspect_ratio_ld": df["L/D (-)"].astype(float),
        "ec_exp_gpa": df["Ec_exp (Gpa)"].astype(float),
        "usage_group": df["Uso"].astype(str),
        "ec_pred_optimized_gpa": df["Eq. Otimizada"].astype(float),
        "residual_gpa": df["Ec+exp - Ec_otim"].astype(float),
        "residual_sq_gpa2": df["(Ec+exp - Ec_otim)^2"].astype(float),
    })
    return out

def main() -> None:
    modeling = clean_model_val(RAW / "Base_MODELAGEM_113_Comparativo_V5.xlsx", "modeling_113")
    validation = clean_model_val(RAW / "Base_VALIDACAO_138_Comparativo_V5.xlsx", "validation_138")
    unified = clean_unified(RAW / "Base_Unificada.xlsx")

    modeling.to_csv(CURATED / "base_modelagem_113_curada.csv", index=False)
    validation.to_csv(CURATED / "base_validacao_138_curada.csv", index=False)
    unified.to_csv(CURATED / "base_unificada_curada.csv", index=False)
    print(f"Exported {len(modeling)} modeling rows, {len(validation)} validation rows, and {len(unified)} unified rows.")

if __name__ == "__main__":
    main()
