-- Per-region leakage: invoice failures minus dunning recovery
SELECT
  customer_country,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed_invoices,
  COUNT(*) FILTER (WHERE status = 'paid_after_retry') AS recovered,
  COUNT(*) FILTER (WHERE status = 'failed' AND dunning_exhausted = TRUE) AS net_leakage,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE status = 'failed' AND dunning_exhausted = TRUE)
    / COUNT(*),
    2
  ) AS leak_pct
FROM invoices
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01'
GROUP BY customer_country
ORDER BY leak_pct DESC;

// src/server/dunning/send-window.ts
import { db } from '~/db'
import { customers } from '~/db/schema'
import { eq } from 'drizzle-orm'

type Region = 'US' | 'EU' | 'UK' | 'BR' | 'JP'

const SEND_WINDOWS: Record<Region, { startHour: number; endHour: number; tz: string }> = {
  US: { startHour: 9,  endHour: 20, tz: 'America/New_York' },
  EU: { startHour: 9,  endHour: 17, tz: 'Europe/Berlin' },
  UK: { startHour: 9,  endHour: 17, tz: 'Europe/London' },
  BR: { startHour: 8,  endHour: 20, tz: 'America/Sao_Paulo' },
  JP: { startHour: 9,  endHour: 18, tz: 'Asia/Tokyo' },
}

export function nextSendWindow(region: Region, baseDate: Date = new Date()): Date {
  const window = SEND_WINDOWS[region]
  const localDate = new Date(baseDate.toLocaleString('en-US', { timeZone: window.tz }))
  localDate.setHours(window.startHour, 0, 0, 0)
  if (localDate < baseDate) localDate.setDate(localDate.getDate() + 1)
  return localDate
}

export async function scheduleDunningEmail(customerId: string, templateId: string) {
  const [customer] = await db.select().from(customers)
    .where(eq(customers.id, customerId)).limit(1)
  const region = inferRegion(customer.country, customer.paymentMethod)
  await queue.send('dunning.send', {
    customerId,
    templateId,
    sendAt: nextSendWindow(region).toISOString(),
  })
}

// src/server/billing/mandate-refresh.ts
import { stripe } from '~/lib/stripe'

export async function refreshExpiringMandates() {
  const expiringMandates = await db.select().from(paymentMandates)
    .where(lt(paymentMandates.expiresAt, addDays(new Date(), 60)))

  for (const mandate of expiringMandates) {
    // Send the customer an email with a fresh SetupIntent link.
    // Do NOT retry the old mandate — that always fails per SEPA scheme rules.
    const intent = await stripe.setupIntents.create({
      customer: mandate.stripeCustomerId,
      payment_method_types: ['sepa_debit'],
      usage: 'off_session',
      metadata: { mandate_refresh: 'true', mandate_id: mandate.id },
    })

    await sendEmail({
      to: mandate.customerEmail,
      template: 'sepa_mandate_refresh',
      data: {
        name: mandate.customerName,
        expiresAt: mandate.expiresAt.toISOString(),
        refreshUrl: `${PUBLIC_URL}/billing/mandate/refresh?cs=${intent.client_secret}`,
      },
    })

    await db.update(paymentMandates)
      .set({ refreshRequestedAt: new Date(), refreshIntentId: intent.id })
      .where(eq(paymentMandates.id, mandate.id))
  }
}

// src/server/billing/pix-retry.ts
import { stripe } from '~/lib/stripe'

export async function retryPixPayment(invoiceId: string) {
  const [invoice] = await db.select().from(invoices)
    .where(eq(invoices.id, invoiceId)).limit(1)

  // Fresh payment intent — NEVER reuse the prior Pix charge.
  // Banco Central reports 38% drop-off if the same QR is shown twice.
  const intent = await stripe.paymentIntents.create({
    amount: invoice.amountDue,
    currency: 'brl',
    customer: invoice.stripeCustomerId,
    payment_method_types: ['pix'],
    payment_method_options: {
      pix: { expires_after_seconds: 86400 },  // 24h window per Banco Central
    },
    metadata: { retry_of: invoice.id, region: 'BR' },
  })

  await db.insert(pixRetries).values({
    invoiceId,
    paymentIntentId: intent.id,
    expiresAt: new Date(Date.now() + 24 * 60 * 60 * 1000),
  })

  return intent
}

// src/server/stripe/webhook.ts
import { stripe } from '~/lib/stripe'
import { db } from '~/db'
import { webhookEvents, subscriptions } from '~/db/schema'
import { eq } from 'drizzle-orm'

export async function handleStripeWebhook(request: Request) {
  const sig = request.headers.get('stripe-signature')!
  const body = await request.text()
  const event = stripe.webhooks.constructEvent(
    body, sig, process.env.STRIPE_WEBHOOK_SECRET!
  )

  // PRIMARY KEY constraint enforces idempotency at the database layer.
  // No race window; no application-level check.
  try {
    await db.insert(webhookEvents).values({
      id: event.id,
      type: event.type,
      receivedAt: new Date(),
      status: 'pending',
    })
  } catch (e: any) {
    if (e.code === 'SQLITE_CONSTRAINT_PRIMARYKEY') {
      return new Response('ok', { status: 200 })  // already processed
    }
    throw e
  }

  // ... business logic, transactional ...

  await db.update(webhookEvents).set({ status: 'completed' })
    .where(eq(webhookEvents.id, event.id))
  return new Response('ok', { status: 200 })
}

// src/server/billing/retry-budget.ts
export const RETRY_BUDGET: Record<string, { maxAttempts: number; cooldownHours: number[] }> = {
  US: { maxAttempts: 4, cooldownHours: [0, 24, 72, 120] },         // card-friendly
  EU: { maxAttempts: 5, cooldownHours: [0, 24, 48, 96, 168] },     // SEPA mandate-friendly
  UK: { maxAttempts: 5, cooldownHours: [0, 24, 72, 120, 168] },    // BACS-friendly
  BR: { maxAttempts: 6, cooldownHours: [0, 6, 24, 48, 72, 120] },  // Pix requires fresh attempts
  JP: { maxAttempts: 4, cooldownHours: [0, 24, 48, 96] },          // Konbini-friendly
}