from sqlalchemy import Column, String, DateTime, Integer, Boolean, Index
from sqlalchemy.sql import func
import uuid

class WebhookEvent(Base):
    __tablename__ = "webhook_events"

    # Primary: the Stripe event ID itself
    stripe_event_id = Column(String(255), primary_key=True)

    # Idempotency key (same as stripe_event_id, but kept separate for clarity)
    idempotency_key = Column(String(255), unique=True, nullable=False, index=True)

    # Webhook metadata
    event_type = Column(String(100), nullable=False)
    event_data = Column(JSON, nullable=False)  # Store the full event JSON

    # Status tracking
    processed = Column(Boolean, default=False)
    status = Column(String(50), default="pending")  # pending, processed, failed
    error_message = Column(Text, nullable=True)

    # Timing
    created_at = Column(DateTime, server_default=func.now())
    processed_at = Column(DateTime, nullable=True)

    # Metadata for debugging
    attempt_count = Column(Integer, default=1)

    __table_args__ = (
        Index("idx_event_type_processed", "event_type", "processed"),
    )

from fastapi import APIRouter, Request, HTTPException, Depends
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session
import json
import logging

router = APIRouter()
logger = logging.getLogger(__name__)

@router.post("/webhooks/stripe")
async def handle_stripe_webhook(
    request: Request,
    db: Session = Depends(get_db)
):
    """
    Handle Stripe webhooks with guaranteed idempotency.
    This endpoint is safe to call multiple times with the same event.
    """

    # 1. Verify the webhook signature (never skip this)
    payload = await request.body()
    signature = request.headers.get("stripe-signature")

    try:
        event = stripe.Webhook.construct_event(
            payload, signature, STRIPE_WEBHOOK_SECRET
        )
    except ValueError as e:
        logger.error(f"Invalid webhook signature: {e}")
        raise HTTPException(status_code=400, detail="Invalid signature")

    event_id = event["id"]
    event_type = event["type"]

    # 2. Check if we've already processed this event
    existing = db.query(WebhookEvent).filter(
        WebhookEvent.stripe_event_id == event_id
    ).first()

    if existing:
        if existing.processed:
            # Already processed successfully—return 200 to make Stripe stop retrying
            logger.info(f"Webhook {event_id} already processed, returning 200")
            return {"status": "already_processed"}

        if existing.status == "failed":
            # Previously failed—decide if you want to retry or skip
            # I prefer to retry failed webhooks on subsequent attempts
            logger.info(f"Webhook {event_id} previously failed, retrying")
            existing.attempt_count += 1
        else:
            # Currently processing—return 200 immediately
            logger.info(f"Webhook {event_id} currently processing")
            return {"status": "processing"}

    # 3. Create a new webhook record with idempotency guarantee
    webhook_record = WebhookEvent(
        stripe_event_id=event_id,
        idempotency_key=event_id,  # Stripe event ID IS the idempotency key
        event_type=event_type,
        event_data=event,
        status="pending"
    )

    try:
        db.add(webhook_record)
        db.commit()
    except IntegrityError:
        # Someone beat us to it (race condition, but safe)
        db.rollback()
        existing = db.query(WebhookEvent).filter(
            WebhookEvent.stripe_event_id == event_id
        ).first()
        if existing and existing.processed:
            return {"status": "already_processed"}
        raise

    # 4. Process the webhook event
    try:
        if event_type == "customer.subscription.updated":
            await handle_subscription_updated(event, db)
        elif event_type == "invoice.payment_succeeded":
            await handle_payment_succeeded(event, db)
        elif event_type == "charge.refunded":
            await handle_refund(event, db)
        else:
            logger.warning(f"Unhandled webhook type: {event_type}")

        # 5. Mark as processed
        webhook_record.processed = True
        webhook_record.status = "processed"
        webhook_record.processed_at = datetime.utcnow()
        db.commit()

        logger.info(f"Webhook {event_id} processed successfully")
        return {"status": "processed"}

    except Exception as e:
        # Mark as failed but don't crash
        logger.error(f"Webhook {event_id} processing failed: {str(e)}", exc_info=True)
        webhook_record.status = "failed"
        webhook_record.error_message = str(e)
        webhook_record.attempt_count += 1
        db.commit()

        # Return 200 anyway—let your monitoring system catch this
        # Returning 5xx will make Stripe retry, which might help
        # But it might also pile up load. I prefer to handle retries manually.
        return {"status": "failed", "error": str(e)}

async def handle_payment_succeeded(event: dict, db: Session):
    """
    Process a successful payment. 

    Idempotency is enforced at two levels:
    1. WebhookEvent table prevents duplicate processing
    2. Invoice table prevents double-crediting accounts
    """
    invoice_id = event["data"]["object"]["id"]
    customer_id = event["data"]["object"]["customer"]
    amount = event["data"]["object"]["amount_paid"]  # in cents

    # Check if this invoice was already credited
    existing_credit = db.query(AccountCredit).filter(
        AccountCredit.stripe_invoice_id == invoice_id
    ).first()

    if existing_credit:
        logger.info(f"Invoice {invoice_id} already credited")
        return

    # Get the tenant/account
    account = db.query(Account).filter(
        Account.stripe_customer_id == customer_id
    ).first()

    if not account:
        raise ValueError(f"No account found for customer {customer_id}")

    # Create the credit record with a UNIQUE constraint on stripe_invoice_id
    credit = AccountCredit(
        account_id=account.id,
        stripe_invoice_id=invoice_id,
        amount=amount,
        created_at=datetime.utcnow()
    )

    db.add(credit)
    db.commit()  # Will fail if duplicate invoice_id, which is good

    logger.info(f"Credited account {account.id} for invoice {invoice_id}")

python
# Use FOR UPDATE to lock the row during processing
webhook_record = db.