// Seeder tests — Priority 1 hardening
//
// Tests every pack for: seed-once, seed-twice (idempotency), remove-once,
// remove-twice, seed-after-remove, and retry-after-failed-status.
//
// Key invariants proven for every pack:
//   1. Every upserted/inserted row carries is_demo: true (where applicable)
//   2. Upserts use onConflict: 'id' — never blind inserts for stable-ID tables
//   3. Delete operations filter ONLY by is_demo=true or stable demo IDs
//   4. Cross-tenant: operations go only to the provided client, never to global state

import { describe, it, expect, vi, beforeEach } from 'vitest'
import type { SupabaseClient } from '@supabase/supabase-js'

// ─── Mock client factory ──────────────────────────────────────────────────────
//
// Tracks upserts, inserts, and deletes.
// Returns pre-configured lookup data for select queries.
// count: N for tables where the seeder reads a COUNT (datenablage guard).

interface UpsertOp { table: string; rows: unknown[]; onConflict: string | undefined }
interface InsertOp { table: string; rows: unknown[] }
interface DeleteOp { table: string; filters: Array<{ method: 'eq' | 'in'; col: string; val: unknown }> }

function makeClient(
  tableData: Record<string, unknown[]> = {},
  countOverride: Record<string, number> = {},
) {
  const upserts: UpsertOp[] = []
  const inserts: InsertOp[] = []
  const deletes: DeleteOp[] = []

  function makeDeleteChain(table: string) {
    const localFilters: Array<{ method: 'eq' | 'in'; col: string; val: unknown }> = []
    const chain: Record<string, unknown> = {}

    chain.eq = vi.fn((col: string, val: unknown) => {
      localFilters.push({ method: 'eq', col, val })
      return chain
    })
    chain.in = vi.fn((col: string, val: unknown) => {
      localFilters.push({ method: 'in', col, val })
      return chain
    })
    chain.then = (resolve: (v: unknown) => unknown) => {
      const snapshot = [...localFilters]
      deletes.push({ table, filters: snapshot })
      return Promise.resolve({ data: null, error: null, count: 0 }).then(resolve)
    }
    return chain
  }

  function makeTableChain(table: string) {
    const rows = tableData[table] ?? null
    const count = countOverride[table] ?? (Array.isArray(rows) ? rows.length : 0)
    const resolved = { data: rows, error: null, count }

    const chain: Record<string, unknown> = {}

    chain.select = vi.fn(() => chain)
    chain.eq     = vi.fn(() => chain)
    chain.in     = vi.fn(() => chain)
    chain.order  = vi.fn(() => chain)
    chain.limit  = vi.fn(() => chain)
    chain.single      = vi.fn(() => Promise.resolve({ data: rows?.[0] ?? null, error: null }))
    chain.maybeSingle = vi.fn(() => Promise.resolve({ data: rows?.[0] ?? null, error: null }))
    chain.then = (resolve: (v: unknown) => unknown) =>
      Promise.resolve(resolved).then(resolve)

    chain.upsert = vi.fn((data: unknown, opts?: unknown) => {
      const rowArr = Array.isArray(data) ? data : [data]
      upserts.push({
        table,
        rows: rowArr,
        onConflict: (opts as { onConflict?: string } | undefined)?.onConflict,
      })
      return Promise.resolve({ data: null, error: null })
    })

    chain.insert = vi.fn((data: unknown) => {
      inserts.push({ table, rows: Array.isArray(data) ? data : [data] })
      return Promise.resolve({ data: null, error: null })
    })

    chain.delete = vi.fn(() => makeDeleteChain(table))

    return chain
  }

  const client = {
    upserts, inserts, deletes,
    from: vi.fn((table: string) => makeTableChain(table)),
  }

  return client as typeof client & SupabaseClient
}

// ─── Standard lookup data required by packs ───────────────────────────────────

