/**
 * Chat Migrator - Migrates topics and messages from Dexie to SQLite
 *
 * ## Overview
 *
 * This migrator handles the largest data migration task: transferring all chat topics
 * and their messages from the old Dexie/IndexedDB storage to the new SQLite database.
 *
 * ## Data Sources
 *
 * | Data | Source | File/Path |
 * |------|--------|-----------|
 * | Topics with messages | Dexie `topics` table | `topics.json` → `{ id, messages[] }` |
 * | Message blocks | Dexie `message_blocks` table | `message_blocks.json` |
 * | Assistants (for meta) | Redux `assistants` slice | `ReduxStateReader.getCategory('assistants')` |
 *
 * ## Target Tables
 *
 * - `topicTable` - Stores conversation topics/threads
 * - `messageTable` - Stores chat messages with tree structure
 *
 * ## Key Transformations
 *
 * 1. **Linear → Tree Structure**
 *    - Old: Messages stored as linear array in `topic.messages[]`
 *    - New: Tree via `parentId` + `siblingsGroupId`
 *
 * 2. **Multi-model Responses**
 *    - Old: `askId` links responses to user message, `foldSelected` marks active
 *    - New: Shared `parentId` + non-zero `siblingsGroupId` groups siblings
 *
 * 3. **Block → Parts**
 *    - Old: `message.blocks: string[]` (IDs) + separate `message_blocks` table
 *    - New: `message.data.parts` (AI SDK UIMessage parts, inline JSON)
 *
 * 4. **Citation Migration**
 *    - Old: Separate `CitationMessageBlock`
 *    - New: Merged into `MainTextBlock.references` as ContentReference[]
 *
 * 5. **Mentions Dropped**
 *    - Old: `message.mentions: Model[]`
 *    - New: Not migrated — derivable from sibling responses' modelId + siblingsGroupId
 *
 * ## `chat_message_file_ref` backfill
 *
 * v1 image/file blocks reference v1 files via `block.file.id`. Those ids
 * survive into v2 as `FileUIPart.providerMetadata.cherry.fileEntryId` (inline
 * JSON on `messageTable.data.parts`), populated by ChatMappings during the
 * image/file mapping. This migrator also creates `chat_message_file_ref` rows
 * (`sourceId=messageId`, `role='attachment'`) for each distinct (message,
 * fileId) pair referencing an existing `file_entry`. Dangling refs (fileId not
 * in `file_entry`) are skipped with warnings.
 *
 * ## Performance Considerations
 *
 * - Uses streaming JSON reader for large data sets (potentially millions of messages)
 * - Processes topics in batches to control memory usage
 * - Pre-loads all blocks into memory map for O(1) lookup (blocks table is smaller)
 * - Uses database transactions for atomicity and performance
 *
 * @since v2.0.0
 */

import { fileEntryTable } from '@data/db/schemas/file'
import { chatMessageFileRefTable } from '@data/db/schemas/fileRelations'
import { messageTable } from '@data/db/schemas/message'
import { pinTable } from '@data/db/schemas/pin'
import { topicTable } from '@data/db/schemas/topic'
import { userModelTable } from '@data/db/schemas/userModel'
import { loggerService } from '@logger'
import type { ExecuteResult, PrepareResult, ValidateResult, ValidationError } from '@shared/data/migration/v2/types'
import type { CherryMessagePart } from '@shared/data/types/message'
import { readCherryMeta } from '@shared/data/types/uiParts'
import { eq, inArray, sql } from 'drizzle-orm'
import { v4 as uuidv4 } from 'uuid'

import type { MigrationContext } from '../core/MigrationContext'
import { assignOrderKeysByScope, assignOrderKeysInSequence } from '../utils/orderKey'
import { BaseMigrator } from './BaseMigrator'
import {
  buildBlockLookup,
  buildMessageTree,
  type ChatMappingDeps,
  findActiveNodeId,
  type NewMessage,
  type NewTopic,
  type OldAssistant,
  type OldBlock,
  type OldTopic,
  type OldTopicMeta,
  resolveBlocks,
  transformMessage,
  transformTopic
} from './mappings/ChatMappings'
import { resolveModelReference } from './transformers/ModelTransformers'

const logger = loggerService.withContext('ChatMigrator')

/**
 * Batch size for processing topics
 * Chosen to balance memory usage and transaction overhead
 */
const TOPIC_BATCH_SIZE = 50

/**
 * Batch size for inserting messages
 * SQLite has limits on the number of parameters per statement
 */
const MESSAGE_INSERT_BATCH_SIZE = 100

const FILE_REF_INSERT_BATCH_SIZE = 100
const SKIP_WARNING_SAMPLE_LIMIT = 10
const INARRAY_CHUNK = 500

/**
 * Yield each FileEntryId referenced by file parts in a message's parts array.
 * v1→v2 ChatMappings stashes `block.file.id` into `providerMetadata.cherry.fileEntryId`
 * during the image/file mapping; external (user-path) files have no fileEntryId.
 */
function* extractFileEntryIds(parts: CherryMessagePart[] | undefined): Iterable<string> {
  if (!parts) return
  for (const part of parts) {
    if (part.type !== 'file') continue
    const fileId = readCherryMeta(part)?.fileEntryId
    if (fileId) yield fileId
  }
}

/**
 * Build a topic's content-less virtual root row (`role = 'root'`, `parentId = null`).
 * Mirrors `MessageService.createRootMessageTx` so migrated topics match freshly created
 * ones. `createdAt` is the topic's creation time so the root sorts before its children.
 */
function buildVirtualRoot(id: string, topicId: string, createdAt: number): NewMessage {
  return {
    id,
    parentId: null,
    topicId,
    role: 'root',
    data: { parts: [] },
    searchableText: '',
    status: 'success',
    siblingsGroupId: 0,
    modelId: null,
    modelSnapshot: null,
    stats: null,
    createdAt,
    updatedAt: createdAt
  }
}

