// Demo Seeder — executes seed/remove logic for a specific pack code against a
// given tenant's Supabase database. Accepts raw credentials so it can be called
// both from:
//   • The provisioning runner (using cp_tenant_environments credentials)
//   • The demo load API route (using the host-app admin client directly)
//
// All operations are idempotent: upsert on stable IDs, delete-then-insert for
// tables without stable IDs (oee_loss_categories, assessment_responses).

import { createClient } from '@supabase/supabase-js'
import type { SupabaseClient } from '@supabase/supabase-js'
// tdd-guard:skip — seeder module; inline local type replacing deleted control-plane import
type DemoPackCode = 'projektanlage' | 'kalender' | 'lsc_workshop' | 'fabrikanalyse' | 'oee' | 'wertstrom' | 'berater' | 'datenablage' | 'auftragseingang' | 'top_management_demo'
import { DEMO_PROJECTS } from '@/lib/demo/seeds/projektanlage-seed'
import { DEMO_ASSIGNMENTS, DEMO_PLANNING_PROJECTS } from '@/lib/demo/seeds/kalender-seed'
import {
  DEMO_PROCESS_STEPS,
  DEMO_MEASUREMENTS,
  DEMO_LSC_SHIFTS,
  DEMO_LSC_MEASURES,
  DEMO_KAPA_DATA,
  DEMO_ALL_PROCESS_STEP_IDS,
} from '@/lib/demo/seeds/lsc-workshop-seed'
import {
  DEMO_ASSESSMENTS,
  buildAssessmentResponses,
} from '@/lib/demo/seeds/fabrikanalyse-seed'
import { DEMO_OEE_RECORDS, DEMO_OEE_LOSS_CATEGORIES } from '@/lib/demo/seeds/oee-seed'
import { DEMO_VSMS } from '@/lib/demo/seeds/wertstrom-seed'
import { DEMO_DOCUMENTS } from '@/lib/demo/seeds/datenablage-seed'
import { DEMO_AUDIT_ENTRIES, DEMO_EMAIL_ENTRIES } from '@/lib/demo/seeds/admin-seed'
import { DEMO_INTAKES } from '@/lib/demo/seeds/auftragseingang-seed'
import {
  DEMO_PROJECT_IDS,
  DEMO_ASSESSMENT_IDS,
  DEMO_PLANNING_PROJECT_IDS,
  DEMO_INTAKE_IDS,
  DEMO_TOP_MGMT_PROJECT_ID,
  DEMO_TOP_MGMT_ASSESSMENT_ID,
  DEMO_TOP_MGMT_AGENDA_ID,
  DEMO_TOP_MGMT_AGENDA_DAY_IDS,
  DEMO_TOP_MGMT_PROCESS_STEP_IDS,
  DEMO_TOP_MGMT_WORKSHOP_ACTION_IDS,
  DEMO_TOP_MGMT_VSM_ID,
  DEMO_TOP_MGMT_CONSULTANT_ID,
} from '@/lib/demo/demo-ids'
import {
  DEMO_TOP_MGMT_SUPPLIER,
  DEMO_TOP_MGMT_PROJECT,
  DEMO_TOP_MGMT_PROJECT_CODE,
  DEMO_TOP_MGMT_PROJECT_TYPE_CODES,
  DEMO_TOP_MGMT_CONSULTANTS,
  DEMO_TOP_MGMT_AGENDA,
  DEMO_TOP_MGMT_AGENDA_DAYS,
  DEMO_TOP_MGMT_AGENDA_ITEMS,
  DEMO_TOP_MGMT_AGENDA_PARTICIPANTS,
  DEMO_TOP_MGMT_PROCESS_STEPS,
  DEMO_TOP_MGMT_CYCLE_MEASUREMENTS,
  DEMO_TOP_MGMT_PROCESS_STEP_OBSERVATIONS,
  DEMO_TOP_MGMT_LSC_SHIFTS,
  DEMO_TOP_MGMT_KAPA_DATA,
  DEMO_TOP_MGMT_LSC_MEASURES,
  DEMO_TOP_MGMT_WORKSHOP_ACTIONS,
  DEMO_TOP_MGMT_ASSESSMENT,
} from '@/lib/demo/seeds/top-management-demo-seed'
import { TOP_MANAGEMENT_DEMO_FA_RESPONSES } from '@/lib/demo/seeds/top-management-demo-fa-responses'
import { seedTopManagementDemoQaf } from '@/lib/demo/seeds/top-management-demo-qaf'
import { seedTopManagementDemoVsm } from '@/lib/demo/seeds/top-management-demo-vsm'

// ─── Types ────────────────────────────────────────────────────────────────────

export interface SeedPackResult {
  packCode: DemoPackCode
  success: boolean
  recordsAffected: number
  errors: string[]
}

// ─── Client factory ───────────────────────────────────────────────────────────

export function createTenantAdminClient(supabaseUrl: string, serviceRoleKey: string): SupabaseClient {
  return createClient(supabaseUrl, serviceRoleKey, {
    auth: { autoRefreshToken: false, persistSession: false },
  })
}

// ─── System user ID for provisioning-triggered seeds ─────────────────────────
// Seeding via the provisioning runner has no operator user ID. We use a
// well-known placeholder UUID that is stable and identifiable in audit logs.
const SYSTEM_USER_ID = '00000000-0000-0000-0000-000000000000'

// ─── Main entry points ────────────────────────────────────────────────────────

/**
 * Seed a single demo pack into a tenant database.
 * @param client  Supabase admin client for the target tenant database.
 * @param packCode  The pack to seed.
 * @param userId  The user to associate with created records (use SYSTEM_USER_ID for automated runs).
 */
