"""MatrixOne SDK -> exact values -> SQL aggregation -> pandas -> local chart."""

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

import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import pandas as pd
from sqlalchemy import text

from sdk_common import tables, frame


def main():
    definitions = [
        ("sdk_ds_orders", "id BIGINT PRIMARY KEY,channel VARCHAR(30),"
         "amount DECIMAL(20,4),customer_id BIGINT,event_at DATETIME(6)"),
        ("sdk_ds_summary", "channel VARCHAR(30) PRIMARY KEY,n_rows BIGINT,"
         "n_amount BIGINT,revenue DECIMAL(20,4)"),
    ]
    records = [dict(
        id=i, channel="web" if i % 2 else "mobile",
        amount=None if i == 3 else Decimal(i) * 10 + Decimal("0.25"),
        customer_id=None if i == 5 else (9007199254740993 if i == 1 else i),
        event_at=datetime(2026, 1, 1, 8, 0, 0, 123456) + timedelta(days=(i - 1) // 2),
    ) for i in range(1, 13)]
    with tables(definitions) as client:
        with client.session() as session:
            session.execute(text("INSERT INTO sdk_ds_orders VALUES"
                                 "(:id,:channel,:amount,:customer_id,:event_at)"), records)
        with client.session() as session:
            raw = frame(session, "SELECT * FROM sdk_ds_orders ORDER BY id")
            summary = frame(session, "SELECT channel,COUNT(*) AS n_rows,"
                            "COUNT(amount) AS n_amount,SUM(amount) AS revenue "
                            "FROM sdk_ds_orders GROUP BY channel ORDER BY channel")
            daily = frame(session, "SELECT CAST(event_at AS DATE) AS day,"
                          "SUM(amount) AS revenue FROM sdk_ds_orders "
                          "WHERE event_at < :cutoff GROUP BY CAST(event_at AS DATE) ORDER BY day",
                          {"cutoff": datetime(2026, 1, 7)})
        assert len(raw) == 12 and raw["amount"].isna().sum() == 1
        assert raw.loc[0, "amount"] == Decimal("10.2500")
        assert raw.loc[0, "event_at"].microsecond == 123456
        raw["customer_id"] = raw["customer_id"].astype("Int64")
        assert pd.isna(raw.loc[4, "customer_id"])
        assert raw.loc[0, "customer_id"] == 9007199254740993
        expected = [("mobile", 6, 6, Decimal("421.5000")),
                    ("web", 6, 5, Decimal("331.2500"))]
        assert list(summary.itertuples(index=False, name=None)) == expected
        for channel, _, _, revenue in expected:
            values = raw.loc[raw["channel"] == channel, "amount"].dropna()
            assert sum(values, Decimal(0)) == revenue
        assert sum(daily["revenue"], Decimal(0)) == Decimal("752.7500")
        with client.session() as session:
            session.execute(text("INSERT INTO sdk_ds_summary VALUES"
                                 "(:channel,:n_rows,:n_amount,:revenue)"),
                            summary.to_dict("records"))
        actual = client.execute("SELECT * FROM sdk_ds_summary ORDER BY channel").rows
        assert [tuple(row) for row in actual] == expected
        # Keyset pages bound each SQL result; fetchmany() alone would not do this.
        ids, last_id = [], 0
        with client.session() as session:
            while True:
                page = frame(session, "SELECT id FROM sdk_ds_orders WHERE id > :last_id "
                             "ORDER BY id LIMIT 5", {"last_id": last_id})
                if page.empty:
                    break
                ids.extend(page["id"].tolist())
                last_id = int(page["id"].iloc[-1])
        assert ids == list(range(1, 13))
        output = Path(tempfile.mkdtemp(prefix="mo-sdk-eda-"))
        # Float conversion is only for plotting; exact money stays Decimal above.
        ax = daily.assign(revenue=daily["revenue"].map(float)).plot(x="day", y="revenue")
        ax.set_ylabel("Synthetic revenue")
        plt.tight_layout()
        plt.savefig(output / "daily.png")
        plt.close("all")
        daily.to_csv(output / "daily.csv", index=False)
        report = {"rows": 12, "null_amounts": 1, "revenue": "752.7500",
                  "keyset_rows": len(ids), "writeback_verified": True}
        (output / "checks.json").write_text(json.dumps(report, indent=2))
        print(summary.to_string(index=False))
        print(json.dumps(report))
        print("Local artifacts:", output)


if __name__ == "__main__":
    main()
