Datatemplate voor betalingsverwerking

import pandas as pd
import numpy as np
from datetime import timedelta

# CONFIGURATION
INPUT_FILE = 'payments.csv'
OUTPUT_FILE = 'stripe_event_log.csv'

# Load Data
try:
    df = pd.read_csv(INPUT_FILE)
    print(f"Loaded {len(df)} rows from {INPUT_FILE}")
except FileNotFoundError:
    print(f"Error: {INPUT_FILE} not found. Please export from Stripe Dashboard first.")
    exit()

# Standardize Date Columns (Ensure UTC)
date_cols = ['Created (UTC)', 'Captured (UTC)', 'Refunded (UTC)', 
             'Dispute Date (UTC)', 'Payout Date (UTC)']

# Helper to parse dates safely
def parse_date(date_str):
    return pd.to_datetime(date_str, errors='coerce')

for col in date_cols:
    if col in df.columns:
        df[col] = parse_date(df[col])

# EVENT LOG LIST
event_log_rows = []

def add_event(row, event_name, timestamp_col, extra_attrs={}):
    if pd.notna(row.get(timestamp_col)):
        base_event = {
            'PaymentIntentId': row.get('id'),
            'EventName': event_name,
            'EventTimestamp': row.get(timestamp_col),
            'SourceSystem': 'Stripe',
            'PaymentAmount': row.get('Amount'),
            'PaymentCurrency': row.get('Currency'),
            'PaymentStatus': row.get('Status'),
            'CustomerId': row.get('Customer ID'),
            'PaymentMethodType': row.get('Card Brand')  # or 'Payment Method'
        }
        base_event.update(extra_attrs)
        event_log_rows.append(base_event)

# PROCESS ROWS
for index, row in df.iterrows():
    created_ts = row.get('Created (UTC)')
    
    # 1. Payment Request Created
    add_event(row, 'Payment Request Created', 'Created (UTC)')

    # 2. Payment Details Validated (Inferred: Immediate upon creation for successful intents)
    # Note: Using Created + 1 second to sequence it after creation
    if pd.notna(created_ts):
        event_log_rows.append({
            'PaymentIntentId': row.get('id'),
            'EventName': 'Payment Details Validated',
            'EventTimestamp': created_ts + timedelta(seconds=1),
            'SourceSystem': 'Stripe',
            'PaymentAmount': row.get('Amount'),
            'PaymentCurrency': row.get('Currency')
        })

    # 3. Payment Review Needed (Requires Custom Column 'Radar Risk Level' or similar)
    if row.get('Radar Risk Level') == 'elevated':
        # Timestamp inferred as Created + 2s if specific review timestamp missing
        event_log_rows.append({
            'PaymentIntentId': row.get('id'),
            'EventName': 'Payment Review Needed',
            'EventTimestamp': created_ts + timedelta(seconds=2),
            'SourceSystem': 'Stripe'
        })

    # 4. Payment Review Approved (Requires Custom Column 'Review Status' = 'approved')
    if row.get('Radar Risk Level') == 'elevated' and row.get('Status') != 'failed':
         # Inferred timestamp
         event_log_rows.append({
            'PaymentIntentId': row.get('id'),
            'EventName': 'Payment Review Approved',
            'EventTimestamp': created_ts + timedelta(seconds=5),
            'SourceSystem': 'Stripe'
        })

    # 5. Payment Authentication Requested (Requires 3DS columns, often inferred from Status='requires_action')
    if row.get('Status') == 'requires_action':
         add_event(row, 'Payment Authentication Requested', 'Created (UTC)')

    # 6. Payment Authorized
    if pd.notna(row.get('Captured (UTC)')):
        add_event(row, 'Payment Authorized', 'Captured (UTC)')
    elif row.get('Status') == 'succeeded':
        # Fallback if Captured date is missing but status is success
        add_event(row, 'Payment Authorized', 'Created (UTC)')

    # 7. Payment Failed
    if row.get('Status') == 'failed':
        add_event(row, 'Payment Failed', 'Created (UTC)', {'FailureCode': row.get('Failure Code')})

    # 8. Payment Error Resolved (Inferred: If previously failed but now Succeeded - requires history export)
    # Limitation: Hard to detect in flat 'current state' export.

    # 9. Payment Notification Sent (Inferred: Receipt sent)
    if pd.notna(created_ts) and row.get('Receipt Sent') == 'Yes':
        add_event(row, 'Payment Notification Sent', 'Created (UTC)')

    # 10. Funds Available (Derived from Available On column)
    add_event(row, 'Funds Available', 'Available On (UTC)')

    # 11. Payment Included in Payout (Derived from Payout Date)
    # This marks the grouping event
    add_event(row, 'Payment Included in Payout', 'Payout Date (UTC)', {'PayoutId': row.get('Payout ID')})

    # 12. Funds Transferred (Derived: Same as Payout Date for simplified export)
    add_event(row, 'Funds Transferred', 'Payout Date (UTC)', {'PayoutId': row.get('Payout ID')})

    # 13. Dispute Opened
    add_event(row, 'Dispute Opened', 'Dispute Date (UTC)')

    # 14. Dispute Closed (Requires Dispute Status or Evidence Due Date)
    if pd.notna(row.get('Dispute Date (UTC)')) and row.get('Dispute Status') in ['won', 'lost']:
        # Using Dispute Date + offset as fallback if Closed Date missing
        closed_ts = row.get('Dispute Date (UTC)') + timedelta(days=30)
        event_log_rows.append({
            'PaymentIntentId': row.get('id'),
            'EventName': 'Dispute Closed',
            'EventTimestamp': closed_ts,
            'SourceSystem': 'Stripe'
        })

    # 15. Payment Refunded
    add_event(row, 'Payment Refunded', 'Refunded (UTC)')

    # 16. Payment Reconciled (Derived: If Payout Date exists, it is reconciled)
    if pd.notna(row.get('Payout Date (UTC)')):
        reconciled_ts = row.get('Payout Date (UTC)') + timedelta(days=1) # Bank processing time
        event_log_rows.append({
            'PaymentIntentId': row.get('id'),
            'EventName': 'Payment Reconciled',
            'EventTimestamp': reconciled_ts,
            'SourceSystem': 'Stripe'
        })

