server/levelRepository.ts

import { createHash, randomUUID } from 'node:crypto'
import { Readable } from 'node:stream'
import type { Pool, PoolClient } from 'pg'
import type { BoardView, BriefConcept, CaseDocument, CaseState, Evidence, Exhibit, ExhibitRelation, OrganizationKind, PartyKind, SourceFileType } from '../src/types.js'
import { isDocumentExhibit, isEventExhibit, isFolderExhibit, isPartyExhibit } from '../src/types.js'
import { clearBoard, cloneBoard } from './boardClone.js'
import type { ObjectStorage } from './objectStorage.js'

export type UploadedDocument = { buffer: Buffer; originalname: string; mimetype: string; size: number }
export type AssetRecord = { original_name: string; mime_type: string; byte_size: string; content: Buffer | null; storage_provider: 'postgres' | 's3'; object_key: string | null }
export type AssetResponse = { originalName: string; mimeType: string; byteSize: number; stream: NodeJS.ReadableStream }
export type TemplateSummary = { id: string; slug: string; name: string; currentVersion: number; versionCount: number; updatedAt: string }

export interface LevelRepository {
  listLevels(): Promise<unknown[]>
  createLevel(input: { id: string; title: string; subtitle: string }): Promise<CaseState>
  listTemplates(): Promise<TemplateSummary[]>
  instantiateTemplate(templateSlug: string, input: { id: string; title?: string; version?: number }): Promise<CaseState | null>
  saveLevelAsTemplate(levelId: string, input: { slug: string; name: string }): Promise<TemplateSummary | null>
  getLevel(levelId: string, authorMode?: boolean): Promise<CaseState | null>
  saveLevel(state: CaseState, authorMode: boolean): Promise<void>
  resetLevel(levelId: string): Promise<CaseState | null>
  getAsset(assetId: string): Promise<AssetResponse | null>
  uploadDocument(levelId: string, file: UploadedDocument): Promise<CaseDocument | null>
}

type LevelRow = {
  id: string; slug: string; board_id: string; title: string; subtitle: string; status: string
  viewport_x: number; viewport_y: number; viewport_zoom: number; updated_at: Date; revision: string
  source_template_version_id: string | null
}
type ExhibitRow = {
  id: string; exhibit_type_id: Exhibit['type']; xpos: number; ypos: number; width: number; height: number; rotation: number; z_index: number; hidden: boolean
  title: string; content: string; is_open: boolean | null; document_type_id: SourceFileType | null
  asset_id: string | null; published_at: Date | null; occurred_at: Date | null
  original_name: string | null; mime_type: string | null; byte_size: string | null
  source_document_id: string | null; source_region_key: string | null
  party_kind: PartyKind | null; organization_kind: OrganizationKind | null
}

