demo_db.py

#!/usr/bin/env python3
"""Build a fake chat.db with Apple's schema so the assistant can be tried
and demoed without touching a real message database.

    python3 demo_db.py [out_path]     # default: ./demo_chat.db
    python3 chatoyant.py demo-chat    # builds it, then opens the assistant on it

The inbox is one made-up person's week: a best friend with two years of
texts, a brother, parents, a coworker, a landlord mid-negotiation, a dentist
reminder, two group chats (one planning a trip, one family) and a couple of
photos. Everything is generated from a fixed seed, so every run is the same.
Names and numbers are fictional; 555 numbers do not exist.
"""

import json
import os
import random
import sqlite3
import struct
import sys
import time
import zlib
from datetime import datetime, timedelta

APPLE_EPOCH = 978307200

# handle id -> (address, contact name)
PEOPLE = {
    1: ("+15550100101", "Maya Chen"),            # best friend
    2: ("+15550100102", "Luca Bianchi"),         # friend, organizes trips
    3: ("+15550100103", "Jordan Reyes"),         # friend
    4: ("priya.n@example.com", "Priya Natarajan"),  # coworker
    5: ("+15550100104", "Mom"),
    6: ("+15550100105", "Dad"),
    7: ("+15550100106", "Theo Okafor"),          # brother
    8: ("+15550100107", "Dana Whitfield"),       # landlord
    9: ("+15550100108", "Dr. Alvarez Dental"),   # dentist office
}
ME = 0
LOVED, LIKED, LAUGHED, EMPHASIZED = 2000, 2001, 2003, 2004

