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:47

Rounds: 1

Mode: Conversation

Active Models: claude, gemini, codex

Context Mode: clean

Methodology

Models

• claude: claude-opus-4-6
• gemini: gemini-3.1-pro-preview
• codex: gpt-5.4

System Prompt (per model)

You are claude, alongside gemini-3.1-pro-preview, gpt-5.4 and 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:41:46 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:46:35

The Full System

This problem has one root cause and four layers of defense. I'll build all of them.

Root cause: The payment charge and the database write are not atomic. You can't make them atomic across two systems, so you have to design for the gap.

Defense layers: 1. Write a pending order before charging, so the crash window is narrower and recovery has something to find 2. Stripe webhooks as an asynchronous backup confirmation path 3. A reconciliation job that periodically compares Stripe's records to yours 4. Support tools that let a human resolve it in under two minutes

"""
subscription_system.py

Complete payment flow, recovery, and support system for a small SaaS product.

Architecture overview:

    The fundamental problem is that "charge the card" and "write the order to our
    database" are two separate operations that can't be wrapped in a single 
    transaction. The card network and your PostgreSQL instance don't share a 
    transaction log. So you design for partial failure at every step.

    The strategy:
    1. Write a PENDING order to our DB *before* we talk to Stripe.
    2. Charge via Stripe with an idempotency key tied to that order.
    3. On success, mark the order COMPLETED, provision access, send email.
    4. If step 3 fails (crash, timeout, network), three safety nets catch it:
       a. Stripe webhook fires → webhook handler completes the order
       b. Reconciliation job finds orphaned Stripe charges → completes them
       c. User contacts support → support tool resolves it immediately

Dependencies:
    pip install flask sqlalchemy stripe python-dateutil requests
"""

import os
import uuid
import json
import hmac
import hashlib
import logging
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime, timedelta
from functools import wraps
from typing import Optional, Tuple

import stripe
from flask import Flask, request, jsonify, render_template_string, g
from sqlalchemy import (
    create_engine, Column, String, Integer, Float, DateTime,
    Boolean, Text, Enum as SAEnum, event
)
from sqlalchemy.orm import declarative_base, sessionmaker, Session
from sqlalchemy.dialects.postgresql import UUID

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

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(name)s: %(message)s"
)
logger = logging.getLogger("subscription_system")

stripe.api_key = os.environ.get("STRIPE_SECRET_KEY", "sk_test_...")
STRIPE_WEBHOOK_SECRET = os.environ.get("STRIPE_WEBHOOK_SECRET", "whsec_...")
STRIPE_PRICE_ID = os.environ.get("STRIPE_PRICE_ID", "price_yearly_249")

DATABASE_URL = os.environ.get("DATABASE_URL", "postgresql://localhost/saas_app")
SMTP_HOST = os.environ.get("SMTP_HOST", "smtp.postmarkapp.com")
SMTP_PORT = int(os.environ.get("SMTP_PORT", "587"))
SMTP_USER = os.environ.get("SMTP_USER", "")
SMTP_PASS = os.environ.get("SMTP_PASS", "")
FROM_EMAIL = os.environ.get("FROM_EMAIL", "[email protected]")
SUPPORT_EMAIL = os.environ.get("SUPPORT_EMAIL", "[email protected]")
APP_URL = os.environ.get("APP_URL", "https://yourapp.com")

YEARLY_PRICE_CENTS = 24900  # $249.00

# ---------------------------------------------------------------------------
# Database Models
# ---------------------------------------------------------------------------

engine = create_engine(DATABASE_URL, echo=False, pool_pre_ping=True)
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()


class Order(Base):
    """
    The order record is the central piece of the recovery strategy.

    It is created BEFORE we ever talk to Stripe, with status='pending'.
    This means that even if the server crashes mid-charge, we have a record
    that says "we tried to charge this person." The reconciliation job and
    webhooks can find it and finish the work.

    If the crash happens before even THIS write completes — truly catastrophic 
    timing — the reconciliation job will still find the Stripe charge by 
    scanning Stripe's records directly. That's layer 3.
    """
    __tablename__ = "orders"

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

    # Idempotency key sent to Stripe. If we crash and retry, Stripe returns
    # the same result instead of double-charging.
    idempotency_key = Column(String(64), unique=True, nullable=False)

    user_email = Column(String(255), nullable=False, index=True)
    user_name = Column(String(255), nullable=True)

    # 'pending' → 'completed' → normal flow
    # 'pending' → 'failed' → charge was declined or errored
    # 'pending' → 'completed' (via webhook or reconciliation) → crash recovery
    # 'orphaned' → set by reconciliation when Stripe has a charge we have no order for
    status = Column(String(20), nullable=False, default="pending", index=True)

    amount_cents = Column(Integer, nullable=False)
    currency = Column(String(3), nullable=False, default="usd")

    # Stripe references — filled in as we get them
    stripe_payment_intent_id = Column(String(255), nullable=True, index=True)
    stripe_customer_id = Column(String(255), nullable=True)
    stripe_checkout_session_id = Column(String(255), nullable=True, index=True)

    # Tracking
    confirmation_email_sent = Column(Boolean, default=False)
    subscription_provisioned = Column(Boolean, default=False)

    # How was this order completed? 'direct' (normal flow), 'webhook', 
    # 'reconciliation', 'support_manual'
    completed_via = Column(String(30), nullable=True)

    error_log = Column(Text, nullable=True)  # append-only log of what went wrong

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    completed_at = Column(DateTime, nullable=True)

    def append_error(self, msg: str):
        timestamp = datetime.utcnow().isoformat()
        entry = f"[{timestamp}] {msg}\n"
        self.error_log = (self.error_log or "") + entry

    def __repr__(self):
        return (
            f"<Order {self.id[:8]} | {self.user_email} | {self.status} | "
            f"${self.amount_cents/100:.2f} | PI:{self.stripe_payment_intent_id}>"
        )


class Subscription(Base):
    """Active subscription record. This is what gates product access."""
    __tablename__ = "subscriptions"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    order_id = Column(String(36), nullable=False, index=True)
    user_email = Column(String(255), nullable=False, index=True)
    plan = Column(String(50), nullable=False, default="yearly")
    status = Column(String(20), nullable=False, default="active")
    starts_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    expires_at = Column(DateTime, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)


class ReconciliationLog(Base):
    """Audit trail for every reconciliation run."""
    __tablename__ = "reconciliation_log"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    run_at = Column(DateTime, default=datetime.utcnow)
    charges_scanned = Column(Integer, default=0)
    orders_completed = Column(Integer, default=0)
    orphans_created = Column(Integer, default=0)
    errors = Column(Text, nullable=True)
    details = Column(Text, nullable=True)  # JSON blob of what was found/fixed


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


# ---------------------------------------------------------------------------
# Core Business Logic: Order Completion
# 
# This is deliberately extracted into a single function because THREE different
# code paths need to call it: the normal payment flow, the webhook handler,
# and the reconciliation job. If "completing an order" is scattered across
# the codebase, you'll fix a bug in one path and not the others.
# ---------------------------------------------------------------------------

