import json
from pathlib import Path

folder = Path("lesson_files")
folder.mkdir(exist_ok=True)

orders = [
    {"customer": "Amara", "drink": "Latte", "price": 3.40},
    {"customer": "Jonas", "drink": "Tea", "price": 2.10},
    {"customer": "Amara", "drink": "Mocha", "price": 3.80},
]
(folder / "orders.json").write_text(json.dumps(orders))
print("Saved", len(orders), "orders")

loaded = json.loads((folder / "orders.json").read_text())
amara_total = 0
for order in loaded:
    if order["customer"] == "Amara":
        amara_total += order["price"]
print(f"Amara spent ${amara_total:.2f}")

import sqlite3

db_path = folder / "cafe.db"
if db_path.exists():
    db_path.unlink()      # start fresh every time we run the lesson
print("Database file exists yet?", db_path.exists())

conn = sqlite3.connect(db_path)
print("Connected:", type(conn).__name__)
print("Database file exists now?", db_path.exists())

# the connection is the phone line, the cursor runs our SQL commands
cursor = conn.cursor()
cursor.execute("SELECT 2 + 3")
print(cursor.fetchone())

cursor.execute("SELECT 'hello', UPPER('sqlite'), 7 * 6, ROUND(10 / 3.0, 2)")
row = cursor.fetchone()
print(row)
print(type(row), "with", len(row), "values")

cursor.execute("""
    CREATE TABLE drinks (
        id      INTEGER PRIMARY KEY,
        name    TEXT NOT NULL,
        size    TEXT,
        price   REAL,
        vegan   INTEGER
    )
""")
print("Table 'drinks' created")

cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
print("Tables in the database:", cursor.fetchall())

cursor.execute("PRAGMA table_info(drinks)")
for column in cursor.fetchall():
    print(f"  column {column[1]:<6} type {column[2]}")

cursor.execute("CREATE TABLE drinks (id INTEGER PRIMARY KEY, name TEXT)")

cursor.execute("CREATE TABLE IF NOT EXISTS drinks (id INTEGER PRIMARY KEY, name TEXT)")
print("No error: IF NOT EXISTS skips it because the table is already there")

cursor.execute("INSERT INTO drinks (name, size, price, vegan) VALUES ('Latte', 'regular', 3.40, 0)")
conn.commit()
print("New row id:", cursor.lastrowid)

new_drink = ("Oat Flat White", "regular", 3.60, 1)
cursor.execute("INSERT INTO drinks (name, size, price, vegan) VALUES (?, ?, ?, ?)", new_drink)
conn.commit()
print("New row id:", cursor.lastrowid)

menu = [
    ("Latte", "large", 3.90, 0),
    ("Tea", "regular", 2.10, 1),
    ("Mocha", "regular", 3.80, 0),
    ("Hot Chocolate", "large", 3.50, 0),
    ("Espresso", "small", 2.20, 1),
    ("Iced Latte", "large", 4.10, 0),
    ("Chai Latte", "regular", 3.30, 0),
]
cursor.executemany("INSERT INTO drinks (name, size, price, vegan) VALUES (?, ?, ?, ?)", menu)
conn.commit()
print("Rows added:", cursor.rowcount)

cursor.execute("SELECT * FROM drinks")
for row in cursor.fetchall():
    print(row)

cursor.execute("INSERT INTO drinks (name, size, price, vegan) VALUES (?, ?, ?, ?)", ("Matcha", 3.70))

cursor.execute("INSERT INTO drinks (name, size, price, vegan) VALUES (?, ?, ?, ?)",
               ("Matcha", "regular", 3.70, 1))
conn.commit()
print("Four placeholders, four values. Matcha is row", cursor.lastrowid)

cursor.execute("INSERT INTO drinks (name, size, price, vegan) VALUES (?, ?, ?, ?)",
               ("Babyccino", "small", 0.50, 0))
conn.commit()
print("Answer:", cursor.lastrowid)