# Create Event Log DataFrame
event_log_df = pd.DataFrame(event_log_rows)

# Add Extraction Timestamp
event_log_df['ExtractionTimestamp'] = pd.Timestamp.now()

# Export
event_log_df.to_csv(OUTPUT_FILE, index=False)
print(f"Transformation Complete. Exported {len(event_log_df)} events to {OUTPUT_FILE}")

/* Activity 1: Payment Request Created */
SELECT
  id AS PaymentIntentId,
  'Payment Request Created' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date AS ExtractionTimestamp,
  amount AS PaymentAmount,
  currency AS PaymentCurrency,
  status AS PaymentStatus,
  customer_id AS CustomerId,
  CAST(NULL AS VARCHAR) AS FailureCode,
  CAST(NULL AS VARCHAR) AS PaymentMethodType,
  CAST(NULL AS VARCHAR) AS PayoutId,
  CAST(NULL AS VARCHAR) AS ProcessingUser
FROM payment_intents
WHERE created >= current_date - interval '3' month

UNION ALL

/* Activity 2: Payment Details Validated */
SELECT
  payment_intent_id,
  'Payment Details Validated' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  amount,
  currency,
  status,
  customer_id,
  failure_code,
  payment_method_details_type,
  CAST(NULL AS VARCHAR),
  CAST(NULL AS VARCHAR)
FROM charges
WHERE created >= current_date - interval '3' month

UNION ALL

/* Activity 3: Payment Review Needed */
SELECT
  c.payment_intent_id,
  'Payment Review Needed' AS EventName,
  r.created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  c.amount,
  c.currency,
  r.status,
  c.customer_id,
  NULL,
  c.payment_method_details_type,
  NULL,
  NULL
FROM reviews r
JOIN charges c ON r.charge_id = c.id
WHERE r.created >= current_date - interval '3' month
  AND r.opened_reason = 'rule'

UNION ALL

/* Activity 4: Payment Review Approved */
SELECT
  c.payment_intent_id,
  'Payment Review Approved' AS EventName,
  r.closed_on AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  c.amount,
  c.currency,
  r.status,
  c.customer_id,
  NULL,
  c.payment_method_details_type,
  NULL,
  CAST(NULL AS VARCHAR) -- User info rarely available in standard Sigma tables