def complete_order(
    db: Session,
    order: Order,
    stripe_payment_intent_id: str,
    stripe_customer_id: Optional[str],
    completed_via: str
) -> Tuple[bool, str]:
    """
    Idempotently complete an order: provision the subscription, send the 
    confirmation email, update the order record.

    Returns (success: bool, message: str).

    IDEMPOTENCY IS CRITICAL HERE. This function may be called multiple times
    for the same order (webhook fires, then reconciliation runs, etc.). 
    Every operation checks whether it's already been done before doing it.
    """
    # Guard: already completed? Nothing to do.
    if order.status == "completed":
        logger.info(f"Order {order.id} already completed. Skipping.")
        return True, "already_completed"

    try:
        # Update order with Stripe details
        order.stripe_payment_intent_id = stripe_payment_intent_id
        order.stripe_customer_id = stripe_customer_id
        order.status = "completed"
        order.completed_via = completed_via
        order.completed_at = datetime.utcnow()

        # Provision subscription (idempotent: check if one already exists)
        if not order.subscription_provisioned:
            existing_sub = db.query(Subscription).filter_by(
                order_id=order.id
            ).first()

            if not existing_sub:
                now = datetime.utcnow()
                subscription = Subscription(
                    order_id=order.id,
                    user_email=order.user_email,
                    plan="yearly",
                    status="active",
                    starts_at=now,
                    expires_at=now + timedelta(days=365),
                )
                db.add(subscription)
                logger.info(
                    f"Provisioned subscription for {order.user_email} "
                    f"(order {order.id})"
                )

            order.subscription_provisioned = True

        # Send confirmation email (idempotent: check the flag)
        if not order.confirmation_email_sent:
            email_sent = send_confirmation_email(order)
            if email_sent:
                order.confirmation_email_sent = True
            else:
                # Email failure is NOT a reason to fail the whole operation.
                # The user has access. We log it and the team can resend.
                order.append_error("Confirmation email failed to send.")
                logger.error(
                    f"Failed to send confirmation email for order {order.id}"
                )

        db.commit()
        logger.info(
            f"Order {order.id} completed via {completed_via} for "
            f"{order.user_email}"
        )
        return True, "completed"

    except Exception as e:
        db.rollback()
        logger.exception(f"Failed to complete order {order.id}: {e}")
        return False, str(e)


# ---------------------------------------------------------------------------
# Email Logic
# ---------------------------------------------------------------------------

CONFIRMATION_EMAIL_HTML = """\
<!DOCTYPE html>
<html>
<head><meta charset="utf-8"></head>
<body style="font-family: -apple-system, sans-serif; max-width: 560px; margin: 0 auto; padding: 40px 20px; color: #1a1a1a;">
    <h1 style="font-size: 22px;">You're all set.</h1>

    <p>Hi {{ name }},</p>

    <p>Your yearly subscription is now active. Here's your receipt:</p>

    <table style="width: 100%; border-collapse: collapse; margin: 24px 0;">
        <tr style="border-bottom: 1px solid #e0e0e0;">
            <td style="padding: 12px 0; color: #666;">Plan</td>
            <td style="padding: 12px 0; text-align: right;">Yearly</td>
        </tr>
        <tr style="border-bottom: 1px solid #e0e0e0;">
            <td style="padding: 12px 0; color: #666;">Amount</td>
            <td style="padding: 12px 0; text-align: right;">${{ amount }}</td>
        </tr>
        <tr style="border-bottom: 1px solid #e0e0e0;">
            <td style="padding: 12px 0; color: #666;">Order ID</td>
            <td style="padding: 12px 0; text-align: right; font-family: monospace; font-size: 13px;">{{ order_id }}</td>
        </tr>
        <tr>
            <td style="padding: 12px 0; color: #666;">Access until</td>
            <td style="padding: 12px 0; text-align: right;">{{ expires }}</td>
        </tr>
    </table>

    <a href="{{ app_url }}/login" 
       style="display: inline-block; background: #111; color: #fff; padding: 12px 32px; 
              text-decoration: none; border-radius: 6px; margin: 16px 0;">
        Log in to your account
    </a>

    <p style="margin-top: 32px; font-size: 14px; color: #666;">
        If you have any questions, reply to this email — it goes straight to a human.
    </p>
</body>
</html>
"""

ALERT_EMAIL_TO_SUPPORT = """\
AUTOMATED ALERT: Order completion issue

Order ID: {order_id}
Customer: {email}
Amount: ${amount}
Stripe PI: {payment_intent}
Issue: {issue}
Timestamp: {timestamp}

Action needed: {action}
"""


def send_confirmation_email(order: Order) -> bool:
    """
    Send the purchase confirmation email.

    Returns True if sent successfully, False otherwise.
    Never raises — email failure should not crash the payment flow.
    """
    try:
        expires = (datetime.utcnow() + timedelta(days=365)).strftime("%B %d, %Y")

        # Simple template rendering (avoid Jinja dependency for this example;
        # in production you'd use render_template)
        html = CONFIRMATION_EMAIL_HTML
        html = html.replace("{{ name }}", order.user_name or "there")
        html = html.replace("{{ amount }}", f"{order.amount_cents / 100:.2f}")
        html = html.replace("{{ order_id }}", order.id)
        html = html.replace("{{ expires }}", expires)
        html = html.replace("{{ app_url }}", APP_URL)

        msg = MIMEMultipart("alternative")
        msg["Subject"] = "Your subscription is active"
        msg["From"] = FROM_EMAIL
        msg["To"] = order.user_email
        msg["Reply-To"] = SUPPORT_EMAIL
        msg.attach(MIMEText(html, "html"))

        with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
            server.starttls()
            server.login(SMTP_USER, SMTP_PASS)
            server.send_message(msg)

        logger.info(f"Confirmation email sent to {order.user_email}")
        return True

    except Exception as e:
        logger.exception(f"Email send failed for {order.user_email}: {e}")
        return False


def send_support_alert(order: Order, issue: str, action: str):
    """Alert the support team about a payment issue that may need attention."""
    try:
        body = ALERT_EMAIL_TO_SUPPORT.format(
            order_id=order.id,
            email=order.user_email,
            amount=f"{order.amount_cents / 100:.2f}",
            payment_intent=order.stripe_payment_intent_id or "unknown",
            issue=issue,
            action=action,
            timestamp=datetime.utcnow().isoformat(),
        )

        msg = MIMEText(body)
        msg["Subject"] = f"[PAYMENT ALERT] {issue} — {order.user_email}"
        msg["From"] = FROM_EMAIL
        msg["To"] = SUPPORT_EMAIL

        with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
            server.starttls()
            server.login(SMTP_USER, SMTP_PASS)
            server.send_message(msg)

    except Exception as e:
        # If we can't even email the support team, log it loudly.
        # In production, this would also fire a PagerDuty/Slack alert.
        logger.critical(
            f"COULD NOT SEND SUPPORT ALERT for order {order.id}: {e}"
        )


# ---------------------------------------------------------------------------
# Flask Application
# ---------------------------------------------------------------------------

app = Flask(__name__)


