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

async function addMasterAssignmentToOrders() {
  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 masterId and status columns to orders table...');
    
    // Check if columns already exist
    const [columns] = await connection.execute(`
      SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'orders' 
      AND COLUMN_NAME IN ('masterId', 'status')
    `, [process.env.DB_NAME || 'tuc']);

    const existingColumns = columns.map(col => col.COLUMN_NAME);
    
    if (!existingColumns.includes('masterId')) {
      // First, check if we have any Master users
      const [masters] = await connection.execute(`
        SELECT id FROM users WHERE role = 'Master' AND isDeleted = 0 LIMIT 1
      `);

      if (masters.length === 0) {
        console.log('⚠️  No Master users found. Creating a default Master user...');
        
        // Create a properly hashed password
        const defaultPassword = 'master123'; // You can change this
        const hashedPassword = await bcrypt.hash(defaultPassword, 10);
        
        await connection.execute(`
          INSERT INTO users (name, email, phoneNumber, password, role, modules, isDeleted) 
          VALUES ('Default Master', 'master@tuc.com', '1234567890', ?, 'Master', '[]', 0)
        `, [hashedPassword]);
        
        const [newMaster] = await connection.execute(`
          SELECT id FROM users WHERE email = 'master@tuc.com'
        `);
        
        console.log(`✅ Created default Master user with ID: ${newMaster[0].id}`);
        console.log(`   Email: master@tuc.com`);
        console.log(`   Password: ${defaultPassword}`);
        console.log(`   ⚠️  Please change this password after login!`);
      }

      // Get the first available Master user ID
      const [availableMaster] = await connection.execute(`
        SELECT id FROM users WHERE role = 'Master' AND isDeleted = 0 LIMIT 1
      `);
      
      const defaultMasterId = availableMaster[0].id;
      console.log(`Using Master ID ${defaultMasterId} as default for existing orders`);

      // Add masterId column as nullable first
      await connection.execute(`
        ALTER TABLE orders 
        ADD COLUMN masterId INT NULL AFTER schoolId
      `);
      console.log('✅ Added masterId column (nullable)');

      // Update existing orders with the default Master ID
      const [updateResult] = await connection.execute(`
        UPDATE orders SET masterId = ? WHERE masterId IS NULL
      `, [defaultMasterId]);
      
      console.log(`✅ Updated ${updateResult.affectedRows} existing orders with default Master ID`);

      // Now make the column NOT NULL and add foreign key constraint
      await connection.execute(`
        ALTER TABLE orders 
        MODIFY COLUMN masterId INT NOT NULL,
        ADD CONSTRAINT orders_masterId_users_id_fk 
        FOREIGN KEY (masterId) REFERENCES users(id)
      `);
      console.log('✅ Made masterId NOT NULL and added foreign key constraint');
    } else {
      console.log('⚠️  masterId column already exists');
    }

    if (!existingColumns.includes('status')) {
      await connection.execute(`
        ALTER TABLE orders 
        ADD COLUMN status ENUM('Pending', 'Tailor', 'Stitching', 'Kajbutton', 'Done') 
        NOT NULL DEFAULT 'Pending' AFTER type
      `);
      console.log('✅ Added status column');
    } else {
      console.log('⚠️  status 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) {
  addMasterAssignmentToOrders()
    .then(() => {
      console.log('Migration script completed');
      process.exit(0);
    })
    .catch((error) => {
      console.error('Migration script failed:', error);
      process.exit(1);
    });
}

module.exports = addMasterAssignmentToOrders;