const LOOKUP_DATA = {
  project_types:            [{ id: 'pt-1', code: 'lsc_workshop' }, { id: 'pt-2', code: 'fabrikanalyse' }],
  project_statuses:         [{ id: 'ps-1', code: 'status_10' }, { id: 'ps-2', code: 'status_40' }, { id: 'ps-3', code: 'status_70' }],
  project_responsibilities: [{ id: 'pr-1', code: 'fv' }, { id: 'pr-2', code: 'li' }],
  supplier_master_data:     [
    { id: 'sup-1', supplier_name: 'Continental Teves AG' },
    { id: 'sup-2', supplier_name: 'Bosch Rexroth AG' },
    { id: 'sup-3', supplier_name: 'ZF Friedrichshafen AG' },
  ],
  consultants: [
    { id: 'c-1' }, { id: 'c-2' }, { id: 'c-3' },
    { id: 'c-4' }, { id: 'c-5' }, { id: 'c-6' },
  ],
  appointment_types: [
    { id: 'at-1', code: 'kundenbesuch' },
    { id: 'at-2', code: 'intern' },
  ],
  assessment_questions: Array.from({ length: 54 }, (_, i) => ({
    id:            `q-${i + 1}`,
    index_number:  i + 1,
    sort_order:    i,
  })),
  // For datenablage guard: projects must exist
  projects: [
    { id: 'dddd0001-0000-0000-0000-000000000001', is_demo: true },
  ],
}

// ─── Import under test ────────────────────────────────────────────────────────

import { seedPack, removePack } from '@/lib/demo/seeder'
import {
  DEMO_PROJECT_IDS,
  DEMO_PROCESS_STEP_IDS,
  DEMO_PLANNING_PROJECT_IDS,
  DEMO_ASSESSMENT_IDS,
} from '@/lib/demo/demo-ids'
import { DEMO_PROCESS_STEPS, DEMO_MEASUREMENTS } from '@/lib/demo/seeds/lsc-workshop-seed'

// ─── Helper: assert every row in an array has is_demo: true ──────────────────

function assertAllTagged(rows: unknown[], table: string) {
  for (const row of rows) {
    expect((row as { is_demo?: unknown }).is_demo, `${table}: row missing is_demo=true`).toBe(true)
  }
}

// ─── Core safety guarantees ───────────────────────────────────────────────────

describe('seeder — cross-tenant boundary isolation', () => {
  it('operations go only to the provided client, never to a second client', async () => {
    const clientA = makeClient(LOOKUP_DATA, { projects: 1 })
    const clientB = makeClient(LOOKUP_DATA, { projects: 1 })

    await seedPack(clientA as unknown as SupabaseClient, 'lsc_workshop')

    expect(clientA.from).toHaveBeenCalled()
    expect(clientB.from).not.toHaveBeenCalled()
    expect(clientB.upserts).toHaveLength(0)
    expect(clientB.inserts).toHaveLength(0)
    expect(clientB.deletes).toHaveLength(0)
  })

  it('two parallel seed calls to different clients do not cross-contaminate', async () => {
    const clientA = makeClient(LOOKUP_DATA, { projects: 1 })
    const clientB = makeClient(LOOKUP_DATA, { projects: 1 })

    await Promise.all([
      seedPack(clientA as unknown as SupabaseClient, 'oee'),
      seedPack(clientB as unknown as SupabaseClient, 'wertstrom'),
    ])

    // A only has oee tables, B only has wertstrom tables
    const aUpsertTables = clientA.upserts.map((u) => u.table)
    const bUpsertTables = clientB.upserts.map((u) => u.table)

    expect(aUpsertTables).toContain('oee_records')
    expect(aUpsertTables).not.toContain('value_stream_maps')
    expect(bUpsertTables).toContain('value_stream_maps')
    expect(bUpsertTables).not.toContain('oee_records')
  })
})

// ─── Pack: projektanlage ──────────────────────────────────────────────────────