def get_db() -> Session:
    """Get a database session for the current request."""
    if "db" not in g:
        g.db = SessionLocal()
    return g.db


@app.teardown_appcontext
def close_db(exception=None):
    db = g.pop("db", None)
    if db is not None:
        db.close()


# ---------------------------------------------------------------------------
# LAYER 1: The Payment Flow (with pre-charge order record)
# ---------------------------------------------------------------------------

@app.route("/api/checkout", methods=["POST"])
def create_checkout():
    """
    Step 1 of the payment flow: create a pending order and a Stripe 
    Checkout Session.

    The critical design decision: we write the order to our database BEFORE
    redirecting to Stripe. This means:
    - If Stripe charges the card and our server dies, we still have a record 
      that says "we expected a payment from this person."
    - The webhook handler can look up this pending order and complete it.
    - The reconciliation job can find it.

    The idempotency key ensures that if the user somehow double-submits,
    Stripe won't double-charge.
    """
    db = get_db()

    try:
        data = request.get_json()
        email = data.get("email", "").strip().lower()
        name = data.get("name", "").strip()

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

        # Generate a unique idempotency key for this purchase attempt.
        # We use this both as our own dedup key and pass it to Stripe.
        idempotency_key = f"order_{uuid.uuid4().hex}"

        # --- WRITE THE PENDING ORDER FIRST ---
        order = Order(
            idempotency_key=idempotency_key,
            user_email=email,
            user_name=name,
            status="pending",
            amount_cents=YEARLY_PRICE_CENTS,
            currency="usd",
        )
        db.add(order)
        db.commit()  # This MUST succeed before we talk to Stripe.

        logger.info(f"Created pending order {order.id} for {email}")

        # --- NOW CREATE THE STRIPE CHECKOUT SESSION ---
        # We embed our order ID in the metadata so the webhook can find it.
        checkout_session = stripe.checkout.Session.create(
            mode="payment",
            customer_email=email,
            line_items=[{
                "price": STRIPE_PRICE_ID,
                "quantity": 1,
            }],
            metadata={
                "order_id": order.id,
                "idempotency_key": idempotency_key,
            },
            success_url=f"{APP_URL}/checkout/success?order_id={order.id}",
            cancel_url=f"{APP_URL}/checkout/cancel?order_id={order.id}",
            # Stripe will hold the session and payment intent for us
        )

        # Save the checkout session ID back to our order
        order.stripe_checkout_session_id = checkout_session.id
        db.commit()

        return jsonify({
            "checkout_url": checkout_session.url,
            "order_id": order.id,
        })

    except stripe.error.StripeError as e:
        logger.exception(f"Stripe error during checkout creation: {e}")
        # The pending order exists but Stripe failed. Mark it.
        if 'order' in locals():
            order.append_error(f"Stripe checkout creation failed: {e}")
            order.status = "failed"
            db.commit()
        return jsonify({"error": "Payment system error. You have not been charged."}), 502

    except Exception as e:
        db.rollback()
        logger.exception(f"Unexpected error during checkout: {e}")
        return jsonify({"error": "Something went wrong. You have not been charged."}), 500


@app.route("/checkout/success")
def checkout_success():
    """
    The user lands here after Stripe redirects them back.

    IMPORTANT: This page is NOT the source of truth for whether the payment
    succeeded. The user can hit this URL directly, their browser can crash
    and reload it, etc. The WEBHOOK is the source of truth.

    However, if the webhook already fired (which is common — Stripe sends it
    fast), we can show the user their confirmation immediately.

    If the webhook hasn't fired yet, we show a "processing" state and the
    webhook will complete things in the background.
    """
    db = get_db()
    order_id = request.args.get("order_id")

    if not order_id:
        return render_template_string(ERROR_PAGE_TEMPLATE, **error_page_context(
            "missing_order"
        )), 400

    order = db.query(Order).filter_by(id=order_id).first()

    if not order:
        return render_template_string(ERROR_PAGE_TEMPLATE, **error_page_context(
            "missing_order"
        )), 404

    if order.status == "completed":
        # Happy path: webhook already fired, everything is done.
        return render_template_string(SUCCESS_PAGE_TEMPLATE,
            email=order.user_email,
            order_id=order.id,
            amount=f"{order.amount_cents / 100:.2f}",
            app_url=APP_URL,
        )

    if order.status == "pending":
        # Webhook hasn't fired yet. Try to complete it ourselves by checking
        # the Stripe session directly. This closes the gap for the scenario
        # described in the prompt.
        try:
            session = stripe.checkout.Session.retrieve(
                order.stripe_checkout_session_id,
                expand=["payment_intent"]
            )

            if session.payment_status == "paid":
                pi = session.payment_intent
                success, msg = complete_order(
                    db=db,
                    order=order,
                    stripe_payment_intent_id=pi.id if hasattr(pi, 'id') else str(pi),
                    stripe_customer_id=session.customer,
                    completed_via="success_page_sync",
                )
                if success:
                    return render_template_string(SUCCESS_PAGE_TEMPLATE,
                        email=order.user_email,
                        order_id=order.id,
                        amount=f"{order.amount_cents / 100:.2f}",
                        app_url=APP_URL,
                    )
        except Exception as e:
            logger.exception(f"Failed to sync-complete order {order.id}: {e}")
            order.append_error(f"Success page sync-complete failed: {e}")
            db.commit()

        # If we still couldn't complete it, show a "processing" message
        # rather than an error. The webhook will handle it.
        return render_template_string(PROCESSING_PAGE_TEMPLATE,
            email=order.user_email,
            support_email=SUPPORT_EMAIL,
        )

    # Order is in 'failed' or some other state
    return render_template_string(ERROR_PAGE_TEMPLATE, **error_page_context(
        "payment_failed"
    )), 400


# ---------------------------------------------------------------------------
# LAYER 2: Stripe Webhook Handler
#
# This is the most important safety net. Even if our server crashes during
# the payment flow, Stripe will POST to this endpoint when the charge 
# succeeds. Stripe retries webhooks for up to 3 days, so even if our server
# is down for hours, this will eventually fire.
# ---------------------------------------------------------------------------

@app.route("/webhooks/stripe", methods=["POST"])
def stripe_webhook():
    """
    Handle Stripe webhook events.

    Key event: checkout.session.completed — fires when payment succeeds.
    This is our primary async recovery mechanism.
    """
    payload = request.get_data(as_text=True)
    sig_header = request.headers.get("Stripe-Signature")

    # Verify the webhook signature. Never trust unverified webhooks.
    try:
        event = stripe.Webhook.construct_event(
            payload, sig_header, STRIPE_WEBHOOK_SECRET
        )
    except ValueError:
        logger.warning("Invalid webhook payload")
        return "Invalid payload", 400
    except stripe.error.SignatureVerificationError:
        logger.warning("Invalid webhook signature")
        return "Invalid signature", 400

    logger.info(f"Received Stripe webhook: {event['type']} ({event['id']})")

    if event["type"] == "checkout.session.completed":
        handle_checkout_completed(event["data"]["object"])
    elif event["type"] == "payment_intent.payment_failed":
        handle_payment_failed(event["data"]["object"])
    # Add other event types as needed

    # Always return 200 to Stripe. If we return an error, Stripe retries,
    # which is good for transient failures but bad if we return 500 on
    # every attempt (Stripe will keep retrying for days).
    return "OK", 200


