#!/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")