export async function seedPack(
  client: SupabaseClient,
  packCode: DemoPackCode,
  userId: string = SYSTEM_USER_ID,
): Promise<SeedPackResult> {
  const errors: string[] = []
  let recordsAffected = 0

  switch (packCode) {
    case 'projektanlage':
      recordsAffected += await seedProjektanlage(client, userId, errors)
      break
    case 'kalender':
      recordsAffected += await seedKalender(client, userId, errors)
      break
    case 'lsc_workshop':
      recordsAffected += await seedLscWorkshop(client, errors)
      break
    case 'fabrikanalyse':
      recordsAffected += await seedFabrikanalyse(client, userId, errors)
      break
    case 'oee':
      recordsAffected += await seedOee(client, errors)
      break
    case 'wertstrom':
      recordsAffected += await seedWertstrom(client, userId, errors)
      break
    case 'datenablage':
      recordsAffected += await seedDatenablage(client, userId, errors)
      break
    case 'auftragseingang':
      recordsAffected += await seedAuftragseingang(client, userId, errors)
      break
    case 'top_management_demo':
      recordsAffected += await seedTopManagementDemo(client, userId, errors)
      break
    case 'berater':
      // Berater seeding requires cp_berater_profiles — handled separately
      // by the provisioning step after consulting the control plane.
      // For direct calls, return success with 0 records.
      break
    default: {
      const _exhaustive: never = packCode
      errors.push(`Unknown pack code: ${String(_exhaustive)}`)
    }
  }

  return { packCode, success: errors.length === 0, recordsAffected, errors }
}

/**
 * Remove all demo data for a single pack from a tenant database.
 */
export async function removePack(
  client: SupabaseClient,
  packCode: DemoPackCode,
): Promise<SeedPackResult> {
  const errors: string[] = []
  let recordsAffected = 0

  switch (packCode) {
    case 'projektanlage':
      recordsAffected += await removeProjektanlage(client, errors)
      break
    case 'kalender':
      recordsAffected += await removeKalender(client, errors)
      break
    case 'lsc_workshop':
      recordsAffected += await removeLscWorkshop(client, errors)
      break
    case 'fabrikanalyse':
      recordsAffected += await removeFabrikanalyse(client, errors)
      break
    case 'oee':
      recordsAffected += await removeOee(client, errors)
      break
    case 'wertstrom':
      recordsAffected += await removeWertstrom(client, errors)
      break
    case 'datenablage':
      recordsAffected += await removeDatenablage(client, errors)
      break
    case 'auftragseingang':
      recordsAffected += await removeAuftragseingang(client, errors)
      break
    case 'top_management_demo':
      recordsAffected += await removeTopManagementDemo(client, errors)
      break
    case 'berater':
      // Future: delete demo consultants
      break
    default: {
      const _exhaustive: never = packCode
      errors.push(`Unknown pack code: ${String(_exhaustive)}`)
    }
  }

  return { packCode, success: errors.length === 0, recordsAffected, errors }
}

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

async function seedProjektanlage(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  let count = 0

  const [
    { data: statuses },
    { data: responsibilities },
    { data: suppliers },
  ] = await Promise.all([
    client.from('project_statuses').select('id, code'),
    client.from('project_responsibilities').select('id, code'),
    client.from('supplier_master_data').select('id, supplier_name'),
  ])

  const statusMap = Object.fromEntries((statuses ?? []).map((s: { id: string; code: string }) => [s.code, s.id]))
  const respMap = Object.fromEntries((responsibilities ?? []).map((r: { id: string; code: string }) => [r.code, r.id]))

  const rows = DEMO_PROJECTS.map((p) => {
    const supplierId = (suppliers ?? []).find((s: { id: string; supplier_name: string }) =>
      s.supplier_name.toLowerCase().includes(p.supplier_lookup_name.toLowerCase())
    )?.id ?? null

    return {
      id:                p.id,
      project_code:      p.project_code,
      visit_date:        p.visit_date,
      expected_end_date: p.expected_end_date,
      vehicle_project:   p.vehicle_project,
      supplier_name:     p.supplier_name,
      is_demo:           true,
      // project_type_id omitted in Phase 2b3-iii — Junction (project_type_assignments) is source of truth.
      status_id:         statusMap[p.project_status_code] ?? null,
      responsibility_id: respMap[p.project_responsibility_code] ?? null,
      supplier_id:       supplierId,
      user_id:           userId,
      created_at:        new Date().toISOString(),
    }
  })

  const { error } = await client.from('projects').upsert(rows, { onConflict: 'id', ignoreDuplicates: false })
  if (error) { errors.push(`projektanlage.projects: ${error.message}`); return count }
  count += rows.length

  // Junction-Table: project_type_assignments — UI-Listen mit !inner Join (z.B. /lsc-workshop)
  // brauchen Rows hier, sonst sind Demo-Projekte unsichtbar.
  const assignmentRows = DEMO_PROJECTS.map((p) => ({
    project_id:        p.id,
    project_type_code: p.project_type_code,
  }))

  // Multi-Type Demo: DEMO-2026-004 (Mahle) zusätzlich als fabrikanalyse —
  // demonstriert M:N-Junction-Modell (max. 3 pro Projekt).
  const mahle = DEMO_PROJECTS.find((p) => p.project_code === 'DEMO-2026-004')
  if (mahle) {
    assignmentRows.push({
      project_id:        mahle.id,
      project_type_code: 'fabrikanalyse',
    })
  }

  // LSC Workshop demo data is seeded for all 5 demo projects (see lsc-workshop-seed),
  // so every demo project also carries an lsc_workshop type → appears in /lsc-workshop.
  for (const projectId of [DEMO_PROJECT_IDS[1], DEMO_PROJECT_IDS[4]]) {
    assignmentRows.push({ project_id: projectId, project_type_code: 'lsc_workshop' })
  }

  const { error: assignmentErr } = await client
    .from('project_type_assignments')
    .upsert(assignmentRows, { onConflict: 'project_id,project_type_code', ignoreDuplicates: false })
  if (assignmentErr) { errors.push(`projektanlage.project_type_assignments: ${assignmentErr.message}`); return count }
  count += assignmentRows.length

  // Documents
  count += await seedDatenablage(client, userId, errors)

  // Audit log (delete first to avoid duplicates)
  await client.from('user_audit_log').delete().eq('is_demo', true)
  const { error: auditErr } = await client.from('user_audit_log').insert(DEMO_AUDIT_ENTRIES)
  if (auditErr) errors.push(`user_audit_log: ${auditErr.message}`)
  else count += DEMO_AUDIT_ENTRIES.length

  // Email queue
  const { error: emailErr } = await client
    .from('email_queue')
    .upsert(DEMO_EMAIL_ENTRIES, { onConflict: 'id', ignoreDuplicates: false })
  if (emailErr) errors.push(`email_queue: ${emailErr.message}`)
  else count += DEMO_EMAIL_ENTRIES.length

  return count
}