describe('seeder — projektanlage', () => {
  let client: ReturnType<typeof makeClient>

  beforeEach(() => {
    client = makeClient(LOOKUP_DATA, { projects: 1 })
  })

  it('seed once: upserts projects with is_demo=true', async () => {
    const result = await seedPack(client as unknown as SupabaseClient, 'projektanlage')
    expect(result.success).toBe(true)

    const projectUpsert = client.upserts.find((u) => u.table === 'projects')
    expect(projectUpsert).toBeDefined()
    assertAllTagged(projectUpsert!.rows, 'projects')
  })

  it('seed once: project rows use stable DEMO_PROJECT_IDS', async () => {
    await seedPack(client as unknown as SupabaseClient, 'projektanlage')

    const projectUpsert = client.upserts.find((u) => u.table === 'projects')!
    const ids = projectUpsert.rows.map((r) => (r as { id: string }).id)
    for (const id of DEMO_PROJECT_IDS) {
      expect(ids).toContain(id)
    }
  })

  it('seed once: uses onConflict: id (idempotent upsert)', async () => {
    await seedPack(client as unknown as SupabaseClient, 'projektanlage')

    for (const u of client.upserts) {
      if (u.table === 'projects') {
        expect(u.onConflict).toBe('id')
      }
    }
  })

  it('seed twice: no error, upsert called twice (idempotent)', async () => {
    const r1 = await seedPack(client as unknown as SupabaseClient, 'projektanlage')
    const r2 = await seedPack(client as unknown as SupabaseClient, 'projektanlage')
    expect(r1.success).toBe(true)
    expect(r2.success).toBe(true)

    const projectUpserts = client.upserts.filter((u) => u.table === 'projects')
    expect(projectUpserts).toHaveLength(2)
  })

  it('remove once: deletes projects by is_demo=true filter only', async () => {
    await removePack(client as unknown as SupabaseClient, 'projektanlage')

    const projectDelete = client.deletes.find((d) => d.table === 'projects')
    expect(projectDelete).toBeDefined()

    const hasDemoFilter = projectDelete!.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )
    expect(hasDemoFilter).toBe(true)
  })

  it('remove once: never deletes with non-is_demo filter on user tables', async () => {
    await removePack(client as unknown as SupabaseClient, 'projektanlage')

    const userTables = ['projects', 'documents', 'user_audit_log', 'email_queue']
    for (const del of client.deletes.filter((d) => userTables.includes(d.table))) {
      const hasSafeFilter = del.filters.some(
        (f) => (f.method === 'eq' && f.col === 'is_demo') || (f.method === 'in' && f.col === 'project_id')
      )
      expect(hasSafeFilter, `Delete on ${del.table} missing safe filter`).toBe(true)
    }
  })

  it('remove twice: safe — no error on second removal', async () => {
    const r1 = await removePack(client as unknown as SupabaseClient, 'projektanlage')
    const r2 = await removePack(client as unknown as SupabaseClient, 'projektanlage')
    expect(r1.success).toBe(true)
    expect(r2.success).toBe(true)
  })

  it('seed after remove: successfully re-seeds', async () => {
    const remove = await removePack(client as unknown as SupabaseClient, 'projektanlage')
    const reseed = await seedPack(client as unknown as SupabaseClient, 'projektanlage')
    expect(remove.success).toBe(true)
    expect(reseed.success).toBe(true)

    const projectUpserts = client.upserts.filter((u) => u.table === 'projects')
    expect(projectUpserts.length).toBeGreaterThanOrEqual(1)
  })
})

// ─── Pack: kalender ───────────────────────────────────────────────────────────

describe('seeder — kalender', () => {
  let client: ReturnType<typeof makeClient>

  beforeEach(() => {
    client = makeClient(LOOKUP_DATA)
  })

  it('seed once: upserts assignments with is_demo=true', async () => {
    const result = await seedPack(client as unknown as SupabaseClient, 'kalender')
    expect(result.success).toBe(true)

    const assignUpsert = client.upserts.find((u) => u.table === 'assignments')
    expect(assignUpsert).toBeDefined()
    assertAllTagged(assignUpsert!.rows, 'assignments')
  })

  it('seed once: upserts planning_projects first (FK dependency)', async () => {
    await seedPack(client as unknown as SupabaseClient, 'kalender')

    const ppIdx = client.upserts.findIndex((u) => u.table === 'planning_projects')
    const assIdx = client.upserts.findIndex((u) => u.table === 'assignments')
    expect(ppIdx).toBeGreaterThanOrEqual(0)
    expect(assIdx).toBeGreaterThan(ppIdx)
  })

  it('seed once: planning_projects use stable IDs', async () => {
    await seedPack(client as unknown as SupabaseClient, 'kalender')

    const ppUpsert = client.upserts.find((u) => u.table === 'planning_projects')!
    const ids = ppUpsert.rows.map((r) => (r as { id: string }).id)
    for (const id of DEMO_PLANNING_PROJECT_IDS) {
      expect(ids).toContain(id)
    }
  })

  it('seed once: uses onConflict: id for assignments', async () => {
    await seedPack(client as unknown as SupabaseClient, 'kalender')

    const u = client.upserts.find((u) => u.table === 'assignments')!
    expect(u.onConflict).toBe('id')
  })

  it('seed: fails gracefully with no consultants', async () => {
    const noConsultantsClient = makeClient({ ...LOOKUP_DATA, consultants: [] })
    const result = await seedPack(noConsultantsClient as unknown as SupabaseClient, 'kalender')
    // Should return success=false with an error about consultants
    expect(result.success).toBe(false)
    expect(result.errors.join(' ')).toContain('Berater')
  })

  it('remove: deletes assignments by is_demo=true only', async () => {
    await removePack(client as unknown as SupabaseClient, 'kalender')

    const assignDelete = client.deletes.find((d) => d.table === 'assignments')
    expect(assignDelete).toBeDefined()
    expect(assignDelete!.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )).toBe(true)
  })

  it('remove: also removes planning_projects by stable IDs', async () => {
    await removePack(client as unknown as SupabaseClient, 'kalender')

    const ppDelete = client.deletes.find((d) => d.table === 'planning_projects')
    expect(ppDelete).toBeDefined()
    expect(ppDelete!.filters.some(
      (f) => f.method === 'in' && f.col === 'id'
    )).toBe(true)
  })
})

