"""Build a minute-rate Reading table for the index experiments of Lecture 11 (Database Design Principles).

Ten hypothetical sensors, S01 to S10, each report one value per minute for the 30 days of
September 2026: 432,000 rows in the same Reading schema as the bridge-monitoring database of
Lecture 10. The values are synthetic (normal, mean 100, standard deviation 10, fixed seed).

    python make_minute_readings.py [path]

writes `minute_readings.sqlite` (or the path given). Only the Python standard library is needed.
"""
import datetime as dt
import random
import sqlite3
import sys

N_SENSORS = 10
DAYS = 30
START = dt.datetime(2026, 9, 1)

DDL = """CREATE TABLE Reading (
    sensor_id TEXT NOT NULL,
    seq_no    INTEGER NOT NULL,
    time      TEXT NOT NULL,
    value     REAL,
    PRIMARY KEY (sensor_id, seq_no)
);"""


def rows(seed=778):
    """The readings, in the order a logger would deliver them: minute by minute, all sensors."""
    rng = random.Random(seed)
    out = []
    for k in range(DAYS * 1440):
        t = (START + dt.timedelta(minutes=k)).strftime("%Y-%m-%dT%H:%M:00Z")
        for s in range(N_SENSORS):
            out.append((f"S{s + 1:02d}", k + 1, t, round(rng.gauss(100, 10), 1)))
    return out


def build(con, indexes=()):
    """Create Reading (plus any CREATE INDEX statements given) and load it in one transaction."""
    con.execute(DDL)
    for stmt in indexes:
        con.execute(stmt)
    with con:
        con.executemany("INSERT INTO Reading VALUES (?, ?, ?, ?)", rows())
    return con


if __name__ == "__main__":
    import os
    path = sys.argv[1] if len(sys.argv) > 1 else "minute_readings.sqlite"
    if os.path.exists(path):
        sys.exit(f"{path} already exists; remove it first")
    con = build(sqlite3.connect(path))
    n = con.execute("SELECT COUNT(*) FROM Reading").fetchone()[0]
    con.close()
    print(f"wrote {path}: Reading {n}")