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

async function addShopAssignmentsToUsers() {
  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('Adding assignedShopIds and assignedShopNames columns to users table...');
    
    // Check if columns already 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 
        ADD COLUMN assignedShopIds JSON DEFAULT ('[]')
      `);
      console.log('✅ Added assignedShopIds column');
    } else {
      console.log('⚠️  assignedShopIds column already exists');
    }

    if (!existingColumns.includes('assignedShopNames')) {
      await connection.execute(`
        ALTER TABLE users 
        ADD COLUMN assignedShopNames JSON DEFAULT ('[]')
      `);
      console.log('✅ Added assignedShopNames column');
    } else {
      console.log('⚠️  assignedShopNames column already exists');
    }

    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) {
  addShopAssignmentsToUsers()
    .then(() => {
      console.log('Migration script completed');
      process.exit(0);
    })
    .catch((error) => {
      console.error('Migration script failed:', error);
      process.exit(1);
    });
}

module.exports = addShopAssignmentsToUsers;