""" Bundle -> monthly-monitoring upsell worker. The Provider Compliance Bundle ($899/yr) includes the customer's FIRST OIG/SAM exclusion screening. Federal exclusion screening is a *monthly* obligation, so after that first screening we invite the customer to continue with standalone OIG/SAM monitoring ($79/month) -- the recurring product. This worker finds paid bundle orders whose first cycle is done and sends a one-time upsell email with a direct link to the recurring checkout. Schedule: daily (systemd timer pw-bundle-upsell). Each order is emailed at most once (tracked by compliance_orders.bundle_upsell_sent_at). Window: send roughly 3-4 weeks after the bundle was paid -- long enough that the first screening/certificate has been delivered, soon enough to convert before the next monthly obligation lapses. """ import logging import os import smtplib import sys from datetime import datetime, timedelta, timezone from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText import psycopg2 LOG = logging.getLogger("workers.bundle_upsell") logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(name)s] %(levelname)s %(message)s") DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://pw:pw@localhost:5432/performancewest") DOMAIN = os.getenv("DOMAIN", "performancewest.net") SMTP_HOST = os.getenv("SMTP_HOST", "co.carrierone.com") SMTP_PORT = int(os.getenv("SMTP_PORT", "587")) SMTP_USER = os.getenv("SMTP_USER", "noreply@performancewest.net") SMTP_PASS = os.getenv("SMTP_PASS", "") SMTP_FROM = os.getenv("SMTP_FROM", "Performance West ") BUNDLE_SLUG = "provider-compliance-bundle" # How long after the bundle is paid before we send the monitoring upsell. Lower # bound gives the first screening + certificate time to be delivered; upper bound # stops us emailing ancient orders when the worker is first switched on. UPSELL_AFTER = timedelta(days=21) UPSELL_BEFORE = timedelta(days=120) # Override knob for testing / disabling. UPSELL_ENABLED = os.getenv("BUNDLE_UPSELL_ENABLED", "1") == "1" def build_email_html(customer_name: str, npi: str, order_url: str) -> str: npi_line = ( f'

For NPI ' f'{npi}.

' if npi else "" ) return f"""

Keep your exclusion screening current

Continue monthly OIG/SAM monitoring

Hi {customer_name},

Your Provider Compliance Bundle included your first OIG LEIE and SAM exclusion screening. But exclusion screening is a monthly obligation under federal guidance — a one-time check doesn't keep you covered the rest of the year.

{npi_line}

Ongoing OIG/SAM Exclusion Monitoring

We re-screen you (and listed staff) every month against the current OIG LEIE and SAM lists, and issue a fresh audit-ready certificate each cycle.

$79 / month — cancel anytime

Set up monthly monitoring →

Questions? Reply to this email or call (888) 411-0383.

Performance West — healthcare compliance.

""" def build_email_text(customer_name: str, npi: str, order_url: str) -> str: npi_line = f"NPI: {npi}\n\n" if npi else "" return ( f"Hi {customer_name},\n\n" "Your Provider Compliance Bundle included your FIRST OIG/SAM exclusion " "screening. Exclusion screening is a monthly obligation under federal " "guidance, so a one-time check doesn't keep you covered all year.\n\n" f"{npi_line}" "Continue with ongoing OIG/SAM Exclusion Monitoring: we re-screen you (and " "listed staff) every month and issue a fresh audit-ready certificate each " "cycle. $79/month, cancel anytime.\n\n" f"Set up monthly monitoring: {order_url}\n\n" "Questions? Reply to this email or call (888) 411-0383.\n\n" "Performance West\n" ) def send_upsell_email(to_email: str, customer_name: str, npi: str) -> bool: order_url = f"https://{DOMAIN}/order/oig-sam-screening" if npi: order_url += f"?npi={npi}" msg = MIMEMultipart("alternative") msg["From"] = SMTP_FROM msg["To"] = to_email msg["Subject"] = "Keep your OIG/SAM exclusion screening current ($79/mo)" msg["Reply-To"] = f"support@{DOMAIN}" msg.attach(MIMEText(build_email_text(customer_name, npi, order_url), "plain")) msg.attach(MIMEText(build_email_html(customer_name, npi, order_url), "html")) try: with smtplib.SMTP(SMTP_HOST, SMTP_PORT, timeout=30) as server: server.ehlo() server.starttls() server.ehlo() server.login(SMTP_USER, SMTP_PASS) server.sendmail(SMTP_USER, [to_email], msg.as_string()) LOG.info("Sent bundle->monitoring upsell to %s", to_email) return True except Exception as e: LOG.error("Failed to send bundle upsell to %s: %s", to_email, e) return False def process_upsells(): if not UPSELL_ENABLED: LOG.info("Bundle upsell disabled (BUNDLE_UPSELL_ENABLED=0)") return now = datetime.now(timezone.utc) window_start = now - UPSELL_BEFORE window_end = now - UPSELL_AFTER sent = 0 conn = psycopg2.connect(DATABASE_URL) try: with conn.cursor() as cur: # Paid bundle orders in the upsell window that haven't been upsold yet, # and where the customer doesn't ALREADY have an OIG/SAM order (don't # pitch monitoring to someone who already bought it). cur.execute( """ SELECT b.order_number, b.customer_email, b.customer_name, b.intake_data FROM compliance_orders b WHERE b.service_slug = %s AND b.payment_status = 'paid' AND b.paid_at BETWEEN %s AND %s AND b.bundle_upsell_sent_at IS NULL AND NOT EXISTS ( SELECT 1 FROM compliance_orders o WHERE o.customer_email = b.customer_email AND o.service_slug = 'oig-sam-screening' ) LIMIT 100 """, (BUNDLE_SLUG, window_start, window_end), ) rows = cur.fetchall() LOG.info("Bundle upsell: %d candidate(s) in window", len(rows)) for order_number, email, name, intake in rows: if not email: continue npi = "" if isinstance(intake, dict): npi = str(intake.get("npi") or "") ok = send_upsell_email(email, name or "there", npi) if ok: cur.execute( "UPDATE compliance_orders SET bundle_upsell_sent_at = %s WHERE order_number = %s", (now, order_number), ) conn.commit() sent += 1 LOG.info("Bundle upsell run complete: %d email(s) sent", sent) except Exception as e: LOG.error("Bundle upsell error: %s", e) conn.rollback() finally: conn.close() if __name__ == "__main__": process_upsells()