insforge--insforge/backend/tests/unit/stripe-services.test.ts

import { beforeEach, describe, expect, it, vi } from 'vitest';
import type {
  StripeAccount,
  StripeCharge,
  StripeInvoice,
  StripePaymentIntent,
  StripePrice,
  StripeProduct,
  StripeSubscription,
} from '../../src/types/payments';

const { mockPool, mockProvider, mockGetSecretByKey, mockEncrypt, mockLogger } = vi.hoisted(() => ({
  mockPool: {
    query: vi.fn(),
    connect: vi.fn(),
  },
  mockProvider: {
    retrieveAccount: vi.fn(),
    syncCatalog: vi.fn(),
    createProduct: vi.fn(),
    updateProduct: vi.fn(),
    deleteProduct: vi.fn(),
    createPrice: vi.fn(),
    updatePrice: vi.fn(),
    createCustomer: vi.fn(),
    listCustomers: vi.fn(),
    createCustomerPortalSession: vi.fn(),
    createCheckoutSession: vi.fn(),
    constructWebhookEvent: vi.fn(),
    listWebhookEndpoints: vi.fn(),
    createWebhookEndpoint: vi.fn(),
    deleteWebhookEndpoint: vi.fn(),
    listSubscriptions: vi.fn(),
    listSubscriptionItems: vi.fn(),
    retrievePaymentIntent: vi.fn(),
    retrieveCharge: vi.fn(),
    retrieveInvoiceByPaymentIntent: vi.fn(),
  },
  mockGetSecretByKey: vi.fn(),
  mockEncrypt: vi.fn(),
  mockLogger: { info: vi.fn(), warn: vi.fn(), error: vi.fn(), debug: vi.fn() },
}));

vi.mock('../../src/infra/database/database.manager', () => ({
  DatabaseManager: {
    getInstance: () => ({
      getPool: () => mockPool,
    }),
  },
}));

vi.mock('../../src/providers/payments/stripe.provider', () => ({
  StripeProvider: vi.fn(function () {
    return mockProvider;
  }),
  maskStripeKey: (apiKey: string) => `masked:${apiKey.slice(-4)}`,
  validateStripeSecretKey: (environment: 'test' | 'live', value: string) => {
    const prefix = environment === 'test' ? 'sk_test_' : 'sk_live_';
    if (!value.startsWith(prefix)) {
      throw new Error(`STRIPE_${environment.toUpperCase()}_SECRET_KEY must start with ${prefix}`);
    }
  },
}));

vi.mock('../../src/services/secrets/secret.service', () => ({
  SecretService: {
    getInstance: () => ({
      getSecretByKey: mockGetSecretByKey,
    }),
  },
}));

vi.mock('../../src/infra/security/encryption.manager', () => ({
  EncryptionManager: {
    encrypt: mockEncrypt,
  },
}));

vi.mock('../../src/utils/logger', () => ({ default: mockLogger }));

function expectStripeScopedDelete(
  queryMock: typeof mockPool.query,
  tableName: string,
  environment: string
) {
  expect(queryMock).toHaveBeenCalledWith(
    expect.stringMatching(
      new RegExp(
        `DELETE FROM payments\\.${tableName}[\\s\\S]*provider\\s*=\\s*(?:'stripe'|\\$2)`,
        'i'
      )
    ),
    [environment, 'stripe']
  );
}

function expectStripeRuntimeDelete(
  queryMock: typeof mockPool.query,
  tableName: string,
  environment: string
) {
  expect(queryMock).toHaveBeenCalledWith(
    expect.stringMatching(
      new RegExp(`DELETE FROM payments\\.${tableName}[\\s\\S]*environment\\s*=\\s*\\$1`, 'i')
    ),
    [environment]
  );
}

function expectProviderRuntimeDelete(
  queryMock: typeof mockPool.query,
  tableName: string,
  environment: string,
  provider: string
) {
  expect(queryMock).toHaveBeenCalledWith(
    expect.stringMatching(
      new RegExp(`DELETE FROM payments\\.${tableName}[\\s\\S]*provider\\s*=\\s*\\$1`, 'i')
    ),
    [provider, environment]
  );
}

import { PaymentCustomerService } from '../../src/services/payments/payment-customer.service';
import { StripeCheckoutService } from '../../src/services/payments/stripe/checkout.service';
import { StripeConfigService } from '../../src/services/payments/stripe/config.service';
import { StripeCustomerPortalService } from '../../src/services/payments/stripe/customer-portal.service';
import { PaymentTransactionService } from '../../src/services/payments/transaction.service';
import { StripePriceService } from '../../src/services/payments/stripe/price.service';
import { StripeProductService } from '../../src/services/payments/stripe/product.service';
import { StripeSubscriptionService } from '../../src/services/payments/stripe/subscription.service';
import { StripeSyncService } from '../../src/services/payments/stripe/sync.service';
import { StripeWebhookService } from '../../src/services/payments/stripe/webhook.service';

const stripeServices = {
  getConfig: () => StripeConfigService.getInstance().getConfig(),
  setStripeSecretKey: (environment: 'test' | 'live', secretKey: string) =>
    StripeConfigService.getInstance().setStripeSecretKey(
      environment,
      secretKey,
      async (syncEnvironment, provider) => {
        await StripeSyncService.getInstance().syncPaymentsEnvironmentAfterKeyChange(
          syncEnvironment,
          provider
        );
      }
    ),
  removeStripeSecretKey: (environment: 'test' | 'live') =>
    StripeConfigService.getInstance().removeStripeSecretKey(environment),
  seedStripeKeysFromEnv: () => StripeSyncService.getInstance().seedStripeKeysFromEnv(),
  getStatus: () => StripeConfigService.getInstance().getStatus(),
  configureWebhook: async (environment: 'test' | 'live') => ({
    connection: await StripeConfigService.getInstance().configureManagedStripeWebhook(environment),
  }),
  syncPayments: (input: { environment: 'test' | 'live' | 'all' }) =>
    StripeSyncService.getInstance().syncPayments(input),
  listProducts: (input: { environment: 'test' | 'live' }) =>
    StripeProductService.getInstance().listProducts(input),
  getProduct: (environment: 'test' | 'live', productId: string) =>
    StripeProductService.getInstance().getProduct(environment, productId),
  createProduct: (input: Parameters<StripeProductService['createProduct']>[0]) =>
    StripeProductService.getInstance().createProduct(input),
  updateProduct: (productId: string, input: Parameters<StripeProductService['updateProduct']>[1]) =>
    StripeProductService.getInstance().updateProduct(productId, input),
  deleteProduct: (environment: 'test' | 'live', productId: string) =>
    StripeProductService.getInstance().deleteProduct(environment, productId),
  listPrices: (input: Parameters<StripePriceService['listPrices']>[0]) =>
    StripePriceService.getInstance().listPrices(input),
  getPrice: (environment: 'test' | 'live', priceId: string) =>
    StripePriceService.getInstance().getPrice(environment, priceId),
  createPrice: (input: Parameters<StripePriceService['createPrice']>[0]) =>
    StripePriceService.getInstance().createPrice(input),
  updatePrice: (priceId: string, input: Parameters<StripePriceService['updatePrice']>[1]) =>
    StripePriceService.getInstance().updatePrice(priceId, input),
  archivePrice: (environment: 'test' | 'live', priceId: string) =>
    StripePriceService.getInstance().archivePrice(environment, priceId),
  createCheckoutSession: (
    input: Parameters<StripeCheckoutService['createCheckoutSession']>[0],
    user: Parameters<StripeCheckoutService['createCheckoutSession']>[1]
  ) => StripeCheckoutService.getInstance().createCheckoutSession(input, user),
  createCustomerPortalSession: (
    input: Parameters<StripeCustomerPortalService['createCustomerPortalSession']>[0],
    user: Parameters<StripeCustomerPortalService['createCustomerPortalSession']>[1]
  ) => StripeCustomerPortalService.getInstance().createCustomerPortalSession(input, user),
  handleStripeWebhook: (environment: 'test' | 'live', rawBody: Buffer, signature: string) =>
    StripeWebhookService.getInstance().handleStripeWebhook(environment, rawBody, signature),
  listTransactions: (input: Parameters<PaymentTransactionService['listTransactions']>[0]) =>
    PaymentTransactionService.getInstance().listTransactions(input, 'stripe'),
  listCustomers: (input: Parameters<PaymentCustomerService['listCustomers']>[0]) =>
    PaymentCustomerService.getInstance().listCustomers(input),
  listSubscriptions: (input: Parameters<StripeSubscriptionService['listSubscriptions']>[0]) =>
    StripeSubscriptionService.getInstance().listSubscriptions(input),
};

