"""Chronological train/test split, train-only preprocessing and atomic writeback."""

import json
import tempfile
from datetime import datetime, timedelta
from pathlib import Path

import numpy as np
from sklearn.impute import SimpleImputer
from sklearn.linear_model import Ridge
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler
from sqlalchemy import text

from sdk_common import tables, frame


def main():
    definitions = [
        ("sdk_ml_features", "id BIGINT PRIMARY KEY,event_at DATETIME(6),"
         "x DOUBLE,z DOUBLE,label DOUBLE"),
        ("sdk_ml_runs", "run_id VARCHAR(30) PRIMARY KEY,train_n INT,test_n INT,"
         "mae DOUBLE,mse DOUBLE,r2 DOUBLE,model_json TEXT"),
        ("sdk_ml_predictions", "run_id VARCHAR(30),id BIGINT,prediction DOUBLE,"
         "PRIMARY KEY(run_id,id)"),
    ]
    records = [dict(id=i, event_at=datetime(2026, 1, 1) + timedelta(days=i-1),
                    x=None if i == 4 else float(i), z=float(i % 3),
                    label=float(3*i + 2*(i % 3) + 5)) for i in range(1, 17)]
    with tables(definitions) as client:
        with client.session() as session:
            session.execute(text("INSERT INTO sdk_ml_features VALUES"
                                 "(:id,:event_at,:x,:z,:label)"), records)
        cutoff = datetime(2026, 1, 13)
        with client.session() as session:
            train = frame(session, "SELECT * FROM sdk_ml_features WHERE event_at < :cutoff "
                          "ORDER BY event_at,id", {"cutoff": cutoff})
            test = frame(session, "SELECT * FROM sdk_ml_features WHERE event_at >= :cutoff "
                         "ORDER BY event_at,id", {"cutoff": cutoff})
        assert len(train) == 12 and len(test) == 4
        assert train["event_at"].max() < test["event_at"].min()
        features = ["x", "z"]  # Deliberately exclude label, id and future values.
        for dataset in [train, test]:
            # Floating features are deliberate, separate from identifiers/money.
            dataset[features] = dataset[features].astype("float64")
            dataset["label"] = dataset["label"].astype("float64")
        pipeline = Pipeline([
            ("impute", SimpleImputer(strategy="median")),
            ("scale", StandardScaler()),
            ("model", Ridge(alpha=1.0)),
        ])
        pipeline.fit(train[features], train["label"])
        assert np.allclose(pipeline["impute"].statistics_,
                           np.nanmedian(train[features].to_numpy(), axis=0))
        # Negative control: using the combined data changes the x median.
        leaked_median = np.nanmedian(np.vstack([train[features], test[features]]), axis=0)
        assert leaked_median[0] != pipeline["impute"].statistics_[0]
        predictions = pipeline.predict(test[features])
        metrics = {"mae": float(mean_absolute_error(test["label"], predictions)),
                   "mse": float(mean_squared_error(test["label"], predictions)),
                   "r2": float(r2_score(test["label"], predictions))}
        assert all(np.isfinite(list(metrics.values())))
        manifest = dict(
            features=features, cutoff=cutoff.isoformat(),
            imputer=pipeline["impute"].statistics_.tolist(),
            mean=pipeline["scale"].mean_.tolist(),
            scale=pipeline["scale"].scale_.tolist(),
            coef=pipeline["model"].coef_.tolist(),
            intercept=float(pipeline["model"].intercept_), alpha=1.0,
        )
        serialized = json.dumps(manifest, allow_nan=False)
        run = dict(run_id="synthetic_ridge_v1", train_n=len(train), test_n=len(test),
                   model_json=serialized, **metrics)
        prediction_rows = [dict(run_id=run["run_id"], id=int(i), prediction=float(p))
                           for i, p in zip(test["id"], predictions)]
        # Failure between model metadata and prediction writes must leave neither.
        try:
            with client.session() as session:
                session.execute(text("INSERT INTO sdk_ml_runs VALUES"
                                     "(:run_id,:train_n,:test_n,:mae,:mse,:r2,:model_json)"), run)
                raise RuntimeError("intentional writeback failure")
        except RuntimeError as error:
            assert str(error) == "intentional writeback failure"
        assert client.execute("SELECT COUNT(*) FROM sdk_ml_runs").scalar() == 0
        assert client.execute("SELECT COUNT(*) FROM sdk_ml_predictions").scalar() == 0
        with client.session() as session:
            session.execute(text("INSERT INTO sdk_ml_runs VALUES"
                                 "(:run_id,:train_n,:test_n,:mae,:mse,:r2,:model_json)"), run)
            session.execute(text("INSERT INTO sdk_ml_predictions VALUES(:run_id,:id,:prediction)"),
                            prediction_rows)
        stored = client.execute("SELECT model_json FROM sdk_ml_runs").scalar()
        recovered = json.loads(stored)
        values = test[features].to_numpy(dtype=float)
        filled = np.where(np.isnan(values), np.array(recovered["imputer"]), values)
        transformed = (filled - np.array(recovered["mean"])) / np.array(recovered["scale"])
        restored_predictions = transformed @ np.array(recovered["coef"]) + recovered["intercept"]
        rows = client.execute("SELECT id,prediction FROM sdk_ml_predictions ORDER BY id").rows
        assert [row[0] for row in rows] == test["id"].tolist()
        assert np.allclose([row[1] for row in rows], predictions, rtol=1e-12, atol=1e-12)
        assert np.allclose(restored_predictions, predictions, rtol=1e-12, atol=1e-12)
        output = Path(tempfile.mkdtemp(prefix="mo-sdk-ml-"))
        (output / "model.json").write_text(serialized)
        (output / "metrics.json").write_text(json.dumps(metrics, indent=2))
        print(json.dumps(dict(train=12, test=4, leakage_control=True,
                              rollback_verified=True, writeback_verified=True, **metrics)))
        print("Local artifacts:", output)


if __name__ == "__main__":
    main()
