# Run billing schema migration
psql -h localhost -U postgres -d nself -f src/migrations/billing/001_initial_schema.sql

# Nginx log format (custom)
log_format billing '$remote_addr - $remote_user [$time_local] '
                   '"$request" $status $body_bytes_sent '
                   '"$http_user_agent" customer_id=$http_x_customer_id '
                   'service=api endpoint=$uri method=$request_method';

# Parse logs and batch insert (cron job every minute)
tail -f /var/log/nginx/access.log | \
  grep 'customer_id=' | \
  awk '{...}' | \
  psql -c "INSERT INTO billing_usage_records ..."

# In application request handler
usage_track_api_request() {
  local endpoint="$1"
  local method="$2"
  local status="$3"

  # Add to buffer (Redis LPUSH or Bash array)
  billing_record_usage "api" 1 "{\"endpoint\":\"$endpoint\",\"method\":\"$method\",\"status\":$status}"
}

# Cron job runs every hour
measure_storage_usage() {
  local customer_id="$1"

  # Measure MinIO storage
  local minio_bytes
  minio_bytes=$(mc du --json minio/customer-${customer_id} | jq -r '.size')

  # Measure PostgreSQL storage
  local postgres_bytes
  postgres_bytes=$(psql -tc "
    SELECT pg_total_relation_size('customer_${customer_id}_data')
  ")

  local total_bytes=$((minio_bytes + postgres_bytes))
  local gb_hours
  gb_hours=$(awk "BEGIN {print ($total_bytes / 1073741824) * 1}")  # 1 hour

  # Record usage
  usage_track_storage "$total_bytes" 1
}

# Parse Nginx logs for bytes sent
measure_bandwidth() {
  local customer_id="$1"
  local period_start="$2"
  local period_end="$3"

  # Aggregate from Nginx logs
  local bytes_sent
  bytes_sent=$(awk -v start="$period_start" -v end="$period_end" '
    $4 >= start && $4 <= end && /customer_id='$customer_id'/ {
      sum += $10  # $body_bytes_sent
    }
    END { print sum }
  ' /var/log/nginx/access.log)

  local gb
  gb=$(awk "BEGIN {print $bytes_sent / 1073741824}")

  # Record as egress bandwidth
  usage_track_bandwidth "$bytes_sent" "egress"
}

# Track container CPU time
measure_compute_usage() {
  local container_id="$1"
  local customer_id="$2"

  # Get CPU usage from cAdvisor or Docker stats
  local cpu_seconds
  cpu_seconds=$(docker stats --no-stream --format "{{.CPUPerc}}" "$container_id" | \
    sed 's/%//' | \
    awk '{print $1 / 100}')  # Convert to seconds

  # Record usage
  usage_track_compute "$cpu_seconds" "{\"container\":\"$container_id\"}"
}

# Function wrapper
execute_function() {
  local function_name="$1"
  shift
  local args="$@"

  local start_time
  start_time=$(date +%s%3N)  # Milliseconds

  # Execute function
  "$function_name" "$args"
  local exit_code=$?

  local end_time
  end_time=$(date +%s%3N)
  local duration=$((end_time - start_time))

  # Track usage
  usage_track_function "$function_name" "$duration" 256  # 256MB memory

  return $exit_code
}

# Increment current usage counter
HINCRBY usage:api:${customer_id}:${period} count 1

# Get current usage
HGET usage:api:${customer_id}:${period} count

# Buffer events in memory (array or Redis)
USAGE_BUFFER=()
USAGE_BUFFER_SIZE=1000
USAGE_BUFFER_TIMEOUT=60  # seconds

track_usage_event() {
  local service="$1"
  local quantity="$2"
  local metadata="$3"

  # Add to buffer
  USAGE_BUFFER+=("$service|$quantity|$metadata|$(date -u +%Y-%m-%dT%H:%M:%SZ)")

  # Flush if buffer full or timeout reached
  if [ ${#USAGE_BUFFER[@]} -ge $USAGE_BUFFER_SIZE ]; then
    flush_usage_buffer
  fi
}

flush_usage_buffer() {
  if [ ${#USAGE_BUFFER[@]} -eq 0 ]; then
    return
  fi

  # Build batch INSERT
  local values=""
  for event in "${USAGE_BUFFER[@]}"; do
    IFS='|' read -r service quantity metadata timestamp <<< "$event"
    values="${values}('${CUSTOMER_ID}', '${service}', ${quantity}, '${metadata}', '${timestamp}'),"
  done
  values="${values%,}"  # Remove trailing comma

  # Execute batch insert
  psql -c "
    INSERT INTO billing_usage_records (customer_id, service_name, quantity, metadata, recorded_at)
    VALUES ${values}
  "

  # Clear buffer
  USAGE_BUFFER=()
}

# Cron job flushes buffer every 60 seconds
*/1 * * * * /path/to/flush_usage_buffer.sh

# Use PgBouncer for connection reuse
psql -h pgbouncer-host -p 6432 -U billing_user -d nself

# Core Stripe API call function
stripe_api_call() {
  local method="$1"      # GET, POST, DELETE
  local endpoint="$2"    # /v1/customers, /v1/subscriptions, etc.
  shift 2
  local data_args=("$@") # -d key=value pairs

  local url="${STRIPE_API_BASE}${endpoint}"
  local max_retries=3
  local retry_count=0
  local response

  while [ $retry_count -lt $max_retries ]; do
    response=$(curl -s -w "\n%{http_code}" \
      -X "$method" \
      -u "${STRIPE_SECRET_KEY}:" \
      -H "Stripe-Version: ${STRIPE_API_VERSION}" \
      "${data_args[@]}" \
      "$url")

    local http_code
    http_code=$(echo "$response" | tail -n1)
    local body
    body=$(echo "$response" | sed '$d')

    # Success (2xx)
    if [[ "$http_code" =~ ^2 ]]; then
      echo "$body"
      return 0
    fi

    # Rate limit (429) - exponential backoff
    if [ "$http_code" -eq 429 ]; then
      local wait_time=$((2 ** retry_count))
      sleep "$wait_time"
      retry_count=$((retry_count + 1))
      continue
    fi

    # Server error (5xx) - retry
    if [[ "$http_code" =~ ^5 ]]; then
      sleep 2
      retry_count=$((retry_count + 1))
      continue
    fi

    # Client error (4xx) - don't retry
    echo "Error: HTTP $http_code - $body" >&2
    return 1
  done

  echo "Error: Max retries exceeded" >&2
  return 1
}

# Create customer
stripe_api_call POST /v1/customers \
  -d email="[email protected]" \
  -d name="John Doe" \
  -d metadata[project_name]="myproject"

# Get subscription
stripe_api_call GET /v1/subscriptions/sub_1234567890

# Update subscription
stripe_api_call POST /v1/subscriptions/sub_1234567890 \
  -d cancel_at_period_end=true

# Delete customer
stripe_api_call DELETE /v1/customers/cus_1234567890

# Nginx configuration
location /api/webhooks/stripe {
  proxy_pass http://localhost:3000/webhooks/stripe;
  proxy_set_header Host $host;
  proxy_set_header X-Real-IP $remote_addr;
  proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

  # No timeout - webhooks can be slow
  proxy_read_timeout 300s;

  # Preserve raw body for signature verification
  proxy_request_buffering off;
}

#!/usr/bin/env bash
# webhooks/stripe-handler.sh

handle_stripe_webhook() {
  local raw_body="$1"
  local signature="$2"  # From Stripe-Signature header

  # 1. Verify webhook signature
  if ! verify_stripe_signature "$raw_body" "$signature"; then
    echo "Error: Invalid webhook signature" >&2
    return 1
  fi

  # 2. Parse event
  local event_type
  event_type=$(echo "$raw_body" | jq -r '.type')
  local event_id
  event_id=$(echo "$raw_body" | jq -r '.id')

  # 3. Check idempotency (prevent duplicate processing)
  if webhook_event_exists "$event_id"; then
    echo "Event already processed: $event_id" >&2
    return 0
  fi

  # 4. Log event
  log_webhook_event "$event_id" "$event_type" "$raw_body"

  # 5. Route to handler
  case "$event_type" in
    customer.created|customer.updated)
      handle_customer_event "$raw_body"
      ;;
    customer.subscription.created|customer.subscription.updated)
      handle_subscription_updated "$raw_body"
      ;;
    customer.subscription.deleted)
      handle_subscription_deleted "$raw_body"
      ;;
    customer.subscription.trial_will_end)
      handle_trial_ending "$raw_body"
      ;;
    invoice.created|invoice.finalized)
      handle_invoice_created "$raw_body"
      ;;
    invoice.paid)
      handle_invoice_paid "$raw_body"
      ;;
    invoice.payment_failed)
      handle_payment_failed "$raw_body"
      ;;
    payment_intent.succeeded)
      handle_payment_succeeded "$raw_body"
      ;;
    payment_intent.payment_failed)
      handle_payment_failed_intent "$raw_body"
      ;;
    payment_method.attached|payment_method.detached)
      handle_payment_method "$raw_body"
      ;;
    *)
      echo "Unhandled event type: $event_type" >&2
      ;;
  esac

  return 0
}