describe('Stripe payment services', () => {
  const connectedTestConnectionRow = {
    environment: 'test',
    status: 'connected',
    accountId: 'acct_123',
    accountEmail: 'owner@example.com',
    accountLivemode: false,
    webhookEndpointId: 'we_123',
    webhookEndpointUrl: 'http://localhost:7130/api/webhooks/stripe/test',
    webhookConfiguredAt: new Date('2026-04-27T00:00:00.000Z'),
    lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
    lastSyncStatus: 'succeeded',
    lastSyncError: null,
    lastSyncCounts: { products: 1, prices: 1 },
  };
  const checkoutUser = {
    id: '00000000-0000-4000-8000-000000000001',
    email: 'buyer@example.com',
    role: 'authenticated' as const,
  };
  const anonCheckoutUser = {
    role: 'anon' as const,
  };
  const checkoutSessionRow = {
    id: '2a22bd54-2e90-4d33-94bd-e1fb59dfb111',
    environment: 'test',
    mode: 'subscription',
    status: 'open',
    paymentStatus: 'unpaid',
    subjectType: 'team',
    subjectId: 'team_123',
    customerEmail: 'buyer@example.com',
    checkoutSessionId: 'cs_test_123',
    customerId: 'cus_123',
    paymentIntentId: null,
    subscriptionId: null,
    url: 'https://checkout.stripe.com/c/pay/cs_test_123',
    lastError: null,
    createdAt: new Date('2026-04-28T00:00:00.000Z'),
    updatedAt: new Date('2026-04-28T00:00:00.000Z'),
  };
  const customerPortalSessionRow = {
    id: '5d0d37dc-6304-4be5-8424-0a3de87d01d8',
    environment: 'test',
    status: 'initialized',
    subjectType: 'team',
    subjectId: 'team_123',
    customerId: null,
    returnUrl: 'https://example.com/account',
    configuration: 'bpc_123',
    url: null,
    lastError: null,
    createdAt: new Date('2026-04-29T00:00:00.000Z'),
    updatedAt: new Date('2026-04-29T00:00:00.000Z'),
  };

  beforeEach(() => {
    vi.clearAllMocks();
    mockPool.query.mockReset();
    mockPool.connect.mockReset();
    mockGetSecretByKey.mockResolvedValue('sk_test_1234567890');
    mockEncrypt.mockReturnValue('encrypted-secret');
    mockPool.query.mockResolvedValue({ rowCount: 1, rows: [] });
    mockPool.connect.mockResolvedValue({
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    });
    mockProvider.retrieveAccount.mockResolvedValue({
      id: 'acct_123',
      object: 'account',
      email: 'owner@example.com',
      charges_enabled: true,
      details_submitted: true,
    } as unknown as StripeAccount);
    mockProvider.createProduct.mockResolvedValue({
      id: 'prod_new',
      object: 'product',
      name: 'New Product',
      active: true,
      description: null,
      default_price: null,
      metadata: {},
    } as unknown as StripeProduct);
    mockProvider.updateProduct.mockResolvedValue({
      id: 'prod_123',
      object: 'product',
      name: 'Updated Product',
      active: false,
      description: 'Updated description',
      default_price: null,
      metadata: { tier: 'updated' },
    } as unknown as StripeProduct);
    mockProvider.deleteProduct.mockResolvedValue({
      id: 'prod_123',
      deleted: true,
    });
    mockProvider.createPrice.mockResolvedValue({
      id: 'price_new',
      object: 'price',
      product: 'prod_123',
      active: true,
      currency: 'usd',
      unit_amount: 2000,
      unit_amount_decimal: null,
      type: 'recurring',
      lookup_key: 'pro_monthly',
      billing_scheme: 'per_unit',
      tax_behavior: 'exclusive',
      recurring: { interval: 'month', interval_count: 1 },
      metadata: {},
    } as unknown as StripePrice);
    mockProvider.updatePrice.mockResolvedValue({
      id: 'price_123',
      object: 'price',
      product: 'prod_123',
      active: false,
      currency: 'usd',
      unit_amount: 1000,
      unit_amount_decimal: null,
      type: 'recurring',
      lookup_key: 'pro_monthly',
      billing_scheme: 'per_unit',
      tax_behavior: 'exclusive',
      recurring: { interval: 'month', interval_count: 1 },
      metadata: { archived: 'true' },
    } as unknown as StripePrice);
    mockProvider.createCustomer.mockResolvedValue({
      id: 'cus_123',
      object: 'customer',
      email: 'buyer@example.com',
      metadata: { insforge_subject_type: 'team', insforge_subject_id: 'team_123' },
    });
    mockProvider.listCustomers.mockResolvedValue([]);
    mockProvider.createCustomerPortalSession.mockResolvedValue({
      id: 'bps_123',
      object: 'billing_portal.session',
      customer: 'cus_123',
      return_url: 'https://example.com/account',
      url: 'https://billing.stripe.com/p/session/test_123',
    });
    mockProvider.createCheckoutSession.mockResolvedValue({
      id: 'cs_test_123',
      object: 'checkout.session',
      mode: 'payment',
      url: 'https://checkout.stripe.com/c/pay/cs_test_123',
      status: 'open',
      payment_status: 'unpaid',
      customer: 'cus_123',
      payment_intent: null,
      subscription: null,
    });
    mockProvider.listWebhookEndpoints.mockResolvedValue([]);
    mockProvider.createWebhookEndpoint.mockResolvedValue({
      id: 'we_new',
      object: 'webhook_endpoint',
      url: 'http://localhost:7130/api/webhooks/stripe/test',
      secret: 'whsec_new',
    });
    mockProvider.listSubscriptions.mockResolvedValue([]);
    mockProvider.listSubscriptionItems.mockResolvedValue([]);
    mockProvider.retrievePaymentIntent.mockResolvedValue({
      id: 'pi_123',
      object: 'payment_intent',
      status: 'succeeded',
      customer: 'cus_123',
      latest_charge: 'ch_123',
      amount: 5000,
      amount_received: 5000,
      currency: 'usd',
      description: null,
      receipt_email: 'buyer@example.com',
      created: 1777334400,
      metadata: {},
    } as unknown as StripePaymentIntent);
    mockProvider.retrieveCharge.mockResolvedValue({
      id: 'ch_123',
      object: 'charge',
      customer: 'cus_123',
      payment_intent: 'pi_123',
      amount_refunded: 0,
      refunded: false,
      refunds: { data: [] },
      billing_details: { email: 'buyer@example.com' },
      description: null,
      metadata: {},
    } as unknown as StripeCharge);
    mockProvider.retrieveInvoiceByPaymentIntent.mockResolvedValue(null as StripeInvoice | null);
    mockProvider.syncCatalog.mockResolvedValue({
      account: {
        id: 'acct_123',
        object: 'account',
        email: 'owner@example.com',
        charges_enabled: true,
        details_submitted: true,
      } as unknown as StripeAccount,
      products: [
        {
          id: 'prod_123',
          object: 'product',
          name: 'Pro',
          active: true,
          metadata: {},
        },
      ] as unknown as StripeProduct[],
      prices: [
        {
          id: 'price_123',
          object: 'price',
          product: 'prod_123',
          active: true,
          currency: 'usd',
          type: 'recurring',
          lookup_key: 'pro_monthly_usd',
          recurring: { interval: 'month', interval_count: 1 },
          metadata: {},
        },
      ] as unknown as StripePrice[],
    });
  });

  it('reports Stripe key configuration from the secret store', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('sk_test_secret1234')
      .mockResolvedValueOnce('sk_live_secret5678');

    await expect(stripeServices.getConfig()).resolves.toEqual({
      keys: [
        {
          environment: 'test',
          value: 'sk_test_secret1234',
        },
        {
          environment: 'live',
          value: 'sk_live_secret5678',
        },
      ],
    });

    expect(mockGetSecretByKey).toHaveBeenCalledWith('STRIPE_TEST_SECRET_KEY');
    expect(mockGetSecretByKey).toHaveBeenCalledWith('STRIPE_LIVE_SECRET_KEY');
  });

  it('upserts encrypted Stripe keys into the canonical secret names and syncs payments immediately', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockGetSecretByKey.mockResolvedValue(null);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_123' }],
      })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] });

    await stripeServices.setStripeSecretKey('test', ' sk_test_newsecret1234 ');

    expect(mockProvider.retrieveAccount).toHaveBeenCalledTimes(1);
    expect(mockEncrypt).toHaveBeenCalledWith('sk_test_newsecret1234');
    expect(mockProvider.listWebhookEndpoints).toHaveBeenCalledTimes(1);
    expect(mockProvider.createWebhookEndpoint).toHaveBeenCalledWith({
      url: 'http://localhost:7130/api/webhooks/stripe/test',
      enabledEvents: [
        'customer.created',
        'customer.updated',
        'customer.deleted',
        'checkout.session.completed',
        'checkout.session.async_payment_succeeded',
        'checkout.session.async_payment_failed',
        'checkout.session.expired',
        'invoice.paid',
        'invoice.payment_failed',
        'payment_intent.succeeded',
        'payment_intent.payment_failed',
        'charge.refunded',
        'refund.created',
        'refund.updated',
        'refund.failed',
        'customer.subscription.created',
        'customer.subscription.updated',
        'customer.subscription.deleted',
        'customer.subscription.paused',
        'customer.subscription.resumed',
      ],
      metadata: {
        managed_by: 'insforge',
        insforge_webhook: 'stripe_payments',
        insforge_environment: 'test',
        insforge_endpoint_path: '/api/webhooks/stripe/test',
        insforge_endpoint_url: 'http://localhost:7130/api/webhooks/stripe/test',
      },
    });
    expect(mockEncrypt).toHaveBeenCalledWith('whsec_new');
    expect(mockClient.query).toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_SECRET_KEY',
      'encrypted-secret',
    ]);
    expect(mockClient.query).toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_WEBHOOK_SECRET',
      'encrypted-secret',
    ]);
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_subscription_items WHERE environment = $1',
      ['test']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_subscriptions WHERE environment = $1',
      ['test']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_products WHERE environment = $1',
      ['test']
    );
    expect(mockProvider.syncCatalog).toHaveBeenCalledTimes(1);
    expect(mockProvider.listCustomers).toHaveBeenCalledTimes(1);
    expect(mockProvider.listSubscriptions).toHaveBeenCalledTimes(1);
  });

  it('saves Stripe keys even when managed webhook setup fails', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockGetSecretByKey.mockResolvedValue(null);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_123' }],
      })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] });
    mockProvider.createWebhookEndpoint.mockRejectedValueOnce(
      new Error(
        'Invalid URL: URL must be publicly accessible. Consider using a tool like the Stripe CLI to test webhooks locally.'
      )
    );

    await expect(
      stripeServices.setStripeSecretKey('test', 'sk_test_newsecret1234')
    ).resolves.toBeUndefined();

    expect(mockProvider.retrieveAccount).toHaveBeenCalledTimes(1);
    expect(mockProvider.createWebhookEndpoint).toHaveBeenCalledTimes(1);
    expect(mockClient.query).toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_SECRET_KEY',
      'encrypted-secret',
    ]);
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/UPDATE system\.secrets/i),
      ['STRIPE_TEST_WEBHOOK_SECRET']
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.provider_connections/i),
      [
        'test',
        'acct_123',
        'owner@example.com',
        false,
        null,
        null,
        expect.objectContaining({ id: 'acct_123' }),
        false,
      ]
    );
    expect(mockEncrypt).not.toHaveBeenCalledWith('whsec_new');
    expect(mockProvider.syncCatalog).toHaveBeenCalledTimes(1);
    expect(mockProvider.listCustomers).toHaveBeenCalledTimes(1);
    expect(mockProvider.listSubscriptions).toHaveBeenCalledTimes(1);
  });

  it('recreates existing InsForge-managed Stripe webhooks even when the stored endpoint URL is stale', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockGetSecretByKey.mockResolvedValue(null);
    mockPool.query.mockResolvedValueOnce({
      rows: [{ accountId: 'acct_123' }],
    });
    mockProvider.listWebhookEndpoints.mockResolvedValueOnce([
      {
        id: 'we_old',
        object: 'webhook_endpoint',
        url: 'https://old.example.com/api/webhooks/stripe/test',
        metadata: {
          managed_by: 'insforge',
          insforge_webhook: 'stripe_payments',
          insforge_environment: 'test',
        },
      },
      {
        id: 'we_live',
        object: 'webhook_endpoint',
        url: 'https://old.example.com/api/webhooks/stripe/live',
        metadata: {
          managed_by: 'insforge',
          insforge_webhook: 'stripe_payments',
          insforge_environment: 'live',
        },
      },
      {
        id: 'we_developer',
        object: 'webhook_endpoint',
        metadata: { managed_by: 'developer' },
      },
    ]);

    await stripeServices.setStripeSecretKey('test', 'sk_test_newsecret1234');

    expect(mockProvider.deleteWebhookEndpoint).toHaveBeenCalledWith('we_old');
    expect(mockProvider.deleteWebhookEndpoint).not.toHaveBeenCalledWith('we_live');
    expect(mockProvider.deleteWebhookEndpoint).not.toHaveBeenCalledWith('we_developer');
    expect(mockProvider.createWebhookEndpoint).toHaveBeenCalledTimes(1);
  });

  it('skips webhook recreation and sync when saving the same Stripe key', async () => {
    mockPool.query.mockResolvedValueOnce({
      rows: [{ accountId: 'acct_123' }],
    });

    await stripeServices.setStripeSecretKey('test', ' sk_test_1234567890 ');

    expect(mockProvider.retrieveAccount).not.toHaveBeenCalled();
    expect(mockEncrypt).not.toHaveBeenCalled();
    expect(mockProvider.listWebhookEndpoints).not.toHaveBeenCalled();
    expect(mockProvider.createWebhookEndpoint).not.toHaveBeenCalled();
    expect(mockProvider.syncCatalog).not.toHaveBeenCalled();
    expect(mockProvider.listCustomers).not.toHaveBeenCalled();
    expect(mockProvider.listSubscriptions).not.toHaveBeenCalled();
  });

  it('configures managed Stripe webhooks on demand using the stored key', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/RETURNING\s+environment/i.test(sql)) {
          return Promise.resolve({
            rows: [
              {
                ...connectedTestConnectionRow,
                webhookEndpointId: 'we_new',
                webhookEndpointUrl: 'http://localhost:7130/api/webhooks/stripe/test',
                webhookConfiguredAt: new Date('2026-04-28T00:00:00.000Z'),
              },
            ],
          });
        }

        return Promise.resolve({ rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValueOnce({
      rows: [{ accountId: 'acct_123' }],
    });

    const result = await stripeServices.configureWebhook('test');

    expect(mockProvider.retrieveAccount).toHaveBeenCalledTimes(1);
    expect(mockProvider.listWebhookEndpoints).toHaveBeenCalledTimes(1);
    expect(mockProvider.createWebhookEndpoint).toHaveBeenCalledTimes(1);
    expect(mockProvider.syncCatalog).not.toHaveBeenCalled();
    expect(mockProvider.listCustomers).not.toHaveBeenCalled();
    expect(mockProvider.listSubscriptions).not.toHaveBeenCalled();
    expect(mockEncrypt).toHaveBeenCalledWith('whsec_new');
    expect(mockClient.query).toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_WEBHOOK_SECRET',
      'encrypted-secret',
    ]);
    expect(result.connection.webhookEndpointId).toBe('we_new');
    expect(result.connection.webhookConfiguredAt).toBe('2026-04-28T00:00:00.000Z');
  });

  it('clears the payment mirror when on-demand webhook configuration finds another Stripe account', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/RETURNING\s+environment/i.test(sql)) {
          return Promise.resolve({
            rows: [
              {
                ...connectedTestConnectionRow,
                accountId: 'acct_new',
                accountEmail: 'new-owner@example.com',
                webhookEndpointId: 'we_new',
                webhookEndpointUrl: 'http://localhost:7130/api/webhooks/stripe/test',
                webhookConfiguredAt: new Date('2026-04-28T00:00:00.000Z'),
                lastSyncedAt: null,
                lastSyncStatus: null,
                lastSyncError: null,
                lastSyncCounts: {},
              },
            ],
          });
        }

        return Promise.resolve({ rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValueOnce({
      rows: [{ accountId: 'acct_old' }],
    });
    mockProvider.retrieveAccount.mockResolvedValueOnce({
      id: 'acct_new',
      object: 'account',
      email: 'new-owner@example.com',
    } as unknown as StripeAccount);

    const result = await stripeServices.configureWebhook('test');

    expectStripeRuntimeDelete(mockClient.query, 'stripe_subscription_items', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_subscriptions', 'test');
    expectProviderRuntimeDelete(mockClient.query, 'transactions', 'test', 'stripe');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_checkout_sessions', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_customer_portal_sessions', 'test');
    expectStripeScopedDelete(mockClient.query, 'customers', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_prices', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_products', 'test');
    expect(result.connection.accountId).toBe('acct_new');
  });

  it('requires a configured Stripe key before configuring managed webhooks', async () => {
    mockGetSecretByKey.mockResolvedValueOnce(null);

    await expect(stripeServices.configureWebhook('test')).rejects.toThrow(
      'STRIPE_TEST_SECRET_KEY is not configured'
    );

    expect(mockProvider.retrieveAccount).not.toHaveBeenCalled();
    expect(mockProvider.listWebhookEndpoints).not.toHaveBeenCalled();
    expect(mockProvider.createWebhookEndpoint).not.toHaveBeenCalled();
  });

  it('treats a missing stored Stripe account id as unknown and re-syncs', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: null }],
      })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] });

    await stripeServices.setStripeSecretKey('test', 'sk_test_1234567890');

    expect(mockProvider.retrieveAccount).toHaveBeenCalledTimes(1);
    expect(mockProvider.createWebhookEndpoint).toHaveBeenCalledTimes(1);
    expectStripeRuntimeDelete(mockClient.query, 'stripe_subscription_items', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_subscriptions', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_prices', 'test');
    expectStripeScopedDelete(mockClient.query, 'customers', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_products', 'test');
    expect(mockProvider.syncCatalog).toHaveBeenCalledTimes(1);
    expect(mockProvider.listCustomers).toHaveBeenCalledTimes(1);
    expect(mockProvider.listSubscriptions).toHaveBeenCalledTimes(1);
  });

  it('persists rotated keys without recreating webhooks or syncing when the Stripe account is unchanged', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockGetSecretByKey.mockResolvedValue('sk_test_oldsecret1234');
    mockPool.query.mockResolvedValueOnce({
      rows: [{ accountId: 'acct_123' }],
    });

    await stripeServices.setStripeSecretKey('test', 'sk_test_rotated1234');

    expect(mockProvider.retrieveAccount).toHaveBeenCalledTimes(1);
    expect(mockEncrypt).toHaveBeenCalledWith('sk_test_rotated1234');
    expect(mockProvider.listWebhookEndpoints).not.toHaveBeenCalled();
    expect(mockProvider.createWebhookEndpoint).not.toHaveBeenCalled();
    expect(mockProvider.syncCatalog).not.toHaveBeenCalled();
    expect(mockProvider.listCustomers).not.toHaveBeenCalled();
    expect(mockProvider.listSubscriptions).not.toHaveBeenCalled();
    expect(mockClient.query).toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_SECRET_KEY',
      'encrypted-secret',
    ]);
    expect(mockClient.query).not.toHaveBeenCalledWith(expect.any(String), [
      'STRIPE_TEST_WEBHOOK_SECRET',
    ]);
    expect(mockClient.query).not.toHaveBeenCalledWith(expect.any(String), [
      'STRIPE_TEST_WEBHOOK_SECRET',
      'encrypted-secret',
    ]);
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.provider_connections/i),
      ['test', 'acct_123', 'owner@example.com', false, expect.objectContaining({ id: 'acct_123' })]
    );
  });

  it('clears the environment catalog mirror when a new key points to another Stripe account', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockProvider.retrieveAccount.mockResolvedValueOnce({
      id: 'acct_new',
      object: 'account',
      email: 'new-owner@example.com',
    } as unknown as StripeAccount);
    mockProvider.syncCatalog.mockRejectedValueOnce(new Error('sync failed'));
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_old' }],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            status: 'error',
            accountId: 'acct_new',
            accountEmail: 'new-owner@example.com',
            accountLivemode: false,
            lastSyncedAt: null,
            lastSyncStatus: 'failed',
            lastSyncError: 'sync failed',
            lastSyncCounts: {},
          },
        ],
      });

    await expect(
      stripeServices.setStripeSecretKey('test', 'sk_test_newsecret1234')
    ).resolves.toBeUndefined();

    expectStripeRuntimeDelete(mockClient.query, 'stripe_subscription_items', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_subscriptions', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_prices', 'test');
    expectStripeRuntimeDelete(mockClient.query, 'stripe_products', 'test');
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.provider_connections/i),
      ['test', 'error', 'sync failed']
    );
  });

  it('soft-removes Stripe keys without clearing the mirrored payment data', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rowCount: 1, rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);

    await expect(stripeServices.removeStripeSecretKey('live')).resolves.toBe(true);

    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/UPDATE system\.secrets/i),
      ['STRIPE_LIVE_SECRET_KEY']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_subscription_items WHERE environment = $1',
      ['live']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_subscriptions WHERE environment = $1',
      ['live']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_prices WHERE environment = $1',
      ['live']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.customers WHERE environment = $1',
      ['live']
    );
    expect(mockClient.query).not.toHaveBeenCalledWith(
      'DELETE FROM payments.stripe_products WHERE environment = $1',
      ['live']
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/UPDATE payments\.provider_connections/i),
      ['live', 'STRIPE_LIVE_SECRET_KEY is not configured']
    );
  });

  it('seeds Stripe keys from environment variables', async () => {
    const originalEnv = { ...process.env };
    process.env.STRIPE_TEST_SECRET_KEY = 'sk_test_seed1234';
    process.env.STRIPE_LIVE_SECRET_KEY = 'sk_live_seed5678';
    mockGetSecretByKey.mockResolvedValue(null);

    try {
      await stripeServices.seedStripeKeysFromEnv();
    } finally {
      process.env = originalEnv;
    }

    expect(mockEncrypt).toHaveBeenCalledWith('sk_test_seed1234');
    expect(mockEncrypt).toHaveBeenCalledWith('sk_live_seed5678');
  });

  it('does not overwrite active Stripe keys when seeding from environment variables', async () => {
    const originalEnv = { ...process.env };
    process.env.STRIPE_TEST_SECRET_KEY = 'sk_test_seed1234';
    mockGetSecretByKey.mockResolvedValue('sk_test_existing1234');

    try {
      await stripeServices.seedStripeKeysFromEnv();
    } finally {
      process.env = originalEnv;
    }

    expect(mockEncrypt).not.toHaveBeenCalledWith('sk_test_seed1234');
  });

  it('does not seed Stripe webhook secrets from environment variables', async () => {
    const originalEnv = { ...process.env };
    delete process.env.STRIPE_TEST_SECRET_KEY;
    delete process.env.STRIPE_LIVE_SECRET_KEY;
    process.env.STRIPE_TEST_WEBHOOK_SECRET = 'whsec_test_seed1234';
    process.env.STRIPE_LIVE_WEBHOOK_SECRET = 'whsec_live_seed5678';
    mockGetSecretByKey.mockResolvedValue(null);

    try {
      await stripeServices.seedStripeKeysFromEnv();
    } finally {
      process.env = originalEnv;
    }

    expect(mockEncrypt).not.toHaveBeenCalledWith('whsec_test_seed1234');
    expect(mockEncrypt).not.toHaveBeenCalledWith('whsec_live_seed5678');
    expect(mockPool.query).not.toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_WEBHOOK_SECRET',
      'encrypted-secret',
    ]);
    expect(mockPool.query).not.toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_LIVE_WEBHOOK_SECRET',
      'encrypted-secret',
    ]);
  });

  it('records an unconfigured status when an environment key is missing', async () => {
    mockGetSecretByKey.mockResolvedValue(null);
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          environment: 'live',
          status: 'unconfigured',
          accountId: null,
          accountEmail: null,
          accountLivemode: null,
          lastSyncedAt: null,
          lastSyncStatus: 'failed',
          lastSyncError: 'STRIPE_LIVE_SECRET_KEY is not configured',
          lastSyncCounts: {},
        },
      ],
    });

    const result = await stripeServices.syncPayments({ environment: 'live' });

    expect(result.results[0]?.connection.status).toBe('unconfigured');
    expect(result.results[0]?.connection.lastSyncStatus).toBe('failed');
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/payments\.provider_connections/i),
      expect.any(Array)
    );
  });

  it('fetches Stripe products and prices and commits a successful sync', async () => {
    const mockLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockSyncClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockSubscriptionsClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 0 }),
      release: vi.fn(),
    };
    mockPool.connect
      .mockResolvedValueOnce(mockLockClient)
      .mockResolvedValueOnce(mockSyncClient)
      .mockResolvedValueOnce(mockSubscriptionsClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_123' }],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            status: 'connected',
            accountId: 'acct_123',
            accountEmail: 'owner@example.com',
            accountLivemode: false,
            lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
            lastSyncStatus: 'succeeded',
            lastSyncError: null,
            lastSyncCounts: { products: 1, prices: 1 },
          },
        ],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            status: 'connected',
            accountId: 'acct_123',
            accountEmail: 'owner@example.com',
            accountLivemode: false,
            lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
            lastSyncStatus: 'succeeded',
            lastSyncError: null,
            lastSyncCounts: { products: 1, prices: 1 },
          },
        ],
      });

    const result = await stripeServices.syncPayments({ environment: 'test' });

    expect(result.results[0]?.connection.status).toBe('connected');
    expect(mockProvider.syncCatalog).toHaveBeenCalledTimes(1);
    expect(mockProvider.listCustomers).toHaveBeenCalledTimes(1);
    expect(mockLockClient.query).toHaveBeenCalledWith('SELECT pg_advisory_lock(hashtext($1))', [
      'payments_environment_test',
    ]);
    expect(mockLockClient.query).toHaveBeenCalledWith('SELECT pg_advisory_unlock(hashtext($1))', [
      'payments_environment_test',
    ]);
    expect(mockSyncClient.query).toHaveBeenCalledWith('BEGIN');
    expect(mockSyncClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/DELETE FROM payments\.stripe_prices/i),
      ['test', ['price_123']]
    );
    expect(mockSyncClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/DELETE FROM payments\.stripe_products/i),
      ['test', ['prod_123']]
    );
    expect(mockSyncClient.query).toHaveBeenCalledWith('COMMIT');
  });

  it('syncs payments by refreshing catalog before subscriptions', async () => {
    const mockLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockCatalogClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockCustomersClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 0 }),
      release: vi.fn(),
    };
    const mockSubscriptionsClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 0 }),
      release: vi.fn(),
    };
    const connectedRow = {
      environment: 'test',
      status: 'connected',
      accountId: 'acct_123',
      accountEmail: 'owner@example.com',
      accountLivemode: false,
      webhookEndpointId: 'we_123',
      webhookEndpointUrl: 'https://example.com/api/webhooks/stripe/test',
      webhookConfiguredAt: new Date('2026-04-27T00:00:00.000Z'),
      lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
      lastSyncStatus: 'succeeded',
      lastSyncError: null,
      lastSyncCounts: { products: 1, prices: 1 },
    };

    mockPool.connect
      .mockResolvedValueOnce(mockLockClient)
      .mockResolvedValueOnce(mockCatalogClient)
      .mockResolvedValueOnce(mockCustomersClient)
      .mockResolvedValueOnce(mockSubscriptionsClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_123' }],
      })
      .mockResolvedValueOnce({ rows: [connectedRow] })
      .mockResolvedValueOnce({ rows: [connectedRow] });

    const result = await stripeServices.syncPayments({ environment: 'test' });

    expect(result.results).toHaveLength(1);
    expect(result.results[0]).toMatchObject({
      environment: 'test',
      connection: { status: 'connected', accountId: 'acct_123' },
      subscriptions: { environment: 'test', synced: 0, unmapped: 0, deleted: 0 },
    });
    expect(mockProvider.syncCatalog).toHaveBeenCalledTimes(1);
    expect(mockProvider.listCustomers).toHaveBeenCalledTimes(1);
    expect(mockProvider.listSubscriptions).toHaveBeenCalledTimes(1);
    expect(mockProvider.syncCatalog.mock.invocationCallOrder[0]).toBeLessThan(
      mockProvider.listCustomers.mock.invocationCallOrder[0]
    );
    expect(mockProvider.listCustomers.mock.invocationCallOrder[0]).toBeLessThan(
      mockProvider.listSubscriptions.mock.invocationCallOrder[0]
    );
    expect(mockProvider.createWebhookEndpoint).not.toHaveBeenCalled();
    expect(mockCustomersClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/UPDATE payments\.customers/i),
      ['test', expect.any(Date), []]
    );
    expect(mockSubscriptionsClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/DELETE FROM payments\.stripe_subscriptions/i),
      ['test', []]
    );
  });

  it('continues syncing subscriptions when customer mirroring fails', async () => {
    const mockLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockCatalogClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockSubscriptionsClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 0 }),
      release: vi.fn(),
    };
    const connectedRow = {
      environment: 'test',
      status: 'connected',
      accountId: 'acct_123',
      accountEmail: 'owner@example.com',
      accountLivemode: false,
      webhookEndpointId: 'we_123',
      webhookEndpointUrl: 'https://example.com/api/webhooks/stripe/test',
      webhookConfiguredAt: new Date('2026-04-27T00:00:00.000Z'),
      lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
      lastSyncStatus: 'succeeded',
      lastSyncError: null,
      lastSyncCounts: { products: 1, prices: 1 },
    };

    mockPool.connect
      .mockResolvedValueOnce(mockLockClient)
      .mockResolvedValueOnce(mockCatalogClient)
      .mockResolvedValueOnce(mockSubscriptionsClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_123' }],
      })
      .mockResolvedValueOnce({ rows: [connectedRow] })
      .mockResolvedValueOnce({ rows: [connectedRow] });
    mockProvider.listCustomers.mockRejectedValueOnce(new Error('customer sync failed'));

    await expect(stripeServices.syncPayments({ environment: 'test' })).resolves.toMatchObject({
      results: [
        {
          environment: 'test',
          connection: { status: 'connected', accountId: 'acct_123' },
          subscriptions: { environment: 'test', synced: 0, unmapped: 0, deleted: 0 },
        },
      ],
    });

    expect(mockProvider.listSubscriptions).toHaveBeenCalledTimes(1);
    expect(mockLogger.warn).toHaveBeenCalledWith(
      'Stripe customer mirror sync failed during payments sync',
      {
        environment: 'test',
        error: 'customer sync failed',
      }
    );
  });

  it('clears account-scoped payment mirrors when catalog sync resolves to a different account', async () => {
    const mockLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockSyncClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockSubscriptionsClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 0 }),
      release: vi.fn(),
    };
    mockPool.connect
      .mockResolvedValueOnce(mockLockClient)
      .mockResolvedValueOnce(mockSyncClient)
      .mockResolvedValueOnce(mockSubscriptionsClient);
    mockProvider.syncCatalog.mockResolvedValueOnce({
      account: {
        id: 'acct_new',
        object: 'account',
        email: 'new-owner@example.com',
        charges_enabled: true,
        details_submitted: true,
      } as unknown as StripeAccount,
      products: [],
      prices: [],
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_old' }],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            status: 'connected',
            accountId: 'acct_new',
            accountEmail: 'new-owner@example.com',
            accountLivemode: false,
            lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
            lastSyncStatus: 'succeeded',
            lastSyncError: null,
            lastSyncCounts: { products: 0, prices: 0 },
          },
        ],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            status: 'connected',
            accountId: 'acct_new',
            accountEmail: 'new-owner@example.com',
            accountLivemode: false,
            lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
            lastSyncStatus: 'succeeded',
            lastSyncError: null,
            lastSyncCounts: { products: 0, prices: 0 },
          },
        ],
      });

    await expect(stripeServices.syncPayments({ environment: 'test' })).resolves.toMatchObject({
      results: [
        {
          connection: {
            accountId: 'acct_new',
            status: 'connected',
          },
        },
      ],
    });

    expectProviderRuntimeDelete(mockSyncClient.query, 'transactions', 'test', 'stripe');
    expectStripeRuntimeDelete(mockSyncClient.query, 'stripe_checkout_sessions', 'test');
    expectStripeRuntimeDelete(mockSyncClient.query, 'stripe_customer_portal_sessions', 'test');
    expectStripeScopedDelete(mockSyncClient.query, 'customers', 'test');
    expectStripeScopedDelete(mockSyncClient.query, 'customer_mappings', 'test');
    expectStripeScopedDelete(mockSyncClient.query, 'webhook_events', 'test');
    expectStripeRuntimeDelete(mockSyncClient.query, 'stripe_prices', 'test');
    expectStripeRuntimeDelete(mockSyncClient.query, 'stripe_products', 'test');
    expect(mockProvider.createWebhookEndpoint).toHaveBeenCalledTimes(1);
    expect(mockEncrypt).toHaveBeenCalledWith('whsec_new');
    expect(mockSyncClient.query).toHaveBeenCalledWith(expect.stringMatching(/system\.secrets/i), [
      'STRIPE_TEST_WEBHOOK_SECRET',
      'encrypted-secret',
    ]);
    expect(mockSyncClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.provider_connections/i),
      [
        'test',
        'acct_new',
        'new-owner@example.com',
        false,
        { products: 0, prices: 0 },
        expect.objectContaining({ id: 'acct_new' }),
        'we_new',
        'http://localhost:7130/api/webhooks/stripe/test',
        true,
      ]
    );
    expect(mockSyncClient.query).toHaveBeenCalledWith('COMMIT');
  });

  it('lists products from the requested local Stripe mirror environment', async () => {
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            productId: 'prod_123',
            name: 'Pro',
            description: null,
            active: true,
            defaultPriceId: 'price_123',
            metadata: {},
            syncedAt: new Date('2026-04-27T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] });

    await expect(stripeServices.listProducts({ environment: 'test' })).resolves.toEqual({
      products: [
        {
          environment: 'test',
          productId: 'prod_123',
          name: 'Pro',
          description: null,
          active: true,
          defaultPriceId: 'price_123',
          metadata: {},
          syncedAt: '2026-04-27T00:00:00.000Z',
        },
      ],
    });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/FROM payments\.stripe_products/i),
      ['test']
    );
  });

  it('creates products with the requested Stripe key and updates only the returned product mirror', async () => {
    mockGetSecretByKey.mockResolvedValue('sk_live_1234567890');
    const mockLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockMirrorClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValueOnce(mockLockClient).mockResolvedValueOnce(mockMirrorClient);
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          environment: 'live',
          status: 'connected',
          accountId: 'acct_123',
          accountEmail: 'owner@example.com',
          accountLivemode: true,
          lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
          lastSyncStatus: 'succeeded',
          lastSyncError: null,
          lastSyncCounts: { products: 1, prices: 1 },
        },
      ],
    });

    const result = await stripeServices.createProduct({
      environment: 'live',
      name: 'New Product',
      active: true,
      metadata: { tier: 'new' },
      idempotencyKey: 'agent-product-123',
    });

    expect(mockProvider.createProduct).toHaveBeenCalledWith({
      name: 'New Product',
      active: true,
      metadata: { tier: 'new' },
      idempotencyKey: 'insforge:live:product:agent-product-123',
    });
    expect(mockGetSecretByKey).toHaveBeenCalledWith('STRIPE_LIVE_SECRET_KEY');
    expect(mockMirrorClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_products/i),
      expect.arrayContaining(['live', 'prod_new', 'New Product'])
    );
    expect(mockProvider.syncCatalog).not.toHaveBeenCalled();
    expect(result.product).toMatchObject({
      environment: 'live',
      productId: 'prod_new',
      name: 'New Product',
      active: true,
    });
  });

  it('updates and deletes products through the requested Stripe provider', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValue({
      rows: [
        {
          environment: 'test',
          status: 'connected',
          accountId: 'acct_123',
          accountEmail: 'owner@example.com',
          accountLivemode: false,
          lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
          lastSyncStatus: 'succeeded',
          lastSyncError: null,
          lastSyncCounts: { products: 1, prices: 1 },
        },
      ],
    });

    await expect(
      stripeServices.updateProduct('prod_123', {
        environment: 'test',
        name: 'Updated Product',
        active: false,
      })
    ).resolves.toMatchObject({
      product: {
        environment: 'test',
        productId: 'prod_123',
        active: false,
      },
    });

    await expect(stripeServices.deleteProduct('test', 'prod_123')).resolves.toEqual({
      productId: 'prod_123',
      deleted: true,
    });

    expect(mockProvider.updateProduct).toHaveBeenCalledWith('prod_123', {
      name: 'Updated Product',
      active: false,
    });
    expect(mockProvider.deleteProduct).toHaveBeenCalledWith('prod_123');
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_products/i),
      expect.arrayContaining(['test', 'prod_123', 'Updated Product'])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/DELETE FROM payments\.stripe_prices[\s\S]*product_id = \$2/i),
      ['test', 'prod_123']
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/DELETE FROM payments\.stripe_products[\s\S]*product_id = \$2/i),
      ['test', 'prod_123']
    );
    expect(mockProvider.syncCatalog).not.toHaveBeenCalled();
  });

  it('lists prices from the requested local Stripe mirror with an optional product filter', async () => {
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          environment: 'test',
          priceId: 'price_123',
          productId: 'prod_123',
          active: true,
          currency: 'usd',
          unitAmount: 1000,
          unitAmountDecimal: null,
          type: 'recurring',
          lookupKey: 'pro_monthly',
          billingScheme: 'per_unit',
          taxBehavior: 'exclusive',
          recurringInterval: 'month',
          recurringIntervalCount: 1,
          metadata: {},
          syncedAt: new Date('2026-04-27T00:00:00.000Z'),
        },
      ],
    });

    await expect(
      stripeServices.listPrices({
        environment: 'test',
        productId: 'prod_123',
      })
    ).resolves.toEqual({
      prices: [
        {
          environment: 'test',
          priceId: 'price_123',
          productId: 'prod_123',
          active: true,
          currency: 'usd',
          unitAmount: 1000,
          unitAmountDecimal: null,
          type: 'recurring',
          lookupKey: 'pro_monthly',
          billingScheme: 'per_unit',
          taxBehavior: 'exclusive',
          recurringInterval: 'month',
          recurringIntervalCount: 1,
          metadata: {},
          syncedAt: '2026-04-27T00:00:00.000Z',
        },
      ],
    });
  });

  it('creates, updates, and archives prices through the requested Stripe provider', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValue({
      rows: [
        {
          environment: 'test',
          status: 'connected',
          accountId: 'acct_123',
          accountEmail: 'owner@example.com',
          accountLivemode: false,
          lastSyncedAt: new Date('2026-04-27T00:00:00.000Z'),
          lastSyncStatus: 'succeeded',
          lastSyncError: null,
          lastSyncCounts: { products: 1, prices: 1 },
        },
      ],
    });

    await expect(
      stripeServices.createPrice({
        environment: 'test',
        productId: 'prod_123',
        currency: 'usd',
        unitAmount: 2000,
        recurring: { interval: 'month', intervalCount: 1 },
        idempotencyKey: 'agent-price-123',
      })
    ).resolves.toMatchObject({
      price: {
        environment: 'test',
        priceId: 'price_new',
        productId: 'prod_123',
        active: true,
      },
    });

    await expect(
      stripeServices.updatePrice('price_123', {
        environment: 'test',
        active: false,
        metadata: { archived: 'true' },
      })
    ).resolves.toMatchObject({
      price: {
        environment: 'test',
        priceId: 'price_123',
        active: false,
      },
    });

    await expect(stripeServices.archivePrice('test', 'price_123')).resolves.toMatchObject({
      price: {
        environment: 'test',
        priceId: 'price_123',
        active: false,
      },
      archived: true,
    });

    expect(mockProvider.createPrice).toHaveBeenCalledWith({
      productId: 'prod_123',
      currency: 'usd',
      unitAmount: 2000,
      recurring: { interval: 'month', intervalCount: 1 },
      idempotencyKey: 'insforge:test:price:agent-price-123',
    });
    expect(mockProvider.updatePrice).toHaveBeenCalledWith('price_123', {
      active: false,
      metadata: { archived: 'true' },
    });
    expect(mockProvider.updatePrice).toHaveBeenCalledWith('price_123', { active: false });
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_prices/i),
      expect.arrayContaining(['test', 'price_new', 'prod_123', true, 'usd'])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_prices/i),
      expect.arrayContaining(['test', 'price_123', 'prod_123', false, 'usd'])
    );
    expect(mockProvider.syncCatalog).not.toHaveBeenCalled();
  });

  it('rejects subscription checkout without a billing subject', async () => {
    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'subscription',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
        },
        checkoutUser
      )
    ).rejects.toThrow(/billing subject/i);

    expect(mockProvider.createCheckoutSession).not.toHaveBeenCalled();
  });

  it('uses a shared environment lock and a key-scoped lock for idempotent checkout creation', async () => {
    const mockEnvironmentLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockIdempotencyLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockCheckoutClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 1 }),
      release: vi.fn(),
    };
    mockPool.connect
      .mockResolvedValueOnce(mockEnvironmentLockClient)
      .mockResolvedValueOnce(mockIdempotencyLockClient)
      .mockResolvedValueOnce(mockCheckoutClient);
    mockPool.query.mockResolvedValueOnce({ rows: [] }).mockResolvedValueOnce({
      rows: [checkoutSessionRow],
    });

    await stripeServices.createCheckoutSession(
      {
        environment: 'test',
        mode: 'subscription',
        lineItems: [{ priceId: 'price_123', quantity: 1 }],
        successUrl: 'https://example.com/success',
        cancelUrl: 'https://example.com/cancel',
        customerEmail: 'buyer@example.com',
        subject: { type: 'team', id: 'team_123' },
        idempotencyKey: 'checkout-123',
      },
      checkoutUser
    );

    expect(mockEnvironmentLockClient.query).toHaveBeenCalledWith(
      'SELECT pg_advisory_lock_shared(hashtext($1))',
      ['payments_environment_test']
    );
    expect(mockEnvironmentLockClient.query).toHaveBeenCalledWith(
      'SELECT pg_advisory_unlock_shared(hashtext($1))',
      ['payments_environment_test']
    );
    expect(mockIdempotencyLockClient.query).toHaveBeenCalledWith(
      'SELECT pg_advisory_lock(hashtext($1))',
      ['payments_checkout_test_checkout-123']
    );
    expect(mockIdempotencyLockClient.query).toHaveBeenCalledWith(
      'SELECT pg_advisory_unlock(hashtext($1))',
      ['payments_checkout_test_checkout-123']
    );
  });

  it('uses only the shared environment lock when checkout has no caller idempotency key', async () => {
    const mockEnvironmentLockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    const mockCheckoutClient = {
      query: vi.fn().mockResolvedValue({ rows: [], rowCount: 1 }),
      release: vi.fn(),
    };
    mockPool.connect
      .mockResolvedValueOnce(mockEnvironmentLockClient)
      .mockResolvedValueOnce(mockCheckoutClient);
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          ...checkoutSessionRow,
          id: '8cf48e7a-8e8c-44be-b6f1-68151d4e7331',
          mode: 'payment',
          subjectType: null,
          subjectId: null,
          customerEmail: 'anon@example.com',
        },
      ],
    });

    await stripeServices.createCheckoutSession(
      {
        environment: 'test',
        mode: 'payment',
        lineItems: [{ priceId: 'price_123', quantity: 1 }],
        successUrl: 'https://example.com/success',
        cancelUrl: 'https://example.com/cancel',
        customerEmail: 'anon@example.com',
      },
      anonCheckoutUser
    );

    expect(mockEnvironmentLockClient.query).toHaveBeenCalledWith(
      'SELECT pg_advisory_lock_shared(hashtext($1))',
      ['payments_environment_test']
    );
    expect(mockEnvironmentLockClient.query).toHaveBeenCalledWith(
      'SELECT pg_advisory_unlock_shared(hashtext($1))',
      ['payments_environment_test']
    );
    expect(mockPool.connect).toHaveBeenCalledTimes(2);
    expect(mockEnvironmentLockClient.query).not.toHaveBeenCalledWith(
      'SELECT pg_advisory_lock(hashtext($1))',
      [expect.stringMatching(/^payments_checkout_test_/)]
    );
  });

  it('creates an authorized checkout row before identified Stripe checkout', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [checkoutSessionRow] });
    mockProvider.createCheckoutSession.mockResolvedValueOnce({
      id: 'cs_test_123',
      object: 'checkout.session',
      mode: 'subscription',
      url: 'https://checkout.stripe.com/c/pay/cs_test_123',
      status: 'open',
      payment_status: 'unpaid',
      customer: 'cus_123',
      payment_intent: null,
      subscription: null,
    });

    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'subscription',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
          customerEmail: 'buyer@example.com',
          subject: { type: 'team', id: 'team_123' },
          metadata: { plan: 'pro' },
          idempotencyKey: 'checkout-123',
        },
        checkoutUser
      )
    ).resolves.toMatchObject({
      checkoutSession: {
        id: checkoutSessionRow.id,
        environment: 'test',
        checkoutSessionId: 'cs_test_123',
        mode: 'subscription',
        customerId: 'cus_123',
      },
    });

    expect(mockProvider.createCustomer).not.toHaveBeenCalled();
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/SET LOCAL ROLE authenticated/i)
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_checkout_sessions/i),
      expect.arrayContaining([
        'test',
        'subscription',
        'team',
        'team_123',
        'buyer@example.com',
        JSON.stringify([{ priceId: 'price_123', quantity: 1 }]),
        'https://example.com/success',
        'https://example.com/cancel',
      ])
    );
    expect(mockProvider.createCheckoutSession).toHaveBeenCalledWith({
      mode: 'subscription',
      lineItems: [{ priceId: 'price_123', quantity: 1 }],
      successUrl: 'https://example.com/success',
      cancelUrl: 'https://example.com/cancel',
      customerId: null,
      customerEmail: 'buyer@example.com',
      clientReferenceId: expect.any(String),
      metadata: {
        plan: 'pro',
        insforge_checkout_mode: 'subscription',
        insforge_subject_type: 'team',
        insforge_subject_id: 'team_123',
        insforge_checkout_session_id: expect.any(String),
      },
      idempotencyKey: 'insforge:test:checkout_session:checkout-123',
    });
    const checkoutUpdateCall = mockPool.query.mock.calls.find(([sql]) =>
      /UPDATE payments\.stripe_checkout_sessions/i.test(String(sql))
    );
    expect(checkoutUpdateCall?.[1]?.[8]).toEqual({
      plan: 'pro',
      insforge_checkout_mode: 'subscription',
      insforge_subject_type: 'team',
      insforge_subject_id: 'team_123',
    });
  });

  it('returns an existing checkout session for matching caller idempotency retries', async () => {
    const existingCheckoutSessionRow = {
      ...checkoutSessionRow,
      id: 'f3478541-f24c-4833-a060-b81691a761ef',
      checkoutSessionId: 'cs_existing_123',
      url: 'https://checkout.stripe.com/c/pay/cs_existing_123',
    };
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_checkout_sessions/i.test(sql)) {
          return Promise.resolve({ rowCount: 0, rows: [] });
        }

        if (/FROM payments\.stripe_checkout_sessions[\s\S]*idempotency_key = \$2/i.test(sql)) {
          return Promise.resolve({ rows: [existingCheckoutSessionRow] });
        }

        return Promise.resolve({ rowCount: 1, rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);

    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'subscription',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
          customerEmail: 'buyer@example.com',
          subject: { type: 'team', id: 'team_123' },
          idempotencyKey: 'checkout-123',
        },
        checkoutUser
      )
    ).resolves.toMatchObject({
      checkoutSession: {
        id: existingCheckoutSessionRow.id,
        checkoutSessionId: 'cs_existing_123',
      },
    });

    expect(mockProvider.createCheckoutSession).not.toHaveBeenCalled();
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/SET LOCAL ROLE authenticated/i)
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/FROM payments\.stripe_checkout_sessions[\s\S]*idempotency_key = \$2/i),
      [
        'test',
        'checkout-123',
        expect.stringMatching(/^[a-f0-9]{64}$/),
        'subscription',
        'team',
        'team_123',
        'buyer@example.com',
        JSON.stringify([{ priceId: 'price_123', quantity: 1 }]),
        'https://example.com/success',
        'https://example.com/cancel',
        'insforge_checkout_session_id',
        JSON.stringify({
          insforge_checkout_mode: 'subscription',
          insforge_subject_type: 'team',
          insforge_subject_id: 'team_123',
        }),
      ]
    );
    expect(mockPool.query).not.toHaveBeenCalled();
  });

  it('resumes incomplete idempotent checkout rows instead of returning unusable rows', async () => {
    const existingCheckoutSessionRow = {
      ...checkoutSessionRow,
      id: 'f3478541-f24c-4833-a060-b81691a761ef',
      status: 'initialized',
      checkoutSessionId: null,
      customerId: null,
      url: null,
    };
    const openedCheckoutSessionRow = {
      ...checkoutSessionRow,
      id: existingCheckoutSessionRow.id,
      checkoutSessionId: 'cs_test_123',
      url: 'https://checkout.stripe.com/c/pay/cs_test_123',
    };
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_checkout_sessions/i.test(sql)) {
          return Promise.resolve({ rowCount: 0, rows: [] });
        }

        if (/FROM payments\.stripe_checkout_sessions[\s\S]*idempotency_key = \$2/i.test(sql)) {
          return Promise.resolve({ rows: [existingCheckoutSessionRow] });
        }

        return Promise.resolve({ rowCount: 1, rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockImplementation((sql: string) => {
      if (/SELECT customer_id AS "providerCustomerId"/i.test(sql)) {
        return Promise.resolve({ rows: [] });
      }

      if (/UPDATE payments\.stripe_checkout_sessions/i.test(sql)) {
        return Promise.resolve({ rows: [openedCheckoutSessionRow] });
      }

      return Promise.resolve({ rowCount: 1, rows: [] });
    });

    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'subscription',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
          customerEmail: 'buyer@example.com',
          subject: { type: 'team', id: 'team_123' },
          idempotencyKey: 'checkout-123',
        },
        checkoutUser
      )
    ).resolves.toMatchObject({
      checkoutSession: {
        id: existingCheckoutSessionRow.id,
        checkoutSessionId: 'cs_test_123',
        url: 'https://checkout.stripe.com/c/pay/cs_test_123',
      },
    });

    expect(mockProvider.createCheckoutSession).toHaveBeenCalledWith({
      mode: 'subscription',
      lineItems: [{ priceId: 'price_123', quantity: 1 }],
      successUrl: 'https://example.com/success',
      cancelUrl: 'https://example.com/cancel',
      customerId: null,
      customerEmail: 'buyer@example.com',
      clientReferenceId: existingCheckoutSessionRow.id,
      metadata: {
        insforge_checkout_mode: 'subscription',
        insforge_subject_type: 'team',
        insforge_subject_id: 'team_123',
        insforge_checkout_session_id: existingCheckoutSessionRow.id,
      },
      idempotencyKey: 'insforge:test:checkout_session:checkout-123',
    });
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/UPDATE payments\.stripe_checkout_sessions/i),
      expect.arrayContaining([existingCheckoutSessionRow.id, expect.any(String)])
    );
  });

  it('does not expose idempotent checkout rows hidden by checkout session RLS', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_checkout_sessions/i.test(sql)) {
          return Promise.resolve({ rowCount: 0, rows: [] });
        }

        if (/FROM payments\.stripe_checkout_sessions[\s\S]*idempotency_key = \$2/i.test(sql)) {
          return Promise.resolve({ rows: [] });
        }

        return Promise.resolve({ rowCount: 1, rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);

    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'subscription',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
          customerEmail: 'buyer@example.com',
          subject: { type: 'team', id: 'team_123' },
          idempotencyKey: 'checkout-123',
        },
        checkoutUser
      )
    ).rejects.toThrow(/Idempotency key is already used/);

    expect(mockProvider.createCheckoutSession).not.toHaveBeenCalled();
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/SET LOCAL ROLE authenticated/i)
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/FROM payments\.stripe_checkout_sessions[\s\S]*idempotency_key = \$2/i),
      [
        'test',
        'checkout-123',
        expect.stringMatching(/^[a-f0-9]{64}$/),
        'subscription',
        'team',
        'team_123',
        'buyer@example.com',
        JSON.stringify([{ priceId: 'price_123', quantity: 1 }]),
        'https://example.com/success',
        'https://example.com/cancel',
        'insforge_checkout_session_id',
        JSON.stringify({
          insforge_checkout_mode: 'subscription',
          insforge_subject_type: 'team',
          insforge_subject_id: 'team_123',
        }),
      ]
    );
    expect(mockClient.query).toHaveBeenCalledWith('ROLLBACK');
    expect(mockPool.query).not.toHaveBeenCalled();
  });

  it('reuses an existing Stripe customer mapping for identified checkout', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ providerCustomerId: 'cus_existing' }],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            ...checkoutSessionRow,
            id: '0dd01831-1742-4f1f-9622-f2274ca7f6c5',
            mode: 'payment',
            subjectType: 'organization',
            subjectId: 'org_123',
            customerId: 'cus_existing',
          },
        ],
      });

    await stripeServices.createCheckoutSession(
      {
        environment: 'test',
        mode: 'payment',
        lineItems: [{ priceId: 'price_123', quantity: 1 }],
        successUrl: 'https://example.com/success',
        cancelUrl: 'https://example.com/cancel',
        subject: { type: 'organization', id: 'org_123' },
      },
      checkoutUser
    );

    expect(mockProvider.createCustomer).not.toHaveBeenCalled();
    expect(mockProvider.createCheckoutSession).toHaveBeenCalledWith(
      expect.objectContaining({
        customerId: 'cus_existing',
        customerEmail: null,
        metadata: {
          insforge_checkout_mode: 'payment',
          insforge_subject_type: 'organization',
          insforge_subject_id: 'org_123',
          insforge_checkout_session_id: expect.any(String),
        },
        clientReferenceId: expect.any(String),
        idempotencyKey: expect.stringMatching(/^insforge:test:checkout_session:/),
      })
    );
  });

  it('asks Stripe to create a Customer for identified one-time checkout without a mapping', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValueOnce({ rows: [] }).mockResolvedValueOnce({
      rows: [
        {
          ...checkoutSessionRow,
          id: '81b9f7aa-87c7-4f5a-8d3b-9112730f13af',
          mode: 'payment',
          subjectType: 'organization',
          subjectId: 'org_123',
          customerEmail: 'buyer@example.com',
          customerId: 'cus_123',
        },
      ],
    });

    await stripeServices.createCheckoutSession(
      {
        environment: 'test',
        mode: 'payment',
        lineItems: [{ priceId: 'price_123', quantity: 1 }],
        successUrl: 'https://example.com/success',
        cancelUrl: 'https://example.com/cancel',
        customerEmail: 'buyer@example.com',
        subject: { type: 'organization', id: 'org_123' },
      },
      checkoutUser
    );

    expect(mockProvider.createCustomer).not.toHaveBeenCalled();
    expect(mockProvider.createCheckoutSession).toHaveBeenCalledWith(
      expect.objectContaining({
        mode: 'payment',
        customerId: null,
        customerEmail: 'buyer@example.com',
        customerCreation: 'always',
        metadata: {
          insforge_checkout_mode: 'payment',
          insforge_subject_type: 'organization',
          insforge_subject_id: 'org_123',
          insforge_checkout_session_id: expect.any(String),
        },
      })
    );
  });

  it('allows anonymous one-time checkout without creating a customer mapping', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          ...checkoutSessionRow,
          id: '7b98f4d1-880f-4e2a-b9f2-414398e376a1',
          mode: 'payment',
          subjectType: null,
          subjectId: null,
          customerEmail: 'anon@example.com',
        },
      ],
    });

    await stripeServices.createCheckoutSession(
      {
        environment: 'test',
        mode: 'payment',
        lineItems: [{ priceId: 'price_123', quantity: 2 }],
        successUrl: 'https://example.com/success',
        cancelUrl: 'https://example.com/cancel',
        customerEmail: 'anon@example.com',
      },
      anonCheckoutUser
    );

    expect(mockProvider.createCustomer).not.toHaveBeenCalled();
    expect(mockProvider.createCheckoutSession).toHaveBeenCalledWith({
      mode: 'payment',
      lineItems: [{ priceId: 'price_123', quantity: 2 }],
      successUrl: 'https://example.com/success',
      cancelUrl: 'https://example.com/cancel',
      customerId: null,
      customerEmail: 'anon@example.com',
      clientReferenceId: expect.any(String),
      metadata: {
        insforge_checkout_mode: 'payment',
        insforge_checkout_session_id: expect.any(String),
      },
      idempotencyKey: expect.stringMatching(/^insforge:test:checkout_session:/),
    });
    expect(mockPool.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/payments\.customer_mappings/i),
      expect.any(Array)
    );
  });

  it('rejects caller-controlled InsForge checkout metadata before creating Stripe checkout', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);

    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'payment',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
          customerEmail: 'anon@example.com',
          metadata: {
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_victim',
          },
        },
        anonCheckoutUser
      )
    ).rejects.toThrow(/reserved for InsForge/i);

    expect(mockProvider.createCheckoutSession).not.toHaveBeenCalled();
    expect(mockProvider.createCustomer).not.toHaveBeenCalled();
    expect(mockClient.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_checkout_sessions/i),
      expect.any(Array)
    );
  });

  it('does not call Stripe when stripe_checkout_sessions RLS denies the insert', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_checkout_sessions/i.test(sql)) {
          return Promise.reject({ code: '42501', message: 'new row violates row-level security' });
        }

        return Promise.resolve({ rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);

    await expect(
      stripeServices.createCheckoutSession(
        {
          environment: 'test',
          mode: 'payment',
          lineItems: [{ priceId: 'price_123', quantity: 1 }],
          successUrl: 'https://example.com/success',
          cancelUrl: 'https://example.com/cancel',
          subject: { type: 'team', id: 'team_123' },
        },
        checkoutUser
      )
    ).rejects.toThrow(/RLS policies/i);

    expect(mockProvider.createCheckoutSession).not.toHaveBeenCalled();
    expect(mockProvider.createCustomer).not.toHaveBeenCalled();
  });

  it('creates a Stripe customer portal session from an existing billing subject mapping', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_customer_portal_sessions/i.test(sql)) {
          return Promise.resolve({ rows: [customerPortalSessionRow] });
        }

        return Promise.resolve({ rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ providerCustomerId: 'cus_123' }],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            ...customerPortalSessionRow,
            status: 'created',
            customerId: 'cus_123',
            url: 'https://billing.stripe.com/p/session/test_123',
          },
        ],
      });

    await expect(
      stripeServices.createCustomerPortalSession(
        {
          environment: 'test',
          subject: { type: 'team', id: 'team_123' },
          returnUrl: 'https://example.com/account',
          configuration: 'bpc_123',
        },
        checkoutUser
      )
    ).resolves.toEqual({
      customerPortalSession: {
        id: customerPortalSessionRow.id,
        environment: 'test',
        status: 'created',
        subjectType: 'team',
        subjectId: 'team_123',
        customerId: 'cus_123',
        returnUrl: 'https://example.com/account',
        configuration: 'bpc_123',
        url: 'https://billing.stripe.com/p/session/test_123',
        lastError: null,
        createdAt: '2026-04-29T00:00:00.000Z',
        updatedAt: '2026-04-29T00:00:00.000Z',
      },
    });

    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/SET LOCAL ROLE authenticated/i)
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_customer_portal_sessions/i),
      expect.arrayContaining(['test', 'team', 'team_123', 'https://example.com/account', 'bpc_123'])
    );
    expect(mockProvider.createCustomerPortalSession).toHaveBeenCalledWith({
      customerId: 'cus_123',
      returnUrl: 'https://example.com/account',
      configuration: 'bpc_123',
    });
  });

  it('rejects customer portal sessions when the billing subject has no Stripe customer mapping', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_customer_portal_sessions/i.test(sql)) {
          return Promise.resolve({
            rows: [{ ...customerPortalSessionRow, subjectId: 'missing_team' }],
          });
        }

        return Promise.resolve({ rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query.mockResolvedValue({ rows: [] });

    await expect(
      stripeServices.createCustomerPortalSession(
        {
          environment: 'test',
          subject: { type: 'team', id: 'missing_team' },
          returnUrl: 'https://example.com/account',
        },
        checkoutUser
      )
    ).rejects.toThrow(/No Stripe customer is mapped/i);

    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_customer_portal_sessions/i),
      expect.any(Array)
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/UPDATE payments\.stripe_customer_portal_sessions/i),
      [expect.any(String), expect.stringMatching(/No Stripe customer is mapped/i)]
    );
    expect(mockProvider.createCustomerPortalSession).not.toHaveBeenCalled();
  });

  it('does not call Stripe when stripe_customer_portal_sessions RLS denies the insert', async () => {
    const mockClient = {
      query: vi.fn().mockImplementation((sql: string) => {
        if (/INSERT INTO payments\.stripe_customer_portal_sessions/i.test(sql)) {
          return Promise.reject({ code: '42501', message: 'new row violates row-level security' });
        }

        return Promise.resolve({ rows: [] });
      }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);

    await expect(
      stripeServices.createCustomerPortalSession(
        {
          environment: 'test',
          subject: { type: 'team', id: 'team_123' },
          returnUrl: 'https://example.com/account',
        },
        checkoutUser
      )
    ).rejects.toThrow(/stripe_customer_portal_sessions RLS policies/i);

    expect(mockProvider.createCustomerPortalSession).not.toHaveBeenCalled();
    expect(mockPool.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/customer_mappings/i),
      expect.any(Array)
    );
  });

  it('rejects anonymous customer portal sessions', async () => {
    await expect(
      stripeServices.createCustomerPortalSession(
        {
          environment: 'test',
          subject: { type: 'team', id: 'team_123' },
          returnUrl: 'https://example.com/account',
        },
        anonCheckoutUser
      )
    ).rejects.toThrow(/authenticated user/i);

    expect(mockPool.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/customer_mappings/i),
      expect.any(Array)
    );
    expect(mockProvider.createCustomerPortalSession).not.toHaveBeenCalled();
  });

  it('stores duplicate processed Stripe webhook events without reprocessing', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_123',
      type: 'checkout.session.completed',
      livemode: false,
      data: { object: { id: 'cs_test_123', object: 'checkout.session' } },
    });
    mockPool.query
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook('test', Buffer.from('{"id":"evt_123"}'), 'sig_123')
    ).resolves.toMatchObject({
      received: true,
      handled: false,
      event: {
        eventId: 'evt_123',
        processingStatus: 'processed',
      },
    });

    expect(mockPool.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.transactions/i),
      expect.any(Array)
    );
  });

  it('records one-time transactions from checkout.session.completed webhooks', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_123',
      type: 'checkout.session.completed',
      created: 1777334700,
      livemode: false,
      data: {
        object: {
          id: 'cs_test_123',
          object: 'checkout.session',
          mode: 'payment',
          payment_status: 'paid',
          amount_total: 4500,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          customer_details: { email: 'buyer@example.com' },
          payment_intent: 'pi_123',
          subscription: null,
          metadata: {
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_123',
          },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook('test', Buffer.from('{"id":"evt_123"}'), 'sig_123')
    ).resolves.toMatchObject({
      received: true,
      handled: true,
      event: {
        eventId: 'evt_123',
        processingStatus: 'processed',
      },
    });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'payment_intent',
        'pi_123',
        'one_time_payment',
        'succeeded',
        'team',
        'team_123',
        'cus_123',
        'buyer@example.com',
        4500,
        'usd',
        new Date('2026-04-28T00:05:00.000Z'),
        new Date('2026-04-28T00:00:00.000Z'),
        expect.objectContaining({ id: 'cs_test_123' }),
      ])
    );
    const transactionCall = mockPool.query.mock.calls.find(([sql]) =>
      /WITH refs[\s\S]*INSERT INTO payments\.transactions/i.test(String(sql))
    );
    expect(String(transactionCall?.[0])).toMatch(/RETURNING\s+tx\.id/i);
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/WITH refund_totals[\s\S]*UPDATE payments\.transactions original/i),
      ['test', 'pi_123', null]
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.customer_mappings/i),
      ['test', 'team', 'team_123', 'cus_123']
    );
  });

  it('does not mark webhook events failed when finalization fails after processing', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_postprocess_123',
      type: 'checkout.session.completed',
      created: 1777334700,
      livemode: false,
      data: {
        object: {
          id: 'cs_postprocess_123',
          object: 'checkout.session',
          mode: 'payment',
          payment_status: 'paid',
          amount_total: 4500,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          customer_details: { email: 'buyer@example.com' },
          payment_intent: 'pi_postprocess_123',
          subscription: null,
          metadata: {
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_123',
          },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_postprocess_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_postprocess_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockRejectedValueOnce(new Error('mark processed failed'));

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_postprocess_123"}'),
        'sig_123'
      )
    ).rejects.toThrow('mark processed failed');

    expect(mockPool.query).toHaveBeenCalledTimes(6);
    expect(mockLogger.error).toHaveBeenCalledWith(
      'Failed to finalize Stripe webhook event after processing',
      {
        environment: 'test',
        eventId: 'evt_postprocess_123',
        handled: true,
        error: 'mark processed failed',
      }
    );
  });

  it('marks webhook events failed when applying the webhook event fails', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_apply_fail_123',
      type: 'invoice.paid',
      livemode: false,
      data: {
        object: {
          id: 'in_apply_fail_123',
          object: 'invoice',
          amount_due: 9900,
          amount_paid: 9900,
          currency: 'usd',
          customer: 'cus_123',
          customer_email: 'buyer@example.com',
          description: 'Failure invoice',
          created: 1777334400,
          status_transitions: { paid_at: 1777334700 },
          lines: { data: [] },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_apply_fail_123',
            eventType: 'invoice.paid',
            livemode: false,
            accountId: null,
            objectType: 'invoice',
            objectId: 'in_apply_fail_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockRejectedValueOnce(new Error('activity write failed'))
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_apply_fail_123',
            eventType: 'invoice.paid',
            livemode: false,
            accountId: null,
            objectType: 'invoice',
            objectId: 'in_apply_fail_123',
            processingStatus: 'failed',
            attemptCount: 1,
            lastError: 'activity write failed',
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_apply_fail_123"}'),
        'sig_123'
      )
    ).rejects.toThrow('activity write failed');

    expect(mockPool.query).toHaveBeenLastCalledWith(
      expect.stringMatching(/UPDATE payments\.webhook_events/i),
      ['stripe', 'test', 'evt_apply_fail_123', 'failed', 'activity write failed']
    );
  });

  it('maps customer identity for completed delayed checkout sessions before payment settles', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_delayed_completed_123',
      type: 'checkout.session.completed',
      livemode: false,
      data: {
        object: {
          id: 'cs_test_delayed_123',
          object: 'checkout.session',
          mode: 'payment',
          payment_status: 'unpaid',
          amount_total: 4500,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          customer_details: { email: 'buyer@example.com' },
          payment_intent: 'pi_delayed_123',
          subscription: null,
          metadata: {
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_123',
          },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_delayed_completed_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_delayed_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_delayed_completed_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_delayed_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_delayed_completed_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.customer_mappings/i),
      ['test', 'team', 'team_123', 'cus_123']
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining(['test', 'pending', 'team', 'team_123', 'cus_123'])
    );
  });

  it('deduplicates one-time checkout activity by checkout session when PaymentIntent is absent', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_no_payment_required_123',
      type: 'checkout.session.completed',
      livemode: false,
      data: {
        object: {
          id: 'cs_test_no_payment_required_123',
          object: 'checkout.session',
          mode: 'payment',
          payment_status: 'no_payment_required',
          amount_total: 0,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          customer_details: { email: 'buyer@example.com' },
          payment_intent: null,
          subscription: null,
          metadata: {
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_123',
          },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_no_payment_required_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_no_payment_required_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_no_payment_required_123',
            eventType: 'checkout.session.completed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_no_payment_required_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_no_payment_required_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'checkout_session',
        'cs_test_no_payment_required_123',
        'one_time_payment',
        'pending',
        'team',
        'team_123',
        'cus_123',
        'buyer@example.com',
        0,
        'usd',
        new Date('2026-04-28T00:00:00.000Z'),
        expect.objectContaining({ id: 'cs_test_no_payment_required_123' }),
      ])
    );
  });

  it('records failed delayed checkout payments from async failure webhooks', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_async_failed_123',
      type: 'checkout.session.async_payment_failed',
      created: 1777335000,
      livemode: false,
      data: {
        object: {
          id: 'cs_test_async_123',
          object: 'checkout.session',
          mode: 'payment',
          payment_status: 'unpaid',
          amount_total: 4500,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          customer_details: { email: 'buyer@example.com' },
          payment_intent: 'pi_async_123',
          subscription: null,
          metadata: {
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_123',
          },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_async_failed_123',
            eventType: 'checkout.session.async_payment_failed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_async_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_async_failed_123',
            eventType: 'checkout.session.async_payment_failed',
            livemode: false,
            accountId: null,
            objectType: 'checkout.session',
            objectId: 'cs_test_async_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_async_failed_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'payment_intent',
        'pi_async_123',
        'one_time_payment',
        'failed',
        'team',
        'team_123',
        'cus_123',
        'buyer@example.com',
        4500,
        'usd',
        new Date('2026-04-28T00:10:00.000Z'),
        expect.objectContaining({ id: 'cs_test_async_123' }),
      ])
    );
  });

  it('records subscription invoice transactions from invoice webhooks', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_invoice_123',
      type: 'invoice.paid',
      livemode: false,
      data: {
        object: {
          id: 'in_123',
          object: 'invoice',
          amount_due: 9900,
          amount_paid: 9900,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          customer_email: 'buyer@example.com',
          description: 'Subscription invoice',
          number: 'INV-123',
          metadata: {},
          status_transitions: { paid_at: 1777334500 },
          parent: {
            type: 'subscription_details',
            quote_details: null,
            subscription_details: {
              subscription: 'sub_123',
              metadata: {
                insforge_subject_type: 'organization',
                insforge_subject_id: 'org_123',
              },
            },
          },
          payments: {
            data: [
              {
                payment: {
                  type: 'payment_intent',
                  payment_intent: 'pi_invoice_123',
                },
              },
            ],
          },
          lines: {
            data: [
              {
                pricing: {
                  price_details: {
                    product: 'prod_123',
                    price: 'price_123',
                  },
                },
              },
            ],
          },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_invoice_123',
            eventType: 'invoice.paid',
            livemode: false,
            accountId: null,
            objectType: 'invoice',
            objectId: 'in_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rowCount: 1, rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_invoice_123',
            eventType: 'invoice.paid',
            livemode: false,
            accountId: null,
            objectType: 'invoice',
            objectId: 'in_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook('test', Buffer.from('{"id":"evt_invoice_123"}'), 'sig_123')
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'payment_intent',
        'pi_invoice_123',
        'subscription_invoice',
        'succeeded',
        'organization',
        'org_123',
        'cus_123',
        'buyer@example.com',
        9900,
        'usd',
        'Subscription invoice',
        new Date('2026-04-28T00:01:40.000Z'),
        new Date('2026-04-28T00:00:00.000Z'),
        expect.objectContaining({ id: 'in_123' }),
      ])
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /related_object_ids = tx\.related_object_ids \|\| EXCLUDED\.related_object_ids/i
      ),
      expect.any(Array)
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.customer_mappings[\s\S]*ON CONFLICT DO NOTHING/i
      ),
      ['stripe', 'test', 'organization', 'org_123', 'cus_123']
    );
  });

  it('resolves invoice subjects from existing customer mappings without backfilling', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_invoice_456',
      type: 'invoice.paid',
      livemode: false,
      data: {
        object: {
          id: 'in_456',
          object: 'invoice',
          amount_due: 9900,
          amount_paid: 9900,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_456',
          customer_email: 'buyer@example.com',
          description: 'Subscription invoice',
          number: 'INV-456',
          metadata: {},
          status_transitions: { paid_at: 1777334500 },
          parent: {
            type: 'subscription_details',
            quote_details: null,
            subscription_details: {
              subscription: 'sub_456',
              metadata: {},
            },
          },
          payments: {
            data: [
              {
                payment: {
                  type: 'payment_intent',
                  payment_intent: 'pi_invoice_456',
                },
              },
            ],
          },
          lines: { data: [] },
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_invoice_456',
            eventType: 'invoice.paid',
            livemode: false,
            accountId: null,
            objectType: 'invoice',
            objectId: 'in_456',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [{ subjectType: 'user', subjectId: 'user_456' }] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_invoice_456',
            eventType: 'invoice.paid',
            livemode: false,
            accountId: null,
            objectType: 'invoice',
            objectId: 'in_456',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook('test', Buffer.from('{"id":"evt_invoice_456"}'), 'sig_123')
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/FROM payments\.customer_mappings/i),
      ['test', 'cus_456']
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining(['test', 'payment_intent', 'pi_invoice_456', 'user', 'user_456'])
    );
    expect(mockPool.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.customer_mappings/i),
      expect.any(Array)
    );
  });

  it('ignores PaymentIntent webhooks that are not InsForge one-time checkout payments', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_pi_invoice_123',
      type: 'payment_intent.succeeded',
      livemode: false,
      data: {
        object: {
          id: 'pi_invoice_123',
          object: 'payment_intent',
          amount: 9900,
          amount_received: 9900,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          latest_charge: 'ch_123',
          metadata: {},
          receipt_email: null,
          description: null,
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_pi_invoice_123',
            eventType: 'payment_intent.succeeded',
            livemode: false,
            accountId: null,
            objectType: 'payment_intent',
            objectId: 'pi_invoice_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_pi_invoice_123',
            eventType: 'payment_intent.succeeded',
            livemode: false,
            accountId: null,
            objectType: 'payment_intent',
            objectId: 'pi_invoice_123',
            processingStatus: 'ignored',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_pi_invoice_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({
      received: true,
      handled: false,
      event: { processingStatus: 'ignored' },
    });

    expect(mockPool.query).not.toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.transactions/i),
      expect.any(Array)
    );
  });

  it('records InsForge one-time PaymentIntent webhooks with non-refund uniqueness', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_pi_checkout_123',
      type: 'payment_intent.succeeded',
      livemode: false,
      data: {
        object: {
          id: 'pi_checkout_123',
          object: 'payment_intent',
          amount: 4500,
          amount_received: 4500,
          currency: 'usd',
          created: 1777334400,
          customer: 'cus_123',
          latest_charge: 'ch_123',
          metadata: {
            insforge_checkout_mode: 'payment',
            insforge_subject_type: 'team',
            insforge_subject_id: 'team_123',
          },
          receipt_email: 'buyer@example.com',
          description: 'One-time checkout',
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_pi_checkout_123',
            eventType: 'payment_intent.succeeded',
            livemode: false,
            accountId: null,
            objectType: 'payment_intent',
            objectId: 'pi_checkout_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_pi_checkout_123',
            eventType: 'payment_intent.succeeded',
            livemode: false,
            accountId: null,
            objectType: 'payment_intent',
            objectId: 'pi_checkout_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_pi_checkout_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'payment_intent',
        'pi_checkout_123',
        'one_time_payment',
        'succeeded',
        'team',
        'team_123',
        'cus_123',
        'buyer@example.com',
        4500,
        'usd',
        'One-time checkout',
        new Date('2026-04-28T00:00:00.000Z'),
        new Date('2026-04-28T00:00:00.000Z'),
        expect.objectContaining({ id: 'pi_checkout_123' }),
      ])
    );
    const transactionSql = mockPool.query.mock.calls.find(([sql]) =>
      /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i.test(
        String(sql)
      )
    )?.[0];
    expect(String(transactionSql)).toMatch(
      /ORDER BY\s+CASE WHEN tx\.provider_object_type = \$2 AND tx\.provider_object_id = \$3 THEN 0 ELSE 1 END,\s+tx\.created_at DESC/i
    );
    expect(String(transactionSql)).toMatch(
      /ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)[\s\S]*DO UPDATE SET[\s\S]*amount_refunded = COALESCE\(\$14, tx\.amount_refunded, 0\)/i
    );
  });

  it('records refund activity and copies context from the original payment', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_refund_123',
      type: 'refund.created',
      livemode: false,
      data: {
        object: {
          id: 're_123',
          object: 'refund',
          amount: 2500,
          currency: 'usd',
          created: 1777334600,
          charge: 'ch_123',
          payment_intent: 'pi_invoice_123',
          reason: 'requested_by_customer',
          description: null,
          status: 'succeeded',
          metadata: {},
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_refund_123',
            eventType: 'refund.created',
            livemode: false,
            accountId: null,
            objectType: 'refund',
            objectId: 're_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            subjectType: 'organization',
            subjectId: 'org_123',
            customerId: 'cus_123',
            customerEmailSnapshot: 'buyer@example.com',
            invoiceId: 'in_123',
            subscriptionId: 'sub_123',
            productId: 'prod_123',
            priceId: 'price_123',
            description: 'Subscription invoice',
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_refund_123',
            eventType: 'refund.created',
            livemode: false,
            accountId: null,
            objectType: 'refund',
            objectId: 're_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook('test', Buffer.from('{"id":"evt_refund_123"}'), 'sig_123')
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'refund',
        're_123',
        'payment_intent',
        'pi_invoice_123',
        'refund',
        'refunded',
        'organization',
        'org_123',
        'buyer@example.com',
        2500,
        'usd',
        'requested_by_customer',
        new Date('2026-04-28T00:03:20.000Z'),
        new Date('2026-04-28T00:03:20.000Z'),
        expect.objectContaining({ id: 're_123' }),
      ])
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /DO UPDATE SET[\s\S]*subject_type = COALESCE\(EXCLUDED\.subject_type, tx\.subject_type\)[\s\S]*related_object_ids = tx\.related_object_ids \|\| EXCLUDED\.related_object_ids/i
      ),
      expect.any(Array)
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /WITH refund_totals[\s\S]*UPDATE payments\.transactions original[\s\S]*related_object_ids = original_context\.related_object_ids \|\| refund\.related_object_ids/i
      ),
      ['test', 'pi_invoice_123', 'ch_123']
    );
  });

  it('hydrates refund context from Stripe when refund arrives before the payment event', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_refund_early_123',
      type: 'refund.created',
      livemode: false,
      data: {
        object: {
          id: 're_early_123',
          object: 'refund',
          amount: 1500,
          currency: 'usd',
          created: 1777334600,
          charge: 'ch_early_123',
          payment_intent: 'pi_early_123',
          reason: 'requested_by_customer',
          description: null,
          status: 'succeeded',
          metadata: {},
        },
      },
    });
    mockProvider.retrievePaymentIntent.mockResolvedValueOnce({
      id: 'pi_early_123',
      object: 'payment_intent',
      status: 'succeeded',
      customer: 'cus_early_123',
      latest_charge: 'ch_early_123',
      amount: 4500,
      amount_received: 4500,
      currency: 'usd',
      description: 'Early checkout',
      receipt_email: 'early@example.com',
      created: 1777334400,
      metadata: {
        insforge_checkout_mode: 'payment',
        insforge_subject_type: 'team',
        insforge_subject_id: 'team_early_123',
      },
    } as unknown as StripePaymentIntent);
    mockProvider.retrieveCharge.mockResolvedValueOnce({
      id: 'ch_early_123',
      object: 'charge',
      customer: 'cus_early_123',
      payment_intent: 'pi_early_123',
      amount_refunded: 1500,
      refunded: false,
      refunds: { data: [] },
      billing_details: { email: 'early@example.com' },
      description: 'Early checkout',
      metadata: {},
    } as unknown as StripeCharge);
    mockProvider.retrieveInvoiceByPaymentIntent.mockResolvedValueOnce(null);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_refund_early_123',
            eventType: 'refund.created',
            livemode: false,
            accountId: null,
            objectType: 'refund',
            objectId: 're_early_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            subjectType: 'team',
            subjectId: 'team_early_123',
            customerId: 'cus_early_123',
            customerEmailSnapshot: 'early@example.com',
            invoiceId: null,
            subscriptionId: null,
            productId: null,
            priceId: null,
            description: 'Early checkout',
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_refund_early_123',
            eventType: 'refund.created',
            livemode: false,
            accountId: null,
            objectType: 'refund',
            objectId: 're_early_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_refund_early_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockProvider.retrievePaymentIntent).toHaveBeenCalledWith('pi_early_123');
    expect(mockProvider.retrieveCharge).toHaveBeenCalledWith('ch_early_123');
    expect(mockProvider.retrieveInvoiceByPaymentIntent).toHaveBeenCalledWith('pi_early_123');
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'payment_intent',
        'pi_early_123',
        'one_time_payment',
        'succeeded',
        'team',
        'team_early_123',
        'cus_early_123',
        'early@example.com',
        4500,
        'usd',
        'Early checkout',
        new Date('2026-04-28T00:00:00.000Z'),
        new Date('2026-04-28T00:00:00.000Z'),
        expect.objectContaining({ id: 'pi_early_123' }),
      ])
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*provider_parent_object_type/i
      ),
      expect.arrayContaining([
        'test',
        'refund',
        're_early_123',
        'payment_intent',
        'pi_early_123',
        'refund',
        'refunded',
        'team',
        'team_early_123',
        'early@example.com',
      ])
    );
  });

  it('hydrates subscription refund context from Stripe invoice payments when refund arrives first', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_refund_sub_early_123',
      type: 'refund.created',
      livemode: false,
      data: {
        object: {
          id: 're_sub_early_123',
          object: 'refund',
          amount: 3300,
          currency: 'usd',
          created: 1777334600,
          charge: 'ch_sub_early_123',
          payment_intent: 'pi_sub_early_123',
          reason: 'requested_by_customer',
          description: null,
          status: 'succeeded',
          metadata: {},
        },
      },
    });
    mockProvider.retrievePaymentIntent.mockResolvedValueOnce({
      id: 'pi_sub_early_123',
      object: 'payment_intent',
      status: 'succeeded',
      customer: 'cus_sub_early_123',
      latest_charge: 'ch_sub_early_123',
      amount: 9900,
      amount_received: 9900,
      currency: 'usd',
      description: null,
      receipt_email: null,
      created: 1777334400,
      metadata: {},
    } as unknown as StripePaymentIntent);
    mockProvider.retrieveCharge.mockResolvedValueOnce({
      id: 'ch_sub_early_123',
      object: 'charge',
      customer: 'cus_sub_early_123',
      payment_intent: 'pi_sub_early_123',
      amount_refunded: 3300,
      refunded: false,
      refunds: { data: [] },
      billing_details: { email: 'subscription@example.com' },
      description: null,
      metadata: {},
    } as unknown as StripeCharge);
    mockProvider.retrieveInvoiceByPaymentIntent.mockResolvedValueOnce({
      id: 'in_sub_early_123',
      object: 'invoice',
      amount_due: 9900,
      amount_paid: 9900,
      currency: 'usd',
      created: 1777334400,
      customer: 'cus_sub_early_123',
      customer_email: 'subscription@example.com',
      description: 'Subscription invoice',
      number: 'INV-SUB-123',
      metadata: {},
      status_transitions: { paid_at: 1777334500 },
      parent: {
        type: 'subscription_details',
        quote_details: null,
        subscription_details: {
          subscription: 'sub_early_123',
          metadata: {
            insforge_subject_type: 'organization',
            insforge_subject_id: 'org_early_123',
          },
        },
      },
      payments: {
        data: [
          {
            id: 'inpay_sub_early_123',
            payment: {
              type: 'payment_intent',
              payment_intent: 'pi_sub_early_123',
            },
          },
        ],
      },
      lines: {
        data: [
          {
            pricing: {
              price_details: {
                product: 'prod_sub_early_123',
                price: 'price_sub_early_123',
              },
            },
          },
        ],
      },
    } as unknown as StripeInvoice);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_refund_sub_early_123',
            eventType: 'refund.created',
            livemode: false,
            accountId: null,
            objectType: 'refund',
            objectId: 're_sub_early_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_refund_sub_early_123',
            eventType: 'refund.created',
            livemode: false,
            accountId: null,
            objectType: 'refund',
            objectId: 're_sub_early_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_refund_sub_early_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockProvider.retrieveInvoiceByPaymentIntent).toHaveBeenCalledWith('pi_sub_early_123');
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*ON CONFLICT \(provider, environment, provider_object_type, provider_object_id\)/i
      ),
      expect.arrayContaining([
        'test',
        'payment_intent',
        'pi_sub_early_123',
        'subscription_invoice',
        'succeeded',
        'organization',
        'org_early_123',
        'cus_sub_early_123',
        'subscription@example.com',
      ])
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.transactions[\s\S]*provider_parent_object_type/i
      ),
      expect.arrayContaining([
        'test',
        'refund',
        're_sub_early_123',
        'payment_intent',
        'pi_sub_early_123',
        'refund',
        'refunded',
        'organization',
        'org_early_123',
        'cus_sub_early_123',
        'subscription@example.com',
      ])
    );
  });

  it('upserts subscription projections from subscription webhooks', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_sub_123',
      type: 'customer.subscription.updated',
      livemode: false,
      data: {
        object: {
          id: 'sub_123',
          object: 'subscription',
          customer: 'cus_123',
          status: 'active',
          current_period_start: 1777334400,
          current_period_end: 1779926400,
          cancel_at_period_end: true,
          cancel_at: 1779926400,
          canceled_at: null,
          trial_start: null,
          trial_end: null,
          latest_invoice: 'in_123',
          metadata: {
            insforge_subject_type: 'organization',
            insforge_subject_id: 'org_123',
          },
          items: {
            data: [
              {
                id: 'si_123',
                object: 'subscription_item',
                quantity: 1,
                metadata: {},
                price: {
                  id: 'price_123',
                  product: 'prod_123',
                },
              },
            ],
          },
        },
      },
    });
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_sub_123',
            eventType: 'customer.subscription.updated',
            livemode: false,
            accountId: null,
            objectType: 'subscription',
            objectId: 'sub_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rowCount: 1, rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_sub_123',
            eventType: 'customer.subscription.updated',
            livemode: false,
            accountId: null,
            objectType: 'subscription',
            objectId: 'sub_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-04-28T00:00:00.000Z'),
            processedAt: new Date('2026-04-28T00:00:01.000Z'),
            createdAt: new Date('2026-04-28T00:00:00.000Z'),
            updatedAt: new Date('2026-04-28T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook('test', Buffer.from('{"id":"evt_sub_123"}'), 'sig_123')
    ).resolves.toMatchObject({ received: true, handled: true });

    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_subscriptions/i),
      expect.arrayContaining(['test', 'sub_123', 'cus_123', 'organization', 'org_123', 'active'])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_subscriptions/i),
      expect.arrayContaining([true, new Date(1779926400 * 1000)])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_subscription_items/i),
      expect.arrayContaining(['test', 'si_123', 'sub_123', 'prod_123', 'price_123', 1])
    );
    expect(mockClient.query).toHaveBeenCalledWith('COMMIT');
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /INSERT INTO payments\.customer_mappings[\s\S]*ON CONFLICT DO NOTHING/i
      ),
      ['stripe', 'test', 'organization', 'org_123', 'cus_123']
    );
  });

  it('syncs existing Stripe subscriptions as unmapped when no billing subject exists', async () => {
    const mockClient = {
      query: vi.fn().mockResolvedValue({ rowCount: 0, rows: [] }),
      release: vi.fn(),
    };
    mockPool.connect.mockResolvedValue(mockClient);
    mockPool.query
      .mockResolvedValueOnce({
        rows: [{ accountId: 'acct_123' }],
      })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rows: [connectedTestConnectionRow] });
    mockProvider.listSubscriptions.mockResolvedValueOnce([
      {
        id: 'sub_existing',
        object: 'subscription',
        customer: 'cus_existing',
        status: 'active',
        current_period_start: null,
        current_period_end: null,
        cancel_at_period_end: false,
        cancel_at: null,
        canceled_at: null,
        trial_start: null,
        trial_end: null,
        latest_invoice: 'in_existing',
        metadata: {},
        items: {
          has_more: true,
          data: [
            {
              id: 'si_existing',
              object: 'subscription_item',
              current_period_start: 1777334400,
              current_period_end: 1779926400,
              quantity: 1,
              metadata: {},
              price: {
                id: 'price_existing',
                product: 'prod_existing',
              },
            },
          ],
        },
      } as unknown as StripeSubscription,
    ]);
    mockProvider.listSubscriptionItems.mockResolvedValueOnce([
      {
        id: 'si_existing',
        object: 'subscription_item',
        current_period_start: 1777334400,
        current_period_end: 1779926400,
        quantity: 1,
        metadata: {},
        price: {
          id: 'price_existing',
          product: 'prod_existing',
        },
      },
      {
        id: 'si_extra',
        object: 'subscription_item',
        current_period_start: 1777248000,
        current_period_end: 1780012800,
        quantity: 2,
        metadata: {},
        price: {
          id: 'price_extra',
          product: 'prod_extra',
        },
      },
    ]);

    await expect(stripeServices.syncPayments({ environment: 'test' })).resolves.toMatchObject({
      results: [
        {
          subscriptions: {
            environment: 'test',
            synced: 1,
            unmapped: 1,
            deleted: 0,
          },
        },
      ],
    });

    expect(mockProvider.listSubscriptions).toHaveBeenCalledTimes(1);
    expect(mockProvider.listCustomers).toHaveBeenCalledTimes(1);
    expect(mockProvider.listSubscriptionItems).toHaveBeenCalledWith('sub_existing');
    expect(mockLogger.warn).not.toHaveBeenCalledWith(
      'Stripe subscription projection is missing InsForge billing subject',
      expect.any(Object)
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_subscriptions/i),
      expect.arrayContaining(['test', 'sub_existing', 'cus_existing', null, null, 'active'])
    );
    const subscriptionInsertCall = mockClient.query.mock.calls.find(([sql]) =>
      /INSERT INTO payments\.stripe_subscriptions/i.test(String(sql))
    );
    expect(subscriptionInsertCall?.[1]).toEqual(
      expect.arrayContaining([new Date(1777248000 * 1000), new Date(1780012800 * 1000)])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_subscription_items/i),
      expect.arrayContaining([
        'test',
        'si_existing',
        'sub_existing',
        'prod_existing',
        'price_existing',
        1,
      ])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.stripe_subscription_items/i),
      expect.arrayContaining(['test', 'si_extra', 'sub_existing', 'prod_extra', 'price_extra', 2])
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /DELETE FROM payments\.stripe_subscription_items[\s\S]*NOT \(subscription_item_id = ANY/i
      ),
      ['test', 'sub_existing', ['si_existing', 'si_extra']]
    );
    expect(mockClient.query).toHaveBeenCalledWith(
      expect.stringMatching(
        /DELETE FROM payments\.stripe_subscriptions[\s\S]*NOT \(subscription_id = ANY/i
      ),
      ['test', ['sub_existing']]
    );
  });

  it('lists transactions for an environment and billing subject', async () => {
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          environment: 'test',
          provider: 'stripe',
          type: 'one_time_payment',
          status: 'succeeded',
          subjectType: 'team',
          subjectId: 'team_123',
          providerCustomerId: 'cus_123',
          customerEmailSnapshot: 'buyer@example.com',
          providerReferenceId: 'pi_123',
          providerReferenceType: 'payment_intent',
          amount: '4500',
          amountRefunded: null,
          currency: 'usd',
          description: null,
          paidAt: new Date('2026-04-28T00:00:00.000Z'),
          failedAt: null,
          refundedAt: null,
          providerCreatedAt: new Date('2026-04-28T00:00:00.000Z'),
          createdAt: new Date('2026-04-28T00:00:01.000Z'),
          updatedAt: new Date('2026-04-28T00:00:01.000Z'),
        },
      ],
    });

    await expect(
      stripeServices.listTransactions({
        environment: 'test',
        subjectType: 'team',
        subjectId: 'team_123',
        limit: 25,
      })
    ).resolves.toEqual({
      transactions: [
        {
          environment: 'test',
          provider: 'stripe',
          type: 'one_time_payment',
          status: 'succeeded',
          subjectType: 'team',
          subjectId: 'team_123',
          providerCustomerId: 'cus_123',
          customerEmailSnapshot: 'buyer@example.com',
          providerReferenceId: 'pi_123',
          providerReferenceType: 'payment_intent',
          amount: 4500,
          amountRefunded: null,
          currency: 'usd',
          description: null,
          paidAt: '2026-04-28T00:00:00.000Z',
          failedAt: null,
          refundedAt: null,
          providerCreatedAt: '2026-04-28T00:00:00.000Z',
          createdAt: '2026-04-28T00:00:01.000Z',
          updatedAt: '2026-04-28T00:00:01.000Z',
        },
      ],
    });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/FROM payments\.transactions/i),
      ['test', 'stripe', 'team', 'team_123', 25]
    );
  });

  it('lists mirrored Stripe customers for one environment', async () => {
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          environment: 'test',
          provider: 'stripe',
          providerCustomerId: 'cus_123',
          email: 'buyer@example.com',
          name: 'Buyer Example',
          phone: '+1 555-0100',
          deleted: false,
          metadata: { segment: 'pro' },
          raw: {
            address: { country: 'us' },
            invoice_settings: {
              default_payment_method: {
                card: {
                  brand: 'visa',
                  last4: '4242',
                },
              },
            },
          },
          providerCreatedAt: new Date('2026-05-01T00:00:00.000Z'),
          syncedAt: new Date('2026-05-02T00:00:00.000Z'),
          paymentsCount: 3,
          lastPaymentAt: new Date('2026-05-03T12:30:00.000Z'),
          totalSpend: 4200,
          totalSpendCurrency: 'usd',
        },
      ],
    });

    await expect(
      stripeServices.listCustomers({
        environment: 'test',
        limit: 10,
      })
    ).resolves.toEqual({
      customers: [
        {
          environment: 'test',
          provider: 'stripe',
          providerCustomerId: 'cus_123',
          email: 'buyer@example.com',
          name: 'Buyer Example',
          phone: '+1 555-0100',
          deleted: false,
          metadata: { segment: 'pro' },
          providerCreatedAt: '2026-05-01T00:00:00.000Z',
          syncedAt: '2026-05-02T00:00:00.000Z',
          paymentsCount: 3,
          lastPaymentAt: '2026-05-03T12:30:00.000Z',
          totalSpend: 4200,
          totalSpendCurrency: 'usd',
          paymentMethodBrand: 'visa',
          paymentMethodLast4: '4242',
          countryCode: 'US',
        },
      ],
    });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/FROM payments\.customers/i),
      ['test', 10, 'stripe']
    );
  });

  it('lists mirrored Stripe customers without live Stripe enrichment', async () => {
    mockPool.query.mockResolvedValueOnce({
      rows: [
        {
          environment: 'test',
          provider: 'stripe',
          providerCustomerId: 'cus_sparse',
          email: 'sparse@example.com',
          name: 'Sparse Customer',
          phone: null,
          deleted: false,
          metadata: {},
          raw: {},
          providerCreatedAt: new Date('2026-05-01T00:00:00.000Z'),
          syncedAt: new Date('2026-05-02T00:00:00.000Z'),
          paymentsCount: 0,
          lastPaymentAt: null,
          totalSpend: null,
          totalSpendCurrency: null,
        },
      ],
    });

    await expect(
      stripeServices.listCustomers({
        environment: 'test',
        limit: 10,
      })
    ).resolves.toEqual({
      customers: [
        {
          environment: 'test',
          provider: 'stripe',
          providerCustomerId: 'cus_sparse',
          email: 'sparse@example.com',
          name: 'Sparse Customer',
          phone: null,
          deleted: false,
          metadata: {},
          providerCreatedAt: '2026-05-01T00:00:00.000Z',
          syncedAt: '2026-05-02T00:00:00.000Z',
          paymentsCount: 0,
          lastPaymentAt: null,
          totalSpend: null,
          totalSpendCurrency: null,
          paymentMethodBrand: null,
          paymentMethodLast4: null,
          countryCode: null,
        },
      ],
    });

    expect(mockGetSecretByKey).not.toHaveBeenCalled();
  });

  it('lists subscriptions with their subscription items', async () => {
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            subscriptionId: 'sub_123',
            customerId: 'cus_123',
            subjectType: 'organization',
            subjectId: 'org_123',
            status: 'active',
            currentPeriodStart: new Date('2026-04-28T00:00:00.000Z'),
            currentPeriodEnd: new Date('2026-05-28T00:00:00.000Z'),
            cancelAtPeriodEnd: false,
            cancelAt: null,
            canceledAt: null,
            trialStart: null,
            trialEnd: null,
            latestInvoiceId: 'in_123',
            metadata: { plan: 'pro' },
            syncedAt: new Date('2026-04-28T00:00:02.000Z'),
            createdAt: new Date('2026-04-28T00:00:01.000Z'),
            updatedAt: new Date('2026-04-28T00:00:02.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            subscriptionItemId: 'si_123',
            subscriptionId: 'sub_123',
            productId: 'prod_123',
            priceId: 'price_123',
            quantity: '1',
            metadata: {},
            createdAt: new Date('2026-04-28T00:00:01.000Z'),
            updatedAt: new Date('2026-04-28T00:00:02.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.listSubscriptions({
        environment: 'test',
        subjectType: 'organization',
        subjectId: 'org_123',
        limit: 10,
      })
    ).resolves.toMatchObject({
      subscriptions: [
        {
          environment: 'test',
          subscriptionId: 'sub_123',
          subjectType: 'organization',
          subjectId: 'org_123',
          status: 'active',
          items: [
            {
              subscriptionItemId: 'si_123',
              productId: 'prod_123',
              priceId: 'price_123',
              quantity: 1,
            },
          ],
        },
      ],
    });
  });

  it('records mirrored Stripe customers from customer.updated webhooks', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_customer_123',
      type: 'customer.updated',
      created: 1777334700,
      livemode: false,
      data: {
        object: {
          id: 'cus_123',
          object: 'customer',
          email: 'buyer@example.com',
          name: 'Buyer Example',
          phone: '+1 555-0100',
          metadata: { segment: 'pro' },
          created: 1777334400,
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_customer_123',
            eventType: 'customer.updated',
            livemode: false,
            accountId: null,
            objectType: 'customer',
            objectId: 'cus_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-05-02T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-05-02T00:00:00.000Z'),
            updatedAt: new Date('2026-05-02T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_customer_123',
            eventType: 'customer.updated',
            livemode: false,
            accountId: null,
            objectType: 'customer',
            objectId: 'cus_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-05-02T00:00:00.000Z'),
            processedAt: new Date('2026-05-02T00:00:01.000Z'),
            createdAt: new Date('2026-05-02T00:00:00.000Z'),
            updatedAt: new Date('2026-05-02T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_customer_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({
      received: true,
      handled: true,
      event: {
        eventId: 'evt_customer_123',
        processingStatus: 'processed',
      },
    });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.customers/i),
      [
        'test',
        'cus_123',
        'buyer@example.com',
        'Buyer Example',
        '+1 555-0100',
        false,
        { segment: 'pro' },
        expect.objectContaining({ id: 'cus_123' }),
        new Date('2026-04-28T00:00:00.000Z'),
        expect.any(Date),
        false,
      ]
    );
  });

  it('clears Stripe customer mappings when customer.deleted webhooks arrive', async () => {
    mockGetSecretByKey
      .mockResolvedValueOnce('whsec_test_123')
      .mockResolvedValueOnce('sk_test_1234567890');
    mockProvider.constructWebhookEvent.mockReturnValueOnce({
      id: 'evt_customer_deleted_123',
      type: 'customer.deleted',
      created: 1777334700,
      livemode: false,
      data: {
        object: {
          id: 'cus_deleted_123',
          object: 'customer',
          deleted: true,
        },
      },
    });
    mockPool.query
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_customer_deleted_123',
            eventType: 'customer.deleted',
            livemode: false,
            accountId: null,
            objectType: 'customer',
            objectId: 'cus_deleted_123',
            processingStatus: 'pending',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-05-02T00:00:00.000Z'),
            processedAt: null,
            createdAt: new Date('2026-05-02T00:00:00.000Z'),
            updatedAt: new Date('2026-05-02T00:00:00.000Z'),
          },
        ],
      })
      .mockResolvedValueOnce({ rows: [] })
      .mockResolvedValueOnce({ rowCount: 1, rows: [] })
      .mockResolvedValueOnce({
        rows: [
          {
            environment: 'test',
            eventId: 'evt_customer_deleted_123',
            eventType: 'customer.deleted',
            livemode: false,
            accountId: null,
            objectType: 'customer',
            objectId: 'cus_deleted_123',
            processingStatus: 'processed',
            attemptCount: 1,
            lastError: null,
            receivedAt: new Date('2026-05-02T00:00:00.000Z'),
            processedAt: new Date('2026-05-02T00:00:01.000Z'),
            createdAt: new Date('2026-05-02T00:00:00.000Z'),
            updatedAt: new Date('2026-05-02T00:00:01.000Z'),
          },
        ],
      });

    await expect(
      stripeServices.handleStripeWebhook(
        'test',
        Buffer.from('{"id":"evt_customer_deleted_123"}'),
        'sig_123'
      )
    ).resolves.toMatchObject({
      received: true,
      handled: true,
      event: {
        eventId: 'evt_customer_deleted_123',
        processingStatus: 'processed',
      },
    });

    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/INSERT INTO payments\.customers/i),
      [
        'test',
        'cus_deleted_123',
        null,
        null,
        null,
        true,
        {},
        expect.objectContaining({ id: 'cus_deleted_123', deleted: true }),
        null,
        expect.any(Date),
        true,
      ]
    );
    expect(mockPool.query).toHaveBeenCalledWith(
      expect.stringMatching(/DELETE FROM payments\.customer_mappings/i),
      ['test', 'cus_deleted_123']
    );
  });
});