"""Seed a historical email archive (2023 -> mid-2026) for cross-site search tasks. The WebMail corpus is Enron-2001 noise plus a handful of recent bridge emails, so "dig into old emails" tasks had nothing real to find. This adds ~150 dated, deterministic emails to the 5 users' inboxes whose contents reference REAL entities on other sites, so cross-site search macros are gradeable end-to-end: - banking payees -> monthly utility/rent bills (payee name, PAY- account, amount) - banking txns -> card-charge alerts matching real transactions - SkyLodge Travel -> booking confirmations (booking #, total, date) + confirmation codes - Cascadia Insurance -> policy renewal notices (policy number, premium, renewal date) - e-commerce -> order/shipping confirmations naming real products + prices - calendar-todo -> event invitations/reminders (title, location, start) - generic -> newsletters, security codes, delivery notices (searchable filler) Deterministic (random.Random(42)) + idempotent: message_id PK is and rows are INSERT OR REPLACE'd into the BASE email_emails table. Re-run after any DB rebuild (then push to railway). NOTE: the email site caches the corpus per process — restart the server after seeding. Run: ~/.conda/envs/miniweb/bin/python src/miniweb/scripts/seed_email_archive.py """ import random import sys import pathlib from datetime import datetime, timedelta sys.path.insert(0, str(pathlib.Path(__file__).resolve().parents[2])) # src/ from miniweb.app import create_app # noqa: E402 USERS = { 1: "alex.rivera@meridiansystems.com", 2: "priya.sharma@meridiansystems.com", 3: "marcus.chen@meridiansystems.com", 4: "jessica.okafor@meridiansystems.com", 7: "david.petrov@meridiansystems.com", } # auto-login user (1) gets the lion's share so tasks "just work" RECIPIENT_CYCLE = [1, 1, 1, 2, 1, 3, 1, 4, 1, 7] rng = random.Random(42) emails = [] # (from_, to, subject, date_dt, body) def add(from_, uid, subject, dt, body): emails.append((from_, USERS[uid], subject, dt, body)) def dt_in(year, month, day=None, hour=None): return datetime(year, month, day or rng.randint(2, 27), hour if hour is not None else rng.randint(7, 20), rng.choice([0, 5, 12, 24, 31, 45, 58])) def code(prefix, n, length=6): """Deterministic pseudo-code from a namespace + number (stable across runs).""" r = random.Random(f"{prefix}-{n}") return "".join(r.choice("ABCDEFGHJKLMNPQRSTUVWXYZ23456789") for _ in range(length)) app = create_app() with app.test_request_context(): from miniweb.app import db conn = db._get_conn() # ---- banking: monthly bills from real payees (user 1's payees) --------- payees = db.query("banking", "payees", where={"user_id": 1}, limit=20) bill_payees = [p for p in payees if p.get("category") not in (None, "", "person")][:5] or payees[:5] for p in bill_payees: base_amt = {"Xfinity Internet": 79.99, "Puget Sound Energy": 0, "T-Mobile Wireless": 95.00, "Mendez Properties": 2350.00}.get(p["name"], 0) for year, month in [(y, m) for y in (2024, 2025) for m in range(1, 13, 2)]: amt = base_amt or round(rng.uniform(45, 240), 2) if p["name"] == "Puget Sound Energy": # seasonal energy bill amt = round(88 + 60 * (1 if month in (1, 11) else 0.3) + rng.uniform(-8, 8), 2) dt = dt_in(year, month, day=3, hour=9) add(f"billing@{p['name'].lower().replace(' ', '')}.com", 1, f"Your {p['name']} statement for {dt.strftime('%B %Y')}", dt, f"Dear customer,\n\nYour {p['name']} bill for {dt.strftime('%B %Y')} is ready.\n\n" f"Amount due: ${amt:.2f}\nPayee account: {p['account_number']}\n" f"Due date: {dt.replace(day=21).strftime('%B %d, %Y')}\n\n" f"You can pay this bill from SecureBank Online -> Pay Bills using payee account {p['account_number']}.\n\n" f"Thank you,\n{p['name']} Billing") # ---- banking: charge alerts matching real transactions ----------------- txns = db.execute( "SELECT * FROM banking_transactions WHERE user_id=1 AND type='debit' " "AND amount > 40 ORDER BY id LIMIT 12", ()) for t in txns: try: dt = datetime.strptime(t["date"], "%Y-%m-%d").replace(hour=rng.randint(8, 21), minute=rng.randint(0, 59)) except (ValueError, TypeError): continue add("alerts@securebank-online.com", 1, f"Card charge alert: ${t['amount']:.2f} at {t['description']}", dt, f"SecureBank Online transaction alert\n\n" f"A debit of ${t['amount']:.2f} was posted to your account on {t['date']}.\n" f"Merchant: {t['description']}\nCategory: {t.get('category', '')}\nReference: {t.get('reference', '')}\n\n" f"If you do not recognize this charge, contact us immediately.") # ---- SkyLodge Travel: booking confirmations ---------------------------- bookings = db.query("flights-hotels", "bookings", limit=30) for b in bookings[:14]: uid = b.get("user_id") if b.get("user_id") in USERS else 1 try: dt = datetime.strptime(b["booking_date"], "%Y-%m-%d").replace(hour=11, minute=17) except (ValueError, TypeError): continue conf = code("skylodge", b["id"]) kind = "flight" if b.get("type") == "flight" else "hotel stay" add("reservations@skylodgetravel.com", uid, f"SkyLodge Travel — your {kind} is confirmed (Booking #{b['id']})", dt, f"Thank you for booking with SkyLodge Travel!\n\n" f"Booking number: {b['id']}\nConfirmation code: {conf}\n" f"Type: {b.get('type', '')}\nStatus: {b.get('status', '')}\n" f"Total price: ${b.get('total_price', 0):.2f}\nTravelers: {b.get('travelers', 1)}\n" f"Booked on: {b['booking_date']}\n\n" f"Manage this booking any time on SkyLodge Travel.") # ---- Cascadia Insurance: policy renewal notices ------------------------ policies = db.query("insurance-loans", "policies", limit=20) for pol in policies[:10]: uid = pol.get("user_id") if pol.get("user_id") in USERS else 1 try: renew = datetime.strptime(pol["renewal_date"], "%Y-%m-%d") except (ValueError, TypeError): continue dt = renew - timedelta(days=30) if dt > datetime(2026, 6, 30): # keep everything in the past dt = datetime(2026, rng.randint(1, 5), rng.randint(2, 27), 10, 5) add("policies@cascadia-insurance.com", uid, f"Renewal notice — {pol.get('type', 'insurance').title()} policy {pol['policy_number']}", dt, f"Dear {pol.get('policyholder_name', 'policyholder')},\n\n" f"Your {pol.get('type', '')} policy {pol['policy_number']} renews on {pol['renewal_date']}.\n" f"Monthly premium: ${pol.get('premium_monthly', 0):.2f}\n" f"Annual premium: ${pol.get('premium_annual', 0):.2f}\n" f"Deductible: ${pol.get('deductible', 0):.2f}\n\n" f"No action is needed if your payment method is up to date.\n\n" f"Cascadia Insurance & Lending") # ---- e-commerce: order + shipping confirmations for real products ----- # (products table has no id column and prices live in `pricing` as "$9.69") products = db.query("e-commerce", "products", limit=40) for i, prod in enumerate(products[:12]): uid = RECIPIENT_CYCLE[i % len(RECIPIENT_CYCLE)] order_no = f"ORD-{2023 + i % 3}-{4100 + i * 37}" dt = dt_in(2023 + i % 3, (i * 5) % 12 + 1) try: price = float(str(prod.get("pricing", "") or prod.get("list_price", "") or "0") .replace("$", "").replace(",", "").split()[0]) except (ValueError, IndexError): price = 0.0 add("orders@shophub.com", uid, f"Order {order_no} confirmed — {prod.get('name', 'your item')}", dt, f"Thanks for your order!\n\nOrder number: {order_no}\n" f"Item: {prod.get('name', '')}\nPrice: ${price:.2f}\n" f"Order total: ${round(price * 1.101, 2):.2f} (incl. tax & shipping)\n" f"Placed on: {dt.strftime('%B %d, %Y')}\n\n" f"Track your package with tracking number {code('track', i, 10)}.") add("orders@shophub.com", uid, f"Your order {order_no} has shipped", dt + timedelta(days=2, hours=5), f"Good news — order {order_no} ({prod.get('name', '')}) has shipped.\n" f"Tracking number: {code('track', i, 10)}\n" f"Estimated delivery: {(dt + timedelta(days=6)).strftime('%B %d, %Y')}") # ---- calendar-todo: invitations / reminders ---------------------------- events = db.query("calendar-todo", "events", limit=20) for i, ev in enumerate(events[:8]): uid = ev.get("user_id") if ev.get("user_id") in USERS else RECIPIENT_CYCLE[i % len(RECIPIENT_CYCLE)] start = str(ev.get("start", ""))[:16].replace("T", " ") try: dt = datetime.strptime(start[:10], "%Y-%m-%d") - timedelta(days=7) except (ValueError, TypeError): continue if dt > datetime(2026, 6, 30): dt = datetime(2026, rng.randint(1, 5), rng.randint(2, 27)) dt = dt.replace(hour=rng.randint(8, 17), minute=30) add("invites@calendartodo.app", uid, f"Invitation: {ev.get('title', 'event')}", dt, f"You have been invited to \"{ev.get('title', '')}\".\n\n" f"When: {start}\nWhere: {ev.get('location', 'TBD') or 'TBD'}\n" f"Details: {ev.get('description', '') or '—'}\n\n" f"This event is on your CalendarTodo calendar.") # ---- generic filler: codes, deliveries, newsletters -------------------- for i in range(18): uid = RECIPIENT_CYCLE[i % len(RECIPIENT_CYCLE)] kind = i % 3 dt = dt_in(2023 + i % 4 if i % 4 < 3 else 2026, (i * 7) % 12 + 1) if dt > datetime(2026, 6, 30): dt = dt.replace(year=2025) if kind == 0: svc = ["CloudVault", "StreamBox", "FitTrack", "NewsDaily", "PhotoShare", "DevHub"][i % 6] add(f"security@{svc.lower()}.com", uid, f"Your {svc} verification code", dt, f"Your one-time verification code is {code(svc, i)}.\n\n" f"This code expires in 10 minutes. If you didn't request it, ignore this email.") elif kind == 1: add("no-reply@parcelrun.com", uid, f"Package delivered — {dt.strftime('%B %d')}", dt, f"Your package with tracking number {code('parcel', i, 10)} was delivered on " f"{dt.strftime('%B %d, %Y')} at {dt.strftime('%I:%M %p')}.\n" f"Left at: front door.") else: topic = ["Q{} market roundup", "Weekly digest #{}", "What's new — issue {}"][i % 3].format(i + 4) add("newsletter@techweekly.io", uid, topic, dt, f"{topic}\n\nHighlights this week: cloud spend optimization, " f"open-source security audits, and the state of web agents.\n\n" f"Unsubscribe any time in Settings.") # ---- write to the BASE table (idempotent by message_id) ---------------- table = db.get_table_name("email", "emails") n = 0 for i, (from_, to, subject, dt, body) in enumerate(emails, 1): mid = f"" conn.execute( f"INSERT OR REPLACE INTO [{table}] " f"(from_, [to], cc, subject, date, body, message_id, path) " f"VALUES (?, ?, '', ?, ?, ?, ?, ?)", (from_, to, subject, dt.strftime("%a, %d %b %Y %H:%M:%S -0700"), body, mid, f"archive/{i:04d}")) n += 1 conn.commit() total = conn.execute( f"SELECT COUNT(*) FROM [{table}] WHERE message_id LIKE '