FROM reviews r
JOIN charges c ON r.charge_id = c.id
WHERE r.closed_on >= current_date - interval '3' month
  AND r.closed_reason = 'approved'

UNION ALL

/* Activity 5: Payment Authentication Requested */
SELECT
  payment_intent_id,
  'Payment Authentication Requested' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  amount,
  currency,
  status,
  customer_id,
  NULL,
  payment_method_details_type,
  NULL,
  NULL
FROM charges
WHERE created >= current_date - interval '3' month
  AND outcome_type = 'issuer_declined'
  AND failure_code = 'authentication_required'

UNION ALL

/* Activity 6: Payment Authorized */
SELECT
  payment_intent_id,
  'Payment Authorized' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  amount,
  currency,
  status,
  customer_id,
  NULL,
  payment_method_details_type,
  NULL,
  NULL
FROM charges
WHERE created >= current_date - interval '3' month
  AND paid = true
  AND refunded = false

UNION ALL

/* Activity 7: Payment Failed */
SELECT
  payment_intent_id,
  'Payment Failed' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  amount,
  currency,
  status,
  customer_id,
  failure_code,
  payment_method_details_type,
  NULL,
  NULL
FROM charges
WHERE created >= current_date - interval '3' month
  AND status = 'failed'

UNION ALL

/* Activity 8: Payment Error Resolved */
/* Inferred: Successful charge exists for an Intent that has a recorded error */
SELECT
  pi.id,
  'Payment Error Resolved' AS EventName,
  pi.created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  pi.amount,
  pi.currency,
  pi.status,
  pi.customer_id,
  NULL,
  NULL,
  NULL,
  NULL
FROM payment_intents pi
WHERE pi.created >= current_date - interval '3' month
  AND pi.last_payment_error_code IS NOT NULL
  AND pi.status = 'succeeded'

UNION ALL

/* Activity 9: Payment Notification Sent */
SELECT
  payment_intent_id,
  'Payment Notification Sent' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  amount,
  currency,
  status,
  customer_id,
  NULL,
  payment_method_details_type,
  NULL,
  NULL
FROM charges
WHERE created >= current_date - interval '3' month
  AND receipt_sent = true

UNION ALL

/* Activity 10: Funds Available */
SELECT
  c.payment_intent_id,
  'Funds Available' AS EventName,
  bt.available_on AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  bt.amount,
  bt.currency,
  bt.status,
  c.customer_id,
  NULL,
  NULL,
  NULL,
  NULL
FROM balance_transactions bt
JOIN charges c ON bt.source_id = c.id
WHERE bt.available_on >= current_date - interval '3' month
  AND bt.type = 'charge'

UNION ALL

/* Activity 11: Payment Included in Payout */
SELECT
  c.payment_intent_id,
  'Payment Included in Payout' AS EventName,
  p.created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  p.amount,
  p.currency,
  p.status,
  c.customer_id,
  NULL,
  NULL,
  p.id AS PayoutId,
  NULL
FROM balance_transactions bt
JOIN payouts p ON bt.payout_id = p.id
JOIN charges c ON bt.source_id = c.id
WHERE p.created >= current_date - interval '3' month
  AND bt.type = 'charge'

UNION ALL

/* Activity 12: Funds Transferred */
SELECT
  c.payment_intent_id,
  'Funds Transferred' AS EventName,
  p.arrival_date AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  p.amount,
  p.currency,
  p.status,
  c.customer_id,
  NULL,
  NULL,
  p.id AS PayoutId,
  NULL
FROM balance_transactions bt
JOIN payouts p ON bt.payout_id = p.id
JOIN charges c ON bt.source_id = c.id
WHERE p.arrival_date >= current_date - interval '3' month
  AND p.status = 'paid'
  AND bt.type = 'charge'

UNION ALL

/* Activity 13: Dispute Opened */
SELECT
  c.payment_intent_id,
  'Dispute Opened' AS EventName,
  d.created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  d.amount,
  d.currency,
  d.status,
  c.customer_id,
  d.reason,
  NULL,
  NULL,
  NULL
