"""Insert one million rows into SQLite with different primary keys and time it.

Usage: python3 uuid_insert_test.py [rows] [runs]
Writes results.csv next to this file. Needs only the Python standard library.
"""
import csv, os, platform, sqlite3, statistics, sys, tempfile, time, uuid

ROWS = int(sys.argv[1]) if len(sys.argv) > 1 else 1_000_000
RUNS = int(sys.argv[2]) if len(sys.argv) > 2 else 3
BATCH = 10_000
PAYLOAD = "x" * 20


def uuid7():
    """UUID version 7 as laid out in RFC 9562: 48-bit ms timestamp, then random bits."""
    ms = time.time_ns() // 1_000_000
    b = bytearray(ms.to_bytes(6, "big") + os.urandom(10))
    b[6] = (b[6] & 0x0F) | 0x70
    b[8] = (b[8] & 0x3F) | 0x80
    return uuid.UUID(bytes=bytes(b))


def keys(kind):
    if kind == "integer":
        return list(range(1, ROWS + 1))
    make = uuid.uuid4 if kind.startswith("v4") else uuid7
    out = [make() for _ in range(ROWS)]
    return [str(u) for u in out] if kind.endswith("text") else [u.bytes for u in out]


def run(kind):
    ks = keys(kind)
    col = {"integer": "INTEGER", "text": "TEXT", "blob": "BLOB"}[kind.split("-")[-1]]
    path = os.path.join(tempfile.mkdtemp(), "t.db")
    db = sqlite3.connect(path, isolation_level=None)
    db.execute("PRAGMA journal_mode=OFF")
    db.execute("PRAGMA synchronous=OFF")
    db.execute("PRAGMA cache_size=-2000")  # 2 MB page cache
    db.execute(f"CREATE TABLE t (id {col} PRIMARY KEY, payload TEXT) WITHOUT ROWID")
    t0 = time.perf_counter()
    for i in range(0, ROWS, BATCH):
        db.execute("BEGIN")
        db.executemany("INSERT INTO t VALUES (?, ?)", ((k, PAYLOAD) for k in ks[i:i + BATCH]))
        db.execute("COMMIT")
    secs = time.perf_counter() - t0
    db.close()
    size = os.path.getsize(path)
    os.remove(path)
    return secs, size


if __name__ == "__main__":
    print("SQLite", sqlite3.sqlite_version, "| Python", platform.python_version(), "|", platform.platform())
    rows = []
    for kind in ["integer", "v7-text", "v7-blob", "v4-text", "v4-blob"]:
        res = [run(kind) for _ in range(RUNS)]
        for n, (secs, size) in enumerate(res, 1):
            rows.append([kind, n, ROWS, round(secs, 3), size])
        print(kind, "median", round(statistics.median(s for s, _ in res), 2), "s,", res[0][1] // 1_000_000, "MB")
    with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), "results.csv"), "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["key", "run", "rows", "insert_seconds", "database_bytes"])
        w.writerows(rows)
