const mysql = require('mysql2/promise');
require('dotenv').config();

async function removeShopAssignmentsFromUsers() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'tuc'
  });

  try {
    console.log('Removing assignedShopIds and assignedShopNames columns from users table...');
    
    // Check if columns exist
    const [columns] = await connection.execute(`
      SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users' 
      AND COLUMN_NAME IN ('assignedShopIds', 'assignedShopNames')
    `, [process.env.DB_NAME || 'tuc']);

    const existingColumns = columns.map(col => col.COLUMN_NAME);
    
    if (existingColumns.includes('assignedShopIds')) {
      await connection.execute(`
        ALTER TABLE users 
        DROP COLUMN assignedShopIds
      `);
      console.log('✅ Removed assignedShopIds column');
    } else {
      console.log('⚠️  assignedShopIds column does not exist');
    }

    if (existingColumns.includes('assignedShopNames')) {
      await connection.execute(`
        ALTER TABLE users 
        DROP COLUMN assignedShopNames
      `);
      console.log('✅ Removed assignedShopNames column');
    } else {
      console.log('⚠️  assignedShopNames column does not exist');
    }

    console.log('✅ Migration completed successfully!');
    
  } catch (error) {
    console.error('❌ Migration failed:', error.message);
    throw error;
  } finally {
    await connection.end();
  }
}

// Run the migration
if (require.main === module) {
  removeShopAssignmentsFromUsers()
    .then(() => {
      console.log('Migration script completed');
      process.exit(0);
    })
    .catch((error) => {
      console.error('Migration script failed:', error);
      process.exit(1);
    });
}

module.exports = removeShopAssignmentsFromUsers;