const sequelize = require('../config/database');
const { QueryTypes } = require('sequelize');

async function cleanupIndexes() {
    try {
        await sequelize.authenticate();
        console.log('Database connected...');

        const tableName = 'users';

        // fetch existing indexes
        const [results] = await sequelize.query(`SHOW INDEX FROM ${tableName}`);

        // Group indexes by key_name
        const indexes = {};
        results.forEach(row => {
            if (!indexes[row.Key_name]) {
                indexes[row.Key_name] = [];
            }
            indexes[row.Key_name].push(row);
        });

        console.log(`Found ${Object.keys(indexes).length} unique indexes on table '${tableName}'.`);

        // Identify duplicate indexes
        // We want to keep 'PRIMARY', and arguably the first instance of 'username', 'email' etc.
        // Usually valid indexes are 'PRIMARY', 'username', 'email'.
        // Duplicates often look like 'username_2', 'username_3', or 'users_username_unique' etc.

        const validIndexes = ['PRIMARY', 'username', 'email', 'users_username_unique', 'users_email_unique'];
        // Note: Sequelize default naming might vary, but simplified names are usually preferred.
        // Let's drop anything that ends in _number or looks like a duplicate.

        const indexesToDrop = [];

        for (const keyName of Object.keys(indexes)) {
            // Logic: 
            // 1. Keep PRIMARY
            if (keyName === 'PRIMARY') continue;

            // 2. If it's a simple name like 'username' or 'email' (and we haven't seen it), keep it.
            // But wait, the problem is we have TOO MANY.
            // Let's aggressively drop anything that looks like a generated duplicate suffix.
            // Regex for ending in _\d+
            if (keyName.match(/_\d+$/)) {
                indexesToDrop.push(keyName);
            }
            // Also check for multiple "unique" naming variations if necessary
            // For now, let's just list them to the user or auto-drop the obvious spam ones.
        }

        if (indexesToDrop.length === 0) {
            console.log('No obvious duplicate indexes ending in _number found.');
            console.log('Listing all indexes for manual review if needed:');
            console.log(Object.keys(indexes));

            // Fallback: if we have way too many, maybe we should drop ALL except PRIMARY and let Sequelize re-add the correct ones?
            // Actually, safest is to drop the ones that likely caused the overflow.
            // If the user has 64 indexes, most are junk.
            // Let's look for anything that isn't 'PRIMARY', 'username', 'email'.

            const keepers = ['PRIMARY', 'username', 'email'];
            const candidates = Object.keys(indexes).filter(k => !keepers.includes(k));

            if (candidates.length > 5) {
                console.log(`\nDetected ${candidates.length} potential extra indexes. Dropping all except PRIMARY, username, email.`);
                // push all candidates to drop
                candidates.forEach(c => {
                    if (!indexesToDrop.includes(c)) indexesToDrop.push(c);
                });
            }
        }

        console.log(`Dropping ${indexesToDrop.length} indexes...`);

        for (const indexName of indexesToDrop) {
            try {
                console.log(`Dropping index: ${indexName}`);
                await sequelize.query(`DROP INDEX \`${indexName}\` ON ${tableName}`);
            } catch (err) {
                console.error(`Failed to drop ${indexName}:`, err.message);
            }
        }

        console.log('Cleanup complete.');
        process.exit(0);
    } catch (error) {
        console.error('Error during cleanup:', error);
        process.exit(1);
    }
}

cleanupIndexes();