async function removeProjektanlage(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0
  const projectIds = [...DEMO_PROJECT_IDS]

  await client.from('documents').delete().eq('is_demo', true)
  await client.from('user_audit_log').delete().eq('is_demo', true)
  await client.from('email_queue').delete().eq('is_demo', true)
  await client.from('project_consultants').delete().in('project_id', projectIds)

  // Scoped to the shared dddd pool: other pack families (ddd7 top_management_demo)
  // also tag is_demo=true and must only be removed via their own removePack —
  // a blanket is_demo delete would cascade their whole pack away.
  const { error, count: deleted } = await client
    .from('projects')
    .delete()
    .eq('is_demo', true)
    .in('id', projectIds)
  if (error) errors.push(`projects: ${error.message}`)
  else count += deleted ?? 0

  return count
}

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

async function seedKalender(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  let count = 0

  const [
    { data: consultants },
    { data: appointmentTypes },
  ] = await Promise.all([
    client.from('consultants').select('id').eq('is_active', true).order('last_name').limit(6),
    client.from('appointment_types').select('id, code').order('sort_order').limit(20),
  ])

  if (!consultants || consultants.length === 0) {
    errors.push('kalender: keine aktiven Berater gefunden')
    return count
  }
  if (!appointmentTypes || appointmentTypes.length === 0) {
    errors.push('kalender: keine Auftragsarten gefunden')
    return count
  }

  void userId

  // Upsert planning_projects first (assignments FK)
  const { error: ppErr } = await client
    .from('planning_projects')
    .upsert(DEMO_PLANNING_PROJECTS, { onConflict: 'id', ignoreDuplicates: false })
  if (ppErr) { errors.push(`planning_projects: ${ppErr.message}`); return count }
  count += DEMO_PLANNING_PROJECTS.length

  // Map Einsatzart code → appointment_type_id; fall back to the first type so
  // the NOT NULL FK is always satisfied even if a code is missing in this tenant.
  const typeByCode = new Map<string, string>(
    (appointmentTypes as Array<{ id: string; code: string }>).map((t) => [t.code, t.id]),
  )
  const fallbackTypeId = (appointmentTypes as Array<{ id: string }>)[0].id

  const rows = DEMO_ASSIGNMENTS
    .filter((a) => a.consultant_lookup_index < consultants.length)
    .map((a) => ({
      id:                  a.id,
      date:                a.date,
      project_id:          a.project_id,
      consultant_id:       (consultants as Array<{ id: string }>)[a.consultant_lookup_index].id,
      appointment_type_id: typeByCode.get(a.appointment_code) ?? fallbackTypeId,
      description:         null,
      work_mode:           'onsite' as const,
      status:              'fixed' as const,
      is_all_day:          true,
      requires_travel:     a.appointment_code === 'RE',
      source:              'manual',
      is_demo:             true,
      created_at:          new Date().toISOString(),
      updated_at:          new Date().toISOString(),
    }))

  const { error } = await client.from('assignments').upsert(rows, { onConflict: 'id', ignoreDuplicates: false })
  if (error) errors.push(`kalender.assignments: ${error.message}`)
  else count += rows.length

  return count
}

async function removeKalender(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0

  const { error, count: deleted } = await client.from('assignments').delete().eq('is_demo', true)
  if (error) errors.push(`assignments: ${error.message}`)
  else count += deleted ?? 0

  const { error: ppErr } = await client
    .from('planning_projects')
    .delete()
    .in('id', [...DEMO_PLANNING_PROJECT_IDS])
  if (ppErr) errors.push(`planning_projects: ${ppErr.message}`)

  return count
}

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

async function seedLscWorkshop(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0

  const { error: stepsErr } = await client
    .from('process_steps')
    .upsert(DEMO_PROCESS_STEPS, { onConflict: 'id', ignoreDuplicates: false })
  if (stepsErr) { errors.push(`process_steps: ${stepsErr.message}`); return count }
  count += DEMO_PROCESS_STEPS.length

  const BATCH = 30
  for (let i = 0; i < DEMO_MEASUREMENTS.length; i += BATCH) {
    const { error } = await client
      .from('cycle_measurements')
      .upsert(DEMO_MEASUREMENTS.slice(i, i + BATCH), { onConflict: 'id', ignoreDuplicates: false })
    if (error) { errors.push(`cycle_measurements[${i}]: ${error.message}`); break }
    count += Math.min(BATCH, DEMO_MEASUREMENTS.length - i)
  }

  const { error: shiftsErr } = await client
    .from('lsc_shifts')
    .upsert(DEMO_LSC_SHIFTS, { onConflict: 'id', ignoreDuplicates: false })
  if (shiftsErr) errors.push(`lsc_shifts: ${shiftsErr.message}`)
  else count += DEMO_LSC_SHIFTS.length

  const { error: measErr } = await client
    .from('lsc_measures')
    .upsert(DEMO_LSC_MEASURES, { onConflict: 'id', ignoreDuplicates: false })
  if (measErr) errors.push(`lsc_measures: ${measErr.message}`)
  else count += DEMO_LSC_MEASURES.length

  const { error: kapaErr } = await client
    .from('kapa_workshop_data')
    .upsert(DEMO_KAPA_DATA, { onConflict: 'project_id', ignoreDuplicates: false })
  if (kapaErr) errors.push(`kapa_workshop_data: ${kapaErr.message}`)
  else count += DEMO_KAPA_DATA.length

  return count
}

async function removeLscWorkshop(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0
  const projectIds = [...DEMO_PROJECT_IDS]

  await client.from('lsc_shifts').delete().in('project_id', projectIds)
  await client.from('lsc_measures').delete().in('project_id', projectIds)
  await client.from('kapa_workshop_data').delete().in('project_id', projectIds)

  const { error: cmErr } = await client
    .from('cycle_measurements')
    .delete()
    .in('process_step_id', DEMO_ALL_PROCESS_STEP_IDS)
  if (cmErr) errors.push(`cycle_measurements: ${cmErr.message}`)

  // Scoped to the shared dddd pool projects — never touch other pack families
  // (ddd7 top_management_demo steps are removed only by removeTopManagementDemo).
  const { error, count: deleted } = await client
    .from('process_steps')
    .delete()
    .eq('is_demo', true)
    .in('project_id', projectIds)
  if (error) errors.push(`process_steps: ${error.message}`)
  else count += deleted ?? 0

  return count
}

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