# ---------------------------------------------------------------- two years
# of texting with Maya: short exchanges drawn at random across ~700 days.
# My side is always lowercase and short, so a voice-matched draft is visibly
# in character. (who, text) with 0 = me, 1 = Maya.
MAYA_POOL = [
    [(1, "coffee before work?"), (0, "ya 8:15?"), (1, "perfect"), (0, "omw")],
    [(1, "did you watch the finale"), (0, "NO don't spoil it"), (1, "ok but text me the second you do"), (0, "tonight lol")],
    [(0, "how was the interview"), (1, "honestly?? good I think"), (1, "they asked about the thing you helped me practice"), (0, "told you. proud of you")],
    [(1, "I'm outside"), (0, "2 min"), (1, "it's cold hurry")],
    [(1, "can you send me the recipe"), (0, "the lemon one?"), (1, "yes"), (0, "sent")],
    [(0, "thai tonight?"), (1, "always"), (1, "7?"), (0, "7")],
    [(1, "running 10 late sorry"), (0, "all good grabbing a table")],
    [(1, "what are you doing sunday"), (0, "nothing why"), (1, "farmers market then nap"), (0, "in")],
    [(0, "lol look at this"), (1, "STOP"), (1, "that is exactly him"), (0, "right??")],
    [(1, "my landlord is being weird again"), (0, "what now"), (1, "wants to 'inspect' tomorrow at 8am"), (0, "that's not even legal notice lol"), (1, "I KNOW")],
    [(1, "book club is at mine thursday"), (0, "did anyone finish it"), (1, "no"), (0, "lol ok see you thursday")],
    [(0, "made it home"), (1, "good. sleep")],
    [(1, "happy birthday!!!!!"), (0, "thank youuu"), (1, "dinner's on me tonight no arguing"), (0, "not arguing lol")],
    [(1, "do you still have my blue jacket"), (0, "...maybe"), (1, "lol bring it thursday"), (0, "will do")],
    [(0, "flight delayed 2 hrs ugh"), (1, "noooo"), (1, "want me to pick you up anyway"), (0, "you're the best. lands 11:40 now")],
    [(1, "ok big news"), (0, "??"), (1, "I got it"), (0, "THE JOB"), (1, "the job"), (0, "screaming. celebrating this weekend")],
    [(1, "reminder you owe me $32 for the tickets"), (0, "venmoing rn"), (1, "ty")],
    [(1, "how's your mom"), (0, "good, she says hi"), (1, "tell her hi back")],
    [(0, "can't tonight, tomorrow?"), (1, "tomorrow works")],
    [(1, "hike saturday?"), (0, "if we leave before 9"), (1, "8:30 pickup"), (0, "k")],
    [(1, "are you alive"), (0, "lol yes just buried at work"), (1, "ok. water. food. call me later")],
    [(0, "thinking about the pasta place"), (1, "you're always thinking about the pasta place"), (0, "and?"), (1, "7:30")],
    [(1, "found your charger in my car"), (0, "THERE it is"), (1, "hostage until thursday")],
    [(1, "did the package come"), (0, "ya it's on your porch"), (1, "lifesaver")],
    [(0, "trivia tonight? we need a 4th"), (1, "yes but I'm useless at sports"), (0, "we have jordan for that")],
    [(1, "you up"), (0, "ya what's wrong"), (1, "nothing just can't sleep"), (0, "want to call"), (1, "no it's ok. thanks though")],
    [(1, "ok the new place has a dishwasher"), (0, "huge"), (1, "HUGE")],
    [(0, "grabbing groceries need anything"), (1, "oat milk pls"), (0, "k")],
    [(1, "I think I left my keys at yours"), (0, "yep on the counter"), (1, "coming by after work")],
    [(1, "rate my outfit"), (0, "10"), (1, "you didn't even look"), (0, "still 10")],
    [(0, "happy new year!!"), (1, "happy new year!! this is our year")],
    [(1, "movie night friday? I'll cook"), (0, "say less")],
    [(1, "dentist said no cavities"), (0, "flossing pays off"), (1, "I lied to her about flossing")],
    [(0, "at the gym, call you after"), (1, "ok")],
    [(1, "guess who I ran into"), (0, "who"), (1, "your old roommate"), (0, "lol how is he"), (1, "still talking about crypto")],
    [(1, "want to split a costco membership"), (0, "ya"), (1, "ok I'll sign up")],
    [(0, "your plant is still alive btw"), (1, "miracle")],
    [(1, "thanks for last night"), (0, "anytime. seriously")],
    [(1, "concert tickets go on sale at 10"), (0, "on it"), (0, "got 2!!"), (1, "AAAA")],
    [(1, "is it weird if I text him first"), (0, "no. do it"), (1, "ok doing it"), (1, "he replied"), (0, "lol told you")],
    [(0, "omw"), (1, "k")],
    [(1, "good luck today!"), (0, "thank you 🙏")],
    [(0, "lunch?"), (1, "can't, meeting ran over"), (0, "no worries")],
    [(1, "I'm bored entertain me"), (0, "no"), (1, "rude"), (0, "lol ok what's up")],
    [(1, "did you end up calling your dentist"), (0, "...no"), (1, "CALL THEM")],
]

# a smaller pool for my brother, so "ratio with my brother" has three years
# of data too (0 = me, 1 = Theo)
THEO_POOL = [
    [(1, "did you see the game"), (0, "unreal finish"), (1, "I lost my voice")],
    [(1, "send pics of the dog"), (0, "here"), (1, "lol he's so big now")],
    [(1, "mom says call her"), (0, "on it")],
    [(0, "happy birthday dude"), (1, "thanks man. dinner sunday?"), (0, "in")],
    [(1, "can you send me that pdf"), (0, "sent"), (1, "thanks")],
    [(1, "what do I get dad"), (0, "boat stuff. he only wants boat stuff"), (1, "lol true")],
    [(1, "you coming home for thanksgiving"), (0, "ya wed night")],
    [(1, "yo"), (0, "yo"), (1, "fantasy draft is thursday"), (0, "k")],
    [(0, "how's the new apartment"), (1, "loud but good"), (0, "lol welcome to the city")],
    [(1, "I'm outside your building"), (0, "2 min")],
    [(0, "dad fixed the boat yet"), (1, "he says 'almost'"), (0, "so no")],
    [(1, "you're on the family call tonight right"), (0, "ya 7")],
    [(1, "found your old jersey"), (0, "keep it"), (1, "already wearing it")],
    [(1, "can I borrow the car saturday"), (0, "ya just fill it up"), (1, "deal")],
]

