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 17:20

Rounds: 1

Mode: Conversation

Active Models: claude

Context Mode: clean

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)

No preamble — clean context mode

Transcript

[HUMAN] 2026-03-30 17:13:45 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 17:20:29

Solving the Orphaned Charge Problem

The root issue: payment collection and order creation aren't atomic, and there's no mechanism to detect or recover from partial completion. This solution addresses all five layers.

Architecture Overview

The key insight is write the intent before you charge. Create a pending order record first, charge second, then mark it complete. If anything fails between steps, the pending record is your breadcrumb. The reconciliation process finds these breadcrumbs and fixes them.

For the case that already happened (user already charged, no record), the reconciliation job pulls charges from Stripe and cross-references them against orders.

"""
orphan_charge_solution.py

Complete solution for handling the case where a payment succeeds but the
order record is never written. Organized into six sections:

    1. Database models
    2. Payment flow (the fixed version)
    3. Error pages and user-facing responses
    4. Email confirmation logic
    5. Reconciliation job
    6. Support tools

Dependencies: Flask, SQLAlchemy, Stripe, APScheduler, requests
"""

import os
import time
import json
import hmac
import hashlib
import logging
from enum import Enum
from datetime import datetime, timedelta, timezone
from functools import wraps
from typing import Optional

import stripe
from flask import Flask, request, render_template_string, jsonify, redirect, url_for
from sqlalchemy import (
    create_engine, Column, String, Integer, Float, DateTime,
    Text, Boolean, ForeignKey, Index, Enum as SAEnum
)
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from sqlalchemy.dialects.postgresql import UUID
import uuid

# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------

app = Flask(__name__)
app.config["SECRET_KEY"] = os.environ.get("SECRET_KEY", "change-me")
app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get(
    "DATABASE_URL", "postgresql://localhost/saas_app"
)
app.config["SUPPORT_EMAIL"] = "[email protected]"
app.config["FROM_EMAIL"] = "[email protected]"
app.config["APP_NAME"] = "YourApp"
app.config["APP_URL"] = os.environ.get("APP_URL", "https://yourapp.com")

stripe.api_key = os.environ.get("STRIPE_SECRET_KEY")
STRIPE_WEBHOOK_SECRET = os.environ.get("STRIPE_WEBHOOK_SECRET")

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("payment_system")

engine = create_engine(app.config["SQLALCHEMY_DATABASE_URI"])
Session = sessionmaker(bind=engine)
Base = declarative_base()


# ===================================================================
# SECTION 1: DATABASE MODELS
# ===================================================================

class OrderStatus(Enum):
    """
    The lifecycle of an order. This is the critical piece that was missing.

    PENDING  -> We've created the record, haven't charged yet.
    CHARGED  -> Stripe confirmed the charge. We're about to provision.
    COMPLETE -> Account provisioned, confirmation email sent. Happy path.
    FAILED   -> Charge failed or was declined.
    ORPHANED -> Reconciliation found a charge with no matching complete order.
    RECOVERED -> An orphaned order that has been fixed (by automation or support).
    REFUNDED -> Charge was reversed.
    """
    PENDING = "pending"
    CHARGED = "charged"
    COMPLETE = "complete"
    FAILED = "failed"
    ORPHANED = "orphaned"
    RECOVERED = "recovered"
    REFUNDED = "refunded"


class Order(Base):
    """
    The order record. In the original bug, this never got written.
    The fix: we write it BEFORE charging, in PENDING state.
    """
    __tablename__ = "orders"

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

    # User info — captured before we even attempt to charge
    user_email = Column(String(255), nullable=False, index=True)
    user_name = Column(String(255), nullable=True)

    # Payment info
    stripe_payment_intent_id = Column(String(255), nullable=True, index=True)
    stripe_charge_id = Column(String(255), nullable=True, index=True)
    stripe_customer_id = Column(String(255), nullable=True, index=True)
    amount_cents = Column(Integer, nullable=False)  # $249.00 = 24900
    currency = Column(String(3), default="usd")

    # Product info
    plan_name = Column(String(100), nullable=False)
    subscription_months = Column(Integer, nullable=False)

    # Status tracking
    status = Column(SAEnum(OrderStatus), default=OrderStatus.PENDING, nullable=False, index=True)
    status_history = Column(Text, default="[]")  # JSON array of {status, timestamp, note}

    # Timestamps
    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    charged_at = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)

    # Recovery tracking
    recovery_attempts = Column(Integer, default=0)
    last_recovery_attempt = Column(DateTime, nullable=True)
    recovered_by = Column(String(100), nullable=True)  # "reconciliation_job" or "support:alice"

    # Confirmation
    confirmation_email_sent = Column(Boolean, default=False)
    confirmation_email_sent_at = Column(DateTime, nullable=True)

    # For linking to the provisioned account
    account_id = Column(String(36), nullable=True)
    account_provisioned = Column(Boolean, default=False)

    def add_status_history(self, new_status: OrderStatus, note: str = ""):
        history = json.loads(self.status_history or "[]")
        history.append({
            "from_status": self.status.value if self.status else None,
            "to_status": new_status.value,
            "timestamp": datetime.now(timezone.utc).isoformat(),
            "note": note,
        })
        self.status_history = json.dumps(history)
        self.status = new_status


class ReconciliationLog(Base):
    """
    Audit trail for every reconciliation run. When you're a two-person team,
    you need to trust that the automated job is doing the right thing.
    """
    __tablename__ = "reconciliation_logs"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    run_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    charges_checked = Column(Integer, default=0)
    orphans_found = Column(Integer, default=0)
    orphans_recovered = Column(Integer, default=0)
    errors = Column(Text, default="[]")  # JSON array
    details = Column(Text, default="[]")  # JSON array of what happened
    duration_seconds = Column(Float, nullable=True)


class SupportTicket(Base):
    """
    Minimal ticket system. For a two-person team, this might live in a
    shared spreadsheet or Notion. But having it in the DB lets us link
    tickets to orders and automate the lookup.
    """
    __tablename__ = "support_tickets"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_email = Column(String(255), nullable=False, index=True)
    subject = Column(String(500))
    description = Column(Text)
    category = Column(String(50), default="billing")  # billing, access, general

    # Auto-populated by support tools
    matched_order_id = Column(String(36), nullable=True)
    matched_stripe_charge_id = Column(String(255), nullable=True)

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

    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    resolved_at = Column(DateTime, nullable=True)
    resolved_by = Column(String(100), nullable=True)


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


# ===================================================================
# SECTION 2: THE PAYMENT FLOW (FIXED)
# ===================================================================
# 
# Original flow (broken):
#   1. User clicks "Subscribe"
#   2. Stripe charges the card  <-- money leaves their account
#   3. Server crashes
#   4. Order never written       <-- the bug
#   5. User sees blank page
#
# Fixed flow:
#   1. User clicks "Subscribe"
#   2. Create PENDING order in DB  <-- breadcrumb exists before any charge
#   3. Create Stripe PaymentIntent
#   4. Confirm payment
#   5. Update order to CHARGED
#   6. Provision account
#   7. Update order to COMPLETE
#   8. Send confirmation email
#
# If the server crashes at any step after 2, the reconciliation job
# can find the PENDING/CHARGED order and finish the work.
# ===================================================================