// ─── Pack: lsc_workshop ───────────────────────────────────────────────────────

describe('seeder — lsc_workshop', () => {
  let client: ReturnType<typeof makeClient>

  beforeEach(() => {
    client = makeClient()
  })

  it('seed once: upserts all 9 process steps', async () => {
    await seedPack(client as unknown as SupabaseClient, 'lsc_workshop')

    const stepsUpsert = client.upserts.find((u) => u.table === 'process_steps')
    expect(stepsUpsert).toBeDefined()
    expect(stepsUpsert!.rows).toHaveLength(DEMO_PROCESS_STEPS.length)
    assertAllTagged(stepsUpsert!.rows, 'process_steps')
  })

  it('seed once: process steps use stable DEMO_PROCESS_STEP_IDS', async () => {
    await seedPack(client as unknown as SupabaseClient, 'lsc_workshop')

    const stepsUpsert = client.upserts.find((u) => u.table === 'process_steps')!
    const ids = stepsUpsert.rows.map((r) => (r as { id: string }).id)
    for (const id of DEMO_PROCESS_STEP_IDS) {
      expect(ids).toContain(id)
    }
  })

  it('seed once: batches measurements in groups ≤ 30', async () => {
    await seedPack(client as unknown as SupabaseClient, 'lsc_workshop')

    const measUpserts = client.upserts.filter((u) => u.table === 'cycle_measurements')
    // 9 steps × 7 measurements = 63, so 3 batches: 30, 30, 3
    expect(measUpserts).toHaveLength(Math.ceil(DEMO_MEASUREMENTS.length / 30))
    for (const batch of measUpserts) {
      expect(batch.rows.length).toBeLessThanOrEqual(30)
    }
  })

  it('seed once: all measurement rows tagged is_demo=true', async () => {
    await seedPack(client as unknown as SupabaseClient, 'lsc_workshop')

    const measUpserts = client.upserts.filter((u) => u.table === 'cycle_measurements')
    for (const batch of measUpserts) {
      assertAllTagged(batch.rows, 'cycle_measurements')
    }
  })

  it('seed twice: idempotent — uses onConflict: id each time', async () => {
    await seedPack(client as unknown as SupabaseClient, 'lsc_workshop')
    await seedPack(client as unknown as SupabaseClient, 'lsc_workshop')

    const measUpserts = client.upserts.filter((u) => u.table === 'cycle_measurements')
    // 3 batches × 2 calls = 6
    expect(measUpserts).toHaveLength(Math.ceil(DEMO_MEASUREMENTS.length / 30) * 2)
    for (const u of measUpserts) {
      expect(u.onConflict).toBe('id')
    }
  })

  it('remove: deletes cycle_measurements by stable DEMO_PROCESS_STEP_IDS (not by user data)', async () => {
    await removePack(client as unknown as SupabaseClient, 'lsc_workshop')

    const cmDelete = client.deletes.find((d) => d.table === 'cycle_measurements')
    expect(cmDelete).toBeDefined()

    const usesStableIds = cmDelete!.filters.some(
      (f) => f.method === 'in' && f.col === 'process_step_id' &&
        Array.isArray(f.val) && (f.val as string[]).every((id) => id.startsWith('dddd0010'))
    )
    expect(usesStableIds).toBe(true)
  })

  it('remove: deletes process_steps by is_demo=true (not by user data)', async () => {
    await removePack(client as unknown as SupabaseClient, 'lsc_workshop')

    const psDelete = client.deletes.find((d) => d.table === 'process_steps')
    expect(psDelete).toBeDefined()
    expect(psDelete!.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )).toBe(true)
  })

  it('remove twice: safe — no error', async () => {
    const r1 = await removePack(client as unknown as SupabaseClient, 'lsc_workshop')
    const r2 = await removePack(client as unknown as SupabaseClient, 'lsc_workshop')
    expect(r1.success).toBe(true)
    expect(r2.success).toBe(true)
  })
})

