import { nanoid } from 'nanoid'

import * as Runtime from '../../../test/runtime.js'
import * as Db from '../Db.js'
import * as Organizations from './organizations.js'
import * as Projects from './projects.js'
import * as SponsoredTransactions from './sponsoredTransactions.js'

const create = () => Db.postgres({ connectionString: Runtime.postgresUrl, schema: `t_${nanoid()}` })

/** Baseline insert input; tests override per case. */
const input = {
  apiKeyId: 'key_1',
  billable: true,
  chainId: 42431,
  environment: 'production',
  orgId: 'org_1',
  projectId: 'prj_1',
  signPayload: `0x${'bb'.repeat(32)}`,
  transaction: `0x76${'cc'.repeat(16)}`,
  transactionHash: `0x${'aa'.repeat(32)}`,
} satisfies SponsoredTransactions.upsert.Input

/** Redacts nondeterministic row fields (generated nanoid ids, timestamps). */
function redact(record: SponsoredTransactions.Record) {
  return {
    ...record,
    createdAt: '<timestamp>',
    finalizedAt: record.finalizedAt === null ? null : '<timestamp>',
    id: /^stx_[0-9A-Za-z]{24}$/.test(record.id) ? 'stx_<id>' : record.id,
  }
}

describe('upsert', () => {
  test('behavior: records a pending sponsorship with a generated id', async () => {
    const db = create()
    await db.migrate()
    const record = await SponsoredTransactions.upsert(db, input)
    expect(record.id).toMatch(/^stx_/)
    expect(redact(record)).toMatchInlineSnapshot(`
      {
        "apiKeyId": "key_1",
        "billable": true,
        "chainId": 42431,
        "createdAt": "<timestamp>",
        "currency": null,
        "environment": "production",
        "feeAmount": null,
        "feeMax": null,
        "feeToken": null,
        "finalizedAt": null,
        "id": "stx_<id>",
        "meterReportedAt": null,
        "orgId": "org_1",
        "projectId": "prj_1",
        "signPayload": "0xbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb",
        "status": "pending",
        "transaction": "0x76cccccccccccccccccccccccccccccccc",
        "transactionHash": "0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
      }
    `)
    expect(await SponsoredTransactions.get(db, record.id)).toStrictEqual(record)
    await db.close()
  })

  test('behavior: records organization-level sponsorship without a project', async () => {
    const db = create()
    await db.migrate()
    const { projectId: _, ...organization } = input
    const record = await SponsoredTransactions.upsert(db, organization)

    expect(record.orgId).toMatchInlineSnapshot(`"org_1"`)
    expect(record.projectId).toBeNull()
    await db.close()
  })

  test('behavior: preserves an explicit fee token', async () => {
    const db = create()
    await db.migrate()
    const record = await SponsoredTransactions.upsert(db, { ...input, feeToken: `0x${'fe'.repeat(20)}` }) // prettier-ignore
    expect(record.feeToken).toMatchInlineSnapshot(`"0xfefefefefefefefefefefefefefefefefefefefe"`)
    await db.close()
  })

  test('behavior: records fill intents without a transaction hash', async () => {
    const db = create()
    await db.migrate()
    const { transactionHash: _, ...intent } = input
    const record = await SponsoredTransactions.upsert(db, intent)
    expect(record.transactionHash).toBeNull()
    expect(record.status).toMatchInlineSnapshot(`"pending"`)
    await db.close()
  })

  test('behavior: a raw submission upgrades a fill intent in place', async () => {
    const db = create()
    await db.migrate()
    const { transactionHash: _, ...intent } = input
    const first = await SponsoredTransactions.upsert(db, intent)
    const second = await SponsoredTransactions.upsert(db, input)

    // Same commitment, one row: the raw record fills the intent's hash.
    expect(second.id).toBe(first.id)
    expect(second.transactionHash).toMatchInlineSnapshot(
      `"0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"`,
    )
    expect(await SponsoredTransactions.listPending(db)).toHaveLength(1)
    await db.close()
  })

  test('behavior: rejects a sign payload already attributed to another org', async () => {
    const db = create()
    await db.migrate()
    const first = await SponsoredTransactions.upsert(db, input)

    await expect(
      SponsoredTransactions.upsert(db, {
        ...input,
        orgId: 'org_2',
        transactionHash: `0x${'dd'.repeat(32)}`,
      }),
    ).rejects.toThrow()

    expect(await SponsoredTransactions.get(db, first.id)).toStrictEqual(first)
    await db.close()
  })

  test('behavior: rejects a sign payload already attributed to another project', async () => {
    const db = create()
    await db.migrate()
    const { transactionHash: _, ...intent } = input
    const first = await SponsoredTransactions.upsert(db, intent)

    await expect(
      SponsoredTransactions.upsert(db, { ...input, projectId: 'prj_2' }),
    ).rejects.toBeInstanceOf(SponsoredTransactions.AttributionConflictError)

    expect(await SponsoredTransactions.get(db, first.id)).toStrictEqual(first)
    await db.close()
  })

  test('behavior: a raw submission reopens a failed fill intent', async () => {
    const db = create()
    await db.migrate()
    const { transactionHash: _, ...intent } = input
    const first = await SponsoredTransactions.upsert(db, intent)
    await SponsoredTransactions.fail(db, first.id, '2026-01-01T00:00:00.000Z')

    const second = await SponsoredTransactions.upsert(db, input)

    expect(second.id).toBe(first.id)
    expect(second.status).toMatchInlineSnapshot(`"pending"`)
    expect(second.finalizedAt).toBeNull()
    expect(second.transactionHash).toMatchInlineSnapshot(
      `"0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"`,
    )
    expect(await SponsoredTransactions.listPending(db)).toHaveLength(1)
    await db.close()
  })

  test('behavior: a duplicate intent never clears a known hash', async () => {
    const db = create()
    await db.migrate()
    const first = await SponsoredTransactions.upsert(db, input)
    const { transactionHash: _, ...intent } = input
    const second = await SponsoredTransactions.upsert(db, intent)

    expect(second.id).toBe(first.id)
    expect(second.transactionHash).toMatchInlineSnapshot(
      `"0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"`,
    )
    await db.close()
  })
})