cursor.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, card_pin TEXT)")
cursor.executemany("INSERT INTO customers (name, city, card_pin) VALUES (?, ?, ?)", [
    ("Amara", "Leeds", "4821"),
    ("Jonas", "Bristol", "1937"),
    ("Mei", "Leeds", "7702"),
    ("Priya", "York", "5560"),
    ("Leo", "Bristol", "3018"),
    ("Hana", "Leeds", "8844"),
    ("Zoe", "York", "2205"),
])
conn.commit()
print("Customers stored:", cursor.rowcount)

def find_customer_unsafe(name):
    sql = f"SELECT name, city FROM customers WHERE name = '{name}'"
    print("  SQL sent:", sql)
    return cursor.execute(sql).fetchall()

print(find_customer_unsafe("Jonas"))

sneaky = "nobody' OR '1'='1"
print(find_customer_unsafe(sneaky))

find_customer_unsafe("O'Brien")

def find_customer(name):
    sql = "SELECT name, city FROM customers WHERE name = ?"
    return cursor.execute(sql, (name,)).fetchall()

print("Jonas   ->", find_customer("Jonas"))
print("O'Brien ->", find_customer("O'Brien"))
print("sneaky  ->", find_customer(sneaky))

search = input("Search for a customer: ").strip().title()
results = find_customer(search)
if results:
    print(f"Found: {results[0][0]} from {results[0][1]}")
else:
    print("No customer by that name")

cursor.execute("SELECT name, price FROM drinks WHERE price < 3")
for name, price in cursor.fetchall():
    print(f"{name:<12} ${price:.2f}")

cursor.execute("SELECT name, size FROM drinks WHERE vegan = 1 AND size = 'regular'")
print("Vegan regulars:", cursor.fetchall())

cursor.execute("SELECT name FROM drinks WHERE size = 'small' OR price > 4")
print("Small or pricey:", cursor.fetchall())

cursor.execute("SELECT name, size FROM drinks WHERE name LIKE '%latte%'")
for name, size in cursor.fetchall():
    print(f"  {name} ({size})")

cursor.execute("SELECT name, price FROM drinks ORDER BY price DESC LIMIT 3")
print("Three most expensive:")
for position, (name, price) in enumerate(cursor.fetchall(), start=1):
    print(f"  {position}. {name} ${price:.2f}")

cursor.execute("SELECT name, size, price FROM drinks ORDER BY size, name")
for row in cursor.fetchall()[:6]:
    print(row)

cursor.execute("SELECT name FROM drinks WHERE price BETWEEN 3 AND 3.6")
print("Mid-price:", [row[0] for row in cursor.fetchall()])

cursor.execute("SELECT name FROM drinks WHERE size IN ('small', 'large')")
print("Not regular:", [row[0] for row in cursor.fetchall()])

cursor.execute("SELECT name, price FROM drinks WHERE price > ?", (3.5,))
print("First match:", cursor.fetchone())
print("Next match: ", cursor.fetchone())
print("The rest:   ", cursor.fetchall())

for row in cursor.execute("SELECT name FROM drinks WHERE vegan = 1 ORDER BY name"):
    print("Vegan:", row[0])

import random

cursor.execute("""
    CREATE TABLE orders (
        id          INTEGER PRIMARY KEY,
        customer_id INTEGER,
        drink       TEXT,
        qty         INTEGER,
        price       REAL,
        day         TEXT
    )
""")
print("orders table ready")

random.seed(9)
prices = {"Latte": 3.40, "Tea": 2.10, "Mocha": 3.80, "Espresso": 2.20, "Chai Latte": 3.30}
days = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]

fake_orders = []
for _ in range(40):
    drink = random.choice(list(prices))
    fake_orders.append((random.randint(1, 6), drink, random.randint(1, 3), prices[drink], random.choice(days)))

cursor.executemany("INSERT INTO orders (customer_id, drink, qty, price, day) VALUES (?, ?, ?, ?, ?)", fake_orders)
conn.commit()
print(len(fake_orders), "orders, the first three:", fake_orders[:3])

count = cursor.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
cups = cursor.execute("SELECT SUM(qty) FROM orders").fetchone()[0]
print(f"{count} orders, {cups} cups served")