/**
 * Assistant data from Redux for assistant lookup. Both `assistants[]` and the
 * standalone `defaultAssistant` slot can carry topics under `.topics[]` —
 * iterating only `assistants[]` (the previous behavior) silently dropped every
 * topic that lived under the v1 default assistant.
 */
interface AssistantState {
  assistants: OldAssistant[]
  defaultAssistant?: OldAssistant
}

/**
 * Prepared data for execution phase. `pinned` carries the legacy `pinned`
 * flag from the source so the migrator can emit a corresponding `pin` row
 * (the polymorphic pin table replaces the old per-topic isPinned column).
 */
interface PreparedTopicData {
  topic: NewTopic
  messages: NewMessage[]
  pinned: boolean
}

export class ChatMigrator extends BaseMigrator {
  readonly id = 'chat'
  readonly name = 'ChatData'
  readonly description = 'Migrate chat topics and messages'
  readonly order = 4

  // Prepared data for execution
  private topicCount = 0
  private messageCount = 0
  private blockLookup: Map<string, OldBlock> = new Map()
  private assistantLookup: Map<string, OldAssistant> = new Map()
  // Topic metadata from Redux (name, pinned, etc.) - Dexie only has messages
  private topicMetaLookup: Map<string, OldTopicMeta> = new Map()
  // Topic → AssistantId mapping from Redux (Dexie topics don't store assistantId)
  private topicAssistantLookup: Map<string, string> = new Map()
  private skippedTopics = 0
  private skippedMessages = 0
  private orphanedAssistantTopics = 0
  // Valid assistant IDs from AssistantMigrator (for FK validation)
  private validAssistantIds: Set<string> | null = null
  // v1 → v2 id remap (e.g. legacy 'default' → UUID) from AssistantMigrator
  private legacyAssistantIdRemap: Map<string, string> = new Map()
  // Valid model IDs from ProviderModelMigrator/SQLite for FK validation
  private validModelIds: Set<string> | null = null
  // Block statistics for diagnostics
  private blockStats = { requested: 0, resolved: 0, messagesWithMissingBlocks: 0, messagesWithEmptyBlocks: 0 }
  // Count of messages promoted to root because no migrated ancestor was found
  private promotedToRootCount = 0
  // Buffered transformed topics across all streamed batches. Inserted in a
  // post-stream pass once orderKey can be assigned globally per groupId.
  private stagedTopics: PreparedTopicData[] = []
  // chat_message_file_ref backfill state
  private migratedFileEntryIds: Set<string> = new Set()
  private skippedWarnings: Map<string, { count: number; samples: string[] }> = new Map()
  private fileRefInsertCount = 0

  override reset(): void {
    this.topicCount = 0
    this.messageCount = 0
    this.blockLookup = new Map()
    this.assistantLookup = new Map()
    this.topicMetaLookup = new Map()
    this.topicAssistantLookup = new Map()
    this.skippedTopics = 0
    this.skippedMessages = 0
    this.orphanedAssistantTopics = 0
    this.blockStats = { requested: 0, resolved: 0, messagesWithMissingBlocks: 0, messagesWithEmptyBlocks: 0 }
    this.promotedToRootCount = 0
    this.validAssistantIds = null
    this.legacyAssistantIdRemap = new Map()
    this.validModelIds = null
    this.stagedTopics = []
    this.migratedFileEntryIds = new Set()
    this.skippedWarnings = new Map()
    this.fileRefInsertCount = 0
  }

  /**
   * Prepare phase - validate source data and count items
   *
   * Steps:
   * 1. Check if topics.json and message_blocks.json exist
   * 2. Load all blocks into memory for fast lookup
   * 3. Load assistant data for generating meta
   * 4. Count topics and estimate message count
   * 5. Validate sample data for integrity
   */

  private sanitizeMessageModelReferences(messages: NewMessage[]): number {
    let droppedModelRefs = 0

    for (const message of messages) {
      const resolution = resolveModelReference(message.modelId ?? null, this.validModelIds)
      if (resolution.kind === 'resolved') {
        message.modelId = resolution.modelId
        continue
      }

      if (resolution.kind === 'dangling') {
        droppedModelRefs += 1
        logger.warn(`Dropping dangling message model ref: message=${message.id}, model=${resolution.modelId}`)
      }

      message.modelId = null
    }

    return droppedModelRefs
  }

