const { pool } = require('./src/config/database')

async function migrateSecurityTables() {
  console.log('🔄 Migrating security tables to add organization_id...\n')
  
  try {
    // Check if columns already exist
    console.log('Checking existing table structure...')
    
    const [flaColumns] = await pool.execute(`
      SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'failed_login_attempts' 
        AND COLUMN_NAME = 'organization_id'
    `)
    
    const [pchColumns] = await pool.execute(`
      SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'password_change_history' 
        AND COLUMN_NAME = 'organization_id'
    `)
    
    // Migrate failed_login_attempts table
    if (flaColumns.length === 0) {
      console.log('\n📊 Migrating failed_login_attempts table...')
      
      // Add column
      console.log('  Adding organization_id column...')
      await pool.execute(`
        ALTER TABLE failed_login_attempts 
        ADD COLUMN organization_id int(11) DEFAULT NULL AFTER login_id
      `)
      console.log('  ✅ Column added')
      
      // Add index
      console.log('  Adding index on organization_id...')
      await pool.execute(`
        ALTER TABLE failed_login_attempts 
        ADD KEY idx_organization_id (organization_id)
      `)
      console.log('  ✅ Index added')
      
      // Add foreign key
      console.log('  Adding foreign key constraint...')
      try {
        await pool.execute(`
          ALTER TABLE failed_login_attempts 
          ADD CONSTRAINT failed_login_attempts_ibfk_1 
          FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE SET NULL
        `)
        console.log('  ✅ Foreign key constraint added')
      } catch (error) {
        if (error.code === 'ER_DUP_KEYNAME') {
          console.log('  ⚠️  Foreign key constraint already exists, skipping')
        } else {
          throw error
        }
      }
      
      // Populate organization_id from existing data
      console.log('  Populating organization_id from existing data...')
      const [updateResult] = await pool.execute(`
        UPDATE failed_login_attempts fla
        LEFT JOIN users u ON fla.login_id = u.login_id
        SET fla.organization_id = u.organization_id
        WHERE u.organization_id IS NOT NULL
      `)
      console.log(`  ✅ Updated ${updateResult.affectedRows} rows with organization_id`)
      
      console.log('✅ failed_login_attempts migration completed!')
    } else {
      console.log('\n✅ failed_login_attempts already has organization_id column')
    }
    
    // Migrate password_change_history table
    if (pchColumns.length === 0) {
      console.log('\n📊 Migrating password_change_history table...')
      
      // Add column
      console.log('  Adding organization_id column...')
      await pool.execute(`
        ALTER TABLE password_change_history 
        ADD COLUMN organization_id int(11) NOT NULL DEFAULT 1 AFTER changed_by
      `)
      console.log('  ✅ Column added')
      
      // Add index
      console.log('  Adding index on organization_id...')
      await pool.execute(`
        ALTER TABLE password_change_history 
        ADD KEY idx_organization_id (organization_id)
      `)
      console.log('  ✅ Index added')
      
      // Add foreign key
      console.log('  Adding foreign key constraint...')
      try {
        await pool.execute(`
          ALTER TABLE password_change_history 
          ADD CONSTRAINT password_change_history_ibfk_3 
          FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE
        `)
        console.log('  ✅ Foreign key constraint added')
      } catch (error) {
        if (error.code === 'ER_DUP_KEYNAME') {
          console.log('  ⚠️  Foreign key constraint already exists, skipping')
        } else {
          throw error
        }
      }
      
      // Populate organization_id from existing data
      console.log('  Populating organization_id from existing data...')
      const [updateResult] = await pool.execute(`
        UPDATE password_change_history pch
        INNER JOIN users u ON pch.user_id = u.id
        SET pch.organization_id = u.organization_id
      `)
      console.log(`  ✅ Updated ${updateResult.affectedRows} rows with organization_id`)
      
      console.log('✅ password_change_history migration completed!')
    } else {
      console.log('\n✅ password_change_history already has organization_id column')
    }
    
    // Verify migration
    console.log('\n🔍 Verifying migration...')
    
    const [flaCount] = await pool.execute(`
      SELECT 
        COUNT(*) as total,
        COUNT(organization_id) as with_org_id,
        COUNT(*) - COUNT(organization_id) as without_org_id
      FROM failed_login_attempts
    `)
    
    const [pchCount] = await pool.execute(`
      SELECT 
        COUNT(*) as total,
        COUNT(organization_id) as with_org_id
      FROM password_change_history
    `)
    
    console.log('\nfailed_login_attempts:')
    console.log(`  Total records: ${flaCount[0].total}`)
    console.log(`  With organization_id: ${flaCount[0].with_org_id}`)
    console.log(`  Without organization_id (NULL): ${flaCount[0].without_org_id}`)
    
    console.log('\npassword_change_history:')
    console.log(`  Total records: ${pchCount[0].total}`)
    console.log(`  With organization_id: ${pchCount[0].with_org_id}`)
    
    console.log('\n✅ Migration completed successfully!')
    console.log('\n📝 Next steps:')
    console.log('  1. Restart your backend server')
    console.log('  2. Test the security endpoints')
    console.log('  3. Verify organization isolation is working')
    
  } catch (error) {
    console.error('\n❌ Migration failed:', error.message)
    console.error('\nError details:', error)
    throw error
  } finally {
    await pool.end()
  }
}

// Run the migration
migrateSecurityTables()
  .then(() => {
    console.log('\n🎉 Migration script completed!')
    process.exit(0)
  })
  .catch((error) => {
    console.error('\n❌ Migration script failed:', error)
    process.exit(1)
  })

