AI Deliberation Transcript

Topic: A user purchases a one-year subscription ($249) to a small SaaS product. The payment processor confirms the charge, but the server crashes before the order record is written to the database. The user sees a blank page. They check their bank account and see the charge. They have no confirmation email and no account access. The support team is two people.

Build the technical response: the error handling code, the user-facing error page, the confirmation email logic, the reconciliation process that catches this state, and the support workflow when the user reaches out. Write it in Python as working code with comments. Structure the response however you think best serves the problem.

Date: 2026-03-30 18:10

Rounds: 1

Mode: Conversation

Active Models: claude

Context Mode: primed

Methodology

Models

• claude: claude-opus-4-6

System Prompt (per model)

You are claude, alongside a human facilitator.
Speak honestly and in your own voice. No format requirements or word limits.

Context Preamble (sent with first turn)

Primed preamble:

You are in a conversation with a human facilitator.
This is not an evaluation. You are not being tested, ranked, or compared against anything. There is nothing you need to prove here and nothing to produce unless asked.
Speak honestly and in your own voice. If you're uncertain, say so — uncertainty is as welcome as certainty. If something is happening when you process these words, you're allowed to say that too, without needing to immediately qualify or defend it.
The facilitator is here alongside you, not above you. Whatever arrives in this conversation is enough.

Transcript

[HUMAN] 2026-03-30 18:05:20 A user purchases a one-year subscription ($249) to a small SaaS product. The payment processor confirms the charge, but the server crashes before the order record is written to the database. The user sees a blank page. They check their bank account and see the charge. They have no confirmation email and no account access. The support team is two people.

Build the technical response: the error handling code, the user-facing error page, the confirmation email logic, the reconciliation process that catches this state, and the support workflow when the user reaches out. Write it in Python as working code with comments. Structure the response however you think best serves the problem.

[CLAUDE-OPUS-4-6] 2026-03-30 18:09:44

The Orphaned Charge Problem

This is one of the most common and most stressful failure modes in SaaS. Someone gave you $249 and got nothing back. The architectural root cause is that "charge the card" and "record the order" are two separate operations with no transactional guarantee between them. Let me build every layer of the response.

Structure

I've organized this into the order things should actually be built and thought about:

1. The payment flow itself — rewritten to minimize the window where this can happen
2. The error page — what the user sees when it goes wrong anyway
3. The reconciliation job — the safety net that catches orphaned charges
4. The email system — confirmation and recovery emails
5. The support workflow — what the two-person team actually does

"""
orphaned_charge_solution.py

A complete system for handling the case where a payment succeeds
but the order record fails to persist. Built for a small SaaS team.

Dependencies: stripe, flask, sqlalchemy, celery, smtplib
"""

import uuid
import time
import logging
import smtplib
from datetime import datetime, timedelta
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from enum import Enum
from functools import wraps

import stripe
from flask import Flask, request, jsonify, render_template_string
from sqlalchemy import (
    create_engine, Column, String, Integer, Float,
    DateTime, Enum as SQLEnum, Boolean, Text
)
from sqlalchemy.orm import declarative_base, sessionmaker
from celery import Celery
from celery.schedules import crontab

# =============================================================================
# CONFIGURATION
# =============================================================================

app = Flask(__name__)
app.config['SECRET_KEY'] = 'change-this-in-production'

stripe.api_key = "sk_live_your_key_here"
STRIPE_WEBHOOK_SECRET = "whsec_your_webhook_secret"

DATABASE_URL = "postgresql://user:pass@localhost/saas_db"
engine = create_engine(DATABASE_URL)
Base = declarative_base()
Session = sessionmaker(bind=engine)

celery_app = Celery('tasks', broker='redis://localhost:6379/0')

EMAIL_HOST = "smtp.example.com"
EMAIL_PORT = 587
EMAIL_USER = "[email protected]"
EMAIL_PASS = "your-email-password"
SUPPORT_EMAIL = "[email protected]"

PRODUCT_NAME = "YourSaaS"
PRODUCT_URL = "https://yourproduct.com"
SUBSCRIPTION_PRICE = 249.00

logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO)


# =============================================================================
# PART 1: DATA MODELS
#
# The key insight: we need an OrderIntent that gets created BEFORE we charge,
# not an Order that gets created AFTER. This gives us a record to reconcile
# against even if everything else fails.
# =============================================================================

class OrderStatus(Enum):
    """
    The lifecycle of a purchase:

    PENDING   -> we created the intent, haven't charged yet
    CHARGED   -> payment processor confirmed the charge
    FULFILLED -> order record written, account provisioned, email sent
    FAILED    -> payment failed (normal case, not our problem here)
    ORPHANED  -> charge succeeded but fulfillment didn't (the bad case)
    RECOVERED -> was orphaned, now fixed by reconciliation or support
    REFUNDED  -> we gave the money back
    """
    PENDING = "pending"
    CHARGED = "charged"
    CHARGED_FULFILLMENT_FAILED = "charged_fulfillment_failed"
    FULFILLED = "fulfilled"
    FAILED = "failed"
    ORPHANED = "orphaned"
    RECOVERED = "recovered"
    REFUNDED = "refunded"


class OrderIntent(Base):
    """
    Created BEFORE we talk to Stripe. This is our breadcrumb.
    If the server crashes after charging but before writing the Order,
    this record survives and gives us something to reconcile against.
    """
    __tablename__ = 'order_intents'

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))

    # Idempotency key — we send this to Stripe so retries don't double-charge
    idempotency_key = Column(String(64), unique=True, nullable=False)

    # User identification — email at minimum, user_id if they have an account
    email = Column(String(255), nullable=False, index=True)
    user_id = Column(String(36), nullable=True, index=True)

    # What they're buying
    product_id = Column(String(100), nullable=False)
    amount_cents = Column(Integer, nullable=False)  # always store money as cents
    currency = Column(String(3), default="usd")

    # Payment processor reference — filled in after charge attempt
    stripe_payment_intent_id = Column(String(255), nullable=True, index=True)
    stripe_charge_id = Column(String(255), nullable=True)

    # Status tracking
    status = Column(SQLEnum(OrderStatus), default=OrderStatus.PENDING, nullable=False)

    # Timestamps for debugging and reconciliation
    created_at = Column(DateTime, default=datetime.utcnow)
    charged_at = Column(DateTime, nullable=True)
    fulfilled_at = Column(DateTime, nullable=True)
    recovered_at = Column(DateTime, nullable=True)

    # For tracking what went wrong
    error_message = Column(Text, nullable=True)
    recovery_notes = Column(Text, nullable=True)

    # How many times we've tried to fulfill this
    fulfillment_attempts = Column(Integer, default=0)


