#!/usr/bin/env python3 """Seed realistic estimate data across all funnel stages for testing.""" import json import uuid from datetime import datetime, timedelta import sys, os ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) sys.path.insert(0, ROOT) # Load .env if present env_path = os.path.join(ROOT, ".env") if os.path.exists(env_path): for line in open(env_path): line = line.strip() if line and not line.startswith("#") and "=" in line: k, v = line.split("=", 1) os.environ.setdefault(k, v) from app import create_app from app.models import db, Estimate, Company app = create_app() # Existing IDs GF = "e81f4619-a522-4f54-ae3b-cdfa6abf921c" MR = "307d7335-a929-4103-b66f-e04117c4ed77" HV = "3d735c64-e5e5-4d9a-ba8a-f0fa864d1a3a" REP = "9ad18bb3-ef0f-479f-94c9-fae5db6a5ee4" MGR = "0d5ccc1f-772d-4015-a146-765ef98bd46c" OWN = "004b70be-a3e5-43ab-90bc-a9766ce017dd" NOW = datetime.utcnow() def ts(d): return (NOW - timedelta(days=d)).strftime("%Y-%m-%d %H:%M:%S") # (company, number, value, cost, stage, source, type, desc, created_ago, stage_change_ago, assignee, address, city, state, project_id, reject_reason, competitor, comp_price) ESTS = [ # === GREENFIELD LANDSCAPING === (GF, "GL-2026-001", 8500, 3200, "draft", "referral", "Landscape Design", "Full backyard landscape redesign with retaining wall and irrigation.", 1, 1, REP, "1234 Oak Dr", "Madison", "WI", None, "", "", None), (GF, "GL-2026-002", 22000, 9500, "draft", "website", "Commercial Landscape", "Perimeter landscaping for church campus with shrub beds and trees.", 0, 0, MGR, "5678 Pine St", "Madison", "WI", None, "", "", None), (GF, "GL-2026-003", 15000, 6100, "draft", "email", "Outdoor Patio", "Paver patio with pergola for restaurant expansion.", 2, 2, REP, "890 State St", "Madison", "WI", None, "", "", None), (GF, "GL-2026-004", 12500, 4800, "delivered", "website", "Garden Installation", "Raised bed veg garden with composting and drip irrigation.", 14, 12, OWN, "2345 Elm Ct", "Sun Prairie", "WI", None, "", "", None), (GF, "GL-2026-005", 45000, 18000, "delivered", "referral", "Commercial Landscape", "HOA landscape renovation for 24-unit complex.", 10, 8, MGR, "3456 Riverside Dr", "Madison", "WI", None, "", "", None), (GF, "GL-2026-006", 6800, 2600, "delivered", "angi", "Deck Repair", "Deck repair and staining, composite railing replacement.", 7, 5, REP, "4567 Maple Ave", "Fitchburg", "WI", None, "", "", None), (GF, "GL-2026-007", 35000, 14200, "delivered", "website", "School Garden", "Educational garden with raised beds and rainwater collection.", 5, 4, OWN, "5678 School Rd", "Madison", "WI", None, "", "", None), (GF, "GL-2026-008", 18500, 7200, "accepted", "referral", "Full Landscape", "Complete front/backyard with flagstone walkways and lighting.", 28, 20, MGR, "6789 Birch Ln", "Madison", "WI", None, "", "", None), (GF, "GL-2026-009", 52000, 21000, "accepted", "website", "Commercial Landscape", "Multi-phase renovation for 48-unit apartment complex.", 35, 25, OWN, "7890 Lakeview Ter", "Madison", "WI", None, "", "", None), (GF, "GL-2026-010", 9200, 3500, "accepted", "email", "Retaining Wall", "Segmented retaining wall with drainage, 40ft x 4ft.", 18, 14, REP, "8901 Cedar Rd", "Verona", "WI", None, "", "", None), (GF, "GL-2026-011", 7500, 2900, "rejected", "website", "Irrigation", "Smart irrigation system for 1/2 acre yard.", 21, 17, REP, "9012 Willow Way", "Madison", "WI", None, "Chose competitor - $1,200 lower", "ProScape Landscaping", 6300), (GF, "GL-2026-012", 28000, 11500, "rejected", "referral", "Full Landscape", "Luxury landscape with water feature and outdoor kitchen.", 30, 26, MGR, "1012 Estate Dr", "McFarland", "WI", None, "Project on hold - budget constraints", "", None), (GF, "GL-2026-013", 14200, 5400, "closed", "referral", "Patio & Lighting", "Natural stone patio with LED lighting and fire pit.", 55, 5, OWN, "2013 Stone Path", "Madison", "WI", None, "", "", None), (GF, "GL-2026-014", 11000, 4200, "expired", "website", "Tree Removal", "Remove 3 mature oaks, stump grinding, reseed lawn.", 40, 5, REP, "3014 Oak Hollow", "Stoughton", "WI", None, "", "", None), # === METRO ROOFING === (MR, "MR-2026-101", 38000, 16500, "draft", "website", "Commercial Roof", "TPO membrane replacement on 12,000 sq ft flat roof.", 1, 1, REP, "4015 Commerce Dr", "Madison", "WI", None, "", "", None), (MR, "MR-2026-102", 15500, 6800, "draft", "angi", "Roof Repair", "Shingle replacement on north slope with flashing repair.", 3, 3, OWN, "5016 Walnut St", "Madison", "WI", None, "", "", None), (MR, "MR-2026-103", 62000, 26000, "delivered", "referral", "Church Roof", "Standing seam metal roof. 18,000 sq ft with complex valleys.", 12, 10, MGR, "6017 Faith Ave", "Sun Prairie", "WI", None, "", "", None), (MR, "MR-2026-104", 45000, 19000, "delivered", "website", "Flat Roof", "EPDM to TPO conversion on 10-unit condominium.", 8, 6, REP, "7018 River Rd", "Madison", "WI", None, "", "", None), (MR, "MR-2026-105", 22500, 9500, "accepted", "referral", "Full Roof Replacement", "Architectural shingles on 2,800 sq ft home. New fascia/soffits.", 25, 18, OWN, "8019 Ridge View", "Fitchburg", "WI", None, "", "", None), (MR, "MR-2026-106", 55000, 23000, "accepted", "email", "Commercial Roof", "Bulged roof replacement with energy coating and skylights.", 32, 22, MGR, "9020 Children Way", "Madison", "WI", None, "", "", None), (MR, "MR-2026-107", 125000, 52000, "rejected", "website", "Hospital Roof", "Emergency roof repair on hospital wing.", 22, 19, MGR, "1021 Hospital Dr", "Madison", "WI", None, "Lost - hospital insurance certification", "National Roofing Systems", 118000), (MR, "MR-2026-108", 18500, 7800, "rejected", "angi", "Roof Replacement", "Full shingle replacement, 2,400 sq ft, 2-story.", 15, 12, REP, "2022 Neighborhood Ct", "Middleton", "WI", None, "Went with neighbor recommendation", "", None), (MR, "MR-2026-109", 19800, 8200, "closed", "referral", "Roof Replacement", "Cedar shake to architectural shingle conversion. Done.", 60, 3, OWN, "3023 Cedar Hill", "Madison", "WI", None, "", "", None), (MR, "MR-2026-110", 78000, 32000, "expired", "email", "Multi-Unit Roof", "Flat roof on 16-unit complex. Offer expired.", 45, 4, MGR, "4024 Valley Rd", "Waunakee", "WI", None, "", "", None), # === PREMIER HVAC === (HV, "PH-2026-201", 85000, 38000, "draft", "email", "Commercial HVAC", "20-ton rooftop unit with VRF zoning for office building.", 2, 2, MGR, "5025 Business Park Dr", "Madison", "WI", None, "", "", None), (HV, "PH-2026-202", 42000, 18500, "delivered", "website", "HVAC Upgrade", "High-efficiency furnace and AC replacement. Heat pump option.", 9, 7, REP, "6026 Library Ln", "Madison", "WI", None, "", "", None), (HV, "PH-2026-203", 16500, 6800, "accepted", "referral", "Heat Pump", "Cold-climate heat pump with gas furnace backup.", 20, 15, OWN, "7027 Hearth Rd", "Stoughton", "WI", None, "", "", None), (HV, "PH-2026-204", 95000, 41000, "closed", "referral", "Commercial HVAC", "Full HVAC overhaul for 40-room boutique hotel. Done.", 70, 10, MGR, "8028 Riverside Blvd", "Madison", "WI", None, "", "", None), ] STAGE_FLOW = { "draft": ["draft"], "delivered": ["draft", "delivered"], "accepted": ["draft", "delivered", "accepted"], "rejected": ["draft", "delivered", "rejected"], "expired": ["draft", "delivered", "expired"], "scheduled": ["draft", "delivered", "accepted", "scheduled"], "closed": ["draft", "delivered", "accepted", "scheduled", "closed"], } with app.app_context(): existing = {e.estimate_number for e in Estimate.query.all()} created = 0 for (company, number, value, cost, stage, source, ptype, desc, created_ago, change_ago, assignee, addr, city, state, proj_id, reject, comp, comp_price) in ESTS: if number in existing: continue created_at = NOW - timedelta(days=created_ago) changed_at = NOW - timedelta(days=change_ago) stages = STAGE_FLOW.get(stage, ["draft"]) hist = [] ts_dt = {} for i, s in enumerate(stages): if i == 0: t = created_at elif i == len(stages) - 1: t = changed_at else: t = created_at + (changed_at - created_at) * (i / len(stages)) ts_dt[s] = t hist.append({"stage": s, "timestamp": t.strftime("%Y-%m-%d %H:%M:%S"), "changed_by": assignee}) margin = ((value - cost) / value) * 100 est = Estimate( id=str(uuid.uuid4()), company_id=company, estimate_number=number, customer_name=desc.split(" ")[0], total_value=value, estimated_cost=cost, estimated_margin=round(margin, 1), stage=stage, stage_history=json.dumps(hist), source=source, project_type=ptype, description=desc, address=addr, city=city, state=state, zip_code="53703", deposit=value * 0.10, tax_amount=round(value * 0.05, 2), line_items_json=json.dumps([{"item": desc[:80], "qty": 1, "unit_price": value, "total": value}]), delivered_at=ts_dt.get("delivered"), accepted_at=ts_dt.get("accepted"), rejected_at=ts_dt.get("rejected"), closed_at=ts_dt.get("closed"), expires_at=created_at + timedelta(days=30), rejection_reason=reject, competitor_name=comp, competitor_price=comp_price, assigned_to=assignee, created_at=created_at, updated_at=NOW, ) db.session.add(est) created += 1 db.session.commit() print(f"Seeded {created} estimates ({len(existing)} duplicates skipped)") # Verify stages = db.session.query(Estimate.stage, db.func.count(Estimate.id)).group_by(Estimate.stage).all() order = {"draft": 0, "delivered": 1, "accepted": 2, "scheduled": 3, "closed": 4, "rejected": 5, "expired": 6} print("\nStage distribution:") for s, c in sorted(stages, key=lambda x: order.get(x[0], 7)): print(f" {s:12s} {c}") total = db.session.query(db.func.count(Estimate.id)).scalar() val = db.session.query(db.func.sum(Estimate.total_value)).scalar() or 0 print(f"\nTotal: {total} estimates, ${val:,.0f} pipeline value") for cid, cnt in db.session.query(Estimate.company_id, db.func.count(Estimate.id)).group_by(Estimate.company_id).all(): co = Company.query.get(cid) print(f" {co.name}: {cnt} estimates")