import { promises as fs } from 'fs';
import path from 'path';
import { SQLiteManager } from './sqlite-manager.js';
import { randomUUID } from 'crypto';

export interface MigrationResult {
  success: boolean;
  error?: string;
  migratedItems: number;
  errors: string[];
  duration: number;
  backupPath?: string;
}

export interface MigrationStatus {
  needsMigration: boolean;
  isRequired: boolean;
  hasLegacyData: boolean;
  backupExists: boolean;
  lastMigration?: number;
}

/**
 * Data Migration Manager
 * Handles migration from file-based JSON storage to SQLite
 */
export class DataMigration {
  private db: SQLiteManager;
  private legacyDataPath: string;
  private backupPath: string;
  private useInternalMethods: boolean;

  constructor(db: SQLiteManager, dataPath = '.atlas', useInternalMethods = false) {
    this.db = db;
    this.legacyDataPath = path.join(dataPath, 'data');
    this.backupPath = path.join(dataPath, 'backup');
    this.useInternalMethods = useInternalMethods;
  }

  /**
   * Get data using appropriate method (internal during initialization, public otherwise)
   */
  private async dbGet<T>(sql: string, params: any[] = []): Promise<{ success: boolean; data?: T; error?: string }> {
    if (this.useInternalMethods) {
      return this.db.getForMigration<T>(sql, params);
    } else {
      return this.db.get<T>(sql, params);
    }
  }

  /**
   * Run query using appropriate method (internal during initialization, public otherwise)
   */
  private async dbRun(sql: string, params: any[] = []): Promise<{ success: boolean; error?: string }> {
    if (this.useInternalMethods) {
      const result = await this.db.runForMigration(sql, params);
      return { success: result.success, error: result.error };
    } else {
      const result = await this.db.run(sql, params);
      return { success: result.success, error: result.error };
    }
  }

  /**
   * Check if migration is needed
   */
  async checkMigrationStatus(): Promise<MigrationStatus> {
    try {
      // Check if legacy data exists
      const hasLegacyData = await this.hasLegacyData();
      
      // Check if backup exists
      const backupExists = await this.pathExists(this.backupPath);
      
      // Check if migration was already completed
      const migrationRecord = await this.dbGet<{ value: string }>(
        'SELECT value FROM atlas_metadata WHERE key = ?',
        ['migration_status']
      );
      
      const lastMigrationRecord = await this.dbGet<{ value: string }>(
        'SELECT value FROM atlas_metadata WHERE key = ?',
        ['last_migration_timestamp']
      );

      // Check if database actually has migrated data
      const hasMigratedData = await this.hasDataInDatabase();
      
      const isRequired = hasLegacyData && (!hasMigratedData || migrationRecord.data?.value !== 'completed');
      const lastMigration = lastMigrationRecord.data?.value ? 
        parseInt(lastMigrationRecord.data.value) : undefined;

      return {
        needsMigration: isRequired,
        isRequired,
        hasLegacyData,
        backupExists,
        lastMigration
      };
    } catch (error) {
      console.error('Failed to check migration status:', error);
      return {
        needsMigration: false,
        isRequired: false,
        hasLegacyData: false,
        backupExists: false
      };
    }
  }

  /**
   * Perform the migration
   */
  async migrate(): Promise<MigrationResult> {
    const startTime = Date.now();
    const errors: string[] = [];
    let migratedItems = 0;
    let backupPath: string | undefined;

    try {
      console.error('🔄 Starting data migration from JSON files to SQLite...');

      // Disable foreign key constraints during migration
      await this.dbRun('PRAGMA foreign_keys = OFF');
      console.error('🔧 Disabled foreign key constraints for migration');

      // Create backup first
      backupPath = await this.createBackup();
      console.error(`📦 Backup created at: ${backupPath}`);

      // Migrate only active modules (skip deprecated/removed modules)
      // Core project data
      migratedItems += await this.migrateProjectData(errors);
      
      // Active 12-factor modules
      migratedItems += await this.migrateAgileData(errors);
      migratedItems += await this.migrateKanbanData(errors);
      migratedItems += await this.migrateDocumentationData(errors);
      migratedItems += await this.migrateMemoryData(errors);
      migratedItems += await this.migrateADRData(errors);
      migratedItems += await this.migrateDevelopmentData(errors);
      migratedItems += await this.migrateWorkspaceData(errors);
      migratedItems += await this.migrateProcessAutomationData(errors);
      migratedItems += await this.migrateProductRequirementsData(errors);
      migratedItems += await this.migrateDataManagementData(errors);
      migratedItems += await this.migrateRAGData(errors);
      
      // Skip deprecated/removed modules:
      console.error('⚠️  Skipping deprecated modules: approval-wrapper, deployment-management, deprecation, interaction-framework, memory, repo-setup, smart-estimation, workflow-recipes');
      console.error('⚠️  Skipping inactive modules: business-guidance, code-analysis, error-analysis, issue-tracking, product-roadmap, testing-framework, performance-monitoring, security');

      // Re-enable foreign key constraints
      await this.dbRun('PRAGMA foreign_keys = ON');
      console.log('🔧 Re-enabled foreign key constraints');

      // Update migration status
      await this.updateMigrationStatus('completed');

      const duration = Date.now() - startTime;
      console.log(`✅ Migration completed in ${duration}ms. Migrated ${migratedItems} items.`);

      return {
        success: true,
        migratedItems,
        errors,
        duration,
        backupPath
      };

    } catch (error) {
      // Re-enable foreign key constraints even on error
      try {
        await this.dbRun('PRAGMA foreign_keys = ON');
        console.log('🔧 Re-enabled foreign key constraints after error');
      } catch (pragmaError) {
        console.warn('⚠️ Failed to re-enable foreign key constraints:', pragmaError);
      }

      const duration = Date.now() - startTime;
      const errorMessage = error instanceof Error ? error.message : 'Unknown error';
      errors.push(`Migration failed: ${errorMessage}`);

      console.error('❌ Migration failed:', error);

      return {
        success: false,
        migratedItems,
        errors,
        duration,
        backupPath
      };
    }
  }

  /**
   * Create backup of existing data
   */
  private async createBackup(): Promise<string> {
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const backupDir = path.join(this.backupPath, `backup-${timestamp}`);
    
    await fs.mkdir(backupDir, { recursive: true });

    // Copy all JSON files to backup
    const files = await this.findJsonFiles(this.legacyDataPath);
    
    for (const file of files) {
      const relativePath = path.relative(this.legacyDataPath, file);
      const backupFile = path.join(backupDir, relativePath);
      
      await fs.mkdir(path.dirname(backupFile), { recursive: true });
      await fs.copyFile(file, backupFile);
    }

    return backupDir;
  }

  /**
   * Migrate agile management data
   */
  private async migrateAgileData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Ensure default project exists to avoid foreign key constraints
      await this.ensureDefaultProject();
      console.log('✅ Default project ensured for agile data');

      // Migrate sprints
      const sprintsFile = path.join(this.legacyDataPath, 'agile', 'sprints.json');
      if (await this.pathExists(sprintsFile)) {
        const sprints = await this.readJsonFile(sprintsFile, errors);
        if (Array.isArray(sprints)) {
          for (const sprint of sprints) {
            await this.migrateSprint(sprint);
            count++;
          }
        }
      }

      // Migrate stories
      const storiesFile = path.join(this.legacyDataPath, 'agile', 'stories.json');
      if (await this.pathExists(storiesFile)) {
        const stories = await this.readJsonFile(storiesFile, errors);
        if (Array.isArray(stories)) {
          for (const story of stories) {
            await this.migrateStory(story);
            count++;
          }
        }
      }

