import { randomUUID } from 'node:crypto'
import { z } from 'zod'
import { afterAll, beforeAll, describe, expect, it } from 'vitest'
import { safeParseSchema } from '../supabase/functions/_backend/utils/schema_validation.ts'

import { BASE_URL, createDirectApiKeyWithBindings, executeSQL, fetchTestRequest, getAuthHeaders, getSupabaseClient, TEST_EMAIL, USER_ID } from './test-utils.ts'

const ORG_ID = randomUUID()
const globalId = randomUUID()
const name = `Test Audit Organization ${globalId}`
const customerId = `cus_audit_${ORG_ID}`
const APIKEY_AUDIT_APP_ID = `com.audit.logs.${globalId.replace(/-/g, '')}`

// Schema for audit log response
const auditLogSchema = z.object({
  id: z.number(),
  created_at: z.string(),
  table_name: z.string(),
  record_id: z.string(),
  operation: z.string(),
  user_id: z.string().nullable(),
  org_id: z.string(),
  old_record: z.unknown(),
  new_record: z.unknown(),
  changed_fields: z.array(z.string()).nullable(),
  actor_type: z.enum(['user', 'apikey', 'system', 'unknown']),
  actor_user_id: z.string().nullable(),
  actor_user_email: z.string().nullable(),
  actor_apikey_id: z.number().nullable(),
  actor_apikey_name: z.string().nullable(),
})

const auditLogsResponseSchema = z.object({
  data: z.array(auditLogSchema),
  total: z.number(),
  page: z.number(),
  limit: z.number(),
})

interface AuditLog {
  id: number
  created_at: string
  table_name: string
  record_id: string
  operation: string
  user_id: string | null
  org_id: string
  old_record: unknown
  new_record: unknown
  changed_fields: string[] | null
  actor_type: 'user' | 'apikey' | 'system' | 'unknown'
  actor_user_id: string | null
  actor_user_email: string | null
  actor_apikey_id: number | null
  actor_apikey_name: string | null
}

function parseAuditLogsResponse(value: unknown) {
  return safeParseSchema(auditLogsResponseSchema, value)
}

let authHeaders: Record<string, string>
let apiKeyAuthHeaders: Record<string, string>
let apiKeyId: number | null = null
let actorUserEmail: string

async function waitForAuditLog(
  url: string,
  matcher: (log: AuditLog) => boolean,
  maxAttempts = 8,
  delayMs = 200,
) {
  let lastStatus = 0
  let lastBody: unknown = null

  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    const response = await fetchTestRequest(url, {
      headers: authHeaders,
    })
    lastStatus = response.status
    lastBody = await response.json()

    if (response.status === 200) {
      const safe = parseAuditLogsResponse(lastBody)
      if (safe.success) {
        const matchedLog = safe.data.data.find(matcher)
        if (matchedLog)
          return matchedLog
      }
    }

    if (attempt < maxAttempts - 1)
      await new Promise(resolve => setTimeout(resolve, delayMs * (attempt + 1)))
  }

  throw new Error(`Audit log did not reach expected state. Last status: ${lastStatus}. Last body: ${JSON.stringify(lastBody)}`)
}