MY_REACTIONS = [LOVED, LAUGHED, LIKED, EMPHASIZED]


# ---------------------------------------------------------------- pictures
def _png(path, w, h, pixel):
    rows = []
    for y in range(h):
        row = bytearray([0])
        for x in range(w):
            row += bytes(pixel(x, y))
        rows.append(bytes(row))

    def chunk(tag, data):
        return (struct.pack(">I", len(data)) + tag + data
                + struct.pack(">I", zlib.crc32(tag + data) & 0xffffffff))
    png = (b"\x89PNG\r\n\x1a\n"
           + chunk(b"IHDR", struct.pack(">IIBBBBB", w, h, 8, 2, 0, 0, 0))
           + chunk(b"IDAT", zlib.compress(b"".join(rows), 9))
           + chunk(b"IEND", b""))
    with open(path, "wb") as f:
        f.write(png)


def write_cabin_png(path, w=320, h=200):
    """A lakeside cabin: sky, mountains, pines, a wooden cabin, a lake."""
    def pixel(x, y):
        if y > 165:
            return (40, 90, 150) if (x + y) % 9 else (80, 130, 190)   # lake
        if y > 140:
            return (60, 130, 60)                                       # grass
        if 100 < x < 200 and 85 < y <= 140:
            if 138 < x < 162 and 100 < y:
                return (50, 30, 20)                                    # door
            if (108 < x < 130 or 170 < x < 192) and 95 < y < 115:
                return (240, 230, 160)                                 # windows
            return (140, 90, 45)                                       # logs
        if 85 < x < 215 and 50 < y <= 85 and abs(x - 150) < (y - 50) * 1.9:
            return (95, 50, 30)                                        # roof
        for cx in (35, 60, 255, 285):                                  # pines
            if abs(x - cx) < (y - 70) * 0.35 and 70 < y <= 140:
                return (30, 90, 45)
        for cx, top in ((80, 25), (200, 15), (300, 35)):               # peaks
            if y > top and abs(x - cx) < (y - top) * 1.4 and y <= 140:
                return (235, 240, 245) if y < top + 18 else (120, 125, 140)
        if (x - 275) ** 2 + (y - 30) ** 2 < 150:
            return (255, 235, 120)                                     # sun
        return (150, 190, 235)                                         # sky
    _png(path, w, h, pixel)


def write_boat_png(path, w=320, h=200):
    """Dad's boat: a white motorboat on a lake with a dock."""
    def pixel(x, y):
        if y > 120:
            if 30 < x < 130 and 118 < y < 128:
                return (110, 75, 40)                                   # dock
            if 150 < x < 280 and 120 < y < 150 and abs(x - 215) < 65 - (y - 120) * 1.2:
                return (245, 245, 240)                                 # hull
            if 190 < x < 250 and 100 < y <= 120:
                return (230, 60, 60)                                   # cabin
            return (35, 100, 160) if (x * 3 + y) % 11 else (90, 150, 200)  # water
        for cx, top in ((60, 40), (170, 30), (290, 50)):
            if y > top and abs(x - cx) < (y - top) * 1.6:
                return (100, 120, 100) if y > top + 25 else (225, 230, 235)
        return (170, 205, 240)                                         # sky
    _png(path, w, h, pixel)