      // Migrate epics
      const epicsFile = path.join(this.legacyDataPath, 'agile', 'epics.json');
      if (await this.pathExists(epicsFile)) {
        const epics = await this.readJsonFile(epicsFile, errors);
        if (Array.isArray(epics)) {
          for (const epic of epics) {
            await this.migrateEpic(epic);
            count++;
          }
        }
      }

    } catch (error) {
      errors.push(`Agile data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate kanban data
   */
  private async migrateKanbanData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const boardsFile = path.join(this.legacyDataPath, 'kanban', 'boards.json');
      if (await this.pathExists(boardsFile)) {
        const boards = await this.readJsonFile(boardsFile);
        if (Array.isArray(boards)) {
          for (const board of boards) {
            await this.migrateKanbanBoard(board);
            count++;
          }
        }
      }
    } catch (error) {
      errors.push(`Kanban data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate documentation data
   */
  private async migrateDocumentationData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const docsFile = path.join(this.legacyDataPath, 'documentation', 'documents.json');
      if (await this.pathExists(docsFile)) {
        const docs = await this.readJsonFile(docsFile);
        if (Array.isArray(docs)) {
          for (const doc of docs) {
            await this.migrateDocument(doc);
            count++;
          }
        }
      }
    } catch (error) {
      errors.push(`Documentation migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate business guidance data
   */
  private async migrateBusinessData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Migrate business plans
      const businessFile = path.join(this.legacyDataPath, 'business', 'plans.json');
      if (await this.pathExists(businessFile)) {
        const plans = await this.readJsonFile(businessFile);
        if (Array.isArray(plans)) {
          for (const plan of plans) {
            await this.migrateBusinessPlan(plan);
            count++;
          }
        }
      }

      // Migrate market analyses
      const marketAnalysesFile = path.join(this.legacyDataPath, 'business', 'market-analyses.json');
      if (await this.pathExists(marketAnalysesFile)) {
        const analyses = await this.readJsonFile(marketAnalysesFile);
        if (Array.isArray(analyses)) {
          for (const analysis of analyses) {
            await this.migrateMarketAnalysis(analysis);
            count++;
          }
        }
      }

      // Migrate competitor analyses
      const competitorFile = path.join(this.legacyDataPath, 'business', 'competitor-analyses.json');
      if (await this.pathExists(competitorFile)) {
        const analyses = await this.readJsonFile(competitorFile);
        if (Array.isArray(analyses)) {
          for (const analysis of analyses) {
            await this.migrateCompetitorAnalysis(analysis);
            count++;
          }
        }
      }

      // Migrate financial projections
      const financialFile = path.join(this.legacyDataPath, 'business', 'financial-projections.json');
      if (await this.pathExists(financialFile)) {
        const projections = await this.readJsonFile(financialFile);
        if (Array.isArray(projections)) {
          for (const projection of projections) {
            await this.migrateFinancialProjection(projection);
            count++;
          }
        }
      }

      // Migrate startup assessments
      const assessmentFile = path.join(this.legacyDataPath, 'business', 'startup-assessments.json');
      if (await this.pathExists(assessmentFile)) {
        const assessments = await this.readJsonFile(assessmentFile);
        if (Array.isArray(assessments)) {
          for (const assessment of assessments) {
            await this.migrateStartupAssessment(assessment);
            count++;
          }
        }
      }

      // Migrate pitch decks
      const pitchDeckFile = path.join(this.legacyDataPath, 'business', 'pitch-decks.json');
      if (await this.pathExists(pitchDeckFile)) {
        const decks = await this.readJsonFile(pitchDeckFile);
        if (Array.isArray(decks)) {
          for (const deck of decks) {
            await this.migratePitchDeck(deck);
            count++;
          }
        }
      }

      // Migrate startup metrics
      const metricsFile = path.join(this.legacyDataPath, 'business', 'startup-metrics.json');
      if (await this.pathExists(metricsFile)) {
        const metrics = await this.readJsonFile(metricsFile);
        if (Array.isArray(metrics)) {
          for (const metric of metrics) {
            await this.migrateStartupMetrics(metric);
            count++;
          }
        }
      }

      // Migrate business reviews
      const reviewFile = path.join(this.legacyDataPath, 'business', 'business-reviews.json');
      if (await this.pathExists(reviewFile)) {
        const reviews = await this.readJsonFile(reviewFile);
        if (Array.isArray(reviews)) {
          for (const review of reviews) {
            await this.migrateBusinessReview(review);
            count++;
          }
        }
      }
    } catch (error) {
      errors.push(`Business data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate memory data
   */
  private async migrateMemoryData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const memoryFile = path.join(this.legacyDataPath, 'memory', 'memories.json');
      if (await this.pathExists(memoryFile)) {
        const memories = await this.readJsonFile(memoryFile);
        if (Array.isArray(memories)) {
          for (const memory of memories) {
            await this.migrateMemory(memory);
            count++;
          }
        }
      }
    } catch (error) {
      errors.push(`Memory data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual sprint
   */
  private async migrateSprint(sprint: any): Promise<void> {
    const id = sprint.id || randomUUID();
    const projectId = sprint.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO agile_sprints 
       (id, project_id, name, goal, status, start_date, end_date, duration, team, 
        story_points_planned, story_points_completed, stories_total, stories_completed, 
        velocity, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        sprint.name || 'Unnamed Sprint',
        sprint.goal || null,
        sprint.status || 'planning',
        sprint.startDate ? new Date(sprint.startDate).getTime() : null,
        sprint.endDate ? new Date(sprint.endDate).getTime() : null,
        sprint.duration || 14,
        JSON.stringify(sprint.team || []),
        sprint.storyPointsPlanned || 0,
        sprint.storyPointsCompleted || 0,
        sprint.storiesTotal || 0,
        sprint.storiesCompleted || 0,
        sprint.velocity || 0,
        sprint.createdAt ? new Date(sprint.createdAt).getTime() : now,
        sprint.updatedAt ? new Date(sprint.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate individual story
   */
  private async migrateStory(story: any): Promise<void> {
    const id = story.id || randomUUID();
    const projectId = story.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO agile_stories 
       (id, project_id, sprint_id, epic_id, title, description, status, story_points, 
        priority, assignee, tags, acceptance_criteria, design_document_url, 
        implementation_document_url, documentation_status, documentation_last_reviewed, 
        documentation_reviewers, groomed_with_user_feedback, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        story.sprintId || null,
        story.epicId || story.epic || null,
        story.title || 'Untitled Story',
        story.description || null,
        story.status || 'todo',
        story.storyPoints || 0,
        story.priority || 'medium',
        story.assignee || null,
        JSON.stringify(story.tags || []),
        JSON.stringify(story.acceptanceCriteria || []),
        story.designDocumentUrl || null,
        story.implementationDocumentUrl || null,
        story.documentationStatus || 'pending',
        story.documentationLastReviewed ? new Date(story.documentationLastReviewed).getTime() : null,
        JSON.stringify(story.documentationReviewers || []),
        story.groomedWithUserFeedback || false,
        story.createdAt ? new Date(story.createdAt).getTime() : now,
        story.updatedAt ? new Date(story.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate individual epic
   */
  private async migrateEpic(epic: any): Promise<void> {
    const id = epic.id || randomUUID();
    const projectId = epic.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO agile_epics 
       (id, project_id, title, description, status, story_points, priority, 
        target_quarter, business_value, success_criteria, dependencies, risks, 
        created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        epic.title || 'Untitled Epic',
        epic.description || null,
        epic.status || 'planning',
        epic.storyPoints || 0,
        epic.priority || 'medium',
        epic.targetQuarter || null,
        epic.businessValue || null,
        JSON.stringify(epic.successCriteria || []),
        JSON.stringify(epic.dependencies || []),
        JSON.stringify(epic.risks || []),
        epic.createdAt ? new Date(epic.createdAt).getTime() : now,
        epic.updatedAt ? new Date(epic.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate kanban board
   */
  private async migrateKanbanBoard(board: any): Promise<void> {
    const id = board.id || randomUUID();
    const projectId = board.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO kanban_boards 
       (id, project_id, name, description, columns, cards, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        board.name || 'Untitled Board',
        board.description || null,
        JSON.stringify(board.columns || []),
        JSON.stringify(board.cards || []),
        board.createdAt ? new Date(board.createdAt).getTime() : now,
        board.updatedAt ? new Date(board.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate document
   */
  private async migrateDocument(doc: any): Promise<void> {
    const id = doc.id || randomUUID();
    const projectId = doc.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO documents 
       (id, project_id, title, content, type, path, tags, status, author, version, 
        created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        doc.title || 'Untitled Document',
        doc.content || '',
        doc.type || 'markdown',
        doc.path || null,
        JSON.stringify(doc.tags || []),
        doc.status || 'draft',
        doc.author || null,
        doc.version || 1,
        doc.createdAt ? new Date(doc.createdAt).getTime() : now,
        doc.updatedAt ? new Date(doc.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate business plan
   */
  private async migrateBusinessPlan(plan: any): Promise<void> {
    const id = plan.id || randomUUID();
    const projectId = plan.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO business_plans 
       (id, project_id, plan_type, content, metadata, status, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        plan.planType || 'general',
        JSON.stringify(plan.content || {}),
        JSON.stringify(plan.metadata || {}),
        plan.status || 'draft',
        plan.createdAt ? new Date(plan.createdAt).getTime() : now,
        plan.updatedAt ? new Date(plan.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate market analysis
   */
  private async migrateMarketAnalysis(analysis: any): Promise<void> {
    const id = analysis.id || randomUUID();
    const projectId = analysis.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO market_analyses 
       (id, project_id, industry, target_market, geographic_scope, market_size, 
        growth_rate, trends, opportunities, challenges, recommendations, 
        competitors, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        analysis.industry || '',
        analysis.targetMarket || '',
        analysis.geographicScope || 'global',
        analysis.marketSize || null,
        analysis.growthRate || null,
        JSON.stringify(analysis.trends || []),
        JSON.stringify(analysis.opportunities || []),
        JSON.stringify(analysis.challenges || []),
        JSON.stringify(analysis.recommendations || []),
        JSON.stringify(analysis.competitors || []),
        analysis.createdAt ? new Date(analysis.createdAt).getTime() : now,
        analysis.updatedAt ? new Date(analysis.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate competitor analysis
   */
  private async migrateCompetitorAnalysis(analysis: any): Promise<void> {
    const id = analysis.id || randomUUID();
    const projectId = analysis.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO competitor_analyses 
       (id, project_id, industry, business_idea, competition_level, 
        competitors, gaps, opportunities, recommendations, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        analysis.industry || '',
        analysis.businessIdea || '',
        analysis.competitionLevel || null,
        JSON.stringify(analysis.competitors || []),
        JSON.stringify(analysis.gaps || []),
        JSON.stringify(analysis.opportunities || []),
        JSON.stringify(analysis.recommendations || []),
        analysis.createdAt ? new Date(analysis.createdAt).getTime() : now,
        analysis.updatedAt ? new Date(analysis.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate financial projection
   */
  private async migrateFinancialProjection(projection: any): Promise<void> {
    const id = projection.id || randomUUID();
    const projectId = projection.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO financial_projections 
       (id, project_id, business_model, timeline, currency, revenue, expenses, 
        net_income, cash_flow, break_even_month, total_investment_needed, roi, 
        scenarios, assumptions, recommendations, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        projection.businessModel || '',
        projection.timeline || 36,
        projection.currency || 'USD',
        JSON.stringify(projection.revenue || {}),
        JSON.stringify(projection.expenses || {}),
        JSON.stringify(projection.netIncome || {}),
        JSON.stringify(projection.cashFlow || {}),
        projection.breakEvenMonth || null,
        projection.totalInvestmentNeeded || null,
        projection.roi || null,
        JSON.stringify(projection.scenarios || null),
        JSON.stringify(projection.assumptions || []),
        JSON.stringify(projection.recommendations || []),
        projection.createdAt ? new Date(projection.createdAt).getTime() : now,
        projection.updatedAt ? new Date(projection.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate startup assessment
   */
  private async migrateStartupAssessment(assessment: any): Promise<void> {
    const id = assessment.id || randomUUID();
    const projectId = assessment.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO startup_assessments 
       (id, project_id, stage, score, categories, strengths, weaknesses, 
        next_steps, stage_recommendations, overall, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        assessment.stage || 'idea',
        assessment.score || 0,
        JSON.stringify(assessment.categories || []),
        JSON.stringify(assessment.strengths || []),
        JSON.stringify(assessment.weaknesses || []),
        JSON.stringify(assessment.nextSteps || []),
        JSON.stringify(assessment.stageRecommendations || []),
        assessment.overall || null,
        assessment.createdAt ? new Date(assessment.createdAt).getTime() : now,
        assessment.updatedAt ? new Date(assessment.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate pitch deck
   */
  private async migratePitchDeck(deck: any): Promise<void> {
    const id = deck.id || randomUUID();
    const projectId = deck.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO pitch_decks 
       (id, project_id, business_idea, template, slides, presentation_tips, 
        key_messages, markdown, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        deck.businessIdea || '',
        deck.template || 'standard',
        JSON.stringify(deck.slides || []),
        JSON.stringify(deck.presentationTips || []),
        JSON.stringify(deck.keyMessages || []),
        deck.markdown || '',
        deck.createdAt ? new Date(deck.createdAt).getTime() : now,
        deck.updatedAt ? new Date(deck.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate startup metrics
   */
  private async migrateStartupMetrics(metrics: any): Promise<void> {
    const id = metrics.id || randomUUID();
    const projectId = metrics.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO startup_metrics 
       (id, project_id, metrics_type, current_metrics, goals, timeframe, 
        trends, benchmarks, recommendations, health_score, alerts, 
        created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        metrics.metricsType || 'saas',
        JSON.stringify(metrics.currentMetrics || {}),
        JSON.stringify(metrics.goals || {}),
        metrics.timeframe || 'monthly',
        JSON.stringify(metrics.trends || []),
        JSON.stringify(metrics.benchmarks || []),
        JSON.stringify(metrics.recommendations || []),
        metrics.healthScore || null,
        JSON.stringify(metrics.alerts || []),
        metrics.createdAt ? new Date(metrics.createdAt).getTime() : now,
        metrics.updatedAt ? new Date(metrics.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate business review
   */
  private async migrateBusinessReview(review: any): Promise<void> {
    const id = review.id || randomUUID();
    const projectId = review.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO business_reviews 
       (id, project_id, business_name, current_stage, overall_health_score, 
        strengths, gaps, strategic_paths, immediate_actions, data_collected, 
        review_date, next_review_date, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        review.businessName || '',
        review.currentStage || 'idea',
        review.overallHealthScore || 0,
        JSON.stringify(review.strengths || []),
        JSON.stringify(review.gaps || []),
        JSON.stringify(review.strategicPaths || []),
        JSON.stringify(review.immediateActions || []),
        JSON.stringify(review.dataCollected || {}),
        review.reviewDate || new Date().toISOString(),
        review.nextReviewDate || new Date(Date.now() + 90 * 24 * 60 * 60 * 1000).toISOString(),
        review.createdAt ? new Date(review.createdAt).getTime() : now,
        review.updatedAt ? new Date(review.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate memory
   */
  private async migrateMemory(memory: any): Promise<void> {
    const id = memory.id || randomUUID();
    const projectId = memory.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO memories 
       (id, project_id, title, type, content, tags, metadata, importance, category, source, 
        created_by, embedding_vector, similarity_score, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        memory.title || null,
        memory.type || 'general',
        memory.content || '',
        JSON.stringify(memory.tags || []),
        JSON.stringify(memory.metadata || {}),
        memory.importance || 'medium',
        memory.category || null,
        memory.source || null,
        memory.createdBy || null,
        memory.embeddingVector || null,
        memory.similarityScore || null,
        memory.createdAt ? new Date(memory.createdAt).getTime() : now,
        memory.updatedAt ? new Date(memory.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Update migration status
   */
  private async updateMigrationStatus(status: string): Promise<void> {
    const now = Date.now();
    
    await this.dbRun(
      'INSERT OR REPLACE INTO atlas_metadata (key, value, updated_at) VALUES (?, ?, ?)',
      ['migration_status', status, now]
    );
    
    await this.dbRun(
      'INSERT OR REPLACE INTO atlas_metadata (key, value, updated_at) VALUES (?, ?, ?)',
      ['last_migration_timestamp', now.toString(), now]
    );
  }

  /**
   * Migrate ADR data
   */
  private async migrateADRData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Look for ADR files in various possible locations
      const adrPaths = [
        path.join(this.legacyDataPath, 'adr'),
        path.join(this.legacyDataPath, 'adr-management'),
        path.join(this.legacyDataPath, 'adrs')
      ];

      for (const adrPath of adrPaths) {
        if (await this.pathExists(adrPath)) {
          // Check for index file
          const indexFile = path.join(adrPath, 'index.json');
          if (await this.pathExists(indexFile)) {
            const index = await this.readJsonFile(indexFile);
            if (index && index.adrs && Array.isArray(index.adrs)) {
              for (const adrSummary of index.adrs) {
                // Read the full ADR file
                const adrFile = path.join(adrPath, `${adrSummary.id}.json`);
                if (await this.pathExists(adrFile)) {
                  const adr = await this.readJsonFile(adrFile);
                  if (adr) {
                    await this.migrateADR(adr);
                    count++;
                  }
                }
              }
            }
          }

          // Also check for individual ADR files without index
          const adrFiles = await this.findJsonFiles(adrPath);
          for (const adrFile of adrFiles) {
            if (!adrFile.endsWith('index.json')) {
              const adr = await this.readJsonFile(adrFile);
              if (adr && adr.id && adr.id.startsWith('ADR-')) {
                await this.migrateADR(adr);
                count++;
              }
            }
          }
        }
      }

    } catch (error) {
      errors.push(`ADR data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual ADR
   */
  private async migrateADR(adr: any): Promise<void> {
    const id = adr.id || `ADR-${Date.now().toString().slice(-4)}`;
    const projectId = adr.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO adr_records 
       (id, project_id, title, status, date, deciders, template, context, decision, 
        consequences, tags, decision_drivers, considered_options, pros_and_cons, 
        supersedes, superseded_by, related_to, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        adr.title || 'Untitled ADR',
        adr.status || 'proposed',
        adr.date ? new Date(adr.date).getTime() : now,
        JSON.stringify(adr.deciders || []),
        adr.template || 'nygard',
        adr.context || '',
        adr.decision || '',
        adr.consequences || '',
        JSON.stringify(adr.tags || []),
        JSON.stringify(adr.decisionDrivers || []),
        JSON.stringify(adr.consideredOptions || []),
        JSON.stringify(adr.prosAndCons || []),
        JSON.stringify(adr.supersedes || []),
        adr.supersededBy || null,
        JSON.stringify(adr.relatedTo || []),
        adr.createdAt ? new Date(adr.createdAt).getTime() : now,
        adr.updatedAt ? new Date(adr.updatedAt).getTime() : now
      ]
    );

    // Migrate status history if available
    if (adr.statusHistory && Array.isArray(adr.statusHistory)) {
      for (const historyEntry of adr.statusHistory) {
        await this.dbRun(
          `INSERT OR REPLACE INTO adr_status_history 
           (id, adr_id, project_id, from_status, to_status, changed_at, changed_by, reason) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            id,
            projectId,
            historyEntry.from || null,
            historyEntry.to || 'proposed',
            historyEntry.date ? new Date(historyEntry.date).getTime() : now,
            historyEntry.changedBy || 'unknown',
            historyEntry.reason || null
          ]
        );
      }
    }
  }

  /**
   * Migrate development module data
   */
  private async migrateDevelopmentData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const devFile = path.join(this.legacyDataPath, 'development', 'development.json');
      if (await this.pathExists(devFile)) {
        const devData = await this.readJsonFile(devFile);
        if (devData) {
          // Migrate features
          if (devData.features) {
            for (const featureId in devData.features) {
              const feature = devData.features[featureId];
              await this.migrateDevelopmentFeature(feature);
              count++;
            }
          }

          // Migrate TDD sessions
          if (devData.sessions) {
            for (const sessionId in devData.sessions) {
              const session = devData.sessions[sessionId];
              await this.migrateTDDSession(session);
              count++;
            }
          }

          // Migrate TDD config
          if (devData.config) {
            await this.migrateTDDConfig(devData.config);
            count++;
          }
        }
      }
    } catch (error) {
      errors.push(`Development data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual development feature
   */
  private async migrateDevelopmentFeature(feature: any): Promise<void> {
    const id = feature.id || randomUUID();
    const projectId = feature.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO development_features 
       (id, project_id, name, description, status, test_coverage, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        feature.name || 'Untitled Feature',
        feature.description || '',
        feature.status || 'planning',
        feature.testCoverage || 0.0,
        feature.createdAt ? new Date(feature.createdAt).getTime() : now,
        feature.updatedAt ? new Date(feature.updatedAt).getTime() : now
      ]
    );

    // Migrate test cases
    if (feature.testCases && Array.isArray(feature.testCases)) {
      for (const testCase of feature.testCases) {
        await this.dbRun(
          `INSERT OR REPLACE INTO development_test_cases 
           (id, feature_id, project_id, name, description, type, status, expected_behavior, 
            actual_behavior, error_message, created_at, updated_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            testCase.id || randomUUID(),
            id,
            projectId,
            testCase.name || 'Untitled Test',
            testCase.description || '',
            testCase.type || 'unit',
            testCase.status || 'pending',
            testCase.expectedBehavior || '',
            testCase.actualBehavior || null,
            testCase.errorMessage || null,
            testCase.createdAt ? new Date(testCase.createdAt).getTime() : now,
            testCase.updatedAt ? new Date(testCase.updatedAt).getTime() : now
          ]
        );
      }
    }
  }

  /**
   * Migrate TDD session
   */
  private async migrateTDDSession(session: any): Promise<void> {
    const id = session.id || randomUUID();
    const projectId = session.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO development_tdd_sessions 
       (id, feature_id, project_id, current_phase, started_at, completed_at, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        session.featureId || 'unknown',
        projectId,
        session.currentPhase || 'red',
        session.startedAt ? new Date(session.startedAt).getTime() : now,
        session.completedAt ? new Date(session.completedAt).getTime() : null,
        session.createdAt ? new Date(session.createdAt).getTime() : now,
        session.updatedAt ? new Date(session.updatedAt).getTime() : now
      ]
    );

    // Migrate phase history
    if (session.phaseHistory && Array.isArray(session.phaseHistory)) {
      for (const phase of session.phaseHistory) {
        await this.dbRun(
          `INSERT OR REPLACE INTO development_tdd_phase_history 
           (id, session_id, project_id, phase, started_at, completed_at, notes) 
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            id,
            projectId,
            phase.phase || 'red',
            phase.startedAt ? new Date(phase.startedAt).getTime() : now,
            phase.completedAt ? new Date(phase.completedAt).getTime() : null,
            phase.notes || null
          ]
        );
      }
    }
  }

  /**
   * Migrate TDD configuration
   */
  private async migrateTDDConfig(config: any): Promise<void> {
    const projectId = config.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO development_tdd_config 
       (project_id, enforce_test_first, minimum_test_coverage, 
        require_tests_before_implementation, auto_generate_test_templates, 
        created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [
        projectId,
        config.enforceTestFirst !== false,
        config.minimumTestCoverage || 80.0,
        config.requireTestsBeforeImplementation !== false,
        config.autoGenerateTestTemplates !== false,
        now,
        now
      ]
    );
  }

  /**
   * Migrate workspace module data
   */
  private async migrateWorkspaceData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const workspaceFile = path.join(this.legacyDataPath, 'workspace', 'workspace.json');
      if (await this.pathExists(workspaceFile)) {
        const workspaceData = await this.readJsonFile(workspaceFile);
        if (workspaceData) {
          // Migrate workspaces
          if (workspaceData.workspaces) {
            for (const workspaceId in workspaceData.workspaces) {
              const workspace = workspaceData.workspaces[workspaceId];
              await this.migrateWorkspace(workspace, workspaceData.activeWorkspaceId === workspaceId);
              count++;
            }
          }
        }
      }
    } catch (error) {
      errors.push(`Workspace data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual workspace
   */
  private async migrateWorkspace(workspace: any, isActive: boolean): Promise<void> {
    const id = workspace.id || `ws-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO workspaces 
       (id, name, description, root_path, active, settings, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        workspace.name || 'Untitled Workspace',
        workspace.description || '',
        workspace.rootPath || process.cwd(),
        isActive,
        JSON.stringify(workspace.settings || {}),
        workspace.createdAt ? new Date(workspace.createdAt).getTime() : now,
        workspace.updatedAt ? new Date(workspace.updatedAt).getTime() : now
      ]
    );

    // Migrate repositories
    if (workspace.repositories && Array.isArray(workspace.repositories)) {
      for (const repo of workspace.repositories) {
        await this.dbRun(
          `INSERT OR REPLACE INTO workspace_repositories 
           (id, workspace_id, name, path, type, is_primary, remote_url, current_branch, 
            last_sync, dependencies, created_at, updated_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            repo.id || `repo-${randomUUID()}`,
            id,
            repo.name || 'Untitled Repository',
            repo.path || '',
            repo.type || 'local',
            repo.primary || false,
            repo.remote || null,
            repo.branch || null,
            repo.lastSync ? new Date(repo.lastSync).getTime() : null,
            JSON.stringify(repo.dependencies || []),
            repo.createdAt ? new Date(repo.createdAt).getTime() : now,
            repo.updatedAt ? new Date(repo.updatedAt).getTime() : now
          ]
        );
      }
    }
  }

  /**
   * Migrate process automation module data
   */
  private async migrateProcessAutomationData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const processesPath = path.join(this.legacyDataPath, 'process-automation', 'processes');
      const executionsPath = path.join(this.legacyDataPath, 'process-automation', 'executions');
      const templatesPath = path.join(this.legacyDataPath, 'process-automation', 'templates');

      // Migrate processes
      if (await this.pathExists(processesPath)) {
        const processFiles = await this.findJsonFiles(processesPath);
        for (const processFile of processFiles) {
          const process = await this.readJsonFile(processFile);
          if (process) {
            await this.migrateProcess(process);
            count++;
          }
        }
      }

      // Migrate executions
      if (await this.pathExists(executionsPath)) {
        const processDirs = await fs.readdir(executionsPath, { withFileTypes: true });
        for (const processDir of processDirs) {
          if (processDir.isDirectory()) {
            const executionFiles = await this.findJsonFiles(path.join(executionsPath, processDir.name));
            for (const executionFile of executionFiles) {
              const execution = await this.readJsonFile(executionFile);
              if (execution) {
                await this.migrateProcessExecution(execution);
                count++;
              }
            }
          }
        }
      }

      // Migrate templates
      if (await this.pathExists(templatesPath)) {
        const templateFiles = await this.findJsonFiles(templatesPath);
        for (const templateFile of templateFiles) {
          const template = await this.readJsonFile(templateFile);
          if (template) {
            await this.migrateProcessTemplate(template);
            count++;
          }
        }
      }
    } catch (error) {
      errors.push(`Process automation data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual process
   */
  private async migrateProcess(process: any): Promise<void> {
    const id = process.id || randomUUID();
    const projectId = process.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO process_definitions 
       (id, project_id, name, description, version, persona, category, tags, 
        variables, on_success, on_failure, is_active, created_by, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        process.name || 'Untitled Process',
        process.description || '',
        process.version || '1.0.0',
        process.persona || null,
        process.metadata?.category || null,
        JSON.stringify(process.metadata?.tags || []),
        JSON.stringify(process.variables || {}),
        null, // on_success - not in legacy format
        null, // on_failure - not in legacy format
        process.metadata?.isActive !== false,
        process.metadata?.createdBy || 'system',
        process.metadata?.createdAt ? new Date(process.metadata.createdAt).getTime() : now,
        now
      ]
    );

    // Migrate activities
    if (process.activities && Array.isArray(process.activities)) {
      for (let i = 0; i < process.activities.length; i++) {
        const activity = process.activities[i];
        await this.dbRun(
          `INSERT OR REPLACE INTO process_activities 
           (id, process_id, activity_order, activity_id, type, name, description, 
            config, retry_policy, timeout, required, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            id,
            i,
            activity.id || randomUUID(),
            activity.type || 'tool',
            activity.name || 'Untitled Activity',
            activity.description || '',
            JSON.stringify(activity.config || {}),
            activity.retryPolicy ? JSON.stringify(activity.retryPolicy) : null,
            activity.timeout || null,
            activity.required !== false,
            now
          ]
        );
      }
    }

    // Migrate triggers
    if (process.triggers && Array.isArray(process.triggers)) {
      for (const trigger of process.triggers) {
        await this.dbRun(
          `INSERT OR REPLACE INTO process_triggers 
           (id, process_id, trigger_id, type, name, enabled, config, ai_suggested, 
            reasoning, created_at, updated_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            id,
            trigger.id || randomUUID(),
            trigger.type || 'manual',
            trigger.name || 'Untitled Trigger',
            trigger.enabled !== false,
            JSON.stringify(trigger.config || {}),
            trigger.aiSuggested || false,
            trigger.reasoning || null,
            now,
            now
          ]
        );
      }
    }
  }

  /**
   * Migrate individual process execution
   */
  private async migrateProcessExecution(execution: any): Promise<void> {
    const id = execution.id || randomUUID();
    const projectId = execution.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO process_executions 
       (id, process_id, project_id, process_version, status, triggered_by, 
        trigger_type, variables, error_message, error_code, started_at, 
        completed_at, duration, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        execution.processId || 'unknown',
        projectId,
        execution.processVersion || '1.0.0',
        execution.status || 'pending',
        execution.triggeredBy || 'system',
        execution.triggerType || 'manual',
        JSON.stringify(execution.variables || {}),
        execution.error?.message || null,
        execution.error?.code || null,
        execution.startedAt ? new Date(execution.startedAt).getTime() : now,
        execution.completedAt ? new Date(execution.completedAt).getTime() : null,
        execution.duration || null,
        now
      ]
    );

    // Migrate activity results
    if (execution.activityResults) {
      for (const [activityId, result] of Object.entries(execution.activityResults as any)) {
        const typedResult = result as any;
        await this.dbRun(
          `INSERT OR REPLACE INTO process_activity_results 
           (id, execution_id, activity_id, status, started_at, completed_at, 
            duration, output, error, retry_count, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            id,
            activityId,
            typedResult.status || 'pending',
            typedResult.startedAt ? new Date(typedResult.startedAt).getTime() : null,
            typedResult.completedAt ? new Date(typedResult.completedAt).getTime() : null,
            typedResult.duration || null,
            typedResult.output ? JSON.stringify(typedResult.output) : null,
            typedResult.error ? JSON.stringify(typedResult.error) : null,
            0,
            now
          ]
        );
      }
    }

    // Migrate logs
    if (execution.logs && Array.isArray(execution.logs)) {
      for (const log of execution.logs) {
        await this.dbRun(
          `INSERT INTO process_execution_logs 
           (id, execution_id, activity_id, timestamp, level, message, data) 
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            id,
            null,
            log.timestamp ? new Date(log.timestamp).getTime() : now,
            log.level || 'info',
            log.message || '',
            null
          ]
        );
      }
    }
  }

  /**
   * Migrate individual process template
   */
  private async migrateProcessTemplate(template: any): Promise<void> {
    const id = template.id || randomUUID();
    const projectId = template.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO process_templates 
       (id, project_id, name, description, persona, category, variables, 
        process_definition, is_system, created_by, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        template.name || 'Untitled Template',
        template.description || '',
        template.persona || null,
        template.category || 'general',
        JSON.stringify(template.variables || {}),
        JSON.stringify(template.processDefinition || {}),
        template.isSystem || false,
        template.createdBy || 'system',
        now,
        now
      ]
    );
  }

  /**
   * Migrate issue tracking module data
   */
  private async migrateIssueTrackingData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const issuesPath = path.join(this.legacyDataPath, 'issue-tracking', 'issues');
      const templatesPath = path.join(this.legacyDataPath, 'issue-tracking', 'templates');
      const milestonesPath = path.join(this.legacyDataPath, 'issue-tracking', 'milestones');

      // Migrate issues
      if (await this.pathExists(issuesPath)) {
        const issueFiles = await this.findJsonFiles(issuesPath);
        for (const issueFile of issueFiles) {
          const issue = await this.readJsonFile(issueFile);
          if (issue) {
            await this.migrateIssue(issue);
            count++;
          }
        }
      }

      // Note: Templates and milestones are not yet in the SQLite schema
      // They can be added later if needed
    } catch (error) {
      errors.push(`Issue tracking data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual issue
   */
  private async migrateIssue(issue: any): Promise<void> {
    const id = issue.id || `ISSUE-${Date.now().toString().slice(-4)}`;
    const projectId = issue.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO issue_tracker_issues 
       (id, project_id, type, title, description, status, priority, created_by, 
        assigned_to, labels, affected_modules, related_issues, resolution, 
        closed_at, metadata, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        issue.type || 'bug',
        issue.title || 'Untitled Issue',
        issue.description || '',
        issue.status || 'open',
        issue.priority || 'medium',
        issue.createdBy || 'unknown',
        issue.assignedTo || null,
        JSON.stringify(issue.labels || []),
        JSON.stringify(issue.affectedModules || []),
        JSON.stringify(issue.relatedIssues || []),
        issue.resolution || null,
        issue.closedAt ? new Date(issue.closedAt).getTime() : null,
        JSON.stringify(issue.metadata || {}),
        issue.createdAt ? new Date(issue.createdAt).getTime() : now,
        issue.updatedAt ? new Date(issue.updatedAt).getTime() : now
      ]
    );

    // Migrate comments
    if (issue.comments && Array.isArray(issue.comments)) {
      for (const comment of issue.comments) {
        await this.dbRun(
          `INSERT OR REPLACE INTO issue_tracker_comments 
           (id, issue_id, project_id, author, content, type, edited_at, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            comment.id || randomUUID(),
            id,
            projectId,
            comment.author || 'unknown',
            comment.content || '',
            comment.type || 'comment',
            comment.editedAt ? new Date(comment.editedAt).getTime() : null,
            comment.createdAt ? new Date(comment.createdAt).getTime() : now
          ]
        );
      }
    }

    // Migrate attachments
    if (issue.attachments && Array.isArray(issue.attachments)) {
      for (const attachment of issue.attachments) {
        await this.dbRun(
          `INSERT OR REPLACE INTO issue_tracker_attachments 
           (id, issue_id, project_id, filename, path, size, mime_type, 
            uploaded_by, uploaded_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            attachment.id || randomUUID(),
            id,
            projectId,
            attachment.filename || 'unknown',
            attachment.path || '',
            attachment.size || 0,
            attachment.mimeType || 'application/octet-stream',
            attachment.uploadedBy || 'unknown',
            attachment.uploadedAt ? new Date(attachment.uploadedAt).getTime() : now
          ]
        );
      }
    }
  }

  /**
   * Migrate product roadmap module data
   */
  private async migrateProductRoadmapData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const roadmapsPath = path.join(this.legacyDataPath, 'roadmaps');
      
      // Migrate roadmaps
      if (await this.pathExists(roadmapsPath)) {
        const roadmapFiles = await fs.readdir(roadmapsPath);
        
        for (const file of roadmapFiles) {
          if (file.startsWith('roadmap-') && file.endsWith('.json')) {
            const roadmapFile = path.join(roadmapsPath, file);
            const roadmap = await this.readJsonFile(roadmapFile);
            if (roadmap) {
              await this.migrateRoadmap(roadmap);
              count++;
            }
          }
        }

        // Migrate bulk data files
        const bulkFiles = ['themes.json', 'initiatives.json', 'features.json', 'milestones.json', 'releases.json', 'reviews.json'];
        
        for (const bulkFile of bulkFiles) {
          const filePath = path.join(roadmapsPath, bulkFile);
          if (await this.pathExists(filePath)) {
            const data = await this.readJsonFile(filePath);
            
            if (Array.isArray(data)) {
              switch (bulkFile) {
                case 'themes.json':
                  for (const theme of data) {
                    await this.migrateRoadmapTheme(theme);
                    count++;
                  }
                  break;
                case 'initiatives.json':
                  for (const initiative of data) {
                    await this.migrateRoadmapInitiative(initiative);
                    count++;
                  }
                  break;
                case 'features.json':
                  for (const feature of data) {
                    await this.migrateRoadmapFeature(feature);
                    count++;
                  }
                  break;
                case 'milestones.json':
                  for (const milestone of data) {
                    await this.migrateRoadmapMilestone(milestone);
                    count++;
                  }
                  break;
                case 'releases.json':
                  for (const release of data) {
                    await this.migrateRoadmapRelease(release);
                    count++;
                  }
                  break;
                case 'reviews.json':
                  for (const review of data) {
                    await this.migrateRoadmapReview(review);
                    count++;
                  }
                  break;
              }
            }
          }
        }
      }
    } catch (error) {
      errors.push(`Product roadmap data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual roadmap
   */
  private async migrateRoadmap(roadmap: any): Promise<void> {
    const id = roadmap.id || `roadmap-${randomUUID()}`;
    const projectId = roadmap.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO product_roadmaps 
       (id, project_id, name, vision, time_horizon, status, owner, stakeholders, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        roadmap.name || 'Untitled Roadmap',
        roadmap.vision || '',
        roadmap.timeHorizon || 'quarterly',
        roadmap.status || 'draft',
        roadmap.owner || 'unknown',
        JSON.stringify(roadmap.stakeholders || []),
        roadmap.createdAt ? new Date(roadmap.createdAt).getTime() : now,
        roadmap.updatedAt ? new Date(roadmap.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate roadmap theme
   */
  private async migrateRoadmapTheme(theme: any): Promise<void> {
    const id = theme.id || `theme-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO roadmap_themes 
       (id, roadmap_id, name, description, objectives, priority, start_quarter, end_quarter, status, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        theme.roadmapId || 'unknown',
        theme.name || 'Untitled Theme',
        theme.description || '',
        JSON.stringify(theme.objectives || []),
        theme.priority || 'medium',
        theme.timeframe?.startQuarter || 'Q1 2024',
        theme.timeframe?.endQuarter || 'Q4 2024',
        theme.status || 'planned',
        theme.createdAt ? new Date(theme.createdAt).getTime() : now,
        theme.updatedAt ? new Date(theme.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate roadmap initiative
   */
  private async migrateRoadmapInitiative(initiative: any): Promise<void> {
    const id = initiative.id || `initiative-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO roadmap_initiatives 
       (id, theme_id, roadmap_id, title, description, status, 
        user_impact, revenue_impact, cost_savings, strategic_value, customer_satisfaction,
        development_weeks, design_weeks, qa_weeks, effort_confidence,
        epic_ids, risks, dependencies, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        initiative.themeId || 'unknown',
        initiative.roadmapId || 'unknown',
        initiative.title || 'Untitled Initiative',
        initiative.description || '',
        initiative.status || 'ideation',
        initiative.value?.userImpact || 'medium',
        initiative.value?.revenueImpact || 0,
        initiative.value?.costSavings || 0,
        initiative.value?.strategicValue || 5,
        initiative.value?.customerSatisfaction || 0,
        initiative.effort?.developmentWeeks || 0,
        initiative.effort?.designWeeks || 0,
        initiative.effort?.qaWeeks || 0,
        initiative.effort?.confidence || 'medium',
        JSON.stringify(initiative.epicIds || []),
        JSON.stringify(initiative.risks || []),
        JSON.stringify(initiative.dependencies || []),
        initiative.createdAt ? new Date(initiative.createdAt).getTime() : now,
        initiative.updatedAt ? new Date(initiative.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate roadmap feature
   */
  private async migrateRoadmapFeature(feature: any): Promise<void> {
    const id = feature.id || `feature-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO roadmap_features 
       (id, initiative_id, roadmap_id, name, description, status, priority,
        business_value_score, business_value_rationale, business_value_metrics,
        technical_complexity, user_stories, target_release, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        feature.initiativeId || 'unknown',
        feature.roadmapId || 'unknown',
        feature.name || 'Untitled Feature',
        feature.description || '',
        feature.status || 'proposed',
        feature.priority || 50,
        feature.businessValue?.score || 50,
        feature.businessValue?.rationale || '',
        JSON.stringify(feature.businessValue?.metrics || []),
        feature.technicalComplexity || 'medium',
        JSON.stringify(feature.userStories || []),
        feature.targetRelease || null,
        feature.createdAt ? new Date(feature.createdAt).getTime() : now,
        feature.updatedAt ? new Date(feature.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate roadmap milestone
   */
  private async migrateRoadmapMilestone(milestone: any): Promise<void> {
    const id = milestone.id || `milestone-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO roadmap_milestones 
       (id, roadmap_id, name, date, type, description, deliverables, status, dependencies, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        milestone.roadmapId || 'unknown',
        milestone.name || 'Untitled Milestone',
        milestone.date ? new Date(milestone.date).getTime() : now,
        milestone.type || 'release',
        milestone.description || '',
        JSON.stringify(milestone.deliverables || []),
        milestone.status || 'upcoming',
        JSON.stringify(milestone.dependencies || []),
        milestone.createdAt ? new Date(milestone.createdAt).getTime() : now,
        milestone.updatedAt ? new Date(milestone.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate roadmap release
   */
  private async migrateRoadmapRelease(release: any): Promise<void> {
    const id = release.id || `release-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO roadmap_releases 
       (id, roadmap_id, version, name, date, features, themes, goals, status, notes, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        release.roadmapId || 'unknown',
        release.version || '1.0.0',
        release.name || 'Untitled Release',
        release.date ? new Date(release.date).getTime() : now,
        JSON.stringify(release.features || []),
        JSON.stringify(release.themes || []),
        JSON.stringify(release.goals || []),
        release.status || 'planning',
        release.notes || null,
        release.createdAt ? new Date(release.createdAt).getTime() : now,
        release.updatedAt ? new Date(release.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate roadmap review
   */
  private async migrateRoadmapReview(review: any): Promise<void> {
    const id = review.id || `review-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO roadmap_reviews 
       (id, roadmap_id, review_date, participants, decisions, feedback, next_review_date, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        review.roadmapId || 'unknown',
        review.reviewDate ? new Date(review.reviewDate).getTime() : now,
        JSON.stringify(review.participants || []),
        JSON.stringify(review.decisions || []),
        JSON.stringify(review.feedback || []),
        review.nextReviewDate ? new Date(review.nextReviewDate).getTime() : null,
        review.createdAt ? new Date(review.createdAt).getTime() : now,
        review.updatedAt ? new Date(review.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate product requirements module data
   */
  private async migrateProductRequirementsData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const requirementsPath = path.join(this.legacyDataPath, 'product-requirements');
      
      // Migrate requirements
      if (await this.pathExists(requirementsPath)) {
        const requirementsFile = path.join(requirementsPath, 'requirements.json');
        if (await this.pathExists(requirementsFile)) {
          const requirements = await this.readJsonFile(requirementsFile);
          if (Array.isArray(requirements)) {
            for (const requirement of requirements) {
              await this.migrateProductRequirement(requirement);
              count++;
            }
          }
        }

        // Migrate individual requirement files
        const requirementFiles = await this.findJsonFiles(requirementsPath);
        for (const file of requirementFiles) {
          if (file.includes('req-') && file.endsWith('.json') && !file.includes('requirements.json')) {
            const requirement = await this.readJsonFile(file);
            if (requirement) {
              await this.migrateProductRequirement(requirement);
              count++;
            }
          }
        }

        // Migrate templates if they exist
        const templatesFile = path.join(requirementsPath, 'templates.json');
        if (await this.pathExists(templatesFile)) {
          const templates = await this.readJsonFile(templatesFile);
          if (Array.isArray(templates)) {
            for (const template of templates) {
              await this.migrateRequirementTemplate(template);
              count++;
            }
          }
        }
      }
    } catch (error) {
      errors.push(`Product requirements data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual product requirement
   */
  private async migrateProductRequirement(requirement: any): Promise<void> {
    const id = requirement.id || `req-${randomUUID()}`;
    const projectId = requirement.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO product_requirements 
       (id, project_id, name, type, description, acceptance_criteria, priority, status,
        related_stories, tags, created_at, updated_at, created_by, approved_by, approved_at,
        version, parent_requirement_id, child_requirement_ids, dependencies,
        business_value, technical_notes, test_criteria, compliance_requirements,
        estimated_effort, actual_effort, target_release, documentation_links) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        requirement.name || 'Untitled Requirement',
        requirement.type || 'functional',
        requirement.description || '',
        JSON.stringify(requirement.acceptanceCriteria || requirement.acceptance_criteria || []),
        requirement.priority || 'medium',
        requirement.status || 'draft',
        JSON.stringify(requirement.relatedStories || requirement.related_stories || []),
        JSON.stringify(requirement.tags || []),
        requirement.createdAt || requirement.created_at ? new Date(requirement.createdAt || requirement.created_at).getTime() : now,
        requirement.updatedAt || requirement.updated_at ? new Date(requirement.updatedAt || requirement.updated_at).getTime() : now,
        requirement.createdBy || requirement.created_by || 'system',
        requirement.approvedBy || requirement.approved_by || null,
        requirement.approvedAt || requirement.approved_at ? new Date(requirement.approvedAt || requirement.approved_at).getTime() : null,
        requirement.version || 1,
        requirement.parentRequirementId || requirement.parent_requirement_id || null,
        JSON.stringify(requirement.childRequirementIds || requirement.child_requirement_ids || []),
        JSON.stringify(requirement.dependencies || []),
        requirement.businessValue || requirement.business_value || null,
        requirement.technicalNotes || requirement.technical_notes || null,
        JSON.stringify(requirement.testCriteria || requirement.test_criteria || []),
        JSON.stringify(requirement.complianceRequirements || requirement.compliance_requirements || []),
        requirement.estimatedEffort || requirement.estimated_effort || null,
        requirement.actualEffort || requirement.actual_effort || null,
        requirement.targetRelease || requirement.target_release || null,
        JSON.stringify(requirement.documentationLinks || requirement.documentation_links || [])
      ]
    );

    // Migrate repositories
    if (requirement.repositories && Array.isArray(requirement.repositories)) {
      for (const repo of requirement.repositories) {
        await this.dbRun(
          `INSERT OR REPLACE INTO requirement_repositories 
           (id, requirement_id, project_id, name, implementation_status, branch, pr_url, notes, created_at, updated_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            repo.id || `repo-${randomUUID()}`,
            id,
            projectId,
            repo.name || 'unknown',
            repo.implementationStatus || repo.implementation_status || 'not_started',
            repo.branch || null,
            repo.prUrl || repo.pr_url || null,
            repo.notes || null,
            now,
            now
          ]
        );
      }
    }

    // Migrate story links
    if (requirement.storyLinks && Array.isArray(requirement.storyLinks)) {
      for (const link of requirement.storyLinks) {
        await this.dbRun(
          `INSERT OR REPLACE INTO requirement_story_links 
           (id, requirement_id, story_id, project_id, link_type, notes, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            link.id || `link-${randomUUID()}`,
            id,
            link.storyId || link.story_id,
            projectId,
            link.linkType || link.link_type || 'implements',
            link.notes || null,
            link.createdAt || link.created_at ? new Date(link.createdAt || link.created_at).getTime() : now
          ]
        );
      }
    }

    // Migrate change history if available
    if (requirement.changes && Array.isArray(requirement.changes)) {
      for (const change of requirement.changes) {
        await this.dbRun(
          `INSERT OR REPLACE INTO requirement_changes 
           (id, requirement_id, project_id, field, old_value, new_value, changed_by, changed_at, reason) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            change.id || `change-${randomUUID()}`,
            id,
            projectId,
            change.field || 'unknown',
            change.oldValue || change.old_value || null,
            change.newValue || change.new_value || null,
            change.changedBy || change.changed_by || 'system',
            change.changedAt || change.changed_at ? new Date(change.changedAt || change.changed_at).getTime() : now,
            change.reason || null
          ]
        );
      }
    }
  }

  /**
   * Migrate requirement template
   */
  private async migrateRequirementTemplate(template: any): Promise<void> {
    const id = template.id || `template-${randomUUID()}`;
    const projectId = template.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO requirement_templates 
       (id, project_id, name, type, description_template, acceptance_criteria_template, 
        test_criteria_template, tags, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        template.name || 'Untitled Template',
        template.type || 'functional',
        template.descriptionTemplate || template.description_template || '',
        JSON.stringify(template.acceptanceCriteriaTemplate || template.acceptance_criteria_template || []),
        JSON.stringify(template.testCriteriaTemplate || template.test_criteria_template || []),
        JSON.stringify(template.tags || []),
        template.createdAt || template.created_at ? new Date(template.createdAt || template.created_at).getTime() : now,
        template.updatedAt || template.updated_at ? new Date(template.updatedAt || template.updated_at).getTime() : now
      ]
    );
  }

  /**
   * Migrate testing framework module data
   */
  private async migrateTestingFrameworkData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const testingPath = path.join(this.legacyDataPath, 'testing-framework');
      
      // Migrate test history
      const historyFile = path.join(testingPath, 'test-history.json');
      if (await this.pathExists(historyFile)) {
        const history = await this.readJsonFile(historyFile);
        if (Array.isArray(history)) {
          for (const result of history) {
            await this.migrateTestResult(result);
            count++;
          }
        }
      }

      // Migrate flaky tests
      const flakyFile = path.join(testingPath, 'flaky-tests.json');
      if (await this.pathExists(flakyFile)) {
        const flakyTests = await this.readJsonFile(flakyFile);
        if (flakyTests && typeof flakyTests === 'object') {
          for (const key in flakyTests) {
            const test = flakyTests[key];
            if (test) {
              await this.migrateFlakyTest(test);
              count++;
            }
          }
        }
      }

      // Migrate test baselines
      const baselineFile = path.join(testingPath, 'test-baselines.json');
      if (await this.pathExists(baselineFile)) {
        const baselines = await this.readJsonFile(baselineFile);
        if (baselines && typeof baselines === 'object') {
          for (const name in baselines) {
            const baseline = baselines[name];
            if (baseline && baseline.id) {
              await this.migrateTestBaseline(baseline, name);
              count++;
            }
          }
        }
      }

      // Migrate test configurations
      const configFile = path.join(testingPath, 'test-configs.json');
      if (await this.pathExists(configFile)) {
        const configs = await this.readJsonFile(configFile);
        if (configs && typeof configs === 'object') {
          for (const framework in configs) {
            const config = configs[framework];
            if (config) {
              await this.migrateTestConfiguration(config, framework);
              count++;
            }
          }
        }
      }
    } catch (error) {
      errors.push(`Testing framework data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual test result
   */
  private async migrateTestResult(result: any): Promise<void> {
    const id = result.id || `test-${randomUUID()}`;
    const projectId = result.projectId || 'default-project';
    const timestamp = result.timestamp ? new Date(result.timestamp).getTime() : Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO test_results 
       (id, project_id, timestamp, branch, commit, framework, command, duration,
        summary_total, summary_passed, summary_failed, summary_skipped, summary_pending,
        summary_success_rate, coverage_data, tags, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        timestamp,
        result.branch || null,
        result.commit || null,
        result.framework || 'unknown',
        result.command || 'npm test',
        result.duration || 0,
        result.summary?.total || 0,
        result.summary?.passed || 0,
        result.summary?.failed || 0,
        result.summary?.skipped || 0,
        result.summary?.pending || 0,
        result.summary?.successRate || 0,
        result.coverage ? JSON.stringify(result.coverage) : null,
        JSON.stringify(result.tags || []),
        timestamp
      ]
    );

    // Migrate test suites
    if (result.suites && Array.isArray(result.suites)) {
      for (const suite of result.suites) {
        const suiteId = suite.id || `suite-${randomUUID()}`;
        
        await this.dbRun(
          `INSERT OR REPLACE INTO test_suites 
           (id, result_id, name, path, status, duration, timestamp, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            suiteId,
            id,
            suite.name || 'Unknown Suite',
            suite.path || '',
            suite.status || 'passed',
            suite.duration || null,
            timestamp,
            timestamp
          ]
        );

        // Migrate test cases
        if (suite.tests && Array.isArray(suite.tests)) {
          for (const test of suite.tests) {
            const caseId = test.id || `case-${randomUUID()}`;
            
            await this.dbRun(
              `INSERT OR REPLACE INTO test_cases 
               (id, suite_id, result_id, name, status, duration, error_message, error_stack,
                error_expected, error_actual, error_type, assertions, skipped, tags, created_at) 
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
              [
                caseId,
                suiteId,
                id,
                test.name || 'Unknown Test',
                test.status || 'passed',
                test.duration || null,
                test.error?.message || null,
                test.error?.stack || null,
                test.error?.expected ? JSON.stringify(test.error.expected) : null,
                test.error?.actual ? JSON.stringify(test.error.actual) : null,
                test.error?.type || null,
                test.assertions || null,
                test.skipped || false,
                JSON.stringify(test.tags || []),
                timestamp
              ]
            );
          }
        }
      }
    }

    // Migrate coverage files
    if (result.coverage?.files && Array.isArray(result.coverage.files)) {
      for (const file of result.coverage.files) {
        const coverageId = `coverage-${randomUUID()}`;
        
        await this.dbRun(
          `INSERT OR REPLACE INTO test_coverage_files 
           (id, result_id, path, lines_total, lines_covered, lines_skipped, lines_percentage,
            statements_total, statements_covered, statements_skipped, statements_percentage,
            functions_total, functions_covered, functions_skipped, functions_percentage,
            branches_total, branches_covered, branches_skipped, branches_percentage,
            uncovered_lines, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            coverageId,
            id,
            file.path || '',
            file.lines?.total || 0,
            file.lines?.covered || 0,
            file.lines?.skipped || 0,
            file.lines?.percentage || 0,
            file.statements?.total || 0,
            file.statements?.covered || 0,
            file.statements?.skipped || 0,
            file.statements?.percentage || 0,
            file.functions?.total || 0,
            file.functions?.covered || 0,
            file.functions?.skipped || 0,
            file.functions?.percentage || 0,
            file.branches?.total || 0,
            file.branches?.covered || 0,
            file.branches?.skipped || 0,
            file.branches?.percentage || 0,
            JSON.stringify(file.uncoveredLines || []),
            timestamp
          ]
        );
      }
    }
  }

  /**
   * Migrate flaky test
   */
  private async migrateFlakyTest(test: any): Promise<void> {
    const id = test.testId || test.id || `flaky-${randomUUID()}`;
    const projectId = test.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO flaky_tests 
       (id, project_id, test_name, suite_name, failure_rate, total_runs,
        failures, passes, first_seen, last_seen, recent_runs, error_patterns,
        created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        test.name || test.testName || 'Unknown Test',
        test.suite || test.suiteName || 'Unknown Suite',
        test.failureRate || 0,
        test.totalRuns || 0,
        test.failures || 0,
        test.passes || 0,
        test.firstSeen ? new Date(test.firstSeen).getTime() : now,
        test.lastSeen ? new Date(test.lastSeen).getTime() : now,
        JSON.stringify(test.recentRuns || []),
        JSON.stringify(test.errorPatterns || []),
        now,
        now
      ]
    );
  }

  /**
   * Migrate test baseline
   */
  private async migrateTestBaseline(baseline: any, name: string): Promise<void> {
    const id = `baseline-${randomUUID()}`;
    const projectId = baseline.projectId || 'default-project';
    const now = Date.now();

    // First check if the test result exists
    const resultCheck = await this.dbGet(
      'SELECT id FROM test_results WHERE id = ?',
      [baseline.id]
    );

    if (resultCheck.success && resultCheck.data) {
      await this.dbRun(
        `INSERT OR REPLACE INTO test_baselines 
         (id, project_id, result_id, name, created_at) 
         VALUES (?, ?, ?, ?, ?)`,
        [
          id,
          projectId,
          baseline.id,
          name || 'baseline',
          now
        ]
      );
    }
  }

  /**
   * Migrate test configuration
   */
  private async migrateTestConfiguration(config: any, framework: string): Promise<void> {
    const id = config.id || `config-${randomUUID()}`;
    const projectId = config.projectId || 'default-project';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO test_configurations 
       (id, project_id, framework, command, config_file, test_match,
        coverage_threshold_global, coverage_threshold_paths, setup_files,
        test_environment, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        framework,
        config.command || 'npm test',
        config.configFile || null,
        JSON.stringify(config.testMatch || []),
        config.coverageThreshold?.global ? JSON.stringify(config.coverageThreshold.global) : null,
        config.coverageThreshold ? JSON.stringify(
          Object.entries(config.coverageThreshold)
            .filter(([key]) => key !== 'global')
            .reduce((acc, [key, val]) => ({ ...acc, [key]: val }), {})
        ) : '{}',
        JSON.stringify(config.setupFiles || []),
        config.testEnvironment || null,
        now,
        now
      ]
    );
  }

  /**
   * Helper methods
   */
  private async hasLegacyData(): Promise<boolean> {
    const possiblePaths = [
      path.join(this.legacyDataPath, 'agile'),
      path.join(this.legacyDataPath, 'kanban'),
      path.join(this.legacyDataPath, 'documentation'),
      path.join(this.legacyDataPath, 'business'),
      path.join(this.legacyDataPath, 'memory')
    ];

    for (const p of possiblePaths) {
      if (await this.pathExists(p)) {
        return true;
      }
    }

    return false;
  }

  private async pathExists(filePath: string): Promise<boolean> {
    try {
      await fs.access(filePath);
      return true;
    } catch {
      return false;
    }
  }

  private async readJsonFile(filePath: string, errors?: string[]): Promise<any> {
    try {
      const content = await fs.readFile(filePath, 'utf-8');
      return JSON.parse(content);
    } catch (error) {
      const errorMsg = `Failed to read JSON file ${filePath}: ${error instanceof Error ? error.message : 'Unknown error'}`;
      console.warn(errorMsg);
      if (errors) {
        errors.push(errorMsg);
      }
      return null;
    }
  }

  private async findJsonFiles(dir: string): Promise<string[]> {
    const files: string[] = [];
    
    try {
      const entries = await fs.readdir(dir, { withFileTypes: true });
      
      for (const entry of entries) {
        const fullPath = path.join(dir, entry.name);
        
        if (entry.isDirectory()) {
          const subFiles = await this.findJsonFiles(fullPath);
          files.push(...subFiles);
        } else if (entry.isFile() && entry.name.endsWith('.json')) {
          files.push(fullPath);
        }
      }
    } catch (error) {
      // Directory doesn't exist or can't be read
    }
    
    return files;
  }

  /**
   * Migrate code analysis module data
   */
  private async migrateCodeAnalysisData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const codeAnalysisPath = path.join(this.legacyDataPath, 'code-analysis');
      
      // Migrate analysis history
      const analysisHistoryFile = path.join(codeAnalysisPath, 'analysis-history.json');
      if (await this.pathExists(analysisHistoryFile)) {
        const history = await this.readJsonFile(analysisHistoryFile);
        if (Array.isArray(history)) {
          for (const result of history) {
            await this.migrateAnalysisResult(result);
            count++;
          }
        }
      }

      // Migrate code reviews
      const codeReviewsFile = path.join(codeAnalysisPath, 'code-reviews.json');
      if (await this.pathExists(codeReviewsFile)) {
        const reviews = await this.readJsonFile(codeReviewsFile);
        if (Array.isArray(reviews)) {
          for (const review of reviews) {
            await this.migrateCodeReview(review);
            count++;
          }
        }
      }

      // Migrate metrics history files
      const metricsFiles = await this.findJsonFiles(codeAnalysisPath);
      for (const file of metricsFiles) {
        if (file.includes('metrics-') && file.endsWith('.json') && 
            !file.includes('analysis-history') && !file.includes('code-reviews')) {
          const metrics = await this.readJsonFile(file);
          if (metrics && metrics.entries && Array.isArray(metrics.entries)) {
            for (const entry of metrics.entries) {
              await this.migrateMetricsHistoryEntry(entry, metrics.projectId);
              count++;
            }
            
            // Migrate baseline if available
            if (metrics.baseline) {
              await this.migrateMetricsBaseline(metrics.baseline, metrics.projectId);
              count++;
            }
          }
        }
      }

      // Migrate custom rules files
      const rulesFiles = await this.findJsonFiles(codeAnalysisPath);
      for (const file of rulesFiles) {
        if (file.includes('rules-') && file.endsWith('.json')) {
          const rules = await this.readJsonFile(file);
          if (Array.isArray(rules)) {
            const projectId = this.extractProjectIdFromPath(file);
            for (const rule of rules) {
              await this.migrateCustomRule(rule, projectId);
              count++;
            }
          }
        }
      }

    } catch (error) {
      errors.push(`Code analysis data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate individual analysis result
   */
  private async migrateAnalysisResult(result: any): Promise<void> {
    const id = result.id || `analysis-${randomUUID()}`;
    const projectId = result.projectId || 'default-project';
    const timestamp = result.timestamp ? new Date(result.timestamp).getTime() : Date.now();

    // Calculate summary metrics from files
    const files = result.files || [];
    const filesAnalyzed = files.length;
    const averageComplexity = filesAnalyzed > 0 
      ? files.reduce((sum: number, f: any) => sum + (f.metrics?.complexity?.average || 0), 0) / filesAnalyzed 
      : 0;
    const averageMaintainability = filesAnalyzed > 0 
      ? files.reduce((sum: number, f: any) => sum + (f.metrics?.maintainability?.average || 0), 0) / filesAnalyzed 
      : 0;
    const totalIssues = files.reduce((sum: number, f: any) => sum + (f.issues?.length || 0), 0);
    const criticalIssues = files.reduce((sum: number, f: any) => 
      sum + (f.issues?.filter((i: any) => i.severity === 'critical' || i.severity === 'high')?.length || 0), 0);
    const duplicationPercentage = filesAnalyzed > 0 
      ? files.reduce((sum: number, f: any) => sum + (f.metrics?.duplication?.percentage || 0), 0) / filesAnalyzed 
      : 0;

    const overallScore = result.summary?.overallScore || 
      Math.max(0, Math.round(100 - (averageComplexity * 5) - (totalIssues * 2) - duplicationPercentage));
    const overallGrade = result.summary?.overallGrade || 
      (overallScore >= 90 ? 'A' : overallScore >= 80 ? 'B' : overallScore >= 70 ? 'C' : overallScore >= 60 ? 'D' : 'F');

    await this.dbRun(
      `INSERT OR REPLACE INTO code_analysis_results 
       (id, project_id, timestamp, files_analyzed, average_complexity, average_maintainability,
        total_issues, critical_issues, duplication_percentage, overall_score, overall_grade,
        options, recommendations, baseline_comparison, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        timestamp,
        filesAnalyzed,
        averageComplexity,
        averageMaintainability,
        totalIssues,
        criticalIssues,
        duplicationPercentage,
        overallScore,
        overallGrade,
        JSON.stringify({}), // options
        JSON.stringify(result.recommendations || []),
        JSON.stringify({}), // baseline comparison
        timestamp
      ]
    );

    // Migrate file results
    for (const file of files) {
      await this.migrateFileAnalysis(file, id, projectId, timestamp);
    }
  }

  /**
   * Migrate individual file analysis
   */
  private async migrateFileAnalysis(file: any, analysisId: string, projectId: string, timestamp: number): Promise<void> {
    const fileResultId = `file-${randomUUID()}`;
    const metrics = file.metrics || {};
    const complexity = metrics.complexity || {};
    const maintainability = metrics.maintainability || {};
    const duplication = metrics.duplication || {};
    const quality = metrics.quality || {};

    await this.dbRun(
      `INSERT OR REPLACE INTO code_analysis_file_results 
       (id, analysis_id, project_id, file_path, file_size, language, last_modified,
        cyclomatic_complexity, cognitive_complexity, halstead_difficulty, halstead_volume, halstead_effort,
        max_complexity, total_complexity, function_complexities,
        maintainability_index, maintainability_rating, line_count, comment_ratio, test_coverage,
        duplication_percentage, duplicate_blocks, duplicated_lines, total_lines,
        total_issues, high_issues, medium_issues, low_issues, code_smells, technical_debt,
        quality_score, quality_grade, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        fileResultId,
        analysisId,
        projectId,
        file.path || '',
        file.size || 0,
        file.language || 'unknown',
        file.lastModified ? new Date(file.lastModified).getTime() : timestamp,
        complexity.cyclomatic || complexity.average || 0,
        complexity.cognitive || complexity.average || 0,
        complexity.halstead?.difficulty || 0,
        complexity.halstead?.volume || 0,
        complexity.halstead?.effort || 0,
        complexity.max || 0,
        complexity.total || 0,
        JSON.stringify(complexity.functions || []),
        maintainability.index || maintainability.average || 0,
        maintainability.rating || 'F',
        0, // line count - would need to be calculated
        0, 0, // comment ratio, test coverage
        duplication.percentage || 0,
        JSON.stringify(duplication.duplicateBlocks || []),
        duplication.duplicatedLines || 0,
        duplication.totalLines || 0,
        file.issues?.length || 0,
        file.issues?.filter((i: any) => i.severity === 'high' || i.severity === 'critical')?.length || 0,
        file.issues?.filter((i: any) => i.severity === 'medium')?.length || 0,
        file.issues?.filter((i: any) => i.severity === 'low')?.length || 0,
        quality.codeSmells || 0,
        quality.technicalDebt || 0,
        quality.score || 0,
        quality.grade || 'F',
        timestamp
      ]
    );

    // Migrate issues
    if (file.issues && Array.isArray(file.issues)) {
      for (const issue of file.issues) {
        await this.migrateCodeIssue(issue, analysisId, fileResultId, projectId, file.path);
      }
    }
  }

  /**
   * Migrate individual code issue
   */
  private async migrateCodeIssue(issue: any, analysisId: string, fileResultId: string, projectId: string, filePath: string): Promise<void> {
    const issueId = `issue-${randomUUID()}`;

    await this.dbRun(
      `INSERT OR REPLACE INTO code_analysis_issues 
       (id, analysis_id, file_result_id, project_id, issue_type, severity, rule, message,
        file_path, line_number, column_number, end_line_number, end_column_number,
        is_fixable, category, suggestion, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        issueId,
        analysisId,
        fileResultId,
        projectId,
        issue.type || 'warning',
        issue.severity || 'medium',
        issue.rule || 'unknown',
        issue.message || '',
        filePath || issue.location?.file || '',
        issue.location?.line || issue.line || 1,
        issue.location?.column || issue.column || 1,
        issue.location?.endLine || null,
        issue.location?.endColumn || null,
        issue.fixable || false,
        issue.category || 'style',
        issue.suggestion || null,
        Date.now()
      ]
    );
  }

  /**
   * Migrate individual code review
   */
  private async migrateCodeReview(review: any): Promise<void> {
    const id = review.id || `review-${randomUUID()}`;
    const projectId = review.projectId || 'default-project';
    const timestamp = review.timestamp ? new Date(review.timestamp).getTime() : Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO code_reviews 
       (id, project_id, file_path, file_hash, file_name, timestamp, overall_score,
        summary, approved, comparison_data, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        review.file || '',
        review.fileHash || null,
        review.fileName || null,
        timestamp,
        review.score || review.overallScore || 0,
        review.summary || '',
        review.approved || false,
        JSON.stringify(review.comparison || {}),
        timestamp
      ]
    );

    // Migrate findings
    if (review.findings && Array.isArray(review.findings)) {
      for (const finding of review.findings) {
        await this.migrateCodeReviewFinding(finding, id, projectId, review.file);
      }
    }

    // Migrate suggestions
    if (review.suggestions && Array.isArray(review.suggestions)) {
      for (const suggestion of review.suggestions) {
        await this.migrateCodeReviewSuggestion(suggestion, id, projectId, review.file);
      }
    }
  }

  /**
   * Migrate code review finding
   */
  private async migrateCodeReviewFinding(finding: any, reviewId: string, projectId: string, filePath: string): Promise<void> {
    const findingId = `finding-${randomUUID()}`;

    await this.dbRun(
      `INSERT OR REPLACE INTO code_review_findings 
       (id, review_id, project_id, finding_type, severity, title, description,
        file_path, line_number, column_number, end_line_number, end_column_number,
        suggested_fix, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        findingId,
        reviewId,
        projectId,
        finding.type || 'improvement',
        finding.severity || 'medium',
        finding.title || '',
        finding.description || '',
        filePath || finding.location?.file || '',
        finding.location?.line || 1,
        finding.location?.column || 1,
        finding.location?.endLine || null,
        finding.location?.endColumn || null,
        finding.suggestedFix || null,
        Date.now()
      ]
    );
  }

  /**
   * Migrate code review suggestion
   */
  private async migrateCodeReviewSuggestion(suggestion: any, reviewId: string, projectId: string, filePath: string): Promise<void> {
    const suggestionId = `suggestion-${randomUUID()}`;

    await this.dbRun(
      `INSERT OR REPLACE INTO code_review_suggestions 
       (id, review_id, project_id, title, description, priority, suggestion_type,
        file_path, line_number, column_number, example, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        suggestionId,
        reviewId,
        projectId,
        suggestion.title || '',
        suggestion.description || '',
        suggestion.priority || 'medium',
        suggestion.type || null,
        filePath || null,
        suggestion.location?.line || null,
        suggestion.location?.column || null,
        suggestion.example || null,
        Date.now()
      ]
    );
  }

  /**
   * Migrate metrics history entry
   */
  private async migrateMetricsHistoryEntry(entry: any, projectId: string): Promise<void> {
    const id = entry.id || `history-${randomUUID()}`;
    const timestamp = entry.timestamp ? new Date(entry.timestamp).getTime() : Date.now();
    const metrics = entry.metrics || {};

    await this.dbRun(
      `INSERT OR REPLACE INTO code_metrics_history 
       (id, project_id, timestamp, branch, commit_hash, tags,
        average_complexity, max_complexity, total_complexity,
        average_maintainability, min_maintainability, duplication_percentage,
        total_issues, high_issues, medium_issues, low_issues,
        quality_score, quality_grade, raw_metrics, summary_data, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId || 'default-project',
        timestamp,
        entry.branch || null,
        entry.commit || null,
        JSON.stringify(entry.tags || []),
        metrics.complexity?.average || 0,
        metrics.complexity?.max || 0,
        metrics.complexity?.total || 0,
        metrics.maintainability?.average || 0,
        metrics.maintainability?.min || 100,
        metrics.duplication?.percentage || 0,
        metrics.quality?.issues?.total || 0,
        metrics.quality?.issues?.high || 0,
        metrics.quality?.issues?.medium || 0,
        metrics.quality?.issues?.low || 0,
        metrics.quality?.score || 0,
        metrics.quality?.grade || 'F',
        JSON.stringify(metrics),
        JSON.stringify(entry.summary || {}),
        timestamp
      ]
    );
  }

  /**
   * Migrate metrics baseline
   */
  private async migrateMetricsBaseline(baseline: any, projectId: string): Promise<void> {
    const id = `baseline-${randomUUID()}`;

    // First create a metrics history entry if it doesn't exist
    const historyId = baseline.id || `history-${randomUUID()}`;
    const timestamp = baseline.timestamp ? new Date(baseline.timestamp).getTime() : Date.now();

    // Check if history entry exists
    const existingHistory = await this.dbGet(
      'SELECT id FROM code_metrics_history WHERE id = ?',
      [historyId]
    );

    if (!existingHistory.success || !existingHistory.data) {
      // Create the history entry
      await this.migrateMetricsHistoryEntry(baseline, projectId);
    }

    await this.dbRun(
      `INSERT OR REPLACE INTO code_metrics_baselines 
       (id, project_id, metrics_history_id, name, commit_hash, created_at)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId || 'default-project',
        historyId,
        'baseline',
        baseline.commit || null,
        timestamp
      ]
    );
  }

  /**
   * Migrate custom rule
   */
  private async migrateCustomRule(rule: any, projectId: string): Promise<void> {
    const id = rule.id || `rule-${randomUUID()}`;
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO code_analysis_custom_rules 
       (id, project_id, name, description, pattern, severity, category, message,
        is_active, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId || 'default-project',
        rule.name || 'Unnamed Rule',
        rule.description || '',
        rule.pattern || '',
        rule.severity || 'warning',
        rule.category || 'style',
        rule.message || '',
        true,
        now,
        now
      ]
    );
  }

  /**
   * Extract project ID from file path
   */
  private extractProjectIdFromPath(filePath: string): string {
    const match = filePath.match(/rules-([^.]+)\.json$/);
    return match ? match[1] : 'default-project';
  }

  /**
   * Migrate deployment management data
   */
  private async migrateDeploymentData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Migrate deployment config
      const configFile = path.join(this.legacyDataPath, 'deployment-management', 'deployment-config.json');
      if (await this.pathExists(configFile)) {
        const config = await this.readJsonFile(configFile);
        if (config) {
          await this.migrateDeploymentConfig(config);
          count++;

          // Migrate environments
          if (config.environments && Array.isArray(config.environments)) {
            for (const env of config.environments) {
              await this.migrateDeploymentEnvironment(env, config.projectId || 'default');
              count++;
            }
          }
        }
      }

      // Migrate deployments
      const deploymentsDir = path.join(this.legacyDataPath, 'deployment-management', 'deployments');
      if (await this.pathExists(deploymentsDir)) {
        const deploymentFiles = await this.findJsonFiles(deploymentsDir);
        for (const file of deploymentFiles) {
          const deployments = await this.readJsonFile(file);
          if (Array.isArray(deployments)) {
            for (const deployment of deployments) {
              await this.migrateDeployment(deployment);
              count++;
            }
          }
        }
      }

      // Migrate deployment plans
      const plansFile = path.join(this.legacyDataPath, 'deployment-management', 'deployment-plans.json');
      if (await this.pathExists(plansFile)) {
        const plans = await this.readJsonFile(plansFile);
        if (Array.isArray(plans)) {
          for (const plan of plans) {
            await this.migrateDeploymentPlan(plan);
            count++;
          }
        }
      }

      // Migrate release notes
      const releaseNotesFile = path.join(this.legacyDataPath, 'deployment-management', 'release-notes.json');
      if (await this.pathExists(releaseNotesFile)) {
        const releaseNotes = await this.readJsonFile(releaseNotesFile);
        if (Array.isArray(releaseNotes)) {
          for (const release of releaseNotes) {
            await this.migrateReleaseNotes(release);
            count++;
          }
        }
      }

      // Migrate migrations
      const migrationsFile = path.join(this.legacyDataPath, 'deployment-management', 'migrations.json');
      if (await this.pathExists(migrationsFile)) {
        const migrations = await this.readJsonFile(migrationsFile);
        if (Array.isArray(migrations)) {
          for (const migration of migrations) {
            await this.migrateMigration(migration);
            count++;
          }
        }
      }

    } catch (error) {
      errors.push(`Deployment data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate deployment config
   */
  private async migrateDeploymentConfig(config: any): Promise<void> {
    const id = config.id || randomUUID();
    const projectId = config.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO deployment_configs 
       (id, project_id, provider, settings, hooks, health_checks, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        config.provider || 'custom',
        JSON.stringify(config.settings || {}),
        JSON.stringify(config.hooks || {}),
        JSON.stringify(config.healthChecks || []),
        now,
        now
      ]
    );
  }

  /**
   * Migrate deployment environment
   */
  private async migrateDeploymentEnvironment(env: any, projectId: string): Promise<void> {
    const id = env.id || randomUUID();
    const now = Date.now();

    // Get config ID
    const configResult = await this.dbGet<{ id: string }>(
      'SELECT id FROM deployment_configs WHERE project_id = ? LIMIT 1',
      [projectId]
    );
    const configId = configResult.data?.id || randomUUID();

    await this.dbRun(
      `INSERT OR REPLACE INTO deployment_environments 
       (id, project_id, config_id, name, type, url, branch, auto_deploy_branch, 
        variables, status, last_deployment_id, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        configId,
        env.name || 'unnamed',
        env.type || 'custom',
        env.url || null,
        env.branch || null,
        env.autoDeployBranch || null,
        JSON.stringify(env.variables || []),
        env.status || 'inactive',
        env.lastDeployment?.id || null,
        env.createdAt ? new Date(env.createdAt).getTime() : now,
        env.updatedAt ? new Date(env.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate deployment
   */
  private async migrateDeployment(deployment: any): Promise<void> {
    const id = deployment.id || randomUUID();
    const projectId = deployment.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO deployments 
       (id, project_id, environment_id, version, commit, branch, author, 
        status, type, started_at, completed_at, duration, logs, 
        rollback_from, metadata, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        deployment.environmentId || 'unknown',
        deployment.version || 'unknown',
        deployment.commit || null,
        deployment.branch || null,
        deployment.author || null,
        deployment.status || 'pending',
        deployment.type || 'manual',
        deployment.startedAt ? new Date(deployment.startedAt).getTime() : now,
        deployment.completedAt ? new Date(deployment.completedAt).getTime() : null,
        deployment.duration || null,
        JSON.stringify(deployment.logs || []),
        deployment.rollbackFrom || null,
        JSON.stringify(deployment.metadata || {}),
        now
      ]
    );
  }

  /**
   * Migrate deployment plan
   */
  private async migrateDeploymentPlan(plan: any): Promise<void> {
    const id = plan.id || randomUUID();
    const projectId = plan.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO deployment_plans 
       (id, project_id, environment, version, changes, estimated_duration, 
        risks, approval_required, rollback_plan, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        plan.environment || 'unknown',
        plan.version || 'unknown',
        JSON.stringify(plan.changes || []),
        plan.estimatedDuration || 0,
        JSON.stringify(plan.risks || []),
        plan.approvalRequired || false,
        plan.rollbackPlan ? JSON.stringify(plan.rollbackPlan) : null,
        now
      ]
    );
  }

  /**
   * Migrate release notes
   */
  private async migrateReleaseNotes(release: any): Promise<void> {
    const id = release.id || randomUUID();
    const projectId = release.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO release_notes 
       (id, project_id, version, date, features, fixes, breaking_changes, 
        deprecated, contributors, acknowledgments, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        release.version || 'unknown',
        release.date ? new Date(release.date).getTime() : now,
        JSON.stringify(release.changes?.features || []),
        JSON.stringify(release.changes?.fixes || []),
        JSON.stringify(release.changes?.breaking || []),
        JSON.stringify(release.changes?.deprecated || []),
        JSON.stringify(release.contributors || []),
        JSON.stringify(release.acknowledgments || []),
        now
      ]
    );
  }

  /**
   * Migrate migration record
   */
  private async migrateMigration(migration: any): Promise<void> {
    const id = migration.id || randomUUID();
    const projectId = migration.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO migrations 
       (id, project_id, version, name, description, type, status, checksum, 
        up_script, down_script, dependencies, tags, author, created_at, 
        executed_at, duration, error, logs) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        migration.version || 'unknown',
        migration.name || 'unnamed',
        migration.description || null,
        migration.type || 'schema',
        migration.status || 'pending',
        migration.checksum || '',
        JSON.stringify(migration.up || { type: 'sql', content: '', transaction: true }),
        migration.down ? JSON.stringify(migration.down) : null,
        JSON.stringify(migration.dependencies || []),
        JSON.stringify(migration.tags || []),
        migration.author || null,
        migration.createdAt ? new Date(migration.createdAt).getTime() : now,
        migration.executedAt ? new Date(migration.executedAt).getTime() : null,
        migration.duration || null,
        migration.error || null,
        JSON.stringify(migration.logs || [])
      ]
    );
  }

  /**
   * Migrate security data
   */
  private async migrateSecurityData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Migrate security policies
      const policiesFile = path.join(this.legacyDataPath, 'security', 'policies.json');
      if (await this.pathExists(policiesFile)) {
        const policies = await this.readJsonFile(policiesFile);
        if (policies) {
          await this.migrateSecurityPolicy(policies);
          count++;
        }
      }

      // Migrate security scans
      const scansDir = path.join(this.legacyDataPath, 'security', 'scans');
      if (await this.pathExists(scansDir)) {
        const scanFiles = await this.findJsonFiles(scansDir);
        for (const file of scanFiles) {
          const scans = await this.readJsonFile(file);
          if (Array.isArray(scans)) {
            for (const scan of scans) {
              await this.migrateSecurityScan(scan);
              count++;
            }
          }
        }
      }

      // Migrate security events (already in security_events table)
      const eventsFile = path.join(this.legacyDataPath, 'security', 'events.json');
      if (await this.pathExists(eventsFile)) {
        const events = await this.readJsonFile(eventsFile);
        if (Array.isArray(events)) {
          for (const event of events) {
            await this.migrateSecurityEvent(event);
            count++;
          }
        }
      }

      // Migrate secrets findings
      const secretsFile = path.join(this.legacyDataPath, 'security', 'secrets.json');
      if (await this.pathExists(secretsFile)) {
        const secrets = await this.readJsonFile(secretsFile);
        if (Array.isArray(secrets)) {
          for (const secret of secrets) {
            await this.migrateSecuritySecret(secret);
            count++;
          }
        }
      }

      // Migrate vulnerabilities
      const vulnerabilitiesFile = path.join(this.legacyDataPath, 'security', 'vulnerabilities.json');
      if (await this.pathExists(vulnerabilitiesFile)) {
        const vulnerabilities = await this.readJsonFile(vulnerabilitiesFile);
        if (Array.isArray(vulnerabilities)) {
          for (const vulnerability of vulnerabilities) {
            await this.migrateSecurityVulnerability(vulnerability);
            count++;
          }
        }
      }

    } catch (error) {
      errors.push(`Security data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate security policy
   */
  private async migrateSecurityPolicy(policy: any): Promise<void> {
    const id = policy.id || randomUUID();
    const projectId = policy.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO security_policies 
       (id, project_id, name, description, require_approval_for, roles, 
        risk_thresholds, log_level, enabled, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        policy.name || 'Default Security Policy',
        policy.description || null,
        JSON.stringify(policy.requireApprovalFor || []),
        JSON.stringify(policy.roles || {}),
        JSON.stringify(policy.riskThresholds || {}),
        policy.logLevel || 'info',
        policy.enabled !== false,
        policy.createdAt ? new Date(policy.createdAt).getTime() : now,
        policy.updatedAt ? new Date(policy.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate security scan
   */
  private async migrateSecurityScan(scan: any): Promise<void> {
    const id = scan.id || randomUUID();
    const projectId = scan.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO security_scans 
       (id, project_id, scan_type, status, total_issues, critical_issues, 
        high_issues, medium_issues, low_issues, findings, recommendations, 
        scan_time, scanned_at, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        scan.scanType || scan.type || 'comprehensive',
        scan.summary?.status || scan.status || 'pass',
        scan.summary?.totalIssues || 0,
        scan.summary?.critical || 0,
        scan.summary?.high || 0,
        scan.summary?.medium || 0,
        scan.summary?.low || 0,
        JSON.stringify(scan.findings || []),
        JSON.stringify(scan.recommendations || []),
        scan.summary?.scanTime || scan.scanTime || 0,
        scan.scannedAt ? new Date(scan.scannedAt).getTime() : now,
        now
      ]
    );
  }

  /**
   * Migrate security event
   */
  private async migrateSecurityEvent(event: any): Promise<void> {
    const id = event.id || randomUUID();
    const timestamp = event.timestamp ? new Date(event.timestamp).getTime() : Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO security_events 
       (id, event_type, tool_name, user_id, context, risk_level, details, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        event.type || event.eventType || 'unknown',
        event.toolName || event.tool || null,
        event.userId || event.user || null,
        JSON.stringify(event.context || {}),
        event.riskLevel || event.severity || 'low',
        JSON.stringify(event.details || event.data || {}),
        timestamp
      ]
    );
  }

  /**
   * Migrate security secret
   */
  private async migrateSecuritySecret(secret: any): Promise<void> {
    const id = secret.id || randomUUID();
    const projectId = secret.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO security_secrets 
       (id, project_id, type, file, line, pattern, severity, status, 
        resolved_at, resolved_by, notes, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        secret.type || 'unknown',
        secret.file || 'unknown',
        secret.line || 0,
        secret.pattern || '',
        secret.severity || 'medium',
        secret.status || 'active',
        secret.resolvedAt ? new Date(secret.resolvedAt).getTime() : null,
        secret.resolvedBy || null,
        secret.notes || null,
        secret.createdAt ? new Date(secret.createdAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate security vulnerability
   */
  private async migrateSecurityVulnerability(vulnerability: any): Promise<void> {
    const id = vulnerability.id || randomUUID();
    const projectId = vulnerability.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO security_vulnerabilities 
       (id, project_id, package, version, severity, cve, cvss, description, 
        recommendation, status, patched_at, ignored_reason, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        vulnerability.package || vulnerability.name || 'unknown',
        vulnerability.version || 'unknown',
        vulnerability.severity || 'moderate',
        vulnerability.cve || null,
        vulnerability.cvss || null,
        vulnerability.description || null,
        vulnerability.recommendation || vulnerability.fix || null,
        vulnerability.status || 'active',
        vulnerability.patchedAt ? new Date(vulnerability.patchedAt).getTime() : null,
        vulnerability.ignoredReason || null,
        vulnerability.createdAt ? new Date(vulnerability.createdAt).getTime() : now,
        vulnerability.updatedAt ? new Date(vulnerability.updatedAt).getTime() : now
      ]
    );
  }

  /**
   * Migrate workflow recipes module data
   */
  private async migrateWorkflowRecipesData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // For workflow recipes, we'll check if there's any legacy data
      // Since this module is currently using mock data, we'll create some default recipes
      const defaultRecipes = [
        {
          id: randomUUID(),
          name: 'tdd_feature_implementation',
          description: 'Test-driven development workflow for implementing new features',
          category: 'development',
          riskLevel: 'medium',
          steps: [
            { name: 'Write tests', description: 'Create test cases first' },
            { name: 'Implement code', description: 'Write minimal code to pass tests' },
            { name: 'Refactor', description: 'Improve code quality while keeping tests green' }
          ],
          prerequisites: ['Test framework setup', 'Clear requirements']
        },
        {
          id: randomUUID(),
          name: 'security_review',
          description: 'Comprehensive security review process',
          category: 'security',
          riskLevel: 'high',
          steps: [
            { name: 'Code scan', description: 'Automated security scanning' },
            { name: 'Manual review', description: 'Human security expert review' },
            { name: 'Fix issues', description: 'Address identified vulnerabilities' }
          ],
          prerequisites: ['Security scanning tools', 'Security expertise']
        }
      ];

      const projectId = 'default';
      const now = Date.now();

      for (const recipe of defaultRecipes) {
        // Check if recipe already exists
        const existing = await this.dbGet(
          'SELECT id FROM workflow_recipes WHERE name = ? AND project_id = ?',
          [recipe.name, projectId]
        );

        if (!existing.data) {
          await this.dbRun(
            `INSERT INTO workflow_recipes 
             (id, project_id, name, description, category, risk_level, steps, 
              prerequisites, created_by, created_at, updated_at) 
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              recipe.id,
              projectId,
              recipe.name,
              recipe.description,
              recipe.category,
              recipe.riskLevel,
              JSON.stringify(recipe.steps),
              JSON.stringify(recipe.prerequisites),
              'system',
              now,
              now
            ]
          );
          count++;
        }
      }

    } catch (error) {
      errors.push(`Workflow recipes data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate performance monitoring data (in-memory data not persisted to files)
   * Note: Since the legacy performance monitoring used in-memory storage,
   * there's no file-based data to migrate. This method is included for completeness.
   */
  private async migratePerformanceMonitoringData(errors: string[]): Promise<number> {
    // The legacy performance monitoring module used in-memory storage only
    // No file-based data to migrate
    console.log('Performance monitoring migration: No file-based data to migrate (was in-memory only)');
    return 0;
  }

  /**
   * Migrate error analysis data
   * Note: Since the legacy error-analysis module primarily used the ErrorHandler 
   * in-memory storage with limited file persistence, there's minimal data to migrate.
   */
  private async migrateErrorAnalysisData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Check for any persisted error logs
      const errorLogsPath = path.join(this.legacyDataPath, 'errors');
      
      if (await this.pathExists(errorLogsPath)) {
        // Check for error log files
        const errorLogFile = path.join(errorLogsPath, 'error-logs.json');
        if (await this.pathExists(errorLogFile)) {
          const errorLogs = await this.readJsonFile(errorLogFile);
          if (Array.isArray(errorLogs)) {
            for (const errorLog of errorLogs) {
              await this.migrateErrorLog(errorLog);
              count++;
            }
          }
        }

        // Check for error patterns file
        const patternsFile = path.join(errorLogsPath, 'error-patterns.json');
        if (await this.pathExists(patternsFile)) {
          const patterns = await this.readJsonFile(patternsFile);
          if (Array.isArray(patterns)) {
            for (const pattern of patterns) {
              await this.migrateErrorPattern(pattern);
              count++;
            }
          }
        }

        // Check for error resolutions file
        const resolutionsFile = path.join(errorLogsPath, 'error-resolutions.json');
        if (await this.pathExists(resolutionsFile)) {
          const resolutions = await this.readJsonFile(resolutionsFile);
          if (Array.isArray(resolutions)) {
            for (const resolution of resolutions) {
              await this.migrateErrorResolution(resolution);
              count++;
            }
          }
        }
      }

      // The legacy module was primarily in-memory, so limited file data exists
      if (count === 0) {
        console.log('Error analysis migration: No file-based error data found (was primarily in-memory)');
      }

    } catch (error) {
      errors.push(`Error analysis data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }

  /**
   * Migrate error log entry
   */
  private async migrateErrorLog(errorLog: any): Promise<void> {
    const id = errorLog.id || randomUUID();
    const projectId = errorLog.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO error_logs 
       (id, project_id, timestamp, tool_name, error_type, severity, message, 
        stack_trace, context, user_action, system_state, root_cause, 
        suggestions, recovery_method, recovery_successful, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        errorLog.timestamp ? new Date(errorLog.timestamp).getTime() : now,
        errorLog.toolName || errorLog.tool || 'unknown',
        errorLog.errorType || errorLog.type || 'unknown',
        errorLog.severity || 'medium',
        errorLog.message || 'No message provided',
        errorLog.stackTrace || errorLog.stack || null,
        JSON.stringify(errorLog.context || {}),
        errorLog.userAction || null,
        errorLog.systemState || null,
        JSON.stringify(errorLog.rootCause || {}),
        JSON.stringify(errorLog.suggestions || []),
        errorLog.recoveryMethod || null,
        errorLog.recoverySuccessful ? 1 : 0,
        now
      ]
    );
  }

  /**
   * Migrate error pattern
   */
  private async migrateErrorPattern(pattern: any): Promise<void> {
    const id = pattern.id || randomUUID();
    const projectId = pattern.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO error_patterns 
       (id, project_id, pattern_name, error_type, frequency, severity, 
        description, affected_tools, first_seen, last_seen, occurrence_count, 
        time_pattern, common_context, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        pattern.patternName || pattern.title || 'Unnamed Pattern',
        pattern.errorType || pattern.type || 'unknown',
        pattern.frequency || 'Occasional',
        pattern.severity || 'medium',
        pattern.description || '',
        JSON.stringify(pattern.affectedTools || []),
        pattern.firstSeen ? new Date(pattern.firstSeen).getTime() : now,
        pattern.lastSeen ? new Date(pattern.lastSeen).getTime() : now,
        pattern.occurrenceCount || pattern.count || 0,
        pattern.timePattern || null,
        pattern.commonContext || null,
        now,
        now
      ]
    );
  }

  /**
   * Migrate error resolution
   */
  private async migrateErrorResolution(resolution: any): Promise<void> {
    const id = resolution.id || randomUUID();
    const projectId = resolution.projectId || 'default';
    const now = Date.now();

    await this.dbRun(
      `INSERT OR REPLACE INTO error_resolutions 
       (id, error_id, suggestion_id, project_id, implementation, 
        effectiveness, notes, resolved_by, resolved_at, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        resolution.errorId || randomUUID(),
        resolution.suggestionId || randomUUID(),
        projectId,
        resolution.implementation || '',
        resolution.effectiveness || null,
        resolution.notes || '',
        resolution.resolvedBy || 'system',
        resolution.resolvedAt ? new Date(resolution.resolvedAt).getTime() : now,
        now
      ]
    );
  }

  /**
   * Migrate RAG retrieval module data
   */
  private async migrateRAGData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const ragPath = path.join(this.legacyDataPath, 'rag');
      
      // Migrate vector store index
      const indexFile = path.join(ragPath, 'vector-index.json');
      if (await this.pathExists(indexFile)) {
        const indexData = await this.readJsonFile(indexFile);
        
        // Migrate documents and chunks
        if (indexData && indexData.chunks && Array.isArray(indexData.chunks)) {
          const documentMap = new Map<string, string>();
          
          // First pass - create documents
          for (const item of indexData.chunks) {
            if (item.chunk && item.chunk.documentId && !documentMap.has(item.chunk.documentId)) {
              const docId = await this.migrateRAGDocument(item.chunk);
              documentMap.set(item.chunk.documentId, docId);
              count++;
            }
          }
          
          // Second pass - create chunks
          for (const item of indexData.chunks) {
            if (item.chunk && item.chunk.documentId) {
              const docId = documentMap.get(item.chunk.documentId);
              if (docId) {
                await this.migrateRAGChunk(item.chunk, docId, item.embedding);
                count++;
              }
            }
          }
        }
        
        // Migrate document-chunk relationships
        if (indexData && indexData.documentChunks && Array.isArray(indexData.documentChunks)) {
          // Already handled in chunk migration
        }
      }
      
      // Migrate embeddings cache
      const embeddingsPath = path.join(ragPath, 'embeddings-cache');
      if (await this.pathExists(embeddingsPath)) {
        const cacheFiles = await this.findJsonFiles(embeddingsPath);
        for (const file of cacheFiles) {
          const cacheData = await this.readJsonFile(file);
          if (cacheData && typeof cacheData === 'object') {
            for (const [hash, embedding] of Object.entries(cacheData)) {
              if (embedding && typeof embedding === 'object') {
                await this.migrateEmbeddingCache(hash, embedding as any);
                count++;
              }
            }
          }
        }
      }
      
    } catch (error) {
      errors.push(`RAG data migration error: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }

    return count;
  }
  
  /**
   * Migrate RAG document
   */
  private async migrateRAGDocument(chunk: any): Promise<string> {
    const docId = chunk.documentId || randomUUID();
    const projectId = 'default';
    const now = Date.now();
    
    // Try to infer document details from chunk
    const path = chunk.documentId; // Using documentId as path for legacy data
    const title = path.split('/').pop()?.replace(/\.[^/.]+$/, '') || 'Untitled';
    
    await this.dbRun(
      `INSERT OR IGNORE INTO rag_documents 
       (id, project_id, path, content, title, size, last_modified, 
        embedding_status, chunk_count, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        docId,
        projectId,
        path,
        '', // Content not available in legacy format
        title,
        0, // Size not available
        now,
        'completed',
        0, // Will be updated later
        now,
        now
      ]
    );
    
    return docId;
  }
  
  /**
   * Migrate RAG chunk
   */
  private async migrateRAGChunk(chunk: any, documentId: string, embedding: any): Promise<void> {
    const chunkId = chunk.id || randomUUID();
    const projectId = 'default';
    const now = Date.now();
    
    // Convert embedding array to buffer if available
    let embeddingBuffer = null;
    if (embedding && Array.isArray(embedding)) {
      const float32Array = new Float32Array(embedding);
      embeddingBuffer = Buffer.from(float32Array.buffer);
    }
    
    await this.dbRun(
      `INSERT OR REPLACE INTO rag_chunks 
       (id, project_id, document_id, content, chunk_index, start_offset, 
        end_offset, chunk_type, chunk_level, language, metadata, 
        embedding_vector, embedding_model, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        chunkId,
        projectId,
        documentId,
        chunk.content || '',
        chunk.index || 0,
        chunk.metadata?.startOffset || 0,
        chunk.metadata?.endOffset || chunk.content?.length || 0,
        chunk.metadata?.type || 'paragraph',
        chunk.metadata?.level || null,
        chunk.metadata?.language || null,
        JSON.stringify(chunk.metadata || {}),
        embeddingBuffer,
        'all-MiniLM-L6-v2', // Default model
        now
      ]
    );
    
    // Update document chunk count
    await this.dbRun(
      `UPDATE rag_documents 
       SET chunk_count = chunk_count + 1 
       WHERE id = ?`,
      [documentId]
    );
  }
  
  /**
   * Migrate embedding cache entry
   */
  private async migrateEmbeddingCache(textHash: string, embedding: any): Promise<void> {
    const id = randomUUID();
    const projectId = 'default';
    const now = Date.now();
    
    // Convert embedding to buffer
    let embeddingBuffer = null;
    let dimension = 0;
    
    if (embedding.embedding && Array.isArray(embedding.embedding)) {
      dimension = embedding.embedding.length;
      const float32Array = new Float32Array(embedding.embedding);
      embeddingBuffer = Buffer.from(float32Array.buffer);
    }
    
    if (embeddingBuffer) {
      await this.dbRun(
        `INSERT OR REPLACE INTO rag_embeddings_cache 
         (id, project_id, text_hash, model_name, embedding_vector, 
          dimension, created_at, last_used) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          id,
          projectId,
          textHash,
          embedding.model || 'all-MiniLM-L6-v2',
          embeddingBuffer,
          dimension,
          embedding.timestamp || now,
          now
        ]
      );
    }
  }

  /**
   * Migrate project management data
   */
  private async migrateProjectData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      // Check if .atlas marker file exists in current directory
      const markerPath = '.atlas';
      if (await this.pathExists(markerPath)) {
        let marker;
        try {
          // Check if it's a file (not a directory)
          const stat = await fs.stat(markerPath);
          if (!stat.isFile()) {
            console.log('⚠️  .atlas is a directory, not a marker file - skipping project migration');
            return count;
          }
          
          const markerContent = await fs.readFile(markerPath, 'utf-8');
          marker = JSON.parse(markerContent);
        } catch (readError) {
          console.log('⚠️  Failed to read .atlas marker file - skipping project migration');
          return count;
        }
        
        // Check if project already exists in database
        const existing = await this.dbGet(
          'SELECT id FROM projects WHERE id = ?',
          [marker.projectId]
        );

        if (!existing.data) {
          // Migrate project to database
          const now = Date.now();
          const projectRoot = process.cwd();
          
          await this.dbRun(
            `INSERT INTO projects (id, name, description, config, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?)`,
            [
              marker.projectId,
              marker.projectName,
              `Atlas MCP project: ${marker.projectName}`,
              JSON.stringify({
                projectRoot,
                atlasVersion: marker.atlasVersion || '1.0.0',
                migratedFrom: 'file-based',
                migratedAt: now
              }),
              new Date(marker.createdAt).getTime(),
              now
            ]
          );
          
          console.log(`📦 Migrated project: ${marker.projectName} (${marker.projectId})`);
          count++;
        }
      }

      // Check for legacy project configuration files
      const configPath = path.join(this.legacyDataPath, 'config', 'project.json');
      if (await this.pathExists(configPath)) {
        const configData = await this.readJsonFile(configPath);
        
        // Update project configuration if it exists
        if (configData && configData.projectId) {
          const existing = await this.dbGet<{ id: string; config: string }>(
            'SELECT id, config FROM projects WHERE id = ?',
            [configData.projectId]
          );

          if (existing.data) {
            const currentConfig = JSON.parse(existing.data.config || '{}');
            const updatedConfig = {
              ...currentConfig,
              ...configData,
              migratedConfigAt: Date.now()
            };

            await this.dbRun(
              'UPDATE projects SET config = ?, updated_at = ? WHERE id = ?',
              [JSON.stringify(updatedConfig), Date.now(), configData.projectId]
            );
            
            console.log(`📝 Updated project configuration for: ${configData.projectId}`);
            count++;
          }
        }
      }

    } catch (error) {
      const errorMsg = `Failed to migrate project data: ${error instanceof Error ? error.message : 'Unknown error'}`;
      console.error(errorMsg);
      errors.push(errorMsg);
    }

    return count;
  }

  /**
   * Migrate local AI module data (embeddings and code memory)
   */
  private async migrateLocalAIData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const localAIPath = path.join(this.legacyDataPath, 'local-ai');
      
      // Migrate embeddings
      const embeddingsFile = path.join(localAIPath, 'embeddings.json');
      if (await this.pathExists(embeddingsFile)) {
        const embeddings = await this.readJsonFile(embeddingsFile);
        if (embeddings && embeddings.embeddings && Array.isArray(embeddings.embeddings)) {
          for (const [id, embedding] of embeddings.embeddings) {
            try {
              await this.migrateEmbedding(id, embedding);
              count++;
            } catch (error) {
              errors.push(`Failed to migrate embedding ${id}: ${error instanceof Error ? error.message : 'Unknown error'}`);
            }
          }
        }
      }

      // Migrate code memory graph data
      const memoryFile = path.join(localAIPath, 'code-memory.json');
      if (await this.pathExists(memoryFile)) {
        const memory = await this.readJsonFile(memoryFile);
        if (memory && memory.nodes) {
          for (const [nodeId, node] of Object.entries(memory.nodes)) {
            try {
              await this.migrateCodeMemoryNode(nodeId, node as any);
              count++;
            } catch (error) {
              errors.push(`Failed to migrate memory node ${nodeId}: ${error instanceof Error ? error.message : 'Unknown error'}`);
            }
          }
        }
      }

      // Migrate search history if it exists
      const searchHistoryFile = path.join(localAIPath, 'search-history.json');
      if (await this.pathExists(searchHistoryFile)) {
        const searchHistory = await this.readJsonFile(searchHistoryFile);
        if (Array.isArray(searchHistory)) {
          for (const search of searchHistory) {
            try {
              await this.migrateSearchSession(search);
              count++;
            } catch (error) {
              errors.push(`Failed to migrate search session: ${error instanceof Error ? error.message : 'Unknown error'}`);
            }
          }
        }
      }

      console.log(`📦 Migrated ${count} local AI items (embeddings, memory, searches)`);
      
    } catch (error) {
      const errorMsg = `Failed to migrate local AI data: ${error instanceof Error ? error.message : 'Unknown error'}`;
      console.error(errorMsg);
      errors.push(errorMsg);
    }

    return count;
  }

  /**
   * Migrate data management data
   */
  private async migrateDataManagementData(errors: string[]): Promise<number> {
    let count = 0;

    try {
      const dataManagementPath = path.join(this.legacyDataPath, 'data-management');
      
      // Migrate export logs
      const exportsFile = path.join(dataManagementPath, 'exports.json');
      if (await this.pathExists(exportsFile)) {
        const exports = await this.readJsonFile(exportsFile);
        if (Array.isArray(exports)) {
          for (const exportData of exports) {
            await this.migrateDataExport(exportData);
            count++;
          }
        }
      }

      // Migrate import logs
      const importsFile = path.join(dataManagementPath, 'imports.json');
      if (await this.pathExists(importsFile)) {
        const imports = await this.readJsonFile(importsFile);
        if (Array.isArray(imports)) {
          for (const importData of imports) {
            await this.migrateDataImport(importData);
            count++;
          }
        }
      }

      // Migrate storage configurations
      const storageConfigFile = path.join(dataManagementPath, 'storage-config.json');
      if (await this.pathExists(storageConfigFile)) {
        const storageConfig = await this.readJsonFile(storageConfigFile);
        if (storageConfig) {
          await this.migrateStorageConfig(storageConfig);
          count++;
        }
      }

      // Migrate sync logs
      const syncLogsFile = path.join(dataManagementPath, 'sync-logs.json');
      if (await this.pathExists(syncLogsFile)) {
        const syncLogs = await this.readJsonFile(syncLogsFile);
        if (Array.isArray(syncLogs)) {
          for (const syncLog of syncLogs) {
            await this.migrateSyncLog(syncLog);
            count++;
          }
        }
      }

      // Migrate backup records
      const backupsFile = path.join(dataManagementPath, 'backups.json');
      if (await this.pathExists(backupsFile)) {
        const backups = await this.readJsonFile(backupsFile);
        if (Array.isArray(backups)) {
          for (const backup of backups) {
            await this.migrateDataBackup(backup);
            count++;
          }
        }
      }

      console.log(`📊 Migrated ${count} data management items`);
      
    } catch (error) {
      const errorMsg = `Failed to migrate data management data: ${error instanceof Error ? error.message : 'Unknown error'}`;
      console.error(errorMsg);
      errors.push(errorMsg);
    }

    return count;
  }

  /**
   * Migrate a data export record
   */
  private async migrateDataExport(exportData: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    const result = await this.dbRun(
      `INSERT OR IGNORE INTO data_exports 
       (id, project_id, export_path, format, total_items, modules, 
        include_config, include_media, status, created_at, completed_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        exportData.id || randomUUID(),
        projectId,
        exportData.exportPath || exportData.path || '',
        exportData.format || 'json',
        exportData.totalItems || exportData.itemCount || 0,
        JSON.stringify(exportData.modules || []),
        exportData.includeConfig !== false,
        exportData.includeMedia === true,
        exportData.status || 'completed',
        exportData.createdAt || exportData.timestamp || now,
        exportData.completedAt || exportData.timestamp || now
      ]
    );

    if (!result.success) {
      throw new Error(`Failed to migrate export: ${result.error}`);
    }
  }

  /**
   * Migrate a data import record
   */
  private async migrateDataImport(importData: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    const result = await this.dbRun(
      `INSERT OR IGNORE INTO data_imports 
       (id, project_id, source_path, source_type, merge_strategy, imported_items, 
        imported_modules, backup_path, status, created_at, completed_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        importData.id || randomUUID(),
        projectId,
        importData.sourcePath || importData.path || '',
        importData.sourceType || 'atlas_export',
        importData.mergeStrategy || 'skip_existing',
        importData.importedItems || importData.itemCount || 0,
        JSON.stringify(importData.importedModules || importData.modules || []),
        importData.backupPath || null,
        importData.status || 'completed',
        importData.createdAt || importData.timestamp || now,
        importData.completedAt || importData.timestamp || now
      ]
    );

    if (!result.success) {
      throw new Error(`Failed to migrate import: ${result.error}`);
    }
  }

  /**
   * Migrate storage configuration
   */
  private async migrateStorageConfig(configData: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    const result = await this.dbRun(
      `INSERT OR REPLACE INTO storage_configs 
       (id, project_id, mode, custom_path, isolation, active, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        configData.id || randomUUID(),
        projectId,
        configData.mode || 'project-local',
        configData.customPath || configData.path || null,
        configData.isolation || 'project',
        configData.active !== false,
        configData.createdAt || now,
        configData.updatedAt || now
      ]
    );

    if (!result.success) {
      throw new Error(`Failed to migrate storage config: ${result.error}`);
    }
  }

  /**
   * Migrate a sync log record
   */
  private async migrateSyncLog(syncData: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    const result = await this.dbRun(
      `INSERT OR IGNORE INTO data_sync_logs 
       (id, project_id, sync_type, destination, modules, format, 
        file_count, total_size, status, created_at, completed_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        syncData.id || randomUUID(),
        projectId,
        syncData.syncType || 'to_project',
        syncData.destination || syncData.dest || '',
        JSON.stringify(syncData.modules || []),
        syncData.format || 'json',
        syncData.fileCount || syncData.files?.length || 0,
        syncData.totalSize || syncData.size || 0,
        syncData.status || 'completed',
        syncData.createdAt || syncData.timestamp || now,
        syncData.completedAt || syncData.timestamp || now
      ]
    );

    if (!result.success) {
      throw new Error(`Failed to migrate sync log: ${result.error}`);
    }
  }

  /**
   * Migrate a backup record
   */
  private async migrateDataBackup(backupData: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    const result = await this.dbRun(
      `INSERT OR IGNORE INTO data_backups 
       (id, project_id, backup_path, backup_type, modules, include_files, 
        compression_used, encryption_used, retention_days, file_size, 
        status, created_at, expires_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        backupData.id || randomUUID(),
        projectId,
        backupData.backupPath || backupData.path || '',
        backupData.backupType || 'full',
        JSON.stringify(backupData.modules || []),
        backupData.includeFiles === true,
        backupData.compressionUsed === true,
        backupData.encryptionUsed === true,
        backupData.retentionDays || 30,
        backupData.fileSize || backupData.size || null,
        backupData.status || 'completed',
        backupData.createdAt || backupData.timestamp || now,
        backupData.expiresAt || (now + (backupData.retentionDays || 30) * 24 * 60 * 60 * 1000)
      ]
    );

    if (!result.success) {
      throw new Error(`Failed to migrate backup: ${result.error}`);
    }
  }

  /**
   * Migrate a single embedding
   */
  private async migrateEmbedding(id: string, embedding: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    // Extract features and metadata from legacy embedding
    const features = embedding.metadata?.features || [];
    const filePath = embedding.metadata?.filePath || 'unknown';
    const language = embedding.metadata?.language || 'unknown';
    const content = embedding.metadata?.originalContent || embedding.text || '';

    // Convert legacy embedding data
    const embeddingData = {
      vector: embedding.vector || [],
      textHash: id,
      dimensions: embedding.vector?.length || 128,
      model: 'legacy-embedding'
    };

    await this.dbRun(
      `INSERT OR REPLACE INTO ai_embeddings 
       (id, project_id, file_path, content, language, features, functions, imports, 
        embedding_data, file_size, last_modified, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        projectId,
        filePath,
        content,
        language,
        JSON.stringify(features),
        JSON.stringify([]), // Functions not available in legacy format
        JSON.stringify([]), // Imports not available in legacy format
        JSON.stringify(embeddingData),
        content.length,
        embedding.timestamp ? new Date(embedding.timestamp).getTime() : now,
        embedding.timestamp ? new Date(embedding.timestamp).getTime() : now,
        now
      ]
    );
  }

  /**
   * Migrate a code memory node
   */
  private async migrateCodeMemoryNode(nodeId: string, node: any): Promise<void> {
    const projectId = 'default-project';
    const now = Date.now();

    // Convert legacy node to code change record
    if (node.type === 'file') {
      await this.dbRun(
        `INSERT OR REPLACE INTO ai_code_changes 
         (id, project_id, file_path, change_type, language, features_added, 
          functions_added, metadata, created_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          nodeId,
          projectId,
          node.name || 'unknown',
          'legacy_import',
          'unknown',
          JSON.stringify([]),
          JSON.stringify([]),
          JSON.stringify({
            legacyNode: true,
            originalType: node.type,
            properties: node.properties || {}
          }),
          node.timestamp ? new Date(node.timestamp).getTime() : now
        ]
      );
    }
  }

  /**
   * Migrate a search session
   */
  private async migrateSearchSession(search: any): Promise<void> {
    const projectId = 'default-project';
    
    await this.dbRun(
      `INSERT OR REPLACE INTO ai_search_sessions 
       (id, project_id, query, results_found, top_similarity, filters, created_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [
        search.id || `search-${randomUUID()}`,
        projectId,
        search.query || '',
        search.results?.length || 0,
        search.results?.[0]?.score || 0,
        JSON.stringify(search.filters || {}),
        search.timestamp ? new Date(search.timestamp).getTime() : Date.now()
      ]
    );
  }

  /**
   * Check if database actually contains migrated data
   */
  private async hasDataInDatabase(): Promise<boolean> {
    try {
      // Check for agile data (main indicator of migration)
      const sprintCount = await this.dbGet<{ count: number }>('SELECT COUNT(*) as count FROM agile_sprints');
      const storyCount = await this.dbGet<{ count: number }>('SELECT COUNT(*) as count FROM agile_stories');
      const epicCount = await this.dbGet<{ count: number }>('SELECT COUNT(*) as count FROM agile_epics');
      
      const hasAgileData = (sprintCount.data?.count > 0) || (storyCount.data?.count > 0) || (epicCount.data?.count > 0);
      
      return hasAgileData;
    } catch (error) {
      console.warn('⚠️ Failed to check database data:', error);
      return false;
    }
  }

  /**
   * Ensure default project exists for foreign key constraints
   */
  private async ensureDefaultProject(): Promise<void> {
    try {
      const defaultProjectId = 'default-project';
      
      // Check if default project already exists
      const existing = await this.dbGet(
        'SELECT id FROM projects WHERE id = ?',
        [defaultProjectId]
      );
      
      if (!existing.success || !existing.data) {
        // Create default project
        await this.dbRun(
          `INSERT INTO projects (id, name, description, config, created_at, updated_at) 
           VALUES (?, ?, ?, ?, ?, ?)`,
          [
            defaultProjectId,
            'Default Project',
            'Default project for migrated data',
            '{}',
            Date.now(),
            Date.now()
          ]
        );
        console.log('📁 Created default project for migration');
      }
    } catch (error) {
      console.warn('⚠️ Failed to ensure default project:', error);
      // Don't fail the entire migration for this
    }
  }
}