beforeAll(async () => {
  authHeaders = await getAuthHeaders()

  // Seed via SQL to avoid Kong/PostgREST upstream flakes under CF shard load.
  const [actorUser] = await executeSQL(
    'SELECT email FROM public.users WHERE id = $1::uuid LIMIT 1',
    [USER_ID],
  )
  if (!actorUser?.email) {
    throw new Error(`Failed to load audit actor user for id=${USER_ID}: no rows`)
  }
  actorUserEmail = actorUser.email

  const trialAt = new Date(Date.now() + 15 * 24 * 60 * 60 * 1000).toISOString()
  await executeSQL(
    `INSERT INTO public.stripe_info (
       customer_id, status, product_id, subscription_id, trial_at, is_good_plan
     ) VALUES ($1, 'succeeded', 'prod_LQIregjtNduh4q', $2, $3::timestamptz, true)`,
    [customerId, `sub_${globalId}`, trialAt],
  )

  // Org insert still triggers INSERT audit logs via DB triggers.
  await executeSQL(
    `INSERT INTO public.orgs (
       id, name, management_email, created_by, customer_id
     ) VALUES ($1::uuid, $2, $3, $4::uuid, $5)`,
    [ORG_ID, name, TEST_EMAIL, USER_ID, customerId],
  )

  await executeSQL(
    `INSERT INTO public.org_users (org_id, user_id, rbac_role_name)
     SELECT $1::uuid, $2::uuid, 'org_super_admin'
     WHERE NOT EXISTS (
       SELECT 1 FROM public.org_users
       WHERE org_id = $1::uuid AND user_id = $2::uuid
     )`,
    [ORG_ID, USER_ID],
  )

  await executeSQL(
    `INSERT INTO public.apps (app_id, name, icon_url, owner_org)
     VALUES ($1, $2, 'https://example.com/icon.png', $3::uuid)`,
    [APIKEY_AUDIT_APP_ID, `Audit API App ${globalId}`, ORG_ID],
  )

  const apiKeyData = await createDirectApiKeyWithBindings({
    userId: USER_ID,
    key: randomUUID(),
    name: `audit-api-key-${globalId}`,
    orgId: ORG_ID,
    roleName: 'org_admin',
    appId: APIKEY_AUDIT_APP_ID,
    appRoleName: 'app_admin',
  })
  if (!apiKeyData?.id || !apiKeyData.key) {
    throw new Error('Failed to create isolated audit API key')
  }

  apiKeyId = apiKeyData.id
  apiKeyAuthHeaders = {
    'Content-Type': 'application/json',
    'Authorization': apiKeyData.key,
  }
})

afterAll(async () => {
  await executeSQL('DELETE FROM public.audit_logs WHERE org_id = $1::uuid', [ORG_ID])
  if (apiKeyId !== null)
    await executeSQL('DELETE FROM public.apikeys WHERE id = $1', [apiKeyId])
  await executeSQL('DELETE FROM public.app_versions WHERE app_id = $1', [APIKEY_AUDIT_APP_ID])
  await executeSQL('DELETE FROM public.apps WHERE app_id = $1', [APIKEY_AUDIT_APP_ID])
  await executeSQL('DELETE FROM public.org_users WHERE org_id = $1::uuid', [ORG_ID])
  await executeSQL('DELETE FROM public.orgs WHERE id = $1::uuid', [ORG_ID])
  await executeSQL('DELETE FROM public.stripe_info WHERE customer_id = $1', [customerId])
}, 60_000)