FROM disputes d
JOIN charges c ON d.charge_id = c.id
WHERE d.created >= current_date - interval '3' month

UNION ALL

/* Activity 14: Dispute Closed */
SELECT
  c.payment_intent_id,
  'Dispute Closed' AS EventName,
  -- Approximation: using available_on date of the reversal transaction as closure time
  bt.available_on AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  d.amount,
  d.currency,
  d.status,
  c.customer_id,
  d.reason,
  NULL,
  NULL,
  NULL
FROM disputes d
JOIN charges c ON d.charge_id = c.id
JOIN balance_transactions bt ON d.charge_id = bt.source_id
WHERE bt.available_on >= current_date - interval '3' month
  AND d.status IN ('won', 'lost')
  AND bt.type = 'adjustment'

UNION ALL

/* Activity 15: Payment Refunded */
SELECT
  payment_intent_id,
  'Payment Refunded' AS EventName,
  created AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  amount,
  currency,
  status,
  CAST(NULL AS VARCHAR),
  reason,
  CAST(NULL AS VARCHAR),
  CAST(NULL AS VARCHAR),
  CAST(NULL AS VARCHAR)
FROM refunds
WHERE created >= current_date - interval '3' month

UNION ALL

/* Activity 16: Payment Reconciled */
SELECT
  c.payment_intent_id,
  'Payment Reconciled' AS EventName,
  p.arrival_date AS EventTimestamp,
  'Stripe' AS SourceSystem,
  current_date,
  p.amount,
  p.currency,
  'reconciled' AS PaymentStatus,
  c.customer_id,
  NULL,
  NULL,
  p.id,
  NULL
FROM balance_transactions bt
JOIN payouts p ON bt.payout_id = p.id
JOIN charges c ON bt.source_id = c.id
WHERE p.arrival_date >= current_date - interval '3' month
  AND p.status = 'paid'
  AND bt.type = 'charge'

import stripe
import pandas as pd
import time
from datetime import datetime, timezone

class=class="hl-string">"hl-comment"># --- CONFIGURATION ---
STRIPE_API_KEY = class="hl-string">"sk_test_replace_with_your_key" 
START_DATE = datetime(2023, 1, 1, tzinfo=timezone.utc)
END_DATE = datetime(2023, 1, 31, tzinfo=timezone.utc)
OUTPUT_FILE = class="hl-string">"stripe_event_log.csv"

stripe.api_key = STRIPE_API_KEY

class=class="hl-string">"hl-comment"># Helper to convert unix timestamp to ISO format
def format_timestamp(ts):
    return datetime.fromtimestamp(ts, timezone.utc).isoformat()

class=class="hl-string">"hl-comment"># Helper to get PaymentIntent ID from various objects
def get_payment_intent_id(obj):
    if class="hl-string">'payment_intent' in obj and obj[class="hl-string">'payment_intent']:
        return obj[class="hl-string">'payment_intent']
    if obj.get(class="hl-string">'object') == class="hl-string">'payment_intent':
        return obj[class="hl-string">'id']
    if obj.get(class="hl-string">'object') == class="hl-string">'charge':
        return obj.get(class="hl-string">'payment_intent')
    return None

class=class="hl-string">"hl-comment"># Helper to fetch all balance transactions for a payout
def fetch_payout_transactions(payout_id):
    transactions = []
    limit = 100
    params = {class="hl-string">'payout': payout_id, class="hl-string">'limit': limit}
    
    for txn in stripe.BalanceTransaction.list(**params).auto_paging_iter():
        transactions.append(txn)
        class=class="hl-string">"hl-comment"># Rate limit safety
        time.sleep(0.02)
        
    return transactions