def handle_checkout_completed(session_data: dict):
    """
    Handle a completed checkout session from Stripe.

    This is where we recover from the exact scenario in the prompt:
    the user paid, the server crashed, and now Stripe is telling us 
    (asynchronously) that the payment went through.
    """
    db = SessionLocal()

    try:
        order_id = session_data.get("metadata", {}).get("order_id")
        payment_intent_id = session_data.get("payment_intent")
        customer_id = session_data.get("customer")
        session_id = session_data.get("id")

        order = None

        # Try to find the order by ID from metadata (normal case)
        if order_id:
            order = db.query(Order).filter_by(id=order_id).first()

        # Fallback: find by checkout session ID
        if not order and session_id:
            order = db.query(Order).filter_by(
                stripe_checkout_session_id=session_id
            ).first()

        # Fallback: find by payment intent ID (in case of retry)
        if not order and payment_intent_id:
            order = db.query(Order).filter_by(
                stripe_payment_intent_id=payment_intent_id
            ).first()

        if not order:
            # CRITICAL: Stripe has a completed charge that we have NO record of.
            # This is the worst case — the pending order write also failed.
            # Create an orphan order so we have a record, and alert support.
            logger.critical(
                f"Stripe checkout {session_id} completed but NO matching order "
                f"found! Payment intent: {payment_intent_id}. "
                f"Creating orphan order."
            )

            customer_email = session_data.get("customer_email", "[email protected]")

            order = Order(
                idempotency_key=f"orphan_{session_id}",
                user_email=customer_email,
                status="pending",  # Will be completed below
                amount_cents=session_data.get("amount_total", YEARLY_PRICE_CENTS),
                currency=session_data.get("currency", "usd"),
                stripe_checkout_session_id=session_id,
            )
            order.append_error(
                f"ORPHAN: Created from webhook. No pre-existing order found. "
                f"Checkout session: {session_id}"
            )
            db.add(order)
            db.commit()

            # Alert the support team
            send_support_alert(
                order,
                issue="Orphan payment detected",
                action=(
                    "A Stripe charge completed but no matching order existed in "
                    "our database. An order has been auto-created and the "
                    "subscription provisioned. Please verify the customer has "
                    "proper access."
                )
            )

        # Now complete the order (idempotent — safe to call even if already done)
        success, msg = complete_order(
            db=db,
            order=order,
            stripe_payment_intent_id=payment_intent_id,
            stripe_customer_id=customer_id,
            completed_via="webhook",
        )

        if not success:
            logger.error(
                f"Webhook handler failed to complete order {order.id}: {msg}"
            )
            order.append_error(f"Webhook completion failed: {msg}")
            db.commit()

    except Exception as e:
        db.rollback()
        logger.exception(f"Webhook handler crashed: {e}")
        # Don't re-raise — we already returned 200 to Stripe.
        # The reconciliation job is the next safety net.
    finally:
        db.close()


def handle_payment_failed(payment_intent_data: dict):
    """Handle a failed payment intent."""
    db = SessionLocal()
    try:
        pi_id = payment_intent_data.get("id")
        order = db.query(Order).filter_by(
            stripe_payment_intent_id=pi_id
        ).first()

        if order and order.status == "pending":
            order.status = "failed"
            order.append_error(
                f"Payment failed. Stripe PI: {pi_id}. "
                f"Last error: {payment_intent_data.get('last_payment_error', {}).get('message', 'unknown')}"
            )
            db.commit()
            logger.info(f"Marked order {order.id} as failed")
    except Exception as e:
        db.rollback()
        logger.exception(f"Failed to handle payment_failed webhook: {e}")
    finally:
        db.close()


# ---------------------------------------------------------------------------
# LAYER 3: Reconciliation Job
#
# This runs on a schedule (every 15 minutes via cron, or however you like).
# It's the "belt AND suspenders" approach: even if webhooks fail (Stripe 
# endpoint misconfigured, our server was down for 3+ days, etc.), this will 
# eventually find and fix orphaned charges.
# ---------------------------------------------------------------------------

