CG-Feedback-Monitor/server/db/queries/assignmentTransfer.ts

/**
 * Einzel-Assignment Export/Import für Lead-Übergabe (Urlaub/Krankheit).
 * Enthält Runtime-Daten inkl. Checklisten + referenzierte Berater/Gruppen.
 * Struktur wird per stabilem Schlüssel gemappt; Berater werden bei Namensgleichheit
 * wiederverwendet, sonst neu angelegt.
 */
import { db } from '../../database'
import {
  getAllRows, getRowById, getRowsWhereEquals, getRowsWhereIn, insertRow,
} from '../crud'
import type {
  Assignment, AssignmentType, PhaseTemplate, ChecklistItemDef, ChecklistResponse,
  Group, Consultant, MeetingInstance, ConversationEntry, ConversationSkillScore,
  Assessment, FeedbackDraft, AssignmentFeedback,
  FeedbackCategoryRating, FeedbackDimensionText, FeedbackCategoryText,
  FeedbackDimension, FeedbackCategory, FeedbackCriterionItem, ConversationLineTag,
  AssignmentTransferPackage, AssignmentImportResult,
} from '../../../src/db/types'
import { ASSIGNMENT_TRANSFER_KIND } from '../../../src/db/types'
import { listMeetingInstancesForAssignment } from './meetings'
import { listChecklistItemDefsForPhaseTemplates, listChecklistResponsesForMeetings } from './checklists'
import { listConversationEntriesForMeetings } from './conversationEntries'
import { listAssessmentsForMeetings } from './assessments'
import { listMeetingCategoryRatingsForMeetings } from './meetingCategoryRatings'

const norm = (s: string) => s.trim().toLowerCase().replace(/\s+/g, ' ')
const personKey = (c: Pick<Consultant, 'firstName' | 'lastName'>) =>
  `${norm(c.firstName)}|${norm(c.lastName)}`
const emailKey = (c: Pick<Consultant, 'email'>): string | null => {
  const e = (c.email ?? '').trim().toLowerCase()
  return e || null
}
const groupKey = (g: Pick<Group, 'name' | 'cohortYear'>) =>
  `${norm(g.name)}|${g.cohortYear}`

function collectCriterionIds(pkgParts: {
  assignmentType: AssignmentType
  phaseTemplates: PhaseTemplate[]
  checklistItemDefs: ChecklistItemDef[]
  assessments: Assessment[]
  checklistResponses: ChecklistResponse[]
  conversationEntries: ConversationEntry[]
  meetingInstances: MeetingInstance[]
}): Set<number> {
  const ids = new Set<number>()
  for (const id of pkgParts.assignmentType.criteriaIds ?? []) ids.add(id)
  for (const p of pkgParts.phaseTemplates) for (const id of p.criteriaIds ?? []) ids.add(id)
  for (const c of pkgParts.checklistItemDefs) {
    if (c.linkedCriterionItemId != null) ids.add(c.linkedCriterionItemId)
  }
  for (const a of pkgParts.assessments) ids.add(a.criteriaId)
  for (const r of pkgParts.checklistResponses) {
    if (r.criterionItemId != null) ids.add(r.criterionItemId)
  }
  for (const e of pkgParts.conversationEntries) {
    for (const t of e.lineTags ?? []) ids.add(t.criterionItemId)
  }
  for (const m of pkgParts.meetingInstances) {
    for (const t of m.generalNoteTags ?? []) ids.add(t.criterionItemId)
  }
  return ids
}

function remapTags(
  tags: ConversationLineTag[] | undefined,
  criterionMap: Map<number, number>,
): ConversationLineTag[] | undefined {
  if (!tags) return tags
  return tags.map(t => ({
    text: t.text,
    criterionItemId: criterionMap.get(t.criterionItemId) ?? t.criterionItemId,
  }))
}

function requireMap(map: Map<number, number>, oldId: number, label: string): number {
  const next = map.get(oldId)
  if (next == null) throw new Error(`${label}: Quell-ID ${oldId} konnte nicht gemappt werden`)
  return next
}

