"""Seed report-exercising test data for the green-hills dev tenant. Safe to re-run. Fills the tables the Reports & Analytics module reads but that the base seed_dev_data.py leaves empty (contracts, invoices, invoice_payments, services, crew_members, service_crew_assignments) and backfills plots.reserved_at, which several reports filter on. Data shape is deliberate, not arbitrary — see src/apps/reports/services/data.py: * plots — only status in ('reserved','occupied') AND reserved_at NOT NULL count as "sold", so reserved_at must be set for monthly-sales and the capacity projected-fill column. * invoices — revenue-by-section resolves the section through COALESCE(contracts.section_id, plots.section_id) via invoices.contract_id; invoices with no contract fall under "Unassigned", so a couple are left unlinked on purpose. * due_date — outstanding-balances buckets by age into 0-30 / 31-60 / 61-90 / 90+, and excludes status in ('void','draft','paid'), so the open invoices span all four. * services — service-schedule with no date filter shows only scheduled_date >= today, so every service is future-dated. The ORM's scheduled_date/scheduled_time map to the legacy columns service_date/service_time. Usage: docker compose exec api python scripts/seed_report_data.py """ import asyncio import os import sys from datetime import date, timedelta sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) from sqlalchemy import text from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession from src.core.config import settings TODAY = date.today() def d(days: int) -> str: """ISO date offset from today — negative for the past.""" return (TODAY + timedelta(days=days)).isoformat() # ── Plot types (name, default_price) — widens revenue-by-plot-type beyond "Single" PLOT_TYPES = [ ("Companion", 2400.00), ("Cremation", 600.00), ] # ── Plots to mark sold: # (plot_ref, status, reserved_at offset in days, plot type name, price_override) # Spread over the trailing ~10 months so the capacity report's 365-day sales rate # has something to project from. 8 reserved + 4 occupied, leaving ~27 vacant. # price_override is cleared to None on most plots so the *plot type's* # default_price drives revenue-by-plot-type — the base seed sets a flat 1200 # override on every plot, and Plot.price_override wins over PlotType.default_price # in the report, which would otherwise flatten every type to the same number. # A-5 keeps a bespoke override so that branch stays covered. SOLD_PLOTS = [ ("A-1", "occupied", -300, "Single", None), ("A-2", "occupied", -260, "Companion", None), ("A-3", "reserved", -220, "Single", None), ("A-4", "reserved", -180, "Cremation", None), ("A-5", "reserved", -150, "Companion", 2650.00), ("A-6", "reserved", -120, "Single", None), ("A-7", "reserved", -90, "Cremation", None), ("B-1", "occupied", -240, "Single", None), ("B-2", "occupied", -200, "Companion", None), ("B-3", "reserved", -140, "Single", None), ("B-4", "reserved", -80, "Cremation", None), ("B-5", "reserved", -40, "Companion", None), ] # ── Crew (name, email, phone, address) — all four columns are NOT NULL CREW = [ ("Daniel Okafor", "daniel.okafor@green-hills.com", "+1-416-555-0141", "12 Rosewood Ave, Toronto, ON"), ("Priya Raman", "priya.raman@green-hills.com", "+1-416-555-0172", "88 Cedar Line, Toronto, ON"), ("Marcus Webb", "marcus.webb@green-hills.com", "+1-416-555-0193", "45 Elmgrove Rd, Toronto, ON"), ("Sofia Alvarez", "sofia.alvarez@green-hills.com", "+1-416-555-0128", "7 Birchpoint Cres, Toronto, ON"), ] # ── Contracts: (number, plot_ref, purchaser, email, total, signed_at offset, agent) # `agent` picks which user lands in contracts.created_by, which is what the # monthly-sales "Agent Performance" block groups by. CONTRACTS = [ ("GH-C-2026-001", "A-1", "Eleanor Whitfield", "eleanor.whitfield@example.com", 3450.00, -298, "admin"), ("GH-C-2026-002", "A-2", "Raymond Beaulieu", "raymond.beaulieu@example.com", 4820.00, -258, "manager"), ("GH-C-2026-003", "A-3", "Hana Kowalski", "hana.kowalski@example.com", 2975.00, -218, "admin"), ("GH-C-2026-004", "A-4", "Desmond Achebe", "desmond.achebe@example.com", 1640.00, -178, "manager"), ("GH-C-2026-005", "B-1", "Margaret Lindqvist","margaret.lindqvist@example.com",3980.00, -238, "admin"), ("GH-C-2026-006", "B-2", "Tobias Fernandes", "tobias.fernandes@example.com", 5120.00, -198, "manager"), ("GH-C-2026-007", "B-3", "Yusuf Karim", "yusuf.karim@example.com", 3300.00, -138, "admin"), ("GH-C-2026-008", "B-4", "Claudia Moreau", "claudia.moreau@example.com", 1890.00, -78, "manager"), ] # ── Invoices: (number, contract_number|None, status, total, paid, balance, # due_date offset, created_at offset) # created_at spans 5 distinct calendar months so monthly-sales groups into # several rows and can compute month-over-month growth. # due_date offsets place the open invoices in every aging bucket: # -12 -> 0-30 | -45 -> 31-60 | -75 -> 61-90 | -140/-160 -> 90+ INVOICES = [ # fully paid — excluded from outstanding-balances, counted in revenue ("GH-INV-2026-001", "GH-C-2026-001", "paid", 3450.00, 3450.00, 0.00, -270, -298), ("GH-INV-2026-002", "GH-C-2026-002", "paid", 4820.00, 4820.00, 0.00, -230, -258), ("GH-INV-2026-003", "GH-C-2026-005", "paid", 3980.00, 3980.00, 0.00, -210, -238), # 90+ days overdue ("GH-INV-2026-004", "GH-C-2026-003", "overdue", 2975.00, 0.00, 2975.00, -160, -218), ("GH-INV-2026-005", "GH-C-2026-006", "partial", 5120.00, 2000.00, 3120.00, -140, -198), # 61-90 days overdue ("GH-INV-2026-006", "GH-C-2026-004", "overdue", 1640.00, 0.00, 1640.00, -75, -178), # 31-60 days overdue ("GH-INV-2026-007", "GH-C-2026-007", "partial", 3300.00, 1100.00, 2200.00, -45, -138), # 0-30 days overdue ("GH-INV-2026-008", "GH-C-2026-008", "outstanding", 1890.00, 0.00, 1890.00, -12, -78), ("GH-INV-2026-009", "GH-C-2026-007", "outstanding", 875.00, 200.00, 675.00, -5, -50), # not yet due ("GH-INV-2026-010", "GH-C-2026-008", "outstanding", 1250.00, 0.00, 1250.00, 20, -20), # deliberately contract-less — exercises the "Unassigned" section path ("GH-INV-2026-011", None, "outstanding", 540.00, 0.00, 540.00, -20, -35), ("GH-INV-2026-012", None, "paid", 720.00, 720.00, 0.00, -60, -65), ] # ── Payments backing the partially-paid invoices above. # Keeps paid_amount + balance_due == total_amount consistent. PAYMENTS = [ ("GH-INV-2026-001", 3450.00, "cheque", -270, "RCPT-1001"), ("GH-INV-2026-002", 4820.00, "eft", -230, "RCPT-1002"), ("GH-INV-2026-003", 3980.00, "card", -210, "RCPT-1003"), ("GH-INV-2026-005", 2000.00, "eft", -150, "RCPT-1004"), ("GH-INV-2026-007", 1100.00, "cheque", -50, "RCPT-1005"), ("GH-INV-2026-009", 200.00, "cash", -10, "RCPT-1006"), ("GH-INV-2026-012", 720.00, "card", -62, "RCPT-1007"), ] # ── Services — all future-dated (service-schedule defaults to scheduled_date >= today) # (decedent, service_type, day offset, time, status, plot_ref, officiant, attendees) SERVICES = [ ("Eleanor Whitfield", "interment", 2, "10:00", "confirmed", "A-1", "Rev. Alan Pike", 45), ("Raymond Beaulieu", "memorial_service", 5, "13:30", "confirmed", "A-2", "Rev. Marta Deane", 120), ("Margaret Lindqvist","interment", 9, "09:15", "awaiting_family_confirm", "B-1", "Fr. Peter Nowak", 30), ("Tobias Fernandes", "foundation_pour", 16, "08:00", "draft", "B-2", None, 0), ("Hana Kowalski", "interment", 24, "11:45", "awaiting_family_confirm", "A-3", "Rev. Alan Pike", 60), ("Yusuf Karim", "closure", 45, "14:00", "draft", "B-3", None, 8), ] async def main(): db_url = settings.DATABASE_URL if not db_url.startswith("postgresql+psycopg://"): db_url = "postgresql+psycopg://" + db_url.split("://", 1)[-1] engine = create_async_engine(db_url, echo=False) SessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False) async with SessionLocal() as db: row = (await db.execute( text("SELECT id FROM accounts WHERE subdomain = 'green-hills'") )).fetchone() if not row: print("ERROR: green-hills tenant not found. Run seed_dev_data.py first.") return tid = str(row[0]) print(f"Tenant id: {tid}") stats: dict[str, list[int]] = {} def tally(table: str, created: bool): s = stats.setdefault(table, [0, 0]) s[0 if created else 1] += 1 # ── Users for contracts.created_by (agent attribution) ──────────────── agents = {} for key, email in (("admin", "admin@green-hills.com"), ("manager", "manager@green-hills.com")): r = (await db.execute( text("SELECT id FROM users WHERE email = :e AND tenant_id = :tid AND deleted_at IS NULL"), {"e": email, "tid": tid}, )).fetchone() agents[key] = str(r[0]) if r else None print(f"Agents: admin={agents['admin']} manager={agents['manager']}") # ── Sections ───────────────────────────────────────────────────────── sections = { name: str(sid) for sid, name in (await db.execute( text("SELECT id, name FROM sections WHERE tenant_id = :tid"), {"tid": tid} )).all() } print(f"Sections: {sections}") # ── 1. Plot types ──────────────────────────────────────────────────── for name, price in PLOT_TYPES: exists = (await db.execute( text("SELECT id FROM plot_types WHERE tenant_id = :tid AND name = :n"), {"tid": tid, "n": name}, )).fetchone() if exists: tally("plot_types", False) continue await db.execute( text(""" INSERT INTO plot_types (tenant_id, name, default_price, default_width_m, default_length_m, default_depth_m, default_gap_m, created_at, updated_at) VALUES (:tid, :n, :p, 1.2, 2.4, 1.8, 0.3, NOW(), NOW()) """), {"tid": tid, "n": name, "p": price}, ) tally("plot_types", True) plot_types = { name: str(pid) for pid, name in (await db.execute( text("SELECT id, name FROM plot_types WHERE tenant_id = :tid"), {"tid": tid} )).all() } # ── 2. Plots — backfill status + reserved_at (idempotent by reserved_at) ─ plot_ids: dict[str, str] = {} for ref, status, offset, pt_name, price_ovr in SOLD_PLOTS: r = (await db.execute( text("""SELECT id, reserved_at FROM plots WHERE tenant_id = :tid AND plot_ref = :ref"""), {"tid": tid, "ref": ref}, )).fetchone() if not r: print(f"[warn] plot {ref} not found — skipping") continue plot_ids[ref] = str(r[0]) if r[1] is not None: tally("plots", False) continue await db.execute( text(""" UPDATE plots SET status = :st, reserved_at = CAST(:ra AS timestamptz), plot_type_id = COALESCE(:ptid, plot_type_id), price_override = :povr, updated_at = NOW() WHERE id = :id """), { "st": status, "ra": d(offset), "ptid": plot_types.get(pt_name), "povr": price_ovr, "id": plot_ids[ref], }, ) tally("plots", True) # ── 3. Crew members ────────────────────────────────────────────────── for name, email, phone, address in CREW: exists = (await db.execute( text("SELECT id FROM crew_members WHERE tenant_id = :tid AND email = :e"), {"tid": tid, "e": email}, )).fetchone() if exists: tally("crew_members", False) continue await db.execute( text(""" INSERT INTO crew_members (tenant_id, name, email, phone, address, is_available, created_at, updated_at) VALUES (:tid, :n, :e, :ph, :ad, TRUE, NOW(), NOW()) """), {"tid": tid, "n": name, "e": email, "ph": phone, "ad": address}, ) tally("crew_members", True) crew_ids = [ str(r[0]) for r in (await db.execute( text("""SELECT id FROM crew_members WHERE tenant_id = :tid AND deleted_at IS NULL ORDER BY name"""), {"tid": tid}, )).all() ] # ── 4. Contracts ───────────────────────────────────────────────────── contract_ids: dict[str, str] = {} for num, plot_ref, pname, pemail, total, signed_off, agent in CONTRACTS: r = (await db.execute( text("SELECT id FROM contracts WHERE tenant_id = :tid AND contract_number = :n"), {"tid": tid, "n": num}, )).fetchone() if r: contract_ids[num] = str(r[0]) tally("contracts", False) continue section_id = sections.get("SECTION A" if plot_ref.startswith("A") else "SECTION B") new_id = (await db.execute( text(""" INSERT INTO contracts (tenant_id, contract_number, status, contract_type, total_amount, section_id, plot_id, created_by, purchaser_name, purchaser_email, signed_at, currency, payment_plan_type, created_at, updated_at) VALUES (:tid, :n, 'signed', 'pre_need', :total, :sid, :pid, :cby, :pname, :pemail, CAST(:signed AS timestamptz), 'CAD', 'full', CAST(:signed AS timestamptz), NOW()) RETURNING id """), { "tid": tid, "n": num, "total": total, "sid": section_id, "pid": plot_ids.get(plot_ref), "cby": agents.get(agent), "pname": pname, "pemail": pemail, "signed": d(signed_off), }, )).fetchone() contract_ids[num] = str(new_id[0]) tally("contracts", True) # ── 5. Invoices ────────────────────────────────────────────────────── invoice_ids: dict[str, str] = {} purchaser_by_contract = {c[0]: (c[2], c[3]) for c in CONTRACTS} for num, cnum, status, total, paid, balance, due_off, created_off in INVOICES: r = (await db.execute( text("SELECT id FROM invoices WHERE tenant_id = :tid AND invoice_number = :n"), {"tid": tid, "n": num}, )).fetchone() if r: invoice_ids[num] = str(r[0]) tally("invoices", False) continue pname, pemail = purchaser_by_contract.get(cnum, ("Walk-in Purchaser", "walkin@example.com")) new_id = (await db.execute( text(""" INSERT INTO invoices (tenant_id, contract_id, invoice_number, status, total_amount, paid_amount, balance_due, due_date, purchaser_name, purchaser_email, created_at, updated_at) VALUES (:tid, :cid, :n, :st, :total, :paid, :bal, CAST(:due AS date), :pname, :pemail, CAST(:created AS timestamptz), NOW()) RETURNING id """), { "tid": tid, "cid": contract_ids.get(cnum) if cnum else None, "n": num, "st": status, "total": total, "paid": paid, "bal": balance, "due": d(due_off), "pname": pname, "pemail": pemail, "created": d(created_off), }, )).fetchone() invoice_ids[num] = str(new_id[0]) tally("invoices", True) # ── 6. Invoice payments ────────────────────────────────────────────── for inv_num, amount, method, received_off, receipt in PAYMENTS: inv_id = invoice_ids.get(inv_num) if not inv_id: continue exists = (await db.execute( text("""SELECT id FROM invoice_payments WHERE tenant_id = :tid AND receipt_number = :rc"""), {"tid": tid, "rc": receipt}, )).fetchone() if exists: tally("invoice_payments", False) continue await db.execute( text(""" INSERT INTO invoice_payments (tenant_id, invoice_id, amount, method, received_on, receipt_number, recorded_by, created_at, updated_at) VALUES (:tid, :iid, :amt, :m, CAST(:ro AS date), :rc, :by, NOW(), NOW()) """), { "tid": tid, "iid": inv_id, "amt": amount, "m": method, "ro": d(received_off), "rc": receipt, "by": agents.get("admin"), }, ) tally("invoice_payments", True) # ── 7. Services (future-dated) ─────────────────────────────────────── service_ids: list[str] = [] for decedent, stype, day_off, stime, status, plot_ref, officiant, attendees in SERVICES: r = (await db.execute( text("""SELECT id FROM services WHERE tenant_id = :tid AND decedent_name = :dn AND service_type = :st AND deleted_at IS NULL"""), {"tid": tid, "dn": decedent, "st": stype}, )).fetchone() if r: service_ids.append(str(r[0])) tally("services", False) continue section_name = "SECTION A" if plot_ref.startswith("A") else "SECTION B" new_id = (await db.execute( text(""" INSERT INTO services (tenant_id, plot_id, section_id, service_type, service_date, service_time, status, decedent_name, officiant, expected_attendees, duration_minutes, section, plot_location, created_by, created_at, updated_at) VALUES (:tid, :pid, :sid, :stype, CAST(:sdate AS date), CAST(:stime AS time), :status, :dn, :off, :att, 60, :secname, :ploc, :cby, NOW(), NOW()) RETURNING id """), { "tid": tid, "pid": plot_ids.get(plot_ref), "sid": sections.get(section_name), "stype": stype, "sdate": d(day_off), "stime": stime, "status": status, "dn": decedent, "off": officiant, "att": attendees, "secname": section_name, "ploc": plot_ref, "cby": agents.get("admin"), }, )).fetchone() service_ids.append(str(new_id[0])) tally("services", True) # ── 8. Crew assignments — 2 crew per service ────────────────────────── if crew_ids: for idx, svc_id in enumerate(service_ids): for slot in range(2): crew_id = crew_ids[(idx * 2 + slot) % len(crew_ids)] exists = (await db.execute( text("""SELECT id FROM service_crew_assignments WHERE tenant_id = :tid AND service_id = :svc AND crew_member_id = :crew"""), {"tid": tid, "svc": svc_id, "crew": crew_id}, )).fetchone() if exists: tally("service_crew_assignments", False) continue await db.execute( text(""" INSERT INTO service_crew_assignments (tenant_id, service_id, crew_member_id, created_at, updated_at) VALUES (:tid, :svc, :crew, NOW(), NOW()) """), {"tid": tid, "svc": svc_id, "crew": crew_id}, ) tally("service_crew_assignments", True) await db.commit() # ── Summary ────────────────────────────────────────────────────────── print() print("=" * 52) print(" SEED SUMMARY — Green Hills report data") print("=" * 52) print(f" {'Table':<28} {'created':>8} {'skipped':>8}") print(f" {'-'*28} {'-'*8} {'-'*8}") for table in sorted(stats): created, skipped = stats[table] print(f" {table:<28} {created:>8} {skipped:>8}") print("=" * 52) print(" Reports: http://localhost:3000/portal/reports") print() await engine.dispose() if __name__ == "__main__": asyncio.run(main())