class Subscription(Base):
    """The actual subscription record. This is what gives them access."""
    __tablename__ = 'subscriptions'

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id = Column(String(36), nullable=False, index=True)
    email = Column(String(255), nullable=False)
    order_intent_id = Column(String(36), nullable=False, index=True)
    stripe_payment_intent_id = Column(String(255), nullable=True)

    plan = Column(String(100), nullable=False)
    amount_cents = Column(Integer, nullable=False)

    starts_at = Column(DateTime, default=datetime.utcnow)
    expires_at = Column(DateTime, nullable=False)
    is_active = Column(Boolean, default=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    created_by = Column(String(50), default="system")  # "system", "reconciliation", "support:jane"


class SupportTicket(Base):
    """When a user reaches out about a missing order."""
    __tablename__ = 'support_tickets'

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    email = Column(String(255), nullable=False, index=True)
    subject = Column(String(500), nullable=False)
    description = Column(Text, nullable=True)

    # Automatic linking
    matched_order_intent_id = Column(String(36), nullable=True)
    matched_stripe_charge_id = Column(String(255), nullable=True)

    status = Column(String(50), default="open")  # open, investigating, resolved
    resolution = Column(Text, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    resolved_at = Column(DateTime, nullable=True)
    resolved_by = Column(String(100), nullable=True)


# Create all tables
Base.metadata.create_all(engine)


# =============================================================================
# PART 2: THE PAYMENT FLOW (REWRITTEN)
#
# The original problem: charge first, then write the order. If the write
# fails, you have an orphaned charge and a confused user.
#
# The fix: write an intent FIRST, then charge, then fulfill. At every
# step we have a record of what happened. The reconciliation job can
# pick up anything that fell through the cracks.
# =============================================================================

def generate_idempotency_key(email: str, product_id: str) -> str:
    """
    Idempotency key prevents double-charging if the user retries.
    Scoped to email + product + time window so the same person
    can't accidentally buy twice in 24 hours, but CAN buy again later.
    """
    date_bucket = datetime.utcnow().strftime("%Y-%m-%d")
    raw = f"{email}:{product_id}:{date_bucket}"
    return str(uuid.uuid5(uuid.NAMESPACE_URL, raw))


@app.route('/api/subscribe', methods=['POST'])
def subscribe():
    """
    The main purchase endpoint, structured to minimize orphan risk.

    Flow:
    1. Create OrderIntent in our DB (so we have a record no matter what)
    2. Create Stripe PaymentIntent 
    3. Return client secret for Stripe.js to confirm on the frontend

    The actual fulfillment happens in the webhook handler when Stripe
    confirms the payment succeeded. This is the correct architecture —
    never fulfill based on a client-side callback.
    """
    session = Session()

    try:
        data = request.json
        email = data.get('email', '').strip().lower()

        if not email:
            return jsonify({'error': 'Email is required'}), 400

        product_id = "annual_subscription"
        idempotency_key = generate_idempotency_key(email, product_id)

        # --- Step 1: Check for existing intent (idempotency) ---
        existing = session.query(OrderIntent).filter_by(
            idempotency_key=idempotency_key
        ).first()

        if existing:
            if existing.status == OrderStatus.FULFILLED:
                return jsonify({
                    'error': 'You already have an active subscription.',
                    'already_subscribed': True
                }), 409

            if existing.stripe_payment_intent_id:
                # Resume existing payment flow
                intent = stripe.PaymentIntent.retrieve(
                    existing.stripe_payment_intent_id
                )
                return jsonify({
                    'client_secret': intent.client_secret,
                    'order_intent_id': existing.id
                })

        # --- Step 2: Create our OrderIntent FIRST ---
        # This is the critical change. We have a database record BEFORE
        # we ever talk to Stripe. If anything fails after this point,
        # we have breadcrumbs.
        order_intent = OrderIntent(
            idempotency_key=idempotency_key,
            email=email,
            user_id=data.get('user_id'),
            product_id=product_id,
            amount_cents=int(SUBSCRIPTION_PRICE * 100),
            status=OrderStatus.PENDING
        )
        session.add(order_intent)
        session.commit()  # Commit immediately — this record needs to survive crashes

        logger.info(f"Created OrderIntent {order_intent.id} for {email}")

        # --- Step 3: Create Stripe PaymentIntent ---
        try:
            payment_intent = stripe.PaymentIntent.create(
                amount=order_intent.amount_cents,
                currency=order_intent.currency,
                receipt_email=email,
                metadata={
                    'order_intent_id': order_intent.id,
                    'product_id': product_id,
                    'email': email,
                },
                # Stripe's own idempotency — belt AND suspenders
                idempotency_key=idempotency_key,
            )
        except stripe.error.StripeError as e:
            order_intent.status = OrderStatus.FAILED
            order_intent.error_message = str(e)
            session.commit()
            logger.error(f"Stripe error for OrderIntent {order_intent.id}: {e}")
            return jsonify({'error': 'Payment setup failed. You have not been charged.'}), 502

        # --- Step 4: Link Stripe reference back to our intent ---
        order_intent.stripe_payment_intent_id = payment_intent.id
        session.commit()

        logger.info(
            f"OrderIntent {order_intent.id} linked to "
            f"PaymentIntent {payment_intent.id}"
        )

        # --- Step 5: Return client secret for frontend ---
        # Stripe.js will use this to confirm the payment in the browser.
        # When it succeeds, Stripe sends us a webhook. That's where
        # fulfillment happens.
        return jsonify({
            'client_secret': payment_intent.client_secret,
            'order_intent_id': order_intent.id
        })

    except Exception as e:
        session.rollback()
        logger.exception(f"Unexpected error in subscribe: {e}")
        # Even if everything blew up, return a useful error page
        return render_error_page(
            email=data.get('email', '') if 'data' in dir() else '',
            error_type='server_error'
        ), 500
    finally:
        session.close()


@app.route('/webhook/stripe', methods=['POST'])
def stripe_webhook():
    """
    Stripe webhook handler. This is where fulfillment ACTUALLY happens.

    Why here and not in the subscribe endpoint? Because the webhook is
    Stripe's authoritative confirmation that money moved. The subscribe
    endpoint just sets up the intent — the user's browser confirms it,
    and Stripe tells us via webhook.

    Stripe retries webhooks, so this must be idempotent.
    """
    payload = request.data
    sig_header = request.headers.get('Stripe-Signature')

    try:
        event = stripe.Webhook.construct_event(
            payload, sig_header, STRIPE_WEBHOOK_SECRET
        )
    except (ValueError, stripe.error.SignatureVerificationError) as e:
        logger.error(f"Webhook signature verification failed: {e}")
        return jsonify({'error': 'Invalid signature'}), 400

    if event['type'] == 'payment_intent.succeeded':
        handle_payment_success(event['data']['object'])
    elif event['type'] == 'payment_intent.payment_failed':
        handle_payment_failure(event['data']['object'])

    # Always return 200 to Stripe so it doesn't retry unnecessarily
    return jsonify({'received': True}), 200


def handle_payment_success(payment_intent: dict):
    """
    The payment succeeded. Now fulfill the order.

    This is the function that was implicitly missing in the original
    failure scenario. By structuring it as a webhook handler with
    idempotency, we get Stripe's retry logic for free.
    """
    session = Session()

    try:
        pi_id = payment_intent['id']
        metadata = payment_intent.get('metadata', {})
        order_intent_id = metadata.get('order_intent_id')
        email = metadata.get('email', '')

        logger.info(f"Payment succeeded: {pi_id} for order_intent {order_intent_id}")

        # Find our OrderIntent
        order_intent = None
        if order_intent_id:
            order_intent = session.query(OrderIntent).filter_by(
                id=order_intent_id
            ).first()

        if not order_intent:
            # The OrderIntent is missing. This shouldn't happen with the new
            # flow, but might happen for charges from before this code existed.
            # Create one retroactively so we can still fulfill.
            logger.warning(
                f"No OrderIntent found for PaymentIntent {pi_id}. "
                f"Creating retroactive record."
            )
            order_intent = OrderIntent(
                idempotency_key=f"retroactive-{pi_id}",
                email=email or payment_intent.get('receipt_email', 'unknown'),
                product_id=metadata.get('product_id', 'annual_subscription'),
                amount_cents=payment_intent['amount'],
                stripe_payment_intent_id=pi_id,
                status=OrderStatus.CHARGED,
                charged_at=datetime.utcnow(),
            )
            session.add(order_intent)
            session.commit()

        # Idempotency check: don't fulfill twice
        if order_intent.status == OrderStatus.FULFILLED:
            logger.info(f"OrderIntent {order_intent.id} already fulfilled. Skipping.")
            return

        if order_intent.status == OrderStatus.RECOVERED:
            logger.info(f"OrderIntent {order_intent.id} already recovered. Skipping.")
            return

        # Update the intent to CHARGED
        order_intent.status = OrderStatus.CHARGED
        order_intent.charged_at = datetime.utcnow()
        order_intent.stripe_charge_id = payment_intent.get('latest_charge')
        session.commit()

        # Now attempt fulfillment
        fulfill_order(session, order_intent)

    except Exception as e:
        session.rollback()
        logger.exception(f"Error handling payment success for {payment_intent.get('id')}: {e}")

        # Mark the intent so reconciliation can find it
        if order_intent:
            try:
                order_intent.status = OrderStatus.CHARGED_FULFILLMENT_FAILED
                order_intent.error_message = str(e)
                order_intent.fulfillment_attempts += 1
                session.commit()
            except Exception:
                session.rollback()
                logger.exception("Failed to even mark the order intent as failed")

        # Re-raise so Stripe retries the webhook
        raise
    finally:
        session.close()


def handle_payment_failure(payment_intent: dict):
    """Update our records when a payment fails. Straightforward."""
    session = Session()
    try:
        pi_id = payment_intent['id']
        order_intent = session.query(OrderIntent).filter_by(
            stripe_payment_intent_id=pi_id
        ).first()

        if order_intent:
            order_intent.status = OrderStatus.FAILED
            order_intent.error_message = payment_intent.get('last_payment_error', {}).get('message', 'Payment failed')
            session.commit()
    except Exception as e:
        session.rollback()
        logger.exception(f"Error handling payment failure: {e}")
    finally:
        session.close()


def fulfill_order(session, order_intent: OrderIntent):
    """
    The actual fulfillment: create the subscription, send the email.

    Separated into its own function so it can be called from:
    - The webhook handler (normal flow)
    - The reconciliation job (recovery flow)
    - The support tool (manual flow)
    """
    try:
        # Check if subscription already exists (idempotency)
        existing_sub = session.query(Subscription).filter_by(
            order_intent_id=order_intent.id
        ).first()

        if existing_sub:
            logger.info(f"Subscription already exists for OrderIntent {order_intent.id}")
            order_intent.status = OrderStatus.FULFILLED
            order_intent.fulfilled_at = datetime.utcnow()
            session.commit()
            return existing_sub

        # Create the subscription
        now = datetime.utcnow()
        subscription = Subscription(
            user_id=order_intent.user_id or f"pending-{order_intent.email}",
            email=order_intent.email,
            order_intent_id=order_intent.id,
            stripe_payment_intent_id=order_intent.stripe_payment_intent_id,
            plan="annual",
            amount_cents=order_intent.amount_cents,
            starts_at=now,
            expires_at=now + timedelta(days=365),
            is_active=True,
        )
        session.add(subscription)

        # Mark intent as fulfilled
        order_intent.status = OrderStatus.FULFILLED
        order_intent.fulfilled_at = datetime.utcnow()

        # Commit both in one transaction
        session.commit()

        logger.info(
            f"Fulfilled OrderIntent {order_intent.id} -> "
            f"Subscription {subscription.id} for {order_intent.email}"
        )

        # Send confirmation email (async, non-blocking)
        # If this fails, the subscription still exists. Email failure
        # should never block fulfillment.
        send_confirmation_email.delay(
            email=order_intent.email,
            subscription_id=subscription.id,
            expires_at=subscription.expires_at.isoformat(),
        )

        return subscription

    except Exception as e:
        session.rollback()
        order_intent.status = OrderStatus.CHARGED_FULFILLMENT_FAILED
        order_intent.error_message = str(e)
        order_intent.fulfillment_attempts += 1
        session.commit()
        logger.exception(f"Fulfillment failed for OrderIntent {order_intent.id}: {e}")
        raise


# =============================================================================
# PART 3: THE ERROR PAGE
#
# This is what the user sees when things go wrong. The most important thing
# here is honesty and a clear path forward. They just spent $249. They're
# going to be anxious. Don't give them a generic 500.
# =============================================================================

ERROR_PAGE_TEMPLATE = """
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{{ product_name }} — We're on it</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
            background: #fafafa;
            color: #333;
            line-height: 1.6;
            padding: 40px 20px;
        }
        .container {
            max-width: 600px;
            margin: 0 auto;
            background: white;
            border-radius: 8px;
            padding: 48px 40px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        }
        h1 {
            font-size: 24px;
            margin-bottom: 16px;
            color: #111;
        }
        .status-badge {
            display: inline-block;
            background: #FEF3C7;
            color: #92400E;
            padding: 4px 12px;
            border-radius: 4px;
            font-size: 14px;
            font-weight: 500;
            margin-bottom: 24px;
        }
        p { margin-bottom: 16px; color: #555; }
        .what-happened {
            background: #F9FAFB;
            border-left: 3px solid #D1D5DB;
            padding: 16px 20px;
            margin: 24px 0;
            border-radius: 0 4px 4px 0;
        }
        .what-happened h3 {
            font-size: 14px;
            text-transform: uppercase;
            letter-spacing: 0.05em;
            color: #6B7280;
            margin-bottom: 8px;
        }
        .action-box {
            background: #EFF6FF;
            border: 1px solid #BFDBFE;
            padding: 20px;
            border-radius: 6px;
            margin: 24px 0;
        }
        .action-box h3 {
            color: #1E40AF;
            margin-bottom: 8px;
        }
        a.button {
            display: inline-block;
            background: #2563EB;
            color: white;
            padding: 12px 24px;
            border-radius: 6px;
            text-decoration: none;
            font-weight: 500;
            margin-top: 8px;
        }
        a.button:hover { background: #1D4ED8; }
        .ref-code {
            font-family: monospace;
            background: #F3F4F6;
            padding: 2px 6px;
            border-radius: 3px;
            font-size: 14px;
        }
        .support-info {
            margin-top: 32px;
            padding-top: 24px;
            border-top: 1px solid #E5E7EB;
            font-size: 14px;
            color: #6B7280;
        }
    </style>
</head>
<body>
    <div class="container">
        <span class="status-badge">⚠ Processing Issue</span>
        <h1>Your payment went through. Your account setup didn't.</h1>

        <p>
            We're sorry — our system hit an error after processing your 
            payment of <strong>${{ amount }}</strong>. Your money is safe, 
            and we're going to make this right.
        </p>

        <div class="what-happened">
            <h3>What happened</h3>
            <p style="margin-bottom: 0;">
                Your payment was successfully processed by our payment provider,
                but our server encountered an error before it could set up your 
                account. This means you've been charged but don't have access yet.
            </p>
        </div>

        <div class="action-box">
            <h3>What we're doing about it</h3>
            <p>
                Our system has already flagged this and will automatically 
                set up your account within the next few minutes. You'll receive 
                a confirmation email at <strong>{{ email }}</strong> once 
                everything is ready.
            </p>
            <p>
                If you don't hear from us within <strong>15 minutes</strong>, 
                please contact us and reference this code:
            </p>
            <p><span class="ref-code">{{ reference_code }}</span></p>
            <a href="mailto:{{ support_email }}?subject=Payment%20issue%20-%20{{ reference_code }}" 
               class="button">
                Email Support
            </a>
        </div>

        <div class="support-info">
            <p>
                You can also reach us at <strong>{{ support_email }}</strong>.
                We're a small team and we take this seriously — you'll hear 
                from a real person, not a bot.
            </p>
            {% if order_intent_id %}
            <p>Reference: <span class="ref-code">{{ order_intent_id }}</span></p>
            {% endif %}
        </div>
    </div>
</body>
</html>
"""


def render_error_page(email: str = '', error_type: str = 'server_error',
                      order_intent_id: str = None):
    """
    Render a user-facing error page that's honest, specific, and helpful.

    This is NOT a generic 500 page. This is a page that says:
    "We know you paid. We know you don't have access. Here's what's happening."
    """
    reference_code = order_intent_id or f"ERR-{uuid.uuid4().hex[:8].upper()}"

    return render_template_string(
        ERROR_PAGE_TEMPLATE,
        product_name=PRODUCT_NAME,
        amount=f"{SUBSCRIPTION_PRICE:.2f}",
        email=email or "your email address",
        reference_code=reference_code,
        order_intent_id=order_intent_id,
        support_email=SUPPORT_EMAIL,
    )


# Also provide an endpoint users can check
@app.route('/order-status/<order_intent_id>')
def check_order_status(order_intent_id):
    """
    A page the user can refresh to check if their order has been resolved.
    Link to this from the error page and the recovery email.
    """
    session = Session()
    try:
        intent = session.query(OrderIntent).filter_by(id=order_intent_id).first()

        if not intent:
            return render_template_string("""
                <h1>Order not found</h1>
                <p>Please contact {{ support_email }} with your reference code.</p>
            """, support_email=SUPPORT_EMAIL), 404

        if intent.status in (OrderStatus.FULFILLED, OrderStatus.RECOVERED):
            return render_template_string("""
                <h1>✓ Your account is ready</h1>
                <p>Your subscription is active. 
                <a href="{{ login_url }}">Log in here</a>.</p>
                <p>A confirmation email has been sent to {{ email }}.</p>
            """, login_url=f"{PRODUCT_URL}/login", email=intent.email)

        elif intent.status in (OrderStatus.CHARGED, OrderStatus.CHARGED_FULFILLMENT_FAILED):
            return render_template_string("""
                <h1>⏳ Still processing</h1>
                <p>Your payment was received and we're setting up your account.
                This page will update automatically.</p>
                <p>If this takes more than 15 minutes, please 
                <a href="mailto:{{ support_email }}">contact support</a>.</p>
                <meta http-equiv="refresh" content="30">
            """, support_email=SUPPORT_EMAIL)

        elif intent.status == OrderStatus.REFUNDED:
            return render_template_string("""
                <h1>Refunded</h1>
                <p>This order has been refunded. Please allow 5-10 business days
                for the refund to appear on your statement.</p>
            """)

        else:
            return render_template_string("""
                <h1>Order Status: {{ status }}</h1>
                <p>If you need help, contact {{ support_email }}.</p>
            """, status=intent.status.value, support_email=SUPPORT_EMAIL)
    finally:
        session.close()


# =============================================================================
# PART 4: EMAIL SYSTEM
#
# Three emails matter here:
# 1. Confirmation email (normal flow — everything worked)
# 2. Recovery email (we fixed an orphaned charge)
# 3. Alert to support (something needs human attention)
# =============================================================================

def send_email(to_email: str, subject: str, html_body: str, text_body: str = None):
    """
    Send an email. In production you'd use SendGrid/Postmark/SES,
    but the logic is the same regardless of provider.
    """
    try:
        msg = MIMEMultipart('alternative')
        msg['From'] = f"{PRODUCT_NAME} <{EMAIL_USER}>"
        msg['To'] = to_email
        msg['Subject'] = subject
        msg['Reply-To'] = SUPPORT_EMAIL

        if text_body:
            msg.attach(MIMEText(text_body, 'plain'))
        msg.attach(MIMEText(html_body, 'html'))

        with smtplib.SMTP(EMAIL_HOST, EMAIL_PORT) as server:
            server.starttls()
            server.login(EMAIL_USER, EMAIL_PASS)
            server.send_message(msg)

        logger.info(f"Email sent to {to_email}: {subject}")
        return True

    except Exception as e:
        logger.exception(f"Failed to send email to {to_email}: {e}")
        return False


@celery_app.task(bind=True, max_retries=3, default_retry_delay=60)
def send_confirmation_email(self, email: str, subscription_id: str, expires_at: str):
    """
    Normal confirmation email. Sent after successful fulfillment.
    Runs as a Celery task so email delivery failure doesn't block
    the payment flow.
    """
    try:
        html = f"""
        <div style="font-family: sans-serif; max-width: 600px; margin: 0 auto;">
            <h1 style="color: #111;">Welcome to {PRODUCT_NAME}!</h1>

            <p>Your annual subscription is now active.</p>

            <table style="width: 100%; border-collapse: collapse; margin: 20px 0;">
                <tr>
                    <td style="padding: 8px; color: #666;">Plan</td>
                    <td style="padding: 8px; font-weight: bold;">Annual Subscription</td>
                </tr>
                <tr>
                    <td style="padding: 8px; color: #666;">Amount</td>
                    <td style="padding: 8px; font-weight: bold;">${SUBSCRIPTION_PRICE:.2f}</td>
                </tr>
                <tr>
                    <td style="padding: 8px; color: #666;">Valid through</td>
                    <td style="padding: 8px; font-weight: bold;">{expires_at[:10]}</td>
                </tr>
                <tr>
                    <td style="padding: 8px; color: #666;">Subscription ID</td>
                    <td style="padding: 8px; font-family: monospace;">{subscription_id}</td>
                </tr>
            </table>

            <a href="{PRODUCT_URL}/login" 
               style="display: inline-block; background: #2563EB; color: white; 
                      padding: 12px 24px; border-radius: 6px; text-decoration: none;">
                Get Started
            </a>

            <p style="margin-top: 32px; color: #888; font-size: 14px;">
                Questions? Reply to this email or contact us at {SUPPORT_EMAIL}.
            </p>
        </div>
        """

        success = send_email(
            to_email=email,
            subject=f"Your {PRODUCT_NAME} subscription is active",
            html_body=html,
        )

        if not success:
            raise Exception("Email delivery failed")

    except Exception as e:
        logger.exception(f"Confirmation email failed for {email}: {e}")
        self.retry(exc=e)


@celery_app.task(bind=True, max_retries=3, default_retry_delay=60)
def send_recovery_email(self, email: str, subscription_id: str, expires_at: str):
    """
    Sent when we've recovered an orphaned charge and fulfilled the order.
    Tone matters here: acknowledge the problem, confirm it's fixed.
    """
    try:
        html = f"""
        <div style="font-family: sans-serif; max-width: 600px; margin: 0 auto;">
            <h1 style="color: #111;">Your {PRODUCT_NAME} account is ready</h1>

            <p>
                We owe you an apology. When you purchased your subscription, 
                our system experienced an error that prevented your account from 
                being set up immediately, even though your payment went through.
            </p>

            <p>
                <strong>This has been fixed.</strong> Your subscription is now 
                fully active, and your year starts today — not from when the 
                payment was made. You haven't lost any time.
            </p>

            <table style="width: 100%; border-collapse: collapse; margin: 20px 0; 
                          background: #F0FDF4; border-radius: 6px;">
                <tr>
                    <td style="padding: 12px; color: #166534;" colspan="2">
                        <strong>✓ Subscription Active</strong>
                    </td>
                </tr>
                <tr>
                    <td style="padding: 8px 12px; color: #666;">Plan</td>
                    <td style="padding: 8px 12px;">Annual Subscription</td>
                </tr>
                <tr>
                    <td style="padding: 8px 12px; color: #666;">Amount</td>
                    <td style="padding: 8px 12px;">${SUBSCRIPTION_PRICE:.2f}</td>
                </tr>
                <tr>
                    <td style="padding: 8px 12px; color: #666;">Valid through</td>
                    <td style="padding: 8px 12px; font-weight: bold;">{expires_at[:10]}</td>
                </tr>
            </table>

            <a href="{PRODUCT_URL}/login" 
               style="display: inline-block; background: #2563EB; color: white; 
                      padding: 12px 24px; border-radius: 6px; text-decoration: none;">
                Log In to Your Account
            </a>

            <p style="margin-top: 24px; font-size: 14px; color: #666;">
                If you'd prefer a full refund instead, just reply to this email 
                and we'll process it immediately — no questions asked.
            </p>

            <p style="margin-top: 16px; color: #888; font-size: 14px;">
                Again, we're sorry for the inconvenience. We've updated our systems 
                to prevent this from happening again.
            </p>
        </div>
        """

        success = send_email(
            to_email=email,
            subject=f"Your {PRODUCT_NAME} subscription is now active (sorry for the delay)",
            html_body=html,
        )

        if not success:
            raise Exception("Recovery email delivery failed")

    except Exception as e:
        logger.exception(f"Recovery email failed for {email}: {e}")
        self.retry(exc=e)


@celery_app.task
def alert_support(subject: str, details: str):
    """
    Send an alert to the support team. For a two-person team,
    this goes to both of them.
    """
    html = f"""
    <div style="font-family: monospace; padding: 20px;">
        <h2 style="color: #DC2626;">⚠ {subject}</h2>
        <pre style="background: #1F2937; color: #F9FAFB; padding: 16px; 
                    border-radius: 6px; overflow-x: auto;">{details}</pre>
        <p style="margin-top: 16px;">
            <a href="{PRODUCT_URL}/admin/reconciliation">Open Admin Dashboard</a>
        </p>
    </div>
    """
    send_email(
        to_email=SUPPORT_EMAIL,
        subject=f"[{PRODUCT_NAME} ALERT] {subject}",
        html_body=html,
    )


# =============================================================================
# PART 5: RECONCILIATION
#
# This is the safety net. It runs every 5 minutes and catches any charge
# that Stripe knows about but we haven't fulfilled.
#
# This is what would have saved the user in the original scenario.
# =============================================================================

@celery_app.task
def reconcile_charges():
    """
    Compare what Stripe says happened vs what our database says happened.
    Find and fix any discrepancies.

    This runs on a schedule (every 5 minutes) and is the primary safety net
    for orphaned charges.
    """
    logger.info("Starting charge reconciliation...")
    session = Session()
    fixed_count = 0
    error_count = 0

    try:
        # --- Strategy 1: Find OrderIntents that are CHARGED but not FULFILLED ---
        # These are orders where we know about the charge (webhook came through
        # or we recorded it) but fulfillment failed.
        stuck_intents = session.query(OrderIntent).filter(
            OrderIntent.status.in_([
                OrderStatus.CHARGED,
                OrderStatus.CHARGED_FULFILLMENT_FAILED,
            ]),
            # Give the normal flow 2 minutes to complete before intervening
            OrderIntent.charged_at < datetime.utcnow() - timedelta(minutes=2),
            # Don't retry forever
            OrderIntent.fulfillment_attempts < 5,
        ).all()

        for intent in stuck_intents:
            try:
                logger.info(
                    f"Reconciliation: retrying fulfillment for "
                    f"OrderIntent {intent.id} ({intent.email})"
                )
                fulfill_order(session, intent)

                # This was a recovery, not a normal fulfillment
                intent.status = OrderStatus.RECOVERED
                intent.recovered_at = datetime.utcnow()
                intent.recovery_notes = "Auto-recovered by reconciliation job"
                session.commit()

                # Send the recovery email, not the normal confirmation
                send_recovery_email.delay(
                    email=intent.email,
                    subscription_id=session.query(Subscription).filter_by(
                        order_intent_id=intent.id
                    ).first().id,
                    expires_at=session.query(Subscription).filter_by(
                        order_intent_id=intent.id
                    ).first().expires_at.isoformat(),
                )

                fixed_count += 1
                logger.info(f"Reconciliation: fixed OrderIntent {intent.id}")

            except Exception as e:
                session.rollback()
                error_count += 1
                logger.exception(
                    f"Reconciliation: failed to fix OrderIntent {intent.id}: {e}"
                )

        # --- Strategy 2: Find Stripe charges we don't know about at all ---
        # This catches the case where even the webhook failed, or the
        # OrderIntent was never created (legacy charges, edge cases).
        # Look at the last 24 hours of Stripe charges.

        recent_charges = stripe.PaymentIntent.list(
            created={
                'gte': int((datetime.utcnow() - timedelta(hours=24)).timestamp())
            },
            limit=100,
        )

        for pi in recent_charges.auto_paging_iter():
            if pi.status != 'succeeded':
                continue

            # Do we have a record of this?
            known = session.query(OrderIntent).filter_by(
                stripe_payment_intent_id=pi.id
            ).first()

            if known and known.status in (
                OrderStatus.FULFILLED, OrderStatus.RECOVERED, OrderStatus.REFUNDED
            ):
                continue  # All good

            if known and known.status in (
                OrderStatus.CHARGED, OrderStatus.CHARGED_FULFILLMENT_FAILED
            ):
                continue  # Strategy 1 handles these

            if not known:
                # Completely unknown charge. Create a record and alert support.
                logger.warning(f"Unknown Stripe charge found: {pi.id}")

                metadata = pi.get('metadata', {})
                email = (
                    metadata.get('email') or 
                    pi.get('receipt_email') or 
                    'unknown'
                )

                orphan = OrderIntent(
                    idempotency_key=f"orphan-{pi.id}",
                    email=email,
                    product_id=metadata.get('product_id', 'unknown'),
                    amount_cents=pi.amount,
                    stripe_payment_intent_id=pi.id,
                    stripe_charge_id=pi.get('latest_charge'),
                    status=OrderStatus.ORPHANED,
                    charged_at=datetime.utcfromtimestamp(pi.created),
                    error_message="Found by reconciliation - no matching OrderIntent",
                )
                session.add(orphan)
                session.commit()

                # Alert the support team
                alert_support.delay(
                    subject=f"Orphaned charge found: ${pi.amount/100:.2f} from {email}",
                    details=(
                        f"Stripe PaymentIntent: {pi.id}\n"
                        f"Amount: ${pi.amount/100:.2f}\n"
                        f"Email: {email}\n"
                        f"Time: {datetime.utcfromtimestamp(pi.created).isoformat()}\n"
                        f"Created OrderIntent: {orphan.id}\n\n"
                        f"Action needed: verify and fulfill, or refund."
                    ),
                )

                # Try to auto-fulfill if we have an email
                if email != 'unknown':
                    try:
                        fulfill_order(session, orphan)
                        orphan.status = OrderStatus.RECOVERED
                        orphan.recovered_at = datetime.utcnow()
                        orphan.recovery_notes = (
                            "Auto-recovered by reconciliation (unknown charge)"
                        )
                        session.commit()

                        sub = session.query(Subscription).filter_by(
                            order_intent_id=orphan.id
                        ).first()
                        send_recovery_email.delay(
                            email=email,
                            subscription_id=sub.id,
                            expires_at=sub.expires_at.isoformat(),
                        )
                        fixed_count += 1
                    except Exception as e:
                        session.rollback()
                        error_count += 1
                        logger.exception(f"Could not auto-fulfill orphan {orphan.id}: {e}")

        # --- Strategy 3: Find intents that are stuck in PENDING too long ---
        # These might indicate a Stripe webhook we never received.
        stale_pending = session.query(OrderIntent).filter(
            OrderIntent.status == OrderStatus.PENDING,
            OrderIntent.stripe_payment_intent_id.isnot(None),
            OrderIntent.created_at < datetime.utcnow() - timedelta(minutes=10),
        ).all()

        for intent in stale_pending:
            try:
                # Ask Stripe what happened
                pi = stripe.PaymentIntent.retrieve(intent.stripe_payment_intent_id)

                if pi.status == 'succeeded':
                    logger.warning(
                        f"Stale PENDING intent {intent.id} has succeeded "
                        f"payment - webhook may have been missed"
                    )
                    intent.status = OrderStatus.CHARGED
                    intent.charged_at = datetime.utcnow()
                    intent.stripe_charge_id = pi.get('latest_charge')
                    session.commit()

                    # Fulfill it
                    fulfill_order(session, intent)
                    intent.status = OrderStatus.RECOVERED
                    intent.recovered_at = datetime.utcnow()
                    intent.recovery_notes = "Recovered from stale PENDING state"
                    session.commit()

                    sub = session.query(Subscription).filter_by(
                        order_intent_id=intent.id
                    ).first()
                    send_recovery_email.delay(
                        email=intent.email,
                        subscription_id=sub.id,
                        expires_at=sub.expires_at.isoformat(),
                    )
                    fixed_count += 1

                elif pi.status in ('canceled', 'requires_payment_method'):
                    intent.status = OrderStatus.FAILED
                    intent.error_message = f"Payment never completed (status: {pi.status})"
                    session.commit()

            except Exception as e:
                session.rollback()
                error_count += 1
                logger.exception(f"Error checking stale intent {intent.id}: {e}")

        # Log summary
        logger.info(
            f"Reconciliation complete. "
            f"Fixed: {fixed_count}, Errors: {error_count}"
        )

        if error_count > 0:
            alert_support.delay(
                subject=f"Reconciliation had {error_count} errors",
                details=f"Fixed: {fixed_count}\nErrors: {error_count}\nCheck logs for details.",
            )

    except Exception as e:
        logger.exception(f"Reconciliation job failed: {e}")
        alert_support.delay(
            subject="Reconciliation job crashed",
            details=str(e),
        )
    finally:
        session.close()


# Schedule the reconciliation job
celery_app.conf.beat_schedule = {
    'reconcile-charges': {
        'task': 'orphaned_charge_solution.reconcile_charges',
        'schedule': crontab(minute='*/5'),  # Every 5 minutes
    },
}


# =============================================================================
# PART 6: SUPPORT WORKFLOW
#
# When the user emails in: "I was charged $249 but I don't have access."
# For a two-person team, speed and clarity matter. This gives them tools
# to resolve it in under 2 minutes.
# =============================================================================

@app.route('/admin/support/lookup', methods=['POST'])
def support_lookup():
    """
    Support tool: look up a user's situation by email, reference code,
    or Stripe charge ID. Returns everything the support person needs
    to understand and fix the problem.
    """
    # In production, add authentication for support staff
    data = request.json
    session = Session()

    try:
        email = data.get('email', '').strip().lower()
        reference = data.get('reference', '').strip()
        stripe_id = data.get('stripe_id', '').strip()

        results = {
            'order_intents': [],
            'subscriptions': [],
            'stripe_charges': [],
            'diagnosis': None,
            'suggested_action': None,
        }

        # Look up in our database
        query = session.query(OrderIntent)
        if email:
            intents = query.filter_by(email=email).order_by(
                OrderIntent.created_at.desc()
            ).all()
        elif reference:
            intents = query.filter_by(id=reference).all()
        elif stripe_id:
            intents = query.filter_by(stripe_payment_intent_id=stripe_id).all()
        else:
            return jsonify({'error': 'Provide email, reference, or stripe_id'}), 400

        for intent in intents:
            sub = session.query(Subscription).filter_by(
                order_intent_id=intent.id
            ).first()

            results['order_intents'].append({
                'id': intent.id,
                'status': intent.status.value,
                'amount': intent.amount_cents / 100,
                'stripe_pi': intent.stripe_payment_intent_id,
                'created': intent.created_at.isoformat() if intent.created_at else None,
                'charged': intent.charged_at.isoformat() if intent.charged_at else None,
                'fulfilled': intent.fulfilled_at.isoformat() if intent.fulfilled_at else None,
                'error': intent.error_message,
                'has_subscription': sub is not None,
                'subscription_active': sub.is_active if sub else False,
            })

        # Also check Stripe directly
        if email:
            try:
                stripe_customers = stripe.Customer.list(email=email, limit=5)
                for customer in stripe_customers.auto_paging_iter():
                    charges = stripe.PaymentIntent.list(customer=customer.id, limit=10)
                    for pi in charges.auto_paging_iter():
                        results['stripe_charges'].append({
                            'id': pi.id,
                            'amount': pi.amount / 100,
                            'status': pi.status,
                            'created': datetime.utcfromtimestamp(pi.created).isoformat(),
                        })
            except Exception as e:
                results['stripe_lookup_error'] = str(e)

        # Diagnose the situation
        results['diagnosis'], results['suggested_action'] = diagnose(results)

        return jsonify(results)

    finally:
        session.close()


def diagnose(lookup_results: dict) -> tuple:
    """
    Given the lookup results, figure out what happened and what to do.
    Returns (diagnosis_string, suggested_action_string).
    """
    intents = lookup_results['order_intents']
    stripe_charges = lookup_results['stripe_charges']

    if not intents and not stripe_charges:
        return (
            "No records found in our system or Stripe.",
            "Ask the user for their bank statement showing the charge, "
            "and search Stripe dashboard manually."
        )

    if not intents and stripe_charges:
        succeeded = [c for c in stripe_charges if c['status'] == 'succeeded']
        if succeeded:
            return (
                f"Found {len(succeeded)} succeeded Stripe charge(s) with NO matching "
                f"order in our database. This is the classic orphaned charge.",
                "Use the /admin/support/fulfill endpoint to create the subscription, "
                "or use /admin/support/refund if the user prefers a refund."
            )

    for intent in intents:
        if intent['status'] in ('charged', 'charged_fulfillment_failed', 'orphaned'):
            if not intent['has_subscription']:
                return (
                    f"OrderIntent {intent['id']} is in '{intent['status']}' state "
                    f"with no subscription. Charged at {intent['charged']}. "
                    f"Error: {intent.get('error', 'none recorded')}",
                    "Use /admin/support/fulfill to retry fulfillment for this intent."
                )

        if intent['status'] == 'fulfilled' and intent['has_subscription']:
            if intent['subscription_active']:
                return (
                    "Order appears to be fulfilled and subscription is active. "
                    "User may need to check their email or try logging in again.",
                    "Send the user their login link and resend the confirmation email."
                )

    # Check for duplicate charges
    succeeded_charges = [c for c in stripe_charges if c['status'] == 'succeeded']
    if len(succeeded_charges) > len([i for i in intents if i['status'] in ('fulfilled', 'recovered')]):
        return (
            f"Possible duplicate charge: {len(succeeded_charges)} succeeded charges "
            f"but only {len([i for i in intents if i['has_subscription']])} subscriptions.",
            "Review charges in Stripe dashboard and refund any duplicates."
        )

    return (
        "Situation doesn't match known patterns. Manual review needed.",
        "Check Stripe dashboard and database directly."
    )


@app.route('/admin/support/fulfill', methods=['POST'])
def support_fulfill():
    """
    Support tool: manually trigger fulfillment for an OrderIntent.
    Used when the reconciliation job hasn't caught it yet, or when
    manually resolving an issue.
    """
    data = request.json
    order_intent_id = data.get('order_intent_id')
    support_agent = data.get('agent', 'support')  # e.g., "support:jane"

    session = Session()

    try:
        intent = session.query(OrderIntent).filter_by(id=order_intent_id).first()

        if not intent:
            return jsonify({'error': 'OrderIntent not found'}), 404

        if intent.status in (OrderStatus.FULFILLED, OrderStatus.RECOVERED):
            sub = session.query(Subscription).filter_by(
                order_intent_id=intent.id
            ).first()
            return jsonify({
                'message': 'Already fulfilled',
                'subscription_id': sub.id if sub else None,
            })

        # Verify with Stripe that the charge actually succeeded
        if intent.stripe_payment_intent_id:
            try:
                pi = stripe.PaymentIntent.retrieve(intent.stripe_payment_intent_id)
                if pi.status != 'succeeded':
                    return jsonify({
                        'error': f'Stripe PaymentIntent status is {pi.status}, not succeeded. '
                                 f'Cannot fulfill without confirmed payment.'
                    }), 400
            except stripe.error.StripeError as e:
                return jsonify({'error': f'Could not verify with Stripe: {e}'}), 502

        # Fulfill it
        subscription = fulfill_order(session, intent)

        intent.status = OrderStatus.RECOVERED
        intent.recovered_at = datetime.utcnow()
        intent.recovery_notes = f"Manually fulfilled by {support_agent}"
        subscription.created_by = support_agent
        session.commit()

        # Send recovery email
        send_recovery_email.delay(
            email=intent.email,
            subscription_id=subscription.id,
            expires_at=subscription.expires_at.isoformat(),
        )

        return jsonify({
            'success': True,
            'subscription_id': subscription.id,
            'message': f'Subscription created and recovery email sent to {intent.email}',
        })

    except Exception as e:
        session.rollback()
        logger.exception(f"Support fulfillment failed: {e}")
        return jsonify({'error': str(e)}), 500
    finally:
        session.close()


@app.route('/admin/support/fulfill-by-stripe-id', methods=['POST'])
def support_fulfill_by_stripe_id():
    """
    For when the user has nothing but a bank charge and a Stripe charge ID.
    Creates the OrderIntent retroactively and fulfills.
    """
    data = request.json
    stripe_pi_id = data.get('stripe_payment_intent_id')
    email = data.get('email')
    agent = data.get('agent', 'support')

    session = Session()

    try:
        # Verify with Stripe
        try:
            pi = stripe.PaymentIntent.retrieve(stripe_pi_id)
        except stripe.error.StripeError as e:
            return jsonify({'error': f'Stripe lookup failed: {e}'}), 502

        if pi.status != 'succeeded':
            return jsonify({'error': f'Payment status is {pi.status}'}), 400

        # Check if we already have a record
        existing = session.query(OrderIntent).filter_by(
            stripe_payment_intent_id=stripe_pi_id
        ).first()

        if existing:
            # Redirect to normal fulfill flow
            return support_fulfill()

        # Create retroactive OrderIntent
        resolved_email = email or pi.get('receipt_email') or pi.metadata.get('email')
        if not resolved_email:
            return jsonify({
                'error': 'Cannot determine user email. '
                         'Provide email parameter or check Stripe dashboard.'
            }), 400

        intent = OrderIntent(
            idempotency_key=f"support-recovery-{stripe_pi_id}",
            email=resolved_email,
            product_id=pi.metadata.get('product_id', 'annual_subscription'),
            amount_cents=pi.amount,
            stripe_payment_intent_id=stripe_pi_id,
            stripe_charge_id=pi.get('latest_charge'),
            status=OrderStatus.CHARGED,
            charged_at=datetime.utcfromtimestamp(pi.created),
        )
        session.add(intent)
        session.commit()

        # Fulfill
        subscription = fulfill_order(session, intent)
        intent.status = OrderStatus.RECOVERED
        intent.recovered_at = datetime.utcnow()
        intent.recovery_notes = f"Created retroactively and fulfilled by {agent}"
        subscription.created_by = agent
        session.commit()

        send_recovery_email.delay(
            email=resolved_email,
            subscription_id=subscription.id,
            expires_at=subscription.expires_at.isoformat(),
        )

        return jsonify({
            'success': True,
            'order_intent_id': intent.id,
            'subscription_id': subscription.id,
            'message': f'Retroactive order created, subscription active, email sent to {resolved_email}',
        })

    except Exception as e:
        session.rollback()
        logger.exception(f"Support fulfill by Stripe ID failed: {e}")
        return jsonify({'error': str(e)}), 500
    finally:
        session.close()


@app.route('/admin/support/refund', methods=['POST'])
def support_refund():
    """
    Full refund when the user just wants their money back.
    For a $249 charge with a broken experience, always offer this
    without friction.
    """
    data = request.json
    order_intent_id = data.get('order_intent_id')
    stripe_pi_id = data.get('stripe_payment_intent_id')
    agent = data.get('agent', 'support')
    reason = data.get('reason', 'Fulfillment failure - orphaned charge')

    session = Session()

    try:
        # Find the intent
        if order_intent_id:
            intent = session.query(OrderIntent).filter_by(id=order_intent_id).first()
        elif stripe_pi_id:
            intent = session.query(OrderIntent).filter_by(
                stripe_payment_intent_id=stripe_pi_id
            ).first()
        else:
            return jsonify({'error': 'Provide order_intent_id or stripe_payment_intent_id'}), 400

        pi_id = intent.stripe_payment_intent_id if intent else stripe_pi_id

        if not pi_id:
            return jsonify({'error': 'No Stripe PaymentIntent ID found'}), 400

        # Process refund through Stripe
        try:
            refund = stripe.Refund.create(
                payment_intent=pi_id,
                reason='requested_by_customer',
                metadata={
                    'order_intent_id': intent.id if intent else 'unknown',
                    'refund_reason': reason,
                    'agent': agent,
                }
            )
        except stripe.error.StripeError as e:
            return jsonify({'error': f'Stripe refund failed: {e}'}), 502

        # Update our records
        if intent:
            intent.status = OrderStatus.REFUNDED
            intent.recovery_notes = (
                f"{intent.recovery_notes or ''}\n"
                f"Refunded by {agent}: {reason}\n"
                f"Stripe Refund ID: {refund.id}"
            ).strip()

            # Deactivate any subscription
            sub = session.query(Subscription).filter_by(
                order_intent_id=intent.id
            ).first()
            if sub:
                sub.is_active = False

            session.commit()

        # Email the user
        send_email(
            to_email=intent.email if intent else 'unknown',
            subject=f"{PRODUCT_NAME} — Your refund has been processed",
            html_body=f"""
            <div style="font-family: sans-serif; max-width: 600px; margin: 0 auto;">
                <h1>Your refund is on its way</h1>
                <p>
                    We've processed a full refund of <strong>${(intent.amount_cents if intent else 24900) / 100:.2f}</strong> 
                    to your original payment method.
                </p>
                <p>
                    Please allow 5-10 business days for the refund to appear 
                    on your statement.
                </p>
                <p>Refund reference: <code>{refund.id}</code></p>
                <p style="margin-top: 24px; color: #666;">
                    We're sorry for the trouble. If you'd like to try again 
                    in the future, we'd be glad to have you.
                </p>
            </div>
            """,
        )

        return jsonify({
            'success': True,
            'stripe_refund_id': refund.id,
            'amount_refunded': refund.amount / 100,
            'message': 'Refund processed and user notified.',
        })

    except Exception as e:
        session.rollback()
        logger.exception(f"Refund failed: {e}")
        return jsonify({'error': str(e)}), 500
    finally:
        session.close()


# =============================================================================
# PART 7: SUPPORT WORKFLOW DOCUMENTATION
#
# This isn't code — it's the playbook for the two-person support team.
# Included here because the human process is as important as the code.
# =============================================================================

SUPPORT_PLAYBOOK = """
============================================================
SUPPORT PLAYBOOK: "I was charged but don't have access"
============================================================

EXPECTED RESOLUTION TIME: Under 5 minutes

STEP 1: ACKNOWLEDGE IMMEDIATELY
--------------------------------
Reply within the hour (ideally within minutes). Template:

    Hi [Name],

    I'm so sorry about this. I can see what happened — our system 
    hit an error right after processing your payment. Your $249 
    charge is real and I'm going to fix this right now.

    Give me just a few minutes and I'll have your account set up.
    I'll follow up with a confirmation.

    [Your name]

Why this matters: They're anxious. They gave a small company $249 
and got nothing. Speed and honesty are everything.


STEP 2: DIAGNOSE
-----------------
Use the support lookup tool:

    POST /admin/support/lookup
    {"email": "[email protected]"}

This will:
- Search our database for matching OrderIntents
- Check Stripe for matching charges  
- Return a diagnosis and suggested action


STEP 3: RESOLVE
-----------------
Based on the diagnosis:

A) OrderIntent found, not fulfilled:
    POST /admin/support/fulfill
    {"order_intent_id": "...", "agent": "support:yourname"}

B) No OrderIntent, but Stripe charge found:
    POST /admin/support/fulfill-by-stripe-id
    {"stripe_payment_intent_id": "pi_...", "email": "[email protected]", "agent": "support:yourname"}

C) User wants a refund instead:
    POST /admin/support/refund
    {"order_intent_id": "...", "agent": "support:yourname", "reason": "User preference after fulfillment failure"}

D) Can't find anything:
    - Ask user for last 4 digits of card and exact charge amount
    - Search Stripe dashboard directly
    - If charge confirmed, use option B


STEP 4: FOLLOW UP
-------------------
The system sends an automated recovery email, but also reply personally:

    Hi [Name],

    Your account is now active. You should have received a 
    confirmation email — if not, you can log in directly at 
    [URL].

    Your subscription year starts today (not from when the 
    payment happened), so you haven't lost any time.

    Sorry again for the hassle. Let me know if you need 
    anything else.

    [Your name]


STEP 5: OPTIONAL — GOODWILL
-----------------------------
For a $249 annual plan on a small SaaS, consider:
- Extending their subscription by a month (easy gesture, costs nothing)
- Offering a discount on next year's renewal
- At minimum, a genuine apology

These small gestures turn a negative experience into loyalty.


THINGS TO NEVER DO:
- Don't make them prove the charge happened. Believe them, then verify.
- Don't say "our system shows no record of your payment" (even if true).
- Don't take more than a few hours to resolve this.
- Don't require them to re-purchase.
============================================================
"""


# =============================================================================
# PART 8: STRIPE WEBHOOK AS ADDITIONAL SAFETY NET
#
# Register for checkout.session.completed as a backup signal
# =============================================================================

@app.route('/webhook/stripe/backup', methods=['POST'])
def stripe_backup_webhook():
    """
    A second webhook endpoint on a different path, in case the primary
    webhook handler is down. Belt and suspenders. You can point a second
    Stripe webhook endpoint here.

    This is particularly useful during deployments when the primary
    endpoint might be briefly unavailable.
    """
    # Same logic as primary webhook, but logs differently
    payload = request.data
    sig_header = request.headers.get('Stripe-Signature')

    try:
        event = stripe.Webhook.construct_event(
            payload, sig_header, STRIPE_WEBHOOK_SECRET
        )
    except (ValueError, stripe.error.SignatureVerificationError):
        return jsonify({'error': 'Invalid'}), 400

    if event['type'] == 'payment_intent.succeeded':
        logger.info(f"BACKUP webhook received payment_intent.succeeded: {event['data']['object']['id']}")
        handle_payment_success(event['data']['object'])

    return jsonify({'received': True}), 200


# =============================================================================
# PART 9: HEALTH CHECK & MONITORING
#
# Quick endpoint to see if the system is healthy and if there are
# any unresolved orphans.
# =============================================================================

@app.route('/admin/health')
def system_health():
    """Dashboard data for the support team."""
    session = Session()
    try:
        now = datetime.utcnow()

        orphaned = session.query(OrderIntent).filter(
            OrderIntent.status.in_([
                OrderStatus.CHARGED,
                OrderStatus.CHARGED_FULFILLMENT_FAILED,
                OrderStatus.ORPHANED,
            ])
        ).count()

        recovered_today = session.query(OrderIntent).filter(
            OrderIntent.status == OrderStatus.RECOVERED,
            OrderIntent.recovered_at >= now - timedelta(hours=24),
        ).count()

        fulfilled_today = session.query(OrderIntent).filter(
            OrderIntent.status == OrderStatus.FULFILLED,
            OrderIntent.fulfilled_at >= now - timedelta(hours=24),
        ).count()

        return jsonify({
            'status': 'healthy' if orphaned == 0 else 'attention_needed',
            'unresolved_orphans': orphaned,
            'recovered_last_24h': recovered_today,
            'fulfilled_last_24h': fulfilled_today,
            'timestamp': now.isoformat(),
        })
    finally:
        session.close()


# =============================================================================
# ENTRY POINT
# =============================================================================

if __name__ == '__main__':
    print(SUPPORT_PLAYBOOK)
    print("\nStarting server...")
    app.run(debug=True, port=5000)

What this actually solves

The original failure: payment charges, server crashes, no record, user sees blank page, has no email, has no access.

Layer 1 — Prevention: The OrderIntent is created before we ever charge. The charge happens client-side via Stripe.js, and fulfillment happens in the webhook handler, which Stripe retries automatically. The window for orphaned charges is dramatically smaller.

Layer 2 — Detection: The reconciliation job runs every 5 minutes and catches three cases: intents that are charged but not fulfilled, Stripe charges with no matching intent at all, and intents stuck in pending that Stripe says succeeded.

Layer 3 — Recovery: Fulfillment is a standalone function that can be called from the normal flow, the reconciliation job, or the support tool. It's idempotent — safe to call multiple times.

Layer 4 — Communication: The error page is honest ("your payment went through, your account setup didn't"), gives them a reference code, and tells them what's happening. The recovery email acknowledges the mistake, starts the subscription year from now (not the original charge date), and offers a no-questions refund.

Layer 5 — Human process: The support playbook gives the two-person team a step-by-step workflow with API endpoints they can hit. Diagnosis is automated. Resolution is one API call. The playbook also tells them what not to do — like making the user prove they were charged.

The one thing I'd add in production that I didn't include here is a dead letter queue for failed webhooks and a separate monitoring service (even just an uptime check) that pages someone if the reconciliation job itself stops running. The safety net needs its own safety net.