#!/usr/bin/env python3
"""
Exports spreadsheet-trace metrics directly from the cleaned author workbooks and compares them
with the final closed-form equation as written in the frozen manuscript.
"""
from __future__ import annotations
from pathlib import Path
import importlib.util
import numpy as np
import pandas as pd

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

spec = importlib.util.spec_from_file_location("model_equation", ROOT / "scripts" / "01_model_equation.py")
module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(module)
add_prediction_columns = module.add_prediction_columns

def metric_frame(df: pd.DataFrame, pred_col: str, obs_col: str = "ec_exp_gpa") -> dict:
    err = df[obs_col] - df[pred_col]
    mae = float(np.mean(np.abs(err)))
    rmse = float(np.sqrt(np.mean(err ** 2)))
    mean_obs = float(df[obs_col].mean())
    cov_rmse = float(rmse / mean_obs * 100.0) if mean_obs != 0 else float("nan")
    bias = float(np.mean(err))
    return {
        "n": int(len(df)),
        "mean_obs_gpa": mean_obs,
        "mae_gpa": mae,
        "rmse_gpa": rmse,
        "cov_rmse_percent": cov_rmse,
        "mean_signed_error_gpa": bias,
    }

def main() -> None:
    datasets = {
        "modeling_113": pd.read_csv(CURATED / "base_modelagem_113_curada.csv"),
        "validation_138": pd.read_csv(CURATED / "base_validacao_138_curada.csv"),
        "unified_251": pd.read_csv(CURATED / "base_unificada_curada.csv"),
    }
    rows = []
    for name, df in datasets.items():
        workbook_metrics = {"dataset": name, "source": "spreadsheet_optimized_column"}
        workbook_metrics.update(metric_frame(df, "ec_pred_optimized_gpa"))
        rows.append(workbook_metrics)

        formula_df = add_prediction_columns(df)
        formula_metrics = {"dataset": name, "source": "final_closed_form_equation"}
        formula_metrics.update(metric_frame(formula_df, "ec_pred_from_final_formula_gpa"))
        rows.append(formula_metrics)

        formula_df.to_csv(RESULTS / f"{name}_with_final_formula_predictions.csv", index=False)

    out = pd.DataFrame(rows)
    out.to_csv(RESULTS / "workbook_trace_metrics.csv", index=False)
    print(out.to_string(index=False))

if __name__ == "__main__":
    main()
