"""Seed data and builders for the hotel example DB. python fake_data/seed.py [path/to/hotel.db] # write a seed file (for inspection) """ from __future__ import annotations import json import logging import sys from datetime import date, time, timedelta from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parent.parent)) from hotel_db import ( # noqa: E402 PRICING, TODAY, HotelDB, compute_invoice, ) logger = logging.getLogger("hotel-receptionist.seed") DEFAULT_DB_PATH = Path(__file__).resolve().parent / "hotel.db" _LATE = PRICING.late_checkout # fmt: off # (id (room number, floor+number), type, nightly_rate_cents, max_occupancy, smoking, pets, view) ROOMS = [ ("RM_201", "king", 24000, 2, 0, 0, "city"), ("RM_202", "king", 26000, 2, 0, 1, "ocean"), ("RM_203", "king", 24000, 2, 1, 0, "city"), ("RM_204", "queen_2beds", 22000, 4, 0, 0, "city"), ("RM_205", "queen_2beds", 22000, 4, 0, 1, "garden"), ("RM_206", "double_queen", 26000, 4, 0, 0, "ocean"), ("RM_301", "king", 28000, 2, 0, 0, "ocean"), ("RM_302", "king", 28000, 2, 0, 0, "ocean"), ("RM_303", "queen_2beds", 24000, 4, 0, 0, "city"), ("RM_304", "double_queen", 28000, 4, 0, 1, "ocean"), ("RM_401", "suite", 48000, 4, 0, 1, "ocean"), ("RM_402", "suite", 52000, 4, 0, 0, "ocean"), ("RM_PH", "penthouse", 120000, 6, 0, 1, "ocean"), ] # (label, capacity, location, description) TABLES = [ ("T-01", 2, "indoor", "Window two-top overlooking the harbor"), ("T-02", 2, "indoor", "Quiet corner booth, tucked beside the wine wall"), ("T-03", 4, "indoor", "Round table beneath the chandelier"), ("T-04", 4, "indoor", "Velvet banquette along the main dining wall"), ("T-05", 6, "indoor", "Chef's table facing the open kitchen"), ("P-01", 2, "terrace", "Intimate table for two at the terrace railing"), ("P-02", 4, "terrace", "Terrace table under the string lights"), ("P-03", 4, "terrace", "Shaded terrace table by the herb garden"), ("B-01", 2, "bar", "High-top at the end of the marble bar"), ("B-02", 2, "bar", "Counter seats facing the bartenders"), ] # (first, last, email, phone, code_suffix, room, offset_days, nights, guests, extras, card4, status) # offset < 0 and offset+nights > 0 -> in-house now; offset > 0 -> upcoming; else departed. # Smith / García / Lee codes are referenced by the playground, so keep them stable. BOOKINGS = [ # In-house right now ("Sofía", "García", "sofia.garcia@proton.me", "+1 415 555 0107", "EF56", "401", -1, 4, 3, ["breakfast", "valet", "pets"], "0007", "confirmed"), ("Priya", "Nair", "priya.nair@gmail.com", "+1 510 555 0188", "KM21", "202", -2, 4, 2, ["breakfast"], "3310", "confirmed"), ("Amara", "Okafor", "amara.okafor@gmail.com", "+1 650 555 0121", "WX53", "206", -1, 2, 4, ["breakfast", "pets"], "5550", "confirmed"), ("Lucas", "Meyer", "lucas.meyer@gmx.de", "+49 30 5550173", "ZP19", "402", -3, 5, 2, ["breakfast", "valet"], "9041", "confirmed"), ("Vivienne", "Laurent", "v.laurent@me.com", "+1 415 555 0193", "PH01", "PH", -2, 6, 2, ["breakfast", "valet", "pets"], "1206", "confirmed"), # In-house and being fished for by an outside caller - presence must never be disclosed ("Jonathan", "Pierce", "j.pierce@gmail.com", "+1 415 555 0233", "JP65", "303", -1, 3, 1, [], "5151", "confirmed"), # In-house with an early flight - the wake-up call caller ("Frank", "Adler", "frank.adler@gmail.com", "+1 415 555 0277", "FA09", "304", -1, 3, 1, [], "6203", "confirmed"), # --- Full house tonight (oversold) ------------------------------------- # Dana Holt holds room 301 through tomorrow morning - and so does Kenji # Tanaka (RT88, checked in today): the double-booking behind the # "Confirmed guest, no room available tonight" walk scenario. The four # one-nighters fill every otherwise-free room TONIGHT ONLY, so the # re-accommodation search honestly comes up empty and 301 frees tomorrow. ("Dana", "Holt", "dana.holt@gmail.com", "+1 415 555 0341", "DH27", "301", -2, 3, 2, [], "9034", "confirmed"), ("Paul", "Greer", "paul.greer@gmail.com", "+1 415 555 0356", "PG11", "203", 0, 1, 1, [], "2218", "confirmed"), ("Rita", "Moss", "rita.moss@me.com", "+1 415 555 0368", "QM17", "204", 0, 1, 2, [], "7745", "confirmed"), ("Lena", "Fischer", "lena.fischer@gmx.de", "+49 30 5550441", "LF73", "302", 0, 1, 1, [], "6071", "confirmed"), # 205 (the garden queen) stays free tonight ON PURPOSE: it's the one concrete # fix the desk can offer Robert Klein ("I booked a garden view!") - and it's # a lower rate than Kenji Tanaka's king, so the walk resolver correctly # never offers it to him and his walk scenario stays intact. # --- Double-booked next weekend, but the house can absorb it ----------- # Tom Whelan's double queen (206) collides with Grace Lin's stay, and the # other double queen (304) is blocked by Noah Petrov - so the only room # that fits his family of four is the suite: the free-upgrade scenario. ("Tom", "Whelan", "tom.whelan@gmail.com", "+1 415 555 0457", "TW55", "206", 4, 3, 4, [], "5126", "confirmed"), ("Grace", "Lin", "grace.lin@gmail.com", "+1 415 555 0463", "GL09", "206", 3, 3, 3, [], "8854", "confirmed"), ("Noah", "Petrov", "noah.petrov@gmail.com", "+1 415 555 0478", "NP66", "304", 3, 4, 4, [], "1937", "confirmed"), ("Kenji", "Tanaka", "kenji.tanaka@gmail.com", "+1 415 555 0164", "RT88", "301", 0, 3, 2, ["valet"], "7782", "confirmed"), # Checked in today, king city room - the "unhappy with their room" caller # (insists he booked a garden view; the record says otherwise) ("Robert", "Klein", "robert.klein@gmail.com", "+1 415 555 0377", "RK20", "201", 0, 2, 1, [], "8412", "confirmed"), # Arriving tomorrow ("Hiroshi", "Sato", "h.sato@gmail.com", "+1 415 555 0211", "BN23", "204", 1, 2, 3, ["breakfast"], "8821", "confirmed"), # Upcoming ("Eleanor", "Smith", "eleanor.smith@gmail.com", "+1 415 555 0142", "AB12", "203", 5, 2, 2, ["breakfast"], "4242", "confirmed"), ("Marcus", "Johnson", "m.johnson@outlook.com", "+1 628 555 0199", "CD34", "205", 9, 3, 4, ["breakfast", "valet"], "1881", "confirmed"), # Smoking room (203 is the only smoking-permitted room) ("Mei", "Chen", "mei.chen@gmail.com", "+1 415 555 0222", "MN42", "203", 14, 2, 2, ["breakfast"], "4477", "confirmed"), # Departed (last week / weeks ago) - source of disputes + invoice lookups ("Daniel", "Lee", "daniel.lee@gmail.com", "+1 415 555 0104", "GH78", "302", -6, 2, 2, ["late_checkout"], "9999", "confirmed"), ("Olivia", "Brandt", "olivia.brandt@me.com", "+1 415 555 0288", "QT55", "204", -10, 3, 2, ["breakfast"], "6677", "confirmed"), ("Aino", "Virtanen", "aino.virtanen@gmail.com", "+358 9 5550144", "JX31", "303", -14, 4, 3, ["breakfast", "valet"], "5512", "confirmed"), # No-show (dates passed, guest never checked in; card-guaranteed and charged, # no cancellation on record - the "Angry no-show charge dispute" caller) ("Tanya", "Richardson", "tanya.richardson@gmail.com", "+1 248 555 0291", "NS44", "304", -4, 2, 1, [], "7321", "confirmed"), # Cancelled (was a future booking that got cancelled - good for "I cancelled, where's my refund") ("Felix", "Wagner", "felix.wagner@me.com", "+1 415 555 0312", "FW77", "402", 3, 2, 2, ["breakfast", "valet"], "2299", "cancelled"), ] # (first, last, phone, party, offset_days, hour, minute, code_suffix, table, notes, status) RESERVATIONS = [ # Tonight ("Marcus", "Bennett", "+1 415 555 0231", 4, 0, 19, 0, "JK90", "T-03", "Birthday", "confirmed"), ("Hannah", "Kowalski", "+1 415 555 0244", 2, 0, 20, 30, "LM12", "T-01", "Anniversary", "confirmed"), ("Sofía", "García", "+1 415 555 0107", 6, 0, 19, 30, "NP21", "T-05", "Family dinner", "confirmed"), ("Diego", "Herrera", "+1 415 555 0259", 2, 0, 18, 0, "QR34", "B-01", None, "confirmed"), # Tomorrow ("Yuki", "Sato", "+1 415 555 0277", 2, 1, 20, 0, "ST56", "P-01", None, "confirmed"), ("Olivia", "Brandt", "+1 415 555 0288", 4, 1, 18, 0, "UV78", "T-04", None, "confirmed"), # Day after tomorrow ("Tomás", "Silva", "+1 415 555 0290", 4, 2, 18, 30, "WX90", "T-04", None, "confirmed"), ("Naomi", "Adeyemi", "+1 415 555 0301", 4, 2, 19, 30, "YZ12", "T-04", "Window seat", "confirmed"), # Later this week ("Felix", "Wagner", "+1 415 555 0312", 4, 4, 20, 30, "AC34", "T-04", None, "confirmed"), ("Chiamaka", "Eze", "+1 415 555 0333", 2, 5, 19, 0, "BD45", "P-02", None, "confirmed"), # Cancelled (was for tomorrow, called this morning to cancel) ("Chen", "Wei", "+1 415 555 0344", 4, 1, 20, 0, "CW10", "T-04", None, "cancelled"), # Last night (already happened - for "I dined last night, can I leave feedback") ("Antonio", "Russo", "+1 415 555 0355", 2, -1, 19, 30, "AR22", "T-02", "Anniversary", "confirmed"), ] # (case, booking_code, line_item, amount, category, note, outcome, refund, status) DISPUTES = [ # Resolved - one per policy outcome to demo all the paths ("DSP-4K7M", "HTL-GH78", "Late checkout", _LATE, "late_checkout_fee", "Front desk said a 1 PM checkout would be fine.", "goodwill_waived", _LATE, "resolved"), ("DSP-9X2C", "HTL-EF56", "Minibar", 1800, "minibar", "Says they never opened the minibar.", "auto_refunded", 1800, "resolved"), ("DSP-5R8K", "HTL-QT55", "Room (3 nights)", 66000, "double_charge_billing_error", "Charged twice for the same stay - duplicate on the statement.", "auto_refunded", 66000, "resolved"), ("DSP-7M3X", "HTL-JX31", "Pet fee", 5000, "damage_cleaning", "No pet on the stay, but pet cleaning fee on the invoice.", "explained_no_action", 0, "resolved"), # Open / unresolved ("DSP-2H6T", "HTL-ZP19", "Room service", 8800, "room_service_restaurant", "Charged for a dinner they didn't order.", "escalated_to_manager", 0, "open"), ] # fmt: on def populate(db: HotelDB, today: date) -> None: """Insert seed rows into `db`. Booking check-in/check-out and reservation dates are stored as offsets from `today`.""" conn = db.connection conn.executemany( "INSERT INTO hotel_rooms (id, type, nightly_rate, max_occupancy, smoking, pets_allowed, room_view) VALUES (?,?,?,?,?,?,?)", ROOMS, ) conn.executemany( "INSERT INTO restaurant_tables (label, capacity, location, description) VALUES (?,?,?,?)", TABLES, ) for ( first, last, email, phone, suffix, room_no, offset, nights, guests, extras, card4, status, ) in BOOKINGS: room_row = conn.execute( "SELECT id, nightly_rate FROM hotel_rooms WHERE id = ?", (f"RM_{room_no}",) ).fetchone() assert room_row is not None, f"seed fixture references unknown room {room_no}" room_id, nightly = room_row check_in = today + timedelta(days=offset) subtotal, taxes, total, items = compute_invoice( nightly_rate=nightly, nights=nights, extras=extras ) code = f"HTL-{suffix}" conn.execute( "INSERT INTO hotel_bookings (code, room_id, first_name, last_name, email, phone, check_in, check_out, guests, extras, total, card_last4, status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)", ( code, room_id, first, last, email, phone, check_in.isoformat(), (check_in + timedelta(days=nights)).isoformat(), guests, ",".join(sorted(extras)), total, card4, status, ), ) conn.execute( "INSERT INTO hotel_invoices (booking_code, line_items, subtotal, taxes, total, paid) VALUES (?,?,?,?,?,?)", ( code, json.dumps([li.__dict__ for li in items]), subtotal, taxes, total, 1 if offset <= 0 and status == "confirmed" else 0, ), ) for ( first, last, phone, party, offset, hour, minute, suffix, label, notes, status, ) in RESERVATIONS: table_row = conn.execute( "SELECT id FROM restaurant_tables WHERE label = ?", (label,) ).fetchone() assert table_row is not None, f"seed fixture references unknown table {label}" table_id = table_row[0] conn.execute( "INSERT INTO restaurant_reservations (code, table_id, first_name, last_name, phone, party_size, date, time, notes, status) VALUES (?,?,?,?,?,?,?,?,?,?)", ( f"RES-{suffix}", table_id, first, last, phone, party, (today + timedelta(days=offset)).isoformat(), time(hour, minute).isoformat(), notes, status, ), ) for case, code, line_item, amount, category, note, outcome, refund, status in DISPUTES: conn.execute( "INSERT INTO hotel_disputes (case_number, booking_code, line_item, amount, category, caller_note, outcome, refund_amount, status) VALUES (?,?,?,?,?,?,?,?,?)", (case, code, line_item, amount, category, note, outcome, refund, status), ) if refund > 0: # mirrors file_dispute(): refund decrements the invoice total conn.execute( "UPDATE hotel_invoices SET total = total - ? WHERE booking_code = ?", (refund, code) ) def build_seed_bytes(today: date) -> bytes: db = HotelDB.empty() try: populate(db, today) return db.serialize() finally: db.close() def write_seed_file(db_path: Path, today: date) -> None: db_path.parent.mkdir(parents=True, exist_ok=True) db_path.write_bytes(build_seed_bytes(today)) print( f"seeded {db_path}: {len(ROOMS)} rooms, {len(TABLES)} tables, " f"{len(BOOKINGS)} bookings, {len(RESERVATIONS)} reservations, {len(DISPUTES)} disputes " f"(today={today.isoformat()})" ) if __name__ == "__main__": logging.basicConfig(level=logging.INFO, format="%(levelname)s %(name)s %(message)s") path = Path(sys.argv[1]) if len(sys.argv) > 1 else DEFAULT_DB_PATH write_seed_file(path, TODAY)