cursor.execute("""
    SELECT ROUND(SUM(qty * price), 2), ROUND(AVG(qty * price), 2), MIN(price), MAX(price)
    FROM orders
""")
total, average, cheapest, dearest = cursor.fetchone()
print(f"Takings ${total}, average order ${average}")
print(f"Cheapest drink ${cheapest}, dearest ${dearest}")

cursor.execute("""
    SELECT drink, COUNT(*), SUM(qty)
    FROM orders
    GROUP BY drink
    ORDER BY SUM(qty) DESC
""")
print(f"{'Drink':<11}{'Orders':>7}{'Cups':>6}")
for drink, n_orders, n_cups in cursor.fetchall():
    print(f"{drink:<11}{n_orders:>7}{n_cups:>6}")

cursor.execute("""
    SELECT day, ROUND(SUM(qty * price), 2) AS takings
    FROM orders
    GROUP BY day
    ORDER BY takings DESC
""")
for day, takings in cursor.fetchall():
    print(f"{day}  ${takings:>6.2f}  {'#' * int(takings // 5)}")

cursor.execute("""
    SELECT customer_id, COUNT(*) AS visits
    FROM orders
    GROUP BY customer_id
    HAVING visits >= 7
    ORDER BY visits DESC
""")
print("Regulars (7+ orders):", cursor.fetchall())

answer = input("WHERE or HAVING? ").strip().upper()
if answer == "WHERE":
    print("Correct! WHERE filters rows first, HAVING filters the groups afterwards.")
else:
    print("Not quite: WHERE filters rows first, HAVING filters the groups afterwards.")

cursor.execute("""
    SELECT drink, SUM(qty) FROM orders
    WHERE day IN ('Sat', 'Sun')
    GROUP BY drink ORDER BY 2 DESC
""")
print("Weekend favourites:", cursor.fetchall())

cursor.execute("SELECT name, price FROM drinks WHERE size = 'large'")
print("Before:", cursor.fetchall())

cursor.execute("UPDATE drinks SET price = price + 0.20 WHERE size = 'large'")
conn.commit()
print("Rows changed:", cursor.rowcount)

cursor.execute("SELECT name, ROUND(price, 2) FROM drinks WHERE size = 'large'")
print("After: ", cursor.fetchall())

cursor.execute("UPDATE drinks SET vegan = ?, name = ? WHERE id = ?", (1, "Oat Latte", 1))
conn.commit()
print(cursor.execute("SELECT * FROM drinks WHERE id = 1").fetchone())

cursor.execute("DELETE FROM drinks WHERE name = ?", ("Babyccino",))
conn.commit()
print("Deleted:", cursor.rowcount)
print("Drinks left:", cursor.execute("SELECT COUNT(*) FROM drinks").fetchone()[0])

cursor.execute("UPDATE drinks SET prise = 2.50 WHERE name = 'Tea'")

cursor.execute("UPDATE drinks SET price = 2.50 WHERE name = 'Tea'")
conn.commit()
print("Tea now costs", cursor.execute("SELECT price FROM drinks WHERE name = 'Tea'").fetchone()[0])

cursor.execute("DELETE FROM drinks")
print("Drinks visible right now:", cursor.execute("SELECT COUNT(*) FROM drinks").fetchone()[0])

conn.rollback()
print("After rollback:", cursor.execute("SELECT COUNT(*) FROM drinks").fetchone()[0])

cursor.execute("CREATE TABLE wallets (owner TEXT PRIMARY KEY, balance REAL CHECK (balance >= 0))")
cursor.executemany("INSERT INTO wallets VALUES (?, ?)", [("Amara", 20.0), ("Jonas", 5.0)])
conn.commit()

def show_wallets():
    for owner, balance in cursor.execute("SELECT owner, balance FROM wallets"):
        print(f"  {owner:<6} ${balance:.2f}")

show_wallets()

def transfer(sender, receiver, amount):
    try:
        cursor.execute("UPDATE wallets SET balance = balance + ? WHERE owner = ?", (amount, receiver))
        cursor.execute("UPDATE wallets SET balance = balance - ? WHERE owner = ?", (amount, sender))
        conn.commit()
        print(f"Sent ${amount:.2f} from {sender} to {receiver}")
    except sqlite3.IntegrityError as problem:
        conn.rollback()
        print(f"Transfer cancelled ({problem})")