async function seedFabrikanalyse(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  let count = 0

  const assessmentRows = DEMO_ASSESSMENTS.map((a) => ({
    id:                a.id,
    title:             a.title,
    supplier_name:     a.supplier_name,
    location:          a.location,
    assessment_number: a.assessment_number,
    conducted_at:      a.conducted_at,
    project_id:        a.project_id,
    status:            a.status,
    is_demo:           true,
    created_by:        userId,
    created_at:        new Date().toISOString(),
    updated_at:        new Date().toISOString(),
  }))

  const { error: aErr } = await client
    .from('assessments')
    .upsert(assessmentRows, { onConflict: 'id', ignoreDuplicates: false })

  if (aErr) { errors.push(`assessments: ${aErr.message}`); return count }
  count += assessmentRows.length

  const { data: questions, error: qErr } = await client
    .from('assessment_questions')
    .select('id, index_number, sort_order')
    .eq('is_active', true)
    .order('sort_order')

  if (qErr || !questions) {
    errors.push(`assessment_questions fetch: ${qErr?.message ?? 'no data'}`)
    return count
  }

  // One batched delete for every demo assessment, then one batched insert —
  // keeps the operation idempotent without duplicate response rows.
  await client
    .from('assessment_responses')
    .delete()
    .in('assessment_id', DEMO_ASSESSMENTS.map((a) => a.id))

  const responses = DEMO_ASSESSMENTS.flatMap((a) =>
    buildAssessmentResponses(a.id, questions).map((r) => ({
      assessment_id:   r.assessment_id,
      question_id:     r.question_id,
      is_relevant:     r.is_relevant,
      is_flagged:      r.is_flagged,
      selected_rating: r.is_relevant ? r.selected_rating : null,
      comment:         r.comment,
      updated_at:      new Date().toISOString(),
    })),
  )

  const { error: rErr } = await client.from('assessment_responses').insert(responses)
  if (rErr) errors.push(`assessment_responses: ${rErr.message}`)
  else count += responses.length

  return count
}

async function removeFabrikanalyse(client: SupabaseClient, errors: string[]): Promise<number> {
  await client.from('assessment_responses').delete().in('assessment_id', [...DEMO_ASSESSMENT_IDS])

  // Scoped to the legacy stable ids — the ddd7 top_management_demo assessment
  // is removed only by removeTopManagementDemo.
  const { error, count: deleted } = await client
    .from('assessments')
    .delete()
    .eq('is_demo', true)
    .in('id', [...DEMO_ASSESSMENT_IDS])
  if (error) errors.push(`assessments: ${error.message}`)
  return deleted ?? 0
}

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

async function seedOee(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0

  const { error: oeeErr } = await client
    .from('oee_records')
    .upsert(DEMO_OEE_RECORDS, { onConflict: 'id', ignoreDuplicates: false })
  if (oeeErr) { errors.push(`oee_records: ${oeeErr.message}`); return count }
  count += DEMO_OEE_RECORDS.length

  await client.from('oee_loss_categories').delete().eq('is_demo', true)
  const { error: lcErr } = await client.from('oee_loss_categories').insert(DEMO_OEE_LOSS_CATEGORIES)
  if (lcErr) errors.push(`oee_loss_categories: ${lcErr.message}`)
  else count += DEMO_OEE_LOSS_CATEGORIES.length

  return count
}

async function removeOee(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0
  await client.from('oee_loss_categories').delete().eq('is_demo', true)
  const { error, count: deleted } = await client.from('oee_records').delete().eq('is_demo', true)
  if (error) errors.push(`oee_records: ${error.message}`)
  else count += deleted ?? 0
  return count
}

// ─── Pack: wertstrom ──────────────────────────────────────────────────────────
// Includes the standalone Referenz-Beispielwertstrom "Elektronisches
// Steuergerät" (Kais-Auftrag, KAR-878, DEMO_VSM_REFERENCE_ECU) — already part
// of DEMO_VSMS itself (wertstrom-seed.ts), so no separate wiring is needed
// here.

async function seedWertstrom(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  const rows = DEMO_VSMS.map((vsm) => ({
    id:          vsm.id,
    project_id:  vsm.project_id,
    title:       vsm.title,
    description: vsm.description,
    nodes:       vsm.nodes,
    connections: vsm.connections,
    layout:      vsm.layout,
    is_demo:     true,
    created_by:  userId,
    created_at:  new Date().toISOString(),
    updated_at:  new Date().toISOString(),
  }))
  const { error } = await client
    .from('value_stream_maps')
    .upsert(rows, { onConflict: 'id', ignoreDuplicates: false })
  if (error) { errors.push(`value_stream_maps: ${error.message}`); return 0 }
  return rows.length
}

async function removeWertstrom(client: SupabaseClient, errors: string[]): Promise<number> {
  // Scoped to this pack's own seeded VSM ids — the ddd7 top_management_demo
  // Wertstrom is removed only by removeTopManagementDemo.
  const { error, count: deleted } = await client
    .from('value_stream_maps')
    .delete()
    .eq('is_demo', true)
    .in('id', DEMO_VSMS.map((vsm) => vsm.id))
  if (error) errors.push(`value_stream_maps: ${error.message}`)
  return deleted ?? 0
}

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

async function seedDatenablage(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  // Guard: requires projects to exist
  const { count: demoProjectCount } = await client
    .from('projects')
    .select('id', { count: 'exact', head: true })
    .eq('is_demo', true)

  if (!demoProjectCount || demoProjectCount === 0) {
    errors.push('datenablage: Projektanlage-Demo muss zuerst geladen werden')
    return 0
  }

  const { error: docErr } = await client
    .from('documents')
    .upsert(
      DEMO_DOCUMENTS.map((d) => ({
        ...d,
        uploaded_by: userId,
        created_at:  new Date(Date.now() - Math.floor(Math.random() * 14) * 86400 * 1000).toISOString(),
        updated_at:  new Date().toISOString(),
        checksum:    null,
      })),
      { onConflict: 'id', ignoreDuplicates: false },
    )
  if (docErr) { errors.push(`documents: ${docErr.message}`); return 0 }
  return DEMO_DOCUMENTS.length
}

async function removeDatenablage(client: SupabaseClient, errors: string[]): Promise<number> {
  const { error, count: deleted } = await client.from('documents').delete().eq('is_demo', true)
  if (error) errors.push(`documents: ${error.message}`)
  return deleted ?? 0
}