describe('[GET] /organization/audit', () => {
  it('get audit logs for organization', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success) {
      expect(safe.data.page).toBe(0)
      expect(safe.data.limit).toBe(50)
      expect(Array.isArray(safe.data.data)).toBe(true)
    }
  })

  it('get audit logs with pagination', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&page=0&limit=10`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success) {
      expect(safe.data.page).toBe(0)
      expect(safe.data.limit).toBe(10)
    }
  })

  it('get audit logs filtered by table name', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=orgs`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success) {
      // All returned logs should be for the 'orgs' table
      for (const log of safe.data.data) {
        expect(log.table_name).toBe('orgs')
      }
    }
  })

  it('get audit logs filtered by operation', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&operation=INSERT`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success) {
      // All returned logs should be INSERT operations
      for (const log of safe.data.data) {
        expect(log.operation).toBe('INSERT')
      }
    }
  })

  it('get audit logs with combined filters', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=orgs&operation=INSERT`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success) {
      for (const log of safe.data.data) {
        expect(log.table_name).toBe('orgs')
        expect(log.operation).toBe('INSERT')
      }
    }
  })

  it('limit is capped at 100', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&limit=200`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success) {
      // Server should cap limit at 100
      expect(safe.data.limit).toBe(100)
    }
  })

  it('get audit logs with missing orgId returns error', async () => {
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(400)
    const responseData = await response.json() as { error: string }
    expect(responseData.error).toBe('invalid_body')
  })

  it('get audit logs with invalid orgId returns error', async () => {
    const invalidOrgId = randomUUID()
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${invalidOrgId}`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(400)
    const responseData = await response.json() as { error: string }
    expect(responseData.error).toBe('invalid_org_id')
  })

  it('audit logs keep org and user snapshots without foreign keys', async () => {
    const orphanOrgId = randomUUID()
    const orphanUserId = randomUUID()
    const rows = await executeSQL(
      `
      INSERT INTO public.audit_logs (
        table_name,
        record_id,
        operation,
        user_id,
        org_id,
        actor_type,
        actor_user_id,
        actor_user_email
      )
      VALUES ('orgs', $1::text, 'DELETE', $2::uuid, $1::uuid, 'user', $2::uuid, $3::text)
      RETURNING id, org_id, user_id, actor_type, actor_user_email
      `,
      [orphanOrgId, orphanUserId, 'deleted-user@example.com'],
    )

    expect(rows).toHaveLength(1)
    expect(rows[0].org_id).toBe(orphanOrgId)
    expect(rows[0].user_id).toBe(orphanUserId)
    expect(rows[0].actor_type).toBe('user')
    expect(rows[0].actor_user_email).toBe('deleted-user@example.com')

    await executeSQL('DELETE FROM public.audit_logs WHERE id = $1', [rows[0].id])
  })
})