transfer("Amara", "Jonas", 8)
show_wallets()

transfer("Jonas", "Amara", 50)
show_wallets()

try:
    with conn:
        conn.execute("UPDATE wallets SET balance = balance + 100 WHERE owner = 'Amara'")
        conn.execute("UPDATE wallets SET balance = balance - 100 WHERE owner = 'Jonas'")
except sqlite3.IntegrityError:
    print("with conn: rolled back automatically")
show_wallets()

cursor.execute("INSERT INTO drinks (size, price, vegan) VALUES (?, ?, ?)", ("small", 2.90, 1))

cursor.execute("INSERT INTO drinks (name, size, price, vegan) VALUES (?, ?, ?, ?)",
               ("Cortado", "small", 2.90, 1))
conn.commit()
print("Cortado added as drink", cursor.lastrowid)

print(cursor.execute("SELECT id, customer_id, drink FROM orders LIMIT 3").fetchall())
print(cursor.execute("SELECT id, name FROM customers LIMIT 3").fetchall())

cursor.execute("""
    SELECT orders.id, customers.name, orders.drink, orders.qty
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
    ORDER BY orders.id
    LIMIT 5
""")
for row in cursor.fetchall():
    print(row)

cursor.execute("""
    SELECT c.name, c.city, ROUND(SUM(o.qty * o.price), 2) AS spent
    FROM orders AS o
    JOIN customers AS c ON o.customer_id = c.id
    GROUP BY c.name
    ORDER BY spent DESC
""")
for name, city, spent in cursor.fetchall():
    print(f"{name:<6} {city:<8} ${spent:>6.2f}")

cursor.execute("""
    SELECT c.name, COUNT(o.id) AS n_orders
    FROM customers AS c
    LEFT JOIN orders AS o ON o.customer_id = c.id
    GROUP BY c.name
    ORDER BY n_orders
""")
print(cursor.fetchall())

cursor.execute("""
    SELECT c.city, COUNT(*) AS orders, SUM(o.qty) AS cups
    FROM orders AS o JOIN customers AS c ON o.customer_id = c.id
    GROUP BY c.city
""")
for city, n, cups in cursor.fetchall():
    print(f"{city}: {n} orders, {cups} cups")

plants_db = sqlite3.connect(":memory:")
plants_db.execute("CREATE TABLE plants (id INTEGER PRIMARY KEY, name TEXT, every_days INTEGER)")
plants_db.execute("CREATE TABLE waterings (plant_id INTEGER, day INTEGER)")
plants_db.executemany("INSERT INTO plants (name, every_days) VALUES (?, ?)",
                      [("Basil", 2), ("Cactus", 14), ("Fern", 4), ("Monstera", 7)])
print("Plants:", plants_db.execute("SELECT name FROM plants").fetchall())

log = [(1, 1), (1, 3), (1, 5), (2, 1), (3, 2), (3, 6), (4, 3)]
plants_db.executemany("INSERT INTO waterings VALUES (?, ?)", log)
print("Waterings logged:", plants_db.execute("SELECT COUNT(*) FROM waterings").fetchone()[0])

def thirsty_plants(today):
    rows = plants_db.execute("""
        SELECT p.name, p.every_days, MAX(w.day) AS last
        FROM plants AS p JOIN waterings AS w ON w.plant_id = p.id
        GROUP BY p.id
    """).fetchall()
    for name, every, last in rows:
        due = last + every
        status = "WATER NOW" if due <= today else f"ok until day {due}"
        print(f"  {name:<9} last watered day {last:<2} {status}")

print("Day 7:")
thirsty_plants(7)

plants_db.execute("INSERT INTO waterings VALUES (?, ?)", (1, 7))
print("Watered the Basil. Day 8:")
thirsty_plants(8)
plants_db.close()

conn.row_factory = sqlite3.Row
cursor = conn.cursor()

row = cursor.execute("SELECT * FROM drinks WHERE name = 'Mocha'").fetchone()
print(row["name"], "costs", row["price"])
print("Columns:", row.keys())

print("Still works by position:", row[1], row[3])
print("As a real dict:", dict(row))