// ─── Pack: auftragseingang ────────────────────────────────────────────────────
//
// intakes has no is_demo column → idempotency/removal keys on the stable
// DEMO_INTAKE_IDS. Reviewer + creator are resolved here so the seed data stays
// portable across tenant databases. Status_history is left untouched (a demo
// intake starts cleanly in its backlog/review lane).

async function seedAuftragseingang(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  // Reviewers for the "review" lane — any active consultants, round-robin.
  const { data: consultants } = await client
    .from('consultants')
    .select('id')
    .eq('is_active', true)
    .order('last_name')
    .limit(10)
  const reviewerIds = (consultants ?? []).map((c: { id: string }) => c.id)

  let reviewerCursor = 0
  const rows = DEMO_INTAKES.map((i) => {
    let reviewingConsultantId: string | null = null
    if (i.wants_reviewer && reviewerIds.length > 0) {
      reviewingConsultantId = reviewerIds[reviewerCursor % reviewerIds.length]
      reviewerCursor++
    }
    return {
      id:                        i.id,
      intake_id:                 i.intake_id,
      status:                    i.status,
      intake_type:               i.intake_type,
      requesting_department:     i.requesting_department,
      priority:                  i.priority,
      project_lead:              i.project_lead,
      delivery_scope:            i.delivery_scope,
      part_designation:          i.part_designation,
      vehicle_project:           i.vehicle_project,
      product_line:              i.product_line,
      supplier_name_freetext:    i.supplier_name_freetext,
      supplier_plant_location:   i.supplier_plant_location,
      order_reason:              i.order_reason,
      order_objective:           i.order_objective,
      preferred_first_visit_start: i.preferred_first_visit_start,
      preferred_first_visit_end:   i.preferred_first_visit_end,
      reviewing_consultant_id:   reviewingConsultantId,
      created_by_auth_user_id:   userId,
      created_by_fachbereich_id: null,
      created_at:                new Date().toISOString(),
      updated_at:                new Date().toISOString(),
    }
  })

  const { error } = await client.from('intakes').upsert(rows, { onConflict: 'id', ignoreDuplicates: false })
  if (error) { errors.push(`auftragseingang.intakes: ${error.message}`); return 0 }
  return rows.length
}

async function removeAuftragseingang(client: SupabaseClient, errors: string[]): Promise<number> {
  // intake_status_history / intake_field_values FK ON DELETE CASCADE.
  const { error, count: deleted } = await client
    .from('intakes')
    .delete({ count: 'exact' })
    .in('id', [...DEMO_INTAKE_IDS])
  if (error) errors.push(`auftragseingang.intakes: ${error.message}`)
  return deleted ?? 0
}

// ─── Pack: top_management_demo (KAR-981) ───────────────────────────────────────
//
// ONE cohesive demo project (2026_067, "Demo Electronics Systems GmbH")
// spanning Projektanlage, Agenda/LSC-Workshop, Fabrikanalyse, QAF-Vergleich
// and Wertstrom — unlike every other pack above, it does not share the
// `DEMO_PROJECT_IDS` pool. See lib/demo/demo-ids.ts (`DEMO_TOP_MGMT_*`, prefix
// `ddd7...`) and lib/demo/seeds/top-management-demo-*.ts for the content.