  async prepare(ctx: MigrationContext): Promise<PrepareResult> {
    const warnings: string[] = []

    try {
      // Step 1: Verify export files exist
      const topicsExist = await ctx.sources.dexieExport.tableExists('topics')
      if (!topicsExist) {
        logger.warn('topics.json not found, skipping chat migration')
        return {
          success: true,
          itemCount: 0,
          warnings: ['topics.json not found - no chat data to migrate']
        }
      }

      const blocksExist = await ctx.sources.dexieExport.tableExists('message_blocks')
      if (!blocksExist) {
        warnings.push('message_blocks.json not found - messages will have empty blocks')
      }

      // Step 2: Load all blocks into lookup map
      // Blocks table is typically smaller than messages, safe to load entirely
      if (blocksExist) {
        logger.info('Loading message blocks into memory...')
        const blocks = await ctx.sources.dexieExport.readTable<OldBlock>('message_blocks')
        this.blockLookup = buildBlockLookup(blocks)
        logger.info(`Loaded ${this.blockLookup.size} blocks into lookup map`)
      }

      // Step 3: Load assistant data for model lookup
      // Also extract topic metadata from assistants (Redux stores topic metadata in assistants.topics[]).
      // `state.defaultAssistant` is a sibling slot (not inside `assistants[]`) and
      // can also carry topics — must be visited too, otherwise its topics show
      // up post-migration as "Unnamed Topic" with no timestamp source.
      const assistantState = ctx.sources.reduxState.getCategory<AssistantState>('assistants')
      const allAssistants: OldAssistant[] = []
      if (assistantState?.assistants) allAssistants.push(...assistantState.assistants)
      if (assistantState?.defaultAssistant) allAssistants.push(assistantState.defaultAssistant)

      // AssistantMigrator remapped legacy 'default' to a UUID; replay the same
      // remap on every reference we read out of v1 so topicAssistantLookup
      // points at the new id (else the FK whitelist check below would orphan
      // every default-assistant topic).
      this.legacyAssistantIdRemap = (ctx.sharedData.get('legacyAssistantIdRemap') as Map<string, string>) ?? new Map()
      const remapAssistantId = (raw: string): string => this.legacyAssistantIdRemap.get(raw) ?? raw

      if (allAssistants.length > 0) {
        for (const assistant of allAssistants) {
          const remappedId = remapAssistantId(assistant.id)
          this.assistantLookup.set(remappedId, assistant)

          // Extract topic metadata from this assistant's topics array
          // Redux stores topic metadata (name, pinned, etc.) but with messages: []
          // Also track topic → assistantId mapping (Dexie doesn't store assistantId)
          // First-write-wins so primary slot (assistants[0]) keeps its meta when
          // the same topic.id appears under defaultAssistant — mirrors AssistantMigrator's
          // primary-wins merge contract.
          if (assistant.topics && Array.isArray(assistant.topics)) {
            for (const topic of assistant.topics) {
              if (topic.id && !this.topicMetaLookup.has(topic.id)) {
                this.topicMetaLookup.set(topic.id, topic)
                this.topicAssistantLookup.set(topic.id, remappedId)
              }
            }
          }
        }
        logger.info(
          `Loaded ${this.assistantLookup.size} assistants and ${this.topicMetaLookup.size} topic metadata entries`
        )
      } else {
        warnings.push('No assistant data found - topics will have null assistantId and missing names')
      }

      // Step 4: Count topics and estimate messages
      const topicReader = ctx.sources.dexieExport.createStreamReader('topics')
      this.topicCount = await topicReader.count()
      logger.info(`Found ${this.topicCount} topics to migrate`)

      // Estimate message count from sample
      if (this.topicCount > 0) {
        const sampleTopics = await topicReader.readSample<OldTopic>(10)
        const avgMessagesPerTopic =
          sampleTopics.reduce((sum, t) => sum + (t.messages?.length || 0), 0) / sampleTopics.length
        this.messageCount = Math.round(this.topicCount * avgMessagesPerTopic)
        logger.info(`Estimated ${this.messageCount} messages based on sample`)
      }

      // Step 5: Validate sample data
      if (this.topicCount > 0) {
        const sampleTopics = await topicReader.readSample<OldTopic>(5)
        for (const topic of sampleTopics) {
          if (!topic.id) {
            warnings.push(`Found topic without id - will be skipped`)
          }
          if (!topic.messages || !Array.isArray(topic.messages)) {
            warnings.push(`Topic ${topic.id} has invalid messages array`)
          }
        }
      }

      logger.info('Prepare phase completed', {
        topics: this.topicCount,
        estimatedMessages: this.messageCount,
        blocks: this.blockLookup.size,
        assistants: this.assistantLookup.size
      })

      return {
        success: true,
        itemCount: this.topicCount,
        warnings: warnings.length > 0 ? warnings : undefined
      }
    } catch (error) {
      logger.error('Prepare failed', error as Error)
      return {
        success: false,
        itemCount: 0,
        warnings: [error instanceof Error ? error.message : String(error)]
      }
    }
  }