/** Exportiert ein Assignment inkl. aller Meetings/Notizen/Checklisten/Feedbacks und referenzierter Berater. */
export function exportAssignmentTransfer(assignmentId: number): AssignmentTransferPackage {
  const assignment = getRowById<Assignment>('assignments', assignmentId)
  if (!assignment) throw new Error('Assignment nicht gefunden')

  const assignmentType = getRowById<AssignmentType>('assignmentTypes', assignment.assignmentTypeId)
  if (!assignmentType) throw new Error('Assignment-Typ nicht gefunden')

  const phaseTemplates = getRowsWhereEquals<PhaseTemplate>('phaseTemplates', 'assignmentTypeId', assignment.assignmentTypeId)
    .sort((a, b) => a.order - b.order)
  const phaseIds = phaseTemplates.map(p => p.id!)
  const checklistItemDefs = listChecklistItemDefsForPhaseTemplates(phaseIds)

  const meetingInstances = listMeetingInstancesForAssignment(assignmentId)
  const meetingIds = meetingInstances.map(m => m.id!)
  const conversationEntries = listConversationEntriesForMeetings(meetingIds)
  const entryIds = conversationEntries.map(e => e.id!)
  const conversationSkillScores = getAllRows<ConversationSkillScore>('conversationSkillScores')
    .filter(s => entryIds.includes(s.conversationEntryId))
  const assessments = listAssessmentsForMeetings(meetingIds)
  const meetingCategoryRatings = listMeetingCategoryRatingsForMeetings(meetingIds)
  const checklistResponses = listChecklistResponsesForMeetings(meetingIds)

  const feedbackDrafts = getRowsWhereEquals<FeedbackDraft>('feedbackDrafts', 'assignmentId', assignmentId)
  const assignmentFeedbacks = getRowsWhereEquals<AssignmentFeedback>('assignmentFeedbacks', 'assignmentId', assignmentId)
  const feedbackIds = assignmentFeedbacks.map(f => f.id!)
  const feedbackCategoryRatings = getRowsWhereIn<FeedbackCategoryRating>('feedbackCategoryRatings', 'assignmentFeedbackId', feedbackIds)
  const feedbackDimensionTexts = getRowsWhereIn<FeedbackDimensionText>('feedbackDimensionTexts', 'assignmentFeedbackId', feedbackIds)
  const feedbackCategoryTexts = getRowsWhereIn<FeedbackCategoryText>('feedbackCategoryTexts', 'assignmentFeedbackId', feedbackIds)

  const instituteeIds = new Set<number>(assignment.instituteeIds)
  for (const e of assignment.instituteeEnrollments ?? []) instituteeIds.add(e.instituteeId)
  for (const e of conversationEntries) instituteeIds.add(e.instituteeId)
  for (const a of assessments) instituteeIds.add(a.instituteeId)
  for (const r of meetingCategoryRatings) instituteeIds.add(r.instituteeId)
  for (const r of checklistResponses) if (r.instituteeId != null) instituteeIds.add(r.instituteeId)
  for (const f of feedbackDrafts) instituteeIds.add(f.instituteeId)
  for (const f of assignmentFeedbacks) instituteeIds.add(f.instituteeId)

  const consultants = getRowsWhereIn<Consultant>('consultants', 'id', [...instituteeIds])
  const groupIds = [...new Set(consultants.map(c => c.groupId))]
  const groups = getRowsWhereIn<Group>('groups', 'id', groupIds)

  const criterionIds = collectCriterionIds({
    assignmentType, phaseTemplates, checklistItemDefs, assessments,
    checklistResponses, conversationEntries, meetingInstances,
  })
  for (const r of meetingCategoryRatings) {
    for (const i of getRowsWhereEquals<FeedbackCriterionItem>('feedbackCriterionItems', 'categoryId', r.categoryId)) {
      criterionIds.add(i.id!)
    }
  }
  for (const r of feedbackCategoryRatings) {
    for (const i of getRowsWhereEquals<FeedbackCriterionItem>('feedbackCriterionItems', 'categoryId', r.feedbackCategoryId)) {
      criterionIds.add(i.id!)
    }
  }
  for (const s of conversationSkillScores) {
    for (const i of getRowsWhereEquals<FeedbackCriterionItem>('feedbackCriterionItems', 'categoryId', s.criteriaId)) {
      criterionIds.add(i.id!)
    }
  }

  const feedbackCriterionItems = getRowsWhereIn<FeedbackCriterionItem>('feedbackCriterionItems', 'id', [...criterionIds])
    .map(i => ({ ...i, learnedPatternHints: undefined }))
  const categoryIds = new Set(feedbackCriterionItems.map(i => i.categoryId))
  for (const r of meetingCategoryRatings) categoryIds.add(r.categoryId)
  for (const r of feedbackCategoryRatings) categoryIds.add(r.feedbackCategoryId)
  for (const t of feedbackCategoryTexts) categoryIds.add(t.feedbackCategoryId)
  for (const s of conversationSkillScores) categoryIds.add(s.criteriaId)

  const feedbackCategories = getRowsWhereIn<FeedbackCategory>('feedbackCategories', 'id', [...categoryIds])
  const dimensionIds = new Set(feedbackCategories.map(c => c.dimensionId))
  for (const t of feedbackDimensionTexts) dimensionIds.add(t.feedbackDimensionId)
  const feedbackDimensions = getRowsWhereIn<FeedbackDimension>('feedbackDimensions', 'id', [...dimensionIds])

  return {
    kind: ASSIGNMENT_TRANSFER_KIND,
    version: 1,
    exportedAt: new Date().toISOString(),
    source: {
      assignmentId,
      assignmentNumber: assignment.assignmentNumber,
      title: assignment.title,
      client: assignment.client,
    },
    groups,
    consultants,
    assignmentType,
    phaseTemplates,
    checklistItemDefs,
    feedbackDimensions,
    feedbackCategories,
    feedbackCriterionItems,
    assignment,
    meetingInstances,
    conversationEntries,
    conversationSkillScores,
    assessments,
    meetingCategoryRatings,
    checklistResponses,
    feedbackDrafts,
    assignmentFeedbacks,
    feedbackCategoryRatings,
    feedbackDimensionTexts,
    feedbackCategoryTexts,
  }
}