async function seedTopManagementDemo(
  client: SupabaseClient,
  userId: string,
  errors: string[],
): Promise<number> {
  let count = 0

  // ── Schutz-Check: never overwrite a REAL project_code 2026_067 ───────────
  const { data: existingByCode, error: codeErr } = await client
    .from('projects')
    .select('id')
    .eq('project_code', DEMO_TOP_MGMT_PROJECT_CODE)
    .maybeSingle()
  if (codeErr) { errors.push(`top_management_demo Schutz-Check: ${codeErr.message}`); return count }
  if (existingByCode && existingByCode.id !== DEMO_TOP_MGMT_PROJECT_ID) {
    errors.push(
      `top_management_demo: Projekt mit project_code ${DEMO_TOP_MGMT_PROJECT_CODE} existiert bereits (id ${existingByCode.id as string}, nicht unsere Demo-ID) — Seed abgebrochen, um kein echtes Projekt zu überschreiben.`,
    )
    return count
  }

  // ── Supplier: lookup-or-create by exact name, collision-check by number ──
  const { data: existingSupplier, error: supLookupErr } = await client
    .from('supplier_master_data')
    .select('id')
    .eq('supplier_name', DEMO_TOP_MGMT_SUPPLIER.supplier_name)
    .maybeSingle()
  if (supLookupErr) { errors.push(`top_management_demo.supplier lookup: ${supLookupErr.message}`); return count }

  let supplierId = existingSupplier?.id as string | undefined
  if (!supplierId) {
    const { data: numberCollision, error: numErr } = await client
      .from('supplier_master_data')
      .select('id')
      .eq('supplier_number', DEMO_TOP_MGMT_SUPPLIER.supplier_number)
      .maybeSingle()
    if (numErr) { errors.push(`top_management_demo.supplier collision-check: ${numErr.message}`); return count }
    if (numberCollision) {
      errors.push(`top_management_demo: supplier_number ${DEMO_TOP_MGMT_SUPPLIER.supplier_number} ist bereits vergeben — Seed abgebrochen.`)
      return count
    }
    const { data: insertedSupplier, error: supInsErr } = await client
      .from('supplier_master_data')
      .insert({
        id: DEMO_TOP_MGMT_SUPPLIER.id,
        supplier_number: DEMO_TOP_MGMT_SUPPLIER.supplier_number,
        supplier_name: DEMO_TOP_MGMT_SUPPLIER.supplier_name,
        supplier_location: DEMO_TOP_MGMT_SUPPLIER.supplier_location,
        plant: DEMO_TOP_MGMT_SUPPLIER.plant,
        country: DEMO_TOP_MGMT_SUPPLIER.country,
        city: DEMO_TOP_MGMT_SUPPLIER.city,
        is_active: true,
      })
      .select('id')
      .single()
    if (supInsErr || !insertedSupplier) {
      errors.push(`top_management_demo.supplier insert: ${supInsErr?.message ?? 'no row'}`)
      return count
    }
    supplierId = insertedSupplier.id as string
  }
  count += 1

  // ── Projekt ────────────────────────────────────────────────────────────────
  const [{ data: statuses }, { data: responsibilities }] = await Promise.all([
    client.from('project_statuses').select('id, code'),
    client.from('project_responsibilities').select('id, code'),
  ])
  const statusId =
    (statuses ?? []).find((s: { id: string; code: string }) => s.code === DEMO_TOP_MGMT_PROJECT.project_status_code)
      ?.id ?? null
  const responsibilityId =
    (responsibilities ?? []).find(
      (r: { id: string; code: string }) => r.code === DEMO_TOP_MGMT_PROJECT.project_responsibility_code,
    )?.id ?? null

  const { project_status_code: _statusCode, project_responsibility_code: _respCode, ...projectFields } =
    DEMO_TOP_MGMT_PROJECT
  void _statusCode
  void _respCode

  const { error: projErr } = await client.from('projects').upsert(
    {
      ...projectFields,
      supplier_id: supplierId,
      status_id: statusId,
      responsibility_id: responsibilityId,
      user_id: userId,
      created_at: new Date().toISOString(),
      updated_at: new Date().toISOString(),
    },
    { onConflict: 'id', ignoreDuplicates: false },
  )
  if (projErr) { errors.push(`top_management_demo.projects: ${projErr.message}`); return count }
  count += 1

  const assignmentRows = DEMO_TOP_MGMT_PROJECT_TYPE_CODES.map((code) => ({
    project_id: DEMO_TOP_MGMT_PROJECT_ID,
    project_type_code: code,
  }))
  const { error: assignErr } = await client
    .from('project_type_assignments')
    .upsert(assignmentRows, { onConflict: 'project_id,project_type_code', ignoreDuplicates: false })
  if (assignErr) { errors.push(`top_management_demo.project_type_assignments: ${assignErr.message}`); return count }
  count += assignmentRows.length

  // ── Consultants ────────────────────────────────────────────────────────────
  const normalizeName = (s: string) => s.trim().toLowerCase().replace(/\s+/g, ' ')
  const { data: allConsultants, error: consErr } = await client
    .from('consultants')
    .select('id, first_name, last_name, display_name, team_code')
  if (consErr) {
    errors.push(`top_management_demo.consultants lookup: ${consErr.message}`)
  } else {
    type ConsultantRow = { id: string; first_name: string; last_name: string; display_name: string; team_code: string }
    const consultants = (allConsultants ?? []) as ConsultantRow[]
    const findByName = (name: string): ConsultantRow | undefined =>
      consultants.find(
        (c) =>
          normalizeName(`${c.first_name} ${c.last_name}`) === normalizeName(name) ||
          normalizeName(c.display_name) === normalizeName(name),
      )

    const kais = findByName(DEMO_TOP_MGMT_CONSULTANTS.lead.name)
    if (!kais) {
      errors.push(
        `top_management_demo: Consultant "${DEMO_TOP_MGMT_CONSULTANTS.lead.name}" nicht im Roster gefunden — project_consultants (lead) übersprungen.`,
      )
    }

    let storch = findByName(DEMO_TOP_MGMT_CONSULTANTS.collaborator.name)
    if (!storch) {
      // Demo-tagged with a stable ddd7 id: the row must be recognizable as
      // seed-created (is_demo) without being swept by the legacy demo-cleanup
      // routes' consultants sweeps — those exclude this id explicitly (see
      // app/api/admin/demo/route.ts). removeTopManagementDemo keeps it too
      // (master data). If a real user account for this consultant arrives
      // later, flip is_demo=false and set auth_user_id.
      const { data: insertedStorch, error: storchErr } = await client
        .from('consultants')
        .insert({
          id: DEMO_TOP_MGMT_CONSULTANT_ID,
          first_name: DEMO_TOP_MGMT_CONSULTANTS.collaborator.firstName,
          last_name: DEMO_TOP_MGMT_CONSULTANTS.collaborator.lastName,
          display_name: DEMO_TOP_MGMT_CONSULTANTS.collaborator.name,
          team_code: kais?.team_code ?? 'MO-2',
          is_active: true,
          auth_user_id: null,
          is_demo: true,
        })
        .select('id, first_name, last_name, display_name, team_code')
        .single()
      if (storchErr || !insertedStorch) {
        errors.push(
          `top_management_demo: Anlegen von "${DEMO_TOP_MGMT_CONSULTANTS.collaborator.name}" fehlgeschlagen: ${storchErr?.message ?? 'no row'}`,
        )
      } else {
        storch = insertedStorch as ConsultantRow
      }
    }

    await client.from('project_consultants').delete().eq('project_id', DEMO_TOP_MGMT_PROJECT_ID)
    const consultantRows: Array<{
      project_id: string
      consultant_id: string
      role_in_project: 'lead' | 'collaborator'
      assigned_at: string
      is_demo: boolean
    }> = []
    if (kais) {
      consultantRows.push({
        project_id: DEMO_TOP_MGMT_PROJECT_ID,
        consultant_id: kais.id,
        role_in_project: 'lead',
        assigned_at: DEMO_TOP_MGMT_CONSULTANTS.assignedAt,
        is_demo: true,
      })
    }
    if (storch) {
      consultantRows.push({
        project_id: DEMO_TOP_MGMT_PROJECT_ID,
        consultant_id: storch.id,
        role_in_project: 'collaborator',
        assigned_at: DEMO_TOP_MGMT_CONSULTANTS.assignedAt,
        is_demo: true,
      })
    }
    if (consultantRows.length) {
      const { error: pcErr } = await client.from('project_consultants').insert(consultantRows)
      if (pcErr) errors.push(`top_management_demo.project_consultants: ${pcErr.message}`)
      else count += consultantRows.length
    }
  }

  // ── Agenda ─────────────────────────────────────────────────────────────────
  const { error: agendaErr } = await client
    .from('agenda')
    .upsert({ ...DEMO_TOP_MGMT_AGENDA, updated_at: new Date().toISOString() }, { onConflict: 'id', ignoreDuplicates: false })
  if (agendaErr) { errors.push(`top_management_demo.agenda: ${agendaErr.message}`); return count }
  count += 1

  const { error: daysErr } = await client
    .from('agenda_day')
    .upsert(DEMO_TOP_MGMT_AGENDA_DAYS, { onConflict: 'id', ignoreDuplicates: false })
  if (daysErr) errors.push(`top_management_demo.agenda_day: ${daysErr.message}`)
  else count += DEMO_TOP_MGMT_AGENDA_DAYS.length

  const itemRows = DEMO_TOP_MGMT_AGENDA_ITEMS.map((item) => ({
    id: item.id,
    agenda_day_id: DEMO_TOP_MGMT_AGENDA_DAY_IDS[item.dayIndex],
    sort_order: item.sort_order,
    start_time: item.start_time,
    end_time: item.end_time,
    duration_minutes: item.duration_minutes,
    title: item.title,
    description: item.description,
    responsible: item.responsible,
    location: item.location,
    item_type: item.item_type,
  }))
  const { error: itemsErr } = await client
    .from('agenda_item')
    .upsert(itemRows, { onConflict: 'id', ignoreDuplicates: false })
  if (itemsErr) errors.push(`top_management_demo.agenda_item: ${itemsErr.message}`)
  else count += itemRows.length

  const participantRows = DEMO_TOP_MGMT_AGENDA_PARTICIPANTS.map((p) => ({
    id: p.id,
    agenda_id: DEMO_TOP_MGMT_AGENDA_ID,
    name: p.name,
    company: p.company,
    position: p.position,
    participant_type: p.participant_type,
  }))
  const { error: participantsErr } = await client
    .from('agenda_participant')
    .upsert(participantRows, { onConflict: 'id', ignoreDuplicates: false })
  if (participantsErr) errors.push(`top_management_demo.agenda_participant: ${participantsErr.message}`)
  else count += participantRows.length

  // ── LSC ────────────────────────────────────────────────────────────────────
  const { error: stepsErr } = await client
    .from('process_steps')
    .upsert(DEMO_TOP_MGMT_PROCESS_STEPS, { onConflict: 'id', ignoreDuplicates: false })
  if (stepsErr) { errors.push(`top_management_demo.process_steps: ${stepsErr.message}`); return count }
  count += DEMO_TOP_MGMT_PROCESS_STEPS.length

  const MEASUREMENT_BATCH = 30
  for (let i = 0; i < DEMO_TOP_MGMT_CYCLE_MEASUREMENTS.length; i += MEASUREMENT_BATCH) {
    const { error } = await client
      .from('cycle_measurements')
      .upsert(DEMO_TOP_MGMT_CYCLE_MEASUREMENTS.slice(i, i + MEASUREMENT_BATCH), {
        onConflict: 'id',
        ignoreDuplicates: false,
      })
    if (error) { errors.push(`top_management_demo.cycle_measurements[${i}]: ${error.message}`); break }
    count += Math.min(MEASUREMENT_BATCH, DEMO_TOP_MGMT_CYCLE_MEASUREMENTS.length - i)
  }

  const { error: obsErr } = await client
    .from('process_step_observations')
    .upsert(
      DEMO_TOP_MGMT_PROCESS_STEP_OBSERVATIONS.map((o) => ({
        ...o,
        created_by: userId,
        updated_at: new Date().toISOString(),
      })),
      { onConflict: 'process_step_id', ignoreDuplicates: false },
    )
  if (obsErr) errors.push(`top_management_demo.process_step_observations: ${obsErr.message}`)
  else count += DEMO_TOP_MGMT_PROCESS_STEP_OBSERVATIONS.length

  const { error: shiftsErr } = await client
    .from('lsc_shifts')
    .upsert(DEMO_TOP_MGMT_LSC_SHIFTS, { onConflict: 'id', ignoreDuplicates: false })
  if (shiftsErr) errors.push(`top_management_demo.lsc_shifts: ${shiftsErr.message}`)
  else count += DEMO_TOP_MGMT_LSC_SHIFTS.length

  const { error: kapaErr } = await client
    .from('kapa_workshop_data')
    .upsert(DEMO_TOP_MGMT_KAPA_DATA, { onConflict: 'project_id', ignoreDuplicates: false })
  if (kapaErr) errors.push(`top_management_demo.kapa_workshop_data: ${kapaErr.message}`)
  else count += 1

  const { error: measuresErr } = await client
    .from('lsc_measures')
    .upsert(DEMO_TOP_MGMT_LSC_MEASURES, { onConflict: 'id', ignoreDuplicates: false })
  if (measuresErr) errors.push(`top_management_demo.lsc_measures: ${measuresErr.message}`)
  else count += DEMO_TOP_MGMT_LSC_MEASURES.length

  const { error: actionsErr } = await client
    .from('workshop_actions')
    .upsert(DEMO_TOP_MGMT_WORKSHOP_ACTIONS, { onConflict: 'id', ignoreDuplicates: false })
  if (actionsErr) errors.push(`top_management_demo.workshop_actions: ${actionsErr.message}`)
  else count += DEMO_TOP_MGMT_WORKSHOP_ACTIONS.length

  // ── Fabrikanalyse ──────────────────────────────────────────────────────────
  const { error: assessmentErr } = await client
    .from('assessments')
    .upsert(
      { ...DEMO_TOP_MGMT_ASSESSMENT, created_by: userId, updated_at: new Date().toISOString() },
      { onConflict: 'id', ignoreDuplicates: false },
    )
  if (assessmentErr) {
    errors.push(`top_management_demo.assessments: ${assessmentErr.message}`)
  } else {
    count += 1

    const { data: questions, error: qErr } = await client
      .from('assessment_questions')
      .select('id, index_number')
      .eq('is_active', true)

    if (qErr || !questions) {
      errors.push(`top_management_demo.assessment_questions fetch: ${qErr?.message ?? 'no data'}`)
    } else {
      const byIndex = new Map(
        (questions as Array<{ id: string; index_number: number }>).map((q) => [q.index_number, q.id]),
      )
      const missing: number[] = []
      const responses: Array<{
        assessment_id: string
        question_id: string
        is_relevant: boolean
        is_flagged: boolean
        selected_rating: number | null
        comment: string
        updated_at: string
      }> = []
      for (const r of TOP_MANAGEMENT_DEMO_FA_RESPONSES) {
        const questionId = byIndex.get(r.index_number)
        if (!questionId) { missing.push(r.index_number); continue }
        responses.push({
          assessment_id: DEMO_TOP_MGMT_ASSESSMENT_ID,
          question_id: questionId,
          is_relevant: r.is_relevant,
          is_flagged: false,
          selected_rating: r.is_relevant ? r.rating : null,
          comment: r.comment,
          updated_at: new Date().toISOString(),
        })
      }
      if (missing.length) {
        errors.push(
          `top_management_demo.assessment_responses: keine assessment_questions.index_number für ${missing.join(', ')} gefunden — übersprungen, nicht still ignoriert.`,
        )
      }

      await client.from('assessment_responses').delete().eq('assessment_id', DEMO_TOP_MGMT_ASSESSMENT_ID)
      if (responses.length) {
        const { error: rErr } = await client.from('assessment_responses').insert(responses)
        if (rErr) errors.push(`top_management_demo.assessment_responses insert: ${rErr.message}`)
        else count += responses.length
      }
    }
  }

  // ── QAF (echte Ingest-/Compare-Kette, siehe top-management-demo-qaf.ts) ─────
  let qafCurrentFileId: string | null = null
  try {
    const qafResult = await seedTopManagementDemoQaf(client, userId)
    qafCurrentFileId = qafResult.currentFileId
    count += 3 // 2× qaf_file + 1× qaf_comparison
  } catch (err) {
    errors.push(`top_management_demo.qaf: ${err instanceof Error ? err.message : String(err)}`)
  }

  // ── Wertstrom (direkter Insert-Fallback, siehe top-management-demo-vsm.ts) ──
  if (qafCurrentFileId) {
    try {
      await seedTopManagementDemoVsm(client, userId, { qafCurrentFileId })
      count += 1
    } catch (err) {
      errors.push(`top_management_demo.wertstrom: ${err instanceof Error ? err.message : String(err)}`)
    }
  } else {
    errors.push('top_management_demo.wertstrom: übersprungen (kein QAF-Current-File — QAF-Seed ist vorher fehlgeschlagen).')
  }

  return count
}