  /**
   * Execute phase - perform the actual data migration
   *
   * Processing strategy:
   * 1. Stream topics in batches to control memory
   * 2. For each topic batch:
   *    a. Transform topics and their messages
   *    b. Build message tree structure
   *    c. Insert topics in single transaction
   *    d. Insert messages in batched transactions
   * 3. Report progress throughout
   */
  async execute(ctx: MigrationContext): Promise<ExecuteResult> {
    if (this.topicCount === 0) {
      logger.info('No topics to migrate')
      return { success: true, processedCount: 0 }
    }

    let processedTopics = 0
    let processedMessages = 0

    try {
      const topicReader = ctx.sources.dexieExport.createStreamReader('topics')

      const sharedAssistantIds = (ctx.sharedData.get('assistantIds') as Set<string>) ?? null
      if (!sharedAssistantIds) {
        throw new Error('validAssistantIds not set in sharedData. AssistantMigrator must run before ChatMigrator.')
      }
      // Defensive clone — v2 has no system-reserved 'default' row, so the set
      // is exactly the migrated user assistants (legacy 'default' appears here
      // under its remapped UUID, not under the literal 'default').
      this.validAssistantIds = new Set(sharedAssistantIds)
      this.validModelIds = ctx.db?.select
        ? new Set((await ctx.db.select({ id: userModelTable.id }).from(userModelTable)).map((row) => row.id))
        : null

      // ChatMappings promotes any v1 inline base64 (block.url=data: or
      // legacy metadata.generateImageResponse.images) into v2 file_entry
      // rows during transformMessage — written through the migration's
      // own DB handle, *not* through `application.get('FileManager')`:
      // migration runs in preboot, before any `WhenReady` service is up.
      const mappingDeps: ChatMappingDeps = { db: ctx.db, filesDataDir: ctx.paths.filesDataDir }

      // Buffer all topics first; orderKey is stamped post-stream because per-batch
      // keys would collide across batches sharing a `groupId` partition.
      await topicReader.readInBatches<OldTopic>(TOPIC_BATCH_SIZE, async (topics, batchIndex) => {
        logger.debug(`Processing topic batch ${batchIndex + 1}`, { count: topics.length })

        for (const oldTopic of topics) {
          try {
            const prepared = await this.prepareTopicData(oldTopic, mappingDeps)
            if (prepared) {
              this.stagedTopics.push(prepared)
            } else {
              this.skippedTopics++
            }
          } catch (error) {
            logger.error('Failed to transform topic', error as Error, {
              topicId: oldTopic.id,
              batchIndex,
              messageCount: oldTopic.messages?.length ?? 0,
              assistantId: oldTopic.assistantId
            })
            this.skippedTopics++
          }
        }

        // 0..50% during stream; insertStagedTopics covers 50..100%.
        const progress = Math.round((this.stagedTopics.length / this.topicCount) * 50)
        this.reportProgress(progress, `Prepared ${this.stagedTopics.length}/${this.topicCount} conversations`, {
          key: 'migration.progress.prepared_chats',
          params: { processed: this.stagedTopics.length, total: this.topicCount }
        })
      })

      this.migratedFileEntryIds = await this.loadMigratedFileEntryIds(ctx)
      logger.info('Loaded migrated file entry IDs for chat_message_file_ref backfill', {
        referencedCount: this.migratedFileEntryIds.size
      })

      const insertResult = this.insertStagedTopics(ctx)
      processedTopics = insertResult.topicsInserted
      processedMessages = insertResult.messagesInserted
      const pinsInserted = insertResult.pinsInserted

      logger.info('Execute completed', {
        processedTopics,
        processedMessages,
        pinsInserted,
        skippedTopics: this.skippedTopics,
        skippedMessages: this.skippedMessages
      })

      // Log block statistics for diagnostics
      logger.info('Block migration statistics', {
        blocksRequested: this.blockStats.requested,
        blocksResolved: this.blockStats.resolved,
        blocksMissing: this.blockStats.requested - this.blockStats.resolved,
        messagesWithEmptyBlocks: this.blockStats.messagesWithEmptyBlocks,
        messagesWithMissingBlocks: this.blockStats.messagesWithMissingBlocks
      })

      if (this.fileRefInsertCount > 0 || this.skippedWarnings.size > 0) {
        logger.info('File ref backfill statistics', {
          fileRefsInserted: this.fileRefInsertCount,
          skippedWarnings: Object.fromEntries(
            [...this.skippedWarnings.entries()].map(([k, v]) => [k, { count: v.count, samples: v.samples }])
          )
        })
      }

      return {
        success: true,
        processedCount: processedTopics
      }
    } catch (error) {
      logger.error('Execute failed', error as Error)
      return {
        success: false,
        processedCount: processedTopics,
        error: error instanceof Error ? error.message : String(error)
      }
    }
  }