describe('audit log triggers', () => {
  it('organization UPDATE creates audit log with changed_fields', async () => {
    const newName = `Updated Audit Organization ${randomUUID()}`

    // Update the organization
    const { error: updateError } = await getSupabaseClient()
      .from('orgs')
      .update({ name: newName })
      .eq('id', ORG_ID)
    expect(updateError).toBeNull()

    // Wait a bit for the trigger to execute
    await new Promise(resolve => setTimeout(resolve, 100))

    // Fetch audit logs for this org
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=orgs&operation=UPDATE`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)
    if (safe.success)
      expect(safe.data.data.length).toBeGreaterThan(0)

    if (safe.success && safe.data.data.length > 0) {
      const latestUpdate = safe.data.data[0]
      expect(latestUpdate.operation).toBe('UPDATE')
      expect(latestUpdate.table_name).toBe('orgs')
      expect(latestUpdate.record_id).toBe(ORG_ID)
      expect(latestUpdate.org_id).toBe(ORG_ID)
      expect(latestUpdate.actor_type).toBe('system')
      expect(latestUpdate.actor_user_id).toBeNull()
      expect(latestUpdate.actor_user_email).toBeNull()
      // Changed fields should include 'name' and 'updated_at'
      expect(Array.isArray(latestUpdate.changed_fields)).toBe(true)
      expect(latestUpdate.changed_fields).toContain('name')
      // old_record should have the old name
      expect(latestUpdate.old_record).toBeTruthy()
      // new_record should have the new name
      expect(latestUpdate.new_record).toBeTruthy()
      if (latestUpdate.new_record && typeof latestUpdate.new_record === 'object') {
        expect((latestUpdate.new_record as Record<string, unknown>).name).toBe(newName)
      }
    }
  })

  it('organization and app bookkeeping update rows do not create audit logs', async () => {
    const bookkeepingOrgId = randomUUID()
    const bookkeepingAppId = `com.audit.bookkeeping.${bookkeepingOrgId.replace(/-/g, '')}`
    const bookkeepingCustomerId = `cus_audit_bookkeeping_${bookkeepingOrgId}`
    const trialAt = new Date(Date.now() + 15 * 24 * 60 * 60 * 1000).toISOString()

    await executeSQL(
      `INSERT INTO public.stripe_info (
         customer_id, status, product_id, subscription_id, trial_at, is_good_plan
       ) VALUES ($1, 'succeeded', 'prod_LQIregjtNduh4q', $2, $3::timestamptz, true)`,
      [bookkeepingCustomerId, `sub_bookkeeping_${bookkeepingOrgId}`, trialAt],
    )
    await executeSQL(
      `INSERT INTO public.orgs (
         id, name, management_email, created_by, customer_id
       ) VALUES ($1::uuid, $2, $3, $4::uuid, $5)`,
      [bookkeepingOrgId, `Audit Bookkeeping Org ${bookkeepingOrgId}`, TEST_EMAIL, USER_ID, bookkeepingCustomerId],
    )
    await executeSQL(
      `INSERT INTO public.apps (app_id, name, icon_url, owner_org)
       VALUES ($1, $2, 'https://example.com/icon.png', $3::uuid)`,
      [bookkeepingAppId, `Audit Bookkeeping App ${bookkeepingOrgId}`, bookkeepingOrgId],
    )

    const [orgRow] = await executeSQL(
      'SELECT id::text AS id FROM public.orgs WHERE id = $1::uuid',
      [bookkeepingOrgId],
    )
    const [appRow] = await executeSQL(
      'SELECT app_id FROM public.apps WHERE app_id = $1',
      [bookkeepingAppId],
    )
    expect(orgRow?.id).toBe(bookkeepingOrgId)
    expect(appRow?.app_id).toBe(bookkeepingAppId)

    async function countOrgUpdates() {
      const rows = await executeSQL(
        `SELECT COUNT(*)::integer AS count
         FROM public.audit_logs
         WHERE org_id = $1::uuid
           AND table_name = 'orgs'
           AND operation = 'UPDATE'`,
        [bookkeepingOrgId],
      )
      return rows[0]?.count ?? 0
    }

    async function countAppUpdates() {
      const rows = await executeSQL(
        `SELECT COUNT(*)::integer AS count
         FROM public.audit_logs
         WHERE org_id = $1::uuid
           AND table_name = 'apps'
           AND record_id = $2
           AND operation = 'UPDATE'`,
        [bookkeepingOrgId, bookkeepingAppId],
      )
      return rows[0]?.count ?? 0
    }

    const beforeOrg = await countOrgUpdates()
    const beforeApp = await countAppUpdates()

    const { error: orgStatsError } = await getSupabaseClient()
      .from('orgs')
      .update({ stats_updated_at: new Date().toISOString() })
      .eq('id', bookkeepingOrgId)
    expect(orgStatsError).toBeNull()

    const { error: orgRefreshError } = await getSupabaseClient()
      .from('orgs')
      .update({
        stats_refresh_requested_at: new Date().toISOString(),
        updated_at: new Date().toISOString(),
      })
      .eq('id', bookkeepingOrgId)
    expect(orgRefreshError).toBeNull()

    const { error: orgUpdatedAtError } = await getSupabaseClient()
      .from('orgs')
      .update({ updated_at: new Date().toISOString() })
      .eq('id', bookkeepingOrgId)
    expect(orgUpdatedAtError).toBeNull()

    const { error: appStatsError } = await getSupabaseClient()
      .from('app_stats_refresh_state')
      .update({ stats_updated_at: new Date().toISOString() })
      .eq('app_id', bookkeepingAppId)
    expect(appStatsError).toBeNull()

    const { error: appRefreshError } = await getSupabaseClient()
      .from('app_stats_refresh_state')
      .update({
        stats_refresh_requested_at: new Date().toISOString(),
      })
      .eq('app_id', bookkeepingAppId)
    expect(appRefreshError).toBeNull()

    await executeSQL(
      `UPDATE public.apps
       SET updated_at = now()
       WHERE app_id = $1`,
      [bookkeepingAppId],
    )

    await new Promise(resolve => setTimeout(resolve, 100))

    expect(await countOrgUpdates()).toBe(beforeOrg)
    expect(await countAppUpdates()).toBe(beforeApp)

    await executeSQL('DELETE FROM public.audit_logs WHERE org_id = $1::uuid', [bookkeepingOrgId])
    await executeSQL('DELETE FROM public.apps WHERE app_id = $1', [bookkeepingAppId])
    await executeSQL('DELETE FROM public.orgs WHERE id = $1::uuid', [bookkeepingOrgId])
    await executeSQL('DELETE FROM public.stripe_info WHERE customer_id = $1', [bookkeepingCustomerId])
  })

  it('org_users INSERT creates audit log', async () => {
    // Get another user to add to the org
    const { data: anotherUser, error: userError } = await getSupabaseClient()
      .from('users')
      .select('id')
      .neq('id', USER_ID)
      .limit(1)
      .single()

    expect(userError).toBeNull()
    expect(anotherUser).toBeTruthy()

    if (!anotherUser) {
      console.warn('Skipping test: Could not find another user')
      return
    }

    // Add user to org
    const { error: insertError } = await getSupabaseClient()
      .from('org_users')
      .insert({
        org_id: ORG_ID,
        user_id: anotherUser.id,
        rbac_role_name: 'org_member',
      })
    expect(insertError).toBeNull()

    // Wait a bit for the trigger to execute
    await new Promise(resolve => setTimeout(resolve, 100))

    // Fetch audit logs
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=org_users&operation=INSERT`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)

    if (safe.success && safe.data.data.length > 0) {
      const latestInsert = safe.data.data[0]
      expect(latestInsert.operation).toBe('INSERT')
      expect(latestInsert.table_name).toBe('org_users')
      expect(latestInsert.org_id).toBe(ORG_ID)
      expect(latestInsert.old_record).toBeNull() // INSERT has no old record
      expect(latestInsert.new_record).toBeTruthy()
    }

    // Clean up: delete the org_user
    await getSupabaseClient()
      .from('org_users')
      .delete()
      .eq('org_id', ORG_ID)
      .eq('user_id', anotherUser.id)
  })

  it('org_users DELETE creates audit log', async () => {
    // Get another user to add and then remove
    const { data: anotherUser, error: userError } = await getSupabaseClient()
      .from('users')
      .select('id')
      .neq('id', USER_ID)
      .limit(1)
      .single()

    expect(userError).toBeNull()
    expect(anotherUser).toBeTruthy()

    if (!anotherUser) {
      console.warn('Skipping test: Could not find another user')
      return
    }

    // Add user to org
    const { error: insertError } = await getSupabaseClient()
      .from('org_users')
      .insert({
        org_id: ORG_ID,
        user_id: anotherUser.id,
        rbac_role_name: 'org_member',
      })
    expect(insertError).toBeNull()

    // Wait for insert trigger
    await new Promise(resolve => setTimeout(resolve, 100))

    // Delete the org_user
    const { error: deleteError } = await getSupabaseClient()
      .from('org_users')
      .delete()
      .eq('org_id', ORG_ID)
      .eq('user_id', anotherUser.id)
    expect(deleteError).toBeNull()

    // Wait for delete trigger
    await new Promise(resolve => setTimeout(resolve, 100))

    // Fetch audit logs for DELETE
    const response = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=org_users&operation=DELETE`, {
      headers: authHeaders,
    })
    expect(response.status).toBe(200)
    const responseData = await response.json()
    const safe = parseAuditLogsResponse(responseData)
    expect(safe.success).toBe(true)

    if (safe.success && safe.data.data.length > 0) {
      const latestDelete = safe.data.data[0]
      expect(latestDelete.operation).toBe('DELETE')
      expect(latestDelete.table_name).toBe('org_users')
      expect(latestDelete.org_id).toBe(ORG_ID)
      expect(latestDelete.new_record).toBeNull() // DELETE has no new record
      expect(latestDelete.old_record).toBeTruthy()
    }
  })
})

// These tests verify that audit logs are created when using API key authentication
// This was a bug where CLI/API users were not logged because request identity resolution missed API keys.
describe('audit logs for app_versions via API key', () => {
  const testVersionName = `99.0.0-audit-test-${randomUUID()}`
  let createdVersionId: number | null = null

  afterAll(async () => {
    // Clean up: delete the test version if it was created
    if (createdVersionId) {
      await getSupabaseClient().from('app_versions').delete().eq('id', createdVersionId)
      // Also clean up any audit logs for this version
      await getSupabaseClient().from('audit_logs').delete().eq('record_id', createdVersionId.toString())
    }
  })

  it('app_version INSERT via API creates audit log with user_id from API key', async () => {
    // Create a bundle via the API (uses API key authentication)
    const response = await fetchTestRequest(`${BASE_URL}/bundle`, {
      method: 'POST',
      headers: apiKeyAuthHeaders,
      body: JSON.stringify({
        app_id: APIKEY_AUDIT_APP_ID,
        version: testVersionName,
        external_url: 'https://example.com/test-audit-bundle.zip',
        checksum: 'abc123def456',
      }),
    })

    expect(response.status).toBe(200)
    const responseData = await response.json() as { status: string, bundle: { id: number } }
    expect(responseData.status).toBe('success')
    expect(responseData.bundle).toBeTruthy()
    createdVersionId = responseData.bundle.id

    // Wait for the trigger to execute
    await new Promise(resolve => setTimeout(resolve, 200))

    // Fetch audit logs for the dedicated test org
    const auditResponse = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=app_versions&operation=INSERT`, {
      headers: authHeaders,
    })
    expect(auditResponse.status).toBe(200)
    const auditData = await auditResponse.json()
    const safe = parseAuditLogsResponse(auditData)
    expect(safe.success).toBe(true)

    if (safe.success) {
      // Find the audit log for our created version
      const versionAuditLog = safe.data.data.find(
        log => log.record_id === createdVersionId?.toString(),
      )

      expect(versionAuditLog).toBeTruthy()
      if (versionAuditLog) {
        expect(versionAuditLog.operation).toBe('INSERT')
        expect(versionAuditLog.table_name).toBe('app_versions')
        expect(versionAuditLog.org_id).toBe(ORG_ID)
        // This is the key assertion: user_id should be set from the API key
        expect(versionAuditLog.user_id).toBe(USER_ID)
        expect(versionAuditLog.actor_type).toBe('apikey')
        expect(versionAuditLog.actor_user_id).toBe(USER_ID)
        expect(versionAuditLog.actor_user_email).toBe(actorUserEmail)
        expect(versionAuditLog.actor_apikey_id).toBe(apiKeyId)
        expect(versionAuditLog.actor_apikey_name).toContain('audit-api-key-')
        expect(versionAuditLog.old_record).toBeNull()
        expect(versionAuditLog.new_record).toBeTruthy()
        if (versionAuditLog.new_record && typeof versionAuditLog.new_record === 'object') {
          expect((versionAuditLog.new_record as Record<string, unknown>).name).toBe(testVersionName)
          // Fat columns must stay out of audit TOAST to protect primary DB memory.
          expect((versionAuditLog.new_record as Record<string, unknown>).manifest).toBeUndefined()
          expect((versionAuditLog.new_record as Record<string, unknown>).native_packages).toBeUndefined()
        }
      }
    }
  })

  it('app_version UPDATE via API creates audit log with user_id from API key', async () => {
    // Skip if we don't have a version to update
    if (!createdVersionId) {
      console.warn('Skipping UPDATE test: no version was created')
      return
    }

    // Update the bundle via the API - note: endpoint requires version_id (number), not version name
    const response = await fetchTestRequest(`${BASE_URL}/bundle/metadata`, {
      method: 'POST',
      headers: apiKeyAuthHeaders,
      body: JSON.stringify({
        app_id: APIKEY_AUDIT_APP_ID,
        version_id: createdVersionId,
        comment: 'Updated via API key test',
      }),
    })

    expect(response.status).toBe(200)

    const updateAuditLog = await waitForAuditLog(
      `${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=app_versions&operation=UPDATE`,
      log => log.record_id === createdVersionId?.toString() && log.changed_fields?.includes('comment') === true,
    )

    expect(updateAuditLog.operation).toBe('UPDATE')
    expect(updateAuditLog.table_name).toBe('app_versions')
    expect(updateAuditLog.org_id).toBe(ORG_ID)
    // user_id should be set from the API key
    expect(updateAuditLog.user_id).toBe(USER_ID)
    expect(updateAuditLog.actor_type).toBe('apikey')
    expect(updateAuditLog.actor_user_id).toBe(USER_ID)
    expect(updateAuditLog.actor_user_email).toBe(actorUserEmail)
    expect(updateAuditLog.actor_apikey_id).toBe(apiKeyId)
    expect(updateAuditLog.old_record).toBeTruthy()
    expect(updateAuditLog.new_record).toBeTruthy()
    // changed_fields should include 'comment'
    expect(Array.isArray(updateAuditLog.changed_fields)).toBe(true)
    expect(updateAuditLog.changed_fields).toContain('comment')
  })

  it('app_version soft-DELETE via API creates UPDATE audit log with user_id from API key', async () => {
    // Skip if we don't have a version to delete
    if (!createdVersionId) {
      console.warn('Skipping DELETE test: no version was created')
      return
    }

    const versionIdToDelete = createdVersionId

    // Delete the bundle via the API - note: this is a soft-delete (sets deleted=true)
    const response = await fetchTestRequest(`${BASE_URL}/bundle`, {
      method: 'DELETE',
      headers: apiKeyAuthHeaders,
      body: JSON.stringify({
        app_id: APIKEY_AUDIT_APP_ID,
        version: testVersionName,
      }),
    })

    expect(response.status).toBe(200)

    // Wait for the trigger to execute
    await new Promise(resolve => setTimeout(resolve, 200))

    // Fetch audit logs for UPDATE operations (soft-delete creates UPDATE, not DELETE)
    const auditResponse = await fetchTestRequest(`${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=app_versions&operation=UPDATE`, {
      headers: authHeaders,
    })
    expect(auditResponse.status).toBe(200)
    const auditData = await auditResponse.json()
    const safe = parseAuditLogsResponse(auditData)
    expect(safe.success).toBe(true)

    if (safe.success) {
      // Find the audit log for our soft-deleted version:app_versions!channels_version_fkey(look for 'deleted' in changed_fields)
      const deleteAuditLog = safe.data.data.find(
        log => log.record_id === versionIdToDelete.toString()
          && log.changed_fields?.includes('deleted'),
      )

      expect(deleteAuditLog).toBeTruthy()
      if (deleteAuditLog) {
        expect(deleteAuditLog.operation).toBe('UPDATE')
        expect(deleteAuditLog.table_name).toBe('app_versions')
        expect(deleteAuditLog.org_id).toBe(ORG_ID)
        // user_id should be set from the API key
        expect(deleteAuditLog.user_id).toBe(USER_ID)
        expect(deleteAuditLog.actor_type).toBe('apikey')
        expect(deleteAuditLog.actor_user_id).toBe(USER_ID)
        expect(deleteAuditLog.actor_user_email).toBe(actorUserEmail)
        expect(deleteAuditLog.actor_apikey_id).toBe(apiKeyId)
        // Both old and new record should exist for UPDATE
        expect(deleteAuditLog.old_record).toBeTruthy()
        expect(deleteAuditLog.new_record).toBeTruthy()
        // changed_fields should include 'deleted'
        expect(deleteAuditLog.changed_fields).toContain('deleted')
        // Verify the deleted flag was set to true
        if (deleteAuditLog.new_record && typeof deleteAuditLog.new_record === 'object') {
          expect((deleteAuditLog.new_record as Record<string, unknown>).deleted).toBe(true)
        }
      }
    }
  })
})