def run_reconciliation(lookback_hours: int = 24):
    """
    Compare recent Stripe charges against our order records.
    Find any charges that succeeded but don't have a completed order.
    Fix them.

    This is designed to be run as a cron job:
        python -c "from subscription_system import run_reconciliation; run_reconciliation()"

    Or via a management command, APScheduler, Celery beat, etc.
    """
    db = SessionLocal()
    log = ReconciliationLog()
    fixed_orders = []
    errors = []

    logger.info(f"Starting reconciliation (lookback: {lookback_hours}h)")

    try:
        # Fetch all successful charges from Stripe in the lookback window.
        # We use PaymentIntents because that's the modern Stripe API.
        cutoff = datetime.utcnow() - timedelta(hours=lookback_hours)
        cutoff_timestamp = int(cutoff.timestamp())

        charges_scanned = 0
        has_more = True
        starting_after = None

        while has_more:
            params = {
                "created": {"gte": cutoff_timestamp},
                "limit": 100,
            }
            if starting_after:
                params["starting_after"] = starting_after

            payment_intents = stripe.PaymentIntent.list(**params)

            for pi in payment_intents.data:
                charges_scanned += 1

                # Only care about successful charges
                if pi.status != "succeeded":
                    continue

                # Check if we have a completed order for this payment intent
                order = db.query(Order).filter_by(
                    stripe_payment_intent_id=pi.id
                ).first()

                if order and order.status == "completed":
                    continue  # All good

                # Also try to find by metadata
                if not order:
                    order_id = pi.metadata.get("order_id")
                    if order_id:
                        order = db.query(Order).filter_by(id=order_id).first()

                # Also check via checkout session if available
                if not order and hasattr(pi, 'metadata'):
                    idempotency_key = pi.metadata.get("idempotency_key")
                    if idempotency_key:
                        order = db.query(Order).filter_by(
                            idempotency_key=idempotency_key
                        ).first()

                if order and order.status == "completed":
                    # Found it by alternate lookup, PI just wasn't set. Update it.
                    if not order.stripe_payment_intent_id:
                        order.stripe_payment_intent_id = pi.id
                        db.commit()
                    continue

                if order and order.status == "pending":
                    # FOUND THE PROBLEM: paid in Stripe, pending in our DB.
                    logger.warning(
                        f"Reconciliation: Order {order.id} is pending but "
                        f"Stripe PI {pi.id} succeeded. Completing now."
                    )
                    success, msg = complete_order(
                        db=db,
                        order=order,
                        stripe_payment_intent_id=pi.id,
                        stripe_customer_id=pi.customer,
                        completed_via="reconciliation",
                    )
                    if success:
                        fixed_orders.append({
                            "order_id": order.id,
                            "email": order.user_email,
                            "pi": pi.id,
                            "resolution": "completed_pending_order",
                        })
                    else:
                        errors.append(f"Failed to complete order {order.id}: {msg}")

                elif not order:
                    # NO ORDER AT ALL for a successful charge. 
                    # This is the truly bad case. Create one.

                    # Try to get the email from the Stripe charge
                    customer_email = "[email protected]"
                    if pi.customer:
                        try:
                            customer = stripe.Customer.retrieve(pi.customer)
                            customer_email = customer.email or customer_email
                        except Exception:
                            pass
                    if pi.receipt_email:
                        customer_email = pi.receipt_email

                    # Only create if this payment matches our expected amount
                    # (avoid picking up unrelated Stripe charges)
                    if pi.amount != YEARLY_PRICE_CENTS:
                        logger.info(
                            f"Reconciliation: Skipping PI {pi.id} — amount "
                            f"{pi.amount} doesn't match expected {YEARLY_PRICE_CENTS}"
                        )
                        continue

                    logger.critical(
                        f"Reconciliation: Stripe PI {pi.id} ({customer_email}, "
                        f"${pi.amount/100:.2f}) has NO order in our DB. "
                        f"Creating orphan order."
                    )

                    orphan_order = Order(
                        idempotency_key=f"recon_orphan_{pi.id}",
                        user_email=customer_email,
                        status="pending",
                        amount_cents=pi.amount,
                        currency=pi.currency,
                        stripe_payment_intent_id=pi.id,
                        stripe_customer_id=pi.customer,
                    )
                    orphan_order.append_error(
                        f"ORPHAN: Created by reconciliation. No order existed "
                        f"for Stripe PI {pi.id}."
                    )
                    db.add(orphan_order)
                    db.commit()

                    success, msg = complete_order(
                        db=db,
                        order=orphan_order,
                        stripe_payment_intent_id=pi.id,
                        stripe_customer_id=pi.customer,
                        completed_via="reconciliation",
                    )

                    if success:
                        fixed_orders.append({
                            "order_id": orphan_order.id,
                            "email": customer_email,
                            "pi": pi.id,
                            "resolution": "created_orphan_and_completed",
                        })
                    else:
                        errors.append(
                            f"Failed to complete orphan order for PI {pi.id}: {msg}"
                        )

                    # Alert support
                    send_support_alert(
                        orphan_order,
                        issue="Orphan charge found in reconciliation",
                        action=(
                            f"A Stripe charge (PI: {pi.id}) for {customer_email} "
                            f"had no matching order. One has been created and the "
                            f"subscription provisioned automatically. Please verify."
                        ),
                    )

            has_more = payment_intents.has_more
            if payment_intents.data:
                starting_after = payment_intents.data[-1].id

        # Write the reconciliation log
        log.charges_scanned = charges_scanned
        log.orders_completed = len(fixed_orders)
        log.orphans_created = sum(
            1 for f in fixed_orders if f["resolution"] == "created_orphan_and_completed"
        )
        log.details = json.dumps(fixed_orders, indent=2) if fixed_orders else None
        log.errors = "\n".join(errors) if errors else None
        db.add(log)
        db.commit()

        logger.info(
            f"Reconciliation complete. Scanned: {charges_scanned}, "
            f"Fixed: {len(fixed_orders)}, Errors: {len(errors)}"
        )

        return {
            "scanned": charges_scanned,
            "fixed": len(fixed_orders),
            "errors": len(errors),
            "details": fixed_orders,
        }

    except Exception as e:
        db.rollback()
        logger.exception(f"Reconciliation crashed: {e}")
        raise
    finally:
        db.close()


# ---------------------------------------------------------------------------
# LAYER 4: Support Workflow Tools
#
# The support team is two people. They need to resolve "I was charged but 
# have no access" in under 2 minutes. These endpoints power an internal
# admin page or can be used directly via curl.
# ---------------------------------------------------------------------------

def require_admin_auth(f):
    """
    Simple admin authentication decorator.
    In production, use proper session-based auth or an admin framework.
    """
    @wraps(f)
    def decorated(*args, **kwargs):
        auth = request.headers.get("Authorization", "")
        expected = f"Bearer {os.environ.get('ADMIN_API_KEY', 'admin-dev-key')}"
        if not hmac.compare_digest(auth, expected):
            return jsonify({"error": "Unauthorized"}), 401
        return f(*args, **kwargs)
    return decorated


@app.route("/admin/support/lookup", methods=["GET"])
@require_admin_auth
def support_lookup():
    """
    Look up a customer by email or Stripe payment intent ID.
    This is the FIRST thing support does when a user says "I was charged 
    but don't have access."

    Usage:
        GET /admin/support/[email protected]
        GET /admin/support/lookup?payment_intent=pi_xxx
        GET /admin/support/lookup?stripe_charge=ch_xxx
    """
    db = get_db()
    email = request.args.get("email", "").strip().lower()
    pi_id = request.args.get("payment_intent", "").strip()

    results = {
        "orders_in_db": [],
        "subscriptions_in_db": [],
        "stripe_charges": [],
        "diagnosis": None,
        "recommended_action": None,
    }

    # Search our database
    orders = []
    if email:
        orders = db.query(Order).filter_by(user_email=email).order_by(
            Order.created_at.desc()
        ).all()
    elif pi_id:
        orders = db.query(Order).filter_by(
            stripe_payment_intent_id=pi_id
        ).all()

    for order in orders:
        results["orders_in_db"].append({
            "id": order.id,
            "email": order.user_email,
            "status": order.status,
            "amount": f"${order.amount_cents / 100:.2f}",
            "stripe_pi": order.stripe_payment_intent_id,
            "subscription_provisioned": order.subscription_provisioned,
            "confirmation_email_sent": order.confirmation_email_sent,
            "completed_via": order.completed_via,
            "created_at": order.created_at.isoformat() if order.created_at else None,
            "error_log": order.error_log,
        })

    # Search subscriptions
    if email:
        subs = db.query(Subscription).filter_by(user_email=email).order_by(
            Subscription.created_at.desc()
        ).all()
        for sub in subs:
            results["subscriptions_in_db"].append({
                "id": sub.id,
                "order_id": sub.order_id,
                "status": sub.status,
                "plan": sub.plan,
                "starts_at": sub.starts_at.isoformat(),
                "expires_at": sub.expires_at.isoformat(),
            })

    # Search Stripe directly
    try:
        if email:
            # Find Stripe customers by email
            customers = stripe.Customer.list(email=email, limit=5)
            for customer in customers.data:
                pis = stripe.PaymentIntent.list(
                    customer=customer.id, limit=10
                )
                for pi in pis.data:
                    results["stripe_charges"].append({
                        "payment_intent": pi.id,
                        "amount": f"${pi.amount / 100:.2f}",
                        "status": pi.status,
                        "created": datetime.fromtimestamp(pi.created).isoformat(),
                        "customer_id": pi.customer,
                    })
        elif pi_id:
            pi = stripe.PaymentIntent.retrieve(pi_id)
            results["stripe_charges"].append({
                "payment_intent": pi.id,
                "amount": f"${pi.amount / 100:.2f}",
                "status": pi.status,
                "created": datetime.fromtimestamp(pi.created).isoformat(),
                "customer_id": pi.customer,
            })
    except stripe.error.StripeError as e:
        results["stripe_charges"].append({"error": str(e)})

    # --- DIAGNOSIS ---
    # Automatically figure out what's wrong and suggest what to do.
    has_completed_order = any(
        o["status"] == "completed" for o in results["orders_in_db"]
    )
    has_active_sub = any(
        s["status"] == "active" for s in results["subscriptions_in_db"]
    )
    has_successful_stripe_charge = any(
        c.get("status") == "succeeded" for c in results["stripe_charges"]
    )
    has_pending_order = any(
        o["status"] == "pending" for o in results["orders_in_db"]
    )

    if has_active_sub and has_completed_order:
        results["diagnosis"] = (
            "HEALTHY: Order completed and subscription active. "
            "If user can't access, it's a login/account issue, not a payment issue."
        )
        results["recommended_action"] = "Help user log in. Check their account status."

    elif has_successful_stripe_charge and not has_completed_order:
        results["diagnosis"] = (
            "PAYMENT GAP: Stripe charge succeeded but no completed order in our DB. "
            "This is the crash scenario."
        )
        results["recommended_action"] = (
            "Use POST /admin/support/resolve to provision their subscription. "
            "This will create/complete the order, provision access, and send "
            "the confirmation email."
        )

    elif has_pending_order and has_successful_stripe_charge:
        results["diagnosis"] = (
            "STUCK PENDING: Order exists as pending, Stripe charge succeeded. "
            "Server likely crashed before completion."
        )
        results["recommended_action"] = (
            "Use POST /admin/support/resolve with the order_id to complete it."
        )

    elif not results["orders_in_db"] and not results["stripe_charges"]:
        results["diagnosis"] = (
            "NO RECORDS: Nothing in our DB or Stripe for this email/PI. "
            "User may have used a different email, or the charge is from "
            "a different service."
        )
        results["recommended_action"] = (
            "Ask the user for the last 4 digits of their card and exact charge "
            "amount/date. Search Stripe dashboard manually."
        )

    elif has_successful_stripe_charge and has_completed_order and not has_active_sub:
        results["diagnosis"] = (
            "PROVISIONING GAP: Order is marked completed but no subscription exists. "
            "The complete_order function partially failed."
        )
        results["recommended_action"] = (
            "Use POST /admin/support/resolve to re-provision."
        )

    else:
        results["diagnosis"] = "UNCLEAR: Review the data above manually."
        results["recommended_action"] = "Check Stripe dashboard and order details."

    return jsonify(results)