  /**
   * Validate phase - verify migrated data integrity
   *
   * Validation checks:
   * 1. Topic count matches source (minus skipped)
   * 2. Message count is within expected range
   * 3. Sample topics have correct structure
   * 4. Foreign key integrity (messages belong to existing topics)
   */
  async validate(ctx: MigrationContext): Promise<ValidateResult> {
    const errors: ValidationError[] = []
    const db = ctx.db

    try {
      // Count topics in target
      const topicResult = db.select({ count: sql<number>`count(*)` }).from(topicTable).get()
      const targetTopicCount = topicResult?.count ?? 0

      // Count messages in target
      const messageResult = db.select({ count: sql<number>`count(*)` }).from(messageTable).get()
      const targetMessageCount = messageResult?.count ?? 0

      logger.info('Validation counts', {
        sourceTopics: this.topicCount,
        targetTopics: targetTopicCount,
        skippedTopics: this.skippedTopics,
        targetMessages: targetMessageCount
      })

      // Validate topic count
      const expectedTopics = this.topicCount - this.skippedTopics
      if (targetTopicCount < expectedTopics) {
        errors.push({
          key: 'topic_count_low',
          message: `Topic count too low: expected ${expectedTopics}, got ${targetTopicCount}`
        })
      } else if (targetTopicCount > expectedTopics) {
        // More topics than expected could indicate duplicate insertions or data corruption
        logger.warn(`Topic count higher than expected: expected ${expectedTopics}, got ${targetTopicCount}`)
      }

      const expectedPins = this.stagedTopics.filter((d) => d.pinned).length
      if (expectedPins > 0) {
        const pinResult = db
          .select({ count: sql<number>`count(*)` })
          .from(pinTable)
          .where(eq(pinTable.entityType, 'topic'))
          .get()
        const targetPinCount = pinResult?.count ?? 0
        if (targetPinCount < expectedPins) {
          errors.push({
            key: 'pin_count_low',
            message: `Pin row count too low: expected ${expectedPins}, got ${targetPinCount}`
          })
        }
      }

      // Sample validation: check a few topics have messages
      const sampleTopics = db.select().from(topicTable).limit(5).all()
      for (const topic of sampleTopics) {
        const msgCount = db
          .select({ count: sql<number>`count(*)` })
          .from(messageTable)
          .where(eq(messageTable.topicId, topic.id))
          .get()

        if (msgCount?.count === 0) {
          // This is a warning, not an error - some topics may legitimately have no messages
          logger.warn(`Topic ${topic.id} has no messages after migration`)
        }
      }

      // Check for orphan messages (messages without valid topic)
      // This shouldn't happen due to foreign key constraints, but verify anyway
      const orphanCheck = db
        .select({ count: sql<number>`count(*)` })
        .from(messageTable)
        .where(sql`${messageTable.topicId} NOT IN (SELECT id FROM ${topicTable})`)
        .get()

      if (orphanCheck && orphanCheck.count > 0) {
        errors.push({
          key: 'orphan_messages',
          message: `Found ${orphanCheck.count} orphan messages without valid topics`
        })
      }

      // Check for dangling parentId references (parentId points to non-existent message)
      const danglingParentCheck = db
        .select({ count: sql<number>`count(*)` })
        .from(messageTable)
        .where(
          sql`${messageTable.parentId} IS NOT NULL AND ${messageTable.parentId} NOT IN (SELECT id FROM ${messageTable})`
        )
        .get()

      if (danglingParentCheck && danglingParentCheck.count > 0) {
        errors.push({
          key: 'dangling_parent_ids',
          message: `Found ${danglingParentCheck.count} messages with dangling parentId`
        })
      }

      // Warn-only (not pushed to errors): unlike topic/pin counts which compare
      // across data sources (v1 Dexie → v2 SQLite), this is a same-DB self-check
      // ("rows I committed are still there"). A mismatch implies an infrastructure
      // fault (WAL loss, CASCADE from an unexpected file_entry delete), not a
      // migration logic bug — so it warrants investigation, not migration abort.
      if (this.fileRefInsertCount > 0) {
        const fileRefResult = db.select({ count: sql<number>`count(*)` }).from(chatMessageFileRefTable).get()
        const targetFileRefCount = fileRefResult?.count ?? 0
        if (targetFileRefCount < this.fileRefInsertCount) {
          logger.warn(
            `chat_message_file_ref count mismatch: expected ${this.fileRefInsertCount}, got ${targetFileRefCount}`
          )
        }
      }

      // Invariant check: each topic must have exactly one virtual root (parentId IS NULL).
      // The migrator inserts one per topic and reparents former physical roots onto it,
      // so >1 here means a bug (the message_topic_root_uniq index would also reject it).
      const multiRootCheck = db
        .select({ count: sql<number>`count(*)` })
        .from(sql`(SELECT topic_id FROM ${messageTable} WHERE parent_id IS NULL GROUP BY topic_id HAVING count(*) > 1)`)
        .get()

      if (multiRootCheck && multiRootCheck.count > 0) {
        logger.warn(`Found ${multiRootCheck.count} topics with multiple root messages (multi-root forest)`)
        errors.push({
          key: 'multi_root_topics',
          message: `Found ${multiRootCheck.count} topics with multiple root messages`
        })
      }

      // Strong signal that AssistantMigrator dropped most of its rows or that
      // source data has shifted underfoot — surfaces before user notices every
      // topic stranded with NULL assistantId.
      if (this.topicCount > 0 && this.orphanedAssistantTopics / this.topicCount > 0.5) {
        logger.warn(
          `High orphan-assistant ratio: ${this.orphanedAssistantTopics}/${this.topicCount} topics had no resolvable assistant (assistantId=NULL)`
        )
      }

      const diagnostics = {
        skippedMessages: this.skippedMessages,
        orphanedAssistantTopics: this.orphanedAssistantTopics,
        messagesWithMissingBlocks: this.blockStats.messagesWithMissingBlocks,
        messagesWithEmptyBlocks: this.blockStats.messagesWithEmptyBlocks,
        promotedToRootCount: this.promotedToRootCount,
        fileRefsInserted: this.fileRefInsertCount,
        fileRefsDanglingSkipped: this.skippedWarnings.get('chat_message_dangling_file_entry')?.count ?? 0
      }
      logger.info('Validation diagnostics', diagnostics)

      return {
        success: errors.length === 0,
        errors,
        stats: {
          sourceCount: this.topicCount,
          targetCount: targetTopicCount,
          skippedCount: this.skippedTopics
        },
        diagnostics
      }
    } catch (error) {
      logger.error('Validation failed', error as Error)
      return {
        success: false,
        errors: [
          {
            key: 'validation',
            message: error instanceof Error ? error.message : String(error)
          }
        ],
        stats: {
          sourceCount: this.topicCount,
          targetCount: 0,
          skippedCount: this.skippedTopics
        }
      }
    }
  }

  private recordSkippedWarning(reason: string, message: string): void {
    const bucket = this.skippedWarnings.get(reason) ?? { count: 0, samples: [] }
    bucket.count += 1
    if (bucket.samples.length < SKIP_WARNING_SAMPLE_LIMIT) {
      bucket.samples.push(message)
    }
    this.skippedWarnings.set(reason, bucket)
  }

  private async loadMigratedFileEntryIds(ctx: MigrationContext): Promise<Set<string>> {
    const referencedFileIds = new Set<string>()
    for (const data of this.stagedTopics) {
      for (const msg of data.messages) {
        for (const fileId of extractFileEntryIds(msg.data?.parts)) {
          referencedFileIds.add(fileId)
        }
      }
    }
    if (referencedFileIds.size === 0) return new Set()
    const allIds = [...referencedFileIds]
    const result = new Set<string>()
    for (let i = 0; i < allIds.length; i += INARRAY_CHUNK) {
      const chunk = allIds.slice(i, i + INARRAY_CHUNK)
      try {
        const rows = await ctx.db
          .select({ id: fileEntryTable.id })
          .from(fileEntryTable)
          .where(inArray(fileEntryTable.id, chunk))
        for (const row of rows) result.add(row.id)
      } catch (err) {
        logger.error('Failed to query file_entry during chat_message_file_ref backfill', err as Error, {
          chunkStart: i,
          chunkSize: chunk.length,
          totalReferencedIds: allIds.length
        })
        throw err
      }
    }
    return result
  }