describe('listPending', () => {
  test('behavior: returns only pending rows, capped by limit', async () => {
    const db = create()
    await db.migrate()
    await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'b1'.repeat(32)}`, transactionHash: `0x${'a1'.repeat(32)}` }) // prettier-ignore
    const second = await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'b2'.repeat(32)}`, transactionHash: `0x${'a2'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'b3'.repeat(32)}`, transactionHash: `0x${'a3'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, second.id, {
      feeAmount: '100',
      finalizedAt: new Date().toISOString(),
    })

    const pending = await SponsoredTransactions.listPending(db)
    // Inserts can share a millisecond, so assert membership, not order.
    expect(pending.map((row) => row.transactionHash).sort((left, right) => String(left).localeCompare(String(right)))) // prettier-ignore
      .toMatchInlineSnapshot(`
      [
        "0xa1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1a1",
        "0xa3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3a3",
      ]
    `)
    expect(await SponsoredTransactions.listPending(db, { limit: 1 })).toHaveLength(1)
    await db.close()
  })
})

describe('assignTransactionHash', () => {
  test('behavior: reopens a failed intent when reconciliation finds its hash', async () => {
    const db = create()
    await db.migrate()
    await Organizations.create(db, { id: 'org_1', name: 'One' })
    const { transactionHash: _, ...intent } = input
    const record = await SponsoredTransactions.upsert(db, intent)
    await SponsoredTransactions.fail(db, record.id, new Date().toISOString())

    await SponsoredTransactions.assignTransactionHash(db, record.id, input.transactionHash)

    const assigned = await SponsoredTransactions.get(db, record.id)
    expect(assigned?.status).toMatchInlineSnapshot(`"pending"`)
    expect(assigned?.finalizedAt).toBeNull()
    expect(assigned?.transactionHash).toMatchInlineSnapshot(
      `"0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"`,
    )
    await db.close()
  })

  test('behavior: does not reopen failed intents after the recovery window', async () => {
    const db = create()
    await db.migrate()
    await Organizations.create(db, { id: 'org_1', name: 'One' })
    const { transactionHash: _, ...intent } = input
    const record = await SponsoredTransactions.upsert(db, intent)
    await SponsoredTransactions.fail(
      db,
      record.id,
      new Date(Date.now() - SponsoredTransactions.failedIntentRecoveryTtlMs - 1).toISOString(),
    )

    await SponsoredTransactions.assignTransactionHash(db, record.id, input.transactionHash)

    const assigned = await SponsoredTransactions.get(db, record.id)
    expect(assigned?.status).toMatchInlineSnapshot(`"failed"`)
    expect(assigned?.transactionHash).toBeNull()
    await db.close()
  })

  test('behavior: does not reopen failed intents for deleted orgs', async () => {
    const db = create()
    await db.migrate()
    const { transactionHash: _, ...intent } = input
    const record = await SponsoredTransactions.upsert(db, intent)
    await SponsoredTransactions.fail(db, record.id, new Date().toISOString())

    await SponsoredTransactions.assignTransactionHash(db, record.id, input.transactionHash)

    const assigned = await SponsoredTransactions.get(db, record.id)
    expect(assigned?.status).toMatchInlineSnapshot(`"failed"`)
    expect(assigned?.transactionHash).toBeNull()
    await db.close()
  })

  test('behavior: keeps an over-limit failed intent retryable across a period rollover', async () => {
    const db = create()
    await db.migrate()
    await Organizations.create(db, { id: 'org_1', name: 'One' })
    const { transactionHash: _, ...intent } = input
    vi.useFakeTimers({ now: new Date('2026-01-31T23:59:00.000Z') })
    try {
      const failed = await SponsoredTransactions.upsert(db, { ...intent, feeMax: '100' })
      await SponsoredTransactions.fail(db, failed.id, new Date().toISOString())
      vi.setSystemTime(new Date('2026-02-01T00:01:00.000Z'))
      await SponsoredTransactions.upsert(db, {
        ...intent,
        feeMax: '100',
        signPayload: `0x${'bc'.repeat(32)}`,
      })

      await expect(
        SponsoredTransactions.assignTransactionHash(db, failed.id, input.transactionHash, {
          chainIds: [input.chainId],
          max: 100n,
          since: '2026-02-01T00:00:00.000Z',
        }),
      ).rejects.toBeInstanceOf(SponsoredTransactions.PeriodSpendLimitError)
      expect(await SponsoredTransactions.get(db, failed.id)).toMatchObject({
        createdAt: '2026-01-31T23:59:00.000Z',
        finalizedAt: '2026-02-01T00:01:00.000Z',
        status: 'failed',
        transactionHash: null,
      })
      expect(
        (
          await SponsoredTransactions.listIntents(db, {
            failedSince: '2026-02-01T00:00:00.000Z',
          })
        ).map((record) => record.id),
      ).toContain(failed.id)
    } finally {
      vi.useRealTimers()
      await db.close()
    }
  })
})

describe('finalize', () => {
  test('behavior: transitions pending → finalized with the actual fee', async () => {
    const db = create()
    await db.migrate()
    const record = await SponsoredTransactions.upsert(db, input)
    await SponsoredTransactions.finalize(db, record.id, {
      feeAmount: '21000',
      finalizedAt: '2026-01-01T00:00:00.000Z',
    })
    const finalized = await SponsoredTransactions.get(db, record.id)
    expect(finalized?.status).toMatchInlineSnapshot(`"finalized"`)
    expect(finalized?.feeAmount).toMatchInlineSnapshot(`"21000"`)
    expect(finalized?.finalizedAt).toMatchInlineSnapshot(`"2026-01-01T00:00:00.000Z"`)
    await db.close()
  })

  test('behavior: no-op on rows that are no longer pending', async () => {
    const db = create()
    await db.migrate()
    const record = await SponsoredTransactions.upsert(db, input)
    await SponsoredTransactions.fail(db, record.id, '2026-01-01T00:00:00.000Z')
    await SponsoredTransactions.finalize(db, record.id, {
      feeAmount: '21000',
      finalizedAt: '2026-01-02T00:00:00.000Z',
    })
    const row = await SponsoredTransactions.get(db, record.id)
    expect(row?.status).toMatchInlineSnapshot(`"failed"`)
    expect(row?.feeAmount).toBeNull()
    await db.close()
  })
})

describe('fail', () => {
  test('behavior: transitions pending → failed without a fee', async () => {
    const db = create()
    await db.migrate()
    const record = await SponsoredTransactions.upsert(db, input)
    await SponsoredTransactions.fail(db, record.id, '2026-01-01T00:00:00.000Z')
    const failed = await SponsoredTransactions.get(db, record.id)
    expect(failed?.status).toMatchInlineSnapshot(`"failed"`)
    expect(failed?.feeAmount).toBeNull()
    expect(failed?.finalizedAt).toMatchInlineSnapshot(`"2026-01-01T00:00:00.000Z"`)
    await db.close()
  })
})

describe('migration 0032', () => {
  test('behavior: rescales legacy attodollar fee amounts idempotently', async () => {
    const db = create()
    await db.migrate()
    // A pre-scaling row stored raw attodollars: 2.1e18 + 1, non-divisible so
    // the rescale must round up.
    const legacy = await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'31'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, legacy.id, {
      feeAmount: '2100000000000000001',
      finalizedAt: new Date().toISOString(),
    })
    // An already-scaled row sits far below the threshold and stays untouched.
    const scaled = await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'32'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, scaled.id, {
      feeAmount: '420000',
      finalizedAt: new Date().toISOString(),
    })

    const rescale = Db.migrations.find((migration) => migration.name === '0032_sponsored_transactions_fee_amount_rescale') // prettier-ignore
    // Applying twice proves the threshold guard: a re-run never rescales again.
    for (let run = 0; run < 2; run++) {
      await rescale!.up(db.kysely).execute()
      expect((await SponsoredTransactions.get(db, legacy.id))?.feeAmount).toBe('2100001')
      expect((await SponsoredTransactions.get(db, scaled.id))?.feeAmount).toBe('420000')
    }
    await db.close()
  })
})

describe('spend', () => {
  test('behavior: sums finalized fees plus pending caps within scope', async () => {
    const db = create()
    await db.migrate()
    const chainIds = [input.chainId]
    // Finalized in scope: counts its actual fee, never its cap.
    const finalized = await SponsoredTransactions.upsert(db, { ...input, feeMax: '900000', signPayload: `0x${'01'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, finalized.id, {
      feeAmount: '100000',
      finalizedAt: new Date().toISOString(),
    })
    // Pending in scope: counts its signed cap.
    await SponsoredTransactions.upsert(db, { ...input, feeMax: '250000', signPayload: `0x${'02'.repeat(32)}` }) // prettier-ignore
    // Pending without a recorded cap (pre-column row): contributes nothing.
    await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'03'.repeat(32)}` })
    // Failed: out of scope regardless of cap.
    const failed = await SponsoredTransactions.upsert(db, { ...input, feeMax: '700000', signPayload: `0x${'04'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.fail(db, failed.id, new Date().toISOString())
    // Non-billable and foreign-chain rows: out of scope.
    await SponsoredTransactions.upsert(db, { ...input, billable: false, environment: 'sandbox', feeMax: '500000', signPayload: `0x${'05'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.upsert(db, { ...input, chainId: 1337, feeMax: '500000', signPayload: `0x${'06'.repeat(32)}` }) // prettier-ignore
    // Other orgs: out of scope.
    await SponsoredTransactions.upsert(db, { ...input, feeMax: '500000', orgId: 'org_2', signPayload: `0x${'07'.repeat(32)}` }) // prettier-ignore

    expect(await SponsoredTransactions.spend(db, { chainIds, orgId: 'org_1', since: '2000-01-01T00:00:00.000Z' })).toBe(350000n) // prettier-ignore
    await db.close()
  })

  test('behavior: rows created before the window start are out of scope', async () => {
    const db = create()
    await db.migrate()
    await SponsoredTransactions.upsert(db, { ...input, feeMax: '250000', signPayload: `0x${'08'.repeat(32)}` }) // prettier-ignore
    const future = new Date(Date.now() + 60_000).toISOString()
    expect(await SponsoredTransactions.spend(db, { chainIds: [input.chainId], orgId: 'org_1', since: future })).toBe(0n) // prettier-ignore
    await db.close()
  })

  test('behavior: promotional spend is isolated by project', async () => {
    const db = create()
    await db.migrate()
    await SponsoredTransactions.upsert(db, {
      ...input,
      billable: false,
      feeMax: '100',
      projectId: 'prj_1',
      signPayload: `0x${'09'.repeat(32)}`,
    })
    await SponsoredTransactions.upsert(db, {
      ...input,
      billable: false,
      feeMax: '200',
      projectId: 'prj_2',
      signPayload: `0x${'0a'.repeat(32)}`,
    })

    expect(
      await SponsoredTransactions.spend(db, {
        billable: false,
        chainIds: [input.chainId],
        orgId: 'org_1',
        projectId: 'prj_1',
        since: '2000-01-01T00:00:00.000Z',
      }),
    ).toBe(100n)
    await db.close()
  })
})

describe('reserve', () => {
  test('behavior: enforces promotional limits independently per project', async () => {
    const db = create()
    await db.migrate()
    const limit = {
      billable: false,
      chainIds: [input.chainId],
      max: 100n,
      projectId: 'prj_1',
      since: '2000-01-01T00:00:00.000Z',
    } as const
    await SponsoredTransactions.reserve(
      db,
      {
        ...input,
        billable: false,
        feeMax: '100',
        signPayload: `0x${'0b'.repeat(32)}`,
      },
      limit,
    )
    await SponsoredTransactions.reserve(
      db,
      {
        ...input,
        billable: false,
        feeMax: '100',
        projectId: 'prj_2',
        signPayload: `0x${'0c'.repeat(32)}`,
      },
      { ...limit, projectId: 'prj_2' },
    )

    await expect(
      SponsoredTransactions.reserve(
        db,
        {
          ...input,
          billable: false,
          feeMax: '1',
          signPayload: `0x${'0d'.repeat(32)}`,
        },
        limit,
      ),
    ).rejects.toBeInstanceOf(SponsoredTransactions.PeriodSpendLimitError)
    await db.close()
  })
})

describe('reservePromotion', () => {
  async function configure(db: Db.Db, projectSpendLimit: string | null) {
    await Organizations.create(db, { id: 'org_1', name: 'Privy' })
    await Organizations.setSponsorshipSubsidy(db, 'org_1', {
      durationDays: 90,
      projectSpendLimit,
    })
  }

  test('behavior: atomically records the first sponsorship and immutable window', async () => {
    const db = create()
    await db.migrate()
    await configure(db, '0.000001')
    const project = await Projects.create(db, { name: 'App', orgId: 'org_1' })
    const result = await SponsoredTransactions.reservePromotion(
      db,
      { ...input, billable: false, projectId: project.id },
      {
        at: '2026-01-01T00:00:00.000Z',
        chainIds: [input.chainId],
        projectId: project.id,
      },
    )
    const updated = await Projects.get(db, project.id)
    expect({
      endsAt: updated?.sponsorshipSubsidyEndsAt,
      startsAt: updated?.sponsorshipSubsidyStartsAt,
    }).toMatchInlineSnapshot(`
      {
        "endsAt": "2026-04-01T00:00:00.000Z",
        "startsAt": "2026-01-01T00:00:00.000Z",
      }
    `)
    if (result.status === 'ineligible') throw new Error('Expected a promotion reservation.')
    expect(result.record.billable).toBe(false)
    expect((await Projects.get(db, project.id))?.sponsorshipSpendLimit).toBe('1')
    await db.close()
  })

  test('behavior: records an uncapped promotion', async () => {
    const db = create()
    await db.migrate()
    await configure(db, null)
    const project = await Projects.create(db, { name: 'App', orgId: 'org_1' })
    const result = await SponsoredTransactions.reservePromotion(
      db,
      { ...input, billable: false, feeMax: '100', projectId: project.id },
      {
        at: '2026-01-01T00:00:00.000Z',
        chainIds: [input.chainId],
        projectId: project.id,
      },
    )
    if (result.status === 'ineligible') throw new Error('Expected a promotion reservation.')
    expect(result.record.billable).toBe(false)
    expect(await Projects.get(db, project.id)).toMatchObject({
      sponsorshipSubsidyEndsAt: '2026-04-01T00:00:00.000Z',
      sponsorshipSubsidyStartsAt: '2026-01-01T00:00:00.000Z',
      sponsorshipSpendLimit: null,
    })
    await SponsoredTransactions.reservePromotion(
      db,
      {
        ...input,
        billable: false,
        feeMax: '100',
        projectId: project.id,
        signPayload: `0x${'24'.repeat(32)}`,
      },
      {
        at: '2026-01-02T00:00:00.000Z',
        chainIds: [input.chainId],
        projectId: project.id,
      },
    )
    expect((await Projects.get(db, project.id))?.sponsorshipSpendLimit).toBeNull()
    await db.close()
  })

  test('behavior: reads the current organization policy when activating', async () => {
    const db = create()
    await db.migrate()
    await configure(db, '0.000001')
    const project = await Projects.create(db, { name: 'App', orgId: 'org_1' })
    await Organizations.setSponsorshipSubsidy(db, 'org_1', {
      durationDays: 90,
      projectSpendLimit: '0.000002',
    })

    const result = await SponsoredTransactions.reservePromotion(
      db,
      { ...input, billable: false, feeMax: '2', projectId: project.id },
      {
        at: '2026-01-01T00:00:00.000Z',
        chainIds: [input.chainId],
        projectId: project.id,
      },
    )

    expect(result.status).toBe('subsidized')
    expect((await Projects.get(db, project.id))?.sponsorshipSpendLimit).toBe('2')
    await db.close()
  })

  test('behavior: conflicting envelopes do not activate another project', async () => {
    const db = create()
    await db.migrate()
    await configure(db, null)
    const first = await Projects.create(db, { name: 'First', orgId: 'org_1' })
    const second = await Projects.create(db, { name: 'Second', orgId: 'org_1' })
    await SponsoredTransactions.upsert(db, { ...input, projectId: first.id })

    await expect(
      SponsoredTransactions.reservePromotion(
        db,
        { ...input, billable: false, projectId: second.id },
        {
          at: '2026-01-01T00:00:00.000Z',
          chainIds: [input.chainId],
          projectId: second.id,
        },
      ),
    ).rejects.toBeInstanceOf(SponsoredTransactions.AttributionConflictError)

    expect(await Projects.get(db, second.id)).toMatchObject({
      sponsorshipSubsidyEndsAt: null,
      sponsorshipSubsidyStartsAt: null,
    })
    await db.close()
  })

  test('behavior: later policy changes cannot raise an activated project cap', async () => {
    const db = create()
    await db.migrate()
    await configure(db, '0.000001')
    const project = await Projects.create(db, { name: 'App', orgId: 'org_1' })
    const promotion = {
      at: '2026-01-01T00:00:00.000Z',
      chainIds: [input.chainId],
      projectId: project.id,
    } as const
    await SponsoredTransactions.reservePromotion(
      db,
      {
        ...input,
        billable: false,
        feeMax: '1',
        projectId: project.id,
      },
      promotion,
    )
    await Organizations.setSponsorshipSubsidy(db, 'org_1', {
      durationDays: 90,
      projectSpendLimit: '0.000002',
    })

    const result = await SponsoredTransactions.reservePromotion(
      db,
      {
        ...input,
        billable: false,
        feeMax: '1',
        projectId: project.id,
        signPayload: `0x${'23'.repeat(32)}`,
      },
      promotion,
    )
    if (result.status === 'ineligible') throw new Error('Expected a promotion reservation.')
    expect(result.record.billable).toBe(true)
    expect((await Projects.get(db, project.id))?.sponsorshipSpendLimit).toBe('1')
    await db.close()
  })

  test('behavior: preserves activation when the first sponsorship exceeds the cap', async () => {
    const db = create()
    await db.migrate()
    await configure(db, '0')
    const project = await Projects.create(db, { name: 'App', orgId: 'org_1' })
    const result = await SponsoredTransactions.reservePromotion(
      db,
      { ...input, billable: false, feeMax: '1', projectId: project.id },
      {
        at: '2026-01-01T00:00:00.000Z',
        chainIds: [input.chainId],
        projectId: project.id,
      },
    )
    if (result.status === 'ineligible') throw new Error('Expected a promotion reservation.')
    expect(result.record.billable).toBe(true)
    expect(await Projects.get(db, project.id)).toMatchObject({
      sponsorshipSubsidyEndsAt: '2026-04-01T00:00:00.000Z',
      sponsorshipSubsidyStartsAt: '2026-01-01T00:00:00.000Z',
    })
    expect(await SponsoredTransactions.listPending(db)).toMatchObject([{ billable: true }])
    await db.close()
  })

  test('behavior: concurrent first sponsorships share one promotion window', async () => {
    const schema = `t_${nanoid()}`
    const firstDb = Db.postgres({ connectionString: Runtime.postgresUrl, schema })
    const secondDb = Db.postgres({ connectionString: Runtime.postgresUrl, schema })
    await firstDb.migrate()
    await configure(firstDb, '0.000002')
    const project = await Projects.create(firstDb, { name: 'App', orgId: 'org_1' })
    const reserve = (db: Db.Db, at: string, signPayload: string) =>
      SponsoredTransactions.reservePromotion(
        db,
        {
          ...input,
          billable: false,
          projectId: project.id,
          signPayload,
        },
        {
          at,
          chainIds: [input.chainId],
          projectId: project.id,
        },
      )
    const [first, second] = await Promise.all([
      reserve(firstDb, '2026-01-01T00:00:00.000Z', `0x${'21'.repeat(32)}`),
      reserve(secondDb, '2026-01-02T00:00:00.000Z', `0x${'22'.repeat(32)}`),
    ])
    expect(first.status).toBe('subsidized')
    expect(second.status).toBe('subsidized')
    const updated = await Projects.get(firstDb, project.id)
    if (!updated?.sponsorshipSubsidyEndsAt || !updated.sponsorshipSubsidyStartsAt)
      throw new Error('Expected a promotion window.')
    expect(
      Date.parse(updated.sponsorshipSubsidyEndsAt) - Date.parse(updated.sponsorshipSubsidyStartsAt),
    ).toBe(90 * 86_400_000)
    await Promise.all([firstDb.close(), secondDb.close()])
  })
})

describe('listUnreported', () => {
  test('behavior: lists finalized billable in-scope rows, oldest first', async () => {
    const db = create()
    await db.migrate()
    const chainIds = [input.chainId]
    const first = await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'11'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, first.id, { feeAmount: '100', finalizedAt: '2026-01-01T00:00:00.000Z' }) // prettier-ignore
    const second = await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'12'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, second.id, { feeAmount: '200', finalizedAt: '2026-01-02T00:00:00.000Z' }) // prettier-ignore
    // Pending, sandbox, and foreign-chain rows never report.
    await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'13'.repeat(32)}` })
    await SponsoredTransactions.upsert(db, { ...input, billable: false, environment: 'sandbox', signPayload: `0x${'14'.repeat(32)}` }) // prettier-ignore
    const promotion = await SponsoredTransactions.upsert(db, {
      ...input,
      billable: false,
      signPayload: `0x${'16'.repeat(32)}`,
    })
    await SponsoredTransactions.finalize(db, promotion.id, {
      feeAmount: '300',
      finalizedAt: '2026-01-03T00:00:00.000Z',
    })

    const rows = await SponsoredTransactions.listUnreported(db, { chainIds })
    expect(rows.map((row) => row.id)).toStrictEqual([first.id, second.id])

    await SponsoredTransactions.markReported(db, first.id, new Date().toISOString())
    const remaining = await SponsoredTransactions.listUnreported(db, { chainIds })
    expect(remaining.map((row) => row.id)).toStrictEqual([second.id])
    await db.close()
  })
})

describe('markReported', () => {
  test('behavior: marks once; re-marking never overwrites the first timestamp', async () => {
    const db = create()
    await db.migrate()
    const record = await SponsoredTransactions.upsert(db, { ...input, signPayload: `0x${'15'.repeat(32)}` }) // prettier-ignore
    await SponsoredTransactions.finalize(db, record.id, { feeAmount: '100', finalizedAt: '2026-01-01T00:00:00.000Z' }) // prettier-ignore
    await SponsoredTransactions.markReported(db, record.id, '2026-01-02T00:00:00.000Z')
    await SponsoredTransactions.markReported(db, record.id, '2026-01-03T00:00:00.000Z')
    const row = await SponsoredTransactions.get(db, record.id)
    expect(row?.meterReportedAt).toMatchInlineSnapshot(`"2026-01-02T00:00:00.000Z"`)
    await db.close()
  })
})