@app.route("/admin/support/resolve", methods=["POST"])
@require_admin_auth
def support_resolve():
    """
    One-click resolution for "charged but no access" issues.

    This is the nuclear option that support can use: given evidence that the
    user was charged, complete their order and provision access.

    Request body:
        {
            "email": "[email protected]",
            "payment_intent_id": "pi_xxx",  // from Stripe lookup
            "order_id": "xxx"               // optional, from our DB lookup
        }

    This is designed for the two-person support team to use when a user writes in
    with "I was charged $249 but I have no account." Support does the lookup,
    confirms the Stripe charge, and hits this endpoint.
    """
    db = get_db()
    data = request.get_json()

    email = data.get("email", "").strip().lower()
    payment_intent_id = data.get("payment_intent_id", "").strip()
    order_id = data.get("order_id", "").strip()
    support_agent = data.get("agent", "unknown")

    if not email or not payment_intent_id:
        return jsonify({
            "error": "Both 'email' and 'payment_intent_id' are required."
        }), 400

    # Verify the charge exists in Stripe before doing anything
    try:
        pi = stripe.PaymentIntent.retrieve(payment_intent_id)
        if pi.status != "succeeded":
            return jsonify({
                "error": f"Payment intent {payment_intent_id} has status "
                         f"'{pi.status}', not 'succeeded'. Cannot resolve."
            }), 400
    except stripe.error.StripeError as e:
        return jsonify({
            "error": f"Could not verify Stripe payment: {e}"
        }), 400

    # Find or create the order
    order = None
    if order_id:
        order = db.query(Order).filter_by(id=order_id).first()

    if not order:
        order = db.query(Order).filter_by(
            stripe_payment_intent_id=payment_intent_id
        ).first()

    if not order:
        # Check for pending orders for this email
        order = db.query(Order).filter_by(
            user_email=email, status="pending"
        ).order_by(Order.created_at.desc()).first()

    if not order:
        # No order at all — create one from the Stripe data
        order = Order(
            idempotency_key=f"support_resolve_{payment_intent_id}",
            user_email=email,
            status="pending",
            amount_cents=pi.amount,
            currency=pi.currency,
            stripe_payment_intent_id=payment_intent_id,
            stripe_customer_id=pi.customer,
        )
        order.append_error(
            f"Created by support agent '{support_agent}' during manual resolution. "
            f"Original order record was missing."
        )
        db.add(order)
        db.commit()
        logger.info(f"Support created order {order.id} for {email}")

    # Complete the order
    success, msg = complete_order(
        db=db,
        order=order,
        stripe_payment_intent_id=payment_intent_id,
        stripe_customer_id=pi.customer,
        completed_via=f"support_manual ({support_agent})",
    )

    if success:
        # Fetch the subscription to include in the response
        sub = db.query(Subscription).filter_by(order_id=order.id).first()

        return jsonify({
            "status": "resolved",
            "order_id": order.id,
            "email": order.user_email,
            "subscription_active": sub is not None,
            "subscription_expires": sub.expires_at.isoformat() if sub else None,
            "confirmation_email_sent": order.confirmation_email_sent,
            "message": (
                f"Order completed. Subscription provisioned for {email}. "
                f"Confirmation email {'sent' if order.confirmation_email_sent else 'FAILED — resend manually'}."
            ),
        })
    else:
        return jsonify({
            "status": "error",
            "error": msg,
            "order_id": order.id,
        }), 500


@app.route("/admin/support/resend-email", methods=["POST"])
@require_admin_auth
def support_resend_email():
    """Resend the confirmation email for an order."""
    db = get_db()
    data = request.get_json()
    order_id = data.get("order_id")

    order = db.query(Order).filter_by(id=order_id).first()
    if not order:
        return jsonify({"error": "Order not found"}), 404

    sent = send_confirmation_email(order)
    if sent:
        order.confirmation_email_sent = True
        order.append_error("Confirmation email re-sent by support.")
        db.commit()
        return jsonify({"status": "sent"})
    else:
        return jsonify({"error": "Email send failed. Check SMTP config."}), 500


# ---------------------------------------------------------------------------
# User-Facing Pages
#
# These are what the user actually sees. The error page is the most important
# one — it's what they see when something goes wrong, and it needs to:
# 1. Acknowledge the problem clearly
# 2. Tell them their money is safe
# 3. Give them a clear next step
# 4. Not be a generic "500 Internal Server Error"
# ---------------------------------------------------------------------------