// ─── Pack: fabrikanalyse ──────────────────────────────────────────────────────

describe('seeder — fabrikanalyse', () => {
  let client: ReturnType<typeof makeClient>

  beforeEach(() => {
    client = makeClient(LOOKUP_DATA)
  })

  it('seed once: upserts assessment with is_demo=true', async () => {
    const result = await seedPack(client as unknown as SupabaseClient, 'fabrikanalyse')
    expect(result.success).toBe(true)

    const aUpsert = client.upserts.find((u) => u.table === 'assessments')
    expect(aUpsert).toBeDefined()
    assertAllTagged(aUpsert!.rows, 'assessments')
  })

  it('seed once: uses stable DEMO_ASSESSMENT_IDS[0]', async () => {
    await seedPack(client as unknown as SupabaseClient, 'fabrikanalyse')

    const aUpsert = client.upserts.find((u) => u.table === 'assessments')!
    const id = (aUpsert.rows[0] as { id: string }).id
    expect(id).toBe(DEMO_ASSESSMENT_IDS[0])
  })

  it('seed once: deletes existing responses before inserting (prevents duplicates)', async () => {
    await seedPack(client as unknown as SupabaseClient, 'fabrikanalyse')

    const responsesDelete = client.deletes.find((d) => d.table === 'assessment_responses')
    expect(responsesDelete).toBeDefined()

    const insertAfterDelete = client.inserts.find((ins) => ins.table === 'assessment_responses')
    expect(insertAfterDelete).toBeDefined()
  })

  it('seed twice: deletes before each insert — no duplicate rows', async () => {
    await seedPack(client as unknown as SupabaseClient, 'fabrikanalyse')
    await seedPack(client as unknown as SupabaseClient, 'fabrikanalyse')

    const responseDeletes = client.deletes.filter((d) => d.table === 'assessment_responses')
    const responseInserts = client.inserts.filter((ins) => ins.table === 'assessment_responses')
    // Each seed call = one delete + one insert
    expect(responseDeletes).toHaveLength(2)
    expect(responseInserts).toHaveLength(2)
  })

  it('remove: deletes assessment_responses before assessments (child before parent)', async () => {
    await removePack(client as unknown as SupabaseClient, 'fabrikanalyse')

    const delTables = client.deletes.map((d) => d.table)
    const responsesIdx = delTables.lastIndexOf('assessment_responses')
    const assessmentsIdx = delTables.indexOf('assessments')
    expect(responsesIdx).toBeGreaterThanOrEqual(0)
    expect(assessmentsIdx).toBeGreaterThan(responsesIdx)
  })

  it('remove: assessment_responses filtered by stable DEMO_ASSESSMENT_IDS', async () => {
    await removePack(client as unknown as SupabaseClient, 'fabrikanalyse')

    const arDelete = client.deletes.find((d) => d.table === 'assessment_responses')!
    const usesStableIds = arDelete.filters.some(
      (f) => f.method === 'in' && f.col === 'assessment_id'
    )
    expect(usesStableIds).toBe(true)
  })

  it('remove: assessments filtered by is_demo=true', async () => {
    await removePack(client as unknown as SupabaseClient, 'fabrikanalyse')

    const aDelete = client.deletes.find((d) => d.table === 'assessments')!
    expect(aDelete.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )).toBe(true)
  })
})

// ─── Pack: oee ────────────────────────────────────────────────────────────────

