const mysql = require('mysql2/promise');

async function fixCategoryTable() {
  let connection;
  
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'smile24',
      port: 3306
    });

    console.log('🔧 Fixing Category table schema...');

    // Add the missing parentId column
    await connection.execute(`
      ALTER TABLE Category 
      ADD COLUMN IF NOT EXISTS parentId VARCHAR(191) NULL AFTER icon
    `);

    console.log('✅ Added parentId column to Category table');

    // Add foreign key constraint
    try {
      await connection.execute(`
        ALTER TABLE Category 
        ADD CONSTRAINT fk_category_parent 
        FOREIGN KEY (parentId) REFERENCES Category(id) ON DELETE SET NULL
      `);
      console.log('✅ Added foreign key constraint for parentId');
    } catch (error) {
      if (!error.message.includes('already exists')) {
        console.log('⚠️  Foreign key constraint may already exist:', error.message);
      }
    }
    
  } catch (error) {
    console.error('❌ Error fixing Category table:', error.message);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

fixCategoryTable();