const uuidPattern = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i
function requireUuid(value: string, label: string) {
  if (!uuidPattern.test(value)) throw new Error(`${label} must be a UUID`)
  return value
}
function timestamp(value: string | undefined) {
  if (!value) return null
  const date = new Date(value)
  return Number.isFinite(date.getTime()) ? date.toISOString() : null
}
function documentType(document: CaseDocument): SourceFileType {
  const allowed: SourceFileType[] = ['image', 'pdf', 'web_capture', 'email', 'article', 'filing', 'price_list', 'text', 'file']
  return allowed.includes(document.fileType) ? document.fileType : 'file'
}
export function createLevelRepository(pool: Pool, editingEnabled: boolean, objectStorage: ObjectStorage): LevelRepository {
  async function findLevel(client: Pool | PoolClient, slug: string, lock = false) {
    const result = await client.query<LevelRow>(`SELECT l.id,l.slug,l.board_id,l.title,l.subtitle,l.status,
      l.viewport_x,l.viewport_y,l.viewport_zoom,l.updated_at,l.source_template_version_id,b.revision::text
      FROM osint.levels l JOIN osint.boards b ON b.id=l.board_id WHERE l.slug = $1${lock ? ' FOR UPDATE OF l,b' : ''}`, [slug])
    return result.rows[0] || null
  }

  async function createDefaultBoardViews(client: PoolClient, boardId: string) {
    const viewId = randomUUID()
    await client.query(`INSERT INTO osint.board_views (id,board_id,view_type_id,placement_mode,dock_edge,height)
      VALUES ($1,$2,'timeline','docked','bottom',112)`, [viewId, boardId])
    await client.query("INSERT INTO osint.timeline_views (view_id,range_mode) VALUES ($1,'auto')", [viewId])
  }

  async function assembleLevel(slug: string, authorMode = false): Promise<CaseState | null> {
    const level = await findLevel(pool, slug)
    if (!level) return null
    const [exhibitsResult, blocksResult, regionsResult, membershipsResult, connectionsResult, metadataResult, eventEvidenceResult,
      aliasesResult, partyEvidenceResult, briefResult, conceptsResult, viewsResult] = await Promise.all([
      pool.query<ExhibitRow>(`SELECT e.id,e.exhibit_type_id,e.xpos,e.ypos,e.width,e.height,e.rotation,e.z_index,e.hidden,
        COALESCE(f.title, d.title, n.title, ev.title, p.display_name, '') AS title,
        COALESCE(f.label_text, n.note_text, ev.narrative_text, p.summary, '') AS content,
        f.is_open, d.document_type_id, d.asset_id, d.published_at, ev.occurred_at,
        p.party_kind, op.organization_kind,
        a.original_name, a.mime_type, a.byte_size,
        s.source_document_exhibit_id AS source_document_id, sr.region_key AS source_region_key
        FROM osint.exhibits e
        LEFT JOIN osint.folder_exhibits f ON f.exhibit_id = e.id
        LEFT JOIN osint.document_exhibits d ON d.exhibit_id = e.id
        LEFT JOIN osint.note_exhibits n ON n.exhibit_id = e.id
        LEFT JOIN osint.event_exhibits ev ON ev.exhibit_id = e.id
        LEFT JOIN osint.party_exhibits p ON p.exhibit_id = e.id
        LEFT JOIN osint.organization_parties op ON op.exhibit_id = e.id
        LEFT JOIN osint.assets a ON a.id = d.asset_id
        LEFT JOIN osint.exhibit_sources s ON s.exhibit_id = e.id
        LEFT JOIN osint.document_regions sr ON sr.id = s.source_region_id
        WHERE e.board_id = $1 ORDER BY e.z_index, e.created_at, e.id`, [level.board_id]),
      pool.query<{ document_exhibit_id: string; content: string }>(
        `SELECT b.document_exhibit_id, b.content FROM osint.document_content_blocks b
         JOIN osint.exhibits e ON e.id = b.document_exhibit_id WHERE e.board_id = $1 ORDER BY b.document_exhibit_id, b.sort_order`, [level.board_id]),
      pool.query<{ document_exhibit_id: string; region_key: string; label: string; excerpt: string; occurred_at: Date | null }>(
        `SELECT r.document_exhibit_id, r.region_key, r.label, r.excerpt, r.occurred_at FROM osint.document_regions r
         JOIN osint.exhibits e ON e.id = r.document_exhibit_id WHERE e.board_id = $1 ORDER BY r.document_exhibit_id, r.sort_order`, [level.board_id]),
      pool.query<{ folder_exhibit_id: string; child_exhibit_id: string; sort_order: number; xpos: number; ypos: number }>(
        `SELECT m.folder_exhibit_id, m.child_exhibit_id, m.sort_order, child.xpos, child.ypos
         FROM osint.folder_memberships m JOIN osint.exhibits child ON child.id = m.child_exhibit_id
         WHERE m.board_id = $1 ORDER BY m.sort_order, m.child_exhibit_id`, [level.board_id]),
      pool.query<{ id: string; from_exhibit_id: string; to_exhibit_id: string; label: string | null; tightness: number; tag_style: 'luggage' | 'compact'; tag_position_percent: number; tag_lateral_offset: number }>(
        `SELECT id, from_exhibit_id, to_exhibit_id, label, tightness, tag_style, tag_position_percent, tag_lateral_offset FROM osint.exhibit_connections WHERE board_id = $1 ORDER BY created_at, id`, [level.board_id]),
      pool.query<{ exhibit_id: string; field_key: string; value: string }>(
        `SELECT v.exhibit_id, f.field_key, v.value FROM osint.exhibit_metadata_text_values v
         JOIN osint.metadata_fields f ON f.id = v.field_id WHERE f.board_id = $1 ORDER BY f.field_key`, [level.board_id]),
      pool.query<{ event_exhibit_id: string; evidence_exhibit_id: string; sort_order: number; note: string | null }>(
        `SELECT event_exhibit_id,evidence_exhibit_id,sort_order,note FROM osint.event_evidence WHERE board_id=$1
         ORDER BY event_exhibit_id,sort_order,evidence_exhibit_id`, [level.board_id]),
      pool.query<{ party_exhibit_id: string; alias: string }>(
        `SELECT a.party_exhibit_id,a.alias FROM osint.party_aliases a JOIN osint.exhibits e ON e.id=a.party_exhibit_id
         WHERE e.board_id=$1 ORDER BY a.party_exhibit_id,a.sort_order,a.id`, [level.board_id]),
      pool.query<{ party_exhibit_id: string; evidence_exhibit_id: string; sort_order: number; note: string | null }>(
        `SELECT party_exhibit_id,evidence_exhibit_id,sort_order,note FROM osint.party_evidence WHERE board_id=$1
         ORDER BY party_exhibit_id,sort_order,evidence_exhibit_id`, [level.board_id]),
      pool.query<{ body: string }>('SELECT body FROM osint.level_briefs WHERE board_id=$1', [level.board_id]),
      pool.query<{ id: string; label: string; context_text: string; expected_party_kind: PartyKind | null; resolved_party_exhibit_id: string | null }>(
        `SELECT id,label,context_text,expected_party_kind,resolved_party_exhibit_id FROM osint.brief_concepts
         WHERE board_id=$1 ORDER BY sort_order,id`, [level.board_id]),
      pool.query<{ id: string; view_type_id: 'timeline'; placement_mode: 'docked' | 'canvas' | 'window'; dock_edge: 'top' | 'right' | 'bottom' | 'left' | null; xpos: number | null; ypos: number | null; width: number | null; height: number; z_index: number; visible: boolean; range_mode: 'auto' | 'fixed'; range_start: string | null; range_end: string | null }>(
        `SELECT v.id,v.view_type_id,v.placement_mode,v.dock_edge,v.xpos,v.ypos,v.width,v.height,v.z_index,v.visible,
          t.range_mode,t.range_start::text,t.range_end::text FROM osint.board_views v
          JOIN osint.timeline_views t ON t.view_id=v.id WHERE v.board_id=$1 ORDER BY v.z_index,v.created_at`, [level.board_id]),
    ])

    const blocks = new Map<string, string[]>()
    for (const row of blocksResult.rows) blocks.set(row.document_exhibit_id, [...(blocks.get(row.document_exhibit_id) || []), row.content])
    const regions = new Map<string, CaseDocument['regions']>()
    for (const row of regionsResult.rows) regions.set(row.document_exhibit_id, [...(regions.get(row.document_exhibit_id) || []), {
      id: row.region_key, label: row.label, excerpt: row.excerpt, date: row.occurred_at?.toISOString(),
    }])
    const metadata = new Map<string, Record<string, string>>()
    for (const row of metadataResult.rows) metadata.set(row.exhibit_id, { ...(metadata.get(row.exhibit_id) || {}), [row.field_key]: row.value })
    const aliases = new Map<string, string[]>()
    for (const row of aliasesResult.rows) aliases.set(row.party_exhibit_id, [...(aliases.get(row.party_exhibit_id) || []), row.alias])
    const relations: ExhibitRelation[] = [
      ...membershipsResult.rows.map(row => ({ id: `contains:${row.folder_exhibit_id}:${row.child_exhibit_id}`, fromExhibitId: row.folder_exhibit_id,
        toExhibitId: row.child_exhibit_id, type: 'contains' as const, sortOrder: row.sort_order })),
      ...eventEvidenceResult.rows.map(row => ({ id: `supports:${row.event_exhibit_id}:${row.evidence_exhibit_id}`, fromExhibitId: row.event_exhibit_id,
        toExhibitId: row.evidence_exhibit_id, type: 'supports' as const, sortOrder: row.sort_order, note: row.note || undefined })),
      ...partyEvidenceResult.rows.map(row => ({ id: `concerns:${row.party_exhibit_id}:${row.evidence_exhibit_id}`, fromExhibitId: row.party_exhibit_id,
        toExhibitId: row.evidence_exhibit_id, type: 'concerns' as const, sortOrder: row.sort_order, note: row.note || undefined })),
      ...exhibitsResult.rows.flatMap(row => row.source_document_id ? [{ id: `source:${row.id}`, fromExhibitId: row.id, toExhibitId: row.source_document_id,
        type: 'source' as const, sourceRegionId: row.source_region_key || undefined, sortOrder: 0 }] : []),
    ]
    const base = (row: ExhibitRow) => ({ id: row.id, title: row.title, x: row.xpos, y: row.ypos, width: row.width, height: row.height,
      rotation: row.rotation, zIndex: row.z_index, hidden: row.hidden })
    const documents: CaseDocument[] = exhibitsResult.rows.filter(row => row.exhibit_type_id === 'document').map(row => {
      const type = row.document_type_id || 'file'
      const publishedAt = row.published_at?.toISOString()
      return { ...base(row), type: 'document', publishedAt,
        body: blocks.get(row.id) || [], regions: regions.get(row.id) || [], assetId: row.asset_id || undefined,
        fileName: row.original_name || undefined, mimeType: row.mime_type || undefined,
        fileSize: row.byte_size === null ? undefined : Number(row.byte_size), fileType: type, metadata: metadata.get(row.id) || {} }
    })
    const evidence: Evidence[] = []
    for (const row of exhibitsResult.rows.filter(row => row.exhibit_type_id !== 'document' && !row.hidden)) {
      const common = { ...base(row), title: row.title, content: row.content }
      if (row.exhibit_type_id === 'folder') evidence.push({ ...common, type:'folder', isOpen:Boolean(row.is_open) })
      else if (row.exhibit_type_id === 'event') evidence.push({ ...common, type:'event', eventDate:row.occurred_at?.toISOString() })
      else if (row.exhibit_type_id === 'party') evidence.push({ ...common, type:'party', partyKind:row.party_kind || 'person', organizationKind:row.organization_kind || undefined, aliases:aliases.get(row.id) || [] })
      else if (row.exhibit_type_id === 'note') evidence.push({ ...common, type:'note' })
      else throw new Error(`Unsupported exhibit type ${row.exhibit_type_id}`)
    }
    const views: BoardView[] = viewsResult.rows.map(row => ({ id: row.id, type: 'timeline', visible: row.visible, zIndex: row.z_index,
      placement: row.placement_mode === 'docked'
        ? { mode: 'docked', dockEdge: row.dock_edge || 'bottom', size: row.height }
        : { mode: row.placement_mode, x: row.xpos || 0, y: row.ypos || 0, width: row.width || 900, height: row.height },
      rangeMode: row.range_mode, range: row.range_start && row.range_end ? { start: row.range_start, end: row.range_end } : undefined }))
    const concepts: BriefConcept[] = conceptsResult.rows.map(row => ({ id: row.id, label: row.label, context: row.context_text,
      ...(authorMode && row.expected_party_kind ? { expectedPartyKind: row.expected_party_kind } : {}), resolvedPartyExhibitId: row.resolved_party_exhibit_id || undefined }))
    return { id: level.slug, title: level.title, subtitle: level.subtitle, exhibits: [...documents, ...evidence], relations,
      connections: connectionsResult.rows.map(row => ({ id: row.id, fromExhibitId: row.from_exhibit_id, toExhibitId: row.to_exhibit_id,
        label: row.label || undefined, tightness: row.tightness, tagStyle: row.tag_style,
        tagPosition: row.tag_position_percent, tagOffset: row.tag_lateral_offset })),
      viewport: { x: level.viewport_x, y: level.viewport_y, zoom: level.viewport_zoom }, updatedAt: level.updated_at.toISOString(),
      views, revision: Number(level.revision),
      brief: { body: briefResult.rows[0]?.body || '', concepts }, levelStatus: level.status, editingAllowed: editingEnabled && authorMode,
      sourceTemplateVersionId: level.source_template_version_id || undefined }
  }

  async function templateSummary(slug: string): Promise<TemplateSummary | null> {
    const result = await pool.query<{
      id: string; slug: string; name: string; current_version: number; version_count: number; updated_at: Date
    }>(`SELECT t.id,t.slug,t.name,current.version AS current_version,COUNT(v.id)::int AS version_count,t.updated_at
        FROM osint.level_templates t
        JOIN osint.level_template_versions current ON current.id=t.current_version_id
        JOIN osint.level_template_versions v ON v.template_id=t.id
        WHERE t.slug=$1 GROUP BY t.id,current.version`, [slug])
    const row = result.rows[0]
    return row ? { id: row.id, slug: row.slug, name: row.name, currentVersion: row.current_version,
      versionCount: row.version_count, updatedAt: row.updated_at.toISOString() } : null
  }

  async function replaceBoard(client: PoolClient, level: LevelRow, state: CaseState) {
    if (!Array.isArray(state.exhibits) || !Array.isArray(state.views)) throw new Error('Level state must contain exhibits and views')
    const documents = state.exhibits.filter(isDocumentExhibit)
    const evidence = state.exhibits.filter((exhibit): exhibit is Evidence => !isDocumentExhibit(exhibit))
    const documentIds = new Set(documents.map(document => requireUuid(document.id, 'Document id')))
    const evidenceIds = new Set(evidence.map(exhibit => requireUuid(exhibit.id, 'Exhibit id')))
    const allIds = state.exhibits.map(exhibit => exhibit.id)
    if (new Set(allIds).size !== allIds.length) throw new Error('Exhibit ids must be unique within a board')
    const expectedConceptKinds = new Map((await client.query<{ id: string; expected_party_kind: PartyKind | null }>(
      'SELECT id,expected_party_kind FROM osint.brief_concepts WHERE board_id=$1', [level.board_id])).rows.map(row => [row.id, row.expected_party_kind]))

    await client.query(`UPDATE osint.levels SET title=$2, subtitle=$3, viewport_x=$4, viewport_y=$5, viewport_zoom=$6,
      updated_at=NOW() WHERE id=$1`, [level.id, state.title, state.subtitle, state.viewport.x, state.viewport.y, state.viewport.zoom])
    await client.query('UPDATE osint.boards SET revision=revision+1, updated_at=NOW() WHERE id=$1', [level.board_id])
    await client.query('DELETE FROM osint.exhibit_connections WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.folder_memberships WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.event_evidence WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.party_evidence WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.party_relationships WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.board_views WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.brief_concepts WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.level_briefs WHERE board_id=$1', [level.board_id])
    await client.query('DELETE FROM osint.exhibit_sources WHERE exhibit_id IN (SELECT id FROM osint.exhibits WHERE board_id=$1)', [level.board_id])
    await client.query('DELETE FROM osint.metadata_fields WHERE board_id=$1', [level.board_id])
    for (const table of ['folder_exhibits', 'image_documents', 'note_exhibits', 'event_exhibits', 'person_parties', 'organization_parties', 'party_exhibits', 'document_exhibits']) {
      await client.query(`DELETE FROM osint.${table} WHERE exhibit_id IN (SELECT id FROM osint.exhibits WHERE board_id=$1)`, [level.board_id])
    }
    if (allIds.length) await client.query('DELETE FROM osint.exhibits WHERE board_id=$1 AND NOT (id = ANY($2::uuid[]))', [level.board_id, allIds])
    else await client.query('DELETE FROM osint.exhibits WHERE board_id=$1', [level.board_id])

    for (const exhibit of state.exhibits) {
      await client.query(`INSERT INTO osint.exhibits (id,board_id,exhibit_type_id,xpos,ypos,width,height,rotation,z_index,hidden)
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
        ON CONFLICT (id) DO UPDATE SET exhibit_type_id=$3,xpos=$4,ypos=$5,width=$6,height=$7,rotation=$8,z_index=$9,hidden=$10,updated_at=NOW()`,
      [exhibit.id, level.board_id, exhibit.type, exhibit.x, exhibit.y, exhibit.width, exhibit.height, exhibit.rotation, exhibit.zIndex, exhibit.hidden])
    }
    for (const document of documents) {
      await client.query(`INSERT INTO osint.document_exhibits (exhibit_id,document_type_id,asset_id,title,published_at,captured_at,source_uri)
        VALUES ($1,$2,$3,$4,$5,$6,$7)`, [document.id, documentType(document), document.assetId || null, document.title,
        timestamp(document.publishedAt), timestamp(document.capturedAt), document.sourceUri || null])
      if (document.fileType === 'image') await client.query('INSERT INTO osint.image_documents (exhibit_id) VALUES ($1)', [document.id])
      for (const [sortOrder, content] of document.body.entries()) await client.query(
        'INSERT INTO osint.document_content_blocks (id,document_exhibit_id,sort_order,content) VALUES ($1,$2,$3,$4)', [randomUUID(), document.id, sortOrder, content])
      for (const [sortOrder, region] of document.regions.entries()) await client.query(
        `INSERT INTO osint.document_regions (id,document_exhibit_id,region_key,label,excerpt,occurred_at,sort_order)
         VALUES ($1,$2,$3,$4,$5,$6,$7)`, [randomUUID(), document.id, region.id, region.label, region.excerpt, timestamp(region.date), sortOrder])
    }
    for (const exhibit of evidence) {
      if (isFolderExhibit(exhibit)) await client.query(
        'INSERT INTO osint.folder_exhibits (exhibit_id,title,label_text,is_open) VALUES ($1,$2,$3,$4)',
        [exhibit.id, exhibit.title, exhibit.content, exhibit.isOpen])
      if (exhibit.type === 'note') await client.query('INSERT INTO osint.note_exhibits (exhibit_id,title,note_text) VALUES ($1,$2,$3)', [exhibit.id, exhibit.title, exhibit.content])
      if (isEventExhibit(exhibit)) await client.query(
        'INSERT INTO osint.event_exhibits (exhibit_id,title,narrative_text,occurred_at) VALUES ($1,$2,$3,$4)',
        [exhibit.id, exhibit.title, exhibit.content, timestamp(exhibit.eventDate)])
      if (isPartyExhibit(exhibit)) {
        const partyKind = exhibit.partyKind
        await client.query('INSERT INTO osint.party_exhibits (exhibit_id,party_kind,display_name,summary) VALUES ($1,$2,$3,$4)',
          [exhibit.id, partyKind, exhibit.title, exhibit.content])
        if (partyKind === 'person') await client.query('INSERT INTO osint.person_parties (exhibit_id) VALUES ($1)', [exhibit.id])
        else await client.query('INSERT INTO osint.organization_parties (exhibit_id,organization_kind) VALUES ($1,$2)',
          [exhibit.id, exhibit.organizationKind || 'business'])
        for (const [sortOrder, alias] of (exhibit.aliases || []).filter(Boolean).entries()) await client.query(
          'INSERT INTO osint.party_aliases (id,party_exhibit_id,alias,sort_order) VALUES ($1,$2,$3,$4)', [randomUUID(), exhibit.id, alias, sortOrder])
      }
    }

    for (const relation of state.relations) {
      if (!allIds.includes(relation.fromExhibitId) || !allIds.includes(relation.toExhibitId) || relation.fromExhibitId === relation.toExhibitId) throw new Error('Relation references an unknown or identical exhibit')
      if (relation.type === 'contains') await client.query(`INSERT INTO osint.folder_memberships
        (board_id,folder_exhibit_id,child_exhibit_id,sort_order) VALUES ($1,$2,$3,$4)`,
      [level.board_id, relation.fromExhibitId, relation.toExhibitId, relation.sortOrder])
      if (relation.type === 'supports') await client.query(`INSERT INTO osint.event_evidence
        (board_id,event_exhibit_id,evidence_exhibit_id,sort_order,note) VALUES ($1,$2,$3,$4,$5)`,
      [level.board_id, relation.fromExhibitId, relation.toExhibitId, relation.sortOrder, relation.note || null])
      if (relation.type === 'concerns') await client.query(`INSERT INTO osint.party_evidence
        (board_id,party_exhibit_id,evidence_exhibit_id,sort_order,note) VALUES ($1,$2,$3,$4,$5)`,
      [level.board_id, relation.fromExhibitId, relation.toExhibitId, relation.sortOrder, relation.note || null])
      if (relation.type === 'source') {
        if (!documentIds.has(relation.toExhibitId)) throw new Error('Exhibit source must reference a document')
        let regionId: string | null = null
        if (relation.sourceRegionId) {
          const region = await client.query<{ id: string }>(
            'SELECT id FROM osint.document_regions WHERE document_exhibit_id=$1 AND region_key=$2', [relation.toExhibitId, relation.sourceRegionId])
          regionId = region.rows[0]?.id || null
        }
        await client.query('INSERT INTO osint.exhibit_sources (exhibit_id,source_document_exhibit_id,source_region_id) VALUES ($1,$2,$3)',
          [relation.fromExhibitId, relation.toExhibitId, regionId])
      }
    }
    for (const connection of state.connections) {
      requireUuid(connection.id, 'Connection id')
      if (!allIds.includes(connection.fromExhibitId) || !allIds.includes(connection.toExhibitId) || connection.fromExhibitId === connection.toExhibitId) throw new Error('Connection references an unknown or identical exhibit')
      const tightness = Math.max(0, Math.min(100, Math.round(Number(connection.tightness ?? 65))))
      const tagStyle = connection.tagStyle === 'compact' ? 'compact' : 'luggage'
      const tagPosition = Math.max(0, Math.min(100, Math.round(Number(connection.tagPosition ?? 50))))
      const lateralLimit = Math.round(10 + (100 - tightness) * .6)
      const tagOffset = Math.max(-lateralLimit, Math.min(lateralLimit, Math.round(Number(connection.tagOffset ?? 0))))
      await client.query(`INSERT INTO osint.exhibit_connections (id,board_id,connection_type_id,from_exhibit_id,to_exhibit_id,label,tightness,tag_style,tag_position_percent,tag_lateral_offset)
        VALUES ($1,$2,'thread',$3,$4,$5,$6,$7,$8,$9)`, [connection.id, level.board_id, connection.fromExhibitId, connection.toExhibitId, connection.label?.trim() || null, tightness, tagStyle, tagPosition, tagOffset])
    }
    for (const view of state.views) {
      requireUuid(view.id, 'Board view id')
      const placement = view.placement
      const docked = placement.mode === 'docked'
      await client.query(`INSERT INTO osint.board_views
        (id,board_id,view_type_id,placement_mode,dock_edge,xpos,ypos,width,height,z_index,visible)
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`, [view.id, level.board_id, view.type, placement.mode,
        placement.mode === 'docked' ? placement.dockEdge : null, placement.mode === 'docked' ? null : placement.x, placement.mode === 'docked' ? null : placement.y,
        placement.mode === 'docked' ? null : placement.width, placement.mode === 'docked' ? placement.size : placement.height, view.zIndex, view.visible])
      if (view.type === 'timeline') {
        if (view.rangeMode === 'fixed' && (!view.range || !timestamp(view.range.start) || !timestamp(view.range.end) || Date.parse(view.range.end) <= Date.parse(view.range.start))) throw new Error('Timeline end must be after timeline start')
        await client.query(`INSERT INTO osint.timeline_views (view_id,range_mode,range_start,range_end) VALUES ($1,$2,$3,$4)`,
          [view.id, view.rangeMode, view.rangeMode === 'fixed' ? view.range!.start : null, view.rangeMode === 'fixed' ? view.range!.end : null])
      }
    }
    const fields = new Map<string, string>()
    for (const document of documents) for (const key of Object.keys(document.metadata || {})) {
      if (!fields.has(key)) {
        const fieldId = randomUUID(); fields.set(key, fieldId)
        await client.query(`INSERT INTO osint.metadata_fields (id,board_id,field_key,label,value_type) VALUES ($1,$2,$3,$3,'text')`, [fieldId, level.board_id, key])
      }
      await client.query('INSERT INTO osint.exhibit_metadata_text_values (exhibit_id,field_id,value) VALUES ($1,$2,$3)',
        [document.id, fields.get(key), document.metadata[key]])
    }
    const brief = state.brief || { body: '', concepts: [] }
    await client.query('INSERT INTO osint.level_briefs (board_id,body) VALUES ($1,$2)', [level.board_id, brief.body || ''])
    for (const [sortOrder, concept] of brief.concepts.entries()) {
      requireUuid(concept.id, 'Brief concept id')
      if (concept.resolvedPartyExhibitId && !evidenceIds.has(concept.resolvedPartyExhibitId)) throw new Error('Concept resolution references an unknown party')
      const expected = concept.expectedPartyKind || expectedConceptKinds.get(concept.id) || null
      await client.query(`INSERT INTO osint.brief_concepts
        (id,board_id,label,context_text,sort_order,expected_party_kind,resolved_party_exhibit_id) VALUES ($1,$2,$3,$4,$5,$6,$7)`,
      [concept.id, level.board_id, concept.label, concept.context, sortOrder, expected, concept.resolvedPartyExhibitId || null])
    }
  }

  return {
    async listLevels() {
      const result = await pool.query(`SELECT slug AS id, title, subtitle, status, updated_at AS "updatedAt"
        FROM osint.levels ORDER BY updated_at DESC`)
      return result.rows
    },
    async createLevel(input) {
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        const boardId = randomUUID(); const levelId = randomUUID()
        await client.query(`INSERT INTO osint.boards (id,board_kind) VALUES ($1,'level')`, [boardId])
        await client.query(`INSERT INTO osint.levels (id,slug,board_id,title,subtitle) VALUES ($1,$2,$3,$4,$5)`,
          [levelId, input.id, boardId, input.title, input.subtitle])
        await createDefaultBoardViews(client, boardId)
        await client.query('COMMIT')
      } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
      return (await assembleLevel(input.id))!
    },
    async listTemplates() {
      const result = await pool.query<{
        id: string; slug: string; name: string; current_version: number; version_count: number; updated_at: Date
      }>(`SELECT t.id,t.slug,t.name,current.version AS current_version,COUNT(v.id)::int AS version_count,t.updated_at
          FROM osint.level_templates t
          JOIN osint.level_template_versions current ON current.id=t.current_version_id
          JOIN osint.level_template_versions v ON v.template_id=t.id
          GROUP BY t.id,current.version ORDER BY t.updated_at DESC,t.slug`)
      return result.rows.map(row => ({ id: row.id, slug: row.slug, name: row.name, currentVersion: row.current_version,
        versionCount: row.version_count, updatedAt: row.updated_at.toISOString() }))
    },
    async instantiateTemplate(templateSlug, input) {
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        const version = await client.query<{ id: string; board_id: string; title: string; subtitle: string }>(
          `SELECT v.id,v.board_id,v.title,v.subtitle FROM osint.level_templates t
           JOIN osint.level_template_versions v ON v.template_id=t.id
           WHERE t.slug=$1 AND (($2::int IS NULL AND v.id=t.current_version_id) OR v.version=$2)
           FOR SHARE OF t,v`, [templateSlug, input.version ?? null])
        const source = version.rows[0]
        if (!source) { await client.query('ROLLBACK'); return null }
        const boardId = randomUUID(); const levelId = randomUUID()
        await client.query(`INSERT INTO osint.boards (id,board_kind) VALUES ($1,'level')`, [boardId])
        await client.query(`INSERT INTO osint.levels
          (id,slug,board_id,source_template_version_id,title,subtitle) VALUES ($1,$2,$3,$4,$5,$6)`,
        [levelId, input.id, boardId, source.id, input.title || source.title, source.subtitle])
        await cloneBoard(client, source.board_id, boardId)
        await client.query('COMMIT')
      } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
      return assembleLevel(input.id)
    },
    async saveLevelAsTemplate(levelId, input) {
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        const level = await findLevel(client, levelId, true)
        if (!level) { await client.query('ROLLBACK'); return null }
        await client.query('SELECT pg_advisory_xact_lock(hashtext($1))', [input.slug])
        let template = await client.query<{ id: string }>('SELECT id FROM osint.level_templates WHERE slug=$1 FOR UPDATE', [input.slug])
        let templateId = template.rows[0]?.id
        if (!templateId) {
          templateId = randomUUID()
          await client.query('INSERT INTO osint.level_templates (id,slug,name) VALUES ($1,$2,$3)', [templateId, input.slug, input.name])
          template = await client.query<{ id: string }>('SELECT id FROM osint.level_templates WHERE id=$1 FOR UPDATE', [templateId])
        }
        const versionResult = await client.query<{ version: number }>(
          'SELECT COALESCE(MAX(version),0)::int+1 AS version FROM osint.level_template_versions WHERE template_id=$1', [templateId])
        const version = versionResult.rows[0].version
        const boardId = randomUUID(); const versionId = randomUUID()
        await client.query(`INSERT INTO osint.boards (id,board_kind) VALUES ($1,'template_version')`, [boardId])
        await client.query(`INSERT INTO osint.level_template_versions
          (id,template_id,version,board_id,title,subtitle,created_from_level_id) VALUES ($1,$2,$3,$4,$5,$6,$7)`,
        [versionId, templateId, version, boardId, level.title, level.subtitle, level.id])
        await cloneBoard(client, level.board_id, boardId)
        await client.query('UPDATE osint.level_templates SET name=$2,current_version_id=$3,updated_at=NOW() WHERE id=$1',
          [templateId, input.name, versionId])
        await client.query('COMMIT')
      } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
      return templateSummary(input.slug)
    },
    getLevel(levelId, authorMode = false) { return assembleLevel(levelId, authorMode) },
    async saveLevel(state) {
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        const level = await findLevel(client, state.id, true)
        if (!level) throw new Error('Level not found')
        await replaceBoard(client, level, state)
        await client.query('COMMIT')
      } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
    },
    async resetLevel(levelId) {
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        const level = await findLevel(client, levelId, true)
        if (!level) { await client.query('ROLLBACK'); return null }
        if (level.source_template_version_id) {
          const version = await client.query<{ board_id: string; title: string; subtitle: string }>(
            'SELECT board_id,title,subtitle FROM osint.level_template_versions WHERE id=$1 FOR SHARE', [level.source_template_version_id])
          const source = version.rows[0]
          if (!source) throw new Error('Source template version not found')
          await clearBoard(client, level.board_id)
          await cloneBoard(client, source.board_id, level.board_id)
          await client.query(`UPDATE osint.levels SET title=$2,subtitle=$3,viewport_x=0,viewport_y=28,viewport_zoom=0.7,updated_at=NOW()
            WHERE id=$1`, [level.id, source.title, source.subtitle])
        }
        await client.query('COMMIT')
      } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
      return assembleLevel(levelId)
    },
    async getAsset(assetId) {
      if (!uuidPattern.test(assetId)) return null
      const result = await pool.query<AssetRecord>('SELECT original_name,mime_type,byte_size,content,storage_provider,object_key FROM osint.assets WHERE id=$1', [assetId])
      const asset = result.rows[0]
      if (!asset) return null
      if (asset.storage_provider === 'postgres') {
        if (!asset.content) throw new Error(`PostgreSQL asset ${assetId} has no content`)
        return { originalName: asset.original_name, mimeType: asset.mime_type, byteSize: Number(asset.byte_size), stream: Readable.from(asset.content) }
      }
      if (!asset.object_key) throw new Error(`Object asset ${assetId} has no object key`)
      const object = await objectStorage.getObject(asset.object_key)
      return object ? { originalName: asset.original_name, mimeType: asset.mime_type, byteSize: Number(asset.byte_size), stream: object.stream } : null
    },
    async uploadDocument(levelId, file) {
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        const level = await findLevel(client, levelId, true)
        if (!level) { await client.query('ROLLBACK'); return null }
        const candidateAssetId = randomUUID(); const exhibitId = randomUUID()
        const checksum = createHash('sha256').update(file.buffer).digest('hex')
        let assetId = (await client.query<{ id: string }>('SELECT id FROM osint.assets WHERE checksum_sha256=$1 AND byte_size=$2 FOR SHARE', [checksum,file.size])).rows[0]?.id
        if (!assetId) {
          const objectKey = `assets/${checksum.slice(0,2)}/${checksum}`
          const stored = await objectStorage.putObject(objectKey,file.buffer,file.mimetype || 'application/octet-stream')
          const asset = await client.query<{ id: string }>(`INSERT INTO osint.assets
            (id,original_name,mime_type,byte_size,content,checksum_sha256,storage_provider,storage_bucket,object_key,etag)
            VALUES ($1,$2,$3,$4,NULL,$5,'s3',$6,$7,$8)
            ON CONFLICT (checksum_sha256,byte_size) DO UPDATE SET checksum_sha256=EXCLUDED.checksum_sha256 RETURNING id`,
          [candidateAssetId,file.originalname,file.mimetype || 'application/octet-stream',file.size,checksum,objectStorage.bucket,objectKey,stored.etag || null])
          assetId = asset.rows[0].id
        }
        const fileType: SourceFileType = file.mimetype.startsWith('image/') ? 'image' : file.mimetype === 'application/pdf' ? 'pdf' : file.mimetype.startsWith('text/') ? 'text' : 'file'
        await client.query(`INSERT INTO osint.exhibits (id,board_id,exhibit_type_id,xpos,ypos,width,height,z_index,hidden)
          VALUES ($1,$2,'document',100,100,174,145,(SELECT COUNT(*) FROM osint.exhibits WHERE board_id=$2),FALSE)`, [exhibitId, level.board_id])
        await client.query(`INSERT INTO osint.document_exhibits (exhibit_id,document_type_id,asset_id,title) VALUES ($1,$2,$3,$4)`,
          [exhibitId, fileType, assetId, file.originalname])
        if (fileType === 'image') await client.query('INSERT INTO osint.image_documents (exhibit_id) VALUES ($1)', [exhibitId])
        await client.query('UPDATE osint.levels SET updated_at=NOW() WHERE id=$1', [level.id])
        await client.query('UPDATE osint.boards SET revision=revision+1,updated_at=NOW() WHERE id=$1', [level.board_id])
        await client.query('COMMIT')
        return { id: exhibitId,type:'document',title:file.originalname,x:100,y:100,width:174,height:145,rotation:0,zIndex:0,hidden:false,
          fileType,metadata:{},body:[],regions:[],assetId,fileName:file.originalname,mimeType:file.mimetype,fileSize:file.size }
      } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
    },
  }
}