"""Build the bridge-monitoring database used in Lecture 10 (The Structured Query Language).

The city, its bridges, and everything else here are hypothetical. Bridge and Sensor are the
instances of Lecture 09; the other seven relations complete the schema translated there, and
Reading holds one daily summary value per sensor from August 1 to September 28, 2026.

Run it to create `bridges.sqlite` in the current folder (or at the path given):

    python make_bridges_db.py [path]

Only the Python standard library is needed.
"""

import datetime as dt
import random
import sqlite3
import sys

# ---------------------------------------------------------------------------
# Schema: Lecture 09's nine relations, as SQL DDL
# ---------------------------------------------------------------------------

DDL = {
    "Bridge": """CREATE TABLE Bridge (
    bridge_id  TEXT PRIMARY KEY,
    name       TEXT NOT NULL,
    year_built INTEGER CHECK (year_built BETWEEN 1800 AND 2100),
    length_m   REAL CHECK (length_m > 0)
);""",
    "Suspension": """CREATE TABLE Suspension (
    bridge_id    TEXT PRIMARY KEY REFERENCES Bridge (bridge_id),
    cable_dia_mm REAL CHECK (cable_dia_mm > 0)
);""",
    "Truss": """CREATE TABLE Truss (
    bridge_id  TEXT PRIMARY KEY REFERENCES Bridge (bridge_id),
    truss_type TEXT
);""",
    "Arch": """CREATE TABLE Arch (
    bridge_id TEXT PRIMARY KEY REFERENCES Bridge (bridge_id),
    rise_m    REAL CHECK (rise_m > 0)
);""",
    "Manufacturer": """CREATE TABLE Manufacturer (
    mfr_name TEXT PRIMARY KEY,
    country  TEXT
);""",
    "Sensor": """CREATE TABLE Sensor (
    sensor_id    TEXT PRIMARY KEY,
    type         TEXT NOT NULL
                 CHECK (type IN ('accelerometer', 'strain_gauge', 'temperature')),
    install_year INTEGER,
    bridge_id    TEXT REFERENCES Bridge (bridge_id),
    mfr_name     TEXT REFERENCES Manufacturer (mfr_name)
);""",
    "Reading": """CREATE TABLE Reading (
    sensor_id TEXT NOT NULL REFERENCES Sensor (sensor_id),
    seq_no    INTEGER NOT NULL,
    time      TEXT NOT NULL,
    value     REAL,
    PRIMARY KEY (sensor_id, seq_no)
);""",
    "Engineer": """CREATE TABLE Engineer (
    emp_id        TEXT PRIMARY KEY,
    name          TEXT NOT NULL,
    supervisor_id TEXT REFERENCES Engineer (emp_id)
);""",
    "Inspects": """CREATE TABLE Inspects (
    emp_id    TEXT REFERENCES Engineer (emp_id),
    bridge_id TEXT REFERENCES Bridge (bridge_id),
    date      TEXT NOT NULL,
    rating    INTEGER CHECK (rating BETWEEN 0 AND 9),
    PRIMARY KEY (emp_id, bridge_id)
);""",
}

# Creation order respects the foreign keys: a table is created after the tables it references.
TABLES = list(DDL)

# ---------------------------------------------------------------------------
# Instance
# ---------------------------------------------------------------------------

ROWS = {
    "Bridge": [
        ("B1", "Mill Creek Bridge", 1931, 118),
        ("B2", "Riverside Bridge", 1959, 240),
        ("B3", "Harbor Bridge", 1972, 610),
        ("B4", "Station Street Bridge", 1908, 85),
    ],
    "Suspension": [("B3", 410)],
    "Truss": [("B2", "Warren"), ("B4", "Pratt")],
    "Arch": [("B1", 14.5)],
    "Manufacturer": [
        ("Borealis Instruments", "Canada"),
        ("Kestrel Gauges", "USA"),
        ("Meridian Sensing", "Germany"),
    ],
    "Sensor": [
        ("S1", "accelerometer", 2019, "B1", "Kestrel Gauges"),
        ("S2", "strain_gauge", 2019, "B1", "Kestrel Gauges"),
        ("S3", "strain_gauge", 2021, "B2", "Borealis Instruments"),
        ("S4", "accelerometer", 2021, "B3", "Borealis Instruments"),
        ("S5", "temperature", 2022, "B3", "Kestrel Gauges"),
        ("S6", "strain_gauge", 2022, "B3", "Borealis Instruments"),
    ],
    "Engineer": [
        ("E1", "Ana Ruiz", None),
        ("E2", "Ben Okafor", "E1"),
        ("E3", "Chen Wei", "E1"),
        ("E4", "Dana Kowalski", "E2"),
    ],
    "Inspects": [
        ("E1", "B4", "2024-09-30", 5),
        ("E2", "B1", "2025-10-14", 6),
        ("E2", "B3", "2025-06-12", 7),
        ("E3", "B3", "2026-04-08", 7),
        ("E3", "B4", "2026-03-02", 4),
        ("E4", "B1", "2026-05-20", 5),
    ],
}

FIRST_DAY = dt.date(2026, 8, 1)
LAST_DAY = dt.date(2026, 9, 28)
S2_LAST_DAY = dt.date(2026, 8, 26)          # strain gauge S2 goes silent after this day
S5_MISSING = {dt.date(2026, 8, 19), dt.date(2026, 9, 3), dt.date(2026, 9, 4)}  # value failed range check

# Daily summary per sensor: peak acceleration in mg, peak strain in microstrain, mean temperature in °C
LEVEL = {"S1": 12.0, "S2": 80.0, "S3": 110.0, "S4": 20.0, "S6": 140.0}
SPREAD = {"S1": 3.0, "S2": 12.0, "S3": 15.0, "S4": 5.0, "S6": 20.0}


def _readings(seed=778):
    rng = random.Random(seed)
    rows = []
    for sid, *_ in ROWS["Sensor"]:
        seq = 0
        day = FIRST_DAY
        while day <= LAST_DAY:
            if not (sid == "S2" and day > S2_LAST_DAY):
                seq += 1
                if sid == "S5":
                    k = (day - FIRST_DAY).days
                    value = None if day in S5_MISSING else round(25.0 - 0.1 * k + rng.uniform(-2, 2), 1)
                else:
                    value = round(LEVEL[sid] + rng.uniform(-SPREAD[sid], SPREAD[sid]), 1)
                rows.append((sid, seq, f"{day.isoformat()}T06:00:00Z", value))
            day += dt.timedelta(days=1)
    return rows


ROWS["Reading"] = _readings()


def build(con):
    """Create the nine tables in an open connection and load the instance."""
    con.execute("PRAGMA foreign_keys = ON")
    with con:
        for t in TABLES:
            con.execute(DDL[t])
        for t in TABLES:
            if ROWS[t]:
                marks = ", ".join("?" * len(ROWS[t][0]))
                con.executemany(f"INSERT INTO {t} VALUES ({marks})", ROWS[t])
    return con


def connect(path=":memory:"):
    return build(sqlite3.connect(path))


def script():
    """The whole database as one SQL script (schema plus INSERT statements)."""
    con = connect()
    return "\n".join(con.iterdump())


if __name__ == "__main__":
    import os
    path = sys.argv[1] if len(sys.argv) > 1 else "bridges.sqlite"
    if os.path.exists(path):
        sys.exit(f"{path} already exists; remove it first")
    con = connect(path)
    counts = {t: con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] for t in TABLES}
    con.close()
    print(f"wrote {path}: " + ", ".join(f"{t} {n}" for t, n in counts.items()))