  private collectFileRefRows(
    batchMessages: NewMessage[],
    now: number
  ): Array<typeof chatMessageFileRefTable.$inferInsert> {
    const rows: Array<typeof chatMessageFileRefTable.$inferInsert> = []
    for (const msg of batchMessages) {
      const dedupKey = new Set<string>()
      for (const fileId of extractFileEntryIds(msg.data?.parts)) {
        if (!this.migratedFileEntryIds.has(fileId)) {
          this.recordSkippedWarning(
            'chat_message_dangling_file_entry',
            `Message id=${msg.id} references file_entry id=${fileId} which is absent from v2 file_entry`
          )
          continue
        }
        const compositeKey = `${msg.id}:${fileId}`
        if (dedupKey.has(compositeKey)) continue
        dedupKey.add(compositeKey)
        rows.push({
          id: uuidv4(),
          fileEntryId: fileId,
          sourceId: msg.id,
          role: 'attachment',
          createdAt: now,
          updatedAt: now
        })
      }
    }
    return rows
  }

  /**
   * Prepare a single topic and its messages. See README-ChatMigrator.md for the
   * source layout (Dexie topic rows + Redux topic metadata + defaultAssistant slot)
   * and the merge contract.
   */
  private async prepareTopicData(oldTopic: OldTopic, deps?: ChatMappingDeps): Promise<PreparedTopicData | null> {
    // Validate required fields
    if (!oldTopic.id) {
      logger.error('Topic missing id, skipping', new Error('missing topic id'), {
        messageCount: oldTopic.messages?.length ?? 0,
        assistantId: oldTopic.assistantId
      })
      return null
    }

    // Merge Redux meta first so the empty-topic skip below can see user-intent flags.
    const topicMeta = this.topicMetaLookup.get(oldTopic.id)
    if (topicMeta) {
      // Redux topic.name can be empty from ancient migrations (see store/migrate.ts:303-305).
      oldTopic.name = topicMeta.name || oldTopic.name
      oldTopic.pinned = topicMeta.pinned ?? oldTopic.pinned
      oldTopic.prompt = topicMeta.prompt ?? oldTopic.prompt
      oldTopic.isNameManuallyEdited = topicMeta.isNameManuallyEdited ?? oldTopic.isNameManuallyEdited
      if (topicMeta.createdAt && !oldTopic.createdAt) oldTopic.createdAt = topicMeta.createdAt
      if (topicMeta.updatedAt && !oldTopic.updatedAt) oldTopic.updatedAt = topicMeta.updatedAt
    }

    // Drop empty topics (abandoned "new topic" clicks). `name` is not a signal — v1 auto-names on creation.
    const hasMessages = Array.isArray(oldTopic.messages) && oldTopic.messages.length > 0
    const hasUserIntent = Boolean(
      oldTopic.pinned || oldTopic.isNameManuallyEdited || (oldTopic.prompt && oldTopic.prompt.trim())
    )
    if (!hasMessages && !hasUserIntent) {
      logger.info('Skipping empty topic (no messages, no user-intent metadata)', { topicId: oldTopic.id })
      return null
    }

    if (!oldTopic.name) {
      // TODO: i18n
      oldTopic.name = 'Unnamed Topic'
    }

    // Without this, parseTimestamp() falls back to Date.now() and stamps every
    // missing-timestamp topic with the migration moment.
    if (!oldTopic.createdAt || !oldTopic.updatedAt) {
      // Older v1 versions stored createdAt as numeric epoch-ms; Date.parse returns NaN on those.
      const toMillis = (createdAt: unknown): number => {
        if (typeof createdAt === 'number' && Number.isFinite(createdAt)) return createdAt
        if (typeof createdAt === 'string' && createdAt.length > 0) return Date.parse(createdAt)
        return NaN
      }
      const messageMillis = (oldTopic.messages ?? [])
        .map((m) => toMillis(m.createdAt))
        .filter((t) => Number.isFinite(t))
      if (messageMillis.length > 0) {
        if (!oldTopic.createdAt) {
          oldTopic.createdAt = new Date(Math.min(...messageMillis)).toISOString()
        }
        if (!oldTopic.updatedAt) {
          oldTopic.updatedAt = new Date(Math.max(...messageMillis)).toISOString()
        }
      } else {
        logger.warn('Topic has no derivable timestamp source, falling back to Date.now()', {
          topicId: oldTopic.id,
          messageCount: oldTopic.messages?.length ?? 0
        })
      }
    }

    // Resolve topic.assistantId. v2 has no system-reserved 'default' row;
    // any unresolved reference becomes NULL and the renderer composes a
    // runtime default from Preference. Both orphan branches (no source id /
    // dangling FK) bump the counter so the >50% diagnostic catches users with
    // mass-orphaned topics. Legacy 'default' from Dexie is replayed through
    // the AssistantMigrator id remap before the FK whitelist check, so a
    // migrated v1 default still resolves under its new UUID.
    const lookupHit = this.topicAssistantLookup.get(oldTopic.id) || oldTopic.assistantId
    const sourceAssistantId = lookupHit ? (this.legacyAssistantIdRemap.get(lookupHit) ?? lookupHit) : lookupHit
    let resolvedAssistantId: string | null
    if (!sourceAssistantId) {
      resolvedAssistantId = null
      this.orphanedAssistantTopics++
    } else if (this.validAssistantIds && !this.validAssistantIds.has(sourceAssistantId)) {
      logger.warn(`Topic ${oldTopic.id}: assistant ${sourceAssistantId} not in assistant table, setting NULL`)
      resolvedAssistantId = null
      this.orphanedAssistantTopics++
    } else {
      resolvedAssistantId = sourceAssistantId
    }

    // Write resolved value back for transformTopic consumption. transformTopic
    // converts falsy to NULL, so empty string here yields the desired NULL FK.
    oldTopic.assistantId = resolvedAssistantId ?? ''

    // Get messages array (may be empty or undefined)
    const oldMessages = oldTopic.messages || []

    // Build message tree structure
    const messageTree = buildMessageTree(oldMessages)

    // === First pass: identify messages to skip (no blocks) ===
    const skippedMessageIds = new Set<string>()
    const messageParentMap = new Map<string, string | null>() // messageId -> parentId

    for (const oldMsg of oldMessages) {
      const blockIds = oldMsg.blocks || []
      const blocks = resolveBlocks(blockIds, this.blockLookup)

      // Track block statistics for diagnostics
      this.blockStats.requested += blockIds.length
      this.blockStats.resolved += blocks.length
      if (blockIds.length === 0) {
        this.blockStats.messagesWithEmptyBlocks++
      } else if (blocks.length < blockIds.length) {
        this.blockStats.messagesWithMissingBlocks++
        if (blocks.length === 0) {
          logger.warn(`Message ${oldMsg.id} has ${blockIds.length} block IDs but none found in message_blocks`)
        }
      }

      // Store parent info from tree
      const treeInfo = messageTree.get(oldMsg.id)
      messageParentMap.set(oldMsg.id, treeInfo?.parentId ?? null)

      // Mark for skipping if no blocks
      if (blocks.length === 0) {
        skippedMessageIds.add(oldMsg.id)
        this.skippedMessages++
      }
    }

    // === Helper: resolve parent through skipped messages ===
    // If parentId points to a skipped message, follow the chain to find a non-skipped ancestor
    const resolveParentId = (parentId: string | null): string | null => {
      let currentParent = parentId
      const visited = new Set<string>() // Prevent infinite loops

      while (currentParent && skippedMessageIds.has(currentParent)) {
        if (visited.has(currentParent)) {
          // Circular reference, break out
          return null
        }
        visited.add(currentParent)
        currentParent = messageParentMap.get(currentParent) ?? null
      }

      return currentParent
    }

    // === Second pass: transform messages that have blocks ===
    const newMessages: NewMessage[] = []
    for (const oldMsg of oldMessages) {
      // Skip messages marked for skipping
      if (skippedMessageIds.has(oldMsg.id)) {
        continue
      }

      try {
        const treeInfo = messageTree.get(oldMsg.id)
        if (!treeInfo) {
          logger.warn(`Message ${oldMsg.id} not found in tree, using defaults`)
          continue
        }

        // Resolve blocks for this message (we know it has blocks from first pass)
        const blockIds = oldMsg.blocks || []
        const blocks = resolveBlocks(blockIds, this.blockLookup)

        // Resolve parentId through any skipped messages
        const resolvedParentId = resolveParentId(treeInfo.parentId)

        const newMsg = await transformMessage(
          oldMsg,
          resolvedParentId, // Use resolved parent instead of original
          treeInfo.siblingsGroupId,
          blocks,
          oldTopic.id,
          deps
        )

        newMessages.push(newMsg)
      } catch (error) {
        logger.warn(`Failed to transform message ${oldMsg.id}`, { error })
        this.skippedMessages++
      }
    }

    // Fix dangling parentIds from second-pass skips (transform failure).
    // resolveParentId only handles first-pass skips; if a message passed the first
    // pass (had blocks) but failed transform, its children still reference it.
    // Walk the ancestor chain to find the nearest migrated parent.
    const migratedMessageIds = new Set(newMessages.map((m) => m.id))
    for (const msg of newMessages) {
      if (msg.parentId && !migratedMessageIds.has(msg.parentId)) {
        let ancestor = messageParentMap.get(msg.parentId) ?? null
        const visited = new Set<string>([msg.parentId])
        while (ancestor && !migratedMessageIds.has(ancestor)) {
          if (visited.has(ancestor)) break
          visited.add(ancestor)
          ancestor = messageParentMap.get(ancestor) ?? null
        }
        if (ancestor) {
          logger.warn(`Resolved dangling parentId for message ${msg.id}: ${msg.parentId} → ${ancestor}`)
        } else {
          logger.warn(
            `No migrated ancestor found for message ${msg.id} (original parentId: ${msg.parentId}), setting as root`
          )
          this.promotedToRootCount++
        }
        msg.parentId = ancestor
      }
    }

    // Calculate activeNodeId using smart selection logic
    // Priority: 1) Original activeNode if migrated, 2) foldSelected if migrated, 3) last migrated
    let activeNodeId: string | null = null
    if (newMessages.length > 0) {
      const migratedIds = new Set(newMessages.map((m) => m.id))

      // Try to use the original active node (handles foldSelected for multi-model)
      const originalActiveId = findActiveNodeId(oldMessages)
      if (originalActiveId && migratedIds.has(originalActiveId)) {
        activeNodeId = originalActiveId
      } else {
        // Original active was skipped; find a foldSelected among migrated messages
        const foldSelectedMsg = oldMessages.find((m) => m.foldSelected && migratedIds.has(m.id))
        if (foldSelectedMsg) {
          activeNodeId = foldSelectedMsg.id
        } else {
          // Fallback to last migrated message
          activeNodeId = newMessages[newMessages.length - 1].id
        }
      }
    }

    // Transform topic with correct activeNodeId
    const newTopic = transformTopic(oldTopic, activeNodeId)

    return {
      topic: newTopic,
      messages: newMessages,
      pinned: oldTopic.pinned ?? false
    }
  }