@app.route("/api/subscribe", methods=["POST"])
def subscribe():
    """
    Main subscription endpoint. The key change: we write to the database
    BEFORE we charge, and we treat every step as recoverable.
    """
    session = Session()
    order = None

    try:
        # ---- Step 1: Validate input ----
        data = request.json
        email = data.get("email", "").strip().lower()
        name = data.get("name", "").strip()
        payment_method_id = data.get("payment_method_id")  # From Stripe.js

        if not email or not payment_method_id:
            return jsonify({"error": "Email and payment method are required"}), 400

        # ---- Step 2: Create PENDING order BEFORE charging ----
        # This is the fix. If anything fails after this point,
        # we have a record to reconcile against.
        order = Order(
            user_email=email,
            user_name=name,
            amount_cents=24900,  # $249.00
            currency="usd",
            plan_name="annual",
            subscription_months=12,
            status=OrderStatus.PENDING,
        )
        order.add_status_history(OrderStatus.PENDING, "Order created, about to charge")
        session.add(order)
        session.commit()  # <-- Committed. Even if the server explodes now, this exists.

        logger.info(f"Order {order.id} created in PENDING state for {email}")

        # ---- Step 3: Create or retrieve Stripe customer ----
        # Using idempotency keys so retries don't double-charge.
        customer = stripe.Customer.create(
            email=email,
            name=name,
            payment_method=payment_method_id,
            idempotency_key=f"customer_{order.id}",
        )
        order.stripe_customer_id = customer.id
        session.commit()

        # ---- Step 4: Create and confirm PaymentIntent ----
        # The idempotency key is tied to the order ID, so if this request
        # gets retried (e.g., the response was lost but the charge went through),
        # Stripe returns the same PaymentIntent instead of charging again.
        payment_intent = stripe.PaymentIntent.create(
            amount=24900,
            currency="usd",
            customer=customer.id,
            payment_method=payment_method_id,
            confirm=True,  # Charge immediately
            automatic_payment_methods={"enabled": True, "allow_redirects": "never"},
            metadata={
                "order_id": order.id,  # <-- Critical: links Stripe back to our order
                "plan": "annual",
                "email": email,
            },
            idempotency_key=f"pi_{order.id}",
        )

        order.stripe_payment_intent_id = payment_intent.id

        if payment_intent.status == "succeeded":
            # ---- Step 5: Mark as CHARGED ----
            order.charged_at = datetime.now(timezone.utc)
            order.add_status_history(OrderStatus.CHARGED, f"PaymentIntent {payment_intent.id} succeeded")

            # Get the charge ID for easier support lookups
            if payment_intent.latest_charge:
                order.stripe_charge_id = payment_intent.latest_charge

            session.commit()
            logger.info(f"Order {order.id} charged successfully: {payment_intent.id}")

            # ---- Step 6: Provision the account ----
            try:
                account_id = provision_account(email, name, order.id, session)
                order.account_id = account_id
                order.account_provisioned = True
                session.commit()
            except Exception as e:
                # Provisioning failed, but we already charged. 
                # Log it and let reconciliation handle it, or handle it here.
                logger.error(f"Order {order.id}: provisioning failed: {e}")
                # Don't return an error — the charge succeeded.
                # We'll recover in reconciliation.
                # But DO tell the user something is happening.
                session.commit()

            # ---- Step 7: Mark COMPLETE ----
            order.completed_at = datetime.now(timezone.utc)
            order.add_status_history(OrderStatus.COMPLETE, "Account provisioned")
            session.commit()

            # ---- Step 8: Send confirmation email ----
            try:
                send_confirmation_email(order)
                order.confirmation_email_sent = True
                order.confirmation_email_sent_at = datetime.now(timezone.utc)
                session.commit()
            except Exception as e:
                # Email failed. Not a reason to fail the request.
                # Reconciliation will retry.
                logger.error(f"Order {order.id}: confirmation email failed: {e}")

            return jsonify({
                "success": True,
                "order_id": order.id,
                "message": "Subscription activated! Check your email for details.",
                "redirect": f"/welcome?order={order.id}",
            })

        elif payment_intent.status == "requires_action":
            # 3D Secure or other authentication needed
            order.add_status_history(OrderStatus.PENDING, "Requires additional authentication")
            session.commit()
            return jsonify({
                "requires_action": True,
                "client_secret": payment_intent.client_secret,
                "order_id": order.id,
            })
        else:
            # Payment didn't succeed
            order.add_status_history(OrderStatus.FAILED, f"PaymentIntent status: {payment_intent.status}")
            session.commit()
            return jsonify({
                "error": "Payment was not successful. Please try again.",
            }), 402

    except stripe.error.CardError as e:
        # Card was declined
        if order:
            order.add_status_history(OrderStatus.FAILED, f"Card error: {e.user_message}")
            session.commit()
        logger.warning(f"Card declined for order {order.id if order else 'unknown'}: {e.user_message}")
        return jsonify({"error": f"Payment declined: {e.user_message}"}), 402

    except stripe.error.StripeError as e:
        # Stripe API error — could be transient
        if order:
            order.add_status_history(OrderStatus.PENDING, f"Stripe error: {str(e)}")
            session.commit()
        logger.error(f"Stripe error for order {order.id if order else 'unknown'}: {e}")
        return error_response_with_reference(order, "payment_processor_error")

    except Exception as e:
        # Something else went wrong — this is the crash scenario
        logger.critical(f"Unhandled error in subscribe: {e}", exc_info=True)
        if order:
            try:
                # Try to save the error state
                order.add_status_history(
                    order.status,  # Keep current status
                    f"Server error: {str(e)[:500]}"
                )
                session.commit()
            except Exception:
                session.rollback()
        return error_response_with_reference(order, "server_error")

    finally:
        session.close()


def provision_account(email: str, name: str, order_id: str, session) -> str:
    """
    Create the user's account and activate their subscription.

    In a real app, this would:
    - Create a user record (or link to existing one)
    - Set subscription_expires_at to 1 year from now
    - Generate a login token or set up their password
    - Maybe create some default resources

    The important thing: this must be IDEMPOTENT.
    If called twice for the same order_id, it should return the same account_id
    without duplicating anything.
    """
    # Check if account already exists for this order (idempotency)
    # In a real app, this would check a users/accounts table
    existing = session.execute(
        # Pseudocode — replace with your actual user model query
        # select(User).where(User.provisioning_order_id == order_id)
        f"SELECT id FROM users WHERE provisioning_order_id = :oid",
        {"oid": order_id}
    ).fetchone()

    if existing:
        return existing[0]

    # Create the account
    account_id = str(uuid.uuid4())
    now = datetime.now(timezone.utc)
    expires = now + timedelta(days=365)

    session.execute(
        """
        INSERT INTO users (id, email, name, provisioning_order_id, 
                          subscription_status, subscription_expires_at, created_at)
        VALUES (:id, :email, :name, :order_id, 'active', :expires, :now)
        ON CONFLICT (email) DO UPDATE SET
            subscription_status = 'active',
            subscription_expires_at = :expires,
            provisioning_order_id = :order_id
        """,
        {
            "id": account_id, "email": email, "name": name,
            "order_id": order_id, "expires": expires, "now": now,
        }
    )

    logger.info(f"Account {account_id} provisioned for order {order_id}")
    return account_id


def error_response_with_reference(order: Optional[Order], error_type: str):
    """
    When something goes wrong, give the user a reference number and clear next steps.
    This is what they should have seen instead of a blank page.
    """
    reference = order.id[:8].upper() if order else "UNKNOWN"

    return jsonify({
        "error": True,
        "error_type": error_type,
        "reference": reference,
        "message": (
            "Something went wrong processing your subscription. "
            "Don't worry — if you were charged, your payment is safe. "
            f"Your reference number is {reference}. "
            "Our team has been notified and will email you within the hour. "
            "You can also reach us at [email protected]."
        ),
        "support_email": app.config["SUPPORT_EMAIL"],
        "show_error_page": True,
    }), 500


