# /// script
# requires-python = ">=3.10"
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""A bitemporal table in SQLite.

A rate table with valid_from/valid_to (when the rate applies) and
tx_from/tx_to (when the database believed it). Ravi's rate of 20 is recorded
on 1 March, then corrected on 15 March to 25, valid from 1 March, by closing
the old row's tx_to and inserting the new row. Two questions: what did we
believe on 10 March about 5 March, and what do we believe now? Then the same
history kept with a single updated_at column.
"""
import argparse, json, sqlite3

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE rate (person TEXT, rate INT, valid_from TEXT, valid_to TEXT, tx_from TEXT, tx_to TEXT)")
c.execute("INSERT INTO rate VALUES ('Ravi', 20, '2025-03-01', '9999-12-31', '2025-03-01', '9999-12-31')")
c.execute("UPDATE rate SET tx_to = '2025-03-15' WHERE person = 'Ravi' AND tx_to = '9999-12-31'")        # the correction closes the old belief
c.execute("INSERT INTO rate VALUES ('Ravi', 25, '2025-03-01', '9999-12-31', '2025-03-15', '9999-12-31')")
AS_OF = "SELECT rate FROM rate WHERE person = 'Ravi' AND valid_from <= ? AND ? < valid_to AND tx_from <= ? AND ? < tx_to"
believed = c.execute(AS_OF, ("2025-03-05", "2025-03-05", "2025-03-10", "2025-03-10")).fetchone()[0]
now = c.execute(AS_OF, ("2025-03-05", "2025-03-05", "2025-03-20", "2025-03-20")).fetchone()[0]
u = sqlite3.connect(":memory:"); u.execute("CREATE TABLE rate (person TEXT, rate INT, updated_at TEXT)")
u.execute("INSERT INTO rate VALUES ('Ravi', 20, '2025-03-01')"); u.execute("UPDATE rate SET rate = 25, updated_at = '2025-03-15' WHERE person = 'Ravi'")
single = u.execute("SELECT rate, updated_at FROM rate").fetchall()
rows = [{"question": "rate for 5 March, as believed on 10 March", "bitemporal": believed, "updated_at_only": "cannot answer: the 20 is gone"},
        {"question": "rate for 5 March, as believed now", "bitemporal": now, "updated_at_only": str(single[0][0])}]
for r in rows: print(f"{r['question']:<44} bitemporal: {r['bitemporal']}   single updated_at: {r['updated_at_only']}")
print("the single-column table holds:", single)

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"questions": rows}}, f)