def main():
    event_log = []
    
    class=class="hl-string">"hl-comment"># Convert dates to unix timestamps for API
    start_ts = int(START_DATE.timestamp())
    end_ts = int(END_DATE.timestamp())
    
    print(fclass="hl-string">"Fetching events from {START_DATE} to {END_DATE}...")
    
    class=class="hl-string">"hl-comment"># Iterate through Stripe Events
    class=class="hl-string">"hl-comment"># Note: We use auto_paging_iter() for handling pagination automatically
    params = {
        class="hl-string">'created': {class="hl-string">'gte': start_ts, class="hl-string">'lte': end_ts},
        class="hl-string">'limit': 100,
        class="hl-string">'types': [
            class="hl-string">'payment_intent.created', class="hl-string">'payment_intent.requires_action', 
            class="hl-string">'payment_intent.succeeded', class="hl-string">'payment_intent.payment_failed',
            class="hl-string">'charge.created', class="hl-string">'charge.succeeded', class="hl-string">'charge.failed', class="hl-string">'charge.refunded',
            class="hl-string">'charge.dispute.created', class="hl-string">'charge.dispute.closed',
            class="hl-string">'review.opened', class="hl-string">'review.closed',
            class="hl-string">'payout.created', class="hl-string">'payout.paid', class="hl-string">'payout.updated'
        ]
    }

    try:
        for event in stripe.Event.list(**params).auto_paging_iter():
            event_type = event[class="hl-string">'type']
            data = event[class="hl-string">'data'][class="hl-string">'object']
            timestamp = format_timestamp(event[class="hl-string">'created'])
            
            class=class="hl-string">"hl-comment"># Base row structure
            row = {
                class="hl-string">'PaymentIntentId': get_payment_intent_id(data),
                class="hl-string">'EventName': None,
                class="hl-string">'EventTimestamp': timestamp,
                class="hl-string">'SourceSystem': class="hl-string">'Stripe',
                class="hl-string">'ExtractionTimestamp': datetime.now(timezone.utc).isoformat(),
                class="hl-string">'PaymentAmount': data.get(class="hl-string">'amount', 0) / 100.0 if class="hl-string">'amount' in data else None,
                class="hl-string">'PaymentCurrency': data.get(class="hl-string">'currency', class="hl-string">'').upper(),
                class="hl-string">'PaymentStatus': data.get(class="hl-string">'status'),
                class="hl-string">'CustomerId': data.get(class="hl-string">'customer'),
                class="hl-string">'FailureCode': data.get(class="hl-string">'failure_code'),
                class="hl-string">'PaymentMethodType': data.get(class="hl-string">'payment_method_types', [None])[0] if isinstance(data.get(class="hl-string">'payment_method_types'), list) else None,
                class="hl-string">'PayoutId': data.get(class="hl-string">'id') if event_type.startswith(class="hl-string">'payout') else None,
                class="hl-string">'ProcessingUser': class="hl-string">'Stripe System'
            }

            class=class="hl-string">"hl-comment"># --- ACTIVITY MAPPING ---

            class=class="hl-string">"hl-comment"># 1. Payment Request Created
            if event_type == class="hl-string">'payment_intent.created':
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Request Created'
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 2. Payment Details Validated (Mapped to Charge Creation)
            elif event_type == class="hl-string">'charge.created':
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Details Validated'
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 3. Payment Review Needed
            elif event_type == class="hl-string">'review.opened':
                class=class="hl-string">"hl-comment"># Review objects map to PaymentIntents via payment_intent field
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Review Needed'
                row[class="hl-string">'PaymentIntentId'] = data.get(class="hl-string">'payment_intent')
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 4. Payment Review Approved
            elif event_type == class="hl-string">'review.closed':
                if data.get(class="hl-string">'reason') == class="hl-string">'approved':
                    row[class="hl-string">'EventName'] = class="hl-string">'Payment Review Approved'
                    row[class="hl-string">'PaymentIntentId'] = data.get(class="hl-string">'payment_intent')
                    row[class="hl-string">'ProcessingUser'] = class="hl-string">'Stripe Reviewer'
                    event_log.append(row)

            class=class="hl-string">"hl-comment"># 5. Payment Authentication Requested
            elif event_type == class="hl-string">'payment_intent.requires_action':
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Authentication Requested'
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 6. Payment Authorized
            elif event_type == class="hl-string">'charge.succeeded':
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Authorized'
                event_log.append(row)
                
                class=class="hl-string">"hl-comment"># 9. Payment Notification Sent (Derived)
                if data.get(class="hl-string">'receipt_sent') or data.get(class="hl-string">'receipt_email'):
                    notif_row = row.copy()
                    notif_row[class="hl-string">'EventName'] = class="hl-string">'Payment Notification Sent'
                    event_log.append(notif_row)
                
                class=class="hl-string">"hl-comment"># 10. Funds Available (Derived from Balance Transaction)
                if data.get(class="hl-string">'balance_transaction'):
                    try:
                        bt = stripe.BalanceTransaction.retrieve(data[class="hl-string">'balance_transaction'])
                        funds_row = row.copy()
                        funds_row[class="hl-string">'EventName'] = class="hl-string">'Funds Available'
                        class=class="hl-string">"hl-comment"># Use available_on (timestamp) for when funds actually clear
                        funds_row[class="hl-string">'EventTimestamp'] = format_timestamp(bt[class="hl-string">'available_on'])
                        event_log.append(funds_row)
                    except Exception as e:
                        print(fclass="hl-string">"Warning: Could not fetch BT for charge {data['id']}: {e}")

            class=class="hl-string">"hl-comment"># 7. Payment Failed
            elif event_type == class="hl-string">'charge.failed':
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Failed'
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 8. Payment Error Resolved (Inferred from success after failure)
            elif event_type == class="hl-string">'payment_intent.succeeded':
                class=class="hl-string">"hl-comment"># If the successful PI has a last_payment_error field, it means it recovered
                if data.get(class="hl-string">'last_payment_error'):
                    row[class="hl-string">'EventName'] = class="hl-string">'Payment Error Resolved'
                    event_log.append(row)

            class=class="hl-string">"hl-comment"># 13. Dispute Opened
            elif event_type == class="hl-string">'charge.dispute.created':
                row[class="hl-string">'EventName'] = class="hl-string">'Dispute Opened'
                class=class="hl-string">"hl-comment"># Dispute object links to charge, which links to PI
                if not row[class="hl-string">'PaymentIntentId']:
                    ch = stripe.Charge.retrieve(data[class="hl-string">'charge'])
                    row[class="hl-string">'PaymentIntentId'] = ch.get(class="hl-string">'payment_intent')
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 14. Dispute Closed
            elif event_type == class="hl-string">'charge.dispute.closed':
                row[class="hl-string">'EventName'] = class="hl-string">'Dispute Closed'
                if not row[class="hl-string">'PaymentIntentId']:
                    ch = stripe.Charge.retrieve(data[class="hl-string">'charge'])
                    row[class="hl-string">'PaymentIntentId'] = ch.get(class="hl-string">'payment_intent')
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 15. Payment Refunded
            elif event_type == class="hl-string">'charge.refunded':
                row[class="hl-string">'EventName'] = class="hl-string">'Payment Refunded'
                event_log.append(row)

            class=class="hl-string">"hl-comment"># 11. Payment Included in Payout & 12. Funds Transferred
            elif event_type in [class="hl-string">'payout.created', class="hl-string">'payout.paid']:
                payout_id = data[class="hl-string">'id']
                payout_ts = timestamp
                activity_name = class="hl-string">'Payment Included in Payout' if event_type == class="hl-string">'payout.created' else class="hl-string">'Funds Transferred'
                
                class=class="hl-string">"hl-comment"># Fetch ALL transactions in this payout to link back to individual payments
                payout_txns = fetch_payout_transactions(payout_id)
                
                for txn in payout_txns:
                    if txn[class="hl-string">'type'] == class="hl-string">'charge':
                        class=class="hl-string">"hl-comment"># We need to get the PaymentIntent ID from the Source (Charge)
                        class=class="hl-string">"hl-comment"># Note: txn[class="hl-string">'source'] is usually the ID string, need to fetch object if ID not known
                        class=class="hl-string">"hl-comment"># To avoid N+1 API calls, we rely on txn source usually being Charge ID. 
                        class=class="hl-string">"hl-comment"># Ideally we fetch the charge, but for speed we might have to use ChargeID if PI not available.
                        class=class="hl-string">"hl-comment"># However, to be compliant with schema, we fetch the charge to get PI.
                        class=class="hl-string">"hl-comment"># Optimizing: txn object in payout usually has simplified source.
                        
                        try:
                            class=class="hl-string">"hl-comment"># Retrieving charge to get PI is safest but slow. 
                            class=class="hl-string">"hl-comment"># In high volume, consider caching or relying on Charge ID as Case ID if PIs are not used.
                            class=class="hl-string">"hl-comment"># Here we assume PI is needed.
                            charge_obj = stripe.Charge.retrieve(txn[class="hl-string">'source'])
                            pi_id = charge_obj.get(class="hl-string">'payment_intent')
                            
                            if pi_id:
                                p_row = {
                                    class="hl-string">'PaymentIntentId': pi_id,
                                    class="hl-string">'EventName': activity_name,
                                    class="hl-string">'EventTimestamp': payout_ts,
                                    class="hl-string">'SourceSystem': class="hl-string">'Stripe',
                                    class="hl-string">'ExtractionTimestamp': datetime.now(timezone.utc).isoformat(),
                                    class="hl-string">'PaymentAmount': txn[class="hl-string">'amount'] / 100.0,
                                    class="hl-string">'PaymentCurrency': txn[class="hl-string">'currency'].upper(),
                                    class="hl-string">'PayoutId': payout_id,
                                    class="hl-string">'ProcessingUser': class="hl-string">'Stripe Payout System'
                                }
                                event_log.append(p_row)
                        except Exception as e:
                            pass class=class="hl-string">"hl-comment"># Skip if charge lookup fails

            class=class="hl-string">"hl-comment"># 16. Payment Reconciled
            elif event_type == class="hl-string">'payout.updated':
                if data.get(class="hl-string">'reconciliation_status') == class="hl-string">'completed':
                     class=class="hl-string">"hl-comment"># Same logic as Payout Paid, fetch txns
                    payout_id = data[class="hl-string">'id']
                    payout_ts = timestamp
                    payout_txns = fetch_payout_transactions(payout_id)
                    for txn in payout_txns:
                        if txn[class="hl-string">'type'] == class="hl-string">'charge':
                             try:
                                charge_obj = stripe.Charge.retrieve(txn[class="hl-string">'source'])
                                pi_id = charge_obj.get(class="hl-string">'payment_intent')
                                if pi_id:
                                    r_row = {
                                        class="hl-string">'PaymentIntentId': pi_id,
                                        class="hl-string">'EventName': class="hl-string">'Payment Reconciled',
                                        class="hl-string">'EventTimestamp': payout_ts,
                                        class="hl-string">'SourceSystem': class="hl-string">'Stripe',
                                        class="hl-string">'ExtractionTimestamp': datetime.now(timezone.utc).isoformat(),
                                        class="hl-string">'PayoutId': payout_id
                                    }
                                    event_log.append(r_row)
                             except:
                                 pass
            
            class=class="hl-string">"hl-comment"># Rate limit handling for the main loop
            time.sleep(0.01)

    except Exception as e:
        print(fclass="hl-string">"An error occurred: {e}")

    class=class="hl-string">"hl-comment"># Export to CSV
    if event_log:
        df = pd.DataFrame(event_log)
        class=class="hl-string">"hl-comment"># Clean up columns
        columns = [class="hl-string">'PaymentIntentId', class="hl-string">'EventName', class="hl-string">'EventTimestamp', class="hl-string">'SourceSystem', 
                   class="hl-string">'ExtractionTimestamp', class="hl-string">'PaymentAmount', class="hl-string">'PaymentCurrency', 
                   class="hl-string">'PaymentStatus', class="hl-string">'CustomerId', class="hl-string">'FailureCode', class="hl-string">'PayoutId', class="hl-string">'ProcessingUser']
        class=class="hl-string">"hl-comment"># Filter only existing keys to avoid errors if some are missing
        final_cols = [c for c in columns if c in df.columns]
        df = df[final_cols]
        
        class=class="hl-string">"hl-comment"># Filter out rows with no PaymentIntentId (e.g. standalone charges or errors)
        df = df.dropna(subset=[class="hl-string">'PaymentIntentId'])
        
        print(fclass="hl-string">"Extracted {len(df)} events. Saving to {OUTPUT_FILE}...")
        df.to_csv(OUTPUT_FILE, index=False)
        print(class="hl-string">"Done.")
    else:
        print(class="hl-string">"No events found in the specified range.")

if __name__ == class="hl-string">"__main__":
    main()