describe('audit logs for channel promotions via API key bundle flow', () => {
  const sourceVersionName = `99.0.0-audit-channel-source-${randomUUID()}`
  const targetVersionName = `99.0.0-audit-channel-target-${randomUUID()}`
  const channelName = `audit-channel-${randomUUID()}`
  let sourceVersionId: number | null = null
  let targetVersionId: number | null = null
  let channelId: number | null = null

  beforeAll(async () => {
    const supabase = getSupabaseClient()

    const { data: insertedVersions, error: versionError } = await supabase
      .from('app_versions')
      .insert([
        {
          app_id: APIKEY_AUDIT_APP_ID,
          name: sourceVersionName,
          owner_org: ORG_ID,
        },
        {
          app_id: APIKEY_AUDIT_APP_ID,
          name: targetVersionName,
          owner_org: ORG_ID,
        },
      ])
      .select('id,name')

    if (versionError || !insertedVersions || insertedVersions.length !== 2) {
      throw new Error(`Failed to create audit channel versions: ${versionError?.message ?? 'missing versions'}`)
    }

    sourceVersionId = insertedVersions.find(version => version.name === sourceVersionName)?.id ?? null
    targetVersionId = insertedVersions.find(version => version.name === targetVersionName)?.id ?? null
    if (sourceVersionId === null || targetVersionId === null) {
      throw new Error('Failed to resolve audit channel version IDs')
    }

    const { data: insertedChannel, error: channelError } = await supabase
      .from('channels')
      .insert({
        name: channelName,
        app_id: APIKEY_AUDIT_APP_ID,
        version: sourceVersionId,
        created_by: USER_ID,
        owner_org: ORG_ID,
      })
      .select('id')
      .single()

    if (channelError || !insertedChannel) {
      throw new Error(`Failed to create audit channel: ${channelError?.message ?? 'missing channel'}`)
    }

    channelId = insertedChannel.id
  })

  afterAll(async () => {
    const supabase = getSupabaseClient()

    if (channelId !== null) {
      await supabase.from('channels').delete().eq('id', channelId)
      await supabase.from('audit_logs').delete().eq('record_id', channelId.toString()).eq('table_name', 'channels')
    }

    if (sourceVersionId !== null) {
      await supabase.from('app_versions').delete().eq('id', sourceVersionId)
    }

    if (targetVersionId !== null) {
      await supabase.from('app_versions').delete().eq('id', targetVersionId)
    }
  })

  it('channel UPDATE via API key bundle promotion keeps audit user_id attribution', async () => {
    if (!channelId || !targetVersionId) {
      throw new Error('Audit channel promotion test setup did not complete')
    }
    const promotionChannelId = channelId

    const response = await fetchTestRequest(`${BASE_URL}/bundle`, {
      method: 'PUT',
      headers: apiKeyAuthHeaders,
      body: JSON.stringify({
        app_id: APIKEY_AUDIT_APP_ID,
        version_id: targetVersionId,
        channel_id: promotionChannelId,
      }),
    })

    expect(response.status).toBe(200)

    const promotionAuditLog = await waitForAuditLog(
      `${BASE_URL}/organization/audit?orgId=${ORG_ID}&tableName=channels&operation=UPDATE`,
      log => log.record_id === promotionChannelId.toString() && log.changed_fields?.includes('version') === true,
    )

    expect(promotionAuditLog.operation).toBe('UPDATE')
    expect(promotionAuditLog.table_name).toBe('channels')
    expect(promotionAuditLog.org_id).toBe(ORG_ID)
    expect(promotionAuditLog.user_id).toBe(USER_ID)
    expect(promotionAuditLog.actor_type).toBe('apikey')
    expect(promotionAuditLog.actor_user_id).toBe(USER_ID)
    expect(promotionAuditLog.actor_user_email).toBe(actorUserEmail)
    expect(promotionAuditLog.actor_apikey_id).toBe(apiKeyId)
    expect(promotionAuditLog.changed_fields).toContain('version')

    if (promotionAuditLog.new_record && typeof promotionAuditLog.new_record === 'object') {
      expect((promotionAuditLog.new_record as Record<string, unknown>).version).toBe(targetVersionId)
    }
  })
})