verify_stripe_signature() {
  local payload="$1"
  local signature_header="$2"

  # Parse signature header
  local timestamp
  timestamp=$(echo "$signature_header" | grep -o 't=[^,]*' | cut -d= -f2)
  local expected_signature
  expected_signature=$(echo "$signature_header" | grep -o 'v1=[^,]*' | cut -d= -f2)

  # Check timestamp (reject if > 5 minutes old)
  local current_time
  current_time=$(date +%s)
  if [ $((current_time - timestamp)) -gt 300 ]; then
    echo "Error: Webhook timestamp too old" >&2
    return 1
  fi

  # Compute HMAC SHA256
  local signed_payload="${timestamp}.${payload}"
  local computed_signature
  computed_signature=$(echo -n "$signed_payload" | \
    openssl dgst -sha256 -hmac "$STRIPE_WEBHOOK_SECRET" | \
    awk '{print $2}')

  # Compare signatures (constant-time comparison)
  if [ "$computed_signature" != "$expected_signature" ]; then
    echo "Error: Signature mismatch" >&2
    return 1
  fi

  return 0
}

webhook_event_exists() {
  local event_id="$1"

  local count
  count=$(psql -tAc "
    SELECT COUNT(*) FROM billing_webhook_events WHERE event_id = '$event_id'
  ")

  [ "$count" -gt 0 ]
}

log_webhook_event() {
  local event_id="$1"
  local event_type="$2"
  local payload="$3"

  psql -c "
    INSERT INTO billing_webhook_events (event_id, event_type, payload)
    VALUES ('$event_id', '$event_type', '$payload'::jsonb)
    ON CONFLICT (event_id) DO NOTHING
  "
}

# Process webhook with retry on failure
process_webhook_with_retry() {
  local event_id="$1"
  local handler="$2"
  local payload="$3"
  local max_attempts=3
  local attempt=1

  while [ $attempt -le $max_attempts ]; do
    if $handler "$payload"; then
      # Success - mark as processed
      psql -c "
        UPDATE billing_webhook_events
        SET status = 'processed'
        WHERE event_id = '$event_id'
      "
      return 0
    fi

    # Failure - log and retry
    psql -c "
      UPDATE billing_webhook_events
      SET status = 'failed', attempts = $attempt
      WHERE event_id = '$event_id'
    "

    attempt=$((attempt + 1))
    sleep $((attempt * 2))  # Exponential backoff
  done

  # All attempts failed
  psql -c "
    UPDATE billing_webhook_events
    SET status = 'failed_permanent'
    WHERE event_id = '$event_id'
  "
  return 1
}

# Graceful degradation
get_customer_info() {
  local customer_id="$1"

  # Try Stripe API first
  local customer_data
  customer_data=$(stripe_api_call GET "/v1/customers/$customer_id" 2>/dev/null)

  if [ $? -eq 0 ]; then
    echo "$customer_data"
    return 0
  fi

  # Fallback to local database cache
  customer_data=$(psql -tAc "
    SELECT row_to_json(c) FROM billing_customers c WHERE customer_id = '$customer_id'
  ")

  if [ -n "$customer_data" ]; then
    echo "$customer_data"
    return 0
  fi

  echo "Error: Customer not found" >&2
  return 1
}

# Prevent cascading failures
STRIPE_API_FAILURES=0
STRIPE_API_CIRCUIT_OPEN=false
STRIPE_API_CIRCUIT_THRESHOLD=5
STRIPE_API_CIRCUIT_TIMEOUT=60  # seconds

stripe_api_call_with_circuit_breaker() {
  # Check if circuit is open
  if [ "$STRIPE_API_CIRCUIT_OPEN" = true ]; then
    echo "Error: Circuit breaker open for Stripe API" >&2
    return 1
  fi

  # Call API
  if stripe_api_call "$@"; then
    # Success - reset failure count
    STRIPE_API_FAILURES=0
    return 0
  else
    # Failure - increment count
    STRIPE_API_FAILURES=$((STRIPE_API_FAILURES + 1))

    # Open circuit if threshold reached
    if [ $STRIPE_API_FAILURES -ge $STRIPE_API_CIRCUIT_THRESHOLD ]; then
      STRIPE_API_CIRCUIT_OPEN=true
      echo "Circuit breaker opened for Stripe API" >&2

      # Schedule circuit close after timeout
      (sleep $STRIPE_API_CIRCUIT_TIMEOUT && STRIPE_API_CIRCUIT_OPEN=false) &
    fi

    return 1
  fi
}

# .env.test
STRIPE_SECRET_KEY=your_stripe_test_secret_key_here
STRIPE_PUBLISHABLE_KEY=pk_test_51Nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxm
STRIPE_WEBHOOK_SECRET=whsec_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# Success
4242424242424242  # Visa
5555555555554444  # Mastercard

# Decline
4000000000000002  # Generic decline
4000000000009995  # Insufficient funds
4000000000009987  # Lost card
4000000000009979  # Stolen card

# 3D Secure
4000002500003155  # Requires authentication

# Webhook Testing
stripe trigger customer.created
stripe trigger invoice.payment_failed

# test/integration/billing/stripe-integration.test.sh

test_create_customer() {
  local email="test-$(date +%s)@example.com"

  # Create customer
  local customer_id
  customer_id=$(create_stripe_customer "$email" "Test User" | jq -r '.id')

  # Verify in database
  local db_customer
  db_customer=$(psql -tAc "
    SELECT customer_id FROM billing_customers WHERE customer_id = '$customer_id'
  ")

  assert_equals "$customer_id" "$db_customer"
}

test_subscription_lifecycle() {
  # Create customer
  local customer_id
  customer_id=$(create_stripe_customer "[email protected]" "Test" | jq -r '.id')

  # Create subscription
  local subscription_id
  subscription_id=$(create_stripe_subscription "$customer_id" "pro" | jq -r '.id')

  # Verify active status
  local status
  status=$(get_subscription_status "$subscription_id")
  assert_equals "active" "$status"

  # Cancel subscription
  cancel_stripe_subscription "$subscription_id"

  # Verify canceled
  status=$(get_subscription_status "$subscription_id")
  assert_equals "canceled" "$status"

  # Cleanup
  delete_stripe_customer "$customer_id"
}

# Forward webhooks to localhost
stripe listen --forward-to http://localhost:3000/api/webhooks/stripe

# Trigger specific events
stripe trigger customer.subscription.updated
stripe trigger invoice.payment_succeeded
stripe trigger payment_intent.payment_failed

# View webhook events
stripe events list
stripe events retrieve evt_xxxxxxxxxxxxxx

# Fast quota enforcement
quota_enforce() {
  local service="$1"
  local quantity="${2:-1}"

  # 1. Get current usage (cached)
  local current_usage
  current_usage=$(quota_get_current_usage "$service")

  # 2. Get quota limit (cached)
  local quota_limit
  quota_limit=$(quota_get_limit "$service")

  # 3. Get enforcement mode
  local enforcement_mode
  enforcement_mode=$(quota_get_enforcement_mode "$service")

  # 4. Check if quota would be exceeded
  local new_usage
  new_usage=$(awk "BEGIN {print $current_usage + $quantity}")

  if (( $(echo "$new_usage > $quota_limit" | bc -l) )); then
    # Quota exceeded
    if [ "$enforcement_mode" = "hard" ]; then
      # Hard limit - deny request
      log_quota_exceeded "$service" "$new_usage" "$quota_limit" "blocked"
      return 1
    else
      # Soft limit - allow with warning
      log_quota_exceeded "$service" "$new_usage" "$quota_limit" "warning"
      return 0
    fi
  fi

  # Within quota
  return 0
}

# Example: API request handler
handle_api_request() {
  local endpoint="$1"
  local method="$2"

  # Enforce quota
  if ! quota_enforce "api" 1; then
    # Hard limit exceeded - return 429
    echo "HTTP/1.1 429 Too Many Requests"
    echo "Content-Type: application/json"
    echo "Retry-After: 3600"  # 1 hour
    echo ""
    echo '{
      "error": "quota_exceeded",
      "message": "API request quota exceeded. Upgrade your plan to continue.",
      "quota": 10000,
      "used": 10000,
      "reset_at": "2026-02-01T00:00:00Z",
      "upgrade_url": "https://yourdomain.com/billing/plans"
    }'
    return 1
  fi

  # Process request
  process_api_request "$endpoint" "$method"

  # Track usage
  usage_track_api_request "$endpoint" "$method" 200

  return 0
}

# Cache quota limits (rarely change)
declare -A QUOTA_LIMITS_CACHE
declare -A QUOTA_MODES_CACHE

quota_get_limit() {
  local service="$1"

  # Check cache
  if [ -n "${QUOTA_LIMITS_CACHE[$service]}" ]; then
    echo "${QUOTA_LIMITS_CACHE[$service]}"
    return 0
  fi

  # Cache miss - query database
  local limit
  limit=$(psql -tAc "
    SELECT q.limit_value
    FROM billing_quotas q
    JOIN billing_subscriptions s ON q.plan_name = s.plan_name
    WHERE s.customer_id = '$CUSTOMER_ID'
      AND q.service_name = '$service'
      AND s.status = 'active'
    LIMIT 1
  ")

  # Cache for 5 minutes
  QUOTA_LIMITS_CACHE[$service]="$limit"
  (sleep 300 && unset QUOTA_LIMITS_CACHE[$service]) &

  echo "$limit"
}

quota_get_current_usage() {
  local service="$1"
  local period_start
  period_start=$(date -u +%Y-%m-01)  # Current month

  # Try Redis first
  local redis_key="usage:${CUSTOMER_ID}:${service}:${period_start}"
  local usage
  usage=$(redis-cli GET "$redis_key" 2>/dev/null)

  if [ -n "$usage" ]; then
    echo "$usage"
    return 0
  fi

  # Redis miss - query PostgreSQL
  usage=$(psql -tAc "
    SELECT COALESCE(SUM(quantity), 0)
    FROM billing_usage_records
    WHERE customer_id = '$CUSTOMER_ID'
      AND service_name = '$service'
      AND recorded_at >= '$period_start'
  ")

  # Cache in Redis (expire at end of month)
  local expire_at
  expire_at=$(date -u -d "$(date +%Y-%m-01) +1 month" +%s)
  local ttl=$((expire_at - $(date +%s)))

  redis-cli SETEX "$redis_key" "$ttl" "$usage" >/dev/null 2>&1

  echo "$usage"
}

calculate_overage_charges() {
  local customer_id="$1"
  local period_start="$2"
  local period_end="$3"

  # Get subscription plan
  local plan_name
  plan_name=$(psql -tAc "
    SELECT plan_name FROM billing_subscriptions
    WHERE customer_id = '$customer_id'
      AND status = 'active'
    LIMIT 1
  ")

  # Calculate overages for all services
  psql -tAc "
    SELECT
      u.service_name,
      SUM(u.quantity) AS usage,
      q.limit_value AS quota,
      GREATEST(0, SUM(u.quantity) - q.limit_value) AS overage,
      q.overage_price,
      GREATEST(0, SUM(u.quantity) - q.limit_value) * q.overage_price AS charge
    FROM billing_usage_records u
    JOIN billing_quotas q ON q.service_name = u.service_name AND q.plan_name = '$plan_name'
    WHERE u.customer_id = '$customer_id'
      AND u.recorded_at >= '$period_start'
      AND u.recorded_at < '$period_end'
      AND q.enforcement_mode = 'soft'
      AND q.overage_price IS NOT NULL
    GROUP BY u.service_name, q.limit_value, q.overage_price
    HAVING SUM(u.quantity) > q.limit_value
  " | while IFS='|' read -r service usage quota overage price charge; do
    printf "Service: %s\n" "$service"
    printf "  Usage: %s\n" "$usage"
    printf "  Quota: %s\n" "$quota"
    printf "  Overage: %s\n" "$overage"
    printf "  Price: $%s per unit\n" "$price"
    printf "  Charge: $%s\n" "$charge"
    echo
  done
}

calculate_invoice_line_items() {
  local customer_id="$1"
  local period_start="$2"
  local period_end="$3"

  # 1. Get subscription price
  local plan_name
  plan_name=$(psql -tAc "
    SELECT plan_name FROM billing_subscriptions
    WHERE customer_id = '$customer_id' AND status = 'active'
    LIMIT 1
  ")

  local base_price
  base_price=$(psql -tAc "
    SELECT price_monthly FROM billing_plans WHERE plan_name = '$plan_name'
  ")

  echo "Subscription: $plan_name - \$$base_price"

  # 2. Calculate usage overages
  local overage_total=0
  while IFS='|' read -r service usage quota overage price charge; do
    if (( $(echo "$overage > 0" | bc -l) )); then
      echo "Overage: $service - $overage units × \$$price = \$$charge"
      overage_total=$(awk "BEGIN {print $overage_total + $charge}")
    fi
  done < <(psql -tAF'|' -c "
    SELECT
      u.service_name,
      SUM(u.quantity) AS usage,
      q.limit_value AS quota,
      GREATEST(0, SUM(u.quantity) - q.limit_value) AS overage,
      q.overage_price,
      GREATEST(0, SUM(u.quantity) - q.limit_value) * q.overage_price AS charge
    FROM billing_usage_records u
    JOIN billing_quotas q ON q.service_name = u.service_name AND q.plan_name = '$plan_name'
    WHERE u.customer_id = '$customer_id'
      AND u.recorded_at >= '$period_start'
      AND u.recorded_at < '$period_end'
      AND q.enforcement_mode = 'soft'
      AND q.overage_price IS NOT NULL
    GROUP BY u.service_name, q.limit_value, q.overage_price
    HAVING SUM(u.quantity) > q.limit_value
  ")

  # 3. Subtotal
  local subtotal
  subtotal=$(awk "BEGIN {print $base_price + $overage_total}")
  echo "Subtotal: \$$subtotal"

  # 4. Tax (if applicable)
  local tax_rate="${BILLING_TAX_RATE:-0}"
  local tax_amount
  tax_amount=$(awk "BEGIN {print $subtotal * $tax_rate}")
  echo "Tax (${tax_rate}%): \$$tax_amount"

  # 5. Total
  local total
  total=$(awk "BEGIN {print $subtotal + $tax_amount}")
  echo "Total: \$$total"

  echo "$total"
}

calculate_proration() {
  local old_price="$1"
  local new_price="$2"
  local period_start="$3"
  local period_end="$4"
  local change_date="$5"

  # Calculate full period duration (seconds)
  local period_duration
  period_duration=$(( $(date -d "$period_end" +%s) - $(date -d "$period_start" +%s) ))

  # Calculate remaining time
  local remaining_duration
  remaining_duration=$(( $(date -d "$period_end" +%s) - $(date -d "$change_date" +%s) ))

  # Calculate used time
  local used_duration
  used_duration=$(( period_duration - remaining_duration ))

  # Proration amounts
  local used_amount
  used_amount=$(awk "BEGIN {print $old_price * $used_duration / $period_duration}")

  local remaining_amount
  remaining_amount=$(awk "BEGIN {print $new_price * $remaining_duration / $period_duration}")

  # Credit/charge
  local proration
  proration=$(awk "BEGIN {print $remaining_amount - ($old_price - $used_amount)}")

  printf "Used: \$%.2f (%d days)\n" "$used_amount" "$((used_duration / 86400))"
  printf "Remaining: \$%.2f (%d days)\n" "$remaining_amount" "$((remaining_duration / 86400))"
  printf "Proration: \$%.2f\n" "$proration"

  echo "$proration"
}

# Enable Stripe Tax
STRIPE_TAX_ENABLED=true
STRIPE_TAX_INCLUSIVE=false  # Show tax separately

# Stripe calculates tax automatically based on:
# - Customer location (IP or billing address)
# - Product taxability
# - Local tax rates

calculate_tax() {
  local subtotal="$1"
  local customer_region="$2"  # US-CA, US-NY, etc.

  # Tax rates by region
  declare -A TAX_RATES
  TAX_RATES["US-CA"]=0.0725   # California: 7.25%
  TAX_RATES["US-NY"]=0.04     # New York: 4%
  TAX_RATES["US-TX"]=0.0625   # Texas: 6.25%
  TAX_RATES["CA-ON"]=0.13     # Ontario: 13% HST
  TAX_RATES["GB"]=0.20        # UK: 20% VAT
  TAX_RATES["EU"]=0.19        # EU average: 19% VAT

  local tax_rate="${TAX_RATES[$customer_region]:-0}"
  local tax_amount
  tax_amount=$(awk "BEGIN {print $subtotal * $tax_rate}")

  echo "$tax_amount"
}

# Tax-exclusive (US model)
# Price: $99.00
# Tax: $8.41 (8.5%)
# Total: $107.41

# Tax-inclusive (EU model)
# Price: €99.00 (includes 19% VAT)
# VAT amount: €15.79
# Net price: €83.21

generate_invoice_pdf() {
  local invoice_id="$1"
  local output_file="${2:-${invoice_id}.pdf}"

  # Get invoice data from Stripe
  local invoice_data
  invoice_data=$(stripe_api_call GET "/v1/invoices/$invoice_id")

  # Parse invoice fields
  local customer_name
  customer_name=$(echo "$invoice_data" | jq -r '.customer_name')
  local customer_email
  customer_email=$(echo "$invoice_data" | jq -r '.customer_email')
  local total
  total=$(echo "$invoice_data" | jq -r '.total / 100')
  local created
  created=$(echo "$invoice_data" | jq -r '.created' | xargs -I{} date -d @{} '+%Y-%m-%d')

  # Generate HTML
  cat > /tmp/invoice.html <<EOF
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Invoice $invoice_id</title>
  <style>
    body { font-family: Arial, sans-serif; margin: 40px; }
    .header { border-bottom: 2px solid #333; padding-bottom: 20px; }
    .company { font-size: 24px; font-weight: bold; }
    .invoice-info { margin: 20px 0; }
    .line-items { width: 100%; border-collapse: collapse; margin: 20px 0; }
    .line-items th, .line-items td { border-bottom: 1px solid #ddd; padding: 10px; text-align: left; }
    .total { font-size: 18px; font-weight: bold; text-align: right; }
  </style>
</head>
<body>
  <div class="header">
    <div class="company">Your Company Name</div>
    <div>123 Main St, San Francisco, CA 94102</div>
  </div>

  <div class="invoice-info">
    <h2>Invoice</h2>
    <p><strong>Invoice ID:</strong> $invoice_id</p>
    <p><strong>Date:</strong> $created</p>
    <p><strong>Bill To:</strong><br>
       $customer_name<br>
       $customer_email
    </p>
  </div>

  <table class="line-items">
    <thead>
      <tr>
        <th>Description</th>
        <th>Quantity</th>
        <th>Unit Price</th>
        <th>Amount</th>
      </tr>
    </thead>
    <tbody>
EOF

  # Add line items
  echo "$invoice_data" | jq -r '.lines.data[] | "\(.description)|\(.quantity)|\(.amount / 100)"' | \
  while IFS='|' read -r desc qty amount; do
    cat >> /tmp/invoice.html <<EOF
      <tr>
        <td>$desc</td>
        <td>$qty</td>
        <td>\$$amount</td>
        <td>\$$(awk "BEGIN {print $qty * $amount}")</td>
      </tr>
EOF
  done

  cat >> /tmp/invoice.html <<EOF
    </tbody>
  </table>

  <div class="total">
    <p>Total: \$$total</p>
  </div>

  <p style="margin-top: 40px; font-size: 12px; color: #666;">
    Thank you for your business!
  </p>
</body>
</html>
EOF

  # Convert to PDF
  wkhtmltopdf /tmp/invoice.html "$output_file"

  echo "Invoice PDF generated: $output_file"
}

send_invoice_email() {
  local customer_email="$1"
  local invoice_id="$2"
  local invoice_pdf="$3"
  local total="$4"

  # Compose email
  local subject="Invoice $invoice_id from Your Company"
  local body="Dear Customer,

Thank you for your business. Your invoice is ready.

Invoice ID: $invoice_id
Amount Due: \$$total

Please find the attached invoice PDF.

If you have any questions, please contact [email protected].

Best regards,
Your Company Team"

  # Send via MailPit/SMTP
  echo "$body" | mail -s "$subject" -a "$invoice_pdf" "$customer_email"

  # Or use Stripe-hosted invoice
  local invoice_url
  invoice_url=$(stripe_api_call GET "/v1/invoices/$invoice_id" | jq -r '.hosted_invoice_url')

  echo "Invoice email sent to $customer_email"
  echo "View online: $invoice_url"
}

subscription_state_transition() {
  local subscription_id="$1"
  local from_state="$2"
  local to_state="$3"
  local reason="$4"

  # Log transition
  psql -c "
    INSERT INTO billing_subscription_events
      (subscription_id, from_state, to_state, reason, occurred_at)
    VALUES
      ('$subscription_id', '$from_state', '$to_state', '$reason', NOW())
  "

  # Update subscription status
  psql -c "
    UPDATE billing_subscriptions
    SET status = '$to_state', updated_at = NOW()
    WHERE subscription_id = '$subscription_id'
  "

  # Trigger side effects
  case "$to_state" in
    active)
      provision_resources "$subscription_id"
      send_activation_email "$subscription_id"
      ;;
    past_due)
      send_payment_failed_email "$subscription_id"
      schedule_retry "$subscription_id"
      ;;
    canceled)
      deprovision_resources "$subscription_id"
      send_cancellation_email "$subscription_id"
      ;;
    trialing)
      provision_trial_resources "$subscription_id"
      send_trial_welcome_email "$subscription_id"
      ;;
  esac
}

upgrade_subscription() {
  local subscription_id="$1"
  local new_plan="$2"

  # Get new plan price
  local new_price_id
  new_price_id=$(psql -tAc "
    SELECT stripe_price_id FROM billing_plans WHERE plan_name = '$new_plan'
  ")

  # Update in Stripe (with proration)
  stripe_api_call POST "/v1/subscriptions/$subscription_id" \
    -d "items[0][price]=$new_price_id" \
    -d "proration_behavior=create_prorations"

  # Sync to database (webhook will update)
  echo "Subscription upgraded to $new_plan"
}

downgrade_subscription() {
  local subscription_id="$1"
  local new_plan="$2"

  # Get new plan price
  local new_price_id
  new_price_id=$(psql -tAc "
    SELECT stripe_price_id FROM billing_plans WHERE plan_name = '$new_plan'
  ")

  # Schedule downgrade for end of period
  stripe_api_call POST "/v1/subscriptions/$subscription_id" \
    -d "items[0][price]=$new_price_id" \
    -d "proration_behavior=none" \
    -d "billing_cycle_anchor=unchanged"

  echo "Subscription will downgrade to $new_plan at period end"
}

create_subscription_with_trial() {
  local customer_id="$1"
  local plan="$2"
  local trial_days="${3:-14}"

  # Get plan price
  local price_id
  price_id=$(psql -tAc "
    SELECT stripe_price_id FROM billing_plans WHERE plan_name = '$plan'
  ")

  # Calculate trial end
  local trial_end
  trial_end=$(date -u -d "+${trial_days} days" +%s)

  # Create subscription
  stripe_api_call POST /v1/subscriptions \
    -d "customer=$customer_id" \
    -d "items[0][price]=$price_id" \
    -d "trial_end=$trial_end" \
    -d "trial_settings[end_behavior][missing_payment_method]=cancel"

  echo "Trial subscription created (ends in $trial_days days)"
}

handle_trial_ending() {
  local subscription_data="$1"

  local subscription_id
  subscription_id=$(echo "$subscription_data" | jq -r '.id')
  local customer_id
  customer_id=$(echo "$subscription_data" | jq -r '.customer')
  local trial_end
  trial_end=$(echo "$subscription_data" | jq -r '.trial_end')

  # Send trial ending notification (3 days before)
  if [ $((trial_end - $(date +%s))) -le 259200 ]; then
    send_trial_ending_email "$customer_id" "$trial_end"
  fi
}

# Webhook: customer.subscription.trial_will_end
handle_trial_will_end() {
  local subscription_data="$1"

  local customer_id
  customer_id=$(echo "$subscription_data" | jq -r '.customer')
  local subscription_id
  subscription_id=$(echo "$subscription_data" | jq -r '.id')

  # Check if payment method on file
  local has_payment_method
  has_payment_method=$(stripe_api_call GET "/v1/customers/$customer_id" | \
    jq -r '.invoice_settings.default_payment_method != null')

  if [ "$has_payment_method" = "false" ]; then
    # No payment method - send urgent email
    send_payment_method_required_email "$customer_id"
  else
    # Payment method on file - send conversion reminder
    send_trial_ending_reminder_email "$customer_id"
  fi
}

# Webhook: invoice.payment_failed
handle_payment_failed() {
  local invoice_data="$1"

  local subscription_id
  subscription_id=$(echo "$invoice_data" | jq -r '.subscription')
  local attempt_count
  attempt_count=$(echo "$invoice_data" | jq -r '.attempt_count')
  local customer_email
  customer_email=$(echo "$invoice_data" | jq -r '.customer_email')

  # Update subscription status
  psql -c "
    UPDATE billing_subscriptions
    SET status = 'past_due'
    WHERE subscription_id = '$subscription_id'
  "

  # Send dunning email based on attempt
  case $attempt_count in
    1)
      send_email "$customer_email" "payment_failed_1"
      schedule_retry "$subscription_id" 3  # Retry in 3 days
      ;;
    2)
      send_email "$customer_email" "payment_failed_2"
      schedule_retry "$subscription_id" 2  # Retry in 2 days
      ;;
    3)
      send_email "$customer_email" "payment_failed_final"
      schedule_retry "$subscription_id" 2  # Final retry in 2 days
      ;;
    4)
      # Final failure - downgrade
      send_email "$customer_email" "subscription_canceled"
      downgrade_to_free "$subscription_id"
      ;;
  esac
}

# Allow access for 7 days after payment failure
check_subscription_access() {
  local subscription_id="$1"

  local status last_payment_failure
  IFS='|' read -r status last_payment_failure <<< "$(psql -tAF'|' -c "
    SELECT status, last_payment_failed_at
    FROM billing_subscriptions
    WHERE subscription_id = '$subscription_id'
  ")"

  # Active status - allow access
  if [ "$status" = "active" ]; then
    return 0
  fi

  # Past due - check grace period
  if [ "$status" = "past_due" ]; then
    local grace_period_end
    grace_period_end=$(date -d "$last_payment_failure +7 days" +%s)
    local now
    now=$(date +%s)

    if [ $now -le $grace_period_end ]; then
      return 0  # Within grace period
    fi
  fi

  # Denied
  return 1
}

cancel_subscription() {
  local subscription_id="$1"

  # Cancel at period end (default)
  stripe_api_call POST "/v1/subscriptions/$subscription_id" \
    -d "cancel_at_period_end=true"

  # Update database
  psql -c "
    UPDATE billing_subscriptions
    SET cancel_at_period_end = TRUE
    WHERE subscription_id = '$subscription_id'
  "

  echo "Subscription will cancel at end of current period"
}

cancel_subscription_immediately() {
  local subscription_id="$1"

  # Cancel immediately
  stripe_api_call DELETE "/v1/subscriptions/$subscription_id"

  # Update database
  psql -c "
    UPDATE billing_subscriptions
    SET status = 'canceled'
    WHERE subscription_id = '$subscription_id'
  "

  echo "Subscription canceled immediately"
}

refund_invoice() {
  local invoice_id="$1"
  local amount="${2:-}"  # Full refund if not specified

  # Get payment intent from invoice
  local payment_intent
  payment_intent=$(stripe_api_call GET "/v1/invoices/$invoice_id" | \
    jq -r '.payment_intent')

  # Create refund
  if [ -n "$amount" ]; then
    stripe_api_call POST /v1/refunds \
      -d "payment_intent=$payment_intent" \
      -d "amount=$amount"
  else
    stripe_api_call POST /v1/refunds \
      -d "payment_intent=$payment_intent"
  fi

  echo "Refund created for invoice $invoice_id"
}

# NEVER commit secrets to git
# Use environment variables or secrets manager

# .env (gitignored)
STRIPE_SECRET_KEY=sk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...

# Production: Use secrets manager
# AWS Secrets Manager
aws secretsmanager get-secret-value --secret-id stripe-api-key

# HashiCorp Vault
vault kv get secret/billing/stripe-api-key

# Rotate API keys quarterly
rotate_stripe_api_key() {
  # 1. Create new restricted key in Stripe Dashboard
  # 2. Update environment variable
  export STRIPE_SECRET_KEY="sk_live_NEW_KEY"

  # 3. Test new key
  if stripe_api_call GET /v1/customers/cus_test >/dev/null 2>&1; then
    echo "New key validated"
  else
    echo "New key failed - rolling back"
    export STRIPE_SECRET_KEY="sk_live_OLD_KEY"
    return 1
  fi

  # 4. Update secrets manager
  aws secretsmanager update-secret \
    --secret-id stripe-api-key \
    --secret-string "$STRIPE_SECRET_KEY"

  # 5. Revoke old key in Stripe Dashboard
}

# Create separate keys for different environments
# Test mode:
STRIPE_SECRET_KEY=your_stripe_test_secret_key_here  # Full permissions for development

# Production:
# - Billing service: Limited to customers, subscriptions, invoices
# - Frontend: Publishable key only (pk_live_...)
# - Webhooks: Webhook secret (whsec_...)

verify_stripe_webhook() {
  local payload="$1"
  local signature_header="$2"
  local webhook_secret="$STRIPE_WEBHOOK_SECRET"

  # Extract timestamp and signature
  local timestamp
  timestamp=$(echo "$signature_header" | grep -oP 't=\K[^,]+')
  local expected_sig
  expected_sig=$(echo "$signature_header" | grep -oP 'v1=\K[^,]+')

  # Check timestamp (reject if > 5 minutes old)
  local current_time
  current_time=$(date +%s)
  if [ $((current_time - timestamp)) -gt 300 ]; then
    echo "Error: Webhook too old" >&2
    return 1
  fi

  # Compute signature
  local signed_payload="${timestamp}.${payload}"
  local computed_sig
  computed_sig=$(echo -n "$signed_payload" | \
    openssl dgst -sha256 -hmac "$webhook_secret" -binary | \
    xxd -p -c 256)

  # Constant-time comparison (prevent timing attacks)
  if ! compare_constant_time "$computed_sig" "$expected_sig"; then
    echo "Error: Invalid signature" >&2
    return 1
  fi

  return 0
}

# Constant-time string comparison
compare_constant_time() {
  local a="$1"
  local b="$2"

  [ "$a" = "$b" ]
}

# Always use SSL/TLS for PostgreSQL connections
psql "postgresql://user:pass@host:5432/db?sslmode=require"

# Stripe API always uses HTTPS (enforced)

# Generate strong encryption key
openssl rand -base64 32 > /etc/nself/billing_encryption_key

# Encrypt sensitive metadata before storing
encrypt_metadata() {
  local plaintext="$1"
  local key_file="/etc/nself/billing_encryption_key"

  echo -n "$plaintext" | \
    openssl enc -aes-256-cbc -a -salt -pass file:"$key_file"
}

decrypt_metadata() {
  local ciphertext="$1"
  local key_file="/etc/nself/billing_encryption_key"

  echo -n "$ciphertext" | \
    openssl enc -aes-256-cbc -d -a -pass file:"$key_file"
}

audit_log() {
  local event_type="$1"
  local customer_id="$2"
  local action="$3"
  local changes="$4"

  psql -c "
    INSERT INTO billing_audit_log
      (event_type, customer_id, action, changes)
    VALUES
      ('$event_type', '$customer_id', '$action', '$changes'::jsonb)
  "
}

# Usage
audit_log "subscription" "cus_123" "upgrade" '{"from":"pro","to":"enterprise"}'
audit_log "invoice" "cus_123" "refund" '{"invoice_id":"in_456","amount":99.00}'

# Keep audit logs for 7 years (compliance requirement)
# Archive old logs to cold storage
psql -c "
  INSERT INTO billing_audit_log_archive
  SELECT * FROM billing_audit_log
  WHERE occurred_at < NOW() - INTERVAL '1 year'
"

psql -c "
  DELETE FROM billing_audit_log
  WHERE occurred_at < NOW() - INTERVAL '1 year'
"

# Layer 1: In-memory cache
declare -A QUOTA_CACHE
declare -A USAGE_CACHE
CACHE_TTL=300  # 5 minutes

get_cached_quota() {
  local key="$1"
  local cache_time="${QUOTA_CACHE_TIME[$key]}"
  local current_time
  current_time=$(date +%s)

  # Check if cached and not expired
  if [ -n "$cache_time" ] && [ $((current_time - cache_time)) -lt $CACHE_TTL ]; then
    echo "${QUOTA_CACHE[$key]}"
    return 0
  fi

  # Cache miss - fetch from Layer 2
  local value
  value=$(get_quota_from_redis "$key")

  if [ -n "$value" ]; then
    # Cache in memory
    QUOTA_CACHE[$key]="$value"
    QUOTA_CACHE_TIME[$key]="$current_time"
    echo "$value"
    return 0
  fi

  # Fetch from database
  value=$(get_quota_from_db "$key")
  QUOTA_CACHE[$key]="$value"
  QUOTA_CACHE_TIME[$key]="$current_time"

  # Also cache in Redis
  redis-cli SETEX "quota:$key" "$CACHE_TTL" "$value" >/dev/null 2>&1

  echo "$value"
}

# Layer 2: Redis cache
get_quota_from_redis() {
  local key="$1"
  redis-cli GET "quota:$key" 2>/dev/null || echo ""
}

# Layer 3: Materialized view
get_usage_from_materialized_view() {
  local customer_id="$1"
  local service="$2"

  psql -tAc "
    SELECT current_usage
    FROM billing_current_usage
    WHERE customer_id = '$customer_id'
      AND service_name = '$service'
      AND period_start = DATE_TRUNC('month', NOW())
  "
}

# Layer 4: Raw table aggregation
get_usage_from_raw_table() {
  local customer_id="$1"
  local service="$2"

  psql -tAc "
    SELECT COALESCE(SUM(quantity), 0)
    FROM billing_usage_records
    WHERE customer_id = '$customer_id'
      AND service_name = '$service'
      AND recorded_at >= DATE_TRUNC('month', NOW())
  "
}

# PgBouncer configuration
# /etc/pgbouncer/pgbouncer.ini

[databases]
nself = host=127.0.0.1 port=5432 dbname=nself

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

# Use PgBouncer in application
psql -h 127.0.0.1 -p 6432 -U billing_user -d nself

# Buffer usage events and insert in batches
USAGE_BUFFER="/tmp/usage_buffer.csv"

buffer_usage_event() {
  local customer_id="$1"
  local service="$2"
  local quantity="$3"
  local metadata="$4"
  local timestamp="$(date -u +%Y-%m-%dT%H:%M:%SZ)"

  # Append to CSV buffer
  echo "$customer_id,$service,$quantity,\"$metadata\",$timestamp" >> "$USAGE_BUFFER"

  # Flush if buffer size exceeds threshold
  local buffer_size
  buffer_size=$(wc -l < "$USAGE_BUFFER")
  if [ $buffer_size -ge 1000 ]; then
    flush_usage_buffer
  fi
}

flush_usage_buffer() {
  if [ ! -f "$USAGE_BUFFER" ] || [ ! -s "$USAGE_BUFFER" ]; then
    return
  fi

  # Bulk insert using COPY
  psql -c "
    COPY billing_usage_records (customer_id, service_name, quantity, metadata, recorded_at)
    FROM '$USAGE_BUFFER'
    WITH (FORMAT csv, DELIMITER ',', QUOTE '\"')
  "

  # Clear buffer
  > "$USAGE_BUFFER"
}

# Cron job flushes buffer every minute
* * * * * /usr/local/bin/flush_usage_buffer.sh

# Process large datasets in parallel
process_invoices_parallel() {
  local month="$1"
  local parallelism=4

  # Get all customer IDs
  psql -tAc "
    SELECT DISTINCT customer_id
    FROM billing_subscriptions
    WHERE status = 'active'
  " | xargs -P $parallelism -I {} bash -c '
    generate_invoice_for_customer "{}" "'$month'"
  '
}

# Rate limit billing API calls
rate_limit_check() {
  local customer_id="$1"
  local limit=100  # 100 requests per minute
  local window=60  # 60 seconds

  local key="rate_limit:billing:${customer_id}"
  local current_count
  current_count=$(redis-cli GET "$key" 2>/dev/null || echo "0")

  if [ "$current_count" -ge "$limit" ]; then
    echo "Rate limit exceeded" >&2
    return 1
  fi

  # Increment counter
  redis-cli INCR "$key" >/dev/null
  redis-cli EXPIRE "$key" "$window" >/dev/null

  return 0
}

# Usage in API handler
handle_billing_api_request() {
  local customer_id="$1"

  if ! rate_limit_check "$customer_id"; then
    echo "HTTP/1.1 429 Too Many Requests"
    echo "Retry-After: 60"
    return 1
  fi

  # Process request
  ...
}

# Respect Stripe's rate limits (100 req/sec)
STRIPE_RATE_LIMIT=100
STRIPE_RATE_WINDOW=1  # second
STRIPE_REQUEST_COUNT=0
STRIPE_WINDOW_START=$(date +%s)

stripe_api_call_with_rate_limit() {
  local current_time
  current_time=$(date +%s)

  # Reset counter if window expired
  if [ $((current_time - STRIPE_WINDOW_START)) -ge $STRIPE_RATE_WINDOW ]; then
    STRIPE_REQUEST_COUNT=0
    STRIPE_WINDOW_START=$current_time
  fi

  # Check rate limit
  if [ $STRIPE_REQUEST_COUNT -ge $STRIPE_RATE_LIMIT ]; then
    # Wait until next window
    sleep $((STRIPE_RATE_WINDOW - (current_time - STRIPE_WINDOW_START)))
    STRIPE_REQUEST_COUNT=0
    STRIPE_WINDOW_START=$(date +%s)
  fi

  # Increment counter
  STRIPE_REQUEST_COUNT=$((STRIPE_REQUEST_COUNT + 1))

  # Make API call
  stripe_api_call "$@"
}

# Check customer's payment method
debug_payment_failure() {
  local customer_id="$1"

  echo "=== Customer Info ==="
  stripe_api_call GET "/v1/customers/$customer_id" | jq '{
    id, email, default_source, invoice_settings
  }'

  echo "=== Recent Failed Invoices ==="
  stripe_api_call GET "/v1/invoices?customer=$customer_id&status=uncollectible" | \
    jq '.data[] | {id, amount_due, attempted, attempt_count, next_payment_attempt}'

  echo "=== Payment Methods ==="
  stripe_api_call GET "/v1/customers/$customer_id/payment_methods" | \
    jq '.data[] | {id, type, card: .card | {brand, last4, exp_month, exp_year}}'
}

# Debug quota caching issues
debug_quota() {
  local customer_id="$1"
  local service="$2"

  echo "=== Database Usage ==="
  psql -c "
    SELECT SUM(quantity) AS db_usage
    FROM billing_usage_records
    WHERE customer_id = '$customer_id'
      AND service_name = '$service'
      AND recorded_at >= DATE_TRUNC('month', NOW())
  "

  echo "=== Redis Cache ==="
  redis-cli GET "usage:${customer_id}:${service}:$(date +%Y-%m)"

  echo "=== Materialized View ==="
  psql -c "
    SELECT current_usage, period_start
    FROM billing_current_usage
    WHERE customer_id = '$customer_id'
      AND service_name = '$service'
  "

  echo "=== Quota Limit ==="
  psql -c "
    SELECT q.limit_value, q.enforcement_mode
    FROM billing_quotas q
    JOIN billing_subscriptions s ON q.plan_name = s.plan_name
    WHERE s.customer_id = '$customer_id'
      AND q.service_name = '$service'
      AND s.status = 'active'
  "
}

# Debug webhook issues
debug_webhook() {
  local event_id="$1"

  echo "=== Event from Stripe ==="
  stripe_api_call GET "/v1/events/$event_id"

  echo "=== Local Event Record ==="
  psql -c "
    SELECT event_id, event_type, status, processed_at, payload
    FROM billing_webhook_events
    WHERE event_id = '$event_id'
  "

  echo "=== Recent Webhook Failures ==="
  psql -c "
    SELECT event_id, event_type, status, attempts, error_message
    FROM billing_webhook_events
    WHERE status IN ('failed', 'failed_permanent')
      AND occurred_at >= NOW() - INTERVAL '1 hour'
    ORDER BY occurred_at DESC
    LIMIT 10
  "
}

# Sync all invoices from Stripe
reconcile_invoices() {
  local sync_date="${1:-$(date -d yesterday +%Y-%m-%d)}"

  echo "Syncing invoices for $sync_date"

  # Fetch all invoices from Stripe
  local invoices
  invoices=$(stripe_api_call GET "/v1/invoices?created[gte]=$(date -d "$sync_date" +%s)&limit=100")

  echo "$invoices" | jq -c '.data[]' | while read -r invoice; do
    local invoice_id
    invoice_id=$(echo "$invoice" | jq -r '.id')
    local customer_id
    customer_id=$(echo "$invoice" | jq -r '.customer')
    local total_amount
    total_amount=$(echo "$invoice" | jq -r '.total / 100')
    local status
    status=$(echo "$invoice" | jq -r '.status')
    local period_start
    period_start=$(echo "$invoice" | jq -r '.period_start' | xargs -I{} date -d @{} '+%Y-%m-%d')
    local period_end
    period_end=$(echo "$invoice" | jq -r '.period_end' | xargs -I{} date -d @{} '+%Y-%m-%d')

    # Upsert to database
    psql -c "
      INSERT INTO billing_invoices (invoice_id, customer_id, total_amount, status, period_start, period_end)
      VALUES ('$invoice_id', '$customer_id', $total_amount, '$status', '$period_start', '$period_end')
      ON CONFLICT (invoice_id)
      DO UPDATE SET
        status = EXCLUDED.status,
        total_amount = EXCLUDED.total_amount
    "
  done

  echo "Reconciliation complete"
}

# Run daily at 2 AM
0 2 * * * /usr/local/bin/reconcile_invoices.sh