describe('seeder — oee', () => {
  let client: ReturnType<typeof makeClient>

  beforeEach(() => {
    client = makeClient()
  })

  it('seed once: upserts oee_records with is_demo=true', async () => {
    const result = await seedPack(client as unknown as SupabaseClient, 'oee')
    expect(result.success).toBe(true)

    const u = client.upserts.find((u) => u.table === 'oee_records')
    expect(u).toBeDefined()
    assertAllTagged(u!.rows, 'oee_records')
  })

  it('seed once: deletes then inserts loss categories (no stable IDs)', async () => {
    await seedPack(client as unknown as SupabaseClient, 'oee')

    const lcDelete = client.deletes.find((d) => d.table === 'oee_loss_categories')
    const lcInsert = client.inserts.find((ins) => ins.table === 'oee_loss_categories')
    expect(lcDelete).toBeDefined()
    expect(lcInsert).toBeDefined()
  })

  it('seed once: loss category delete uses is_demo=true filter', async () => {
    await seedPack(client as unknown as SupabaseClient, 'oee')

    const lcDelete = client.deletes.find((d) => d.table === 'oee_loss_categories')!
    expect(lcDelete.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )).toBe(true)
  })

  it('seed twice: idempotent — oee_records upserted twice, loss cats deleted and re-inserted twice', async () => {
    await seedPack(client as unknown as SupabaseClient, 'oee')
    await seedPack(client as unknown as SupabaseClient, 'oee')

    const lcDeletes = client.deletes.filter((d) => d.table === 'oee_loss_categories')
    const lcInserts = client.inserts.filter((ins) => ins.table === 'oee_loss_categories')
    expect(lcDeletes).toHaveLength(2)
    expect(lcInserts).toHaveLength(2)
  })

  it('remove: deletes oee_loss_categories and oee_records by is_demo=true', async () => {
    await removePack(client as unknown as SupabaseClient, 'oee')

    for (const table of ['oee_loss_categories', 'oee_records']) {
      const del = client.deletes.find((d) => d.table === table)
      expect(del, `Expected delete on ${table}`).toBeDefined()
      expect(del!.filters.some(
        (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
      ), `${table} delete missing is_demo filter`).toBe(true)
    }
  })
})

// ─── Pack: wertstrom ──────────────────────────────────────────────────────────

describe('seeder — wertstrom', () => {
  let client: ReturnType<typeof makeClient>

  beforeEach(() => {
    client = makeClient()
  })

  it('seed once: upserts value_stream_maps with is_demo=true', async () => {
    const result = await seedPack(client as unknown as SupabaseClient, 'wertstrom')
    expect(result.success).toBe(true)

    const u = client.upserts.find((u) => u.table === 'value_stream_maps')
    expect(u).toBeDefined()
    assertAllTagged(u!.rows, 'value_stream_maps')
  })

  it('seed twice: idempotent — uses onConflict: id', async () => {
    await seedPack(client as unknown as SupabaseClient, 'wertstrom')
    await seedPack(client as unknown as SupabaseClient, 'wertstrom')

    const vsUpserts = client.upserts.filter((u) => u.table === 'value_stream_maps')
    expect(vsUpserts).toHaveLength(2)
    for (const u of vsUpserts) {
      expect(u.onConflict).toBe('id')
    }
  })

  it('remove: deletes by is_demo=true, never by user-keyed filter', async () => {
    await removePack(client as unknown as SupabaseClient, 'wertstrom')

    const del = client.deletes.find((d) => d.table === 'value_stream_maps')
    expect(del).toBeDefined()
    expect(del!.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )).toBe(true)

    // Must NOT have a filter like eq('created_by', userId) — that would be user-data-based
    const hasUserKeyedFilter = del!.filters.some(
      (f) => f.col === 'created_by' || f.col === 'user_id'
    )
    expect(hasUserKeyedFilter).toBe(false)
  })
})

// ─── Pack: datenablage ────────────────────────────────────────────────────────