rows = cursor.execute("SELECT name, price FROM drinks WHERE vegan = 1").fetchall()
vegan_menu = [dict(r) for r in rows]
for item in vegan_menu:
    print(item)
print("Ready for JSON:", json.dumps(vegan_menu[:2]))

print(row["nmae"])

print(row["name"])
print("Safe check:", "nmae" in row.keys(), "name" in row.keys())

conn.close()
print("Old connection closed")

conn.execute("SELECT COUNT(*) FROM drinks")

from contextlib import closing

with closing(sqlite3.connect(db_path)) as conn:
    print("Drinks on the menu:", conn.execute("SELECT COUNT(*) FROM drinks").fetchone()[0])
print("Connection closed automatically")

def run_query(sql, params=()):
    """Open the database, run one query, return a list of dicts, close it."""
    with closing(sqlite3.connect(db_path)) as conn:
        conn.row_factory = sqlite3.Row
        return [dict(row) for row in conn.execute(sql, params)]

print(run_query("SELECT name, price FROM drinks WHERE price > ? ORDER BY price", (3.8,)))

def run_change(sql, params=()):
    """Run an INSERT, UPDATE or DELETE and commit it. Returns rows affected."""
    with closing(sqlite3.connect(db_path)) as conn:
        with conn:
            return conn.execute(sql, params).rowcount

print("Updated:", run_change("UPDATE drinks SET price = ? WHERE name = ?", (2.30, "Espresso")))
print(run_query("SELECT name, price FROM drinks WHERE name = ?", ("Espresso",)))

import pandas as pd

conn = sqlite3.connect(db_path)
menu_df = pd.read_sql_query("SELECT name, size, price FROM drinks ORDER BY price DESC", conn)
print(menu_df.head(6))

sales = pd.read_sql_query("""
    SELECT c.name AS customer, o.drink, o.qty, o.price, o.day
    FROM orders AS o JOIN customers AS c ON o.customer_id = c.id
    WHERE o.day = ?
""", conn, params=("Sat",))
print(sales)

sales["total"] = sales["qty"] * sales["price"]
print("Saturday takings: $", round(sales["total"].sum(), 2))
print(sales.groupby("drink")["qty"].sum())

specials = pd.DataFrame({"name": ["Pumpkin Latte", "Iced Mocha"], "price": [4.20, 4.30]})
specials.to_sql("specials", conn, if_exists="replace", index=False)
print(conn.execute("SELECT * FROM specials").fetchall())
conn.close()

from datetime import date, timedelta

library_path = folder / "library.db"
if library_path.exists():
    library_path.unlink()

lib = sqlite3.connect(library_path)
lib.row_factory = sqlite3.Row
TODAY = date(2026, 3, 16)
print("Library database ready, today is", TODAY)

with lib:
    lib.execute("""
        CREATE TABLE books (
            id     INTEGER PRIMARY KEY,
            title  TEXT NOT NULL UNIQUE,
            author TEXT,
            year   INTEGER
        )""")
    lib.execute("""
        CREATE TABLE loans (
            id        INTEGER PRIMARY KEY,
            book_id   INTEGER NOT NULL,
            friend    TEXT NOT NULL,
            lent_on   TEXT NOT NULL,
            due_on    TEXT NOT NULL,
            returned  INTEGER DEFAULT 0
        )""")
print([r["name"] for r in lib.execute("SELECT name FROM sqlite_master WHERE type = 'table'")])

def add_book(title, author, year):
    try:
        with lib:
            lib.execute("INSERT INTO books (title, author, year) VALUES (?, ?, ?)", (title, author, year))
        print(f"  Added: {title}")
    except sqlite3.IntegrityError:
        print(f"  '{title}' is already on your shelf")

add_book("Dune", "Frank Herbert", 1965)
add_book("Dune", "Frank Herbert", 1965)

for title, author, year in [
    ("The Hobbit", "J. R. R. Tolkien", 1937),
    ("Matilda", "Roald Dahl", 1988),
    ("Small Island", "Andrea Levy", 2004),
    ("The Martian", "Andy Weir", 2011),
    ("Persuasion", "Jane Austen", 1817),
]:
    add_book(title, author, year)