ERROR_PAGE_TEMPLATE = """\
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>{{ title }}</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
            background: #fafafa;
            color: #1a1a1a;
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            max-width: 520px;
            background: #fff;
            border-radius: 12px;
            padding: 48px 40px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        }
        h1 { font-size: 22px; margin-bottom: 16px; }
        p { line-height: 1.6; color: #444; margin-bottom: 12px; }
        .highlight {
            background: #f0f7ff;
            border-left: 3px solid #2563eb;
            padding: 16px 20px;
            border-radius: 0 8px 8px 0;
            margin: 20px 0;
        }
        .highlight p { margin: 0; color: #1e40af; }
        a.btn {
            display: inline-block;
            background: #111;
            color: #fff;
            padding: 12px 28px;
            text-decoration: none;
            border-radius: 6px;
            margin-top: 20px;
            font-size: 15px;
        }
        a.btn:hover { background: #333; }
        .support-link { font-size: 14px; color: #666; margin-top: 16px; }
        .support-link a { color: #2563eb; }
    </style>
</head>
<body>
    <div class="container">
        <h1>{{ heading }}</h1>
        <p>{{ message }}</p>

        {% if show_payment_assurance %}
        <div class="highlight">
            <p><strong>If you were charged</strong>, your payment is safe. 
            Our system will automatically detect and process it, or you can 
            contact us and we'll sort it out immediately.</p>
        </div>
        {% endif %}

        <a class="btn" href="mailto:{{ support_email }}?subject=Payment%20Issue&body=Hi%2C%20I%20was%20charged%20but%20didn%27t%20receive%20access.%20My%20email%20is%20{{ user_email_hint }}">
            Email Support
        </a>

        <p class="support-link">
            Or email <a href="mailto:{{ support_email }}">{{ support_email }}</a> directly.
            We typically respond within an hour.
        </p>
    </div>
</body>
</html>
"""

SUCCESS_PAGE_TEMPLATE = """\
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>You're all set</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
            background: #fafafa;
            color: #1a1a1a;
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            max-width: 520px;
            background: #fff;
            border-radius: 12px;
            padding: 48px 40px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.08);
            text-align: center;
        }
        .check { font-size: 48px; margin-bottom: 20px; }
        h1 { font-size: 24px; margin-bottom: 12px; }
        p { line-height: 1.6; color: #444; margin-bottom: 8px; }
        .details { font-size: 14px; color: #888; margin: 20px 0; }
        a.btn {
            display: inline-block;
            background: #111;
            color: #fff;
            padding: 14px 36px;
            text-decoration: none;
            border-radius: 6px;
            margin-top: 24px;
            font-size: 16px;
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="check">✓</div>
        <h1>You're all set.</h1>
        <p>Your yearly subscription is now active.</p>
        <p>A confirmation email has been sent to <strong>{{ email }}</strong>.</p>
        <p class="details">Order {{ order_id }} · ${{ amount }}</p>
        <a class="btn" href="{{ app_url }}/login">Go to your account →</a>
    </div>
</body>
</html>
"""

PROCESSING_PAGE_TEMPLATE = """\
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Processing your payment</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
            background: #fafafa;
            color: #1a1a1a;
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            max-width: 520px;
            background: #fff;
            border-radius: 12px;
            padding: 48px 40px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        }
        h1 { font-size: 22px; margin-bottom: 16px; }
        p { line-height: 1.6; color: #444; margin-bottom: 12px; }
        .spinner {
            width: 32px; height: 32px;
            border: 3px solid #e0e0e0;
            border-top-color: #111;
            border-radius: 50%;
            animation: spin 0.8s linear infinite;
            margin-bottom: 24px;
        }
        @keyframes spin { to { transform: rotate(360deg); } }
    </style>
    <script>
        // Auto-refresh every 5 seconds. Once the webhook fires and completes
        // the order, the /checkout/success endpoint will show the success page.
        setTimeout(function() { location.reload(); }, 5000);
    </script>
</head>
<body>
    <div class="container">
        <div class="spinner"></div>
        <h1>Processing your payment...</h1>
        <p>This usually takes just a moment. This page will update automatically.</p>
        <p>If this takes more than a minute, don't worry — your payment is being 
        processed and you'll receive a confirmation email at 
        <strong>{{ email }}</strong>.</p>
        <p style="margin-top: 20px; font-size: 14px; color: #888;">
            If you need help: <a href="mailto:{{ support_email }}" style="color: #2563eb;">{{ support_email }}</a>
        </p>
    </div>
</body>
</html>
"""


def error_page_context(error_type: str) -> dict:
    """
    Generate context for the error page template based on the error type.

    The tone matters here. The user just got charged $249 and sees an error.
    They need to know: (1) we know something went wrong, (2) their money is 
    safe, (3) there's a human they can reach. No jargon, no "500 error."
    """
    contexts = {
        "missing_order": {
            "title": "Something went wrong",
            "heading": "We can't find your order.",
            "message": (
                "It looks like something went wrong during checkout. "
                "This can happen if there was a brief connection issue."
            ),
            "show_payment_assurance": True,
            "support_email": SUPPORT_EMAIL,
            "user_email_hint": "",
        },
        "payment_failed": {
            "title": "Payment issue",
            "heading": "There was a problem with your payment.",
            "message": (
                "Your payment couldn't be processed. You have not been charged. "
                "Please try again or use a different payment method."
            ),
            "show_payment_assurance": False,
            "support_email": SUPPORT_EMAIL,
            "user_email_hint": "",
        },
        "server_error": {
            "title": "Something went wrong",
            "heading": "Something went wrong on our end.",
            "message": (
                "We're sorry — our server encountered an issue while processing "
                "your request. Our team has been notified."
            ),
            "show_payment_assurance": True,
            "support_email": SUPPORT_EMAIL,
            "user_email_hint": "",
        },
    }
    return contexts.get(error_type, contexts["server_error"])


# Global error handlers so the user never sees a raw stack trace
@app.errorhandler(500)
def handle_500(e):
    logger.exception(f"Unhandled 500 error: {e}")
    return render_template_string(
        ERROR_PAGE_TEMPLATE, **error_page_context("server_error")
    ), 500


@app.errorhandler(502)
def handle_502(e):
    return render_template_string(
        ERROR_PAGE_TEMPLATE, **error_page_context("server_error")
    ), 502


# ---------------------------------------------------------------------------
# Reconciliation Runner (called via cron or management command)
# ---------------------------------------------------------------------------

def setup_cron_reconciliation():
    """
    Example of setting up periodic reconciliation using APScheduler.

    In practice, for a two-person team, a simple cron job is fine:
        */15 * * * * cd /app && python -c "from subscription_system import run_reconciliation; run_reconciliation()"

    Or use Heroku Scheduler, Railway cron, a systemd timer, etc.
    """
    try:
        from apscheduler.schedulers.background import BackgroundScheduler

        scheduler = BackgroundScheduler()

        # Run every 15 minutes, looking back 2 hours.
        # The overlap is intentional — better to check the same charges twice
        # (the function is idempotent) than to miss one.
        scheduler.add_job(
            run_reconciliation,
            'interval',
            minutes=15,
            kwargs={"lookback_hours": 2},
            id="reconciliation",
            name="Payment Reconciliation",
        )

        # Run a broader sweep once daily (catches anything the 15-min job missed)
        scheduler.add_job(
            run_reconciliation,
            'cron',
            hour=3,  # 3 AM UTC
            kwargs={"lookback_hours": 48},
            id="daily_reconciliation",
            name="Daily Payment Reconciliation",
        )

        scheduler.start()
        logger.info("Reconciliation scheduler started")

    except ImportError:
        logger.warning(
            "APScheduler not installed. Use cron for reconciliation instead."
        )