async function removeTopManagementDemo(client: SupabaseClient, errors: string[]): Promise<number> {
  let count = 0
  const projectId = DEMO_TOP_MGMT_PROJECT_ID

  // Storage: qaf-uploads objects under this project's own prefix only.
  const { data: qafObjects } = await client.storage.from('qaf-uploads').list(projectId)
  if (qafObjects?.length) {
    await client.storage.from('qaf-uploads').remove(qafObjects.map((o) => `${projectId}/${o.name}`))
  }

  // QAF — every qaf_* table below is scoped by OUR project_id only, never a
  // blanket is_demo filter (qaf_* tables have no is_demo column anyway).
  await client.from('qaf_comparison').delete().eq('project_id', projectId)
  await client.from('qaf_file').delete().eq('project_id', projectId)
  await client.from('qaf_part').delete().eq('project_id', projectId)

  // Wertstrom
  await client.from('value_stream_imports').delete().eq('project_id', projectId)
  const { error: vsmErr, count: vsmDeleted } = await client
    .from('value_stream_maps')
    .delete({ count: 'exact' })
    .eq('id', DEMO_TOP_MGMT_VSM_ID)
  if (vsmErr) errors.push(`top_management_demo.value_stream_maps: ${vsmErr.message}`)
  else count += vsmDeleted ?? 0

  // Fabrikanalyse
  await client.from('assessment_responses').delete().eq('assessment_id', DEMO_TOP_MGMT_ASSESSMENT_ID)
  const { error: assessErr, count: assessDeleted } = await client
    .from('assessments')
    .delete({ count: 'exact' })
    .eq('id', DEMO_TOP_MGMT_ASSESSMENT_ID)
  if (assessErr) errors.push(`top_management_demo.assessments: ${assessErr.message}`)
  else count += assessDeleted ?? 0

  // LSC — scoped by project_id where the column exists, by stable id lists
  // otherwise (workshop_actions/process_steps have no is_demo we could rely
  // on cross-pack-safely for the former, and using it for the latter would
  // hit other packs' is_demo=true rows too — task instruction: never a
  // blanket `.eq('is_demo', true)` here).
  await client.from('lsc_shifts').delete().eq('project_id', projectId)
  await client.from('lsc_measures').delete().eq('project_id', projectId)
  await client.from('kapa_workshop_data').delete().eq('project_id', projectId)
  await client.from('workshop_actions').delete().in('id', [...DEMO_TOP_MGMT_WORKSHOP_ACTION_IDS])
  await client.from('process_step_observations').delete().in('process_step_id', [...DEMO_TOP_MGMT_PROCESS_STEP_IDS])
  const { error: cmErr } = await client
    .from('cycle_measurements')
    .delete()
    .in('process_step_id', [...DEMO_TOP_MGMT_PROCESS_STEP_IDS])
  if (cmErr) errors.push(`top_management_demo.cycle_measurements: ${cmErr.message}`)
  const { error: stepsErr, count: stepsDeleted } = await client
    .from('process_steps')
    .delete({ count: 'exact' })
    .in('id', [...DEMO_TOP_MGMT_PROCESS_STEP_IDS])
  if (stepsErr) errors.push(`top_management_demo.process_steps: ${stepsErr.message}`)
  else count += stepsDeleted ?? 0

  // Agenda
  await client.from('agenda_participant').delete().eq('agenda_id', DEMO_TOP_MGMT_AGENDA_ID)
  await client.from('agenda_item').delete().in('agenda_day_id', [...DEMO_TOP_MGMT_AGENDA_DAY_IDS])
  await client.from('agenda_day').delete().eq('agenda_id', DEMO_TOP_MGMT_AGENDA_ID)
  const { error: agendaErr, count: agendaDeleted } = await client
    .from('agenda')
    .delete({ count: 'exact' })
    .eq('id', DEMO_TOP_MGMT_AGENDA_ID)
  if (agendaErr) errors.push(`top_management_demo.agenda: ${agendaErr.message}`)
  else count += agendaDeleted ?? 0

  // Consultants — only the junction row is pack-owned. The consultant roster
  // row (Storch, created if missing) and Kais' own row are shared Stammdaten
  // used across the whole app and are deliberately NOT deleted here.
  await client.from('project_consultants').delete().eq('project_id', projectId)

  // Project + junction. Supplier master data is likewise shared Stammdaten
  // (lookup-or-create) and is deliberately NOT deleted here.
  await client.from('project_type_assignments').delete().eq('project_id', projectId)
  const { error: projErr, count: projDeleted } = await client
    .from('projects')
    .delete({ count: 'exact' })
    .eq('id', projectId)
  if (projErr) errors.push(`top_management_demo.projects: ${projErr.message}`)
  else count += projDeleted ?? 0

  return count
}