/** Importiert ein Transfer-Paket als neues Assignment (Merge, kein Wipe). */
export function importAssignmentTransfer(pkg: AssignmentTransferPackage): AssignmentImportResult {
  if (!pkg || pkg.kind !== ASSIGNMENT_TRANSFER_KIND || pkg.version !== 1) {
    throw new Error('Ungültiges Assignment-Übergabe-Format')
  }
  if (!pkg.assignment || !pkg.assignmentType) {
    throw new Error('Paket unvollständig (Assignment/Typ fehlt)')
  }

  const groupMap = new Map<number, number>()
  const consultantMap = new Map<number, number>()
  const dimensionMap = new Map<number, number>()
  const categoryMap = new Map<number, number>()
  const criterionMap = new Map<number, number>()
  const typeMap = new Map<number, number>()
  const phaseMap = new Map<number, number>()
  const checklistDefMap = new Map<number, number>()
  const meetingMap = new Map<number, number>()
  const entryMap = new Map<number, number>()
  const feedbackMap = new Map<number, number>()

  const consultantsMatched: string[] = []
  const consultantsCreated: string[] = []

  const localDims = getAllRows<FeedbackDimension>('feedbackDimensions')
  for (const d of pkg.feedbackDimensions) {
    const byId = d.id != null ? getRowById<FeedbackDimension>('feedbackDimensions', d.id) : undefined
    if (byId && norm(byId.name) === norm(d.name)) {
      dimensionMap.set(d.id!, d.id!)
      continue
    }
    const hit = localDims.find(x => norm(x.name) === norm(d.name))
    if (!hit?.id) {
      throw new Error(`Feedback-Dimension „${d.name}" fehlt lokal — bitte zuerst npm run setup / Starter laden.`)
    }
    dimensionMap.set(d.id!, hit.id)
  }

  const localCats = getAllRows<FeedbackCategory>('feedbackCategories')
  for (const c of pkg.feedbackCategories) {
    const dimId = requireMap(dimensionMap, c.dimensionId, 'Dimension')
    const byId = c.id != null ? getRowById<FeedbackCategory>('feedbackCategories', c.id) : undefined
    if (byId && norm(byId.name) === norm(c.name) && byId.dimensionId === dimId) {
      categoryMap.set(c.id!, c.id!)
      continue
    }
    const hit = localCats.find(x => x.dimensionId === dimId && norm(x.name) === norm(c.name))
    if (!hit?.id) {
      throw new Error(`Feedback-Kategorie „${c.name}" fehlt lokal — bitte Starter-Struktur angleichen.`)
    }
    categoryMap.set(c.id!, hit.id)
  }

  const localItems = getAllRows<FeedbackCriterionItem>('feedbackCriterionItems')
  for (const item of pkg.feedbackCriterionItems) {
    const catId = requireMap(categoryMap, item.categoryId, 'Kategorie')
    const byId = item.id != null ? getRowById<FeedbackCriterionItem>('feedbackCriterionItems', item.id) : undefined
    if (byId && norm(byId.name) === norm(item.name) && byId.categoryId === catId) {
      criterionMap.set(item.id!, item.id!)
      continue
    }
    const hit = localItems.find(x => x.categoryId === catId && norm(x.name) === norm(item.name))
    if (!hit?.id) {
      throw new Error(`Kriterium „${item.name}" fehlt lokal — bitte Starter-Struktur angleichen.`)
    }
    criterionMap.set(item.id!, hit.id)
  }

  const localTypes = getAllRows<AssignmentType>('assignmentTypes')
  {
    const t = pkg.assignmentType
    const byId = t.id != null ? getRowById<AssignmentType>('assignmentTypes', t.id) : undefined
    let typeId: number
    if (byId && norm(byId.name) === norm(t.name)) {
      typeId = t.id!
    } else {
      const hit = localTypes.find(x => norm(x.name) === norm(t.name))
      if (hit?.id) {
        typeId = hit.id
      } else {
        typeId = insertRow('assignmentTypes', {
          name: t.name,
          defaultPhaseKeys: t.defaultPhaseKeys,
          criteriaIds: (t.criteriaIds ?? [])
            .map(id => criterionMap.get(id))
            .filter((id): id is number => id != null),
        })
      }
    }
    typeMap.set(t.id!, typeId)
  }

  const targetTypeId = requireMap(typeMap, pkg.assignmentType.id!, 'Assignment-Typ')
  const localPhases = getRowsWhereEquals<PhaseTemplate>('phaseTemplates', 'assignmentTypeId', targetTypeId)
  for (const p of pkg.phaseTemplates) {
    const byKey = localPhases.find(x => x.phaseKey === p.phaseKey && norm(x.label) === norm(p.label))
      ?? localPhases.find(x => x.phaseKey === p.phaseKey)
    let phaseId: number
    if (byKey?.id) {
      phaseId = byKey.id
    } else {
      phaseId = insertRow('phaseTemplates', {
        assignmentTypeId: targetTypeId,
        phaseKey: p.phaseKey,
        label: p.label,
        withWhom: p.withWhom,
        isRepeatable: p.isRepeatable,
        hasConversation: p.hasConversation,
        hasAssessment: p.hasAssessment,
        hasChecklist: p.hasChecklist,
        criteriaIds: (p.criteriaIds ?? [])
          .map(id => criterionMap.get(id))
          .filter((id): id is number => id != null),
        order: p.order,
      })
    }
    phaseMap.set(p.id!, phaseId)
  }

  for (const def of pkg.checklistItemDefs) {
    const phaseId = requireMap(phaseMap, def.phaseTemplateId, 'Phase')
    const localDefs = getRowsWhereEquals<ChecklistItemDef>('checklistItemDefs', 'phaseTemplateId', phaseId)
    const hit = localDefs.find(x => norm(x.label) === norm(def.label) && x.order === def.order)
      ?? localDefs.find(x => norm(x.label) === norm(def.label))
    let defId: number
    if (hit?.id) {
      defId = hit.id
    } else {
      defId = insertRow('checklistItemDefs', {
        phaseTemplateId: phaseId,
        label: def.label,
        linkedCriterionItemId: def.linkedCriterionItemId != null
          ? (criterionMap.get(def.linkedCriterionItemId) ?? null)
          : null,
        scope: def.scope,
        order: def.order,
        scoreContribution: def.scoreContribution,
      })
    }
    checklistDefMap.set(def.id!, defId)
  }

  const pkgGroupsById = new Map(pkg.groups.map(g => [g.id!, g]))
  for (const g of pkg.groups) {
    const locals = getAllRows<Group>('groups')
    const hit = locals.find(x => groupKey(x) === groupKey(g))
    groupMap.set(g.id!, hit?.id ?? insertRow('groups', { name: g.name, cohortYear: g.cohortYear }))
  }

  for (const c of pkg.consultants) {
    const label = `${c.firstName} ${c.lastName}`.trim()
    const locals = getAllRows<Consultant>('consultants')
    const ek = emailKey(c)
    const hit = ek
      ? locals.find(x => emailKey(x) === ek)
      : locals.find(x => personKey(x) === personKey(c))
    if (hit?.id) {
      consultantMap.set(c.id!, hit.id)
      consultantsMatched.push(label)
      continue
    }
    const srcGroup = pkgGroupsById.get(c.groupId)
    if (!srcGroup) throw new Error(`Gruppe für Berater „${label}" fehlt im Paket`)
    let groupId = groupMap.get(srcGroup.id!)
    if (groupId == null) {
      const gLocals = getAllRows<Group>('groups')
      const gHit = gLocals.find(x => groupKey(x) === groupKey(srcGroup))
      groupId = gHit?.id ?? insertRow('groups', { name: srcGroup.name, cohortYear: srcGroup.cohortYear })
      groupMap.set(srcGroup.id!, groupId)
    }
    const newId = insertRow('consultants', {
      groupId,
      firstName: c.firstName,
      lastName: c.lastName,
      email: ek ?? '',
    })
    consultantMap.set(c.id!, newId)
    consultantsCreated.push(label)
  }

  let newAssignmentId = 0
  let checklistResponseCount = 0

  db.exec('BEGIN')
  try {
    const a = pkg.assignment
    newAssignmentId = insertRow('assignments', {
      title: a.title,
      client: a.client,
      assignmentTypeId: targetTypeId,
      instituteeIds: a.instituteeIds.map(id => requireMap(consultantMap, id, 'Berater')),
      leadName: a.leadName,
      status: a.status,
      createdAt: a.createdAt || new Date().toISOString(),
      assignmentNumber: a.assignmentNumber,
      description: a.description,
      instituteeEnrollments: (a.instituteeEnrollments ?? []).map(e => ({
        ...e,
        instituteeId: requireMap(consultantMap, e.instituteeId, 'Berater'),
      })),
      deletedAt: null,
      archivedAt: null,
    })

    for (const m of pkg.meetingInstances) {
      const meetingId = insertRow('meetingInstances', {
        assignmentId: newAssignmentId,
        phaseTemplateId: requireMap(phaseMap, m.phaseTemplateId, 'Phase'),
        phaseIndex: m.phaseIndex,
        date: m.date,
        status: m.status,
        generalNotes: m.generalNotes ?? '',
        deletedAt: m.deletedAt ?? null,
        generalNoteTags: remapTags(m.generalNoteTags, criterionMap),
      })
      meetingMap.set(m.id!, meetingId)
    }

    for (const e of pkg.conversationEntries) {
      const entryId = insertRow('conversationEntries', {
        meetingInstanceId: requireMap(meetingMap, e.meetingInstanceId, 'Meeting'),
        instituteeId: requireMap(consultantMap, e.instituteeId, 'Berater'),
        sequenceIndex: e.sequenceIndex,
        note: e.note,
        fillerCount: e.fillerCount ?? 0,
        updatedAt: e.updatedAt || new Date().toISOString(),
        lineTags: remapTags(e.lineTags, criterionMap),
      })
      entryMap.set(e.id!, entryId)
    }

    for (const s of pkg.conversationSkillScores) {
      insertRow('conversationSkillScores', {
        conversationEntryId: requireMap(entryMap, s.conversationEntryId, 'Protokoll-Eintrag'),
        criteriaId: requireMap(categoryMap, s.criteriaId, 'Kategorie (Skill-Score)'),
        score: s.score,
      })
    }

    for (const asmt of pkg.assessments) {
      insertRow('assessments', {
        meetingInstanceId: requireMap(meetingMap, asmt.meetingInstanceId, 'Meeting'),
        instituteeId: requireMap(consultantMap, asmt.instituteeId, 'Berater'),
        criteriaId: requireMap(criterionMap, asmt.criteriaId, 'Kriterium'),
        score: asmt.score,
        note: asmt.note ?? '',
        updatedAt: asmt.updatedAt || new Date().toISOString(),
        scoreNeedsReview: asmt.scoreNeedsReview ?? false,
      })
    }

    for (const r of pkg.meetingCategoryRatings) {
      insertRow('meetingCategoryRatings', {
        meetingInstanceId: requireMap(meetingMap, r.meetingInstanceId, 'Meeting'),
        instituteeId: requireMap(consultantMap, r.instituteeId, 'Berater'),
        categoryId: requireMap(categoryMap, r.categoryId, 'Kategorie'),
        score: r.score,
        note: r.note,
      })
    }

    for (const r of pkg.checklistResponses) {
      insertRow('checklistResponses', {
        meetingInstanceId: requireMap(meetingMap, r.meetingInstanceId, 'Meeting'),
        instituteeId: r.instituteeId != null ? requireMap(consultantMap, r.instituteeId, 'Berater') : null,
        checklistItemId: requireMap(checklistDefMap, r.checklistItemId, 'Checklistenpunkt'),
        rating: r.rating,
        note: r.note ?? '',
        criterionItemId: r.criterionItemId != null ? (criterionMap.get(r.criterionItemId) ?? null) : null,
        criterionOverride: r.criterionOverride ?? false,
      })
      checklistResponseCount++
    }

    for (const d of pkg.feedbackDrafts) {
      insertRow('feedbackDrafts', {
        assignmentId: newAssignmentId,
        instituteeId: requireMap(consultantMap, d.instituteeId, 'Berater'),
        content: d.content,
        updatedAt: d.updatedAt || new Date().toISOString(),
      })
    }

    for (const f of pkg.assignmentFeedbacks) {
      const fbId = insertRow('assignmentFeedbacks', {
        assignmentId: newAssignmentId,
        instituteeId: requireMap(consultantMap, f.instituteeId, 'Berater'),
        status: f.status,
        aiModel: f.aiModel ?? '',
        updatedAt: f.updatedAt || new Date().toISOString(),
      })
      feedbackMap.set(f.id!, fbId)
    }

    for (const r of pkg.feedbackCategoryRatings) {
      insertRow('feedbackCategoryRatings', {
        assignmentFeedbackId: requireMap(feedbackMap, r.assignmentFeedbackId, 'Feedback'),
        feedbackCategoryId: requireMap(categoryMap, r.feedbackCategoryId, 'Kategorie'),
        rating: r.rating,
      })
    }

    for (const t of pkg.feedbackDimensionTexts) {
      insertRow('feedbackDimensionTexts', {
        assignmentFeedbackId: requireMap(feedbackMap, t.assignmentFeedbackId, 'Feedback'),
        feedbackDimensionId: requireMap(dimensionMap, t.feedbackDimensionId, 'Dimension'),
        achievements: t.achievements ?? '',
        developmentNeeds: t.developmentNeeds ?? '',
        suggestedAchievements: t.suggestedAchievements,
        suggestedDevelopmentNeeds: t.suggestedDevelopmentNeeds,
      })
    }

    for (const t of pkg.feedbackCategoryTexts) {
      insertRow('feedbackCategoryTexts', {
        assignmentFeedbackId: requireMap(feedbackMap, t.assignmentFeedbackId, 'Feedback'),
        feedbackCategoryId: requireMap(categoryMap, t.feedbackCategoryId, 'Kategorie'),
        benefits: t.benefits ?? '',
        concerns: t.concerns ?? '',
        suggestedBenefits: t.suggestedBenefits,
        suggestedConcerns: t.suggestedConcerns,
      })
    }

    db.exec('COMMIT')
  } catch (err) {
    db.exec('ROLLBACK')
    throw err
  }

  return {
    assignmentId: newAssignmentId,
    consultantsMatched,
    consultantsCreated,
    meetings: pkg.meetingInstances.length,
    checklistResponses: checklistResponseCount,
  }
}