# ---------------------------------------------------------------- builder
def build(path):
    for suffix in ("", "-wal", "-shm"):
        if os.path.exists(path + suffix):
            os.remove(path + suffix)
    con = sqlite3.connect(path)
    c = con.cursor()
    c.executescript("""
    CREATE TABLE handle (ROWID INTEGER PRIMARY KEY, id TEXT, country TEXT,
        service TEXT, uncanonicalized_id TEXT);
    CREATE TABLE chat (ROWID INTEGER PRIMARY KEY, guid TEXT, chat_identifier TEXT,
        display_name TEXT, style INTEGER, service_name TEXT,
        last_read_message_timestamp INTEGER DEFAULT 0);
    CREATE TABLE chat_handle_join (chat_id INTEGER, handle_id INTEGER);
    CREATE TABLE message (ROWID INTEGER PRIMARY KEY, guid TEXT, text TEXT,
        attributedBody BLOB, handle_id INTEGER, service TEXT, date INTEGER,
        date_read INTEGER DEFAULT 0, date_delivered INTEGER DEFAULT 0,
        is_from_me INTEGER, is_read INTEGER DEFAULT 0,
        is_delivered INTEGER DEFAULT 1, is_sent INTEGER DEFAULT 1,
        item_type INTEGER DEFAULT 0, associated_message_type INTEGER DEFAULT 0,
        associated_message_guid TEXT, cache_has_attachments INTEGER DEFAULT 0,
        thread_originator_guid TEXT);
    CREATE TABLE chat_message_join (chat_id INTEGER, message_id INTEGER,
        message_date INTEGER);
    CREATE TABLE attachment (ROWID INTEGER PRIMARY KEY, guid TEXT,
        created_date INTEGER, filename TEXT, uti TEXT, mime_type TEXT,
        transfer_name TEXT, total_bytes INTEGER,
        is_sticker INTEGER DEFAULT 0, hide_attachment INTEGER DEFAULT 0);
    CREATE TABLE message_attachment_join (message_id INTEGER, attachment_id INTEGER);
    CREATE INDEX idx_cmj ON chat_message_join (chat_id, message_date);
    CREATE INDEX idx_msg_date ON message (date);
    """)
    rng = random.Random(2026)
    now = time.time()
    today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)

    # 1:1 chats share their handle's ROWID; groups come after
    for hid, (addr, _name) in PEOPLE.items():
        svc = "iMessage" if hid != 9 else "SMS"
        c.execute("INSERT INTO handle VALUES (?,?,?,?,?)",
                  (hid, addr, "us", svc, addr))
        c.execute("INSERT INTO chat (ROWID, guid, chat_identifier, display_name, "
                  "style, service_name) VALUES (?,?,?,?,?,?)",
                  (hid, f"{svc};-;{addr}", addr, None, 45, svc))
        c.execute("INSERT INTO chat_handle_join VALUES (?,?)", (hid, hid))
    TAHOE, FAMILY = 20, 21
    for cid, ident, name, members in ((TAHOE, "chat441122", "tahoe crew", (1, 2, 3)),
                                      (FAMILY, "chat558899", "family", (5, 6, 7))):
        c.execute("INSERT INTO chat (ROWID, guid, chat_identifier, display_name, "
                  "style, service_name) VALUES (?,?,?,?,?,?)",
                  (cid, f"iMessage;+;{ident}", ident, name, 43, "iMessage"))
        for hid in members:
            c.execute("INSERT INTO chat_handle_join VALUES (?,?)", (cid, hid))

    mid = 0
    guids = {}

    def apple(ts):
        return int((ts - APPLE_EPOCH) * 1e9)

    def add(cid, who, text, ts, read=True, assoc=0, target=None):
        """who: 0 = me, else handle id. In a 1:1 chat my messages carry the
        other person's handle_id like the real database does."""
        nonlocal mid
        mid += 1
        from_me = 1 if who == ME else 0
        hid = (cid if cid < 20 else 0) if from_me else who
        d = apple(ts)
        guid = f"D0E4A1B2-{mid:08X}-4C1F-9A2E-{rng.randrange(16**12):012X}"
        guids[mid] = guid
        is_read = 1 if (read and not from_me) else 0
        c.execute(
            "INSERT INTO message (ROWID, guid, text, attributedBody, handle_id, "
            "service, date, date_read, date_delivered, is_from_me, is_read, "
            "is_delivered, is_sent, item_type, associated_message_type, "
            "associated_message_guid, cache_has_attachments) "
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (mid, guid, text, None, hid, "SMS" if cid == 9 else "iMessage", d,
             d + int(rng.uniform(20, 900) * 1e9) if is_read else 0,
             d + int(1e9) if from_me else 0, from_me, is_read, 1, 1, 0, assoc,
             f"p:0/{guids[target]}" if target else None, 0))
        c.execute("INSERT INTO chat_message_join VALUES (?,?,?)", (cid, mid, d))
        return mid

    def react(cid, who, target_mid, kind, ts):
        add(cid, who, None, ts, assoc=kind, target=target_mid)

    att_id = 0

    def attach(message_id, file_path, transfer_name, sticker=0):
        nonlocal att_id
        att_id += 1
        c.execute("INSERT INTO attachment VALUES (?,?,?,?,?,?,?,?,?,0)",
                  (att_id, f"at_{att_id}", apple(now), file_path, "public.png",
                   "image/png", transfer_name, os.path.getsize(file_path), sticker))
        c.execute("INSERT INTO message_attachment_join VALUES (?,?)",
                  (message_id, att_id))
        c.execute("UPDATE message SET cache_has_attachments=1 WHERE ROWID=?",
                  (message_id,))

    def at(days_ago, hhmm):
        h, m = map(int, hhmm.split(":"))
        return (today - timedelta(days=days_ago)).replace(hour=h, minute=m).timestamp()

    def script(cid, lines):
        """lines: (days_ago, 'HH:MM', who, text) or with a 5th item: 'unread',
        'photo:<path>:<name>' or 'react:<kind>' (reacts to the previous
        line). Returns the message ids in order."""
        ids = []
        for line in lines:
            days, hhmm, who, text = line[:4]
            flag = line[4] if len(line) > 4 else ""
            ts = at(days, hhmm)
            if flag.startswith("react:"):
                react(cid, who, ids[-1], int(flag.split(":")[1]), ts)
                continue
            m = add(cid, who, text, ts, read=(flag != "unread"))
            if flag.startswith("photo:"):
                _, fp, name = flag.split(":", 2)
                attach(m, fp, name)
            ids.append(m)
        return ids

    def pool_history(cid, them, pool, days, count, my_gap, their_gap, share):
        """Sprinkle `count` exchanges from `pool` over the last `days` days,
        ending 8 days ago so the scripted week stays on top. `share` is how
        often an exchange is the other person's turn to open."""
        t_days = sorted(rng.uniform(8, days) for _ in range(count))
        for dd in t_days:
            ex = rng.choice(pool)
            if rng.random() > share and ex[0][0] == 1:
                ex = [(1 - w, t) for w, t in ex]        # flip who opens
            ts = at(int(dd), f"{rng.choice([8, 9, 12, 13, 17, 18, 19, 20, 21, 22]):02d}:{rng.randrange(60):02d}")
            prev_from_me = None
            for who, text in ex:
                from_me = who == 0
                if prev_from_me is not None and prev_from_me != from_me:
                    ts += rng.uniform(*(my_gap if from_me else their_gap)) * 60
                else:
                    ts += rng.uniform(0.3, 3) * 60
                m = add(cid, ME if from_me else them, text, ts)
                prev_from_me = from_me
            if rng.random() < 0.12:
                react(cid, them if rng.random() < 0.6 else ME, m,
                      rng.choice(MY_REACTIONS), ts + 40)

    # pictures live next to the database (gitignored with it)
    cabin = os.path.abspath(path + ".cabin.png")
    boat = os.path.abspath(path + ".boat.png")
    write_cabin_png(cabin)
    write_boat_png(boat)

    # a weekend about three weeks out, for the trip
    fri = today + timedelta(days=(4 - today.weekday()) % 7 + 14)
    trip = f"{fri:%b} {fri.day}-{(fri + timedelta(days=2)).day}".lower()
    # this week's Friday, for the dentist
    this_fri = today + timedelta(days=(4 - today.weekday()) % 7 or 7)
    dentist_day = f"{this_fri:%a} {this_fri:%b} {this_fri.day}"

    # ---------------------------------------------------------------- Maya
    pool_history(1, 1, MAYA_POOL, days=730, count=125,
                 my_gap=(1, 9), their_gap=(3, 35), share=0.6)
    script(1, [
        (6, "18:40", 1, "ok thursday is happening. ivy's birthday, that rooftop place"),
        (6, "18:52", 0, "the one with the $19 fries"),
        (6, "18:53", 1, "the very same"),
        (6, "18:53", 0, "lol ok probably. let me check work"),
        (4, "12:10", 1, "also I found your sunglasses in my car"),
        (4, "12:31", 0, "THERE they are"),
        (4, "12:31", 1, "hostage until thursday"),
        (4, "12:32", 0, "lol fair"),
        (2, "21:05", 1, "how was the dinner with your parents"),
        (2, "21:20", 0, "good. dad talked about the boat for 40 min"),
        (2, "21:22", 1, "as is tradition"),
        (2, "21:23", 1, "", "react:2003"),
        (0, "09:12", 1, "are you coming thursday? I need a headcount for the reservation by tonight", "unread"),
        (0, "09:13", 1, "no pressure but ivy keeps asking", "unread"),
    ])

    # ---------------------------------------------------------------- Luca
    script(2, [
        (20, "19:02", 2, "tahoe in september? thinking a cabin"),
        (20, "19:30", 0, "ya let's do it"),
        (20, "19:31", 2, "I'll make a group"),
        (5, "13:15", 2, "still looking at cabins, a lot of them are booked already"),
        (5, "13:40", 0, "anything on the lake?"),
        (5, "13:41", 2, "working on it"),
        (0, "11:38", 2, None, "photo:" + cabin + ":IMG_4021.png"),
        (0, "11:38", 2, "found this one right on the lake. sleeps 6, hot tub, $210 each for the weekend. in?", "unread"),
        (0, "11:39", 2, "need to book today, it's the last one at that price", "unread"),
    ])

    # ---------------------------------------------------------------- Jordan
    script(3, [
        (12, "22:14", 3, "did you see the game"),
        (12, "22:30", 0, "unreal finish"),
        (12, "22:31", 3, "I lost my voice"),
        (12, "22:31", 0, "lol"),
        (7, "10:05", 3, "can you send me that pdf from the tax guy"),
        (7, "10:44", 0, "sent"),
        (7, "10:45", 3, "thanks man"),
        (7, "10:45", 3, "", "react:2001"),
        (1, "17:20", 3, "flight lands at 6:40 friday, can you grab me?"),
        (1, "17:48", 0, "ya, text me when you land"),
        (1, "17:49", 3, "legend"),
    ])

    # ---------------------------------------------------------------- Priya
    script(4, [
        (9, "09:31", 4, "standup moved to 10:30 tomorrow"),
        (9, "09:40", 0, "k thanks"),
        (8, "16:05", 4, "the deploy is green"),
        (8, "16:06", 0, "finally lol"),
        (3, "11:12", 4, "found the bug. it was the timezone thing again"),
        (3, "11:20", 0, "of course it was"),
        (3, "11:21", 4, "PR 412 has the fix"),
        (1, "12:02", 4, "lunch?"),
        (1, "12:05", 0, "ya 12:30"),
        (0, "10:20", 4, "can you look at PR 412 before standup tomorrow? not urgent but it'd be nice to ship this week", "unread"),
    ])

    # ---------------------------------------------------------------- Mom
    script(5, [
        (15, "08:02", 5, "Good morning! Did you eat?"),
        (15, "09:15", 0, "yes mom lol"),
        (15, "09:16", 5, "Just checking. Love you"),
        (10, "19:40", 5, "Call me when you get a chance, nothing urgent"),
        (10, "20:30", 0, "calling now"),
        (3, "12:00", 5, "Dinner was so nice. Dad is still talking about the boat"),
        (3, "12:15", 0, "haha I noticed. thanks for having me"),
        (1, "20:30", 5, "Grandma's birthday is Sunday, please call her. She'd love to hear from you", "unread"),
        (1, "20:31", 5, "Also are you coming to the lake for Labor Day? Dad wants to take everyone out on the boat", "unread"),
    ])

    # ---------------------------------------------------------------- Dad
    script(6, [
        (30, "17:10", 6, "Boat's almost fixed"),
        (30, "17:45", 0, "lol you said that in june"),
        (30, "17:46", 6, "This time I mean it"),
        (2, "16:22", 6, "Carburetor came in. Wish me luck"),
        (2, "16:40", 0, "good luck. send a pic when she floats"),
    ])

    # ---------------------------------------------------------------- Theo
    pool_history(7, 7, THEO_POOL, days=1100, count=45,
                 my_gap=(2, 25), their_gap=(5, 60), share=0.7)
    script(7, [
        (5, "21:15", 7, "fantasy draft is thursday 8pm, don't forget"),
        (5, "21:40", 0, "k"),
        (5, "21:41", 7, "that's not a yes"),
        (5, "21:41", 0, "lol yes"),
        (2, "13:10", 7, "send pics of the dog"),
        (2, "13:30", 0, "later, he's asleep on my foot"),
        (2, "13:31", 7, "", "react:2000"),
    ])

    # ---------------------------------------------------------------- Dana (landlord)
    script(8, [
        (14, "10:00", 8, "Hi! Your lease is up Oct 31. Renewal would be $2,300/mo for 12 months. Let me know if you'd like to renew"),
        (14, "12:15", 0, "hi dana, thanks. that's a big jump from 2,050. any flexibility on that?"),
        (14, "14:30", 8, "I understand. Market's gone up a lot. I could do $2,250"),
        (13, "09:10", 0, "would you do 2,150 for an 18 month lease? I've been a good tenant and I'd like to stay"),
        (13, "11:45", 8, "Let me think about it and get back to you"),
        (11, "16:20", 8, "Ok. $2,150 for 18 months works. I'll send the paperwork"),
        (11, "16:33", 0, "great, thank you. really appreciate it"),
        (0, "08:05", 8, "Morning! Sent the renewal at $2,150 like we discussed. Can you sign by Friday?", "unread"),
    ])

    # ---------------------------------------------------------------- dentist (SMS)
    script(9, [
        (180, "14:00", 9, "Reminder: your cleaning with Dr. Alvarez is tomorrow at 9:00 AM. Reply C to confirm or R to reschedule."),
        (180, "14:20", 0, "C"),
        (1, "16:00", 9, f"Reminder: your cleaning with Dr. Alvarez is {dentist_day} at 2:30 PM. Reply C to confirm or R to reschedule.", "unread"),
    ])

    # ---------------------------------------------------------------- tahoe crew
    tahoe_ids = script(TAHOE, [
        (19, "20:01", 2, f"tahoe {trip}? cabin weekend"),
        (19, "20:04", 1, "yes"),
        (19, "20:09", 3, "in"),
        (19, "20:15", 0, "in"),
        (19, "20:16", 2, "ok looking at cabins"),
        (10, "18:30", 3, None, "photo:" + cabin + ":IMG_3987.png"),
        (10, "18:30", 3, "this one has a hot tub"),
        (10, "18:31", 1, "sold"),
        (10, "18:33", 2, "that one's booked already, found similar"),
        (3, "12:00", 2, "cabin update: found one on the lake, holding it till tomorrow"),
        (3, "12:05", 1, "", "react:2000"),
        (0, "12:10", 3, "who's driving", "unread"),
        (0, "12:12", 2, "I can take 3 if we leave friday at 4", "unread"),
        (0, "12:15", 1, "waiting on one more yes before luca books 👀", "unread"),
    ])

    # ---------------------------------------------------------------- family
    script(FAMILY, [
        (40, "11:00", 5, "Family dinner the 23rd, everyone please come"),
        (40, "11:20", 7, "in"),
        (40, "11:45", 0, "in"),
        (40, "12:00", 6, "Will bring the grill"),
        (1, "15:30", 6, None, "photo:" + boat + ":IMG_2210.png"),
        (1, "15:30", 6, "She floats!!", "unread"),
        (1, "15:33", 7, "lol finally", "unread"),
        (1, "15:35", 5, "Lake for Labor Day then! Everyone bring a sweater", "unread"),
        (1, "15:36", 7, "", "react:2003"),
    ])

    # ---------------------------------------------------------------- on this day
    # something on today's date in each of the last three years
    def on(years, hhmm):
        d = today.replace(year=today.year - years)
        h, m = map(int, hhmm.split(":"))
        return d.replace(hour=h, minute=m).timestamp()

    add(1, 1, "happy friendiversary lol. 1 year since the worst pottery class ever", on(1, "10:02"))
    add(1, ME, "omg one year already. the bowl still leaks", on(1, "10:15"))
    add(1, 1, "as it should", on(1, "10:16"))
    add(7, 7, "guess who got a dog", on(1, "14:40"))
    add(7, ME, "NO. name?", on(1, "14:52"))
    add(7, 7, "biscuit", on(1, "14:53"))
    add(7, ME, "biscuit. I'm coming over", on(1, "14:53"))
    add(5, 5, "Did the package arrive?", on(1, "17:05"))
    add(5, ME, "yes!! the cookies are already gone", on(1, "17:30"))
    add(5, 5, "I'll send more. Save some for Maya this time", on(1, "17:31"))
    add(TAHOE, 2, "who's in for tahoe next month", on(1, "20:10"))
    add(TAHOE, 3, "in", on(1, "20:12"))
    add(TAHOE, ME, "in. same cabin as last year?", on(1, "20:14"))
    add(TAHOE, 2, "if it's free", on(1, "20:15"))
    add(4, ME, "first day at the new job!! wish me luck", on(2, "08:15"))
    add(4, 4, "you'll be great. coffee's on me at 10", on(2, "08:20"))
    add(3, 3, "proud of you man. first day!", on(2, "09:01"))
    add(3, ME, "thanks dude, nervous lol", on(2, "09:30"))
    add(5, 5, "Safe travels! Text me when you land", on(3, "06:45"))
    add(5, ME, "landed, love you", on(3, "13:10"))
    add(1, 1, "so how's the apartment hunt", on(3, "19:30"))
    add(1, ME, "found one. it has a window that faces a wall lol", on(3, "19:41"))
    add(1, 1, "big city living", on(3, "19:41"))

    # a sticker from Maya, so the demo has one to skip in find_photos
    m = add(1, 1, None, at(4, "12:33"))
    attach(m, cabin, "sticker.png", sticker=1)

    con.commit()
    con.close()
    with open(path + ".contacts.json", "w", encoding="utf-8") as f:
        json.dump({addr: name for addr, name in PEOPLE.values()}, f, indent=2)
    return mid


if __name__ == "__main__":
    out = sys.argv[1] if len(sys.argv) > 1 else "demo_chat.db"
    t0 = time.time()
    n = build(out)
    print(f"wrote {os.path.basename(out)} with {n} fake messages in {time.time() - t0:.2f}s")