#!/usr/bin/env python3
"""Summarise results-<tag>.csv and raw/<tag>/explain/*.txt into markdown.

Usage: python3 summarize.py <tag> [<tag> ...]  -> prints markdown to stdout
"""
import csv
import re
import statistics
import sys
from pathlib import Path

HERE = Path(__file__).resolve().parent
ORDER = ["p1", "p2", "p3", "p4", "p4b", "own_p1", "own_p2", "own_p4", "own_p4b", "own_lit"]
KEEP = re.compile(r"(Scan|Result|Aggregate|Limit|Sort|InitPlan|SubPlan|Filter:|Index Cond:|"
                  r"One-Time Filter:|Rows Removed|Recheck Cond:|Sort Key:|Execution Time)")


def tables(tag):
    rows = list(csv.DictReader(open(HERE / f"results-{tag}.csv")))
    data = {}
    for r in rows:
        if int(r["run"]) == 0:  # warm-up
            continue
        data.setdefault((r["index"], r["variant"], r["scenario"]), []).append(float(r["ms"]))
    out = [f"### {tag}: {rows[0]['version']}\n"]
    for idx in ("noidx", "idx"):
        scen = sorted({k[2] for k in data if k[0] == idx})
        if not scen:
            continue
        out.append(f"\n**{'zonder index op user_id' if idx == 'noidx' else 'met btree-index op user_id'}** "
                   "(mediaan ms; runs tussen haakjes)\n")
        out.append("| variant | " + " | ".join(scen) + " |")
        out.append("|---|" + "---|" * len(scen))
        for v in ORDER:
            cells = []
            for s in scen:
                xs = data.get((idx, v, s))
                if not xs:
                    cells.append("")
                    continue
                cells.append(f"**{statistics.median(xs):.1f}** ({', '.join(f'{x:.1f}' for x in xs)})")
            if any(cells):
                out.append(f"| {v} | " + " | ".join(cells) + " |")
    return "\n".join(out)


def explains(tag):
    out = [f"### EXPLAIN-samenvatting {tag}\n"]
    for f in sorted((HERE / "raw" / tag / "explain").glob("*.txt"),
                    key=lambda p: (p.stem.split("_")[0] != "noidx",
                                   ORDER.index("_".join(p.stem.split("_")[1:-1])),
                                   p.stem.split("_")[-1])):
        lines = [l.rstrip() for l in f.read_text().splitlines() if KEEP.search(l)]
        out.append(f"`{f.stem}`\n```")
        out.extend(lines)
        out.append("```")
    return "\n".join(out)


if __name__ == "__main__":
    for t in sys.argv[1:]:
        print(tables(t))
        print()
        print(explains(t))
        print()