describe('seeder — datenablage', () => {
  it('seed: upserts documents with is_demo=true when projects exist', async () => {
    const client = makeClient(LOOKUP_DATA, { projects: 3 })
    const result = await seedPack(client as unknown as SupabaseClient, 'datenablage')
    expect(result.success).toBe(true)

    const u = client.upserts.find((u) => u.table === 'documents')
    expect(u).toBeDefined()
    assertAllTagged(u!.rows, 'documents')
  })

  it('seed: fails gracefully when no demo projects exist (guard)', async () => {
    const client = makeClient({ ...LOOKUP_DATA, projects: [] }, { projects: 0 })
    const result = await seedPack(client as unknown as SupabaseClient, 'datenablage')
    expect(result.success).toBe(false)
    expect(result.errors.join(' ')).toContain('Projektanlage')
  })

  it('seed twice: uses onConflict: id for documents', async () => {
    const client = makeClient(LOOKUP_DATA, { projects: 3 })
    await seedPack(client as unknown as SupabaseClient, 'datenablage')
    await seedPack(client as unknown as SupabaseClient, 'datenablage')

    const docUpserts = client.upserts.filter((u) => u.table === 'documents')
    expect(docUpserts).toHaveLength(2)
    for (const u of docUpserts) {
      expect(u.onConflict).toBe('id')
    }
  })

  it('remove: deletes documents by is_demo=true only', async () => {
    const client = makeClient(LOOKUP_DATA, { projects: 3 })
    await removePack(client as unknown as SupabaseClient, 'datenablage')

    const del = client.deletes.find((d) => d.table === 'documents')
    expect(del).toBeDefined()
    expect(del!.filters.some(
      (f) => f.method === 'eq' && f.col === 'is_demo' && f.val === true
    )).toBe(true)
  })
})

// ─── Remove safety proofs — no non-demo data touched ────────────────────────

describe('seeder — remove safety: never deletes real customer data', () => {
  const ALL_PACKS = ['projektanlage', 'kalender', 'lsc_workshop', 'fabrikanalyse', 'oee', 'wertstrom', 'datenablage'] as const

  it('every delete has at least one filter (no unfiltered DELETE ALL)', async () => {
    for (const pack of ALL_PACKS) {
      const client = makeClient(LOOKUP_DATA, { projects: 3 })
      await removePack(client as unknown as SupabaseClient, pack)

      for (const del of client.deletes) {
        expect(
          del.filters.length,
          `Pack "${pack}" table "${del.table}": delete has no filters — would wipe all rows`
        ).toBeGreaterThan(0)
      }
    }
  })

  it('every delete filter uses is_demo=true or a stable demo ID list', async () => {
    for (const pack of ALL_PACKS) {
      const client = makeClient(LOOKUP_DATA, { projects: 3 })
      await removePack(client as unknown as SupabaseClient, pack)

      for (const del of client.deletes) {
        const isSafe = del.filters.every((f) => {
          if (f.method === 'eq' && f.col === 'is_demo' && f.val === true) return true
          if (f.method === 'in' && f.col === 'id') {
            // All IDs must be stable dddd-prefixed UUIDs
            return Array.isArray(f.val) && (f.val as string[]).every((id) => id.startsWith('dddd'))
          }
          if (f.method === 'in' && (f.col === 'project_id' || f.col === 'process_step_id' || f.col === 'assessment_id')) {
            // Must also be stable dddd-prefixed UUIDs
            return Array.isArray(f.val) && (f.val as string[]).every((id) => id.startsWith('dddd'))
          }
          return false
        })

        expect(
          isSafe,
          `Pack "${pack}" table "${del.table}": unsafe delete filter: ${JSON.stringify(del.filters)}`
        ).toBe(true)
      }
    }
  })
})

// ─── Pack coverage ────────────────────────────────────────────────────────────

describe('seeder — all pack codes handled', () => {
  const ALL_PACK_CODES = [
    'projektanlage', 'kalender', 'lsc_workshop', 'fabrikanalyse',
    'oee', 'wertstrom', 'datenablage', 'berater',
  ] as const

  for (const pack of ALL_PACK_CODES) {
    it(`seedPack does not throw for pack "${pack}"`, async () => {
      const client = makeClient(LOOKUP_DATA, { projects: 3 })
      await expect(
        seedPack(client as unknown as SupabaseClient, pack)
      ).resolves.not.toThrow()
    })

    it(`removePack does not throw for pack "${pack}"`, async () => {
      const client = makeClient(LOOKUP_DATA, { projects: 3 })
      await expect(
        removePack(client as unknown as SupabaseClient, pack)
      ).resolves.not.toThrow()
    })
  }
})