5why-analyzer/scripts/fix-indexes.js

#!/usr/bin/env node
/**
 * Fix Database Indexes
 * Ensures all indexes exist on renamed 5Why_ tables
 *
 * Run: node scripts/fix-indexes.js
 */

import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

const dbConfig = {
  host: process.env.DB_HOST || 'mysql.theaken.com',
  port: parseInt(process.env.DB_PORT) || 33306,
  user: process.env.DB_USER || 'A102',
  password: process.env.DB_PASSWORD || 'Bb123456',
  database: process.env.DB_NAME || 'db_A102'
};

// Index definitions for each table
const indexDefinitions = {
  '5Why_users': [
    { name: 'idx_employee_id', columns: 'employee_id' },
    { name: 'idx_email', columns: 'email' },
    { name: 'idx_role', columns: 'role' }
  ],
  '5Why_analyses': [
    { name: 'idx_user_id', columns: 'user_id' },
    { name: 'idx_status', columns: 'status' },
    { name: 'idx_created_at', columns: 'created_at' }
  ],
  '5Why_analysis_perspectives': [
    { name: 'idx_analysis_id', columns: 'analysis_id' }
  ],
  '5Why_analysis_whys': [
    { name: 'idx_perspective_id', columns: 'perspective_id' },
    { name: 'idx_level', columns: 'level' }
  ],
  '5Why_llm_configs': [
    { name: 'idx_provider', columns: 'provider' },
    { name: 'idx_is_active', columns: 'is_active' }
  ],
  '5Why_system_settings': [
    { name: 'idx_setting_key', columns: 'setting_key' },
    { name: 'idx_is_public', columns: 'is_public' }
  ],
  '5Why_audit_logs': [
    { name: 'idx_user_id', columns: 'user_id' },
    { name: 'idx_action', columns: 'action' },
    { name: 'idx_created_at', columns: 'created_at' },
    { name: 'idx_entity', columns: 'entity_type, entity_id' }
  ],
  '5Why_sessions': [
    { name: 'idx_expires', columns: 'expires' }
  ]
};

async function fixIndexes() {
  let connection;

  try {
    console.log('\n╔════════════════════════════════════════════════════════╗');
    console.log('║  5 Why Analyzer - Fix Database Indexes                 ║');
    console.log('╚════════════════════════════════════════════════════════╝\n');

    console.log('🔄 Connecting to database...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected successfully\n');

    // Process each table
    for (const [tableName, indexes] of Object.entries(indexDefinitions)) {
      console.log(`📋 Processing table: ${tableName}`);

      // Check if table exists
      const [tableExists] = await connection.execute(
        `SELECT COUNT(*) as count FROM information_schema.tables
         WHERE table_schema = ? AND table_name = ?`,
        [dbConfig.database, tableName]
      );

      if (tableExists[0].count === 0) {
        console.log(`   ⚠ Table ${tableName} not found, skipping...`);
        continue;
      }

      // Get existing indexes
      const [existingIndexes] = await connection.execute(
        `SELECT DISTINCT INDEX_NAME FROM information_schema.statistics
         WHERE table_schema = ? AND table_name = ?`,
        [dbConfig.database, tableName]
      );
      const existingIndexNames = existingIndexes.map(idx => idx.INDEX_NAME);

      // Create missing indexes
      for (const index of indexes) {
        if (existingIndexNames.includes(index.name)) {
          console.log(`   ✓ Index ${index.name} already exists`);
        } else {
          try {
            await connection.execute(
              `CREATE INDEX ${index.name} ON \`${tableName}\` (${index.columns})`
            );
            console.log(`   ✓ Created index: ${index.name} on (${index.columns})`);
          } catch (err) {
            if (err.code === 'ER_DUP_KEYNAME') {
              console.log(`   ⚠ Index ${index.name} already exists (duplicate)`);
            } else {
              console.error(`   ✗ Error creating index ${index.name}: ${err.message}`);
            }
          }
        }
      }
    }

    // Verify unique constraints on 5Why_users
    console.log('\n📋 Verifying unique constraints on 5Why_users...');

    // Check employee_id unique constraint
    const [empUnique] = await connection.execute(
      `SELECT COUNT(*) as count FROM information_schema.statistics
       WHERE table_schema = ? AND table_name = '5Why_users'
       AND column_name = 'employee_id' AND non_unique = 0`,
      [dbConfig.database]
    );

    if (empUnique[0].count > 0) {
      console.log('   ✓ employee_id has unique constraint');
    } else {
      console.log('   ⚠ employee_id missing unique constraint, adding...');
      try {
        await connection.execute(
          `ALTER TABLE 5Why_users ADD UNIQUE INDEX idx_employee_id_unique (employee_id)`
        );
        console.log('   ✓ Added unique constraint on employee_id');
      } catch (err) {
        if (err.code === 'ER_DUP_KEYNAME' || err.message.includes('Duplicate')) {
          console.log('   ✓ Unique constraint already exists');
        } else {
          console.error(`   ✗ Error: ${err.message}`);
        }
      }
    }

    // Check email unique constraint
    const [emailUnique] = await connection.execute(
      `SELECT COUNT(*) as count FROM information_schema.statistics
       WHERE table_schema = ? AND table_name = '5Why_users'
       AND column_name = 'email' AND non_unique = 0`,
      [dbConfig.database]
    );

    if (emailUnique[0].count > 0) {
      console.log('   ✓ email has unique constraint');
    } else {
      console.log('   ⚠ email missing unique constraint, adding...');
      try {
        await connection.execute(
          `ALTER TABLE 5Why_users ADD UNIQUE INDEX idx_email_unique (email)`
        );
        console.log('   ✓ Added unique constraint on email');
      } catch (err) {
        if (err.code === 'ER_DUP_KEYNAME' || err.message.includes('Duplicate')) {
          console.log('   ✓ Unique constraint already exists');
        } else {
          console.error(`   ✗ Error: ${err.message}`);
        }
      }
    }

    console.log('\n╔════════════════════════════════════════════════════════╗');
    console.log('║  Index Fix Complete!                                   ║');
    console.log('╚════════════════════════════════════════════════════════╝\n');

    // Show current indexes for 5Why_users
    console.log('📊 Current indexes on 5Why_users:');
    const [userIndexes] = await connection.execute(
      `SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
       FROM information_schema.statistics
       WHERE table_schema = ? AND table_name = '5Why_users'
       ORDER BY INDEX_NAME, SEQ_IN_INDEX`,
      [dbConfig.database]
    );

    userIndexes.forEach(idx => {
      const unique = idx.NON_UNIQUE === 0 ? ' (UNIQUE)' : '';
      console.log(`   - ${idx.INDEX_NAME}: ${idx.COLUMN_NAME}${unique}`);
    });

  } catch (error) {
    console.error('\n❌ Fix indexes failed:');
    console.error('   Error:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 Database connection closed\n');
    }
  }
}

// Execute
fixIndexes();