# ---------------------------------------------------------------------------
# CLI Support Tool
#
# For the two-person support team, sometimes it's fastest to just run a
# command in the terminal rather than navigating an admin UI.
# ---------------------------------------------------------------------------

def cli_lookup(email: str = None, payment_intent: str = None):
    """
    Command-line support lookup tool.

    Usage:
        python -c "from subscription_system import cli_lookup; cli_lookup(email='[email protected]')"
    """
    import requests as req

    admin_key = os.environ.get("ADMIN_API_KEY", "admin-dev-key")
    params = {}
    if email:
        params["email"] = email
    if payment_intent:
        params["payment_intent"] = payment_intent

    resp = req.get(
        f"{APP_URL}/admin/support/lookup",
        params=params,
        headers={"Authorization": f"Bearer {admin_key}"},
    )

    data = resp.json()

    print("\n" + "=" * 60)
    print("SUPPORT LOOKUP RESULTS")
    print("=" * 60)

    print(f"\n📋 DIAGNOSIS: {data.get('diagnosis', 'Unknown')}")
    print(f"🔧 ACTION: {data.get('recommended_action', 'None')}")

    print(f"\n--- Orders in DB ({len(data.get('orders_in_db', []))}) ---")
    for o in data.get("orders_in_db", []):
        print(f"  Order {o['id'][:12]}... | {o['status']} | {o['amount']} | "
              f"Sub: {o['subscription_provisioned']} | Email sent: {o['confirmation_email_sent']}")

    print(f"\n--- Subscriptions ({len(data.get('subscriptions_in_db', []))}) ---")
    for s in data.get("subscriptions_in_db", []):
        print(f"  Sub {s['id'][:12]}... | {s['status']} | {s['plan']} | "
              f"Expires: {s['expires_at']}")

    print(f"\n--- Stripe Charges ({len(data.get('stripe_charges', []))}) ---")
    for c in data.get("stripe_charges", []):
        if "error" in c:
            print(f"  Error: {c['error']}")
        else:
            print(f"  PI {c['payment_intent']} | {c['status']} | {c['amount']} | "
                  f"{c['created']}")

    print()


def cli_resolve(email: str, payment_intent_id: str, agent: str = "cli"):
    """
    Command-line resolution tool.

    Usage:
        python -c "from subscription_system import cli_resolve; cli_resolve('[email protected]', 'pi_xxx', 'alice')"
    """
    import requests as req

    admin_key = os.environ.get("ADMIN_API_KEY", "admin-dev-key")

    resp = req.post(
        f"{APP_URL}/admin/support/resolve",
        json={
            "email": email,
            "payment_intent_id": payment_intent_id,
            "agent": agent,
        },
        headers={"Authorization": f"Bearer {admin_key}"},
    )

    data = resp.json()

    if resp.status_code == 200:
        print(f"\n✅ RESOLVED")
        print(f"   Order: {data['order_id']}")
        print(f"   Email: {data['email']}")
        print(f"   Subscription active: {data['subscription_active']}")
        print(f"   Expires: {data.get('subscription_expires', 'N/A')}")
        print(f"   Confirmation email: {'sent' if data['confirmation_email_sent'] else 'FAILED'}")
        print(f"   {data['message']}")
    else:
        print(f"\n❌ FAILED: {data.get('error', 'Unknown error')}")

    print()


# ---------------------------------------------------------------------------
# Application startup
# ---------------------------------------------------------------------------

if __name__ == "__main__":
    import sys

    if len(sys.argv) > 1 and sys.argv[1] == "reconcile":
        # Run as: python subscription_system.py reconcile [hours]
        hours = int(sys.argv[2]) if len(sys.argv) > 2 else 24
        result = run_reconciliation(lookback_hours=hours)
        print(f"Reconciliation complete: {result}")

    elif len(sys.argv) > 1 and sys.argv[1] == "lookup":
        # Run as: python subscription_system.py lookup [email protected]
        cli_lookup(email=sys.argv[2] if len(sys.argv) > 2 else None)

    elif len(sys.argv) > 1 and sys.argv[1] == "resolve":
        # Run as: python subscription_system.py resolve [email protected] pi_xxx agent_name
        if len(sys.argv) < 4:
            print("Usage: python subscription_system.py resolve <email> <payment_intent_id> [agent]")
            sys.exit(1)
        cli_resolve(
            email=sys.argv[2],
            payment_intent_id=sys.argv[3],
            agent=sys.argv[4] if len(sys.argv) > 4 else "cli",
        )

    else:
        # Run the web server
        setup_cron_reconciliation()
        app.run(host="0.0.0.0", port=int(os.environ.get("PORT", 8000)), debug=False)

How the layers work together for the prompt's exact scenario

What happened: User paid $249. Server crashed. No order record, no email, no access. User sees blank page, sees charge on bank statement.

Layer 1 (prevention): The pending order was (probably) written before the charge. If the crash was truly catastrophic and even the pending order didn't make it, layers 2 and 3 still catch it.

Layer 2 (webhook): Within seconds to minutes, Stripe sends a checkout.session.completed webhook. handle_checkout_completed fires, finds the pending order (or creates an orphan if none exists), calls complete_order, provisions the subscription, sends the confirmation email. The user gets the email and can log in, without ever contacting support.

Layer 3 (reconciliation): If the webhook also failed (endpoint misconfigured, server was down when Stripe retried, etc.), the reconciliation job that runs every 15 minutes will find the Stripe charge, see there's no matching completed order, and fix it. The user gets their email, possibly 15 minutes late.

Layer 4 (support): If the user contacts support before layers 2 or 3 kick in, the support person runs the lookup, sees the diagnosis ("PAYMENT GAP: Stripe charge succeeded but no completed order"), and hits resolve. Takes about 90 seconds. The user gets access and a confirmation email immediately.

The error page: Instead of a blank page or stack trace, the user sees a clear message acknowledging something went wrong, assuring them their payment is safe, with a direct email link to support. The processing page auto-refreshes every 5 seconds, so if the webhook completes the order while they're still looking at it, it converts to the success page.

Three things worth noting about the design:

1. complete_order is one function called by all paths. This is the most important structural decision. If completion logic is duplicated across the normal flow, the webhook, the reconciliation job, and the support tool, bugs will live in the gaps between them.

2. Everything is idempotent. Every function checks whether its work has already been done before doing it. This means it's always safe to run any recovery path, even if another path already fixed the problem.

3. The reconciliation job compares Stripe's records to ours, not the other way around. Stripe is the source of truth for "money moved." Our database is the source of truth for "access granted." The reconciliation job's only question is: "Is there money that moved without access being granted?"