def find_book(title):
    return lib.execute("SELECT * FROM books WHERE title = ?", (title,)).fetchone()

def is_on_loan(book_id):
    sql = "SELECT COUNT(*) FROM loans WHERE book_id = ? AND returned = 0"
    return lib.execute(sql, (book_id,)).fetchone()[0] > 0

book = find_book("Matilda")
print(dict(book), "| on loan?", is_on_loan(book["id"]))

def lend_book(title, friend, lent_on=TODAY, days=14):
    book = find_book(title)
    if book is None:
        print(f"  No book called '{title}'")
        return
    if is_on_loan(book["id"]):
        print(f"  Sorry {friend}, '{title}' is already lent out")
        return
    due = lent_on + timedelta(days=days)
    with lib:
        lib.execute("INSERT INTO loans (book_id, friend, lent_on, due_on) VALUES (?, ?, ?, ?)",
                    (book["id"], friend, lent_on.isoformat(), due.isoformat()))
    print(f"  Lent '{title}' to {friend}, due back {due}")

lend_book("Matilda", "Amara", lent_on=date(2026, 2, 10))
lend_book("Small Island", "Jonas", lent_on=date(2026, 2, 25))
lend_book("Persuasion", "Mei", lent_on=date(2026, 3, 9))
lend_book("Matilda", "Hana")
lend_book("Moby Dick", "Leo")

def return_book(title):
    book = find_book(title)
    if book is None:
        print(f"  No book called '{title}'")
        return
    with lib:
        changed = lib.execute("UPDATE loans SET returned = 1 WHERE book_id = ? AND returned = 0",
                              (book["id"],)).rowcount
    print(f"  Thanks for returning '{title}'" if changed else f"  '{title}' wasn't on loan")

return_book("Small Island")
return_book("Dune")

def overdue_report(today=TODAY):
    rows = lib.execute("""
        SELECT b.title, l.friend, l.due_on
        FROM loans AS l JOIN books AS b ON l.book_id = b.id
        WHERE l.returned = 0 AND l.due_on < ?
        ORDER BY l.due_on
    """, (today.isoformat(),)).fetchall()
    print(f"  Overdue on {today}: {len(rows)} book(s)")
    for r in rows:
        late = (today - date.fromisoformat(r["due_on"])).days
        print(f"  - {r['title']:<12} with {r['friend']:<6} {late} days late")

overdue_report()

def shelf_report():
    rows = lib.execute("""
        SELECT b.title, b.author, l.friend
        FROM books AS b
        LEFT JOIN loans AS l ON l.book_id = b.id AND l.returned = 0
        ORDER BY b.title
    """).fetchall()
    for r in rows:
        where = f"with {r['friend']}" if r["friend"] else "on the shelf"
        print(f"  {r['title']:<13} {r['author']:<17} {where}")

shelf_report()

def run_library():
    while True:
        choice = input("[l]end [r]eturn [o]verdue [s]helf [q]uit: ").strip().lower()
        if choice == "l":
            lend_book(input("  Title: "), input("  Friend: "))
        elif choice == "r":
            return_book(input("  Title: "))
        elif choice == "o":
            overdue_report()
        elif choice == "s":
            shelf_report()
        elif choice == "q":
            print("  Goodbye, happy reading!")
            break
        else:
            print("  Please choose l, r, o, s or q")
print("Menu ready")

run_library()  # asks for input()

history = pd.read_sql_query("""
    SELECT b.title, l.friend, l.lent_on, l.due_on, l.returned
    FROM loans AS l JOIN books AS b ON l.book_id = b.id
    ORDER BY l.lent_on
""", lib)
print(history)

busiest = lib.execute("""
    SELECT friend, COUNT(*) AS n FROM loans GROUP BY friend ORDER BY n DESC, friend LIMIT 1
""").fetchone()
print(f"Most loans: {busiest['friend']} ({busiest['n']})")
lib.close()
print("Library saved in", library_path)

with closing(sqlite3.connect(library_path)) as again:
    books, loans = again.execute("SELECT (SELECT COUNT(*) FROM books), (SELECT COUNT(*) FROM loans)").fetchone()
print(f"Reopened the file: {books} books and {loans} loans remembered")