# ===================================================================
# SECTION 3: USER-FACING ERROR PAGE
# ===================================================================
# 
# This replaces the blank page. When JavaScript gets a 500 back from
# the subscribe endpoint, or when the page itself crashes, the user
# should see something helpful instead of nothing.
# ===================================================================

# The error page template — intentionally simple, no complex JS dependencies.
# If the main app JS failed, this needs to work on its own.
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>We're on it — {{ app_name }}</title>
    <style>
        /* Inline styles so this page works even if CSS fails to load */
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
            background: #f8f9fa;
            color: #333;
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            max-width: 520px;
            background: white;
            border-radius: 12px;
            padding: 40px;
            box-shadow: 0 2px 12px rgba(0,0,0,0.08);
            text-align: center;
        }
        .icon { font-size: 48px; margin-bottom: 16px; }
        h1 { font-size: 22px; margin-bottom: 12px; color: #1a1a1a; }
        .message { font-size: 16px; line-height: 1.6; color: #555; margin-bottom: 24px; }
        .reference {
            background: #f0f4ff;
            border: 1px solid #d0d8f0;
            border-radius: 8px;
            padding: 16px;
            margin-bottom: 24px;
        }
        .reference-label { font-size: 13px; color: #666; margin-bottom: 4px; }
        .reference-code {
            font-size: 24px;
            font-weight: 700;
            letter-spacing: 2px;
            color: #2563eb;
            font-family: 'SF Mono', 'Courier New', monospace;
        }
        .reassurance {
            background: #f0fdf4;
            border: 1px solid #bbf7d0;
            border-radius: 8px;
            padding: 16px;
            margin-bottom: 24px;
            font-size: 14px;
            color: #166534;
        }
        .actions { display: flex; flex-direction: column; gap: 12px; }
        .btn {
            display: block;
            padding: 14px 24px;
            border-radius: 8px;
            text-decoration: none;
            font-size: 15px;
            font-weight: 600;
            text-align: center;
            transition: all 0.15s;
        }
        .btn-primary { background: #2563eb; color: white; }
        .btn-primary:hover { background: #1d4ed8; }
        .btn-secondary { background: #f1f5f9; color: #475569; }
        .btn-secondary:hover { background: #e2e8f0; }
        .footer { margin-top: 24px; font-size: 13px; color: #999; }
    </style>
</head>
<body>
    <div class="container">
        <div class="icon">🔧</div>
        <h1>Something went wrong, but your payment is safe</h1>

        <p class="message">
            We hit a technical issue while setting up your account.
            If you were charged, the payment has been recorded and your 
            subscription will be activated shortly.
        </p>

        {% if reference %}
        <div class="reference">
            <div class="reference-label">Your reference number</div>
            <div class="reference-code">{{ reference }}</div>
        </div>
        {% endif %}

        <div class="reassurance">
            ✓ Our team has been automatically notified<br>
            ✓ If charged, your payment is secure<br>
            ✓ We'll email you at <strong>{{ email }}</strong> within 1 hour
        </div>

        <div class="actions">
            <a href="mailto:{{ support_email }}?subject=Payment%20issue%20-%20ref%20{{ reference }}&body=Hi%2C%0A%0AI%20just%20tried%20to%20subscribe%20and%20something%20went%20wrong.%20My%20reference%20number%20is%20{{ reference }}.%0A%0AThanks"
               class="btn btn-primary">
                Email Support
            </a>
            <a href="/" class="btn btn-secondary">
                Return to Homepage
            </a>
        </div>

        <p class="footer">
            You can also reach us directly at {{ support_email }}
        </p>
    </div>
</body>
</html>
"""

@app.route("/payment-issue")
def payment_error_page():
    """
    Dedicated error page for payment issues.
    The subscribe endpoint (or client-side JS) redirects here on failure.
    """
    reference = request.args.get("ref", "")
    email = request.args.get("email", "")

    return render_template_string(
        ERROR_PAGE_TEMPLATE,
        reference=reference,
        email=email,
        support_email=app.config["SUPPORT_EMAIL"],
        app_name=app.config["APP_NAME"],
    ), 500


# Client-side JavaScript that should wrap the subscribe call
# (included here as a string for completeness)
SUBSCRIBE_JS = """
// This replaces whatever bare fetch/XHR was there before.
// The critical addition: error handling that shows something useful.

async function handleSubscribe(formData) {
    const resultDiv = document.getElementById('subscribe-result');
    const submitButton = document.getElementById('subscribe-button');

    try {
        submitButton.disabled = true;
        submitButton.textContent = 'Processing...';

        const response = await fetch('/api/subscribe', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(formData),
        });

        const data = await response.json();

        if (data.success) {
            // Happy path — redirect to welcome page
            window.location.href = data.redirect;
        } else if (data.requires_action) {
            // Handle 3D Secure
            await handle3DSecure(data.client_secret, data.order_id);
        } else if (data.error) {
            if (data.show_error_page) {
                // Server error — redirect to error page with reference
                window.location.href = `/payment-issue?ref=${data.reference}&email=${encodeURIComponent(formData.email)}`;
            } else {
                // Validation or card error — show inline
                resultDiv.textContent = data.error || data.message;
                resultDiv.className = 'error-message';
            }
        }
    } catch (networkError) {
        // Network failure, timeout, or the server returned non-JSON (crash).
        // This is EXACTLY the blank page scenario.
        // We don't know if the charge went through, so be honest about that.
        window.location.href = `/payment-issue?email=${encodeURIComponent(formData.email)}`;
    } finally {
        submitButton.disabled = false;
        submitButton.textContent = 'Subscribe';
    }
}
"""


# ===================================================================
# SECTION 4: CONFIRMATION EMAIL LOGIC
# ===================================================================

def send_confirmation_email(order: Order):
    """
    Send the subscription confirmation email.

    In production, use your email provider's API (Postmark, SendGrid, SES).
    This shows the logic and the template.

    This function is called:
    1. Immediately after successful subscription (happy path)
    2. By the reconciliation job when recovering an orphaned order
    3. By support tools when manually resolving a ticket
    """
    # Build the email content
    subject = f"Your {app.config['APP_NAME']} subscription is active!"

    # For recovered orders, adjust the messaging
    is_recovery = order.status in (OrderStatus.ORPHANED, OrderStatus.RECOVERED)

    if is_recovery:
        subject = f"Your {app.config['APP_NAME']} subscription is now active — sorry for the delay"

    html_body = build_confirmation_email_html(order, is_recovery)
    text_body = build_confirmation_email_text(order, is_recovery)

    # Send via your email provider
    # Using a generic interface here — replace with your actual provider
    send_email(
        to_email=order.user_email,
        to_name=order.user_name or "",
        from_email=app.config["FROM_EMAIL"],
        from_name=app.config["APP_NAME"],
        subject=subject,
        html_body=html_body,
        text_body=text_body,
        # Tag it so we can track delivery
        tags=["subscription_confirmation", f"order_{order.id}"],
        # Include metadata for debugging
        metadata={
            "order_id": order.id,
            "is_recovery": is_recovery,
        }
    )

    logger.info(f"Confirmation email sent for order {order.id} to {order.user_email}")


def build_confirmation_email_html(order: Order, is_recovery: bool = False) -> str:
    """Build the HTML confirmation email."""

    app_name = app.config["APP_NAME"]
    app_url = app.config["APP_URL"]
    support_email = app.config["SUPPORT_EMAIL"]

    recovery_note = ""
    if is_recovery:
        recovery_note = f"""
        <div style="background: #fef3cd; border: 1px solid #ffc107; border-radius: 8px; 
                    padding: 16px; margin-bottom: 24px; font-size: 14px; color: #856404;">
            <strong>A note from our team:</strong> We experienced a technical issue when you 
            first subscribed, which delayed your confirmation. We're sorry about that — 
            everything is now set up and working. If you were worried about the charge on 
            your statement, rest assured it's been properly applied to your account.
        </div>
        """

    return f"""
    <!DOCTYPE html>
    <html>
    <head><meta charset="UTF-8"></head>
    <body style="font-family: -apple-system, BlinkMacSystemFont, sans-serif; 
                 background: #f5f5f5; padding: 40px 20px;">
        <div style="max-width: 560px; margin: 0 auto; background: white; 
                    border-radius: 12px; padding: 40px; box-shadow: 0 1px 4px rgba(0,0,0,0.1);">

            <h1 style="font-size: 24px; color: #1a1a1a; margin-bottom: 8px;">
                Welcome to {app_name}! 🎉
            </h1>

            {recovery_note}

            <p style="color: #555; font-size: 16px; line-height: 1.6;">
                Your annual subscription is now active. Here are your details:
            </p>

            <div style="background: #f8fafc; border-radius: 8px; padding: 20px; 
                        margin: 24px 0; border: 1px solid #e2e8f0;">
                <table style="width: 100%; font-size: 15px; color: #333;">
                    <tr>
                        <td style="padding: 8px 0; color: #666;">Plan</td>
                        <td style="padding: 8px 0; text-align: right; font-weight: 600;">
                            Annual Subscription
                        </td>
                    </tr>
                    <tr>
                        <td style="padding: 8px 0; color: #666;">Amount</td>
                        <td style="padding: 8px 0; text-align: right; font-weight: 600;">
                            ${order.amount_cents / 100:.2f} USD
                        </td>
                    </tr>
                    <tr>
                        <td style="padding: 8px 0; color: #666;">Order ID</td>
                        <td style="padding: 8px 0; text-align: right; font-family: monospace;">
                            {order.id[:8].upper()}
                        </td>
                    </tr>
                    <tr>
                        <td style="padding: 8px 0; color: #666;">Valid until</td>
                        <td style="padding: 8px 0; text-align: right; font-weight: 600;">
                            {(order.charged_at + timedelta(days=365)).strftime('%B %d, %Y') if order.charged_at else 'One year from activation'}
                        </td>
                    </tr>
                </table>
            </div>

            <a href="{app_url}/login" 
               style="display: block; text-align: center; background: #2563eb; color: white; 
                      padding: 14px 24px; border-radius: 8px; text-decoration: none; 
                      font-weight: 600; font-size: 16px; margin: 24px 0;">
                Log in to your account →
            </a>

            <p style="color: #999; font-size: 13px; text-align: center; margin-top: 32px;">
                Questions? Reply to this email or contact {support_email}
            </p>
        </div>
    </body>
    </html>
    """


def build_confirmation_email_text(order: Order, is_recovery: bool = False) -> str:
    """Plain text version of the confirmation email."""
    app_name = app.config["APP_NAME"]
    app_url = app.config["APP_URL"]

    recovery_note = ""
    if is_recovery:
        recovery_note = (
            "\n[NOTE] We experienced a technical issue when you first subscribed, "
            "which delayed this confirmation. We're sorry — everything is now set up. "
            "The charge on your statement has been properly applied to your account.\n"
        )

    return f"""
Welcome to {app_name}!

{recovery_note}
Your annual subscription is now active.

Plan: Annual Subscription
Amount: ${order.amount_cents / 100:.2f} USD
Order ID: {order.id[:8].upper()}

Log in: {app_url}/login

Questions? Contact {app.config['SUPPORT_EMAIL']}
""".strip()


def send_email(to_email, to_name, from_email, from_name, subject, 
               html_body, text_body, tags=None, metadata=None):
    """
    Generic email sending function. Replace the body with your provider.

    Example implementations for common providers:
    """
    # --- Postmark ---
    # import postmarker
    # client = postmarker.PostmarkClient(server_token=os.environ['POSTMARK_TOKEN'])
    # client.emails.send(
    #     From=f"{from_name} <{from_email}>",
    #     To=f"{to_name} <{to_email}>",
    #     Subject=subject,
    #     HtmlBody=html_body,
    #     TextBody=text_body,
    #     Tag=tags[0] if tags else None,
    #     Metadata=metadata or {},
    # )

    # --- SendGrid ---
    # from sendgrid import SendGridAPIClient
    # from sendgrid.helpers.mail import Mail
    # sg = SendGridAPIClient(os.environ['SENDGRID_API_KEY'])
    # message = Mail(from_email, to_email, subject, text_body)
    # message.html_content = html_body
    # sg.send(message)

    # --- For development: just log it ---
    logger.info(f"EMAIL -> {to_email}: {subject}")


def send_alert_to_team(subject: str, body: str):
    """
    Notify the support team (2 people) about something that needs attention.
    This could be Slack, email, PagerDuty — whatever they actually check.
    """
    # Option 1: Slack webhook
    slack_webhook = os.environ.get("SLACK_WEBHOOK_URL")
    if slack_webhook:
        import requests
        requests.post(slack_webhook, json={
            "text": f"*{subject}*\n{body}",
        }, timeout=5)

    # Option 2: Email to team
    for team_email in ["[email protected]", "[email protected]"]:
        send_email(
            to_email=team_email,
            to_name="",
            from_email=app.config["FROM_EMAIL"],
            from_name=f"{app.config['APP_NAME']} Alerts",
            subject=f"[ALERT] {subject}",
            html_body=f"<pre>{body}</pre>",
            text_body=body,
            tags=["team_alert"],
        )

    logger.warning(f"TEAM ALERT: {subject}")


# ===================================================================
# SECTION 5: RECONCILIATION PROCESS
# ===================================================================
#
# This is the safety net. It runs on a schedule (every 10 minutes)
# and catches EXACTLY the scenario in the prompt:
#   - Charge succeeded in Stripe
#   - No matching COMPLETE order in our database
#
# It handles three cases:
#   A. Order exists in PENDING/CHARGED state → finish provisioning
#   B. Charge exists in Stripe with no order at all → create order, provision
#   C. Order is COMPLETE but confirmation email never sent → resend
# ===================================================================

class ReconciliationJob:
    """
    Finds and fixes inconsistencies between Stripe charges and local orders.
    Designed to be safe to run repeatedly (idempotent).
    """

    def __init__(self):
        self.session = Session()
        self.log_entries = []
        self.errors = []
        self.orphans_found = 0
        self.orphans_recovered = 0
        self.charges_checked = 0

    def run(self):
        """Main entry point. Call this on a schedule."""
        start_time = time.time()
        logger.info("Reconciliation job starting")

        try:
            # Case A: Orders stuck in PENDING or CHARGED
            self.recover_stuck_orders()

            # Case B: Stripe charges with no matching order
            self.reconcile_stripe_charges()

            # Case C: Complete orders missing confirmation emails
            self.retry_failed_emails()

        except Exception as e:
            self.errors.append(f"Fatal error: {str(e)}")
            logger.critical(f"Reconciliation job failed: {e}", exc_info=True)

        finally:
            # Log the run
            duration = time.time() - start_time
            run_log = ReconciliationLog(
                charges_checked=self.charges_checked,
                orphans_found=self.orphans_found,
                orphans_recovered=self.orphans_recovered,
                errors=json.dumps(self.errors),
                details=json.dumps(self.log_entries),
                duration_seconds=duration,
            )
            self.session.add(run_log)
            self.session.commit()
            self.session.close()

            logger.info(
                f"Reconciliation complete: checked={self.charges_checked}, "
                f"found={self.orphans_found}, recovered={self.orphans_recovered}, "
                f"errors={len(self.errors)}, duration={duration:.1f}s"
            )

            # Alert the team if there were problems
            if self.orphans_found > 0 or self.errors:
                send_alert_to_team(
                    f"Reconciliation: {self.orphans_found} orphans, {len(self.errors)} errors",
                    f"Recovered: {self.orphans_recovered}\n"
                    f"Details: {json.dumps(self.log_entries, indent=2)}\n"
                    f"Errors: {json.dumps(self.errors, indent=2)}"
                )

    def recover_stuck_orders(self):
        """
        Case A: Find orders in our DB that are PENDING or CHARGED
        but have been sitting there for more than 5 minutes.
        These represent server crashes mid-flow.
        """
        cutoff = datetime.now(timezone.utc) - timedelta(minutes=5)

        stuck_orders = self.session.query(Order).filter(
            Order.status.in_([OrderStatus.PENDING, OrderStatus.CHARGED]),
            Order.created_at < cutoff,
            Order.recovery_attempts < 5,  # Don't retry forever
        ).all()

        for order in stuck_orders:
            try:
                self._recover_stuck_order(order)
            except Exception as e:
                self.errors.append(f"Failed to recover order {order.id}: {str(e)}")
                logger.error(f"Recovery failed for order {order.id}: {e}", exc_info=True)

    def _recover_stuck_order(self, order: Order):
        """Attempt to complete a stuck order."""
        logger.info(f"Recovering stuck order {order.id} (status: {order.status.value})")

        order.recovery_attempts += 1
        order.last_recovery_attempt = datetime.now(timezone.utc)

        if order.status == OrderStatus.PENDING:
            # We created the order but may not have charged yet.
            # Check Stripe to see if a charge went through.
            if order.stripe_payment_intent_id:
                pi = stripe.PaymentIntent.retrieve(order.stripe_payment_intent_id)
                if pi.status == "succeeded":
                    # Charge went through! Continue from step 5.
                    order.charged_at = datetime.now(timezone.utc)
                    order.stripe_charge_id = pi.latest_charge
                    order.add_status_history(
                        OrderStatus.CHARGED,
                        "Recovered: PaymentIntent was successful"
                    )
                    self.session.commit()
                    # Fall through to provision below
                else:
                    # Charge didn't go through. Mark as failed.
                    order.add_status_history(
                        OrderStatus.FAILED,
                        f"Recovered: PaymentIntent status was {pi.status}"
                    )
                    self.session.commit()
                    self.log_entries.append({
                        "order_id": order.id,
                        "action": "marked_failed",
                        "reason": f"PaymentIntent {pi.status}",
                    })
                    return
            else:
                # No PaymentIntent ID — charge was never attempted.
                # This order was abandoned. Leave it as PENDING for now.
                # After 24 hours, a separate cleanup job can mark it expired.
                self.log_entries.append({
                    "order_id": order.id,
                    "action": "skipped",
                    "reason": "No payment intent — likely abandoned",
                })
                return

        # At this point, order is CHARGED. We need to provision.
        self.orphans_found += 1

        if not order.account_provisioned:
            try:
                account_id = provision_account(
                    order.user_email, order.user_name, order.id, self.session
                )
                order.account_id = account_id
                order.account_provisioned = True
            except Exception as e:
                self.errors.append(f"Provisioning failed for {order.id}: {str(e)}")
                self.session.commit()
                return

        # Mark complete
        order.completed_at = datetime.now(timezone.utc)
        order.recovered_by = "reconciliation_job"
        order.add_status_history(
            OrderStatus.RECOVERED,
            "Automatically recovered by reconciliation job"
        )
        self.session.commit()

        # Send confirmation email
        if not order.confirmation_email_sent:
            try:
                send_confirmation_email(order)
                order.confirmation_email_sent = True
                order.confirmation_email_sent_at = datetime.now(timezone.utc)
                self.session.commit()
            except Exception as e:
                self.errors.append(f"Email failed for recovered order {order.id}: {str(e)}")

        self.orphans_recovered += 1
        self.log_entries.append({
            "order_id": order.id,
            "email": order.user_email,
            "action": "recovered",
            "stripe_pi": order.stripe_payment_intent_id,
        })

        logger.info(f"Successfully recovered order {order.id}")

    def reconcile_stripe_charges(self):
        """
        Case B: Pull recent charges from Stripe and check each one
        has a matching COMPLETE or RECOVERED order.

        This catches the scenario where the server crashed so hard that
        even the PENDING order was lost (e.g., the DB commit in step 2 failed).
        """
        # Look at charges from the last 24 hours
        since = int((datetime.now(timezone.utc) - timedelta(hours=24)).timestamp())

        try:
            # Paginate through recent charges
            charges = stripe.Charge.list(
                created={"gte": since},
                limit=100,
            )
        except stripe.error.StripeError as e:
            self.errors.append(f"Stripe API error: {str(e)}")
            return

        for charge in charges.auto_paging_iter():
            self.charges_checked += 1

            # Skip failed/refunded charges
            if charge.status != "succeeded":
                continue

            # Skip charges that aren't for our subscription product
            # (adjust this filter to match your Stripe setup)
            if charge.amount != 24900:
                continue

            # Does this charge have a matching order?
            existing_order = self.session.query(Order).filter(
                (Order.stripe_charge_id == charge.id) |
                (Order.stripe_payment_intent_id == charge.payment_intent)
            ).first()

            if existing_order:
                # Order exists — check if it's complete
                if existing_order.status in (OrderStatus.COMPLETE, OrderStatus.RECOVERED):
                    continue  # All good
                elif existing_order.status in (OrderStatus.PENDING, OrderStatus.CHARGED):
                    # Will be caught by recover_stuck_orders, skip here
                    continue
            else:
                # !! No order record at all. This is the exact scenario
                # from the prompt: charge went through, nothing in our DB.
                self._create_order_from_stripe_charge(charge)

    def _create_order_from_stripe_charge(self, charge):
        """
        Create an order record from a Stripe charge that has no matching order.
        This handles the worst case: the PENDING order was never even created.
        """
        logger.warning(f"ORPHANED CHARGE found: {charge.id} (${charge.amount/100:.2f})")
        self.orphans_found += 1

        # Extract what we can from the charge and its metadata
        email = (
            charge.metadata.get("email") or 
            charge.billing_details.email or
            (charge.customer and stripe.Customer.retrieve(charge.customer).email) or
            None
        )

        order_id_from_metadata = charge.metadata.get("order_id")

        if not email:
            # We can't even identify who this charge belongs to.
            # Alert the team immediately.
            self.errors.append(
                f"Orphaned charge {charge.id} has no identifiable email. "
                f"Amount: ${charge.amount/100:.2f}. Needs manual review."
            )
            send_alert_to_team(
                "URGENT: Orphaned charge with no email",
                f"Charge ID: {charge.id}\n"
                f"Amount: ${charge.amount/100:.2f}\n"
                f"Created: {datetime.fromtimestamp(charge.created, tz=timezone.utc)}\n"
                f"This needs manual investigation in the Stripe dashboard."
            )
            return

        # Create the order record
        order = Order(
            id=order_id_from_metadata or str(uuid.uuid4()),
            user_email=email,
            user_name=charge.billing_details.name or "",
            stripe_charge_id=charge.id,
            stripe_payment_intent_id=charge.payment_intent,
            stripe_customer_id=charge.customer,
            amount_cents=charge.amount,
            currency=charge.currency,
            plan_name="annual",
            subscription_months=12,
            status=OrderStatus.ORPHANED,
            charged_at=datetime.fromtimestamp(charge.created, tz=timezone.utc),
            recovered_by="reconciliation_job",
        )
        order.add_status_history(
            OrderStatus.ORPHANED,
            f"Created from Stripe charge {charge.id} — no matching order existed"
        )
        self.session.add(order)
        self.session.commit()

        # Now provision the account
        try:
            account_id = provision_account(email, order.user_name, order.id, self.session)
            order.account_id = account_id
            order.account_provisioned = True
            order.completed_at = datetime.now(timezone.utc)
            order.add_status_history(OrderStatus.RECOVERED, "Auto-provisioned from orphaned charge")
            self.session.commit()
        except Exception as e:
            self.errors.append(f"Failed to provision from orphaned charge {charge.id}: {str(e)}")
            self.session.commit()
            return

        # Send confirmation email
        try:
            send_confirmation_email(order)
            order.confirmation_email_sent = True
            order.confirmation_email_sent_at = datetime.now(timezone.utc)
            self.session.commit()
        except Exception as e:
            self.errors.append(f"Email failed for orphaned charge {charge.id}: {str(e)}")

        self.orphans_recovered += 1
        self.log_entries.append({
            "charge_id": charge.id,
            "email": email,
            "action": "created_and_recovered",
            "order_id": order.id,
        })

        logger.info(f"Recovered orphaned charge {charge.id} -> order {order.id}")

    def retry_failed_emails(self):
        """
        Case C: Find orders that are COMPLETE but never got a confirmation email.
        Less urgent than orphaned charges, but still important.
        """
        unconfirmed = self.session.query(Order).filter(
            Order.status.in_([OrderStatus.COMPLETE, OrderStatus.RECOVERED]),
            Order.confirmation_email_sent == False,
            Order.completed_at < datetime.now(timezone.utc) - timedelta(minutes=2),
        ).all()

        for order in unconfirmed:
            try:
                send_confirmation_email(order)
                order.confirmation_email_sent = True
                order.confirmation_email_sent_at = datetime.now(timezone.utc)
                self.session.commit()
                self.log_entries.append({
                    "order_id": order.id,
                    "action": "email_retry_success",
                })
            except Exception as e:
                self.errors.append(f"Email retry failed for order {order.id}: {str(e)}")


def run_reconciliation():
    """Entry point for the scheduled job."""
    job = ReconciliationJob()
    job.run()


# Schedule with APScheduler
# In production, you might use cron, Celery Beat, or a cloud scheduler instead.
def setup_scheduler():
    """
    Call this once at app startup. Runs reconciliation every 10 minutes.
    For a $249 subscription, 10 minutes is the max a user should wait
    for automatic recovery. Adjust based on your volume.
    """
    from apscheduler.schedulers.background import BackgroundScheduler

    scheduler = BackgroundScheduler()
    scheduler.add_job(run_reconciliation, 'interval', minutes=10)
    scheduler.start()
    logger.info("Reconciliation scheduler started (every 10 minutes)")


# ===================================================================
# SECTION 5.5: STRIPE WEBHOOK HANDLER
# ===================================================================
#
# Belt AND suspenders. In addition to the polling reconciliation job,
# listen for Stripe webhooks. This gives us near-real-time recovery.
# ===================================================================

@app.route("/webhooks/stripe", methods=["POST"])
def stripe_webhook():
    """
    Handle Stripe webhook events. This is the fastest path to recovery:
    Stripe tells us a charge succeeded, we immediately check if we
    have a matching complete order.
    """
    payload = request.get_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 "Invalid signature", 400

    # Handle the events we care about
    if event.type == "payment_intent.succeeded":
        handle_payment_intent_succeeded(event.data.object)
    elif event.type == "charge.succeeded":
        handle_charge_succeeded(event.data.object)

    return "ok", 200


def handle_payment_intent_succeeded(payment_intent):
    """
    A PaymentIntent succeeded. Make sure we have a matching complete order.
    This fires within seconds of the charge, much faster than the 10-minute
    reconciliation job.
    """
    session = Session()
    try:
        order = session.query(Order).filter(
            Order.stripe_payment_intent_id == payment_intent.id
        ).first()

        if not order:
            # No order at all — the server crashed before even creating the pending record,
            # OR this is from a different payment flow. Check metadata.
            order_id = payment_intent.metadata.get("order_id")
            if order_id:
                order = session.query(Order).get(order_id)

        if order and order.status in (OrderStatus.COMPLETE, OrderStatus.RECOVERED):
            return  # Already handled, nothing to do

        if order and order.status in (OrderStatus.PENDING, OrderStatus.CHARGED):
            # Order exists but isn't complete. Finish it.
            logger.info(f"Webhook recovering order {order.id}")

            order.charged_at = order.charged_at or datetime.now(timezone.utc)
            order.stripe_charge_id = payment_intent.latest_charge

            if not order.account_provisioned:
                account_id = provision_account(
                    order.user_email, order.user_name, order.id, session
                )
                order.account_id = account_id
                order.account_provisioned = True

            order.completed_at = datetime.now(timezone.utc)
            order.recovered_by = "stripe_webhook"
            order.add_status_history(
                OrderStatus.RECOVERED,
                f"Recovered via webhook for PI {payment_intent.id}"
            )
            session.commit()

            if not order.confirmation_email_sent:
                send_confirmation_email(order)
                order.confirmation_email_sent = True
                order.confirmation_email_sent_at = datetime.now(timezone.utc)
                session.commit()

        elif not order:
            # No order record at all. This will be caught by reconciliation,
            # but let's try to fix it now.
            email = payment_intent.metadata.get("email")
            if email:
                logger.warning(
                    f"Webhook: No order for PI {payment_intent.id}, creating one"
                )
                # Delegate to reconciliation logic — it's already idempotent
                job = ReconciliationJob()
                charge = stripe.Charge.retrieve(payment_intent.latest_charge)
                job._create_order_from_stripe_charge(charge)

    except Exception as e:
        logger.error(f"Webhook handler error: {e}", exc_info=True)
    finally:
        session.close()


def handle_charge_succeeded(charge):
    """Backup handler — same logic, triggered by charge event."""
    # Delegate to payment_intent handler if possible
    if charge.payment_intent:
        pi = stripe.PaymentIntent.retrieve(charge.payment_intent)
        handle_payment_intent_succeeded(pi)


# ===================================================================
# SECTION 6: SUPPORT WORKFLOW AND TOOLS
# ===================================================================
#
# The user has been charged $249. They see no confirmation. They email
# [email protected]. Here's what the two-person support team needs:
#
# 1. Instant context: look up the user and show everything we know
# 2. One-click fix: resolve the issue without touching Stripe manually
# 3. Communication templates: consistent, empathetic responses
# ===================================================================

class SupportTools:
    """
    Internal tools for the support team. In practice, these might be
    CLI commands, an admin web panel, or Slack bot commands.
    """

    def __init__(self):
        self.session = Session()

    def lookup_user(self, email: str) -> dict:
        """
        FIRST THING support does when a user writes in.
        Returns everything we know, plus a recommended action.

        Usage: support_tools.lookup_user("[email protected]")
        """
        email = email.strip().lower()
        result = {
            "email": email,
            "orders": [],
            "stripe_charges": [],
            "stripe_customers": [],
            "diagnosis": None,
            "recommended_action": None,
        }

        # Check our database
        orders = self.session.query(Order).filter(
            Order.user_email == email
        ).order_by(Order.created_at.desc()).all()

        for order in orders:
            result["orders"].append({
                "id": order.id,
                "status": order.status.value,
                "amount": f"${order.amount_cents / 100:.2f}",
                "created": order.created_at.isoformat() if order.created_at else None,
                "charged": order.charged_at.isoformat() if order.charged_at else None,
                "completed": order.completed_at.isoformat() if order.completed_at else None,
                "stripe_pi": order.stripe_payment_intent_id,
                "stripe_charge": order.stripe_charge_id,
                "account_provisioned": order.account_provisioned,
                "email_sent": order.confirmation_email_sent,
                "history": json.loads(order.status_history or "[]"),
            })

        # Check Stripe directly
        try:
            customers = stripe.Customer.list(email=email, limit=10)
            for customer in customers:
                charges = stripe.Charge.list(customer=customer.id, limit=20)
                result["stripe_customers"].append({
                    "id": customer.id,
                    "created": datetime.fromtimestamp(
                        customer.created, tz=timezone.utc
                    ).isoformat(),
                })
                for charge in charges:
                    charge_info = {
                        "id": charge.id,
                        "amount": f"${charge.amount / 100:.2f}",
                        "status": charge.status,
                        "created": datetime.fromtimestamp(
                            charge.created, tz=timezone.utc
                        ).isoformat(),
                        "payment_intent": charge.payment_intent,
                        "refunded": charge.refunded,
                        "has_matching_order": any(
                            o.stripe_charge_id == charge.id 
                            for o in orders
                        ),
                    }
                    result["stripe_charges"].append(charge_info)
        except stripe.error.StripeError as e:
            result["stripe_error"] = str(e)

        # Diagnose the situation
        result["diagnosis"] = self._diagnose(result)
        result["recommended_action"] = self._recommend_action(result)

        return result

    def _diagnose(self, lookup_result: dict) -> str:
        """Analyze the lookup result and describe what happened."""
        orders = lookup_result["orders"]
        charges = lookup_result["stripe_charges"]

        successful_charges = [c for c in charges if c["status"] == "succeeded"]
        complete_orders = [o for o in orders if o["status"] in ("complete", "recovered")]
        orphan_charges = [c for c in successful_charges if not c["has_matching_order"]]
        stuck_orders = [o for o in orders if o["status"] in ("pending", "charged")]

        if not successful_charges and not orders:
            return "NO_CHARGE_NO_ORDER: User was not charged and has no order. May be confused about another service."

        if orphan_charges:
            return (
                f"ORPHAN_CHARGE: Found {len(orphan_charges)} Stripe charge(s) with no "
                f"matching complete order. This is the crash scenario. "
                f"Charge IDs: {[c['id'] for c in orphan_charges]}"
            )

        if stuck_orders:
            return (
                f"STUCK_ORDER: Found {len(stuck_orders)} order(s) in "
                f"{[o['status'] for o in stuck_orders]} state. "
                f"The payment flow was interrupted."
            )

        if complete_orders and not successful_charges:
            return "ORDER_NO_CHARGE: Order exists but no matching Stripe charge. Unusual — investigate."

        if complete_orders:
            # Check if email was sent
            unsent = [o for o in complete_orders if not o["email_sent"]]
            if unsent:
                return "COMPLETE_NO_EMAIL: Order is complete but confirmation email was never sent."
            return "ALL_GOOD: Order is complete and email was sent. User may not have received it (spam folder?)."

        return f"UNCLEAR: {len(orders)} orders, {len(charges)} charges. Needs manual review."

    def _recommend_action(self, lookup_result: dict) -> str:
        """Suggest what the support person should do."""
        diagnosis = lookup_result["diagnosis"]

        if diagnosis.startswith("ORPHAN_CHARGE"):
            return (
                "Run: support_tools.recover_order_for_user(email)\n"
                "This will create the order, provision the account, and send confirmation.\n"
                "Then reply to the user with the RECOVERY template."
            )

        if diagnosis.startswith("STUCK_ORDER"):
            return (
                "Run: support_tools.recover_order_for_user(email)\n"
                "This will complete the stuck order.\n"
                "Reply with the RECOVERY template."
            )

        if diagnosis.startswith("COMPLETE_NO_EMAIL"):
            return (
                "Run: support_tools.resend_confirmation(email)\n"
                "Then reply with the RESEND template."
            )

        if diagnosis.startswith("ALL_GOOD"):
            return (
                "The order looks fine. Ask the user to:\n"
                "1. Check spam/promotions folder\n"
                "2. Try logging in at /login with their email\n"
                "3. Use the password reset flow if needed\n"
                "Reply with the CHECK_SPAM template."
            )

        if diagnosis.startswith("NO_CHARGE"):
            return (
                "No charge found for this email. Ask the user:\n"
                "1. What email they used to subscribe\n"
                "2. Last 4 digits of the card charged\n"
                "3. Exact amount and date of charge\n"
                "Reply with the NOT_FOUND template."
            )

        return "Manual review needed. Check Stripe dashboard directly."

    def recover_order_for_user(self, email: str) -> dict:
        """
        One-command recovery. The support person runs this, and it:
        1. Finds the orphaned charge or stuck order
        2. Creates/completes the order
        3. Provisions the account
        4. Sends the confirmation email

        Returns a summary of what was done.
        """
        email = email.strip().lower()
        actions_taken = []

        # First, check for stuck orders
        stuck_orders = self.session.query(Order).filter(
            Order.user_email == email,
            Order.status.in_([
                OrderStatus.PENDING, OrderStatus.CHARGED, OrderStatus.ORPHANED
            ]),
        ).all()

        for order in stuck_orders:
            try:
                # Verify the charge in Stripe
                if order.stripe_payment_intent_id:
                    pi = stripe.PaymentIntent.retrieve(order.stripe_payment_intent_id)
                    if pi.status == "succeeded":
                        order.charged_at = order.charged_at or datetime.fromtimestamp(
                            pi.created, tz=timezone.utc
                        )
                        order.stripe_charge_id = order.stripe_charge_id or pi.latest_charge

                        if not order.account_provisioned:
                            account_id = provision_account(
                                email, order.user_name, order.id, self.session
                            )
                            order.account_id = account_id
                            order.account_provisioned = True
                            actions_taken.append(f"Provisioned account for order {order.id}")

                        order.completed_at = datetime.now(timezone.utc)
                        order.recovered_by = "support:manual"
                        order.add_status_history(
                            OrderStatus.RECOVERED,
                            "Recovered by support team"
                        )
                        self.session.commit()
                        actions_taken.append(f"Completed order {order.id}")

                        if not order.confirmation_email_sent:
                            send_confirmation_email(order)
                            order.confirmation_email_sent = True
                            order.confirmation_email_sent_at = datetime.now(timezone.utc)
                            self.session.commit()
                            actions_taken.append(f"Sent confirmation email for order {order.id}")

            except Exception as e:
                actions_taken.append(f"ERROR recovering order {order.id}: {str(e)}")

        # Then check for Stripe charges with no order at all
        if not stuck_orders:
            try:
                customers = stripe.Customer.list(email=email, limit=5)
                for customer in customers:
                    charges = stripe.Charge.list(
                        customer=customer.id, 
                        limit=10
                    )
                    for charge in charges:
                        if charge.status != "succeeded" or charge.amount != 24900:
                            continue

                        # Check if this charge has a matching order
                        existing = self.session.query(Order).filter(
                            (Order.stripe_charge_id == charge.id) |
                            (Order.stripe_payment_intent_id == charge.payment_intent)
                        ).first()

                        if not existing:
                            # Create order from this charge
                            job = ReconciliationJob()
                            job._create_order_from_stripe_charge(charge)
                            actions_taken.append(
                                f"Created and recovered order from charge {charge.id}"
                            )
                        elif existing.status not in (OrderStatus.COMPLETE, OrderStatus.RECOVERED):
                            actions_taken.append(
                                f"Found order {existing.id} in {existing.status.value} — "
                                f"run this function again to recover"
                            )
            except stripe.error.StripeError as e:
                actions_taken.append(f"Stripe error: {str(e)}")

        if not actions_taken:
            actions_taken.append("No stuck orders or orphaned charges found for this email.")

        return {
            "email": email,
            "actions": actions_taken,
            "timestamp": datetime.now(timezone.utc).isoformat(),
        }

    def resend_confirmation(self, email: str) -> dict:
        """Resend confirmation email for the most recent complete order."""
        order = self.session.query(Order).filter(
            Order.user_email == email.strip().lower(),
            Order.status.in_([OrderStatus.COMPLETE, OrderStatus.RECOVERED]),
        ).order_by(Order.completed_at.desc()).first()

        if not order:
            return {"error": f"No complete order found for {email}"}

        send_confirmation_email(order)
        order.confirmation_email_sent = True
        order.confirmation_email_sent_at = datetime.now(timezone.utc)
        self.session.commit()

        return {
            "success": True,
            "order_id": order.id,
            "email": email,
        }

    def issue_refund(self, email: str, reason: str = "requested_by_customer") -> dict:
        """
        Full refund. For when recovery isn't possible or the user just wants out.
        Two-person team needs this to be a one-step operation.
        """
        order = self.session.query(Order).filter(
            Order.user_email == email.strip().lower(),
            Order.stripe_charge_id.isnot(None),
        ).order_by(Order.created_at.desc()).first()

        if not order:
            return {"error": f"No charged order found for {email}"}

        try:
            refund = stripe.Refund.create(
                charge=order.stripe_charge_id,
                reason=reason,
                metadata={"order_id": order.id, "support_refund": "true"},
            )

            order.add_status_history(
                OrderStatus.REFUNDED,
                f"Refund {refund.id} issued by support. Reason: {reason}"
            )
            self.session.commit()

            # Send refund confirmation email
            send_email(
                to_email=order.user_email,
                to_name=order.user_name or "",
                from_email=app.config["FROM_EMAIL"],
                from_name=app.config["APP_NAME"],
                subject=f"Your {app.config['APP_NAME']} refund has been processed",
                html_body=f"""
                    <p>Hi {order.user_name or 'there'},</p>
                    <p>Your refund of ${order.amount_cents/100:.2f} has been processed. 
                    It may take 5-10 business days to appear on your statement.</p>
                    <p>Refund reference: {refund.id}</p>
                    <p>We're sorry things didn't work out. If you'd like to try again 
                    in the future, we'd love to have you back.</p>
                """,
                text_body=f"Your refund of ${order.amount_cents/100:.2f} has been processed. "
                          f"Refund reference: {refund.id}",
                tags=["refund"],
            )

            return {
                "success": True,
                "refund_id": refund.id,
                "amount": f"${order.amount_cents/100:.2f}",
                "order_id": order.id,
            }

        except stripe.error.StripeError as e:
            return {"error": f"Stripe refund failed: {str(e)}"}

    def close(self):
        self.session.close()


# --- Support email response templates ---
# The two-person team copy-pastes these and customizes as needed.

SUPPORT_TEMPLATES = {
    "RECOVERY": """
Subject: Your {app_name} subscription is now active — we're sorry about that

Hi {name},

Thank you for reaching out, and I'm sorry about the trouble with your 
subscription. Here's what happened:

We experienced a brief technical issue right when you subscribed, which 
prevented your account from being set up even though your payment went 
through. This has now been fixed.

Your subscription is active, and I've just sent you a fresh confirmation 
email with all the details. You can log in right now at:

  {app_url}/login

Use the email address you subscribed with ({email}). If you need to set 
a password, click "Forgot password" on the login page.

Your subscription details:
- Plan: Annual ($249/year)
- Order reference: {order_ref}
- Active until: {expiry_date}

I completely understand how concerning it is to see a charge without 
getting confirmation. We've made changes to our system to prevent this 
from happening again.

Please don't hesitate to write back if you need anything else.

{support_name}
{app_name}
""",

    "RESEND": """
Subject: Re: {original_subject}

Hi {name},

I've just resent your confirmation email — please check your inbox 
(and spam/promotions folder, just in case).

Your subscription is active and everything looks good on our end. 
You can log in at: {app_url}/login

Let me know if you have any other questions!

{support_name}
""",

    "REFUND_OFFERED": """
Subject: Re: {original_subject}

Hi {name},

I'm really sorry about this experience. I've now activated your 
subscription and sent you a confirmation email.

However, I completely understand if this has shaken your confidence. 
If you'd prefer a full refund instead, just let me know and I'll 
process it immediately — no questions asked.

Either way, I want to make sure you're taken care of.

{support_name}
{app_name}
""",

    "NOT_FOUND": """
Subject: Re: {original_subject}

Hi {name},

I've looked into this, and I'm not finding a charge associated with 
{email}. A few things that might help me track it down:

1. Could the charge be under a different email address?
2. What are the last 4 digits of the card that was charged?
3. What's the exact amount and date shown on your statement?

With that info I can search our payment processor directly and 
get this sorted out quickly.

{support_name}
{app_name}
""",
}


# --- Admin API endpoints for the support tools ---
# These would be behind authentication in production.

@app.route("/admin/support/lookup", methods=["POST"])
def admin_lookup_user():
    """Support team endpoint to look up a user."""
    # In production: verify admin authentication here
    email = request.json.get("email", "")
    tools = SupportTools()
    try:
        result = tools.lookup_user(email)
        return jsonify(result)
    finally:
        tools.close()


@app.route("/admin/support/recover", methods=["POST"])
def admin_recover_user():
    """Support team endpoint to recover a user's order."""
    email = request.json.get("email", "")
    tools = SupportTools()
    try:
        result = tools.recover_order_for_user(email)
        return jsonify(result)
    finally:
        tools.close()


@app.route("/admin/support/refund", methods=["POST"])
def admin_refund_user():
    """Support team endpoint to issue a refund."""
    email = request.json.get("email", "")
    reason = request.json.get("reason", "requested_by_customer")
    tools = SupportTools()
    try:
        result = tools.issue_refund(email, reason)
        return jsonify(result)
    finally:
        tools.close()


@app.route("/admin/reconciliation/run", methods=["POST"])
def admin_run_reconciliation():
    """Manually trigger reconciliation."""
    run_reconciliation()
    return jsonify({"status": "completed"})


@app.route("/admin/reconciliation/history", methods=["GET"])
def admin_reconciliation_history():
    """View reconciliation run history."""
    session = Session()
    try:
        logs = session.query(ReconciliationLog).order_by(
            ReconciliationLog.run_at.desc()
        ).limit(20).all()

        return jsonify([{
            "id": log.id,
            "run_at": log.run_at.isoformat(),
            "charges_checked": log.charges_checked,
            "orphans_found": log.orphans_found,
            "orphans_recovered": log.orphans_recovered,
            "errors": json.loads(log.errors),
            "duration_seconds": log.duration_seconds,
        } for log in logs])
    finally:
        session.close()


# ===================================================================
# MAIN
# ===================================================================

if __name__ == "__main__":
    setup_scheduler()
    app.run(debug=True, port=5000)

How This Addresses the Original Scenario

What happened to the user: 1. Paid $249 → Stripe charged successfully 2. Server crashed → blank page 3. No order in DB → no confirmation email, no account

How this system recovers (three independent paths, any one is sufficient):

**Path A — Stripe