  /**
   * Post-stream insert pass: stamp orderKey, insert topics+messages with
   * FK toggling, emit pin rows for legacy `pinned: true` topics.
   */
  private insertStagedTopics(ctx: MigrationContext): {
    topicsInserted: number
    messagesInserted: number
    pinsInserted: number
  } {
    const db = ctx.db

    // Sort by updatedAt DESC so the stamped orderKey matches the default
    // unpinned list sort — otherwise drag-mode would see arbitrary order.
    const sortedTopics = [...this.stagedTopics]
      .sort((a, b) => b.topic.updatedAt - a.topic.updatedAt)
      .map((d) => d.topic)
    const stampedTopics = assignOrderKeysByScope(sortedTopics, (t) => t.groupId)
    const orderKeyById = new Map(stampedTopics.map((t) => [t.id, t.orderKey]))
    for (const data of this.stagedTopics) {
      const orderKey = orderKeyById.get(data.topic.id)
      if (!orderKey) {
        throw new Error(`orderKey lookup miss for topic id=${data.topic.id}`)
      }
      data.topic.orderKey = orderKey
    }

    let topicsInserted = 0
    let messagesInserted = 0
    const seenMessageIds = new Set<string>()
    const total = this.stagedTopics.length || 1

    for (let start = 0; start < this.stagedTopics.length; start += TOPIC_BATCH_SIZE) {
      const batch = this.stagedTopics.slice(start, start + TOPIC_BATCH_SIZE)

      // Dedupe message ids within the batch and against prior batches; remap
      // children's parentIds to keep the tree intact after the rename.
      const batchMessages: NewMessage[] = []
      const idRemap = new Map<string, string>()
      const batchIds = new Set<string>()
      for (const data of batch) {
        // Bring migrated topics into the virtual-root model: every topic gets one
        // content-less `role = 'root'` row (parentId = null), and the former physical
        // roots (parentId = null content messages) are reparented onto it. This makes
        // the single-root invariant and `role = 'root'` ⇔ `parentId IS NULL` hold for
        // migrated data exactly as for freshly created topics. Mirrors
        // MessageService.createRootMessageTx.
        const rootId = uuidv4()
        batchMessages.push(buildVirtualRoot(rootId, data.topic.id, data.topic.createdAt))
        for (const msg of data.messages) {
          if (msg.parentId === null) {
            msg.parentId = rootId
          }
          if (seenMessageIds.has(msg.id) || batchIds.has(msg.id)) {
            const newId = uuidv4()
            logger.warn(`Duplicate message ID found: ${msg.id}, assigning new ID: ${newId}`)
            idRemap.set(msg.id, newId)
            msg.id = newId
          }
          batchIds.add(msg.id)
          batchMessages.push(msg)
        }
      }
      if (idRemap.size > 0) {
        for (const msg of batchMessages) {
          if (msg.parentId && idRemap.has(msg.parentId)) {
            msg.parentId = idRemap.get(msg.parentId)!
          }
        }
      }
      const droppedRefs = this.sanitizeMessageModelReferences(batchMessages)
      if (droppedRefs > 0) logger.info(`Filtered ${droppedRefs} dangling message model references`)

      const now = Date.now()
      const batchFileRefRows = this.collectFileRefRows(batchMessages, now)

      // FK stays OFF for the whole migration (MigrationDbService sets the PRAGMA once on
      // its single connection), so this batch can insert self-referencing message.parentId
      // rows that resolve within the batch. assertOwnedForeignKeys() below verifies the result.
      db.transaction((tx) => {
        tx.insert(topicTable)
          .values(batch.map((d) => d.topic))
          .run()
        for (let i = 0; i < batchMessages.length; i += MESSAGE_INSERT_BATCH_SIZE) {
          tx.insert(messageTable)
            .values(batchMessages.slice(i, i + MESSAGE_INSERT_BATCH_SIZE))
            .run()
        }
        if (batchFileRefRows.length > 0) {
          for (let i = 0; i < batchFileRefRows.length; i += FILE_REF_INSERT_BATCH_SIZE) {
            tx.insert(chatMessageFileRefTable)
              .values(batchFileRefRows.slice(i, i + FILE_REF_INSERT_BATCH_SIZE))
              .run()
          }
        }
      })

      for (const id of batchIds) seenMessageIds.add(id)
      this.fileRefInsertCount += batchFileRefRows.length
      topicsInserted += batch.length
      messagesInserted += batchMessages.length

      const progress = 50 + Math.round((topicsInserted / total) * 50)
      this.reportProgress(
        progress,
        `Migrated ${topicsInserted}/${this.stagedTopics.length} conversations, ${messagesInserted} messages`,
        {
          key: 'migration.progress.migrated_chats',
          params: { processed: topicsInserted, total: this.stagedTopics.length, messages: messagesInserted }
        }
      )
    }

    // ON CONFLICT DO NOTHING so a retry doesn't trip the (entity_type, entity_id) UNIQUE.
    const pinned = this.stagedTopics.filter((d) => d.pinned)
    let pinsInserted = 0
    if (pinned.length > 0) {
      const sorted = [...pinned].sort((a, b) => b.topic.updatedAt - a.topic.updatedAt)
      const now = Date.now()
      const pinRows = assignOrderKeysInSequence(
        sorted.map((d) => ({
          id: uuidv4(),
          entityType: 'topic',
          entityId: d.topic.id,
          createdAt: now,
          updatedAt: now
        }))
      )
      try {
        // Counter assigned only on commit so the catch reports 0 on rollback.
        const inserted = db.transaction((tx) => {
          let count = 0
          for (let i = 0; i < pinRows.length; i += MESSAGE_INSERT_BATCH_SIZE) {
            const batch = pinRows.slice(i, i + MESSAGE_INSERT_BATCH_SIZE)
            const result = tx.insert(pinTable).values(batch).onConflictDoNothing().returning({ id: pinTable.id }).all()
            count += result.length
          }
          return count
        })
        pinsInserted = inserted
      } catch (error) {
        logger.error('Pin row emission failed (transaction rolled back)', error as Error, {
          pinsExpected: pinRows.length
        })
        throw error
      }
    }

    // Self-check FK integrity for the tables this migrator owns: topic.assistantId →
    // assistant (migrated at order 2), message.topicId / parentId / modelId, and
    // chat_message_file_ref.sourceId/fileEntryId all resolve by now.
    this.assertOwnedForeignKeys(db, [topicTable, messageTable, pinTable, chatMessageFileRefTable])

    return { topicsInserted, messagesInserted, pinsInserted }
  }
}
