// Migration script to update existing favorites and create indexes
// Run with: node migrate-favorites.js

import mongoose from 'mongoose';
import dotenv from 'dotenv';
import Favorite from './models/favorite.js';

dotenv.config();

const colors = {
  reset: '\x1b[0m',
  green: '\x1b[32m',
  red: '\x1b[31m',
  yellow: '\x1b[33m',
  blue: '\x1b[34m'
};

function log(message, color = 'reset') {
  console.log(`${colors[color]}${message}${colors.reset}`);
}

async function migrate() {
  try {
    // Connect to MongoDB
    log('\n🔌 Connecting to MongoDB...', 'blue');
    await mongoose.connect(process.env.MONGO_URI, {
      useNewUrlParser: true,
      useUnifiedTopology: true,
    });
    log('✅ Connected to MongoDB', 'green');

    // Get database statistics before migration
    log('\n📊 Collecting statistics...', 'blue');
    const totalFavorites = await Favorite.countDocuments();
    log(`Total favorites in database: ${totalFavorites}`, 'yellow');

    // Check for duplicates
    log('\n🔍 Checking for duplicate favorites...', 'blue');
    const duplicates = await Favorite.aggregate([
      {
        $group: {
          _id: {
            user: '$user',
            nomineeWebsite: '$nomineeWebsite',
            directory: '$directory',
            aiWebsite: '$aiWebsite'
          },
          count: { $sum: 1 },
          ids: { $push: '$_id' }
        }
      },
      {
        $match: { count: { $gt: 1 } }
      }
    ]);

    if (duplicates.length > 0) {
      log(`⚠️  Found ${duplicates.length} sets of duplicate favorites`, 'yellow');
      
      // Remove duplicates (keep the oldest one)
      let removedCount = 0;
      for (const dup of duplicates) {
        const idsToRemove = dup.ids.slice(1); // Keep first, remove rest
        const result = await Favorite.deleteMany({ _id: { $in: idsToRemove } });
        removedCount += result.deletedCount;
      }
      
      log(`🗑️  Removed ${removedCount} duplicate favorites`, 'green');
    } else {
      log('✅ No duplicates found', 'green');
    }

    // Verify data integrity BEFORE creating indexes
    log('\n🔍 Verifying data integrity...', 'blue');
    
    // Find entries where ALL three fields are null
    const allNullFavorites = await Favorite.find({
      $and: [
        { nomineeWebsite: null },
        { directory: null },
        { aiWebsite: null }
      ]
    });
    
    // Find entries where itemType doesn't match the populated field
    const mismatchedFavorites = await Favorite.find({
      $or: [
        { itemType: 'nominee', nomineeWebsite: null },
        { itemType: 'directory', directory: null },
        { itemType: 'ai', aiWebsite: null }
      ]
    });

    const totalInvalid = allNullFavorites.length + mismatchedFavorites.length;
    
    if (totalInvalid > 0) {
      log(`⚠️  Found ${totalInvalid} invalid favorites:`, 'yellow');
      log(`    - ${allNullFavorites.length} with no item reference`, 'yellow');
      log(`    - ${mismatchedFavorites.length} with mismatched itemType`, 'yellow');
      
      // Show details of mismatched entries
      if (mismatchedFavorites.length > 0) {
        mismatchedFavorites.forEach(fav => {
          log(`    - ID: ${fav._id}, Type: ${fav.itemType}, User: ${fav.user}`, 'yellow');
        });
      }
      
      log('  Removing invalid favorites...', 'yellow');
      
      // Remove all null entries
      const deleteResult1 = await Favorite.deleteMany({
        $and: [
          { nomineeWebsite: null },
          { directory: null },
          { aiWebsite: null }
        ]
      });
      
      // Remove mismatched entries
      const deleteResult2 = await Favorite.deleteMany({
        $or: [
          { itemType: 'nominee', nomineeWebsite: null },
          { itemType: 'directory', directory: null },
          { itemType: 'ai', aiWebsite: null }
        ]
      });
      
      const totalDeleted = deleteResult1.deletedCount + deleteResult2.deletedCount;
      log(`✅ Removed ${totalDeleted} invalid favorites`, 'green');
    } else {
      log('✅ All favorites have valid item references', 'green');
    }

    // Create indexes
    log('\n🔧 Creating database indexes...', 'blue');
    
    try {
      // Drop existing indexes (except _id)
      const existingIndexes = await Favorite.collection.getIndexes();
      for (const indexName of Object.keys(existingIndexes)) {
        if (indexName !== '_id_') {
          await Favorite.collection.dropIndex(indexName);
          log(`  Dropped index: ${indexName}`, 'yellow');
        }
      }
    } catch (error) {
      log('  No existing indexes to drop', 'yellow');
    }

    // Create new indexes
    await Favorite.createIndexes();
    log('✅ All indexes created successfully', 'green');

    // List all indexes
    const indexes = await Favorite.collection.getIndexes();
    log('\n📋 Current indexes:', 'blue');
    Object.keys(indexes).forEach(indexName => {
      console.log(`  - ${indexName}:`, indexes[indexName]);
    });

    // Update itemType for existing records if missing or incorrect
    log('\n🔄 Updating itemType fields...', 'blue');
    
    // Update nominees
    const nomineeUpdate = await Favorite.updateMany(
      { nomineeWebsite: { $ne: null }, itemType: { $ne: 'nominee' } },
      { $set: { itemType: 'nominee' } }
    );
    if (nomineeUpdate.modifiedCount > 0) {
      log(`  Updated ${nomineeUpdate.modifiedCount} nominee itemTypes`, 'green');
    }

    // Update directories
    const directoryUpdate = await Favorite.updateMany(
      { directory: { $ne: null }, itemType: { $ne: 'directory' } },
      { $set: { itemType: 'directory' } }
    );
    if (directoryUpdate.modifiedCount > 0) {
      log(`  Updated ${directoryUpdate.modifiedCount} directory itemTypes`, 'green');
    }

    // Update AI websites
    const aiUpdate = await Favorite.updateMany(
      { aiWebsite: { $ne: null }, itemType: { $ne: 'ai' } },
      { $set: { itemType: 'ai' } }
    );
    if (aiUpdate.modifiedCount > 0) {
      log(`  Updated ${aiUpdate.modifiedCount} ai itemTypes`, 'green');
    }

    // Final statistics
    log('\n📊 Final Statistics:', 'blue');
    const finalCount = await Favorite.countDocuments();
    const byType = await Favorite.aggregate([
      { $group: { _id: '$itemType', count: { $sum: 1 } } }
    ]);
    
    log(`Total favorites: ${finalCount}`, 'yellow');
    byType.forEach(type => {
      log(`  - ${type._id}: ${type.count}`, 'yellow');
    });

    log('\n✅ Migration completed successfully!', 'green');
    log('🎉 Your favorites database is now optimized and ready to use!', 'green');

  } catch (error) {
    log('\n❌ Migration failed:', 'red');
    console.error(error);
  } finally {
    // Close connection
    await mongoose.connection.close();
    log('\n🔌 Database connection closed', 'blue');
  }
}

// Run migration
log('🚀 Starting Favorites Database Migration', 'yellow');
log('='.repeat(50), 'yellow');

migrate().catch(error => {
  log('\n❌ Fatal error:', 'red');
  console.error(error);
  process.exit(1);
});
